数据库函数select查询语句的分类语法结构多表连接,DISTINCT关键字表列的别名和m。。。

数据库函数select查询语句的分类语法结构多表连接,DISTINCT
关键字表列的别名和m。

数据库函数 select 查询语句
SQL语句
* DDL create,drop, alter
* DML insert,delete,update,select
* DCL grant,revoke
* TCL commit; rollback;
DDL修饰符:
* unsigned ⾮负数
* zerofill 0填充
* default 默认值
* null 空值
约束：
* primary key 主键，⾮空 + 唯⼀
* unique 唯⼀键，不能有重值
* not null ⾮空
* auto_increment ⾃增长 int，必须是primary key主键
1.SELECT 查询语句的分类语法结构多表连接案例
简单查询语句从⼀个表中查询数据
复杂查询语句多表连接查询（将来源于多个表的列横向叠加）
*内连接 *左连接 *右连接 *全连接 * ⾃连接 *⾮等值连接
复合查询（将查询结果集上下叠加）
*Union*Union All *Intersect *Minus
并集交集差集
⼦查询
* ⾮关联⼦查询 *关联⼦查询
语法结构
SELECT select_list # 1个或多个列名，之间⽤逗号隔开，列也称作投影
[ INTO new_table ] # 结果集放⼊指定⽂件
FROM [table_source] #表名
[ WHERE search_condition ] #表⾏过滤条件
[ GROUP BY group_by_expression ] #按照指定的列将表⾏分组，形成新的⾏
[ HAVING search_condition ] #对分组后的新⾏进⾏过滤
[ ORDER BY order_expression [ ASC | DESC ] ] #按照指定的1个或多个列进⾏排序，ASC=增序，DESC=降序
select where⼦句常⽤的算数逻辑⽐较运算符通配运算符和优先级
where⼦句对⾏记录进⾏过滤
1.算数运算符：
+,-,*, / 对应加，减，乘，除
2.逻辑运算符：
not (⾮）;and (⽽且)；or(或者)；
3.⽐较运算符：
= 等于； !=或<>不等于； >⼤于； <⼩于； >=⼤于等于； <=⼩于等于；
is null为空值；is not null为⾮空值；
in (值列表)在值列表中not in (值列表)不在值列表中；
between 低值and ⾼值（包含低值和⾼值）在低值和⾼值之间；
not between 低值and ⾼值（包含低值和⾼值）不在低值和⾼值范围内；
like ‘通配符’按照通配符进⾏匹配；
4.常⽤通配符：
% 匹配0个或任意多个字符_匹配任意1个字符
5.运算符优先级
各类运算符之间存在优先级，只记住括号( )的优先级最⾼即可
1.查询学⽣表中性别为‘⼥’，体重超过60公⽄的学⽣的所有信息
三个表
查询学⽣表中性别为‘⼥’，体重超过60公⽄的学⽣的所有信息
select * from stu where sex='⼥' and weight>60;
2.查询学⽣表中1班或者2班中，⾝⾼超过190的学⽣
select * from stu where (cno=1 or cno=2) and height>190;
或者
select * from stu where cno in (1,2) and height>190;
3.查询学⽣表中3班学⽣⾥⾯考分在400和520之间的⼥⽣
select * from stu where cno=3 and sex='⼥' and score between 400 and 520;
或者
select * from stu where cno=3 and sex='⼥' and (score>= 400 and score<=520);
4.查询学⽣表中没有分配班级⽽且是⼥⽣的学⽣
select * from stu where cno is null and sex='⼥';
5.在学⽣表体重低于40公⽄且⾝⾼低于1.65⽶的学⽣，列出其姓名，⾝⾼，体重，总分以及总分占750分满分的百分⽐select sname,height,weight,score,score/750*100 from stu where height/100<1.65 and weight<40;
6.在学⽣表中查找学⽣姓名，第⼆个字是‘侯’，或者第⼀个字是‘张’且名字只有两个字的学⽣
select * from stu where sname like '_侯%' or sname like '张_';
2mysql 常⽤的字符串数值⽇期条件判断 CASE 空值聚合关键字列的别名函数
函数⽤来处理SQL语句中的数据，可以嵌⼊在SQL语句中使⽤，增加了SQL语句对数据的处理功能
函数可以有0到多个参数，但是总会有⼀个返回值
函数可以⽤来计算、修改、格式化输出⼆维表中的各类数据
不同数据库的函数的名称和⽤法略有不同，
但都会提供如：
字符串处理、数值处理、⽇期处理、统计等分类的函数、⽅便⽤户处理各类数据
1.字符串函数 char_length(字符串) 的长度三个字姓名的学⽣
char_length(str)字符串长度
计算参数str中有多少个字符，str可以是具体的⼀个字符串，也可以是表中的列
1.查看字符串“中国⼈”有⼏个字（后⾯为常量from可省略
select char_length('中国⼈');
2.学⽣表中姓名为三个字的学⽣有哪些？
select * from stu where char_length(sname)=3;
或
select * from stu where sname like '___';
有些需求仅靠SQL语句提供的功能⽆法实现，必须依靠数据库提供的函数
2.concat(str1,str2,……)拼接把参数str1和str2拼接成⼀个字符串班级+姓名把参数str1和str2拼接成⼀个字符串
1.把‘我是’和‘中国⼈’拼接成⼀句话
select concat('我是','中国⼈');
2.学⽣表打印已分班的学⽣姓名和班级，以xxx是x班的形式打印结果
select concat(sname,'是',cno,'班') 名称表from stu where cno is not null;
3.substr(str,pos,len)截取把参数str字符串从第pos位起，截取len位把参数str字符串从第pos位起，截取len位字符串姓⽒
1.把‘我是中国⼈’字符串从第3位起截取3位字符
select substr('我是中国⼈',3,3);
⼆班的同学都有什么姓⽒？ (截取第⼀个字段) ⼆班同学的姓⽒
select substr(sname,1,1) from stu where cno=2;
4.)MySQL 数值四舍五⼊函数round(num,n)，数字和⾝⾼体重bmi值
四舍五⼊不要⼩数缺省为正数 0
对数字num进⾏四舍五⼊运算，从第n位⼩数算起(保留⼏位)
1.)15.5469，保留2位⼩数，从第2位⼩数进⾏四舍五⼊运算
select round(15.5469,2);
2.)计算肥胖学⽣许褚的BMI值，四舍五⼊保留2位⼩数，体重/⾝⾼^2
select round(weight/(height/100*height/100),2) from stu where sname='许褚';
5.)MySQL ⽇期函数year(date1) month(date1)
year(date1)
获取⽇期date1的年份
select year(‘2019-11-27 09:00:00’);
month(date1)
获取⽇期date1的⽉份
1.学⽣表中哪些同学是1990年出⽣的?
select * from stu where year(birth)=1990;
2.学⽣表中哪些同学是8⽉出⽣的?
select * from stu where month(birth)=8;
6.)计算时间curdate查 datediff(date1,date2) 计算年龄⽣⽇⼩于23岁curdate()获取当前⽇期
curtime()获取当前时间
now()获取当前的⽇期和时间
datediff(date1,date2)返回date1和date2两个⽇期间隔的天数
1.计算2018年6⽉1⽇和2018年元旦之间间隔的天数
select datediff('2018-6-1','2018-1-1');
select datediff('2019-11-29','2015-10-20'); 和静差1501天
2计算学⽣表中学⽣的年龄，显⽰姓名，⽣⽇，年龄（保留2位⼩数）,只显⽰⼩于23岁的同学
select sname,birth,round(datediff(now(),birth)/365,2) from stu where round(datediff(now(),birth)/365,2)<23;
7.MySQL 条件判断函数 if(expr,v1,v2) 分数姓名复姓
if(expr,v1,v2)如果表达式expr成⽴，返回v1值否则，返回v2值
1..如果学⽣⾼考分⼤于等于520，其为统招⽣，否则其为委培⽣，从学⽣表中查找，显⽰姓名，考分，类型（统招或委培）
select sname,score,if(score>=520,'统招','委培') 类型 from stu;
2.新来的学⽣都姓什么，需要考虑复姓(诸葛、太史、夏侯)和外号(⼤乔、⼩乔)？
select sname,substr(sname,if(sname in ('⼤乔','⼩乔'),2,1),if(substr(sname,1,2) in ('诸葛','太史','夏侯'),2,1)) 姓 from stu;
先从外号中筛选出⼤乔⼩乔，选出乔姓，然后截取两位名字，如果是诸葛，夏侯，太史截取两位，其他的就截取⼀位。

