MySQL常用指令

基本概念

• 数据库是用来存储和管理数据的仓库

• MySQL是关系型数据库，即使用二维表格来存储数据

• SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。也就是说MySQL数据库使用SQL语言进行操作。

• “关系型数据库管理系统（RDBMS - Relational database management system ）”，即“数据库服务器”，每个服务器(RDBMS)里面能创建多个数据库(DATABASE)。
  

• 每个数据库(DATABASE)中还可以包含多张数据库表(TABLES)。
  
  数据库表就是一个多行多列的表格。在创建表时，需要指定表的列数，以及列名称，列类型等信息。而不用指定表格的行数，行数是没有上限的。下面是tab_student表的结构，左边列是student的一些属性，右边列示对应的数据类型：
  
  当把表格创建好了之后，就可以向表格中添加数据了。向表格添加数据是以行为单位的。下面是s_student表的记录，向表中添加的数据必须符合表结构里的数据类型：
  

数据库操作指令

1. 查

SHOW DATABASES; #注意sql语句必须要以分号结尾，表示语句的结束

2. 增

CREATE DATABASE [IF NOT EXISTS] 数据库名称; #创建数据库

有两种创建数据库的方法

CREATE DATABASE IF NOT EXISTS MYDAB1; #在MYDB1不存在时创建名为MYDB1的数据库，如果MYDB1存在，不会对其产生影响

CREATE DATABASE MYDB1;  #创建MYDB1的数据库，如果数据库存在，报错

//当数据库不存在时，用上述两种方法都是一样的

//上述创建了一个mydb1的数据库，此时再创建同名的数据库时，这两种创建方法就不一样。

---

//可以看到当数据库存在时再去创建同名数据库，第一种方法CREATE DATABASE IF NOT EXISTS MYDAB1，不会报错，但有警告，原数据库中的内容也都不会改变；但是第二种方法CREATE DATABASE MYDAB1，就会报错。

3. 删

DROP DATABASE [IF EXISTS] 数据库名字; #删除数据库

4. 改

① 查看mysql编码

    SHOW VARIABLES LIKE 'CHAR%';

• character_set_client：你发送的数据必须与client指定的编码一致！！！服务器会使用该编码来解读客户端发送过来的数据；
• character_set_connection：通过该编码与client一致！该编码不会导致乱码！当执行的是查询语句时，客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致，那么转换就不会出现问题；
• character_set_database：数据库默认编码，在创建数据库时，如果没有指定编码，那么默认使用database编码；
• character_set_server：MySQL服务器默认编码；
• character_set_results：响应的编码，即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码；

② 修改
修改character_set_client、character_set_results、character_set_connection为GBK，就不会出现乱码了。但其实只需要修改character_set_client和character_set_results。

控制台的编码只能是GBK，而不能修改为UTF8，这就出现一个问题。客户端发送的数据是GBK，而character_set_client为UTF8，这就说明客户端数据到了服务器端后一定会出现乱码。既然不能修改控制台的编码，那么只能修改character_set_client为GBK了。
服务器发送给客户端的数据编码为character_set_result，它如果是UTF8，那么控制台使用GBK解码也一定会出现乱码。因为无法修改控制台编码，所以只能把character_set_result修改为GBK。

• 修改character_set_client变量：set character_set_client=gbk;
• 修改character_set_results变量：set character_set_results=gbk;
  

重启MySQL，发现配置的编码失效。

设置编码只对当前连接有效，这说明每次登录MySQL提示符后都要去修改这两个编码，但可以通过修改配置文件来处理这一问题：
配置文件路径：D:\Program Files\MySQL\MySQL Server 5.1\ my.ini（你安装MySQL的地址）

DDL(数据库定义语言)

数据定义语言是用来定义数据库对象：库、表、列等；

1. 增

创建表

CREATE TABLE 表名(  列名 列类型,  列名 列类型,  ......);

其中列名是自己起的名字，列类型是对应的数据类型。

常用类型：

字符 表示意思 int 整型 double 浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99 decimal 泛型，在钱方面使用该类型，因为不会出现精度缺失问题 char 固定长度字符串类型 varchar 可变长度字符串类型 text 字符串类型 blob 字节类型 date 日期类型，格式为：yyyy-MM-dd time 时间类型，格式为：hh:mm:ss timestamp 时间戳类型；既有年月日又有时分秒

2. 查

• 查看当前数据库中所有的表

SHOW TABLES; 

• 查看指定表的创建语句

SHOW CREATE TABLE 表名; 

• 查看表结构(使用表格的形式展示)

DESC 表名;  

3. 删

DROP TABLE 表名;   #删除表

4. 改

• 添加列

ALTER TABLE 表名 ADD (列名 列类型); 

• 修改列类型

ALTER TABLE 表名 MODIFY 列名 修改后的列类型;

• 修改列名

ALTER TABLE 表名 change 修改前列名 修改后列名 列类型;

• 删除列

ALTER TABLE 表名 DROP 列名;

• 修改表名

ALTER TABLE 原来的表名 RENAME TO 之后的表名;

DML(数据操作语言)

数据操作语言是用来定义数据库记录（数据），是对数据进行操作；

