SQL知识汇总

一 MySQL安装和卸载

1.MySQL卸载

1.先停掉MySQL服务
    此电脑 ---> 右键 --->管理 ---> 服务和应用程序 
        --> 服务 --->MySQL57 ---> 右键 ---> 停止
        
2.卸载MySQL
    控制面板 --> 卸载程序 ---> 卸载MySQL
    
3.如果数据和配置文件不需要，那么可以在安装目录中全部删除。

4.删除MySQL的服务
    以管理员身份运行Dos窗口 ,执行 sc delete MySQL57
    
    
5.如果以上操作执行完还不能安装MySQL那么需要清理注册表
    清理注册表请百度。
    
6.以上操作还不行请重装系统。

2.MySQL安装

详见博客 https://www.cnblogs.com/LzMingYueShanPao/p/14584111.html

二 MySQL概述

1.作用：
        ①用来持久化保存数据  ②可以方便的对数据进行管理(增，删，改，查)和操作
2.概念
    DB : 数据库
    DBMS ：数据库管理系统
    SQL：结构化查询语言
    
3.常见的数据库管理系统:MySQL，Oracle,DB2,Access,SQLServer

4.SQL的优点：①所有的数据库都支持SQL ②易学  ③虽然简单易学但是是一种强有力的语言（可以做复杂的操作）
    
5.SQL(Structural query language)语句分为以下三种类型：
    DML: Data Manipulation Language 数据操纵语言
    DDL:  Data Definition Language 数据定义语言
    DCL:  Data Control Language 数据控制语言

常用的操作：

#查看所有的库：
    show databases;
#查看所有的表
    show tables;
#选库
    use 库名;

三 DML-上

1.查询语句

/*
    select 字段名1，字段名2，字段名3....
    from 表名
    
    select *  #*指的是所有字段
    from 表名
*/

2.别名

/*
    select 字段名1 as 别名，字段名2 别名，字段名3 "别 名"
    from 表名
    
    注意：如果别名中间用空格那么必须使用双引号.
*/

3.注意

/*
1.SQL 语言大小写不敏感。 
2.SQL 可以写在一行或者多行
3.关键字不能被缩写也不能分行
4.各子句一般要分行写。
5.使用缩进提高语句的可读性。
*/

4.过滤

/*
select xxxx
from 表名
where 过滤条件
*/

5.运算符

#获取员工薪水大于等于5000小于等于8000的所有员工
select salary
from employees
#where salary between 5000 and 8000; #范围[5000,8000]
where salary >= 5000 and salary <= 8000;

#获取部门号为10或者20的员工
select department_id
from employees
where department_id in(10,20);#相当于 department_id=10 or department_id=20

/*
like :模糊查询
select xx
from 表名
where 字段名 like xxx
说明：
    '%a%' : %表示任意个数的任意字符
    '_a%' : _表示一个任意的字符
    '_\_%' : \_表示内容为_(\就是转义字符)
    '_$_%' escape '$' : escape用来指定自定义的转义字符
*/

#如果要判断某个字段的内容是否为null 一定要用 is null (不要写成：字段名=null)


#需求：获取员工薪水不在[5000,8000]的所有员工
select salary
from employees
where salary not between 5000 and 8000;

#需求：获取员工的奖金率不为null的员工
select commision_pct
from employees
where commision_pct is not null;

6.排序

/*
select xxxx
from 表名
where 过滤条件
order by 字段名1 desc/asc,字段名2 desc/asc......
说明：
    1.asc表示升序，desc表示降序。
    2.如果没有明确指明是升序还是降序默认为升序
*/
#需求：获取薪水大于5000的员工，并先按照部门号排序如果部门号相同再按照薪水排序
select salary,department_id
from employees
where salary > 5000
order by department_id asc,salary asc;

四 DML-下

1.多表查询

/*
sql99语法:
    select xxxx
    from 表名1 join 表名2
    on 连接条件
    join 表名3
    on 连接条件
    .........
    where 过滤条件
    order by 字段名 desc/asc ......
*/
#需求：获取员工的姓名和部门名称（sql92语法）
select first_name,department_name
from employees,departments
where employees.department_id=departments.departement_id;

#需求：获取员工的姓名和部门名称（sql99语法）
select first_name,department_name
from employees join departments
on employees.department_id=departments.departement_id;

