Mysql入门

资源来源	MySQL 基础课程_MySQL - 蓝桥云课
	MySQL 数据类型 | 菜鸟教程

一、修改配置与登录

配置文件修改

sudo gedit /etc/mysql/my.cnf

连接Mysql

sudo service mysql start

使用账号密码登录

mysql -u root

二、创建数据库并插入数据

在数据库中新建一张表的语句格式为：

CREATE TABLE 表的名字
(
列名a 数据类型(数据长度),
列名b 数据类型(数据长度)，
列名c 数据类型(数据长度)
);

我们尝试在 mysql_shiyan 中新建一张表 employee，包含姓名，ID 和电话信息，所以语句为：

CREATE TABLE employee (id int(10),name char(20),phone int(12));

或者另一种格式

三、数据的类型

MySQL 支持所有标准 SQL 数值数据类型。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，存储 UTC 时间，显示时自动转换时区。当更新记录时，如果未显式修改 TIMESTAMP 列的值，MySQL 会自动将其更新为当前时间。

类型	大小 ( bytes)	范围	格式	用途
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	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

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

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。一个中文字符在UTF-8下存储为三个字节
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

	CHAR(M)	VARCHAR(M)
存储时	自动填充空格到 M 长度	保留原始输入的空格
检索时	自动截断尾部空格	保留尾部空格

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

枚举与集合类型（Enumeration and Set Types）

• ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合，选一个。
• SET: 集合类型，用于存储多个值，可以选择多个预定义的集合,可多选。

空间数据类型（Spatial Data Types）

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

插入数据

格式：
INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

注意：
CHAR ,VARCHAR,TEXT,DATE,TIME,ENUM 等类型的数据需要单引号修饰，而 INT,FLOAT,DOUBLE 等则不需要
TIME类型在MySQL中无需指定长度
key是保留关键字

我们尝试向 employee 中加入 Tom、Jack 和 Rose：

INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);

INSERT INTO employee VALUES(02,'Jack',119119119);

INSERT INTO employee(id,name) VALUES(03,'Rose');

可知表名后可带列名参数插入数据，也可以不要。03的phone值为NULL，因为没有插入

实例

create database library; 
use library;
create table book( book_id int primary key, book_name varchar(50) not null ); create table reader( reader_id int primary key, reader_name varchar(50) not null );

四、约束

在 MySQL 中，通常有这几种约束：

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

创建约束

可以在使用 CREATE TABLE 语句创建表时或在使用 创建表后指定约束 ALTER TABLE 语句。

CREATE TABLE _table_name_ (  
    _column1 datatype_ _constraint_,  
    _column2 datatype_ _constraint_,  
    _column3 datatype_ _constraint_,  
    ....  
);

PRIMARY KEY：主键约束​​

PRIMARY KEY（主键）是数据库表的核心约束，用于​​唯一标识表中的每一条记录​​。为 book_id 添加主键约束后，需满足以下规则：

• ​​唯一性​​：表中所有记录的 book_id 值必须互不相同（不能重复）。
• ​​非空性​​：book_id 不能为空（NULL），每条记录必须有值。
• ​​索引优化​​：MySQL 会自动为 PRIMARY KEY 创建索引（主键索引），大幅提升基于 book_id 的查询效率（如 WHERE book_id=1）。

DEFAULT: 默认值约束

默认值约束 (DEFAULT) 规定，当有 DEFAULT 约束的列，插入数据为空时，将使用默认值。
括号就是函数传参

UNIQUE : 唯一约束

