(MySQL学习) 35.MySQL笔记（婷姐初级、周阳高级）

#开启关闭服务
#管理员启动cmd，，net start/stop mysql，，
#登录数据库，，mysql 【-h localhost -P 端口号】 -u 用户名  -p,,
#退出数据库，，exit,, / #ctrl+c
#查看版本号，，mysql -V,,
##常见语句
#查看当前所有数据库
SHOW DATABASES;
#打开指定的库，，use 库名；
#查看某库所有的表，，show tables； // show tables from 库名；
 /*创建表，， create table（
    字段名称 字段类型，
    字段名称 字段类型
    ）；*/
#查看表结构，，desc 表名；
#修改字符集 set names gbk;
#查看表内容，，select * from 表名；
#插入内容，，insert into 表名 （id ， name） values （1，‘yangbo’）；
#更新修改内容，，update  表名 set name='yb' where id=1;
#删除内容，，delete from 表名  where id=1;
##不区分大小写
##单行注释   #注释    -- 注释 
##多行注释  /* 注释 */
 ###data query language/ data manipulation lan/
### data define lan/transcation control lan/
###DQL数据查询语言
##进阶1 基础查询-----------------------------------------
# select  (字段、常量、表达式、函数） from  表名；
USE myemployees;
 SELECT
  job_id          #F12变规范
FROM
  jobs;
  
SELECT
  `manager_id`,  #`` 区分系统sql关键字和字段名
  `first_name`,
  #逗号自己加
   `email`
FROM
  `employees`;

SELECT * FROM employees;  

SELECT    1+4;

#查询函数
SELECT VERSION();

#起别名alias  便于理解 ，防止重名
SELECT last_name AS 姓 , first_name AS 名 FROM employees;
SELECT last_name 姓 FROM employees ;  # 无AS
SELECT last_name 'x  #i ng' FROM employees;

#去重
SELECT  DISTINCT department_id FROM employees;

# + 号的作用
SELECT 10+10;   #==20
SELECT "10"+10; #==20
SELECT '1o'+10; #==11
SELECT 'oo'+10; #==10
SELECT NULL+10; #==null

#拼接文本，判断是否为null
SELECT CONCAT(last_name,first_name)  AS 姓名 FROM employees;
SELECT CONCAT(`first_name`,`job_id`,IFNULL(`commission_pct`,0)) AS infor 
FROM employees;

##进阶2 条件查询--------------------------------------------
 /*
select 查询列表  #3
from 表名        #1
where 条件       #2

条件表达 <  >  =  !=  <>  <=  >=
#逻辑表达  &&  ||  !   连接多条件表达
#          and  or  not 
#模糊查询  
    like   
    between  and
    in 
    is null  
*/
USE `myemployees`;

SELECT
  *
FROM
  employees
WHERE salary > 12000;

 

SELECT
  last_name,
  `department_id`
FROM
  `employees`
WHERE `department_id` <> 90;


SELECT 
    last_name,
    salary
FROM 
    employees
WHERE
    salary>=10000 AND salary<=20000;

#查询部门编号不是在90-120，或者工资大于15000的员工信息
SELECT *
FROM  employees
#where not(`department_id`>=90 and `department_id`<=120) or salary>15000;
#where department_id not between 90 and 15000 or salary>15000;
WHERE NOT(department_id BETWEEN 90 AND 15000) OR salary>15000;


#模糊查询 通配符
# % 表示任意多个字符  _ 表示1个字符 , %%不能代表null!!!!!!!!!!
SELECT *
FROM employees
WHERE last_name LIKE '%a%';  #姓包含a
SELECT *
FROM employees
WHERE last_name LIKE '_i%'; #查询第二个字符为i，注意%的使用
SELECT *
FROM `employees`
WHERE last_name LIKE '_\_%'; #查询第二个字符为下划线。\为转义符
SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$'; #escape指定转义符为$

#between A and B ===  >=A and <=B,所以 A<=B
SELECT * 
FROM employees
WHERE `employee_id` BETWEEN 100 AND 120; 

#in
/*
in === multiple or , 不支持通配符，因为or是=，不是like
*/
SELECT * 
FROM employees
WHERE `job_id` IN ('AD_VP','IT_PROG');
# where `job_id`='AD_VP' OR `job_id`='IT_PROG';


#is null  /  is not null
SELECT * 
FROM  employees
WHERE commission_pct IS NULL;

SELECT ISNULL(`commission_pct`),commission_pct  #判断为null=1，notnull=0
FROM `employees`;

#安全等于 <=>
SELECT * 
FROM employees
WHERE `job_id` <=> 'AD_VP';

SELECT * 
FROM employees
WHERE salary <=> 12000;

SELECT * 
FROM employees
WHERE commission_pct <=> NULL;


##进阶3 排序---------------------------------------------

#ORDER BY 默认asc ，降序，放在查询最后(除limit之外)
#工资从高到低
SELECT * FROM `employees` ORDER BY salary DESC;

#加筛选条件
#按表达式排序
#ifnull(字段，0)，字段值为null，赋值为0，不为null，为原值
SELECT salary, salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM  employees
ORDER BY salary*12*(1+IFNULL(`commission_pct`,0));

#按照别名排序
SELECT salary, salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM  employees
ORDER BY 年薪 DESC;  #说明order by 最后一步执行

#按函数排序
SELECT `last_name`,LENGTH(`last_name`)
FROM `employees`
ORDER BY LENGTH(`last_name`) DESC;

#多条件排序 员工信息 先按工资升序，后按 员工编号降序
SELECT *
FROM employees
ORDER BY salary ASC,`employee_id` DESC;


##进阶4 常见函数-------------------------------------------------

/*
函数分为单行和多行函数
单行比如length()，肯定有返回值
    1.字符函数（处理字符串）
多行函数，又称组函数，统计用
*/

#一、 字符函数##########################

#1.length，获取字符长度
SELECT LENGTH('莫默123aaa');
#UTF8中文为3个字节，GBK中文2个字节
SHOW VARIABLES LIKE '%char%';

#2.concat
SELECT CONCAT(`last_name`,'_',`first_name`) AS 姓名 FROM employees;

#3.upper lower 

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

#4.substr / substring
SELECT SUBSTR('李莫愁爱上了陆展元',7) AS output; #从第7个位置开始
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS output; #从第1个位置开始的三个字符

#大写首字母
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)))
FROM employees;

#5 instr
#返回substr在str中第一次出现的索引，若不匹配，返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS output;
#substr和instr连用，取邮箱用户名
SELECT SUBSTR(email,1,INSTR(email,'@')-1) FROM ..;
#6. trim 
#去掉首位空格或指定str
 SELECT TRIM('  莫默  ') AS output;
SELECT TRIM('a' FROM 'aa默aa') AS output;

#7. lpad
#左填充str为10个字符长，用padstr填充
SELECT LPAD('莫默',10,'*') AS output;

#8. rpad
SELECT RPAD('y',5,'-') AS output; 

#9 replace
# 在str中，from_str被to_str替代
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS output;

#二、数学函数############################

SELECT ROUND(1.4);
SELECT ROUND(1.459,2);

SELECT CEIL(1.01); # 取右  取≥该参数的最小整数

SELECT FLOOR(-1.09); #往数轴 取左

SELECT TRUNCATE(1.123456,4); #取小数点前4位

#取余
SELECT MOD(10,3);
SELECT MOD(-10,3);  #结果和被除数正负一致
SELECT 10%3; 

#出一个随机数，范围0-1
SELECT RAND(0123456);
#三、日期函数##############################
SELECT NOW();

SELECT CURDATE();


SELECT CURTIME();
#获得指定的部分 year年 month月 date日 hour小时 minute分 second秒
SELECT YEAR(NOW());
SELECT YEAR('1994-09-10');
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW()); #返回英文月

#日期格式的字符  转换成指定的格式
#%Y1994 %y94 %m01 %c1 %d30 %H24 %h12 %i00 59 %s00 59
SELECT MONTHNAME( STR_TO_DATE('1994-4-2','%Y-%c-%d') )output ;
#           日期型字符    该字符的格式
/*
#查询入职日期为1992-4-3的员工信息
select * from employees where hiredate = '1992-4-3'; 
若日期型字符格式为04-03 92
select * from employees where hiredate = str_to_date('04-03 1992','%m-%d %Y');
*/
#时间格式的转换为特定格式的字符串
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') 日期;
#查询有奖金的员工的员工名和入职日期，要求该日期格式输出为xx月/xx日 xxxx年
 SELECT
  last_name 姓名,
  DATE_FORMAT (hiredate, '%m月/%d日 %Y年') 入职日期
FROM
  employees
WHERE commission_pct IS NOT NULL;

#求日期之差
SELECT DATEDIFF(CURDATE(),'1994-01-21');


#四、 其他函数###################################
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
SELECT PASSWORD('yb'); #加密
SELECT MD5('yb');    #加密
#五、流程控制函数###################################
#if
SELECT IF(10>5,1,0); #和excel的if一样
SELECT IF(salary<20000,IF(salary<10000,'small','media'),'high') 
    grade #nesting 嵌套
FROM employees;

SELECT last_name, commission_pct,
 IF(commission_pct IS NULL,'呵呵','哈哈')   备注
FROM employees;

