数据库XX语言：DDL, DML, DQL, DCL
1 数据库
1.1 连贯数据库
命令行连贯
mysql -uroot -pupdate mysql.user set password = password('XXX') where user = 'root' and Host = 'localhost'; -- 批改用户明码flush privileges; -- 刷新权限-----------------------------------------------show tables;describe 表名; -- 显示表信息exit; -- 退出连贯1.2 操作数据库
create database [if not exists] 数据库名 -- 创立drop database [if exists] 数据库名 -- 删除use 数据库名 -- 应用/切换 如果数据库名是关键词，则加上``show databases; -- 查看数据类型：
数值：
- 整型：tinyint 1B,smallint 2B, int 4B, bigint 8B
- 浮点型：float 4B, double 8B, decimal（字符串模式的浮点数，罕用于金融计算）
字符串
- char 固定大小 0~255,
- varchar 可变 0~65535
- tinytext 微型文本 2^8 - 1
- text 文本串 2^16-1
工夫日期
- date YYYY- MM- DD
- time HH: mm: ss
- datetime YYYY- MM - DD HH: mm: ss
- timestamp 工夫戳，1970.1.1到当初的毫秒数
- year 年份示意
NULL
每个表都必须存在以下五个字段：
- id 主键
- `version`乐观锁
- is_delete 伪删除
- gmt_create 创立工夫
- gmt_update 批改工夫
2 表
2.1 创立表
CREATE TABLE [IF NOT EXISTS] `student`{ `id` INT(4) [NOT NULL AUTO_INCREMENT COMMENT '学号'], ... PRIMARY KEY(`id`)}[ENGINE=INNODB DEFAULT CHARSET=utf-8]查看创立语句
SHOW CREATE DATABASE school -- 查看创立数据库语句SHOW CREATE TABLE student -- 查看创立表语句DESC student -- 显示表构造数据表的类型
INNODB（默认）
MYISAM（早年应用）
| MYISAM | INNODB | |
|---|---|---|
| 事务反对 | 不反对 | 反对 |
| 数据行锁定 | 不反对 | 反对 |
| 外键束缚 | 不反对 | 反对 |
| 全文索引 | 反对 | 不反对 |
| 表空间大小 | 较小 | 较大，约为2倍 |
惯例应用的操作：
- MYISAM：节约空间，速度较快
- INNODB：安全性高，事务的解决，多表多用户操作
所有的数据库文件都存在data目录下，实质还是文件的存储
MySQL引擎在物理文件上的区别
- INNODB在数据库表中只有*.frm文件，以及下级目录下的ibdata1文件
MYISAM对应文件
- *.frm - 表构造的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
2.2 批改删除表
批改（alter）
ALTER TABLE teacher RENAME AS teacher1 -- 批改表名ALTER TABLE teacher1 ADD age INT(11) -- 减少字段-- 批改表的字段ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 批改束缚ALTER TABLE teacher1 CHANGE age age1 VARCHAR(1) -- 重命名-- 删除表的字段ALTER TABLE teacher1 DROP age1删除
-- 删除表DROP TABLE [IF EXISTS] teacher13 MySQL数据管理
3.1 外键
ALTER TABLE `student`ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);以上操作都是物理外键，数据库级别的外键，不倡议应用，防止数据库过多造成困扰
3.2 DML语言
数据库意义：数据存储，数据管理
增加：insert into 表名[(字段1,2,3...)]values(值1,2,3...)[,(),()...]
批改：update 表名 set 字段1 = 值1[,字段2 = 值2...] where 条件
删除：delete from 表名 where 条件
truncate 表名：齐全清空一个数据库表，表的构造和索引束缚不会变！
truncate 从新设置，自增列，计数器会归零，不会影响事务
where子句
=, >, <, <=, >= <>或者!=（不等于）, BETWEEN X AND Y （[X,Y]）
AND, OR
留神：没写条件则批改表的所有记录！
4 DQL查问数据
DQL:Data QueryLanguage 数据查询语言
select 字段 [as 别名] from 表 [as 别名]
函数
concat(a,b)
去重 distinct select distinct 字段 ...
能够select的表达式：文本值，列，Null，函数，计算表达式，零碎变量...
4.1 含糊查问
is null,like, in
-- 查问姓刘的同学-- like联合 %代表0到任意个字符 _代表一个字符select `StudentNo`,`StudentName` from `student`where StudentName like '刘%'--查问1001,1002,1003号学员select `StudentNo`,`StudentName` from `student`where StudentNo in (1001,1002,1003)4.2 联表查问
where 等值查问
join on 连贯查问
查表思路：
- 剖析需要：剖析查问的字段来自哪些表
- 确定应用哪种连贯查问（7种）
- 确定交叉点（判断的条件：student.studentNo = grade.studentNo）
-- 查问缺考的学生select s.StudentNo,studentName,SubjectNo,StudentResultfrom student sleft join result ron s.studentNo = r.studentNowhere StudentResult is null自连贯
外围：一张表拆成两张一样的表
select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目'from `category` as a,`category` as bwhere a.`categoryid` = b.`pid`4.3 分页和排序
limit & order by
排序：升序 ASC 降序 DESC
-- 分页：起始值，页面大小limit start(0-),sizelimit 0,5 -- 第一页limit 5,5 -- 第二页...limit (N-1)*pageSize,pageSize --第N页4.4 嵌套查问
select 字段 from 表
where(子查问)
-- 由里及外select StudentNo,StudentName from student where StudentNo in ( select StudentNo from result where StudentResult>80 and SubjectNo = ( select SubjectNo from `subject` where `SubjectName` = '高等数学-2' ))5 MySQL函数
官网：https://dev.mysql.com/doc/ref...
5.1 罕用函数
-- 数学函数select abs(-8) -- 8select ceiling(9.4) -- 10select floor(9.4) -- 9select rand() -- 0~1select sign(-10/0/10) -- -1,0,1-- 字符串函数select char_length('xxxxx')select concat('a','b','c')select insert('xxx',pos,length,'yyy')select lower('XXX')select upper('xxx')select instr('xyz','x') -- 返回子串第一次呈现的索引select replace('abc','b','d')select substr('abcdefg',pos,length)select reverse('cba')-- 查问姓周的同学,替换成邹select replace(studentname,'周''邹'） from studentwhere studentname like '周%' -- 工夫和日期函数（记住）select current_date()select curdate()select now() -- 以后工夫select localtime()select sysdate()-- 零碎select system_user() select user()select version()5.2 聚合函数
select count(字段) from 表 -- 疏忽null值，有主键，count(字段)查问效率高select count(*) from 表select count(1) from 表select sum(`StudentResult`) as 总和 from resultselect avg(`StudentResult`) as 平均分 from resultselect max(`StudentResult`) as 最高分 from resultselect min(`StudentResult`) as 最低分 from result-- 分组：查问不同课程的平均分，最高分，最低分select SubjectName,avg(StudentResult) as 平均分,max(StudentResult) as 最高分,min(StudentResult) as 最低分from result rinner join `subject` subon r.`SubjectNo` = sub.`SubjectNo`group by r.SubjectNo -- 分组字段having 平均分>805.3 MD5加密
不可逆
破解原理：暴力破解，一个个试
MD5(pwd)
将用户传递进来的明码，进行MD5加密，而后比对加密后的值
6 事务
将一组SQL放在一个批次中执行
准则：ACID（原子性，一致性，隔离性，持久性）
原子性（Atomicity）
要么都胜利，要么都失败
一致性（Consistency）
事务前后的数据完整性要保障统一
隔离性（Isolation）
事物之间要互相隔离
持久性（Durability）
事务一旦提交则不可逆，被长久化到数据库中
隔离导致的问题：脏读（读到另一事务未提交的数据）、不可反复读（屡次读取后果不同）、幻读（读取别的事务插入的数据）
-- mysql默认开启事务主动提交set autocommit = 0 -- 敞开set autocommit = 1 -- 开启（默认）-- 手动处理事务set autocommit = 0-- 事务开启start transactioninsert XXXinsert yyy-- 提交commit-- 回滚rollback-- 事务完结set autocommit = 1-- 理解savepoint 保留点名rollback to savepoint 保留点名release savepoint 保留点名7 索引
MySQL官网对索引的定义为：索引（Index）是帮忙MySQL高效获取数据的数据结构。提取句子骨干，就能够失去索引的实质：索引是数据结构。
7.1 索引的分类
- 主键索引（primary key）
- 惟一索引（unique key）
- 惯例索引（key/index）
- 全文索引（fulltext）
-- 索引的应用-- 1.在创立表的时候给字段减少索引-- 2.创立结束后，减少索引-- 显示所有的索引信息select index from 表名-- 减少一个全文索引alter table 数据库.表名 add fulltext index 索引名(字段名)-- explain剖析sql执行的情况explain select * from student --非全文索引explain select * from student where match(studentName) against('刘')7.2 测试索引
插入100万条数据测试索引
-- 插入100万条数据delimiter $$ -- 写函数前必须写，标记create function mock_data()returns intbegin declare num int default 1000000; declare i int default 0; while i<num do -- 插入语句 insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) values(concat('用户',i),'178343244@qq.com',concat('18',floor(rand()*(999999999-100000000))+100000000),floor(rand()*2),uuid(),floor(rand()*100)); set i = i+1; end while; return i;end;select mock_data();-- 测试索引select * from app_user where `name` = '用户9999'; -- 0.0993 secexplain select * from app_user where `name` = '用户9999'; -- 查问99万条数据-- 创立一般索引-- create index 索引名 on 表(字段)-- 索引名：id_表名_字段名create index id_app_user_name on app_user(`name`);select * from app_user where `name` = '用户9999'; -- 0.001 secexplain select * from app_user where `name` = '用户9999'; -- 查问1条数据总结：索引在小数据量的时候用途不大，但在大数据的时候，区别非常显著
7.3 索引准则
- 索引不是越多越好
- 不要对常常变动的数据加索引
- 小数据量的表不须要加索引
- 索引个别加在常常查问的字段上
索引的数据结构
Hash类型的索引
Btree：InnoDB默认
http://blog.codinglabs.org/ar...
8 权限治理和备份
8.1 权限治理
SQL命令操作：实质是对用户表mysql.user进行改变
-- 创立用户create user xxx identified by '123456'-- 批改明码set password [for xxx] = password('123456')-- 重命名rename user xxx to yyy-- 用户受权 库.表grant all privileges on *.* to xxx -- all privileges 除受权权限-- 查问权限show grants for xxxshow grants for root@localhost-- 撤销权限revoke all privileges on *.* from xxx-- 删除用户drop user xxx8.2 MySQL备份
目标：保证数据不失落，数据转移
形式：
- 间接复制物理文件
- 在Navicat这种可视化工具中手动导出
- 应用命令行导出 mysqldump
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql# 导入# 登录的状况下，切换到指定的数据库# source 备份文件source d:/a.sqlmysql -u用户名 -p明码 库名< 备份文件9 标准数据库设计
数据库设计：剖析需要→概要设计（E-R图）
三大范式：
- 第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
1NF保障每一列不可再分
- 第二范式（2NF）：满足1NF的前提下，非码属性必须齐全依赖于候选码（在1NF根底上打消非主属性对主码的局部函数依赖）
2NF保障每张表只形容一件事物
- 第三范式（3NF）：满足2NF的前提下，任何非主属性不依赖于其它非主属性（在2NF根底上打消传递依赖）
3NF保障表中每一列数据都与主键间接相干
规范性和性能的问题：
关联查问的表不得超过三张
- 思考商业化的需要和指标（老本，用户体验）数据库的性能更加重要
- 在标准性能的问题的时候，须要适当思考规范性
- 成心给某些表减少一些冗余的字段。（多表查问→单表查问）
- 成心减少一些计算列（大数据量查问→小数据量查问）
10 JDBC
Java操作数据库
程序通过数据库驱动操作数据库
须要导入一个数据库驱动包：mysql-connector-java-X.X.XX.jar
public class JDBCFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { // 1. 加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，主动加载DriverManager.registerDriver(new com.mysql.jdbc,Driver()); // 2. 用户信息和url String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true"; // 协定(jdbc:mysql)://主机地址:端口号/数据库名?参数 String username = "root"; String password = "1031"; // 3. 连贯胜利，数据库对象Connection Connection connection = DriverManager.getConnection(url,username,password); // 4. Statement执行SQL对象 Statement statement = connection.createStatement(); String sql = "select * from users"; ResultSet resultSet = statement.executeQuery(sql); // 5.查看返回后果 while(resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("name")); System.out.println("pwd="+resultSet.getObject("password")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birthday="+resultSet.getObject("birthday")); System.out.println("================="); } // 6. 开释连贯，耗资源，用完关掉 resultSet.close(); statement.close(); connection.close(); }}Java应用JDBC连贯数据库步骤：
- 加载驱动
- 连贯数据库DriverManager
- 获取执行sql的对象Statement
- 获取返回的后果集
- 开释连贯
Statement & ResultSet
Connection connection = DriverManager.getConnection(url,username,password);Statement statement = connection.createStatement();String sql = "select * from users";ResultSet resultSet = statement.executeQuery(sql); // 查问操作返回ResultSetstatement.execute(); // 执行任何SQLstatement.executeUpdate(); // 更新、插入，删除，返回一个受影响的行数// ResultSetresultSet.getObject("id") // 不晓得返回类型resultSet.getString("id") // 晓得返回类型// 遍历指针（光标）resultSet.next() // 挪动到下一行resultSet.beforeFirst() // 挪动到最前resultSet.afterLast() // 挪动到最初resultSet.previous() // 挪动到前一行resultSet.absolute(row) //挪动到指定行10.1 封装JDBC（JDBCUtils.java）
import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JDBCUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); // 1.驱动只有加载一次 Class.forName(driver); } catch (IOException | ClassNotFoundException e) { e.printStackTrace(); } } //获取连贯 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } //开释连贯 public static void release(Connection connection, Statement statement, ResultSet resultSet){ try { if (resultSet != null){ resultSet.close(); } if (statement != null){ statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } }}测试用JDBC封装类来操作数据库
import com.zhg.lesson02.utils.JDBCUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestInsert { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JDBCUtils.getConnection(); statement = connection.createStatement(); String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(4,'zhg','123456','zhomgun@qq.com','2020-01-01')"; // 删改只需改sql语句 int i = statement.executeUpdate(sql); if(i > 0){ System.out.println("插入胜利！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.release(connection,statement,resultSet); } }}import com.zhg.lesson02.utils.JDBCUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestSelect { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JDBCUtils.getConnection(); statement = connection.createStatement(); String sql = "select * from users"; // 删改只需改sql语句 resultSet = statement.executeQuery(sql); while(resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("name")); System.out.println("pwd="+resultSet.getObject("password")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birthday="+resultSet.getObject("birthday")); System.out.println("================="); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.release(connection,statement,resultSet); } }}10.2 SQL注入
SQL注入即是指web应用程序对用户输出数据的合法性没有判断或过滤不严，攻击者能够在web应用程序中当时定义好的查问语句的结尾上增加额定的SQL语句，在管理员不知情的状况下实现非法操作，以此来实现坑骗数据库服务器执行非受权的任意查问，从而进一步失去相应的数据信息。
import com.zhg.lesson02.utils.JDBCUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class SQLInjection { public static void main(String[] args) { login("zhg","123456"); // 失常登录 login("'or'1=1","'or'1=1"); //SQL注入：用or拼接SQL语句... } // 登录业务 public static void login(String username,String password){ Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JDBCUtils.getConnection(); statement = connection.createStatement(); String sql = "select * from users where `NAME` ='"+username+"' and `PASSWORD` = '"+password+"'"; // 删改只需改sql语句 resultSet = statement.executeQuery(sql); while(resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("name")); System.out.println("pwd="+resultSet.getObject("password")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birthday="+resultSet.getObject("birthday")); System.out.println("================="); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.release(connection,statement,resultSet); } }}10.3 PreparedStatement对象
PreparedStatement能够避免SQL注入
import com.zhg.lesson02.utils.JDBCUtils;import java.sql.*;import java.util.Date;public class TestInsert { public static void main(String[] args) { Connection connection = null; PreparedStatement statement = null; ResultSet resultSet = null; try { connection = JDBCUtils.getConnection(); //应用?占位符代替参数 String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)"; // 删改只需改sql语句 statement = connection.prepareStatement(sql); // 预编译的SQL，先写SQL，而后不执行 //手动给参数赋值 statement.setInt(1,5); statement.setString(2,"hugh"); statement.setString(3,"123456"); statement.setString(4,"24245525@qq.com"); // 留神点：sql.Date 数据库 new java.sql.Date(工夫戳) // util.Date Java new Date().getTime() 取得工夫戳 statement.setDate(5,new java.sql.Date(new Date().getTime())); int i = statement.executeUpdate(); if(i > 0){ System.out.println("插入胜利！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.release(connection,statement,resultSet); } }}PreparedStatement能够避免SQL注入的实质：把传递进来的参数当作字符，如果存在转义字符，就间接本义
10.4 数据库连接池
数据库连贯→执行→开释，十分节约系统资源
池化技术：筹备一些事后的资源，过去就连贯事后筹备好的
编写连接池，实现接口DataSource
开源数据源实现
DBCP
C3P0
Druid：阿里
应用这些数据库连接池之后，咱们在我的项目开发中不须要编写数据库的代码了！
DBCP，须要jar包：commons-dbcp-1.4, commons-pool-1.6
C3P0，须要jar包：c3p0-0.9.5.5, mcharge-commons-java-0.2.19
无论应用什么数据源，实质还是一样的，DataSource接口不会变，办法就不会变