第二阶段_数据库学习笔记

一,数据库

–1,概述

        专门用来存储数据和管理数据库的一个仓库.英文叫database
        分类: 关系型和非关系型
                1, 关系型数据库: 存的数据都有关系,代表是:Mysql / Oracle…
                2, 非关系型数据库: 存的数据之间有着松散的关系, 代表是: Redis

–2,安装

        1, 服务端: 存储数据,管理数据
                端口号默认是3306,设置密码,服务名叫MySQL,安装路径中不要包含中文!!!
        2, 客户端: 连接服务端,操作服务端的数据

–3,数据库的结构

        数据库 -> 表(字段1…) -> 数据 / 记录

二,SQL语言

–1,概述

        全称是结构化查询语言,专门用来操作数据库的语言,而且是一个标准语言,可以操作任何数据库

–2,SQL的分类

        DML:是指数据库的操纵语言,就是对数据的操作(新增,修改,查询,删除CRUD)
        DDL:是指数据库的定义语言,比如说创建表…
        DCL:是指数据库的控制语言,比如说权限管理
        DQL:是指数据库的查询语言,比如进行复杂的查询语句

三,数据库的常见操作

–1,查询

–2,新建

–3,删库 !!!

四,表的常见操作

–0,使用数据库

–1,查询

–2,创建

        语法: create table 表名(字段名称 字段类型(字段长度),字段2,字段3)

mysql> create table student(
    -> id int(3),
    -> name varchar(10),
    -> age int(3)
    -> );
Query OK, 0 rows affected (0.01 sec)

练习:创建门店表
mysql> create table tb_door(
    -> id int(11),
    -> door_name varchar(100),
    -> tel varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

–3,修改

–4,删除

–5,描述表结构

五,记录的常见操作

–1,查询

语法: select * from 表名

–2,插入

        语法: insert into 表名 values(1,2,3)
        注意:
                1, values后面括号里,要写几个值? 参考表里字段的个数,要和字段的个数匹配
                2, 值的意义是什么呢? 要和字段的顺序位置匹配.每个值交给每个对应字段来保存
                3, 值的写法有一些细节：如果字段是varchar类型,值必须有字符串标识(单引号 / 双引号)

–3,修改

–4,删除

六,总结

        查询所有数据库: show databases;
        创建数据库: create database 库名 default character set utf8;
        删库: drop database 库名;

        查询所有表: show tables;
        新建表: create table 表名(字段名称 字段类型(字段长度),2,3);
                字段长度:整数限制不住,字符串必须在规定长度内否则报错
        删除表: drop table 表名;
        修改表: alter table 表名 add column 字段名 字段类型(长度)
        描述表: desc 表名

        查询所有记录: select * from 表名;
        新增记录: insert into 表名 values(1,2,3);
                要写几个值必须和表里字段的个数匹配. 值的顺序必须和字段顺序匹配.
        删除记录: delete from 表名 ;
        修改记录: update 表名 set 字段名=字段值;

七,数据类型

        1,数字:
                整型 tinyint / int 小数float / double 精确的小数 numeric(5,2) / decimal(5,2)–5是指有5位数字,2是指包含着的小数位数
        2,日期:
                年月日 date 时分秒 time 年月日时分秒 datetime 毫秒数 timestamp时间戳
        3,字符串:
                char和varchar的区别?
                        前者,长度固定,浪费空间,但查询高效.
                        后者,长度可变,节省空间,但查询相对低效

八,字段约束

–1,概述

        是通过不同的方式,给不同的字段添加不同的约束,实现不同效果
        常见的字段约束: 非空约束/唯一约束/主键约束…
        通常在设计表时,就已经设计好了表里一些字段的约束情况

–2,非空约束

        哪个字段,添加了非空约束,哪个字段的值,从此,必须有值不能为空. 使用not null来实现.

–3,唯一约束

        哪个字段,添加了唯一约束,哪个字段的值,从此,必须唯一不能重复. 使用unique来实现.

–4,主键约束

#1.主键约束:每张表都应该有主键,特点是:非空且唯一
CREATE TABLE test6(id INT PRIMARY KEY)
INSERT INTO test6 VALUES(1)
INSERT INTO test6 VALUES(1)#id是主键,值唯一
INSERT INTO test6 VALUES(NULL)#id是主键,值不能为空
SELECT * FROM test6
#主键约束要求,值不能为空而且不能相同!!
#2.问题是:要去关心主键值到底应该写几了呢??
INSERT INTO test6 VALUES(3)
#3.从此以后,交给MySQL数据库来维护主键的值,自增++
CREATE TABLE test7(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10)
)
INSERT INTO test7 VALUES(NULL,'rose');
INSERT INTO test7 VALUES(NULL,'jack');