#case多条件判断，可以和select搭配，也可单独使用
/*
case 常量
when 值1 then 显示的值1
when 值2 then 显示的值2
。。。
else 要显示的值n
end
*/
#在部门30的，工资×1.1
#在部门40的，工资×1.2
#在部门50的，工资×1.3
#其他部门，原工资
SELECT `department_id`, salary, 
CASE `department_id`
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END 
AS 新工资
FROM employees; 
#excel嵌套大法
SELECT department_id,salary,
IF(department_id=30,salary*1.1,
IF(department_id=40,salary*1.2,
IF(department_id=50,salary*1.3,salary)))AS 新工资 
FROM employees;

/* 多重if
case 
when 条件1 then 显示的值1
when 条件2 then 显示的值2
。。。
else 要显示的值n
end
*/
#工资<10000,small;10000<=工资<20000,media;20000<工资，high
SELECT salary,
CASE
WHEN salary>20000 THEN 'high'
WHEN salary>10000 THEN 'media'
ELSE 'small'
END
AS grade 
FROM employees;
#excel嵌套大法
SELECT salary,IF(salary<20000,IF(salary<10000,'small','media'),'high') 
                    grade #nesting 嵌套
FROM employees;

##二、统计函数

#sum avg max min count
#sum avg只能处理数字型，max 、min、count可以字符型
#全部忽略null值
#可以搭配distinct，去重
#和统计函数一同使用的时group by 后的字段。☆☆☆☆☆

SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary)
FROM employees;

SELECT SUM(salary),ROUND(AVG(salary),2),MAX(salary),MIN(salary),COUNT(salary)
FROM employees;

SELECT SUM(`commission_pct`),MAX(hiredate)
FROM employees;

SELECT SUM(DISTINCT salary), COUNT(DISTINCT `commission_pct`),
    SUM(salary),COUNT(`commission_pct`)
FROM `employees`;

#count单独使用
SELECT COUNT(*) FROM `employees`;#☆☆☆
SELECT COUNT(1) FROM `employees`;

#进阶5 分组查询 ------------------------------------------------
/*
select 统计函数，字段
from 表名
【where 包含表名字段的条件】  #不可使用别名
group by 字段   #可使用别名
【having 包含统计函数的字段】  #可使用别名
【order by 字段】 #可使用别名

*/
#简单分组查询：每个部门平均工资
SELECT AVG(salary),`department_id`
FROM employees
GROUP BY `department_id`;
#前筛选--分组查询：邮箱中包含a字符的，每个部门平均工资，
#where要跟在from 后面一行
SELECT AVG(salary),department_id,email
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#有奖金的每个领导手下的员工最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
#后筛选--分组查询：领导编号>102的每个领导手下员工的最低工资＞5000的领导编号
#，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102           #优先考虑前筛选☆☆☆☆☆
GROUP BY manager_id
HAVING MIN(salary)>5000;
#按函数--分组查询：按员工姓名长度分组，查询每组个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#多字段--分组查询
#每个部门每个工种的平均员工工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
#排序--分组查询
#每个部门每个工种的员工平均工资，按照平均工资大小降序
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

#进阶6 多表连接查询-----------------------------------------------
#当查询的字段来自多个表
#分类：内连接：等值连接、非等值连接、自连接
#      外连接：左外连接、右外连接、全外连接
#      交叉连接

##92版 内连接    
USE girls;
SELECT NAME,boyName FROM beauty ,boys
WHERE beauty.`boyfriend_id`=boys.`id`;

#查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees , departments
WHERE employees.`department_id`=departments.`department_id`;
#为表名起别名，且from先执行，所以select 里面的job.id有歧义，只能用别名去标明
#查询员工名、工种号、工种名 #
#SELECT last_name,employees.job_id,job_title
SELECT last_name,e.job_id,job_title
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`;


#筛选--内连接
#有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#查询城市名中第二个字符为o的部门名和城市名
EXPLAIN SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

#分组-内连接
#查询每个城市的部门个数
SELECT COUNT(*), city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`;
#查询有奖金的每个部门的部门名和部门领导编号，以及该部门最低工资
SELECT department_name,e.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL;

#三表连接
#查询员工名、部门名、所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

#非等值连接
#查询员工工资和工资级别
SELECT salary, grade_level
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#自连接
#查询员工名和上级名称
SELECT a.last_name,b.last_name
FROM employees a,employees b
WHERE a.`manager_id`=b.`employee_id`;

#99版内连接
#查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees e 
JOIN departments d
ON e.`department_id`=d.`department_id`;

#为表名起别名，且from先执行，所以select 里面的job.id有歧义，只能用别名去标明
#查询员工名、工种号、工种名 #
SELECT last_name,e.job_id,job_title
FROM employees e
JOIN jobs j
ON e.`job_id`=j.`job_id`;

#筛选--内连接
#有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`commission_pct` IS NOT NULL;


#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE l.`city` LIKE '%o%';

#分组-内连接
#查询每个城市的部门个数
SELECT COUNT(*),city
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;

#查询有奖金的每个部门的部门名和部门领导编号，以及该部门最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_name`;



#三表连接
#查询员工名、部门名、所在城市
SELECT last_name,department_name,city
FROM departments d
JOIN  employees e ON e.`department_id`=d.`department_id`
JOIN locations l ON d.`location_id`=l.`location_id`;

#非等值连接
#查询员工工资和工资级别
SELECT salary, grade_level
FROM employees e
JOIN job_grades j 
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#自连接
#查询员工名和上级名称
SELECT a.last_name,b.last_name
FROM employees a
JOIN employees b ON  a.`manager_id`=b.`employee_id`;

SELECT department_name,COUNT(*) 员工个数
FROM departments d INNER 
JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY d.`department_name`
HAVING COUNT(*)>3
ORDER BY  员工个数 DESC;

#左外连接#查询非交集（内连接查询的即为交集）
SELECT b.*, bo.boyName
FROM beauty b       #主表
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName` IS  NULL;
#右外连接
SELECT bo.boyName,b.*
FROM boys bo
RIGHT JOIN beauty b
ON bo.`id`=b.`boyfriend_id`
WHERE bo.`boyName` IS  NULL;
#-----
SELECT bo.*,b.name
FROM boys bo
LEFT JOIN beauty b
ON bo.`id`=b.`boyfriend_id`;

#交叉连接(笛卡尔乘积)
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
SELECT b.*,bo.*
FROM beauty b,boys bo;
####7种join大法
#左拼
SELECT * FROM a LEFT JOIN b ON a.id=b.id;
#左拼左独有
SELECT * FROM a LEFT JOIN b ON a.id=b.id WHERE b.id IS NULL;

#右拼
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;
#右拼右独有
SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE b.id IS NULL;

#内连接
SELECT * FROM a INNER JOIN b ON a.id=b.id ;

##全外连接(mysql不支持全外，使用union连接去重的特性，实现full join)
SELECT * FROM a LEFT  JOIN b ON a.id=b.id
UNION 
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;

##左独有  拼  右独有
SELECT * FROM a LEFT JOIN b ON a.id=b.id WHERE b.id IS NULL;
UNION
SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE b.id IS NULL;


#习题 那个城市没有部门
SELECT l.city ,d.department_name
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_name` IS NULL;

#习题 查询部门名为SAL/IT的员工信息
SELECT d.department_name,e.*
FROM departments d
LEFT JOIN employees e  #注意主表不同，结果不同（把lfet换成right，39行）
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN( 'SAL' ,'IT');

##进阶7 子查询---------------------------------------
#出现在其他语句的select查询语句为子查询
#子查询出现的位置：    select后面
#               仅支持标量子查询
#            from后面
#               仅支持表子查询（多行多列）
#            where后面（**）/having后面（**）
#               标量子查询（一行一列）√
#               列子查询（多行一列）  √
#               行子查询（一行多列）
#            exists后面
#               表子查询
/*
#where和having后面：标量、列、行子查询
特点： 子查询放在小括号内
    一般放在条件的右侧
    标量子查询一般搭配单行操作符使用：> < >= <= = <>
    列子查询一般搭配多行操作符使用 in any/some all
    查询过程中，子查询优先执行
*/
##标量子查询
#案例1 谁的工资比Abel高？
SELECT *
FROM employees
WHERE salary>( SELECT salary FROM employees WHERE last_name='Abel');
#案例2 返回job_id和141号员工相同，salary比143号员工高的员工信息
SELECT * 
FROM employees
WHERE job_id = 
    (SELECT job_id FROM employees WHERE employee_id=141)
AND   salary>
    (SELECT salary FROM employees WHERE employee_id = 143);
#案例3 返回工资最少的员工的信息
SELECT * 
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees );
#案例4 查询最低工资大于50号部门的最低工资的部门name和其最低工资（外加多表查询）
SELECT MIN (salary), d.department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.department_name
HAVING MIN (salary) >
  (SELECT
    MIN (salary)
  FROM
    employees
  WHERE department_id = 50);
##列子查询
#案例1 返回location_id是1400或者1700的部门中所有员工姓名
SELECT *
FROM employees
WHERE department_id IN (
 SELECT DISTINCT department_id 
 FROM departments
 WHERE location_id IN(1400,1700)
);
SELECT *
FROM employees
WHERE department_id =ANY (
 SELECT DISTINCT department_id 
 FROM departments
 WHERE location_id IN(1400,1700)
);
# is not in == <>all
# is in == =any
#返回其他工种中比job_id为'IT-PROG'工种任一工资低的员工号、姓名、jobid、salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
    SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
    SELECT DISTINCT MAX(salary) FROM employees WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
