MYSQL中查询相关知识

查询语句：通过SQL语句将实体表信息以虚表的形式展现
全查询（所有列查询，所有行显示）
select * from 表名;
select * from scoretext;
筛选部分列查询
select 列字段列表 from 表
select username，userage，useraddress from userinfo;
重命名查询（列重命名 as）
select username as 姓名，usersex as 性别 from userinfo；
重命名查询（表重命名）
select u.userName from userinfo u;
排序查询 order by asc|desc
select * from userinfo order by userage asc;按照年龄进行升序排列
select * from userinfo order by userage desc;按照年龄进行降序排列
select * from userinfo order by userage,userid asc;先按照年龄进行升序排列，年龄相同的按照id进行升序排列
区间查询 limit ？，？
select * from userinfo limit 8,5；从几开始，几个数（从8开始，取出5个数，即9,10,11,12,13）
分页表达式如何操作 page？页码 size：每页显示条数
select * from 表名 （page-1）*size，size；
分页和排序查询（先排序后分页）
select * from userinfo order by userage limit 0,10;先把表中的年龄进行排序从1开始选取10个数
条件查询where
（1）关系运算符的条件查询 > >= < <= <> !=
select * from userinfo where userage>28;
select * from userinfo where userage>=28;
select * from userinfo where userage<28;
select * from userinfo where userage<=28;
select * from userinfo where userage<>28;
select * from userinfo where uaerage !=28;
（2）逻辑运算符的条件查询 and or between and
select * from userinfo where userage>18 and uaerage<30;
select * from userinfo where usersex=‘男’ or usersex=‘女’;
select * from userinfo where userage between 18 and 30;
范围查询 in not in
select * from userinfo where useraddress in（‘北京’,‘南京’,上海’’）;查询住址在北京或者南京或者上海的人（or）
select * from userinfo where useraddress not in(‘苏州’,‘深圳’,‘广州’);查询住址不在苏州和深圳和广州的人（and）
模糊查询 like not like %(0,正无穷)
select * from userinfo where username like ‘张%’;以张开头的后面匹配任意字符的查询
select * from userinfo where username like ‘%张’;以张结尾的前面匹配任意字符的查询
select * from userinfo where username like ‘%张%’;中间出现张字前面后面任意字符匹配的查询
select * from userinfo where username not like ‘李%’;
唯一查询
select distinct useaddress from userinfo;使用distinct对某一列进行去重复查询
子查询：将一个查询的结果当成是另一个查询的条件，那么这个查询就是子查询，另一个查询就是父查询
查询年龄比张亮大的人
select * from userinfo where userage>(select userage from userinfo where username=‘张亮’?
select userage from userinfo where username=‘张亮’; 子查询
select * from userinfo where userage>31;父查询
查询年龄和任意一个西安人年龄相同的人
select * from userinfo where userage in (select userage from userinfo where useraddress=‘西安’);
注意：子查询的查询结果有且只能为1列；子查询的结果如果有多行时，不能使用关系运算符，只能使用in，not in等等
select * from userinfo where userage > any(select userage from userinfo where useraddress=‘西安’);
select * from userinfo where userage > all(select userage from userinfo where useraddress=‘西安’);
子查询的相关习题（已知建好的表）
1.显示前三名作者信息
select * from authors order by asc limit 0,3;
2.显示倒数2名作者信息
select * from author order by desc limit 0,2;
3.显示最新借阅图书的信息
select * from bookinfo where bookid=(select * from borrowinfo order by borrodate desc limit 0,1);
4.显示出版社为工人出版社的图书的信息
select * from bookinfo where bookid in (select distinct bookid from pubinfo where pubid=(select pubid from publisher where pubname=‘南京出版设’));
5.显示作者姓名中含有辰的作者出版的图书的信息
select * from books where bookid in
(select bookid from bookauthor where authorid=
select authorid from authors where authorname like '%辰%));
6.显示借阅最新出版的图书的读者
select * from readers where readername in
(select readerid from borrowinfo where bookid=
(select bookid from borrowinfo where borrowdate order by desc limit 0,1));
7.显示书名中带有文字的图书的信息
select * from books where bookname like ‘%文%’;
8.显示年龄最大的读者借阅图书的信息
select * from books where bookid in
(select bookid from borrowinfo where readerid=
(select readerid from readers where readerage order by desc limit 0,1));
常量查询
select 9;
select ‘a’;
select 9+1;
select ‘9’+1转为int进行查询
select concat (‘a’,‘b’);
合并列查询
select 8,‘a’;
select (select 8),(select ‘a’);
合并进行查询
select 9;
union
select 10;把查询出来的行9和查询出来的行9进行行合并
select * from userinfo where usersex=‘女’ unoin select * from userinfo where userage=28;
select * from userinfo where usersex=‘女’ union all selecct * from userinfo where userage=28;
为空查询（is, null , is not null）
select * from userinfo where useraddress is null;
select * from userinfo where useaddress is not null;
函数查询

abs(x) 返回x的绝对值
bin(x) 返回x的二进制(oct返回八进制，hex返回十六进制)
ceiling 返回大于x的最小整数的值
floor(x) 返回小于x的最大整数值
greatest(x1,x2,…xn)返回集合中最大的值
least(x1,x2,…xn) 返回集合中最小的值
mod(x,y) 返回x/y的模(余数)
pi() 返回pi的值(圆周率)
rand() 返回0到1内的随机值，可以通过提供一个参数(种子)使rand()随机数生成一个指定的值
round(x,y) 返回参数x的四舍五入的有y位小数的值
sign(x) 返回代表数字x的符号的值
sqrt(x) 返回一个数的平方根
select abs(-5), abs(7), abs(0);求绝对值
select sign(-7), sign(7), sign(0);返回数字代表的值-1代表负数，0代表0,1代表正数
select bin(5), cot(15), hex(23);求数字的二进制，八进制，十六进制
select floor(5.6), ceiling(7.8), round(6.5);求比数字小的最大的数，比数字大的最小的数，四舍五入
select mod(9,6),pi(), rand();求余数，圆周率，随机数
select least(1,2,4,3,6), greatest(4,7,9,5,3);集合的最小值，集合的最大值
字符函数
bit_length(str)返回字符串的比特长度
length(s)返回字符串str中的字节数
char_length(S)返回字符串中str中的字符数
注意：一个汉字是一个字符对应三个字节
select bit_length(‘我是001’), length(‘我是001’), char_length(‘我是001’);
字符拼接替换
select concat(‘a’,‘b’,‘c’);字符串的拼接，只是单纯的拼接值
select concat_ws(’， ‘,‘a ‘,’ v’,’ w’);把avg拼接起来，中间以，为分隔符
select insert(‘abcdefgh’,‘3’,‘5’,‘888’);原来的字符串，从什么位置开始，替换的个数，区间要替换的内容
select replace(‘abcdefgh’,‘def’,‘999’);原来的字符串，要替换的原来的内容，替换的内容
select replace(‘abcdefAAhihreAAABJBAcjekcj’,‘a’,‘33’);替换内容区分大小写，而SQL书写格式是不区分大小写的
去除空格操作
trim(str)去除字符串首部和尾部的空格
ltrim(str)去除字符串首部的空格
rtrim(str)去除字符串尾部的空格
select concat(trim(’ a b c ‘)‘5’),concat(ltrim(’ a b c ‘)‘5’),concat(rtrim(’ a b c ')‘5’));
返回字符串
left(str,s)返回字符串str中最左边的s个字符
right(str,s)返回字符串str中最右边的s个字符
substr(str,n,m)返回字符串str中从n开始到m结束的字符
select left(‘giuideihehri’,4),right(‘vcuwbxeuri’,6),left(‘cbiuwejexe’,5,4);
字符串出现的位置
position(substr,in str)返回字串substr在字符串str中第一次出现的位置
insert(str,substr)返回字串substr在字符串str中第一次出现的位置
select position(‘ac’,in ‘shuuiuecdacuiefued’);
select insert(‘shuuiuecdacuiefued’,‘ac’);
其他类型
select upper(‘AaAa’),lower(‘AaAa’);
select reverse(‘abcdef’);将字符串进行反转
select ASCII(‘a’);查询ASCII值
select char(97);查询值对应的字符
时间日期函数
select now(),curdate(),curtime();完整日期+时间，日期，时间
date_add （year month day hour minute second）
select date_add(now(),interval 3 year),date_add(now(),interval -3 year);现在时间的三年后和三年前
select date_add(now(),interval 3 month),date_add(now(),interval -3 month);现在时间的三个月前和三个月后
select date_add(now(),interval 3 day),date_add(now(),interval -3 day);
select 现在时间的三天前和三天后year(now()),month(now()),dayofmonth(now()),hour(now()),minute(now()),second(now()),dayofweek(now());获取到具体时间日期
类型转换函数
select cast(‘029’ as signed integer);字符串转为整型
select cast(29 as char);整型转为字符串，只支持char,不支持varchar
select cast(now() as signed integer);
分组和聚合函数
聚合函数：sum,avg,max,min,count
select sum(userage),avg(userage),max(userage),min(userage) count(userid),count(useraddress) from userinfo;usrid代表*表示全行，useraddress代表有地址的行除去空行
求出每个城市有几个人 group by
select useraddress ,count(userid) from userinfo group by useraddress;
select useraddress, max(userage), min(userage) from userinfo group by useraddress;
注意：如果使用分组，select和from之间只允许出现聚合函数和分组字段，即group by后面的东西和聚合函数
求出除了null之外的城市都有几个人
select useraddress,count(userid) from userinfo where useraddress is not null group by useraddress;
求出出现三次以上的城市有哪些
select useraddress,count(userid) from userinfo group by useraddress having count(userid)>3;
注意：having和where作用一模一样，只不过，having专用于分组，在分组之后执行 where>group>having
表连接
（1）内连接：两张表是平等关系，一一映射
select 带显示列字段 from 表A inner join 表B on 连接条件 inner join 表C on 连接条件…[where筛选条件];
select * from stuinfo;
select * from classinfo;
select stuname,classname from stuinfo s inner join classinfo c on s.classid=c.classid;
select stuname,classname from stuinfo s,classinfo c where s.classid=c.classid;
select username,rolerights from roles r inner join users u on r.roleid=u.roleid;
select username,rolerights from roles r,users u where r.roleid=u.roleid;
（2）左连接：两张表以左表为主,一一映射,左记录不存在，使用null补全
select 带显示列字段 from 表A inner join 表B on 连接条件 left[outer] inner join 表C on 连接条件…[where筛选条件];
（3）右连接：两张表以右表为主,一一映射,右记录不存在，使用null补全
select 带显示列字段 from 表A inner join 表B on 连接条件 right[outer] inner join 表C on 连接条件…[where筛选条件];