MySQL入门基础

1. MySQL数据库操作基础

• MySQL指令的大小写不敏感，但是库名和表明的创建是区分大小写的。

No.	操作名称	mysql>运行指令	释义补充
1	创建数据库	create database [if not exists] DataBD1 [charset=utf8] [collate utf8_general_ci];	在MySQL路径/var/lib/mysql下创建一个名称为DataBD1的目录; [指定中文编码格式] [增加一个uft8校验规则]
2	删除数据库	drop database DataBd1;	删除MySQL路径下的与数据库同名的目录
3	[查看] 所有数据库	show databases;	展示所有数据库目录
4	使用数据库	use DataBD1	进入 DataBD1这个目录
5	改变数据库 编码格式	alter database person charset=uft8;	将编码格式改成utf8
6	[查看] 数据库引擎	show engines \G:	InnoDB、MyISAM
7	[查看] 中文编码格式	show charset;	uft8、GBK
8	[查看] 校验规则	show collation;	utf8_general_ci数据库规则不区分大小写 utf8_bin数据库规则区分大小写 （区分大小写：筛选时使用）
9	[查看] 表单规则	show create table person [\G];	查看person表单的详细数据信息
10	[查看] 字符集	show variables like ‘character_%’;	% ：通配符
11	[查看]数据库的使用情况	show processlist;

• 备份对指定的数据库的历史操作，binlog会记录

mysqldump -P3306 -u root -p 'password' -B database  > BackupPath

• 备份数据还原

source BackupPath

2. 数据表操作

