建库、建表、修改表、复制表、字符类型、数值类型、枚举类型、日期时间类型、检索目录、数据导入命令、数据导入步骤、数据导出命令、非空、默认值、唯一索引

大家好，欢迎来到IT知识分享网。

1 案例1：表管理

1.1 问题

1. 建库练习
2. 建表练习
3. 修改表练习

1.2 方案

在MySQL50主机完成练习。

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：建库练习

库名命名规则：

仅可以使用数字、字母、下划线、不能纯数字

区分字母大小写，

具有唯一性

不可使用MySQL命令或特殊字符

命令操作如下所示：

 //库名区分字母大小写 mysql> create database gamedb ; Query OK, 1 row affected (0.14 sec) mysql> create database GAMEDB ; Query OK, 1 row affected (0.08 sec) mysql> create database GAMEDB ; ERROR 1007 (HY000): Can't create database 'GAMEDB'; database exists //重名报错 //加if not exists 命令避免重名报错 mysql> create database if not exists gamedb ; Query OK, 1 row affected, 1 warning (0.03 sec) //正常 mysql> show databases; //查看创建的库 +--------------------+ | Database | +--------------------+ | GAMEDB | | gamedb | | information_schema | | mysql | | performance_schema | | sys | | tarena | +--------------------+ 7 rows in set (0.00 sec) mysql> drop database gamedb; //删除库 Query OK, 0 rows affected (0.11 sec) mysql> drop database gamedb; // 删除没有的库报错 ERROR 1008 (HY000): Can’t drop database ‘gamedb’; database doesn’t exist //加if exists 删除没有的库，也不报错 mysql> drop database if exists gamedb; Query OK, 0 rows affected, 1 warning (0.00 sec)

步骤二：建表练习

命令操作如下所示：

 mysql> create database 学生库; //建库 Query OK, 1 row affected (0.11 sec) mysql> create table 学生库.学生信息表( //建表 -> 姓名 char(10), -> 班级 char(9), -> 性别 char(4), -> 年龄 int -> ); Query OK, 0 rows affected (0.47 sec) mysql> use 学生库; //进入库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; //查看表 +---------------------+ | Tables_in_学生库 | +---------------------+ | 学生信息表 | +---------------------+ 1 row in set (0.00 sec) mysql> desc 学生信息表; //查看表头 +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | 姓名 | char(10) | YES | | NULL | | | 班级 | char(9) | YES | | NULL | | | 性别 | char(4) | YES | | NULL | | | 年龄 | int | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) //删除表 mysql> drop table 学生库.学生信息表; //删除库 mysql> drop database 学生库;

使用英文命名，重新建库、建表

 mysql> create database studb; //建库 Query OK, 1 row affected (0.11 sec) mysql> create table studb.stu( //建表 -> name char(10), -> class char(9), -> gender char(4), -> age int -> ); Query OK, 0 rows affected (1.17 sec) mysql> desc studb.stu; //查看表头 +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | class | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | | age | int | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

步骤三：修改表练习

