Linux实战之MySQL数据库——MySQL管理命令与基础操作_mysql linux

mysql管理命令

mysql命令行管理

（1）数据库链接
链接本地数据库：mysql -uroot -pCloudbu@123
链接远程数据库：mysql -h192.168.213.129 -uroot -pCloudbu@123

（2）数据库操作
查看

使用 use databasename;
创建 create database test；
删除 drop database test;
（3）数据库表的操作
查看当前数据库下拥有的所有表： show tables
查看数据库表属性定义：desc user;

创建新账号

mysql> use mysql;
mysql> create user 'anliu01'@'%' identified by 'Zhao123@com';
mysql> flush privileges;
1
2
3

mysql新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问（重新启动mysql服务器也可以使新设置生效）

flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里 【在"不重启MySQL服务"的情况下直接生效，重启风险太大】

mysqladmin工具

mysqladmin是一个执行管理操作的工具，它可以检查服务器的配置和当前状态，创建和删除数据库等

mysqladmin [option] command [command option] command ......
1

OPTION

示例

(1) 查看服务器状态

[root@nfs01 ~]# mysqladmin -uroot -p status
Enter password:
Uptime: 1054  Threads: 2  Questions: 16  Slow queries: 0  Opens: 150  Flush tab               les: 3  Open tables: 54  Queries per second avg: 0.015
1
2
3

(2) 修改root密码

[root@nfs01 ~]# mysqladmin -uroot -pZhao123@com password 'New123@com'
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
1
2

(3) 查看mysqlserver是否可用

[root@nfs01 ~]# mysqladmin -uroot -p ping
Enter password:
mysqld is alive
1
2
3

(4) 查看mysql版本
mysqladmin -uroot -p version
(5) 显示服务器的所有运行进程
mysqladmin -uroot -p -i 1 processlist
(5) 创建数据库
mysqladmin -uroot -p create data_test
(6) 显示系统的所有数据库
mysqlshow -uroot -p
(7) 显示数据库中的所有表
mysqlshow -uroot -p datebasename
(8) 显示数据库表列的统计

[root@nfs01 ~]# mysqlshow -uroot -p school -v
Enter password:
Database: school
+----------+----------+
|  Tables  | Columns  |
+----------+----------+
| student1 |        4 |
| student2 |        6 |
| student3 |        4 |
+----------+----------+
3 rows in set.
1
2
3
4
5
6
7
8
9
10
11

(9) 显示数据库表列和行的统计

[root@nfs01 ~]# mysqlshow -uroot -p school -v -v
Enter password:
Database: school
+----------+----------+------------+
|  Tables  | Columns  | Total Rows |
+----------+----------+------------+
| student1 |        4 |          4 |
| student2 |        6 |          2 |
| student3 |        4 |          1 |
+----------+----------+------------+
3 rows in set.
1
2
3
4
5
6
7
8
9
10
11

(10) 删除数据库
mysqladmin -uroot -p drop date_test
(11) 重载权限信息
mysqladmin -uroot -p reload

mysqldump数据库逻辑备份

mysqldump是数据库逻辑备份程序
应用场景 非大数据备份解决方案、SQL语句重现、备份数据表需要SELECT权限

OPTION

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
1

关于数据库名：

关于其它参数说明：
--single-transaction #基于此项可以实现对InnoDB表做热备份
-x, --lock-all-tables #执行备份时为所有表请求加锁 MyISAM
-l, --lock-tables
-E, --events #备份事件调度器代码
--opt #同时启动各种高级选项
-R, --routines #备份存储过程和存储函数
-F, --flush-logs #备份之前刷新日志
--triggers #备份触发器
--master-data=2 #备库，该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将会输出CHANGE MASTER命令，主从下有用
-d #只备份库结构，不包含数据内容
-e, --extended-insert  #用INSERT DELAYED命令插入行，即设置扩展Insert（-e --skip-extended-insert禁用扩展Insert）
1
2
3
4
5
6
7
8
9
10
11
12

示例

https://blog.csdn.net/qq_42049496/article/details/104560537

数据库基础操作

系统数据库

数据库存储引擎

数据存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据库操作，mysql的核心就是存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛不同的功能，通过删选不同的存储引擎可以获得额外的速度或者功能。

查看存储引擎 mysql> show engines\G

*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
1
2
3
4
5
6
7

InnoDB是默认的引擎，Support的值为 Default

存储引擎的选择

（1）几种常用存储引擎汇总

（2）存储引擎的选择
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

除非需要InnoDB不具备的特性，并且没有其他办法替代，否则都应该优先考虑InnoDB；不需要InnoDB的特性，并且其他的引擎更加合适当前情况，例如多读少写，对数据恢复要求不高，反而是对存储空间要求较高

数据表的基本操作

表的创建

创建表

语法

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];

==在同一张表中，字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的
1
2
3
4
5
6
7
8
9

示例1

