MYSQL基础学习笔记

一、数据库的相关概念

1、数据库的好处

• 1. 可以持久化数据到本地
  2. 结构化查询

2、数据库的常见概念

• 1. DB:数据库，存储数据的容器
  2. DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或者管理DB
  3. SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流的的数据库软件通用的语言

3、数据库存储数据的特点

• 1. 数据存放在表中，然后表在放到库中
  2. 一个库中有多张表，每张表具有唯一的表名用来标识自己
  3. 表中有一个或多个列，列又称为“字段“，相当于java中的”属性“
  4. 表中的每一行数据相当于java中的”对象“

4、常见的管理系统

• mysql、Oracle（Oracle公司）
• db2（IBM公司）
• sqlserver（微软）

二、DQL（查询语言）

• 特点
  • 查询列表可以是字段、常量、表达式、函数，也可以是多个
  • 查询结果是个虚拟表

1、基础查询

• 着重符号 ``,用来区分关键字和字段

• 不区分字符串和字符，可以用‘’也可以用“”

• 使用as别名也可以省略，特殊方式

  • select salary as “out put” from empolyees;

• +号：

  • select ‘100’+90；结果：190

  • select ‘John’+90；结果：90

  • select null+90；结果：null

    • 其中一方为字符串，会试图将字符型数值转换为数值型，如果转换成功，则作加法运算

      如果转换失败，则将字符型数值转换为0

      只要其中一方为null，则结果肯定为null

• 去重distinct

• select distinct 字段名 from 表名

• 字符串拼接使用concat

  • select concat(last_name,first_name) as 姓名 from employees；

• ifnull用法

  • select ifnull(str1,str2) from xxx;如果str1为null，则显示为str2

• 函数isnull(expr)

  • 判断某字段或表达式是否为null，如果是，返回1，如果不是，返回0

2、条件查询

• select from where 执行顺序：
  • 1—from 2—where 3----select
• where条件后面判断相当于java中的if，如果为true，查出对应内容，如果false，直接过滤
• 条件运算符:
  • < > = != <> >= <=
• 逻辑运算符：
  • 作用：用于连接条件表达式
  • && || ！ and or not
• 模糊查询
  • like
    • 一般和通配符搭配使用，可以判断字符型或数值型
    • 通配符：
      • % 任意多个字符，包括0个字符
      • _ 任意单个字符
    • 特殊情况，通配符就是要查询的字符,可以使用\，或者escape
      • select* from employees where last_name like ‘_\_%’
      • select * from employees where last_name like ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’
  • between and
    • 包含临界值
    • 顺序不能颠倒，小的放前面，大的放后面
    • select * from employees where employee_id between 100 and 120
  • in
    • in列表的值类型必须一致或兼容
    • 不支持通配符
    • select * from employees where job_id in (‘IT_PROT’,‘AD_VP’,‘AD_PRES’)
  • is null
    • =或者<>不能用于判断null值
    • select * from employees where commission_pct is null
  • <=>
    • 安全等于，既可以判断null值也可以判断普通的数值，与is null相比可读性较差
    • select * from employees where commission_pct <=> null
• 经典面试题：
  • 试问select * from employees和select * from employees where commission_pct like ‘%%’ and last_name like '%%'结果是否一致，为什么？
  • 答：结果不一致，因为%代表一个或多个字符，但是不包括null值，所以不一致。

3、排序查询

• select 查询列表 from 表名 【where 筛选条件】order by 排序列表 【asc|desc】
• asc代表升序，desc代表降序，如果不写，默认是升序
• order by子句一般放在查询语句的最后面，limit子句除外
• order by支持别名
  • select *，salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc
• order by支持按函数排序
  • select length(last_name) 字节长度,last_name,salary from employees order by length(last_name) desc
• order by支持多字段排序
  • 先按第一个字段排序，再第一值有相同的情况下，按第二字段的排序来，以此类推
  • select * from employees order by salary asc,employee_id desc;

4、常见函数

• 概念：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

• 优点：

  • 隐藏了实现细节
  • 提高了代码的重用性

• 调用：

  • select 函数名（实参列表）【from 表名】

• 分类：

  • 单行函数
    • 如：concat、length、ifnull等
    • 字符函数、数学函数、日期函数、其他函数、流程控制函数
  • 分组函数
    • 做统计使用，又称为统计函数、聚合函数、组函数

