MySQL: 2、SQL语言 ①概念、分类
一、SQL的简介:
1.SQL的概念:
SQL就是结构化查询语言,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统
2.SQL的作用:
- 是所有关系型数据库的统一查询规范,不同的关系型数据库都支SQL
- 不同数据库之间的SQL也有一些区别,这个称为 方言
3.SQL的注释方式:
-- 空格 单行注释
/* */ 多行注释
# MySql特有的单行注释
二、SQL的分类
分类 说明
![](https://img2020.cnblogs.com/blog/1424761/202009/1424761-20200924095735507-497728930.png)
三、DDL数据定义语言:操作数据库、表
DDL操作数据库
1、创建数据库
create database 数据库名; -- 创建指定名称的数据库
create database 数据库名 character set 字符集; -- 创建指定名称的数据库并指定字符集
ps: 如果没设置字符集,那么数据库默认使用的是Latin1字符集,也就是ISO-8859-1。
该数据库下的表也会使用该字符集
2、查看、选择数据库
use 数据库; -- 切换数据库
select database(); -- 查看当前正在使用的数据库
show databases; -- 查看MySQL中都有哪些数据库
show create database 数据库名; -- 查看一个数据库的定义信息
datetime 日期类型,显示为 yyyy-MM-dd HH:mm:ss
![](https://img2020.cnblogs.com/blog/1424761/202009/1424761-20200924113327217-1531728993.png)
2、创建表
create table 表名(
字段名1 字段类型(长度),
字段名2 字段类型 -- 注意最后一列不需要加逗号
)ENGINE=InnoDB DEFAULT CHARSET = utf8;
3、查看表
show tables; -- 查看当前数据库中所有的表名
desc 表名; -- 查看数据库表的结构
show create table 表名; -- 查看表的字符集
4、删除表
drop table 表名; -- 从数据库中删除表
drop table if exists 表名;-- 判断表是否存在,存在就删除,不存在就不删除
5、修改表
rename table 旧表名 to 新表名; -- 修改表名
alter table 表名 character set 字符集; -- 修改表的字符集
alter table 表名 add 字段名 字段类型; -- 向表中添加字段
alter table 表名 modify 字段名 字段类型; -- 修改表中字段的数据类型或者长度
alter table 表名 change 旧字段名 新字段名 类型(长度); -- 修改字段名
alter table 表名 drop 字段名; -- 删除字段名
四、DML数据操作语言:操作表中数据
1、往表中插入数据
方式1:插入全部字段,并将所有字段名都写出来
insert into 表名 (字段名1,字段名2...) values(字段值1,字段值2...);
方式2:插入全部字段,但是不写字段名
insert into 表名 values(字段值1,字段值2...);
方式3:指定插入字段的值
insert into 表名 (字段名1) values(字段值1);
ps: 1. varchar char date 类型的值必须使用单引或者双引号
2. 如果插入空值,可以忽略不写或者插入null
2、更改表中数据的值
方式1: 不带条件的修改,也就是修改全部字段的数据
update 表名 set 列名 = 值;
方式2:带条件的修改
update 表名 set 列名 = 值 where 条件表达式:字段名 = 值;
update 表名 set 列名 = 值,列名 = 值 where 条件表达式:字段名 = 值;
3、删除表中的数据
方式1: 删除表中所有数据
delete from 表名;
truncate table 表名;
ps:着两种都是直接删除表中所有的数据,但这两种又有些区别
delete from 表名; --》 有多少条记录就执行多少次删除操作,效率低
truncate table 表名; --》 先删除整张表,然后再重新创建一张一模一样的表,效率高
方式2: 根据条件删除数据
delete from 表名 where 字段名 = 值;
五、DQL数据查询语言:查询表中的数据
1、简单查询
1. 查询表中多少有数据:
select * from 表名;
2. 查询表中的所有记录,但仅显示特定的字段
select 字段1,字段2 from 表名;
3. 查询表中的数据,并将列名用其他别名显示
select
eid AS '编号',
ename AS '姓名',
sex AS '性别',
salary AS '薪资',
hire_date '入职时间', -- AS 可以省略
dept_name '部门名称'
FROM emp;
4. 查询表中的数据,并去掉重复的数据
-- 使用distinct 关键字,去掉重复部门信息
SELECT DISTINCT dept_name FROM emp;
5. 查询表中的数据,并将查询结果参与运算
SELECT ename , salary + 1000 FROM emp;
2、条件查询
如果查询语句中没有设置条件,就会查询所有的行信息,在实际应用中,
一定要指定查询条件,对记录进行过滤
select 列名 from 表名 where 条件表达式;
* 先取出表中的每条数据,满足条件的数据就返回,不满足的就过滤掉
1.比较运算符:
![](https://img2020.cnblogs.com/blog/1424761/202009/1424761-20200925150549569-880967886.png)
2.逻辑运算符
![](https://img2020.cnblogs.com/blog/1424761/202009/1424761-20200925150608920-1694366880.png)
3.模糊查询、通配符
![](https://img2020.cnblogs.com/blog/1424761/202009/1424761-20200925150746200-1990782403.png)
# 查询含有'精'字的所有员工信息
SELECT * FROM emp WHERE ename LIKE '%精%';
# 查询以'孙'开头的所有员工信息
SELECT * FROM emp WHERE ename LIKE '孙%';
# 查询第二个字为'兔'的所有员工信息
SELECT * FROM emp WHERE ename LIKE '_兔%';
# 查询没有部门的员工信息
SELECT * FROM emp WHERE dept_name IS NULL;
-- SELECT * FROM emp WHERE dept_name = NULL; 错误方式
# 查询有部门的员工信息
SELECT * FROM emp WHERE dept_name IS NOT NULL;
3、排序
- 通过 ORDER BY 字据可以将查询出的结果进行排序(排序只显示效果,不会影响真实数据)
- 语法结构:
select 字段名 from 表名 [ where 字段 = 值 ] order by 字段名 [ asc / desc] ;
asc 表示升序(默认) desc 表示降序
- 单列排序: 只按照某一个字段进行排序,就是单列排序
- 案例: 通过salary字段对emp表的数据进行排序
-- 默认升序排序 asc
select * from emp order by salay;
-- 降序排序
select * from emp order by salay desc;
- 组合排序: 同时对多个字段进行排序,如果第一个字段相同就按照第二个字段进行排序,以此类推
- 案例:再salary字段的基础上再按照id进行排序,如果salary字段相同就以id做降序排序
-- 组合排序
select * from emp order by salary desc, eid desc;
4、聚合函数
- 聚合函数就是纵向查询,它对某一列的值进行计算,然后返回一个单一的值,另外聚合函数回忽略null空值
- 语法结构:
select 聚合函数(字段名) from 表名;
- 常用的聚合函数:
- 例子:
#1 查询员工的总数
-- 统计表中的记录条数 使用 count()
select count(eid) from emp; -- 使用某个字段
select count(*) from emp; -- 使用 *
select count(1) from emp; -- 使用1 与 * 的效果相同
-- 下面这条sql 得到的总条数不准确,因为count函数会忽略空值
所以再做统计时尽量不要使用带null的列进行统计
select count(name) from emp;
#2 查看员工总薪水、最高最小薪水、薪水平均值
select
sum(salary) as '总薪水',
max(salary) as '最高薪水',
min(salary) as '最低薪水',
avg(salary) as '平均薪水'
from emp;
#3 查询薪水大于4000的员工个数
select count(eid) from emp where salary > 4000;
#4 查询部门为 '教学部' 的所有员工的个数
select count(*) from emp where dept_name = '教学部';
#5 查询部门为 '市场部' 所有员工的平均薪资
select avg(salary) as '市场部平均薪资' from emp where dept_name = '市场部';
5、分组查询
- 分组查询是指使用 group by 语句对查询的信息进行分组,相同数据作为一组
- 语法格式:
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];
- 案例:
#1. 查询有几个部门
select dept_name as '部门名称' from emp group by dept_name;
#2. 查询每个部门的平均薪资
select dept_name as '部门名称', avg(salary) as '平均薪资' from emp group by dept_name;
#3. 查询每个部门的拼接薪资,部门名称不能为null
select
dept_name as '部门名称',
avg(salary) as '平均薪资'
from emp wherer dept_name is not null group by dept_name;
#4. 查询平均薪资大于6000的部门,需要再分组后在次进行过来,使用having
select
dept_name as '部门名称',
avg(salary) as '平均薪资'
from emp where dept_nameis not null droup by dept_name having avg(salary) > 6000 ;
# where 与 having的区别:
where 进行分组前过滤且where 后面不能写聚合函数
having 进行分组后过滤且 having后面可以写聚合函数
6、limit分页查询
- limit是限制的意思,用于限制返回的查询结果的行数,limit语法是mysql的方言,主要用来完成分页操作
- 语法结构:
select 字段1,字段2.... from 表名 limit offset , length;
- 参数说明:
limit offset, length; 关键字可以接受一个或者两个为0或者正整数的参数
offset 起始行数,从0开始计数,如果省略默认为0
length 返回的行数
- 案例:
-- 查询emp表中前5条数据
select * from emp limit 5;
select * from emp limit 0, 5;
-- 查询emp表中 从第4条开始的数据,查询6条
select * from emp limit 3,6;
-- 分页操作 每页显示3条数据
select * from emp limit 0,3; -- 第一页
select * from emp limit 3,3; -- 第二页
select * from emp limit 6,3; -- 第三页
-- 分页公式: 起始索引= (当前页 - 1) * 每页条数
六、DCL数据控制语言:用来定义数据库的访问权限和安全级别,及创建用户
MySql默认使用的都是 root 用户,超级管理员,拥有全部的权限。除了root用户以外,
我们还可以通 过DCL语言来定义一些权限较小的用户, 分配不同的权限来管理和维护数据库
1、创建用户
格式: CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
主机名:指定该用户在哪个主机上可以登陆,本地用户可用 localhost 如果想让该
用户可以从任意远程主机登陆,可以使用通配符 %
案例:
1)创建 admin1 用户,只能在 localhost 这个服务器登录 mysql 服务器,密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
2) 创建 admin2 用户可以在任何电脑上登录 mysql 服务器,密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';
ps: % 表示 用户可以在任意电脑登录 mysql服务器.
2、用户授权
格式:GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
案例:
1) 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限:查询
GRANT SELECT ON db4.products TO 'admin1'@'localhost';
2) 给 admin2 用户分配所有权限,对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';
3、查看权限
格式:
SHOW GRANTS FOR '用户名'@'主机名';
案例:
SHOW GRANTS FOR 'root'@'localhost';
4、删除用户:DROP USER '用户名'@'主机名';
-- 删除 admin1 用户
DROP USER 'admin1'@'localhost';
5、查询用户
选择名为 mysql的数据库, 直接查询 user表即可
SELECT * FROM USER;