MySQL CRUD操作

1.DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

2.DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3.DCL（Data Control Language）数据库控制语言  授权，角色控制等
GRANT 授权
REVOKE 取消授权

4.TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点
ROLLBACK  回滚
SET TRANSACTION

SQL主要分成四部分：
（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。

1. insert 数据插入

-- DML insert replace  数据插入
-- insert [into] 表名(字段列表...) value();
-- insert [into] 表名(字段列表...) values(),(),();
select * from teacher;
alter table teacher modify id int unsigned auto_increment;
alter table student drop constraint stfk;
alter table student add constraint stfk foreign key (tid) references teacher(id) on delete set null on update cascade ;
alter table student modify id int unsigned auto_increment;
-- id name
insert into teacher value(null,'赵丽');
insert teacher(name) value('李强');
insert teacher(name) select name from teacher;
replace teacher(name) values('jack'),('andy');
-- 如果有存在的主键，则此记录修改，没有就插入数据
replace into teacher values(668,'张丽丽');
insert teacher(name,id) value('aaaa',null);

select * from teacher;

2. update

select version(), database(), user();
show tables;
use wxdb;

-- sql crud update 数据更新修改  数据库 表 表结构 表数据记录 表字段
-- update 表名 set field1=value1,f2=v2...;  where 1   true 1=1
select *
from teacher;

update teacher
set name = '李强强'
where id = 115;
-- 给 teacher.name 增加唯一索引
alter table teacher
    modify name varchar(30) unique;

alter table teacher
    add score tinyint unsigned default 60;

update teacher
set name = 'lisi'
where id = 669;

update teacher
set score=score - 2
where name like '___';

update teacher
set id=110,
    name='赵二',
    score=88
where id = 120;

3. delete

-- 数据删除  drop database db;  drop table t1; drop table if exists t1,t2,t3;
-- 删除修改 需要控制好where
delete from teacher where id = 110;

begin;
delete from teacher where id = 669;

commit; --
rollback;

select * from teacher;

-- 删除所有，无条件   一条一条记录删除，删除效率，但可以加条件，会触发删除触发器
delete from teacher;
-- table = 结构 + 数据  效率高，不能有条件，自增主键，恢复默认
 truncate teacher;
 
 alter table teacher modify score tinyint unsigned default 80 after id;


alter table teacher modify score tinyint unsigned default 80 after name;

4. select

(1) 简单查询 别名 计算列 if() ifnull() concat()

-- crud select 查询语句
select * from teacher;
select id,name,score from teacher;
-- 列别名name 姓名  表别名 tt
select name 姓名,score as '成绩' from teacher tt;

alter table teacher add birthday date default '2000-12-20';
select year(curdate()),month(curdate()),dayofmonth(curdate());


select concat('姓名：','李四') 姓名,concat(18,'岁') 年龄;

select id,name,score+5,concat(year(now())-year(birthday),'岁') 年龄 from teacher;

alter table teacher add gender enum('男','女') default null after name;

select * from teacher;
-- ifnull()
select ifnull(null,'a'),ifnull('b','c');
-- if(a,b,c)
select if(true,'yes','no'),if(null,'yes','no'),if(2=3,'yes','no');
-- '' null 0 false
select if('',2,3);


select id 编号,concat(name,if(gender = '男','(先生)',if(gender='女','(女士)',''))) 姓名,ifnull(gender,'保密') 性别 from teacher;

（2） 查询条件 where

-- select 查询条件
select * from teacher where  1=1 or 12=3
-- where id = 6     = >= < <= != <>
-- and or not

-- 模糊查询 like % 0个或任意个任意符号   _代表一个任何符号
select * from teacher where name like '%强%';
select * from teacher where name like '__强';
select * from teacher where name like '__%';

select * from teacher where name = '李%';

select * from teacher where name like '李%' or name like '张%';

-- not like
select * from teacher where name not like '李%'


-- 正则表达式条件  regexp
select 1=1,'ab'<>'c',1=3;

select * from teacher where name regexp '^a.*$';
select * from teacher where name regexp '^.*a.*$';
select * from teacher where name regexp 'a';

select * from teacher where name regexp '^[a-zA-Z]{2}$';

-- 查询只有两个汉字的。
select * from teacher where name regexp '^[\\u4e00-\\u9fa5]{2}$';
select * from teacher where name regexp '\\d{3}';

-- 检查正则表达式
select 'myjava' regexp '^.*java$';


-- select 查询条件
select * from teacher where  1=1 or 12=3
-- where id = 6     = >= < <= != <>
-- and or not

-- 模糊查询 like % 0个或任意个任意符号   _代表一个任何符号
select * from teacher where name like '%强%';
select * from teacher where name like '__强';
select * from teacher where name like '__%';

select * from teacher where name = '李%';

select * from teacher where name like '李%' or name like '张%';

-- not like
select * from teacher where name not like '李%'


-- 正则表达式条件  regexp
select 1=1,'ab'<>'c',1=3;

select * from teacher where name regexp '^a.*$';
select * from teacher where name regexp '^.*a.*$';
select * from teacher where name regexp 'a';

select * from teacher where name regexp '^[a-zA-Z]{2}$';

-- 查询只有两个汉字的。
select * from teacher where name regexp '^[\\u4e00-\\u9fa5]{2}$';
select * from teacher where name regexp '\\d{3}';

