MySQL: 2、SQL语言 ①概念、分类
一、SQL的简介：
1.SQL的概念：
SQL就是结构化查询语言，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统
2.SQL的作用：
- 是所有关系型数据库的统一查询规范，不同的关系型数据库都支SQL
- 不同数据库之间的SQL也有一些区别，这个称为 方言
3.SQL的注释方式：
-- 空格 单行注释
/* */ 多行注释
# MySql特有的单行注释
二、SQL的分类
分类 说明
三、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
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.比较运算符：
2.逻辑运算符
3.模糊查询、通配符
# 查询含有'精'字的所有员工信息
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;
