MySQL-----表操作.数据库字典.表约束

目录

6.表操作

1.创建表

2.修改字段

3.改表名

4.删表

5.复制表

7.数据库字典

8.表约束

1.约束

2.外键约束

3.修改约束

1.修改主键约束

2.修改非空约束

3.修改唯一约束

4.修改外键约束

---

6.表操作

1.创建表

1.创建表user 温馨提示: 为表明及字段名建议添加反引号 ``
mysql> create table user (
-> user_id int(10),
-> username varchar(255),
-> password varchar(255)
-> )
-> ;
Query OK, 0 rows affected (0.03 sec)2.查看当前库的所有表
mysql> show tables; 
+--------------------------+
| Tables_in_table_lls_test |
+--------------------------+
| user 					   |
+--------------------------+
1 row in set (0.00 sec)3.查看表的创建语句
mysql> show create table user; 
+-------+----------------------------------------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`user_id` int(10) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | -- 5.引擎和字符集
+-------+-----------------------------------------------------------------------+
1 row in set (0.01 sec)4.展示表的详细信息
mysql> desc user; 
+----------+--------------+------+-----+---------+-------+
| Field    | Type 		  | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| user_id  | int(10) 	  | YES  | 		| NULL	 |		 |
| username | varchar(255) | YES  |		| NULL   |	 	 |
| password | varchar(255) | YES  |	    | NULL   | 		 |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.修改字段

1.原表
mysql> desc user; 
+----------+--------------+------+-----+---------+-------+
| Field    | Type 		  | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| user_id  | int(10) 	  | YES  | 		| NULL	 |		 |
| username | varchar(255) | YES  |		| NULL   |	 	 |
| password | varchar(255) | YES  |	    | NULL   | 		 |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)2.修改字段的类型
mysql> alter table user modify username varchar(20); 
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table user modify password int(6); 
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 03.添加字段
mysql> alter table user add name varchar(255);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table user add nickname varchar(255); 
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 04.删除表字段
mysql> alter table user drop password;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 05.改变字段名及类型
mysql> alter table user change username username_vip varchar(255); 
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 05.改表
mysql> desc user;
+--------------+--------------+------+-----+---------+-------+
| Field 		| Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| user_id 		| int(10)	  | YES  | 	   | NULL	 | 		 |
| username_vip  | varchar(255)| YES  |	   | NULL	 |		 |
| name 			| varchar(255)| YES  | 	   | NULL	 | 		 |
| nickname 		| varchar(255)| YES  | 	   | NULL	 |		 |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.改表名

1.改前
mysql> show tables; 
+--------------------------+
| Tables_in_table_lls_test |
+--------------------------+
| user 					   |
+--------------------------+
1 row in set (0.00 sec)2.修改表名
mysql> alter table user rename admin_user; 
Query OK, 0 rows affected (0.00 sec)3.改后
mysql> show tables;
+--------------------------+
| Tables_in_table_lls_test |
+--------------------------+
| admin_user 				|
+--------------------------+
1 row in set (0.00 sec)4.修改表名的第二种方式
mysql> rename table admin_user to user;
Query OK, 0 rows affected (0.01 sec)

4.删表

判断后删除表
mysql> drop table if exists user;
Query OK, 0 rows affected (0.01 sec)

5.复制表

 1.复制原表(user)结构到新表(user_test)中----蠕虫复制:先复制表结构，后插入原表数据
mysql> create table user_test like user; 
Query OK, 0 rows affected (0.02 sec)2.查看表
mysql> show tables; 
+--------------------------+
| Tables_in_table_lls_test |
+--------------------------+
| user 						|
| user_test 				|
+--------------------------+
2 rows in set (0.00 sec)3.查看复制的新表信息--结构
mysql> desc user_test; 
+----------+--------------+------+-----+---------+-------+
| Field    | Type 		  | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| user_id  | int(10) 	  | YES  | 		| NULL 	| 		|
| username | varchar(255) | YES  | 		| NULL	 |		 |
| password | varchar(255) | YES  | 		| NULL	 | 		|
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)4.查询原表user--数据
mysql> select * from user; 
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 	  | lijian	 | 111 		|
+---------+----------+----------+
1 row in set (0.00 sec)5.查看新表--数据
mysql> select * from user_test; 
Empty set (0.00 sec)6.插入原表数据到新表--数据
mysql> insert into user_test select * from user; 
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 07.查看新表数据，已复制成功--数据
mysql> select * from user_test; 
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 	  | lijian	 | 111 		|
+---------+----------+----------+
1 row in set (0.00 sec)8.复制表的第二种方式
mysql> create table user_test2 select * from user; 
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> show tables;
+--------------------------+
| Tables_in_table_lls_test |
+--------------------------+
| user					   |
| user_test 				|
| user_test2 				|
+--------------------------+
3 rows in set (0.00 sec)mysql> select * from user_test2;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 	  | lijian	 | 111	    |
+---------+----------+----------+
1 row in set (0.00 sec)