#创建数据库school
mysql> create database school;
Query OK, 1 row affected (0.03 sec)
#在数据库school中创建数据表student1
mysql> create table school.student1(
    -> id int,
    -> name varchar(50),
    -> sex enum('m','f'),
    -> age int
    -> );
Query OK, 0 rows affected (0.11 sec)
#进入数据库school
mysql> use school
#查看数据表student1的结构
mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
#在数据表student1中插入表项
mysql> insert into student1 values
    -> (1,'tianyun','m',33),
    -> (2,'alice','f',20),
    -> (3,'jack','f',40);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0
#在数据表student1中插入指定表项
mysql> insert into student1(name) values ('kang');
Query OK, 1 row affected (0.01 sec)
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
30
31
32
33
34

示例2

mysql> create table student2(
    -> id int,
    -> name varchar(50),
    -> born_year year,
    -> birthday date,
    -> class_time time,
    -> reg_time datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc student2;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| born_year  | year(4)     | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| class_time | time        | YES  |     | NULL    |       |
| reg_time   | datetime    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> insert into student2 values(1,'tom',now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> insert into student2 values(1,'tom',1992,19920101,125959,20200217181323);
Query OK, 1 row affected (0.03 sec)

mysql> select * from student2;
+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birthday   | class_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | tom  |      2020 | 2020-02-18 | 18:16:18   | 2020-02-18 18:16:18 |
|    1 | tom  |      1992 | 1992-01-01 | 12:59:59   | 2020-02-17 18:13:23 |
+------+------+-----------+------------+------------+---------------------+
2 rows in set (0.00 sec)
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
30
31
32
33
34
35
36

示例3

mysql> create table student3( id int, name varchar(50), sex enum('male','female'),hobby set('music','book','game','disc') );
Query OK, 0 rows affected (0.04 sec)

mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| id    | int(11)                           | YES  |     | NULL    |       |
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('male','female')             | YES  |     | NULL    |       |
| hobby | set('music','book','game','disc') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> show create table student3\G
*************************** 1. row ***************************
       Table: student3
Create Table: CREATE TABLE `student3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `hobby` set('music','book','game','disc') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)

mysql> insert into student3 values(1,'tom','male','book,game');
Query OK, 1 row affected (0.03 sec)

mysql> select * from student3;
+------+------+------+-----------+
| id   | name | sex  | hobby     |
+------+------+------+-----------+
|    1 | tom  | male | book,game |
+------+------+------+-----------+
1 row in set (0.03 sec)
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
30
31
32
33
34
35

查看表结构

DESCRIBE 表名;
DESC 表名;
SHOW CREATE TABLE 表名;	#查看表详细结构
1
2
3

表完整性约束

用于保证数据的完整性和一致性

设置字段默认值 DEFAULT

mysql> create table student4(
    -> id int not null,
    -> name varchar(50) not null,
    -> sex enum('m','f')default 'm' not null,
    -> age int unsigned default 18 not null,
    -> hobby set('music','disc','dance','book')default 'book,dance'
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc student4;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(50)                        | NO   |     | NULL       |       |
| sex   | enum('m','f')                      | NO   |     | m          |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| hobby | set('music','disc','dance','book') | YES  |     | dance,book |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.01 sec)

mysql> insert into student4 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;
+----+------+-----+-----+-------+
| id | name | sex | age | hobby |
+----+------+-----+-----+-------+
|  1 | jack | m   |  20 | book  |
+----+------+-----+-----+-------+
1 row in set (0.00 sec)

mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student4 values(3,NULL,'m',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null
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
30
31
32
33
34
35
36

设置唯一约束 UNIQUE

mysql> create database company;
Query OK, 1 row affected (0.00 sec)
方法一：
mysql> create table company.department1(
    -> dept_id int,
    -> dept_name varchar(30) UNIQUE,
    -> comment varchar(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc company.department1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
方法二：
mysql>  create table company.department2(
    -> dept_id int,
    -> dept_name varchar(30),
    -> comment varchar(50),
    -> UNIQUE(dept_name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc company.department2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |     | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
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
30
31
32
33
34
35
36
37

设置主键约束 PRIMARY KEY

primary key字段的值是不允许重复，且不允许NULL（UNIQUE + NOT NULL）

单列做主键

方法一：
mysql> create table student6(
    -> id int primary key not null auto_increment,
    -> name varchar(50) not null,
    -> sex enum('male','female') not null default 'male',
    -> age int not null default 18
    -> );
Query OK, 0 rows affected (0.04 sec)
方法二：
mysql> create table student7(
    -> id int not null auto_increment,
    -> name varchar(50) not null,
    -> sex enum('male','female') not null default 'male',
    -> age int not null default 18,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student6 values(1,'alice','female',22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student6(name,sex,age) values
    -> ('jack','male',19),
    -> ('tom','male',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student6;
+----+-------+--------+-----+
| id | name  | sex    | age |
+----+-------+--------+-----+
|  1 | alice | female |  22 |
|  2 | jack  | male   |  19 |
|  3 | tom   | male   |  23 |
+----+-------+--------+-----+
3 rows in set (0.00 sec)
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
30
31
32
33
34
35
36

多列做主键（复合主键）

mysql> create table service(
    -> host_ip varchar(15) not null,
    -> service_name varchar(10) not null,
    -> port varchar(5) not null,
    -> allow enum('Y','N') default 'N',
    -> primary key(host_ip,port)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc service;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| host_ip      | varchar(15)   | NO   | PRI | NULL    |       |
| service_name | varchar(10)   | NO   |     | NULL    |       |
| port         | varchar(5)    | NO   | PRI | NULL    |       |
| allow        | enum('Y','N') | YES  |     | N       |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into service values
    -> ('192.168.213.168','ftp','21','Y'),
    -> ('192.168.213.168','httpd','80','Y');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from service;
+-----------------+--------------+------+-------+
| host_ip         | service_name | port | allow |
+-----------------+--------------+------+-------+
| 192.168.213.168 | ftp          | 21   | Y     |
| 192.168.213.168 | httpd        | 80   | Y     |
+-----------------+--------------+------+-------+
2 rows in set (0.00 sec)
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
30
31
32
33
34

设置字段值自增 AUTO_INCREMENT

mysql> CREATE TABLE company.department3(
    -> dept_id INT PRIMARY KEY AUTO_INCREMENT,
    -> dept_name VARCHAR(30),
    -> comment VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6

设置外键约束 FOREIGN KEY

使用外键约束可以保证数据的完整性，例如教师对应的表和课程表中老师的id

constraint 外键名 foreign key 外键字段 references 主表名(关联字段) [主表记录删除时的动
作] [主表记录更新时的动作]
1
2

外键约束方式

(1) cascade
级联方式，删除/更新父表的某条记录，子表中引用该值的记录会自动被删除/更新

mysql> create table employees(
name varchar(50) not null,
mail varchar(20),
primary key(name)
)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table payroll02(
id int auto_increment not null,
name varchar(50) not null,
payroll int not null,
primary key(id),
constraint employee foreign key(name) references employees(name) on delete cascade on update cascade
)engine=innodb;

mysql> insert into employees values
("zhangsan","zhangsan@163.com"),
("lisi","lisi@163.com");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into payroll02 values(1,"zahngsan",12000),(2,"lisi",13030);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`payroll02`, CONSTRAINT `employee` FOREIGN KEY (`name`) REFERENCES `employees` (`name`) ON DELETE CASCADE ON UPDATE CASCADE)
#无法添加或更新子行：外键约束失败
mysql> insert into payroll02 values(1,"zhangsan",12000),(2,"lisi",13030);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from employees;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from employees;
Empty set (0.00 sec)

mysql> select * from payroll02;
Empty set (0.00 sec)
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
30
31
32
33
34
35
36

结论：当父表中某个员工的记录修改时，子表也会同步修改；当父表中删除某个员工的记录，子表也会同步删除

(2) set null
设置为null，主表主键值被更新或删除，从表的外键被设置为null，但要求该外键列没有not null属性约束

mysql> insert into employees values ("zhangsan","zhangsan@163.com"), ("lisi","lisi@163.com");
mysql> insert into payroll02 values(1,"zhangsan",12000),(2,"lisi",13030);

mysql> alter table payroll02 drop foreign key employee;
mysql> alter table payroll02 add constraint employee foreign key(name) references employees(name) on delete set null on update set null;
ERROR 1830 (HY000): Column 'name' cannot be NOT NULL: needed in a foreign key constraint 'employee' SET NULL
#列“name”不能为空：外键约束“employee”SET NULL中需要
mysql> alter table payroll02 modify name varchar(50) null;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table payroll02 add constraint employee foreign key(name) references employees(name) on delete set null on update set null;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from employees where name="lisi";
Query OK, 1 row affected (0.00 sec)

mysql> select * from employees;
+----------+------------------+
| name     | mail             |
+----------+------------------+
| zhangsan | zhangsan@163.com |
+----------+------------------+
1 row in set (0.00 sec)

mysql> select * from payroll02;
+----+----------+---------+
| id | name     | payroll |
+----+----------+---------+
|  1 | zhangsan |   12000 |
|  2 | NULL     |   13030 |
+----+----------+---------+
2 rows in set (0.00 sec)
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
30
31
32
33
34

(3) 3.no action/restrict
禁止模式，拒绝父表删除和更新

mysql> alter table payroll02 drop foreign key employee;
mysql> alter table payroll02 add constraint employee foreign key(name) references employees(name) on delete no action on update restrict;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update employees set name="zhangsi" where name="zhangsan";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`payroll02`, CONSTRAINT `employee` FOREIGN KEY (`name`) REFERENCES `employees` (`name`) ON UPDATE RESTRICT)
#无法删除或更新父行：外键约束失败
1
2
3
4
5
6
7
8

外键约束默认是禁止模式

在创建表时添加外键不指定模式的情况下，需要关联主表的column必须是索引，如果不是索引无法添加外键约束

mysql> create table test2(
    -> course_id INT(11) NOT NULL AUTO_INCREMENT,
    -> identified_no INT(18) UNIQUE,
    -> name VARCHAR(30) DEFAULT NULL,
    -> PRIMARY KEY(course_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE test1(
    -> course_id INT(11) NOT NULL AUTO_INCREMENT,
    -> identified_no INT(18) UNIQUE,
    -> name VARCHAR(30) DEFAULT NULL,
    -> PRIMARY KEY(course_id),
    -> CONSTRAINT course_id1_fk FOREIGN KEY(name) REFERENCES test2 (name)
    -> );
ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'course_id1_fk' in the referenced table 'test2'
#无法添加外键约束。引用的表“test2”中缺少约束“course-id1-fk”的索引

mysql> drop table if exists test2;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test2(
    -> course_id INT(11) NOT NULL AUTO_INCREMENT,
    -> identified_no INT(18) UNIQUE,
    -> name VARCHAR(30) DEFAULT NULL,
    -> PRIMARY KEY(course_id),
    -> INDEX(name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE test1(
    -> course_id INT(11) NOT NULL AUTO_INCREMENT,
    -> identified_no INT(18) UNIQUE,
    -> name VARCHAR(30) DEFAULT NULL,
    -> PRIMARY KEY(course_id),
    -> CONSTRAINT course_id1_fk FOREIGN KEY(name) REFERENCES test2 (name)
    -> );
Query OK, 0 rows affected (0.04 sec)
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
30
31
32
33
34
35
36
37
38

结果：name列如果不是索引，无法作为外键的引用列，当我们添加name为索引，发现添加外键约束成功

修改表

1.修改表名
ALTER TABLE 表名 RENAME 新表名;
2.增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3.删除字段
ALTER TABLE 表名 DROP 字段名;
4.修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];	#不能修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
1
2
3
4
5
6
7
8
9
10
11

1.修改存储引擎
alter table student1 engine=innodb;
2.添加字段

mysql> create table student10(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int not null default 22;
1
2
3
4
5
6

3.删除字段
alter table student10 drop sex;
4.修改字段类型modify
alter table department1 modify dept_name char(30);

mysql> alter table department1
    -> modify dept_id int not null primary key;	#修改字段类型、约束、主键（已有的也必须写）
1
2

5.增加约束（针对已有的主键增加auto_increment不用再指明primary key）
alter table department1 modify dept_id int not null auto_increment;
6.增加[复合]主键
alter table service add primary key(host_ip[,port]);
7.增加主键和自动增长
alter table department1 modify dept_id int not null primary key auto_increment;
8.删除主键
alter table department1 drop primary key;
9.删除自增约束
alter table student modify id int not null

复制表

key包括主键、外键、索引
复制表结构，包括key
create table 新表名 like 源表;
只复制表结构
create table 新表名 select * from 源表 where 1=2; 条件为假，查不到任何记录
复制表结构+记录（不会复制key）
create table 新表名 select * from 源表;

删除表

删除表 drop table 表名;
删除表中数据

delete from 表名	#可以通过where对要删除的记录进行选择
truncate table 表名	#删除表中的所有记录
1
2

表的查询

CREATE TABLE `employee5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `sex` enum('male','female') NOT NULL DEFAULT 'male',
  `hire_date` date NOT NULL,
  `post` varchar(50) NOT NULL,
  `job_description` varchar(100) DEFAULT NULL,
  `salary` double(15,2) NOT NULL,
  `office` int(11) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

mysql> insert into employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20200304','instructor','teach',5000,501,100),
('tom','male','20200305','instructor','teach',5500,501,100),
('robin','male','20200404','instructor','teach',8000,501,100),
('alice','female','20200507','instructor','teach',7200,501,100),
('localhost','male','20200611','hr','hrcc',600,502,100),
('harry','female','20200324','hr',NULL,6000,502,100),
('emma','male','20200704','sale','slaecc',20000,503,102),
('christine','female','20200601','slae','salecc',2200,503,102),
('zhuzhu','male','20190304','sale',NULL,2200,503,102),
('gogo','male','20200422','sale','',2200,503,102);
Query OK, 10 rows affected (0.00 sec)
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

单表查询

简单查询

select * from employee5;
select name,salary,dep_id from employee5;
避免重复DISTINCT
select distinct post from employee5;
通过四则运算查询
select name,salary*14 [[as] annual_salary] from employee5;
定义显示格式
CONCAT用于连接字符串
SELECT CONCAT(name,' annual salary：',salary*14) as Annual_salary FROM employee5;

条件查询

单条件查询

SELECT name,post FROM employee5 WHERE post='hr';

多条件查询

SELECT name,salary FROM employee5 WHERE post='hr' AND salary>1000;

关键字BETWEEN AND
select name,salary from employee5 where salary [NOT] BETWEEN 5000 AND 15000;
关键字IS NULL
SELECT name,job_description FROM employee5 WHERE job_description IS [NOT] NULL;
SELECT name,job_description FROM employee5 WHERE job_description='';
关键字IN集合查询
SELECT name,salary FROM employee5 WHERE salary [NOT] IN (4000,5000,6000);
关键字LIKE模糊查询
通配符’%’ 匹配任意个字符
select * from employee5 where name like '%al';
通配符’_’ 匹配任意单个字符
select * from employee5 where name like 'al_ _ _';

查询排序

按单列排序

SELECT * FROM employee5 ORDER BY salary;
SELECT * FROM employee5 ORDER BY salary ASC; 指定列按升序排列
SELECT * FROM employee5 ORDER BY salary DESC; 指定列按降序排列

按多列排序

SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;

限制查询的记录数

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT [0,]5;	#默认初始位置为0
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5;	#从第4条开始，共显示5条
1
2

使用集合函数查询

select COUNT(*) from employee5;
select MIN(salary) from employee5;
select MAX(salary) from employee5;
select AVG(salary) from employee5;
select SUM(salary) from employee5;
select SUM(salary) from employee5 WHERE dep_id=101;
1
2
3
4
5
6

分组查询

GROUP BY关键字和GROUP_CONCAT()函数一起使用

mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
+--------+--------------------------------+
| dep_id | emp_members                    |
+--------+--------------------------------+
|    100 | jack,tom,robin,alice,localhost |
|    101 | harry                          |
|    102 | emma,christine,zhuzhu,gougou   |
+--------+--------------------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

GROUP BY与集合函数一起使用

mysql> SELECT dep_id,COUNT(dep_id) FROM employee5 GROUP BY dep_id;
+--------+---------------+
| dep_id | COUNT(dep_id) |
+--------+---------------+
|    100 |             5 |
|    101 |             1 |
|    102 |             4 |
+--------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

使用正则表达式查询

SELECT * FROM employee5 WHERE name REGEXP '^ali';
SELECT * FROM employee5 WHERE name REGEXP 'yun$';
SELECT * FROM employee5 WHERE name REGEXP 'm{2}';
1
2
3

小结：对字符串匹配的方式

WHERE name = 'tom';
WHERE name LIKE 'to%';
1
2

多表查询

create table employee6(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int
);

insert into employee6(emp_name,age,dept_id) values
('leilei',19,200),
('runrun',26,201),
('tongtong',30,201),
('quanquan',24,202),
('honghong',40,200),
('houhou',28,204);

create table department6(
dept_id int,
dept_name varchar(100)
);

insert into department6 values
(200,'hundan'),
(201,'langren'),
(202,'lieren'),
(203,'nvwu');
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

多表的连接查询

交叉连接： 生成笛卡尔积，它不使用任何匹配条件
内连接： 只连接匹配的行
外连接之左连接： 会显示左边表内所有的值，不论在右边表内匹不匹配
外连接之右连接： 会显示右边表内所有的值，不论在左边表内匹不匹配
全外连接： 包含左、右两个表的全部行

(1) 交叉连接
select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
(2) 内连接
只找出有dept_name的员工 (department6中没有houhou所在dept_id的名称)

mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6
    -> where employee6.dept_id = department6.dept_id;
mysql> select emp_id,emp_name,age,dept_name from employee6,department6
    -> where employee6.dept_id = department6.dept_id;
    
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | leilei   |   19 | hundan    |
|      2 | runrun   |   26 | langren   |
|      3 | tongtong |   30 | langren   |
|      4 | quanquan |   24 | lieren    |
|      5 | honghong |   40 | hundan    |
+--------+----------+------+-----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

(3) 外连接

SELECT 字段列表
FROM 表1 LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
1
2
3

1. 左外连接
   找出所有员工及所属的部门，包括没有部门的员工

mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | leilei   | hundan    |
|      5 | honghong | hundan    |
|      2 | runrun   | langren   |
|      3 | tongtong | langren   |
|      4 | quanquan | lieren    |
|      6 | houhou   | NULL      |
+--------+----------+-----------+
1
2
3
4
5
6
7
8
9
10
11

2. 右外连接
   找出所有部门包含的员工，包括空部门

mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
+--------+----------+-----------+
| emp_id | emp_name | dept_name |
+--------+----------+-----------+
|      1 | leilei   | hundan    |
|      2 | runrun   | langren   |
|      3 | tongtong | langren   |
|      4 | quanquan | lieren    |
|      5 | honghong | hundan    |
|   NULL | NULL     | nvwu      |
+--------+----------+-----------+
1
2
3
4
5
6
7
8
9
10
11

3. 全外连接
   select * from employee6 full join department6;

复合条件连接查询

示例1：以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25【找出公司所有部门中年龄大于25岁的员工】

mysql> select emp_id, emp_name, age, dept_name from employee6,department6                                         -> where employee6.dept_id = department6.dept_id and age>25;                                              +--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      5 | honghong |   40 | hundan    |
|      2 | runrun   |   26 | langren   |
|      3 | tongtong |   30 | langren   |
+--------+----------+------+-----------+
1
2
3
4
5
6
7

示例2：以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示

mysql> select emp_id, emp_name, age, dept_name from employee6,department6
    -> where employee6.dept_id = department6.dept_id order by age asc;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | leilei   |   19 | hundan    |
|      4 | quanquan |   24 | lieren    |
|      2 | runrun   |   26 | langren   |
|      3 | tongtong |   30 | langren   |
|      5 | honghong |   40 | hundan    |
+--------+----------+------+-----------+
1
2
3
4
5
6
7
8
9
10
11

子查询

子查询是将一个查询语句嵌套在另一个查询语句中
内层查询语句的查询结果，可以为外层查询语句提供查询条件
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等
1
2
3
4

1.带IN关键字的子查询
查询employee表，但dept_id必须在department表中出

mysql> select * from employee6
    -> where dept_id IN
    -> (select dept_id from department6);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | leilei   |   19 |     200 |
|      2 | runrun   |   26 |     201 |
|      3 | tongtong |   30 |     201 |
|      4 | quanquan |   24 |     202 |
|      5 | honghong |   40 |     200 |
+--------+----------+------+---------+
1
2
3
4
5
6
7
8
9
10
11
12

2.带比较运算符= 、 !=、> 、<的子查询
查询员工年龄大于等于25岁的部门

mysql> select dept_id,dept_name from department6
    -> where dept_id IN
    -> (select DISTINCT dept_id from employee6 where age>=25);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     201 | langren   |
|     200 | hundan    |
+---------+-----------+
1
2
3
4
5
6
7
8
9

3.带EXISTS关键字的子查询

EXISTS关字键字表示存在，在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值：Ture或False，当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

若department6表中存在dept_id=203，列出employee6表

select * from employee6
WHERE EXISTS
(SELECT dept_name from department6 where dept_id=203);
1
2
3

索引

索引概念

索引是数据库中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度

（1）创建索引时，需要确保该索引是应用在SQL查询语句的条件(一般作为 WHERE 子句的条件)。
（2）实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
（3）索引的缺点：降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
更新表时，MySQL不仅要保存数据，还要保存一下索引文件；建立索引会占用磁盘空间的索引文件。

索引分类及创建

创建表的时候创建索引

CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
1
2
3
4
5
6

EXPLAIN语句输出结果解释

分类1

（1）主键索引

主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。

alert table tablename add primary key (`字段名`);
1

（2）唯一索引

索引列的所有值都只能出现一次，即必须唯一，值可以为空，如果是组合索引，则列值的组合是唯一的。

实例 创建表test01，在表中的id字段上使用UNIQUE关键字创建唯一索引

CREATE table test01(
id int not null,
name char(30) not null,
unique index UniqIdx(id)
);

mysql> show create table test01;
UNIQUE KEY `UniqIdx` (`id`)
1
2
3
4
5
6
7
8

（3）普通索引

基本的索引类型，值可以为空，没有唯一性的限制，其作用只是加快对数据的访问速速。

实例 在book表的year_publication字段上建立普通索引

create table book(
boookid int not null,
bookname varchar(255) not null,
authors varchar(255) not null,
info varchar(255) not null,
comment varchar(255) null,
year_publication year not null,
INDEX [index_year_publication] (year_publication)
);

mysql> show create table book;#查看表结构，有如下语句
KEY `year_publication` (`year_publication`)

#使用explain语句查看索引是否正在使用
mysql> explain select * from book where year_publication=2020 \G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

（4）全文索引

全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

CREATE TABLE department01 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
log text,
FULLTEXT INDEX index_log (log)
);
1
2
3
4
5
6
7

分类2

（1）单列索引

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
实例 创建单列索引

create table test02(
id int not null,
name char(50) null,
index Singlcldx(name(20))
);

mysql> show create table test02;
KEY `Singlcldx` (`name`(20))
1
2
3
4
5
6
7
8

（2）组合索引

组合索引，即一个索引包含多个列
实例 创建组合索引

CREATE TABLE department11(
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
INDEX index_dept_name_comment (dept_name, comment)
);
1
2
3
4
5
6

在已经存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
1
2

（1）创建普通索引
CREATE INDEX index_dept_name ON department (dept_name);
（2）创建唯一索引
CREATE UNIQUE INDEX index_dept_name ON department (dept_name);
（3）创建全文索引示例：
CREATE FULLTEXT INDEX index_dept_name ON department (dept_name);
（4）创建多列索引示例：
CREATE INDEX index_dept_name_ comment ON department (dept_name, comment);

管理索引

查看索引

SHOW CRETAE TABLE 表名\G

测试示例

EXPLAIN SELECT * FROM department WHERE dept_name=‘hr’;

EXPLAIN查看查询优化器如何决定执行查询

删除索引

show create table employee6;
DROP INDEX 索引名 ON 表名;
1
2

实例

mysql> create table school.t2( id int, name varchar(30) );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure autoinsert1()
BEGIN
declare i int default 1;
while(i<100000)do
insert into school.t2 values(i,'ccc');
set i=i+1;
end while;
END$$
    
mysql> delimiter ;
mysql> call autoinsert1();
Query OK, 1 row affected (39.85 sec)

mysql> explain select * from school.t2 where id=20000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100113 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
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
30
31
32
33

视图

视图介绍

MySQL视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据

（1）视图不在数据库中以存储的数据值集形式存在
（2）行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成
（3）定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图
（4）视图是存储在数据库中的SQL查询语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据（如一些敏感的信息），另一原因是可以使复杂的查询易于理解和使用

创建视图

CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]
VIEW 视图名 [(字段1,字段2…)]
AS SELECT语句
[WITH [CASCADED | LOCAL] CHECK OPTION ];
1
2
3
4

