MySQL Queries

Select Clause
 In order to use a specific database:

Use (database_name). Like, use Hotak;

 To select all columns from a table:

select * from Student;

 To select a specific column from a table like ID:

select *

from Student

where student_id=1;

 To order by any column like NAME:

select *

from Student

order by student_first_name;

 We can also comment any line, using double syphon operator:

select *

from Student

-- where student_id=1

order by student_first_name;

 To select any column or columns:

select student_first_name, student_last_name

from Student;

 To give discount for students and give the column a descriptive name:
select

student_id,

student_first_name,

student_points,

(student_points+10) *100 as Discount_book

from Student;

 We can also make the descriptive names with spaces with either single of double quotations:

select

student_id,

student_first_name,

student_points,

(student_points+10) *100 as 'Discount book'

from Student;

 To take all records but remove duplicates:

select distinct student_first_name

from student;

 Exercise: Return all data of student table, make points + 10 as student_new_points.

select *,

student_points + 10 as student_new_points

from student;
 Where Clause
 To take a record with a specific address:

select *

from student_information

where student_address = 'KP';

 To take a record out of KP city:

select *

from student_information

where student_address != 'KP';

or

select *

from student_information

where student_address <> 'KP';

 To select students who are born after 1992/01/01:

SELECT *

from student_information

where student_birthdate > '1992-01-01';

 To place multiple conditions using and operator:

SELECT *

from student_information

where student_birthdate > '1992-01-01'

and student_address = 'LM';

  To place multiple conditions using or operator:

SELECT *

from student_information

where student_birthdate > '1994-01-01'

or student_address = 'LM';

 To combine multiple condition using or & and operators:

SELECT *

from student_information

where student_birthdate > '1994-01-01'

or student_address = 'LM'

and student_email_address = '[Link]@[Link]';

 and operator is calculated first so if you want to make it sense with your condition, put
parenthesis:

SELECT *

from student_information

where student_birthdate > '1994-01-01'

or (student_address = 'KS'

and student_email_address = '[Link]@[Link]');

 Selecting a bunches of rows using not operator:

SELECT *

from student_information

where not student_address = 'kp';

  In operator and not in operator:

SELECT *

from student_information

where student_address in ('LM', 'BR', 'QS');

SELECT *

from student_information

where student_address not in ('LM', 'BR', 'QS');

 Between operator:

SELECT *

from student

where student_points between 60 and 80;

 Like operator: (% any number of character, _ single character)

SELECT *

from student

where student_last_name like 'h%';

SELECT *

from student

where student_last_name like 'hot%';

SELECT *

from student

where student_last_name like '%o%';

SELECT *
from student

where student_last_name like '%k';

SELECT *

from student

where student_last_name like '____k';

SELECT *

from student

where student_last_name like 'h___k';

 Regular Expressions (REGEXP):

select *

from student_information

where student_email_address regexp '[Link]';

 To find last_name started with ho:

select *

from student

where student_last_name regexp '^ho';

 To find last_name ended with tak:

select *

from student

where student_last_name regexp 'tak$';

  To find last_names with tak and mad in anywhere of the name:

select *

from student

where student_last_name regexp 'tak|mad';

 To find last_names with started with ho or it should have mad or an in anywhere of the name:

select *

from student

where student_last_name regexp '^ho|mad|an';

 To find last_name ended with ‘I’ followed by either z,r or a. Means zi,ri or ai:

select *

from student

where student_last_name regexp '[arz]i';

 To find last_name started with a and followed with I or r. Means ai and ar:

select *

from student

where student_last_name regexp 'a[i,r]';

To start last_name started from I to r followed by a:

select *

from student

where student_last_name regexp 'a[i-r]';

^ beginning (quorate)

$ end (dollar sign)

| logical or (vertical bar)

[] single character (square brackets)

[-] multiple characters (square brackets with hyphen)

Is null operator
  In order to find those student’s name whose email_address is null or not null:

select *

from student_information

where Student_email_address is null;

select *

from student_information

where Student_email_address is not null;

Ordered Clause
 To order any column:

select *

from student

order by student_first_name;

 To descend order:

select *

from student

order by student_first_name desc;

Limit Clause
 In order to select only 3 rows of student table:

select *

from student

limit 3;
  To top 3 students:

select *

from student

order by student_points desc

limit 3;

Inner Joins
 To join 2 tables and print the values of both tables (inner keyword is optional):

select *

from student

join student_information

on student.student_id=student_information.student_id;

 To specify a couple of columns only:

select Student.student_id,

Student_first_name,

Student_last_name,

Student_email_address

from student

join student_information

on student.student_id=student_information.student_id;
  To shorten the names of table:

select

s.student_id,

student_first_name,

student_last_name,

student_contact_number

from student s

join student_information si

on s.student_id=si.Student_information_id;

Joining Multiple Tables

 Take a look carefully at the allies:

select

s1.student_id,

Student_first_name,

student_last_name,

s2.subject_id,

subject_name

from student s1

join student_has_subjects s2

on s1.student_id = s2.student_id

join subject s3

on s2.subject_id = s3.subject_id

order by Student_first_name desc;

  We can also write this way:

select

s1.student_id,

s1.Student_first_name,

s1.student_last_name,

s2.subject_id,

s3.subject_name

from student s1

join student_has_subjects s2

on s1.student_id = s2.student_id

join subject s3

on s2.subject_id = s3.subject_id

order by Student_first_name desc;

Outer Joins
 We still learned Inner joins, although we didn’t use the keyword INNER because it’s optional. In
