[关闭]
@EncyKe 2016-10-27T06:00:50.000000Z 字数 7855 阅读 3072

MySQL

数据库



1. 简介

MySQL
是一个 DBMS(数据库管理系统),由瑞典 MySQLAB 开发,目前属于 Oracle,MySQL 是最流行的关系型数据库管理系统。
关联数据库
将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。MySQL 使用 SQL 语言进行操作。

MySQL 开发技巧着重于 DML:SELECTINSERTUPDATEDELETE

2. 安装

2.1. Linux 下安装 MySQL

  1. # 安装 MySQL 服务端、核心程序
  2. sudo apt-get install mysql-server
  3. # 安装 MySQL 客户端
  4. sudo apt-get install mysql-client

2.2. 检查是否安装 MySQL

  1. sudo netstat -tap | grep mysql

3. 基础

3.1. 数据类型

数据类型 大小(字节) 用途 格式
INT 4 整数
FLOAT 4 单精度浮点数
DOUBLE 4 双精度浮点数
ENUM 单选,比如性别 ENUM('a', 'b', 'c')
SET 多选 SET('1', '2', '3')
DATE 3 日期 YYYY-MM-DD
TIME 3 时间点或持续时间 HH:MM:SS
YEAR 1 年份值 YYYY
CHAR 0~255 定长字符串
VARCHAR 0~255 变长字符串
TEXT 0~65535 长文本数据

3.2. 约束分类

约束类型 主键约束 默认约束 唯一约束 外键约束 非空约束
关键字 PRIMARY KEY DEFAULT UNIQUE FOREIGN KEY NOT NULL

3.2.1. 主键约束

主键约束 (PRIMARY KEY)
用于约束表中的一行,作为这一行的标识符,在一张表中通过主键就能准确定位到一行,因此主键十分重要。行中的主键不能有重复且不能为空。

3.2.2. 默认约束

默认约束 (DEFAULT)
只会在使用 INSERT 语句时体现出来,INSERT 语句中,如果被 DEFAULT 约束的位置没有值,那么这个位置将会被 DEFAULT 的值填充;

3.2.3. 唯一约束

唯一约束 (UNIQUE)
比较简单,它规定一张表中指定的一列的值必须不能有重复值,即这一列每个值都是唯一的。

3.2.4. 外键

外键 (FOREIGN KEY)
既能确保数据完整性,也能表现表之间的关系。一个表可以有多个外键,每个外键必须 REFERENCES 另一个表的主键,被外键约束的列,取值必须在它参考的列中有对应值。

3.2.5. 非空约束

非空约束 (NOT NULL)
顾名思义,被非空约束的列,在插入值时必须非空。在 MySQL 中违反非空约束,不会报错,只会有警告;当输入为空时,被记为 0。

3.3. 内置函数

函数名 COUNT SUM AVG MAX MIN
作用 计数 求和 求平均值 最大值 最小值

3.4. 索引

一种与表有关的结构,它的作用相当于书的目录,可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时,若要对表进行查询,没有索引的情况是全表搜索:将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录。这样做会消耗大量数据库系统时间,并造成大量磁盘 I/O 操作。

而如果在表中已建立索引,在索引中找到符合查询条件的索引值,通过索引值就可以快速找到表中的数据,可以大大加快查询速度。

3.5. 视图

从一个或多个表中导出来的表,是一种虚拟存在的表。它就像一个窗口,通过这个窗口可以看到系统专门提供的数据,这样,用户可以不用看到整个数据库中的数据,而只关心对自己有用的数据。

创建视图的语句,后半句是一个 SELECT 查询语句,所以视图也可以建立在多张表上,只需在 SELECT 语句中使用子查询或连接查询。

4. 使用

4.1. 打开

  1. # 打开 MySQL 服务
  2. sudo service mysql start
  3. # 使用 root 用户登录
  4. mysql -u root

4.2. 查看库

  1. show databases;

4.3. 选定库操作

  1. use <数据库名>

示例:use information_schema

4.4. 查看表

  1. show tables;

4.5. 退出

  1. quit

或者

  1. exit

4.6. 新建库

  1. CREATE DATABASE <数据库名>;

示例:CREATE DATABASE mysql_firstdatabase;

保留字 (reserved word)
指在高级语言中已经定义过的字,使用者不能再将这些字作为变量名或过程名使用。

4.7. 新建表

  1. CREATE TABLE <表名>
  2. (
  3. <列名a> 数据类型(数据长度),
  4. <列名b> 数据类型(数据长度),
  5. <列名c> 数据类型(数据长度)
  6. );

4.8. 插入值

  1. INSERT INTO 表名(列名a, 列名b, 列名c) VALUES(值1, 2, 3);

示例:

  1. INSERT INTO employee(id, name, phone) VALUES(01,'Tom',110110110);
  2. # 或者:
  3. INSERT INTO employee VALUES(02, 'Jack', 119119119);
  4. # 或者:
  5. #表中 Rose 的 phone 为 NULL
  6. INSERT INTO employee(id, name) VALUES(03, 'Rose');

