#（#注释 ctrl+/快速注释 ctrl+shift+/快速解除注释）
#切换数据库 use
use shopping ;
1.基础查询
语法：select 字段 from 表明 where 条件
select * from student; #从student 里查询所有字段（*代表所有信息）
select name from student; #从student 里查询name栏
select age ,name from student; #从student 里查询name和age栏，字段之间用英文逗号隔开。
select * from student where name=‘周瑜’; #查询周瑜信息
select age,name from student where name=‘周瑜’; #查询周瑜的年龄信息
2.模糊查 like（一般和通配符%连用）
select * from student where name like ‘曹%’; #首字是曹的
select * from student where name like ‘%操’; #尾字是操的
select * from student where name like ‘%貂%’; #名字中带貂字的
select * from student where name like ‘曹_’; #下划线占位，一个下划线代表后面有一个字
select * from student where name like '曹_’; #_下划线占位，两个下划线代表后面有两个字
select * from student where tel like ‘%5670’; #Tel字段尾号为5670的人的所有信息
select * from student where tel like ‘%5670%’; #Tel字段中有5670的人的所有信息
3.大于 小于 等于 不等于
select * from student where age =‘21’; #查找21岁的人的所有字段（id name age sex home tel bbb称为字段）
select * from student where age<=‘50’ and age>=‘20’;
select * from student where age<=‘20’ or age>=‘50’;
select * from student where age<‘40’ or age>‘40’; #年龄不是四十岁的人的所有字段
select * from student where age<>‘40’; #年龄不是四十岁的人的所有字段
select * from student where age!=‘40’; #年龄不是四十岁的人的所有字段
select name,age,tel from student where name =‘周瑜’ or name=‘小乔’; #查询周瑜和小乔的姓名 年龄 电话信息
select * from student where age =‘40’ and home=‘信阳’; #查询信阳的40岁的人
select * from score where score<‘80’ and subject=‘语文’; #查询语文成绩小于80的所有人的信息
4.between and
select * from student where age between ‘20’ and ‘50’;
select * from student where age between 20 and 50;#查找年龄在20与50之间的人的所有信息
5.统计 count（默认不包含空值）
select * from score;
select COUNT(distinct name) from score ;#distinct 区别 用于,统计去重后有几个人
select count(*) from (select distinct age, name from score) ss ;
6.去重 distinct
select distinct subject from score;
select distinct NAME,score from score;#对name 和xcore 相同的数据去重。
#查询出名字是张飞，周瑜，小乔，关羽，孙权这些人的名字信息。
select * from student where name in (‘张飞’,‘周瑜’,‘小乔’,‘关羽’,‘孙权’);
select * from student where name not in (‘张飞’,‘周瑜’,‘小乔’,‘关羽’,‘孙权’);#查询不包含张飞…的信息
select * from student where name in (‘张飞’);
select * from student where tel is null;
select * from student where tel is not null;#查询电话号码不是空值的所有信息
7.聚合函数
select *from student;
Select sex，avg(age) from student where sex=‘男’;
select sum(age) from student;
select min(age) from student;
select max(age) from student;
select count(id) from student;#统计有多少个id，count后不能有空格
8.分组 group by
select avg(score) from score where subject =‘英语’; #求score 表里的英语成绩的平均分
select subject,avg(score) from score group by subject; #求score 表里的各科成绩的平均分
select subject,avg(score) from score group by subject order by avg (score) desc;
#求score 表里的各科成绩的平均分并把平均分按照降序排列
select name, avg(score) from score group by name; #求score 表中的每个人的成绩的平均分
9.having
Select name from score group by name having min(score)>80
#查询每科成绩都大于80的学生姓名
select name,sum(score) from score group by name having sum(score)>500 ;
#求 score表中各科成绩总分大于500的人及对应成绩
select subject,sum(score) from score group by subject having sum(score)>411 order by sum(score) asc ;
#将每个科目总分在411分以上的科目求出，并按照求出的科目总分进行升序排列
10.排序 ORDER BY
select * from student order by age #字段后不加 asc 或 desc mysql 默认升序排列
select * from student where sex=‘男’ order by age desc;#男的降序排列
select * from student ORDER BY age asc,id desc;#先按照年龄升序排列，如果年龄一样则再按照ID降序排列
select * from student order by id desc ,age asc ;#如果di后不加desc
如果需要排序字段为数值，但字段类型非数值类型，可以通过cast()或者+0转换类型排序
SELECT * from student order by CAST(age AS SIGNED) ASC ;
SELECT * from student order by age+0 ASC ;
11.取前几行 limit
select * from student limit 5;#取前五行(LIMIT 限制）
select * from student limit 5,3;#取过滤到前五行的下三行（取原表的6.7.8行）（偏移五个取三个）
select * from student where sex=‘男’ limit 3;
select * from student where sex =‘男’ order BY age asc limit 3;#将性别为男的按照年龄升序排列并取前三行
12.查询所有语法
1.select 字段1,字段2 from 表格名 where 条件 group by 字段 having 条件 order by 字段1 desc，字段2 asc limit 3,3
select name,subject,sum(score)from score where name in(‘曹操’,‘孙权’) GROUP BY name having sum(score)>100 orDER BY sum(score) desc limit 1,2;
select id,age,sex,home from student
where age>'20’and age!=‘40’ and sex='男order by age desc,id desc limit 3,3;
**13.临时重命名 as **
临时重命名
对于字段太长时，可临时重命名，使结构看着简单易懂（在本语句中起作用，可加as 也可不加）。
select min(age)最小年龄 from student;
select max(age) as 最大年龄 from student;
select name 姓名,sex 性别 from student ;
select st.age,st.name from student st;#完整形式select student.age,student.name from student ;
14.多表联查 join on
条件 两个表至少有一个字段相同（无论内外连接）
内连接
把两个表格匹配条件相同的输出 ；
左外连接
以左表为基础，将左表全部输出，右表中输出与左表匹配的，不匹配的以空值形式输出
右外链接
以右表为基础，将右表全部输出，左表中输出与右表匹配的，不匹配的以空值形式输出
from后连接的第一个表始终显示在最左面（无论左右连接）
select * from student join score on student .name =score.name;
select student.name from student join score on student .name =score.name;
select * from student join score on student .name =score.name and student.name=‘曹操’;
select * from student join score on student .name =score.name where student.name=‘曹操’;
select * from student inner join score on student .name =score.name;#inner join 是join的完整写法
select * from student st join score sc on st .name =sc.name; #重命名
#左外连接
select * from student left join score on student.name = score.name;
select * from student left outer join score on student .name =score.name;#完整写法
#右外连接
select * from student right outer join score on student .name =score.name;
/*
三表联查 找到一个中间表使，这个表分别至少有一个字段和另外两个表相同
只要有表格满足中间表格，可以进行很多表联查
*/
select * from student st right outer join subject su on st.name =su.name
join score sc on su .subject =sc.subject;
select * from student;
select * from subject;
select * from score;
select * from student right join subject on student.name=subject.name join score on subject.subject=score.subject;
Select * from bao1,exelf;
#拼接两个字段无关联的两个表格 笛卡尔积(如果表1有三行数据，表2有四行数据，则拼接后的表格有12行数据。）
15.子查询
就是嵌套 一个SQL语句里面还有一个SQL语句。
子查询作为条件使用时无需重命名。
子查询作为一个表格使用时需要对子查询进行临时重命名。
*/
select count(name) from (select name from student_transcript group by name having min(score)>80 )ss ;
#一张表A 中有id和home两个字段查询出name 重复的所有数据
select * from a where name in (select name from a group by name having count(name)>1 )
select count(distinct name) from student where age>40;
select count(name) from (select age,name from student group by name having age>40)cc;
select count(name) from student where name in (select name from student where age>40 group by name);
#统计有多少人年龄在40岁以上
16.事务
是数据库中的一组逻辑操作，所有的操作要么全部成功要么全部失败
select * from account;
start transaction;
update account set account=account+5000 where name=‘小白’;
update account set account=account-5000 where name=‘小明’;
commit;
select * from information_schema.innodb_trx; #查询数据库正在执行的事务
show profiles; #查看数据库的历史SQL的执行时间
show PROCESSLIST; #查看数据库当前执行的sql
show tables; #查询数据库里的所有表格
desc student; #显示student表格中所有字段信息。
