数据库查询操作DQL

数据库查询操作DQL

1.查询所有的信息
select * from cms_admin；

2.查询单独的一列或者几列
select id,username,role from cms_admin;

select cms_admin.id,cms_admin.username,cms_admin.role from cms_admin；

3.给表明起别名
select id,username,role from cms_admin AS a;

select a.id,a.username,a.role from cms_admin AS a;

4.给字段起别名
select id as “编号”,role as “角色”,username as “姓名” from cms_admin;

select 1,2,3,4,5,id,username from cms_user;

5.带条件的查询
select id,username,email from cms_user where id =1;

select * from cms_user where id >3;

6.查询表中记录是否为NULL



①.查询是否为NULL，需要用<=>查询

②.查询NULL，用is null

7.范围查找
查询编号为3-10之间的用户
select * from cms_user where id between 3 and 10;

指定集合查找
查询编号为1,3，5,7，9,11,13
select * from cms_user where id in(1,3,5,7,9,11,13);


8.模糊查询
%：代表0个、一个或者多个任意字符。
_:代表1个任意字符。

搜索姓张的用户
select * from cms_user where username like “%张%”;

查询用户名中包含in的用户
select * from cms_user where username like “%in%”;

查询用户名为3位的用户

select * from cms_user where username like “___”;

9.逻辑运算符查找

查询用户名为king并且密码设置为king的用户
select * from cms_user where username=‘king’ and password=‘king’;

查询编号大于等于3年龄不为NULL的用户
select * from cms_user where id >=3 and age is not null;

查询编号在5-10的用户并且用户名为4为的用户
select * from cms_user where username like “____” and id between 5 and 10;

查询用户名以张开始或者用户所在省份为2和4的记录
select * from cms_user where username like “张%” or proId in (2,4);

10.分组查询
按照用户所属省份proId分组(只会显示所属组的第一条信息和所有组个数，不知道所属组有几条信息）

select * from cms_user group by proId;

向用户表中添加性别字段
添加新字段
alter table cms_user add sex enum(“男”,“女”,“保密”);
更新数据
update cms_user set sex=“男” where id in (1,3,5,7,9);
update cms_user set sex=“女” where id in (2,4,6,8,10);
update cms_user set sex=“保密” where id in(11,12);
按照性别分组
select * from cms_user group by sex;

按照多个字段分组
select * from cms_user group by sex,age;

查询编号大于5的用户，按照sex分组
select * from cms_user where id >5 group by sex;

11.分组查询配合聚合函数
查询id，sex，按照性别分组，详情内包含用户名。
select id,sex,group_concat(username) from cms_user group by sex;

查询proId，性别详情，注册时间详情，用户名详情，按照proId分组
select proId,group_concat(username),group_concat(sex),group_concat(regTime) from cms_user group by proId\G;

查询编号，sex，用户名详情以及组中总人数，按照sex分组
select id,sex,group_concat(username) as user,count() as totalusers from cms_user group by sex;

统计表中所有的记录（不统计NULL值）
select count() as totalusers from cms_user;

查询编号，性别，用户名详情，组中总人数，最大年龄，最小年龄，平均年龄以及总年龄，按照性别分组。
select id，sex,group_concat(username),count(*) as totaluser,max(age) as max_age,min(age) as min_age,avg(age) as avg_avg,sum(age) as sum_age from cms_user group by sex;

with rollup用法

12.having语句对分组结果进行二次筛选（只能在分组使用）

查询sex，用户详情，组中总人数，最大年龄，最小年龄，根据sex分组。

基于上述查询，再次查询组中人数大于2的。
select sex,group_concat(username) as user,count() as total_user,max(age) as max_age,min(age) as min_age from cms_user group by sex having count()>2;

查询组中人数大于2且最大年龄大于60的
select sex,group_concat(username) as user,count() as total_user,max(age) as max_age,min(age) as min_age from cms_user group by sex having count()>2 and max(age)>60;

13.ORDER BY对查询结果排序
按照id降序排序DESC，默认是ASC（升序）
select * from cms_user order by id DESC;

按照年龄升序排列
select * from cms_user order by age;

也可以按照列的序号进行升序降序排列（如id位于第一列）
select * from cms_user order by 1;

按照年龄升序，id降序排列
select * from cms_user order by age ASC,id DESC;（当第一个字段相同时，按照第二个字段排序 ）

所有的结合
select id,age,sex,group_concat(username),count() as total_user,sum(age) as sum_age from cms_user where id>=2 group by sex having count()>=3 order by age DESC,id ASC;

随机提取记录
select * from cms_user order by rand();

14.通过limit限制显示条数

查询表中前三条记录
select * from cms_user limit 3;

select * from cms_user order by id DESC limit 5;

得到表中第一条记录
select * from cms_user limit 1;

select * from cms_user limit 0,1;（第一个参数表示起始的偏移量，从0开始。第二个参数表示显示的条数）
当用偏移量显示了第一条记录时，则第二次要从1开始

显示前五条记录
select * from cms_user limit 0,5;

再显示后面的5条数据
select * from cms_user limit 5,5;

select id,sex,age,group_concat(username),count() as total_user,max(age) as max_age,min(age) as min_age,avg(age) as avg_age,sum(age) as sum_age from cms_user group by sex having count()>=2 order by age DESC limit 0,2;

15.更新与删除结合排序和限制条数

更新用户年龄为4位的用户，让其年龄-3
update cms_user set age=age-3 where username like “____”;

**更新前3条记录，让其年龄+10 **
update cms_user set age=age+10 where limit 3;

按照id降序排列，更新前三条
update cms_user set age=age+10 order by id DESC limit 3;

删除用户性别为男的用户，按照年龄降序排列，删除前一条记录
delete from cms_user where sex=“男” order by age DESC limit 1;

16.内连接查询

查询cms_user,id,username,proId，与provinces表中的proId所对应省份的名称
select cms_user.id,username,proName from cms_user,provinces where cms_user.proId=provinces.id;

查询cms_user表中的id，username，email，sex
查询provinces表中的proName
select u.id,u.username,u.email,u.sex,p.proName from cms_user as u inner join provinces as p on u.proID=p.id;

查询cms_user表中的id，username，sex
查询provinces表中的proName
条件是cms_user的性别为男
select u.id,u.username,u.sex,p.proName from cms_user as u join provinces as p on u.proId=p.id where u.sex=“男”;

select u.id,u.username,u.sex,p.proName,count() as total_user,group_concat(username) from cms_user as u join provinces as p on u.proId=p.id where u.sex=“男” group by p.proName;
select u.id,u.username,u.sex,p.proName,count(),group_concat(username) from cms_user as u join provinces as p on u.proId=p.id where sex=“男” group by p.proName having count(*)>1;

查询cms_news中的id，title
查询cms_cate中的cateName

select n.id,n.title,c.cateName from cms_news as n join cms_cate as c on n.cId=c.id;

17.外键
作用：保持数据的一致性和完整性
create table employee(id int key auto_increment,username varchar(20) not null,depId int, foreign key(depId) references department(id));


create table employee(id int key auto_increment,username varchar(20) not null,depId int, foreign key(depId) references department(id) on delete cascade);

此句话的外键增加了delete cascade，意思为与主表的id如果进行删除，则字表相应的也会删除

此句话增加了update cascade ，意思为主表的id如果进行更新，则字表相应的也会更新

18.联合查询

19.子查询