1. 增

INSERT INTO 表名[(列名1,列名2, …)] VALUES(值1, 值2, …) 

• 注意：列名如果省略，即没有指定要插入的列，此时要按创建表时列的顺序插入所有列的值；也可写部分列名，此时列名必须和值一一对应

栗子：

2. 删

有两种删除方式

DELETE FROM 表名 [WHERE 条件]

重新创建一张新的表：

TRUNCATE TABLE 表名

注：

   虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！   TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的。   回滚是指事务的回滚，即撤销事务开始后的操作。举一个简单的例子，A给B转账，在数据库中就需要给A,B进行update操作。这2条sql语句必须都执行或者都不执行（称为一个事务）。假如先执行B的update语句，B的金额增加了100，然后执行A的update语句，A的金额减100。如果A的余额大于100，那么2个语句没问题，但是A的余额小于100时，再减100就变成负的了，这不符合实际情况。所以第二条sql就出现无法执行，那么数据库的状态必须回到没有执行B的update语句之前。    当一个事务执行的时候，数据库会依次执行中间的sql语句，当某一条sql发生错误以后，根据事务的原子性，通过2种方式使数据库回到事务没有执行的状态。撤销就是相当于不执行commit；回滚就是执行一遍相反的操作，比如再执行B的update金额减100。    如果commit即提交事务，事务结束；如果rollback即主动回滚之前的数据操作语句DML。注意，数据定义语句DDL不能回滚的。

3. 改

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

不设置条件就会修改所有满足条件的列的内容

DQL(数据查询语言)

DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

1. 基础查询

• 查询所有列

SELECT * FROM 表名;  #查询对应表中的所有数据

• 查询指定列

SELECT 列名1,列名2，…… FROM 表名;    #查询指定列名对应的数据 

2. 条件查询

条件查询就是在查询时给出WHERE子句，根据WHERE后面跟的条件进行查询。
在WHERE子句中可以使用如下运算符及关键字：

运算符或关键字 解释 = 等于 != 不等于 <> 不等于(!=是 SQL92标准 <>是现行标准) < 小于 <= 小于等于 > 大于 >= 大于等于 BETWEEN…AND 在什么之间（包含两端值） IN(value1,value2,…) 允许WHERE子句中规定多个值 IS NULL 筛选出NULL值 AND 二者满足时返回结果 OR 其中一个条件满足就返回结果 NOT 求反

3. 模糊查询

SELECT 字段 FROM 表 WHERE 某字段 Like 条件

其中关于条件，SQL提供了四种匹配模式：

• % ：表示任意0个或多个字符，可匹配任意类型和长度的字符。

• _ ： 表示任意单个字符，可匹配单个任意字符，它常用来限制表达式的字符长度语句：

• [ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

• [^ ] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
  

4. 字段控制查询

• 删除重复列（DISTINCT）

SELECT DISTINCT 列名 FROM 表名;

- 加运算

SELECT 列名1+IFNULL(列名2) FROM 表名; #一般如果列名有NULL值，NULL和任何值相加都是NULL，所以要用到IFNULL函数把NULL转换成数值0

- 给列名添加别名（AS）

SELECT 列名 AS 别名 FROM 表名;

5. 排序(ORDER BY)

• 升序

SELECT *  FROM 表名　ORDER BY 列名 [ASC]; #ASC可省略，因为默认为升序排列

原始数据表：

升序排序后

- 降序

SELECT *  FROM 表名　ORDER BY 列名 DESC;

- 混搭

SELECT *  FROM 表名　ORDER BY 列名1 DESC,列名2 ASC; #在列名1降序排列时，遇到相同排位的按列名2升序排列

6. 聚合函数

函数名 解释 COUNT() 统计指定列不为NULL的记录行数； SUM() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0； AVG() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0； MAX() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算； MIN() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算

7. 分组查询(GROUP BY)

• 分组查询

SELECT 列名1/聚合函数 FROM 表名 GROUP BY 列名2;  #先通过列名2进行分组之后对列名1进行操作

- HAVING 字句

SELECT 列名1/聚合函数 FROM 表名 GROUP BY 列名2 HAVING 条件;  #先通过列名2进行分组，再根据HAVING筛选出满足的条件，然后利用筛选出的数据对列名1进行操作

注意，WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

8. LIMIT

LIMIT用来限定查询结果的起始行，以及总行数（可用来分页查询）。

SELECT * FROM 表名 LIMIT 起始行, 查询的行数;

DCL(数据控制语言)

数据控制语言是用来定义访问权限和安全级别；

1. 创建用户

CREATE USER 用户名@地址 IDENTIFIED BY '密码';

注：地址设置为’%’表示创建的该用户能在任何电脑上登录；若地址设置为localhost表示创建的该用户只能在localhost这个IP登录MySQL服务器。

2. 给用户授权

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@地址

3. 撤销授权

REVOKE 权限1, … , 权限n ON 数据库.* FORM 用户名@地址

4. 查看用户权限

SHOW GRANTS FOR 用户名@地址

5. 删除用户

DROP USER 用户名

6. 修改用户密码

USE mysql;UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;FLUSH PRIVILEGES;  #刷新一下权限