一、数据库操作
1.创建数据库 2.查询数据库 3.修改数据库 4.使用数据库
二、表结构操作
1.创建表 2.查询表 3.修改表 4.删除表
三、表数据操作
1.新增数据 2.修改数据 3.删除数据
四、查询数据
1.基础查询 2.条件查询 3.排序查询 4.聚合函数 5.分组查询 6.分页查询
五、数据类型
1.整型 2.浮点 3.定点数 4.日期和时间 5.字符 6.枚举
一、数据库操作
1.创建数据库
- 直接创建数据库
create database student;
- 不存在则创建,存在则不创建
create database if not exists student;
- 创建数据库,不存在则创建,并指定字符集
create database if not exists student character set utf8;
2.查询数据库
- 显示所有数据库
show databases;
- 查看某个数据库的创建以及字符集
show create database student;
3.修改数据库
- 修改数据库字符集
alter database student character set utf8;
4.使用数据库
- 查询当前正在使用的数据库
select database();
- 使用某个数据库
use student;
二、表结构操作
1.创建表
create table 表名( -- [] 表示非必要添加的代码
列名 类型 [约束] [COMMENT '备注1'],
列名 类型 [约束] [COMMENT '备注2'],
列名 类型 [约束] [COMMENT '备注3'],
...
列名 类型 [约束] [COMMENT '备注4'],
)
2.查询表
(1)查询表中所有数据
SELECT * FROM student;
(2)查询某个数据库中所有的表
show tables;
(3)查询表结构
desc student;
(4)查询表的创建SQL
show create table student;
3.修改表
(1)修改表名
alter table 表名 rename to 新表名;
(2)修改表的字符集
alter table student character set utf8;
(3)添加一列
alter table 表名 add column 列名 列类型 [约束];
alter table 表名 add (列名 列类型 [约束] , 列名 列类型 [约束] , ...... );
alter table 表名 add column 列名 列类型 [约束] [FIRST]/[AFTER 列名];
(4)修改列名和类型
alter table 表名 change 原列名 新列名 新数据类型;
(5)删除列
alter table 表名 drop 列名;
4.删除表
drop table 表名;
drop table if exists 表名;-- 若存在,则删除
三、表数据操作
1.新增数据
insert into 表名 [(字段名1,字段名2......)] values (字段值1,字段值2,......);
列名跟值需要一一对应。
如果表名后没有跟指定字段则必须将全部所有字段值给出。
2.修改数据
update 表名 set 字段名1=新字段值1, 字段名2=新字段值2 ... [ where 条件];
如果不加任何条件,则会将表中所有记录全部修改。
3.删除数据
delete from 表名 [where 条件];
如果不加条件,则删除表中所有记录。
当需要删除所有数据(表结构保留)的时候也可以使用TRUNCATE TABLE 表名
。
delete from 属于DML,所以操作可以回滚。而TRUNCATE TABLE 表名属于DDL,所以操作不能回滚。
从效率上讲TRUNCATE TABLE 表名高于delete from 。
四、查询数据
1.基础查询
(1)多个字段查询
select 字段名1,字段名2,... from 表名;
-- 查询表中所有字段可以使用*号代替字段
(2)去重
select distinct 字段列表 from 表名;-- 关键字 distinct
(3)列计算
select stu_age+1 from student;
如果有null参与计算,计算的结果都会为null,可以使用IFNULL函数解决,如下:
select IFNULL(stu_age,0)+1 from student;--若值为null,则当做0来计算。
(4)取别名
select 字段名1 as 别名 , 字段名2 as 别名2 ... from 表名;
- 如果别名字段有空格需要用引号包起来,如:
'姓名 '
- as 可以省略。
2.条件查询
(1)数值判断
select * from student where stu_age >18;
-- >大于, <小于,>=大于等于, <=小于等于, <> !=不等于
< >和!=都代表不等于的意思。
(2)区间判断
select * from student where stu_age between 18 and 28 ;-- 前后值都包括
(3)非空判断
select * from student where stu_sex is not null ;
select * from student where stu_sex is null ;
(4)包含判断
select * from student where stu_age is not null and stu_age not in (18,28,38);
- 使用not in的时候如果表中有null的值的话,则查询无结果,需要先把null值过滤掉,这是MySQL的bug。
in 、not in
后面的集合可以是具体值也可以是个子查询。
(5)逻辑运算
select * from student where stu_name = "张三" or stu_age = 18;-- and、or、not 相当于&&、||、!
(6)模糊查询
-- 查询姓名第2个字符是h的学生信息
select * from student where stu_name like '_h%';
-- _ 表示任意一个字符;% 表示任意N个字符
3.排序查询
select * from student group by 排序字段1 排序方式1,排序字段2 排序方式2...
-- 排序方式:ASC表示升序(默认);DESC表示降序
多字段排序时,前面字段值相同时才按照后面的排序字段跟方式进行排序。
4.聚合函数
select count(*) as total_num from student ;
-- 表示计算学生表中数据总条数,一般选择非空的列(比如主键),计算总条数一般用*号。
max、min 最大值、最小值。
sum、avg 总和、平均值。
- 使用了聚合函数后不能查询一般字段,如果使用了分组可以使用分组字段。
- where 后面不能跟聚合函数。
5.分组查询
分组后查询字段只能是分组字段跟聚合函数,非分组共有字段则无意义。
select avg(stu_age), stu_sex from student group by stu_sex having avg(stu_age) > 18 ;
-- 按照性别分组,分组后查询平均年龄>18的分组信息
where 跟 having 的区别:
- where 跟 having 都表示条件过滤, where 是先过滤在分组, having 是先分组在过滤。
- where 后面不能跟聚合函数, having 后可以跟聚合函数。
6.分页查询
-- 假设每页数据为 pageSize ,当前页数为 pageNum ,则取出当页数据的SQL公式为
select * from student limit (pageNum-1)*pageSize , pageSize;
只有MySQL可以通过 limit 分页,其它数据库不能使用该方式
五、数据类型
1.整型
类型 | 字节数 | 范围 |
---|---|---|
TINYINT(m) | 1byte | (-128,127) |
SMALLINT(m) | 2byte | (-32768,32767) |
MEDIUMINT(m) | 3byte | (-8388608,8388607) |
INT(m)或INTEGER(m) | 4byte | (-2147483648,2147483647) |
BIGINT(m) | 8byte | (-9223372036854775808,9223372036854775807) |
- 参数m表示的显示宽度,当数字超过则不受此限制。
- 加上 ZEROFILL 后缀表示显示长度不够的用0占位。
- 如果实际插入的值超过了类型的范围限制则报错,无法插入。
- 如果参数m不给出会有默认值,每种类型的默认值不同。
- 默认是有符号的,如果要定义无符号的需要加上后缀关键字 UNSIGNED 。
- ZEROFILL 后缀包括了无符号,与有符号后缀一起使用会冲突。
2.浮点
类型 | 字节数 | 名称 |
---|---|---|
float(m,d) | 4byte | 单精度浮点型 |
double(m,d) | 8byte | 双精度浮点型 |
- m 表示整个小数的长度,d 表示小数位的长数。
可以不给(m,d)值,直接使用float跟double,则默认会按照实际的精度(由实际的硬件跟操作系统决定)来显示。- (m,d)会采取四舍五入,因此会出现精度丢失的问题,是非标准用法,如果要用于数据库的迁移,则最好不要这么使用。
3.定点数
类型 |
---|
decimal(P,D) |
浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。
P 表示有效数字的精度,范围:[1,65],默认为10。
D 表示小数位的长度,范围:[0,30],默认为0。MySQL要求D<=P。
4.日期和时间
类型 | 字节数 | 说明 |
---|---|---|
DATE | 3byte | 表示日期,包含年月日 |
TIME | 3byte | 表示时间,包含时分秒 |
YEAR | 1byte | 表示年份 |
DATETIME | 8byte | 表示日期,包含年月日时分秒 |
TIMESTAMP | 4byte | 表示日期,包含年月日时分秒,自动更新 |
(1)插入当前时间
insert into t (col) values(CURRENT_TIMESTAMP);
insert into t (col) values(CURRENT_TIMESTAMP());
insert into t (col) values(SYSDATE());
insert into t (col) values(NOW());
(2)自定义插入指定时间
insert into t (col) values('2020-07-14 14:05:06');
insert into t (col) values(STR_TO_DATE('2019-08-07 14:05:06','%Y-%m-%d %H:%i:%s') );-- 自定义插入格式
-- %Y表示年,%m表示月,%d表示天,%H表示小时(24小时制,%h表示12小时制),%i表示分,%s表示秒
(3)查询日期时间字段为自定义格式
select DATE_FORMAT(col,'%Y-%m-%d %H:%i:%s ') from t ;
5.字符
类型 | 说明 |
---|---|
char(n) | 固定长度 最大255个字符 |
varchar(n) | 可变长度 最大65535个字符 |
tinytext | 可变长度 最大255个字符 |
text | 可变长度 最大65535个字符 |
mediumtext | 可变长度 最大2^24-1个字符 |
longtext | 可变长度 最大2^32-1个字符 |
BLOB | 二进制形式的长文本数据 |
MEDIUMBLOB | 二进制形式的中等长度文本数据 |
LONGBLOB | 二进制形式的极大文本数据 |
- char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,定长,浪费空间,速度快。
- varchar(n) 是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),所以 varchar(4) 存入3个字符将占用4个字节。不定长,节省空间。
- text 不用指定长度n, text 是实际字符数+2个字节。text类型不能有默认值。
- BLOB 都是存储二进制数据,存储的数据只能整体读出。
6.枚举
类型 |
---|
enum(可能出现的元素列表) |
-- 定义sex列,类型为枚举,值只能是列表中的某一个值,可以为null
create table t(sex enum('男','女') );
-- 插入数据,值如果非null则必须为枚举列表中的一个
insert into t(sex) values ("男");
-- 插入数据,枚举值也可以根据索引来引用,从1开始。
insert into t(sex) values (2);