MySQL系列专题(2)-MySQL的SQL语句和高级特性

#添加一条工作岗位信息
INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES('JAVA_Le','JAVA_Lecturer',2500,9000);

#添加一条员工信息
INSERT INTO `t_employees`
（EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID）
VALUES 
('194','Samuel','McCain','SMCCAIN', '650.501.3876', '1998-07-01', 'SH_CLERK', '3200', NULL, '123', '50');

#修改编号为100 的员工的工资为 25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';

#修改编号为135 的员工信息岗位编号为 ST_MAN，工资为3500
UPDATE t_employees SET JOB_ID=ST_MAN,SALARY = 3500 WHERE EMPLOYEE_ID = '135';

#删除编号为135 的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID='135';

#删除姓Peter，并且名为 Hall 的员工
DELETE FROM t_employees WHERE FIRST_NAME = 'Peter' AND LAST_NAME='Hall';

#清空t_countries整张表
TRUNCATE TABLE t_countries;

-- 员工表  employee
employee_id --编号
first_name --名字
email --邮箱
salary  --月薪
commission_pct --提成
manager_id --所属经理编号
department_id --部门id
job_id --工作编号

-- 部门表 department
department_id --部门编号
department_name --部门名称

-- 工作表 job
job_id --工作id
job_name --工作名称
job_desc --工作内容

--经理表 manager
manager_id --经理编号
manager_name --经理名字

#查询员工表中所有员工的编号、名字、邮箱
SELECT employee_id,first_name,email 
FROM t_employees;

#查询员工表中所有员工的所有信息（所有列）
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;

#查询员工表中所有员工的编号、名字、年薪
SELECT employee_id , first_name , salary*12 
FROM t_employees;

#查询员工表中所有员工的编号、名字、年薪（列名均为中文）
SELECT employee_id as "编号" , first_name as "名字" , salary*12 as "年薪" 
FROM t_employees;

#查询员工表中所有经理的ID。
SELECT DISTINCT manager_id 
FROM t_employees;

#查询员工的编号，名字，薪资。按照工资高低进行降序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC;

#查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;

#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;

#查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30;

#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;

#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #闭区间，包含区间边界的两个值

#查询没有提成的员工信息（编号，名字，薪资 , 提成）
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;

#查询部门编号为70、80、90的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
注：in的查询效率较低，可通过多条件拼接。

#查询名字以"L"开头的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';


#查询名字以"L"开头并且长度为4的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L___';

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    ELSE 结果
END

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT employee_id , first_name , salary , department_id , 
       CASE
           WHEN salary>=10000 THEN 'A'
           WHEN salary>=8000 AND salary<10000 THEN 'B'
           WHEN salary>=6000 AND salary<8000  THEN 'C'
           WHEN salary>=4000 AND salary<6000  THEN 'D'
   ELSE 'E'
       END as "LEVEL"
FROM t_employees;

#查询当前时间
SELECT SYSDATE();

#查询当前时间
SELECT NOW();

#获取当前日期
SELECT CURDATE();

#获取当前时间
SELECT CURTIME();

#拼接内容
SELECT CONCAT('My','S','QL');

#字符串替换
SELECT INSERT('这是一个数据库',3,2,'MySql');#结果为这是 MySql 数据库

#指定内容转换为小写
SELECT LOWER('MYSQL');#mysql

#指定内容转换为大写
SELECT UPPER('mysql');#MYSQL

#指定内容截取
SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL

#统计所有员工每月的工资总和
SELECT sum(salary)
FROM t_employees;

#统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees;

#统计所有员工中月薪最高的工资
SELECT MAX(salary)
FROM t_employees;

#统计所有员工中月薪最低的工资
SELECT MIN(salary)
FROM t_employees;

#统计员工总数
SELECT COUNT(*)
FROM t_employees;

#统计有提成的员工人数
SELECT COUNT(commission_pct) 
FROM t_employees;

#思路：
#1.按照部门编号进行分组（分组依据是 department_id）
#2.再针对各部门的人数进行统计（count）
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id; 

#思路：
#1.按照部门编号进行分组（分组依据department_id）。
#2.针对每个部门进行平均工资统计（avg）。
SELECT department_id , AVG(salary)
FROM t_employees
GROUP BY department_id

#思路：
#1.按照部门编号进行分组（分组依据 department_id）。
#2.按照岗位名称进行分组（分组依据 job_id）。
#3.针对每个部门中的各个岗位进行人数统计（count）。
SELECT department_id , job_id , COUNT(employee_id)
FROM t_employees
GROUP BY department_id , job_id;

#查询各个部门id、总人数、first_name
SELECT department_id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error

#统计60、70、90号部门的最高工资
#思路：
#1).    确定分组依据（department_id）
#2).    对分组后的数据，过滤出部门编号是60、70、90信息
#3).    max()函数处理

