Skip To: Rows | Table | Database | User Accounts
Rows
- SELECT - Querying for data in one or more tables.
- Syntax
SELECT column1,column2 FROM table WHERE column1='value' AND column2='value' ORDER BY column3 LIMIT 5;
- Basic Matching
- Looking for a specific title
select album_id,album_name,date_produced from album where album_name='My Album';
- Looking for all columns
select * from album where album_name='My Album';
- Matching a specific title, case insensitive (by lower casing the conditions).
select * from album where lower(album_name) = lower('my ALBUM');
- Looking for all records that have the word 'love' anywhere in the title
select * from album where album_name like '%love%';
- Looking for all records that start with the word 'The' in the title
select * from album where album_name like 'The%';
- Multiple Conditions
- AND conditions
select * from album where album_name like '%love%' and date_produced > '2002-05-23';
- OR conditions
select * from album where album_name like '%love%' or album_name like '%death%';
- Mixed conditions
select * from album where album_name like '%love%' or album_name like '%death%' and date_produced > '2002-05-23';
- Mixed conditions with mathematical style order of operations
select * from album where (album_name like '%love%' or album_name like '%death%') and date_produced > '2002-05-23';
- Joining Tables
- Joining artist and album tables by the artist_id
select album.*,artist.* from album,artist where album.album_name like '%love%' and album.artist_id=artist.artist_id';
- Altering the Output
- Sorting the output by a given column.
select * from album where album_name like '%love%' order by date_produced;
- Sorting the output by a given column reversed.
select * from album where album_name like '%love%' order by date_produced desc;
- Only returning 4 rows of data (MySQL method).
select * from album where album_name like '%love%' limit 4;
- Functions
- Select only the number of rows found.
select count(*) from album where album_name like '%love%';
- Select the sum of salaries for all Peons.
select sum(salary) from employee where position = 'Peon';
- Select the highest, lowest, and average Peon salary.
select max(salary) from employee where position = 'Peon';
select min(salary) from employee where position = 'Peon';
select avg(salary) from employee where position = 'Peon';
- Reference
- INSERT - Adding a record to a table.
- Syntax
INSERT INTO table (column1,column2) VALUES (value1,value2);
- Basic Examples
- Inserting a new row, filling out first name, last name, and title.
insert into employee (first,last,title) values ('Gerry','Gosselin','Programmer');
- Inserting a new row, filling out first name, last name, and title (different order).
insert into employee (last,first,title) values ('Gosselin','Gerry','Programmer');
- Reference
- UPDATE - Changing one or more field of data in a record.
- Syntax
UPDATE table SET column1=value1, column2=value2 WHERE conditions;
- Basic Examples
- Updating all rows in a table (CAREFUL)
update employee set first='Gerry', last='Gosselin';
- Updating only employee id 4178
update employee set first='Gerry', last='Gosselin' where employee_id=4178;
- Reference
- DELETE - Deleting one or more record from a table.
- Syntax
DELETE FROM table WHERE condition;
- Basic Examples
- Deleting all rows in a table (CAREFUL)
- Deleting only employee id 4178
delete from employee where employee_id=4178;
- Reference
Skip To: Rows | Table | Database | User Accounts
Table
- CREATE - Create a new table in a database.
- Syntax
CREATE TABLE table (column1 type flags, column2 type flags, column3 type flags);
- Basic Examples
- Creating an employee table with an id, first name, and last name.
create table employee (employee_id int, first varchar(25), last varchar(25));
- This time the id column is an auto incrementing primary key
create table employee (employee_id int primary key auto_increment, first varchar(25), last varchar(25));
- This time first name and last name can not have null values.
create table employee (employee_id int primary key auto_increment, first varchar(25) not null, last varchar(25) not null);
- Using an unsigned int instead, more efficient for primary keys.
create table employee (employee_id int unsigned primary key auto_increment, first varchar(25) not null, last varchar(25) not null);
- Reference
- DROP - Remove a table from a database.
- Syntax
- Example
- Dropping the employee table
- Reference
- ALTER - Change an existing table.
- Syntax
ALTER TABLE table (MODIFY|CHANGE|ADD|DROP) column1 type flags, column2 type flags;
- Basic Examples
- Modifying the first name to be a varchar(35)
alter table employee modify first varchar(35) not null;
- Add a social security column
alter table employee add ss varchar(11);
- Drop the social security column
alter table employee drop ss;
- Rename the social security column
alter table employee change ss socsec varchar(11);
- Reference
Skip To: Rows | Table | Database | User Accounts
Database
- CREATE - Create a new database.
- Syntax
CREATE DATABASE database;
- Example
- Creating database 'department'
create database department;
- Reference
- DROP - Remove a database.
- Syntax
- Example
- Dropping database 'department'
drop database department;
- Reference
Skip To: Rows | Table | Database | User Accounts
User Account
- GRANT - Grant a user privileges to a database.
- Syntax
GRANT ALL ON database TO user IDENTIFIED BY password('password');
- Example
- Will create the user myUser if it doesn't exist already
grant all on myDB to myUser identified by password('myPassword');
- Reference
- REVOKE - Remove a user's privileges to a database.
- Syntax
REVOKE ALL ON database FROM user;
- Example
- Will revoke access from myUser.
revoke all on myDB from myUser;
- Reference