MySQL

• Command Line Windows
• MySQL Front

I. Common MySQL Data Types

1. char

• Occupied byte number char(n)
• Fixed length string, fixed size storage control
• For example: char(2)

2. varchar

• Occupied byte number varchar(n)
• Variable length string, storage space equal to actual data space
• A string containing only English characters

3. int

• Four bytes occupied
• integer
• For example, self-increasing ID and number of representations

4. bigint

• Occupy 8 bytes
• Long integer
• Large self-increasing ID

Classification of SQL Statements

1. DQL Statement - Data Query Language (select)

1.1 Simple Query

Query a single field

select field name from table name;
Note: All symbols are in English.

Query multiple fields

select field name, field name from table name;

Query all fields

select * from table name;

1.2 Exercises (Calculating the annual salary of each employee)

select ename,sal*12 from emp;

//Further modify the field name after the annual salary query
select ename,sal*12 as yearsal from emp;

1.3 Conditional Query

Equal sign (=) operator

//Search for 5,000 employees with a monthly salary equal to
select ename,sal from emp where sal = 5000;

// Search for Employees Working for MANAGER
select ename,job from emp where job = 'MANAGER';

Unequal sign (<> or!=) operator

//Search for employees whose monthly salary is not equal to 5000
select ename,sal from emp where sal <> 5000;
//perhaps
select ename,sal from emp where sal != 5000;

between…and…

select ename,sal from emp where sal between 1600 and 3000;

is null(is not null)

select ename,comm from emp where comm is null;

like (Fuzzy Query)

• % wildcard
• Placeholder

1.4 Exercises (Query employees with salaries greater than 1800 and department numbers 20 or 30)

select deptno,ename,sal from emp where sal > 1800 and (deptno = 20 or deptno = 30);

1.5 Data Sorting

Single field sort order by field name

//Sort by salary (default ascending order)
select ename,sal from emp order by sal;
//or
select ename,sal from emp order by sal asc;
//Descending order
select ename,sal from emp order by sal desc;
//Sorting of entry dates
select ename,hiredate from emp order by hiredate desc;

Multiple field sorting

//Sort by department number ascending and salary descending
select deptno,ename,sal from emp order by deptno,sal desc;

Sort by subscript (poor readability)

select * from emp order by 6 desc;

1.6 Data Processing Function

1) One-line processing function

//lower to lowercase
select lower(ename) as lowername from emp;

//Substr takes substr (the truncated string, starting subscript, truncated length)
select substr(ename,1,1) as ename from emp;

//lengh takes length
select length(ename) as namelength from emp;

//trim removes spaces
select * from emp where ename = trim('   king      ');

//round around
select round(123.56);
select round(123.56,1);

//rand() generates random numbers
select rand();
select round(rand()*122);

//case...when...then...else...end
//Usage: Match jobs, when it's MANAGER, salary will be increased by 10%, when it's SALESMAN, salary will be increased by 20%, salary of other jobs will remain unchanged.
select job,ename,
    (case job
        when 'MANAGER' then sal*1.1
        when 'SALESMAN' then sal*1.2
        else sal
    end) as newsal from emp;

//ifnull null processing function
select ename,(sal+comm)*12 from emp;
select ename,(sal+ifnull(comm,0))*12 from emp;

//str_to_date converts a string to a date
 // Usage: str_to_date('date string','date format')

//date_format Converts a date to a date string in a specific format
 // Usage: date_format (date type data,'date format')

2) Multi-line processing functions (null values are automatically ignored)

//sum summation

//avg average

//count gets the number of records
 // Multi-line Processing Functions automatically ignore null values

//distinct to remove duplicate records
 // Usage: distinct field name
 //or distinct field name 1, field name 2...

//Remove Department numbers and duplicate job records

1.7 group by

//Queries by Working Group
select
        job,max(sal) as maxsal
from
        emp
group by
        job;

select
        job,max(sal) as maxsal
from
        emp
group by
        job
order by
        maxsal desc;

//Calculate the maximum salary for different positions in different departments
select
        deptno,job,max(sal) as maxsal
from
        emp
group by 
        deptno,job;

//Find out the maximum salary for each position, except MANAGER
select
        job,max(sal) as maxsal
from
        emp
where
        job <> 'MANAGER'
group by 
        job;

//Find out the average salary for each position and ask for an average salary of more than 2000.
//having is used after group by
//where used before group by
select
        job,avg(sal) as avgsal
from
        emp
group by 
        job
having
        avgsal > 2000;

1.8 select statement summary

A complete SQL Statements are as follows:
select
        xxx
from
        xxx
where
        xxx
group by
        xxx
having 
        xxx
order by 
        xxx

1.9 Link Query (Cross-Table Query)

