## 连接数据库
```
mysql -h 127.0.0.1 -u root -p
```
## 数据库
查看数据库
```
show databases;
```
创建数据库
```
create database gzsp;
```
使用数据库
```
use gzsp;
```
删除数据库
```
drop database gzsp;
```
## 数据类型
### 数字
整型
tinyint 1 -128 +127
int 4 -21亿4千7百万 + -21亿4千7百万
bigint 8 很大很大
无符号 unsigned
tinyint 1 0 - 255
int 4 0-42亿
bigint 8 很大很大
小数
float 4 单精度
double 8 双精度
成绩 距离 : flaot
银行金额: double
### 字符
char(长度) 定长字符串 0-255
varchar(长度) 字符串 0-65535
text 字符串 0-65535
longtext 42亿
### 时间日期
date 0000-00-00
time 00:00:00
datetime 0000-00-00 00:00:00
timestamps int(无符号)
## 数据表
### 查看数据表
查看数据表列表
```
show tables;
```
查看数据表结构
```
desc student;
```
查看建表语句
```
show create table student;
```
### 创建数据表
create table 表名(
字段信息,
字段信息,
字段信息,
字段信息
);
字段信息: 字段名 字段数据类型 字段注释
```sql
create table student(
student_name varchar(50) comment "学生姓名",
student_age tinyint unsigned comment "学生年龄"
);
```
删除数据库
```
drop table student;
```
## 数据表字段
```
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
```
### 添加字段
ALTER TABLE 表名 ADD 字段信息;
```sql
alter table student add student_sex char(1) comment '学生性别';
```
```
desc student;
```
默认字段添加在 尾部
```
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
```
添加字段在表的最前面
ALTER TABLE 表名 ADD 字段信息 FIRST;
1226 1320 1023 届 学院 专业 班级 年 学号
```sql
alter table student add student_id char(12) comment '学生ID' first;
```
```
desc student;
```
```
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| student_id | char(12) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
```
添加字段到指定字段后面
ALTER TABLE 表名 ADD 字段信息 AFTER 指定字段;
```sql
alter table student add birthday datetime comment '出生年月' after student_age;
```
```
desc student;
```
```
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| student_id | char(12) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
```
## 修改
修改数据类型
ALTER TABLE 表名 MODIFY 字段信息;
```
alter table student modify birthday date comment '出生年月';
```
```
desc student;
```
```
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| student_id | char(12) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| birthday | date | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
```
修改字段名
ALTER TABLE 表名 CHANGE 原字段名 新的字段名 字段信息;
```
alter table student change birthday student_birthday date comment '学生出生日期';
```
```
desc student;
```
```
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| student_id | char(12) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| student_birthday | date | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+------------------+---------------------+------+-----+---------+-------+
```
修改字段顺序
ALTER TABLE 表名 MODIFY 字段信息 AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段信息 FIRST;
```
alter table student modify student_id char(12) comment '学生ID' after student_name;
```
```
desc student;
```
```
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| student_name | varchar(50) | YES | | NULL | |
| student_id | char(12) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| student_birthday | date | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+------------------+---------------------+------+-----+---------+-------+
```
```
alter table student modify student_id char(12) comment '学生ID' first;
```
```
desc student;
```
```
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| student_id | char(12) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| student_age | tinyint(3) unsigned | YES | | NULL | |
| student_birthday | date | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+------------------+---------------------+------+-----+---------+-------+
```
modify change 对比
change: 修改字段名 字段信息(数据类型 注释)
modify: 修改数据类型 注释 顺序
### 删除字段
ALTER TABLE 表名 DROP 字段名;
```
alter table student drop student_age;
```
```
desc student;
```
```
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| student_id | char(12) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| student_birthday | date | YES | | NULL | |
| student_sex | char(1) | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
```
```
insert into student values
('122613201026', '小呆呆', '2003-11-17', '男'),
('122613201027', '小酷酷', '2005-1-4', '女');
```
错误的例子
```
insert into student values('122613201026', '小笨笨', '2005-11-14', '保密');
insert into student values('122613201026', '小笨笨', '2005-11-14', 'nv');
```