MySQL基础
单表(DQL)
- concat拼接
SELECT CONCAT(last_name," ",first_name) from employees
ex:
显示出表 employees 中的全部 job_id(不能重复)
SELECT DISTINCT job_id from employees
- IFNULL(expr1,expr2)
- 逻辑表达式(运算符)
and(&&):两个条件如果同时成立,结果为true,否则为false;
or(||):两个条件只要有一个成立,结果为true,否则为false;
not(!):如果条件成立,则not后为false,否则为true;
SELECT last_name,salary
from employees
where salary BETWEEN 10000 and 20000
SELECT last_name,salary
from employees
where salary >= 10000 and salary <= 20000
- in
SELECT employee_id, last_name, salary, manager_id FROM employees
WHERE manager_id IN (100, 101, 201);
- like
注:
‘_’代表一个字符
select last_name
from employees
where last_name like '_a%'
- is null (is not null)
- 排序
查询部门编号大于90的员工信息,按入职时间先后进行排序(按查询条件排序
)
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC
按年薪的高低显示员工的信息和年薪(按表达式排序
)
SELECT
*, 12 * salary * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY
12 * salary * (1 + IFNULL(commission_pct, 0)) DESC
按年薪的高低显示员工的信息和年薪(按别名排序
)
SELECT
*, 12 * salary * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
employees
ORDER BY
年薪 DESC
按姓名的长度显示员工的姓名和工资(按别名排序
)
SELECT
last_name,
salary
FROM
employees
ORDER BY
LENGTH(last_name) DESC
查询员工信息,要求先按工资排序,再按员工编号排序(按多个字段排序
)
SELECT
*
FROM
employees
ORDER BY
salary DESC,
employee_id ASC
- 日期函数
包含日期和时间
SELECT NOW();
包含日期不包含时间
SELECT CURDATE();
包含时间不包含日期
SELECT CURTIME();
str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('09-30-1994','%m-%d-%Y')
date_format:将日期转换成字符
SELECT DATE_FORMAT('1994-01-25','%Y年%m月%d日')
8.分组查询
查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
- 查询领导编号>102的每个领导手下最低工资
注:
领导编号>102这个条件可以在原始表中查询到,所以用where
SELECT
min(salary),
manager_id
FROM
employees
WHERE manager_id > 102
GROUP BY
manager_id
- 添加删选条件:最低工资>5000
注:
最低工资>5000这个条件可以在分组后的表中查到,所以用having
SELECT
min(salary),
manager_id
FROM
employees
WHERE manager_id > 102
GROUP BY
manager_id
HAVING
min(salary) > 5000
多表(DQL)
内连接
查询结果为几张表的交集
- 等值连接
#案例2.查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
#3. 查询部门个数>3的城市名和部门个数,(添加分组+筛选)
#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)
SELECT COUNT(*) 个数,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
- 非等值连接
#查询工资级别的个数>20的个数,并且按工资级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
- 自连接
#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
外连接
用于查询一张表中有,另一张表中没有的记录
外连接的查询结果为主表中的所有记录
,
如果从表中有与主表中记录相匹配的数据,则显示匹配的数据;如果从表中没有与主表中相匹配的数据,则显示null。
左外连接:left 左边为主表
右外连接:right右边为主表
全外连接:两表所有数据都显示,没有匹配数据用null填充
查询哪个部门没有员工
SELECT
d.department_name
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE
e.employee_id IS NULL
子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★
标量子查询(单行) √
列子查询 (多行) √
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
标量子查询
与标量自查询相关的操作符
例:
返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id和工资
SELECT
e.last_name,
e.job_id,
e.salary
FROM
employees e
WHERE
e.job_id = (
SELECT
job_id
FROM
employees
WHERE
employee_id = 141
)
AND e.salary > (
SELECT
salary
FROM
employees
WHERE
employee_id = 143
)
列子查询
与列(多行)子查询相关的操作符
注:
ANY\SOME表示满足子查询表中任一条件即可;ALL表示要满足子查询表中所有条件才行
例:
返回location_id是1400或1700的部门中的所有员工姓名
SELECT
e.last_name
FROM
employees e
WHERE
e.department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_id = 1400
OR location_id = 1700
)
例:
返回其他部门中比job_id为’IT_PROG’部门任一工资低的员工的工号,姓名,job_id 以及salary
SELECT
e.employee_id,
e.last_name,
e.job_id,
e.salary
FROM
employees e
WHERE
e.salary < ANY (
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'
)
分页查询
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数
特点:
①limit语句放在查询语句的最后
②公式
要显示的页数 page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20
联合查询(union)
union 联合 合并:将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
…
应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:★
1、要求多条查询语句的查询列数是一致的!
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重,如果使用union all 可以包含重复项
例:
查询部门编号>90或邮箱包含a的员工信息
ELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
DML
数据操作语言:
插入:insert
修改:update
删除:delete
插入:insert
- 方式一
语法:
insert into 表名(列名,…) values(值1,…);
INSERT INTO beauty
VALUES
(
26,
'hu',
'男',
'1994-01-25',
'1389999',
NULL,
1
),
(
27,
'hu',
'男',
'1994-01-25',
'1389999',
NULL,
1
),
(
28,
'hu',
'男',
'1994-01-25',
'1389999',
NULL,
1
)
- 方式二
INSERT INTO beauty
SET id=19,NAME='刘涛',phone='999';
修改:update
- 单表
语法:
update 表名
set 列=新值,列=新值,…
where 筛选条件;
例:修改beauty表中有’hu’的人的电话为1111
UPDATE beauty
SET phone = '1111'
WHERE
NAME LIKE '%hu%'
- 多表
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,…
where 筛选条件;
例:修改张无忌的女朋友的手机号为114
UPDATE boys bo
JOIN beauty b ON bo.id = b.boyfriend_id
SET b.phone = '112'
WHERE
bo.boyName = '张无忌'
删除 delete,truncate
-
方式一 delete
单表
delete from 表名 where 筛选条件多表
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;例:删除黄晓明的信息以及他女朋友的信息
DELETE b,
bo
FROM
beauty b
JOIN boys bo ON b.boyfriend_id = bo.id
WHERE
bo.boyName = '黄晓明'
- 方式一 truncate
truncate table 表名
delete和truncate对比
:
- delete有返回值,truncate没有返回值
- delete后可以加where筛选条件,truncate不能加
- delete将数据删除后,再插入新的数据,自增长列的值从断点开始,truncate将数据删除后,再插入新的数据,自增长列的值从1开始.
- delete删除可以回滚,truncate删除不可以回滚
DDL 库和表的管理
增
create (if not exists)
改
alter
删
drop (if exists)
库:
创建:create database if not exists 库名
修改:alter database 库名 character set gbk
删除:drop database if exists 库名
表:
创建:create table 表名(
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
列名 列的类型【(长度) 约束】,
…
列名 列的类型【(长度) 约束】
)
修改:
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
删除:drop table if exists 表名
约束
- 六大约束:primary key, not null, default, check, foreign key, unique
- 如何创建约束
CREATE TABLE major (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL
)
CREATE TABLE stuinfo (
id INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
age INT DEFAULT 18,
sex VARCHAR(10) UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)
-
primary key
和unique
区别
都具有唯一性;唯一键可以为null,主键不可以;主键只能有一个,唯一键可以有多个; -
foreign key
:
主表和从表的关联列类型要求一致,名称可以不一致;
主表的关联列必须是一个key;
创建时,必须先创建主表,在创建从表;
删除时,必须先删除主表,再删除从表
TCL(事务)
-
什么是事务?
由一个或者多个SQL语句组成的单独单元,在这个单元中每个SQL语句相互依赖,是一个不可分割的主体,如果有一条语句执行失败,则整个单元回滚,回到之前状态,如果全部执行成功,则事务执行成功。
ACID属性:
原子性:不可分割工作单位,要么都发生,要么都失败;
一致性:从一个一致性状态变换到另外一个一致性转台,通俗点说A,B每个人各有1000块,共2000,转账后,A有800,B有1200,一共还是2000;
隔离性:一个事务的执行不影响其他事务也不被其他事务所影响;
持久性:事务一旦提交,它对数据库中的数据的影响是永久性的。 -
隔离级别:
并发问题:
多个事务访问数据库中的相同数据,没有采取必要的隔离机制就会产生并发问题
- 脏读:事务T1更新了数据但没有提交,事务T2此时读到了数据,但是随后T1回滚,T2读的数据临时且无效
- 不可重复度: 对于两个事务,T1读取了某条数据,随后T2更新了这条数据,T1再读这条数据就不一样了
- 幻读: 系统事务A将数据库中所有数据都删除的时候,但是事务B就在这个时候新插入了一条记录,当事务A删除结束后发现还有一条数据,就好像发生了幻觉一样。
read uncommitted: 级别最低,无法消除任何并发问题
read committed:可以防止脏读(不提交,数据不改变)
repeatable read:可以防止脏读和不可重复度(同一个事务内,不管查询几次相同数据都不会改变,重新开一个事务查询相同数据才会改变)
serializable:可以防止所有并发问题,通过完全锁定事务中涉及的数据表来完成,效率低
视图
- 什么是视图?
是一张虚拟表,通常是一张表或者几张表行列的子集和物理表具有相同的功能,可以进行增删改查的操作,只有在使用时才会动态生成,只保存存在sql逻辑,没有查询数据。
应用场景
:多个地方用到相同的查询结果;查询结果用到的sql语句较为复杂
创建视图
create view 视图名
as
查询结果
修改视图
create or replace view 视图名
as
查询结果
删除视图
drop view 视图名
- table和view对比
创建都是用create关键字,table占用物理空间,view几乎不占用(sql逻辑占用一点点空间),都可以使用增删改查