–5,默认约束

#1.默认约束:给字段添加默认值--用的少!
CREATE TABLE test01(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex CHAR(3) DEFAULT '男'#默认约束
)
#虽然sex设置了默认值,但是只是手动录入时有效,发起insert语句时还是要写具体值的
INSERT INTO test01 VALUES(NULL,'男')

–6,检查约束

#2.检查约束:检查字段的值的合理性
CREATE TABLE test02(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0) #检查约束,了解即可!
)
INSERT INTO test02 VALUES(NULL,10) #ok的
INSERT INTO test02 VALUES(NULL,-10) #会报错,没有通过检查约束

–7,外键约束

#3.外键约束:为了省内存,使用对方表的主键来描述两张表的关系
#情况1:子表里的主键的值 必须 取自于 主表
#情况2:主表里的记录想要删除时,必须保证子表没有引用才行
CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10),
 age INT,
 phone VARCHAR(11)
)
CREATE TABLE tb_user_address(
 user_id INT PRIMARY KEY ,#不能自增!!!
 address VARCHAR(100),
 #1.创建外键FK,描述和1号表的关系
 #foreign key(本表的主键) references 对方表名(对方的主键)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

九,基础函数

MySQL数据库为了提高程序员操作数据的效率,提供了很多函数
常见的有: upper/lower/length/concat/replace/year/month…/round…

–1,LOWER & UPPER

#基础函数:MySQL提供的一些函数
#练习1:查询部门表的所有数据
#查询 所有 from 表名
SELECT * FROM dept
#练习2:查询部门表的所有部门名称
SELECT dname FROM dept
#练习3:查询部门表的所有部门名称和地址
SELECT dname a FROM dept#设置别名
SELECT dname,loc FROM dept
#练习4:查询所有部门名称和大写的名称upper
SELECT dname,UPPER('abc'),UPPER(dname) FROM dept
#练习5:把员工名字转大写
SELECT ename,UPPER(ename) FROM emp
#练习6:把ABC转小写lower
SELECT LOWER('ABC') FROM dept

–2,LENGTH & SUBSTR & CONCAT

#练习7:查询dname的长度
#length:底层会去查询utf8表,规定了一个字母或数字长度为1,一个汉字长度为3
SELECT dname,LENGTH(dname),loc,LENGTH(loc) FROM dept
#练习8:截取部分dname的值
#SUBSTR(a,b,c)-a是字段名称b是开始截取的位置c是截取的长度
SELECT dname,SUBSTR(dname,3) FROM dept
SELECT dname,SUBSTR(dname,3),SUBSTR(dname,3,3) FROM dept
#练习9:把dname的值拼接hello
#concat(1,2,3)-拼接字符串,1是字段名称2是第一次拼接的内容3是继续要拼接的内容
SELECT dname,CONCAT(dname,'hello',100) FROM dept

–3,REPLACE & IFNULL & ROUND

#练习10:把dname的a替换成666
#replace(1,2,3)-替换字符串,1是字段名称2是要被替换掉的3是新值
SELECT dname,REPLACE(dname,'a','666') res FROM dept
#练习11:查询员工的奖金
#ifnull(1,2)-对null元素的替换操作1是字段名称2是null要被换成谁
SELECT comm,IFNULL(comm,0) FROM emp
#练习12:统计员工的月薪
SELECT sal,comm,sal+IFNULL(comm,0) FROM emp
#练习13:对小数的精确处理
#round四舍五入取整 & ceil向上取整 & floor向下取整
SELECT comm,ROUND(comm),CEIL(comm),FLOOR(comm) FROM emp
SELECT comm,ROUND(comm,2) FROM emp#四舍五入,并保留一个小数

-4,日期函数 & 转义符号

#练习14:对日期数据的精确处理
#now & year & month & day & hour & minute & second
SELECT NOW()#获取当前的系统时间
SELECT YEAR('2000-1-1') #获取日期中的年
SELECT YEAR( NOW() ) #获取当前年
SELECT YEAR( NOW() ), MONTH( NOW() ), DAY( NOW() )
SELECT HOUR( NOW() ), MINUTE( NOW() ), SECOND( NOW() )

#转义字符,是指在SQL语句中有特殊函数的一些符号,需要\转义
SELECT 'xi\'an'
SELECT "xi'an" 

十,条件查询

–1,DISTINCT & WHERE

#1.distinct去重
#练习1:查询部门地址
SELECT DISTINCT loc  FROM dept 
#2.where
#练习2:查询编号=1的部门信息
#     查所有    表名  查询条件(字段名称=字段值)
SELECT * FROM dept WHERE deptno=1
#练习3:查询办公地址在二区的部门名称
SELECT dname FROM dept WHERE loc='二区' #办公地址在二区的
#练习4:查询工资>8000的员工信息
SELECT * FROM emp WHERE sal>8000 #工资>8000
#练习5:查询岗位是员工并且工资>3000的员工姓名
SELECT ename FROM emp 
WHERE job='员工' AND sal>3000 #条件间的并且关系
#练习6:查询工资=3000 工资=8000的员工信息
SELECT * FROM emp 
#where sal=3000 or sal=8000#条件间的或者关系
WHERE sal IN(3000,8000)#in子句,效果语义同上

–2,模糊查询

#3.like模糊查询,通常使用%通配0~n个字符
#练习7:查询名字里包含a的员工信息
SELECT * FROM emp WHERE ename LIKE '%a%'#包含a的
#练习8:查询工作岗位中有总字的员工姓名
SELECT ename,job FROM emp WHERE job LIKE '%总%'#包含总
#练习9:查询工作岗位以总开头的员工姓名
SELECT ename,job FROM emp WHERE job LIKE '总%'#以总开头,高效
SELECT ename,job FROM emp WHERE job LIKE '%总'#以总结尾

–3,null & between and

#4.null
#练习10:查询没有奖金的员工的信息
SELECT * FROM emp WHERE comm IS NULL
#练习11:查询有奖金的员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL
#5.between and 在区间范围内
#练习12:查询工资在[5000,10000]的员工信息
SELECT * FROM emp 
#where sal>5000 and sal<10000#更灵活的表达条件
WHERE sal BETWEEN 5000 AND 10000 #效果语义同上,两边都包含
#练习13:查询2015年至2019年入职的员工信息
SELECT * FROM emp 
#WHERE hiredate>='2015-1-1' AND hiredate<='2019-12-31'
WHERE YEAR(hiredate) BETWEEN 2015 AND 2019

–4,order by

#练习:条件查询CRUD
#练习1:修改1号部门的名称和地址
UPDATE dept SET dname='java开发部',loc='北京'
WHERE deptno=1
#练习2:删除3号部门的数据
DELETE FROM dept WHERE deptno=3
#1.order by排序,字典顺序,默认就是升序asc,降序desc
#练习3:把员工按照名字排序
SELECT * FROM emp ORDER BY ename #a~z
#练习4:把员工按照工资排序
SELECT * FROM emp ORDER BY sal DESC #降序
#练习5:把员工按照入职时间排序
SELECT * FROM emp ORDER BY hiredate #升序排

–5,limit

#2.limit限制,分页
#练习6:只展示前两条员工信息
SELECT * FROM emp LIMIT 2 #展示的条数..
SELECT * FROM emp LIMIT 2,3
#m,n,m是从第m+1条开始展示,n是要展示的条数
#练习7:查询工资最高的员工信息
SELECT * FROM emp ORDER BY sal DESC LIMIT 1 
#先排序再分页,   按照工资降序排序   只取第一条

–6,统计

#练习8:统计2019年入职的员工的工资
SELECT sal FROM emp WHERE YEAR(hiredate)=2019
#练习9:统计员工岗位的奖金
SELECT comm FROM emp WHERE job='员工'
#练习10:统计员工的年薪
SELECT sal,comm,sal*16+IFNULL(comm,0)*16 年薪 FROM emp

十一,聚合函数

–1,概述

可以把一列的数据聚合起来,继续分析
常见的聚合函数: max min sum avg count

–2,测试

#聚合函数:count / max / min / sum / avg
#练习1:查询员工的平均工资
SELECT sal FROM emp #5个结果
SELECT AVG(sal) FROM emp #1个结果
#练习2:统计岗位是员工的平均工资
SELECT AVG(sal) FROM emp WHERE job='员工'
#练习3:统计员工的最高工资
SELECT MAX(sal) FROM emp
#练习4:统计岗位是员工的最高工资
SELECT MAX(sal) FROM emp WHERE job='员工'
#练习5:统计员工的最低工资
SELECT MIN(sal) FROM emp
#练习6:统计19年入职的员工的最低工资
SELECT MIN(sal) FROM emp WHERE YEAR(hiredate)=2019
#练习7:统计19年入职的员工的总工资
SELECT SUM(sal) FROM emp WHERE YEAR(hiredate)=2019
#练习8:统计员工的总人数
SELECT COUNT(ename) FROM emp#不推荐使用参数是字段名
SELECT COUNT(comm) FROM emp#不准确,不统计null元素
SELECT COUNT(1) FROM emp #推荐!!
SELECT COUNT(*) FROM emp #推荐!!
#练习9:统计2号部门的员工总人数
SELECT COUNT(1) FROM emp WHERE deptno=2
#练习10:统计2019年以前入职的员工总人数
SELECT COUNT(*) FROM emp WHERE YEAR(hiredate)<2019

#查询平均工资
SELECT AVG(sal),sal FROM emp
#混合列:是指,查询的结果中包含着聚合列和非聚合列
#聚合列是指用了聚合函数的列
#非聚合列是指没用聚合函数的列
#使用分组

十二,分组

–1,group by

#查询平均工资
SELECT AVG(sal),sal FROM emp
#1.报错,因为出现了混合列:是指,查询的结果中包含着聚合列和非聚合列
#聚合列是指用了聚合函数的列
#非聚合列是指没用聚合函数的列
#2.使用分组来解决报错:job  deptno year
#练习1:统计每个岗位的员工人数
SELECT job,COUNT(1) FROM emp
GROUP BY job #分组,按照合理维度分组
#口诀1:什么时候要分组??查询的结果中出现了混合列!
#练习2:统计每个岗位的平均薪资
SELECT AVG(sal),job FROM emp
GROUP BY job
#练习3:统计每个部门的员工人数
SELECT COUNT(1) a,deptno b FROM emp
GROUP BY b #分组时可以使用别名
#练习4:统计每年入职的员工人数
SELECT COUNT(1) a,YEAR(hiredate) b FROM emp
#GROUP BY year(hiredate)
GROUP BY b

–2,having

#分组后的过滤:group by ... having
#练习5:统计每年入职的员工人数,只要2015年以后的
SELECT COUNT(1) a,YEAR(hiredate) b FROM emp
GROUP BY b
HAVING b>2015
#练习6:统计每个部门的平均薪资,只要>5000的
SELECT AVG(sal),deptno FROM emp
GROUP BY deptno
HAVING AVG(sal)>8000
#练习7:统计员工表中deptno出现的次数
SELECT deptno,COUNT(1) FROM emp
#口诀2:按照什么分组合理呢??通常按照非聚合列分组
GROUP BY deptno
HAVING COUNT(1)>1

十三,事务

–1,概述

        1,英文叫transaction,主要作用是用来保证多条SQL,要么全成功要么全失败.
        2,四大特征:ACID
                原子性: 同一个事务里的多条SQL语句,是一个原子密不可分,要不全成功,要不全失败
                一致性: 保证多台服务器里的数据是一致的(分布式系统)
                隔离性: 数据库为了提高操作的效率允许高并发的 访问,并采用了隔离性保证了数据的安全性(采用锁机制)
                持久性: 是指,我们对数据库的操作(增删改)是持久生效的

        3,隔离级别:
                read uncommitted: 读未提交,安全性最差,但是效率高.
                read committed: 读已提交,安全性有所提升,但是效率降低一些.也是Oracle数据库的默认隔离级别
                repeatable read : 可重复读,安全性有所提升,但是效率又会低一些.也是MySQL数据库的默认隔离级别
                Serializable: 串行化,安全性最高,但是性能最低

–2,事务管理的方式

        方式1, 使用MySQL数据库为我们提供的,自动事务管理. 默认会为每条SQL提供事务.
        方式2, 手动管理事务,必须有两个过程: 开启事务 … 结束事务(commit / rollback)
        测试3, 采用 方式2 来模拟事务的管理过程:
        窗口1:

mysql> start transaction;  #开启事务
mysql> insert into dept values(10,'php','bj');  #执行SQL
mysql> commit;  #提交事务

        窗口2:

mysql> use cgb211101;
mysql> select * from dept; #查询(1号窗口提交后,2号窗口才能查到)

十四,多表联查

准备表和数据

create table courses
(
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(3) not null,
primary key (cno)
);
create table scores
(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10,1) not null,
primary key (sno, cno)
);

