MySQL常用操作
登录
mysql -uroot -p密码
DDL
查询
show databases; # 查询目前有哪几个数据库
show tables; # 查询当前数据库中有哪些表
desc tablename; # 查询某张表的结构 describe table
创建
create DATABASE 数据库名称；
create DATABASE if not exists 数据库名称; #创建之前先判断是否存在该数据库
create table 表名{
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
};
删除
drop DATABASE 数据库名称；
drop DATABASE if exists 数据库名称; #若存在该数据库则删除
使用
select database(); #展示当前所处的数据库
use DATABASE; #使用某一数据库
修改
alter table 表名 rename to 新的表名;
alter table 表名 add 列名 数据类型; # 添加一列
alter table 表名 modify 列名 新数据类型; # 修改数据类型
alter table 表名 change 列名 新列名 新数据类型; # 同时修改列名和列数据类型
alter table 表名 drop 列名; # 删除表中某一列
常用数据类型
double(总长度, 小数点后保留位数)
varchar(n) # 变长字符串
char(n) # 定长字符串, 效率比变长高
date # 日期 年-月-日
DML
insert
insert into 表名(列名1, 列名2, ...) values (值1, 值2, ...); # 给指定列添加数据
insert into 表名 values (值1, 值2, 值3, ...); # 添加一行数据到表中
# 批量添加
insert into 表名(列名1, 列名2, ...) values (值1, 值2, ...), values (值1, 值2, ...), values (值1, 值2, ...)...;
insert into 表名 values (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), (值1, 值2, 值3, ...)...;
update
update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where ...];
# 若update中没有加where条件，则会将表中所有数据全部修改!!!
delete
delete from tablename [where 条件];
DQL
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组后条件
order by
排序字段
limit
分页限定
-- distinct 去除重复记录
-- as 可以为查询结果的表项重命名
select math as 数学成绩 from stu;
-- where 中 != 和 <> 都表示不等号， and(&&) or(||)表示 与和
-- 比较null值需要使用 is null 或者 is not null
-- 不能使用= 或 !=
-- where age in (11, 22, 33) 筛选在该集合中的age
-- like模糊查询
-- _匹配单个任意字符, %匹配任意字符
-- 排序 order by
select 字段列表
from 表名
order by
排序字段名1 [排序方式1],
排序字段名2 [排序方式2],
...;
-- asc 升序排列(默认) ascend
-- desc 降序排列 descend
-- 分组查询 group by
-- 聚合函数 不统计null值
-- count(列名) 统计数量, count(*) 或者 count(主键)
-- max(列名) 最大值
-- min(列名) 最小值
-- sum(列名) 求和
-- avg(列名) 平均值
select 聚合函数 from table;
select 字段列表 from 表名 [where 分组条件限定] group by 分组字段名 [having 分组后条件过滤]
where和having的区别：
- 执行时机不一样：where用于分组前筛选，having对分组结果筛选；
- 可判断条件不一样，where不能对聚合函数进行判断，而having可以(having是对结果筛选的，知道聚合函数的结果，当然可以)
-- 分页查询 limit
select 字段列表 from 表名 limit 起始索引, 查询条目数;
-- 索引是从0开始的
-- 起始索引 = (当前页码 - 1) * 每页显示条目数;
约束
- 非空约束（not null）：保证列中所有数据不为空；
- 唯一约束（unique）：保证列中的所有数据各不相同；
- 主键约束（primary key）：主键是某一表项的唯一标识，要求非空且唯一；
- 检查约束（check）：保证某一属性的值满足某条件；
- 默认约束（default）：保存数据时，若未指定值则采用默认值；
- 外键约束（foreign key）：外键用来与其他表之间建立连接，用来保证数据的一致性和完整性；
注：MySQL不支持检查约束。
auto_increment 自增长
外键约束
-- 创建表的时候添加外键约束
create table name(
列名 数据类型,
...
[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);
-- 建完表之后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名称(主表列名);
-- 删除外键约束
alter table 表名 drop foreign key 外键名称;
如图表emp中的外键dep_id是表department主键id的外键。
外键会降低数据库性能，具体实践中不直接设置外键。
关系数据库通过外键可以实现一对多、多对多和一对一的关系。
具体可参考外键 - 廖雪峰的官方网站 (liaoxuefeng.com)
表关系实现
- 一对多实现方式：在多的一方建立外键，指向一的一方的主键。
- 多对多实现方式：建立第三张中间表，表中至少包含两个外键，分别关联两方主键。
可以看到，同一个商品可以对应多个订单；同一个订单可以包含多个商品。
-
一对一：多用于表的拆分，将常用信息属性与不常用信息属性分离，用于提升查询性能
实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为UNIQUE；
多表查询
笛卡尔积：取A、B集合所有组合情况
多表查询—连接查询
- 内连接：相当于查询A B交集数据
- 外连接
- 左外连接：查询A表所有数据和交集数据
- 右外连接：查询B表所有数据和交集数据
-- 内连接分为隐式、显式
-- 隐式
select t1.name, t1.gender t2.dname
from emp t1, dept t2
where t1.dep_id = t2.did;
-- 显式
select t1.name, t1.gender t2.dname
from emp t1 inner join dept t2 on t1.dep_id = t2.did; # inner可省略
-- 外连接
-- 左外连接
select 字段列表
from 表1 left [outer] join 表2
on 条件;
-- 右外连接
select 字段列表
from 表1 right [outer] join 表2
on 条件;
多表查询—子查询
查询中嵌套查询
select *
from emp
where salary > (select salary from emp where name = '猪八戒');
-- 分类
# 单行单列
select 字段列表 from 表 where 字段名 = (子查询);
# 多行单列
select 字段列表 from 表 where 字段名 in (子查询);
# 多行多列
select 字段列表 from (子查询) where 条件;
事务
- 事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令；
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败；
- 事务是一个不可分割的工作逻辑单元（类似操作系统的原语）。
-- 在mysql中建立如下表
CREATE TABLE account(
id int PRIMARY KEY auto_increment,
name VARCHAR(10),
money double(10,2)
);
insert into account(name, money) values('刘伟', 1000), ('微凉', 1000);
select * from account;
begin;
-- 1.查询刘伟的余额
-- 2.刘伟金额减500
update account set money = money - 500 where name = '刘伟';
在这里出现了异常... # 出现异常后最后回滚，数据库会回到事务执行前的状态
-- 3. 微凉金额加500
update account set money = money + 500 where name = '微凉';
rollback;
-- 若异常已经解决, 则用commit提交事务, 使得数据库中数据永久性更改
事务四大特征
ACID
- 原子性（atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
- 一致性（consistency）：事务完成时，必须使所有的数据都保持一只状态
- 隔离性（isolation）：多个事物之间，操作的可见性（隔离性越强，操作越不可见，性能越低）
- 持久性（durability）：事务一旦提交或者回滚，它对数据库中的数据的改变就是永久的
-- 查询事务的默认提交方式
-- mysql中默认自动提交, oracle手动提交
select @@autocommit # 结果为1说明是默认自动提交，即执行一条修改语句后自动提交, 相当于执行修改语句后系统自动为你执行了一条commit
-- 修改默认提交方式为手动提交
set @@autocommit = 0; # 这样的话你执行一条修改语句后不会立刻更新数据库中的数据, 只有你commit之后才会更新数据库