4.9. 删除库

  1. DROP DATABASE <库名>;

4.10. 删除表

  1. DROP TABLE <表名>;

4.11. 重命名表

  1. ENAME TABLE 原名 TO 新名;
  2. # 或者:
  3. ALTER TABLE 原名 RENAME 新名;
  4. # 或者:
  5. ALTER TABLE 原名 RENAME TO 新名;

4.12. 添加列

  1. ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;
  2. # 或者:
  3. ALTER TABLE 表名 ADD 列名 数据类型 约束;
  1. # 在 AFTER 后的列名右边插入
  2. ALTER TABLE 表名 ADD 列名 数据类型 约束 AFTER 列名;
  3. # 使用 FIRST 使之放在第一列
  4. ALTER TABLE 表名 ADD 列名 数据类型 约束 FIRST;

4.13. 删除列

  1. ALTER TABLE 表名 DROP COLUMN 列名;
  2. # 或者:
  3. ALTER TABLE 表名 DROP 列名;

4.14. 重命名列

  1. ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型 约束;

4.15. 修改列的数据类型

  1. # CHANGE 语句中原列名和新列名相同时,指定新的数据类型或约束即可修改数据类型或约束;
  2. ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
  3. # 或者:
  4. ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

4.16. 修改某行

  1. UPDATE 表名字 SET 1=值1,列2=值2 WHERE 条件;

4.17. 删除某行

  1. DELETE FROM 表名字 WHERE 条件;

4.18. 建立索引

  1. ALTER TABLE 表名 ADD INDEX 索引名 (列名);
  2. # 或者:
  3. CREATE INDEX 索引名 ON 表名 (列名);

索引的效果是加快查询速度,当表中数据不够多的时候是感受不出它的效果的。使用 SELECT 语句查询的时候,语句中 WHERE 里面的条件,会自动判断有没有可用的索引。

4.19. 建立视图

  1. CREATE VIEW 视图名(列a, b, c) AS SELECT 1, 2, 3 FROM 表名字;

4.20. 导入

  1. LOAD DATA INFILE '文件路径' INTO TABLE 表名;

4.21. 导出

  1. SELECT 1, 2 INTO OUTFILE '文件路径和文件名' FROM 表名字;

4.22. 备份

  1. mysqldump -u root 库名>备份文件名;
  1. mysqldump -u root 库名 表名>备份文件名;

4.23. 恢复

  1. source <.sql路径>
  2. # 或者:
  3. # 备份的整个数据库
  4. mysqldump -u root 库名<备份文件名;

5. 语句示例

5.1. 简单语句查询示例

  1. SELECT name, age FROM employee;
  2. SELECT name, age FROM employee WHERE age>25;
  3. SELECT name, age, phone FROM employee WHERE name='Mary';
  4. # 筛选出大于 25,小于 30 的age
  5. SELECT name, age FROM employee WHERE age>25 AND age<30;
  6. # 筛选出大于等于 25,小于等于 30 的 age
  7. SELECT name, age FROM employee WHERE age BETWEEN 25 AND 30;
  8. # 在 dpt3 或 dpt4 中
  9. SELECT name, age, phone, in_dpt FROM employee WHERE in_dpt IN ('dpt3', 'dpt4');
  10. # 既不在 dpt3 也不在 dpt4 中
  11. SELECT name, age, phone, in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1', 'dpt3');
  12. # _ 通配一个未定字符
  13. SELECT name, age, phone FROM employee WHERE phone LIKE '1101__';
  14. # %通配不定个未定字符
  15. SELECT name, age, phone FROM employee WHERE name LIKE 'J%';
  16. # 对结果排序
  17. SELECT name, age, salary, phone FROM employee ORDER BY salary DESC;
  18. # 函数计算
  19. SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

5.2. 进阶语句查询示例

5.2.1. 子查询

  1. SELECT of_dpt, COUNT(proj_name) AS count_project FROM project
  2. WHERE of_dpt IN
  3. (SELECT in_dpt FROM employee WHERE name='Tom');

5.2.2. 连接查询

  1. SELECT id, name, people_num
  2. FROM employee, department
  3. WHERE employee.in_dpt = department.dpt_name
  4. ORDER BY id;

此举等同于 JOIN ON 语句:

  1. SELECT id, name, people_num
  2. FROM employee JOIN department
  3. ON employee.in_dpt = department.dpt_name
  4. ORDER BY id;

6. 进阶

6.1. JOIN 从句

6.1.1. 内连接

内连接 (INNER JOIN)
INNER JOIN
  1. SELECT 列名
  2. FROM 1
  3. INNER JOIN 2
  4. ON 1. = 2.值;

6.1.2. 左外链接

左外链接 (LEFT OUTER)
LEFT OUTER JOIN
  1. SELECT 列名
  2. FROM 1
  3. LEFT JOIN 2
  4. ON 1. = 2.
  5. WHERE 2. IS NULL;