7.数据库字典

use information_schema
Database changed
mysql> show tables; -- 1.打开数据库系统表
tables－存放数据库里所有的数据表、以及每个表所在数据库。
schemata－存放数据库里所有的数据库信息
views－存放数据库里所有的视图信息。
columns－存放数据库里所有的列信息。
triggers－存放数据库里所有的触发器。
routines－存放数据库里所有存储过程和函数。
key_column_usage－存放数据库所有的主外键
table_constraints－存放数据库全部约束。
statistics－存放了数据表的索引。

8.表约束

1.约束

需要数据强制执行的数据校验规则，作用: 保证数据的完整性，可以保护数据

五大类约束:

1. NOT NULL ----not null非空约束 数据字段不能为空

2. UNIQUE----unique 唯一约束 数据唯一

3. PRIMARY KEY ---primary key主键约束 "数据的序号" 更加高效查询

4. FOREIGN KEY 外键约束 数据之间有关联关系

5. CHECK 检查约束

   第一题：
   学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
   学号，姓名，性别，年龄，所在系 Sno为主键
   mysql> create table `student`(->     `son` int(10) primary key,->      `Sname` varchar(255) unique,->      `Ssex` enum('男','女'),->      `Sang` int(10) ,->     `Sdept` varchar(255) default '计算机');
   Query OK, 0 rows affected (0.00 sec)mysql> desc student;
   +-------+-------------------+------+-----+-----------+-------+
   | Field | Type              | Null | Key | Default   | Extra |
   +-------+-------------------+------+-----+-----------+-------+
   | son   | int(10)           | NO   | PRI | NULL      |       |
   | Sname | varchar(255)      | YES  | UNI | NULL      |       |
   | Ssex  | enum('男','女')   | YES  |     | NULL      |       |
   | Sang  | int(10)           | YES  |     | NULL      |       |
   | Sdept | varchar(255)      | YES  |     | 计算机    |       |
   +-------+-------------------+------+-----+-----------+-------+
   5 rows in set (0.00 sec)mysql> desc student;
   +-------+-------------------+------+-----+-----------+-------+
   | Field | Type              | Null | Key | Default   | Extra |
   +-------+-------------------+------+-----+-----------+-------+
   | son   | int(10)           | NO   | PRI | NULL      |       |
   | Sname | varchar(255)      | YES  | UNI | NULL      |       |
   | Ssex  | enum('男','女')   | YES  |     | NULL      |       |
   | Sang  | smallint(6)       | YES  |     | NULL      |       |
   | Sdept | varchar(255)      | YES  |     | 计算机    |       |
   +-------+-------------------+------+-----+-----------+-------+
   1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。
   2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。课程表：Course (Cno, Cname,)
   课程号，课程名 Cno为主键
   mysql> create table `Coures`(-> `Con` int(10) primary key,-> `Cname` varchar(255));
   Query OK, 0 rows affected (0.01 sec)学生选课表：SC (Sno, Cno, Score)
   学号，课程号，成绩 Sno,Cno为主键
   mysql>  create table `SC`(->  `son` int ,->  `Con` int ,->  `Score` int(10),->   primary key (son,Con),->   foreign key (son) references student(son),->   foreign key (Con) references Coures(Con));
   Query OK, 0 rows affected (0.01 sec)
   