8.)MySQL CASE运算符多重条件判断考分是700优秀 600良好
CASE运算符为SQL语句增加了多重条件判断⽐if函数更加灵活
case when expr1 then v1 [when expr2 v2 then] …… else vn end
1.如果考分700以上，优秀，600以上，良好，520以上，中等，否则，较差，按照这个原则列出学⽣表中的学⽣，显⽰姓名，考分，和评判等级select sname,score,(case when score>=700 then '优秀' when score>=600 then '良好' when score>=520 then '中等' else '较差' end) 等级 from stu;
9.)MySQL 空值处理函数 ifnull isnull 显⽰未分班的同学空值问题：
使⽤in时，值列表中有null值，不会返回null相关结果
select * from stu where cno in (2,null);
使⽤not in列表中有空值时，⽆查询结果返回
select * from stu where cno not in (2,null);
有时空值会造成意想不到的问题，需要⽤空值函数先对空值进⾏处理
常⽤空值处理函数
ifnull(v1,v2) 如果v1为空返回v2,否则返回v1
isnull(expr) 如果表达式为null空值，返回1，表达式⾮空则返回0
1.仅显⽰2班和没有分班的同学
select * from stu where cno=2 or cno is null;
或
select * from stu where cno=2 or ifnull(cno,0)=0;
或
select * from stu where ifnull(cno,0) in (2,0);
或
select * from stu where isnull(cno)=1 or cno=2;
10.)MySQL 聚合函数 max最⼤ min最⼩ sun求和 avg平均 coun⾏的数量t
聚合函数
max(列名)
统计出列中所有⾏中的最⼤值，列的数据类型可以是数值、字符、⽇期型
min(列名)
统计出列中所有⾏中的最⼩值，列的数据类型可以是数值、字符、⽇期型
sum(列名)
统计出列中所有⾏中数值的总和，列的数据类型不能是字符型
avg(列名)
统计出列中所有⾏中数值的平均值，列的数据类型不能是字符型
count(列名）
统计出列中所有⾏的数量，列中的如果有空值不会被统计计数
1.找出学⽣中最⾼的⾝⾼、最轻的体重、平均⾼考分数、学⽣的总数
select max(height),min(weight),sum(score)/count(*),avg(score),count(*),count(sno),count(cno) from stu;
注意：列中有空值时不会被count函数统计计数只有⼀⾏数据结果
2.⽤⼀个查询语句找出学⽣中最低的⾝⾼、再显⽰所有⼈的姓名
select min(height),sname from stu;
查询语句报错了：min(height)只有1个值1⾏，sname有20⾏，1⾏对接20⾏是个⽭盾的事情，因此不能这么写。

select min(height),group_concat(sname) from stu;
注意：group_concat也属于⼀种聚合函数，此列中把20个⼈的名字（20⾏），⽤逗号分隔连成⼀个字符串（1⾏），这样就不会报错了。

3.)SELECT语句 DISTINCT关键字表列的别名
1.)ditinct查询出指定的1个或多个字段的不重复记录班级号性别
1.查询出学⽣表中都有哪些班号，要求去除重复班号
select distinct cno from stu;
2.列出学⽣表中不重复的班号和性别
select distinct cno,sex from stu;
注意：distinct关键字必须放在第⼀列，后续的1个或多个列作为去重列
select distinct sname,cno,sex,weight from stu;
2.)MySQL 列表的别名表头⽤对应的中⽂显⽰姓名⽣⽇
列别名：给列起个额外的名称⽤来替代列原来的名称
select sname [as] 姓名 from stu;“as”可写可不写
表头⽤对应的中⽂显⽰
1.列出2班的学⽣姓名，性别，⽣⽇。

