SQL分类

SQL

SQL语句通用语法

1.SQL语句可单行也可多行书写，以分号结尾。

2.SQL关键词应该大写，统一规范

SQL分类

DDL

数据库操作

查询所有数据库

mysql> show databases;

创建数据库

mysql> create database [if not exists] test_db [default charset utf8mb4];

注意：UTF8字符集长度为3字节，有些符号占4字节，如Emoji表情符号，所以推荐用utf8mb4字符集

使用数据库

mysql> use test_db;

查询当前使用的数据库

mysql> select database();

删除数据库

mysql> drop database [if exists] test_db;

表操作

创建表

mysql> create table [if not exists] t_user(
    id int [comment '编号'],
    name varchar(20) [comment '姓名'],
    age int comment ['年龄'],
    gender varchar(1) comment ['性别']
)comment '用户表';

int 类型默认11位；

查询所有的表

mysql> show tables;

查询表的结构

mysql> desc t_user;

查询表的创建语句

mysql> show create table t_user;

重命名表

MySQL> alter table t_user rename to user;

删除表

-- 第一种方法
mysql> drop table [if exists] employee; # 删除整张表，从内存清除

-- 第二种方法
mysql> truncate table employee; # 删除表，并重新创建，自增值重置，索引恢复初始大小

-- 第三种方式
mysql> delete from table; # 删除所有表记录，并生成日志以便回滚

处理效率：drop > trustcate > delete

字段操作

添加字段

mysql> alter table t_user add address varchar(100) comment '住址';

删除字段

mysql> alter table t_user drop address;

修改字段类型

mysql> alter table t_user modify address varchar(200) comment '住址';

修改字段名和字段类型

mysql> alter table t_user change address family_address varchar(234) comment '家庭住址';

DDL数据类型

数值类型

字符串类型

varchar需要指明长度，即varchar(length)，否则报错，而char不指定长度为1，即char(1)；
varchar会根据存储的内容决定需要多大的存储空间，而char的长度一经指定不在改变，总是申请固定长度的空间

日期类型

示例

mysql> create table employee(
    id int comment '编号',
    empno varchar(10) comment '员工工号',
    name varchar(10) comment '员工姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    identifyno char(18) comment '身份证号',
    employdate date comment '入职时间'
)comment '员工信息表';

从我们的设计可见，对于长度固定的字符串，我们使用char，对于绝对非负的数据就用unsigned修饰

DDL语句总结

相比DML,DDL语句通常需要加上table/database关键词，毕竟DDL操作的对象有数据库、表、和字段，而且增删改等使用的关键词可能相同，因而要指明操作的是什么对象，即是数据库还是表，或者是哪个表的哪个字段例如：

-- DDL
drop database test1;
drop table t_user;
truncate table t_user; 
alter table t_user ADD constraint foreign key(user_id) references t_score(user_id);

-- DML
select * from t_user;
insert into t_user(id, username, password) values(1, "flameking", "123456");
update t_user set name="Casflawed",password="654321" where id=1;

DML

DML全称Data Maniputation language（数据操作语言），用来对数据库表记录进行增删改操作，即insert，delete，update

insert添加数据

给指定字段添加数据

mysql> insert into user(`id`, `name`, `age`) values(1, 'flameking', 21);

给全部字段添加数据

mysql> insert into user values(1, 'flameking', 21, '男', '河南', '1');

批量添加数据

mysql> insert into user(`id`, `name`, `age`) values(1, 'flameking', 21),(2, 'Casflawed',22),(3,'oneway',23);
mysql> insert into user values(1, 'flameking', 21, '男', '河南', '1'),(5,'casflawed',28,'女','湖南','0');

注意：

• 字符串和日期型数据应该包含在引号中
• 插入数据顺序必须和指定的数据一一对应
• 插入的数据大小应该在字段范围内

update更新记录

形如：update 表名 set 字段名1=值1, 字段名2=值2,.....[where 条件]
mysql> update user set name="Casflawed" where id=1;

-- 如果没有条件则会修改整张表的数据
mysql> update user set family_address="湖南";

delete删除记录

形如：delete from 表名 [where 条件]
mysql> delete from user where id = 1;

-- 如果不带条件则会删除整张表的记录
delete from user;

DQL

单表查询

基本查询

操作的表结构和记录：

1.查询指定字段

select name, workno, age from emp;

2.查询所有字段

select * from emp;

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

-- 别名的意义是为了更好的理解字段，因此这里使用中文别名方便用户更容易理解
select workaddress as 工作地址 from emp;

4.查询不重复的员工地址

-- 关键词 distinct
select distinct workaddress from emp;

条件查询

可能的条件：

注意：
1.MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会得到第三种值 UNKNOWN。
这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。
2.is关键词不能随便用，在null中可以用，但在in中不可以用，注意语法正确

1.查询年龄等于88的员工

select * from emp where age = 88;

2.查询年龄小于20的员工信息

select * from emp where age < 20;

3.查询年龄小于等于20的员工信息

select * from emp where age <= 20;

4.查询没有身份证号的员工信息

select * from emp where idcard is null;

5.查询有身份证号的员工信息

