第1章 MySQL安装和卸载
一 卸载MySQL
/*
1.先把MySQL服务给停掉。
此电脑 -> 右键管理 -> 服务和应用程序 -> 服务 -> MySQL57 -> 右键停止服务
2.卸载MySQL - 需要通过控制面板中的程序管理 - MySQL直接双击卸载(360卫士,腾讯管家等工具也可以)
3.删除MySQL目录中的数据(data可以删也可以不删)
4.删除服务(sc delete 服务器) --- dos窗口须要使用管理员权限打开。
5.如果卸载不干净可以考虑清理注册表(参考百度)
6.重启计算机
*/
二 安装MySQL
安装MySQL教程请点击------>MySQL安装教程
三 MySQL概述
/*
一 作用:
1.实现数据持久化
2.使用完整的管理系统统一管理,易于查询
二
DB: 数据库(database):存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS : 数据库管理系统(Database Management System)。数据库是通过DBMS创建和操作的容器
SQL :结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。
三 常见的数据库管理系统:MySQL、Oracle、DB2、SqlServer等
四 SQL的优点:
1、不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL
2、简单易学
3、虽然简单,但实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。
五
SQL(Structural query language)语句分为以下三种类型:
DML: Data Manipulation Language 数据操纵语言
对表中数据的增,删,改,查操作
DDL: Data Definition Language 数据定义语言
对表进行创建,删除,修改表等操作
DCL: Data Control Language 数据控制语言
权限和事务的处理。
*/
第2章 说明和基本操作
#查看所有的库
show databases;
#选中某一个库
use 库名;
#查看所有的表
show tables;
第3章 DML语言(上)
一 查询操作
#select 字段名1,字段名2...... from 表名
select first_name,salary
from employees;
#查询所有的字段 select * from 表名
select * from employees;
二 别名
#第一种格式 :select 字段名 别名 from 表名
select first_name name from employees;
#第二种格式 :select 字段名 as 别名 from 表名
select first_name as name from employees;
#注意 :别名我们一般都会使用双引号引起来-尤其是在别名中有空格出现时必须使用双引号
select first_name as "first name" from employees;
三 过滤
#where 过滤条件
select salary
from employees
where salary > 1000;
四 运算符
#between...and....
select salary
from employees
where salary between 6000 and 10000; # 相当于salary >= 6000 and salary <= 10000
#in
select salary
from employees
where salary in (2000,3000)l #相当于 salary=2000 or salary=3000
like:模糊查询
#like : 模糊查询
#例 :查询员工的姓名中带有a字符的所有员工
SELECT first_name
FROM employees
WHERE first_name LIKE '%a%';#%表示任意个数任意字符
#例:查询员工的姓名第二个字符带有a的员工。
SELECT first_name
FROM employees
WHERE first_name LIKE '_a%';# _表示任意一个字符
#例:查询员工的姓名第二个字符为_的员工的姓名
SELECT first_name
FROM employees
WHERE first_name LIKE '_\_%'; # \_ 转义字符
#指定转义字符
SELECT first_name
FROM employees
WHERE first_name LIKE '_$_%' ESCAPE '$'; # escape:指定转义字符
五 排序
5.1格式
/*
order by 字段1 desc/asc,字段2 desc/asc.....
注意:
1.如果没有指明是升序还是降序默认是升序
*/
5.2二级排序
/*
例 :
select salary,department_id
from employees
order by salary,department_id;
*/
5.3可以按照别名排序
/*
select salary s
from employees
order by s;
*/
六 多表查询
6.1.说明
①连接的方式:
/*
自连接 vs 非自连接
等值连接 vs 非等值连接
内连接 vs 外连接
*/
②有sql92和sql99语法的区别
sql92语法
select e.first_name,d.department_name
from employees e,departments d
where e.department_id = d.department_id;
sql99语法
/*
select 字段名
from 表1 [inner] join / left [outer] join / right [outer] join 表2
on 连接条件
join 表3
on 连接条件
.........
*/
select e.first_name,d.department_name
from employees e join departments d
on e.department_id = d.department_id;
③内连接vs外连接
/*
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL).
*/
#内连接 :获取所有员工的姓名和部门名称
select e.first_name,d.department_name
from employees e join departments d
on e.department_id = d.department_id;
#左外连接 :获取所有的员工及部门名称
select e.first_name,d.department_name
from employees e left join departments d
on e.department_id = d.department_id;
#右外连接 :获取所有的部门及员工
select e.first_name,d.department_name
from employees e right join departments d
on e.department_id = d.department_id;
#将右外连接变成左外连接
select e.first_name,d.department_name
from departments d left join employees e
on e.department_id = d.department_id;
6.2常见的一个错误(迪卡尔集错误)
#原因 :没有连接条件或者连接条件错误
6.3 满外连接的实现(mysql不支持full join)
#满外连接:full join
#mysql不支持full join我们可以使用关键字union将左外连接的结果和右外连接的结果合并
SELECT e.`first_name`,d.`department_name`
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
UNION
SELECT e.`first_name`,d.`department_name`
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
七 函数
7.1函数的分类
单行函数 ,组函数, 通用函数
7.2单行函数
/*
LOWER('SQL Course') : 将内容全部转为小写
UPPER('SQL Course') :将内容全部转为大写
CONCAT('Hello', 'World') : 将两个字符进行拼接
SUBSTR('HelloWorld',1,5) : 将字符串从1的位置开始截取子串长度为5。(索引从1开始)
LENGTH('HelloWorld') : 字符串长度
INSTR('HelloWorld', 'W') :w在当前字符串中首次出现的位置
LPAD(salary,10,'*') :向右对齐
RPAD(salary, 10, '*') :向左对齐
TRIM('H' FROM 'HelloWorld') : 去除字符串两端指定的字符H
REPLACE('abcd','b','m') : 将当前字符串中的b替换成m
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNCATE: 截断
TRUNCATE(45.926) 45
MOD: 求余
MOD(1600, 300) 100
now() : 当前日期和时间
*/
7.3组函数
/*
组函数
AVG() :求平均数
SUM(): 求和
MAX():求最大值
MIN() :求最小值
COUNT() : 求数量
#avg()和sum() : 注意只能运算数值类型的数据
#处理的数据不包括null
#max()和min() :注意只能运算数值,日期,字符串
count(字段名) : 统计的时候不包括null
count(*) : 统计表中所有的数据的条数(个别字段为null不受影响)。
count(数值) : 统计表中所有的数据的条数。
每条数据都会"存放"该数值然后统计该数值的个数。
效率比count(*)高
*/
7.4通用函数
/*
IFNULL(commission_pct,0) : 如果commission_pct中的内容为null那么就用0替换
----------------------等值------------------------
case 字段名
when 值1 then 相应的操作1
when 值1 then 相应的操作2
....
else
相应的操作
end
----------------------非等值----------------
case
when 条件1 then 相应的操作1
when 条件2 then 相应的操作2
....
else
相应的操作
end
*/
7.5if函数
#if(条件表达式,值1,值2) :如果条件表达式的值为true结果为值1,否则为值2
7.6去重:distinct
#distinct 字段名: 去除重复的数据
八 分组和过滤(having)
8.1分组的格式
/*
select 字段名1,字段名2
from 表名
group by 字段名1,字段名2......
*/
#例:求各部门中不同工种的员工的最低薪水。
SELECT department_id,job_id,MIN(salary)
FROM employees
GROUP BY department_id,job_id;
#注意:select后面如果出现了组函数和其它字段,那么其它字段必须出现在group by的后面。
8.2过滤 having
/*
having 过滤条件
having和where的不同点?
1.having后面可以现出组函数,where后面不能出现组函数
2.过滤条件如果可以出现在where或having后面,那么优先放在where后面(不影响结果)
*/
#例:求各部门平均薪水大于6000的部门。
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)>6000;
第4章 DML语言(中)
一 子查询说明
/*
1.子查询分为单行子查询和多行子查询
单行或多行指的是子查询的结果的条数。
2.单行子查询使用的运算符:
= <> >= <= > <
多行子查询使用的运算符:
in any all
3.在执行带有子查询的sql语句,先执行内(子)查询再执行外查询。
*/
二 单行子查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-By6WDAx2-1625136949293)(/1595897641476.png)]
#例:谁的工资比 Abel 高?
#思路:1.要先查到Abel工资是多少 2.再查全表进行过滤
#方式一:
SELECT salary
FROM employees
WHERE last_name='Abel'; #11000
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和143号员工的薪水 2.再过滤
SELECT job_id
FROM employees
WHERE employee_id=141;#ST_CLERK
SELECT salary
FROM employees
WHERE employee_id=143;#2600
SELECT first_name,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
);
三 多行子查询
#题目:返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
# 工号、姓名、job_id 以及salary
#思路:1.查找job_id为‘IT_PROG’的所有员工的薪水 2.其它部门的薪水和该部门的该薪水做比较
SELECT employee_id,first_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,first_name,job_id,salary
FROM employees
WHERE salary < ALL (
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id <> 'IT_PROG';
第5章 DDL 创建和管理表
一 常用操作
#创建库
create database 库名;
#删除库
drop database 库名;
#选库
use 库名;
#查看库中所有的表
show tables;
#删除表
drop table 表名;
#查看表和库的信息
show create database/table 库名/表名;
#指定库的编码集
create database 库名 character set '编码集';
#指定表的编码集
create table 表名(
字段名 类型
.......
)character set ‘编码集’
二 创建表
①方式一 :白手起家
create table 表名(
id int,
name varchar(20),
........
)
②方式二 : 基于现有的表创建新表(把查询的结果创建成一张表,没有原表中的约束)
create table 表名
select 字段名1.......
from 表名;
三 对表中的列进行增,删,改的操作
对表名修改: alter table 原表名 rename to 新表名;
格式: alter table 表名 add/drop/modify/change column …
#添加列
ALTER TABLE person ADD COLUMN sex CHAR;
#修改列的名称
ALTER TABLE person CHANGE COLUMN age2 age3 CHAR;
#修改列的类型
ALTER TABLE person MODIFY COLUMN sex INT;
#删除列
ALTER TABLE person DROP COLUMN birthday;
清空表:
TRUNCATE TABLE emp;
第6章 DML语言(下)
一 插入数据
#插入单行数据
insert into 表名(字段名1,字段名2...) values(value1,value2......)
#插入多行数据
insert into 表名(字段名1,字段名2...) values(value1,value2......),(value1,value2......).....
#如果插入数据时是全字段插入,那么表名后面的字段可以省略
insert into 表名 values(value1,value2......)
#基于现有的表中的数据插入到当前表中
#注意:数据类型必须保持一致
insert into 表名(字段名1,字段名2....)
select 字段名1,字段名2.....
from 表名
二 删除数据
delete from 表名; #删除表中所有内容
delete from 表名 where 过滤条件;
三 修改数据
#修改表中所有的指定字段的值
update 表名 set 字段名1=value1,字段名2=value2...
update 表名 set 字段名1=value1,字段名2=value2... where 过滤条件
四 truncate和delete的区别?
1.truncate删除的内容不能回滚,delete删除的内容可以回滚(前提是要开启事务)
2.如果删除的数据不用回滚那么优先truncate因为效率高一些
第7章 DCL 数据库事务
一 说明:
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态
案例 :AA给BB转账100
AA :1000
BB :1000
操作 :
1.AA-100 : 900
---------发生异常了--------
2.BB+100 : 执行不了
**上面遇到的问题 :**AA账号操作成功,另一个账号BB操作失败,这样是不行的。
二 在java代码中解决的思路
try{
1.开启事务 : set autocommit=false;
2.AA-100 : 900
---------发生异常了--------
3.BB+100 : 执行不了
commit;
}catch(Exception e){
回滚数据;
}finally{
set autocommit=true;
}
三 mysql中的操作
开启事务 :set autocommit=false;
提交事务 :commit;
回滚事务 : rollback;
第8章 约束
一 约束的说明
当我们向表中插入数据时,对某些字段的数据有所要求,比如不能为null,必须唯一,或者有默认值等等
那么我们就需要对表中的该字段进行约束。
二 有哪些约束?
/*
NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一)
FOREIGN KEY 外键
DEFAULT 默认值
CHECK 检查约束(mysql不支持)
*/
三 约束分类 :列级约束 vs 表级约束
/*
约束: 列级约束 vs 表级约束
列级约束 :只能约束一列。
表级约束 :可以同时约束多列。
注意 :not null和default只有列级约束。
*/
四 创建表时添加约束
方式一 :在字段后面添加约束,只对一个字段有效
CREATE TABLE stu(
id INT PRIMARY KEY,#主键约束(非空且唯一,只要是表一定有主键约束)
age INT DEFAULT 18,#如果没有给该字段插入数据那么默认插入18
NAME VARCHAR(20) NOT NULL,
sid INT UNIQUE #唯一维束内容不能相同
);
方式二 :如果需要对多个字段进行约束 - primary key,unique ,foreign key
CREATE TABLE stu2(
id INT,
age INT,
NAME VARCHAR(20),
passwd VARCHAR(20),
sid INT,
#添加表级约束--可以同时约束多个列
CONSTRAINT stu_id PRIMARY KEY(id),
CONSTRAINT stu_name_pwd UNIQUE(NAME,passwd)
);
五 表创建成功以后添加约束
/*
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 索引名
*/
#表创建成功以后添加约束
CREATE TABLE s(
id INT,
NAME VARCHAR(20),
age INT
)
#添加约束
ALTER TABLE s ADD PRIMARY KEY(id);
INSERT INTO s(id,NAME,age) VALUES(1,"aa",20);
DROP TABLE s;
ALTER TABLE s MODIFY id INT PRIMARY KEY;
#删除约束 -- 仍然保留不能为null
ALTER TABLE s DROP PRIMARY KEY;
DESC s;
/*
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 索引名
*/
六 外键约束
①格式:
CONSTRAINT 索引名 FOREIGN KEY(字段名)
REFERENCES 主表名(字段名)
②注意 :
创建表:应该先创建主表再创建从表
添加内容:先添加主表再添加从表
删除内容:先删除从表再删除主表
③创建表后添加约束的操作
#删除索引
ALTER TABLE emp
DROP FOREIGN KEY 索引名;
#添加约束
ALTER TABLE emp
ADD [CONSTRAINT 索引名] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);
第9章 limit
一 使用 :
limit 索引值,数据的条数
#公式:
#(当前页数-1)*每页条数,每页条数
二 注意 :
索引值是从0开始的
三 作用 :
用来分批获取表中的数据(分页)