MySQL 笔记

发表于 | 更新于: | 分类于 | | 阅读次数:
字数统计: 7.9k 字 | 阅读时长 ≈ 33 分钟
MySQL 入门笔记，涵盖MySQL安装、数据库、数据表、用户管理等基本语法及优化。

安装和配置

安装

1.下载免安装版，将其解压之后，将bin加入path，新建文件夹data, 新建my.ini文件，输入：

1
2
3
4
[mysqld]
basedir = …\mysql
datadir = …\mysql\data
port=3306

2.管理员身份打开cmd, 输入：

1
mysqld --initialize --user=mysql --console

　记录随机密码，之后分别输入以下来启动MySQL：

1
2
mysqld --install MySQL
net start MySQL

3.登录MySQL。

1
mysql -uroot -p

以上可选加入参数：-h：ip或者localhost

输入刚才记录的随机密码即可登录。登录之后可设置自定义密码：

1
set password for root@localhost=Password("0119")

4.安装MySQL的Workbench。

配置

在MySQL里面设置只是临时修改，MySQL重启之后会恢复为默认设置，因此为了永久修改需要修改配置文件。

设置数据表和数据库与Server的编码方式。可以在终端登陆mysql，之后输入\s查看编码方式。修改方式为，将my.ini文件替换为以下内容：

1
2
3
4
5
6
7
[mysql]
default-character-set=utf8
[mysqld]
basedir=...\mysql-5.7.11-winx64
datadir=...\mysql-5.7.11-winx64\data
port=3306
character-set-server=utf8

ps: 以上...\mysql-5.7.11-winx64为MySQL免安装版的解压路径。

Python 连接和使用

通过Python 3与MySQL连接可通过pymysql实现。

1
pip install pymysql

1.连接数据库

1
2
3
4
import pymysql
db = pymysql.connect('localhost', 'root', '0119', 'test_db', charset='utf8')
cursor = db.cursor()
db.close()

2.执行SQL语句

1
2
3
4
5
6
sql = 'DROP TABLE IF EXISTS tbl_name'
try:
    cursor.execute(sql)
    cursor.commit()
except:
    cursor.rollback()

3.fetch操作

  • fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
  • fetchall(): 接收全部的返回结果行.
  • rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

基本语法

  • 以”;”结尾。
  • 关键词不区分大小写，但是推荐用大写。
  • 单行注释采用 “# 注释”、”— 注释”，多行注释采用”/*注释*/“。

数据类型

1. 数值类型

1.整数型

  • tinyint：占用1字节
    • 有符号：-128到127（$-2^7$到$2^7-1$）
    • 无符号：0到255（0到$2^8-1$）
  • smallint：占用2字节
    • 有符号：-32768到32767（$-2^{15}$到$2^{15}-1$）
    • 无符号：0到65535（0到$2^{16}-1$）
  • mediumint：占用3字节
    • 有符号：到$2^{23}-1$​
    • 无符号：0到$2^{24}-1$​
  • int：占用4字节
    • 有符号：$-2^{31}$到$2^{31}-1$
    • 无符号：0到$2^{32}-1$
  • bigint：占用8字节
    • 有符号：$-2^{63}$到$2^{63}-1$
    • 无符号：0到$2^{64}-1$

2.小数型

(1)浮点型
  • float：float(m, d)。单精度浮点型8位精度（4字节），m总位数，d小数位。

  • double：double(m, d)。双精度浮点型16位精度（8字节），m总位数，d小数位。

    (2)定点型

  • decimal：decimal(m, d)。精确数字，m总位数，d小数位。例如 decimal(4,2)最大值位99.99。

