MySQL数据库基础篇
DDL 数据库操作语言
数据库操作
1.查询
查询所有数据库:
show database;
查询当前数据库:
select database();
2. 创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
3. 删除
drop database [if exists] 数据库名;
4.使用
use 数据库名;
表操作
1. 查询当前数据库所有表
show tables;
2. 查询表结果:
desc 表名;
3.查询指定表的建表语句:
show create table 表名;
4.表创建
create table 表名(
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释],
字段3 字段3类型 [comment 字段3注释],
...
字段n 字段n类型 [comment 字段n注释]
)[comment 表注释];
5.添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
6. 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
7. 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
8.删除字段
alter table 表名 drop 字段名;
9.修改表名
alter table 表名 rename to 新表名;
10. 删除表
drop table [if exists] 表名;
删除指定表,并重新创建该表(数据被清空了)
truncate table 表名;
DML 数据操作语言
1. 添加数据
insert into 表名(字段1,字段2,...) values(值1,值2,...), ..., (值1,值2,...);
2.修改数据
update 表名 set 字段1 = 值1, 字段2 = 值2 [where 条件];
3.删除数据
delete from 表名 [where 条件];
DQL 数据搜索语言
select
字段列表 [as] 别名
from
表名列表
where
条件列表 like ,between...and , in , and , or
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表 升序 asc, 降序 desc
limit
分页参数 起始索引(从0开始), 每页暂时记录数
DQL的执行顺序为:
- from
- where
- group by … having …
- select
- order by
- limit
DCL 数据控制语言
用来管理数据库用户, 控制数据库的访问权限。
DCL-用户管理
- 查询用户
USE mysql;
SELECT * FROM user;
- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
主机名: ‘localhost’ 本地主机可登录, ‘%’ 任意主机都可登录。
- 修改用户密码
ALTER user '用户名'@'主机名' IDENTIFIED WITH mysql_native_password by '新密码';
- 删除用户
DROP USER '用户名'@'主机名';
DCL-权限控制
- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
函数
字符串函数
1. concat(s1,s2,...,sn); 字符串拼接,将s1, s2,...sn 拼接成一个字符串
2. lower(str); 将字符串str全部转为小写
3. upper(str); 将字符串str全部转为大写
4. lpad(str, n, pad); 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
5. rpad(str, n, pad); 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度。
6. TRIM(str); 去掉字符串头部和尾部的空格。
7. substring(str, start, len); 返回从字符串str从start位置起的len个长度的字符串。
数值函数
1. ceil(x); 向上取整。
2. floor(x); 向下取整。
3. mod(x,y); 返回x/y的模。 x%y
4. rand(); 返回0~1内的随机数。
5. round(x,y); 求参数x的四舍五入的值,保留y为小数。
日期函数
1. curdate(); 返回当前日期。
2. curtime(); 返回当前时间。
3. now(); 返回当前日期和时间。
4. year(date); 获取指定date的年份。
5. month(date); 获取指定date的月份。
6. day(date); 获取指定date的日期。
7. date_add(date, INTERVAL expr type); 返回一个日期/时间值加上一个时间间隔expr后的时间值。
8. datediff(date1, date2); 返回起始时间date1 和 结束时间date2之间的天数。
流程控制函数
1. if(value, t, f); 如果value为true, 则返回t, 否则返回f。
2. ifnull(value1, value2); 如果value1不为空,返回value1, 否则返回value2。
3. case when [val1] then [res1] ... else [default] end; 如果val为true,返回res1,...否则返回default默认值。
4. case [expr] when [val1] then [res1] ...else [default] end; 如果expr的值等于val1, 返回res1, 否则返回default默认值。
约束
概念:约束是作用于表中字段上的规则, 用于限制存储在表中的数据。
- 目的: 保证数据中数据的正确、有效性和完整性。
- 分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保持数据时,如果未指定该字段的值,则采用默认值 | default |
检查约束 | 保证字段值满足某一个条件 | check |
外键约束 | 用来让两张表的数据之间建立连接, 保证数据的一致性和完整性 | foreign key |
外键约束
概念:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
- 添加外键:
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)
- 删除外键
alter table 表名 drop foreign key 外键名称;
多表查询
多表关系
- 一对多。 (多 加外键)
- 多对多。 (添加中间表 两个外键)
- 一对一。 (单表拆分,任意一方加入外键,关联另一方主键,并且设置外键为唯一)
多表查询概述
概述:指从多张表中查询数据。
笛卡尔积:笛卡尔积是指在数学中,两个集合 A集合 和 B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)。
连接查询
- 内连接:相当于查询A、B交集部分数据
隐式内连接:
select 字段列表 from 表1, 表2 where 条件...;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
- 外连接
- 左外连接:查询左表所有数据,以及两张表交集部分数据。
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
- 右外连接:查询右表所有数据,以及两张表交集部分数据。
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
- 自连接: 当前表与自身的连接查询,自连接必须使用表别名。
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
联合查询-union,union all
概念:对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
子查询
概念:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
select * from t1 where column1 = (select column1 from t2);
子查询外部的语句可以是 insert/update/delete/select 的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值) (常用 >,<, >=,<=,=)
- 列子查询(子查询结果为一列) (常用 in, not in, any, some, all)
- 行子查询(子查询结果为一行) (常用 =, <>, in, not in)
- 表子查询(子查询结果为多行多列) (常用 in)
根据子查询位置,分为:
- where之后
- from之后
- select之后
事务
概述:事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。(银行转账)
事务操作
- 查看/设置事务的提交方式
select @@autocommit; 1:自动提交 0:手动提交
set @@autocommit = 0;
- 提交事务
commit;
- 回滚事务
rollback;
- 开启事务
start transaction / begin;
事务四大特性
- 原子性 : 事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性 : 事务完成时,必须使所有的数据都保持一致状态。
- 隔离性 : 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性 : 事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
- 脏读 : 一个事务读到另一个事务还没提交的数据。
- 不可重复读 : 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
- 幻读 : 一个事务按照条件查询数据时, 没有对应的数据行,但是在插入数据时,又发现这个数据已经存在,好像出现了"幻影";
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read uncommitted | √ | √ | √ |
read committed | x | √ | √ |
repeatable read(默认) | x | x | √ |
serializable | x | x | x |
查看事务隔离级别:
select @@transaction_isolation;
设置事务隔离级别:
set [session|global] transaction isolation level 隔离级别;
事务隔离级别越高,数据越安全,但是性能越差。