MySQL常用操作（查询操作1）--DAY_5

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

--  use self-database cms
-- select index from table_name

SELECT 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 list

 SELECT username FROM cms_admin As a;
 SELECT a.username,a.id FROM cms_admin As a;



-- rename INDEX


SELECT 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 sex

ALTER 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;




-- COUNT

SELECT 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;