#返回其他工种中比job_id为'IT-PROG'工种*所有*工资低的员工号、姓名、jobid、salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
    SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
    SELECT DISTINCT MIN(salary) FROM employees WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
##3、行子查询
#查询员工编号最小，工资最高的员工信息

SELECT * FROM employees
WHERE employee_id = (SELECT MIN(employee_id) FROM employees)
AND  salary = (SELECT MAX(salary) FROM employees);

SELECT * FROM employees e
WHERE (e.`employee_id` , e.`salary`) =(
    SELECT MIN(employee_id) ,MAX(salary) FROM employees);


#二、select后面子查询,**仅支持标量子查询
#每个部门的员工个数
SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`) N
FROM departments d;
SELECT d.* ,COUNT(*) N
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
GROUP BY e.`department_id`;
#员工号102的部门名
SELECT (SELECT d.department_name
    FROM employees e,departments d
    WHERE e.department_id=d.department_id
    AND e.employee_id=102) N
;
##from 后面跟表子查询,表子查询必须要起别名
#案例：每个部门的平均工资的工资等级
SELECT a.avge,a.department_id, j.`grade_level`
FROM (
    SELECT AVG(salary) avge,department_id
    FROM employees
    GROUP BY department_id
) a
, job_grades j
WHERE a.avge BETWEEN j.`lowest_sal` AND j.`highest_sal`;
SELECT a.*, j.`grade_level`
FROM (
    SELECT AVG(salary) avge,department_id
    FROM employees
    GROUP BY department_id
) a
JOIN job_grades j
ON a.avge BETWEEN j.`lowest_sal` AND j.`highest_sal`;

##exists (相关子查询)
SELECT EXISTS(
    SELECT salary FROM employees WHERE salary = 23000
);
#返回布尔向量，0或1
#有员工的部门名
SELECT d.department_name
FROM departments d
WHERE d.department_id IN (SELECT e.department_id FROM employees e);

SELECT d.department_name
FROM departments d
WHERE EXISTS 
(SELECT * FROM employees e WHERE e.`department_id`=d.`department_id`);

(SELECT
  e.*
FROM
  employees e ,departments d
WHERE e.`department_id` = d.`department_id`);
#案例2 没有女朋友的男神信息
USE girls;
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS (
    SELECT * FROM beauty b WHERE b.`boyfriend_id`=bo.`id`
);
##习题：查询各部门中工资比本部门平均工资高的员工信息
SELECT e.* 
FROM employees e,(
    SELECT AVG(salary) av,department_id 
    FROM employees 
    GROUP BY department_id
)a
WHERE e.`department_id`=a.department_id
AND e.`salary`>a.av;


SELECT e.* ,e.`department_id` FROM employees e 
JOIN (
    SELECT AVG(salary) av,department_id 
    FROM employees 
    GROUP BY department_id
)a
ON e.`department_id`=a.department_id
WHERE e.`salary`>a.av;

##进阶8 分页查询--------------------------------------------
#伪代码
#    select 查询列表
#    from 表名
#    【join type
#      where group by  having  order by 】
#    limit 【起始页=0】，size
#公式：******limit (page-1)*size,size*********
#*******************查询虚拟表的极值order by xx desc limit 1*******************
#案例1 查询前5条员工信息
SELECT * FROM employees LIMIT 0,5; #从0开始！！！！！！，substr是1开始
SELECT * FROM employees LIMIT 5;
#案例2 有奖金的员工信息，工资较高的前10名
SELECT * 
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0,10;
#执行顺序
/*
select 查询列表        7
from 表名        1
连接类型 join 表2    2 #产生一个笛卡尔乘积
on 连接条件        3 #根据连接条件筛选
where 筛选条件        4
group by 分组列表    5
having 分组后筛选    6
order by 排序列表    8
limit 偏移，条目数    9
*/

#子查询经典习题
#案例1 查询平均工资最低的部门信息
SELECT d.*
FROM departments d
JOIN (
    SELECT AVG(salary) ag ,e.department_id 
    FROM employees e
    GROUP BY e.`department_id`
    ORDER BY a.ag
    LIMIT 0,1
) a
ON d.`department_id`=a.department_id
;
#平均工资最高的job信息
SELECT j.* 
FROM jobs j,(
    SELECT AVG(salary) av,job_id
    FROM employees 
    GROUP BY job_id
    ORDER BY av DESC
    LIMIT 1
) a
WHERE  j.`job_id`=a.job_id;

#查询平均工资高于公司平均工资的部门
SELECT a.department_id,a.av
FROM (
    SELECT AVG(salary) av,department_id
    FROM employees
    GROUP BY department_id
) a 
WHERE a.av>(
    SELECT AVG(salary) FROM employees
);
#查询公司中所有manager的详细信息
SELECT b.*
FROM employees a
JOIN employees b
ON  a.`manager_id`=b.`employee_id`
GROUP BY b.`employee_id`;

SELECT *
FROM employees
WHERE employee_id = ANY(
    SELECT DISTINCT manager_id
    FROM employees
    GROUP BY manager_id
);
#各部门中，最高工资最低的那个部门 的最低工资为
SELECT MIN(salary)
FROM employees 
WHERE department_id =(
    SELECT department_id
    FROM employees a 
    GROUP BY department_id
    ORDER BY MAX(salary) 
    LIMIT 1)
;
#查询平均工资最高的部门的manager信息
#where 标量子查询结果为null时，如何判断
SELECT *
FROM employees e
RIGHT JOIN (
    SELECT manager_id
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) DESC
    LIMIT 1
) a
ON IFNULL(e.manager_id,1) = IFNULL(a.manager_id,1) ;

SELECT
  *
FROM
  employees e
WHERE IFNULL (e.manager_id, 1) = IFNULL (
    (SELECT
      manager_id
    FROM
      employees
    GROUP BY department_id
    ORDER BY AVG (salary) DESC
    LIMIT 1),1
  );

#查询生日在1988-1-1后的学生姓名和专业名称
USE student;
SELECT  s.`studentname`,m.`majorname`
FROM student s
JOIN major m
ON s.`majorid`=m.`majorid`
WHERE DATEDIFF(s.`borndate`,'1988-1-1')>0;
#每个专业的男生女生人数
SELECT m.`majorname`, sex,COUNT(*)
FROM student s,major m
WHERE s.`majorid`=m.`majorid`
GROUP BY s.majorid , sex;
#变横行
SELECT majorid ,
(SELECT COUNT(*) FROM student WHERE sex="男" AND majorid=s.`majorid`) 男,
(SELECT COUNT(*) FROM student WHERE sex="女" AND majorid=s.`majorid`) 女
FROM student s
GROUP BY majorid;
#查询专业和张翠山一样的学生的最低分
SELECT MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE majorid=(
    SELECT  majorid
    FROM student
    WHERE studentname = '张翠山'
);
#查询哪个专业没有学生，分别左连接，右连接
SELECT m.*
FROM major m
LEFT JOIN (
    SELECT COUNT(*) AS a, majorid
    FROM student
    GROUP BY majorid
) n
ON m.`majorid`=n.majorid
WHERE n.a IS NULL;

#没有成绩的学生人数
SELECT COUNT(*)
FROM result r
RIGHT JOIN student s
ON r.`studentno`=s.`studentno`
WHERE r.`id` IS NULL;

##进阶9 联合查询-----------------------------------------------
/* 语法：查询语句1
    union 【all】
    查询语句2
特点：
查询结果来自多个表，且各表之间无关键列索引
1、查询列数一致
2、每个查询语句的列的排列一致
3、union会去重，显示全部为union all
*/
SELECT c.id ,c.name FROM  china c
UNION ALL
SELECT s.id ,s.name FROM stuinfo s;

###DML语言 database manipulate language
#    插入insert
#    修改update
#    删除delete

#插入语句
#方式一：insert into 表名(列名) values(值，'值')

USE girls;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'安妮·海瑟薇','女','1980-1-1','188888888',NULL,NULL);

#变量nullable,用null作为该字段值，或者不写该字段
INSERT INTO beauty(id,NAME,sex,borndate,phone)
VALUES(14,'三上悠亚','女','1988-1-1','288888888');

#列的顺序可以调换
#字段个数和字段值必须一一对应
#可以省略列名，默认为所有字段，且按照表中顺序排列
INSERT INTO beauty
VALUES(15,'妲露拉·莱莉','女','1982-1-1','388888888',NULL,NULL);

#方式二：insert into 表名 set 字段名=值...
INSERT INTO beauty 
SET id=16,NAME='刘涛',phone='12345678';

#两种方式 PK

#方式一可以插入多行
INSERT INTO beauty(id,NAME,sex,borndate,phone)
VALUES(17,'高圆圆','女','1988-10-10','488888888'),
(18,'白百何','女','1989-01-10','588888888');

INSERT INTO beauty #效率高**********
SELECT 17,'高圆圆','女','1988-10-10','488888888' UNION#*****
SELECT 18,'白百何','女','1989-01-10','588888888';

#方式一支持子查询#行子查询的结果为beauty的新值
INSERT INTO beauty(id,NAME,phone)
SELECT 19,'唐艺昕','688888888'; 

#修改单表记录
#语法 update 表名 set 字段名=值.... where 筛选条件
UPDATE beauty 
SET NAME='唐嫣'
WHERE id=15;

#修改多表记录
#修改张无忌的女朋友电话为18866669999
UPDATE beauty b JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
SET phone = '18866669999'
WHERE bo.`boyName`='张无忌';
#没有男朋友的女神的男朋友都为id=2
UPDATE beauty b LEFT JOIN boys bo 
ON b.`boyfriend_id`=bo.`id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

