DDL

DDL：[Data Mefinition Language] 数据定义语言
关键点： CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE/DROP INDEX 等
主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DDL操作是隐性提交的！不能rollback

 # 创建 user 数据库
    CREATE DATABASE user;

   # 删除user数据库
    DROP DATABASE user;

   # 语法：
   CREATE TABLE [IF NOT EXISTS] 表名(
   字段名  字段类型  【字段约束】,
   字段名  字段类型  【字段约束】,
   字段名  字段类型  【字段约束】
  );


  # 案例：创建一张表【没有添加约束】
  CREATE TABLE IF NOT EXISTS stuinfo (
   stuid INT,
   stuname VARCHAR (20),
   stugender CHAR(1),
   email VARCHAR (20),
   borndate DATETIME
 ) ;
 
 -- 案例：
 DROP TABLE IF EXISTS stuinfo;
 CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT PRIMARY KEY, --添加了 主键约束
    stuname VARCHAR(20) UNIQUE NOT NULL, -- 添加了 唯一约束+非空
    stugender CHAR(1) DEFAULT '男', -- 添加了默认约束
    email VARCHAR(20) NOT NULL,
    age INT CHECK( age BETWEEN 0 AND 100), -- 添加了检查约束，mysql不支持
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id) -- 添加了外键约束-- majorid列 与 major表内的id列
 );

  # 语法：
  ALTER TABLE 表名 ADD|CHANGE|MODIFY|DROP COLUMN 字段名 字段类型 【字段约束】
    
  # 1，修改表名
  ALTER TABLE stuinfo RENAME TO students ;
    
  # 2.添加字段
  ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL ;
    
  # 3.修改字段名
  ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL ;
    
    
  # 4.修改字段类型
  ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;
    
  # 5.删除字段
  ALTER TABLE students DROP COLUMN birthday ;

    DROP TABLE IF EXISTS students ;
  

   # 复制表
    CREATE TABLE newTable LIKE students ;
    
    # 复制表的结构+数据
    CREATE TABLE newTable SELECT * FROM girls.`admin`

    #案例：复制employees表中的last_name，department_id，salary字段到新表 xx 表，但不复制数据
    CREATE TABLE xx 
    SELECT last_name, department_id, salary 
    FROM myemployees.`employees`
    WHERE 1=2; # 恒不成立

DML

DML：[Data Manipulation Language]　 数据操纵语言
关键点： INSERT、UPDATE、DELETE
主要是对表数据进行操作，如对表数据进行增、删、改

#语法：

#插入单行：
insert into 表名(字段名1,字段名2,....) value(值1, 值2....)

#插入多行：
insert into 表名(字段名1,字段名2,....) 
values(值1, 值2....),(值1, 值2....),...

# 案例1： 要求字段和值列表一一对应，且遵循类型和约束的限制
INSERT INTO students(stuid, stuname, stugender, email)
VALUES(1, '侠奢', '男', 'xxxx.com');

# 案例2： 可以为空的字段如何插入（email可以为空）
INSERT INTO students(stuid, stuname, stugender)
VALUES(2, '猪猪', '男');

INSERT INTO students(stuid, stuname, stugender, email)
VALUES(3, '笨笨', '男', NULL);

# 案例3： 默认字段如何插入（stugerder默认为娚）
INSERT INTO students(stuid, stuname, stugender, email)
VALUES(4, '傻傻', DEFAULT, NULL);

# 案例4： 可以省略字段列表，默认所有字段
INSERT INTO students VALUES(5, '呆呆', '女', NULL)

/*
语法：
update 表明 set 字段名 = 新值, 字段名 = 新值, ...
where 筛选条件
*/


# 案例： 将姓名为笨笨的同学的专业编号修改为13号，且邮箱更改为 xx@126.com

UPDATE students SET stuid = 13, email = 'xx@126.com'
WHERE stuname = '笨笨';
 

/*
方式1： delete 语句
    语法：delete from 表名 where 筛选条件


方式2： truncate 语句
    语法：turncate table 表名
                
    
*/


# 案例1： 删除所有叫名字里带有 傻 的同学的信息
DELETE FROM students WHERE stuname LIKE '%傻%';

# 案例2 ：删除表中所有数据
TRUNCATE TABLE students;