2. 字符串类型

  • char：char(n)。0~255字节，定长，不够用空格填充，多余截取，字符串所占用的字节数总是为n。char存取速度比varchar快，但会造成空间浪费。
  • varchar：varchar(n)。0-65535字节，可变长度，根据字符串长度调整长度，字符串所占用的字节数小于等于n。
  • tinytext：0-255字节。短文本字符串。
  • text：0-65535字节，长文本。
  • mediumtext：0-$2^{24}-1$字节，中等长度文本。
  • longtext：0-$2^{32}-1$字节，极大文本。
  • enum：占用1或2个字节，取决于枚举值的位数（最多65535个值）。
  • set：1、2、3、4或8个字节，取决于set成员的数目（最多64个成员）。

3. 日期类型

  • date：占用3字节，范围1000-01-01至9999-12-31，格式为YYYY-mm-dd。
  • time：占用3字节，范围为-838:59:59至838:59:59，格式为HH:MM:SS。
  • year：占用1字节，范围为1901至2155，格式为YYYY。
  • datetime：占用8字节，范围为1000-01-01 00:00:00至9999-12-31 23:59:59，格式为YYYY-mm-dd HH:MM:SS
  • timestamp：占用8字节，范围为1970-01-01 00:00:00 至 2037年某时，格式为YYYYmmdd HHMMSS。

建议用int存储时间的时间戳。

修饰关键字

  • unsigned：无符号。
  • auto_increment：自增。
  • default：默认值。
  • comment：注释，字段解释说明。
  • not null：非空。
  • unique：唯一索引。
  • index：普通索引。
  • primary key：主键。

auto_increment必须指定为primary key。

运算符

  • 算术运算符：+，-，*，/，DIV，%和MOD。
    • 10 DIV 4等于2.
  • 比较运算符：
    • =：赋值和判断是否相等。
    • !=，<>：不等于。
    • >，<，>=， <=：大小比较。
    • BETWEEN...AND表示位于…之间。
    • NOT BETWEEN...AND不在…之间。
    • IN表示在集合里面。
    • NOT IN表示不再集合里面。
    • <=>严格比较两个NULL值是否相等。两者均为NULL时取值为1，一者为NULL时取值为0.
    • LIKE模糊匹配。
    • REGEXP或RLIKE正则表达式匹配。
    • IS NULL为空。
    • IS NOT NULL不为空。
  • 逻辑运算符
    • NOT或!表示逻辑非。
    • OR或者||表示 逻辑或。
    • AND或者&&表示 逻辑与。
    • XOR表示逻辑异或。
  • 位运算符
    • &表示按位与。
    • |表示按位或。
    • ^表示按位异或。
    • !表示取反。
    • <<表示左移。
    • >>表示右移。

函数

MySQL提供了很多用于字符串、数值、日期的转换函数，以及一些高级函数。以下介绍一些常见的高级函数，更多详细内容可以参考 MySQL 函数。

  1. IF(expr, v1, v2)
    1. 功能：如果表达式expr成立，返回结果v1；否则，返回结果v2。
    2. 示例：SELECT IF(1>0, '正确', '错误');
  2. IFNULL(v1, v2)
    1. 功能：如果v1的值不为NULL，则返回v1，否则返回v2。
    2. 示例：SELECT IFNULL(null, 'Hello');
  3. ISNULL(expr)
    1. 功能：判断表达式是否为NULL。
    2. 示例：SELECT ISNULL(NULL);
  4. NULLIF(expr1, expr2)
    1. 功能：比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1。
    2. 示例：SELECT NULLIF(25, 25);
  5. CAST(x AS type)
    1. 功能：转换数据类型
    2. 示例：SELECT CAST("2017-08-29" AS DATE);

SQL语句分类

  • DDL：Data Definition Language。用于创建、删除、修改 库表结构。
  • DML：Data Manipulation Language。用于增、删、改 表的记录。
  • DCL：Data Control Language。用于用户的创建以及授权。
  • DQL：Data Query Language。用于查询数据。

数据定义 DDL

数据库相关操作

1.查看所有数据库

1
SHOW DATABASES;

2.切换 / 使用 数据库

1
USE db_name;