1、单行函数

1、字符函数

• length 获取参数值的字节个数

  • select length(‘张三丰’)；
  • 等于9
  • utf-8编码一个汉字占3个字节，一个字母占1个字节
  • GBK编码一个汉字占2个字节
  • select variables like’%char%'可以查看字符集编码

• concat 拼接字符串

• upper、lower 字母变大小写

  • select concat(upper(last_name),’_’,lower(first_name)) 姓名 from employees

• substr、substring 字符串截取

  • select substr(str,pos);
    • 表示将str从第pos个索引开始截取后面所有字符
    • 注意索引从1开始
  • select substr(str from pos for len)
    • 表示将str从第pos个开始截取，长度为len的字符串

• INSTR(str,substr) 返回字串第一次出现的索引，如果找不到返回0

• TRIM([remstr FROM] str) 去掉str中前后字符为remstr的字符，如果不加remstr，默认去掉前后空格

• LPAD(str,len,padstr) 用指定的padstr字符来左填充str，最终总长度为len

• RPAD(str,len,padstr) 用指定的padstr字符来右填充str，最终总长度为len

• REPLACE(str,from_str,to_str) 替换

2、数学函数

• ROUND(X) 四舍五入取整
  • ROUND(X,D) 四舍五入保留D位小数
• CEIL(X) 向上取整，返回>=该参数的最小整数
• FLOOR(X) 向下取整，返回<=该参数的最大整数
• TRUNCATE(X,D) 截断，从第D位小数开始截断
• MOD(N,M) 取余 相当于 N-N/M*M
• RAND() 获取0-1之间的随机小数

3、日期函数

• NOW() 返回当前系统日期+时间

• CURDATE() 返回当前系统日期，不包含时间

• CURTIME() 返回当前时间，不包含日期

• YEAR(date)、MONTH(date)、DAY(date)、HOUR(time)、MINUTE(time)、SECOND(time)

  • 获取指定的年月日时分秒

• STR_TO_DATE(str,format): 将日期格式的字符转换成指定格式的日期

  • STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) 1999-09-13

• date_format:将日期转换成字符

  • DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) 2018年06月06日

• DATEDIFF(expr1,expr2) 计算日期差

• MONTHNAME(date) 以英文形式返回月

  

4、其他函数

• VERSION() 版本号
• DATABASE() 当前数据库
• USER() 当前用户
• PASSWORD(str) 返回str的加密内容
• MD5(str) 将str以MD5的方式加密

5、流程控制函数

• IF(expr1,expr2,expr3)函数 if else效果 类似java三元运算符

  • select if(10>5,‘大’,‘小’);

• case 函数

  • 使用一：类似switch case的效果

    • 用于等值判断

    CASE case_value
    	WHEN when_value THEN
    		statement_list
    	ELSE
    		statement_list
    END CASE;
    

    select salary 原始工资,department_id,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
    

  • 使用二：类似多重if

    • 用于区间大小判断

    CASE 
    	WHEN 条件一 THEN
    		statement_list2
    	WHEN 条件二 THEN
    	  statement_list2
    	ELSE
    		statement_list
    END CASE;
    

    select salary,CASE
    	WHEN salary>20000 THEN 'A'
    		when salary>15000 then 'B'
    		WHEN salary>10000 then 'C'
    	ELSE
    		'D'
    END AS 级别 from employees;
    

2、分组函数

​ 分组函数也可以联合使用，嵌套使用

• SUM(expr) 求和
• AVG(expr) 平均值
• MIN(expr) 最小值
• MAX(expr)最大值
• COUNT(expr) 计算个数
  • count(字段) 会把该字段中值为null的过滤掉
  • count(*)和count(1)结果一致
    • MYISAM存储引擎下（MYSQL5.5之前默认），count(*)效率更高，因为此存储引擎下默认有个计数器
    • INNODB存储引擎下（MYSQL5.5以后包括5.5），count(*)和count(1)效率差不多，比count（字段）效率要高一些，count(1)相当于在字段前面加了一列，每一行标记一个1，然后数1的个数

特点：

• 1. sum、avg一般都处理数值型，min、max、count可以处理任何类型
  2. 以上分组函数都忽略null值
  3. 可以和distinct搭配使用实现去重的效果
  4. 和分组函数一同查询的字段要求是group by后的字段

