MYSQL使用及语法

下载安装mysql

略 $...$

进入mysql

- $root$ 为用户名，登录时请选择所需要的用户；
$quit|exit$ ，退出mysql；

创建用户并赋予权限

$create~user~test0@localhost~identified~by~'passvv0rd';$
- 创建用户 $test0@localhost$ ，密码为 $passvv0rd$ ，创建时填入你所需要的用户名和主机地址，我们默认使用 $localhost$ 为主机地址；
$grant~all~privileges\\~on~*.*~\\to~test0@localhost\\~[with~grant~option];$
- $with~grant~option$ ：是否可以把自己的权限赋予其他用户；
- 赋予全部权限给用户 $test0@localhost$ ，如果需要选择性的赋予权限， $all~privileges$ 需要被替换为下表中的值；

权限	说明
usage	仅登录，无任何操作权限
file	导入导出文件，可以执行 $select~...~into~outfile$ / $load~data~infile~...$
super	允许用户终止任何查询
select	拥有该权限才可以使用 $select~...~from~table$
insert	拥有该权限才可以使用 $insert~into~...~values~...$
update	拥有该权限才可以使用 $update~table~...~$
delete	拥有该权限才可以使用 $delete~from~...~where~...$
alter	拥有该权限才可以使用 $alter~table~...$
alter rountine	更改或删除函数或过程， $alter/drop~procedure/function$
create	拥有该权限才可以创建表
drop	拥有该权限才可以删除 $database,~table,~index,~view...$
create rountine	创建 $procedure/function$
create temporary tables	创建临时表
create view	创建视图
create user	创建用户
show database	如果没有这个权限，用户使用 $show~databases;$ 只能查看到自己的数据库，而不能查看mysql数据库
show view	拥有该权限才可以使用 $show~create~view$
index	拥有该权限才可以使用 $create/drop~index$
excute	拥有该权限才可以执行 $procedure/function$
event	执行 $event$ 相关语句
lock tables	为表加锁，用于互斥访问
references	拥有该权限才可以为表添加外键约束
reload	拥有该权限才可以使用 $flush~tables/logs/privileges$
replication client	查询 $master/server/slave~server$ 的状态
shutdown	关闭mysql
grant option	拥有该权限就可以将自己拥有的权限赋予其他用户
process	如果没有这个权限，用户使用 $show~processlist;/kill~...;$ 只能查看或终止自己的进程，而不能查看或终止全局进程
all privileges	以上所有权限

----刷新权限
- 使得操作权限跟内存中的一致，常用在修改用户权限后；

数据库操作

----查看数据库列表

$show~databases;$

----创建数据库

$create~database~db0;$

----删除数据库

$drop~database~db0;$

----删除表

$drop~table~tb0~[restrict|cascade];$

----重命名数据库

$alter~database~db0~rename~to~db1;$

----修改数据库所有权

$alter~database~db0~owner~to~test0@localhost;$

----修改数据库编码

$alter~database~db0~charset=utf-8;$

----列出数据库下的表

$show~tables;$

----查看当前数据库

$select~database();$

----导入数据库

$source~/dir0/db0.sql;$

----导出数据库（只导出表结构，不导出任何数据）

$dump~-u~user0~-p~--no-data~db0>db0\_bak.sql;$

$--no-create-info$ 是不添加 $create~table$ 语句

什么都不添加则导出全部数据库（可以直接用于 $source$ ）

表操作

----查看表的各个列的属性

$describe~table0;$

----创建表

$create~table~table0(\\c0~type~[constraint]\\c1~type~[constraint]\\...\\);$

$type$ 为数据类型

数值类型

类型	大小(Bytes)	用途
TINYINT	1	微小整数
SMALLINT	2	小整数
MEDIUMINT	3	中型整数
INT	4	一般整数
BIGINT	8	极大整数
FLOAT	4	单精度浮点数
DOUBLE	8	双精度浮点数
DECIMAL	(MAX{M, D} + 2)	精确到D位小数的P位数，例如ECIMAL(5, 2)可以表示的范围是[-999.99, 999.99]