# 区别
1. DELETE 可以添加WHERE 条件
   TRUNCATE 不能添加，一次全删
2. TRUNCATE 效率高
3. 如果删除带自增长列的表
   使用DELETE删除后，重新插入数据，记录容断点处开始
   使用TRUNCATE删除后，重新插入数据，记录从1开始
4. DELETE 删除数据，会返回受影响的函数
   TRUNCATE 删除数据，不返回受影响的行数
5. DELETE 删除数据，可以支持事务回滚
   TRUNCATE 删除数据，不支持事务回滚
     

DCL

DCL： [Data Control Language] 数据控制语言
关键点： GRANT、REVOKE、ROLLBACK、COMMIT

主要用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
如：
用户授权---GRANT

回滚---ROLLBACK
ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

提交事务---COMMIT [WORK]

对数据进行增、删、改操作时，只有当事务在提交到数据库时才算完成。
在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交和自动提交。

1. 显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；

2. 隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

3. 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；开启
SQL>SET AUTOCOMMIT OFF；关闭

在实际开发中，创建事务的时候需要关闭自动提交（默认是开启的），等操作成功全部成功后显性提交事务。事务提交完毕后重新开启自动提交。

DQL

DQL： [Data Query Language ] 数据查询语言
关键点： SELECT
DQL基本结构是由SELECT语句，FROM语句，WHERE语句组成的查询块：
基本语法：SELECT <字段名表> FROM <表或视图名> WHERE <查询条件>

# SELECT 查询列表（字段；函数...） FROM 表名；

# 1. 查询常量
SELECT 100;

# 2. 查询表达式
SELECT 100%3;

# 3. 查询单个字段
SELECT `last_name` FROM `employees`;

# 4. 查询多个字段
SELECT `last_name`,`email`,`employee_id` FROM `employees`;

# 5. 查询所有字段; 格式化 F12
SELECT * FROM `employees`;

SELECT 
`employee_id`,
`first_name`,
`last_name`,
`email` ,
`phone_number`
FROM
`employees` 

# 6. 查询函数（调用方法，获取返回值）
SELECT DATABASE();# 查询当前所在数据库
SELECT VERSION();# 查询当前mysql版本
SELECT USER();# root 用户

# 7. 起别名
# 方式一：使用as关键字
SELECT USER() AS "用户名";

SELECT last_name AS "姓 名" FROM employees;
# 方式二：使用空格
SELECT last_name "姓名" FROM employees;


# 8. 拼接字段
-- 需求：拆线呢 first_name 和last_name 拼接成的全名，
-- 最终起别名为： 姓名
# 方案一： 使用+ 错误！
SELECT first_name+last_name AS "姓 名" 
FROM employees
# 方案二： 使用concat
SELECT CONCAT(first_name, last_name) AS "姓 名"
FROM employees

# 9. distinct的使用
-- 需求 查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_id FROM employees; # 查询所有并去重

# 10. 查询表的结构
DESC employees;
SHOW COLUMNS FROM employees;

# 11. IFNULL的使用
-- 如果表达式1为null，则显示表达式2
SELECT commission_pct,IFNULL(commission_pct,'空') FROM employees;

/*
select 查询列表 from 表名 where 筛选条件；
执行顺序：
1. from子句
2. where子句

select last_name, first_name from employees where salary > 20000

特点：
1. 按关系表达式筛选
关系运算符：>   <   >=   <=   =   <>(!=)  

2. 按逻辑表达式筛选：
逻辑运算符：and（&&）   or（||）   not（!）

3. 模糊查询
like
in
between and
is null
*/

实例

# 一、按关系表达式查询

# 案例1、 查询部门编号不是100的员工信息
SELECT * FROM employees WHERE job_id <> 100;

# 案例2、查询工资小于15000的姓名、工资
SELECT last_name,salary FROM employees WHERE salary < 15000;

# 二、按逻辑表达式查询

# 案例1、查询部门标号不是50-100之间员工姓名、部门编号、邮箱
# 方式1
SELECT last_name,department_id,email FROM employees WHERE department_id < 50 OR department_id > 100
# 方式2
SELECT last_name,department_id,email FROM employees WHERE NOT(department_id >= 50 AND department_id <= 100)