5、分组查询

• 语法：

  select 分组函数，列（要求出现在group by的后面）
  from 表
  【where 筛选条件】
  group by 分组的列表
  【order by子句】
  

  注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

• 特点：

  • 分组中的筛选条件可以分为两类（数据源）

    类型	数据源	位置	关键字
    分组前筛选	原始表	group by子句的前面	where
    分组后筛选	分组后的结果集	group by子句的后面	having

    • 分组函数做条件肯定是放在having子句中
    • 能用分组前筛选的，优先考虑分组前筛选（性能问题）
    • group by和having后面都支持使用别名

  • group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式或函数用得较少

  • 可以添加排序（排序方法整个分组查询的最后）

6、连接查询

• 含义：
• 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
• 笛卡尔乘积现象
  • 表1有m行，表2有n行，结果=m*n行
  • 发生原因：没有有效的连接条件

• 分类
  • 按年代分类
    • sql92标准：支持内连接，也持部分外连接（只限于Oracle，SQL server，MySQL不支持）
    • sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
  • 按功能分类
    • 内连接
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接（MySQL不支持）
    • 交叉连接

1、sql92标准

1、等值连接

SELECT last_name,department_name from employees e,departments d where e.department_id=d.department_id

• 可以为表其别名
  • 提高语句的简洁度
  • 区分多个重名的字段
  • 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
• 特点：
  • 多表等值连接的结果为多表的交集部分
  • n个表接连，至少需要n-1个连接条件
  • 多表的顺序没有要求
  • 一般需要为表起别名
  • 可以搭配前面介绍的所有子句使用，如排序、分组、筛选

2、非等值连接

SELECT salary,grade_level from employees e,job_grades j WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal

3、自连接

SELECT e.last_name,ee.last_name from employees e,employees ee WHERE e.manager_id=ee.employee_id

2、sql99标准

• 语法

  select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 条件
  

• 分类

  • 内连接：inner
  • 外连接
    • 左外：left 【outer】
    • 右外：right 【outer】
    • 全外：full 【outer】
  • 交叉连接： cross

1、内连接

• 语法：

  select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件
  

• 特点：

  • 可以添加排序、分组、筛选
  • inner可以省略
  • 筛选条件放在where后面，连接条件放在on后i面，提交分离性，便于阅读
  • inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

2、外连接

• 用于查询一个表中有，另一个表没有的记录
• 特点
  • 外连接的查询结果为主表中的所有记录
    • 如果从表中有和它匹配的，则显示匹配的值
    • 如果从表中没有和它匹配的，则显示null
    • 外连接查询结果=内连接结果+主表中有而从表没有的记录
  • 左外连接：left join左边的是主表
  • 右外连接：right join右外的是主表
  • 左外和右外交换两个表的顺序，可以实现同样的效果
  • 全外连接=内连接的结果+表1中有但表2没有的+表2有但表1没有的

3、交叉连接

• 就是笛卡尔乘积

  select b.*,bo.* from beauty b cross join boys bo
  

3、sql92和sql99比较

• 功能：sql99支持的较多
• 可读性：sql99实现连接条件和筛选条件的分离，可读性较高

7、子查询

• 含义

  • 出现在其他语句中的select语句，称为子查询或内查询
  • 外部的查询语句，称为主查询或外查询

  

• 分类

  • 按子查询出现的位置
    • select后面
      • 标量子查询
    • from后面
      • 支持表子查询
    • where或having后面
      • 标量子查询
      • 列子查询
      • 行子查询
    • exists后面（相关子查询）
      • 表子查询
  • 按结果集的行列数不同
    • 标量子查询（结果集只有一行一列）
    • 列子查询（结果集只有一列多行）
    • 行子查询（结果集有多行多列）
    • 表子查询（结果集一般为多行多列）

1、where或having后面

• 标量子查询（单行子查询）

• 列子查询（多行子查询）

• 行子查询（一行多列或多行多列）

• 特点

  • 子查询放在小括号内
  • 子查询一般放在条件的右侧
  • 标量子查询，一般搭配着单行操作符使用
    • > < >= <= = <>
  • 列子查询，一般搭配着多行操作符使用
    • IN、ANY/SOME、ALL
  • 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1、标量子查询

2、列子查询（多行子查询）