日期和时间类型

类型	大小（Bytes）	格式
DATE	3	YYYY-MM-DD（固定10个字符）
TIME	3	HH:MM:SS（固定8个字符）
YEAR	1	YYYY（固定4个字符）
DATETIME	8	YYYY-MM-DD hh:mm:ss（固定19个字符）
TIMESTAMP	4	YYYY-MM-DD hh:mm:ss（固定19个字符）

DATETIME在输入后是一个固定的值，而TIMESTAMP会根据mysql的设定时区将值转化为相应的时间。使用 $set~timezone=...$ 设定客户端时区。

字符串类型

类型	大小（Bytes）	用途
CHAR	0-255	定长字符串
VARCHAR	0-65 535	变长字符串
TINYBLOB	0-255	不超过255个二进制字符串
TINYTEXT	0-255	短文本
BLOB	0-65 535	二进制长文本数据
TEXT	0-65 535	长文本数据
MEDIUMBLOB	0-16 777 215	二进制中长文本
MEDIUMTEXT	0-16 777 215	中长文本
LONGBLOB	0-4 294 967 295	极大二进制长文本
LONGTEXT	0-4 294 967 295	极大文本

CHAR类型用于存储定长字符串，mysql总是根据定义的长度分配足够的空间，当空间未完全使用时会采用空格进行填充；而VARCHAR类型需要使用1或2个额外字节记录字符串长度。

CHAR类型适合存储很短或长度近似的字符串，如MD5值，1个字符CHAR需要1个字节而VARCHAR需要2个字节。

$constraint$ 为约束类型

$NOT~NULL$ ：非空约束，要求该字段不能为空；
$DEFAULT$ ：默认约束，如果没有赋值则添加默认值，例如 $default~'value0'$ ；
$PRIMARY KEY$ ：主键约束，该字段要求唯一标志一行，可以写在数据后面也可以写在最后一行；
$UNNIQUE$ ：唯一约束，该字段要求唯一，可以有多个，可以没有，可以为空；
$CHECK$ ：检查约束，例如 $check(age>18)$ ；
$FOREIGN KEY$ ：外键约束，例如 $foreign~key(uid)~references~table1(sid)$ ，通常写在最后一行而非数据后面；
$AUTO\_INCREMENT$ ：自增一约束，要求数据类型只能是数值类型，且为NOT NULL；
$ON~UPDATE/DELETE$ ：更新约束，与FOREIGN KEY连用，被参照表更新时更新参照表，例如 $foreign~key~(uid)~references~table1(sid)~on~update~restrict|no~action|cascade|set~null$

$restrict$ 要求在被参照表删除/更新对应记录时需检查参照表是否有对应外键，如果有则不允许删除/更新；

$no~action$ ：同 $restrict$ ；

$cascade$ ：在被参照表中删除/更新对应记录时，如果参照表有对应外键则也要删除/更新；

$set~null$ ：在被参照表中删除/更新对应记录时，如果参照表有对应外键则设置为 $NULL$ ；

----导出表数据为 $ext$ 类型的文件

- 通常选择 $txt,~csv,~xml$ ；

----修改表

$alter~table~tb0\\add~newc0~type~constraint\\add~constraint\\drop~oldc0\\drop~constraint\\modify~c0~type;$
可以时添加新列，添加约束，删除列，删除约束，修改数据类型等

----创建索引， $[~~]$ 的内容可选

$create~[unique]~[cluster]~index~idx0~on~tb0(c0[ASC|DESC],~c1[ASC|DESC],~...);$
- $unique$ ：每个索引对应唯一条记录；
- $cluster$ ：索引项的顺序与表中记录的物理顺序对应；
- $ASC$ ：升序排列；
- $DESC$ ：降序排列；

----删除索引

$drop~index~idx0;$

----创建视图

