MySQL指令大全（超详细）！

SQL语言的分类

---

DQL（数据查询语言）

select关键字组合顺序

• 格式

  select
  	...
  from
  	...
  where
  	...
  group by
  	...
  having
  	...
  order by
  	...
  limit
  	...
  

  PS：以上关键字对顺序不能颠倒！

• 执行关键字顺序

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by
  7. limit

select简单查询用法

• 语法格式

  select 字段1，字段2、、、 from 表名

• 用 as（可省略） 字符给查询中的字段起别名：

  • select 字段 as 别名 from 表名

  • select 字段 别名 from 表名

• 若别名中有空格还想省略 as 字符来起别名：

  select 字段 '别名' from 表名

• select中的字段可以使用数学表达式，其显示结果为运算表达式后的值：

  select 字段 +、-、*、/ 数字 from 表名

select条件查询

• 语法格式

  select
  字段1、字段2、字段3、、、
  from
  表名
  where
  条件运算符;
  

• 条件运算符：

  查询条件	谓词
  比较	=, >, <, >=, <=, !=, <>, !>, !<
  确定范围	between and, not between and
  确定集合	in, not in
  字符匹配	like, not like
  空值	is null, is not null
  多重条件（逻辑运算）	and, or, not

  • and优先级比or高，and和or同时出现时，先执行and

  • like为模糊查询

    • % ： 代表多个字符

    • _ ： 代表一个字符

    • 例子：

      select
      	ename
      from
      	emp
      where
      	ename like '_A%'；
      

      意思为在emp表中寻找ename第二个字母为A的元素

    • 若查询的元素中带有 % 或 _ ，则在该字符前方使用转译字符 \

      • 例子：

        select ename from emp where ename like '%\_%'

• select排序

  • 语法格式

    select
    	字段1、字段2、、、
    from
    	表名
    order by
    	字段1 排序参数；
    

    若排序参数为空时默认升序排序！

  • 降序排序在排序参数后增加 desc 字符

    • 例 select * from emp order by sal desc;

  • 手动升序排序在排序参数后增加 asc 字符

    • 例 select * from emp order by sal asc;

  • 同时排序多个字段格式

    • 格式1

      select
      	字段1、字段2、、、
      from
      	表名
      order by
      	字段1 排序参数1，子段2 排序参数2、、、、；
      

      先判读 字段1 排序，若 字段1 相同则按照 字段2 排序！

    • 格式2

      select 字段1、字段2 from 表名 order by 2;

      最后的 2 是指代前面的 字段2

select函数使用

• 单行处理函数

  函数名称	函数意义
  lower(字段)	转换小写
  upper(字段)	转换大写
  substr(字段,起始下标,截取长度)	取子串
  concat(字段1,字段2)	字段拼接
  length(字段)	字段长度
  trim(字符串/字段)	去除字符串/字段前后空格
  round(字段/字面值,保留位数)	四舍五入
  rand()	生成0到1之间的随机数
  ifnull(字段数据,被指定的值)	将字段中的NULL数据转为被指定的值（NULL数据经过任何数据运算都只会是NULL）

  • 特殊单行处理函数

    case 字段 when 事件1 then 处理1 when 事件2 then 处理2 else 剩余处理 end

    • 解释：在case如果 when，则 then，当所有 when then 执行完成的其余情况则 else，最后以 end 结尾

    • 例如：

      select
      	ename, job,
      	(case job when 'MANAGER' then sal * 1.1 when 'SALESMAN' then sal * 1.5 else sal end)
      from
      	emp;
      

      • 解释：当job是MANAGER时sal提升1.1倍，当job是SALESMAN时sal提升1.5倍，其余sal不变

• 多行处理函数（分组函数）

  函数名称	函数意义
  count(字段)	计数
  sum(字段)	求和
  avg(字段)	求平均值
  max(字段)	求最大值
  min(字段)	求最小值

  PS：多行处理函数需要先对数据进行分组，若无分组，则默认整表为一组！

  PSS：多行处理函数会自动忽略NULL！

• 分组查询

  • 格式

    select
    	...
    from
    	...
    group by
    	...
    having //可省略
    	...
    

    PS：在一条select语句当中，若出现group by，则select后面只能跟参加分组的字段或分组函数，其余字段一律不能跟。

    例如：从 emp(员工) 表中找出各个 job(部门) 的分别总 sal(薪资)？

    select
    	job, sum(sal)
    from
    	emp
    group by
    	job;
    

  • having 可用于分组后的进一步筛选，但必须跟在 group by 后！

  • 优化策略：能用where完成的，先用where，否则再用having。

• 去除重复记录使用 distinct

  • 格式

    select distinct 字段1、字段2、、 from 表;

    PS：distinct只能出现在所有字段前方，若身后跟着多个字段，则联合去重！

select多表连接查询