3、行子查询（结果集一行多列或多行多列）

SELECT * from employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) from employees)

2、放在select后面

• 仅仅支持标量子查询

3、from后面

• 将子查询结果充当一张表，要求必须起别名

  SELECT grade_level,tt,department_id from (SELECT avg(salary) tt,department_id from employees GROUP BY department_id) d,job_grades j WHERE d.tt BETWEEN j.lowest_sal and j.highest_sal
  

4、exists后面（相关子查询）

• 语法

  • exists（完整的查询语句），结果1或0

  SELECT * from boys bo WHERE NOT EXISTS (SELECT * from beauty b WHERE b.boyfriend_id=bo.id)
  

8、分页查询

• 应用场景

  • 当要显示的数据一页显示不全，需要分页提交sql请求

• 语法

  select 查询列表 from 表【 join type join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段】 limit 【offset,】size
  
  offset：要显示条目的起始索引（起始索引从0开始）
  size：要显示的条目个数
  

• 特点

  • limit语句放在查询语句的最后

  • 公式

    • 要显示的页数page,每页的条目size

    select 查询列表 from 表 limit （page-1）*size,size
    

9、联合查询

• union，将多条查询语句的结果合并成一个结果

SELECT * FROM employees WHERE email LIKE 'a%'
UNION
SELECT * from employees WHERE department_id>90

• 语法：

  • 查询语句1
    UNION 【all】
    查询语句2
    UNION 【all】
    ...
    

• 应用场景

  • 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

• 特点：

  • 要求多条查询语句的查询列数是一致的
  • 要求多条查询语句的查询的每一列的类型和顺序最好一致
  • union关键字默认去重，如果使用union all可以包含重复项

三、DML语言

1、插入语句

• 语法：

  • 方式一：经典方式

  INSERT INTO 表名（列名,...） values（值1,...）
  

  • 方式二：

  INSERT INTO 表名 set 列名=值,列名=值,...
  

• 两种方式的区别

  • 方式一支持插入多行，方式二不支持

  • 方式一支持子查询，方式二不支持

    INSERT INTO beauty(id,name,phone) 
    SELECT id,boyname,'11809866' from boys WHERE id<3;
    

• 特点：

  • 插入的值的类型要与列的类型一致或兼容
  • 不可以为null的列必须插入值，可以为null的列可以插入null，或不写该列
  • 列的顺序可以调换，但是要一一对应
  • 列数和值必须一致
  • 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

2、修改语句

• 修改单表的记录

  • 语法

    UPDATE 表名 set 列=值,列=值,... WHERE 筛选条件；
    

• 修改多表的记录

  • 语法

    sql92语法：
    UPDATE 表1 别名,表2 别名 SET 列=值,... WHERE 连接条件 AND 筛选条件；
    
    sql99语法：
    UPDATE 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 SET 列=值,... WHERE 筛选条件;
    

3、删除语句

• 单表的删除

  • 方式一

  DELETE FROM 表名 WHERE 筛选条件 LIMIT条件
  

  • 方式二

  TRUNCATE TABLE 表名
  

  注意：truncate不能加where条件，它是整表删除

• 多表的删除

  sql92语法：
  DELETE 表1 别名,表2 别名 FROM 表1 别名,表2 别名 WHERE 连接条件 AND 筛选条件
  
  sql99语法：
  DELETE 表1 别名,表2 别名 FROM 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 WHERE 筛选条件;
  

• delete和truncate区别

  • delete可以加where条件，truncate不能加
  • truncate删除效率高一点
  • 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始
  • truncate删除没有返回值，delete删除有返回值
  • truncate删除不能回滚，delete删除可以回滚

四、DDL语言

1、库的管理

• 库的创建

  CREATE DATABASE [if not exists] 库名 [character set 字符集名]
  

• 库的修改

  • 更改库的字符集

  ALTER DATABASE 库名 CHARACTER SET 字符集名称
  

• 库的删除

  DROP DATABASE [if exists] 库名
  

2、表的管理

• 表的创建

  CREATE TABLEE 【if not exists】 表名（
  列名 列的类型【（长度） 约束】,
  列名 列的类型【（长度） 约束】,
  ...
  ）
  

