在MySQL中可以将SQL分为4大类:
DDL数据定义语言,create创建、alter修改、drop删除、truncate截断
DML数据操纵语言,insert插入、update修改、delete删除
DQL数据查询语言,select查询
DCL数据控制语言,grant授权、revoke回收权限
DDL数据定义语言:
用于定义数据库对象的操作语句,分为数据库和数据表
1. 数据库操作
创建操作:create database 数据库名称 default character set utf8;若数据库已存在则报错
create database if not exists 数据库名称 default character set utf8;若数据库已存在 则不创建不会不报错。
删除操作:drop database 数据库名称;(不存在报错)
drop database if not exists 数据库名称;(不存在也不报错)
查看所有数据库:show databases;mysql中允许同时打开多个数据库
切换当前数据库:use 数据库名称;(不存在会报错)
查看当前操作的数据库:select database();
查看数据库的创建语句:show create datbase test;
查看当前操作的数据库:select database();
查看数据库的创建语句:show create datbase test;
2.数据表的操作
1.创建数据表: create table 数据表的名称(数据列的名称 数据类型 约束规则,......)engine=存
储引擎名称 default charset utf8;
实际上从MySQL5.5开始默认存储引擎就是innodb,但是一般建议还是添加上存储引擎的设置。数据表创建完成,则会发现在data/数据库,目录下出现一个ibd文件(数据表)。
create table tb_student( 按照阿里的规范,要求基表名称必须使用【tb_表】
id bigint primary key auto_increment,
name varchar(20) not null,
sex boolean default 1
) engine=innodb default charset utf8;
2.查看表中的列定义:desc tb_student;
查看具体的列定义:show create table tb_student;
查看当前数据库种的所有表:show tables;
查看表结构:desc 表名称;
查看创建表的sql语句:show create table tb_student;
3.修改表名称:rename table 原始表名称 to 新的表名称;
快速创建一个表和另外一个表的结构相同,只是克隆表结构,不会克隆数据:
create table 新1 表名称 like 已经存在的旧表名称;
修改表结构:alter table 表名称 add/drop/modify 列名称 [类型 :约束]
add增加列:alter table tb_student add class_name varchar(20) default '软件19';
drop删除列:alter table tb_student drop column salary;
修改列定义:alter table tb_student modify class_name int;
修改列定义时,要求已经存在的数据必须符合规则,否则修改失败。modify一般用于
修改列的数据类型和长度,但是如果有数据则必须和目标类型匹配,否则修改失败
修改列的名称:alter table 表名称 change 旧有列名称 新列名称 类型 约束;
4.删除表:drop table 表名称;
3.数据类型
数据类型主要给列上添加域完整性约束。开发者设置列的数据类型后,DBMS会对添加的数据进行合法性验证,如果不合法则报错,拒绝添加。
数据类型主要分为字符串类型、定点数和浮点数类型、日期类型、大对象类型,据类型在不同的存储引擎上表现不同
数值型
类型名称说明
tinyint | 1B 取值范围-128到127 |
smallint | 2B 取值范围为-32768到32767 |
mediumint | 3B 取值范围为-8388608到8388607 |
int | 4B 取值范围为2e9 |
bigint | 8B 取值范围为9e18 |
float | 4B 单精度浮点型,取值范围3.4e38 不能精确存 |
double | 8B 双精度浮点型,取值范围1.7e308 不能精确存 |
decimal和numeric | 采用定点存放浮点数,具体字节数取决于定义时设置的参数,能精确存 |
unsigned设置列为无符号型,只能存放大于等于0的数据,没有负数。当使用无符号类型时取值范围由于没有负数部分,从而导致上限扩大一倍。
decimal和numeric作为字符串存储浮点数,可以实现浮点数的精确存储;
使用方法numeric(总位宽,小数位数),小数位数必须小于等于总位宽;
小数位数最大值30,总位宽最大值为65,注意可能存储的数据会超出范围;
其中的符号位和小数点不占位宽,
int类型不能保存小数位,存储小数时会自动进行四舍五入
auto_increment一般用于主键,可以实现该列的自动生成连续整数值
unsigned禁用负值
zerofill如果查询显示时,实际数据小于指定位宽,则自动添加0值
default用于设置默认值
null的意思为未知的数据,它既不是空字符串,不是任何一个具体的值;不能把任何值与一个null值进行比较。因为null的意思是不确定的值。真正的null值比较必须使用特殊的运算符is null或者is not null
字符串类型
char(n)定长字符串,n值用于表示所允许的最大长度,取值范围0-255
长度限制不区分中英文,表示是允许最多存储多少个字符
使用length函数获取的是字节数,不是字符数。在utf-8编码种一个汉字占用3个字节
varchar(n)可变长度的字符串,n值取值范围为0-65535
实际存储多少个字符就占多少个位置,每个列所占用的长度根据具体内容变化
大对象类型Lob
大对象类型可以分为2大类:文本类型和二进制类型
BLOB是一个二进制大对象,可以容纳可变长度的数据,其中分为tinyblob、blob、
mediumblob、longblob。一般用于存储二进制数据,例如声音、图像、视频等
Text是一个文本大对象,可以容纳可变长度的数据,其中分tinytext、text、mediumtext、
longtext,一般用于存储文本类型数据,例如小说、商品简介
类型名称 | 允许的最大长度 | 说明 |
tinytext | 0-255 | 短文本字符串 |
text | 0-65535 | 长文本字符串 |
mediumtext | 16M | 中等长度的长文本字符串 |
Longtext | 4G | 极大文本数据 |
Tinyblob | 0-255 | 不超过255个长的二进制串 |
Blob | 0-65535 | 二进制类型的长文本字符串 |
Mediumblob | 16M | 中等长度的二进制长文本字符串 |
longblob | 4G | 极大二进制数据 |
日期时间类型
类型名称 | 说明 | 格式 |
date | 日期类型,有效范围为1000-9999年,不包含时分秒 | 3B yyyy-MM-dd |
Time | 时间类型,有效范围为-838到838小时范围内,一般用于表示时间值或者持续时间。不包含年月日 | 3B HH:mm:ss |
year | 年份类型,有效范围1901到2155年 | 1B yyyy |
datetime | 日期时间类型,其中包含日期和时间,可以精确到秒,有效范围为1000到9999年。具体存储时和时区无关 | 8B yyyy-MM-dd HH:mm:ss |
timstamp | 时间戳类型,可以精确到秒,有效分为是1970到2038年,同时支持自动存储修改的时间,和当前时区相关 | 4B,yyyyMMddhhmmss |
集合和枚举类型
set集合类型,可以设置多个集合成员,允许从多个集合成员种选择多个数据进行插入
enum枚举类型,可以设置允许选用的多个值,列只能赋予某个枚举成员值
位类型
bit(1)表示可以存储一个二进制位,只能存储0或者1,其中n的最大上限值为64
bit(8)表示可以存储8个二进制位的值,取值范围为0到255
4.完整性约束
DBMS为了保证存储的数据都是完整有效的,所以提供针对插入的数据进行检查。开发人员指定对应的规则,由DBMS负责检查,如果试图插入不合法的数据,则会自动报错,拒绝插入。
实际有6种约束,可以分为3大类完整性约束。
实体完整性,由主键约束实现
参照完整性,由外键约束实现
用户自定义完整性,由其他4种约束实现
1.not null,非空约束,指定列值不允许为空 not null,
2、default,默认约束一般默认约束经常和非空约束一起使用,当不插入数据时,默认值生效。
3、primary key,主键约束,又叫做实体完整性,不允许数据重复存储,主键可以唯一标识一行数 据主键约束含义是非空、唯一
4、unique,唯一约束,可以没有null值约束,而且也不能针对null进行唯一性判定
5、外键约束,又叫做参照完整性
6、检查约束