# 案例2、奖金率大于0.03 或者 员工编号在 60-110 之间的员工信息
SELECT *
FROM employees
WHERE commission_pct > 0.03 OR (employee_id >= 60 AND employee_id <= 100)

# 三、模糊查询

-- 一. LIKE
/*
功能：一般和通配符搭配使用，对字符型数据进行匹配查询
常见通配符
_ 任意单个字符
% 任意多个字符 支持0个-多个
*/

# 案例1： 查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

# 案例2： 查询姓名中最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%e';

# 案例3： 查询姓名中第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

# 案例4： 查询姓名中第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '__x%';

# 案例5： 查询姓名中第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '_\_%';

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$'; # 设置$为转义字符

-- 二. IN/NOT IN
/*
功能：查询某字段的值是否属于指定的列表之内
字段a in（常量值1，常量值2，常量值3，...）
字段a not in（常量值1，常量值2，常量值3，...）
*/

# 案例1：查询部门编号是30/50/90的员工名、部门编号
SELECT last_name, department_id
FROM employees
WHERE department_id IN(30,50,90)

# 方式2，如果用逻辑表达式：
SELECT last_name, department_id
FROM employees
WHERE department_id = 30 
OR department_id = 50
OR department_id = 90;

# 案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
SELECT *
FROM employees
WHERE job_id NOT IN ('SH_CLERK','IT_PROG');

# 方式2：
SELECT *
FROM employees
WHERE NOT (job_id = 'SH_CLERK' OR job_id = 'IT_PROG');

-- 三. between and/not between and
/*
功能：判断某个字段的值是否介于xx之间
between and/ not between
*/

# 案例1： 查询部门编号是30-90之间的部门编号、员工姓名
SELECT department_id, last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

# 方式2：
SELECT department_id, last_name
FROM employees
WHERE department_id >=30 AND department_id <= 90;

# 案例2： 查询年薪不是1000000-2000000之间的员工姓名，工资，年薪
SELECT last_name, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

-- 四. is null/ is not null

# 案例1： 查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

/*
--------------------三种等于--------------------------
=     只能判断普通内容
IS    只能判断null值
<=>   安全等于，既能判断普通内容，又能判断null值
*/

# 排序查询
/*
语法：

SELECT 查询列表
FROM 表名
WHERE 筛选条件
order by 排序列表

执行顺序：
1. from 子句
2. where 子句
3. select 子句
4. order by 子句

举例：
SELECT *
FROM employees
WHERE commission_pct IS NULL;

特点：
1. 排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2. 升序，通过asc, 默认行为
降序，通过desc     
*/


-- 一、 按单个字段排序
# 案例1： 将员工编号>120的员工信息进行工资的升序
SELECT 
* 
FROM
employees 
WHERE employee_id > 120 
ORDER BY salary ASC 


-- 二、按表达式排序
# 案例1：对有奖金的员工，按年薪降序
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

-- 三、按别名排序
# 案例1：对有奖金的员工，按年薪降序
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

-- 四、按函数的结果排序
# 案例1： 按姓名的字数长度进行升序
SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY LENGTH(last_name);

-- 五、按多个字段排序
# 案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name, salary, department_id 
FROM employees
ORDER BY salary ASC, department_id DESC;

-- 六、按列数排序
# 案例1： 按第二列字符升序排序
SELECT * 
FROM employees
ORDER BY 2

执行顺序：
1. from 子句
2. where 子句
3. group by 子句
4. having 子句
5. select 子句
6. order by 子句

特点：
1. 查询列表往往是 分组函数和被分组的字段
2. 分组查询中的筛选分为两类
          筛选的基表        使用的关键词          位置
分组前筛选    原始表             where        group by 之前

分组后筛选    分组后的结果集      having      group by 之后

注： 分组函数做条件只能放在having之后
*/

-- 一、基本操作
# 案例1：查询每个工种的员工的平均工资
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;

# 案例2：查询每个领导的手下人数
SELECT COUNT(*), manager_id 
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id

-- 二、可以实现分组前的筛选
# 案例1：查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary) 最高工资, email, department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

# 案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资, manager_id, commission_pct 
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

-- 三、可以实现分组后的筛选 --->  HAVING
# 案例1：查询那个部门的员工个数>5
1. 查询每个部门的员工个数
SELECT COUNT(*) 员工个数, department_id
FROM employees
GROUP BY department_id;