命令操作如下所示：

 mysql> alter table studb.stu rename studb.stuinfo; //修改表名 Query OK, 0 rows affected (0.28 sec) mysql> use studb; //进入库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; //查看表 +-----------------+ | Tables_in_studb | +-----------------+ | stuinfo | +-----------------+ 1 row in set (0.00 sec) mysql> alter table studb.stuinfo drop age ; //删除age表头 Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc stuinfo; //查看表头 +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | class | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) //添加表头，默认添加在末尾 mysql> alter table studb.stuinfo add mail char(30) ; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 //查看表头 mysql> desc studb.stuinfo; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | class | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | | mail | char(30) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) //first 把表头添加首位 //after 添加在指定表头名的下方 mysql> alter table studb.stuinfo add number char(9) first , add school char(10) after name; Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 //查看表结构 mysql> desc studb.stuinfo; //查看表头 +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | number | char(9) | YES | | NULL | | | name | char(10) | YES | | NULL | | | school | char(10) | YES | | NULL | | | class | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | | mail | char(30) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec) //修改表头数据类型 mysql> alter table studb.stuinfo modify mail varchar(50); Query OK, 0 rows affected (1.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc studb.stuinfo; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | number | char(9) | YES | | NULL | | | name | char(10) | YES | | NULL | | | school | char(10) | YES | | NULL | | | class | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | | mail | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) //修改表头名 mysql> alter table studb.stuinfo change class 班级 char(9) ; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 //查看表头 mysql> desc studb.stuinfo; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | number | char(9) | YES | | NULL | | | name | char(10) | YES | | NULL | | | school | char(10) | YES | | NULL | | | 班级 | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | | mail | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) //一起删除多个表头 mysql> alter table studb.stuinfo drop school , drop 班级 ,drop mail ; Query OK, 0 rows affected (0.73 sec) Records: 0 Duplicates: 0 Warnings: 0 //查看表头 mysql> desc studb.stuinfo; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | number | char(9) | YES | | NULL | | | name | char(10) | YES | | NULL | | | gender | char(4) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> //使用modify 修改表头的位置 mysql> alter table studb.stuinfo modify gender char(4) after number; Query OK, 0 rows affected (0.77 sec) Records: 0 Duplicates: 0 Warnings: 0 //查看表头 mysql> desc studb.stuinfo; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | number | char(9) | YES | | NULL | | | gender | char(4) | YES | | NULL | | | name | char(10) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) //再修改回原来位置 mysql> alter table studb.stuinfo modify gender char(4) after name; Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 //查看表头 mysql> desc studb.stuinfo; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | number | char(9) | YES | | NULL | | | name | char(10) | YES | | NULL | | | gender | char(4) | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

 复制表 （拷贝已有的表 和系统命令 cp 的功能一样 ）

 //复制tarena库salary表到 studb库 表名不变 mysql> create table studb.salary select * from tarena.salary; Query OK, 8055 rows affected (2.66 sec) Records: 8055 Duplicates: 0 Warnings: 0 //查看表头，源表的key 不会被复制 mysql> desc studb.salary; +-------------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------+------+-----+---------+-------+ | id | int | NO | | 0 | | | date | date | YES | | NULL | | | employee_id | int | YES | | NULL | | | basic | int | YES | | NULL | | | bonus | int | YES | | NULL | | +-------------+------+------+-----+---------+-------+ 5 rows in set (0.00 sec) //查看表行数 mysql> select count(*) from studb.salary; +----------+ | count(*) | +----------+ | 8055 | +----------+ 1 row in set (0.00 sec) //仅仅复制表头 mysql> create table studb.salary2 like tarena.salary; Query OK, 0 rows affected (0.95 sec) //查看表头 mysql> desc studb.salary2; +-------------+------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | date | date | YES | | NULL | | | employee_id | int | YES | MUL | NULL | | | basic | int | YES | | NULL | | | bonus | int | YES | | NULL | | +-------------+------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) //查看表行数 mysql> select count(*) from studb.salary2; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql>

2 案例2：数据类型

2.1 问题

1. 练习字符类型的使用
2. 练习数值类型的使用
3. 练习枚举类型的使用
4. 练习日期时间类型的使用

2.2 方案

常用数据类型：数值类型、字符类型、日期时间类型、枚举类型,每种类型都有对应的命令表示、有具体的存储范围。

• 比如存储： 身高、体重、工资、奖金，适合使用数值类型。
• 比如存储： 姓名、家庭地址、收货地址，适合使用字符类型。
• 比如存储： 生日、出生年份、入职时间、下班时间、注册时间，适合使用日期时间。
• 比如存储： 爱好、性别、社保医院，适合使用枚举类型。

2.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：练习字符类型的使用