SELECT department_id , MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)

# group确定分组依据department_id 
#having过滤出60 70 90部门
#select查看部门编号和max函数。

#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;

#查询表中从第四条开始，查询 10 行
SELECT * FROM t_employees LIMIT 3,10;

#思路：第一页是从 0开始，显示 10 条
SELECT * FROM LIMIT 0,10;

#第二页是从第 10 条开始，显示 10 条
SELECT * FROM LIMIT 10,10;

#第三页是从 20 条开始，显示 10 条
SELECT * FROM LIMIT 20,10;

1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY ： 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果

#1.先查询到 Bruce 的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工资是 6000

#2.查询工资大于 Bruce 的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;

#3.将 1、2 两条语句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）;

#思路：
#1. 先查询 'King' 所在的部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE last_name = 'King'; //部门编号：80、90

#2. 再查询80、90号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90); 

#3.SQL：合并
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列

#1.查询 60 部门所有人的工资（多行多列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;

#2.查询高于 60 部门所有人的工资的员工信息（高于所有）
select * from t_employees where SALARY > ALL(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

#。查询高于 60 部门的工资的员工信息（高于部分）
select * from t_employees where SALARY > ANY(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

#思路：
#1. 先对所有员工的薪资进行排序（排序后的临时表）
select employee_id , first_name , salary
from t_employees
order by salary desc

#2. 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表) 
limit 0,5;

#SQL：合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5;

#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;

#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

#1.查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID

#2.查询所有有部门的员工信息（不包括没有部门的员工） MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID

#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT * FROM t_employees e 
INNER JOIN t_departments d 
on e.department_id = d.department_id
INNER JOIN t_locations l
ON d.location_id = l.location_id

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
LEFT JOIN t_departments d 
ON e.department_id = d.department_id;

#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e 
RIGHT JOIN t_departments d 
ON e.department_id = d.department_id;

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

DELIMITER $$
CREATE PROCEDURE pro_test1()
    BEGIN

        INSERT INTO t2(tname) VALUES('haha');
        UPDATE t1 SET tname='jack1' WHERE tid=1;
        SELECT * FROM t1;
    END $$
  

call pro_test1()

DELIMITER $$
CREATE PROCEDURE pro_test2(IN t2_name VARCHAR(20) , IN t1_id INT)
    BEGIN

        INSERT INTO t2(tname) VALUES(t2_name);
        UPDATE t1 SET tname='jack1' WHERE tid=t1_id;
        SELECT * FROM t1;
    END $$
  
CALL pro_test2('hehe',2)

DELIMITER $$
CREATE PROCEDURE pro_test3(OUT tname VARCHAR(20))
    BEGIN

        SET tname = 'xixi';
      
    END $$

CALL pro_test3(@tname);
SELECT @tname;

DELIMITER $$
CREATE PROCEDURE pro_test4(INOUT tname VARCHAR(20))
    BEGIN
        SELECT tname;
        SELECT CONCAT(tname,"hello") INTO tname;
      
    END $$

SET @tname='jack';
CALL pro_test4(@tname);
SELECT @tname;

declare num2 int default 100;
select num2;

DELIMITER $$
CREATE PROCEDURE pro_test5()
    BEGIN
        DECLARE num INT DEFAULT 100;
        SELECT num;
        set num = 200;
        select num;
    END $$
  
CALL pro_test5

DELIMITER $$
CREATE PROCEDURE pro_test6()
    BEGIN
        DECLARE num INT DEFAULT 100;
        SELECT num;
        SELECT tid INTO num FROM t1 WHERE tname='jack';
        SELECT num;
    END $$

CALL pro_test6

DELIMITER $$
CREATE PROCEDURE pro_test7()
    BEGIN

        SET @param_t1 = 300;
        SELECT @param_t1;
      
        SELECT tid INTO @param_t1 FROM t1 WHERE tname='jack';
        SELECT @param_t1;
    END $$

CALL pro_test7

根据系统变量的作用域分为：全局变量与会话变量（两个@符号）

　　全局变量(@@global.)
　　　　在MySQL启动的时候由服务器自动将全局变量初始化为默认值；
　　　　全局变量的默认值可以通过更改MySQL配置文件(my.ini、my.cnf)来更改。

　　会话变量(@@session.)
　　　　在每次建立一个新的连接的时候，由MySQL来初始化；
　　　　
MYSQL会将当前所有全局变量的值复制一份来做为会话变量（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的）。

全局变量与会话变量的区别：对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话。

IF expression THEN 
   statements;
END IF;
----------------------------------------

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;
----------------------------------------

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)
    BEGIN
      
        IF tid = 1 THEN
            INSERT INTO t1(tname) VALUES('tom');
        END IF;
      
    END $$
  
CALL pro_test8(1);

-----------------------------------------------------------------

DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)
    BEGIN
      
        IF tid = 1 THEN
            INSERT INTO t1(tname) VALUES('tom');
        ELSE
            SELECT CONCAT(tid,'xixi');
        END IF;
      
    END $$
