MySQL基础

MySQL服务启动

在 运行 窗口输入

services.msc

打开系统服务，找到MySql（此为在安装SQL过程中自定义名）
或在终端中

# 启动
net start mysql80
# 停止
net stop mysql80

客户端连接

在终端中

mysql [-h 127.0.0.1] [-P 3306] -u root -p

-h 及 -P 分别为指定IP及端口，可忽略，将按照默认执行

DDL(数据定义语言)

查询

查询所有数据库

show databases;

查询当前数据库

select database();

创建

create database [if not exit] name [default charset 字符集] [collate 排序规则]

[...] 代表可忽略

删除

drop database [if exit] name;

使用

use name

表查询

查询当前数据库所有表

show tables;

查询表结构

desc name;

查询指定表的建表语句

show create table name;

表创建

create table name(
    字段1 字段1类型 [comment 字段1注释]，
    字段2 字段2类型 [comment 字段2注释]，
    字段3 字段3类型 [comment 字段3注释]，
    ...
    字段n 字段n类型 [comment 字段n注释]
)[comment 表注释];

数据类型

数值类型

tinyint     -- 1 byte
smallint    -- 2 bytes
mediumint   -- 3 bytes
int/integer -- 4 bytes
bigint      -- 8 bytes
float       -- 4 bytes
double      -- 8 bytes
decimal     -- 依赖于精度和标度的值

精度为整个数据的长度，标度为小数位数
可以在后面加上 unsigned 表示无符号

例如

score double(4, 1) -- 标度为4，精度为1

字符串类型

char        -- 定长字符串
varchar     -- 变长字符串
tinyblob
tinytext
blob
text
mediumblob
mediumtext
longblob
lobgtext

~`char 为字符串\ ~blob 为二进制数据\ ~text` 为文本数据

日期时间

date        -- YYYY-MM-DD
time        -- HH:MM:SS
year        -- YYYY
datetime    -- YYYY-MM-DD HH:MM:SS
timestamp   -- YYYY-MM-DD HH:MM:SS

但 timestamp 范围只到 2038-01-19 03:14:07

表操作

添加

alter table 表名 add 字段名 类型（长度） [comment 注释] [约束];

修改数据类型

alter table 表名 modify 字段名 新数据类型（长度）;

修改字段名及字段类型

alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

例如将emp表中nickname字段修改为username，类型为varchar(30)
执行为alter table emp change nickname username varchar(30) comment '用户名';

删除字段

alter table 表名 drop 字段名;

修改表名

alter table 表名 rename to 新表名;

删除表

drop table [if exist] 表名;
# 删除指定表 并重新创建该表
truncate table 表名

删除表时表中数据也会被删除

DML(数据操作语言)

添加数据

# 给指定字段添加数据
insert into 表名 (字段1, 字段2, ...) values (value1, value2);
# 给全部字段添加数据
insert into 表名 values (value1， value2, ...);
# 批量添加数据
insert into 表名 (字段1, 字段2, ...) values (value1, value2);
insert into 表名 values (value1， value2, ...), (value1， value2, ...), ...;

修改数据

update 表名 set 字段名1=值1, 字段名2=值2, ... [where 条件];

删除数据

delete from 表名 [where 条件]

没有 where 表名处理所有数据

DQL(数据查询语言)

语法

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段列表
having
    分组后条件列表
order by
    排序字段列表
limit
    分页参数

基本查询

查询多个字段

select 字段1, 字段2, 字段3 ... from 表名;
select * from 表名;

* 表示所有

设置别名

select 字段1 [as 别名1], 字段2 [as 别名2] ... from 表名;

取别名时 as 可以省略

取出重复记录

select distinct 字段列表 from 表名;

条件查询

select 字段列表 from 表名 where 条件列表;

条件有：

聚合函数

将一列数据作为一个整体进行纵向计算

常见聚合函数
count max min avg sum

select 聚合函数(字段列表) from 表名;

null 不参与计算

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

执行顺序为 where > 聚合函数 > having

排序查询

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式：
asc：升序（默认）
desc：降序
先按照字段1排序，若排序结果一样再按照字段2排序

分页查询

select 字段列表 from 表名 limit 起始索引, 查询记录数;

起始索引从0开始，起始索引=（查询页码-1）*每页数据记录数

DCL(数据控制语言)

用于管理数据库用户、控制数据库的访问权限

用户管理

查询用户

use mysql;
select * from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

修改用户密码

create user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户

drop user '用户名'@'主机名';

权限控制

常用权限有

查询权限

show grant for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数

函数是指一段可以直接被另一端程序调用的程序或代码

select 函数(参数);

字符串函数

substring 中索引起始为1

数值函数

日期函数

流程函数

约束

外键约束

外加用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

建立外键

create table 表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);

-- 或者
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名)

删除外键

alter table 表名 drop constraint 外键名称

删除/更新行为

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update cascade on delete cascade

on update 和 on delete 分别表示在更新和删除时的行为
cascade 为行为对应上表

多表查询

多表关系

一对多 多对多 一对一

多表查询概述

笛卡尔积

笛卡尔积是指在数学中集合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 条件 ...;

outer 可以省略

自链接

当前表与自身的连接查询，子链接必须使用表别名

select 字段列表 from 表a 别名a join 表a 别名b on 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

联合查询

将多次查询的结果进行合并，作为一次查询的结果

select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...;

有 all 会进行去重
对于联合查询的多张表的列数必须保持一致字段类型也需保持一致

子查询

sql语句中嵌套 select 语句称为嵌套查询又称子查询

select * from t1 where column1 = (select column from t2);

外部可以是增删改查中的任何语句

标量子查询

返回结果为单个值

常用操作符 = <> > >= < <=

列子查询

返回结果为一列

常用操作符 in not in any some all

行子查询

返回结果为一行（或多行）

常用操作符 = <> in not in

表子查询

返回结果为多行多列

常用操作符 in

事务

事务是操作的集合，是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作
这些操作只能同时成功或同时失败

事务操作

查看/设置事务提交方式

select @@autucommit;
set @@autocommit=0;

开启事务

start transaction;
-- 或
start begin;

提交事务

commit;

回归事务

rollback;

emample1

以张三给李四转账一千元为例

select @@autucommit;
set @@autocommit=0; -- 设置为手动提交

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

update account set money = money + 1000 where name = '李四';

在执行完语句后，由于设定为手动提交，执行后不会自动提交修改数据

在此之后执行

commit;

进行提交，数据修改

当程序出错例如

select @@autucommit;
set @@autocommit=0; -- 设置为手动提交

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

（模拟程序出错）。。。

update account set money = money + 1000 where name = '李四';

commit;

rollback;

若程序无报错，执行commit提交，若报错，执行rollback回滚

example2

start transaction; -- 开启事务

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

（模拟程序出错）。。。

update account set money = money + 1000 where name = '李四';

事务四大特性

原子性

事务是不可分割的最小操作单元，要么全部成功，要么全部失败

一致性

事务完成时必须使所有数据保持一致状态

隔离性

数据库系统提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性

事务一旦提交或回滚，对数据库中的改变就是永久的

事务并发问题

脏读

一个事务督导另一个事务还没提交的数据

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同

幻读

一个事务按照条件查询数据时，没有对应的数据行，但在插入数据，又发现这行数据已经存在

事务隔离级别

从上到下数据安全性越来越高性能越来越低

查看事务隔离级别

select @@transaction_isolation;

设置事务隔离级别

set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}