create table students
(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5),
primary key (sno)
);

create table teachers
(
tno varchar(3) not null,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),
primary key (tno)
);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

十五,笛卡尔积

–1,概述

本质上就是把多张表,联合查询.要求,多张表用逗号隔开

–2,测试

#多表联查:
#1.笛卡尔积
#练习1:查询部门表和员工表里的所有数据
SELECT * FROM dept,emp
#问题:产生了庞大的结果集,出现了重复的数据
#练习2:查询部门表和员工表里的所有数据,添加过滤条件
SELECT * FROM dept,emp
WHERE dept.deptno=emp.deptno; #描述了两个表的关系
#练习3:计算计算机导论课程所得的总分
      #聚合函数(表名.字段名)    表1,表2
SELECT SUM(scores.degree) FROM courses,scores
#描述了两个表的关系(表名.字段名)
WHERE courses.cno=scores.cno
AND courses.cname='计算机导论'#真正的业务条件
#练习4:查询学员陆君的总得分 sno
SELECT SUM(scores.degree) FROM scores,students
WHERE scores.sno=students.sno#描述了两个表的关系(表名.字段名)
AND students.sname='陆君'

十六,连接查询

–1,概述

本质上就是把多张表,联合查询.要求,多张表用join连接

–2,测试

#2.连接查询:join连接表,用on描述表关系
#练习5:统计陈冰老师能讲的课程名称
SELECT courses.cname FROM teachers JOIN courses
ON teachers.tno=courses.tno#描述了两个表的关系
WHERE teachers.tname='陈冰'#真正的业务条件
#练习6:统计操作系统课程的总得分
SELECT SUM(scores.degree) FROM courses JOIN scores
ON courses.cno=scores.cno#描述了两个表的关系
WHERE courses.cname='操作系统'#真正的业务条件
#面试题:三种连接查询的区别?inner join/left join/right join
#inner join内连接,取两个表的交集的数据
#left join左外连接,取左表的所有和右表里满足条件的数据
#right join右外连接,取右表的所有和左表里满足条件的数据

