Mysql

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

–2,安装
1, 服务端: 存储数据,管理数据
端口号默认是3306,设置密码,服务名叫MySQL,安装路径中不要包含中文!!!
2, 客户端: 连接服务端,操作服务端的数据
两种形式: DOS窗口 / 可视化工具sqlyog

–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,描述表结构
–6,练习
mysql> create table tb_order_detail(
    -> id int(11),
    -> order_id int(11),
    -> num tinyint(4),
    -> item varchar(30),
    -> price double
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_order_detail;
五,记录的常见操作
–1,查询
语法: select * from 表名
–2,插入
语法: insert into 表名 values(1,2,3)
注意:
1, values后面括号里,要写几个值? 参考表里字段的个数,要和字段的个数匹配
2, 值的意义是什么呢? 要和字段的顺序位置匹配.每个值交给每个对应字段来保存
3, 值的写法有一些细节??? 如果字段是varchar类型,值必须有字符串的标识(单引号 / 双引号)
–3,修改
–4,删除
六,总结:
–1,常用命令:
查询所有数据库: 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,细节
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来实现.

一,sqlyog工具的使用
–1,新建连接
找到了压缩包,进行解压,进去找一个exe应用程序,双击.
填入正确密码 — 连接测试 — 连接

–2,数据库
直接右键 – 新建数据库 – 设置数据库名/选成utf8的编码表 – 创建

–3,表
选中Tables – 右键 – 新建表 – 设计字段名称/字段类型/字段长度 – 创建

–4,记录
选中表 – 右键 – 打开表 – 可以直接录入数据(修改/删除) – 执行SQL看结果

–5,练习,创建user表
二,字段约束
–1,主键约束
#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');

三,基础函数
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" 

四,条件查询
SQL中可以通过条件来查询数据,满足条件的数据就查出来,没满足的就查不出来.
–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
一,条件查询
–1,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 #升序排

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 
#先排序再分页,   按照工资降序排序   只取第一条

3,统计

#练习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号窗口才能查到)
五,字段约束
–1,默认约束
哪个字段添加了默认约束,哪个字段的值就有了默认值,使用default来实现.

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

2,检查约束
哪个字段添加了检查约束,哪个字段的值,就要通过检查才能够保存成功.用的更少!了解就行!

#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) #会报错,没有通过检查约束

3,外键约束
#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)
)

一,多表联查
–1,准备表和数据
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:查询可以讲高等数学的老师的名字
#笛卡尔积
SELECT tname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述两个表的关系
AND courses.cname='高等数学'
#连接查询
SELECT tname FROM teachers a INNER JOIN courses b
ON a.tno=b.tno#描述两个表的关系
WHERE b.cname='高等数学'
#子查询
SELECT tname FROM teachers WHERE tno=(
 SELECT tno FROM courses WHERE cname='高等数学'
)
#练习2:查询学员曾华的总得分
#笛卡尔积
SELECT SUM(a.degree) FROM scores a,students b
WHERE a.sno=b.sno  #描述两个表的关系
AND  b.sname='曾华' #业务需求
#连接查询
SELECT SUM(a.degree) FROM scores a JOIN students b
ON a.sno=b.sno  #描述两个表的关系
WHERE  b.sname='曾华' #业务需求
#子查询
SELECT SUM(degree) FROM scores WHERE sno=(
 SELECT sno FROM students WHERE sname='曾华'
)
#查询高于平均工资的员工信息
SELECT * FROM emp WHERE sal>(
 SELECT AVG(sal) FROM emp
 
 六,扩展:索引
–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,测试
#练习1:查询research部门的所有员工姓名和工资
#笛卡尔积
SELECT a.ename,a.sal FROM emp a,dept b
WHERE a.deptno=b.deptno  #两个表的关系
AND b.dname='research'#业务条件
#连接查询
SELECT a.ename,a.sal FROM emp a JOIN dept b
ON a.deptno=b.deptno  #两个表的关系
WHERE b.dname='research'#业务条件
#子查询
SELECT ename,sal FROM emp WHERE deptno=(
 SELECT deptno FROM dept WHERE dname='research'
)

#练习2:查询jack所在的部门信息
#笛卡尔积
SELECT b.* FROM emp a,dept b
WHERE a.deptno=b.deptno #两个表的关系
AND a.ename='jack' #业务条件
#连接查询
SELECT b.* FROM emp a JOIN dept b
ON a.deptno=b.deptno #两个表的关系
WHERE a.ename='jack' #业务条件
#子查询
SELECT * FROM dept WHERE deptno=(
 SELECT deptno FROM emp WHERE ename='jack'
)

#练习3:查询总监的部门信息
#笛卡尔积
SELECT a.* FROM dept a,emp b
WHERE a.deptno=b.deptno#两个表的关系
AND b.job='总监' #业务条件
#连接查询
SELECT a.* FROM dept a JOIN emp b
ON a.deptno=b.deptno#两个表的关系
WHERE b.job='总监' #业务条件
#子查询
SELECT * FROM dept WHERE deptno=
( SELECT deptno FROM emp WHERE job='总监')
#练习4:查询李军的平均分
#笛卡尔积
SELECT AVG(b.degree) FROM students a,scores b
WHERE a.sno=b.sno
AND a.sname='李军'
#连接查询
SELECT AVG(b.degree) FROM students a JOIN scores b
ON a.sno=b.sno
WHERE a.sname='李军'
#子查询
SELECT AVG(degree) FROM scores WHERE sno=(
 SELECT sno FROM students WHERE sname='李军'
)
#练习5:查询陈冰能讲的课程名
#笛卡尔积
SELECT a.cname FROM courses a,teachers b
WHERE a.tno=b.tno
AND b.tname='陈冰'
#连接查询
SELECT a.cname FROM courses a JOIN teachers b
ON a.tno=b.tno
WHERE b.tname='陈冰'
#子查询
SELECT cname FROM courses WHERE tno=(
 SELECT tno FROM teachers WHERE tname='陈冰'
)

二,扩展 视图
–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
    }
}

–4,练习
1,去数据库里,创建user表(id,name,pwd)并且插入数据
CREATE TABLE `user` (
   `id` int(11) NOT NULL auto_increment,
   `name` varchar(100) default NULL,
   `pwd` varchar(20) default NULL,
   PRIMARY KEY  (`id`)
 ) ;
INSERT INTO USER VALUES(NULL,'jack','123456');
INSERT INTO USER VALUES(NULL,'rose','123');

2,去idea写jdbc的代码,查询user表里的所有数据
package cn.tedu.jdbc;

import java.sql.*;

//写jdbc的代码,查询user表里的所有数据
public class Test2 {
    public static void main(String[] args) throws Exception {
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接Connection
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //3,获取传输器Statement
        Statement s = c.createStatement();
        //4,执行SQL,查询的SQL语句会保存数据库返回的数据,存入结果集ResultSet
        ResultSet r = s.executeQuery("select * from user");
            //executeQuery()用来执行查询的SQL
            //executeUpdate()//用来执行 增删改的SQL
        //5,处理数据库返回的结果
        while(r.next()){//next()判断有没有数据,有数据就返回true,没数据就返回false
            //getXxx() 获取不同类型的数据
            int id = r.getInt("id");//参数是列名或者是列的索引1 2 3
            String name = r.getString("name");
            String pwd = r.getString("pwd");
            System.out.println(id+name+pwd);//拼串
        }
        //6,关闭资源
        r.close();//关闭结果集
        s.close();//关闭传输器
        c.close();//关闭连接
    }
}