SQL超详细解析

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

字符串：

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

6.日期时间类型

MySQL数据类型	大小	含义
date	3	日期 '2008-12-2' 日期值
YEAR	1	1901 年份值
time	3	时间 '12:25:36' 时间或持续时间
datetime	8	日期时间 '2008-12-2 22:06:44' 混合日期和时间值
timestamp（不常用）	4	自动存储记录修改时间混合日期和时间值时间戳

7、DDL-表操作-修改

往表结构添加字段

ALTER TABLE 表名 ADD 字段名类型（长度） [COMMENT 注释] [约束]；

例：

为emp表增加一个新的字段“昵称”为niname

alter table emp add nickname varchar(20) comment "昵称";

修改字段

修改制定字段数据类型

ALTER TABLE 表名 MODIFY 字段名新数据类型(长度)

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名新字段名类型(长度) [COMMENT 注释] [约束]；

alter table emp change niname username varchar(30) comment "用户名";

删除字段

ALTER TABLE 表名 DROP 字段名;

alter table emp drop username;

修改表名

ALTER TABLE 表名 RENAME TO 新表名

alter table emp rename to emploee

8、DDL-表操作-删除

只删除表

DROP TABLE [IF EXISTS] 表名；

删除表后并重新创建该表
TRUNCATE TABLE 表名；

使用这个指令的目的：可以清空表中的数据而保留表的结构。

·DML-数据库操作语言

用来实现对数据库中数据记录进行增删改操作的。

DML-添加数据

给指定字段添加数据

INSERT INTO 表名 (字段名1，字段名2...) VALUES (值1，值2，...)

给全部字段添加数据

INSRET INTO 表名 VALUES(值1，值2...)

批量添加数据

INSERT INTO 表名 (字段名1，字段名2...) VALUES (值1，值2，...)(值1，值2，...)(值1，值2，...)

INSERT INTO 表名 VALUES (值1，值2，...)(值1，值2，...)(值1，值2，...)

例：

指定字段：insert into book(book_id,book_num,book_name,book_price,book_author) values(7,100,'桃花源记',24,'无名氏');

全部字段：insert into book values(8,'易筋经',10000,'少林寺',10);

DML-修改数据

UPDATE 表名 SET 字段名1 = 值1，字段名2 = 值2，...[WHERE 条件]；

update book set book_name = '东方不败',book_num = 9 where id = 1;

DML-删除数据

DELETE FORM 表名 [WHERE 条件]

注意：

·where条件可以有也可以没有，没有就是删除整张表的所有数据。

·DELETE语句不能删除某一个字段的值（可以使用UPDATE让该字段为null）

·DQL

语法结构：

SELECT 
  字段列表
FROM
  表名列表
WHERE
  条件列表
GROUP BY
  分组字段列表
HAVING
  分组后条件列表
ORDER BY
  排序字段列表
LIMIT
  分页参数

基本查询

1、查询指定字段 name , woekno , age 返回

elsect name,workno,age form emp;

2、查询所有字段返回

elsect id, workno , name, gender ,age, idcard,workaddress,entrydate from emp;

elsect * from emp; #最好少用，因为不直观且效率低,开发文档不会让使用。

3、查询所有员工的工作地址，起别名 as

select workaddress as '工作地址' from emp; # as可以省略

4、查询公司员工的上班地址（不要重复，即去重操作distinct）

select distinct workaddress '工作地址' from emp;

条件查询（WHERE）

1、语法

SELECT 字段列表 FROM 表名 WHERE 条件列表；

2、条件

可以是比较运算符或者逻辑运算符

3、例子

判断年龄等于88的

select * from emp where age = 88;

判断idcard的值为null的

select * from emp where idcard is null;

select * from emp where idcard is not null;

多个条件 && 或 and 或 or

select * from emp where age >= 15 and gender = '女';

LIKE模糊匹配，_匹配单个字符，%匹配多个字符

查询姓名为两个字的员工

select * from emp where name like ''; # 这里打了两个下划线_ _ ，不是很明显_

查询身份证最后一位为x

select * from emp where idcard like '%x'；

聚合函数（count,max,min,avg,sum）

1、介绍

将一列数据作为一个整体，进行纵向计算。

2、常用的聚合函数

# null是不计入计算的,

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

3、语法

select 聚合函数(字段列表) from 表名；

4、例子

count : select count(*) from emp; #这是求有多少个数据

avg: select avg(age) from emp;

统计西安地区员工的年龄之和

select sum(age) from emp where woekaddress = '西安'

分组查询（GROUP BY）

1、语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组时候的过滤条件]

2、where和having的区别

·执行的时间：where是在分组前进行源数据的过滤，不满足条件不能参与分组，having是分组后对结果进行过滤

·判断条件不同：where不能对聚合函数进行判断，而having可以

3、例子

根据性别分组，统计男性员工和女性员工数量

select gender ,count(*) from emp group by gender;

根据性别分组，统计男性员工和女性员工平均年龄

select gender ,avg(age) from emp group by gender;

查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select workaddress ,count(address) from emp where age<45 group by workaddress having count(*) >= 3;

排序查询（ORDER BY）

1、语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2；

#支持多字段排序，在排序过程中，如果字段1相同，则根据字段2排序。

2、排序方式

ASC：升序（默认值）

DESC：降序

3、例子

根据年龄进行升序排序

select * from emp order by age asc; #asc可以省略

根据年龄进行升序排序，年龄相同则按照入职时间进行降序排序

elect * from emp order by age asc ，entrydate desc ;

分页查询（LIMIT）

1、语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；

#注意

·起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数。

·分页查询是数据库的方言，方言指每个数据库中不一样的地方，mysql中分页使用limit。

·如果查询第一页可以省略起始索引，直接简写为limit 10。

2、例子

查询第二页员工数据，每页显示10条记录

select * from emp limit 1,10;

DQL的执行顺序

编写顺序                        执行顺序
SELECT        
  字段列表                        5
FROM
  表名列表                        1                       
WHERE
  条件列表                        2
GROUP BY
  分组字段列表                     3
HAVING
  分组后条件列表                   4
ORDER BY
  排序字段列表                     6
LIMIT
  分页参数                         7

·DCL-数据控制语言

用来管理数据库用户、控制数据库的访问权限

DCL-管理用户

1、查询用户

USE mysql;

SELECT * from

2、创建用户

CREATE USER '用户名'@'主机名' indentified by '密码';

例子：

创建用户haha，只能在localhost 访问，密码是 '123456'；

create user 'haha'@'localhost' indentified by '123456';

创建用户keke，可以在任意主机访问，密码是 '123456'；

create user 'keke'@'%' indentified by '123456';

3、修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

alter user 'haha'@'localhost' identified with mysql_native_password by '1234';

4、删除用户

drop user'用户名'@'主机名'；

DCL-权限控制

常用权限

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CRATE	创建数据库/表