Chronological classification:
        SQL92
            select ename,dname from emp as e,dept as d where e.deptno = d.deptno;
        SQL99
            select 
                ename,dname 
            from 
                emp e
            join
                dept d
            on
                e.deptno = d.deptno
            where
                Data filtering conditions;
Classification of connection modes:
        Inside Link: A key word is omitted:inner
            Equivalent connection
                For example: query the corresponding department name of employees
            Non-Equijoin
                For example, query the salary level corresponding to the salary of employees: show the name, salary, salary level of employees
            Self link
                For example: query the corresponding leadership name of employees, display the employee name and leadership name
                1. Leadership Number of Employees
                2. Query the leader's name and number
                3. Table join condition: a.mgr = b.empno
        External connection
            Left Outer Connection/Left Connection
            Right Outer Connection/Right Connection
        Full connection

1.9.1 SQL92

select ename,dname from emp as e,dept as d where e.deptno = d.deptno;

1.9.2 SQL99 (master this only)

select 
    ename,dname 
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno;

1.9.3 Non-Equivalent Connections

select
    e.ename,e.sal,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal;

1.9.4 Self-Link

select
    e.ename,b.ename
from
    emp e
join
    emp b
on
    e.mgr = b.empno;

1.9.5 External Connection (omitting a keyword: outer)

• Left outer join: All rows in the left table and all matched rows in the right table
• Right outer join: All rows in the right table and all matched rows in the left table

Right outer join

select
    e.ename,d.dname
from 
    emp e
right join 
    dept d
on
    e.deptno = d.deptno;

Practice

• Find out the employee's Department name, employee's leadership name and salary level

  emp,dept,salgrade

  select
  d.dname,
  e.ename,
  b.ename as leadername,
  s.grade
  from
  emp e
  join
  dept d
  on
  e.deptno = d.deptno
  join
  emp b
  on
  e.mgr = b.empno
  join
  salgrade s
  on
  e.sal between s.losal and s.hisal;

1.10 Subquery

1.10.1 After where

//Identify employees whose salaries are higher than the company's average, and ask for their names and salaries.
select ename,sal from emp where sal > (select avg(sal) as avgsal from emp);

//Find out the average salary for each department, and ask to show the average salary and salary level.
//Step 1: Find out the average salary of each department first
select
    e.deptno,avg(sal) as avrsal
from 
    emp e
group by
    e.deptno;
//Step 2: Salary Level
//Connect
select
    t.avgsal,s.grade
from
    (select
        e.deptno,avg(sal) as avgsal
    from 
        emp e
    group by
        e.deptno)  t
join
    salgrade s
on 
    t.avgsal between s.losal and s.hisal;

1.11 UNION Merge and Add Result Set

//Query out employees whose jobs are manager and salesman
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

select ename,job from emp where job in('MANAGER','SALESMAN');

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

1.12 limit (used only in MySQL) to get the first or middle rows of data in a table

//Take the first five data
select * from emp limit 0,5;

//Top five salary earners
select ename,sal from emp order by sal desc limit 5;
//Between Fifth and Eighth
select ename,sal from emp order by sal desc limit 4,4;

2. DML Statement - Data Operating Language (insert, delect, update)

2.1 insert (insert)

insert into t_student(no,name,gender,birth,email)
values
(1,'zhangsan','1',str_to_date('1944-01-02','%Y-%m-%d'),'zhangsan@163.com');

insert into t_student(no,name,gender,birth,email)
values
(2,'lisi','0','1949-08-11','lisi@163.com');
// mysql default date format year, month and day

insert into t_student(no,name,gender,birth,email)
values
(3,'wangwu','1',str_to_date('04-06-1956','%m-%d-%Y'),'wangwu@163.com');

insert into t_student(no,name,gender)
values
(4,'dany','1');

insert into t_student 
values
(5,'jack','1','1977-05-3-23','jack@163.com');//Not recommended. If fields are not assigned, an error will be reported.

insert into emp_bak select * from emp where job = 'MANAGER';

2.2 update (modification)

//Single field
update t_student set birth = str_to_date('1955-09-11','%Y-%m-%d') where no = 4;

//Multiple fields
update t_student set gender = 0, email = 'dany@163.com' where no = 4;

2.3 delete (delete)

//delete from t_student; delete all records

delete from t_student where no = 4;

3. DDL Statement - Database Definition Language (create, drop, alter)

3.1 create tables

no INT(4)
Name: name varchar(32)
Gender: sex char(1)
Date of birth: birth date
 Email: email varchar(128)

create table t_student(
    no int(4),
    name varchar(32),
    gender char(1),
    birth date,
    email varchar(128)
);

3.2 Delete the table (drop)

drop table t_student;//Deleting non-existent tables will cause errors