inner join all records are shown weather they have a null column or not, outer join on the other
hand though shows all records regardless of its null columns.
 There are 2 types of Outer Joins (Left outer join and Right outer join). Again, the keyword outer
is optional. Means it will be read correct weather or not you type it.
 Outer join is used to bring the columns of one table from left to right and from right to left.

select

s.student_id,

s.student_first_name,

s.student_last_name,

shs.subject_id

from student s

right join student_has_subjects shs

on s.student_id = shs.student_id;
 Outer Join in Multiple Tables
select

s.student_id,

s.student_first_name as name,

s2.subject_id,

s3.subject_name

from student s

right join student_has_subjects s2

on s.student_id = s2.Student_id

join subject s3

on s2.Subject_id = s3.subject_id

order by student_id;

The Using Clause

 Using clause works instead of (on s1.student_id = s2.student_id). It doesn’t work if the column
names are different and it neither works with order clause.

select

s.student_id,

s.student_first_name,

s2.subject_id,

s3.subject_name

from student s

join student_has_subjects s2

using (student_id)

left join subject s3

using (subject_id)

order by subject_id;
  On clause works with many-to-many relationships as well, to simplify it we can use using clause
as well. Means, if there are 2 table with 2 same column names and having primary key, we can
simplify on clause with using clause.

Natural Joins
 Natural Joins are dangerous, because we let the database engine to figure out the join. But it’s
very easy to code. Although database admins don’t prefer this methods:

select

s.student_id,

s.student_first_name,

s2.subject_name

from student s

natural join subject s2;

Cross Joins
select

s.student_id,

s.student_first_name,

s2.subject_name

from student s

cross join subject s2;

Unions
 Unions join rows:

select

Student_email_address,

Student_contact_number,

student_birthdate,

'Active' as status

from student_information
where Student_birthdate >= '1993-01-01'

union

select

Student_email_address,

Student_contact_number,

student_birthdate,

'Archived' as status

from student_information

where Student_birthdate < '1993-01-01'

order by student_birthdate;

 In upper program we created a new column called Status, and asked the database engine to
show it on the screen as per the conditions given.
  Practice:
o Write a query to find the Bronze, Silver and Gold students from their points:

select

student_id,

student_first_name,

student_last_name,

'Bronze' as Type

from student

where student_points > 70

union

select

student_id,

student_first_name,

student_last_name,

'Silver' as Type

from student

where student_points > 80

union

select

student_id,

student_first_name,

Student_last_name,

'Gold' as Type

from student

where student_points > 90;

Column Attribute
In MySQL Workbench, column flags can be used with a column to maintain integrity.
The column flags are as follows −
 PK − Primary Key
 NN − NOT NULL
 BIN − Binary
 UN − Unsigned
 UQ − Unique
 ZF − Zero Filled
 G − Generate Column
 AI − Auto Increment
Let us learn about them one by one −

PK
This stands for the primary key. It can be used to make the column as a primary key.

NN
It is for NOT NULL. Used to enforce the column that it will not insert a NULL value.

BIN
This stands for Binary. This can be used to store data as a binary string.

UN
It is for Unsigned and can be used to store an only positive value which can be started
from 0.

UQ
UQ is for Unique. This can be used to enforce the column to insert only unique value for
a specific column.

ZF
ZF is for Zero Filled. Suppose, we have declared int(3) and you want to store 21, then
zero filled would output the result 021.

G
G stands for Generated column: Generated Columns is a feature released on MySQL 5.7.
They can be used during CREATE TABLE or ALTER TABLE statements. It is a way of storing
data without actually sending it through the INSERT or UPDATE clauses in SQL. The database
resolves what the data will be.

AI
AI is for Auto Increment: Auto_increment in mysql
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for
each new record.

Here is the snapshot of column flags visible under MySQL workbench.

  Inserting a Single Row:

insert into [Link]

values (11, 'Jamshid', 'Romal', 77);

 Inserting a default ID (Preferred method. ID’s column must be AI or Auto Increment while
making the table in the beginning or else this will not work.):

insert into [Link]

values (default, 'Jamshid', 'Romal', 77);

 Another way of inserting data into rows:

insert into [Link]

student_id,

student_first_name,

student_last_name,

student_points

values (12, 'Gulzaar', 'Ahmadzai', 82);

  Inserting multiple rows:

insert into [Link]

student_id,

student_first_name,

student_last_name,

student_points

values (13, 'Jawid', 'ullah', 73),

(14, 'Nadim', 'Khan', 88),

(15, 'Baktash', 'Eman', 75);

 Inserting data into multiple tables:

insert into student

Student_id,

Student_first_name,

Student_last_name,

Student_points

values

16,

'Hamid',

'Azizi',

78

);

insert int Student_information

values (16,'0788788033', 'Hamid@[Link]', 16, 'QS', '1993-12-12');

  Creating a copy of a table:

create table student2 as

select * from student;

 Copying a couple of columns of one table into another table:

insert into student2

select *

from student

where student_points < 70;

Note: right click on table and click on truncate table will arise all data from table.

 Updating a single row:

update student

set student_first_name = 'Romal'

where student_id = 7;

 Updating multiple rows:

o First go to Edit menu  Preferences  SQL Editor in the left hand side menu 
uncheck safe updates  Close the workbunch and reopen then:

update student

set student_first_name = 'Salam'

where student_id in (5,9);

Note: If you want to update all records, don’t mention the where clause.
  Deleting Rows:

delete from student2

where student_id = 5;