Database operations

Creating a database

create database free;
create database free character set utf8;
Copy the code

Using a database

use free;
Copy the code

Deleting a Database

drop database free;
Copy the code

Modify database definition information

alter database free charset set utf8;
Copy the code

View the currently selected database

select database();
Copy the code

Show all databases

show databases;
Copy the code

Exiting the database

exit;
Copy the code

Table operation

View all data tables

show tables;
Copy the code

Commonly used parameters

  1. NOT NULL Cannot be NULL

  2. PRIMARY KEY The PRIMARY KEY data is unique, cannot be repeated, and cannot be null

  3. Auto_increment Indicates the automatic increment

  4. The comment annotation

  5. UNIQUE constraint

  6. The DEFAULT DEFAULT value

Create a table

Create table free(' id 'int(11) primary key auto_increment not NULL comment "主键 iD ", 'name' varchar(255) not null UNIQUE comment "表 ", 'create_time' int(11) not null comment "create_time")engine=innodb default charset=utf8;Copy the code

View table fields

desc free;
Copy the code

View the table for details

show create table free;
Copy the code

Modify the name of the table

rename table free to free2;
Copy the code

Increase the field

Alter table free add 'delete_time' int(11) not null comment "delete time ";Copy the code

Modify the fields

Alter table free modify 'delete_time' int(20) comment "delete time 2";Copy the code

Delete the field

alter table free drop `delete_time`;
Copy the code

Modify character set

alter table free character set utf8;
Copy the code

Changing the field name

alter table free change `delete_time` `delete_time2` int(11); 
Copy the code

Delete table

drop table free;
Copy the code

Add data

A single add

Insert into free (id,name,create_time,delete_time) values(null,'test',1111,2222);Copy the code

Multiple add

Insert into free (id,name,create_time,delete_time) VALUES (null,'test2',333,444),(null,'test3',555,666);Copy the code

If you don’t write the column name you have to put all the values in

Insert into free values (null, 'SSS', 222222), a (null, 'eeee, 222222 (2);Copy the code

Modify the data

Modify the values of all fields

update free set name = 'test';
Copy the code

Modify according to conditions

update free set name = 'test2' where id = 1;
Copy the code

Modify multiple fields

update free set name='test3',delete_time=555 where id = 1;
Copy the code

Delete the data

Delete by condition

delete from free where name = 'test';
Copy the code

Delete all data

delete from free; Truncate table free; # primary key resetCopy the code

Query operation

AND AND

Select * from 'user' where 'sex' = 0 and 'cid' = 1;Copy the code

The OR OR

Select * from 'user' where 'sex' = 0 or 'cid' = 1;Copy the code

IN 

Select * from 'user' where 'cid' in(1,2,3);Copy the code

NOT IN 

Select * from 'user' where 'cid' not in(1,2,3);Copy the code

IS NULL Indicates whether the value IS NULL

Select * from 'user' where 'city' is null;Copy the code

BETWEEN the AND in… between

Select * from 'ms_dj_user' WHERE 'created_at' BETWEEN 1595388727 and 1595388729;Copy the code

! = is not equal to

Select * from 'ms_dj_user' where sex! = 0;Copy the code

< > invert

Select * from 'ms_dj_user' where sex <> 0;Copy the code

IS NOT NULL Indicates that the value IS NOT NULL

Select * from 'user' where 'city' is not null;Copy the code

LIKE fuzzy query

Select * from 'user' where 'username' like '_____'; select * from 'user' where 'username' like '_____'; Select * from 'user' where 'username' like '____y' select * from 'user' where 'username' like '____y'; Select * from 'user' where 'username' like 't%'; Select * from 'user' where 'username' like '_i%'; select * from 'user' where 'username' like '_i%'; Select * from 'user' where 'username' like '%a%'; select * from 'user' where 'username' like '%a%';Copy the code

DISTINCT removal repetition

Select distinct 'username' from 'user';Copy the code

The ORDER BY ORDER

Select * from 'user' order by 'age'; select * from 'user' order by 'age'; select * from `user` order by `age` asc; Select * from 'user' order by 'age' desc; Select * from 'user' order by 'age' desc, 'no' asc; select * from 'user' order by 'age' desc, 'no' asc;Copy the code

Aggregation function

The COUNT statistics

Select count(*) from 'user'; Select count(email) from 'user'; Select count(*) from 'user' where 'sal' > 2500;Copy the code

AVG average

Select avg(sal) from 'user';Copy the code

MAX MIN Maximum minimum value

Select Max (sal),min(sal) from 'user';Copy the code

The SUM total

Select sum(sal) from 'user';Copy the code

GROUP BY GROUP query

Select * from 'user' group by 'no'; Select 'sex',sum(sal) from 'user' group by 'sex'; Select 'sex',count(*) from 'user' where 'sal' < 1500 group by 'sex';Copy the code

HAVING group filtering

Where is used to filter data before grouping

You can use aggregate functions after having

Aggregate functions cannot be used after WHERE

Select 'sex',sum(sal) from 'user' group by 'sex' having sum(sal) > 9000;Copy the code

LIMIT the paging

Select * from 'user' limit 0,5; Select * from 'user' LIMIT 5,5; The second page takes 5 pieces of dataCopy the code

Multi-table query

UNION removes duplicate records. / UNION ALL does not remove duplicate records

Requirement: The two result sets to be merged must have the same column number type

select * from `china` union select * from `usa`; Select * from 'China' union all select * from 'USA'; # Do not remove duplicate recordsCopy the code

Even the table query

Select * from 'user' u, 'user_info' I where 'u'. Id = 'I'. Uid; Select u.id, u.name,i.compay from 'user' u, 'user_info' I where 'u'. Id = 'I'. Uid;Copy the code

In the connection

Select * from 'user' inner join 'user_info' I on U.id = i.id; select * from 'user' inner join 'user_info' I on U.id = i.id; # specified field improve query performance select u.i d, u.u sername, i.com pany from ` user ` u inner join ` user_info ` I on u.i d = i.u id;Copy the code

Outer join

The left join is dominated by the left table, and null is displayed if there is no data in the right column

select u.username,u.email,i.company from `user` u left outer join `user_info` i on u.id = i.uid;
Copy the code

The data associated with the left table is null if there is no data in the right table

select u.username,u.email,i.company from `user` u right outer join `user_info` i on u.id = i.uid;
Copy the code

Since the connection

CREATE TABLE emp(empno INT, ename VARCHAR(50), job VARCHAR(50), MGR INT, hiredate DATE, sal DECIMAL(7,2), Comm DECIMAL(7,2), deptno INT); INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp values (7499, 'ALLEN, "SALESMAN", 7698,' 1981-02-20 ', 1600300, 30); INSERT INTO emp values (7521, "WARD", "SALESMAN", 7698, '1981-02-22', 1250500, 30); INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO emp values (7654, "MARTIN", "SALESMAN", 7698, '1981-09-28', 1250140, 30); INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO emp values(7902,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); Query # 7369 employee number The name Manager's serial number and the name of the manager SELECT e1. Empno 'employee number, e1, ename' staff name, e1. MGR 'manager number, e2, ename' manager name 'FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno=7369;Copy the code

The subquery

Nested a select statement in a SELECT statement

A position

Where as part of a query

As a table

  • Employees who are paid more than Jones

    select * from emp where sal > (select sal from emp where ename = ‘jones’);

  • Query employees in the same department as Scott

    select * from emp where empno = (select empno from emp where ename = ‘scott’);

  • Query information about employees whose jobs and salaries are the same as Martin’s

    select job,sal from emp where (job,sal) in (select job,sal from emp where ename = ‘martin’);