#中午,自己测试三种区别???
SELECT * FROM 
dept INNER JOIN emp #内连接,取两张表的交集
ON dept.deptno=emp.deptno
SELECT * FROM 
dept LEFT JOIN emp 
#左连接,取左表的所有和右表满足条件的,不满足的填充null
ON dept.deptno=emp.deptno
SELECT * FROM 
emp RIGHT JOIN dept
#右连接,取右表的所有和左表满足条件的,不满足的填充null
ON dept.deptno=emp.deptno

十七,子查询

–1,概述

又叫嵌套查询,是指,把第一次的查询结果,作为第二次的查询条件,继续发起查询语句.

–2,测试

#3.子查询
#练习1:查询research部门的员工信息
#第一次:查部门表,根据部门名称查部门编号
SELECT deptno FROM dept WHERE dname='research'
#第二次:查员工表,根据部门编号查询员工信息
SELECT * FROM emp WHERE deptno=2
#子查询:
SELECT * FROM emp WHERE deptno=(
  SELECT deptno FROM dept WHERE dname='research'
)
#练习2:查询tony所在的部门名称
#第1次:根据员工名字查部门编号
SELECT deptno FROM emp WHERE ename='tony'
#第2次:根据部门编号查部门名称
SELECT dname FROM dept WHERE deptno=2
#子查询:
SELECT dname FROM dept WHERE deptno=(
 SELECT deptno FROM emp WHERE ename='tony'
)
#练习3:查询java开发部和research部门的员工姓名
#第1次:根据部门名称查部门编号
SELECT deptno FROM dept 
WHERE dname='java开发部' OR dname='research'
#第2次:根据编号查员工姓名
SELECT ename FROM emp 
#where deptno=1 or deptno=2
WHERE deptno IN(1,2) #效果同上
#子查询:第一次查到了多个结果,第二次查就要用in
SELECT ename FROM emp WHERE deptno IN(
 SELECT deptno FROM dept 
  WHERE dname='java开发部' OR dname='research'
)