-- 检查正则表达式
select 'myjava' regexp '^.*java$';

-- in ()  not in()
select * from teacher where id in (112,114,116);

select * from teacher where id not in (112,114,116);



-- 效率低
select * from teacher where not (id in (112,114,116));

-- is null  is not null 判断空
select * from teacher where gender = null;
select * from teacher where gender is null;
select * from teacher where gender is not null;
-- 效率低
select * from teacher where not gender is null;

-- between and     not between and
select * from teacher where score between 70 and 80;
select * from teacher where score>=70 and score<=80;

-- sql and or not  && || !
select * from teacher where ! (score not between 70 and 80);
select * from teacher where score<70 || score >80

(3) 集合查询，count() sum() avg() max() min()

-- 集合查询 聚集函数
select count(0) 总人数, max(score) 最高分, min(score) 最低分, sum(score) 总和, round(avg(score), 1) 平均分
from teacher;


select round(1.5242424, 1), pi(), round(pi()), round(pi(), 2), ceil(1.1), floor(1.9);

select *
from teacher;
select count(0)
from teacher
where score < 60
  and gender = '男';

select avg(score)
from teacher;

select *
from teacher


-- 排号函数window function row_number() over()   asc 升序 desc 降序
select row_number() over (order by rand()) 行号, id ID, name 姓名
from teacher limit 3;

-- 不要掌握 查询不进行读取缓存，效率太低。
select row_number() over (order by rand()) 行号, id ID, name 姓名
from teacher limit 2;

-- 随机  rand()
select rand(),rand(),rand();
-- 排名次 rank() over()    dense_rank() over()
select id,name,score,concat('第',dense_rank() over(order by score desc),'名') 名次
from teacher order by score desc;

-- 子查询 集合函数不在条件上使用
select * from teacher where score = max(score);

 select * from teacher where gender = '男' and score = (select max(score) from teacher where gender = '男');
 
 show tables;
create table stu
(
    id     int unsigned auto_increment comment '学号',
    name   varchar(30) not null comment '姓名',
    gender enum ('男','女') default '女',
    score  tinyint unsigned comment '成绩',
    dept   varchar(100) comment '专业',
    primary key (id)
) engine = innodb
  character set = utf8
  auto_increment = 202101 comment '学生信息表';

show engines;

insert stu
values (null, '李四', '男', 80, '计算机科学'),
       (null, '赵勇', '男', 80, '计算机科学'),
       (null, '李丽', '女', 80, '会计');

select *
from stu;

-- 消除重复
select count(distinct dept)
from stu;

select distinct dept 专业
from stu;
select distinct *
from stu;

-- 常见函数
select left('mysql', 2), right('mysql', 1), mid('mysql', 2, 2);
--
select length('my中国'),char_length('my中国');

select concat(1,2,3),concat_ws('-',10,20,30,40,50,60),space(50),repeat('*',50);


select name from stu;

select GROUP_CONCAT(name order by name desc SEPARATOR '-') from stu;

select group_concat(id) from stu;

(4)限制结果行，分页

show databases;
use wxdb;
show tables;

select * from stu;

select id,name,gender from stu limit 2;
select * from stu limit 0,1;
insert into stu(name,gender,score,dept) select name,gender,score,dept from stu;
-- limit 在查询语句最后边
select * from stu  where name like '李%' limit 5;
select * from stu limit 5,5;

-- 数据分页,如何实现
-- 要求每页3条记录，显示第一页 1*3-3
select * from stu limit 0,3;

-- 显示第3页  3*3-3
select * from stu limit 6,3;


-- pagesize = 3   pageno = 1     pageno*pagesize-pagesize  limit pageno*pagesize-pagesize,pagesize
-- pagesize = 3   pageno = n     n*3-3,3

-- 每页显示5条，显示第三页
select * from stu limit 10,5;

-- 每页40条记录
select count(*) 总记录数,ceil(count(*)/40) 总页数 from stu;
-- 每页40条记录，显示第2页
select * from stu limit 40,40

(5)查询排序，分组查询 分组条件 分组排序

-- 排序 order by 
-- 分组 用于统计 group by  having 分组条件 分组排序 order by 如果最后限制行数  limit
-- select from 表名 where条件 


/* 排序 */
-- order by score [asc | desc] asc 升序  desc降序
select id,name,score from stu order by score desc;

select * from stu;
select id,name,score from stu order by score desc,name;

select id,name from stu order by name;

select * from stu where name like '赵%' order by score desc;

-- 分组 分组查询，分组统计
select dept,count(*),max(score),min(score),avg(score),sum(score) from stu group by dept;

select ifnull(gender,'未知') 性别,count(*) 人数 from stu group by gender;

select dept 专业,count(*) 人数
from stu
where gender = '男'
group by dept
having 人数>2
order by 人数 desc,dept asc
limit 2
;

作业：

1. 查询老师的信息，要求显示id,name,score,leves from teacher; if()

   90> 优秀

   80> 良好

   60> 及格

   <60 补考

   null旷考

2. 统计学生表每个专业有多少人，男生多少人，女生多少人，优秀多少人，良好多少人。

   select num level,count(*) 人数 from
   (select if(score>90,'优秀(>90)',if(score>80,'良好(>80)',if(score>60,'及格','补考'))) num from stu) ss
   group by num order by 人数 desc;