#删除语句
#方式一
#单表删除 ,LIMIT 2 b表示删除两条记录
DELETE FROM beauty WHERE id=19 LIMIT 2;

#多表删除
#删除黄晓明的信息以及他女朋友的信息
DELETE bo,b
FROM boys bo JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

#方式二
TRUNCATE TABLE boys;

#两种方式PK 筛 率 断 返 滚
/*
1.delete可以加where，truncate不能
2.truncate效率高
3.对于自增列，delete删除后在插入，从断点处记录，
  truncate删除后从1 开始记录
4.truncate无返回值，delete显示删除了多少行（多少行受影响）
5.truncate删除不能回滚，delete删除可以回滚
*/
SELECT * FROM boys;

DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys(boyName,userCP)
VALUES('张无忌',100),
('鹿晗',800),
('段誉',300);


###DDL数据定义语言
#库的管理，表的管理
#库、表的创建create、修改alter，管理drop
#位置 programdata/mysql/data

##库的管理
#库的创建
CREATE DATABASE IF NOT EXISTS books;
#库的修改
RENAME DATABASE books TO  newbooks;#不稳定
#修改字符集
ALTER DATABASE books CHARACTER SET gbk;
#库的删除
DROP DATABASE IF EXISTS books;

##表的管理
#表的创建
#create table 表名(
#    列名 类型 【长度，约束】,
#    列名 类型 【长度，约束】
#    );
CREATE DATABASE IF NOT EXISTS books;

CREATE TABLE  IF NOT EXISTS book (
    id INT,
    bookname VARCHAR(20), #(20)为必须
    booauthorid INT,
    publishtime DATETIME
);
CREATE TABLE author(
    id INT,
    b_name VARCHAR(20),
    nation VARCHAR(20)
)
DESC author;
DESCRIBE author;

#表的修改
DESCRIBE book;
#修改列名
ALTER TABLE book CHANGE COLUMN publishtime pubdate DATETIME;
#修改类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE ;
ALTER TABLE stuinfo ADD COLUMN sex CHAR AFTER id;
ALTER TABLE stuinfo ADD COLUMN fam_id INT FIRST;
#删除列
ALTER TABLE book DROP COLUMN annual;
#修改表名
ALTER TABLE author RENAME bookauthor;

##表的删除
DROP TABLE IF EXISTS bookauthor;
SHOW TABLES;

##表的复制
CREATE TABLE author(
    id INT,
    b_name VARCHAR(20),
    nation VARCHAR(20)
);
INSERT INTO author
VALUES
    (1,'村上春树','日本'),
    (2,'路遥','中国'),
    (3,'王小波','中国');

#仅复制表的结构,跨库：库名.表名
CREATE TABLE copy LIKE author;
#复制表的内容＋结构
CREATE TABLE copy1 
SELECT * FROM author; 
#只复制部分数据
CREATE TABLE copy2
SELECT id ,nation
FROM author WHERE nation='中国';
#仅复制某些字段的结构
CREATE TABLE copy3 
SELECT id ,nation
FROM author
WHERE 0; #where 1=2;

###常见数值类型
/*
数值型：整数、小数（定点数、浮点数）
字符型：较短文本char、varchar，
    较长文本text、blob（较长二进制）
日期型：
*/
##数值型：
/*整数：tinyint(3)、smallint(5)、mediumint(7)、int(10)、bigint
    int有符号为正负最大10位，27********，无符号为最大正10位，49********
特点：  1。默认为有符号（-），unsgined关键字表示无符号
    2。数字超过界值，警告，且用界值填充
    3。有默认长度，长度表示显示的最大宽度，位数不够用0填补，但必须搭配zerofill
    4。zerofill只能为无符号
*/
USE test;
DROP TABLE  tab_int;
CREATE TABLE tab_int(
    integer1 INT,
    integer2 INT UNSIGNED,
    integer3 INT(10) ZEROFILL
); 

INSERT INTO tab_int VALUES (-1234567890,1234567890,123);
INSERT INTO tab_int VALUES (-1234567890,12345678901,123);
DESC tab_int;
SELECT * FROM tab_int;
/*小数：
浮点型float(M,D)、double(M,D)
定点型dec(M,D)/decimal(M,D)
通用的情况下，优先float>double>decimal
特点： 1。M表示整数+小数位数
    2.D表示小数位数
    3.如果数值位数超过范围，则插入临界值
    4.M D都可以省略，float和double随数据长度变化，但默认decimal(10,0)
    5.decimal精度高，用于如货币运算
*/
CREATE TABLE tab_dem(
    d1 FLOAT(5,2),
    d2 DOUBLE(5,3),
    d3 DECIMAL
);
DROP TABLE tab_dem;
INSERT INTO tab_dem(d1) VALUES(123.12);
INSERT INTO tab_dem(d2) VALUES(123.1);

SELECT * FROM tab_dem;
DESC tab_dem;

##文本类型
#较短文本 char varchar （二进制binary varbinary）
#较长文本 text blob（二进制）

#特点
/*
    写法        M                特点        
char    char(M)        最大字符数，可以省略，默认1    固定长度    
varchar varchar(M)    最大字符数，不接省略        可变长度

            空间的耗费    效率
            比较耗费    较高
            比较节省    较低


*/
#枚举 enum
CREATE TABLE tab_enum(
    e1 ENUM('a','b','c')
);
DESC tab_enum;
INSERT INTO tab_enum VALUES
    ('a'),    #不区分大小写
    ('c');  #不在列举范围，则插入值为空
SELECT * FROM tab_enum;

#集合 set 
CREATE TABLE tab_set(
    s1 SET('a','b','c')
);
INSERT INTO tab_set VALUES
    ('a'),   #不区分大小写
    ('a,c'); #可加两个
SELECT * FROM tab_set;

##日期类型
/*分类：
date日期
time时间
year年
        字节    范围        时区
datetime    8    1000-9999    不受时区影响
timestamp    4    1970-2038    受影响

*/
#datetime和timestamp(受时区，语法模式、版本影响，但更能反映当前时区的真实时间)
CREATE TABLE tab_date(
    d1 DATETIME,
    d2 TIMESTAMP
);
INSERT INTO tab_date 
VALUES    (NOW(),NOW());
SELECT * FROM tab_date;
#system时间为+8:00东八区
SHOW VARIABLES LIKE 'time_zone';

SET time_zone = '+9:00';
SELECT DATE(NOW());
##常见约束
/*
分类：六大约束
    1，not null 保证该字段不为空
        如ID、姓名
    2，default默认 保证该字段有默认值
        如
    3，primary key主键 用于保证该字段有唯一性，且非空
        比如学号、编号
    4，unique唯一键 用于保证该字段具有唯一性，可以为空
        比如座位号
    5，check【mysql不支持】 
        比如0<年龄<99
    6，foreign key外键 用于限制两表关联列在主表中该列的值
        比如：employees.department_id in departments.department_id
        
在创建表和修改表时，添加约束

分类：    列级约束 ：（除外键）六大常见约束都支持，但是check无效果、foreign key无效果
    表级约束 ：（三键）除了非空、默认都支持    
    
主键和唯一键的PK
        唯一性    是否允许为空    一个表有几个    是否允许组合
    主键    √    ×        最多一个    √，但是不推荐
      唯一键    √    √(只能有一个空)    可以有多个    √，但是不推荐
      
  组合的意思是，让ID和name的组合如：ID_name唯一且不为空    
  
  外键：1，要求在从表(stinf)设置外键
    2，从表(stinf)的外键列要求和主表(major)的类型一致或兼容
    3，主表(major)的关联列必须是一个key (一般为主键)
    4，插入数据时，先插主表(major)，后从表(stinf)
    5，删除数据时，先删从表(stinf)，后主表(major)***级联删除和级联置空

*/

#列级约束

CREATE DATABASE constrain;
CREATE TABLE stinf(
    id INT PRIMARY KEY, #主键
    stuname VARCHAR(20) NOT NULL UNIQUE,#非空 & 唯一键
    gender CHAR(1) CHECK(gender='男' OR  gender='女'),#核查
    seat INT UNIQUE,#唯一键
    age INT DEFAULT 18,#默认
    majorid INT REFERENCES major(id)#外键
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    mname VARCHAR(20) 
);
DESC stinf;
SHOW INDEX FROM stinf; #查看主键、外键、唯一键

#表级约束
DROP TABLE IF EXISTS stinf;
CREATE TABLE IF NOT EXISTS stinf(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uq UNIQUE(seat),#唯一键
    CONSTRAINT ck CHECK(gender IN ('男','女')),#核查
    CONSTRAINT fk_stinf_major FOREIGN KEY(majorID) 
                    REFERENCES major(id)#外键
    #constriant用来给键起名字
);
SHOW INDEX FROM stinf; #查看主键、外键、唯一键
DROP TABLE IF EXISTS stinf;

CREATE TABLE IF NOT EXISTS stinf(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
     PRIMARY KEY(id),#主键
     UNIQUE(seat),#唯一键
     CHECK(gender IN ('男','女')),#核查
     FOREIGN KEY(majorID) REFERENCES major(id)#外键
    
);
SHOW INDEX FROM stinf; #查看主键、外键、唯一键
#通用
DROP TABLE IF EXISTS stinf;

