title: MySQL数据库基础整理笔记
一、基础概念
数据库的概念
- DB 数据库(database):存储数据的“仓库”。他保存了一系列有组织的数据。
- DBMS 数据库管理系统(Database Manegement System):数据库是通过DBMS创建和操作的容器。常见的数据库管理系统:MySQL 、Oracle、DB2、SqlServlet。
- SQL 结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。几乎所有的DBMS都支持SQL。
- DBA 数据库管理员
数据库的特点
- 数据先存放在表中,表再存放到库中。
- 一个数据库可以对应多张表,每个表都有一个自己的名字。用来标识自己。表名具有唯一性。
- 表具有一些特性,这些特性定义了数据库在表中如何存储,类似java中类的设计。
- 表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似jiava中的“属性”。
- 表中的数据是按行存储的,每一行类似于jiava中的“对象”。
MySQL的优点
- 成本低:开放源代码,一般可以免费试用。
- 性能高:执行很快。
- 体积小,便于安装。
DBMS分为两类
- 基于共享文件系统的DBMS(Access)
- 基于客户机——服务器的DBMS(MySQL、 Oracle、SqlServer)
如何干净的卸载MySQL
- 通过电脑卸载软件(电脑管家)或者在控制面板——程序里面搜索到mysql右键卸载。
- 找到mysql的安装目录并且删除。
- 在C盘下面找到ProgramData目录,找到MySQL并删除。
- 如果上面还不行就清理注册列表信息
- HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录
- HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servics\Eventlog\Application\MySQL目录
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl001\Services\MYSQL 目录
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl002\Services\MYSQL 目录
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MYSQL 目录
二、MySQL的常规操作
MySQL服务的启动和停止
- 以管理员身份打开doc命令窗口
- 先打开“服务”找到MySQL80服务设置为手动
- 使用命令
net stop mysql80
停止服务 - 使用命令
net start mysql80
开启服务
MySQL服务端的登录和退出
- 登录之前必须保证服务是启动状态。
- 登录:输入命令
mysql -h localhost -P 3306 -u root -p
来登录MySQL。其中 -h表示主机名,-p表示端口号,-u表示用户,-p表示密码。(输完-p可以直接回车也可以输入密码后回车。) - 如果直接连接本机的MySQL可以直接输入命令
mysql -u root -p
- 退出:输入命令
exit
MySQL的常用命令
- 查看数据库有哪些
show databases;
- mysql 用来保存用户信息的数据库
- information_schema 保存一些元数据信息
- performance_schema 搜集一些性能信息 ,性能参数
- 进入某一个数据库:
use mysql;
会有提示信息Database changed意思是打开了该数据库。- 查看该数据库的表命令:
show tables;
- 查看该数据库的表命令:
- 查看一个指定数据库中的表:
show tables from book;
- 查看当前处于哪一个数据库中:
select database();
- 创建一个表:
create table 表名();
- 查看表结构:
desc 表名;
- 往表中插入数据:
insert into newbook(id,name) values(2,'rose');
- 修改表中的数据:
update newbook set name='wang' where id=2;
- 查看表中的数据:
select * from newbook;
- 删除表中的数据:
delete from newbook where id=2;
- 查看当前数据库版本:
select version();
- 在没有启动MySQL服务的状态下查看版本:
mysql --version
MySQL的语法规范
- 不区分大小写。但建议我们关键字大写。
- 每条命令用";“或者”\g"结尾。
- 每条命令根据需要可以进行缩进或换行。
- 注释
- 单行注释: #注释文字
- 单行注释: – 注释文字
- 多行注释: /* 注释文字 */
三、SQL语句
(1)SQL语言的分类
-
数据查询语言(Data Query Language, DQL)
查询语句,凡是select语句都是DQL。 -
数据操纵语言DML(Data Manipulation Language, DML)
数据操纵语言DML主要有三种形式:
(1) 插入:INSERT
(2) 更新:UPDATE
(3) 删除:DELETE -
数据定义语言(Data Definition Language, DDL)
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等:DDL操作是隐性提交的!不能rollback
-
数据控制语言DCL(control)
数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。grant授权、revlke撤销权限等。 -
事务控制语言TCL(transaction)
commit提交事务,rollback回滚事务。
(2)数据查询
SELECT语句的执行顺序:
1、FORM: 对FROM左边的表和右边的表计算笛卡尔积,产生虚表VT1。
2、ON: 对虚表VT1进行ON过滤,只有那些符合的行才会被记录在虚表VT2中。
3、JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3。
4、WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
5、GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5。
6、HAVING: 对虚拟表VT5应用having过滤,只有符合的记录才会被 插入到虚拟表VT6中。
7、SELECT: 执行select操作,选择指定的列,插入到虚拟表VT7中。
8、DISTINCT: 对VT7中的记录进行去重。产生虚拟表VT8.
9、ORDER BY: 将虚拟表VT8中的记录按照<order_by_list>进行排序操作,产生虚拟表VT9.
10、LIMIT:取出指定行的记录,产生虚拟表VT10, 并将结果返回。
基础查询
-
语法:select 查询列表 from 表名;
-
特点:1、查询列表可以是:表中字段、常量值、表达式、函数(查询寻对象为函数时,相当于返回函数的执行结果)
2、查询的结果是一个虚拟的表格。 -
例:查询表中的某个字段 SELECT 字段 FROM employees;
查询表中的多个字段 SELECT 字段1,字段2 FROM employees; 查询表中的所有字段 SELECT * FROM employees;
查询常量值:SELECT 常量;
查询表达式:表达式;
查询函数:函数名;
-
着重号 " ` " 使用,可以区分字段和关键字。
-
起别名:方式一: SELECT 字段 AS 别名; 方式二:SELECT 字段 别名;
作用:(1)便于理解
(2)如果要查询的字段有重名的情况,使用别名可以区分开来。
(3)MySQL中取别名之后,该sql语句中就只能使用该别名。注意点:如果别名中有特殊符号或者关键字要使用双引号 " "
-
去重:即在查询的字段名名前面加上DISTINCT关键字。注:不可以有多个字段。
例:SELECT DISTINCT 字段名 FROM 表名;
-
“+”的作用 : 在mysql中“+”只能做运算。运算双方如果有一方为数字另一方为字符就会把字符尝试转为数字,转换成功进行相加。转换失败则字符转换成0。运算双方如果有一方为null则结果肯定为null。
-
字段拼接:使用cancat()函数
例:SELECT CONCAT(字段1,字段2) AS 别名 FROM 表名;
注意:字段为null的字段和任何字段拼接的结果都是null;
-
判断为空的函数 IFNULL(参数一:可能为空的字段或表达式,参数二:如果为空转换成的字符)
例:SELECT IFNULL(参数一,参数二) AS 别名 FROM 表名;
补充:isnull(字段或表达式,如果是null则为1,如果不是则为0)
条件查询
-
语法:SELECT 查询列表 FROM 表名 WHERE 筛选条件
-
分类:
-
按条件表达式筛选 条件运算符:> ,< , =, !=(<>), >=, <=
- 例1:SELECT * FROM employees WHERE salary>12000;
- 例2:SELECT CONCAT(first_name,last_name),employee_id
AS “name”
FROM employees
WHERE employee_id != 90;
-
按逻辑表达式筛选 逻辑运算符:And , or, not
- 作用:用于连接条件表达式
- 例1:SELECT last_name,salary,hiredate
FROM employees
WHERE salary>10000 AND salary<20000; - 例2:SELECT *
FROM employees
WHERE NOT job_id=“IT” AND salary=12000;
-
模糊查询: like, between and, in, is null,is not null ,<=>:安全等于
-
模糊匹配:like “%为通配符 表示匹配任意多个字符,包含0个字符,不能匹配null”
“—为通配符 表示匹配任意单个字符”
“\转义字符”
在需要使用转义字符的地方后面加上 escape 【字符】指定该字符为转义字符
like不仅可以操作字符,也可以操作数字。
-
例1 :SELECT *
FROM employees
WHERE last_name LIKE ‘%a%’; -
例2:SELECT *
FROM employees
WHERE first_name LIKE ‘_*_s%’ ESCAPE ‘*’; -
例3 :SELECT *
FROM employees
WHERE employee_id BETWEEN 10 AND 100; #注:and两边的值不能颠倒 -
例4:SELECT *
FROM employees
WHERE job_id IN(‘IT_PROT’,‘AD_VP’); #(IN列表的值必须一致或者兼容) -
例5:SELECT *
FROM employees
WHERE commission_pct is NULL;
-
-
排序查询
-
语法:
SELECT 查询列表
FROM 表
WHERE 筛选条件
ORDER BY 排序列【ASC升序 , DESC降序】
-
注意点:
- 如不指明是asc和desc的话,那么默认是asc。
- order by 子句中可以支持单个字段,多个字段,表达式,函数,别名。
- order by 子句一般是放在查询语句的最后面。limit子句除外。
-
例1:SELECT *
FROM t_book
WHERE author is NOT null
ORDER BY id ASC; -
例2:字节长度函数 LENGTH字段)
SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC; -
例3:双重排序,先按某个排序,再按照另一个排
SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC, salary ASC;
常见函数的学习
- 函数的概念:将我们实现功能的一组逻辑语句封装到一个方法中,对外暴露方法名。隐藏了实现细节,提高了代码的重用性。
- 如何调用函数:SECLECT 函数名();如果后面用到表中的字段可以加from。
- 分类:
- 单行函数:做处理并返回值 如:concat(),length(),ifnull()等
- 分组函数:做统计使用(聚合函数)
- 字符函数
- length() 统计参数的字节个数。(utf-8:一个中文三个字节)
- concat() 用于拼接字符串
- upper()把小写参数变大写
- lower()把大写参数变小写
- substr(参数,索引开始,长度) 截取从某个索引开始长度为(长度)的字符。注:MySQL中索引从1开始。如果只有前两个参数,则截取从索引位置到结束的长度。
- instr(参数1,参数2) 用来返回参数2在参数1中首次出现的位置。
- trim() 去参数前后空格
- trim(‘a’ from 参数)去掉参数前后的a
- lpad(‘字符串’,10,‘*’)用指定的字符左填充,填充后的总字符长度为10.
- rpad右填充。
- replace(‘字符串1’,‘字符串2’,‘字符串3’) 用字符串3去替换字符串1中的字符串2.
- 数学函数
- round(1.567,2)四舍五入结果是1.57
- ceil()向上取整,返回大于等于该参数的最想整数。
- floor()向下取整。
- truncate(1.65,1) 截断 结果是1.6
- mod() 取余
- rand()获取随机数 返回0-1之间的小数。
- 日期函数
- now() 返回当前系统日期+时间
- curdate()返回当前系统日期不包含时间
- curtime()返回当前系统时间,不包含日期
- year/month/day(now())可以获取指定的部分,年、月、日、小时、分钟、秒
- datediff(expr1,expr2):计算两个日期差多少天。
- str_to_ date():将日期格式的字符转换成指定格式的日期
- 例:str_to _date(‘9-13-1999’,’%m-%d-%y’)
- date_format():将日期转换成字符
- 例:date_format(‘2018/6/6’,’%y年%m月%d日’)
- SELECT DATE_FORMAT(‘2018/6/6’,’%m月%d日%y年’);
SELECT STR_TO_DATE(‘08-04-2021’,’%m-%d-%Y’); - 格式如下:
序号 | 格式符 | 功能 |
---|---|---|
1 | %Y | 四位的年份 |
2 | %y | 两位的年份 |
3 | %m | 月份(01,02…) |
4 | %c | 月份(1,2,3…) |
5 | %d | 日(01,02…) |
6 | %H | 小时(24时制) |
7 | %h | 小时(12时制) |
8 | %i | 分钟(00,01…) |
9 | %s | 秒(00,01…) |
-
其他函数
- version()查看版本号
- database() 查看当前所处的数据库
- user() 当前用户
- md5(‘字符’):返回该字符的密码形式
-
流程控制函数
-
if(表达式,“参数1”,“参数2”) 实现类似于三元运算法的效果
-
case()函数 类似switch 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;
- 方式二:
SELECT 字段,
CASE
WHEN 条件1 then 要显示的值1或语句
WHEN 条件2 then 要显示的值2或语句
else 要显示的值n或语句
end
FROM 表;- 例:
SELECT salary AS 原始工资, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END 工资级别 FROM employees;
-
-
分组函数(聚合函数,统计函数)
- sum() 求和 一般只支持数值型 忽略null值
- avg() 平均值 一般只支持数值型 忽略null值
- max() 最大值 支持数值型,字符型,日期型 忽略null值
- min() 最小值 支持数值型,字符型,日期型 忽略null值
- count() 计算非空个数 支持所有类型 忽略null值
注:上面的函数可以和distinct搭配
select 中只能有组函数和分组字段,如果包含其他字段就会报错的(或者而知只出现一个。)
例:SELECT max(DISTINCT salary)
FROM employees-
count()函数的单独介绍
(1)COUNT(*) 统计表中总的行数。
(2)COUNT(任何常量值) 统计表中总的行数。
分组查询 group by
-
语法:select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】 -
特点:(1)分组查询中的筛选条件分为两类
分组前筛选: 数据源:原始表
分组后筛选:数据源:分组后的结果集合 (2)group by 后面可以跟表达式或函数分组。
(3) 可以按多个字段分组,多个字段用“,”隔开。
```mysql select max(salary),job_id FROM employees GROUP BY job_id ORDER BY max(salary); ```
-
添加分组后的筛选条件
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
连接查询(多表查询)
-
sql92语法:
内连接
-
等值连接 :连接条件“等于”
注意点:一旦给表取别名之后,select里面的字段必须用“别名.字段”
可以加筛选条件,使用AND连接
n表连接至少需要n-1个连接条件 多表的顺序没有要求,一般需要为表起别名。
例:
SELECT COUNT(*) 个数,city FROM departments d,locations l WHERE d.location_id=l.location_id AND GROUP BY city
-
非等值连接 连接条件“除了等于其他情况”
例:查询员工的工资和工资级别
SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.lowest_sal
-
自连接 相当于等值连接,只不过自连接只有自己和自己连接
例:查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.manager_id,m.last_name FROM employees e,employees m WHERE e.manager_id=m.employee_id;
-
-
sql99语法:
-
语法:
select 查询列表
from 表1 别名
【连接类型】 join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】 -
内连接 连接类型:inner
-
等值连接
-
案例1:查询员工名、部门名、工种名,并按部门名降序
SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d ON e.department_id=d.department_id INNER JOIN jobs j ON e.job_id=j.job_id ORDER BY department_name DESC;
-
-
非等值连接
-
案例:查询工资级别大于2的个数,并且按工资降序排序
SELECT COUNT(*),grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level;
-
-
自连接
- 案例:略
-
-
外连接
应用场景:查询一个表中有,另一个表没有的记录。
特点:外连接的查询结果为主表中的所有记录
如果从表中有和他匹配的,则显示匹配的值,如果从表中没有和他匹配的,则显示null。
外连接查询结果=内连接结果+主表中有而从表中没有的记录。
左外连接中left join左边的是主表,右外连接right join右边的是主表
左外和右外交换两个表的顺序,可以实现同样的效果。-
左外连接 连接类型:left【outer】
-
例:查询男朋友不在男神表的女神名
SELECT b.name,bo.* FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id=bo.id;
-
-
右外连接 连接类型:right【outer】
-
全外连接 连接类型:full【outer】MySQL不支持。
- 全外连接=内连接的结果+表一中有表二中没有的+表二中有但表一没有的。
-
-
交叉连接 连接类型:cross【outer】
- 结果就是迪卡尔乘积。跟92语法中的“,”迪卡尔乘积一样。
-
子查询
注意一般先执行子查询然后再执行主查询,但相关子查询除外。
-
含义:出现在其他语句中的select语句,称为子查询或内查询
-
分类
-
按子查询出现的位置分类
-
select后面 (相当于对该列数据处理)
-
仅仅支持标量子查询
-
例:查询每个部门的员工个数
SELECT d.* ,( SELECT COUNT(*) FROM employees e WHERE e.department_id=d.department_id ) 个数 FROM departments d;
-
-
from后面
- 表子查询
-
where或having后面
-
标量子查询√
-
列子查询√
-
行子查询
-
例:返回job_id与141号员工相同,salary比143号员工多的员工姓名、job_id和工资。
先查141号员工的job_id
ELECT job_id FROM employees WHERE employee_id=141;
再查143号员工的工资
SELECT salary FROM employees WHERE employee_id=143;
最后查满足题目要求的内容
SELECT last_name,job_id,salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id=141 注:这个地方不能加“;” ) AND salary>( SELECT salary FROM employees WHERE employee_id=143 );
-
-
exists后面(相关子查询)
exists作用:返回0或者1 一般用在where后面。
-
-
表子查询
-
按结果集的行列数不同:
-
标量子查询(结果集只有一行一列)
-
列子查询(结果集只有一列多行)操作符有 IN/NOT IN , ANY|SOME , ALL
-
行子查询(结果集有一行多列)
例:
SELECT * FROM employees WHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees )
-
表子查询(结果集有多行多列)
-
分页查询
-
语法:
select 查询列表 from 表 【join type join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段】 limit offset ,size;
注:offset要显示条目的起始索引(从0开始) size要显示的条目个数字
-
特点:limit语句放在查询语句的最后
联合查询 union
-
将多条查询语句的结果,合并成为一个结果。
-
语法:
查询语句1
union
查询语句2
-
注意事项:
- 可以查询不同表的信息,但是列数必须相同。
- 联合查询,最后也会有去重效果。如果不想去重就使用union all。
(3)数据的增删改 (DML语言 )
数据的插入 insert
-
方式一:
语法:insert into 表名(列名…)values(值…);
要求:
插入的值的类型要与列的类型一致或兼容。不可以为null的列必须插入值
插入的值必须和列名一一对应 且列数和值的个数必须一致。
我们可以省略列名,默认是所有列。而且列的顺序和表中列的顺序一致。例子:
INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'郑真真','女','1998-08-04','110','null',5);
方式二:
语法:insert into 表名 set 列名=值,列名=值…
例:
INSERT INTO beauty SET id=15,name="张飞",phone="1234"
两种方式比较:
方式一支持一次插入多行数据值: values(),(),()
方式一支持子查询,方式二不支持。
修改语句 update
-
修改单表的记录
语法:
update 表名
set 列=新值,列=新值…
where 筛选条件例:
UPDATE beauty SET name="关羽" WHERE `name`="张飞"
-
修改多表记录
语法:
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,...... where 筛选条件
删除数据
-
方式一:delete 该方式删除的可以回滚
单表的删除:语法:delete from 表名 where 筛选条件
例:
DELETE FROM beauty WHERE phone LIKE '%9';
可以搭配“limit 删除条目数”使用,表示删除记录的个数。
多表的删除:
语法:delete 删除哪个表的记录就在这里跟上哪个表名(可以加别名)
from 表名1 别名
inner|left|right join 表2 别名
where 筛选条件。 -
方式二:truncate 清空表中的数据 该方式删除的不可以回滚
语法:truncate table 表名
(4)数据定义语言(DDL)
库和表的管理
-
库的管理
-
创建(create)
语法:create database 库名;
在database后面可以加上if not exists 判断库是否存在,如果存在则不创建,如果不存在则创建。 -
修改(alter)
一般不修改库名。如果要改的话直接改文件夹修改文件名。
ALTER DATABASE 库名 CHARACTER SET 字符集名称; -
删除(drop)
drop database 库名;
在database后面可以加上if t exists 判断库是否存在,如果存在则删除,如果不存在不操作。
-
-
表的管理
-
创建(create)
格式:create table 表名(
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】, )
注:可以加存在处理 if not exists
-
修改(alter)
-
修改列名
格式:alter table 表名 change column 旧列名 新列名 类型
-
修改列的类型或者约束
格式:alter table 表名 modify column 列名 新类型
-
添加列
格式:alter table 表名 add column 新列名 类型
添加到指定列的后面:在语句最后加上【first | after 字段名 】
-
删除列
格式:alter table 表名 drop column 列名
-
修改表名
格式:alter table 表名 rename to 新表名
-
-
删除(drop)
- 语法:drop table 表名;可以加存在处理 if exists
-
-
通用的建库和建表写法
Drop database/table if exitsts 旧库名/表名;
create database/table
-
表的复制
- 方式一:仅仅复制表的结构 :create table 新表 like 旧表名;
- 方式二:复制表的结构和数据:create table 新表 select * from 旧表
- 方式三:仅仅复制部分表的结构 :create table 新表 select 部分列名 from 旧表 where 0;
常见数据类型
-
数值型
整型
特点:如何设置有符号和无符号:默认是有符号的,设置无符号的在数据类型后面加上unsigned
如果插入数据超出整型范围,会报出警告,并且会插入整型上限。
如果不设置长度会有默认长度。当实际的值的长度 小于 (M)时,可以使用zerofill属性,表示用0填充,让0从左侧填满长度,如果没有zerofill,(M)就是无用的。整数类型 字节 范围 Tinyint 1 有符号:-128到127 无符号:0到255 Smallint 2 有符号:-32768到32767 无符号: 0到65535 Mediumint 3 有符号:-8388608到8388607 Int、Integer 4 有符号:-2147483648到2147483647 Bigint 8 很大 小数:定点数和浮点数
特点:M代表整数部位外加小数部位的总长度,D代表小数部位的长度。
M和D其实都可以省略,如果试dec则M,D默认为(10,0)如果是float和double则会根据插入的数据的精度来决定精度。
定点型的精度较高,如果要求插入数值的精度较高,如货币运算就考虑使用定点型。浮点数类型 字节 范围 float(M,D) 4 double(M,D) 8 定点数类型 字节 范围 DEC(M,D) DECIMAL(M,D) M+2 最大的取值范围与double相同给定的有效取值范围由m和b决定。 -
字符型
较短的文本:char、varchar
-
char(M):M的意思是最大的字符数,char是固定长度的字符,比较耗费空间,性能稍微高一点
-
varchar()M:M的意思是最大的字符数 varchar是可变长度的字符,计较节省空间
-
对于char型,M指定了不超过char允许的范围内的该字段的字符数,存储的时候必然按M个字符进行存储,不够的时候用空格补。在检索的时候(我理解是查询的时候),取出的值尾部空格被删除,存储的时候尾部的空格可以认为没有做处理。
-
对于varchar型,M指定了不超过varchar允许的字符数的最大字符数为M,存储的时候以实际长度进行存储但这个长度不能超过M。然后与char的区别在于,不会在写入和读出的时候,对字符串的尾部空格进行删除。
-
binary和varbinary类型:类似于char和varchar,不同的是他们包含二进制字符串而不包含非二进制字符串。
较长的文本:text、blob(较长二进制数据)
-
-
日期型
日期和时间类型 字节 最小值 最大值 date 4 1000-01-01 9999-12-31 datetime 8 1000-01-01 00:00:00 9999-12-12 23:59:59 timestamp 4 1970 01 01 08:00: 01 2038年的某个时刻 time 3 -838:59:59 838:59:59 year 1 1901 2155 -
enum枚举类型
要求插入的值必须属于列表中指定的值之一。
例:CREATE table emp3(
id ENUM(‘a’,‘b’)
) -
Set集合类型
和enum类型类似,里面可以保存0~64个成员。和enum类型最大的区别是:set类型一次可以选取多个成员,而Enum只能选一个,根据成员个数不同,存储所占的字节也不同
常见约束
-
含义:一种限制,用于限制表中的数据,为了保证最终添加到表中的数据的准确性和可靠性。
-
六大约束
- not null :非空约束 用于保证该字段的值不能为空
- default :默认约束 用于保证该字段的值有默认值
- primary key:主键约束 用于保证该字段的值具有唯一性,并且非空
- unique:唯一约束 用于保证该字段的值具有唯一性,可以为空。
- check:检查约束【MySQL不支持】
- foreign key:外键约束 用于限制两个表的关系。用于保证该字段值必须来自于主表的关联列的值。在从表中添加外键约束,用于引用主表中某列的值。
-
添加约束的时机:(1)创建表时 (2) 修改表时
-
约束的添加分类:
-
列级约束 六大约束语法上都支持,但外键约束没有效果
例:
create table yueshu( id INT PRIMARY KEY, stuName VARCHAR(20) NOT NULL, gender CHAR(1) CHECK(gender='男' OR gender='女'), seat INT UNIQUE, age INT DEFAULT 10 )
-
表级约束 除了非空、默认、其他的都支持。
语法:在各个字段的最下面
【constraint 约束名】 约束类型(字段名)例:
create table yueshu( id INT , stuName VARCHAR(20) , gender CHAR(1) , seat INT, age INT , majorid INT, CONSTRAINT ys PRIMARY KEY(id), CONSTRAINT uq UNIQUE(seat), CONSTRAINT ck CHECK(gender='男'), CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)#外键 )
注:上面的ys、uq之类的为#约束名(自己起) 约束名不可以重复且可以不写。
-
-
主键和唯一的大对比
主键:保证唯一性,但不可以为空,一个表中只能有一个主键。允许多个列组合成为一个主键。例:CONSTRAINT ys PRIMARY KEY(字段1,字段2…),
唯一键:保证唯一性,可以为空,一个表中可以有多个。组合唯一键也允许同上。 -
外键的特点
- 要求在从表上设置外键关系
- 从表外键列的类型和主表的对应列的类型要求一致或者兼容。名称没有要求。
- 主表中的关联列必须是一个键,一般指主键和唯一键
- 插入数据时,应该先插入主表再插入从表。删除数据时先删除从表,再删除主表。
-
修改表时添加约束
- 添加非空约束 alter table 表名 modify column 数据类型 约束not null
- 添加默认约束 alter table 表名 modify column 数据类型 约束default
- 添加主键约束 alter table 表名 modify column 数据类型 约束primary key
- 添加主键约束 alter table 表名 add primary key(列名)
- 添加唯一约束 alter table 表名 modify column 数据类型 约束unique
- 添加唯一约束 alter table 表名 add unique(列名)
- 添加外键约束 alter table 表名 add foreing key(列名) references 表名(列名)
-
修改表时删除约束
- 删除约束 alter table 表名 modify column 数据类型(直接不添加约束);
- 常规写法: alter table 表名 drop 约束 列名;
- 删除主键的非常规写法 alter table 表名 drop primary key;(其他的约束删除要按正常写法来写。
标识列(自增长列)
- 可以不用手动的插入值,系统提供默认的序列值
- 在定义列时,在约束后面添加关键字 auto_increment
- 插入数据格式:insert into zeng VALUES(null,“wang”)
- show variables like ‘%auto_increment%’; 查看自增长的步长和偏移量(起始值)可以通过set auto_increment_increment=值 设置步长,但是mysql不能设置偏移量
- 标识列必须和一个键搭配使用。
- 仅仅只能有一个自增长列
- 标识列的类型只能是数值型
- 修改表时设置标识列(遵循修改表的语法)
四、TCL语言(事务控制语言)
-
事务:一个或一组sql语句组成一个执行单元,这个单元要么全部执行,要么全部不执行。
-
查看存储引擎命令:show ENGINES
-
事务的特性:
- 原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务的操作要么都发生,要么都不发生。
- 一致性:(consistency):事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
- 隔离性(Isolation):事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
- 持久性(Durability) 持久性是指一个事务一旦被提交,他对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。
-
事务的创建
- 隐式事务:事务没有明显的开启和结束的标记,比如insert,update、delete语句
- 显式事务:事务具有明显的开启和结束标记。前提:必须先设置自动提交功能为禁用。
- 禁用自动提交功能:set autocommit=0 查看状态:show variables like ‘autocommit’; 打开自动提价功能:set autocommit=1 禁用只对当前事务有效。
- 开始一个事务的步骤
- 步骤一:开启事务
set autocommit=0; 对当前的整个连接都有效果;
或:start transaction; 可选的 - 步骤二:编写事务中的sql语句(CURD)
- 步骤三:结束事务
commit;提交事务
rollback:回滚事务
- 步骤一:开启事务
-
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取隔离机制,就会导致各种并发问题。
-
脏读:对于两个事务t1和t2,t1读取了已经被t2更新但还没有被提交的字段之后,若t2回滚,t1读取的内容就是临时且无效的。
-
不可重复读::对于两个事务t1和t2,t1读取了一个字段,然后t2更新了该字段之后,t1再次读取同一个字段,值就不同了。
-
幻读:两个事务读取同一个表,事务一对表进行插入修改之后,事务二再次读取该表会发现数据不同。
- 注:银行A开启了一个事务窗口,查询当前系统中有没有"wangwu"用户,发现没有,银行B也开启了一个事务窗口,查询当前系统中也没有"wangwu"用户,银行A先创建"wangwu"用户并且提交,由于可重复读取,银行B在一次事务中必须保证查询的数据一致性,因此查询不到"wangwu",结果银行B窗口认为wangwu没有被注册想注册"wangwu"用户,就创建"wangwu"用户结果发现系统提示"wangwu"用户已经被注册",但是在本次事务中又查询不到"wangwu",就好像出现幻觉一样
-
-
数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使他们不会相互影响,避免各种并发的问题。
-
一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱。
-
MySQL有四种事务隔离级别,默认事务隔离级别是REPEATABLE READ
- READ UNCOMMITTED(读未提交数据):允许事务读取未被其他事务提交的变更。脏读、不可重复读和幻读的问题都会出现。
- READ COMMITED(读已提交数据):只允许事务读取已经被其他事务提交的变更。可以避免脏读,但不可重复读和幻读问题仍可能出现。
- REPEATABLE READ(可重复读):确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新,可以避免脏读和不可重复读。但幻读的问题仍然存在。
- SERIALIZABLE(串行化):确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作,所有并发问题都可以避免,但性能十分低下。
- 查看事务各级级别的命令:select @@transaction_isolation;
- 设置事务的隔离级别:set session transaction_isolation level 隔离级别 (同样只针对当前连接有效果。)
-
savepoint 节点名:设置保存点。
- 格式:savepoint a(保存点名)
- 作用:事务回滚到该保存点。
五、视图
-
含义:虚拟表,和普通表一样。行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。
-
创建视图的语法:create view 视图名 as 查询语句
-
例:
CREATE VIEW v1 as SELECT last_name,department_name,job_title FROM employees e inner JOIN departments d ON e.department_id=d.department_id inner JOIN jobs j ON j.job_id=e.job_id;
-
-
使用视图和正常的查询方式一样。
-
视图可以嵌套
-
视图使用的好处
(1)重用sql语句
(2)简化复杂的sql操作,不必知道它的查询细节
(3)保护数据,提高安全性 -
视图的修改
- 格式一: create or replace view 视图名 as 查询语句
- 格式二:alter view 视图名 as 查询语句
-
删除视图
- 语法:drop view 视图名1,视图名2…
-
查看视图
- 语法一:desc 视图名
-
视图的更新(一般无法使用)
- 数据的插入,同时也会把数据插入到原始表
- 数据的更新,同时也会把数据更改到原始表
- 数据的删除,同时也会把原始表中的数据删除。
注:包含以下关键字的sql语句:分组函数、distinct、group by、having 、union或者union all 不能进行视图更新。常量查询不能进行视图更新。select中包含子查询的不能进行视图更新。
六、变量
-
系统变量
-
全局变量:变量是由系统提供的,不是用户自己定义的,属于服务器层面。作用域:服务器每次启动将为所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启。
-
会话变量:作用域:仅仅针对当前会话(连接)有效
使用语法:
1、查看所有的系统变量:show global | session variables
2、查看满足条件的部分系统变量:show global | session variables like ’ ‘;
3、查看指定的某个系统变量的值:select @@global | 【session】.系统变量名
4、为某个系统变量赋值:set global | 【session】 系统变量名 = 值;注意:如果是全局级别则需要加global,如果是会话级别,则需要加session,如果不写,则默认session。
-
-
自定义变量:变量是用户自己定义的,不是系统的
-
用户变量:作用域:针对于当前会话连接有效,同于会话变量的作用域
- 使用:1、声明并初始化 set @用户变量名=值;或set @用户变量名:=值;或select @用户变量名:=值;
- 更新用户变量的值:
- 方式一: set @用户变量名=值;或set @用户变量名:=值;或select @用户变量名:=值;
- 方式二:select 字段 into @变量名 from 表
- 查看用户变量值:select @用户变量名;
- 应用在任何地方。
-
局部变量:作用域:仅仅在定义它的begin end中有效。应用在begin end中第一句话
-
声明:declare 变量名 类型;或赋初始化:declare 变量名 类型 default 值;
-
赋值:
- 方式一: set 局部变量名=值;或set 局部变量名:=值;或select @变量名:=值;
- 方式二:select 字段 into 局部变量名 from 表
-
使用:select 局部变量名;
-
例:
set @n=1; set @b:=2; SELECT @c:=3; SELECT @n; SELECT @b; SELECT @c;
-
-
七、存储过程和函数
-
存储过程和函数:类似于java中的方法
-
好处:
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率。
-
存储过程:一组预先编译好的sql语句的集合,理解成批处理语句。
-
应用场景:适合做批量的插入、批量的更新操作。
-
存储过程的语法:
-
创建:
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end1、注意:参数列表包含三部分分别为:参数模式、参数名 参数类型
参数模式:- in:修饰的参数可以作为输入(输入值)
- out:修饰的参数可以作为输出(返回值)
- inout:修饰的参数既可以作为输入又可以作为输出
2、如果存储过程体仅仅只有一句话,begin end可以省略。存储过程体中的每条sql语句的结尾必须加分号。存储过程的结尾可以使用DELIMITER重新设置结束标志,语法:DELIMITER 结束标记。
3、例1:空参列表,插入到admin表中五条记录。
DELIMITER $ CREATE PROCEDURE myp3() BEGIN INSERT into admin(username,`password`) VALUES('joinn1','0000'),('joinn2','0001'), ('joinn3','0002'),('joinn4','0003'), ('joinn5','0004'); END $
例2:有参列表,创建存储过程实现,根据女神名,查询对应的男神信息
CREATE PROCEDURE mp1(IN beautyName VARCHAR(20)) BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.`name`=beautyName; END $
例3:#创建存储过程实现,用户是否登陆成功
CREATE PROCEDURE p1(IN username VARCHAR(20),IN `password` VARCHAR(20)) BEGIN DECLARE result int(20) DEFAULT '0';#声明并初始化 SELECT COUNT(*) INTO result #赋值 FROM admin WHERE admin.username=username AND admin.`password`=`password`; SELECT if(result>0,'成功','失败');#使用 END $
例4:创建带out模式的存储过程
CREATE PROCEDURE p6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyName INTO boyName FROM boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.`name`=beautyName; END
例5:创建带有多个out模式的存储过程
CREATE PROCEDURE p6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT) BEGIN SELECT bo.boyName,bo.userCP INTO boyName,userCP FROM boys bo INNER JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.`name`=beautyName; END
例6:创建带inout模式参数的存储过程
CREATE PROCEDURE p7(inout a int ,inout b int) BEGIN SET a=a*2; SET b=b*2; END
注:DELIMITER $ 这个设置只对命令行有效(cmd),且设置之后,所有的结束标志都会是这个符号。
-
调用:call 存储过程名(实参列表)
-
例1:调用无参:call myp1()$
-
例2:调用有参:call mp1(‘王晨’)$
-
例3:调用带两个参数的:call p1(‘john’,‘8888’) $
-
例4:调用带out模式的存储过程:
set @bName;#这个可以省略不写 CALL p6('柳岩',@bName); SELECT @bName;
-
例5:调用带多个out模式的存储过程:
set @bName; set @userCP; CALL p6('柳岩',@bName,@userCP); SELECT@bName,@userCP;
-
例6:调用带inout模式的存储过程:
set @c=3,@d=4; CALL p7(@c,@d); SELECT @c,@d;
-
-
-
存储过程的删除:drop procedure 存储过程名; 一次只能删除一个。
-
查看存储过程的信息:show create procedure 存储过程名。
-
可以修改存储过程,但是没有必要。
-
函数:一组预先编译好的sql语句的集合,理解成批处理语句。
- 函数的好处:提高代码的重用性、简化操作、减少了编译次数、并且减少了和数据库服务器的连接次数、提高了效率。
- 函数和存储过程的区别:必须只能有一返回。
- 函数的应用场景:适合做处理数据后返回一个结果。
- 函数创建语法:
create function 函数名(参数列表) returns 返回类型
begin
函数体
end 注意:参数列表包含两部分:参数名 参数类型。
函数体肯定会有return语句,如果没有会报错。
如果return语句没有放在函数体的最后也不报错,但不建议。
当函数体重只有一句话可以省略begin end。例1:#1、有参无返回 返回公司的员工个数
CREATE FUNCTION fun() RETURNS INT BEGIN DECLARE c INT DEFAULT 0; #定义变量 SELECT COUNT(*) INTO c #赋值 FROM employees; RETURN c; END
例2:有参有返回 根据员工名返回其工资
CREATE FUNCTION fun1(empName VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE b DOUBLE DEFAULT 0; SELECT salary INTO b FROM employees WHERE last_name=empName; RETURN b; END
例3:创建函数,实现传入两个float,返回二者之和。
CREATE FUNCTION fun3(f1 FLOAT,f2 FLOAT) RETURNS FLOAT BEGIN DECLARE f3 FLOAT DEFAULT 0; SELECT f1+f2 INTO f3; RETURN f3; END
- 调用语法:select 函数名(参数列表)
例1调用无参:SELECT fun();
例2调用有参且有返回值:SELECT fun1(‘k_ing’);
例3调用:SELECT fun3(1.1,1.2)- 查看函数:show create function 函数名;
- 删除函数:drop function 函数名;
八、流程控制结构
-
分支结构:程序从两条或多条路径中选择一条去执行。
1、if函数:实现简单的双分支
- 语法:if (表达式1,表达式2,表达式3)
- 执行顺序:如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值。
- 应用:任何地方。
2、case结构:一般用于实现等值判断。
-
方式一:类似java中的switch
case 变量|表达式|字段
when 要判断的值 then 返回的值1或语句1;
when 要判断的值 then 返回的值2或语句2;
…
else 要返回的值n或语句;
end -
方式二:类似java中的多重if语句,一般用于区间判断
case
when 要判断的条件 then 返回的值1或语句1;
when 要判断的条件 then 返回的值2或语句2;
…
else 要返回的值n或语句;
end -
特点:可以作为表达式,嵌套在其他语句中使用,可以放在任何地方。也可以作为独立的语句使用,只能放在begin end中。(后面要加“ ;”)
案例:创建存储过程,根据传入的成绩,来显示等级,比如传入的是90-100,显示A,80-90显示B,60-80显示c,否则显示D
CREATE PROCEDURE test(IN score INT) BEGIN CASE WHEN score BETWEEN 90 AND 100 THEN SELECT 'A'; WHEN score BETWEEN 80 AND 90 THEN SELECT 'B'; WHEN score BETWEEN 60 AND 80 THEN SELECT 'C'; ELSE SELECT 'D'; END CASE; END
3、if结构:实现多重分支
-
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
【else 语句n】
end if; -
应用:应用在begin and中
-
案例:创建函数,根据传入的成绩,来显示等级,比如传入的是90-100,返回A,80-90返回B,60-80返回c,否则返回D
CREATE FUNCTION test1(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
-
顺序结构:程序从上往下依次执行。(正常就是顺序结构)
-
循环结构:程序在满足一定条件的基础上,重复执行一段代码。
-
while
-
语法:
【标签】:while 循环条件 do
循环体;
end while【标签】; -
例1:批量插入,根据次数插入到admin表中多条记录
drop PROCEDURE pro_while; CREATE PROCEDURE pro_while(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i<=insertcount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666'); SET i=i+1; END WHILE a; END
例2:(添加leave语句)批量插入,根据次数插入到admin表中多条记录,如果次数大于20则停止。
CREATE PROCEDURE pro_while(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i<=insertcount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('ru',i),'666'); if i>=20 THEN LEAVE a; END IF; SET i=i+1; END WHILE a; END
例3:#(添加interate语句)批量插入,根据次数插入到admin表中多条记录,只插入偶数次。
CREATE PROCEDURE pro_while(IN insertcount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<=insertcount DO SET i=i+1; IF i%2!=0 THEN iterate a ; END IF; INSERT INTO admin(username,`password`) VALUES(CONCAT('ru',i),'666'); END WHILE a; END
-
-
loop
-
语法:
【标签:】 loop
循环体;
end loop 【标签】;注:可以用来模拟简单的死循环
-
-
repeat
-
语法:
【标签:】 repeat
循环体;
until 结束循环的条件
end repeat【标签】;
-
- 循环控制
- iterate 类似于continue 结束本次循环继续下一次
- leave 类似于braak,跳出,结束当前所在的循环
-
T)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertcount DO
INSERT INTO admin(username,password
) VALUES(CONCAT(‘rose’,i),‘666’);
SET i=i+1;
END WHILE a;
END
```
例2:(添加leave语句)批量插入,根据次数插入到admin表中多条记录,如果次数大于20则停止。
```mysql
CREATE PROCEDURE pro_while(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertcount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('ru',i),'666');
if i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END
```
例3:#(添加interate语句)批量插入,根据次数插入到admin表中多条记录,只插入偶数次。
```mysql
CREATE PROCEDURE pro_while(IN insertcount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertcount DO
SET i=i+1;
IF i%2!=0 THEN iterate a ;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('ru',i),'666');
END WHILE a;
END
```
-
loop
-
语法:
【标签:】 loop
循环体;
end loop 【标签】;注:可以用来模拟简单的死循环
-
-
repeat
-
语法:
【标签:】 repeat
循环体;
until 结束循环的条件
end repeat【标签】;
-
- 循环控制
- iterate 类似于continue 结束本次循环继续下一次
- leave 类似于braak,跳出,结束当前所在的循环