2021-12-1 数据库（2）

数据库（2）

1、删除当id为11时的这个数据
delete from test where id=11
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\4b04ca091cc1494ba74ece09c22e55bb\clipboard.png
2、删除id大于9的数据
delete from test where id>9
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\5b457df30385412cb36536fb7194bf6b\clipboard.png
备注：以上，都属于软删除，若设置了自增长，删除后数据还保留着，你若是添加数据，还是再最大值上+1，比如现在再新建就从12开始了
3、新建一个student的表格，带id和name属性
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\6c4a9132b792429c940cb9abd7a90928\clipboard.png
4、删除student 全表（只删除表内容，不删除表结构select为空，desc还在）
1) delete from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\aeda6bcf40064b018b9b8b57c51bfa6d\clipboard.png
2) turncate student;（只删除表内容，不删除表结构select为空，desc还在）
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\652f91fea3344aad82749d85f4d6239a\clipboard.png
3) drop table student;(结构和内容一起删除）
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\9539883bc06146caa9d02866115c50b9\clipboard.png
5、把id为8的 分数栏score改为100分
1） update test set score=100 where id=8;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\d13b3ce0bb4c428fa7cdf8cd5b034684\clipboard.png
2）把id大于5的值名字name值都改成“xiaowang"
update test set name="xiaowang" where id>5;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\958c1a4db86f4abcbf3111a98cd23b9f\clipboard.png
3）把id大于等于4的值名字name值都改成“xiaoxiao"
update test set name="xiaoxiao" where id>=4;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\8bdb112f6c014a40bcefcd10f40a4174\clipboard.png
6、新建一个student表
create table student(id int(4)primary key,age int(8),sex int(4),name varchar(20),class int(4),math int(4));
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\8dc72be73057466a9dc9d672a420c558\clipboard.png
7、查询单个字段name，之前的*是所以的意思
select name from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\0a4150c7e3284e219a5c4086aa3439f2\clipboard.png
8、查询多个字段，math和name
select name,math from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\01be77d06c18422cafa178f2183eabda\clipboard.png
9、查询sex不为0的所有数据，！是不等于的意思
1）select * from student where sex !=0;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\c50549ee89af43c7a8979ad7ccd01167\clipboard.png
2）select * from student where sex <>1;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\89000ebbd47a4a8eb7bcf3761c0a7ca0\clipboard.png
10、查询id小于等于5的名字分数
select id,name,math from student where id<=5;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\6989c7f159f74862ab056979c04a21d5\clipboard.png
备注：题目中已知的内容放where后面，当做条件使用
需要查询的内容放在select后面

11、查询表中班级为1833的性别为1的所有数据
select * from student where class=1833 and sex=1;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\1515ee44ebb04d09bdd04e344c95c28e\clipboard.png
12、查询表中班级为1833或者1835的所有数据
1)select * from student where class=1833 or class=1835;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\5a7542b0843d471d8ad85f545adabb86\clipboard.png
2)select * from student where class in(1833,1835);
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\d3a74d27f3584a4f814c5a1ecea5be49\clipboard.png
1）查询不是这两个班级所有数据
select * from student where class!=1833 and class!=1835;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\a093663696944dcc95853533e326a01d\clipboard.png
2）查询不是这两个班级所有数据
select * from student where class not in(1833,1835);
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\f1168c58a5b64af7854c89767b4aba90\clipboard.png
13、查询年纪大于24小于31的所有数据
1）select * from student where age>24 and age<31;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\d2847b5cfbe24cd2bdc031156077595d\clipboard.png
2）查询年龄在24到31之间的所有数据
select * from student where age between 24 and 31;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\f2c2c260453f45fea8226a29c0d24111\clipboard.png
14、查询大于等于24岁到小于等于31岁的人所有数据
select * from student where age>=24 and age<=31;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\fc21f613bd0e46938b01a5764d63b616\clipboard.png
15、查询班级为空的null所有数据
1）select * from student where class is null;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\8909364544b2400cb003a63ba411a519\clipboard.png
查询班级不为空的null所有数据
2）select * from student where class is not null;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\02d4522ed54a47dab466c60e774dd8d9\clipboard.png
16、查找前五行的数据
1)select * from student limit 5;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\99d7371ced354351be123e8210ee3516\clipboard.png
2)select * from student limit 0,5;（0代表下标）
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\7e43c98eae9f4943b55eb5b807baa4a7\clipboard.png
17、查找3-8行的数据
select * from student limit 2,6;（2代表下标，从2往下数6个数）
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\3d5cdf2deae34f6eaa6cd51d1992d50d\clipboard.png
总价limit x ,y（x是下标，最下目标数-1，x+y=最大目标数）