$create~view~vw0~[(c0,~c1,~...)]\\as~select~...\\~[with~check~option];$
- 如果存在 $[(c0,~c1,~...)]$ ，要求一定在 $select$ 的列内；
- $with~check~option$ ：对视图进行 $insert$ 或 $update$ 时，如果新的行不满足 $select$ 语句则拒绝操作；

----删除视图

$drop~view~vw0~[restrict|cascade];$

----断言约束

$create~assertion~as0\\check(...);$
- 修改表时都会触发断言，如果不满足条件则拒绝；
- mysql并不支持 $create~assertion$ 语句，可以用 $create~trigger$ 替代；

----更新表

$update~tb0\\set~co=newv0~where~...\\set~constraint~...;$

----插入记录

$insert~into~tb0~(c0,~c1,~...)\\values(v0,~v1,~...);$

$insert~into~tb0~(c0,~c1,~...)\\select~...;$
- 要求上下两个个数要对应，值与列一一对应；

中没有指定的列，默认为；
- 插入子查询语句要求 $select$ 出的列与 $(c0,~c1,~...)$ 一一对应；

----删除整行记录

$delete~from~tb0~[where~...];$
- 如果没有 $where$ 语句，则删除整个表的记录（表依然存在）；

----查询语句

$select~[all|distinct]~c0,~c1,...\\from~tb0\\~[where~...]\\~[group~by~c0~[having~...]]\\~[order~by~c0];$

$all$ ：缺省值，显示所有的列；
$distinct$ ：消除重复列，判断条件是 $c0,~c1,~...$ 都不相等；
$*$ 可以代替表示所有列；
$c0~as~newname$ ：可以在列的后面使用 $as$ 创建别名；
$where$ 子句运算符

运算符描述

$=$ 等于

$<>$ 或 $!=$ 不等于

$>$ 大于

$<$ 小于

$>=$ 大于等于

$<=$ 小于等于

$[not]~between...and...$ 在某个范围内（包含端点）

$like/regexp\_like$ 字符匹配

$in$ 集合属于关系

$is~[not]~null$ 是否为空
$is~null$ 不能写成 $~=~null$ ；

运算符	描述
$=$	等于
$<>$ 或 $!=$	不等于
$>$	大于
$<$	小于
$>=$	大于等于
$<=$	小于等于
$[not]~between...and...$	在某个范围内（包含端点）
$like/regexp\_like$	字符匹配
$in$	集合属于关系
$is~[not]~null$	是否为空

两种模式匹配 $like/regexp\_like()$

模式功能

$\_$ （下划线）匹配单个字符

$\%$ （百分号）匹配任意数量的字符

例如： $select~*~from~pet~where~name~like~'\_b\%';$ 是匹配name中以 $b$ 为第二个字符的记录；

如果字符串本身含有 $\_$ 或 $\%$ 则需要使用 $/\_$ 或 $/\%$ 来表示，使用关键字 $escape$ 转义。例如 $select~*~from~users~where~username~like~'\%/\_\%'~escape~'/';$

模式	功能
$\_$ （下划线）	匹配单个字符
$\%$ （百分号）	匹配任意数量的字符

模式	功能
$.$	匹配单个字符
$[abc]$	匹配 $[~]$ 内的任意一个
$[a-z]$	匹配 $a-z$ 的任意一个，大小写不敏感
$[0-9]$	匹配 $0-9$ 的任意一个
$a*$	要求字符串以 $n(n=0,~1,~2,~...)$ 个 $a$ 连续，比如 $'a',~'aa',~'aaa',~...$
^	表示字符串开头
$	表示字符串结尾

^和$需要配合上面的模式使用，例如： $select~*~from~pet~where~regexp\_like(name,$ '^b...[c-e]$' $);$ 要求匹配以b开头，以c或d或e结尾且长度为5的字符串。