CALL pro_test8(2);

---------------------------------------------------------------------

DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)
    BEGIN
      
        IF tid = 1 THEN
            INSERT INTO t1(tname) VALUES('tom');
        ELSEIF tid = 2 THEN
            SELECT CONCAT(tid,'xixi');
        ELSEIF tid = 3 THEN
            SELECT CONCAT(tid,'xxxxxx');
        END IF;
      
    END $$
  
CALL pro_test8(3);

DELIMITER $$
CREATE PROCEDURE pro_test9(IN number INT)
    BEGIN
        -- 满足什么条件继续循环
        WHILE number > 0 DO

            SELECT number;
            SET number=number-1;
      
        END WHILE;
      
    END $$
CALL pro_test9(6);

DELIMITER $$
CREATE PROCEDURE pro_test10(IN number INT)
    BEGIN
      
        REPEAT 
            INSERT INTO t1(tname) VALUES(CONCAT('aa',number));
            SET number=number-1;
            UNTIL number < 0 -- 这里不需要加分号 这个是满足什么条件退出循环
        END REPEAT;
      
    END $$
  

CALL pro_test10(6);

DELIMITER $$
CREATE PROCEDURE pro_test11()
    BEGIN
        BEGIN
            DECLARE i INT DEFAULT 0;
            loop_x : LOOP
                INSERT INTO t1(tname) VALUES(CONCAT('bb',i));
                SET i=i+1;
                IF i > 5 THEN
                    LEAVE loop_x;
                END IF;
            END LOOP;
        END;
    END $$
  

CALL pro_test11

DELIMITER $$
CREATE PROCEDURE pro_test12(IN var INT)
    BEGIN

            CASE var
                WHEN 1 THEN 
                    SELECT 'a';
                WHEN 2 THEN
                    SELECT 'b';
                ELSE
                    SELECT 'c';
            END CASE;
      
    END $$
  

CALL pro_test12(2);

CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...] 
BEGIN
    routine_body
END;

（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。

调用函数
SELECT func_name([parameter[,…]]);

DELIMITER //

CREATE FUNCTION fc_test(id INT)
RETURNS VARCHAR(20)
BEGIN

    SELECT tname INTO @name FROM t1 WHERE tid=id;

    RETURN @name;

END //


SELECT fc_test(2);

NULL、RESTRICT、NO ACTION
删除：从表记录不存在时，主表才可以删除。删除从表，主表不变
更新：从表记录不存在时，主表才可以更新。更新从表，主表不变
 
CASCADE
删除：删除主表时自动删除从表。删除从表，主表不变
更新：更新主表时自动更新从表。更新从表，主表不变
 
SET NULL
删除：删除主表时自动更新从表值为NULL。删除从表，主表不变
更新：更新主表时自动更新从表值为NULL。更新从表，主表不变

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器


CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

DELIMITER $$
CREATE TRIGGER tri_test1 AFTER DELETE
ON t1 FOR EACH ROW
BEGIN

    INSERT INTO t2(tname) VALUES('ssss');

END$$

SELECT * FROM t1;
SELECT * FROM t2;

UPDATE t1 SET tname = 'aaa' WHERE tid = 1;
DELETE FROM t1 WHERE tid=1;

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW view_test1
AS
SELECT * FROM t1;


CREATE VIEW view_test2
AS
SELECT employee_id,first_name,manager_name FROM employee LEFT JOIN manager ON employee.`manager_id` = manager.`manager_id`;

1、非空约束：not null; 指示某列不能存储 NULL 值

2、唯一约束：unique(); unique约束的字段，要求必须是唯一的，但null除外；

3、主键约束：primary key(); 主键约束=not  null + unique，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

4、外键约束：foreign  key ;保证一个表中的数据匹配另一个表中的值的参照完整性。

5、自增约束：auto_increment

6、默认约束：default  给定默认的值

7、检查性约束：check  保证列中的值符合指定的条件。

1、主键索引（primary key）;

2、唯一索引（unique）;

3、常规索引（index）;

4、全文索引（full  text）;
全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词，主要用于全文检索。
MySQL InnoDB从5.6开始已经支持全文索引，但InnoDB内部并不支持中文、日文等，因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。


SHOW INDEX FROM table_name;

索引字段尽量使用数字型（简单的数据类型）
尽量不要让字段的默认值为NULL
使用唯一索引
使用组合索引代替多个列索引
注意重复/冗余的索引、不使用的索引

1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系