1 案例1：表管理
1.1 问题
- 建库练习
- 建表练习
- 修改表练习
- 复制表练习
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)//删除age表头
mysql> alter table studb.stuinfo drop 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)//仅仅复制表头，源表的key 会被复制
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 问题
- 练习字符类型的使用
- 练习数值类型的使用
- 练习枚举类型的使用
- 练习日期时间类型的使用
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 ("英雄",1.292,6.78);
Query OK, 1 row affected (0.07 sec)//查看表记录
mysql> select * from studb.t1 ;
+--------+-------+-------+
| name | level | money |
+--------+-------+-------+
| 法师 | 80 | 88 |
| 英雄 | 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 ("翠花",20211120,1990,20220101183000,"天坛校区",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 问题
- 修改检索目录为/myload。
- 将/etc/passwd文件导入db1库的user3表里，并添加行号字段。
- 将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 | 50331648 |
| 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 | 9223372036853727232 |
| 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查看目录
[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 //禁用selinux
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 里执行系统命令 前加system 指令
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" ;查看表记录
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导出数据时 ，定义列的间隔符号 和 行的间隔符号
(导出tarena库employees表的 员工编号1-5 的员工信息到employees.txt 文件里)
mysql> select name , email , phone_number from tarena.employees where employee_id <= 5 into outfile "/myload/employees.txt" fields terminated by ":" lines terminated by "!!!!!!";查看文件内容
mysql> system cat -b /myload/employees.txt
1 梁伟:liangwei@tedu.cn:13591491431!!!!!!郭岩:guoyan@tedu.cn:13845285867!!!!!!李玉英:liyuying@tedu.cn:15628557234!!!!!!张健:zhangjian@tarena.com:13835990213!!!!!!郑静:zhengjing@tedu.cn:14508936730!!!!!!mysql>4 案例4：表头基本约束
4.1 问题
- 表头不允许赋null值练习
- 表头加默认值练习
- 表头加唯一索引练习
4.2 方案
约束是一种限制，设置在表头上，用来控制表头的赋值，包括以下几种：
- NOT NULL ：非空，用于保证该字段的值不能为空。
- DEFAULT：默认值，用于保证该字段有默认值。
- UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
- PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
- FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
4.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：表头不允许赋空值练习
//建表时给表头设置默认和不允许赋null值
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)//表头name赋null值 报错
mysql> insert into db1.t31 values (null, null , null);
ERROR 1048 (23000): Column 'name' cannot be 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);//表头值重复不可以
mysql> insert into db1.t32 values("tom","666888");
Query OK, 1 row affected (0.08 sec)重复赋值报错
mysql> insert into db1.t32 values("jim","666888");
ERROR 1062 (23000): Duplicate entry '666888' for key 't32.护照'//不重复 可以
mysql> insert into db1.t32 values("jim","766888");
Query OK, 1 row affected (0.05 sec)//查看表记录
mysql> select * from DB1.t43;
+------+--------+
| 姓名 | 护照 |
+------+--------+
| bob | NULL |
| tom | 666888 |
| jim | 766888 |
+------+--------+
3 rows in set (0.00 sec)
