2 MySQL 基本操作数据类型约束条件修改表结构

作者: Kokoronashi | 来源:发表于2019-01-17 05:10 被阅读4次

MySQL 基本操作数据类型约束条件修改表结构

MySQL基本操作

SQL指令分类

名称	命令
DDL 数据定义语言	create alter drop
DML 数据操作语言	insert update delete
DCL 数据控制语言	grant revoke
DTL 数据事物语言	commit rollback savepoint

库管理命令

库类似于系统的文件夹

命令	说明
`show databases;`	显示已有的库
`use 库名`	切换库
`select database();`	显示当前所在的库
`create database 库名;`	创建新库
`show tables;`	显示已有的表
`drop database 库名;`	删除库

表管理命令

表类似于系统的文件

命令	说明
`desc 表明;`	查看表结构
`select * from 表名;`	查看表记录
`drop table 表名;`	删除表

创建表语法

CREATE TABLE 库名.表名(
    字段名1 字段类型(宽度) 约束条件,
    字段名2 字段类型(宽度) 约束条件,
    ....
    字段名N 字段类型(宽度) 约束条件
    );

记录管理命令

**记录类似于文件里的行 **

命令	说明
`select * from 表名;`	查看表记录
`insert into 表名 values(值列表),(第二行);`	插入表记录
`update 表名 set 字段=值;`	修改表记录
`delete from 表名;`	删除表记录

MySQL数据类型

1547623106365

常见的信息种类

类型	常见的信息
数值型	体重身高成绩工资
字符型	姓名工作单位通信住址
枚举型	兴趣爱好性别
日期时间型	出生日期注册时间

数值类型

类型	大小	范围 (默认有符号)	范围 (无符号)	用途
TINYINT	1字节	-128 ~ 127	0 ~ 255	微小整数
SMALLINT	2字节	-32768 ~ 32767	0 ~ 65535	小整数
MEDIUMINT	3字节	-2²³ ~ 2^23-1	0 ~ 2^24-1	中整数
INT	4字节	-2³¹ ~ 2^31-1	0 ~ 2^32-1	大整数
BIGINT	8字节	-2⁶³ ~ 2^63-1	0 ~ 2^64-1	极大整数
FLOAT	4字节			单精度浮点数
DOUBLE	8字节			双精度浮点数
DECIMAL	对DDECIMAL(M,D)	其中M为有效位数	D为小数位,M应大于D	占用M+2字节

整数型

