mysql指令集

DDL:

show databases; 

select database();

create database if not exists demo2 ;

drop database if exists demo2;

Use 数据库名;

Show tables;

Desc 表名;

Show create table 表名;

create table demo2 ( id int );

alter table demo2 add age int;

alter table demo2 change age name varchar(10) comment '姓名' ;

alter table demo2 drop id ;

alter table demo2 rename to demo3;

drop table if exists demo3;

truncate table demo2;

DML:

insert into demo2(id) values (2);

insert into demo2(id) values (3),(4);

insert into demo2 values (5),(6);

update demo2 set id =1 where id =2;

delete from demo2 where id = 6;

DQL:

select id from demo2;

select * from demo2;

select id as '标签' from demo2;

select distinct id as '标签' from demo2;

1. 查询年龄等于 88 的员工

select * from emp where age=88;

1. 查询年龄小于 20 的员工信息

select * from emp where age<20;

C. 查询年龄小于等于 20 的员工信息

 select * from emp where age<=20;

D. 查询没有身份证号的员工信息

select * from emp where idcard is null;

E. 查询有身份证号的员工信息

select * from emp where idcard is not null;

F. 查询年龄不等于 88 的员工信息

select * from emp where age <> 88;

G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息

select * from emp where age between 15 and 20;

1. 查询性别为 女 且年龄小于 25岁的员工信息

select * from emp where age < 25 and gender = '女';

select * from emp where age < 25 and gender = 女;（错误）

I. 查询年龄等于18 或 20 或 40 的员工信息

select * from emp where age in (18,20,40);

J. 查询姓名为两个字的员工信息 _ %

select * from emp where name like '__';

select * from emp where name like __;  //ERROR 1054 (42S22): Unknown column '__' in 'where clause'

K. 查询身份证号最后一位是X的员工信息

 select * from emp where idcard like '%x';

聚合函数

SELECT 聚合函数(字段列表) FROM 表名 ;

注意 : NULL值是不参与所有聚合函数运算的。

A. 统计该企业员工数量

select count(*) from emp;

B. 统计该企业员工的平均年龄

select avg(age) from emp;

C. 统计该企业员工的最大年龄

select max(age) from emp;

D. 统计该企业员工的最小年龄

select min(age) from emp;

E. 统计西安地区员工的年龄之和

select sum(age) from emp where workaddress = '西安';

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组

后过滤条件 ];

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组

之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以

注意事项:

 • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

 • 执行顺序: where > 聚合函数 > having 。

 • 支持多字段分组, 具体语法为 : group by columnA,columnB

A. 根据性别分组 , 统计男性员工 和 女性员工的数量

select gender,count(*) from emp group by gender;

B. 根据性别分组 , 统计男性员工 和 女性员工的平均年龄

 select gender,avg(age) from emp group by gender;

C. 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址

select workaddress from emp where age<45 group by workaddress having count(*)>=3;

D. 统计各个工作地址上班的男性及女性员工的数量

 select gender,workaddress,count(*) from emp group by workaddress,gender;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2

A. 根据年龄对公司的员工进行升序排序

select * from emp order by age;

B. 根据入职时间, 对员工进行降序排序

select * from emp order by entrydate desc;

C. 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

select * from emp order by age,entrydate desc;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

 • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

 • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

 • 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

A. 查询第1页员工数据, 每页展示10条记录

select * from emp limit 10;

B. 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数

select * from emp limit 10,10;

DCL：

select * from mysql.user;

create user 'sinksank'@'localhost' identified by '123456';

 alter user 'sinksank'@'%' identified with mysql_native_password by '1234';

drop user 'itcast'@'localhost';

show grants for 'sinksank'@'%';

grant all on demo to 'sinksank'@'%';

revoke all on demo from 'sinksank'@'%';

---------------分割

函数：

select concat('Hello' , ' MySQL');

select lower('Hello');

select lower('Hello');

select lpad('01', 5, '-');

select lpad('01', 5, '-');

select lpad('01', 5, '-');

select substring('Hello MySQL',1,5);

select ceil(1.1);

select floor(1.9);

select mod(7,4);

select rand();

select round(2.344,2);

select curtime();

select curdate();

select now();

select YEAR(now());

select MONTH(now());

select DAY(now());

select date_add(now(), INTERVAL 70 YEAR );

select datediff('2021-10-01', '2021-12-01');

select if(false, 'Ok', 'Error');

select ifnull('Ok','Default');

select ifnull('','Default');

select ifnull(null,'Default');

mysql> select name, (

    ->  case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '地址'

-> from emp;

约束

mysql> create table tb_user(

    -> id int auto_increment primary key,

    -> name varchar(10) not null unique,

    -> age int check (age>0 && age<=120),

    -> status char(1) default '1',

    -> gender char(1)

    -> );

多表查询:

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

 表结构: emp , dept

 连接条件: emp.dept_id = dept.id

select e.name,d.name from emp e , dept d where e.dept_id = d.id;

//一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

查询emp表的所有数据, 和对应的部门信息

  select e.*,d.name 部名 from emp e left join dept d on e.dept_id = d.id;

 查询dept表的所有数据, 和对应的员工信息(右外连接)

select e.name,d.* from emp e right join dept d on e.dept_id = d.id;

查询员工 及其 所属领导的名字

select a.name,b.name from emp a, emp b where a.managerid = b.id;

查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =

b.id;

注意事项:

 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

select * from emp where salary < 5000

union all

select * from emp where age > 50;

子查询

根据子查询结果不同，分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

查询 "销售部" 的所有员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');

查询在 "方东白" 入职之后的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

查询 "销售部" 和 "市场部" 的所有员工信息

mysql> select * from emp where dept_id in (select id from dept where name = '销售部' or

    -> name = '市场部');

查询比 财务部 所有人工资都高的员工信息

select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));

查询比研发部其中任意一人工资高的员工信息

select * from emp where salary > some(select salary from emp where dept_id = (select id from dept where name = ' 研发部'));

查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) in (select salary,managerid from emp where name = '张无忌');

 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name =

'鹿杖客' or name = '宋远桥' );

 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left

join dept d on e.dept_id = d.id ;

事务：

手动提交

SELECT @@autocommit ;

START TRANSACTION 或 BEGIN ;

或SET @@autocommit = 0 ;

COMMIT

ROLLBACK

-- 开启事务

Begin;

-- 1. 查询张三余额

select * from account where name = '张三';

-- 2. 张三的余额减少1000

update account set money = money - 1000 where name = '张三';

-- 3. 李四的余额增加1000

update account set money = money + 1000 where name = '李四';

-- 如果正常执行完毕, 则提交事务

commit;

-- 如果执行过程中报错, 则回滚事务

-- rollback;

事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |

READ COMMITTED | REPEATABLE READ | SERIALIZABLE }