唯一约束 (UNIQUE) 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。当 INSERT 语句新插入的数据和已有数据重复的时候，如果有 UNIQUE 约束，则 INSERT 失败，比如：
`UNIQUE (name)'

FOREIGN KEY：外键约束
一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值

非空约束
非空约束 (NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空

否则会报错，Mysql​​5.7 及以上​​默认启用 STRICT_TRANS_TABLES直接报错

创建一个简易的成绩管理系统的数据库

五、SELECT语句

SELECT 基本语法

基本格式：
SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

SELECT name,age FROM employee;

数学符号条件

SELECT 语句常常会有 WHERE 限制条件，用于达到更加精确的查询。WHERE 限制条件可以有数学符号 (=,<,>,>=,<=) ，刚才我们查询了 name 和 age，现在稍作修改：

SELECT name,age FROM employee WHERE age>25;

或者查找一个名字为 Mary 的员工的 name,age 和 phone：

SELECT name,age,phone FROM employee WHERE name='Mary';

AND和OR

WHERE 后面可以有不止一条限制，而根据条件之间的逻辑关系，可以用 条件一 OR 条件二和 条件一 AND 条件二 连接：

例如，筛选出 age 小于 25，或 age 大于 30

SELECT name,age FROM employee WHERE age<25 OR age>30;

而刚才的限制条件 age>25 AND age<30 ，如果需要包含 25 和 30 这两个数字的话，可以替换为 age BETWEEN 25 AND 30

IN和NOT IN

关键词 IN 和 NOT IN 的作用和它们的名字一样明显，用于筛选“在”或“不在”某个范围内的结果，比如说我们要查询在 dpt3  或 dpt4 的人:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

而 NOT IN 的效果则是，如下面这条命令，查询出了不在 dpt1 也不在 dpt3 的人：

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

通配符

关键字 LIKE 可用于实现模糊查询，常见于搜索功能中。

和 LIKE 联用的通常还有通配符，代表未知字符。SQL 中的通配符是 _ 和 % 。其中 _ 代表一个未指定字符，% 代表不定个未指定字符
比如，要只记得电话号码前四位数为 1101，而后两位忘记了，则可以用两个 _ 通配符代替：

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

另一种情况，比如只记名字的首字母，又不知道名字长度，则用 % 通配符代替不定个字符：

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

对结果排序

为了使查询结果看起来更顺眼，我们可能需要对结果按某一列来排序，这就要用到 ORDER BY 排序关键词。默认情况下，ORDER BY 的结果是升序排列，而使用关键词 ASC 和 DESC 可指定升序或降序排序。 比如，我们按 salary 降序排列，SQL 语句为：

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

SQL 内置函数和计算

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

其中 COUNT 函数可用于任何数据类型(因为它只是计数)，而 SUM 、AVG 函数都只能对数字类数据类型做计算，MAX 和 MIN 可用于数值、字符串或是日期时间数据类型

具体举例，比如计算出 salary 的最大、最小值，用这样的一条语句：

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

有一个细节你或许注意到了，使用 AS 关键词可以给值重命名，比如最大值被命名为了 max_salary：

子查询

想要知道名为 "Tom" 的员工所在部门做了几个工程。员工信息储存在 employee 表中，但工程信息储存在 project 表中。
对于这样的情况，我们可以用子查询：

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

上面代码包含两个 SELECT 语句，第二个 SELECT 语句将返回一个集合的数据形式，然后被第一个 SELECT 语句用 in 进行判断。
HAVING 关键字可以的作用和 WHERE 是一样的，都是说明接下来要进行条件筛选操作。
区别在于 HAVING 用于对分组后的数据进行筛选，子查询可以拓展到更多层

连接查询

如果需要显示两个表或多个表中的数据，这时就必须使用连接 (join) 操作。 连接的基本思想是把两个或多个表当作一个新的表来操作，如下：

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

这条语句查询出的是，各员工所在部门的人数，其中员工的 id 和 name 来自 employee 表，people_num 来自 department 表：
另一个连接语句格式是使用 JOIN ON 语法，刚才的语句等同于：

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

WHERE改成了ON

六、数据库及表的修改和删除

删除数据库

DROP DATABASE 数据库名字;

对一张表的修改

重命名一张表的语句有多种形式，以下 3 种格式效果是一样的：

RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

删除一张表的语句，类似于刚才用过的删除数据库的语句，格式是这样的：

DROP TABLE 表名字;

对表结构的修改

1、 增加一列
在表中增加一列的语句格式为：

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或：
ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

现在 employee 表中有 id、name、age、salary、phone、in_dpt 这 6 个列，我们尝试加入 height (身高)一个列并指定 DEFAULT 约束：

ALTER TABLE employee ADD height INT(4) DEFAULT 170;

如果要把增加的列插入在指定位置，则需要在语句的最后使用 AFTER 关键词(“AFTER 列 1” 表示新增的列被放置在 “列 1” 的后面)。如果想要放到第一排，则直接在语句末尾加FIRST就好

2、删除一列
删除表中的一列和刚才使用的新增一列的语句格式十分相似，只是把关键词 ADD 改为 DROP ，语句后面不需要有数据类型、约束或位置信息。具体语句格式：

ALTER TABLE 表名字 DROP COLUMN 列名字;

或： ALTER TABLE 表名字 DROP 列名字;

我们把刚才新增的 test 删除：

ALTER TABLE employee DROP test;

3、重命名一列
这条语句其实不只可用于重命名一列，准确地说，它是对一个列做修改(CHANGE) ：

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

注意：这条重命名语句后面的 “数据类型” 不能省略，否则重命名失败。

当原列名和新列名相同的时候，指定新的数据类型或约束，就可以用于修改数据类型或约束。需要注意的是，修改数据类型可能会导致数据丢失，所以要慎重使用。

4、改变数据类型
要修改一列的数据类型，除了使用刚才的 CHANGE 语句外，还可以用这样的 MODIFY 语句：

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

对表的内容修改

1、修改表中某个值
大多数时候我们需要做修改的不会是整个数据库或整张表，而是表中的某一个或几个数据，这就需要我们用下面这条命令达到精确的修改：

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

比如，我们要把 Tom 的 age 改为 21，salary 改为 3000：

UPDATE employee SET age=21,salary=3000 WHERE name='Tom';

2、 删除一行记录
删除表中的一行数据，也必须加上 WHERE 条件，否则整列的数据都会被删除。删除语句：

DELETE FROM 表名字 WHERE 条件;

我们尝试把 Tom 的数据删除：

DELETE FROM employee WHERE name='Tom';

七、其他操作

索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容
对一张表中的某个列建立索引，有以下两种语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);

我们用这两种语句分别建立索引：

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引

索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。这里我们使用命令
SHOW INDEX FROM 表名字;

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表
它就像一个窗口
创建视图的语句格式为：

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

可见创建视图的语句，后半句是一个 SELECT 查询语句，所以视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询
现在我们创建一个简单的视图，名为 v_emp，包含v_name，v_age，v_phone三个列：

CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;

导入

导入用SQL语句编写的文件：source *.sql
导入一个纯数据文件，该文件中将包含与数据表字段相对应的多条数据，这样可以快速导入大量数据
由于导入导出大量数据都属于敏感操作，根据 mysql 的安全策略，导入导出的文件都必须在指定的路径下进行，在 mysql 终端中查看路径变量：

mysql -u root
mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)

注意到 secure_file_priv 变量指定安全路径为 /var/lib/mysql-files/ ，要导入数据文件，需要将该文件移动到安全路径下

现在执行导入语句，文件中的数据成功导入 employee 表：

LOAD DATA INFILE '/var/lib/mysql-files/in.txt' INTO TABLE employee;

导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

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

注意：语句中 “文件路径” 之下不能已经有同名文件。路径要带单引号

备份

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

恢复

source /home/shiyanlou/Desktop/MySQL-06.sql

这就是一条恢复语句，它把 MySQL-06.sql 文件中保存的 mysql_shiyan 数据库恢复。
还有另一种方式恢复数据库，但是在这之前我们先使用命令新建一个空的数据库 test：

mysql -u root          #因为在上一步已经退出了 MySQL，现在需要重新登录

CREATE DATABASE test;  #新建一个名为test的数据库

再次 Ctrl+D 退出 MySQL，然后输入语句进行恢复，把刚才备份的 bak.sql 恢复到 test 数据库：

mysql -u root test < bak.sql

我们输入命令查看 test 数据库的表，便可验证是否恢复成功：

mysql -u root  # 因为在上一步已经退出了 MySQL，现在需要重新登录