十八,扩展:索引

–1,概述

用来提高数据库的查询效率.
分类:
1,单值索引: 是指一个索引只包含着一个字段/列
2,复合索引: 是指一个索引包含着多个字段/列
3,唯一索引: 是一种特殊的单值索引,一个索引只包含着一个字段/列,这个字段的值要唯一

–2,测试

#索引:提高查询效率,建议给经常用来查询的字段加索引
#1.查看索引(主键自带索引)
SHOW INDEX FROM students
#2.创建单值索引
#create index 索引名 on 表名(字段名)
CREATE INDEX index1 ON students(sname)
#3.使用索引(按照索引列去查)
SELECT * FROM students WHERE sname='陆君'
#4.查看SQL的执行性能/计划(只想关注你的SQL是否用到了索引)
EXPLAIN #看执行结果里的key
SELECT * FROM students WHERE sname='陆君'

#5.创建唯一索引:找到合适的列,值要唯一
#语法:create unique index 索引名 on 表名(字段名)
#create unique index index2 on students(ssex)#不让加,ssex值大量重复
CREATE UNIQUE INDEX index2 ON students(sno)
SHOW INDEX FROM students#查看索引
EXPLAIN SELECT * FROM students WHERE sno=101 #使用索引,索引失效!!
#!!6.创建复合索引:一个索引包含着多个列
CREATE INDEX index3 ON emp(ename,job,deptno)
SHOW INDEX FROM emp#查看索引
#使用索引,最左特性(查询条件里必须包含最左元素)
EXPLAIN SELECT * FROM emp WHERE ename='jack'#复合索引生效
EXPLAIN SELECT * FROM emp WHERE job='总监'#复合索引失效!
EXPLAIN SELECT * FROM emp WHERE deptno=2#复合索引失效!
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='总监'#生效
EXPLAIN SELECT * FROM emp WHERE job='总监' AND ename='jack'#生效
EXPLAIN SELECT * FROM emp WHERE job='总监' OR ename='jack'#失效!
#删除索引
ALTER TABLE emp DROP INDEX index3

