为了方便，所有注释我均用英文代替
其中 index代表字段，如cms数据库内，命名为cms_user的数据表包含字段（index）sex

--  use self-database cms
-- select index from table_nameSELECT id,username FROM cms_admin;
SELECT id,username,role FROM cms_admin;-- select * from database_name.table_name
SELECT username FROM cms.cms_admin;
-- select table_name.index, , from database_name.table_name
SELECT cms_admin.username FROM cms.cms_admin;-- rename listSELECT username FROM cms_admin As a;SELECT a.username,a.id FROM cms_admin As a;-- rename INDEXSELECT id AS i ,username as u,email as e from  cms_admin AS a;-- add extra index with select 
SELECT 1,2,3,4,5,id,username from cms.cms_admin as a;-- the way of WHERE 
SELECT username,email from cms_admin where id=1;
SELECT username,email from cms_admin where id>=5;
SELECT id,username,email from cms_admin where id!=5;alter TABLE cms_user ADD age TINYINT unsigned DEFAULT 18;
INSERT cms_user(username,password,regTime,proId,age)
VALUES('test1','test1',1231231,2,NULL);-- select null values
SELECT * from cms_user WHERE  age<=>NULL;
SELECT * from cms_user WHERE  age<=>18;
SELECT * from cms_user WHERE  age is NULL;
SELECT * from cms_user WHERE  age is NOT NULL;-- select regine (between and)
SELECT * from cms_user WHERE  id BETWEEN 3 and 10;
SELECT * from cms_user WHERE  id  NOT BETWEEN 3 and 10;
-- set type SELECT * from cms_user WHERE  id  in(1,3,4,5);
SELECT * from cms_user WHERE  id   not in(1,3,4,5);-- ignore the upper write 
SELECT * from cms_user WHERE  username   not in('king');
SELECT * from cms_user WHERE  username   in('KING');-- % 一个或多个任意字符
-- _代表一个任意字符
-- vague search （like）SELECT * from cms_user WHERE  username  like '%i%';SELECT * from cms_user WHERE  username  like '%';
SELECT * from cms_user WHERE  username  like '____';
SELECT * from cms_user WHERE  username  like '_i%';SELECT * from cms_user WHERE  username NOT like '_i%';-- group by
SELECT * from cms_user GROUP BY proId;-- add index sexALTER TABLE cms_user ADD sex enum('1','2','3') DEFAULT '3';
UPDATE cms_user SET sex='1' WHERE id in(1,2,3,4);
UPDATE cms_user SET sex='2' WHERE id in(5,6,7,8);
UPDATE cms_user SET sex='3' WHERE id in(9,10,11);SELECT * FROM cms_user GROUP by sex;-- acording the local num of index such as 7
-- the num 7 is the local of index sex 
-- we can  use 7 to replace sex index 
SELECT * FROM cms_user GROUP by 7;SELECT * FROM cms_user GROUP by sex,proId;SELECT * FROM cms_user WHERE id>=5 GROUP by sex;-- GROUP_CONCAT()  
-- 聚合函数查询id，sex，用户详情 按照性别分组
SELECT id,sex,GROUP_CONCAT(username) from cms_user GROUP BY sex;SELECT id,GROUP_CONCAT(regTime),GROUP_CONCAT(sex),GROUP_CONCAT(username) 
FROM cms_user GROUP BY proId;SELECT id,GROUP_CONCAT(regTime),GROUP_CONCAT(sex),GROUP_CONCAT(username)
FROM cms_user GROUP BY proId\G;UPDATE cms_user SET age=12 WHERE id=1;
UPDATE cms_user SET age=13 WHERE id=2;
UPDATE cms_user SET age=15 WHERE id=3;
UPDATE cms_user SET age=14 WHERE id=4;
UPDATE cms_user SET age=21 WHERE id=5;
UPDATE cms_user SET age=31 WHERE id=6;
UPDATE cms_user SET age=1 WHERE id=7;
UPDATE cms_user SET age=2 WHERE id=8;-- COUNTSELECT id,sex, GROUP_CONCAT(username) AS users,
COUNT(*) AS totaluser FROM cms_user GROUP BY sex;SELECT COUNT(*) AS totaluser FROM cms_user GROUP BY sex;SELECT COUNT(age) AS totaluser FROM cms_user GROUP BY sex;-- max min avg sum 
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*)as total,
MAX(age) as max,
MIN(age) as min,
AVG(age) as avg,
SUM(age) as sum 
from cms_user
GROUP BY sex;-- WITH  ROLLUP
SELECT id,sex,GROUP_CONCAT(username),
COUNT(*)as total,
MAX(age) as max,
MIN(age) as min
from cms_user
GROUP BY sex WITH ROLLUP;-- second select having
-- if not use GROUP BY then HAVING will not valid though it doesn't error  
SELECT sex ,
MAX(age),
GROUP_CONCAT(username) as user,
COUNT(*) as total,
SUM(age) as sum
from cms_user group by sex;-- NUM lager than 2
SELECT sex ,
MAX(age),
GROUP_CONCAT(username) as user,
COUNT(*) as total,
SUM(age) as sum
from cms_user group by sex
HAVING COUNT(*)>2;SELECT sex ,
MAX(age),
GROUP_CONCAT(username) as user,
COUNT(*) as total,
SUM(age) as sum
from cms_user group by sex
HAVING COUNT(*)>2 AND MAX(age)>30;-- keep original order by select __  from
SELECT sex ,
MAX(age),
GROUP_CONCAT(username) as user,
COUNT(*) as total,
SUM(age) as sum
from cms_user HAVING COUNT(*)>2 AND MAX(age)>30;