命令操作如下所示：

 //建表 mysql> create table studb.t2(name char(3) , address varchar(5) ); Query OK, 0 rows affected (0.30 sec) //查看表头 mysql> desc studb.t2; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | name | char(3) | YES | | NULL | | | address | varchar(5) | YES | | NULL | | +---------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) //插入记录 mysql> insert into studb.t2 values ("a","a"); //正常 Query OK, 1 row affected (0.05 sec) mysql> insert into studb.t2 values ("ab","ab"); //正常 Query OK, 1 row affected (0.08 sec) mysql> insert into studb.t2 values ("abc","abc");//正常 Query OK, 1 row affected (0.04 sec) mysql> insert into studb.t2 values ("abcd","abcd"); //超出字符个数报错 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql>

 mysql8 建表默认支持中文字符集

 //查看字符集 mysql> show create table studb.t2 \G * 1. row * Table: t2 Create Table: CREATE TABLE `t2` ( `name` char(3) DEFAULT NULL, `address` varchar(5) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) 说明 ： ENGINE=InnoDB 定义存储引擎（存储引擎课程里讲） DEFAULT CHARSET=定义表使用的字符集 //插入记录 mysql> insert into studb.t2 values ("张翠山","武当山"); Query OK, 1 row affected (0.07 sec) //查看表记录 mysql> SELECT * FROM studb.t2; +-----------+-----------+ | name | address | +-----------+-----------+ | a | a | | ab | ab | | abc | abc | | 张翠山 | 武当山 | +-----------+-----------+ 4 rows in set (0.00 sec)

步骤二：练习数值类型的使用

命令操作如下所示：

 name 姓名 level 游戏级别 money 游戏币 //建表 mysql> create table studb.t1(name char(10) , level tinyint unsigned , money double ); Query OK, 0 rows affected (0.72 sec) //查看表头 mysql> desc studb.t1; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | level | tinyint unsigned | YES | | NULL | | | money | double | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) //插入数据 mysql> insert into studb.t1 values("法师",80,88); Query OK, 1 row affected (0.04 sec) //超出范围报错 mysql> insert into studb.t1 values("战士",301,1.292); ERROR 1264 (22003): Out of range value for column 'level' at row 1 mysql> mysql> insert into studb.t1 values("猎人",255,1.292); Query OK, 1 row affected (0.06 sec) //整数类型 不存储小数位 mysql> insert into studb.t1 values ("英雄",1.292,6.78); Query OK, 1 row affected (0.07 sec) //查看表记录 mysql> select * from studb.t1 ; +--------+-------+-------+ | name | level | money | +--------+-------+-------+ | 法师 | 80 | 88 | | 猎人 | 255 | 1.292 | | 英雄 | 1 | 6.78 | +--------+-------+-------+ 3 rows in set (0.00 sec)

 步骤三：练习枚举类型的使用

 //建表 mysql> create table studb.t8( -> 姓名 char(10), -> 性别 enum("男","女","保密"), -> 爱好 set("帅哥","金钱","吃","睡") -> ); Query OK, 0 rows affected (0.29 sec) //查看表头 mysql> desc studb.t8 ; +--------+------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------------------------+------+-----+---------+-------+ | 姓名 | char(10) | YES | | NULL | | | 性别 | enum('男','女','保密') | YES | | NULL | | | 爱好 | set('帅哥','金钱','吃','睡') | YES | | NULL | | +--------+------------------------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) //插入记录超出范围报错 mysql> insert into studb.t8 values ("小包总","男人","帅哥,睡,金钱"); ERROR 1265 (01000): Data truncated for column '性别' at row 1 mysql> insert into studb.t8 values ("小包总","男","美女,睡,金钱"); ERROR 1265 (01000): Data truncated for column '爱好' at row 1 mysql> //在范围内插入成功 mysql> insert into studb.t8 values ("丫丫","女","帅哥,吃"); Query OK, 1 row affected (0.09 sec) mysql> select * from studb.t8; +--------+--------+------------+ | 姓名 | 性别 | 爱好 | +--------+--------+------------+ | 丫丫 | 女 | 帅哥,吃 | +--------+--------+------------+ 1 row in set (0.00 sec)

步骤四：练习日期时间类型的使用