默认MySQL库名,表名,字段名支持中文,默认创建表CHARSET=latin1不支持中文数据,建表时需要设置默认字符集包含中文.默认int为带符号型整数,如果只需要正值,创建字段时需要添加unsgined.

 CREATE TABLE `t1` (
    `姓名` char(15) DEFAULT NULL,
    `班级` char(7) DEFAULT NULL,
    `年龄` tinyint(3) unsigned DEFAULT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

浮点型

定义格式: float(总宽度,小数位数)

当字段值与类型不匹配时,字段值作为0处理

数值超出范围时,仅保存最大/最小值

mysql root@localhost:db1> create table t4(socre double(8,2));                      
Query OK, 0 rows affected

mysql root@localhost:db1> desc t4;                                                 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| socre | double(8,2) | YES  |     | <null>  |       |
+-------+-------------+------+-----+---------+-------+

字符类型

定长: char(字符数)

默认字符 1
最大长度255字符
不够制定字符数时在右边用空格补齐
字符数超出时,无法写入数据

变长: varchar(字符数)

不写语法错误
按数据实际大小分配存储空间
字符数超出时,无法写入数据

大文本类型: text/blob

主要用来存放2 进制数据,例如图片视频,不推荐直接存图片视频
字符数大于65535存储时使用

数值类型宽度数和字符类型宽度区别

age int(3) 显示宽度,默认11,如果不限制当位数不足时在左边补空格

name char(2) 字符宽度

枚举类型

类型	说明	定义格式
ENUM	从给定值集合中选择单个值	enum(值1,值2,值N)
SET	从给定值集合中选择一个或多个值	set(值1,值2,值N)

mysql root@localhost:db1> create table t6( 
                          name char(15), 
                          age tinyint unsigned, 
                          pay float(7,2), 
                          sex enum('male','female'), 
                          likes set('money','eat','sex'));

mysql root@localhost:db1> insert into t6 values('leo',30,15000,'male','money,eat,se
                          x'),('lion',18,3500,2,'eat');

日期时间类型

日期时间类型	说明	格式	范围	占用字节
year	年	YYYY	1901 ~ 2155	占用1个字节
date	日期	YYYYMMDD	0001-01-01 ~ 9999-12-31	占用4个字节
time	时间	HHMMSS		占用3个字节
datetime	日期时间	YYYYMMDDHHmmSS	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999	占用8个字节
timestamp	日期时间	YYYYMMDDHHmmSS	1970-01-01 00:00:00.000000 ~ 2038-01-19 03:14:07.999999	占用4个字节

关于日期时间字段

当未给TIMESTAMP字段赋值时,自动以当前系统时间赋值,而DATETIME字段默认赋值为 NULL

YEAR年份的处理

默认用4位数字表示
当只用2位数字赋值时,01_69视为20002069,而70_99视为19701999

时间函数

类型	用途	括号内是否需要写值
now()	获取系统当前日期和时间	N
curdate()	获取当前的系统日期	N
curtime()	获取当前的系统时刻	N
sleep(N)	休眠N秒	Y
year()	执行时动态获得系统日期时间	Y
month()	获取指定时间中的月份	Y
date()	获取指定时间中的日期	Y
time()	获取指定时间中的时刻	Y

mysql> insert into t7 values('jing',year(19901120),date(now()),093000,now());

datetime 和 timestamp区别

范围不同

日期时间类型	说明	格式	范围	占用字节
datetime	日期时间	YYYYMMDDHHmmSS	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999	占用8个字节
timestamp	日期时间	YYYYMMDDHHmmSS	1970-01-01 00:00:00.000000 ~ 2038-01-19 03:14:07.999999	占用4个字节

Default值不同,timestamp默认取当前系统时间

+----------+-----------+------+-----+-------------------+-----------------------------+
| Field    | Type      | Null | Key | Default           | Extra                       |
+----------+-----------+------+-----+-------------------+-----------------------------+
| meetting | datetime  | YES  |     | NULL              |                             |
| party    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

练习

按如下表创建stuinfo表

1547634058541

#创建stu库设定utf8为默认字符集
mysql root@localhost:stu> create database stu CHARACTER SET utf8;
#创建stuinfo表
mysql root@localhost:stu> create table stuinfo( 
                          学号 char(9), 
                          姓名 char(15), 
                          性别 enum('男','女'), 
                          手机号 char(11), 
                          通信地址 char(40));
#插入数据
mysql root@localhost:stu> insert into stuinfo values 
                          ('NSD131201','张三','男','13012345678','朝阳区劲松南路'),
                          ('NSD131202','韩梅梅','女','13722223333','海淀区北环三路'), 
                          ('NSD131203','王五','男','18023445678','丰台区兴隆中街');

mysql root@localhost:stu> select * from stuinfo;                                   
+-----------+--------+------+-------------+----------------+
| 学号      | 姓名   | 性别 | 手机号      | 通信地址       |
+-----------+--------+------+-------------+----------------+
| NSD131201 | 张三   | 男   | 13012345678 | 朝阳区劲松南路 |
| NSD131202 | 韩梅梅 | 女   | 13722223333 | 海淀区北环三路 |
| NSD131203 | 王五   | 男   | 18023445678 | 丰台区兴隆中街 |
+-----------+--------+------+-------------+----------------+

创建一个学员表,包括姓名,入学年份,生日,培训时间段

mysql root@localhost:stu> create table stuinfo2( 
                          name char(15), 
                          starty year, 
                          birth date, 
                          ttime1 time, 
                          ttime2 time);

mysql root@localhost:stu> insert into stuinfo2 values 
                          ('leo',2010,19880729,0800,1800);

mysql root@localhost:stu> select * from stuinfo2;                                  
+------+--------+------------+---------+----------+
| name | starty | birth      | ttime1  | ttime2   |
+------+--------+------------+---------+----------+
| leo  | 2010   | 1988-07-29 | 8:00:00 | 18:00:00 |
+------+--------+------------+---------+----------+

约束条件

命令	说明
NULL	允许为空,默认设置
NOT NULL	不允许为空
Key	索引类型
Default	设置默认值,缺省为NULL

mysql> create table t14( 
                          name char(5) not null, 
                          level int(3) zerofill default 0, 
                          money tinyint(2) zerofill default 0);

mysql> desc t14;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name  | char(5)                      | NO   |     | NULL    |       |
| level | int(3) unsigned zerofill     | YES  |     | 000     |       |
| money | tinyint(2) unsigned zerofill | YES  |     | 00      |       |
+-------+------------------------------+------+-----+---------+-------+

修改表结构

[图片上传失败...(image-315ac5-1547759051212)]

语法结构

基本用法:

ALTER TABLE 表名 执行动作;

执行动作	说明
add	添加字段
modify	修改字段类型
change	修改字段名
drop	删除字段
rename	修改表名

添加字段

语法

ALTER TABLE 表名
ADD 字段名 类型(宽度) 约束条件 [ First | AFTER 字段名 ],
ADD 字段名2 类型(宽度) 约束条件 [ First | AFTER 字段名 ],
... ...,
ADD 字段名N 类型(宽度) 约束条件 [ First | AFTER 字段名 ];

eg

alter table t15
add email varchar(30) default "stu@tedu.cn",
add tel char(11),
add stu_id char(9) first,
add sex enum("male","female","unknow") default "unknow" after name;

mysql> desc t15;
+--------+--------------------------------+------+-----+-------------+-------+
| Field  | Type                           | Null | Key | Default     | Extra |
+--------+--------------------------------+------+-----+-------------+-------+
| stu_id | char(9)                        | YES  |     | NULL        |       |
| name   | char(15)                       | NO   |     |             |       |
| sex    | enum('male','female','unknow') | YES  |     | unknow      |       |
| level  | int(3) unsigned zerofill       | YES  |     | 000         |       |
| money  | tinyint(2) unsigned zerofill   | YES  |     | 00          |       |
| emial  | varchar(30)                    | YES  |     | stu@tedu.cn |       |
| tel    | char(11)                       | YES  |     | NULL        |       |
+--------+--------------------------------+------+-----+-------------+-------+

修改字段类型

语法

ALTER TABLE 表名
MODIFY 字段名 类型(宽度) 约束条件 [ First | AFTER 字段名 ],
MODIFY 字段名2 类型(宽度) 约束条件 [ First | AFTER 字段名 ],
... ...,
MODIFY 字段名N 类型(宽度) 约束条件 [ First | AFTER 字段名 ];

修改字段类型时,若新的类型与字段已经存储数据冲突,不允许修改.不修改的部分要原样写一遍,否则会还原为默认值

如果字段类型省略,都会使用默认值,如不想使用默认值,需要将本来的约束条件写出来

eg

mysql> alter table t15
    -> modify name varchar(15) not null first,
    -> modify emial varchar(30) after tel;

mysql> desc t15;
+--------+--------------------------------+------+-----+---------+-------+
| Field  | Type                           | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| name   | varchar(15)                    | NO   |     | NULL    |       |
| stu_id | char(9)                        | YES  |     | NULL    |       |
| sex    | enum('male','female','unknow') | YES  |     | unknow  |       |
| level  | int(3) unsigned zerofill       | YES  |     | 000     |       |
| money  | tinyint(2) unsigned zerofill   | YES  |     | 00      |       |
| tel    | char(11)                       | YES  |     | NULL    |       |
| emial  | varchar(30)                    | YES  |     | NULL    |       |
+--------+--------------------------------+------+-----+---------+-------+

修改字段名

语法

ALTER TABLE 表名
CHANGE 原字段名 新字段名 类型(宽度) 约束条件,
CHANGE 原字段名2 新字段名2 类型(宽度) 约束条件,
... ...
CHANGE 原字段名N 新字段名N 类型(宽度) 约束条件;

change不可以修改字段顺序

eg

alter table t15
change Email email varchar(20);

必须带字段类型和约束条件否则报错

删除字段

语法

ALTER TABLE 表名
DROP 字段名,
DROP 字段名2;
... ...
DROP 字段名N;

eg

alter table t15
drop emial,
drop tel,
add email varchar(30) default "stu@qq.com";

修改表名

语法

ALTER TABLE 表名
RENAME 新表名;

网友评论

数据库

本文标题：2 MySQL 基本操作数据类型约束条件修改表结构

本文链接：https://www.haomeiwen.com/subject/hllbdqtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

2 MySQL 基本操作 数据类型 约束条件 修改表结构

MySQL 基本操作 数据类型 约束条件 修改表结构