CREATE TABLE IF NOT EXISTS stinf(
    id INT PRIMARY KEY, #主键
    stuname VARCHAR(20) NOT NULL,#非空
    gender CHAR(1) CHECK(gender='男' OR  gender='女'),#核查
    seat INT UNIQUE,#唯一键
    age INT DEFAULT 18,#默认
    majorid INT,
    CONSTRAINT fk_stinf_major FOREIGN KEY(majorid) 
                REFERENCES major(id)#外键
);
SHOW INDEX FROM stinf; #查看主键、外键、唯一键

##修改约束
/*


*/
DROP TABLE IF EXISTS stinf;

CREATE TABLE IF NOT EXISTS stinf(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1) NOT NULL,
    seat INT,
    age INT,
    majorid INT
);
#列级约束修改
ALTER TABLE stinf MODIFY COLUMN id INT PRIMARY KEY ;
ALTER TABLE stinf MODIFY COLUMN age INT DEFAULT 18;
DESC stinf;
SHOW INDEX FROM stinf;
#表级约束修改
#主键也可以这样去添加，但是不能添加constraint 限制名    
ALTER TABLE stinf ADD CONSTRAINT u_seat UNIQUE(seat);
ALTER TABLE stinf
  ADD CONSTRAINT fk_major_stinf FOREIGN KEY (majorid) REFERENCES major (id);
DESC stinf;
SHOW INDEX FROM stinf;

##修改表时删除约束
#1删非空约束
ALTER TABLE stinf MODIFY COLUMN gender CHAR(1);
#2删默认约束
ALTER TABLE stinf MODIFY COLUMN age INT;
#3删主键
ALTER TABLE stinf DROP PRIMARY KEY;
#4删唯一键
ALTER TABLE stinf DROP INDEX seat;
#5删除外键
ALTER TABLE stinf DROP FOREIGN KEY fk_major_stinf;
SHOW INDEX FROM FROM stinf;

##级联删除：关于从表有外键时，删除数据biubiu先删主表，级联可以先删从表
#级联删除
ALTER TABLE student ADD CONSTRAINT fk_stu_maj 
    FOREIGN KEY(majorid) REFERENCES major(majorid) ON DELETE CASCADE;
DELETE FROM major WHERE majorid=3;
#级联置空
ALTER TABLE student DROP FOREIGN KEY fk_stu_maj;
##无法设置成功！！？？
ALTER TABLE student ADD CONSTRAINT fk_stu_maj 
    FOREIGN KEY(majorid) REFERENCES major(majorid) ON DELETE SET NULL;
DELETE FROM major WHERE majorid=2;



#标识列
/*
1 必须搭配  键(只限mysql)
2 至多一个标识列
3 标识列类型只能是数值
4 步长设置通过SET auto_increment_increment = 2;
5 标识列初始值可通过手动添加

*/
DROP TABLE tab_auto;
CREATE TABLE tab_auto(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) 
);