命令操作如下所示：

 //建表 mysql> create table studb.t6( -> 姓名 char(10), -> 生日 date , -> 出生年份 year , -> 家庭聚会 datetime , -> 聚会地点 varchar(15), -> 上班时间 time -> ); Query OK, 0 rows affected (0.25 sec) //查看表头 mysql> desc studb.t6 ; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | 姓名 | char(10) | YES | | NULL | | | 生日 | date | YES | | NULL | | | 出生年份 | year | YES | | NULL | | | 家庭聚会 | datetime | YES | | NULL | | | 聚会地点 | varchar(15) | YES | | NULL | | | 上班时间 | time | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) //插入表头 mysql> insert into studb.t6 -> values ("翠花",,1990,000,"天坛校区",090000); Query OK, 1 row affected (0.05 sec) //查看表记录 mysql> select * from studb.t6; +--------+------------+--------------+---------------------+--------------+--------------+ | 姓名 | 生日 | 出生年份 | 家庭聚会 | 聚会地点 | 上班时间 | +--------+------------+--------------+---------------------+--------------+--------------+ | 翠花 | 2021-11-20 | 1990 | 2022-01-01 18:30:00 | 天坛校区 | 09:00:00 | +--------+------------+--------------+---------------------+--------------+--------------+ 1 row in set (0.00 sec)

3 案例3：数据批量处理

3.1 问题

1. 修改检索目录为/myload。
2. 将/etc/passwd文件导入db1库的user3表里，并添加行号字段。
3. 将db1库user3表所有记录导出, 存到/myload/user.txt文件里。

3.2 方案

在mysql50主机完成练习。

3.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：修改检索目录为/myload。

检查目录存放导入导出数据时存放数据的文件

 [root@mysql50 ~]# mysql -uroot -pNSD2023...a mysql> show variables like "%file%"; 查看与文件相关的配置项 +---------------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------------+---------------------------------+ | character_set_filesystem | binary | | core_file | OFF | | ft_stopword_file | (built-in) | | general_log_file | /var/lib/mysql/mysql50.log | | init_file | | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_in_core_file | ON | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite_files | 2 | | innodb_file_per_table | ON | | innodb_log_file_size |  | | innodb_log_files_in_group | 2 | | innodb_open_files | 4000 | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | | keep_files_on_create | OFF | | large_files_support | ON | | local_infile | OFF | | lower_case_file_system | OFF | | myisam_max_sort_file_size |  | | open_files_limit | 10000 | | performance_schema_max_file_classes | 80 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | -1 | | pid_file | /run/mysqld/mysqld.pid | | relay_log_info_file | relay-log.info | | secure_file_priv | /var/lib/mysql-files/ | | slow_query_log_file | /var/lib/mysql/mysql50-slow.log | +---------------------------------------+---------------------------------+ 28 rows in set (0.00 sec) 查看默认检索目录 mysql> show variables like "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec) mysql> exit 安装MySQL服务软件时自动创建 [root@mysql50 ~]# ls -ld /var/lib/mysql-files/ drwxr-x--- 2 mysql mysql 6 Sep 22 2021 /var/lib/mysql-files/ [root@mysql50 ~]# 修改主配置文件 [root@mysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf [mysqld] secure_file_priv=/myload 添加此行 :wq 创建目录并修改所有者为mysql用户 ,并保证mysql用户对父目录有rx [root@mysql50 ~]# mkdir /myload [root@mysql50 ~]# chown mysql /myload 关闭selinux root@mysql50 ~]# setenforce 0 setenforce: SELinux is disabled 重启服务 [root@mysql50 ~]# systemctl restart mysqld 管理员员登陆查看目录 [root@mysql50 ~]# mysql -uroot -pNSD2023...a mysql> show variables like "secure_file_priv"; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | secure_file_priv | /myload/ | +------------------+----------+ 1 row in set (0.01 sec)

步骤二：将/etc/passwd文件导入db1库的user3表里。