• 表的修改

  • 修改列名

    ALTER TABLE 表名 CHANGE COLUMN 原列名 新列名 类型|约束
    

  • 修改列的类型或约束

    ALTER TABLE 表名 MODIFY COLUMN 列名 类型|约束
    

  • 添加新列

    ALTER TABLE 表名 ADD COLUMN 列名 类型|约束 【first|after 字段名】
    

  • 删除列

    ALTER TABLE 表名 DROP COLUMN 列名
    

  • 修改表名

    ALTER TABLE 表名 RENAME 【TO】 新表名
    

• 表的删除

  DROP TABLE 【if exists】 表名
  

• 表的复制

  • 仅仅复制表的结构

    CREATE TALBE 新表名 LIKE 原表名
    

  • 复制表的结构+数据

    CREATE TABLE 新表名
    SELETE * FROM 原表名
    

3、常见的数据类型

• 数值型

  • 整型

    

    • 特点
      • 默认是有符号，如需设置无符号，需添加unsigned关键字
      • 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
      • 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0再左边填充，但必须搭配zerofill使用，如果使用zerofill，则同时设置了该字段为无符号

  • 小数

    

    • 定点数

      • dec(M,D)
      • decimal(M,D)

    • 浮点数

      • float(M,D)
      • double(M,D)

    • 特点

      • M:整数部位+小数部位

        D:小数部位

        如果超过范围，则插入临界值

      • M和D都可以省略

        如果是decimal，则M默认为10，D默认为0

        如果是float和double，则会根据插入的数值的精度来决定精度

      • 定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用

    • 使用原则

      • 所选择的类型越简单越好，能保存数值的类型越小越好

• 字符型

  • 较短的文本：char,varchar

  

  	写法	M的意思	特点	空间的耗费	效率
  char	char(M)	最大的字符数（可以省略，默认为1）	固定长度的字符	比较耗费	高
  varchar	varchar(M)	最大的字符数（不可以省略）	可变长度的字符	比较节省	低

  

  

  

  • 较长的文本：text、blob（较长的二进制数据）

• 日期型

​ datatime和timestamp的区别

4、常见约束

• 一种限制，用于限制表中的数据，为了保证表中的数据准确和可靠性

  CREATE TABLE 表名(
  	字段名 字段类型 约束
  )
  

• 分类：六大约束

  • NOT NULL:非空，用于保证该字段的值不能为空，如姓名、学号等
  • DEFAULT:默认，用于保证该字段有默认值，如性别
  • PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空，如学号、员工编号等
  • UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空，如座位号
  • CHECK:检查约束【mysql中不支持】，如年龄，性别
  • FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表关联列的值。在从表添加外键约束，用于引用主表中某列的值，如学生表的专业编号，员工表的部门编号，员工表的公众工种

• 主键和唯一的区别：

  	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
  主键	√	×	至多有一个	√
  唯一	√	√	可以有多个	√

• 外键的特点：

  • 要求在从表设置外键关系

  • 从表的外键列类型和主表关联列类型要求一致或兼容，名称无要求

  • 主表的关联列必须是一个key（一般是主键或唯一）

  • 插入数据时，先插入主表，再插入从表

    删除数据时，先删除从表，再删除主表

• 添加约束的时机

  • 创建表时

    • 添加列级约束

      • 语法：

        直接在字段名和类型后面追加约束类型即可

        只支持：默认、非空、主键、唯一

    CREATE TABLE stuinfo(
    	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 REFERENCES major(id) #外键，不支持
    );
    
    CREATE TABLE major(
    	id INT PRIMARY KEY,
    	majorName varchar(20)
    );
    
    #查看stuinfo表中的索引，包括主键、外键、唯一
    SHOW INDEX FROM stuinfo;
    
    

    • 添加表级约束

      • 语法：

        在各个字段的最下面

        【constraint 约束名】 约束类型（字段名）

    CREATE TABLE stuinfo(
    	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='男' OR gender='女'),#检查,不支持
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
    );
    
    

    #通用写法:
    CREATE TABLE IF NOT EXISTS stuinfo(
    	id INT PRIMARY KEY,
    	stuName VARCHAR(20) NOT NULL,
    	sex CHAR(1),
    	age INT DEFAULT 18,
    	seat INT UNIQUE,
    	majorid INT,
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    );
    

  • 修改表时

    • 添加列级约束

    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束
    

    • 添加表级约束

    ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名）
    

    #1.添加非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
    #2.添加默认约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
    #3.添加主键
     #列级约束
    ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
     #表级约束
    ALTER TABLE stuinfo ADD PRIMARY KEY(id);
    #4.添加唯一
     #列级约束
    ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
     #表级约束
    ALTER TABLE stuinfo ADD UNIQUE(seat);
    #5.添加外键
    ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
    

    • 删除约束

    #1.删除非空约束
    ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) null;
    #2.删除默认约束
    ALTER TABLE stuinfo MODIFY COLUMN age INT;
    #3.删除主键
    ALTER TABLE stuinfo DROP PRIMARY KEY;
    #4.删除唯一
    ALTER TABLE stuinfo DROP INDEX seat;
    #5.删除外键
    ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
    

