小汤学编程之MySQL（二）——数据库操作、表结构操作、表数据操作、查询数据和数据类型

一、数据库操作

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);