MySQL语句 格式+例题

一、基础

1.SQL语句

①DDL（data definition language）

-- 库
SHOW DATABASES;               //查询所有数据库
SELECT DATABASE ();           //选择查询当前数据库
CREATE DATABASE [if not exists] test2 [default charset utf8mb4];       //创建数据库
DROP DATABASE [if exists] test1;           //删除当前数据库
USE test1;                                 //使用所选择库


-- 表
SHOW TABLES;                        //展示所有表
CREATE TABLE test3(
     id int comment '编号'，
     name varchar(50) comment '姓名',
     age int comment '年龄'
)comment '用户表';                   //创建表
DESC test3；                         //展示表结构
SHOW CREATE TABLE test3;             //查询表
ALTER TABLE test3 ADD idCard varchar(18) comment '身份证号';    //添加字段
alter table test3 MODIFY idCard char(18);                      //修改字段类型
alter table test3 change name username varchar(30);           //修改姓名和字段类型
ALTER TABLE test3 drop age;           //删除字段
alter table test3 rename to tb_user;  //修改表名

 ②DML （data manipulation language）

-- 增删改查
INSERT INTO test1(id,name) VALUES (09,'中国');         //插入对应数据
INSERT INTO test1 VALUES (10,'日本'),(11,'韩国');     //多条插入，全数据插入
UPDATE test1 set name = '俄罗斯' where id = 11;      //修改id为11的数据条中的name字段
DELETE FROM test1 where id = 10;                    //删除该条数据

③DQL （data query language） 

-- 查询
   select name as '姓名' from test;     //起别名
   select distinct entertime from test;    //去重 
   select * from test where age >= 15 and age<=20;   //条件查询
   select * from test where age in(15,10,22);       //在其中 
   select * from test where name like '__';         //正则表达式 两位字符
   select * from test where name like '%国';        //正则表达%为省略任意个字符

-- 聚合函数 作用于某一列
-- count //数量   sum// 求和   avg//平均   max//最大    min//最小   
   select sum(age) from test where workplace = '北京';  //求北京地区的员工年龄总和
   
-- 分组查询
   select workplace, count(*) from test where age < 40 group by workplace having count(*) >=3;    //查询员工年龄小于45的人数并以工作地点分组，同时取总数大于三的

-- 排序查询
   select * from test order by age desc, idcard asc; //以age的降序排序，若相同则以idcard的升序排序

-- 分页查询
   select * from test limit 0,10;   //每页十条
   select * from test limit 10,10;   //(页码-1)*展示条数
    

案例书写：

select * from emp where age in(20,21,22,23) and gender = '女';

select * from emp where ( age >=20 and age <=40 ) and name like '___'and gender = '男';

select gender, count(*) as '人数' from emp where age < 60 group by gender;

select name, age from emp where age <= 35 order by age asc ,entrytime desc;

select * from emp where gender = '男' and age between 20 and 40 order by age asc , entry time asc limit 5;

④DCL (data control language)

-- 管理用户

create user 'lllly'@'localhost' identified by '1515'; //创建用户

alter user 'lllly'@'localhost' identified with mysql_native_password by '1212';

-- 权限控制

show grants from 'lllly'@'%';            //查询权限
grant all on shujuk.* to 'lllly'@'%';    //授权全部
revoke all on shujuk.* from 'lllly'@'%'; //撤销

 2.函数

-- 常用字符串函数
 concat(s1,s2..)    //拼接
 LPAD(str, n,pad)   //左填充
 RPAD(str, n,pad)   //右填充
 trim('01 1  ')     //去除头尾空格

-- 案例：员工工号填充值

update emp set id = lpad(id, 5, '0');


-- 数值函数
 ceil(x)     // 向上取整
 flood(x)    // 向下取整
 mod(x,y)    // 取模
 rand()      // 随机数 0-1之间
 round(x,y)  // 参数四舍五入保留y位小数


