一. 什么是数据库
概念:
数据库是按照数据结构来组织, 存储和管理数据的仓库, 是一个长期存储在计算机内的, 有组织的, 有共享的, 统一管理的数据集合
分类:
网状结构数据库: 美国通用汽车公司IDS(Integrated Data Store), 以节点形式存储和访问
层次结构数据库: IBM公司IMS(Information Management System), 定向有序的树状结构实现存储和访问
关系型数据库: Oracle, DB2, MySQL, SQL Server, 以表格(Table)存储, 多表间建立关联关系, 通过分类, 合并, 连接, 选取等运算实现访问
非关系型数据库: MongoDB, Redis, 多数使用哈希表, 表中以键值(Key-Value)的方式实现特定的键和一个指针指向的特定数据
二. MySQL简介
MySQL是一个关系型数据库管理系统, 由瑞典MySQL AB公司开发, 属于Oracle旗下产品. MySQL是最流行的关系型数据库管理系统之一
2.1 配置环境变量
Windows
系统变量下创建MYSQL_HOME: C:\Program Files\MySQL\MySQL Server 5.7
系统变量下追加path: ;%MYSQL_HOME%\bin
进入MySQL
win+R打开cmd, 直接输入mysql -uroot -p+密码(明文输入)
或者先输入mysql -uroot -p, 按回车Enter, 再输入密码(密文输入)
2.2 核心文件介绍
C:\Program Files\MySQL\MySQL Server 5.7下:
文件夹bin: 命令文件
文件夹lib: 库文件
文件夹include: 头文件
文件夹share: 字符集, 语言等信息
2.3 MySQL配置文件介绍
使用Notepad++打开C:\ProgramData\MySQL\MySQL Server 5.7下的my.ini文件
default-character-set: 客户端默认字符集
character-set-server: 服务端默认字符集
port: 客户端和服务端的端口号
default-storage-engine=INNODB: MySQL默认存储引擎INNODB
2.4 卸载
控制台卸载(MySQL安装软件重新打开一遍,有删除的选项)
找到MySQL的安装目录进行删除
ProgramData下删除MySQL
注意: 如果卸载后,仍有未删除的MySQL服务,可采用手动删除
方法一: 以管理员身份打开命令行输入sc delete MySQL57回车(我是57版本)
方法二: win+r+regedit打开注册表, 然后Ctrl+F搜索MySQL,删除搜索到的选项
无论采用哪种方法, 最后一步要将MySQL的环境变量删除
三. SQL语言
SQL(Structured Query Language) 结构化查询语言, 用于存储数据, 更新, 查询和管理关系数据库系统的程序设计语言
经验: 通常执行对数据库的"增删改查", 简称C(Create) R(Read) U(Update) D(Delete)
3.1 MySQL应用
对于数据库的操作, 需要在进入MySQL环境下进行指令确认, 并在一句指令的末尾处使用;结束
3.1.1 基本命令(不区分大小写)
查看MySQL中所有数据库
show databases; #查询所有数据库
创建自定义数据库
create database mydb1; #创建mydb1数据库
create database mydb2 character set gbk; #创建数据库并设置编码格式
create database if not exists mydb2; #如果不存在mydb2数据库再创建mydb2(推荐使用)
查看数据库创建信息
show create database mydb2; #查看mydb2数据库时的基本信息
修改数据库
alter database mydb2 character set utf8; #修改mydb2的编码格式为utf-8(注: 在数据库操作里utf-8不写-)
删除数据库
drop database mydb1; #删除数据库mydb1
查看当前所使用的数据库
select database(); #查看当前所使用的数据库
使用数据库
use mydb2; #使用mydb2数据库
3.2 客户端工具
Navicate
Navicate是一套快速, 可靠并价格相宜的数据库管理工具, 专为简化数据库的管理及降低系统管理成本而设
它的设计符合数据库管理员, 开发人员及中小企业的需要, 是以直觉化的图形用户界面而建的, 让你可以用安全并且简单的方式创建, 组织, 访问并共用信息SQLyog
下载地址:
MySQL可能是世界上最流行的开源数据库引擎, 但是使用基本的工具和配置文件可能很难进行管理
SQLyog提供了完整的图形界面, 即使初学者也可以轻松使用MySQL的强大功能, 其拥有广泛的预定义工具和查询, 友好的视觉界面, 类似Excel的查询结果编辑界面等优点
四. 数据查询[重点]
4.1 数据库表的基本结构
关系结构数据库是以表格(Table)进行数据存储, 表格由"行"和"列"组成
经验: 执行查询语句返回的结果是一张虚拟表
4.2 基本查询
语法: select 列名 from 表名;
关键字
select: 指定要查询的列
from: 指定要查询的表
4.2.1 查询部分列
#查询员工表中所有员工的编号, 名字, 邮箱
select employee_id,first_name,email
from t_employees;
4.2.2 查询所有列
#查询员工表中所有员工的所有信息(所有列)
select 所有列的列名 from t_employees;
select * from t_employees;
注意: 生产环境下, 优先使用列名查询, *的方式需转换成全列名, 效率低, 可读性差
4.2.3 对列中的数据进行运算
#查询员工表中所有员工的编号, 名字, 年薪
select employee_id, first_name, salary*12
from t_employees;
算数运算符:
+: 两列做加法运算
-: 两列做减法运算
*: 两列做乘法运算
/: 两列做除法运算
注意: %是占位符, 而非模运算符
4.2.4 列的别名
列 as '列名'
#查询员工表中所有员工的编号,名字,年薪(列名均为中文)
select employee_id as '编号',first_name as '名字',salary*12 as '年薪'
from t_employees;
4.2.5 查询结果去重
distinct 列名
#查询员工表中所有经理的ID
select distinct manager_id
from t_employees;
4.3 排序查询
语法: select 列名 from 表名 order by 排序列[排序规则];
排序规则:
asc: 对前面排序列做升序排序
desc: 对前面排序列做降序排序
注意: 使用order by 排序的列的类型必须是数值类型,如果是varchar排序会不准确,
可以使用CONVERT(列名,DECIMAL)把varchar转换为decimal
DECIMAL数据类型用于在数据库中存储精确的数值
DECIMAL(P，D)表示列可以存储D位小数的P位数
4.3.1 依据单列排序
#查询员工的编号,名字,薪资.按照工资高低进行升序排序
select employee_id,first_name,salary
from t_employees
order by convert(salary,decimal) asc;
4.3.2 依据多列排序
#查询员工的编号,名字,薪资.按照工资高低进行降序排序(薪资相同时,按照编号进行降序排序)
select employee_id,first_name,salary
from t_employees
order by salary desc,employee_id desc;
4.4 条件查询
语法: select 列名 from 表名 where 条件;
关键字:
where 条件: 在查询结果中, 筛选符合条件的查询结果,条件为布尔表达式
4.4.1 等值判断(=)
#查询薪资是11000的员工信息(编号,名字,薪资)
select employee_id,first_name,salary
from t_employees
where salary = 11000;
注意: 与java不同(==), mysql中等值判断使用=
4.4.2 逻辑判断(and,or,not)
#查询薪资是11000并且提成是0.30的员工信息(编号,名字,薪资)
select employee_id,first_name,salary
from t_employees
where salary = 11000 and commission_pct = 0.30;
4.4.3 不等值判断(>,<,>=,<=,!=,<>)
#查询员工的薪资在6000-10000之间的员工信息(编号,名字,薪资)
select employee_id,first_name,salary
from t_employees
where salary >= 6000 and salary <=10000;
4.4.4 区间判断(between and)
#查询员工的薪资在6000~10000之间的员工信息(编号,名字,薪资)
select employee_id,first_name,salary
from t_employees
where salary between 6000 and 10000; #闭区间,包含区间边界的两个值
注: 在区间判断语法中, 小值在前, 大值在后, 反之, 得不到正确结果
4.4.5 NULL值判断(is null, is not null)
is null
列名 is null
is not null
列名 is not null
#查询没有提成的员工信息
select employee_id,first_name,salary,commission_pct
from t_employees
where commission_pct is null;
4.4.6 枚举查询(IN(值1,值2,值3))
#查询部门编号为70,80,90的员工信息(编号,名字,薪资,部门编号)
SELECT employee_id,first_name,salary,department_id
FROM t_employees
WHERE department_id in(70,80,90);
#注: in的查询效率较低,可通过多条件拼接
4.4.7 模糊查询
like _ (单个任意字符)
列名 like '张_'
like % (任意长度的任意字符)
列名 like '张%'
注意: 模糊查询只能和like关键字结合使用
#查询名字以"L"开头的员工信息(编号,名字,薪资,部门编号)
select employee_id,first_name,salary,department_id
from t_employees
where first_name like 'L%'
#查询名字以"L"开头并且长度为4的员工信息(编号,名字,薪资,部门编号)
select employee_id,first_name,salary,department_id
from t_employees
where first_name like 'L___%'
4.4.8 分支结构查询
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果
end
注意: 通过使用case end进行条件判断, 每条数据对应生成一个值
经验: 类似java中的switch
#查询员工信息(编号,名字,薪资,部门编号, 薪资级别<对应条件表达式生成>)
select employee_id,First_name,salary,department_id,
case
when salary >= 10000 then 'A'
when salary >= 8000 and salary < 10000 then 'B'
when salary >= 6000 and salary < 8000 then 'C'
when salary >= 4000 and salary < 6000 then 'D'
else 'E'
end as "level"
from t_employees;
4.5 时间查询
语法: select 时间函数([参数列表])
经验: 执行时间函数查询, 会自动生成一张虚拟表(一行一列)
时间函数
SYSDATE(): 当前系统时间(日,月,年.时,分,秒)
CURDATE(): 获取当前日期
CUITIME(): 获取当前时间
WEEK(DATE): 获取指定日期为一年中的第几周
YEAR(DATE): 获取指定日期的年份
HOUR(TIME): 获取指定时间的小时值
MINUTE(TIME): 获取时间的分钟值
DATEDIFF(DATE1,DATE2): 获取DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N): 计算DATE加上N天后的日期
#当前系统时间
SELECT SYSDATE();
#当前系统日期
SELECT CURDATE();
#当前时间
SELECT CURTIME();
#获取指定日期为一年中的第几周
SELECT WEEK(SYSDATE());
#获取指定日期中的年份
SELECT YEAR('2021-3-16');
#获取小时值
SELECT HOUR(CURTIME());
#获取分钟值
SELECT MINUTE(CURTIME());
#指定日期之间的相隔天数
SELECT DATEDIFF('2021-2-2','2020-2-2');
#计算Date日期加上N天后的日期
SELECT ADDDATE('2020-3-16',1);
4.6 字符串查询
语法: select 字符串函数([参数列表])
字符串函数
CONCAT(str1,str2,str...): 将多个字符串连接
INSERT(str,pos,len,newStr): 将str中指定pos位置开始len长度的内容替换为newStr
LOWER(str): 将指定字符串转换为小写
UPPER(str): 将指定字符串转换为大写
SUBSTRING(str,num,len): 将str字符串指定num位置开始截取len个内容
#拼接内容
select concat('My','S','QL');
#字符串替换
select insert('这是一个数据库',3,2,'MySQL');#结果为: 这是MySQL数据库
#指定内容转换为小写
select lower('MYSQL');#mysql
#指定内容转换为大写
select lower('mysql');#MYSQL
#指定内容截取
select substring('JavaMySQLOracle',5,5);#MySQL
MySQL中的字符串函数下标起始位置是从1开始的
4.7 聚合函数
语法: select 聚合函数(列名) from 表名;
经验: 对多条数据的单列进行统计, 返回统计后的一行结果
聚合函数:
SUM(): 求所有行中单列结果的总和
AVG(): 平均值
MAX(): 最大值
MIN(): 最小值
COUNT(): 求总行数
4.7.1 单列求和
#统计所有员工每月的工资总和
select sum(salary)
from t_employees;
4.7.2 单列平均值
#统计所有员工每月的平均工资
select avg(salary)
from t_employees;
4.7.3 单列最大值
#统计最高月薪的值
select max(salary)
from t_employees;
4.7.4 单列最小值
#统计最低月薪的值
select min(salary)
from t_employees;
4.7.5 总行数
#统计员工总数
select count(employee_id)
from t_employees;
#统计有提成的员工总数
select count(commission_pct)
from t_employees;
注意: 聚合函数自动忽略null值,不进行统计
4.8 分组查询
语法: select 列名 from 表名 where 条件 group by 分组依据(列);
关键字:
group by: 分组依据, 必须在where之后生效
4.8.1 查询各部门的总人数
#思路
#1.按照部门编号进行分组(分组依据是department_id)
#2.再针对各部门的人数进行统计(count)
select department_id,count(employee_id)
from t_employees
group by department_id;
4.8.2 查询各部门的平均工资
#思路
#1.按照部门编号进行分组(分组依据是department_id)
#2.再针对各部门进行平均工资统计(avg)
select department_id,avg(salary)
from t_employees
group by department_id;
4.8.3 查询各个部门,各个岗位的人数
#思路
#1.按照部门编号进行分组(分组依据是department_id)
#2.再按照岗位名称进行分组(分组依据是job_id)
#3.最后针对每个部门的各个岗位进行人数统计(count)
select department_id,job_id,count(employee_id)
from t_employees
group by department_id,job_id;
4.8.4 常见问题
#查询各个部门id,总人数,first_name
select department_id,count(*),first_name
from t_employees
group by department_id;#error
注: 分组查询中,select显示的列只能是分组依据列,或者聚合函数列,不能出现其他列
4.9 分组过滤查询
语法: select 列名 from 表名 where 条件 group by 分组列 having 过滤规则
关键字:
having 过滤规则: 过滤规则定义对分组后的数据进行过滤
4.9.1 统计三个部门的最高工资
#统计60,70,90号部门的最高工资
#思路
#1.确定分组依据(department_id)
#2.对分组后的数据,过滤出部门编号是60,70,90的信息
#3.max()函数处理
select department_id,max(salary)
from t_employees
group by department_id
having department_id in(60,70,90)
#group确定分组依据department_id
#having过滤出60,70,90部门
#select查看部门编号和max函数
4.10 限定查询
select 列名 from 表名 limit 起始行, 查询行数
关键字:
limit offset_start, row_count: 限定查询结果的起始行和总行数
4.10.1 查询前五行记录
#查询表中前五名员工的所有信息
select * from t_employees limit 0,5;
注意: 起始行是从0开始, 代表了第一行,第二个参数代表的是从指定行开始查询几行
4.10.2 查询范围记录
#查询表中从第四条开始, 查询10行
select * from t_employees limit 3,10;
4.10.3 limit经典应用
分页查询: 一页显示10条, 一共查询三页
#思路: 第一页是从0开始, 显示10条
select * from t_employees limit 0,10;
#第二页是从第10条开始, 显示10条
select * from t_employees limit 10,10;
#第二页是从第20条开始, 显示10条
select * from t_employees limit 20,10;
经验: 在分页应用场景中,起始行是变化的, 但是一页显示的条目是不变的
4.11 查询总结
4.11.1 SQL语句编写顺序
select 列名 from 表名 where 条件 group by 分组 having 过滤条件 order by 排序列 (asc|desc) limit 起始行, 总条数
4.11.2 SQL语句执行顺序
from: 指定数据来源表
where: 对查询数据做第一次过滤
group by: 分组
having: 对分组后的数据第二次过滤
select: 查询各字段的值
order by: 排序
limit: 限定查询结果
4.12 子查询(作为条件判断)
select 列名 from 表名 where 条件(子查询结果);
4.12.1 查询工资大于Bruce的员工信息
#1.先查询到Bruce的工资(一行一列)
select salary from t_employees where first_name = 'Bruce'; #工资是6000
#2.查询工资大于Bruce的员工信息
select * from t_employees where salary > 6000;
#3.将1,2两条语句整合
select * from t_employees where salary > (select salary from t_employees where first_name = 'Bruce');
注意: 将子查询"一行一列"的结果作为外部查询的条件, 做第二次查询
子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值判断条件
4.13 子查询(作为枚举查询条件)
select 列名 from 表名 where 列名 in(子查询结果);
4.13.1 查询与名字为King同一部门的员工信息
#1.先查询'King'所在的部门编号(多行单列)
select department_id
from t_employees
where last_name = 'King'; #部门编号80,90
#2.再查询80,90号部门的员工信息
select employee_id,first_name,salary,department_id
from t_employees
where department_id in(80,90);
#3.将1,2合并
select employee_id,first_name,salary,department_id
from t_employees
where department_id in(select department_id from t_employees where last_name = 'King');#N行一列
4.13.2 工资高于'60部门'的所有人的信息
#1.查询 60部门 所有人的工资(多行单列)
select salary from t_employees where department_id = 60;
#2.查询工资高于 60部门所有人的员工信息(高于所有)
select * from t_employees where salary > all(select salary from t_employees where department_id = 60);
#3.查询工资高于 60部门部分员工信息(高于部分)
select * from t_employees where salary > any(select salary from t_employees where department_id = 60);
注意: 当子查询结果集形式为多行单列时可以使用any或all关键字
4.14 子查询(作为一张表)
select 列名 from (子查询的结果集) where 条件;
4.14.1 查询员工表中工资排名前五名的员工信息
#1.先按照员工工资进行排序(排序后的临时表)
select * from t_employees order by salary desc;
#2.再获取临时表中前五名的员工信息
select * from (临时表) limit 0,5;
#3.合并
select * from (select * from employees order by salary desc) as temp limit 0,5;
将子查询"多行多列"的结果作为外部查询的一张表,做第二次查询
注意: 子查询作为临时表,需要为其赋予一个临时表名
4.15 合并查询(了解)
select * from 表名 1 union select * from 表名2
select * from 表名 1 union all select * from 表名2
4.15.1 合并两张表的结果(去除重复记录)
#合并两张表的结果,去除重复记录
select * from t1 union select * from t2;
注意: 合并结果的两张表,列数必须相同, 列的数据类型可以不同
4.15.2 合并两张表的结果(保留重复记录)
#合并两张表的结果, 不去除重复记录(显示所有)
select * from t1 union all select * from t2;
经验: 使用union合并结果集, 会去除两张表中重复的数据
4.16 表连接查询
select 列名 from 表1 连接方式 表2 on 连接条件
4.16.1 内连接查询(inner join on)
#1.查询所有部门的员工信息(不包括没有部门的员工) SQL标准
select * from t_employees inner join t_jobs on t_employees.job_id = t_jobs.job_id;
#2.查询所有部门的员工信息(不包括没有部门的员工) MySQL
select * from t_employees,t_jobs where t_employees.job_id = t_jobs.job_id;
经验: 在MySQL中, 第二种方式也可以作为内连接标准,但是不符合SQL标准
而第一种属于SQL标准, 与其他关系型数据库通用
4.16.2 三表连接查询
#查询所有员工工号,名字,部门名称,部门所在国家ID
SELECT employee_id,t_departments.department_id,t_locations.location_id FROM t_employees
INNER JOIN t_departments
ON t_employees.department_id = t_departments.department_id
INNER JOIN t_locations
ON t_locations.location_id = t_departments.location_id;
4.16.3 左外连接(left join on)
#查询所有员工信息,以及所对应的部门名称(如结果中存在没有部门的员工,用null填充)
SELECT employee_id,first_name,salary,department_name FROM t_employees
left JOIN t_departments
ON t_employees.department_id = t_departments.department_id
注意: 左外连接,是以左表为主表,依次向右匹配,匹配到,返回结果
匹配不到,则返回null值填充
4.16.4 右外连接(right join on)
#查询所有部门信息,以及部门中的所有员工信息(如结果中存在没有员工的部门,用null填充)
SELECT employee_id,first_name,salary,department_name FROM t_employees
right JOIN t_departments
ON t_employees.department_id = t_departments.department_id
注意: 右外连接,是以右表为主表,依次向左匹配,匹配到,返回结果
匹配不到,则返回null值填充
五. DML操作[重点]
5.1 新增(insert)
insert into 表名(列1,列2,列3...) values(值1,值2,值3...);
5.1.1 添加一条消息
#添加一条工作岗位信息
insert into t_jobs
(job_id,job_title,min_salary,max_salary)
values
('java_le','java_lecturer',2500,9000)
#添加一条员工信息
insert into t_employees
(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)
values('207','Steven','Gavin','Gavin','650.501.3876','1998-07-01','SH_CLERK','8000',NULL,'124','50')
注意: 表名后的列名和values里的值要一 一对应(个数顺序,类型)
5.2 修改(update)
update 表名 set 列1 = 新值,列2 = 新值...where 条件;
5.2.1 修改一条消息
#修改编号为100的员工的工资为25000
update t_employees set salary = 25000 where employee_id = '100';
#修改编号为135的员工信息,岗位编号为 ST_MAN 工资为3500
update t_employees set job_id = 'ST_MAN',salary = 3500 where employee_id = '135';
注意: set后多个列名=值,绝大多数情况下都要加where条件,指定修改,否则为整表更新
5.3 删除(delete)
delete from 表名 where 条件;
5.3.1 删除一条信息
#删除编号为135的员工
delete from t_employees where employee_id = '135';
#删除姓Peter,并且名为Hall的员工
delete from t_employees where first_name = 'Peter' and last_name = 'Hall';
注意: 删除时, 如若不加where条件, 删除的是整张表的数据
5.4 清空整表数据(truncate)
truncate table 表名;
5.4.1 清空整张表
#清空t_countries整张表
truncate table t_countries;
注意: 与delete不加where删除整表数据不同, truncate是把表销毁, 再按照原表的格式创建一张新表
六. 数据表操作
6.1 数据类型
MySQL支持多种类型, 大致可以分为三类: 数值, 日期/时间, 和字符串类型, 对于我们约束数据的类型有很大的帮助
6.1.1 数值类型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
| int | 4字节 | (-2147 483 648, 2147 483 647) | (0, 4294 967 295) | 大整数值 |
| double | 8字节 | (-1.797E+308,-2.22E-308) | (0,2.22E-308,1.797E+308) | 双精度浮点数值 |
| double(m,d) | 8字节,m代表长度(整数位+小数位),d代表小数位数 | 同上,受m,d的约束,double(5,2) -999.99~999.99 | 同上,受m,d的约束 | 双精度浮点数 |
| decimal(m,d) | decimal(m,d) | 依赖于m,d的值,m最大值为65 | 依赖于m,d的值,m最大值为65 | 小数值 |
6.1.2 日期类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| date | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| time | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| year | 1 | 1901/2155 | YYYY | 年份值 |
| datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| timestamp | 4 | 1970-01-01 00:00:00/2038结束时间是第2147483647秒北京时间2038-1-19 11:14:07, 格林尼治时间2038年1月19日凌晨03:14:07 | YYYYMMDDHHMMSS | 混合日期和时间值, 时间戳 |
6.1.3 字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| char | 0-255字符 | 定长字符串 char(10) 10个字符 |
| varchar | 0-65535字符 | 变长字符串 varchar(10) 10个字符 |
| blob(binary large object) | 0-65535字符 | 二进制形式的长文本数据 |
| text | 0-65535字符 | 长文本数据 |
char和varchar类型类似, 但它们保存和检索的方式不同, 它们的最大长度和是否尾部空格被保留等方面也不同. 在存储或检索过程中不进行大小写转换
blob是一个二进制大对象, 可以容纳可变数量的数据. 有4种blob类型: TINYBLOB, BLOB, MEDIUMBLOB, 和LONGBLOB,它们只是可容纳值的最大长度不同
6.2 数据表的创建(CREATE)
CREATE TABLE 表名 (
列名 数据类型 约束,
列名 数据类型 约束,
...
列名 数据类型 约束 //最后一行的末尾不加逗号
)charset=utf8 //可根据需要指定表的字符编码集
6.2.1 创建表
| 列名 | 数据类型 | 说明 |
|---|---|---|
| subjectId | INT | 课程编号 |
| subjectName | VARCHAR(20) | 课程名称 |
| subjectHours | INT | 课程时长 |
#创建Subject表
CREATE TABLE `subject`(
subjectId INT,
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=utf8;#指定该表存储数据的字符集
INSERT INTO `subject`(subjectId,subjectName,subjectHours) VALUES (1,'java',20);
6.3 数据表的修改(ALTER)
ALTER table 表名 操作;
6.3.1 向现有表中添加列
#在课程表基础上添加gradeId列
ALTER TABLE `subject` ADD gradeId int;
6.3.2 修改表中的列
#修改课程表中课程名称长度为10个字符
ALTER TABLE `subject` MODIFY subjectName varchar(10);
注意: 修改表中的某列时,也要写全列的名字, 数据类型,约束
6.3.3 删除表中的列
#删除课程表中gradeId列
ALTER TABLE `subject` DROP gradeId;
注意: 删除列时,每次只能删除一列
6.3.4 修改列名
#修改课程表中subjectHours列为classHours
ALTER TABLE `subject` CHANGE subjectHours classHours int;
注意: 修改列名时,在给定列新名称时,要指定列的数据类型和约束
6.3.5 修改表名
#修改学生表的表名subject为sub
ALTER TABLE `subject` rename `sub`;
6.3.6 重置表的自增id
#方法一 清空整张表(表有约束时无法使用)
truncate table
#方法二 删除整张表,修改表的自增起始值为1
DELETE FROM 表名;
ALTER TABLE 表名 AUTO_INCREMENT=1;
6.4 数据表的删除(DROP)
DROP TABLE 表名;
6.4.1 删除学生表
#删除学生表
DROP TABLE `sub`;
七. 约束
问题: 在往已创建表中新增数据时, 可不可以新增两行相同列值的数据?
如果可行, 有什么弊端
7.1 实体完整性约束
表中的一行数据代表一个实体(entity), 实体完整性的作用即是表示每一行数据不重复, 实体唯一
7.1.1 主键约束
PRIMARY KEY 唯一, 标识表中的一行数据,此列的值不可重复,且不能为null
#为表中适用主键的列添加主键约束
CREATE TABLE `subject`(
subjectId INT PRIMARY KEY,#课程编号标识每一个课程编号唯一,且不能为null
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=utf8;
INSERT INTO `subject`(subjectId,subjectName,subjectHours) VALUES (1,'java',20);
INSERT INTO `subject`(subjectId,subjectName,subjectHours) VALUES (1,'java',20);#error 主键 1 已存在
7.1.2 唯一约束
UNIQUE 唯一, 标识表中的一行数据, 不可重复, 可以为null
#为表中列值不允许重复的列添加唯一约束
CREATE TABLE `subject`(
subjectId INT PRIMARY KEY,#课程编号标识每一个课程编号唯一,且不能为null
subjectName VARCHAR(20) UNIQUE,#课程名称唯一
subjectHours INT
)CHARSET=utf8;
INSERT INTO `subject`(subjectId,subjectName,subjectHours) VALUES (1,'java',20);
INSERT INTO `subject`(subjectId,subjectName,subjectHours) VALUES (2,'java',20);#error 课程名称 java 已存在
7.1.3 自动增长列
AUTO_INCREMENT 自动增长, 给主键数值列添加自动增长, 从1开始,每次加1,不能单独使用,和主键配合
#为表中主键列添加自动增长,避免忘记主键ID序号
CREATE TABLE `subject`(
subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长,会从1开始根据添加元素的顺序依次加1
subjectName VARCHAR(20) UNIQUE,#课程名称唯一
subjectHours INT
)CHARSET=utf8;
INSERT INTO `subject`(subjectName,subjectHours) VALUES ('java',20);#课程编号自动从1增长
INSERT INTO `subject`(subjectName,subjectHours) VALUES ('python',20);#第二条编号为2
7.2 域完整性约束
限制列的单元格的数据正确性
7.2.1 非空约束
NOT NULL 非空, 此列必须有值
#课程名称虽然添加了唯一元素, 但是有null值存在的可能, 要避免课程名称为null
CREATE TABLE `subject`(
subjectId INT PRIMARY AUTO_INCREMENT,
subjectName varchar(20) UNIQUE NOT NULL,
sunjectHours INT
)CHARSET=utf8;
insert into `subject`(sunjectName,subjectHours) VALUES('java',20);
insert into `subject`(sunjectName,subjectHours) VALUES('python',20);
insert into `subject`(sunjectName,subjectHours) VALUES(null,20);#error,课程名称约束了非空
7.2.2 默认值约束
default 值 为列赋予默认值,当新增数据不指定值时,书写DEFAULT,以指定的默认值进行填充
#当存储课程信息时,若课程时长没有指定值,则以默认课时 20 填充
CREATE TABLE `subject`(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20
)CHARSET=utf8;
insert into `subject`(sunjectName,subjectHours) VALUES('java',DEFAULT);#课程时长以默认值20填充
7.2.3 引用完整性约束
语法: CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)
详解: FOREIGN KEY 引用外部表的某个列的值, 新增数据时, 约束此列的值必须是引用表中存在的值
#创建专业表
CREATE TABLE speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
specialName VARCHAR(20) UNIQUE NOT NULL,
)CHARSET=utf8;
#创建课程表(课程表的specialId 引用专业表的 id)
CREATE TABLE `subject`(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(20) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20,
specialId INT NOT NULL,
CONSTRAINT fk_subject_specialId FOREIGN KEY(specialId) REFERENCES speciality(id) #引用专业表里的id作为外键,新增课程信息时,约束课程所属的专业
)CHARSET=utf8;
#专业表新增数据
INSERT INTO speciality(specialName) values('java');
INSERT INTO speciality(specialName) values('python');
#课程信息表添加数据
insert into `subject`(subjectName,subjectHours,specialId) VALUES('java',30,1);#专业 id 为 1, 引用的是专业表的java
insert into `subject`(subjectName,subjectHours,specialId) VALUES('python',20,2);#专业 id 为 2, 引用的是专业表的python
注意: 当两张表存在引用关系时,要执行删除操作, 一定要先删除从表(引用表), 再删除主表(被引用表)
7.3 约束创建整合
创建带有约束的表
7.3.1 创建表
| 列名 | 数据类型 | 约束 | 说明 |
|---|---|---|---|
| GradeId | INT | 主键,自动增长 | 班级编号 |
| GradeName | VARCHAR(20) | 唯一,非空 | 班级名称 |
CREATE TABLE Grade(
gradeId INT PRIMARY KEY AUTO_INCREMENT,
gradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
| 列名 | 数据类型 | 约束 | 说明 |
|---|---|---|---|
| student_id | VARCHAR(50) | 主键 | 学号 |
| student_name | VARCHAR(50) | 非空 | 姓名 |
| sex | CHAR(2) | 默认填充'男' | 性别 |
| birthday | DATE | 非空 | 生日 |
| phone | VARCHAR(11) | 无 | 电话 |
| GradeId | INT | 非空,外键约束: 引用班级表的gradeId | 班级编号 |
CREATE TABLE student(
student_id VARCHAR(50) PRIMARY KEY, #学号
student_name VARCHAR(50) NOT NULL, #姓名
sex CHAR(2) DEFAULT '男', #性别
birthday DATE NOT NULL, #生日
phone VARCHAR(11), #电话
gradeId INT NOT NULL, #班级编号
CONSTRAINT fk_student_gradeId FOREIGN KEY(gradeId) REFERENCES Grade(gradeId)
)CHARSET=utf8; #引用Grade表的GradeId列的值为外键,插入时约束学生的班级编号继续存在
INSERT INTO student(student_id,student_name,sex,birthday,phone,gradeId)
VALUES('123','zhangsan','男','1996-09-06','12300001111',1)
注意: 创建关系表时,一定要先创建主表,再创建从表
删除关系表时, 先删除从表, 再删除主表
八. 事务[重点]
8.1 模拟转账
生活中转账是转账方账户扣钱,收款方账户加钱. 我们用数据库操作来模拟现实转账
8.1.1 数据库模拟转账
# A 账户转账给 B 账户1000元
# A 账户减 1000元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1;
#B 账户加1000元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;
上述代码完成了两个账户之间转账的操作
8.1.2 模拟转账错误
# A 账户转账给 B 账户1000元
# A 账户减 1000元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1;
# 断电,异常,出错...
#B 账户加1000元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;
上述代码在减操作后过程中出现了异常或加钱语句出错, 会发现,减钱仍旧是成功的,而加钱失败了
注意: 每条SQL语句都是一个独立的操作, 一个操作执行完成对数据库是永久性的影响
8.2 事务的概念
事务是一个原子操作, 是一个最小执行单位. 可以由一个或多个SQL语句组成, 在同一个事务当中, 所有的SQL语句都成功执行时, 整个事务成功, 有一个SQL语句执行失败, 整个事务都执行失败
8.3 事务的边界
开始: 连接到数据库, 执行一条DML语句, 上一事务结束后, 又输入了一条DML语句,即事务的开始
结束
:
提交:
显示提交: commit;
隐式提交: 一条创建, 删除的语句, 正常退出(客户端退出连接);
回滚:
显示回滚: rollback;
隐式回滚: 非正常退出(断电,宕机), 执行了创建,删除的语句,但是失败了, 会为这个无效的语句执行回滚
8.4 事务的原理
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行效果都会缓存在回滚段中,只有当事务中所有SQL语句均正常结束(commit),才会将回滚段中的数据同步到数据库, 否则无论因为哪种原因失败, 整个事务将回滚(rollback)
8.5 事务的特性
Atomicity(原子性)
表示一个事务内的所有操作是一个整体,要么全部成功,要么全部失败
Consistency(一致性)
表示一个事务内有一个操作失败时,所有的更改过的数据都必须回滚到修改前的状态
Isolation(隔离性)
事务查看数据操作时数据所处的状态, 要么是另一并发事务修改它之前的状态, 要么是另一事务修改它之后的状态, 事务不会查看中间状态的数据
Durability(持久性)
持久性事务完成之后,它对于系统的影响是永久性的
8.6 事务应用
应用环境: 基于增删改语句的操作结果(均返回操作后受影响的行数), 可通过程序逻辑手动控制事务提交或回滚
8.6.1 事务完成转账
# A账户给B账户转账
#1.开启事务
START TRANSACTION;|setAutoCommit=0;#禁止自动提交 setAutoCommit=1 开启自动提交
#2.事务内数据操作语句
UPDATE `account` SET money=money-1000 WHERE id=1;
UPDATE `account` SET money=money+1000 WHERE id=2;
#3.事务内语句都成功了,执行COMMIT;
COMMIT;
#4.事务内如果出现错误,执行ROLLBACK;
ROLLBACK;
注意: 开启事务后,执行的语句均属于当前事务,成功再执行COMMIT,失败要进行ROLLBACK
九. 权限管理
9.1 创建用户
create user 用户名 identified by 密码
9.1.1 创建一个用户
#创建一个zhangsan用户
create user `zhangsan` identified by '123';
9.2 授权
grant all on 数据库.表 to 用户名;
9.2.1 用户授权
#将companydb下所有表的权限都赋给zhangsan
grant all on companydb.* to `zhangsan`;
9.3 撤销权限
revoke all on 数据库.表名 from 用户名
9.3.1 撤销用户权限
#将zhangsan的companydb 的权限删除
revoke all on companydb.* from `zhangsan`;
注意: 撤销权限后,账户要重新连接客户端才会生效
9.4 删除用户
drop user 用户名
9.4.1 删除用户
#删除用户zhangsan
drop user `zhangsan`;
十. 视图
10.1 概念
视图, 虚拟表, 从一个表或多个表中查询出来的表, 作用和真实表一样, 包含一系列带有行和列的数据. 视图中, 用户可以使用select语句查询数据, 也可以用insert, update, delete修改记录, 视图可以使用户操作方便, 并保障数据库系统安全.
10.2 视图特点
优点:
简单化, 数据所见即所得
安全性, 用户只能查询或修改他们所能见得到的数据
逻辑独立性, 可以屏蔽真实表结构变化带来的影响
缺点:
性能相对较差, 简单的查询也会变得稍显复杂
修改不方便, 特别是复杂的聚合视图基本无法修改
10.3 视图的创建
语法: create view 视图名 as 查询数据源表数据;
10.3.1 创建视图
#创建 t_empInfo 的视图, 其视图从 t_employees 表中查询到员工编号,员工姓名, 员工右边,工资
create view t_empInfo
as
select employee_id,first_name,last_name,email,salary from t_employees;
10.3.2 使用视图
#查询 t_empInfo 视图中编号为 101 的员工信息
select * from t_empInfo where employee_id = '101';
10.4 视图的修改
方式一: create or replace view 视图名 as 查询语句
方式二: alter view 视图名 as 查询语句
10.4.1修改视图
#方式1: 如果视图存在则进行修改,反之进行创建
create or replace view t_empInfo
as
select employee_id,first_name,last_name,email,salary from t_employees;
#方式2: 直接对已存在的视图进行修改
alter view t_empInfo
as
select employee_id,first_name,last_name,email,salary from t_employees;
10.5 视图的删除
drop view 视图名;
10.5.1 删除视图
#删除t_empInfo视图
drop view t_empInfo;
注意: 删除视图不会影响原表
10.6 视图的注意事项
注意:
视图不会独立存储数据,原表发生改变,视图也发生改变,没有优化任何查询性能
如果视图包含以下结构中的一种,则视图不可更新
聚合函数的结果
distinct 去重后的结果
group by 分组后的结果
having 筛选过滤后的结果
union,union 联合后的结果
十一. SQL语言分类
数据查询语言 DQL(Data Query Language): select, where, order by, group by, having
数据定义语言 DDL(Data Definition Language): create, alter, drop
数据操作语言 DML(Data Manipulation Language): insert, update, delete
事务处理语言 TPL(Transaction Process Language): commit, rollback
数据控制语言 DCL(Data Control Language): grant, revoke
十二. 综合练习
12.1 创建用户表
#用户表
create table `user`(
userId int primary key auto_increment,
username varchar(20) not null,
`password` varchar(18) not null,
address varchar(100),
phone varchar(11)
)charset=utf8;
#为用户表添加数据
insert into `user`(username,password,address,phone)
values('张三','123','北京昌平沙河','13322456623');
insert into `user`(username,password,address,phone)
values('李四','321','北京海淀','23322456623');
insert into `user`(username,password,address,phone)
values('王五','456','北京朝阳','33322456623');
insert into `user`(username,password,address,phone)
values('刘六','654','北京大兴','43322456623');
12.2 创建分类表
#分类表
create table category(
cid varchar(32) primary key,
cname varchar(100) not null #分类名称
)charset=utf8;
#为分类表添加数据
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');
12.3 创建商品表
#商品表
create table `products`(
`pid` varchar(32) primary key,
`name` varchar(40),
`price` double(7,2),
category_id varchar(32),
constraint fk_products_category_id foreign key(category_id) references category(cid)
)charset=utf8;
#为商品表添加数据
insert into products(pid,`name`,price,category_id) values('p001','联想',5000,'c001');
insert into products(pid,`name`,price,category_id) values('p002','海尔',3000,'c001');
insert into products(pid,`name`,price,category_id) values('p003','雷神',5000,'c001');
insert into products(pid,`name`,price,category_id) values('p004','Jack Jones',800,'c002');
insert into products(pid,`name`,price,category_id) values('p005','真维斯',200,'c002');
insert into products(pid,`name`,price,category_id) values('p006','花花公子',440,'c002');
insert into products(pid,`name`,price,category_id) values('p007','劲霸',2000,'c002');
insert into products(pid,`name`,price,category_id) values('p008','香奈儿',800,'c003');
insert into products(pid,`name`,price,category_id) values('p009','相宜本草',200,'c003');
insert into products(pid,`name`,price,category_id) values('p010','梅明子',200,null);
12.4 创建订单表
#订单表
create table `orders`(
`oid` varchar(32) primary key,
`totalprice` double(12,2), #总计
`userId` int,
constraint fk_orders_userId foreign key(userId) references `user`(userId) #外键
)charset=utf8;
#为订单表添加数据
insert into orders values('o6100',18000.50,1);
insert into orders values('o6101',7200.35,1);
insert into orders values('o6102',600.00,2);
insert into orders values('o6103',1300.26,4);
12.5 创建订单详情表
#订单项表
create table orderitem(
oid varchar(32), #订单id
pid varchar(32), #商品id
num int, #购买商品数量
primary key(oid,pid), #主键
constraint fk_orderitem_oid foreign key(oid) references orders(oid),
constraint fk_orderitem_pid foreign key(pid) references products(pid)
)charset=utf8;
#为订单详情表添加数据
insert into orderitem values('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);
12.6 综合练习一 [多表查询]
12.6.1 查询所有用户的订单
SELECT * FROM `user` INNER JOIN orders ON `user`.userId = orders.userId;
12.6.2 查询用户id为1 的所有订单详情
SELECT * FROM `user`
INNER JOIN orders
ON `user`.userId = orders.userId
INNER JOIN orderitem
ON orders.oid = orderitem.oid
WHERE user.userId = 1;
12.7 综合练习二 [子查询]
12.7.1 查看用户名为张三的订单
SELECT * FROM orders WHERE userId = (SELECT userId FROM `user` WHERE username = '张三');
12.7.2 查询出订单价格大于800的所有用户信息
SELECT * FROM `user` WHERE userId IN(SELECT DISTINCT userId FROM orders WHERE totalprice > 800);
12.8 综合练习三 [分页查询]
12.8.1 查询所有订单信息,每页显示5条数据
#查询第一页
SELECT * FROM orders LIMIT 0,5;
#查询第二页
SELECT * FROM orders LIMIT 5,5;
十三. 其他操作
13.1 修改数据库密码
命令行下输入
mysqladmin -u用户名 -p旧密码 password 新密码