select * from emp where idcard is not null;

6.查询年龄不等于88的员工信息

select * from emp where age != 88;
select * from emp where age <> 88;

7.查询年龄在15岁（包含）到20岁（包含）之间的员工信息

select * from emp where age between 15 and 20;
select * from emp where 15 <= age and age <= 20;
select * from emp where 15 <= age && age <= 20;

-- 如果between后跟大值会怎么样
select * from emp where age between 20 and 15; # 语句部分报错，但返回空的结果集（Empty set）

8.查询性别为女且年龄小于25岁的员工信息

select * from emp where gender = '女' && age < 25;

9.查询年龄等于18或20或40的员工信息

select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in(18,20,40);

in可以用来简写有多个or语句的条件

10.查询姓名为两个字的员工信息

select * from emp where name like '__';

11.查询身份证信息最后一位是X的员工信息

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X'; #有17个下划线

聚合函数

聚合函数简介：

1.统计该企业员工数量

select count(*) from emp;
-- 或者是任意字段
select count(id) from emp;

2.统计该企业员工的平均年龄

select avg(age) from emp;

3.统计该企业员工的最大年龄

select max(age) from emp;

4.统计该企业员工的最小年龄

select min(age) from emp;

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

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

分组查询

形如：select * from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件]

where条件和having条件的区别：
1.执行时机不同，where条件在分组前执行，不符合的记录不参与分组，having条件对分组后的结果集执行
2.判断条件不同，where条件不能对聚合函数进行判断，having可以

练习：
1.根据性别分组，统计男性员工和女性员工的数量

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

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

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

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

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

注意：
1.执行顺序where条件>聚合函数>having条件
2.分组之后一般select分组字段和聚合函数，查询其他字段没有意义

排序查询

-- 由此可见：SQL支持多字段排序，排序规则是，如果字段1相同就按字段2排序
形如：select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

-- 排序方式
1.ASC，升序（默认）
2.DESC，降序

练习：
1.根据年龄对公司的员工进行升序排序

select * from emp order by age asc;

2.更具入职时间，对员工进行降序排序

select * from emp order by entrydate desc;

3.根据年龄对公司的员工进行升序排序，年龄相同再按入职时间进行降序排序

select * from emp order by age, entrydate desc;

分页查询

练习：
1.查询第1页员工数据，每页展示10条数据

-- 如果起始索引是0，可以省略
select * from emp limit 10;

2.查询第2页员工数据，每页展示10条记录

select * from emp limit 1, 10;

单表查询案例练习

1.查询年龄为20,21,22,23岁的员工信息

select * from emp where age in(20,21,22,23);

2.查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工

select * from emp where gender='男' and (age between 20 and 40) and name like '___';

3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数

select gender,count(*) from emp where age < 60 group by gender;

4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排列，如果年龄相同按入职时间降序排序

select name, age from emp where age <= 35 order by age, entrydate desc;

5.查询性别为男，且年龄在20-40岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序

select * from emp where gender='男' and (age between 20 and 40) order by age,entrydate desc limit 5;

补充知识：

1.WHERE子句

where作为筛选语句会根据表记录从头到尾进行比较
where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
在MySQL中，false也可以用0表示，true也可以用1表示

2.连接查询

即将两个表的字段连接起来

3.存储过程(预编译的SQL集合)

优点：因为是预编译过的，因此执行速度会更快 难以调试和扩展

DQL语句执行顺序

注意：group by和having都可以使用select中的别名

DCL

用来管理数据库用户，控制数据库的访问权限（简单来说就是哪些数据库能被那些用户访问）

DCL-管理用户

查询用户

use mysql;  # 用户信息表放在了系统数据库mysql中
select * from user;

Host和User两个字段共同定义一个用户，表示当前用户能在哪台主机上访问数据库服务器，localhost代表用户只能从本地访问数据库，@%代表用户可以从任意远程计算机访问数据库

创建用户

1.创建只能本都访问数据库的用户

create user 'flameking'@'localhost' identified by '123456';

2.创建能在任意主机访问数据库服务的用户

create user if not exists 'casflawed'@'%' identified by '123456';
-- 如果不加@'%'，默认是允许访问任意主机上的数据库服务器
create user if not exists 'casflawed' identified by '123456';

用通配符%代表任意主机，同时上面两种方式创建的用户没有分配任何权限；

修改用户密码

alter user 'flameking'@'localhost' identified with mysql_native_password by '123';

其中mysql_native_password代表MySQL的密码加密方式

删除用户

drop user 'flameking'@'localhost';

DCL-权限控制

前面我们创建用户的时候，发现刚被创建出来的用户可以登录但做不了任何事情，这是因为我们还没分配任何权限

查询用户的权限

show grants for 'casflawed'@'%';

授予用户权限

形如：grant 权限列表 on 数据库名.表名 to 用户;
grant all on *.* to 'casflawed'@'%';    # 经过这段sql授权后，casflawed相当于root，即超级管理员

撤销用户权限

形如：revoke 权限列表 on 数据库名.表名 from 用户;
revoke drop,update on *.* from 'casflawed'@'%';

注意：多个权限之间用‘，’分隔