• 约束的添加分类

  • 列级约束：
    • 六大约束语法上都支持，但外键约束没有效果
  • 表级约束：
    • 除了非空、默认，其他的都支持

  	位置	支持的约束类型	是否可以起约束名
  列级约束	列的后面	语法都支持，但外键没有效果	不可以
  表级约束	所有列的后面	默认和非空不支持，其他支持	可以（主键没有效果）

• 级联删除和级联置空

  • 级联删除

    alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete cascade
    

  • 级联置空

    alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete set null
    

5、标识列

• 又称为自增长列，可以不用手动的插入值，系统提供默认的序列值
• 创建表时设置标识列：

CREATE TABLE 表名（
	id INT PRIMARY KEY AUTO_INCREMENT,
	...
）

• 修改表时设置标识列

#修改
ALTER TABLE 表名 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#删除
ALTER TABLE 表名 MODIFY COLUMN id INT;

• 特点：
  • 标识列必须和一个key搭配，不一定是主键
  • 一个表至多一个标识列
  • 标识列的类型只能是数值型
  • 标识列可以通过 SET AUTO_INCREMENT_INCREMENT=3 设置步长，也可以通过手动插入值设置起始值

五、TCL语言

• Transaction Control Language 事务控制语言

• 存储引擎

  

• 事务的特点

  

• 事务的创建

  • 隐式事务：事务没有明显的开启和结束的标记，如insert、update、delete语句
  • 显示事务：事务具有明显的开启和结束的标记（前提：必须先设置自动提交功能为禁用 set autocommit=0）

  #步骤1：开启事务
  set autocommit=0;
  start transaction;#可选的
  #步骤2：编写事务中的sql语句（select insert update delete）
  #步骤3：结束事务
  commit；#提交事务
  rollback；#回滚事务
  savepoint 节点名;#设置保存点
  

• savepoint配合rollback使用，rollback to 保存点

• 数据库的隔离级别

  

  

#查看隔离级别
SELECT @@TX_ISOLATION;
#设置隔离级别
SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别；

六、视图

• 创建视图

CREATE VIEW 视图名
AS
查询语句；

• 视图的好处

  

• 视图的修改

  • 方式一

CREATE OR REPLACE VIEW 视图名
AS
查询语句；

+ 方式二

