MySQL基础

MySQL基础

• 单表(DQL)
• 多表(DQL)
• 内连接
• 外连接
• 子查询
• 分页查询
• 联合查询(union)

• 插入：insert
• 修改：update
• 删除 delete，truncate

单表(DQL)

1. concat拼接

SELECT CONCAT(last_name,\" \",first_name) from employees

ex：

显示出表 employees 中的全部 job_id(不能重复)

SELECT DISTINCT job_id from employees

1. IFNULL(expr1,expr2)
2. 逻辑表达式（运算符）

and（&&）:两个条件如果同时成立，结果为true，否则为false；
or(||)：两个条件只要有一个成立，结果为true，否则为false；
not(!)：如果条件成立，则not后为false，否则为true；

SELECT last_name,salaryfrom employeeswhere salary BETWEEN 10000 and 20000

SELECT last_name,salaryfrom employeeswhere salary >= 10000 and salary <= 20000

1. in

SELECT employee_id, last_name, salary, manager_id FROM employeesWHERE manager_id IN (100, 101, 201);

1. like

   注:

   ‘_’代表一个字符

select last_namefrom employeeswhere last_name like \'_a%\'

1. is null (is not null)
   
2. 排序
   查询部门编号大于90的员工信息，按入职时间先后进行排序（

   按查询条件排序

   ）

SELECT*FROMemployeesWHEREdepartment_id >= 90ORDER BYhiredate ASC

按年薪的高低显示员工的信息和年薪（

按表达式排序

）

SELECT*, 12 * salary * (1 + IFNULL(commission_pct, 0)) 年薪FROMemployeesORDER BY12 * salary * (1 + IFNULL(commission_pct, 0)) DESC

按年薪的高低显示员工的信息和年薪（

按别名排序

）

SELECT*, 12 * salary * (1 + IFNULL(commission_pct, 0)) 年薪FROMemployeesORDER BY年薪 DESC

按姓名的长度显示员工的姓名和工资（

按别名排序

）

SELECTlast_name,salaryFROMemployeesORDER BYLENGTH(last_name) DESC

查询员工信息，要求先按工资排序，再按员工编号排序（

按多个字段排序

）

SELECT*FROMemployeesORDER BYsalary 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

SELECTmin(salary),manager_idFROMemployeesWHERE manager_id > 102GROUP BYmanager_id

• 添加删选条件：最低工资>5000

  注:

  最低工资>5000这个条件可以在分组后的表中查到，所以用

  having

SELECTmin(salary),manager_idFROMemployeesWHERE manager_id > 102GROUP BYmanager_idHAVINGmin(salary) > 5000

多表(DQL)

内连接

查询结果为几张表的交集

• 等值连接

#案例2.查询名字中包含e的员工名和工种名（添加筛选）SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.`job_id`=  j.`job_id`WHERE e.`last_name` LIKE \'%e%\';

#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）#①查询每个城市的部门个数#②在①结果上筛选满足条件的SELECT city,COUNT(*) 部门个数FROM departments dINNER JOIN locations lON d.`location_id`=l.`location_id`GROUP BY cityHAVING COUNT(*)>3;

#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）SELECT COUNT(*) 个数,department_nameFROM employees eINNER JOIN departments dON e.`department_id`=d.`department_id`GROUP BY department_nameHAVING COUNT(*)>3ORDER BY COUNT(*) DESC;

• 非等值连接

#查询工资级别的个数>20的个数，并且按工资级别降序SELECT COUNT(*),grade_levelFROM employees eJOIN job_grades gON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`GROUP BY grade_levelHAVING COUNT(*)>20ORDER BY grade_level DESC;

• 自连接

#查询姓名中包含字符k的员工的名字、上级的名字SELECT e.last_name,m.last_nameFROM employees eJOIN employees mON e.`manager_id`= m.`employee_id`WHERE e.`last_name` LIKE \'%k%\';

外连接

用于查询一张表中有，另一张表中没有的记录

外连接的查询结果为

主表中的所有记录

，
如果从表中有与主表中记录相匹配的数据，则显示匹配的数据；如果从表中没有与主表中相匹配的数据，则显示null。
左外连接：left 左边为主表
右外连接：right右边为主表
全外连接：两表所有数据都显示，没有匹配数据用null填充

查询哪个部门没有员工

SELECTd.department_nameFROMdepartments dLEFT JOIN employees e ON d.department_id = e.department_idWHEREe.employee_id IS NULL

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询

from后面：支持表子查询where或having后面：★标量子查询（单行） √列子查询  （多行） √行子查询exists后面（相关子查询）表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

标量子查询

与标量自查询相关的操作符

例：
返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资

SELECTe.last_name,e.job_id,e.salaryFROMemployees eWHEREe.job_id = (SELECTjob_idFROMemployeesWHEREemployee_id = 141)AND e.salary > (SELECTsalaryFROMemployeesWHEREemployee_id = 143)

列子查询

与列(多行)子查询相关的操作符

注：

ANY\\SOME表示满足子查询表中任一条件即可；ALL表示要满足子查询表中所有条件才行

例：
返回location_id是1400或1700的部门中的所有员工姓名

SELECTe.last_nameFROMemployees eWHEREe.department_id IN (SELECTdepartment_idFROMdepartmentsWHERElocation_id = 1400OR location_id = 1700)

例：
返回其他部门中比job_id为’IT_PROG’部门任一工资低的员工的工号，姓名，job_id 以及salary

SELECTe.employee_id,e.last_name,e.job_id,e.salaryFROMemployees eWHEREe.salary < ANY (SELECT DISTINCTsalaryFROMemployeesWHEREjob_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=10page1	02  	103	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%\'UNIONSELECT * FROM employees  WHERE department_id>90;

DML

数据操作语言：
插入：insert
修改：update
删除：delete

插入：insert

• 方式一
  语法：
  insert into 表名(列名,…) values(值1,…);

INSERT INTO beautyVALUES(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 beautySET id=19,NAME=\'刘涛\',phone=\'999\';

修改：update

• 单表
  语法：
  update 表名
  set 列=新值,列=新值,…
  where 筛选条件;

例：修改beauty表中有’hu’的人的电话为1111

UPDATE beautySET phone = \'1111\'WHERENAME LIKE \'%hu%\'

• 多表
  update 表1 别名
  inner|left|right join 表2 别名
  on 连接条件
  set 列=值,…
  where 筛选条件;

例：修改张无忌的女朋友的手机号为114

UPDATE boys boJOIN beauty b ON bo.id = b.boyfriend_idSET b.phone = \'112\'WHEREbo.boyName = \'张无忌\'

删除 delete，truncate

• 方式一 delete
  单表
  delete from 表名 where 筛选条件

  多表
  delete 表1的别名,表2的别名
  from 表1 别名
  inner|left|right join 表2 别名 on 连接条件
  where 筛选条件;

  例：删除黄晓明的信息以及他女朋友的信息

DELETE b,boFROMbeauty bJOIN boys bo ON b.boyfriend_id = bo.idWHEREbo.boyName = \'黄晓明\'

• 方式一 truncate
  truncate table 表名

delete和truncate对比

：

1. delete有返回值，truncate没有返回值
2. delete后可以加where筛选条件，truncate不能加
3. delete将数据删除后，再插入新的数据，自增长列的值从断点开始，truncate将数据删除后，再插入新的数据，自增长列的值从1开始.
4. 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(事务)

1. 什么是事务？
   由一个或者多个SQL语句组成的单独单元，在这个单元中每个SQL语句相互依赖，是一个不可分割的主体，如果有一条语句执行失败，则整个单元回滚，回到之前状态，如果全部执行成功，则事务执行成功。

   ACID属性：

   原子性：不可分割工作单位，要么都发生，要么都失败；
   一致性：从一个一致性状态变换到另外一个一致性转台，通俗点说A,B每个人各有1000块，共2000，转账后，A有800，B有1200，一共还是2000；
   隔离性:一个事务的执行不影响其他事务也不被其他事务所影响；
   持久性:事务一旦提交，它对数据库中的数据的影响是永久性的。

2. 隔离级别：

   并发问题：

   多个事务访问数据库中的相同数据，没有采取必要的隔离机制就会产生并发问题

• 脏读：事务T1更新了数据但没有提交，事务T2此时读到了数据，但是随后T1回滚，T2读的数据临时且无效
• 不可重复度: 同一个事务内，T1读取了某条数据，随后T2更新了这条数据，T1再读这条数据就不一样了
• 幻读: 系统事务A将数据库中所有数据都删除的时候，但是事务B就在这个时候新插入了一条记录，当事务A删除结束后发现还有一条数据，就好像发生了幻觉一样。

read uncommitted: 级别最低，无法消除任何并发问题

read committed:可以防止脏读(不提交，数据不改变)

repeatable read：可以防止脏读和不可重复度(同一个事务内，不管查询几次相同数据都不会改变，重新开一个事务查询相同数据才会改变)

serializable：可以防止所有并发问题，通过完全锁定事务中涉及的数据表来完成，效率低

视图

1. 什么是视图？
   是一张虚拟表，通常是一张表或者几张表行列的子集和物理表具有相同的功能，可以进行增删改查的操作，只有在使用时才会动态生成，只保存存在sql逻辑，没有查询数据。

   应用场景

   ：多个地方用到相同的查询结果；查询结果用到的sql语句较为复杂
   创建视图

create view 视图名as查询结果

修改视图

create or replace view 视图名as查询结果

删除视图

drop view 视图名

1. table和view对比
   创建都是用create关键字，table占用物理空间，view几乎不占用(sql逻辑占用一点点空间)，都可以使用增删改查