2.2. 管理数据库
2.2.1. 创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
范例
#创建数据库haha
create database haha;
#查看数据库haha的信息
show create database haha;
MariaDB [(none)]> show create database haha;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
#指定字符集创建新数据库lala
create database lala character SET 'utf8';
create database lala charset=utf8;
#查看数据库lala的信息
show create database lala ;
MariaDB [(none)]> show create database lala;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| lala | CREATE DATABASE `lala` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
#创建数据库实际上就是创建文件
[root@localhost ~]#cat /mysql/3306/data/ #创建了lala和haha目录
aria_log.00000001 ibdata1 lala/ tc.log
aria_log_control ib_logfile0 multi-master.info test/
haha/ ib_logfile1 mysql/
ib_buffer_pool ibtmp1 performance_schema/
[root@localhost ~]#cat /mysql/3306/data/lala/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
说明：create database lala charset=utf8 COLLATE=utf8_unicode_ci;
charset设置字符集类型
COLLATE定义用改字符集中的具体哪一种来进行排序，比如说utf8_general_ci不区分大小写；utf8_bin 区分大小写如果对大小写有需求那么就可以加上COLLATE=utf8_bin这样就可以了
2.2.2. 修改数据库
ALTER DATABASE DB_NAME character set utf8;
范例
#修改数据库lala的字符集为utf8mb4
MariaDB [(none)]> alter database lala charset=utf8mb4;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show create database lala;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| lala | CREATE DATABASE `lala` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
2.2.3. 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
范例
#删除数据库lala
MariaDB [(none)]> drop database lala;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| haha |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.000 sec)
2.2.4. 查看数据库列表
SHOW DATABASES; #查看所有数据库SHOW DATABASE create lala; #查看lala数据库
2.3. 数据类型
2.3.1. 数据类型参考链接
· https://dev.mysql.com/doc/refman/8.0/en/data-types.html
2.3.2. 选择正确的数据类型对于获得高性能至关重要，三大原则：
· 更小的通常更好，尽量使用可正确存储数据的最小数据类型
· 简单就好，简单数据类型的操作通常需要更少的CPU周期
· 尽量避免NULL，包含为NULL的列，对MySQL更难优化
2.3.3. 数据类型
2.4. DDL 语句
2.4.1. 创建表
参考文档
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
创建表：
CREATE TABLE
获取帮助：
HELP CREATE TABLE
外键管理参考文档
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
创建表的方法
• 直接创建
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
注意：
Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型
举例：
#创建student表，有id，name，age，gender四列内容。其中id为主键且自动生成，gender进行枚举，只有M和F两个选项默认为M
MariaDB [haha]> create table student(
-> id int auto_increment primary key,
-> name varchar(20) not null,
-> age tinyint unsigned,
-> gender enum('M','F') default 'M');
MariaDB [haha]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
#简单的创建用户以及查看
MariaDB [haha]> insert student (name,age) values('zhangsan',22),('lisi',23);
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [haha]> select *from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | zhangsan | 22 | M |
| 2 | lisi | 23 | M |
+----+----------+------+--------+
2 rows in set (0.001 sec)
MariaDB [haha]> insert student (name,age,gender) values('xiaohua',20,'F');
Query OK, 1 row affected (0.001 sec)
MariaDB [haha]> select *from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 1 | zhangsan | 22 | M |
| 2 | lisi | 23 | M |
| 3 | xiaohua | 20 | F |
+----+----------+------+--------+
3 rows in set (0.000 sec)
MariaDB [haha]> select *from student where name='xiaohua';
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 3 | xiaohua | 20 | F |
+----+---------+------+--------+
1 row in set (0.000 sec)
说明：上述例子的id会自动增长从1开始每次增长1，这是因为默认定义的增长就是增长初始值为1，增长量为1
MariaDB [haha]> SHOW VARIABLES LIKE 'auto_inc%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 1 | #增长量| auto_increment_offset | 1 | #增长初始值+--------------------------+-------+2 rows in set (0.001 sec)#修改增长初始值，以及增长量MariaDB [haha]> set @@auto_increment_increment=12;Query OK, 0 rows affected (0.000 sec)MariaDB [haha]> SET @@auto_increment_offset=3;Query OK, 0 rows affected (0.000 sec)MariaDB [haha]> SHOW VARIABLES LIKE 'auto_inc%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| auto_increment_increment | 12 || auto_increment_offset | 3 |+--------------------------+-------+2 rows in set (0.001 sec)MariaDB [haha]> insert student(name,age) value('wangwu',30);Query OK, 1 row affected (0.001 sec)MariaDB [haha]> select *from student;+----+----------+------+--------+| id | name | age | gender |+----+----------+------+--------+| 1 | zhangsan | 22 | M || 2 | lisi | 23 | M || 3 | xiaohua | 20 | F || 15 | wangwu | 30 | M | #修改了增亮之后id直接从3直接跳到了15+----+----------+------+--------+4 rows in set (0.000 sec)
• 通过查询现存表创建；新表会被直接插入查询而来的数据
create table user select user,host,password from mysql.user;
MariaDB [haha]> create table user select name from student;Query OK, 5 rows affected (0.022 sec)Records: 5 Duplicates: 0 Warnings: 0MariaDB [haha]> select *from user;+----------+| name |+----------+| zhangsan || lisi || xiaohua || wangwu || wangmazi |+----------+5 rows in set (0.000 sec)MariaDB [haha]> desc user;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+---------+-------+1 row in set (0.022 sec)
• 通过复制现存的表的表结构创建，但不复制数据
create table teacher like student;
MariaDB [haha]> create table teacher like student;Query OK, 0 rows affected (0.003 sec)MariaDB [haha]> desc teacher;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || gender | enum('M','F') | YES | | M | |+--------+---------------------+------+-----+---------+----------------+4 rows in set (0.001 sec)
2.4.2. 表查看
查看表
SHOW TABLES [FROM db_name]
查看表创建命令
SHOW CREATE TABLE tbl_name
查看表结构
DESC [db_name.]tb_nameSHOW COLUMNS FROM [db_name.]tb_name
查看表状态
SHOW TABLE STATUS LIKE 'tbl_name'
注意通过此命令查看状态会觉得很乱，所以可以加上一个\G使其纵向显示
MariaDB [haha]> show table status like 'teacher';+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+| teacher | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2021-12-05 09:00:19 | NULL | NULL | utf8mb4_general_ci | NULL | | | 0 | N |+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+1 row in set (0.001 sec)MariaDB [haha]> show table status like 'teacher'\G;*************************** 1. row *************************** Name: teacher Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 1 Create_time: 2021-12-05 09:00:19 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: Max_index_length: 0 Temporary: N1 row in set (0.001 sec)ERROR: No query specified
查看支持的engine类型
SHOW ENGINES;
2.4.3. 修改和删除表
修改表
ALTER TABLE 'tbl_name'
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
查看修改表帮助
Help ALTER TABLE
删除表
DROP TABLE [IF EXISTS] 'tbl_name';
举例
MariaDB [haha]> alter table student rename s1; #修改表名为s1
Query OK, 0 rows affected (0.025 sec)
MariaDB [haha]> show tables;
+----------------+
| Tables_in_haha |
+----------------+
| s1 |
| teacher |
| user |
+----------------+
3 rows in set (0.000 sec)
MariaDB [haha]> alter table s1 add phone varchar(11) after name; #在s1表的name字段后面添加phone字段
Query OK, 0 rows affected (0.025 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [haha]> desc table s1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table s1' at line 1
MariaDB [haha]> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| phone | varchar(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)
MariaDB [haha]> alter table s1 modify phone int; #修改phone字段的类型为int
Query OK, 5 rows affected (0.007 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [haha]> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| phone | int(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)
MariaDB [haha]> alter table s1 change column phone mobile char(11); #修改字段phone的名车为mobile，同时修改数据类型为char(11)
Query OK, 5 rows affected (0.008 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [haha]> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| mobile | char(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)
MariaDB [haha]> alter table s1 drop column mobile; #删除mobile字段
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [haha]> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
MariaDB [haha]> alter table s1 character set utf8; #修改表s1的字符集
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [haha]> show table status like 's1'\G;
*************************** 1. row ***************************
Name: s1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 28
Create_time: 2021-12-05 09:15:58
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [haha]> alter table s1 change name mingzi varchar(20) character set big5; #修改字段名和数据类型已经该字段的字符集
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [haha]> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mingzi | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
MariaDB [haha]>
MariaDB [haha]> create table t1 select *from s1; #创建表t1内容为s1表的内容（注意这样创建的表不是复制）
Query OK, 5 rows affected (0.005 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [haha]> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| mingzi | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
MariaDB [haha]> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT 0,
`mingzi` varchar(20) CHARACTER SET big5 DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
MariaDB [haha]> show create table s1;
| s1 | CREATE TABLE `s1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mingzi` varchar(20) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') CHARACTER SET utf8mb4 DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=big5 |
MariaDB [haha]> alter table t1 add primary key(id); #对比s1发现t1没有主键，为t1添加主键
Query OK, 0 rows affected (0.022 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [haha]> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL DEFAULT 0,
`mingzi` varchar(20) CHARACTER SET big5 DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
MariaDB [haha]> alter table s1 add foreign key(id) references t1(id); #添加外键
Query OK, 5 rows affected (0.009 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [haha]> show create table s1;
| s1 | CREATE TABLE `s1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mingzi` varchar(20) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') CHARACTER SET utf8mb4 DEFAULT 'M',
PRIMARY KEY (`id`),
CONSTRAINT `s1_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=big5 |
MariaDB [haha]> alter table s1 drop foreign key id; #删除外键（直接用id删不掉）
ERROR 1091 (42000): Can't DROP FOREIGN KEY `id`; check that it exists';
MariaDB [haha]> alter table s1 drop foreign key s1_ibfk_1; #删除外键
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [haha]> show create table s1
| s1 | CREATE TABLE `s1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mingzi` varchar(20) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') CHARACTER SET utf8mb4 DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=big5 |