1.创键带有约束的表
mysql> create table `const_test`( 
-> `conts_test_id` int(10) primary key, -- 主键约束
-> `name` varchar(255) not null, -- 非空约束
-> `idcard` varchar(255) unique, -- 唯一约束
-> `nickname` varchar(255));
Query OK, 0 rows affected (0.04 sec)查看约束信息
mysql> desc const_test; 
+---------------+--------------+------+-----+---------+-------+
| Field		    | Type 		   | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| conts_test_id | int(10) 	   | NO	  | PRI | NULL	  | 	  |
| name 			| varchar(255) | NO   | 	| NULL 	  | 	  |
| idcard 		| varchar(255) | YES  | UNI | NULL 	  | 	  |
| nickname 		| varchar(255) | YES  | 	| NULL	  |		  |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)查看无约束表 对比
mysql> desc user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type 		  | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| user_id  | int(10)	  | YES  |	   | NULL	 | 		 |
| username | varchar(255) | YES  | 	   | NULL	 |		 |
| password | varchar(255) | YES  |	   | NULL	 | 		 |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)2.默认值的增加和主键的自增长策略(从1开始)
mysql> create table `const_test2` ( 
-> `const_test2_id` int(10) primary key not null auto_increment,
-> `name` varchar(255) not null default 'zhangsan',
-> `idcard` varchar(255) not null unique,
-> `nickname` varchar(255) default "lucy");
Query OK, 0 rows affected (0.04 sec)mysql> desc const_test2 ;
+----------------+--------------+------+-----+----------+----------------+
| Field 		 | Type		    | Null | Key | Default  | Extra 		 |
+----------------+--------------+------+-----+----------+----------------+
| const_test2_id | int(10) 		| NO   | PRI | NULL 	| auto_increment | -- 自增长策略
| name 			 | varchar(255) | NO   | 	 | zhangsan |        		 |
| idcard 		 | varchar(255) | NO   | UNI | NULL	    | 				 |
| nickname 		 | varchar(255) | YES  |	 | lucy 	| 				 |
+----------------+--------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)3.增加数据测试
mysql> insert into const_test2
values(null,"zhangsan111","326176378123612783",null);
Query OK, 1 row affected (0.02 sec)4.查看数据
mysql> select * from const_test2; 
+----------------+-------------+--------------------+----------+
| const_test2_id | name 	   | idcard 			| nickname |
+----------------+-------------+--------------------+----------+
| 1 			 | zhangsan111 | 326176378123612783 | NULL 		|
+----------------+-------------+--------------------+----------+
1 row in set (0.00 sec)5.再次加入重复数据，测试唯一约束
mysql> insert into const_test2
values(null,"zhangsan111","326176378123612783",null);
ERROR 1062 (23000): Duplicate entry '326176378123612783' for key 'idcard'

2.外键约束

格式:

constraint 约束名 foreign key 外键约束字段 references 主表(主表外键关联字段)
constraint teacher_student_teacher_id_fk foreign key (teacher_id) references teacher(teacher_id)

外键约束----有外键约束后可以删除从表，不能删除主表，除非删除外键约束。

----1. 创建数据库
mysql> create database fk_table_lls; 
Query OK, 1 row affected (0.00 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fk_table_lls |
| mysql |
| performance_schema |
| sys |
| table_lls_test |
| test |
+--------------------+
7 rows in set (0.00 sec)mysql> use fk_table_lls
Database changed-- 2.创建主表教师表
mysql> create table teacher(
-> teacher_id int(10) primary key auto_increment,
-> teacher_name varchar(255));
Query OK, 0 rows affected (0.01 sec)-- 3.添加测试数据
mysql> insert into teacher values(null,"lijian"); 
Query OK, 1 row affected (0.01 sec)mysql> select * from teacher;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 1 		 | lijian 		|
+------------+--------------+
1 row in set (0.00 sec)-- 4.创建从表学生表，并建立外键约束
mysql> create table student( 
-> student_id int(10) primary key auto_increment,
-> student_name varchar(255),
-> teacher_id int(10),
-> constraint teacher_student_teacher_id_fk foreign key (teacher_id) references teacher(teacher_id));
Query OK, 0 rows affected (0.01 sec)-- 5.查看外键约束信息
mysql> desc teacher;
+--------------+--------------+------+-----+---------+----------------+
| Field 	   | Type 		  | Null | Key | Default | Extra		 |
+--------------+--------------+------+-----+---------+----------------+
| teacher_id   | int(10) 	  | NO   | PRI | NULL    | auto_increment |
| teacher_name | varchar(255) | YES  | 	   | NULL    | 					|
+--------------+--------------+------+-----+------ ---+----------------+
2 rows in set (0.00 sec)mysql> desc student; 
+--------------+--------------+------+-----+---------+----------------+
| Field 	   | Type		  | Null | Key | Default | Extra 		  |
+--------------+--------------+------+-----+---------+----------------+
| student_id   | int(10) 	  | NO 	 | PRI | NULL 	 | auto_increment |
| student_name | varchar(255) | YES  | 	   | NULL	 | 					|
| teacher_id   | int(10) 	  | YES  | MUL | NULL	 |				 |
+--------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)-- 6.插入测试数据
mysql> insert into student values(null,"jiangtuz",1); 
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+------------+--------------+------------+
| student_id | student_name | teacher_id |
+------------+--------------+------------+
| 1 		 | jiangtuz 	| 1			 |
+------------+--------------+------------+
1 row in set (0.00 sec)mysql> select * from teacher;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 1 		 | lijian 		|
+------------+--------------+
1 row in set (0.00 sec)-- 7.删除从表数据---成功
mysql> delete from student where student_id = 1; 
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
Empty set (0.00 sec)mysql> insert into student values(null,"jiangtuz",1);
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+------------+--------------+------------+
| student_id | student_name | teacher_id |
+------------+--------------+------------+
| 2          | jiangtuz 	| 1 		|
+------------+--------------+------------+
1 row in set (0.00 sec)-- 8.删除主表数据失败，具备关联
mysql> delete from teacher where teacher_name = "lijian";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`fk_table_lls`.`student`, CONSTRAINT
`teacher_student_teacher_id_fk` FOREIGN KEY (`teacher_id`) REFERENCES `teacher`(`teacher_id`))

