To create a database named “school”:
create database school;
To open the database named “school” and work on it:
Note that semicolon is the standard way to end SQL statement.
To create a table “learner” with columns for roll number and name:
create table(roll integer, name varchar(80));
Note that MySQL statements, table names and column names are not case-sensitive.
To view the names of all the tables in the current database:
To add a row in the table “learner” with roll “1” and name “Larry Page”:
insert into learner values(1, 'Larry Page');
Note that while inserting rows, the character, date and time should be enclosed in quotes.
To view the contents of the table “learner”:
select * from learner;
To know the database currently in use:
A keyword refers to a special word that has a special meaning to SQL. Examples include select, from, etc.
A clause is a portion of an SQL statement. Each clause is identified by a keyword. For example,
select name is a clause,
from learner is a clause.
A statement is a combination of two or more clauses. For example,
select name from learner; is a statement.
Data types indicate the type of data that you are storing in a column. Following are the data types in MySQL:
- char(size): It is a fixed length string from 1 to 255 characters, right padded with spaces to the specified length when stored. The values must be enclosed in single or double quotes.
- varchar(size): It is a variable length string from 1 to 255 characters in length. The values must be enclosed in single or double quotes.
- decimal(digits, d): It can represent numbers with or without fractional part. Here, “digits” is the maximum number of digits, whereas “d” represents the maximum number of digits after the decimal point.
- int or integer: It is used for storing integers. It supports a maximum of 11 digits.
- date: It represents the date including day, month and year.
- time: It represents time in HH:MM:SS format.
DDL (Data Definition Language) Commands in MySQL allows us to create or delete database tables. They also define indices, specifies links between tables, and imposes table constraints.
Examples of DDL commands include
DML (Data Manipulation Language) Commands include all the query and update commands in SQL.
Examples of DML commands include
Note that while creating a new table, if that table already exists in the database, then it will show an error message “Table already exists”.
To view the structure of a table named “student”:
describe student; or desc student;
To add a column “games” in the “student” table:
alter table student add games varchar(20);
Note that a NULL value is assigned to all the rows in the newly added column.
To change the data type of column “games” to an integer in the “student” table:
alter table student modify games integer;
To delete the column “games” from the “student” table:
alter table student drop games;
To retrieve data from a single column “roll” from “student” table:
select roll from student;
To display data from multiple columns from “student” table:
select roll, name from student;
To change the order of columns while displaying data from tables (name first and then the roll number):
select name, roll from student;
Note that when the data is displayed, the character data is left justified, and the numeric data is right justified.
To retrieve data from all the columns of “student” table:
select * from student;
In the above command, “*” means “all”.
The distinct keyword helps us to eliminate duplicate values from column “math” in “student” table:
select distinct math from student;
Following are the arithmetic operators in MySQL:
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Modulus or remainder (%)
We can perform simple arithmetic operations using the “select” statement. Below are some examples:
select 2 * 5 + 7;
select math + 5 from student;
In the above statement, the values are not modified. Only the results show the modified values.
Column Alias lets different names to appear for different columns:
select math as "Marks in Math" from student;
The above statement doesn’t modify the actual column’s name. Also, the “as” keyword is optional.
We can also put text in the query result:
select roll, name, 'has secured ', math from student;
We can retrieve specific rows using the “where” clause:
select name, math from student where math > 39;
Following are the relational operators in MySQL:
- Equal to (=)
- Less than (<)
- Greater than (>)
- Less than or equal to (<=)
- Greater than or equal to (>=)
- Not equal to (!= or <>)
For character data types, < means earlier in the alphabet, and > means later in the alphabet.
Following are the logical operators in MySQL:
- OR (returns true when any one condition is true)
- AND (requires both conditions to be true)
- NOT (negates a condition)
We can also use || symbol for OR, && symbol for AND, and ! symbol for NOT.
To view the marks in the range 90 to 100:
select * from student where math >= 90 and math <= 100;
To view the employee details where the designation is not “manager”:
select * from employee where not (designation = 'manager');
We can also use parentheses to change the precedence of the various operators in MySQL:
select * from employee where first = 'Steve' and (last = 'Jobs' OR last = 'Ballmer');
To specify conditions using a range, we can use “between” operator also:
select * from student where math between 0 and 39;
In the above statement, both the values, 0 and 39 are included in the range.
To do just the opposite of the above statement, we could use the NOT operator:
select * from student where math not between 0 and 39;
We can also specify conditions based on lists:
select * from student where math in (40, 90, 100);
select * from employee where city in('BANGALORE', 'HYDERABAD', 'MUMBAI');
We could use the NOT operator to do just the opposite of the above statement:
select * from employee where city not in('BANGALORE', 'HYDERABAD', 'MUMBAI');
We can use wildcards to do pattern matches in our SQL query. MySQL uses two wildcard characters: % and _.
The % symbol represents any sequence of zero or more characters. The _ symbol represents a single character.
The “like” clause is used to fetch data that matches the specified pattern:
select * from student where name like '%st';
To display names starting with ‘s’ and then having 3 characters and then ending with ‘smith’:
select name from student where name like 's___smith';
We can use the NOT LIKE to do just the opposite of what LIKE does:
select * from student where name not like 'st%';
NULL in MySQL means a value that is unassigned, or unknown or unavailable. We use the IS NULL keywords for searching for NULL:
select * from student where name is null;
NOT NULL values can be searched using IS NOT NULL:
select * from student where name is not null;
If any column value involved in an arithmetic expression is NULL, the result of that arithmetic expression will also be NULL.
To sort the results of a query, we use ORDER BY:
select * from student order by math;
To sort in descending order:
select * from student order by math desc;
ASC is set, so the data is sorted in ascending order.
To sort based on “math” column, and then by “name” column:
select * from student order by math, name;
To sort “math” in descending order but name in ascending order:
select * from student order by math desc, name;
To apply conditions while sorting:
select * from student where math >= 90 order by name;
We can also sort using column aliases:
select name, math as Marks from student order by Marks;
We can specify specific column names and their corresponding values while inserting a row:
insert into student(roll, name, math) values(26, 'Satya Nadella', 90);
We can also insert date using the “insert” keyword:
insert into employee (joining) values ('2012-08-01');
To set the marks in math to 90 for all rows:
update student set math = 90;
To set the marks in math to 90 where name is ‘Sundar Pichai’:
update student set math = 90 where name = 'sundar pichai';
To update the name and marks of roll number 26:
update student set name = 'Bill Gates', math = 100 where roll = 26;
To delete the row with roll number 25:
delete from student where roll = 25;