MySQL的表操作
#前言：我们说过，库相当于一个文件夹，表相当于文件夹里的一个个文件，表里面的一条记录相当于一行内容，表中的一条记录有对应的标题，称为表的字段
#直观表
#id,name,sex,age为表的字段，其余一行内容称为一条记录
1.建表
#创建表语法格式
Create table <表名> （ <字段名1><类型1>， ….. <字段名n><类型n> ); #提示：
1.其中create table是关键字，不能更改，但是大小写可以变化
2.字段名和类型是必须的
3.同张表中，字段名不能相同
#查看帮助：help create table
#例子:
#1.创建db库 mysql> create database db; Query OK, 1 row affected (0.00 sec) mysql> use db; Database changed #2.创建一个student表 mysql> create table student ( -> id int(4) not null auto_increment, -> name char(20) not null, -> age tinyint(2) not null default '0', -> dept varchar(16) default null, -> primary key(id)); Query OK, 0 rows affected (0.01 sec)
#解释表的字段意思：
create table 表示创建表的固定关键字，student为表名，有四个字段，分别表示
id：学号列， int：数字类型， 4：长度为4， not null：不为空值， auto_incement：自增长
name：名字列，char：定长字符类型， 20：长度为20， not null：不为空值
age：年龄列， tinyint：很小的数字类型，2：长度为2， not null：不为空值， default '0'：默认为0值
dept：系别列，varchar：可变长字符类型，16：长度为16，default null：默认为空
#3.查看建表语句 mysql> show create table student\G; *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.07 sec)
#数据类型
#mysql中，有三种主要的类型：文本、数字和日期/时间类型
#数字类型
类型 大小 用途 TINYINT 1 字节 小整数值 SMALLINT 2 字节 大整数值 MEDIUMINT 3 字节 大整数值 INT或INTEGER 4 字节 大整数值 BIGINT 8 字节 极大整数值 FLOAT 4 字节 单精度 浮点数值 DOUBLE 8 字节
#日期/时间类型
类型 大小(字节) 格式 用途 DATE 3 YYYY-MM-DD 日期值 TIME 3 HH:MM:SS 时间值或持续时间 YEAR 1 YYYY 年份值 DATETIME 8 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
#字符串类型
类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据
#创建表的时候指定存储引擎
mysql> create table test ( -> id int(4) not null auto_increment, -> name char(20) not null, -> primary key(id) -> )engine=innodb; Query OK, 0 rows affected (0.01 sec)
2.查看建表结构及建表语句
#查看表结构命令语法：desc 表名 或者 show columns from 表名
#查看帮助：help desc
mysql> help desc; Name: 'DESC' Description: Syntax: {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { EXTENDED | PARTITIONS | FORMAT = format_name } format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement } The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query). URL: https://dev.mysql.com/doc/refman/5.7/en/explain.html
#例子：
mysql> use db; Database changed
mysql> desc student; #查看表结构 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> show columns from student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
#查看建表语句
mysql> show create table student\G #G以垂直方式显示 *************************** 1. row *************************** Table: student Create Table: CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
3.删除表
#语法格式：drop table <表名>
#例子：删除db库里面的student表
mysql> use db; Database changed mysql> show tables; #查看库中的表 +--------------+ | Tables_in_db | +--------------+ | student | | test | +--------------+ 2 rows in set (0.00 sec) mysql> drop table student; #删除student表 Query OK, 0 rows affected (0.00 sec) mysql> show tables; #再次查看 +--------------+ | Tables_in_db | +--------------+ | test | +--------------+ 1 row in set (0.00 sec)
4.使用alter增删表的字段
#语法格式：alter table 表名 add 字段 类型 其他：
#查看帮助：help alter table
#例子：在test表中添加字段sex,age,qq,类型分别为char(4),int(4),varchar(15)
#1.添加性别列， 默认语句，会默认添加到最后
mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> alter table test add sex char(4); #添加 Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | sex | char(4) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
#2.指定位置添加：指定添加年龄列到name后面的位置
mysql> alter table test add age int(4) after name; #提示：after是后的意思，在name字段添加age Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | sex | char(4) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
#3.指定添加到第一列
mysql> alter table test add qq varchar(15) first; #first：第一 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | qq | varchar(15) | YES | | NULL | | | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | sex | char(4) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
#更改字段类型
#例子：将qq字段的varchar(15)改成char(20)
mysql> alter table test modify column qq char(20); #更改 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | qq | char(20) | YES | | NULL | | | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | sex | char(4) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
#更改字段名称
将qq字段名改为guoke
mysql> alter table test change qq guoke char(20) first; #first指定字段位置 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | guoke | char(20) | YES | | NULL | | | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | sex | char(4) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
#删除字段
#例子：将guoke字段删除
mysql> alter table test drop column guoke; #删除guoke字段 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | int(4) | YES | | NULL | | | sex | char(4) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
5.使用rename或alter更改表名
#使用rename更改表名
#rename语法格式：rename table 原表名 to 新表名
#查看帮助：help rename
#例子：将test表改为student
mysql> show tables; #查看原表名 +--------------+ | Tables_in_db | +--------------+ | test | +--------------+ 1 row in set (0.00 sec) mysql> rename table test to student; #更改 Query OK, 0 rows affected (0.01 sec) mysql> show tables; #更改后再次查看 +--------------+ | Tables_in_db | +--------------+ | student | +--------------+ 1 row in set (0.00 sec)
#使用alter更改表名
#语法：alter table 原表名 rename to 新表名
#例子：将student表改为guoke
mysql> show tables; +--------------+ | Tables_in_db | +--------------+ | student | +--------------+ 1 row in set (0.00 sec) mysql> alter table student rename to guoke; #更改表名 Query OK, 0 rows affected (0.01 sec) mysql> show tables; +--------------+ | Tables_in_db | +--------------+ | guoke | +--------------+ 1 row in set (0.00 sec)