3.创建数据库

1
2
3
4
5
6
7
8
9
10
# 最简单的创建数据库
CREATE DATABASE 数据库名;
# 创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DAFAULT] CHARACTER SET [=] charset_name;
# 创建完之后显示数据库
SHOW CREATE DATABASE db_name;

# 示例
CREATE DATABASE IF NOT EXISTS t CHARACTER SET gbk;
SHOW CREATE DATABASE t;

ps：以上，{}表示多选一，[]表示可选可不选。

4.修改数据库

1
2
3
4
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

# 示例
ALTER DATABASE t CHARACTER SET utf8;

5.删除数据库

1
2
3
4
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

# 示例
DROP DATABASE t;

数据表相关操作

数据表设计的三范式：

  • 第一范式——原子性：数据表的每一列均不可分解；
  • 第二范式：除了主键之外的其他列，均与主键有关。即，一个表只描述一件事情。例如订单表只描述订单信息，产品表只描述产品信息。
  • 第三范式：数据表中的列至于主键直接相关，不是间接相关，每一列只依赖于主键。例如，订单表只需要有用户的id即可，不需要关联用户的其他信息，因为用户的其他信息可以通过用户id在用户表中查询到。避免设计冗余。

1.查看所有数据表

1
2
3
4
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

# 示例
SHOW TABLES FROM mysql;

2.查看数据表信息

1
2
3
# 查看数据表中的所有列
SHOW columns from tbl_name;
DESC tbl_name;

3.创建数据表

1
2
3
4
# 创建数据表
CREATE TABLE tbl_name (col_name col_type, col_name col_type);
# 查看创建的数据表
SHOW CREATE TABLE tbl_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 以下为创建数据表时添加一些约束的示例
# 添加主键约束：每张表只有一个主键，唯一、不允许为空、不允许相同
CREATE TABLE tbl(
id SMALLINT AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL
);
# 添加唯一约束：唯一、可以为空，每张表可以存在多个唯一约束
CREATE TABLE tbl(
id SMALLINT ?UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY
);
# 添加默认约束
CREATE TABLE tbl(
id SMALLINT AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
gender ENUM('1','2','3') DEFAULT '3'
);
# 添加外键约束
# 创建一个省份表（省份id，省份名），用户表（用户id，用户名，用户所在省份id（外键））
CREATE TABLE province(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pname VARCHAR(20) NOT NULL
);
CREATE TABLE user(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES province (id)
);

4.修改数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 修改表名
ALTER TABLE tbl_name rename to  tbl_name_new;
# 修改数据表引擎
ALTER TABLE tbl_name ENGINE=MyISAM;

# 添加一列，FIRST和AFTER指定了添加新列的位置
ALTER TABLE tbl_name ADD new_col_name new_col_type [FIRST | AFTER] col_name_exist;
# 添加多列
ALTER TABLE tbl_name ADD (new_col_name_1 new_col_type_1, col_name_new_2 new_col_type_2);
# 删除列
ALTER TABLE tbl_name DROP col_name_1, DROP col_name_2;

# 修改字段类型
ALTER TABLE tbl_name MODIFY col_name varchar(128) null DEFAULT '';
# 修改字段名称及类型
ALTER TABLE tbl_name CHANGE old_col_name new_col_name col_type;
# 修改字段的相对位置
ALTER TABLE tbl_name MODIFY col_name1 col_type1 FIRST|AFTER col_name2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 以下为修改数据表时添加/删除一些约束的示例

# 添加默认值约束
ALTER TABLE tbl_name ALTER col_name SET DEFAULT 100;
# 修改默认值（删除default约束）
ALTER TABLE tbl_name ALTER col_name DROP DEFAULT;

# 添加主键约束
ALTER TABLE tbl_name ADD PRIMARY KEY (distId, record_time);
# 删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;

