MySQL
MySQL是关系型(SQL)数据库。5.7稳定
1. 数据库的数据类型
-
数值
-
tinyint 1个字节
-
smallint 两个字节
-
mediumint 三个字节
-
int 四个字节
-
bigint 八个字节
-
float 四个字节
-
double 八个字节
-
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
没有值,未知,不要用null进行运算
2. 字段属性
Unsigned:
- 无符号整数
- 限制不能为负数
zerofill:
- 0填充
- 不足位数,用0填充
自增:
- AUTO_INCREMENT
非空:
- NOT NULL
默认:
- 不指定该列时,则使用该默认值
- DEFAULT
3.创建数据表
CREATE TABLE `person` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(50) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` varchar(10) NOT NULL DEFAULT '男' COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
4.增删改查
-
增加
insert into `person`(`age`,`name`) values(1,'长江一号'),(15,'长江十五号')
-
删除
-- 删除指定记录 delete from `person` where `name` = '长江七号' -- 清空表 delete from `person` -- 清空表 truncate table `person`
delete和truncate清空表的区别
-
delete
不影响自增列
-
truncate
自增列归零
-
-
修改
update `person` set `age` = 15 where `name` = '长江七号'
-
查询
select `name` as 姓名,`age` as 年龄 from person as 人
5.修改删除数据表
修改
-- 修改表名
ALTER TABLE person RENAME AS person1
-- 增加字段
ALTER TABLE person1 ADD age INT(11)
-- 修改约束
ALTER TABLE person1 MODIFY age VARCHAR(11)
-- 修改字段名
ALTER TABLE person1 CHANGE age age1 INT(1)
-- 删除表的字段
ALTER TABLE person1 DROP age1
删除
-- 删除表
DROP TABLE IF EXISTS person1
所有的创建和删除加上判断是否存在,以免报错
6.数据表类型
INNODB 默认使用
MYISAM 早些年使用
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 小 | 大,约为2倍 |
安全性 | 低 | 高 |
7.DQL查询数据
7.1. 简单查询语句
select * from person
select `name` as 姓名,`age` as 年龄 from person as 人
-- concat追加字符串
select concat('姓名:',name) as 新名字 from person
7.2. 去重 distinct
select distinct `age` from person
7.3. where字句
检索符合条件的值
select distinct `name` from person where `age` > 0 and `age` < 15
select distinct `name` from person where `age` between 0 and 15
select distinct `name` from person where not `age` = 15
7.4. 模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为null,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为null,结果为真 |
BETWEEN | a between b and c | 如果a在b和c之间,结果为真 |
LIKE | a like b | 如果a匹配b,结果为真 |
IN | a in(a1,a2,a3) | 如果a是a1、a2、a3其中一个值,结果为真 |
-- %匹配任意多字符
select `name` from `person` where `name` like '刘%'
-- _匹配一个字符
select `name` from `person` where `name` like '刘_'
-- 查询age为0、1、2的姓名
select `name` from person where `age` in (0,1,2)
select `name` from person where `name` is null
select `name` from person where `name` is not null
7.5. 连表查询
- inner join:左表记录在右表中有匹配的一行时,才列出
select a.id,a.name,b.sore
from stu as a
inner join grade as b
on a.id = b.id
- left join:以左表为基准,左表每条记录都列出,若右表没有满足on条件的记录,则右表字段为null
select a.id,a.name,b.sore
from stu as a
left join grade as b
on a.id = b.id
- right join:以右表为基准,右表每条记录都列出,若左表没有满足on条件的记录,则左表字段为null
select a.id,a.name,b.sore
from stu as a
right join grade as b
on a.id = b.id
-
自连接
自己的表和自己的表连接,一张表拆为两张表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a INNER JOIN `category` AS b
ON a.`categoryid` = b.`pid`
7.6. 分页(limit)和排序(order by)
-
排序
-
升序 ASC
-
降序 DESC
select `name`,`age` from person where `age` > 0 order by `age` asc select `name`,`age` from person where `age` > 0 order by `age` desc
-
-
分页
- 为什么分页?缓解数据库压力,用户体验更好
- 语法: limit 起始值,条数
- 第n页:limit (n-1)*pageSize,pageSize
select `name`,`age` from person where `age` > 0 limit 0,5
7.7. 分组和过滤
select subjectName,avg(studentResult) as 平均分,max(studentResult) as 最高分,min(studentResult) as 最低分
from result r
inner join `subject` sub
on r.`SubjectNo` = sub.`SubjectNo`
group by r.SubjectNo
having 平均分 > 80
having用于分组过后过滤满足条件的组,通常和聚合函数一起出现
where用于分组前过滤数据,不能接聚合函数
8. MySQL函数
8.1. 常用函数
- 数学运算
-- 绝对值
select ABS(-8)
-- 向上取整
select ceiling(9.4)
-- 向下取整
select floor(9.4)
-- 随机数 0-1
select rand()
-- 符号 0返回0 负数返回-1 正数返回1
select sign(-5)
- 字符串函数
-- 返回字符串长度
select char_length("strstrstr")
-- 拼接字符串
select concat('我','爱','天刀')
-- 替换字符串
select insert('我爱学习helloworld',1,2,'我超级热爱')
-- 大小写转换
select upper('abcd')
select lower('ABCD')
-- 第一次出现的位置
select instr('ABCD',b)
-- 替换 C->K
select replace('ABCDE','CD','KP')
-- 从第m个开始截取n个
select substr('abcde',m,n)
-- 从第m个开始截取到最后
select substr('abcde',m)
-- 反转字符
select reverse('abcdef')
- 时间日期
-- 获取当前日期
select current_date()
select curdate()
select now()
-- 获取本地时间
select localtime()
-- 获取系统时间
select sysdate()
select year(now())
...
- 系统
select system_user()
select user()
select version()
8.2. 聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 求平均 |
MAX() | 最大值 |
MIN() | 最小值 |
… |
-- 求和 都能够统计记录条数
-- 会忽略所有null
SELECT COUNT(name) FROM person
-- 不会忽略null
SELECT COUNT(1) FROM person
-- 不会忽略null,被优化过,速度最快
SELECT COUNT(*) FROM person
按照效率排序:couunt(字段) < count(主键id) < count(1) <count(*)
select count(age) as '总和' from person
select max(age) as '最大年龄' from person
select min(age) as '最小年龄' from person
select avg(age) as '平均' from person
8.3. 数据库级别的MD5加密
md5加密不可逆,同一字符串加密结果一样。所以所谓破解,是将简单常用的收藏为字典,进行反向键值对(加密后-加密前)匹配
update testmd5 set pwd = MD5(pwd)
insert into testmd5 values(4,'长江七号',md5('123456'))
9. 事务
- 将一组sql放在一个批次中去执行~ 要么都成功、要么都失败
参考博客:https://blog.csdn.net/dengjili/article/details/82468576
9.1. 事务原则
事务原则:ACID原则 原子性,一致性,隔离性,持久性
原子性:针对同一个事务,要么都完成,要么都不完成
一致性:事务操作前后的数据完整性保持一致
持久性:事务提交了,持久化到数据库。事务一旦提交,则不可逆
隔离性:多个用户同时操作,排除其他事务对本事务的影响
隔离所导致的一些问题
脏读:指一个事务读取了另外一个事务未提交的数据。
不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。
虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)
MySQL事务隔离级别
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)
9.2. 事务执行
执行事务
-- mysql默认开启事务自动提交
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启
-- 手动处理事务
set autocommit = 0 -- 关闭
-- 事务开启
start transaction -- 标记一个事务,这个开始的sql要么全成功,要么全失败
insert XXX
insert XXX
-- 提交
commit
-- 回滚 回到原来的样子
rollback
-- 事务结束
set autocommit = 1 -- 开启
/*
了解
*/
savepoint 保存点名称 -- 设置一个事务的保存点
-- 回滚到保存点
rollback to savepoint 保存点名称
-- 删除保存点
release savepoint 保存点名称
-- 模拟转账
set autocommit = 0;
start transaction
update account set money = money - 500 where `name` = 'A'
update account set money = money + 500 where `name` = 'B'
commit;
rollback;
set autocommit = 1;
10. 索引
10.1. 索引的分类
- 主键索引(PRIMARY KEY)
- 唯一标识,不允许被索引的数据列包含重复的值,不允许有空值,一个表只能有一个主键
- 唯一索引(UNIQUE KEY)
- 唯一索引可以有多列,不允许被索引的数据列包含重复的值,允许有空值
- 常规索引(KEY/INDEX)
- 默认的,允许被索引的数据列包含重复的值
- 全文索引(FullText index)
- 在特定的数据库引擎下才有
- 快速定位数据
-- 显示所有索引
show index from person
-- 分析查询语句
explain select * from person
-- 常规索引
-- 创建
CREATE INDEX indexName ON table_name (column_name)
-- alert增加
ALTER table tableName ADD INDEX indexName(columnName)
-- 建表时指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
-- 唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
ALTER table mytable ADD UNIQUE [indexName] (username(length))
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
-- 删除索引
ALTER TABLE testalter_tbl DROP INDEX c;
...
建立索引的时机:
一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL的B-Tree只对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE才会使用索引。
10.2. 测试索引
数据量小时,区别不大。数据量大时,区别明显。可用explain查询select语句执行过程。空间换时间。
explain select * from person where `name` = 'zhangsan'
10.3. 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
11. 权限管理和备份
11.1. 权限管理
本质:对mysql.user表增删改查
-- 创建用户 create USER 用户名 identified by '密码'
create USER zmy identified by '123456'
-- 修改指定用户密码
set password for zmy = PASSWORD('111111')
-- 修改当前用户密码
set password = PASSWORD('111111')
-- 重命名
rename USER zmy TO zmy2
-- 授予最高权限 (唯独不能给别人授权。(root能))
grant all privileges on *.* to zmy
-- 查询指定用户权限
show grants for zmy
-- 查询管理员权限
show grants for root@localhost
-- 撤销权限
revoke all privileges on *.* from zmy
-- 删除用户
drop user zmy
11.2. 备份
- 直接拷贝物理文件
- 再可视化工具中备份数据库或者表
- 使用命令导出
//导出
mysqldump -h主机 -u用户名 -p密码 数据库 [表1 表2 表3] >物理磁盘位置+文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:\\a.sql
mysqldump -hlocalhost -uroot -p123456 school >D:\\b.sql
//导入
source D:\\a.sql
12. 数据库规约,三大范式
12.1. 设计数据库
糟糕的设计:
- 数据冗余,浪费空间
- 数据插入和删除都会麻烦,异常(屏蔽使用物理外键)
- 程序性能差
12.2. 三大范式
-
第一范式
要求数据库表的每一列都是不可分割的原子数据项
学校信息 家庭信息 研究生,研一 三口人,北京 研究生,研一应该分为两列
三口人,北京应该分为两列
-
第二范式
前提:满足第一范式
每张表只描述一件事情,保证每一列都和主键有关
-
第三范式
前提:满足第一范式和第二范式
确保数据表中的每一列数据都和主键直接相关,而不能间接相关
学号 姓名 性别 班主任姓名 班主任性别 201 张三 男 陈六 女 202 李四 男 孙七 女 203 王五 男 周八 女 班主任性别直接依赖是班主任姓名,应该分成两张表:
学号 姓名 性别 201 张三 男 202 李四 男 203 王五 男 班主任姓名 班主任性别 陈六 女 孙七 女 周八 女
13. JDBC
10.1. 什么是JDBC?
Sun公司为了简化开发人员对数据库的操作,提供了一个java操作数据库的规范,俗称JDBC,不用的数据库由对应的厂商实现。
- java.sql
- javax.sql
- mysql-connector-java-x.x.x.jar : 连接mysql 的驱动,由mysql的开发公司开发维护
Java中JDBC的使用方法:
import java.sql.*;
public class Test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//建立连接 获取数据库对象Connection
String url = "jdbc:mysql://localhost:3306/school";
String userName = "root";
String password = "123456";
Connection c = DriverManager.getConnection(url,userName,password);
//获取执行sql的对象
Statement state = c.createStatement();
String sql = "select * from `category`";
ResultSet rs = state.executeQuery(sql);
//rs.beforeFirst();//移动到最前面
//rs.afterLast();//移动到最后面
//rs.next();//移动到下一个
//rs.previous()//移动到前一行
//rs.absolute(row);//移动到指定行
while(rs.next()) {
System.out.println(rs.getObject("categoryName"));
}
//释放连接
rs.close();
state.close();
c.close();
}
}
封装后的版本
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 user = null;
private static String password = null;
private Connection conn = null;
private Statement state = null;
private ResultSet rs = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties pt = new Properties();
pt.load(in);
driver = pt.getProperty("driver");
url = pt.getProperty("url");
user = pt.getProperty("user");
password = pt.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
//加载驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public ResultSet exeQuery(String sql) {
try {
conn = getConnection();
state = conn.createStatement();
rs = state.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public int exeUpdate(String sql) {
int row = 0;
try {
conn = getConnection();
state = conn.createStatement();
row = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
public void release() {
if(rs !=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
//调用
JdbcUtils ju = new JdbcUtils();
String sql = "select * from `category`";
ResultSet rs = ju.exeQuery(sql);
try {
while(rs.next()) {
System.out.println(rs.getObject("categoryName"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ju.release();
}
10.2. SQL注入
使用Statement , sql存在漏洞,通过拼接sql字符串
public void login(String username,String password) {
JdbcUtils ju = new JdbcUtils();
String sql = "select * from users where name = '" + username + "' and password = '" + password + "'";
//拼接sql字符串存在sql注入问题,不安全
//当参数中的password传递为 " ' or 1 = 1 ",无视用户名密码就能查询到结果
ResultSet rs = ju.exeQuery(sql);
try {
while(rs.next()) {
System.out.println(rs.getObject("XXX"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ju.release();
}
}
使用PreparedStatement对象可以避免SQL注入(预编译sql、避免sql注入),并且效率更高。
import java.sql.*;
public class JdbcUtils {
public static void main(String[] args) throws ClassNotFoundException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//建立连接 获取数据库对象Connection
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "root";
String password = "123456";
Connection c = null;
PreparedStatement state = null;
ResultSet rs = null;
try {
c = DriverManager.getConnection(url,userName,password);
//获取执行sql的对象
String sql = "select * from `app_user` where id = ?";
state = c.prepareStatement(sql);//预编译 检查
state.setInt(1, 56);
rs = state.executeQuery();//不需要传递sql
while(rs.next()) {
System.out.println(rs.getObject("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
try {
if(rs != null)
rs.close();
if(state != null)
state.close();
if(c != null)
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
10.3.数据库连接池
连接释放很浪费资源,所以引入池。
开源数据源实现,实现DataSourace接口
知名的DataSource实现类:
DBCP
C3P0
Druid:阿里巴巴
DBCP
需要用到的jar包:
commons-dbcp-1.4、commons-pool-1.6
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class Study01 {
private static DataSource dataSource = null;
private Connection conn = null;
private Statement state = null;
private ResultSet rs = null;
static{
try{
InputStream in = Study01.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源
dataSource = BasicDataSourceFactory.createDataSource(properties);
}catch(Exception e){
e.printStackTrace();
}
}
public ResultSet exeQuery(String sql) {
try {
conn = getConnection();
state = conn.createStatement();
rs = state.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public int exeUpdate(String sql) {
int row = 0;
try {
conn = getConnection();
state = conn.createStatement();
row = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
private static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源获取连接
}
public void release() {
if(rs !=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//调用
import java.sql.*;
public class Test {
public static void main(String[] args) {
Study01 ju = new Study01();
String sql = "select * from `app_user`";
ResultSet rs = ju.exeQuery(sql);
try {
while(rs.next()) {
System.out.println(rs.getObject("id"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ju.release();
}
}
}
dbcp配置文件dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
C3P0
需要的jar包
c3p0-0.9.5.5.jar、mchange-commons-java-0.2.19.jar
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//c3p0
public class Study02 {
private static DataSource dataSource = null;
private Connection conn = null;
private Statement state = null;
private ResultSet rs = null;
static{
try{
//创建数据源
dataSource = new ComboPooledDataSource("Mysql");
}catch(Exception e){
e.printStackTrace();
}
}
public ResultSet exeQuery(String sql) {
try {
conn = getConnection();
state = conn.createStatement();
rs = state.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public int exeUpdate(String sql) {
int row = 0;
try {
conn = getConnection();
state = conn.createStatement();
row = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
private static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源获取连接
}
public void release() {
if(rs !=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//调用
import java.sql.*;
public class Test {
public static void main(String[] args) {
Study02 ju = new Study02();
String sql = "select * from `app_user`";
ResultSet rs = ju.exeQuery(sql);
try {
while(rs.next()) {
System.out.println(rs.getObject("id"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
ju.release();
}
}
}
c3p0-config.xml配置文件内容
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/school</property>
<property name="user">root</property>
<property name="password">123456</property>
</default-config>
<!-- This app is massive! -->
<named-config name="Mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/school</property>
<property name="user">root</property>
<property name="password">123456</property>
</named-config>
</c3p0-config>