2.1 SQL分类
SQL语言共分为四类,分别为:
- DDL(Data Definition Language):
数据定义语言,用来定义数据库对象(数据库,表,字段) - DML(Data Manipulation Language):
数据操作语言,用来对数据库表中的数据进行增、删、改 - DQL(Data Query Language):
数据查询语言,用来查询数据库中表的记录 - DCL(Data Control Language):
数据控制语言,用来创建数据库用户、控制数据库的访问权限
2.2 DDL-数据定义语言
数据库操作
- 查询数据库
查询所有数据库:show databases;
查询当前所在数据库:select database();
- 创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
- 删除数据库
drop database [if exists] 数据库名;
- 使用数据库
use 数据库名;
表操作
-
查询表:
查询当前数据库所有表:show tables;
查询表结构:desc 表名;
查询指定表的建表语句:show create table 表名;
-
创建表:
create table 表名 (字段名 字段类型 字段约束,...);
数据库的字段类型分类:
各个类型字段设置长度的注意点整理:
-
整数类型:
对于整数类型,在设置长度后,并不是说只能存储指定长度的值,如:TINYINT(1) 不能存储数字10,这是错误的!
若设置了长度,且在后面为字段添加了约束条件zerofill
,如 int(3) 则表示 最小显示的长度,不足的用0补足,插入1234显示1234,插入12,则显示012。若不添加约束条件 zerofill ,给整数类型设置长度将毫无意义总结:指定的数值是最小显示长度,不是存储长度,整型类型的默认存储长度为11个字符,满足绝大多数环境下的使用,因此int后面不需要指定数值,即使指定了也没有意义。
-
浮点类型和定点类型:
用于表示小数,浮点类型为 float、double,定点类型为 decimal。
MySQL 浮点型和定点型可以用类型名称后加(M,D)来表示,即(精度(该值的总共长度),标度(小数点后面的长度))
,如 float(7,4) 可显示为-999.9999,MySQL保存值时进行四舍五入,如果插入999.00009,则结果为999.0001。整数部分如果大于(M-D)位,则直接报错。
对于DECIMAL
,M 是最大位数(精度),范围是1~65,可不指定,默认值是10。D 是小数点右边的位数(小数位),范围是0~30,并且不能大于M,可不指定,默认值是0。
FLOAT
和DOUBLE
在不指定精度时,默认会按照实际的精度来显示,而 DECIMAL 在不指定精度时,默认精度为10,标度为0,即(10, 0)。总结:decimal 的精度比 float 和 double 高,所以对于一些有高精度要求的数据,建议用 decimal
-
字符串类型
CHAR
存储 定长数据 很方便,CHAR字段上的索引效率级高,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间,不足的自动用空格填充。
VARCHAR
存储 变长数据,但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。这多出的一个字节用于保存实际使用了多大的长度。总结:对于字符串类型的长度设置,长度代表的是字符,不是字节,所以 VARCHAR(255)可以存放255个汉字。从空间上考虑,用varchar合适;从效率上考虑,用char合适。若存放的字符数大于255个时,可以用字段
text
来存取,text 没有长度设置。 -
日期类型
日期没有长度设置
-
修改表:
添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度)
修改字段名和数据类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;
-
删除表
删除表drop table [if exists] 表名;
删除指定表,并重新创建该表truncate table 表名;
在删除表时,表中全部数据也会被删除
2.3 DML-数据操作语言
- 添加数据
给指定字段添加数据
insert into 表名(字段名1,字段名2,...) values(值1,值2,...);
给全部字段添加数据
insert into 表名 values(值1,值2,...);
批量添加数据
insert into 表名(字段名1,字段名2,...) values(值1,值2,...),(值1,值2,...);
insert into 表名 values(值1,值2,...),(值1,值2,...);
- 修改数据
update 表名 set 字段名1 = 值1,字段名2 = 值2,...[where 条件];
- 删除数据
delete from 表名 [where 条件]
delete 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据
delete 语句不能删除某一个字段的值(可以使用UPDATE)
2.4 DQL-数据查询语言
- 基本查询
- 查询多个字段
select 字段1,字段2,... from 表名;
select * from 表名;
- 设置别名
select 字段1 [AS 别名1],字段2 [AS 别名2]... from 表名;
AS
可以省略 - 去除重复记录
select distinct 字段列表 from 表名;
- 条件查询
select 字段列表 from 表名 where 条件列表;
- 聚合函数
- 介绍
将一列数据作为一个整体,进行纵向计算 - 常见聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
- 语法
select 聚合函数(字段列表) from 表名;
注意:null 值不参与所有聚合函数运算
- 分组查询
- 语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
- where 与 having 区别
执行时机不同:where 是分组之前进行过滤,不满足 where 条件,不参与分组;而 having 是分组之后对结果进行过滤
判断条件不同:where 不能对聚合函数进行判断,而 having 可以 - 例如:
注意:
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
- 排序查询
- 语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
- 排序方式
ASC:升序(默认值)
DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
- 分页查询
- 语法:
select 字段列表 from 表名 limit 起始索引,查询记录数;
注意:
- 起始索引从 0 开始,起始索引 = (查询页码 - 1) * 每页显示记录数
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
如果查询的是第一条数据,起始索引可以省略
- 编写顺序和执行顺序
2.5 DCL-数据控制语言
- 管理用户
- 查询用户
use mysql;
select * from user;
- 创建用户
create user '用户名'@'主机名' identifled by '密码';
'用户名'@'%'
表示可以任意主机访问该数据库 - 修改用户密码
alter user '用户名'@'主机名' identifled with mysql_native_password by '新密码';
- 删除用户
drop user '用户名'@'主机名';
- 权限控制
- 查询权限
show grants for '用户名'@'主机名';
- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
注意:
- 多个权限之间,使用逗号隔开
- 授权时,数据库名和表名可以使用 * 进行通配,代表所有