–3,总结

1,优点:
大大提高了查询效率
本质上数据库会为索引列的数据进行排序,快速查询
2,缺点:
本身索引是一个单独的表,也需要占空间的
索引适合查询的业务,但是,也需要同步更新修改一些新的数据,需要一定的时间
3,原则:
什么时候加索引? 频繁的按照一个规则去查询的数据,就应该考虑添加索引
给谁加索引? 给那些经常作为查询条件的字段添加索引
加啥索引? 索引是有分类的, 单值索引 / 唯一索引 / 复合索引,看情况选择不同的索引类型
如何查看SQL的执行计划/性能? explain

十九,扩展 视图

–1,概述

视图:就是一张特殊的表,用来缓存查询的结果

–2,测试

#查询名字里有a的员工的信息
SELECT * FROM emp WHERE ename LIKE '%a%'
#1,创建 
#语法:create view 视图名 as 查询的SQL语句
CREATE VIEW empview AS 
  SELECT * FROM emp WHERE ename LIKE '%a%'
#2,使用
SELECT * FROM empview

–3,总结

#好处是:
#提高SQL的复用性.
#屏蔽了真实的业务表的复杂性
#被所有人共享
#坏处是:
#视图一旦创建成功,就无法被优化
#视图中存了和业务中大量重复的数据,"浪费空间"
#视图通常用来查询,不便于更新

二十,扩展 SQL优化

–1,测试

#1.使用索引的细节:
SHOW INDEX FROM students#查看索引
EXPLAIN SELECT * FROM students WHERE sname LIKE '%王%'#失效!
EXPLAIN SELECT * FROM students WHERE sname LIKE '王%'#生效
EXPLAIN SELECT * FROM students WHERE sname LIKE '%王'#失效!

EXPLAIN SELECT * FROM students WHERE sno=103#失效的,因为sno是varchar类型
EXPLAIN SELECT * FROM students WHERE sno='103' #生效

#2.批量插入:优化事务管理
INSERT INTO dept VALUES(NULL,'ios','guangzhou');
INSERT INTO dept VALUES(NULL,'ios2','guangzhou2');
INSERT INTO dept(dname,loc) VALUES('ios3','guangzhou3');
INSERT INTO dept(loc) VALUES('guangzhou4');
#一次性向数据库插入三条数据,只需要一次事务管理
INSERT INTO dept(loc) VALUES('guangzhou5'),('beijing'),('shanghai');

–2,总结

1, 查询语句中的*尽量换成 字段名称
2, 查询条件where中,尽量用and不用or. 尽量把查询条件的范围控制到最小
3, 查询条件中,尽量用= 别用!= <> , 尽量别用in
4, 表设计时,字段类型尽量用varchar代替char, 尽量用数字代替字符串
5, 索引: 单表中的索引最好控制在5个以内,
模糊查询时,只有一种可以让索引生效: 确定开始元素 ‘abc%’
写SQL时注意字符串的标准写法,
where name=123,name的索引会失效
where name=‘123’,name的索引会生效
注意复合索引的最左特性,如果不遵循会导致复合索引失效
6, 多表联查: 可以使用三种方式 笛卡尔积/连接查询/子查询
尽量不要有太多表联查,阿里的开发规范规定了最多三张表
连接查询相对高效,最高效的是内连接,因为内连接只是查交集
但是,在工作中,使用左连接是最多的,也会使用子查询

