MySQL---表操作

MySQL表操作

创建表

#语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] );#注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的 mysql> create database staff; # 创建库 Query OK, 1 row affected (0.00 sec)mysql> use staff; # 到库目录下 Database changed mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11)); Query OK, 0 rows affected (0.02 sec)mysql> show tables; # 查看库下所有的表 +-----------------+ | Tables_in_staff | +-----------------+ | staff_info | +-----------------+ 1 row in set (0.00 sec)mysql> desc staff_info; # +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)mysql> select id,name,sex from staff_info; # Empty set (0.00 sec)mysql> select * from staff_info; # Empty set (0.00 sec) 创建表 mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT'); Query OK, 1 row affected (0.00 sec)mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher'); Query OK, 1 row affected (0.00 sec)mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> select * from staff_info; +------+----------+------+--------+-------------+---------+ | id | name | age | sex | phone | job | +------+----------+------+--------+-------------+---------+ | 1 | Alex | 83 | female | 13651054608 | IT | | 2 | Egon | 26 | male | 13304320533 | Teacher | | 3 | nezha | 25 | male | 13332353222 | IT | | 4 | boss_jin | 40 | male | 13332353333 | IT | +------+----------+------+--------+-------------+---------+ 4 rows in set (0.00 sec) 插入数据

MySQL的完整性约束

表约束练习代码

查看表结构
查看表结构有两种方式：
　　describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构
　　虽然desc命令可以查看表的定义，但是其输出的信息还不够全面，为了得到更全面的表定义信息，有时候就需要查看创建表的SQL语句，使用show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）

mysql> describe staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)mysql> show create table staff_info\G; *************************** 1. row ***************************Table: staff_info Create Table: CREATE TABLE `staff_info` (`id` int(11) DEFAULT NULL,`name` varchar(50) DEFAULT NULL,`age` int(3) DEFAULT NULL,`sex` enum('male','female') DEFAULT NULL,`phone` bigint(11) DEFAULT NULL,`job` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 row in set (0.01 sec)ERROR: No query specifieddesc和show create table 查看表结构

修改表结构

语法： 1. 修改表名ALTER TABLE 表名 RENAME 新表名;2. 增加字段ALTER TABLE 表名ADD 字段名 数据类型 [完整性约束条件…],ADD 字段名 数据类型 [完整性约束条件…];3. 删除字段ALTER TABLE 表名 DROP 字段名;4. 修改字段ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];5.修改字段排列顺序/在增加的时候指定字段位置ALTER TABLE 表名ADD 字段名 数据类型 [完整性约束条件…] FIRST;ALTER TABLE 表名ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;ALTER TABLE 表名CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;ALTER TABLE 表名MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名; create table t(id int unique,name char(10) not null);#去掉null约束 alter table t modify name char(10) null; # 添加null约束 alter table t modify name char(10) not null;# 去掉unique约束 alter table t drop index id; # 添加unique约束 alter table t modify id int unique;alter处理null和unique约束 alter操作非空和唯一（了解） 1、首先创建一个数据表table_test： create table table_test( `id` varchar(100) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`name`) ); 2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。 先删除主键 alter table table_test drop primary key; 然后再增加主键 alter table table_test add primary key(id); 注:在增加主键之前,必须先把反复的id删除掉。 alter操作主键（了解） 创建press表 CREATE TABLE `press` (`id` int(11) NOT NULL,`name` char(10) DEFAULT NULL,PRIMARY KEY (`id`) ) ；创建book表 CREATE TABLE `book` (`id` int(11) DEFAULT NULL,`bk_name` char(12) DEFAULT NULL,`press_id` int(11) NOT NULL,KEY `press_id` (`press_id`) ) ；为book表添加外键 alter table book add constraint fk_id foreign key(press_id) references press(id);删除外键 alter table book drop foreign key fk_id; 为表添加外键（了解） mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)# 表重命名 mysql> alter table staff_info rename staff; Query OK, 0 rows affected (0.00 sec)mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)# 删除sex列 mysql> alter table staff drop sex; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ rows in set (0.01 sec)# 添加列 mysql> alter table staff add sex enum('male','female'); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0# 修改id的宽度 mysql> alter table staff modify id int(4); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.01 sec)# 修改name列的字段名 mysql> alter table staff change name sname varchar(20); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | sname | varchar(20) | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)# 修改sex列的位置 mysql> alter table staff modify sex enum('male','female') after sname; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(4) | YES | | NULL | | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)# 创建自增id主键 mysql> alter table staff modify id int(4) primary key auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ rows in set (0.00 sec)# 删除主键，可以看到删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key# 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int(11); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.01 sec)mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0# 添加联合主键 mysql> alter table staff add primary key (sname,age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0# 删除主键 mysql> alter table staff drop primary key; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0# 创建主键id mysql> alter table staff add primary key (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(20) | NO | | | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | NO | | 0 | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ rows in set (0.00 sec)# 为主键添加自增属性 mysql> alter table staff modify id int(4) auto_increment; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | NO | | | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | NO | | 0 | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ rows in set (0.00 sec) 示例

 删除表