/*
注意：
    1.多表查询时如果同一个字段在多张表中都会出现那么该字段前
        一定要加表名否则报错。
    2.如果某字段只出现在某一张表中那么该字段前最好也加表名
        这样效率高。
*/
SELECT employees.first_name,employees.department_id,departments.department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;#连接条件

#注意：可以给表起别名。
SELECT e.first_name,e.department_id,d.department_name
FROM employees e,departments d #e和d是表的别名
WHERE e.department_id = d.department_id;

2.迪卡尔集错误

#笛卡尔集错误（左表中的每一条数据都会和右表中所有的数据进行连接）
#错误原因：缺少连接条件或连接条件错误
SELECT e.first_name,e.department_id,d.department_name
FROM employees e,departments d;

3.连接分类

/*
    自连接 vs 非自连接
    内连接 vs 外连接
    等值连接 vs  非等值连接
*/

#需求：查询每个员工的姓名及部门名称(非自连接,等值连接,内连接)
SELECT e.`first_name`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#需求：查询每个员工的姓名及管理者的名字（自连接）
SELECT e.`first_name` AS "员工姓名",e2.`first_name` "老板姓名"
FROM employees e JOIN employees e2
ON e.`manager_id` = e2.`employee_id`;

#需求：查询每个员工的薪水及薪水等级（非等值连接）。
SELECT e.`first_name`,e.`salary`,j.`GRADE`
FROM employees e JOIN job_grades j
#on e.`salary` >= j.`LOWEST_SAL` and e.`salary` <= j.`HIGHEST_SAL`;
ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;


#需求：获取所有的员工及员工的部门名称（左外连接）
SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT OUTER JOIN departments d #outer可以省略不写
ON e.`department_id` = d.`department_id`;

#需求：获取所有的部门及部门中的员工（右外连接）
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT OUTER JOIN departments d #outer可以省略不写
ON e.`department_id` = d.`department_id`;

#需求：获取所有的部门及所有的员工（满外连接）
#注意：mysql不支持full join
#union:将两个查询结果合成一张表（去重）
SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT OUTER JOIN departments d #outer可以省略不写
ON e.`department_id` = d.`department_id`
UNION
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT OUTER JOIN departments d #outer可以省略不写
ON e.`department_id` = d.`department_id`;


#需求：获取员工的薪水大于5000的员工名字和部门名称并对结果按照薪水排序（降序）
SELECT e.`first_name`,e.`salary`,d.`department_name`
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`salary` > 5000
ORDER BY e.salary DESC;

4.补充

# `字段名` ：如果字段名和关键字重名那么在查询时字段名需要用``括起来。
#distinct : 去重
#需求：查询所有的部门号
SELECT DISTINCT department_id
FROM employees;

5.函数

5.1单行函数

/*
LOWER('SQL Course') ：将字符串内容全部变成小写
UPPER('SQL Course') ：将字符串内容全部变成大写
*/
SELECT LOWER('aBcDeF'),UPPER('aBcDeF');
#需求：获取所有人的名字（要求全部大写）
SELECT first_name,UPPER(first_name)
FROM employees;

/*
CONCAT('Hello', 'World') : 字符串拼接
SUBSTR('HelloWorld',1,5) : 截取子串
    第一个参数：字符串内容
    第二个参数：索引开始的位置（从1开始）
    第三个参数：长度
LENGTH('HelloWorld') ：字符串长度
INSTR('HelloWorld', 'W') :字符W在当前字符串首次出现的位置
LPAD(salary,10,'*') : 向右对齐
RPAD(salary, 10, '*') ：向左对齐
TRIM('H' FROM 'HelloWorld') : 去除字符串两端指定的字符(内容是区分大小写的)
REPLACE('abcd','b','m')：将当前字符串中b替换成m
*/
SELECT CONCAT('hello','java','good');
SELECT CONCAT(first_name,last_name)
FROM employees;

SELECT SUBSTR('hellojava',3,3);

SELECT LENGTH('hello');
SELECT first_name,LENGTH(first_name)
FROM employees;

SELECT INSTR('abcdefd','d');

SELECT LPAD(salary,10,"*"),RPAD(salary,10,"*")
FROM employees;