$order~by~c0~[ASC|DESC],~[c1~[ASC|DESC]],~...$ ：
1. 先按 $c0$ 排序，再按 $c1$ 排序，...；
2. $order~by$ 子句必须是 $select$ 语句的最后一个子句；
3. $order~by~一个数字$ 可以用来测试表中含有多少列，当数字大于列数时， $SQL$ 查询会报错；

$group~by~c0,~[c1,~...][having~...]$ ：

先按 $c0$ 取值相同的分组，再按 $c1$ 取值相同的分组，...；
$having$ 子句约束哪些组出现在最终的查询结果中；
$having$ 子句中使用的列名必须出现在 $group~by$ 中，或者是集合函数；

集合函数

名称	参数类型	结果类型	描述
COUNT	任意	数值类型	计数
SUM	数值类型	数值类型	求和
AVG	数值类型	数值类型	平均值
MAX	字符型或数值类型	同参数类型	最大值
MIN	字符型或数值类型	同参数类型	最小值

----连接查询

$select~...\\from~tb0[as~t0]\\~[[inner]~join|left~join|right~join]~tb1[as~t1]\\on~tb0.c0=tb1.c0|using~c0;$
- 对于 $tb0,~tb1$ 可能产生歧义时，使用 $as$ 起别名；
- $inner~join$ ：内连接，可省略 $inner$ ，连接条件是 $tb0.c0=tb1.0$ ；
- $inner~join$ 也可以用 $where~tb0.c0=tb1.c0$ 替代；
- $left~join$ ：左连接，左表的全部记录都包括，右表匹配则包括否则填 $NULL$ ；
- $right~join$ ：右连接，右表的全部记录都包括，左表匹配则包括否则填 $NULL$ ；
- $on$ 和 $using$ 可以相互替换，使用 $using$ 时要求两个表的对应列名相同；

----子查询

subquery
- 也是 $select$ 语句，一般出现在 $where,~having,~insert,~update,~delete$ 子句中；
- 一般使用于 $in,~some|any,~all,~[not]~exist$ 谓词后；
- 相关子查询指子查询中使用到了父查询表中的列；
- $where$ 中将列与集合函数进行判断，例如工资大于平均工资，个人生产量高于 $20\%$ 的总生成量等，不能直接使用 $where~salary~>~AVG(salary)$ ，需要使用子查询：
  
  $where~salary~>\\~(select~AVG(salary)\\~from~staff);$
- $order~by$ 子句不能用于子查询；

----量化比较谓词

- $all$ ：缺省值，子查询结果全部满足；
- $some|any$ ：可以混用，要求子查询结果至少要满足一个，即大于子查询集中的最小值（以大于条件为例）；
- $=~all(...)$ 与 $in$ 含义相同， $<>~all(...)$ 与 $not~in$ 含义相同；

----（不）存在谓词

$[not]~exists~(select~...);$
- $exists$ 为真，当且仅当子查询返回一个非空集合；
- $not~exists$ 为真，当且仅当子查询返回一个空集合；

----联合查询

$select~...~union~[all]~select~...$
- 两个 $select$ 查询的列必须相同，但是它们可以来自不同的表；
- 查询结果会将两个结果合并为一个（因为它们查询列相同所以可以合并）；
- $all$ ：不去重；

----交集查询

$select~...~interselect~[all]~select~...$
- 两个 $select$ 语句的要求和 $union$ 一样；
- 查询结果返回二者的交集；
- $all$ ：不去重；
  
  例如Q1查询结果为: $\{a,~a,~a,~b,~b,~c,~d\}$ ，Q2查询结果为： $\{a,~a,~b,~b,~b,~c,~e\}$ ：
  
  $Q1~interselect~all~Q2$ ： $\{a,~a,~b,~b,~c\}$ ；
  
  $Q1~interselect~Q2$ ： $\{a,~b,~c\}$ ；

----差集查询

$select~...except~[all]~select~...$
- 要求同 $union$ ；
- 查询返回左减右的差集；
- $all$ ：不去重；
例如Q1查询结果为: $\{a,~a,~a,~b,~b,~c,~d\}$ ，Q2查询结果为： $\{a,~a,~b,~b,~b,~c,~e\}$ ：

