1 show tables;
2 desc book;
3 select * from book;
4 alter table book add sscore tinyint unsigned default 60;
5 insert into book values (null,'李五四','98'),(null,'王','10'),(null,'王三五','20');
6 insert into book values (null,'李','98'),(null,'李二','10'),(null,'李三','20');
7 /*查看book表格里面的指定的列,顺序可以随意改变*/
8 select bookid,bookname from book;
9 /*设定book表里面列的别名设定*/
10 select bookname 姓名,sscore 成绩 from book;
11 /*按照sscore 升序*/
12 select * from book order by sscore desc;
13 /*按照sscore 降序*/
14 select * from book order by sscore asc;
15 /*查询book表,姓名为li并且成绩为110的,and为逻辑与,or逻辑或,not逻辑非*/
16 select * from book where bookname='li' and sscore='110';
17 /*查询book表里面成绩不等于110的显示出来,<> != 都为不等于*/
18 select * from book where sscore <>110;
19 select *from book where sscore !=100;
20 /*说反话,即为成绩为50的,这样效率低尽量不要这样写*/
21 select * from book where not sscore !=50;
22 /* in not in 的使用*/
23 /*查询book表格里面的bookname列里面的名字为王五,李二的人,or逻辑或*/
24 select *from book where bookname in('王五','李二');
25 select * from book where bookname ='李三' or bookname='李二';
26 /*查询book表格里面的bookname列里面的名字不为王五的人*/
27 select *from book where bookname not in('王五');
28 /*删除book表里面id为2,16,17的数据信息*/
29 delete from book where bookid in (2,16,17);
30 /*between and not between 可以在日期时间数字里面引用*/
31 select * from book where sscore between 80 and 100 ;
32 /*查询出分数不在80-100之间数字*/
33 select * from book where sscore not between 80 and 100;
34 /*like not like 模糊查询 like和=相同 not like 是取反*/
35 select * from book where bookname like '王五';
36 select * from book where bookname = '李三';
37 -- %一定是配合like使用 %表示0个或者多个任意字符,查找表中李开头的
38 select * from book where bookname like '李%';
39 -- 检索表格里面包含l的所有的信息
40 select * from book where bookname like '%l%';
41 -- -表示单个任意字符,会输出含有2个字的人的信息
42 select * from book where bookname like '__';
43 -- 查找李开头的有2个字的
44 select *from book where bookname like '李_';
45 -- 正则表达式一个点代表一个字母,3个点相当于一个汉字,一个点表示一个字节,表示一个任意符号
46 select * from book where bookname regexp '^......$';
47 -- 此种格式是不正确的%必须配合like使用如下的写法是没有结果的
48 select * from book where bookname ='李%';
49 -- is null is not null 统计为空的值 让bookid=21 的成绩修改为0 ’'此为空字符串不是null
50 update book set sscore =null where bookid in (21);
51 select * from book ;
52 select * from book where sscore is null ;
53 -- select * from book where sscore = null ;不能这样写是错误的
54 select * from book where sscore is not null;
55 -- null 值不参与求平均值,值为0 要参与求平均值
56 -- 查询总归有多少条数据总和
57 select count(*) from book;
58 -- 查询book为空的总共有多少条
59 select count(*) from book where sscore is null ;
60 -- 查询所有学生的平均成绩 avg表示平均分
61 select avg (sscore) from book;
62 -- 查找平均分,最高分,最低分
63 select avg(sscore) 平均分 ,max(sscore) 最高分 ,min(sscore) 最低分 from book;
64 -- 分组统计配合使用
65 select bookname 姓名 count(*) from book group by bookname;
66 -- 统计出性别为女生的总人数
67 select count(*) from book where sgender ='女';
68 -- 统计出矿考的学生 此处一定不能用=
69 select count(*) from book where sscore is null;
70 -- 统计各专业的人数
71 select 姓名 from book group by bookid;
72 -- 消除重复的bookid distinct消除
73 select distinct bookid from book;
74 use xx;
75 alter table book add sgender enum('男','女','保密');
76 insert into book values(50,'小米',90,'女'),(51,'大米',20,'女');
77 desc book;
78 select * from book;
79 drop table student,teacher;
80 -- 统计各专业人数 group by 根据专业分组 order by avg 根据什么降序排列
81 -- 平均分排行前两位的
82 select sdept 专业,count(*) 人数,
83 avg(sscore) 平均分,
84 max(sscore) 最高分,
85 min(sscore) 最低分
86 from student where sgender='男'
87 group by sdept order by avg(sscore) desc limit 2;
88 having avg(sscore)<60;
89 select * from student;
90 select count(*) from student
91 -- 消除重复列的数据 distinct
92 select distinct sdept from student;
93 -- max为集合函数不能直接在where中使用所以如下的语句是错误的
94 select * from student where sscore=max(sscore);错误
95 -- 查询最高分的学生信息,union 并集查询 子查询
96 select * from student where sscore in (select max(sscore) from student)
97 union
98 select * from student where sscore in (select min(sscore) from student);
99 -- 连接查询,效率低显示学生学号,姓名成绩,代课老师姓名
100 select sid,sname,sscore,tname from student ,teacher where stid=tid;
101 -- 连左查询
102 select sid,sname,sscore,tname from student left join teacher on stid=tid;
103 -- 显示老师 ID学生姓名成绩
104 select tid,tname,sid,sname ,sscore from teacher left join student on tid=stid
105 order by tname desc;
106 select * from teacher;
107 insert into teacher values( null,'王老师');
108 /*老师名字按照升序排列*/
109 select * from teacher order by convert (tname using gbk) asc;