1、操作数据库
1.1、数据库操作
-
创建数据库
create database [if not exists] student
-
查看数据库
show databases
-
删除数据库
drop database [if exists] student
-
使用数据库
use database
1.2、数据库的列类型
数值
名称 | 描述 | 大小 |
---|---|---|
tinyint | 十分小的数据 | 1个字节 |
smallint | 较小的数据 | 2个字节 |
mediumint | 中等大小数据 | 3个字节 |
int | 标准的整数 | 4个字节 |
bigint | 较大的整数 | 8个字节 |
float | 浮点数 | 4个字节 |
double | 浮点数 | 8个字节 |
decimal | 字符串形式的浮点数,用于金融计算 |
字符串
名称 | 描述 | 大小 |
---|---|---|
char | 固定的字符串 | 0~255 |
varchar | 可变的字符串,常用变量 string | 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进行计算,结果为NULL
1.3、数据库的表操作
- 创建表
create table
CREATE TABLE `Teacher` (
`id` int(10) NOT NULL AUTO_INCREMENT ,
`name` varchar(100) NOT NULL ,
`age` int(3) NOT NULL ,
`brethdate` datetime NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
- 查看表创建语句
show create table Teacher
- 显示表的具体结构
DESC Teacher
- 修改表
-- 修改表名: alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1
-- 增加表的字段:
alter table teacher1 add age int(11)
-- 修改表的字段(重命名,修改约束!)
-- 修改约束:alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varvhar(11)
-- 字段重命名:alter table 表名 change 旧字段名 新字段名 列属性[]
alter table teacher1 change age age1 int(1)
-- 删除表的字段:alter table 表名 drop 字段名
alter table teacher1 drop age1
- 删除表
-- 删除表(如果表存在再删除)
drop table if exists teacher1
1.4、数据库表的类型
-- 关于数据库引擎
/*
InnoDB 默认使用
MyISAM 早些年使用的
*/
MyISAM | InnoDB | |
---|---|---|
事物支持 | 不支持 | 支持 |
数据行锁定 | 不支持(表锁定) | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约2倍 |
常规使用操作:
- MyISAM 节约空间,速度较快
- InnoDB 安全性高,事物的处理,多表多用户操作
2、MySQL数据库管理
名称 | 意义 |
---|---|
DDL | 数据定义语言 |
DQL | 数据查询语言 |
DML | 数据操纵语言 |
DCL | 数据控制语言 |
2.1、DML数据库操作语言(全部记住)
数据库意义: 数据存储,数据管理
DML语言: 数据操作语言
- insert into 表名([字段名1,字段名2,…])values(”值1“,”值2“,…)
- update 表名 set 字段名 = 值 where 字段名 = 值
- delete from 表名 where 字段名 = 值
truncate table 表名 与 delete from 表名 清空数据的区别
-- 清空student表
truncate table student -- 自增归零
delete from student -- 不影响自增
2.2、DQL数据库查询语言(重点)
DQL语言:Data Query Language
distinct 去重
-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT studentno FROM result; -- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)
select语法
SELECT [ALL | DISTINCT]
{* | table.* | field1,field2,...}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE] -- 指定结果需满足的条件
[GROUP BY field1,field2] -- 指定结果按照哪几个字段来分组,字段之间用“,”隔开
[HAVING condition1 and conditon2] -- 过滤分组的记录必须满足的次要条件,条件之间用 and 链接
[ORDER BY field1 ASC,field2 DESC] -- 指定查询记录按一个或多个条件排序,字段之间用“,”隔开
[LIMIT {[staterow], pageSize}]; -- 分页查询,从staterow(0表示第一条)开始查询,查询pageSize条
2.3、MySQL函数
- 数据函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整ceiling*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
- 字符串函数
SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
- 日期和时间函数
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
- 系统信息函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
- 聚合函数
函数名 | |
---|---|
count() | 行数 |
sum() | 总值 |
avg() | 平均数 |
max() | 最大值 |
min() | 最小值 |
… | … |
MySQL官方文档:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
2.4、事物(Transaction)
什么是事物
- 事物就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消
事物的ACID原则
-
原子性(Atomic)
要么都成功,要么都失败。
-
一致性(Consist)
事物前后的数据完整要保持一致,例如:两个人互相转账钱总数不变。
- 隔离性(Isolated)
每个事物操作互不干扰。
- 持久性 (Durable)
事物一旦提交则不可逆转,被持续化的数据库中。
3、索引
MySQL官方对索引的定义为:索引是帮助MySQL高效取数据的数据结构
3.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引(Primary key)
唯一的标识,主键不可重复,只能有一个列作为主键
-
唯一索引(Unique key)
避免重复的列出现,唯一索引可以重复,多个列都可以标识
- 常规索引(Index/key)
默认的,index
-
全文索引(FullText)
在特定的数据库引擎下才有,MyISAM
# CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
3.2、索引使用原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
4、使用JDBC连接数据库
URL
jdbc:mysql://localhost:3306/sys?useUnicode=true&characterEncoding=utf8&&useSSL=true
Statement 执行SQL语句
statement.executeQuery(); //执行查询语句
statement.execute(); //执行所有语句
statement.executeUpdate(); //执行更新、删除、插入语句
Resultset 返回 查询 结果集
resultset.getObject(); //在不知道列的类型的情况下使用
//如果知道列的类型就使用指定的类型
resultset.getString();
resultset.getInt();
resultset.getFloat();
使用Statement有sql注入风险
public class App {
public static void main(String[] args) throws Exception {
App.jdbcText("'q' or 1=1");
}
public static void jdbcText(String str) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?useUnicode=true&characterEncoding=utf8&useSSL=true",
"root", "123456");
Statement statement = con.createStatement();
String sql = "select * from login where loginUserName ="+str;
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("loginUserName"));
System.out.println(resultSet.getObject("loginPassWord"));
}
resultSet.close();
statement.close();
con.close();
}
}
使用preparedstatement 防止sql注入
public class PreparedStatementText {
public static void main(String[] args) throws Exception {
//PreparedStatementText.Prep("'q' or 1=1");
PreparedStatementText.Prep("123");
}
public static void Prep(String str) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?useUnicode=true&character=utf8&useSSL=true",
"root", "123456");
String sql = "select * from login where loginUserName =?";
PreparedStatement pre = con.prepareStatement(sql);
pre.setString(1,str);
ResultSet resultSet = pre.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("loginUserName"));
System.out.println(resultSet.getObject("loginPassWord"));
}
pre.close();
con.close();
}
}
5、连接池
导入架包
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
<!-- HikariCP 连接池 -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.3.1</version>
</dependency>
5.1、DBCP
- 创建工具类
public class JdbcUtil_DBCP {
private static DataSource dataSource;
private static BasicDataSource ba = new BasicDataSource();
static {
try {
ba.setDriverClassName("com.mysql.jdbc.Driver");
ba.setUsername("root");
ba.setPassword("123456");
ba.setUrl("jdbc:mysql://localhost:3306/sys?useUnicode=true&character=utf8&useSSL=true");
ba.setInitialSize(10);//初始化时创建10个链接
ba.setMaxActive(8);//设置最大连接数
ba.setMaxIdle(5);//这只最大的空闲连接数
ba.setMinIdle(1);//设置最小空闲连接数字
}catch (Exception e){
e.printStackTrace();
}
}
public static Connection getConnaction() throws Exception {
return ba.getConnection();
}
public static void closeConnation(Connection connection) throws Exception {
if ( connection!=null){
connection.close();
}
}
}
- 测试类
public class DbcpText {
public static void main(String[] args) throws Exception {
Connection connection = null;
try {
connection = JdbcUtil_DBCP.getConnaction();
String str = "select * from login where loginUserName =123";
PreparedStatement preparedStatement = connection.prepareStatement(str);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("loginUserName"));
System.out.println(resultSet.getObject("loginPassWord"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
JdbcUtil_DBCP.closeConnation(connection);
}
}
}
5.2、Hikaricp
/**
* hikaricp 连接池实例
*/
public class HikariDemo {
public static void main(String[] args) {
//配置文件
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/sys?useUnicode=true&character=utf8&useSSL=true");
hikariConfig.setDriverClassName("com.mysql.jdbc.Driver");
hikariConfig.setUsername("root");
hikariConfig.setPassword("123456");
hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
HikariDataSource ds = new HikariDataSource(hikariConfig);
Connection con = null;
Statement statement = null;
ResultSet resultSet = null;
try {
con = ds.getConnection();
statement = con.createStatement();
resultSet = statement.executeQuery("select * from login");
while(resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("loginUserName"));
System.out.println(resultSet.getObject("loginPassWord"));
}
con.close();
}catch (Exception e){
e.printStackTrace();
}
}
}