命令操作如下所示：

 建库 [root@mysql50 ~]# mysql -uroot -pNSD2023...a mysql> create database db1; 建表（ 根据导入的文件内容 创建表头） mysql> create table db1.user3(name varchar(30),password char(1),uid int , gid int , comment varchar(200),homedir varchar(50),shell varchar(30)); Query OK, 0 rows affected (0.41 sec) 查看表头 mysql> desc db1.user3; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | name | varchar(30) | YES | | NULL | | | password | char(1) | YES | | NULL | | | uid | int | YES | | NULL | | | gid | int | YES | | NULL | | | comment | varchar(200) | YES | | NULL | | | homedir | varchar(50) | YES | | NULL | | | shell | varchar(30) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 7 rows in set (0.01 sec) 没有数据 mysql> select * from db1.user3; Empty set (0.01 sec) mysql> 拷贝文件到检索目录 system 在MySQL 里执行系统命令 mysql> system cp /etc/passwd /myload/ mysql> system ls /myload/ 查看文件 passwd mysql> 导入数据 mysql> load data infile "/myload/passwd" into table db1.user3 fields terminated by ":" lines terminated by "\n" ; Query OK, 23 rows affected (0.06 sec) Records: 23 Deleted: 0 Skipped: 0 Warnings: 0 查看表记录 mysql> select count(*) from db1.user3; +----------+ | count(*) | +----------+ | 23 | +----------+ 1 row in set (0.00 sec) mysql> select * from db1.user3; +------------------+----------+-------+-------+-----------------------------+-----------------+----------------+ | name | password | uid | gid | comment | homedir | shell | +------------------+----------+-------+-------+-----------------------------+-----------------+----------------+ | root | x | 0 | 0 | root | /root | /bin/bash | | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | | sync | x | 5 | 0 | sync | /sbin | /bin/sync | | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown | | halt | x | 7 | 0 | halt | /sbin | /sbin/halt | | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin | | operator | x | 11 | 0 | operator | /root | /sbin/nologin | | games | x | 12 | 100 | games | /usr/games | /sbin/nologin | | ftp | x | 14 | 50 | FTP User | /var/ftp | /sbin/nologin | | nobody | x | 65534 | 65534 | Kernel Overflow User | / | /sbin/nologin | | dbus | x | 81 | 81 | System message bus | / | /sbin/nologin | | systemd-coredump | x | 999 | 997 | systemd Core Dumper | / | /sbin/nologin | | systemd-resolve | x | 193 | 193 | systemd Resolver | / | /sbin/nologin | | polkitd | x | 998 | 995 | User for polkitd | / | /sbin/nologin | | unbound | x | 997 | 994 | Unbound DNS resolver | /etc/unbound | /sbin/nologin | | tss | x | 59 | 59 | Account used for TPM access | /dev/null | /sbin/nologin | | chrony | x | 996 | 993 | | /var/lib/chrony | /sbin/nologin | | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | | tcpdump | x | 72 | 72 | | / | /sbin/nologin | | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /sbin/nologin | +------------------+----------+-------+-------+-----------------------------+-----------------+----------------+ 23 rows in set (0.00 sec) mysql>

步骤三：将db1库user3表所有记录导出, 存到/myload/user.txt文件里。

命令操作如下所示：

mysql> select * from db1.user3 into outfile "/myload/user.txt" ; Query OK, 23 rows affected (0.00 sec) mysql> system ls /myload/ passwd user.txt mysql> system wc -l /myload/user.txt 23 /myload/user.txt mysql> mysql> system vim /myload/user.txt root x 0 0 root /root /bin/bash bin x 1 1 bin /bin /sbin/nologin daemon x 2 2 daemon /sbin /sbin/nologin adm x 3 4 adm /var/adm /sbin/nologin lp x 4 7 lp /var/spool/lpd /sbin/nologin sync x 5 0 sync /sbin /bin/sync shutdown x 6 0 shutdown /sbin /sbin/shutdown halt x 7 0 halt /sbin /sbin/halt mail x 8 12 mail /var/spool/mail /sbin/nologin operator x 11 0 operator /root /sbin/nologin games x 12 100 games /usr/games /sbin/nologin ftp x 14 50 FTP User /var/ftp /sbin/nologin nobody x 65534 65534 Kernel Overflow User / /sbin/nologin dbus x 81 81 System message bus / /sbin/nologin systemd-coredump x 999 997 systemd Core Dumper / /sbin/nologin systemd-resolve x 193 193 systemd Resolver / /sbin/nologin polkitd x 998 995 User for polkitd / /sbin/nologin unbound x 997 994 Unbound DNS resolver /etc/unbound /sbin/nologin tss x 59 59 Account used for TPM access /dev/null /sbin/nologin chrony x 996 993 /var/lib/chrony /sbin/nologin sshd x 74 74 Privilege-separated SSH /var/empty/sshd /sbin/nologin tcpdump x 72 72 / /sbin/nologin mysql x 27 27 MySQL Server /var/lib/mysql /sbin/nologin 

