网站建设的公司在哪找,wordpress m1 cms,成都大型广告公司有哪些,品牌创建的六个步骤Top
NSD DBA DAY04
案例1#xff1a;表管理案例2#xff1a;数据类型案例3#xff1a;数据批量处理案例4#xff1a;表头基本约束 1 案例1#xff1a;表管理
1.1 问题
建库练习建表练习修改表练习
1.2 方案
在MySQL50主机完成练习。
1.3 步骤
实现此案例需要按照如…Top
NSD DBA DAY04
案例1表管理案例2数据类型案例3数据批量处理案例4表头基本约束 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): Cant 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql 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: 0mysql 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: 0mysql 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 1mysql
mysql8 建表默认支持中文字符集 //查看字符集mysql show create table studb.t2 \G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE t2 (name char(3) DEFAULT NULL,address varchar(5) DEFAULT NULL) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci1 row in set (0.00 sec)说明 ENGINEInnoDB 定义存储引擎存储引擎课程里讲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 1mysqlmysql 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 1mysql insert into studb.t8 values (小包总,男,美女,睡,金钱);ERROR 1265 (01000): Data truncated for column 爱好 at row 1mysql//在范围内插入成功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。
检查目录存放导入导出数据时存放数据的文件 [rootmysql50 ~]# mysql -uroot -pNSD2023...amysql 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服务软件时自动创建[rootmysql50 ~]# ls -ld /var/lib/mysql-files/drwxr-x--- 2 mysql mysql 6 Sep 22 2021 /var/lib/mysql-files/[rootmysql50 ~]#修改主配置文件[rootmysql50 ~]# vim /etc/my.cnf.d/mysql-server.cnf[mysqld]secure_file_priv/myload 添加此行:wq创建目录并修改所有者为mysql用户 ,并保证mysql用户对父目录有rx[rootmysql50 ~]# mkdir /myload[rootmysql50 ~]# chown mysql /myload关闭selinuxrootmysql50 ~]# setenforce 0setenforce: SELinux is disabled重启服务[rootmysql50 ~]# systemctl restart mysqld管理员员登陆查看目录[rootmysql50 ~]# mysql -uroot -pNSD2023...amysql show variables like secure_file_priv;----------------------------| Variable_name | Value |----------------------------| secure_file_priv | /myload/ |----------------------------1 row in set (0.01 sec)
步骤二将/etc/passwd文件导入db1库的user3表里。
命令操作如下所示 建库[rootmysql50 ~]# mysql -uroot -pNSD2023...amysql 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/ 查看文件passwdmysql导入数据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.txtmysql system wc -l /myload/user.txt23 /myload/user.txtmysqlmysql system vim /myload/user.txtroot x 0 0 root /root /bin/bashbin x 1 1 bin /bin /sbin/nologindaemon x 2 2 daemon /sbin /sbin/nologinadm x 3 4 adm /var/adm /sbin/nologinlp x 4 7 lp /var/spool/lpd /sbin/nologinsync x 5 0 sync /sbin /bin/syncshutdown x 6 0 shutdown /sbin /sbin/shutdownhalt x 7 0 halt /sbin /sbin/haltmail x 8 12 mail /var/spool/mail /sbin/nologinoperator x 11 0 operator /root /sbin/nologingames x 12 100 games /usr/games /sbin/nologinftp x 14 50 FTP User /var/ftp /sbin/nologinnobody x 65534 65534 Kernel Overflow User / /sbin/nologindbus x 81 81 System message bus / /sbin/nologinsystemd-coredump x 999 997 systemd Core Dumper / /sbin/nologinsystemd-resolve x 193 193 systemd Resolver / /sbin/nologinpolkitd x 998 995 User for polkitd / /sbin/nologinunbound x 997 994 Unbound DNS resolver /etc/unbound /sbin/nologintss x 59 59 Account used for TPM access /dev/null /sbin/nologinchrony x 996 993 /var/lib/chrony /sbin/nologinsshd x 74 74 Privilege-separated SSH /var/empty/sshd /sbin/nologintcpdump x 72 72 / /sbin/nologinmysql x 27 27 MySQL Server /var/lib/mysql /sbin/nologin 4 案例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)//验证默认值和不允许为nullmysql 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 );//查看表头 唯一索引标志UNImysql 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)
