#前言:我们说过,库相当于一个文件夹,表相当于文件夹里的一个个文件,表里面的一条记录相当于一行内容,表中的一条记录有对应的标题,称为表的字段
#直观表
#id,name,sex,age为表的字段,其余一行内容称为一条记录
#创建表语法格式
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)
#查看表结构命令语法: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)
#语法格式: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)
#语法格式: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)
#使用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)