INSERT INTO tab_auto(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_auto(NAME) VALUES('john');
INSERT INTO tab_auto VALUES(NULL,'john');
INSERT INTO tab_auto VALUES(5,'lily'); #相当于设置了起始值
INSERT INTO tab_auto VALUES(NULL,'john');

TRUNCATE TABLE tab_auto;

SELECT * FROM tab_auto;
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment = 2; #设置步长
SET auto_increment_offset = 3; #mysql无作用
#修改表时 添加删标识列
DROP TABLE tab_auto;
CREATE TABLE tab_auto(
    id INT,
    NAME VARCHAR(20) 
);
ALTER TABLE tab_auto MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE tab_auto MODIFY id INT;

##事务
/*分类：
    隐式事务：insert update delete
    显式事务：set autocommit=0;
          strat transaction;
          select insert update delete;
          ***** 无create alter drop ******
          commit;    /    rollback;
ACID
1 原子性atomicity：一个事务不可再分割，要么都执行要么都不执行
2 一致性consistency：一个事务执行会使数据从一个状态切换到另一个状态
3 隔离性isolation：一个事务的执行不受其他事务的影响
3 持久性durability：一个事务的提交会永久改变数据库的数据

三种数据问题：
    1脏读:T1 T2 ,T2更新没提交，T1读取，读取了临时无效的信息，若T2回滚
    2不可重复度：T1读取，T2更新,T1再读取则信息变化
    3幻读：T1读取，T2更新某些行，T1再读取，出现多行数据

事务隔离级别：
            脏读    不可重复读    幻读
read uncommitted    √    √        √
read committed        ×    √        √
repeatable read        ×    ×        √
serializable        ×    ×        ×

mysql默认repeatable read
Oracle默认read committed
*/
#查看当前隔离级别
SELECT @@tx_isolation;
#设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT @@tx_isolation;
#事务中delete 和truncate 的区别

SET autocommit=0;
START TRANSACTION;
DELETE FROM stuinfo;
ROLLBACK;
SELECT * FROM stuinfo;

SET autocommit=0;
START TRANSACTION;
TRUNCATE stuinfo;
ROLLBACK;
SELECT * FROM stuinfo;
COMMIT;

#演示savepoint的使用
INSERT INTO stuinfo VALUES(1,1,'s','yb');
INSERT INTO stuinfo VALUES(2,2,'x','lz');
SELECT * FROM stuinfo;
COMMIT;

SET autocommit=0;
START TRANSACTION;
DELETE FROM stuinfo WHERE id=1;
SAVEPOINT a;
DELETE FROM stuinfo WHERE id=2;
ROLLBACK TO a;
SELECT * FROM stuinfo;
COMMIT;
##视图
/*
含义：虚拟表，和普通表一样
mysql5.1出现的新特性，是通过表动态生成的数据

比如：一个班里面 分化出一个舞蹈班，有需要时，舞蹈班就可以直接出现

视图和表的PK：
1 创建语法不通 view  table
2 视图只占sql语句的字节，表占空间
3 视图一般不能增删改


*/

#查询各部门平均工资级别
USE myemployees;
CREATE VIEW myv1
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

SELECT v.ag ,j.`grade_level` FROM myv1 v
JOIN job_grades j
ON v.`ag` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#查询平均工资最低的部门信息
SELECT d.department_name, m.department_id, m.ag 
FROM myv1 m JOIN departments d
ON d.`department_id`=m.`department_id`;

#视图的修改
CREATE OR REPLACE VIEW myv1
AS
SELECT AVG(salary) AS average_salary,department_id
FROM employees
GROUP BY department_id;

ALTER VIEW myv1 
AS 
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

#视图的删除
CREATE  VIEW myv2
AS
SELECT department_id
FROM employees
GROUP BY department_id;

DROP VIEW myv1, myv2;
#视图的查看
DESC myv2;
SHOW CREATE VIEW myv2;
SHOW CREATE VIEW myv2\G; #在 DOS中使用

#通过视图增删改原始表
/*
在视图包含以下情况不能insert update delete包含以下sql语句：
1 分组函数、instinct、group by 、having 、union、union all
2 常量视图
3 select包含子查询
4 join
5 from一个不能更新的视图
6 where子句的子查询引用了from子句的表

*/
##变量
/*
系统变量：全局变量、会话变量
自定义变量：用户变量、局部变量
*/

#一、系统变量、会话变量（一个查询编辑器一个会话）
/*
变量由系统提供，不是用户定义，属于服务器层面

注意：如果是全局变量，则需要加global，如果是会话级别，则需要加session，默认session

使用语法：
1，查看所有变量：
show global|session variables;

2，查看满足条件的部分系统变量
show global|session variable like '%char%';

3，查看某个指定变量的值
select @@global|session.变量名;
select @@global.flush;

4，为某个变量赋值(跨连接有效，不能跨重启)
set @@global|session 变量名=值;
set   global|session 变量名=值;

*/
#自定义变量
/*
声明、赋值、使用（查看、比较、运算）

        作用域        定义和使用的位置         语法
用户变量    当前会话    会话的任何地方            必须加@，不限制类型
局部变量    begin end中    只能在begin end中的第一句话    不加@，限定类型
*/
#1，用户变量 ：  作用域：当前会话

#1 声明并初始化
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT  @用户变量名:=值;

#2 赋值（更新用户变量的值）
#方式一
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT  @用户变量名:=值;

#方式二
SELECT 字段 INTO @用户变量名
FROM 表名;

#使用
SELECT @用户变量名;

#案例
SET @count:=1;
SELECT COUNT(*) INTO @count FROM employees;
SELECT @count;

#2,局部变量
/*
作用域：只在定义它的begin 和 end 中有效

*/

#声明 ---------必须放在begin后面第一句----------------------
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;

#赋值
    #方式一
    SET 用户变量名=值;
    SET 用户变量名:=值;
    SELECT  @用户变量名:=值;

    #方式二
    SELECT 字段 INTO 用户变量名 FROM 表名;
    
#使用
SELECT 局部变量;

##存储过程和函数
/*
好处：提高代码的重用性、简化操作
*/

#存储过程
/*
定义：一组预先编译好的SQL语句集合


一、创建语法
create procedure 存储过程名(参数列表)
begin 
    存储过程体（一组合法的SQL语句）
end

注意：
1，参数列表包含三部分：
参数模式  参数名  参数类型 
in         id       int

参数模式：
in :改参数可以作为输入，也就是该参数需要调用方 传入值
out  : 该参数可以作为输出，也就是该参数可以作为返回值
inout : 该参数既可以作为输出，又可以作为输入，即既需要传入值，也能返回值

2，如果存储过程体只有一句话， begin 和 end 可以省略
 存储过程体每条sql均需要结尾加;
 存储过程的结尾可以使用 delimiter 重新设置
 *********delimiter在新连接中要去dos重新设置*************
 语法： delimiter 结束标记
 案例： delimiter $
 
 二、调用语法
 Call 存储过程名(实参列表);
 */
 ##空参列表
 #案例：给girls.admin添加5条数据
 
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
    INSERT INTO admin(username,PASSWORD)
    VALUES('yb1',123),('yb2',123),('yb3',123),('yb4',123),('yb5',23);
END $

CALL myp1()$
SELECT * FROM admin$

##in列表
#案例1：创建存储过程实现：根据女神名查询对应的男生信息

CREATE PROCEDURE myp2(IN beautyname VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo 
    LEFT JOIN beauty b ON b.boyfriend_id=bo.id
    WHERE b.name=beautyname ;
END $

CALL myp2('热巴')$

#出现 Incorrect string value: '\xC8\xC8\xB0\xCD' for column，表示字符集不匹配
SET NAMES gbk$ #(sqlyog默认utf8, dos默认gbk)

#案例2：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0; #声明
    SELECT COUNT(*) INTO result   #赋值
    FROM admin ad
    WHERE ad.username=username AND ad.password = PASSWORD;
    
    SELECT IF(result=0,'失败','成功') "结果";  #使用

END $

##out列表
#案例：创建存储过程实现：根据女神名返回对应的男生名
CREATE PROCEDURE myp4(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
    SELECT bo.boyname INTO boyname
    FROM boys bo  JOIN beauty b
    ON bo.id = b.boyfriend_id
    WHERE b.name=beautyname;
END $

CALL myp4('柳岩',@bname)$
SELECT @bname$

#案例：创建存储过程实现：根据女神名返回对应的男生名和魅力值
CREATE PROCEDURE myp5(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20),
            OUT userCP VARCHAR(20))
BEGIN
    SELECT bo.boyname ,bo.usercp INTO boyname ,usercp  #赋值
    FROM boys bo  JOIN beauty b
    ON bo.id = b.boyfriend_id
    WHERE b.name=beautyname;
END $
/*
set @name='a'$
set @charm='b'$                           省略了声明
*/
CALL myp5('柳岩',@name,@charm)$                   #相当于省去了声明
SELECT @name,@charm$                       #调用

##inout列表
#案例：返回a，b值的二倍
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
    SET a=a*2;  #赋值
    SET b=b*2;
END $
SET @aa=1$          #声明
SET @bb=2$
CALL myp6(@aa,@bb)$
SELECT @aa,@bb$     #调用

##课后案例
#存储过程实现传入用户名密码，插入admin
CREATE PROCEDURE p_insert(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
    INSERT INTO admin(username,PASSWORD) VALUES(username,PASSWORD);
END $
CALL p_insert('yb','122103')$
SELECT * FROM admin$
#传入女神标号，返回姓名电话
CREATE PROCEDURE p_inf(IN id INT,OUT NAME VARCHAR(50), OUT phone VARCHAR(11))
BEGIN
    SELECT b.name,b.phone INTO NAME,phone  #注意要赋值
    FROM beauty b
    WHERE b.id=id;
END $
CALL p_inf(2,@bname,@bphone)$   #省去声明
SELECT @bname ,@bphone$        #调用

#传入两个女生生日日期，然后返回大小
CREATE PROCEDURE myp7(IN date1 DATE,IN date2 DATE,OUT result INT)
BEGIN 
    SELECT DATEDIFF(date1,date2) INTO result;
END $
CALL myp7('1994-09-10',DATE(NOW()),@result)$
SELECT @result$

#传入一个日期类型的日期，输出为**年**月**日格式
CREATE PROCEDURE myp8(IN nor_date DATETIME,OUT for_date VARCHAR(20))
BEGIN
    SELECT DATE_FORMAT(nor_date,'%Y年%m月%d日') INTO for_date;
END $
CALL myp8(DATE(NOW()),@fd)$
SELECT @fd$

#输入 小昭，返回 小昭 and 张无忌
CREATE PROCEDURE myp9(IN beautyname VARCHAR(20),OUT information VARCHAR(50))
BEGIN 
    SELECT CONCAT(beautyname,'and',IFNULL(bo.boyname,'null'))  INTO information
    FROM boys bo ,beauty b
    WHERE b.boyfriend_id=bo.id
    AND b.name=beautyname;
END $
CALL myp9('柳岩',@inf)$
SELECT @inf$

#传入条目数和起始索引，查询beauty表记录,
CREATE PROCEDURE myp10(IN offsett INT,IN increment INT)
BEGIN 
    SELECT * FROM beauty
    LIMIT offsett,increment;
END$

CALL myp10(3,5)$#从第3条记录开始，显示5条


#删除储存过程
DROP PROCEDURE myp7; #只能一个一个的删
#查看储存过程
SHOW CREATE PROCEDURE myp6\G;#在dos执行 


##函数
/*
定义：一组预先编译好的SQL语句集合

区别：存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、更新
      函数：有且仅有1个返回值，适合做处理数据后返回一个结果
*/
/*
一、创建语法：
create function 函数名(参数列表) returns 返回类型
begin
    函数体;
    return ;
end $

注意：1参数列表 包含  （无参数模式） 参数名  参数类型
      2函数体肯定有return语句，如果没有会报错
      3函数体中仅有一句话，则可以省略begin和end
      4使用delimiter语句设置结束标记

二、调用语法

select 函数名(参数列表)
*/

#--------------------------函数案例演示------------------------
#1 无参有返回  案例 ：返回公司的员工个数
USE myemployees;
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE c INT ; #声明
    SELECT COUNT(*) INTO c #赋值
    FROM employees;
    RETURN c; #调用
END $
SELECT myf1()$

#2有参有返回   
#案例：根据员工名返回工资
CREATE FUNCTION myf2( ename VARCHAR(20)) RETURNS INT
BEGIN 
    DECLARE s INT;
    SELECT salary INTO s
    FROM employees e
    WHERE e.last_name=ename;
    RETURN s;
END $
SELECT myf2('chen')$

#根据部门名返回该部门平均工资
CREATE FUNCTION myf3( dname VARCHAR(20)) RETURNS INT
BEGIN 
    SET @s=0;
    SELECT AVG(e.salary) INTO @s
    FROM employees e JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.department_name = dname;
    RETURN @s; 
END $
SELECT myf3('IT')$

#案例 ：传入两个float ，返回二者之和
CREATE FUNCTION mysum(a FLOAT ,b FLOAT) RETURNS FLOAT
BEGIN
    DECLARE absum FLOAT;
    SELECT a+b INTO absum;
    RETURN absum;
END $
#查看、删除函数
SHOW CREATE FUNCTION myf3\G; #dos
DROP FUNCTION myf3;

##流程控制 ——----------------------------------------------
/*
顺序、分支、循环
*/
#一、 分支
#if(条件,ture,false)
#case
/*
1、等值判断
    
    case 字段  
    when 值1  then 返回值1
    when 值2  then 返回值2
    ...
    else 返回值3
    end 

2、 条件判断
    case
    when 条件1  then  返回值1
    when 条件2  then  返回值2
    ...
    else 返回值3
    end
-----可以和select 、update 搭配，case返回的是 返回值1、返回值2...，位置不限------------


3、case可以单独使用,只能放在begin end中
    case 字段  
    when 判断条件1  then 语句1;
    when 判断条件2  then 语句2;
    ...
    else 语句3;
    end case;
*/

#案例
CREATE PROCEDURE grade_level(IN grade INT)
BEGIN
    CASE 
    WHEN  90< grade AND grade <= 100 THEN SELECT 'A' ;
    WHEN  80< grade AND grade <=90   THEN SELECT 'B' ;
    WHEN  70< grade AND grade <=80   THEN SELECT 'C' ;
    ELSE  SELECT 'D' ;
    END CASE;
END $
CALL grade_level(98)$

#if 多重分支，只能用在begin end中
/*
   if      条件1  then 语句1;
   elseif  条件2  then 语句2;
   ...
   ELSE 语句n;
   end if;
*/
#传入成绩，显示等级
CREATE FUNCTION grade_level( grade INT) RETURNS CHAR
BEGIN 
    IF grade>90 AND grade <= 100 THEN RETURN 'A';
    ELSEIF grade >80 THEN RETURN 'B';
    ELSE RETURN 'c';
    END IF;
END$

##循环结构
/*
分类：while 、 loop 、 repeat

循环控制 ：

iterate ，结束本次循环，继续进行下一次
leave  ，跳出，结束当前循环
出现循环控制时，必须给循环结构添加名称
*/

#1，while
/*
【标签】while 循环条件 do 
    循环体
end while 【标签】;
*/

#2,loop
/*
【标签】loop 
    循环体
end loop 【标签】;

可以模拟简单的死循环

*/

#3，repeat 
/*
【标签】repreat 
    循环体
until 结束循环体的条件
end repeat 【标签】;
*/

/*
loop一般实现简单死循环
while先判断后执行
repeat先执行后判断，无条件至少执行一次
*/
#案例，根据次数插入到admin表中多条记录
CREATE PROCEDURE p_while1(IN insertcount INT)
BEGIN 
    DECLARE i INT DEFAULT 1;
    WHILE i<insertcount DO 
        INSERT INTO admin(`username`,`password`) 
            VALUES(CONCAT('rose',i),'123');
        SET i=i+1;
    END WHILE ;
END$
CALL p_while1(50)$

#案例，根据次数插入到admin表中多条记录,到20次就停止
CREATE PROCEDURE p_while2(IN insertcount INT)
BEGIN
    SET @i=1;
    a:WHILE @i<insertcount DO 
        INSERT INTO admin(`username`,`password`)
            VALUES(CONCAT('xiaohu',@i),'0000');
        IF @i>=20  THEN LEAVE a;
        END IF ;
        SET @i=@i+1;
    END WHILE a;
END$
#案例，根据次数插入到admin表中多条记录,结尾为偶数

CREATE PROCEDURE p_while3(IN insertcount INT)
BEGIN
    SET @i=0;
    a:WHILE @i<insertcount DO 
        INSERT INTO admin(`username`,`password`)
            VALUES(CONCAT('xiaohu',@i),'0000');
        SET @i=@i+2;
    END WHILE a;
END$

CREATE PROCEDURE p_while4(IN insertcount INT)
BEGIN 
    SET @i=1;
    a :WHILE @i<insertcount DO 
        SET @i=@i+1;
        IF MOD(@i,2) !=0 THEN  ITERATE a;
        END IF;
        INSERT INTO admin(`username`,`password`) 
            VALUES(CONCAT('ts',@i),'6666');
    END WHILE a ;
END $

####高级sql
/*GA generally available;

##字符集
delimiter ;
show variables like '%char%';

#插件式的存储引擎，将查询处理和其他的系统任务以及数据的存储提取相分离

mysql 分四层
    1连接层：与其他语言的链接，如Perl、Python
    2服务层：mysql查询，内部优化
    3引擎层：可拔插的引擎，innoDB、myISAM
    4存储层：硬件

#查看引擎
show engines;
show variables like '%engine%';

#sql性能下降的原因
    1查询语句写的烂
    2索引失效（单值、复合）
    3关联查询join太多
    4服务器调优及各个参数设置（缓冲、线程数）


*/    
##索引 ：是帮助提高mysql高效获取数据的【【 数据结构】】  
 /*
*********排好序的快速查找数据结构*************

数据本身之外，数据库还维护着一个满足特定查找算法的【数据结构】
这些数据结构以某种方式指向数据，这样就可以在这些数据的基础上实现
高级查找算法，这种数据结构就是索引。

索引往往以索引文件的形式存储在磁盘上

索引往往指的是B数（多路搜索树）结构组织的索引

聚集索引、全文索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。

出了B+树类型，还有哈希索引（hash index）

索引的优劣
优势：    提高数据检索的效率，降低数据库IO的成本（input and output）
    降低数据排序成本，降低了CPU的消耗
劣势：    提高查询速度的同时，降低了更新表的速度（增删改时，索引也会发生变化）
    数据量较大，需要花时间研究建立最优秀的索引
    
索引分类：
单值索引：一个索引只包含单个列，一个表可以有多个单列索引（银行查卡号，ID卡等）
      一个表最好不超5个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列
全文索引：


##索引的结构：类似于二叉树一样
树的宽度又数据量决定，查询的速度由树的高度决定。
判断次数（IO次数）==树的高度

##什么情况适合建索引
1主键自动建立唯一索引
2频繁作为查询条件的字段
3与其他表关联字段，如外键 应该建立索引
4频繁更新的字段不适合建索引
5where中用不到的字段不建索引
6尽量组合索引
7查询中排序的字段（若排序的字段多个，复合索引按照排序中的多个字段顺序建立）
8统计或分组字段（group by和索引有关）
##什么情况不适合建索引
1表字段太少（3百万以下）
2经常增删改的表
3数据列包含太多重复数据（国籍）


##性能查询
用法 ：explain sql语句;
作用 ： 1表的读取顺序            id
    2数据读取操作的操作类型        select_type
    3哪些索引可以使用
    4是哪索引被实际使用
    5表之间的引用
    6每张表有多少行被优化器查询
    
    id、select_type、table、type、possible_key、key_len、ref、rows、extra

id :id相同时，table从上往下执行；存在子查询时，id不同，id值越大，越先执行。
    表子查询的select_type值为derived；
select_type：SIMPLE \ PRIMARY \ SUBQUERY \ DERIVED \ UNION \ UNION RESULT
    SIMPLE : 不包含子查询和union
    PRIMARY：主查询，最外层 ，最后执行
    SUBQUERY：子查询
    DERIVED：表子查询
    UNION：union后面的表的s_t为union
    UNION RESULT:从union表获取结果的select
table：    显示这一张行数据是关于哪张表的
type： All \ index \ range \ ref \ eq_ref \ const,system \ Null
    显示查询使用了何种类型，由好到坏依次为：
    system>const>eq_ref>ref>range>index>All****************
    -----------------------------------------------------------------
    system:系统表，只有一行数据，const的特例，忽视掉
    const：表示通过索引一次就找到了，用于比较primary和unique索引
        因为只匹配一行数据，如where查主键的一个值
    eq_ref：唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配
        explain select * from e ,d where e.id=d.id;
        #e.id的每个值在d.id中只出现一次
？？？ref：非唯一性索引扫描，返回匹配某个单独值的所有行（符合条件的多行）
        explain select * from employees where job_id='AD_VP';
    range:只检索给定范围的行使用一个索引来选择行，可以列显示使用哪个索引
        between and >  <  in
        explain select * from employees where `employee_id` between 100 and 105;
    index:full index scan.与all的区别在于index只遍历索引数
        explain select `employee_id` from employees;
    full：遍历全表
    
********一般要求出现All以后的百万级查询，必须优化。一般到range，更好为ref

possible_keys：显示可能应用在这张表的索引，一个或者多个（但不一定被查询实际使用）
key：实际使用的索引 【查询中若使用了覆盖索引，则该索引仅出现在key列表中】
    【覆盖索引：用c1、c2建索引，查询为select c1，c2 from ..;】
key_len：索引中使用的字节数，可通过该值计算  查询中使用的索引的长度。在不损失精度
     的情况下，【长度越短越好】。该值为索引字段的最大可能长度，【并非实际使用长度】
？？？     即key_len是根据表计算而得，不是通过表内检索出来的
ref：显示索引的哪一列被使用了，如果可能的话，【是一个常数】
rows：根据 表统计信息 及索引选用情况，大致估算出找到所需记录要读取的行数，越小越好
extract：包含不合适在其他列中显示但十分重要的额外信息;
       #using filesort:没有用索引进行排序，使用了一个外部索引【九死一生】
     假设假设索引为ind_t_a_c_b。
     select * from table t where t.a='*' order by t.c;则为using filesort
     select * from table t where t.a='*' order by t.b;则不为using filesort
     select * from table t where t.a='*' order by t.b，t.c;也不为using filesort
       #using temporary 使用了临时表保存中间结果，MYsql对查询结果排序时使用临时表
        常见于order by 和 group by【十死无生】
       #using index 查询中使用了覆盖索引，【效率不错】 
      同时出现了using where 表明索引被用来执行索引键值的查找（where 后面的列被用于建索引）
          没出现using where表明索引中的列只用来select，没进行where
       #using where 使用了where
       #using join buffer 使用了连接缓存，配置文件里的using buffer调大
       #impossible  where ：where子句的值总是false，不能用来获取任何元组
       #selec table optimized away：在没有group by子句的情况下，基于索引优化min/max
       #distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样的动作
       
    
 */    
EXPLAIN 
SELECT id FROM(
SELECT a.id FROM admin a
 UNION
 SELECT b.id FROM beauty b) c ;
 EXPLAIN
 SELECT a.id FROM admin a
 UNION
 SELECT b.id FROM beauty b;
 EXPLAIN SELECT * FROM admin ORDER BY PASSWORD;
 
/*
索引优化

*/
##案例1 单表索引优化
#category为1，comments>1，views最多的article_id
USE test;
CREATE TABLE IF NOT EXISTS article(
    id INT(10)         UNSIGNED   NOT NULL    PRIMARY KEY    AUTO_INCREMENT,
    author_id INT(10)       UNSIGNED   NOT NULL,
    categoryid INT(10)     UNSIGNED   NOT NULL,
    views INT(10)          UNSIGNED   NOT NULL,
    comments INT(10)     UNSIGNED   NOT NULL,
    title  VARBINARY(255)            NOT NULL,
    content TEXT                NOT NULL 
);

INSERT INTO article(`author_id`,`categoryid`,`views`,`comments`,`title`,`content`) 
VALUES
    (1,1,1,1,'1','1'),
    (2,2,2,2,'2','2'),
    (3,3,3,3,'3','3'),
    (4,4,4,4,'4','4');
#情况一：无索引。！type为all，extra提示using filesort
EXPLAIN  SELECT * FROM article WHERE categoryid =1 AND comments >1 
                   ORDER BY views DESC LIMIT 1;
#情况二：按序建索引ccv.用到了索引，type为range，但是仍使用了文件内排序
#原因为comments为范围查询，sql无法利用索引再对后面的views进行检索。
#即range类型查询字段后面的索引无效
CREATE INDEX inx_article_ccv ON article(categoryid,comments,views);
EXPLAIN  SELECT * FROM article WHERE categoryid =1 AND comments >1 
                   ORDER BY views DESC LIMIT 1;
#情况三：符合索引ca_v,type为ref，无文件内排序
DROP INDEX inx_article_ccv ON article;
CREATE INDEX inx_article_cv ON article(categoryid,views);
EXPLAIN  SELECT * FROM article WHERE categoryid =1 AND comments >1 
                   ORDER BY views DESC LIMIT 1;
##案例2：两表索引优化,左拼给右表加索引。
USE myemployees;
EXPLAIN SELECT * FROM employees e LEFT JOIN departments d 
ON e.`department_id`=d.`department_id`;
SHOW INDEX FROM `departments`;
##案例3：三表连接，用小表驱动大表，索引建立在left 或 right 后面的


##索引失效的原因：
/*
1全职匹配我的最爱

2最佳左前缀法则********
    复合索引要遵守该法则，查询应该从索引的最左前列开始，并且不跳过索引的列
    create index stu_1_2_3 on student(`studentno`,`studentname`,`loginpwd`);
    explain select * from student where studentno='S001' and studentname = '张三封' and `loginpwd`='8888';
    explain select * from student where studentno='S001' and loginpwd = '8888';
    explain select * from student where  loginpwd = '8888';##第一个字段不能丢失

3不在索引列上做任何操作(计算、函数、（自动或者手动）的类型转换)，导致索引失效，全表扫描

4储存引擎不能使用索引中范围条件右边的列
create index table.a_b_c on table(a,b,c)
select * from table where a='' and b>X and c=''; #用不到c，但是
select * from table where a='' and b like 'xx%' and c='';#能用到c

5尽量使用覆盖索引

6mysql在使用非等于时（> < !=）,无法使用索引，全表扫描

7is null ，is not null 也无法使用索引

8like以通配符开始，mysql无法使用索引，全表扫描，
    比如select * ...like '%xx'不推荐，而like 'xx%'则使用了索引，type为range
    若使用like '%xx%'，需要使用覆盖索引，
    即索引包含name时，select name ... like '%xx%';
    #select使用*或者非索引列字段也不行

    like 'kk%',虽然也是range，但是与>x 不同，
    like后面的等值条件还能用（显示在len上）， >x后面的则通通失效

9字符串不加单引号，mysql无法使用索引，全表扫描

10少用or，用or连接时索引会失效

总结：

列只和建索引的书写顺序有关：比如index 123，where 1='' and 2='' and 3=''
                    ===where 2='' and 3='' and 1=''
                where 1='' and 2> and 3=
                ==where 1='' and 3= and 2>
where有order，group时  ：where 1= and 3= order by 2
            ==where 1= and order by 2
            ≈where 1= order by 2,3
            但是 where 1= order by 3,2 （出现内排序，group出现temporary）
            但是where 1= and 2= order by 3,2无内排序，因为2定值了
            
            但是 where 1= and 2= order by 3
            ！==where 1= order by 3（出现内排序using filesort）

范围查询与order by结合
index 12
where 1> order by 1 优
where 1> order by 1,2 优
where 1= order by 2 优
where 1> order by 2  内排序 （理解索引的内涵）
where 1> order by 2,1 内排序 （排好序  的快速查找数据结构）
order by a acs,b desc,内排序（order by 默认升序，同升同降无内排序）

group by ：同order by，但能用where就不用having

提高order by 的方法：
1 使用order by大忌使用select * ，应该只查询需要的字段
  1.1当查询的字段的大小总和<max_length_for_sort_data而且排序字段不是
    text/BLOB时，会使用改进算法--单路排序，否则使用对路排序，速度慢
  1.2单路多路都可能超过sort_buffer的容量（单路可能性更大），超出后会创建temporary，导致慢速

2尝试提高sort_buffer_size
3尝试提高max_length_for_sort_data，但是设置太高，容易超过sort_buffer_size



口诀：
全职匹配我最爱，最左点缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不可丢 ，SQL高级也不难
*/

/*查询截取分析

1至少跑一天，观察，看看生产的慢sql情况

2开启慢查询日志，设置阈值：比如超过5秒的就是慢sql，并将其抓取出来

3explain+慢sql分析

4show profile

5运维经理或DBA进行数据库服务器的参数调优

总结：     1慢查询的开启并捕获
    2explain+慢sql分析
    3show profile查询sql在MySQL服务器里面的执行细节和生命周期情况
    4sql数据服务器的参数调优
    
理解小表驱动大表：

A表数量>>B表
当：【select * from a where a.id in (select id from b)】as WAYIN
    的执行顺序为：
    1 select id from b
    2 select * from a where a.id=b.id
此时的执行效率 in＞ exists

A表数量<<B表
当：【select * from a where exists (select 1 from b where a.id =b.id)】as WAYEX
    的执行顺序为
    1 select * from a
    2 select id from a.id=b.id
此时的执行效率 exists＞in

【exists语法为：
exists子查询中会忽略的select后面的查询列表，所以可以写1 ，'x'等常量值

    1 先查出主查询的全部数据（小表），
    2 然后根据子查询（大表）得到的布尔向量决定小表数据的去留】

***总结：小表驱动大表时，即A表<<B表，使用WAYEX。

*/

/*慢查询日志
show variables like '%slow_query%';

set global slow_query_log =1 ;#开启慢查询日志
    
show variables like '%long_query_time%';#默认时间＞10s为慢sql

set global long_query_time=3;#设置阈值为3，查看该值需在新会话查看，非新查询编辑器
set global slow_query_log_file = 'slow.log';#设置慢查日志的文件位置

select sleep(4); #若执行超过阈值的sql会在慢查询中显示

show global status like '%show_queries%';#显示当前系统中较慢的sql 条数

*/

/*show profile
show variables like '%profiling%';

set profiling = on;

show profiles;

show profile cpu, block io for query 17;#一条sql内部执行的完整生命周期

#如果status出现以下条目，降低速度
1 coverting HEAP to MyISAM :查询结果太大，内存不够，往磁盘上搬了
2 creating tpm table ：创建临时表，[拷贝数据到临时表][用完再删除]
3 cooying to tmp table on disk ：把内存中临时表复制到磁盘。危险！！ 
4 locked
*/

/*全局查询日志：#不要在生产环境开启该功能

set global general_log ;
set global log_output='TABLE';
select * from  my.general_log;

*/

/*数据库锁理论
锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库锁中，除传统的计算资源（CPU，RAM,IO）的征用以外，数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库
并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

表锁分类：
#从对数据操作的类型：
        1读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
            （会话1给表1上读锁，能读表1，不能改表1，不能读表2）
            （会话2         能读表1，改表1阻塞，能读表2）    
        2写锁（排它锁）：当前写锁没有完成之前，它会阻断其他写锁和读锁
            （会话1给表1上写锁，能读表1， 能改表1， 不能读表2）
            （会话2能读表2，读表1发生阻塞，改表1更慢）
        总结：读锁阻塞改表，写锁阻塞读写
show open tables; #显示没上锁的表
lock table 表1 read, 表2 write; #表1读锁，表2写锁
unlock tables; #解锁所有的表
show status like 'table%';

行锁：只在事务中，对行增删改时，导致某一行锁定，另一会话阻塞增删改
行锁变表锁：varchar类型的 值为数字的 字段，没加引号，导致该列都被行锁，变成表锁

set autocommit=0;
select * from table where id=x for update;#强制锁定一行
commit;#直到会话1结束，会话2才能读、写
*/
DELIMITER ;

 

Valar morghulis

标签: sql

<div id="blog_post_info">

好文要顶 关注我 收藏该文

be·freedom
关注 - 3
粉丝 - 3

+加关注

0

0

<div class="clear"></div>
<div id="post_next_prev">

<a class="p_n_p_prefix" href="https://www.cnblogs.com/super-yb/p/12337767.html">« </a> 上一篇：    <a title="发布于 2020-02-20 22:16" href="https://www.cnblogs.com/super-yb/p/12337767.html">word--公式添加编号</a>
<br>
<a class="p_n_p_prefix" href="https://www.cnblogs.com/super-yb/p/12703672.html">» </a> 下一篇：    <a title="发布于 2020-04-15 10:37" href="https://www.cnblogs.com/super-yb/p/12703672.html">R语言--倾向性评分matchit</a>

posted on 2020-04-14 17:32  be·freedom  阅读(120)  评论(0)  编辑  收藏

刷新评论刷新页面返回顶部

发表评论

编辑 预览

22f163dc-90a4-4275-b1c0-08d5b4e8a109

Markdown 帮助 自动补全

不改了 退出 订阅评论

[Ctrl+Enter快捷键提交]

【推荐】超50万行VC++源码: 大型组态工控、电力仿真CAD与GIS源码库
【推荐】2019热门技术盛会400则演讲资料全收录
【推荐】12知识点+20干货案例+110面试题，助你拿offer | Python面试宝典

相关博文：
· MySQL基础(非常全)
· 《老罗Android开发视频教程》更新
· Java Web 学习路线
· 自学前端学习路线图
· SQLyog 使用介绍
» 更多推荐...

精品问答：大数据常见问题之 flink 五十问

最新 IT 新闻:
· Chrome 移动版打开 100 页会出现小彩蛋
· GNU C 标准库增加对兆芯 CPU 的检测支持
· EA公布其年度发布会EA Play Live 2020举办时间
· 亚马逊副总裁辞职 称该公司解雇抗议工人的做法是“懦夫”行为
· Ghost博客平台服务器被黑客攻击并安装了加密货币挖掘软件
» 更多新闻...

历史上的今天：

2019-04-14 支持向量机（SVM）课前准备（一）--拉格朗日乘子法


2019-04-14 开通博客

<!--done-->

Copyright © 2020 be·freedom
Powered by .NET Core on Kubernetes Powered by: 博客园