No.	操作名称	mysql>运行指令	释义补充
1	创建表单	create table if not exists person (name char(8),height int ); [character set utf8 engine InnoDB];	person为表的名称；在DataBD1目录下创建 表结构文件(person.frm)和数据索引文件(person.ibd) [指定编码格式和引擎]
2	[查看] 表单信息	desc person;
3	插入数据	insert into person (name, height) values (“MicroCC”,185);	将数据写道缓存，缓存满了后刷新到磁盘
4	[查看]数据	select * from person [where name = “MicroCC” and height <> 185];	一行代表一个记录，一列代表一个属性 （列名称不能重复） [指定筛选条件,名字为MicroCC且身高不为185]
5	删除表中数据	delete [table] from person [where name=’ ']|[查询条件]; delete from person;	删除select选中的内容;删除指定table 清空表自增的数据的记录着不会清空
6	清空表	truncate [table] table_name	清空表自增数据的记录值会被清空
7	增加表格属性	alter table person add primary key(name); alter table person add weight int comment ‘人员身高’; alter table person add weight int comment ‘人员身高’ after name;	add
8	消除属性	alter table person drop primary key; alter table person drop weight;	drop 删除会删除表中该 属性的所有数据
9	修改表属性	alter table person modify weight varchar(5);	modify对某一列的属性进行修改
10	修改表名	alter table person(旧名字) rename User(新名字);	rename; show tables;查看
11	重命名一列属性	alter table User change weight(旧名字) WH(新名字) char(5);	change
12	数据更新	insert into person (name, height) values (“MicroCC”,189) ON duplicate key update height=189	数据冲突则会修改数据 0 rows affected: 冲突数据未更新 1 rows affected: 直接插入 2 rows affected: 冲突数据被更新
13	数据替换	replace into person (name, weight) values (‘Captain’, 200);	1 rows affected: 直接插入 2 rows affected: 冲突数据被删除插入

• 数据的查询

select [distinct] {} [from table] [where] [order by ] limit…

select 条件数据选则执行顺序：1、where ； 2、select； 3、order by；4、limit

select * from table_names limit 20; # 限制20行筛选
select name,id,height / weight [as] total from animals; # 按列名字\计算列表达式并指明列名为total，查找
select distinct weight from animals; # 数据去重
select name,id from animals where id > 2; # 按条件查找

select * from animals order by id [asc|desc]; # 按升序asc或降序desc排序

select name,height+weight as total from animals where total > 20;
# where条件先于之前执行，where会不认识 total；total别名是在数据全部筛选后赋上的。

• where 条件扩充

运算符	释义	运算符	释义
普通运算符 >, >=, <, <=	/	and	且，结果是true（1）或者false（0）
=	等于，注：NULL=NULL结果是NULL	or	或
<=>	等于，注：NULL=NULL结果是true	not	非
!= , <>	不等于	is NULL; is not NULL	是不是NULL
between a and b	[a,b]范围	in(option……)	满足任意条件的，true
like	模糊匹配。%：0个或多个，_：一个字符

• limit

mysql> select id,name,gender from animals limit 1,2; # 默认从0开始，第一个若有数值，则从第一个数开始，往下几个
+----+-----------+--------+
| id | name      | gender |
+----+-----------+--------+
|  2 | dog       | 雄     |
|  3 | butterfly | 雄     |
+----+-----------+--------+
2 rows in set (0.01 sec)


mysql> select id,name,gender from animals limit 1 offset 2; # offset为2处，选中一行
+----+-----------+--------+
| id | name      | gender |
+----+-----------+--------+
|  3 | butterfly | 雄     |
+----+-----------+--------+
1 row in set (0.00 sec)

• 数据更新——对已存在的数据更新

update table_name set name = [] [where……] [order by……] [limit……]

update会在数据查询后进行修改。

mysql> update animals set weight=20 where name='cat';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from animals;
+-----------+----+--------+--------+--------+
| name      | id | height | weight | gender |
+-----------+----+--------+--------+--------+
| cat       |  1 | 000020 |     20 | 雄     |
| dog       |  2 |   NULL |   NULL | 雄     |
| butterfly |  3 |   NULL |   NULL | 雄     |
| ice frog  |  4 |   NULL |   NULL | 雄     |
+-----------+----+--------+--------+--------+
4 rows in set (0.00 sec)

• 对查询的结果进行其他操作，如插入新表格

insert into table_name [] select ……

mysql> create table test_animals like animals; # 创建表结构和animals一样的空表
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test_animals select * from animals where id=1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test_animals;
+------+----+--------+--------+--------+
| name | id | height | weight | gender |
+------+----+--------+--------+--------+
| cat  |  1 | 000020 |     20 | 雄     |
+------+----+--------+--------+--------+
1 row in set (0.00 sec)

3. MySQL数据类型

MySQL对数据的检查是严格的，当输入的数据超过了类型的范围，数据插入就会报错。以下是MySQL数据类型能取到的范围：

• 数值类型

类型	大小(Bytes)	范围（signed）	范围（unsigned）	补充
TINYINT	1	[-128，127]	[0，255]
SMALLINT	2	[-32 768，32 767]	[0，65 535]
MEDIUMINT	3	[-8 388 608，8 388 607]	[0，16 777 215]
INT	4	[-2 147 483 648，2 147 483 647]	[0，4 294 967 295]
BIGINT	8	(-2^63, 2^63-1)	(0，2^64-1)
FLOAT(M,D)	4	依赖于M和D的值	依赖于M和D的值	M:数据长度，D:小数位数 超过了精度四舍五入
DOUBLE(M,D)	8	依赖于M和D的值	依赖于M和D的值	id double(3,2) unsigned
DECIMAL(M,D)	若M>D，为M+2 否则为D+2	依赖于M和D的值	依赖于M和D的值	精确程度更高 M最大65，默认10； D最大30，默认0。
BOOL	tinyint(1)
BIT(M)	M默认为1，为bit位数			以Ascii码显示

• 文本二进制

类型	大小(Bytes)	补充
CHAR(L)	0-255	长度不可超过L；L不代表存储的字节数，而是实际的子符个数 无论是一个中文、还是一个英文字母，都代表一个字符 指定L长度后，实际占用的空间就是L*3(utf8)
VARCHAR(L)	0-65535	变长字符串，会有1-3个字节记录长度，实际的字节数为65532 urf8,一个字符占3字节，L最大为65532 / 3 = 21844 gbk,一个字符占2字节，L最大为65532 / 2 = 32766 指定L长度后，实际占用的空间取决于存入的数据 （例如：L =4， 但只插入一个字符，则实际占用utf8-> 1 * 3+ 1 = 4）1字节记录长度
TINYBLOB	0-255	不超过 255 个字符的二进制字符串
TINYTEXT	0-255	短文本字符串
BLOB	0-65 535	二进制形式的长文本数据
TEXT	0-65 535	长文本数据
MEDIUMBLOB	0-16 777 215	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215	中等长度文本数据
LONGBLOB	0-4 294 967 295	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295	极大文本数据

• 字符串

类型	说明
ENUM	枚举（多选一），只能插入枚举字段中的值或数字，若超过范围或出现其他枚举内容则报错； 下标从1开始：1，2，3……代表枚举内容
SET	集合（多选多），只能插入集合字段中的值或数字，若超过范围或出现其他集合内容则报错； 下标从1(00001)开始，每个bit位代表一个集合内容

mysql> create table Info( id int comment '考生id', Grade enum('及格','不及格') comment '是否及格', Course set('数学','语文','英语','物理','化学','生物') comment '考试科目' );
mysql> desc Info;
+--------+------------------------------------------------------------+------+-----+---------+-------+
| Field  | Type                                                       | Null | Key | Default | Extra |
+--------+------------------------------------------------------------+------+-----+---------+-------+
| id     | int(11)                                                    | YES  |     | NULL    |       |
| Grade  | enum('及格','不及格')                                      | YES  |     | NULL    |       |
| Course | set('数学','语文','英语','物理','化学','生物')             | YES  |     | NULL    |       |
+--------+------------------------------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into Info values (1, '及格', '数学,语文');  # 注意,不带空格
Query OK, 1 row affected (0.01 sec

mysql> select * from Info;
+------+--------+---------------+
| id   | Grade  | Course        |
+------+--------+---------------+
|    1 | 及格   | 数学,语文     |
+------+--------+---------------+
1 row in set (0.00 sec)


mysql> insert into Info values (3, '1', '63');
Query OK, 1 row affected (0.01 sec)

mysql> select * from Info;
+------+--------+-------------------------------------------+
| id   | Grade  | Course                                    |
+------+--------+-------------------------------------------+
|    1 | 及格   | 数学,语文                                 |
|    2 | 及格   | 生物                                      |
|    3 | 及格   | 数学,语文,英语,物理,化学,生物             |
+------+--------+-------------------------------------------+
4 rows in set (0.00 sec)

补充：集合的查找

mysql> select find_in_set('6','1,2,3,4,5,6,2,1');   # 查找6在集合中是第几个出现的
+------------------------------------+
| find_in_set('6','1,2,3,4,5,6,2,1') |
+------------------------------------+
|                                  6 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select * from Info where Course='数学';# 查找Course='数学'，严格匹配 ; <>不等于
Empty set (0.00 sec)

mysql> select * from Info where find_in_set('数学',Course);  # 查找Course中有'数学的。
+------+--------+-------------------------------------------+ 
| id   | Grade  | Course                                    |
+------+--------+-------------------------------------------+
|    1 | 及格   | 数学,语文                                 |
|    3 | 及格   | 数学,语文,英语,物理,化学,生物             |
+------+--------+-------------------------------------------+
3 rows in set (0.00 sec)

• 日期

类型	大小(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	/	YYYYMMDD HHMMSS	时间戳

mysql> create table if not exists data( d1 date d2 time d3 year d4 datetime d5 timestamp );
mysql> desc data;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d1    | date      | YES  |     | NULL              |                             |
| d2    | time      | YES  |     | NULL              |                             |
| d3    | year(4)   | YES  |     | NULL              |                             |
| d4    | datetime  | YES  |     | NULL              |                             |
| d5    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
# 时间戳NULL为no，在不插入时会自动更新为当前系统的时间戳。
mysql> insert into data (d1, d2, d3, d4) values ('2022-1-1', '9:19:19', '2022', '2021-1-1 10:2:1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from data;
+------------+----------+------+---------------------+---------------------+
| d1         | d2       | d3   | d4                  | d5                  |
+------------+----------+------+---------------------+---------------------+
| 2022-01-01 | 09:19:19 | 2022 | 2021-01-01 10:02:01 | 2022-06-28 13:23:37 |
+------------+----------+------+---------------------+---------------------+
1 row in set (0.01 sec)

4. 数据表的属性约束

mysql> desc animals;
+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| name   | varchar(10)       | NO   |     | NULL    |       |
| id     | int(11)           | NO   |     | NULL    |       |
| height | int(11)           | YES  |     | NULL    |       |
| weight | int(11)           | YES  |     | NULL    |       |
| gender | enum('雌','雄')   | YES  |     | 雄      |       |
+--------+-------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

属性类别	属性关键字	说明
空属性	select NULL; 属性名为null的属性为NULL	当数据的空属性(NULL)为YES时，可以选填，默认NULL 空属性为NO时，必须插入对应的值。
默认值	default	不指定输入时，使用默认值；添加默认值空属性不发挥左右了
列描述	comment	添加描述，方便了解含义
零值填充	zerofill	完整显示数据长度
主键	primary key	设置某个Field为查找的主要对象； 数据插入时，主键的值不可重复，不可为空，该数据是唯一的。 主键设置时，会将空属性设为NO 允许多个列一起充当一个主键（复合主键）
自增长	auto_increment	设置为主键值的整数字段，可以设为自增; 一个表里最多只有一个自增字段
唯一键	unique	保证某类数据的存储是唯一的，只针对非NULL有效， NULL可以任意插入
外键	foreign key() references table()	外键在从表定义，用于关联主表数据信息。 主表中被关联的数据必须是unique或primary key属性的 外键的数据必须是主表中存在的（NULL也可）

• 使用not null添加约束

mysql> create table if not exists animals( name varchar(10) NOT NULL, id int NOT NULL, height int, weight int);  # 使用not null 添加约束。默认为NULL
Query OK, 0 rows affected (0.03 sec)

mysql> desc animals;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | NO   |     | NULL    |       |
| id     | int(11)     | NO   |     | NULL    |       |
| height | int(11)     | YES  |     | NULL    |       |
| weight | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into animals values ('cat', '1',30, 30);
Query OK, 1 row affected (0.00 sec)

mysql> insert into animals (name,id) values ('dog','2'); # 对于NULL为YES的可不指定数据，该字段允许为空
Query OK, 1 row affected (0.00 sec)

mysql> insert into animals (height,weight) values (20 ,30);  # name、id没有默认的null，该字段不允许为空
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> select * from animals;
+------+----+--------+--------+
| name | id | height | weight |
+------+----+--------+--------+
| cat  |  1 |     30 |     30 |
| dog  |  2 |   NULL |   NULL |
+------+----+--------+--------+
2 rows in set (0.01 sec)

• 添加默认属性

mysql> alter table animals add gender enum('雌','雄') default '雄';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from animals;
+------+----+--------+--------+--------+
| name | id | height | weight | gender |
+------+----+--------+--------+--------+
| cat  |  1 |     30 |     30 | 雄     |
| dog  |  2 |   NULL |   NULL | 雄     |
+------+----+--------+--------+--------+
2 rows in set (0.00 sec)

• 显示数据宽度

mysql> alter table animals modify height int(6) unsigned zerofill; # 添加zerofill字段后完整显示数据长度
Query OK, 2 rows affected (0.10 sec)                              # int(6)表示整型显示宽度为6
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from animals;
+------+----+--------+--------+--------+
| name | id | height | weight | gender |
+------+----+--------+--------+--------+
| cat  |  1 | 000030 |     30 | 雄     |
| dog  |  2 |   NULL |   NULL | 雄     |
+------+----+--------+--------+--------+
2 rows in set (0.00 sec)

• 添加主键

mysql> alter table animals add primary key(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc animals;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| name   | varchar(10)              | NO   |     | NULL    |       |
| id     | int(11)                  | NO   | PRI | NULL    |       |
| height | int(6) unsigned zerofill | YES  |     | NULL    |       |
| weight | int(11)                  | YES  |     | NULL    |       |
| gender | enum('雌','雄')          | YES  |     | 雄      |       |
+--------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> insert into animals (name,id) values ('mouse',1);  # 添加主键后，id的内容不可再重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> alter table animals drop primary key;  # 删除主键不需要指定id，因为一张表里只有一个主键
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table animals add primary key(name,id);  # 复合主键；插入数据时，name和id只要有一个不同，就可插入
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc animals;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| name   | varchar(10)              | NO   | PRI | NULL    |       |
| id     | int(11)                  | NO   | PRI | NULL    |       |
| height | int(6) unsigned zerofill | YES  |     | NULL    |       |
| weight | int(11)                  | YES  |     | NULL    |       |
| gender | enum('雌','雄')          | YES  |     | 雄      |       |
+--------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

• 增加自增属性

mysql> alter table animals change id id int not null auto_increment primary key;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc animals;
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| name   | varchar(10)              | NO   |     | NULL    |                |
| id     | int(11)                  | NO   | PRI | NULL    | auto_increment |
| height | int(6) unsigned zerofill | YES  |     | NULL    |                |
| weight | int(11)                  | YES  |     | NULL    |                |
| gender | enum('雌','雄')          | YES  |     | 雄      |                |
+--------+--------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into animals (name) values ('butterfly');
Query OK, 1 row affected (0.01 sec)

mysql> insert into animals (name) values ('ice frog');
Query OK, 1 row affected (0.01 sec)
mysql> select * from animals;   # id 自动增长。
+-----------+----+--------+--------+--------+
| name      | id | height | weight | gender |
+-----------+----+--------+--------+--------+
| cat       |  1 | 000030 |     30 | 雄     |
| dog       |  2 |   NULL |   NULL | 雄     |
| butterfly |  3 |   NULL |   NULL | 雄     |
| ice frog  |  4 |   NULL |   NULL | 雄     |
+-----------+----+--------+--------+--------+
4 rows in set (0.00 sec)

• 建立外键约束

mysql> create table if not exists dogs( name varchar(10) unique, dog_id int primary key, class_id int, foreign key(class_id) references animals(id) );   # 注意：foreign中相关联的两个数据类型必须一致。
Query OK, 0 rows affected (0.04 sec)

mysql> insert into dogs (name, dog_id) values ('Tony',1),('John',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into dogs values ('Dahuang',3, 2);
Query OK, 1 row affected (0.00 sec)


mysql> select * from dogs;
+---------+--------+----------+
| name    | dog_id | class_id |
+---------+--------+----------+
| Tony    |      1 |     NULL |
| John    |      2 |     NULL |
| Dahuang |      3 |        2 |
+---------+--------+----------+
3 rows in set (0.00 sec)

mysql> insert into dogs values ('Jennifer',3, 10);  # 只能插入的class_id，必须出现在animals的id中
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

5. 聚合函数

函数	说明
count	自动求满足条件的行数（自动过滤为NULL的字段）
sum	总和
avg	平均值
max	最大值
min	最小值
group by	select分组显示，需要与以上函数搭配使用。group by goup1,group2…, 使用having进行条件筛选

• 例子

mysql> select * from animals;
+-----------+----+--------+--------+--------+
| name      | id | height | weight | gender |
+-----------+----+--------+--------+--------+
| cat       |  1 | 000020 |     20 | 雄     |
| dog       |  2 | 000030 |   NULL | 雄     |
| butterfly |  3 | 000030 |   NULL | 雄     |
| ice frog  |  4 | 000030 |   NULL | 雄     |
+-----------+----+--------+--------+--------+
4 rows in set (0.00 sec)

mysql> select count(distinct height) from animals;
+------------------------+
| count(distinct height) |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)
#########################################################
mysql> select gender,avg(height) from animals group by gender;
+--------+-------------+
| gender | avg(height) |
+--------+-------------+
| 雄     |     27.5000 |
+--------+-------------+
1 row in set (0.00 sec)

mysql> select gender,avg(height) from animals group by gender having avg(height) > 20;