MySQLベースのデータ管理【2】

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; -- <=>  null
--is [not] null   null
select id,username,age,userDesc from user where userDesc is null;

--[not] between ... and     
select id,username,age,sex from user where age between 18 and 20;

--[not] in( ...)     
select id,username,age,sex from user where id in(1,3,5,7,9);

--and / or
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";

--[not] like     
select id,username,age,sex from user where username like "queen";
--         %         _      
select id,username,age,sex from user where username like '% %'; --  username      
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 group by sex;
--    group_concat()             
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 |    |
+----+----------------------------------------------+-----+-----+

--         count() sum() max() min() avg()
select count(*) as total_users from user; --      ，null   ，  total_users
select count(userDesc) from user; --null   
--  sex  ，       ，            
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 |
+----------------------------------------------+-----+------+------------+

--  addr  ，        ，   ，         ，      、   、   
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 |
+------+-------------------------------+------------+--------+--------+---------+

--  with rollup             ，          
select group_concat(username) as usersDetail,
count(*) as totalUsers
from user
group by sex 
with rollup;

--         (addr)
select addr,
group_concat(username) as usersDetail,
count(*) as totalUsers
from user 
group by 1;

--  age>=30       sex  
select age,
group_concat(username) as usersDetail,
count(*) as totalUsers
from user
where age>=30
group by sex;

--having             
select addr,
group_concat(username) as usersDetail,
count(*) as totalUsers
from user
group by addr
having count(*)>=3; 
--having totalUsers>=3;               
--having               ( ：sum(),max())  select     
--having          ，                ，                select   

order by      asc|desc  --  |  

--  
select id,username,age 
from user
order by id desc;
--order by age; --  age  

select id,username,age
from user
order by age asc,id asc; --        ， age,    id

select id,username,age
from user
where age>=30
order by age desc; --        

select rand(); --  (0-1)   

--      
select id,username,age
from user
order by rand(); --          

limit   --           
limit offset,row_count -- offset  ，  row_count   

--  
--   
select id,username,age,sex from user
limit 5;

--   
select id,username,age,sex from user
limit 1,5; --offset 0  ，         
--  
+----+----------+-----+-----+
| id | username | age | sex |
+----+----------+-----+-----+
|  2 | queen    |  27 |    |
|  3 | imooc    |  31 |    |
|  4 |        |  38 |    |
|  5 |       |  38 |    |
|  6 |        |  25 |    |
+----+----------+-----+-----+

--1.  user        ， age 5
update user set age=age+5 limit 3;
--2. user  id      ，       ， age 10
update user set age=age-10 order by id desc limit 3;
--3.  user        
delete from user limit 3;
--4.  user  id            
delete from user order by id desc limit 3;
--update  delete limit         

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;