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

Mick方

于 2024-02-23 08:37:06 发布

阅读量1.5k

点赞数 42

文章标签： adb android

本文链接：https://blog.csdn.net/fmj121030/article/details/136246831

版权

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)
    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 问题

练习字符类型的使用
练习数值类型的使用
练习枚举类型的使用
练习日期时间类型的使用

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 ("翠花",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
    安装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 问题

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

4.2 方案

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

NOT NULL ：非空，用于保证该字段的值不能为空。
DEFAULT：默认值，用于保证该字段有默认值。
UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
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","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)

Mick方

关注

42
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
建库、建表、修改表、复制表、字符类型、数值类型、枚举类型、日期时间类型、检索目录、数据导入命令、数据导入步骤、数据导出命令、非空、默认值、唯一索引

常用数据类型：数值类型、字符类型、日期时间类型、枚举类型,每种类型都有对应的命令表示、有具体的存储范围。步骤三：将db1库user3表所有记录导出, 存到/myload/user.txt文件里。步骤二：将/etc/passwd文件导入db1库的user3表里。仅可以使用数字、字母、下划线、不能纯数字。实现此案例需要按照如下步骤进行。实现此案例需要按照如下步骤进行。实现此案例需要按照如下步骤进行。实现此案例需要按照如下步骤进行。步骤四：练习日期时间类型的使用。步骤三：练习枚举类型的使用。
复制链接

扫一扫