SELECT TRIM('H' FROM 'hhhhhaHbhhhh') AS a;

SELECT REPLACE('abcaaaaaa','a','A');


/*
ROUND: 四舍五入
ROUND(45.926, 2)            45.93

TRUNCATE: 截断
TRUNCATE(45.926)            45

MOD: 求余
MOD(1600, 300)                100
*/
SELECT ROUND(45.926, 2),ROUND(45.926, 1),ROUND(45.926, 0),ROUND(45.926, -1);

SELECT TRUNCATE(45.926, 2),TRUNCATE(45.926, 1),
    TRUNCATE(45.926, 0),TRUNCATE(45.926, -1);
    
SELECT MOD(5,3),MOD(-5,3),MOD(5,-3),MOD(-5,-3);

/*
日期函数 ：now()

VERSION():版本号
*/
SELECT NOW(),VERSION();

/*
通用函数：ifnull(字段名，默认值) ：如果字段的内容为null那么就用默认值替换
*/
SELECT IFNULL(NULL,10);
SELECT commission_pct,IFNULL(commission_pct,1)
FROM employees;
#需求：求所有人的工资
SELECT salary,commission_pct,salary+salary*IFNULL(commission_pct,0) new_salary
FROM employees;

/*
case表达式

格式：
case 字段名
when 匹配值1 then 返回值1
when 匹配值2 then 返回值2
when 匹配值3 then 返回值3
......
else 返回值n
end
*/
#练习：查询部门号为 10, 20, 30 的员工信息, 
#若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 
#则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
#其它部门薪水减100
SELECT first_name,salary,department_id,CASE department_id
                    WHEN 10 THEN salary*1.1
                    WHEN 20 THEN salary*1.2
                    WHEN 30 THEN salary*1.3
                    ELSE salary-100
                    END AS "new_salary"
FROM employees;

5.2多行函数(组函数-聚合函数)

/*
max() :求最大值
min() :求最小值
sum() :求和
avg() :求平均数
count（）：求数量

说明：
    1.sum和avg运算的数据类型只能是数值类型
    2.avg()在求平均数时是不包括为null的数据。
    3.
        ①count(字段名) : 不包括为null的数据
        ②count(*) : 求整张表中所有数据的数量
        ③count(数值) ：和count(*)效果一样但是比count(*)效率高
    4.一旦select后出现组函数那么就不能再写其它字段（group by后面的字段可以写）
*/

6.分组和过滤

/*
select xxxx
from 表名 join 表名2
on 连接条件
join 表名3
on 连接条件
......
where 过滤条件
group by 字段名1，字段名2......
having 过滤条件
order by 字段名 desc/asc,字段名2 desc/asc ........
*/

/*
having和where的区别？
1.位置：where是在group by 之前，having在group by之后
2.使用：having后面可以跟组函数。where后面不能跟组函数。
*/

#需求：求各部门（不包括为null）的最低薪水大于5000的部门。
SELECT department_id,MIN(salary)
FROM employees
#注意：where后面不能写组函数
#where department_id is not null and min(salary) > 5000
GROUP BY department_id
HAVING department_id IS NOT NULL AND MIN(salary) > 5000;


#下面和上面的结果一样但是下面的写法效率更高。
SELECT department_id,MIN(salary)
FROM employees
#注意：where后面不能写组函数
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > 5000;

7.子查询

/*
子查询 ：在一条查询语句a中嵌套另一条查询语句b，那么b语句被叫作子查询，a语句被叫作外查询（主查询）

子查询的分类 ：单行子查询 vs 多行子查询

单行子查询 ：子查询返回的结果只有一条
多行子查询 ：子查询返回的结果有多条

注意：先执行子查询再执行外查询。

单行子查询使用的运算符 ：> < >= <= = <>
多行子查询使用的运算符 ：in any all
*/
#需求：谁的工资比 Abel 高?
#方式一：
#1.先求出Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';#11000
#2.再找出比Abel工资高的人
SELECT salary
FROM employees
WHERE salary > 11000;


#方式二：自连接
SELECT e.`salary`
FROM employees e JOIN employees e2
ON e.`salary`>e2.`salary` AND e2.`last_name`='Abel';

#方式三：子查询
SELECT salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name='Abel'
);