案例4：表头基本约束

4.1 问题

1. 表头不允许赋null值练习
2. 表头加默认值练习
3. 表头加唯一索引练习

4.2 方案

约束是一种限制，设置在表头上，用来控制表头的赋值，包括以下几种：

1. NOT NULL ：非空，用于保证该字段的值不能为空。
2. DEFAULT：默认值，用于保证该字段有默认值。
3. UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
4. PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
5. FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

4.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：表头不允许赋空值练习

 //建表时给表头设置默认和不允许赋null值 mysql> create database if not exists db1; Query OK, 1 row affected (0.07 sec) //建表 mysql> create table db1.t31( -> name char(10) not null , -> class char(7) default "nsd", -> likes set("money","game","film","music") not null default "film,music" ); Query OK, 0 rows affected (0.43 sec) //查看表头 mysql> desc db1.t31; +-------+------------------------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------------+------+-----+------------+-------+ | name | char(10) | NO | | NULL | | | class | char(7) | YES | | nsd | | | likes | set('money','game','film','music') | NO | | film,music | | +-------+------------------------------------+------+-----+------------+-------+ 3 rows in set (0.01 sec) //验证默认值和不允许为null mysql> insert into db1.t31 values (null, null , null); ERROR 1048 (23000): Column 'name' cannot be null //表头name赋null值 报错 //表头likes赋null值 报错 mysql> insert into db1.t31 values ("bob", null , null); ERROR 1048 (23000): Column 'likes' cannot be null //符合约束不报错 mysql> insert into db1.t31 values ("bob",null,"money,game,film"); Query OK, 1 row affected (0.06 sec) //不赋值的表头使用默认值赋值 mysql> insert into db1.t31(name) values("jim"); //根据需要自定义表头的值 mysql> insert into db1.t31 values ("lucy","nsd2108","game,film"); //查看表记录 mysql> select * from db1.t31; +------+---------+-----------------+ | name | class | likes | +------+---------+-----------------+ | bob | NULL | money,game,film | | jim | nsd | film,music | | lucy | nsd2108 | game,film | +------+---------+-----------------+ 3 rows in set (0.00 sec)

步骤二：表头加唯一索引练习

唯一索引 （unique）

约束的方式：表头值唯一 , 但可以赋null 值

 //建表 create table db1.t43 (姓名 char(10) , 护照 char(18) unique ); //查看表头 唯一索引标志UNI mysql> desc db1.t32 ; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | 姓名 | char(10) | YES | | NULL | | | 护照 | char(18) | YES | UNI | NULL | | +--------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) //赋null值 可以 mysql> insert into db1.t32 values("bob",null); Query OK, 1 row affected (0.07 sec) //表头值重复不可以 mysql> insert into db1.t32 values("tom",""); Query OK, 1 row affected (0.08 sec) mysql> insert into db1.t32 values("jim",""); ERROR 1062 (23000): Duplicate entry '' for key 't32.护照' //不重复 可以 mysql> insert into db1.t32 values("jim",""); Query OK, 1 row affected (0.05 sec) //查看表记录 mysql> select * from DB1.t43; +------+--------+ | 姓名 | 护照 | +------+--------+ | bob | NULL | | tom |  | | jim |  | +------+--------+ 3 rows in set (0.00 sec)