湖南
day05数据库
一.视图
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.总结
- 查询语句中的 * 尽量换成字段名称
- 查询条件where中,尽量用and不用or.尽量把查询条件的范围控制到最小
- 查询条件中,尽量用= 别用!=<>,尽量别用in
- 表设计时,字段类型尽量用varchar代替char,尽量用数字代替字符串
- 索引:单表中的索引最好控制在5个以内,
模糊查询时,只有一种可以让索引生效:确定开始元素'abc%'
写SQL时注意字符串的标准写法,
where name=123,name的索引会失效
where name='123',name的索引会生效
注意复合索引的最左特性,如果不遵循会导致复合索引失效 - 多联查:可以使用三种方式 笛卡尔积/连接查询/子查询
尽量不要有太多表联查,阿里的开发规范规定了最多三张表
连接查询相对高效,最高效的是内连接,因为内连接只是查交集
但是,在工作中,使用左连接是最多的,也会使用子查询
三.三范式
1.概述
对于数据库的设计人员,必须要遵循的规范,就叫做范式
常见的范式有三范式:1N 2NF 3NF
好处是:可以让数据减少冗余
2.三范式的规则
第一范式:1NF,是指表里的字段值不能再被分割了
第二范式:2NF,是指遵循了1NF的基础上,再遵循2NF
是指表里都应该设计主键字段(主属性),
其他的非主属性,应该都围绕着主属性展开描述.
第三范式:3NF,是指遵循了前两范式
是指 字段之间的依赖性是直接的,而不是还要间接的产生依赖,导致查询关闭变得复杂
5.JDBC
1.概述
全称叫java database connectivity,专门用来完成 利用一段java程序 来操作 数据库 的事情.
是一套标准,是一套规范. 本质上就是一个jar包(一堆的java工具类)
2.使用步骤
- 找到jar包,并拷贝进项目
- 利用工具类 通过java连接数据库(用户名root 数据库名root 数据库名cgb2111 数据库端口号3307)
- 通过·java程序 发起SQL语句
- 数据库把执行结果,返回给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();//关闭连接
}
}