# 添加唯一约束
ALTER TABLE tbl_name ADD ADD UNIQUE (username);
# 删除唯一约束
ALTER TABLE tbl_name DROP UNIQUE username;

# 添加外键约束
ALTER TABLE tbl_name ADD FOREIGN KEY (pid) REFERENCES tbl_name_2 (id);
# 删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY col_name;

ALTER可以用于修改数据库、数据表名、数据字段。

5.删除数据表

1
DROP TABLE tbl_name ;

数据操作 DML

插入数据记录

1
2
3
4
5
6
7
8
# 插入一条数据，建议插入哪些列就指定哪些列名
INSERT INTO 表名 [(列名1,列名2,...)] VALUES (值1,值2,...)
# 插入多条数据
INSERT INTO 表名  (列名1,列名2,...列名N)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

# 示例
INSERT tb1 VALUES('Liuph', 5000.01);
INSERT tb1 (username,salary) VALUES('Tom', 4500.68);

命令行插入汉字显示乱码的问题：

  • 在插入之前设置编码方式（临时性质的）：set names gbk;
  • 修改配置文件。

更新数据记录

1
2
3
4
5
6
7
8
UPDATE 表名 SET 列名1={value | expr | DEFAULT} [, 列名2 = {expr | DEFAULT}] … [WHERE 更新条件]
# ps: 如果没有指定 WHERE 子句，表中的所有记录将被更新。

# 示例
UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
UPDATE students SET tel=DEFAULT WHERE id=5;
UPDATE user SET age=age+10 WHERE id%2=0;
UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where runoob_id = 3;

删除数据记录

1
2
3
4
5
DELETE FROM 表名 WHERE 删除条件;
# ps: 如果没有指定 WHERE 子句，表中的所有记录将被删除。

# 示例
DELETE FROM runoob_tbl WHERE runoob_id=3;

delete，drop，truncate 都有删除表的作用，区别在于：

  • 1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
  • 2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
  • 3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

数据控制 DCL

ps：涉及用户、权限的更新一般需要FLUSH PRIVILEGES;刷新权限才能生效。

指定IP访问

实际上是通过修改mysql数据库下user表中user=root项的host（user=root的host默认值包括了127.0.0.1、::1和localhost）。

1
2
3
4
USE mysql;
UPDATE user SET host='192.168.0.5' WHERE user='root';
# 刷新权限
FLUSH PRIVILEGES;

之后只有在登陆时输入mysql -uroot -h192.168.0.5 -p才能登录，否则默认采用localhost的host登录。

修改密码

以下介绍三种修改密码的方法。

1.修改mysql数据库下user数据表中user=root的password。

1
2
3
4
USE mysql;
UPDATE user SET password=PASSWORD('0119') WHERE user='root';
# 刷新权限
FLUSH PRIVILEGES;

2.使用MySQL安装路径下bin->mysqladmin。

1
2
# 在终端中直接运行以下命令，将原密码0119修改为新密码admin
mysqladmin -uroot -p0119 password admin

3.在mysql界面中直接设置密码

1
SET password for root@localhost=Password("0119")

忘记密码

  1. 第一步，停止mysql.exe服务。
  2. 在命令行中运行 ：
1
mysqld --skip-grant-tables

用户创建

1
2
3
4
5
6
7
# 创建用户
CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码'
# ps:限制用户只能通过特定的ip登录，如果要使用所有的ip则需要用 %

# 示例
CREATE USER 'liuph'@'%' IDENTIFIED BY '0119';
CREATE USER 'test'@'192.168.0.5' IDENTIFIED BY '0119';