CREATE VIEW 视图名 AS SELECT语句;
示例1：

USE mysql
CREATE VIEW view_user AS SELECT User,Host FROM mysql.user;
SELECT * FROM view_user;
1
2
3

示例2：创建视图案例 (单表)

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
1
2
3
4
5
6
7
8
9

示例3：创建视图案例 (多表)

mysql> create database shop;
Query OK, 1 row affected (0.21 sec)
mysql> use shop
Database changed
mysql> create table product(
-> id int unsigned auto_increment primary key not null,
-> name varchar(60) not null,
-> price double not null
-> );
mysql> insert into product(name,price) values
-> ('pear',4.3),
-> ('orange',6.5),
-> ('apple',5.0)
-> ;
mysql> create table purchase(
-> id int unsigned auto_increment primary key not null,
-> name varchar(60) not null,
-> quantity int not null default 0,
-> gen_time datetime not null
-> );
mysql> insert into purchase(name,quantity,gen_time) values
-> ('apple',7,now()),
-> ('pear',10,now())
-> ;
mysql> create view purchase_detail
-> as select
-> product.name as name, product.price as price,
-> purchase.quantity as quantity,
-> product.price * purchase.quantity as total_value
-> from product,purchase
-> where product.name = purchase.name;
mysql> select * from purchase_detail;
+-------+-------+----------+-------------+
| name | price | quantity | total_value |
+-------+-------+----------+-------------+
| pear | 4.3 | 10 | 43 |
| apple | 5 | 7 | 35 |
+-------+-------+----------+-------------+
2 rows in set (0.04 sec)
mysql> insert into purchase(name,quantity,gen_time) values ('orange',20,now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from purchase_detail;
+--------+-------+----------+-------------+
| name | price | quantity | total_value |
+--------+-------+----------+-------------+
| apple | 5 | 7 | 35 |
| pear | 4.3 | 10 | 43 |
| orange | 6.5 | 20 | 130 |
+--------+-------+----------+-------------+
3 rows in set (0.00 sec)
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

查看视图

（1）SHOW TABLES 查看视图名
（2）SHOW TABLE STATUS 查看详细信息
示例1：查看数据库mysql中视图及所有表详细信息
SHOW TABLE STATUS FROM mysql\G
示例2：查看数据库mysql中视图名view_user详细信息
SHOW TABLE STATUS FROM mysql LIKE 'view_user'\G
（3）SHOW CREATE VIEW 查看视图定义信息
SHOW CREATE VIEW view_user\G
（4）DESCRIBE 查看视图结构
DESC view_user;

修改视图

方法一：删除后新创建

DROP VIEW view_user;
CREATE VIEW view_user
AS SELECT user,host FROM mysql.user;
SELECT * FROM view_user;
1
2
3
4

方法二：ALTER修改视图

ALTER VIEW 视图名
AS SELECT语句;
ALTER VIEW view_user
AS SELECT user,password FROM mysql.user;
1
2
3
4

通过视图操作基表

查询数据SELECT
SELECT * FROM view_user;
更新数据UPDATE
删除数据DELETE

删除视图

DROP VIEW view_name [,view_name]…;
USE mysql;
DROP VIEW view_user ;
1
2
3

数据类型和运算符

在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。

MySQL数据类型

【数值类型】
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型 FLOAT DOUBLE
定点数类型 DEC
位类型 BIT
1
2
3
4
5

整型

有符号整形测试

create table test1(
tinyint_test tinyint,
int_test int
);
mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES  |     | NULL    |       |
| int_test     | int(11)    | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1

测试结果，默认有符号，超过存储范围出错
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

无符号整形测试
约束条件unsigned限定只能存正值（无符号）

create table test2(
tinyint_test tinyint unsigned,	
int_test int unsigned
);
mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)
mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
1
2
3
4
5
6
7
8
9
10