```mysql
ALTER VIEW 视图名
AS
查询语句；

• 删除视图

DROP VIEW 视图名,视图名,...;

• 查看视图

#方式一：
DESC 视图名；
#方式二：
SHOW CREATE VIEW 视图名；

• 视图的更新

  • 插入、更新、删除

    和普通一样，并且更新的值在原始表中也会存在

  

• 视图和表的区别

  	创建语法的关键字	是否实际占用物理空间	使用
  视图	create view	只是保存了sql逻辑	增删改查，一般不能增删改
  表	create table	保存了数据	增删改查

七、变量

1、系统变量

• 变量由系统提供，不是用户定义，属于服务器层面

• 全局变量

  • 必须拥有super权限才能为系统变量赋值
  • 作用域：服务器每次启动将为所有的系统变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启

• 会话变量

  • 作用域：仅仅针对于当前会话（连接）有效

  • 使用语法：

    • 查看所有系统变量

    show global|[session] variables
    

    • 查看满足条件的部分系统变量

    show global|[session] varialbes like '%char%'
    

    • 查看指定的某个系统变量的值

    select @@global|[session].系统变量名
    

    • 为某个变量赋值

    #方式一
    set global|[session] 变量名 = 值;
    #方式二
    set @@global|[session].变量名 = 值;
    

    • 注意：
      • 如果是系统变量，则需要加global,如果是会话级别，则需要加session,如果不写，默认是session级别

2、自定义变量

• 变量是用户自定义的，不是由系统提供

• 用户变量

  • 作用域：针对于当前会话（连接）有效，同于会话变量的作用域

  • 应用在任何地方，也就是begin end的里面或者外面

  • 使用语法

    • 声明并初始化

    set @用户变量名=值;
    set @用户变量名:=值;
    select @用户变量名:=值;
    

    • 赋值（更新用户变量的值）

    #方式一
    set @用户变量名=值;
    set @用户变量名:=值;
    select @用户变量名:=值;
    #方式二：通过select into
    select 字段 into @变量名 from 表;
    

    • 查看用户变量的值

    select @用户变量名;
    

• 局部变量

  • 作用域：仅仅在定义它的begin end中有效

  • 应用在begin end中的第一句话！！！

  • 使用语法

    • 声明

    declare 变量名 类型；
    declare 变量名 类型 default 值；
    

    • 赋值

    #方式一
    set 局部变量名=值;
    set 局部变量名:=值;
    select @局部变量名:=值;
    #方式二：通过select into
    select 字段 into 局部变量名 from 表;
    

    • 查看

    select 局部变量名;
    

• 对比用户变量和局部变量：

	作用域	定义和使用位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	begin end中	只能在begin end中，且为第一句话	一般不加@符号，需要限定类型

八、存储过程和函数

• 类似于java中的方法

1、存储过程

• 一组预先编译好的sql语句的集合，理解成批处理语句

• 好处：

  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

• 使用

  • 创建语法

  create procedure 存储过程名（参数列表）
  begin
  	存储过程体（一组合法的sql语句）
  end
  

  ​ 注意：

  ​ 1、参数列表包含三部分
  ​ 参数模式 参数名 参数类型
  ​ 如：in xxx varchar（20）

  ​ 参数模式：

  ​ in: 该参数可以作为输入，该参数需要调用方传入值，in可以省略

  ​ out:该参数可以作为输出，该参数可以作为返回值

  ​ inout：该参数既可以作为输入又可以作为输出，该参数既需

  ​ 要传入值，又可以返回值

  ​ 2、如果存储过程体仅仅只有一句话，begin end可以省略

  ​ 存储过程体的每条sql语句的结尾要求必须加分号

  ​ 存储过程的结尾可以使用delimiter重新设置

  ​ 语法：

  ​ delimiter 结束标记

  • 调用方法

  call 存储过程名（实参列表）
  

  • 例子

delimiter $
CREATE PROCEDURE myp5(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) into result from admin WHERE admin.username=username and admin.password = password;#赋值
SELECT IF(result>0,‘成功’,‘失败’)result;#使用
END$

```mysql
CREATE PROCEDURE myp6(IN girl_name VARCHAR(20),OUT boy_name VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boy_name from boys bo INNER JOIN  beauty b on bo.id = b.boyfriend_id and b.`name`=girl_name;
END$

call myp6('小昭',@bName)$
SELECT @bName$

CREATE PROCEDURE myp7(IN girl_name VARCHAR(20),OUT boy_name VARCHAR(20),OUT userCP INT)
BEGIN
	SELECT bo.boyName,bo.userCP INTO boy_name,userCP from boys bo INNER JOIN  beauty b on bo.id = b.boyfriend_id and b.`name`=girl_name;
END$

call myp7('小昭',@bName,@userCP)$
SELECT @bName,@userCP$

CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a*2;
	SET b = b*2;
END$

SET @m=10$
SET @n=20$
call myp8(@m,@n)$
SELECT @m,@n$

• 删除存储过程

  • 语法（只支持单个删除）：

  drop procedure 存储过程名
  

• 查看存储过程的信息

show create	 procedure 存储过程名

2、存储函数

• 一组预先编译好的sql语句的集合，理解成批处理语句
• 好处：
  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

• 区别

  • 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、更新
  • 存储函数：有且仅有1个返回，适合做处理数据后返回一个结果

• 创建语法

create function 函数名（参数列表） returns 返回类型
begin
	函数体
end

​ 注意：

​ 1、参数列表：参数名、参数类型

​ 2、函数体：肯定会有return语句，如果没有会报错

​ 如果return语句没有放在函数体的最后也不报错，但不建

​ 议

​ 3、函数体中仅有一句话，则可以省略begin end

​ 4、使用delimiter语句设置结束标记

• 调用语法

select 函数名（参数列表）

​ 例子：

CREATE FUNCTION myf1() returns INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT count(*) into c from employees;
	RETURN c;
END$

SELECT myf1()$

CREATE FUNCTION myf2(ep VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; #定义用户变量
	SELECT salary INTO @sal from employees WHERE last_name=ep;
	return @sal;
END$

SELECT myf2('kochhar')$

• 查看函数

show create function 函数名

• 删除函数

drop function 函数名

九、流程控制结构

顺序结构：程序从上往下一次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环结构：程序在满足一定条件的基础上，重复执行一段代码

1、分支结构

• if函数

  • 功能：实现简单的双分支
  • 语法：

  if(表达式1，表达式2，表达式3)
  

  ​ 执行顺序：

  ​ 如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

  ​ 应用：任何地方

• case结构

  • 情况1：类似于java中的switch语句，一般用于实现的等值判断

    • 语法：

    case 变量|表达式|字段
    when 要判断的值 then 返回的值1或语句1;
    when 要判断的值 then 返回的值2或语句2;
    ...
    else 要返回的值n或语句n;
    end case;
    

  • 情况2：类似于java中的多重if语句，一般用于实现区间判断

    • 语法：

    case 
    when 要判断的条件1 then 返回的值1或语句1；
    when 要判断的条件2 then 返回的值2或语句2；
    ...
    else 要返回的值n或语句n；
    end case；
    

  • 特点

    • 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end的外面

      可以作为独立的语句去使用，只能放在begin end中

    • 如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值

    • else可以省略，如果省略，并且所有when条件都不满足，则返回null

​ 举例

create PROCEDURE myp1(IN score INT)
BEGIN
	CASE 
	WHEN score>=90 and score=<100 THEN
		SELECT 'A'
	when score>=80 and score<90 then
	  SELECT 'B'
	when score>=60 and score<80 then
	  SELECT 'C'
	ELSE
	  SELECT 'D'
END CASE;

END$

call myp1(95)$

• if结构

  • 功能：实现多重分支
  • 语法

  if 条件1 then 语句1；
  elseif 条件2 then 语句2；
  ...
  【else 语句n;】
  end if;
  

  • 应用场合：应用在begin end中

  create FUNCTION myp1(IN score INT) RETURNS CHAR
  BEGIN
  	if score>=90 and score=<100 THEN return 'A';
  	ELSEIF score>=80 then return 'B';
  	ELSEIF score>=60 then return 'C';
  	else return 'D';
  	end if;
  END$ 
  
  SELECT myp1(95)$
  

2、循环结构

• 分类：

  • while、loop、repeat

• 循环控制：

  • iterate 类似于java中的continue，结束本次循环，继续下一次
  • leave 类似于java中的break，结束当前所在的循环

• while

  • 语法

  【标签：】while 循环条件 do
  	循环体；
  end while 【标签】；
  

• loop

  • 语法

  【标签：】loop
  	循环体；
  end loop 【标签】;
  
  #可以用来模拟简单的死循环
  

• repeat

  • 语法

  【标签：】repeat
  	循环体；
  until 结束循环的条件
  end repeat 【标签】;
  

• 举例

delimiter
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO 
		INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666');
		SET i = i+1;
END WHILE;

END $

call pro_while1(10)$

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO 
		INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666');
		if i>=20 THEN LEAVE a;
		end if;
		SET i = i+1;	
END WHILE a;

END $

call pro_while1(100)$

CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	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('rose',i),'666');
		
END WHILE a;

END $

call pro_while1(100)$

create PROCEDURE pro_while2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;#定义一个循环变量i，表示插入次数
	DECLARE str varchar(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;#代表起始索引
	DECLARE len INT DEFAULT 1;#代表截取的字符的长度
	WHILE i<=insertCount DO 
		SET startIndex = FLOOR(RAND()*26+1);#产生一个随机的整数，代表起始索引1-26
		SET len = FLOOR(RAND()*(26-startIndex+1)+1);#产生一个随机的整数，代表截取长度，1-（26-startIndex+1）
		
		INSERT into stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i = i+1;
END WHILE;

END$

call pro_while2(10)$