6.1.3. 右外链接

右外链接 (RIGHT OUTER)
RIGHT OUTER JOIN
  1. SELECT 列名
  2. FROM 1
  3. RIGHT JOIN 2
  4. ON 1. = 2.
  5. WHERE 1. IS NULL;

6.1.4. 全外链接

全外链接 (FULL OUTER)
FULL OUTER JOIN
  1. SELECT 列名
  2. FROM 1
  3. LEFT JOIN 2
  4. ON 1. = 2.
  5. UNION JOIN
  6. SELECT 列名
  7. FROM 2
  8. RIGHT JOIN 1
  9. ON 1. = 2.值;

6.1.5. 交叉连接

交叉连接 (CROSS)
: 即两表叉乘(笛卡尔积连接);

  1. SELECT 列名
  2. FROM 1
  3. CROSS JOIN 2;

6.1.6. 联合更新

  1. UPDATE 1
  2. SET 键='值'
  3. WHERE 1.列名
  4. IN
  5. (SELECT 2.列名
  6. FROM 1
  7. LEFT JOIN 2
  8. ON 1.列名 = 2.列名);
  1. UPDATE 1
  2. JOIN
  3. (SELECT 2.列名
  4. FROM 1
  5. JOIN 2
  6. ON 1.列名 = 2.列名) 2
  7. ON 1.列名 = 2.列名
  8. SET 1.列名 = '值';

示例:将 user1 表中 user_name 与 user2 表中 user_name 相同的 user1 表的 over 替换成 user2 表中的 over 值;

  1. UPDATE user1
  2. SET over='齐天大圣'
  3. WHERE user1.user_name
  4. IN
  5. (SELECT b.user_name
  6. FROM user1 a
  7. LEFT JOIN user2 b
  8. ON a.user_name = b.user_name);

↓↓

  1. UPDATE user1 a
  2. JOIN
  3. (SELECT b.user_name
  4. FROM user1 a
  5. JOIN user2 b
  6. ON a.user_name = b.user_name) b
  7. ON a.user_name = b.user_name
  8. SET a.over = '齐天大圣';

6.1.7. 优化子查询

  1. SELECT 列名,
  2. (
  3. SELECT 列名
  4. FROM 2
  5. WHERE 1.列名 = 2.列名
  6. )
  7. AS 新列名
  8. FROM 1;
  1. SELECT 列名
  2. FROM 1
  3. LEFT JOIN 2
  4. ON 1.列名 = 2.列名;

示例:

  1. SELECT a.user_name, a.over,
  2. (
  3. SELECT over
  4. FROM user2 b
  5. WHERE a.user_name = b.user_name
  6. )
  7. AS over2
  8. FROM user1 a;

↓↓

  1. SELECT a.user_name, a.over, b.over
  2. FROM user1 a
  3. LEFT JOIN user2 b
  4. ON a.user_name = b.user_name;

6.1.8. 优化聚合子查询

示例:查询出四人组中打怪最多的日期;

  1. SELECT a.user_name, b.timestr, b.kills
  2. FROM user1 a
  3. JOIN user_kills b
  4. ON a.id = b.user_id
  5. WHERE b.kills = (SELECT MAX(c.kills)
  6. FROM user_kills c
  7. WHERE c.user_id = b.user_id);

↓↓

  1. SELECT a.user_name, b.timestr, MAX(b.kills)
  2. FROM user1 a
  3. JOIN user_kill b
  4. ON a.id=b.user_id
  5. GROUP BY a.user_name;

或者:

  1. SELECT a.user_name, b.timestr, b.kills
  2. FROM user1 a
  3. JOIN user_kills b ON a.user_id = b.user_id
  4. JOIN user_kills c ON c.user_id = b.user_id
  5. GROUP BY a.user_name, b.timestr, b.kills
  6. HAVING b.kills = MAX(c.kills);

6.1.9. 实现分组选择

示例:分类聚合方式查询每一个用户某一个字段数据最大的两条数据;

  1. SELECT a.user_name, b.timestr, b.kills
  2. FROM user1 a
  3. JOIN user_kills b
  4. ON a.id = b.user_id
  5. WHERE user_name = '孙悟空'
  6. ORDER BY b.'kills' DESC
  7. LIMIT 2;

↓↓

  1. SELECT d.user_name, c.ctimestr, kills
  2. FROM
  3. (SELECT user_id, timestr, kills,
  4. (
  5. SELECT count(*)
  6. FROM user_kills b
  7. WHERE b.user_id = a.user_id AND a.kills <= b.kills
  8. )
  9. AS cnt
  10. FRP, user_kills a
  11. GROUP BY user_id, timestr, kills) c
  12. JOIN user1 d
  13. ON c.user_id = d.id
  14. WHERE cnt <= 2;

6.2. 行列转换

6.3. 成唯一序列号

6.4. 数据查重

6.5. 子查询中匹配两个值

6.6. 同属性多值过滤

6.7. 累积税计算

添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注