#需求：返回job_id与141号员工相同，salary比143号员工多的员工
#            姓名，job_id 和工资
#方式一：
#1.查出141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;#ST_CLERK
#2.查出143号员工的薪水
SELECT salary
FROM employees
WHERE employee_id=143;#2600
#3.查出job_id为ST_CLERK薪水比2600高的员工
SELECT job_id,salary
FROM employees
WHERE job_id='ST_CLERK' AND salary > 2600;

#方式二：子查询
SELECT job_id,salary
FROM employees
WHERE job_id= (
    SELECT job_id
    FROM employees
    WHERE employee_id=141
) AND salary > (
    SELECT salary
    FROM employees
    WHERE employee_id=143
);

#需求：获取比平均薪水高的员工有哪些
#方式一：
#1.获取平均薪水
SELECT AVG(salary)
FROM employees; #6461.682243
#2.获取薪水比6461.682243多的员工
SELECT salary
FROM employees
WHERE salary>6461.682243;


#方式二：
SELECT salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);


#需求：返回公司工资最少的员工的last_name,job_id和salary
#方式一：
#1.求出最低工资
SELECT MIN(salary)
FROM employees;#2100
#2.获取薪水为2100的员工的信息
SELECT last_name,job_id,salary
FROM employees
WHERE salary=2100;

#方式二：
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);


#需求：查询最低工资大于50号部门最低工资的部门id和其最低工资
#用各部门最低薪水和50号部门最低薪水比较
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
    #求50部门最低薪水
    SELECT MIN(salary)
    FROM employees
    WHERE department_id=50
);


#子查询空值问题---子查询没有返回结果
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT salary
    FROM employees
    WHERE first_name='xxx'
);

#错误写法：因为子查询返回的结果是多行而运算符用的是单行子查询用的运算符
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT salary
    FROM employees
);



#需求：题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
#              工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(

    SELECT DISTINCT salary
    FROM employees
    WHERE job_id='IT_PROG'
    
) AND job_id <> 'IT_PROG'

#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
#            的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(

    SELECT DISTINCT salary
    FROM employees
    WHERE job_id='IT_PROG'
    
) AND job_id <> 'IT_PROG'

五 DDL 创建和管理表

1 常用操作

#创建库
create database [if not exists] 库名 [character set '编码集']
#删除库
drop database [if exists] 库名
#选库
use 库名
#查看库中所有的表
show tables;
#删除表
drop table [if exists] 表名
#查看表和库的信息
show create database 库名;
show create table 表名;
#指定库的编码集
create database 库名 character set '编码集'
#指定表的编码集
create table 表名(
字段名 类型，
字段名2 类型
.......
)character set '编码集'
#注意：如果不指定表的编码集那么默认和库的编码集一致。

2 创建表

①方式一 ：白手起家

create table [if not exists] 表名(
字段名 类型，
字段名2 类型
.......
)character set '编码集'
②方式二 : 基于查询结果创建新表

create table [if not exists] 表名
as
sql查询语句
③方式三：基于现有的表结构创建新表（没有数据）

create table [if not exists] 表名
like 库名.表名

3 对表中的列进行增，删，改的操作

/*
alter table 表名 add/drop/change/modify ......
*/
#向表中添加一列
ALTER TABLE student ADD COLUMN sid INT;
#删除表中的列
ALTER TABLE student DROP COLUMN sage;
#修改字段名字
ALTER TABLE student CHANGE COLUMN sname ssname VARCHAR(20);
#修改字段的类型
ALTER TABLE student MODIFY COLUMN sid VARCHAR(20);

4.清空表

truncate table 表名 #清空表中的内容

5.修改表的名字

alter table 原表名 rename to 新表名

六 DCL 数据库事务

1 说明：

事务：将一组逻辑操作单元从一种状态变换到另外一种状态

案例：AA给BB转账1000

AA -= 1000
System.out.println(1/0);
BB += 1000

上面遇到的问题：AA执行成功，但是BB操作失败。（一个人的钱转出另外一个人没有收到）

解决思路：

try{
    开启事务（禁止自动提交）
    AA -= 1000
    System.out.println(1/0);
    BB += 1000
    事务提交
}catch(Exception e){
   事务回滚;
}finally{
    允许自动提交
}