• 格式

  select
  	...
  from
  	表1
  join
  	表2
  on
  	表1和表2的连接条件	
  join
  	表3
  on
  	表1和表3的连接条件
  ...
  

  PS：可以给表起别名！

  select
  	e.ename, d.dname	//用表别名进行操作
  from
  	emp e
  join
  	dept d	//给表起别名
  on
  	e.deptno = d.deptno;	//用表别名进行操作
  

• 多表连接之左外连接

  • 格式

    select
    	字段1、字段2、、
    from
    	表1
    left join
    	表2
    on
    	条件
    

    • 左连接会保留 left join 左端表（表1）的所有数据

• 多表连接之右外连接

  • 格式

    select
    	字段1、字段2、、
    from
    	表1
    right join
    	表2
    on
    	条件
    

    • 右连接会保留 right join 右端表（表2）的所有数据

select子查询

• 格式

select
	//可嵌套select子查询
from
	//可嵌套select子查询
where
	//可嵌套select子查询

子查询在select内的以上三处位置均可嵌入

Union结果集合并

• 格式

select 字段1、字段2、、 from 表1
union
select 字段1、字段2、、 from 表2

PS：可将两表查询出来的结果合并成一个表，但是两表所合并的字段数要一致（select和from之间的字段数量）

Limit分页显示

• 格式

limit startIndex, length;

PS：startIndex是起始下标，默认从0开始，length为步长！

limit在order by后使用！

• 例如：

• 显示排名薪资在第3到5名的员工姓名和薪资

  select
  	ename, sal
  from
  	emp
  order by
  	sal desc
  limit
  	2, 3;
  

• 分页公式

• limit (pageNo - 1) * pageSize, pageSize;

  解释：pageNo为页码值，pageSize为一页多少条记录。

---

DDL语句（数据定义语句）

数据库的创建

# 创建数据库
CREATE DATABASE hsp_db01;
# 若不输入字符集和校对规则的话，默认采用utf8字符集和utf8_general_ci（不区分大小写）校对规则

# 删除数据库
DROP DATABASE hsp_db01;

# 创建一个使用utf8字符集的数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8;

# 创建一个使用utf8字符集，并带校对规则的的数据库
CREATE DATABASE testdb03 CHARACTER set utf8 COLLATE utf8_bin;

表的创建

• 语法格式

  create table 表名(
  	字段名1 数据类型 default 默认值,
    字段名2 数据类型 default 默认值,
    字段名3 数据类型 default 默认值
  );
  

  PS：建议以 t_ 或者 tb_ 开始，可读性强。default省略默认值为NULL

• 数据类型

  数据类型	解释
  varchar	可变长度字符串（最长255）
  char	定长字符串（最长255）
  int	数字整数型（最长11）
  bigint	数字长整型
  float	单精度浮点型
  double	双精度浮点型
  date	短日期类型
  datetime	长日期类型
  clob	字符大对象（超过255字符，小于4G）
  blob	二进制大对象（存储图片、声音、视频流媒体数据）

• 

表操作

• 

查询当前所操作的数据库

• select database();

删除数据库

• drop database 数据库名;

DML语句（数据操作语句）

查看数据库信息

# 查看当前存在的所有数据库
SHOW DATABASES;

# 查看指定数据库的定义信息
SHOW CREATE DATABASE hsp_db02

备份与恢复数据库

# 备份testdb03和hsp_db02数据库（需在DOS窗口下执行）
mysqldump -u root -p -B testdb03 hsp_db02 > /Users/gottenzzp/Documents/mysql备份/bak.sql

DROP DATABASE testdb03

# 恢复数据库（需在Mysql命令行下执行）
source /Users/gottenzzp/Documents/mysql备份/bak.sql

# 备份数据库中的表
mysqldump -u root -p testdb03 users > /Users/gottenzzp/Documents/mysql备份/bak2.sql

# 恢复数据库中的表（需在Mysql命令行下执行），先在mysql命令行上进入要恢复的数据库中，然后使用以下语句
source /Users/gottenzzp/Documents/mysql备份/bak2.sql

插入表中数据

• 语法格式

  insert into 表名(字段1，字段2，字段3····)
  values(值1，值2，值3···);
  

  PS：字段和值得一一对应（位置对应，数据类型对应，长度对应），且表名后的字段若全都省略了，则表示包含所有字段

DCL语句（数据控制语句）

事务

• 
• 

事务的四大特性

• 

数据库调优

索引

• 
• 

常用命令

• 查看MySQL数据库系统的版本号

  select version();

• 在终端连接数据库：

  • 显示密码方式

    mysql -uroot -p密码

  • 隐藏密码方式

    mysql -uroot -p
    Enter password: 密码
    

  • 远程连接数据库

    mysql -h主机ip -P端口号 -u用户名 -p密码

• 退出数据库

  exit

• 显示所有数据库

  show databases;

• 使用数据库

  use 数据库名称

• 查询当前数据库

  select database();

• 终止正在输入的指令

  • control + c
  • 输入 \c

• 创建数据库

  create database 数据库名称