二十一,扩展 三范式

–1,概述

对于数据库的设计人员,必须要遵循的规范,就叫做范式.
常见的范式有三范式: 1NF 2NF 3NF
好处是: 可以让数据减少冗余

–2,三范式的规则

第一范式: 1NF, 是指 表里的字段值不能再被分割了
第二范式: 2NF, 是指 遵循了1NF的基础上,再遵循2NF.
是指 表里都应该设计主键字段(主属性),
其他的非主属性,应该都围绕着主属性展开描述.
第三范式: 3NF, 是指 遵循了前两范式
是指 字段之间的依赖性是直接的,而不是还要间接的产生依赖,导致查询关闭变得复杂

二十二,JDBC

–1,概述

全称叫java database connectivity,专门用来完成 利用一段java程序 来操作 数据库 的事情.
是一套标准,是一套规范. 本质上就是一个jar包(一堆的java工具类)

–2,使用步骤

1, 找到jar包,并拷贝进项目
2, 利用工具类 通过java连接数据库 (用户名root 密码root 数据库名cgb211101 数据库的端口号3306)
3, 通过java程序 发起SQL语句
4, 数据库把执行结果, 返回给java程序

–3,入门案例

1, 找到jar包,并拷贝进项目

创建java工程: File - New - Project - 选择java - next - next - 输入工程名 - Finish
导入jar包: 找到你的jar包,复制…选中idea里的项目,粘贴…
编译jar包: 选中jar包,右键,add as library…

2, 编写代码

package cn.tedu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//利用jdbc,操作数据库
//需求:查询部门表的所有数据
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取数据库的连接
        //String url = "协议://数据库的服务器的IP地址:端口号/数据库名";
        String url = "jdbc:mysql://localhost:3306/cgb211101";
        Connection c = DriverManager.getConnection(url,"root","root");
        //3,获取传输器
        Statement s = c.createStatement();
        //4,执行SQL--查询部门表的所有数据
        ResultSet r = s.executeQuery("select * from dept");//执行查询的SQL
        System.out.println("java程序与数据库连接成功!!");
        //5,处理结果--遍历结果
        //next()从来判断,只要r里有数据就返回true.没数据就返回false
        while( r.next() ){
            //获取不同类型的数据getXxx()
            int deptno = r.getInt(1); //获取第N列的值
            String dname = r.getString(2);
            String loc = r.getString(3);
            System.out.println(deptno+dname+loc);
        }
        //6,关闭资源close
        r.close();//关闭结果集resultset
        s.close();//关闭传输器statement
        c.close();//关闭连接Connection
    }
}

二十三,SQL攻击

–1,概述

1,问题:当用户输入特殊值: jack’#时,甚至不需要密码也能登录
2,产生的原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了…
SELECT * FROM USER WHERE NAME=‘jack’ #’ AND pwd=‘123456’
现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号#,导致了,SQL语义发生改变
3,哪里出现的问题?Statement传输器不安全,低效
4,解决方案?使用新的传输器PreparedStatement代码现有的Statement
5,PreparedStatement工具,安全,高效.而且SQL写法简洁.
PreparedStatement把特殊符号#当做普通文本字符在使用,没有注释的意思了

–2,改造用户登录

package cn.tedu.jdbc;

import java.sql.*;
import java.util.Scanner;

//写jdbc的代码,模拟用户登录的过程
//本质上,就是拿到用户在浏览器输入的账号和密码,利用jdbc,去查库
//1,问题:当用户输入特殊值: jack'#时,甚至不需要密码也能登录
//2,产生的原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了...
//SELECT * FROM USER WHERE NAME='jack'#' AND pwd='123456'
//现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号#
//导致了,SQL语义发生改变
//3,哪里出现的问题?Statement传输器不安全,低效
//4,解决方案?使用新的传输器PreparedStatement代码现有的Statement
//5,PreparedStatement工具,安全,高效.而且SQL写法简洁.
//PreparedStatement把特殊符号当做普通文本字符在使用,没有注释的意思了
public class Test4 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
//        Statement s = c.createStatement();
//        String sql = "SELECT * FROM USER WHERE NAME='"+a+"' AND pwd='"+b+"'";
        System.out.println("请输入账号: ");
        String a = new Scanner(System.in).nextLine();
        System.out.println("请输入账密码: ");
        String b = new Scanner(System.in).nextLine();
        //新的传输器,执行的SQL有新写法--sql骨架
        String sql = "SELECT * FROM USER WHERE NAME=? AND pwd=?";
        //3,获取 新的传输器--安全,高效
        PreparedStatement s = c.prepareStatement(sql);
        //给SQL绑定参数
        s.setString(1,a);//给第一个问号,设置a的值
        s.setString(2,b);//给第二个问号,设置b的值
        //4,执行SQL语句
        ResultSet r = s.executeQuery();

        //5,处理结果集(根据用户名和密码去查,只会查出来一条记录)
        if(r.next()){//如果查到了数据,才表示库里有这个用户信息
            System.out.println("恭喜您,登录成功~~");
        }else{//如果没查到数据,
            System.out.println("请重新输入....");
        }
        //6,释放资源
        r.close();
        s.close();
        c.close();


    }
}