3.修改约束

1.修改主键约束

-- 创建student2表结构与student基本一致，但是不加约束
mysql> create table student2( 
-> student_id int(10),
-> student_name varchar(255),
-> teacher_id int(10));
Query OK, 0 rows affected (0.01 sec)mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field		   | Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10) 	  | YES  |	   | NULL 	 | 		 |
| student_name | varchar(255) | YES  |	   | NULL	 | 		 |
| teacher_id   | int(10) 	  | YES  | 	   | NULL	 |		 |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)-- 添加主键约束
mysql> alter table student2 add primary key (student_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field 	   | Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10) 	  | NO 	 | PRI | NULL	 |		 |
| student_name | varchar(255) | YES  | 	   | NULL 	 |		 |
| teacher_id   | int(10) 	  | YES  | 	   | NULL 	 |		 |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)-- 删除主键约束
mysql> alter table student2 drop primary key; 
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field 	   | Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10) 	  | NO 	 |	   | NULL	 |		 |
| student_name | varchar(255) | YES  | 	   | NULL 	 |		 |
| teacher_id   | int(10) 	  | YES  | 	   | NULL 	 |		 |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.修改非空约束

 -- 删除非空约束
mysql> alter table student2 modify student_name varchar(255) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field 	   | Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10) 	  | NO 	 |	   | NULL	 |		 |
| student_name | varchar(255) | NO   | 	   | NULL 	 |		 |
| teacher_id   | int(10) 	  | YES  | 	   | NULL 	 |		 |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)-- 添加非空约束
mysql> alter table student2 modify student_name varchar(255) null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field 	   | Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10) 	  | NO 	 |	   | NULL	 |		 |
| student_name | varchar(255) | YES  | 	   | NULL 	 |		 |
| teacher_id   | int(10) 	  | YES  | 	   | NULL 	 |		 |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.修改唯一约束

-- 添加唯一约束
mysql> alter table student2 add unique (student_name); 
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field 	   | Type 		  | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10) 	  | NO 	 |	   | NULL	 |		 |
| student_name | varchar(255) | YES  | UNI | NULL 	 |		 |
| teacher_id   | int(10) 	  | YES  | 	   | NULL 	 |		 |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)-- 删除唯一约束
mysql> alter table student2 drop index student_name; 
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | NO | | NULL | |
| student_name | varchar(255) | YES | | NULL | |
| teacher_id | int(10) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> desc teacher;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| teacher_id | int(10) | NO | PRI | NULL | auto_increment |
| teacher_name | varchar(255) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

4.修改外键约束

-- 添加外键约束
mysql> alter table student2 add constraint student2_teacher_teacher_id_fk foreign key (teacher_id) references teacher(teacher_id); 
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | NO | | NULL | |
| student_name | varchar(255) | YES | | NULL | |
| teacher_id | int(10) | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec) -- 删除外键需要两步
-- 第一步：删除外键约束
mysql> alter table student2 drop foreign key student2_teacher_teacher_id_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+ -- 索引还在
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | NO | | NULL | |
| student_name | varchar(255) | YES | | NULL | |
| teacher_id | int(10) | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)-- 第二步：删除外键索引
mysql> alter table student2 drop index student2_teacher_teacher_id_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc student2;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id | int(10) | NO | | NULL | |
| student_name | varchar(255) | YES | | NULL | |
| teacher_id | int(10) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)