where条件筛选记录
select id, username, age from uesr where id= 5 ;
alter table user add userDesc varchar ( 100 ) ;
update user set userDesc= "This is a test" where id<= 5 ;
select id, username, age, userDesc from user where userDesc<=> null ;
select id, username, age, userDesc from user where userDesc is null ;
select id, username, age, sex from user where age between 18 and 20 ;
select id, username, age, sex from user where id in ( 1 , 3 , 5 , 7 , 9 ) ;
select id, username, age, sex from user where sex= "男" and age>= 20 ;
select id, username, age, sex from user where salary between 20000 and 100000 and sex= "男" ;
select id, username, age, sex from user where id= 1 or username= "queen" ;
select id, username, age, sex from user where username like "queen" ;
select id, username, age, sex from user where username like '%三%' ;
select id, username, age, sex from user where username like "___" ;
select id, username, age, sex from user where username like "张_%" ;
group by 对记录进行分组
select id, username, age, sex from user group by sex;
select id, group_concat( username) , age, sex from user group by sex;
+
| id | group_concat( username) | age | sex |
+
| 1 | king, 张三, 张子枫, 刘德华, 吴亦凡, 张阿文, 经过历 | 23 | 男 |
| 2 | queen, imooc, 子怡, 王菲 | 27 | 女 |
+
select count ( * ) as total_users from user ;
select count ( userDesc) from user ;
select group_concat( username) as usersDetail, sex, addr, count ( * ) as totalUsers from user group by sex;
+
| usersDetail | sex | addr | totalUsers |
+
| king, 张三, 张子枫, 刘德华, 吴亦凡, 张阿文, 经过历 | 男 | 上海 | 7 |
| queen, imooc, 子怡, 王菲 | 女 | 上海 | 4 |
+
select addr,
group_concat( username) as usersDetail,
count ( * ) as totalUsers,
sum ( age) as ageSum,
min ( age) as ageMin,
avg ( age) as ageAvg
from user
group by addr;
+
| addr | usersDetail | totalUsers | ageSum | ageMin | ageAvg |
+
| 上海 | king, queen, 张三, 张子枫, 吴亦凡 | 5 | 161 | 23 | 32.2000 |
| 北京 | imooc, 子怡 | 2 | 56 | 25 | 28.0000 |
| 南京 | 刘德华 | 1 | 14 | 14 | 14.0000 |
| 广州 | 王菲 | 1 | 62 | 62 | 62.0000 |
| 湖南 | 经过历 | 1 | 25 | 25 | 25.0000 |
| 西安 | 张阿文 | 1 | 14 | 14 | 14.0000 |
+
select group_concat( username) as usersDetail,
count ( * ) as totalUsers
from user
group by sex
with rollup ;
select addr,
group_concat( username) as usersDetail,
count ( * ) as totalUsers
from user
group by 1 ;
select age,
group_concat( username) as usersDetail,
count ( * ) as totalUsers
from user
where age>= 30
group by sex;
select addr,
group_concat( username) as usersDetail,
count ( * ) as totalUsers
from user
group by addr
having count ( * ) >= 3 ;
order by 实现排序效果
order by 字段名称 asc | desc
select id, username, age
from user
order by id desc ;
select id, username, age
from user
order by age asc , id asc ;
select id, username, age
from user
where age>= 30
order by age desc ;
select rand( ) ;
select id, username, age
from user
order by rand( ) ;
limit限制结果集的显示条数
limit 值
limit offset , row_count
select id, username, age, sex from user
limit 5 ;
select id, username, age, sex from user
limit 1 , 5 ;
+
| id | username | age | sex |
+
| 2 | queen | 27 | 女 |
| 3 | imooc | 31 | 女 |
| 4 | 张三 | 38 | 男 |
| 5 | 张子枫 | 38 | 男 |
| 6 | 子怡 | 25 | 女 |
+
update user set age= age+ 5 limit 3 ;
update user set age= age- 10 order by id desc limit 3 ;
delete from user limit 3 ;
delete from user order by id desc limit 3 ;
单表查询完整select语句的形式
select addr,
group_concat( username) as usersDetail,
count ( * ) as toTalUsers,
sum ( age) as sum_age,
max ( age) as max_age,
min ( age) as min_age,
avg ( age) as avg_age
from user
where id>= 2
group by addr
having sum_age>= 25
order by sum_age
limit 2 ;