测试整数类型的显示宽度

create table t2(
id1 int zerofill,
id2 int(6) zerofill
);
mysql> desc t2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| id2   | int(6) unsigned zerofill  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(3,2222222);	#插入大于宽度限制的值，仍然可以存储
Query OK, 1 row affected (0.03 sec)
mysql> select * from t2;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)
整形的宽度仅为显示宽度，不是限制，因此建议整形无须指定宽度
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

浮点型(float和double)

如果在ASP中使用到Decimal数据类型，直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200。
float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0

mysql> create table test4(float_test float(5,2));	#一共5位，小数占2位
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test4;
+------------+
| float_test |
+------------+
|      10.20 |
|      70.24 |
|      70.25 |
+------------+
3 rows in set (0.00 sec)

mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

定点数decimal测试

mysql> create table test5(decimal_test decimal(5,2));
Query OK, 0 rows affected (0.04 sec)

mysql>  insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1265 | Data truncated for column 'decimal_test' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

字符串(char,varchar,_text)

（1）char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空
（2）char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节；varchar的列长度是可变的,在mysql5.0.3之前varchar的长度范围为0-255个字符，之后varchar的长度范围为0-65535个字节
（3）char类型的字符串检索速度要比varchar类型的快
（4）varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节
（5）text类型不能有默认值
（6）varchar可直接创建索引，text创建索引要指定前多少个字符，varchar查询速度快于text，在都创建索引的情况下，text的索引似乎不起作用