DROP TABLE 表名;

多表结构的创建与分析

分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）#2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）#3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表#多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系#一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

建立表之间的关系

#一对多或称为多对一 三张表：出版社，作者信息，书一对多（或多对一）：一个出版社可以出版多本书关联方式：foreign key =====================多对一===================== create table press( id int primary key auto_increment, name varchar(20) );create table book( id int primary key auto_increment, name varchar(20), press_id int not null, foreign key(press_id) references press(id) on delete cascade on update cascade );insert into press(name) values ('北京工业地雷出版社'), ('人民音乐不好听出版社'), ('知识产权没有用出版社') ;insert into book(name,press_id) values ('九阳神功',1), ('九阴真经',2), ('九阴白骨爪',2), ('独孤九剑',3), ('降龙十巴掌',2), ('葵花宝典',3) ; 多对一示例 班级和学生 一个班级可以对应多个学生，但一个学生只能对应一个班级主机和机房 一个机房可以有多台主机，但是一个主机只能属于一个机房 #多对多 三张表：出版社，作者信息，书多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多关联方式：foreign key+一张新的表 =====================多对多===================== create table author( id int primary key auto_increment, name varchar(20) );#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了 create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) );#插入四个作者，id依次排开 insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每个作者与自己的代表作如下 egon: 九阳神功 九阴真经 九阴白骨爪 独孤九剑 降龙十巴掌 葵花宝典 alex: 九阳神功 葵花宝典 yuanhao: 独孤九剑 降龙十巴掌 葵花宝典 wpq: 九阳神功insert into author2book(author_id,book_id) values (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,1), (2,6), (3,4), (3,5), (3,6), (4,1) ;sql示例 多对多示例 服务和机器 一个服务可能被部署到多台机器上，一台机器上也可以部署多个服务学生和课程 一个学生可以选择多门课程，一门课程也可以被多个学生选择 #一对一 两张表：学生表和客户表一对一：一个学生是一个客户关联方式：foreign key+unique create table customer(-> id int primary key auto_increment,-> name varchar(20) not null,-> qq varchar(10) not null,-> phone char(16) not null-> );create table student(-> id int primary key auto_increment,-> class_name varchar(20) not null,-> customer_id int unique, #该字段一定要是唯一的-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique-> on delete cascade-> on update cascade-> );#增加客户 mysql> insert into customer(name,qq,phone) values-> ('韩蕾','31811231',13811341220),-> ('杨澜','123123123',15213146809),-> ('翁惠天','283818181',1867141331),-> ('杨宗河','283818181',1851143312),-> ('袁承明','888818181',1861243314),-> ('袁清','112312312',18811431230)mysql> #增加学生 mysql> insert into student(class_name,customer_id) values-> ('脱产1班',3),-> ('周末1期',4),-> ('周末1期',5)-> ; 一对一示例 例一：一个用户只有一个博客用户表：id name1 egon2 alex3 wupeiqi博客表 fk+uniqueid url name_id1 xxxx 12 yyyy 33 zzz 2例二：一个管理员唯一对应一个用户用户表：id user password1 egon xxxx2 alex yyyy管理员表：fk+uniqueid user_id password1 1 xxxxx2 2 yyyyy 其他示例

 

转载于:https://www.cnblogs.com/CatdeXin/p/11066432.html

总结

以上是生活随笔为你收集整理的MySQL---表操作的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。