$Q1~except~all~Q2$ ： $\{a,~d\}$ ；Q2存在几个则在Q1删去几个

$Q1~except~Q2$ ： $\{d\}$ ；Q2存在的值则Q1全部删除

----子查询结果当作表使用

$select~...\\from~(select~...)tn\\where~...;$
- $tn$ 为子查询结果所构成表的名字；

函数，存储过程与触发器

----修改终止符号

- mysql默认以 $';'$ 结尾；

----查看系统变量

- $like$ 用于模式匹配，选择需要查看的系统变量；

----调用过程

$call~procedure(param0,~...);$

----创建过程

$delimiter~\#\#\\drop~procedure~if~exists~pc0\#\#\\create~procedure~pc0(in~param0~type,...)\\begin\\select~...\\end~\#\#\\delimiter~;$
- 存储过程是一个包装的 $select$ 语句；
- $in$ ：输入参数，参数可以用于 $select$ 语句判断条件中；

----调用函数

$select~fc0(param0,~...);$

----查看函数/过程信息

$show~function|procedure~status~[like~func0];$

可以使用 $like$ 模糊匹配要查看的名称；

信息名	描述
db	所在数据库
name	名称
type	类型（function/procedure）
definer	创建用户
modified	最后一次修改的时间（YY-mm-dd hh-mm-ss）
created	创建时间（YY-mm-dd hh-mm-ss）
security type	安全等级
comment	自定义描述
character_set_client	函数内容编码
collation_connection	会话规则
database collation	数据库规则

----查看函数内容

$show~create~function~func0;$

----创建函数

$delimiter~\#\#\\drop~function~if~exists~fc0~\#\#\\create~function~fc0(param0~type,~...)~returns~type\\begin\\declare~var0~type~[constraint];\\...\\set~@uvar0=...;\\...\\return~...\\end\#\#\\delimiter~;$
- $declare~var0~type~[constraint];$ ----声明函数内部变量/局部变量
- $declare$ 语句必须放在 $begin~end$ 函数体最前面；
- $set$ ----直接为变量赋值
- $if(...)~then\\...\\ [else~if(...) then]\\ [...]\\...\\ [else]\\ [...]\\end if;$
  
  ----if-else语句
  - mysql中逻辑等于是 $'='$ ，而不是 $'=='$ ；
  - 只有在 $set$ 赋值语句中 $'='$ 才表示赋值，为避免混淆可以使用 $':='$ 表示赋值；
- $[label0:]while~(...)~do\\...\\end~while~[label0];$
  
  ----while语句
  - $label0:$ ：可以在 $while$ 前加上标签，如果有标签 $end~while$ 也要有相应的；
- $leave~label;\\iterate~label;$
  
  ----break，continue语句
- $select~...\\into~var0[,~...]\\from...\\...$
  
  ----通过 $select$ 语句为变量赋值
- $set~@uvar=~...;$ ----声明用户变量
  - 用户变量为用户级全局变量，可以在指定用户会话内多个函数间使用；
  - $@@gvar$ ：系统变量，全部用户都可以访问的全局变量；
- 使用 $select~@uvar|@@gvar;$ 可以查看相应值；

----创建触发器

- $after$ ：在相应操作之后进行，说明该操作已经修改了表；
- $before$ ：在相应操作之前进行，该操作还没有真正修改表；
- $insert|update|delete$ ：只有三种sql操作可以使用触发器；
- $for~each~row$ ：对所有行都生效，不能省略；
- 触发器主体可以使用函数中的语法，例如 $set,~declare$ 。同时触发器中使用两个关键字标志被修改的那一行 $new,~old$ ；
- $new，~old$ 视为变量，对于 $insert$ 触发器只有 $new$ 是合法的，对于 $delete$ 触发器只有 $old$ 是合法的，对于 $update$ 触发器 $new,~old$ 都是合法的；