2 MySQL中的操作

禁止自动提交 ：set AUTOCOMMIT=false
提交事务 ：commit
回滚事务 : rollback

七表中数据的增，删，改

1.向表中插入数据

#单行插入
insert into 表名(字段名1，字段名2，......) values(值1，值2......)
#注意：如果不指定向表中的哪个字段插入数据那么默认就是全字段插入
insert into 表名 values(值1，值2，值3.....)
#多行插入
insert into 表名(字段名1，字段名2...) values(值1，值2...),(值1，值2...)......
#基于查询结果插入到当前表中
insert into 表名(字段名1，字段名2...)
sql查询语句

2.删除数据

delete from 表名 [where 过滤条件]

3.修改数据

update 表名 set 字段名1=值1，字段名2=值2..... [where 过滤条件]

4.truncate table 和 delete from的区别

#delete from 和 truncate:
1.delete from删除的数据可以回滚。truncate删掉的数据不能回滚
2.truncate效率比delete from要高

八约束

1 有哪些约束？

/*
NOT NULL 非空约束，规定某个字段不能为空
UNIQUE  唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY  主键(非空且唯一)
FOREIGN KEY  外键
CHECK（mysql不支持）  检查约束
DEFAULT  默认值
*/

2 约束分类：列级约束 vs 表级约束

列级约束：用来约束单列
表级约束：可以用来同时约束多列

3 创建表时添加约束

3.1方式一：在字段后面添加约束，只对一个字段有效(default , not null 只有列级约束)

create table person(
id int primary key,
name varchar(20) not null,
sid int unique,
age int default 10
)

3.2方式二：如果需要对多个字段进行约束

CREATE TABLE student2(
id INT,
username VARCHAR(20),
pwd VARCHAR(20),
#格式 ：constraint 索引名 primary key(字段名1，字段名2.....)
#在mysql中主键约束和唯一约束会自动创建索引
CONSTRAINT pk_id PRIMARY KEY(id)
)

CREATE TABLE student3(
username VARCHAR(20),
pwd VARCHAR(20),
CONSTRAINT uni_nam_pwd UNIQUE(username,pwd)
);

4 表创建成功以后添加约束

/*
primary key

添加约束 : alter table 表名 add primary key (字段名)

修改约束 : alter table 表名 modify  字段名 类型 primary key

删除约束 : alter table 表名 **drop** primary key

unique:

添加约束 : alter table 表名  add unique(字段名)

添加约束 : alter table 表名 add constraint  索引名 unique(字段名)

修改约束 ：alter table 表名 modify 字段名 类型  unique

删除约束 ：alter table 表名 **drop** index 索引名
not null

添加约束：alter table 表名 modify 字段名 字段类型 not null

删除约束 ：alter table 表名 modify 字段名 字段类型 null

default:

设置默认约束 : alter table tb_name alter 字段名 set default value;

删除默认约束 : alter table tb_name alter 字段名 **drop** default;

foreign key:

添加约束 ：  ALTER TABLE 表名  ADD  [CONSTRAINT emp_dept_id_fk]   FOREIGN KEY(dept_id) 

    REFERENCES dept(dept_id);

删除约束 ：alter table 表名 **drop** foreign key  索引名
*/

5 外键约束

5.1案例

#主表
#AUTO_INCREMENT ：主键自增。
CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);


#从表
CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
/*
CONSTRAINT  索引名 FOREIGN KEY(字段名) REFERENCES 表名(字段名)

ON DELETE CASCADE:级联删除，当删除主表中的数据时从表中对应的数据全部也会被删除。
*/
CONSTRAINT xxxxxx FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE CASCADE
);

5.2注意事项

1.先创建主表再创建从表
2.插入数据时先往主表中插入数据再往从表中插入数据
3.删除数据时先删除从表中的数据再删除主表中的数据

九 limit

1.使用：limit 索引位置，数据的条数

select xxxxx
from 表名1 join 表名2
on 连接条件
......
where 过滤条件
group by 字段名1，字段名2....
having 过滤条件
order by 字段名1 desc/asc .......
limit 索引位置，数据的条数

2.注意：

索引位置是从0开始的

3.分页公式：

limit (页数 - 1) * 每页的条数 , 每页的条数