-- 案例：生成一个六位数的随机验证码

select lpad(round(rand()*1000000,0),6,'0');

-- 日期函数
 curdate()                        //当前年月日
 curtime()                        //当前时间
 now()                            //现在的时间
 date_add(now(),interval 70 year) //增加时间
 datediff(date1,date2)             //时间差距天数

-- 案例：查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
 
-- 流程函数
-- if
select if(,'ok','err');
-- case when then else end
select 
   name, (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from emp;

-- 案例：班级成绩展示优秀或不及格

select id,
       name,
       case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end,
       english,  -- 同理
       chinese   -- 同理
from score;

 3. 约束

-- 案例 构建表
-- id int 主键，自动增长
-- name varchar(10) 不为空，且唯一
-- age int 大于0，并且小于等于120 
-- status char(1) 默认值为1
-- gender char(1) 无

CREATE TABLE tb_user(
   id int primary key auto_increment comment '主键'，
   name varchar(10) not null unique comment '姓名',
   age int check( age > 0 && age <=120) comment '年龄'，
   status char(1) default '1' comment '状态'，
   gender char(1) comment '性别'
) comment'用户表';

-- 插入数据
insert into tb_user(name,age,gender) values('xxy',18,'女');

外键 ：子表为存在外键的表格，关联另一张表 （保证数据的完整性与一致性）

-- 添加外键
CREATE TABLE user(
   id int primary key auto_increment,
   name varchar(20) unique,
   dept_id int foreign key(dept_id) references dept(id)
)comment '员工';

--添加
alter table user add constraint fk_dept_id foreign key (dept_id) references dept(id);

--删除
alter table user drop foreign key fk_dept_id;

--父子表关系：restrict 不操作 , cascade 同步更新， set null 设空
alter table user add constraint fk_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

4.多表查询

 表关系：一对多（一个外键即可），多对多（中间表），一对一

--笛卡尔积 多表查询总共个数

select * from user, dept where user.dept_id = dept.id; //消除不需要的笛卡尔积

-- 连接查询 内连接（查询两个表的交集部分），外连接，自链接

select user.name, dept.id from user dept where user.dept_id =dept.id;

-- 左外连接（交集+表一）
select u.* ,d.name from user u left join dept d on u.dept_id = d.id;

-- 右外连接（交集+表二）
select d.* ,u.name from user u right join dept d on u.dept_id = d.id;

-- 自连接
select u.name, b.name from user u,user s where u.managerid = s.id;

5.联合查询 

union 查询，将多次查询结果合并

-- 将薪资低于5000的员工和年龄大于50的员工全部查询出

select * from emp where salary < 5000;
union   --去重
select * from emp where age > 50; 

-- 查询字段需要一致

6.子查询

-- 标量子查询//子查询为单行单列的
select * from emp where dept_id = (select id from dept where name = '总裁办');

--案例查询在‘东方白’入职之后的员工信息

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

-- 列子查询 //返回的是多行 IN ,NOT IN ,ANY , SOME , ALL

-- 案例查询‘销售部’ 和 ‘市场部’的所有员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部' );

-- 案例查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from emp where name = '研发部'));

--行子查询 返回多列
--案例 查询与‘xxx’的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name = 'xxx');

--表子查询 多行多列 in

 案例练习：

-- 1.
select e.name,e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;

-- 2.
select  e.name,e.age, e.job, d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

-- 3.
select id, name from dept where id in(select dept_id from emp);
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;

--  6.
select e.*, s.grade from salgrade s,  emp e , dept d where e.dept_id = d.id and d.name = '研发部' and e.salary between s.losal and s.hisal;

-- 11.
select d.id,d.name,(select count(*) from emp e where e.dept_id = d.id ) as '人数' from dept d ;

7.事务

一组操作的集合，不可分割的工作单位， 整体操作。

四大特性（ACID）：原子性，一致性，隔离性，持久性。