drop table if exists t_student;//Delete non-existent tables without error

3.3 Quick replication of a table (the replicated table is a new table, not related to the original table)

create table emp_bak as select * from emp;  

drop table if exists t_student;
create table t_student(
    no int(4),
    name varchar(32),
    gender char(1) default '1',
    birth date,
    email varchar(128)
);

insert into t_student(no,name) 
values
(4,'dany');

3.4 alter (modifying table structure)

drop table if exists t_student;
create table t_student(
  no int(4),
  name varchar(32)
);
//Add fields
alter table t_student add email varchar(128);
//Modify fields
alter table t_student modify no int(8);
//Delete field
alter table t_student drop email;
//Change the field name
alter table t_student change name username varchar(32);

4. TCL Statement - Transaction Control Language (commit, rollback)

4.1 Transaction transaction

//Turn off automatic submission
//Mode 1: Start the transaction manually
start transaction;
...DML Sentence
commit;

//Mode 2: Set the automatic provision to off
set autocommit = off;
...DML Sentence
commit;

4.2 Transaction isolation level

3. Create tables to add constraints

1. Non-null constraints

create table t_user(
  id int(4),
  name varchar(32) not null,
  email varchar(128)
);

insert into t_user(id,email) values(1,'1123@126.com')

2. Uniqueness constraints

drop table if exists t_user;
create table t_user(
  id int(4),
  name varchar(32) not null,
  email varchar(128) unique
);

insert into t_user(id,name,email) values(1,'zs','zs@162.com');
insert into t_user(id,name,email) values(1,'ls','zs@162.com');

//Or written as
drop table if exists t_user;
create table t_user(
  id int(4),
  name varchar(32) not null,
  email varchar(128),
  unique(email)
);

//Joint uniqueness
drop table if exists t_user;

create table t_user(
  id int(4),
  name varchar(32) not null,
  email varchar(128),
  unique(name,email)
);

insert into t_user(id,name,email) values(1,'ls','zs@162.com');
insert into t_user(id,name,email) values(2,'ls','zs@162.com');

3. primary key constraint PK

drop table if exists t_user;
create table t_user(
    id int(4) primary key,
    name varchar(32) not null
);

insert into t_user(id,name) values(1,'lisi');
insert into t_user(id,name) values(2,'lisi');

insert into t_user(id,name) values(1,'lisi');

Automatically generating primary key values in MySQL (MySQL-specific)

drop table if exists t_user;
create table t_user(
    id int(4) primary key auto_increment,
    name varchar(32) not null
);

insert into t_user(name) values('zhangsan');

insert into t_user(name) values('lisi');

4. foreign key constraint FK

4.1 example 1

drop table if exists t_student; 
create table t_student(
    id int(4) primary key auto_increment,
    sno int(4),
    sname varchar(32),
    classno int(4),
    cname varchar(32)
);
//Divide the above table into two tables
//Parent table
drop table if exists t_class;
create table t_class(
    cno int(4) primary key,
    cname varchar(32)
);
//Child table
drop table if exists t_student;
create table t_student(
    sno int(4) primary key auto_increment,
    sname varchar(32),
    classno int(4),
    constraint t_student_classno_fk foreign key(classno) references t_class(cno)
);
//Adding Foreign Key Constraints to Class Number


insert into t_class(cno,cname) values(100,'Class 1 in grade three');
insert into t_class(cno,cname) values(200,'Class 2 in grade three');
insert into t_class(cno,cname) values(300,'Class 3 in grade three');

insert into t_student(sname,classno) values('Zhang San',100);
insert into t_student(sname,classno) values('Li Si',100);  

insert into t_student(sname,classno) values('Wang Wu',400);
//With foreign key constraints, errors will be reported

select 
    s.sname,c.cname
from
    t_student s
join
    t_class c
on 
    s.classno = c.cno;  

5. Cascade updates and deletions (rarely used)

5.1 Cascade Delete: on delete cascade

//Modify constraints
alter table t_student drop foreign key t_student_classno_fk;

alter table t_student add constraint t_student_classno_fk foreign key(classno) 
references t_class(cno) on delete cascade;

5.2 Cascade Updates: on update cascade

Fourth, Three Norms of Database Design

1. The first paradigm: primary keys and fields can no longer be divided

2. Second paradigm: Non-primary key fields rely entirely on primary keys

3. The Third Paradigm

4. Summary of Three Paradigms (Several Classical Designs)

//Wife list
create table t_wife(
    wno int(4) primary key,
    wname varchar(32)
);  
//Husband list
create table t_husband(
    hno int(4) primary key,
    hname varchar(32),
    wifeno int(4) unique,
    foreign key(wifeno) references t_wife(wno)
);