用户授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 查看用户权限
SHOW GRANTS FOR '用户名'@'IP地址';
# 授所有权限给用户
GRANT ALL ON . TO '用户名'@'IP地址' IDENTIFIED BY '密码' WITH GRANT OPTION;
# 用户授权
GRANT 权限1,权限2,权限3,... on 数据库名.* TO '用户名'@'IP地址' IDENTIFIED BY '密码';
# 用户权限的收回
REVOKE 权限1,权限2,权限3,... on 数据库名.* TO '用户名'@'IP地址' IDENTIFIED BY '密码';
# ps: 1. 限制用户只能通过特定的ip登录，如果要使用所有的ip则需要用 %
# ps: 2. 授权给所有数据库用 *.*
# ps: 3. 授权所有权限用 ALL 或者 ALL PRIVILEGES

# 示例
GRANT ALL ON . TO 'gis'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT UPDATE,INSERT,DELETE on shop.* TO 'test'@'%' IDENTIFIED BY '123456';
REVOKE SELECT on shop.* TO 'test'@'%' IDENTIFIED BY '123456';

用户删除

1
2
# 删除用户
DROP USER '用户名'@'IP地址';

数据查询 DQL

条件查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 条件查询通用表达式
SELECT 列名1,列名2,... [FROM 表名] [WHERE 查询条件] [GROUP BY 字段名 [ASC|DESC],...] [HAVING 过滤条件] [ORDER BY 排序字段 [ASC|DESC],...] [LIMIT 行数]
# ps: 查询所有字段用 * ，不带where条件

# 查询唯一值
SELECT DISTINCT 列名 FROM 表名;
# 查询结果别名显示
SELECT 列名1,列名2,... AS 列别名 FROM 表名;
# ps: AS 列别名需要在 FROM 和 WHERE 前面才能发挥作用！
# 模糊查询（适用于数据量较少时）
SELECT 列名1,列名2,... FROM 表名 WHERE 字段名 LIKE '%子字符串%';
# 连接多个字段为新的列名
SELECT CONCAT(列名1,列名2,...) [AS 列新名] FROM 表名;
SELECT CONCAT_WS(分隔符,列名1,列名2,...) [AS 列新名] FROM 表名;

# 示例
SELECT * FROM gaodepoi_tbl WHERE type LIKE '%商务住宅%';
SELECT user_name AS usr FROM user;
SELECT CONCAT_WS(';', lng, lat) AS lng_lat FROM gaodepoi_tbl WHERE type LIKE '%商务住宅%';

数据排序

1
2
3
4
5
6
SELECT * FROM 表名 ORDER BY 字段名 [ASC|DESC]
# ps: 1. 默认按升序排列，即ASC
# ps: 2. 可以设定多个字段来排序

# 示例
SELECT * FROM gaodepoi_tbl WHERE type LIKE '%商务住宅%' ORDER BY id;

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 聚合函数
COUNT(字段名)	# 返回字段查询结果中的总行数，忽略空值的行
COUNT(*)	  # 返回查询结果中的总行数，不管是否有空值
COUNT(distinct 字段名) # 返回列值非空的、并且列值不重复的行的数量
COUNT（表达式)	# 根据表达式统计数据
SUM(字段名)	# NULL值不参与计算
SVG(字段名)	# NULL值不参与计算
MAX(字段名)
MIN(字段名)

# 示例
SELECT COUNT(*) FROM user;
SELECT COUNT(sex) FROM user;
SELECT COUNT(DISTINCT salary) FROM salary_tab;
SELECT COUNT(TO_DAYS(DATE)=TO_DAYS(NOW()) or NULL) AS '今日统计';
SELECT AVG(ifnull(salary,0)) FROM salary_tab;

NULL值处理：

1
2
> ifnull(列名, 新值)	#如果该列值为NULL，则替换为新值
>

数据分组