–3,练习: 用新的传输器查询

package cn.tedu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

//利用新的传输器,查询部门编号为1的数据
public class Test5 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //3,获取传输器,并执行SQL骨架
        String sql="select * from dept where deptno = ?";
        PreparedStatement s = c.prepareStatement(sql);
        //设置SQL的参数--是指给第几个问号,设置什么值
        s.setObject(1,1);
        //4,执行SQL
        ResultSet r = s.executeQuery();//执行查询的SQL
        //5,解析结果集
        while(r.next()){
            Object deptno = r.getObject(1);//获取第1列的值
            Object dname = r.getObject(2);//获取第2列的值
            Object loc = r.getObject(3);//获取第3列的值
            System.out.println(""+deptno+dname+loc);
        }
        //6,释放资源
        r.close();
        s.close();
        c.close();
    }
}

二十四,优化: 提供jdbc的工具类

–1,创建工具类

package cn.tedu.jdbc;

import java.sql.*;

//充当了jdbc的工具类,抽取一些共性代码
public class JDBCUtils {
    /**
     * 释放资源
     * @param r 结果集
     * @param s 传输器
     * @param c 连接器
     */
    static public void close(ResultSet r, PreparedStatement s,Connection c){
        if(r != null){//防止了空指针异常
            try {
                r.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                r = null;//手动置空,等着GC进行垃圾回收
            }
        }
        if(s != null) {//防止空指针异常
            try {
                s.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                s = null;//手动置空,等着GC进行垃圾回收
            }
        }
        if(c != null) {//防止空指针异常
            try {
                c.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                c = null;//手动置空,等着GC进行垃圾回收
            }
        }
    }

    /**
     * 获取数据库的连接
     * @return 将给调用者返回一个和数据库连接的对象Connection
     * @throws Exception
     * static:保证资源在内存中,贮存的时间长.只会加载一次节省内存
     * public:工具类可以被所有人使用,最大的访问权限方便调用来调用
     */
    static public Connection get() throws Exception{
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //把获取到的数据库的连接,返回给调用者
        return c;
    }
}

–2,使用工具类(用新的传输器新增)

package cn.tedu.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

//利用新的传输器结合着工具类,新增一个用户信息
public class Test6 {
    public static void main(String[] args) throws Exception {
        //1,利用工具类,来获取数据库的连接
        Connection c = JDBCUtils.get();
        //2,获取传输器,执行SQL
        String sql="insert into user values(null,?,?)";
        PreparedStatement p = c.prepareStatement(sql);
        //给SQL绑定参数
        p.setObject(1,"jerry");
        p.setObject(2,"123");
        //3,执行SQL
        p.executeUpdate();//执行增删改的SQL,返回一个影响行数(通常不处理)
        //4,释放资源
        p.close();
        c.close();
    }
}

–3,改造上面的练习(修改资源释放的代码)

package cn.tedu.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

//利用新的传输器结合着工具类,新增一个用户信息
public class Test6 {
    public static void main(String[] args) {
       //扩大变量的作用范围:为了让try  catch  finally都能用
       Connection c = null;
       PreparedStatement p = null;
       try {
            //1,利用工具类,来获取数据库的连接
            c = JDBCUtils.get();
            //2,获取传输器,执行SQL
            String sql="insert into user values(null,?,?)";
            p = c.prepareStatement(sql);
            //给SQL绑定参数
            p.setObject(1,"jerry");
            p.setObject(2,"123");
            //3,执行SQL
            p.executeUpdate();//执行增删改的SQL,返回一个影响行数(通常不处理)
       }catch (Exception e){
           System.out.println("数据插入失败!!");
       }finally {//保证一定会被执行的代码
           //利用工具类close完成释放资源(新增业务,没有结果集,传入null就可以了)
           JDBCUtils.close(null,p,c);
       }
    }
}