2. 在刚才的结果基础上，筛选哪个部门的员工个数>5
SELECT COUNT(*) 员工个数, department_id
FROM employees
GROUP BY department_id;
HAVING COUNT(*) > 5


# 案例2：每个工种有奖金的员工的最高工资 > 12000的工种编号和最高工资
SELECT job_id 工种编号, MAX(salary) 最高工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 1000;

# 案例3：领导编号>102的每个领导手下的最低工资大于5000的最低工资
SELECT MIN(salary) 最低工资, manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

-- 四、可以实现排序
# 案例：查询有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序

# 分析1： 按工种分组，查询每个工种有奖金的员工的最高工资
SELECT MAX(salary) 最高工资, job_id 工种编号
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY salary
HAVING MAX(salary) > 6000;
ORDER BY MAX(salary) ASC;

-- 五、按多个字段分组
# 案例：查询每个工种每个部门的最低工资，并按最低工资降序
# 注：工种和部门相同的，为一组
SELECT MIN(salary) 最低工资, job_id, department_id 
FROM employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC;

-- 一、字符函数
# 1. concat 拼接字符
SELECT CONCAT('hello', first_name, last_name) 备注 FROM employees

# 2. length 获取字节长度
SELECT LENGTH('hello,大侠');

# 3. char_length 获取字符长度
SELECT CHAR_LENGTH('hello,大侠');

# 4. substring 截取子串
# substring(str,起始索引（从1开始）,截取字符长度)
# substring(str,起始索引（从1开始）)
SELECT SUBSTRING('你好哇！李银河',1,3);

# 5. instr 获取字符串第一次出现的索引
SELECT INSTR('hello,world,我是侠奢', '侠奢');

# 6. trim 去除前后空格(任意字符)
SELECT TRIM('   侠奢   ') AS 我;
SELECT TRIM('a' FROM 'aaaaaaa侠奢aaa') AS 我;

# 7. lpad/rpad 左填充/ 右填充
SELECT LPAD('侠奢',10,'帅');
SELECT RPAD('侠奢',10,'帅');

# 8. upper/lower 变大写/小写

# 案例1：查询员工表的姓名，要求格式：姓首字母大写，其他字符小写，名所有字符大写，且姓和名之间用_分割。
# 最后起别名“OUTPUT”
SELECT UPPER(SUBSTR(first_name,1,1)), first_name
FROM employees;

SELECT LOWER(SUBSTR(first_name,2)) 
FROM employees;

SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)), LOWER(SUBSTR(first_name,2)) ,'_', UPPER(last_name)) "OUTPUT"
FROM employees;


# 9. strtmp 比较两个字符大小
# 前面大返回1，后面大返回-1.否则返回0
SELECT STRCMP('abc', 'abc');

# 10. left/right 截取子串
SELECT LEFT('golang天下第一',6); # 返回golang
 

 -- 二、数学函数
# 1. ABS 求绝对值
SELECT ABS(-2.4);

# 2. ceil 向上取整
SELECT CEIL(1.09) # 返回2

# 3. floor 向下取整
SELECT FLOOR(1.99)； # 返回1

# 4. round 四舍五入
SELECT ROUND（4.6） # 返回5

# 5. truncate 截断
SELECT TRUNCATE(1.876567, 1);  # 返回1.8
SELECT TRUNCATE(1.876567, 2);  # 返回1.87

# 6. mod 取余
SELECT MOD(-10, 3); # 返回1
SELECT -10%3;

-- 三、日期函数   
# 1. NOW 当前日期+时间
SELECT NOW();

# 2. curdate 当前日期
SELECT CURDATE();

# 3. curtime 当前时间
SELECT CURTIME();

# 4. datediff 日期之差
SELECT DATEDIFF('2020-1-1','2000-1-1');

# 5. date_farmat 日期固定格式
SELECT DATE_FORMAT('2010-2-2','%Y年%M月%d日 %H时%i分%s秒')；
# 返回 ；2010年February月02日 00时00分00秒

# 6. str_to_date 按指定格式解析字符串为日期类型
SELECT STR_TO_DATE('3/15 1998', '%m/%d %Y')；