1
2
3
4
5
6
7
8
9
10
11
12
# 按字段分组，按字段顺序排序，过滤符合表达式的项
SELECT count(*) FROM 表名 [WHERE 查询条件] [GROUP BY 字段名 [ASC|DESC]] [HAVING 过滤条件] [ORDER BY 排序字段];
# ps: 1. 出现在SELECT子句中的单独的列，必须出现在GROUP BY子句中作为分组列
# ps: 2. 分组列可以不出现在SELECT子句中，可出现在SELECT子句中的一个复合表达式中
# ps: 3. 如果GROUP BY后面是一个复合表达式，那么在SELECT子句中，它必须整体作为一个表达式的一部分才能使用
# ps: 4. 如果分组列和排序列相同，则可以合并group by和order by子句
# ps: 5. where子句在分组前对记录进行过滤；having子句在分组后对记录进行过滤。因此为了过滤分组结果必须用having子句。

# 示例
SELECT type,COUNT(type) FROM gaodepoi_tbl GROUP BY type;
SELECT userid,COUNT(salary) FROM salary_tab GROUP BY salary,userid;
SELECT year(payment_date),count(*) FROM PENALTIES GROUP BY year(payment_date);
1
2
3
4
5
6
7
8
# 按字段分组，将同组的其他字段拼接起来
SELECT GROUP_CONCAT(列名1,列名2,...) as 新列名1,新列名2,...,分组字段,聚合函数 FROM 表名 [WHERE 查询条件] [GROUP BY 字段名 [ASC|DESC]] [HAVING 过滤条件];
# ps: 如果没有group by子句，则group_concat可用于返回一列的所有值

# 示例
SELECT GROUP_CONCAT(name),type,AVG(lng) FROM gaodepoi_tbl GROUP BY type;
SELECT GROUP_CONCAT(name),type,COUNT(*) FROM gaodepoi_tbl GROUP BY type HAVING COUNT(*)>100;
SELECT GROUP_CONCAT(salary) FROM salary_tab;

连接查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 新表名1.列名1,新表名2.列名2 FROM 表名1 AS 新表名1,表名2 AS 新表名2 WHERE 查询条件;
# 内连接
SELECT 新表名1.列名1,新表名2.列名2 FROM 表名1 AS 新表名1 INNER JOIN 表名2 AS 新表名2 {WHERE|ON} 查询条件;
# 左外连接
SELECT 新表名1.列名1,新表名2.列名2 FROM 表名1 AS 新表名1 LEFT JOIN 表名2 AS 新表名2 ON 查询条件;
# 右外连接
SELECT 新表名1.列名1,新表名2.列名2 FROM 表名1 AS 新表名1 RIGHT JOIN 表名2 AS 新表名2 ON 查询条件;
# ps: 左连接以左边表的数据为准，右连接相反，会导致数据不完全对应时出现NULL值，因此推荐使用内连接查询。
# 自连接
SELECT * FROM 表名 表名实例1 ,表名 表名实例2 WHERE 查询条件; 

# 示例
SELECT s.name,m.score FROM student AS s,mark AS m WHERE s.id=m.stu_id;
SELECT s.name,m.score FROM student AS s INNER JOIN mark AS m WHERE s.id=m.stu_id;
SELECT s.name,m.score FROM student AS s LEFT JOIN mark AS m ON s.id=m.stu_id;
SELECT s.name,m.score FROM student AS s RIGHT JOIN mark AS m ON s.id=m.stu_id;
SELECT * FROM student s ,student a where a.collegeId=s.collegeId AND a.name <> s.name ORDER BY a.collegeId;

联合查询

1
2
3
4
5
6
SELECT 查询1 UNION [ALL | DINSTINCT] 查询2;
# ps: 1. 用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
# ps: 2. DINSTINCT 用于删除结果集中重复的数据；ALL 返回所有结果集，包含重复数据。

# 示例
SELECT name FROM student UNION ALL SELECT score FROM mark;

子查询

1
2
3
4
SELECT 查询1 WHERE 列名 in 查询2;

# 示例
SELECT id FROM student WHERE id in (SELECT id FROM mark);

Limit

1
2
3
4
5
6
SELECT 查询1[ LIMIT N][ OFFSET M];
# ps: 1. OFFSET M 指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
# ps: 2. LIMIT N1,N2 表示从第 N1 开始限制 N2 条，等价于 LIMIT N2 OFFSET N1.