select sname as 学⽣姓名,sex 性别,birth ⽣⽇ from stu where cno=2;
如果别名中有空格，或者别名是SQL保留字（如：select,from,where）别名要⽤双引号“”括起来，否则报错
表的别名
表别名：给表起个额外的名称⽤来替代表原来的名称
select s.sname,s.sex from stu as s;“as”可写可不写
1.列出2班的学⽣姓名，性别，⽣⽇，查询语句使⽤表别名来引⽤列名
select s.sname,sex,s.birth from stu s where o=2;重点是from后⾯的内容。

注意：多表连接查询往往经常使⽤表别名来区分不同表确名字相同的列；
如果表名很长使⽤表别名会使查询语句看起来⽐较简洁；
在数据库图形管理⼯具中书写查询语句时使⽤表别名，键⼊“表别名.列名”时，会出现下拉列表选择框，将⼤⼤提⾼⼯作效率r
4.select 内容 from表order by ⼦句
对查询结果集按照order by后⾯指定的1列或多列排序
分为增序和降序
增序a's'c，默认可以不写
降序 de's'c
对于数值，增序是从⼩到⼤，降序是从⼤到⼩
对于⽇期和时间，增序是从远到近，降序是从近到远
对于英⽂字符，增序是从a到z，降序是从z到a
对于中⽂字符，增序按照字符集编码从⼩到⼤，降序按照字符集编码从⼤到⼩，如果选择gbk编码可以按照拼⾳来排序
对于排序列的名称，可以⽤以下⽅式
列名、列别名、列的序号、函数、表达式等
1.按照体重增序列出学⽣信息
select * from stu order by weight;
2.展⽰已分班学⽣信息，先按照班号增序，再按照考分降序排列
select * from stu where cno is not null order by cno,score desc;
3.按照拼⾳降序排列所有学⽣
注意：
由于汉字在utf8编码中不是按照拼⾳顺序排列的，需要⽤到convert函数转换列sname的编码到GBK（国标库）；如果表列为gbk编码不需要
select * from stu order by convert(sname using gbk) desc;
4.显⽰学⽣性别，姓名，⽣⽇3列信息，先按第1列，再按第3列降序排序
select sex,sname,birth from stu order by 1,3 desc;
5.按照班号增序排序，没有分班的同学排在后⾯
select * from stu order by isnull(cno),cno;
所有的不是空值的返回零排在了前⾯，后⾯空值返回⼀排在了后⾯
6按照《关张赵马黄》的顺序排序2班同学
select * from stu where cno=2 order by (case when substr(sname,1,1)='关' then 1 when substr(sname,1,1)='张' then 2 when substr(sname,1,1)='赵' then 3 when substr(sname,1,1)='马' then 4 when substr(sname,1,1)='黄' then 5 else 6 end);
5.SELECT - LIMIT ⼦句
SELECT语句中的LIMIT⼦句最后执⾏
其作⽤是仅显⽰查询结果集中的部分内容
1.显⽰stu表中考分最⾼的3名学⽣
select * from stu order by score desc limit 3;
或
select * from stu order by score desc limit 0,3;
#limit 0,3中的0为第1条记录，如：limit 5,3意思为从结果集的第6条起，显⽰3条记录。

简单理解：第⼀个参数代表起始⾏（参数值+1），第⼆个参数代表⾏的跨度数⽐如1，3就不⼀样了，1，3代表从第2位开始截取3位，0代表第⼀位。

6.⾯试题alter 和 delete有什么区别？alter 和 update有什么区别？
alter 和 delete有什么区别？
*alter属于DDL语句
*alter改变表的结构
* delete属于DML语句
* delete不会改变表的结构
* delete删除⾏
alter 和 update有什么区别？
*alter属于DDL语句
*alter改变表的结构
* update属于DML语句
* update修改现存⾏的信息
* update不会改变表的结构。