18、模糊搜索，查询表单里name是 xi开头的所有数据
select * from student where name like "xi%";
%叫通配符
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\4eac5725872f42d19fe495bfcdffda51\clipboard.png
查询name后缀带qi的全部数据
select * from student where name like "%qi";
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\586125f66f304c43b77139658c5ba735\clipboard.png
查询name中带ao的全部数据
select * from student where name like "%ao%";
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\9fc1b2b81cc244089a0cb8d9958d065a\clipboard.png
19、根据分数升序
select * from student order by math;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\6ebbe50c31c742d699ffaf3595991634\clipboard.png
select * from student order by math asc;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\3128377d41f94c1d9ef196496ee1f59e\clipboard.png
20、根据分数进行降序
select * from student order by math desc;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\17485e790f444b3f9789dcb2742b176d\clipboard.png

desc ==descend下降
asc ==ascend==上升
聚合函数（where 后面不能有聚合函数）
count()==统计
sum()==求和
avg()==平均值
max() ==最大值
min()  ==最小值
distinct()  ==去重
21、根据班级class字段分组，然后求出每个班级的人数 group by(以什么分组）
select class,count(*) from student group by class;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\3e4d0be568c94308bfd54c34e414b812\clipboard.png
22、帮class和count(*)分别取名为a和b
select class a,count(*) b from student group by class;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\46148410025d4c8cbd4f3496305096e7\clipboard.png
23、改中文名称
select class as "班级",count(*) as "每个班级人数" from student group by class;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\68400f02721d445db82e1dcc7d0554c7\clipboard.png
24、通过聚合函数，min()求最小的，求出student表中分数最低的人
select min (math) from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\1087330d425549e58d3ca72e01be30f9\clipboard.png
24、通过聚合函数，min()求最小的，求出student表中分数最高的人
select max (math) from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\08ed2ae8479647928c9f478bcadce457\clipboard.png
25、通过聚合函数，求出这个表格分数的总和
select sum(math) from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\7773d4d479c14a51b76466ff4aaf2aa0\clipboard.png
26、通过聚合函数，求出student 表中平均值
select avg(math) from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\5f5fbd4c6c2044faae2231d29b3617ff\clipboard.png
27、通过聚合函数，统计student表中有几行
select count(*) from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\aebded0e439d4a8cbb1864bb66e1e4df\clipboard.png
28、通过聚合函数，把student里面的分数去重
select distinct(math) from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\4a53f067eb4c415b93d7e65c3196b09d\clipboard.png
29 、通过聚合函数，求出年龄最大的姓名
1）select name,age from student where age=(select max(age) from student);
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\a9c914f603094120b12831e179747b3b\clipboard.png
2）select name,age from student order by age desc limit 1；
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\836e6a7083cc4b798ac3cc0ee9d739fb\clipboard.png
30、需求：
1）求出每个班级中数学成绩大于80分的人数
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\b0177eacdf53409d8b4c596c2436271e\clipboard.png
2）求出每个班级中性别为1的数学总成绩
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\81d5aa4fe4184992ae7967e13b053f42\clipboard.png
31、求出每个班级的数学总成绩大于200的班级和成绩信息
select class,sum(math) from student group by class having sum(math)>200;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\dd24e40a7d994d3bb64e54a33166504b\clipboard.png
32、上面的把聚合函数改名为s
select class,sum(math)s from student group by class having s>200;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\235c3e54f4bb46b48151327bfe50dac6\clipboard.png
33、查询每个班级数学成绩最高的
select class,max(math) from student group by class;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\41c3fd6527ea4f4190b58b658e0fc789\clipboard.png
34、查询每个班级数学成绩最低的
select class,min(math) from student group by class;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\d0a7eb573a4d4bca901436096b95160a\clipboard.png
重点：
分组函数group by 只能和聚合函数和分组字段一起使用
where 后面可以接group by 分组函数，但是group by 分组函数后不能跟where
group by 前面加where 是为了先过滤再分组
having 跟在group by 后面，作用和where一样的

35、备份表
1）创建一个student1表，表结构和student一致
create table student1 like student;没有内容
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\d9574a3f1d2941ce83ccb8d9cf7d0654\clipboard.png
2)在student1中插入student的所有内容
insert into student1 select * from student;有内容
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\e58618ee5484437ba27227533101ffc5\clipboard.png
3）在空白表student2中插入student 的id,age,sex,三个部分的数据
insert into student2(id,age,sex) select id,age,sex from student;
C:\Users\Administrator\AppData\Local\YNote\data\weixinobU7Vjh6e9H8aeHgMdnuo1oJuNj4\b15e765c2dff4ba0971bf36c6c2d3888\clipboard.png