# 示例
SELECT * FROM student ORDER BY id DESC LIMIT 1;	# 获取记录总数目

字符集和存储引擎

存储引擎

  • MyISAM
    • 全文索引，不支持事务。
    • 表级锁，崩溃恢复支持不好。
  • InnoDB
    • 支持事务，不支持全文索引（5.6版本后开始支持，5.6开始默认数据库引擎是InnoDB）
    • 速度快，推荐。
    • 行级锁，崩溃恢复支持较好。

1.查看数据库引擎

1
2
3
4
# 1.通过数据表查看引擎
SHOW CREATE TABLE tbl_name;
# 查看所有表的状态
SHOW TABLE STATUS\G;

2.修改数据库引擎

1
CREATE TABLE test(name varchar(5)) ENGINE=InnoDB;

字符编码

1.查看字符编码

1
2
3
4
5
# 查看所有字符编码
SHOW CHARACTER SET;
# 查看数据库和数据表的字符编码
SHOW CREATE DATABASE test;
SHOW CREATE TABLE test;

2.修改字符编码

1
2
3
4
# 1. 创建/修改数据库时声明字符编码
CREATE DATABASE test CHARACTER SET gbk;
# 2. 创建/修改数据表时声明字符编码
CREATE  TABLE tbl_name(name varchar(5)) ENGINE=InnoDB charset=gbk;

3.修改默认字符编码

修改my.ini文件：

1
2
[mysql]
default-character-set=utf8

4.字符校对

utf8_general_ci支持不区分大小写。

1
CREATE  TABLE tbl_name(name varchar(5)) ENGINE=InnoDB charset=utf8 collate=utf8_general_ci;

SQL优化

慢查询

1.开启记录慢查询日志

1
2
3
4
5
6
# 查找记录慢查询字段
SHOW VARIABLES LIKE '%slow%';
# 将slow_query_log设置为on
SET GLOBAL slow_query_log=on;
# 设置慢查询等待时间间隔
SET long_query_time=1;

此时可在slow_query_log_file对应的文件名里记录慢查询的查询记录。

2.分析慢查询

1
2
3
4
5
6
7
8
9
# 解释查询语句的执行结果，包括搜索了多少行之类的信息
explain SELECT * FROM account WHERE pid=235645;
# 查找是否开启了记录性能
SHOW VARAIBLES LIKE '%profiling%';
# 显示各个查询语句的性能（查询时间）
SHOW PROFILES;
# 实现某个查询语句的各项时间，即可得知哪一项最耗费时间，然后知乎/百度解决
SHOW PROFILE FOR QUERY query_id;
# ps: logging slow query会比较费时间，因此除非在检测分析慢查询，不建议打开记录慢查询。

3.慢查询优化

给查询条件中的字段添加索引。

1
ALTER TABLE tbl_name ADD INDEX(pid);

数据库缓存

1
2
3
SHOW STATUS LIKE '%qcache%';
# 设置缓存
SET GLOBAL query_cache_size=1024000;

在一次查询之后将结果存入缓存，后续查找可以直接去缓存取结果，极大提高效率。

1
2
# 关闭缓存
SET GLOBAL query_cache_size=0;

其他优化

  • 尽量减少使用SELECT *；
  • 减少使用LIKE的左模糊查询，可以使用右模糊；
  • 使用LIMIT；
  • 查询条件中尽量减少表达式运算。

其他

重复数据处理

数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

1.防止表中出现重复数据

一般来说，如果为数据表的某个字段为主键或者唯一约束，则可确保不出现重复数据。如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉，再插入新记录。

2.统计重复数据

采用GROUP BY对特定字段进行分组，通过HAVING子句过滤重复次数大于1的即可统计重复数据。

1
SELECT COUNT(*) as repetitions, last_name, first_name FROM person_tbl GROUP BY last_name, first_name HAVING repetitions > 1;

