MYSQL基础学习笔记

最新推荐文章于 2024-06-23 18:04:44 发布

深蓝海威

最新推荐文章于 2024-06-23 18:04:44 发布

阅读量458

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Mokyskury/article/details/93977295

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MYSQL基础学习笔记

文章目录

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子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式或函数用得较少
- 可以添加排序（排序方法整个分组查询的最后）

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

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 保存了数据增删改查

	创建语法的关键字	是否实际占用物理空间	使用
视图	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)$

深蓝海威

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL基础学习笔记

MYSQL基础学习笔记文章目录MYSQL基础学习笔记一、数据库的相关概念1、数据库的好处2、数据库的常见概念3、数据库存储数据的特点4、常见的管理系统二、DQL（查询语言）1、基础查询2、条件查询3、排序查询4、常见函数1、单行函数1、字符函数2、数学函数3、日期函数4、其他函数5、流程控制函数2、分组函数5、分组查询6、连接查询1、sql92标准1、等值连接2、非等值连接3、自连接2、sql9...
复制链接

扫一扫