数据库内容整理
目录
一.概述
数据库作用就是用来存储数据和管理数据,本质上就是需要安装MySQL软件. 分类: 关系型数据库MySQL 和 非关系型数据库Redis ,主要观察数据之间的关系
二.使用
-
小黑窗口/DOS窗口:
-
从开始菜单里找MySQL/Mariadb,输入密码
-
win+r 然后输入cmd
-
发起MySQL的命令 -u是指定用户名(root) -p是指定密码(自定义的)
-
命令:mysql -uroot -proot
-
-
-
图形化的工具:sqlyog
三.数据库结构
数据库 -> 表(行和列) -> 数据
四.SQL语言
-
结构化查询语言,用来操作数据库的语言,是一种标准.
-
分类
-
DML（Data Manipulation Language）是数据操纵语言
-
DDL（Data Definition Language）是指数据定义语言
-
DCL（Data Control Language）是指数据控制语言
-
DQL(Data Query Language)是指数据查询语言
-
五.数据库的常见操作
-
建库--create database 库名 default character set utf8;
-
删库--drop database 库名；
-
查看所有数据库--show databases;
-
使用数据库--use 库名；
六.对表的操作
-
创建表：create table 表名(字段名称 字段类型(字段最大长度),字段名称 字段类型(字段最大长度));
-
修改表--添加列：alter table 表名 add column 列名 列的类型(7,2);
-
删除表：drop table 表名;
-
查看所有表：show tables;
-
查看(描述)表结构/设计表：desc 表名;
七.对数据的操作
-
查询表中的所有记录：select * from 表名;
-
插入记录：insert into 表名 values(表里有几个字段,依次按类型赋值);
-
修改记录：update 表名 set 表里字段名=新值 where id=1;
-
删除记录：delete from 表名 where id=2;
-
插入数据/记录注意:
-
需要设置几个值?要看表里有几个字段
-
值的顺序要和字段的顺序保持一致
-
如果字段是字符串类型,设置值时必须要有" " ’ '的标记
-
如果出现中文乱码,可以使用set names gbk;设置
-
八.数据库的使用细节
-
命名规范
-
字段必须以字母开头,不要以数字开头,尽量不要使用拼音
-
长度不能超过30个字符(不同数据库,不同版本会有不同)
-
不能使用SQL保留字:select / from / update / delete / insert into
-
MySQL数据库不区分大小写,多个单词用下划线分开
-
-
数据类型
-
整型:int
-
小数:double(不精确) / numeric(a,b) / decimal(a,b)–a是数字的位数b是小数位数
-
时间: date(年月日) time(时分秒) datetime(年月日时分秒) timestamp(时间戳,指从1970年1月1日到指定日期的毫秒数)
-
字符串: char / varchar
-
char和varchar有什么区别？
-
char:长度固定，不足使用空格补充，最多容纳2000个字符，查询速度极快但浪费空间
-
varchar:可变长度，最多容纳4000个字符，查询稍慢，但节省空间
-
-
特殊场景: 数据长度如果就是固定的,优先选char,因为查的快
-
-
图片: 如果想存入数据库,只会存文件的磁盘路径D:/abc/1.jpg,不是存文件本身
-
九.字段约束
-
概念:当表里的字段值想要添加约束条件时,就可以使用各种字段约束
-
字段约束的功能:哪个字段加了约束,就对哪个字段的值产生约束力
-
常见的有: 主键约束 非空约束 唯一约束 外键约束 默认约束 检查约束
-
主键约束: primary key auto_increment(主键自增)
-
给表里的某个字段设计约束,特点是字段值必须唯一且非空
-
自增策略:auto_increment把主键值交给数据库维护,值会自增
-
-
非空约束
-
非空:not null,给字段加了非空约束,字段值不能为空
-
-
唯一约束
-
唯一:unique,给字段加了唯一约束,字段值不能相同
-
-
外键约束
-
foreign:通过特殊字段(外键),描述了两张表间的关系
-
foreign key(当前表的主键) references 对方表名(对方表的主键)
-
外键约束效果:
-
子表的id必须取自主表的id
-
想删除主表的数据必须先删掉子表相关的
-
-
-
默认约束
-
default: 给字段设置默认值
-
字段名 字段类型(长度) DEFAULT '默认的值'
-
-
检查约束
-
检查约束check:不合法时会报错
-
CHECK(age>0 AND age<18)
-
十.基础函数
-
lower --数据转小写
-
select lower(列名/字段名) from 表名
-
-
upper--数据转大写
-
select upper(列名/字段名) from 表名
-
-
length--数据的长度(一个字母一个字符,一个汉字三个字符)
-
select length(列名/字段名) from 表名
-
-
substr--截取[从1开始,长度为3]
-
substr(1,2,3):1是字段名,2是从哪个字符开始,3是截取长度
-
-
concat--拼接数据
-
select 列名/字段名,concat(列名/字段名,'123') from 表名
-
concat(1,2,3)拼接字符串-1是字段名2和3都是要拼的内容
-
-
replace--把a字符替换成666
-
select 列名/字段名,replace(列名/字段名,'a','666') from 表名
-
replace(1,2,3)替换:1是字段名,是要把2换成3
-
-
ifnull-- #判断,如果comm是null,用10替换
-
select ifnull(comm,10) comm from 表名2
-
-
round & ceil & floor
-
round四舍五入，ceil向上取整，floor向下取整
-
-
日期函数 now当前时间
-
year年 month月 day日 hour时 minute分 second秒
-
select now()现在的时间
-
select year(now()),month(now()),day(now())
-
-
转义符号\
-
select 'xi'an'
-
select "xi\'an"
-
十一.条件查询
-
where条件
-
查询列名/字段名=1的部门信息
-
select * from 表名 where 列名/字段名=1
-
-
多个条件可以用and 或者 or连接
-
查询sql时尽量避免使用or,他会导致结果集过于庞大,查询过慢
-
查询编号是2的或者编号是3的 部门信息
-
select * from 表名 where 列名/字段名=2 OR 列名/字段名=3
-
select * from 表名 where 列名/字段名 IN(2,3)#同上等效
-
-
-
like模糊查询
-
%通配符,通配了0~n个字符,_通配符,通配了1个字符
-
查名称里包含o的部门信息
-
select * from 表名 where 列名/字段名 like '%o%' #低效
-
select * from 表名 where 列名/字段名 like 'o%' #以o开始,高效
-
select * from 表名 where 列名/字段名 like '%o' #以o结束
-
select * from 表名 where 列名/字段名 like 'l__' #了解
-
-
-
null操作空数据
-
select * from 表名 where 列名/字段名 is null;--查询结果是null的
-
-
between and 区间范围,是包含的关系
-
select * from 表名 where 列名/字段名 between 3000 and 10000
-
-
limit 实现分页 展示记录
-
select * from 表名 limit 2,2--从第三条开始,展示2条记录;
-
-
order by 排序--默认为升序(ASC可以省略不写)
-
select * from 表名 order by 列名/字段名 desc #降序;
-
按字母排序:按照字典顺序. 数字:按照数值本身大小. 汉字:按照汉字所对应的utf-8编码表数字大小.
-
-
where
-
select*from 表名 where year(列名/字段名) < 2017
-
-
as关键字 给列设置别名
-
select *,year(now())-year(列名/字段名) AS 年份 from 表名;
-
十二.聚合函数
-
概述
-
把一列的值聚合在一起,再做分析
-
聚合函数:max()最大,min()最小,avg()平均,sum()求和, count()统计个数
-
select min(列名/字段名) from 表名
-
select count(1) from 表名;
-
-
分组(当查询时出现聚合列与非聚合列时,必须按照非聚合列分组)
-
概念:把查询结果进行分组,统计一组数据中的最大值,最小值...使用group by
-
select max(sal) 最高薪,deptno 部门 from emp group by deptno;查每个部门的最高薪
-
完成分组后的过滤使用having(其效果和where一样)(having只能出现在group by之后）
select count(1) 总人数,deptno 部门 from emp group by deptno having count(1)>1
-
十三.事物(transaction)
-
什么是事物?
-
数据库事物是指作为单个逻辑工作单元执行的一系列操作,要么完全执行，要么完全的不执行.
-
用来保证多个操作(增删改) 要么全成功,要么全失败
-
-
事物的4个特性(ACID)
-
原子性:多个操作是绑定在一起的,要么全成功,要么全失败
-
一致性:在多个系统中保证数据是一致的
-
隔离性:数据库允许高并发,在保证性能的同时,隔离用户的操作(底层加了同步锁)
-
持久性:对数据的操作是都有持久影响的
-
-
事物的隔离级别
-
读未提交:效率高,安全性差,可能发生并发数据问题
-
读已提交:牺牲了效率,提高了安全性--Oracle数据的默认隔离级别
-
可重复读:牺牲了效率,进一步提高了安全性. MySQL默认隔离级别
-
串行化:效率最低,安全性最高,不能并发(表级锁),读写都加锁
-
-
开启事物:start transaction
-
结束事物:commit(提交事务,提交后才能持久性影响数据库) 或 rollback(回滚事物,回滚到事物起点,相当于撤销之前所有操作)
-
MySQL数据库默认管理事物,会为每条SQL提供事物
十四.索引(index)
-
概念:
索引--是一种排好序的快速查找的数据结构,事先对数据进行了排序，大大提高了查询效率
-
索引的优缺点
-
优点:
-
索引事先对数据库进行了排序,大大提高了查询效率
-
索引是数据库优化
-
表的主键会默认自动创建唯一索引
-
每个字段都可以被索引
-
大量降低数据库的IO磁盘读写成本,极大提高了检索速度
-
-
缺点:
-
索引本身也是一张表,所以索引列也要占用空间,不适合大量添加
-
索引表中的内容,在业务表中都有,数据重复,空间二次浪费
-
对数据的增删改的操作需要更新索引表的信息,如果数据量巨大,更新效率就很慢
-
随着业务的不断变化,之前的索引可能不满足需求,需要消耗时间去更新索引
-
-
-
索引的分类
-
单值索引:一个索引包含一个列
-
唯一索引:也是一个索引包含一个列,但列的值唯一(主键会自动创建)
-
复合索引:一个索引同时包含多个列
-
-
索引的使用
-
查询表中的索引:show index from 表名
-
创建单值索引(给常用来作为查询条件的字段加):
create index 索引名 on 表名(字段名)
-
观察SQL的性能/执行计划如何优化?
在该条SQL语句前面使用explain关键字
-
创建唯一索引(索引列的值不能重复):
create unique index 索引名 on 表名(字段名)
-
创建复合索引
create index 索引名 on 表名(字段1,字段2)
-
使用符合索引(最左特性(按照符合索引最左边字段名查询),否则复合索引失效)
explain select * from 表名 where 列名='值'
-
删除索引
alter table 表名 drop index 索引名
-
十五.表关联(association)
-
概念
表关联代表了表与表之间的关系
-
表的关系分为4种:
-
一对一 one to one:员工与员工编号
-
一对多 one to many:部门和员工
-
多对一 many to one:员工和部门
-
多对多 many to many:多个老师和多个学生
-
-
多表联查(产生了大量冗余数据)
-
概念:多表查询是指基于两个和两个以上的表的查询.
-
笛卡尔积 : 当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数是两张表条数的乘积,这种现象被称为笛卡尔积现象.(比如有表1(A,B,C,D)和表2(a,b,c,d)两张表,查询时使用select * from 表1,表2;查询出来的结果是Aa,Ab,Ac,Ad,Ba,Bb,Bc,Bd,Ca,Cb,Cc,Cd,Da,Db,Dc,Dd相当于查询出了大量重复的冗余数据)
-
在多表联查中,笛卡尔积查询指把多张表用逗号隔开,最终实现两张表的所有数据,产生大量冗余数据
-
执行顺序:先定表from,然后where过滤,接着and再过滤,最后select查询,所以效率极低
-
查询所有内容:
select * from 表名1,表名2 where 两张表的关系(例如:a.id=b.id)
上面这种查询两个表的方式称为:笛卡尔积
-
查询单个内容(例如:查询部门名称叫a的员工姓名):
select emp.ename from dept,emp where dept.deptno=emp.deptno and dept.dname='a'
-
-
连接查询(使用join连接表名)(效率比笛卡尔积高,由底层顺序决定)
-
执行顺序:先from定左表,然后on确定关系,接着join去右表查,然后where过滤条件,最后select查询,连接查询相对高效
-
优化原则:(由于连接查询先定左表,左表查询全部内容,右表只查询满足条件的.)将数据少的或者数据简单的作为左表,小表驱动大表
-
查询所有内容:
select * from 表1 join 表2 on 两张表的关系(例如:a.id=b.id)
-
查询单个内容(例如:查询部门名称叫a的员工姓名):
select emp.ename from dept join emp on dept.deptno=emp.deptno where dept.dname='a'
三种连接查询:
内连接 inner join :取两个表的交集数据(连接查询默认使用)
左外连接 left join :左表的所有和右表满足条件的,不满足是null
右外连接 right join :右表的所有和左表满足条件的,不满足是null
-
-
子查询(查询效率较低)
-
概念:嵌套在其他查询语句中的查询语句,也叫嵌套查询.把上一次的查询结果,当做下次查询的条件.
-
查询单个内容(例如:查询部门名称叫a的员工姓名):
select ename from emp where deptno=(select deptno from dept where dname='a')
-
查询多个结果使用in
-
-
-
SQL的执行顺序
-
from 选择表
-
on 连接条件
-
join 连接
-
where 条件过滤
-
group by 分组
-
agg_func(column or expression)聚合
-
having 分组过滤
-
select (9) distinct column 选择字段/去重
-
order by 排序
-
limit count offset count 分页
-
-
SQL优化
-
使用字段名称代替*(节省资源,select * 进行查询时,很可能不会用到索引,造成全盘扫描)
-
where里: 尽量用and不用or , 尽量用=不用!= <> , 条件越精确越好 ,(使用or可能会使索引失效,导致全盘扫描)
-
用varchar代替char
-
varchar按数据内容实际长度存储，存储空间小，可以节省存储空间
-
char按声明大小存储，不足补空格
-
其次对于查询来说，在一个相对较小的字段内搜索，效率更高
-
-
字段值尽量用数字代替字符串 , 用默认值代替null
-
给常用来作为查询条件的字段设计索引,表里的索引不要超过5个
-
观察SQL性能,使用explain关键字,本质上就是看有没有用到索引
-
模糊查询,where name like'张%',是高效写法,甚至可以让索引生效
-
批量处理: 避免返回大量数据,批量查询,批量的删,最好使用分页
-
-
视图VIEW
-
概念:视图是基于SQL语句的结果集的可视化的表(相当于是个缓存)
-
创建视图:create view 视图名 as SQL语句;
-
使用视图:select * from 视图名;
-
视图的优缺点:
优点:提高了SQL的复用率
缺点:占内存且无法被优化
-
十六.JDBC
-
概念:全称是java database connectivity,专门用来通过Java程序操作数据库,返回结果.JDBC还是一套标准,支持对各种数据库产品的操作.
-
使用步骤
-
创建工程,导入MySQL提供的jar包,选中jar包右键Add as Library
-
通过Java连接数据库,需要指定连接数据库的参数:用户名,密码,端口号,ip地址,指定库名.
-
发起SQL语句并执行
-
处理结果集
-
-
基础JDBC代码
package cn.tedu.test; import java.sql.*; public class JDBC { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver");//通过反射获取到对象 //2.获取数据库的连接(用户名,密码,端口号,ip地址,指定库名) // 使用数据传输协议(jdbc 连接 数据库) ip地址( 端口号 数据库名称) String url="jdbc:mysql://localhost:3306/cgb2107"; //驱动管理器 Connection c = DriverManager.getConnection(url, "root", "root"); //3.获取传输器 Statement s = c.createStatement(); //4.利用传输器执行SQL,返回结果集 ResultSet r = s.executeQuery("select * from dept");//执行查询语句 //5.处理结果 while (r.next()){//next()判断有数据吗,有就返回true,没有循环结束 //解析数据,获取每个字段的值 /* int a = r.getInt(1);//参数可以是字段名,或者字段参数 System.out.println(a); String b = r.getString(2); System.out.println(b); String loc = r.getString(3); System.out.println(loc); */ for (int i = 1; i < 4; i++) { Object o = r.getObject(i); System.out.println(o); } } //6.释放资源 r.close();//结果集 s.close();//传输器 c.close();//连接 } }
-
JDBC流程优化(利用JDBC模拟用户登录,并解决SQL攻击)
package cn.tedu.test; import java.sql.*; import java.util.Scanner; public class JDBCTest4 { public static void main(String[] args) { //method1(); method2(); } /*创建一个方法,封装 创建驱动 获取数据库连接 两个步骤,方便调用 * 设置为静态的,是为了方便调用,节省内存,只加载一次且被共享,而且调用时可以直接调用不需要创建对象去调用 * (静态资源是共享的是唯一的是只加载一次的,当类被加载时就加载,类消失他才消失) * 封装成方法是高内聚的思想,提高代码的复用性 */ public static Connection getConnection() throws Exception { //1.创建驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取数据库连接 // 防止中文乱码 设置时区 String url ="jdbc:mysql://localhost:3306/cgb2107?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false"; Connection c = DriverManager.getConnection(url, "root", "root"); return c;//返回给调用者 } /** * 模拟登陆 * SQL攻击/注入:当SQL语句中出现了#时,会把后面的SQL语句注释,改变了SQL语义 */ public static void method1(){ try { //调用上面封装的方法并处理方法返回值 Connection c = getConnection(); //3.获取传输器 Statement s = c.createStatement(); //传入动态的账户密码 //当用户输入特殊的名字时jack'#, String n=new Scanner(System.in).nextLine(); String p=new Scanner(System.in).nextLine(); //4.利用传输器执行SQL,返回结果集 String sql="select * from user where name='"+n+"' and password='"+p+"'"; ResultSet r = s.executeQuery(sql); //5.判断是否查到信息 if (r.next()){ System.out.println("恭喜您,登陆成功"); }else { System.out.println("登录失败"); } //6.释放资源 r.close(); s.close(); c.close(); } catch (Exception e) { e.printStackTrace(); } } private static void method2() { //为了能让finally使用变量 Connection c=null; PreparedStatement s =null; ResultSet r =null; try { //调用上面封装的方法并处理方法返回值 c = getConnection(); //3.准备SQL System.out.println("请输入账号"); String n=new Scanner(System.in).nextLine(); System.out.println("请输入密码"); String p=new Scanner(System.in).nextLine(); // String sql="select * from user where name='"+n+"' and password='"+p+"'"; String sql="select * from user where name=? and password=?"; //4.获取 新的传输器 s = c.prepareStatement(sql); //给SQL设置参数 s.setString(1, n); s.setString(2, p); r = s.executeQuery(); //5.判断是否查到信息 if (r.next()){ System.out.println("恭喜您,登陆成功"); }else { System.out.println("登录失败"); } } catch (Exception e) { e.printStackTrace(); }finally { //6.释放资源 close(c, r, s);//调用封装的方法 ,释放资源 } } //封装了方法,用来关闭资源 public static void close(Connection c,ResultSet r,PreparedStatement s){ if (c!=null){//为了防止空指针异常 try { r.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (s!=null){//为了防止空指针异常 try { s.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (c!=null){//为了防止空指针异常 try { c.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
-
什么叫SQL攻击?如何解决?
-
什么叫SQL攻击?
出现了特殊的符号,比如#号,改变了SQL的语义,本质上是因为使用Statement(旧的传输器,SQL中出现了#号,改变了语义,甚至注释掉了后面所有的条件).
-
解决方案:
利用全新的传输器PreparedStatement,把SQL骨架和SQL参数分开执行,遇到#号时,只是把它当成普通文本而不是注释符号了.优点:省去了拼接SQL语句的麻烦,防止了SQL攻击,更加高效.
-
797
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