字符串类型测试
（1）CHAR、VARCHAR

在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格

create table vc(
v varchar(4),
c char(4)
);
mysql> insert into vc values('ab ','ab ');
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         3 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> select concat(v,'='), concat(c,'=') from vc;	#在后面加字符'='，看的更清楚
1
2
3
4
5
6
7
8
9
10
11
12
13

（2）BINARY、VARBINARY

BINARY 和 VARBINARY类似于CHAR 和 VARCHAR，不同的是它们包含二进制字符而不包含非二进制字符串

mysql> create table binary_t (c binary(3));
Query OK, 0 rows affected (0.03 sec)
mysql> desc binary_t;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c     | binary(3) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into binary_t set c='aaa';
Query OK, 1 row affected (0.00 sec)
mysql> select *,hex(c) from binary_t;
+------+--------+
| c    | hex(c) |
+------+--------+
| aaa  | 616161 |
+------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（3）ENUM枚举类型、SET集合类型

字段的值只能在给定范围中选择，常见的是单选按钮和复选框：enum 单选，只能在给定的范围内选一个值；set 多选，在给定的范围内可以选择一个或一个以上的值。

create table student3(
name varchar(50),
sex enum('m','f'),
hobby set('music','book','game','disc')
);
mysql> insert into student3 values('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)
mysql>  select * from student3;
+------+------+-----------+
| name | sex  | hobby     |
+------+------+-----------+
| tom  | m    | book,game |
+------+------+-----------+
1 row in set (0.00 sec)
mysql> insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> show create table student3\G
*************************** 1. row ***************************
Table: student3
Create Table: CREATE TABLE `student3` (
`name` varchar(50) default NULL,
`sex` enum('m','f') default NULL,
`hobby` set('music','book','game','disc') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
# 1 row in set (0.00 sec)
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

二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写
2._BLOB存储的数据只能整体读出
3._TEXT可以指定字符集，_BLOG不用指定字符集

日期时间类型

mysql> create table t3(born_year year);
mysql> insert into t3 values(12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
|      2012 |
|      1980 |
+-----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

其他类型（enum、set）

Geometry、Point、LineString、Polygon、MultiPoint、MultiLineString、MultiPolygon、GeometryCollection

数据类型的属性

字符集

mysql字符集概念

MySQL字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
character_set_client：MySQL客户机字符集。
character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。
character_set_database：数据库字符集。
character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary。
character_set_results：结果集的字符集，MySQL服务器向MySQL客户机返回执行结果时，执行结果以该字符集进行编码。
character_set_server：MySQL服务实例字符集。
character_set_system：元数据(字段名、表名、数据库名等) 的字符集，默认值为utf8。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查看当前MySQL服务实例支持的字符序 SHOW COLLATION;

MySQL字符序命名规则

以字符序对应的字符集名称开头，以国家名居中（或以general居中），以ci、cs或bin结尾（ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较）

默认字符集修改

方法1：修改my.cnf配置文件，可修改MySQL默认的字符集，修改完毕重启MySQL

1.在[mysqld]下添加
default-character-set=utf8 #适合5.1及以前版本 （mysql 5.5及以后版本添加character-set-server=utf8）
init_connect = 'SET NAMES utf8'
2.在[client]下添加
default-character-set=utf8
1
2
3
4
5

方法2：MySQL提供下列MySQL命令可以“临时地”修改MySQL“当前会话的”字符集以及字符序

set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set character_set_server = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;
1
2
3
4
5
6
7
8

方法3：使用MySQL命令 set names utf8; 可以“临时一次性地”设置character_set_client、character_set_connection以及character_set_results的字符集为utf8
方法4：连接MySQL服务器时指定字符集
mysql --default-character-set=字符集 -h 服务器IP地址 -u 账户名 –p密码