3.过滤重复数据

  • 在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据；
1
SELECT DISTINCT last_name, first_name FROM person_tbl;
  • 使用 GROUP BY 来读取数据表中不重复的数据。
1
SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

4.删除重复数据

  • 新建数据表，按Group分组，之后删除旧表，更新新表。
1
2
3
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;
  • 为数据表添加主键索引。
1
ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

MySQL导入

1.MySQL命令

1
2
3
4
mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)

# 示例
mysql -uroot -p123456 < runoob.sql

2.Source命令

1
2
3
4
CREATE DATABASE abc;      # 创建数据库
USE abc;                  # 使用已创建的数据库 
SET names utf8;           # 设置编码
SOURCE /home/abc/abc.sql  # 导入备份数据库

3. LOAD DATA 

1
2
3
4
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl [FIELDS [TERMINATED BY ','] [[OPTIONALLY] ENCLOSED BY  '"']] [LINES [TERMINATED BY '\n']];
# ps: 1. 如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。
# ps: 2. FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。
# ps: 3. 如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

1
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a);

4. mysqlimport 

1
mysqlimport -u root -p --local --fields-terminated-by=":" --columns=b,c,a --lines-terminated-by="\r\n"  database_name dump.txt

mysqlimport常用可选参数说明：

  • -d or —delete：新数据导入数据表中之前删除数据数据表中的所有信息。
  • -f or —force：不管是否遇到错误，mysqlimport将强制继续插入数据。
  • -i or —ignore：mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
  • -l or -lock-tables：数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
  • -r or -replace：这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
  • —fields-enclosed- by= char：指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
  • —fields-terminated- by=char：指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）。
  • —lines-terminated- by=str：此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。
  • -v ：显示版本（version）
  • -p： 提示输入密码（password）等。

MYSQL导出

1.SELECT INTO OUTFILE

1
SELECT * FROM tbl_name INTO OUTFILE '/tmp/runoob.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

2.mysqldump

1
2
3
4
5
6
7
8
9
10
11
mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl
# ps: 导出一个SQL脚本。使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

# 导出某个数据库
mysqldump -u root -p RUNOOB runoob_tbl > dump.txt

# 导出所有数据库
mysqldump -u root -p --all-databases > database_dump.txt

# 导出至其他服务器
mysqldump -u root -p db_name1 mysql -h other-host.com db_name2

事务

  • 只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行，可以避免例如转账过程中A的钱转出去但是B没收到的情况。
  • 事务用来管理 insert,update,delete 语句。

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

1
2
3
4
5
START TRANSACTION;	# 开启事务
UPDATE TABLE user SET money=money-1000 WHERE id=1;
UPDATE TABLE user SET money=money+1000 WHERE id=2;
COMMIT;		# 提交，完成事务
ROLLBACK;	# 否则，失败则回滚。

事务控制语句

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；
  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO identifier 把事务回滚到标记点；
  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务处理主要有两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

视图

等价于将SELECT查询结果另存。

1
2
3
4
CREATE VIEW test_view(name, email) AS SELECT name, email FROM student WHERE id=2;

# 从view中查询
SELECT name from test_view;

索引

索引可以大大提高MySQL的检索速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，也会占用磁盘空间的索引文件。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

索引主要包括普通索引、唯一索引（值必须唯一，但允许有空值）等。索引可以在创建表时直接指定，也可后续修改，或者后续创建和删除。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 1. 创建索引
CREATE INDEX indexName ON mytable(username(length));
CREATE UNIQUE INDEX indexName ON mytable(username(length));
# ps: 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

# 2. 修改表结构，ALTER添加索引
ALTER TABLE tableName ADD INDEX indexName(columnName);
ALTER TABLE mytable ADD UNIQUE [indexName] (username(length));

# 3. 创建表时直接指定
CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) ); 

# 删除索引
DROP INDEX [indexName] ON mytable;