MySQL入门
一、初识MySQL
1.命令行
1.进入root账户
mysql -u root -p
2.查看所有的数据库
show databases;
3.切换数据库
use [databases_name]
4.查看数据库中所有表
show tables;
5.显示数据库中所有的表的信息
describe [tables_name];
6.创建一个数据库
create database [databases_name]
7.退出链接
exit;
8.单行注释
--
9.多行注释
/**/
二、操作数据库
2.操作数据库
操作数据库->操作数据库中的表->操作数据库里的数据
1.创建数据库
create database [if not exists] westos;
2.删除数据库
drop database [if exists] westos;
3.使用数据库
use `school`;
4.查看数据库
show databases;
3.数据库的列类型
3.1 数据
| tinyint | 十分小的数据 | 1字节 |
| smallint | 较小的数据 | 2字节 |
| mediumint | 中等大小的数据 | 3字节 |
| int | 数据 | 4字节 |
| bigint | 较大的数据 | 8字节 |
| float | 浮点数 | 4字节 |
| double | 浮点数 | 8字节 |
| decimal | 字符串形式的浮点数 | 金融计算的时候，一般用decimal |
3.2 字符串
| char | 字符串固定大小的 | 0-255 | |
| varchar | 可变字符串 | 0-65535 | 常用的 类似于java的String |
| tinytext | 微型文本 | 2^8-1 | |
| text | 文本串 | 2^16-1 | 保存大文本 |
3.3 时间日期
java.util.Date
-
Date YYYY-MM-DD 日期格式 -
Time HH: mm: ss 时间格式 -
Datetime YYYY-MM-DD HH: MM: ss 最常用的时间格式 -
Timestamp 时间戳 1970.1.1到现在的毫秒数也较为常用 -
Year 年份表示
3.4 null
- 没有值，未知
- 不要使用null进行运算
4.!数据库的字段属性
4.1 unsighed:
- 无符号的整数
- 声明了该列不能声明为负数
4.2 zerofill：
- 0填充的
- 不足的位数，用0填充，int(3), 5 ... 005
4.3 自增aoto_increment：
- 通常理解为自增，自动在上一条记录的基础上+1
- 通常用来设计唯一主键~index，必须是整数类型
- 可以自定义设计自增的起步值和步长
4.4 非空NUll not null
- 假设设为not null,如果不赋值，就会报错
- Null如果不填写值，默认就是NULL
4.5 默认defaut
- 设置默认的值
- Sex 默认值为男，如果不指定该列的值，则会有默认的值
4.6 拓展
//每一个主键都应该存在一下5个字段
`id` 主键
`version` 乐观锁
`is_delete` 伪删除
`gmt_create` 创建时间
`gmt_update` 修改时间
5.!创建数据库表
5.1 实例
create table if not exists `student`(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment'密码',
`sex` varchar(20) not null default '女' comment '性别',
`birthday` datetime default null comment '生日',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`)
)engine=INNODB default charset = utf8;
5.2 格式
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
5.3 常用命令
SHOW CREATE DATABASE `school`; --查看数据库创建
SHOW CREATE TABLE student; --查看表创建
DESC `student`; --查看表的细节
6.数据表的类型
6.1 MYISAM与INNODB对比
/*
INNODB 默认使用
MYISAM 早些年使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为MYISAM的两倍 |
| 常规使用操作： |
- MYISAM 节约空间，速度较快
- INNODB 安全性高，支持事务处理，多表多用户操作
6.2 在物理空间存在的位置
所有的数据库文件都存在data目录下
本质还是文件的存储
6.3 MySQL引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个*.frm文件，以及上级文件目录的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
6.4 设置数据库表的字符集编码
charset=utf8
不设置的话，会是mysql默认的字符集编码latin1（不支持中文）
7.修改删除表
7.1 修改
7.1.1 修改表名
-- alter table [old_table_name] as [new_table_name];
-- 范例：
alter table student as teacher;
7.1.2 增加表的字段
-- alter table [table_name] add [字段名] [列属性]；
-- 示例：
alter table teacher add age int(3);
7.1.3 修改表的字段
alter table teacher modify age varchar(11); -- 修改约束
alter table teacher change age age1 int(1); -- 字段重命名 现在也可以修改约束
7.2 删除
7.2.1 删除表的字段
alter table teacher drop age1;
7.2.2 删除表(如果表存在再删除)
drop table if exists teacher1;
所有的创建和删除操作尽量加上判断，以免报错
三、MySQL数据管理
8.外键(了解)
图8.1：
- 将student(若为其他名，省略该步)删除
drop table student;
- 添加如下表：
CREATE TABLE `grade`(
`gradeId` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeId`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
8.1 添加外键的三种方式
8.1.1 创建表时添加约束
-- 学生表的 gradeId 字段 要去引用年级表的 gradeId
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
-- constraint 限制，约束
-- foreign 涉外的 外国的
create table if not exists `student`(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment'密码',
`sex` varchar(20) not null default '女' comment '性别',
`birthday` datetime default null comment '生日',
`gradeId` int(10) not null comment '学生的年级',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`),
key `FK_gradeId` (gradeId),
constraint `FK_gradeId` foreign key references `grade` (`gradeid`)
)engine=INNODB default charset = utf8;
8.1.2 修改外键关系
alter table `student` add constraint `FK_gradeId` foreign key (`gradeId`) references `grade`(`gradeId`)；
alter table [slave-table-name] add constraint `FK_slave-key` foreign key (`slave-key`) references `master-table-name` (`masger-key`)
以上操作都是物理外键，是数据库级别的外键，不建议使用！（避免数据库过多造成困扰）
8.1.3 最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（程序去实现）
9.!DML语言(全部记住)
数据库意义： 数据存储，数据管理
DML语言： 数据操作语言
- Insert
- update
- delete
9.1 添加
-- 插入语句，添加
insert into 表名([字段名1,字段名2,字段3])values(`值1`),(`值2`),(`值3`)，(...);
insert into `grade` (`gradeName`) values('大四')
-- 由于主键自增可以省略,如果不写表的字段，就会一一匹配
insert into `grade` values('大四'); -- 发生错误
-- 相当于：
insert into `grade`(`gradeId`,`gradeName`) values('大四',null);
尽量写上字段名
案例：
insert into student(`name`,`pwd`,`sex`) values('zhangshan','aaaaaa','man');
insert into `student`(`name`,`pwd`,`sex`) values('张三','aaaa','男'),
('李四','bbbb','男');
9.2 修改
- 语法1：
update 修改谁 (条件) set 原来的值 = 新值;
案例：
update `student` set name='狂神' where id = 1;
不指定条件的情况下,会改变所有的值
~~只能删库跑路~~
- 语法2：
update 表名 set colnum_name = value,[colnum_name = value,...] where [条件]
-- 修改多个属性
update `student` set `name` = '狂神', email = '24736743@qq.com' where id=1;
条件：where子句 运算符 id等于某个值 大于某个值 再某个区间
操作符会返回布尔值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| == | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5<>6 | true |
|||
<|||
=|||
<=|||
between ... and ...|[2,5]|在某个范围内|
and|&&||
or|或||
-- 通过多个条件定位数据
update `student` set `name`='长江7号' where name='狂神' and sex=`女`;
注意：
- colnum_name是数据库的列，尽量带上``
- 条件，是筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值，也可以是一个变量，如：
update `student` set `birthday` = current_time where `name`= '长江7号' and `sex` = '女';
9.3 删 除
9.3.1 删除
语法
delete from 表名 [where 条件];
-- 例子
delete from `student` where id = 3;
9.3.2 清空
作用：完全清空一个数据库表，表的结构和索引约束不会变。
truncate 表名;
truncate `student`;
9.3.3 delete和truncate区别
- 相同点： 都能删除数据，都不会删除表结构
- 不同：
- truncate 重新设置自增列 计数器会归零
- truncate 不会影响事务
- delete 不会影响自增
了解：delete删除的问题，重启数据库现象：
- InnoDB 自增列会从1开始（存在内存当中的，断电即失）
- MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
四、!!DQL查询数据（最重点）
10.DQL
(Data Query Language:数据查询语言)
- 所有的查询操作都用它 select
- 简单的查询，复杂的查询都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
10.1 select语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] #联合查询
[WHERE ...] #指定结果需满足的条件
[GROUP BY ...] #指定结果按照哪几个字段来分组
[HAVING] #过滤分组的记录必须满足的次要条件
[ORDER BY ...] #指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
# 指定查询的记录从哪条至哪条
注意 : [ ] 括号代表可选的 , { }括号代表必须得
提供素材:
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.19 : Database - school
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `school`;
/*Table structure for table `grade` */
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`GradeID`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*Data for the table `grade` */
INSERT INTO `grade`(`GradeID`,`GradeName`) VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
/*Table structure for table `result` */
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
`ExamDate` DATETIME NOT NULL COMMENT '考试日期',
`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
KEY `SubjectNo` (`SubjectNo`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Data for the table `result` */
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) VALUES (1000,1,'2013-11-11 16:00:00',94),(1000,2,'2012-11-10 10:00:00',75),(1000,3,'2011-12-19 10:00:00',76),(1000,4,'2010-11-18 11:00:00',93),(1000,5,'2013-11-11 14:00:00',97),(1000,6,'2012-09-13 15:00:00',87),(1000,7,'2011-10-16 16:00:00',79),(1000,8,'2010-11-11 16:00:00',74),(1000,9,'2013-11-21 10:00:00',69),(1000,10,'2012-11-11 12:00:00',78),(1000,11,'2011-11-11 14:00:00',66),(1000,12,'2010-11-11 15:00:00',82),(1000,13,'2013-11-11 14:00:00',94),(1000,14,'2012-11-11 15:00:00',98),(1000,15,'2011-12-11 10:00:00',70),(1000,16,'2010-09-11 10:00:00',74),(1001,1,'2013-11-11 16:00:00',76),(1001,2,'2012-11-10 10:00:00',93),(1001,3,'2011-12-19 10:00:00',65),(1001,4,'2010-11-18 11:00:00',71),(1001,5,'2013-11-11 14:00:00',98),(1001,6,'2012-09-13 15:00:00',74),(1001,7,'2011-10-16 16:00:00',85),(1001,8,'2010-11-11 16:00:00',69),(1001,9,'2013-11-21 10:00:00',63),(1001,10,'2012-11-11 12:00:00',70),(1001,11,'2011-11-11 14:00:00',62),(1001,12,'2010-11-11 15:00:00',90),(1001,13,'2013-11-11 14:00:00',97),(1001,14,'2012-11-11 15:00:00',89),(1001,15,'2011-12-11 10:00:00',72),(1001,16,'2010-09-11 10:00:00',90),(1002,1,'2013-11-11 16:00:00',61),(1002,2,'2012-11-10 10:00:00',80),(1002,3,'2011-12-19 10:00:00',89),(1002,4,'2010-11-18 11:00:00',88),(1002,5,'2013-11-11 14:00:00',82),(1002,6,'2012-09-13 15:00:00',91),(1002,7,'2011-10-16 16:00:00',63),(1002,8,'2010-11-11 16:00:00',84),(1002,9,'2013-11-21 10:00:00',60),(1002,10,'2012-11-11 12:00:00',71),(1002,11,'2011-11-11 14:00:00',93),(1002,12,'2010-11-11 15:00:00',96),(1002,13,'2013-11-11 14:00:00',83),(1002,14,'2012-11-11 15:00:00',69),(1002,15,'2011-12-11 10:00:00',89),(1002,16,'2010-09-11 10:00:00',83),(1003,1,'2013-11-11 16:00:00',91),(1003,2,'2012-11-10 10:00:00',75),(1003,3,'2011-12-19 10:00:00',65),(1003,4,'2010-11-18 11:00:00',63),(1003,5,'2013-11-11 14:00:00',90),(1003,6,'2012-09-13 15:00:00',96),(1003,7,'2011-10-16 16:00:00',97),(1003,8,'2010-11-11 16:00:00',77),(1003,9,'2013-11-21 10:00:00',62),(1003,10,'2012-11-11 12:00:00',81),(1003,11,'2011-11-11 14:00:00',76),(1003,12,'2010-11-11 15:00:00',61),(1003,13,'2013-11-11 14:00:00',93),(1003,14,'2012-11-11 15:00:00',79),(1003,15,'2011-12-11 10:00:00',78),(1003,16,'2010-09-11 10:00:00',96),(1004,1,'2013-11-11 16:00:00',84),(1004,2,'2012-11-10 10:00:00',79),(1004,3,'2011-12-19 10:00:00',76),(1004,4,'2010-11-18 11:00:00',78),(1004,5,'2013-11-11 14:00:00',81),(1004,6,'2012-09-13 15:00:00',90),(1004,7,'2011-10-16 16:00:00',63),(1004,8,'2010-11-11 16:00:00',89),(1004,9,'2013-11-21 10:00:00',67),(1004,10,'2012-11-11 12:00:00',100),(1004,11,'2011-11-11 14:00:00',94),(1004,12,'2010-11-11 15:00:00',65),(1004,13,'2013-11-11 14:00:00',86),(1004,14,'2012-11-11 15:00:00',77),(1004,15,'2011-12-11 10:00:00',82),(1004,16,'2010-09-11 10:00:00',87),(1005,1,'2013-11-11 16:00:00',82),(1005,2,'2012-11-10 10:00:00',92),(1005,3,'2011-12-19 10:00:00',80),(1005,4,'2010-11-18 11:00:00',92),(1005,5,'2013-11-11 14:00:00',97),(1005,6,'2012-09-13 15:00:00',72),(1005,7,'2011-10-16 16:00:00',84),(1005,8,'2010-11-11 16:00:00',79),(1005,9,'2013-11-21 10:00:00',76),(1005,10,'2012-11-11 12:00:00',87),(1005,11,'2011-11-11 14:00:00',65),(1005,12,'2010-11-11 15:00:00',67),(1005,13,'2013-11-11 14:00:00',63),(1005,14,'2012-11-11 15:00:00',64),(1005,15,'2011-12-11 10:00:00',99),(1005,16,'2010-09-11 10:00:00',97),(1006,1,'2013-11-11 16:00:00',82),(1006,2,'2012-11-10 10:00:00',73),(1006,3,'2011-12-19 10:00:00',79),(1006,4,'2010-11-18 11:00:00',63),(1006,5,'2013-11-11 14:00:00',97),(1006,6,'2012-09-13 15:00:00',83),(1006,7,'2011-10-16 16:00:00',78),(1006,8,'2010-11-11 16:00:00',88),(1006,9,'2013-11-21 10:00:00',89),(1006,10,'2012-11-11 12:00:00',82),(1006,11,'2011-11-11 14:00:00',70),(1006,12,'2010-11-11 15:00:00',69),(1006,13,'2013-11-11 14:00:00',64),(1006,14,'2012-11-11 15:00:00',80),(1006,15,'2011-12-11 10:00:00',90),(1006,16,'2010-09-11 10:00:00',85),(1007,1,'2013-11-11 16:00:00',87),(1007,2,'2012-11-10 10:00:00',63),(1007,3,'2011-12-19 10:00:00',70),(1007,4,'2010-11-18 11:00:00',74),(1007,5,'2013-11-11 14:00:00',79),(1007,6,'2012-09-13 15:00:00',83),(1007,7,'2011-10-16 16:00:00',86),(1007,8,'2010-11-11 16:00:00',76),(1007,9,'2013-11-21 10:00:00',65),(1007,10,'2012-11-11 12:00:00',87),(1007,11,'2011-11-11 14:00:00',69),(1007,12,'2010-11-11 15:00:00',69),(1007,13,'2013-11-11 14:00:00',90),(1007,14,'2012-11-11 15:00:00',84),(1007,15,'2011-12-11 10:00:00',95),(1007,16,'2010-09-11 10:00:00',92),(1008,1,'2013-11-11 16:00:00',96),(1008,2,'2012-11-10 10:00:00',62),(1008,3,'2011-12-19 10:00:00',97),(1008,4,'2010-11-18 11:00:00',84),(1008,5,'2013-11-11 14:00:00',86),(1008,6,'2012-09-13 15:00:00',72),(1008,7,'2011-10-16 16:00:00',67),(1008,8,'2010-11-11 16:00:00',83),(1008,9,'2013-11-21 10:00:00',86),(1008,10,'2012-11-11 12:00:00',60),(1008,11,'2011-11-11 14:00:00',61),(1008,12,'2010-11-11 15:00:00',68),(1008,13,'2013-11-11 14:00:00',99),(1008,14,'2012-11-11 15:00:00',77),(1008,15,'2011-12-11 10:00:00',73),(1008,16,'2010-09-11 10:00:00',78),(1009,1,'2013-11-11 16:00:00',67),(1009,2,'2012-11-10 10:00:00',70),(1009,3,'2011-12-19 10:00:00',75),(1009,4,'2010-11-18 11:00:00',92),(1009,5,'2013-11-11 14:00:00',76),(1009,6,'2012-09-13 15:00:00',90),(1009,7,'2011-10-16 16:00:00',62),(1009,8,'2010-11-11 16:00:00',68),(1009,9,'2013-11-21 10:00:00',70),(1009,10,'2012-11-11 12:00:00',83),(1009,11,'2011-11-11 14:00:00',88),(1009,12,'2010-11-11 15:00:00',65),(1009,13,'2013-11-11 14:00:00',91),(1009,14,'2012-11-11 15:00:00',99),(1009,15,'2011-12-11 10:00:00',65),(1009,16,'2010-09-11 10:00:00',83),(1010,1,'2013-11-11 16:00:00',83),(1010,2,'2012-11-10 10:00:00',87),(1010,3,'2011-12-19 10:00:00',89),(1010,4,'2010-11-18 11:00:00',99),(1010,5,'2013-11-11 14:00:00',91),(1010,6,'2012-09-13 15:00:00',96),(1010,7,'2011-10-16 16:00:00',72),(1010,8,'2010-11-11 16:00:00',72),(1010,9,'2013-11-21 10:00:00',98),(1010,10,'2012-11-11 12:00:00',73),(1010,11,'2011-11-11 14:00:00',68),(1010,12,'2010-11-11 15:00:00',62),(1010,13,'2013-11-11 14:00:00',67),(1010,14,'2012-11-11 15:00:00',69),(1010,15,'2011-12-11 10:00:00',71),(1010,16,'2010-09-11 10:00:00',66),(1011,1,'2013-11-11 16:00:00',62),(1011,2,'2012-11-10 10:00:00',72),(1011,3,'2011-12-19 10:00:00',96),(1011,4,'2010-11-18 11:00:00',64),(1011,5,'2013-11-11 14:00:00',89),(1011,6,'2012-09-13 15:00:00',91),(1011,7,'2011-10-16 16:00:00',95),(1011,8,'2010-11-11 16:00:00',96),(1011,9,'2013-11-21 10:00:00',89),(1011,10,'2012-11-11 12:00:00',73),(1011,11,'2011-11-11 14:00:00',82),(1011,12,'2010-11-11 15:00:00',98),(1011,13,'2013-11-11 14:00:00',66),(1011,14,'2012-11-11 15:00:00',69),(1011,15,'2011-12-11 10:00:00',91),(1011,16,'2010-09-11 10:00:00',69),(1012,1,'2013-11-11 16:00:00',86),(1012,2,'2012-11-10 10:00:00',66),(1012,3,'2011-12-19 10:00:00',97),(1012,4,'2010-11-18 11:00:00',69),(1012,5,'2013-11-11 14:00:00',70),(1012,6,'2012-09-13 15:00:00',74),(1012,7,'2011-10-16 16:00:00',91),(1012,8,'2010-11-11 16:00:00',97),(1012,9,'2013-11-21 10:00:00',84),(1012,10,'2012-11-11 12:00:00',82),(1012,11,'2011-11-11 14:00:00',90),(1012,12,'2010-11-11 15:00:00',91),(1012,13,'2013-11-11 14:00:00',91),(1012,14,'2012-11-11 15:00:00',97),(1012,15,'2011-12-11 10:00:00',85),(1012,16,'2010-09-11 10:00:00',90),(1013,1,'2013-11-11 16:00:00',73),(1013,2,'2012-11-10 10:00:00',69),(1013,3,'2011-12-19 10:00:00',91),(1013,4,'2010-11-18 11:00:00',72),(1013,5,'2013-11-11 14:00:00',76),(1013,6,'2012-09-13 15:00:00',87),(1013,7,'2011-10-16 16:00:00',61),(1013,8,'2010-11-11 16:00:00',77),(1013,9,'2013-11-21 10:00:00',83),(1013,10,'2012-11-11 12:00:00',99),(1013,11,'2011-11-11 14:00:00',91),(1013,12,'2010-11-11 15:00:00',84),(1013,13,'2013-11-11 14:00:00',98),(1013,14,'2012-11-11 15:00:00',74),(1013,15,'2011-12-11 10:00:00',92),(1013,16,'2010-09-11 10:00:00',90),(1014,1,'2013-11-11 16:00:00',64),(1014,2,'2012-11-10 10:00:00',81),(1014,3,'2011-12-19 10:00:00',79),(1014,4,'2010-11-18 11:00:00',74),(1014,5,'2013-11-11 14:00:00',65),(1014,6,'2012-09-13 15:00:00',88),(1014,7,'2011-10-16 16:00:00',86),(1014,8,'2010-11-11 16:00:00',77),(1014,9,'2013-11-21 10:00:00',86),(1014,10,'2012-11-11 12:00:00',85),(1014,11,'2011-11-11 14:00:00',86),(1014,12,'2010-11-11 15:00:00',75),(1014,13,'2013-11-11 14:00:00',89),(1014,14,'2012-11-11 15:00:00',79),(1014,15,'2011-12-11 10:00:00',73),(1014,16,'2010-09-11 10:00:00',68),(1015,1,'2013-11-11 16:00:00',99),(1015,2,'2012-11-10 10:00:00',60),(1015,3,'2011-12-19 10:00:00',60),(1015,4,'2010-11-18 11:00:00',75),(1015,5,'2013-11-11 14:00:00',78),(1015,6,'2012-09-13 15:00:00',78),(1015,7,'2011-10-16 16:00:00',84),(1015,8,'2010-11-11 16:00:00',95),(1015,9,'2013-11-21 10:00:00',93),(1015,10,'2012-11-11 12:00:00',79),(1015,11,'2011-11-11 14:00:00',74),(1015,12,'2010-11-11 15:00:00',65),(1015,13,'2013-11-11 14:00:00',63),(1015,14,'2012-11-11 15:00:00',74),(1015,15,'2011-12-11 10:00:00',67),(1015,16,'2010-09-11 10:00:00',65),(1016,1,'2013-11-11 16:00:00',97),(1016,2,'2012-11-10 10:00:00',90),(1016,3,'2011-12-19 10:00:00',77),(1016,4,'2010-11-18 11:00:00',75),(1016,5,'2013-11-11 14:00:00',75),(1016,6,'2012-09-13 15:00:00',97),(1016,7,'2011-10-16 16:00:00',96),(1016,8,'2010-11-11 16:00:00',92),(1016,9,'2013-11-21 10:00:00',62),(1016,10,'2012-11-11 12:00:00',83),(1016,11,'2011-11-11 14:00:00',98),(1016,12,'2010-11-11 15:00:00',94),(1016,13,'2013-11-11 14:00:00',62),(1016,14,'2012-11-11 15:00:00',97),(1016,15,'2011-12-11 10:00:00',76),(1016,16,'2010-09-11 10:00:00',82),(1017,1,'2013-11-11 16:00:00',100),(1017,2,'2012-11-10 10:00:00',88),(1017,3,'2011-12-19 10:00:00',86),(1017,4,'2010-11-18 11:00:00',73),(1017,5,'2013-11-11 14:00:00',96),(1017,6,'2012-09-13 15:00:00',64),(1017,7,'2011-10-16 16:00:00',81),(1017,8,'2010-11-11 16:00:00',66),(1017,9,'2013-11-21 10:00:00',76),(1017,10,'2012-11-11 12:00:00',95),(1017,11,'2011-11-11 14:00:00',73),(1017,12,'2010-11-11 15:00:00',82),(1017,13,'2013-11-11 14:00:00',85),(1017,14,'2012-11-11 15:00:00',68),(1017,15,'2011-12-11 10:00:00',99),(1017,16,'2010-09-11 10:00:00',76);
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`LoginPwd` VARCHAR(20) DEFAULT NULL,
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别，取值0或1',
`GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',
`Address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空，即可选输入',
`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`StudentNo`),
UNIQUE KEY `IdentityCard` (`IdentityCard`),
KEY `Email` (`Email`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
/*Data for the table `student` */
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) VALUES (1000,'111111','郭靖',1,1,'13500000001','北京海淀区中关村大街1号','1986-12-11 00:00:00','test1@bdqn.cn','450323198612111234'),(1001,'123456','李文才',1,2,'13500000002','河南洛阳','1981-12-31 00:00:00','test1@bdqn.cn','450323198112311234'),(1002,'111111','李斯文',1,1,'13500000003','天津市和平区','1986-11-30 00:00:00','test1@bdqn.cn','450323198611301234'),(1003,'123456','武松',1,3,'13500000004','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612314234'),(1004,'123456','张三',1,4,'13500000005','北京市通州','1989-12-31 00:00:00','test1@bdqn.cn','450323198612311244'),(1005,'123456','张秋丽 ',2,1,'13500000006','广西桂林市灵川','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311214'),(1006,'123456','肖梅',2,4,'13500000007','地址不详','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311134'),(1007,'111111','欧阳峻峰',1,1,'13500000008','北京东城区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311133'),(1008,'111111','梅超风',1,1,'13500000009','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311221'),(1009,'123456','刘毅',1,2,'13500000011','安徽','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311231'),(1010,'111111','大凡',1,1,'13500000012','河南洛阳','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311044'),(1011,'111111','奥丹斯',1,1,'13500000013','北京海淀区中关村大街*号','1984-12-31 00:00:00','test1@bdqn.cn','450323198412311234'),(1012,'123456','多伦',2,3,'13500000014','广西南宁中央大街','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311334'),(1013,'123456','李梅',2,1,'13500000015','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311534'),(1014,'123456','张得',2,4,'13500000016','北京海淀区中关村大街*号','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311264'),(1015,'123456','李东方',1,4,'13500000017','广西桂林市灵川','1976-12-31 00:00:00','test1@bdqn.cn','450323197612311234'),(1016,'111111','刘奋斗',1,1,'13500000018','上海卢湾区','1986-12-31 00:00:00','test1@bdqn.cn','450323198612311251'),(1017,'123456','可可',2,3,'13500000019','北京长安街1号','1981-09-10 00:00:00','test1@bdqn.cn','450323198109108311'),(10066,'','Tom',1,1,'13500000000','','0000-00-00 00:00:00','email@22.com','33123123123123123');
/*Table structure for table `subject` */
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
`GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`SubjectNo`)
) ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
/*Data for the table `subject` */
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) VALUES (1,'高等数学-1',110,1),(2,'高等数学-2',110,2),(3,'高等数学-3',100,3),(4,'高等数学-4',130,4),(5,'C语言-1',110,1),(6,'C语言-2',110,2),(7,'C语言-3',100,3),(8,'C语言-4',130,4),(9,'JAVA第一学年',110,1),(10,'JAVA第二学年',110,2),(11,'JAVA第三学年',100,3),(12,'JAVA第四学年',130,4),(13,'数据库结构-1',110,1),(14,'数据库结构-2',110,2),(15,'数据库结构-3',100,3),(16,'数据库结构-4',130,4),(17,'C#基础',130,1);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
10.2 指定查询字段
10.2.1 查询全部
语法：
select 字段, ... from 表
有时候，列名字不是那么见名知意，就起别名 AS
字段名 as 别名
表名 as 别名
案例：
-- 查询全波的学生 select 字段 from 表
SELECT * FROM student;
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名，给结果起一个名字 as
-- 可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号, StudentName AS 学生姓名 FROM student AS s;
-- 函数 Concat(a, b) 拼接字符串
SELECT CONCAT('姓名：', StudentName) AS 新名字 FROM student;
10.2.2 去重
查询全部的考试成绩:
select * from result;
查询有哪些同学参加了考试：
SELECT `StudentNo` FROM result;
发现重复数据，去重distinct:
SELECT DISTINCT `StudentNo` FROM result;
作用:去除select查询出来的结果中重复的数据，重复的数据只显示一条
使用数据库可以做的一些事情：
select version();-- 查询版本系统(函数)
select 100+3-2 as 计算结果; -- 用来计算(表达式)
select @@auto_increment_increment --查询自增的步长(变量) v8.0不可用
-- 学员考试成绩+1分查看
select `StudentNo`,`StudentResult` + 1 as `提分后` from result;
数据库中的表达式： 文本值，列，null，函数，计算表达式，系统变量……
select 表达式 from 表；
10.3 where条件子句
作用:检索数据中符合条件的值
where输入一个布尔值
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a && b | 逻辑与 |
| or || | a or b a||b | 逻辑或 |
| Not ! | not a !a | 逻辑非 |
| 尽量使用英文符号 |
10.3.1 !模糊查询
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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 | SQL匹配，如果a匹配b，则结果为真 |
| in | a in (a1,a2,...) | 假设a在a1，或者a2...其中的某一个值中，结果为真 |
- 案例 like：
- 查询姓刘的同学，名字后面只有一个字的
select StudentNo,StudentName from student where StudentName like '刘_'; - 查询姓刘的同学，名字后面有两个字的
select StudentNo,StudentName from student where StudentName like '刘__'; - 查询名字中间有三字的
select StudentNo,StudentName from student where StudentName like '%三%';
- 查询姓刘的同学，名字后面只有一个字的
- 案例 in(具体的一个或多个值 不能用%和_)
- 查询1001，1002，1003号学员
select StudentNo,StudentName from student where StudentNo in (1001,1002,1003); - 查询在安徽的学生
select StudentNo,StudentName from student where `Address` in '安徽';
- 查询1001，1002，1003号学员
- 案例null not null
- 查询地址为空的学生 null
select StudentNo,StudentName from student where `Address` = '' or is null; - 查询有出生日期的同学
select StudentNo,StudentName from student where Borndate is not null;
- 查询地址为空的学生 null
10.4 联表查询
10.4.1 join
初学比较复杂，推荐链接SQL的各种连接Join详解
10.4.2 自连接
自己的表和自己的表连接，核心：
一张表拆为两张一样的表即可
父类：
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 信息开发 |
| 5 | 美术开发 |
子类：
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
附上表:
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;
INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
查询：
SELECT a.`categoryName` AS '父栏目',
b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`pid` = b.`categoryid`;
10.5 分页和排序
分页 limit
语法 limit 起始值,页面的大小
排序 order by
升序 asc
降序 desc
/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)
推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
......
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]
*/
-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
#查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10
10.6 子查询
where(这个值是计算出来的)
本质: 在where语句中嵌套一个子查询语句
SELECT studentNo,subjectNo,studentResult
FROM result r
WHERE `SubjectNo` = (
SELECT subjectNo FROM `subject`
WHERE `SubjectName` = '数据库结构-1'
)
ORDER BY studentResult DESC
LIMIT 0,10
SELECT DISTINCT s.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
WHERE studentResult >= 80 AND subjectNo = (
SELECT subjectNo FROM `subject` WHERE subjectName = '高等数学-2'
)
SELECT studentNo,studentName FROM student WHERE studentNo IN (
SELECT studentNo FROM result WHERE StudentResult>80 AND subjectNo = (
SELECT subjectNo FROM `subject` WHERE `subjectName` = '高等数学-2'
)
)
10.7 分组
group by
-- 查询不同课程的大于80的科目的平均分
-- 核心： 根据不同的课程分组
SELECT subjectName,AVG(studentResult) AS 平均分,
MAX(studentResult) AS 最高分,MIN(studentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.subjectNo
HAVING 平均分 >= 80
五、MySQL函数
官网：https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html
11.常用函数
-- 数学运算
SELECT ABS(-8)
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(-09) -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱helloworld',1,2,'超级热爱') -- 插入，替换 [a,b],从1开始
-- 会被换成‘超级热爱helloworld’
-- 可以把此处2换成100之类会将所有删除换成‘超级热爱’
SELECT LOWER('LKJsdfa') -- 小写字母
SELECT UPPER('jdsflkLL') -- 大写字母
SELECT INSTR('asdfsdaf','s') -- 返回第一次出现的字串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('努力就能成功',4) -- 返回指定的子字符串（源字符串，截取位置 [,截取长度]）
SELECT REVERSE('努力就能成功') -- 反转
-- 查询姓李的的同学
SELECT * FROM student
WHERE studentName LIKE '李%'
-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 当前日期
SELECT CURDATE() -- 获取当期日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MINUTE(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
12.聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| ... | ... |
-- 查询不同课程的大于80的科目的平均分
-- 核心： 根据不同的课程分组
SELECT subjectName,AVG(studentResult) AS 平均分,
MAX(studentResult) AS 最高分,MIN(studentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.subjectNo
HAVING 平均分 >= 80
13.数据库级别的MD5加密（扩展）
md5不可逆，具体的值的md5值是一样的
CREATE TABLE testmd5(
id INT(4) NOT NULL,
`name` VARCHAR(100) NOT NULL,
pwd VARCHAR(100) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1
-- 插入时候加密
insert into testmd5 values(4,'xiaoming',md5('123456'))
-- 如何检校：将用户传递进来的密码，进行md5加密，然后对比加密后的值
select * from testmd5 where `name`='小明' and pwd=md5('123456')
六、事务
要么都成功，要么都失败
——————
1、SQL执行 A给B转账 A1000 --> 200 B 200
2、SQL执行 B收到A的钱 A800 --> B 400
——————
将一组SQL放在一个批次中去执行~
事务原则：ACID原则 原子性 一致性 隔离性 持久性 （脏读、幻读、……）
https://blog.csdn.net/dengjili/article/details/82468576
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。一致性（Consistency）
事务前后数据的完整性必须保持一致。隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
mysql是默认开启事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启（默认）
/* 使用set语句来改变自动提交模式 */
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
/* 注意
1.MySQL中默认是自动提交
2.使用事务时应先关闭自动提交
*/
/*开始一个事务,标记事务的起始点*/
START TRANSACTION
/*提交一个事务给数据库*/
COMMIT
/*将事务回滚,数据回到本次事务的初始状态*/
ROLLBACK
/*还原MySQL数据库的自动提交*/
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
模拟场景
SET autocommit = 0;
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE account(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO account(`name`,`money`)VALUES('a',2000.00),('b',10000.0);
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务（一组事务）
UPDATE account SET money = money-500 WHERE `name` = 'a'; -- a减500
UPDATE account SET money = money+500 WHERE `name` = 'b'; -- b加500
COMMIT; -- 提交事务，就被持久化了！
ROLLBACK; -- 回滚
SET autocommit = 1; --恢复默认值
七、索引
索引是帮助mysql高效获取数据的数据结构
作用 :
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
分类 :
-
主键索引 (Primary Key)
- 唯一标识，主键不可重复，只能有一个列作为主键
-
唯一索引 (Unique)
- 不允许具有索引值相同的行，唯一索引可以重复，多个列都可表示为唯一索引
-
常规索引 (Index)
- 默认的，index / key关键字来设置
-
全文索引 (FullText)
- 在特定的数据库引擎下才有，MyISAM，现在很多都有了
- 快速定位数据
1.主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
2.唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
-
主键索引只能有一个
-
唯一索引可能有多个
3.常规索引
作用 : 快速定位特定数据
注意 :
-
index 和 key 关键字都可以设置常规索引
-
应加在查询找条件的字段
-
不宜添加太多常规索引,影响数据的插入,删除和修改操作
4.全文索引
作用 : 快速定位特定数据
注意 :
- 只能用于MyISAM类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
5.开始学习索引
创建表:
CREATE TABLE `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男；1：女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';
创建一个函数:
SET GLOBAL log_bin_trust_function_creators=TRUE;
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
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),
CONCAT(i,'@qq.com'),
CONCAT('18',FLOOR(RAND()*999999999-100000000)),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*2)
);
-- 在上方插入语句
SET i = i + 1;
END WHILE;
RETURN i;
END;
运行:
SELECT mock_data();
可在增加索引前看看没加索引的时候查询速度：
SELECT * FROM app_user WHERE `name`='用户9999'
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'
增加索引
-- id_表名_字段名
CREATE INDEX 索引名 ON 表(字段);
CREATE INDEX id_app_user_name ON app_user(`name`);
再看看增加索引后的查询速度：
SELECT * FROM app_user WHERE `name`='用户9999'
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'
/*
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
6.索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加再常用来查询的字段上！
索引的数据结构
八、权限管理和备份
1.用户管理
可视化界面
sql命令操作
用户表：mysql.user
本质：对这张表增删改查
-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER test IDENTIFIED BY '123456' ;
-- 修改密码
SET PASSWORD = PASSWORD('111111');
/*注：在mysql8.0以上版本，
update mysql.user set password='newpassword' where user='root';
和
update mysql.user set password=PASSWORD('newpassword') where User='root';
这两条命令已经不起作用了。*/
ALTER USER test IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '111111';
-- 重命名
RENAME USER 原来名字 TO 新名字;
RENAME USER test TO test2;
-- 用户授权 all privileges 全部的权限，库.表
-- all privileges 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO test2;
-- 查看权限
SHOW GRANTS FOR test2;
SHOW GRANTS FOR root@localhost;
-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM test2;
-- 删除用户
DROP USER test2;
2.MySQL备份
- 保证重要的数据不丢失
- 数据转移 A------>B
MySQL数据库备份的方式:
-
直接拷贝
-
使用类似与SQLyog这种可视化工具中手动导出
-
在想要导出的表或者库中，右键，选择备份或导出：
-
-
-
使用命令行导出 mysqldump
-
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名 mysqldump -h localhost -u root -p qq123456 school student >D:/a.sql #在MySQL8中 mysqldump -h localhost -u root -p school student >D:/a.sql #回车后会请求输入密码，之后再执行
-
-
用命令行导入
-
在登录的情况下
source d:/a.sql -
注意，要使用符合数据要求的数据库
show databases;查看数据库 #如果 mydb 不存在，先创建 mydb create database mydb; #再选中 mydb use mydb; -
或者：
mysql -u 用户名 -p 库名< 备份文件
-
九、规范数据库设计
1.为什么需要设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（比如个人博客）
- 收集信息，分析需求
- 用户表（用户登陆注销，用户个人信息，写博客，//创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- //自定义表（系统信息，某个关键的字，或者一些主字段）key:value
- 标识实体之间的关系（把需求落地到每个字段）
- 写博客：user-->blog
- 创建分类：user-->category
- 关注：user-->user
- 友链：links
- 评论：user-user-blog
2.三大范式
为什么需要数据规范化：
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，适当考虑一下规范性
- 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
十、！JDBC(重点)
1.数据库驱动
驱动：比如声卡、显卡，数据库也要驱动
程序会通过数据库驱动和数据库打交道
2.JDBC
sun公司为了简化开发人员对数据库的统一操作，提供了一个java操作数据库的规范，俗称JDBC
规范的实现由具体厂商操作
对于开发人员来说，只需要掌握JDBC的接口的操作即可
没有什么是加一层是解决不了的，如果有，再加一层
java.sql
javax.sql
还需要导入一个数据库驱动包
3.第一个JDBC程序
创建测试数据库：
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users (id,`name`,`password`,email,birthday)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
-
创建一个Maven项目
-
导入数据库驱动依赖
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.17</version> </dependency> -
编写测试代码
mysql错误：java.sql.SQLException: The server time zone value '�й���ʱ��' is unrecognized or represents
package com.huangdekai.javaWebLearn; // 第一个JDBC程序 import java.sql.*; public class jdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.用户信息和url //教程中只有useUnicode=true&characterEncoding=utf8&useSSL=true //出现了上述连接的mysql错误，在url里添加了serverTimezone=UTC String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC"; String username = "root"; String password = "qq123456"; //3.连接成功，数据库对象 Connection代表数据库 Connection connection = DriverManager.getConnection(url, username, password); //4.执行SQL的对象 Statement statement = connection.createStatement(); //5.执行SQL的对象去执行SQL，可能存在结果，查看返回结果 String sql = "SELECT * FROM users"; //返回的结果集，结果封装了我们全部的查询出来的结果 ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("name")); System.out.println("password="+resultSet.getObject("password")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birthday="+resultSet.getObject("birthday")); } //6.释放连接 resultSet.close(); statement.close(); connection.close(); } }
步骤总结：
-
加载驱动
Class.forname("com.mysql.jdbc.Driver");现在似乎推荐用
Class.forname("com.mysql.cj.jdbc.Driver");上述程序可互换
-
连接数据库DriverManager
-
获得执行sql的对象 Statement
-
获得返回的结果集
-
释放连接
DriverManager
//DriverManager.registerDriver(new com.sql.jdbc.Driver());
Class.forName("com.sql.jdbc.Driver");//固定写法，加载驱动
Connection connection = DriverManager.getConnection(url,username,password);
// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
// mysql ---- 3306
/*
jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
协议://主机端口:端口号
*/
// oralce ---- 1521
/*
jdbc:oracle:thin@localhost:1521:sid
*/
Statment 执行SQL的对象
prepareStatement执行SQL的对象
String sql = "select * from users";//编写SQL
statement.executeQuery();//查询操作返回ResultSet
statement.execute(); //执行任何SQL，因此效率慢点
statement.executeUpdate();//更新、插入、删除，都是用这个，返回受影响的行数
ResultSet 查询的结果集：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();
4.statement对象
jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作：
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.excuteUpdate(sql);
if(num>0){
System.out.println("插入成功");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作，示例操作：
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.excuteUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
CRUD操作-update
Statement st = conn.createStatement();
String sql = "update user set `name`='' where `name`=''";
int num = st.excuteUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
CRUD操作-read
使用executeQuery(String sql)方法完成数值查询操作，示例操作：
Statement st = conn.createStatement();
String sql = "select * from user where id = 1";
ResultSet rs = st.excuteQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}
代码实现
-
准备
-
路径
-
db.properties内容
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC username = root password = qq123456
-
-
提取工具类
package com.huangdekai.javaWebLearn.utils; 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 (Exception 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){ if (resultSet != null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement != null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } -
编写增删改的方法
executeUpdate
package com.huangdekai.javaWebLearn;
import static org.junit.Assert.assertTrue;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Unit test for simple App.
*/
public class AppTest
{
@Test
public void TestInsert(){
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,'bilibili','123122','bilibili@qq.com','1970-01-01')," +
"(5,'danlidanli','122342','danlidanlli@qq.com','1970-01-01')" ;
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
@Test
public void TestDelete(){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "DELETE from users where `id` = 4" ;
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
@Test
public void TestUpdate(){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "update users set `name`='duzhuan2' where `id` = 2" ;
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
- 查询
executeQurey
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestExecuteQurey {
@Test
public void testExecuteQurey(){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from users";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id=" + resultSet.getInt("id"));
System.out.println("name=" + resultSet.getString("name"));
System.out.println("password=" + resultSet.getString("password"));
System.out.println("email=" + resultSet.getString("email"));
System.out.println("birthday="+resultSet.getDate("birthday"));
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
sql注入的问题
sql存在漏洞，会被攻击，导致数据泄露，SQL会被拼接 or
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLDI {
public static void main(String[] args) {
//login("duzhuan","123456");//正常输入
login(" 'or '1=1","' or'1=1");
// select * from users where `name` = '' or '1=1' and `password` =''or'1=1'
}
// 登陆业务
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+"'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
5.PreparedStatement对象
PreparedStatement 可以防止SQL注入，效率更好
目录结构
增删改
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import org.junit.Test;
import javax.swing.text.Utilities;
import java.sql.*;
import java.util.Date;
public class TestPreparedStatment {
@Test
public void testInsert(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符
String sql = "insert into " +
"users(`id`,`name`,`password`,`email`,`birthday`) " +
"value(?,?,?,?,?)";//占位符
preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//手动给参数赋值
preparedStatement.setInt(1,4); //id赋值
preparedStatement.setString(2,"杜撰");
preparedStatement.setString(3,"aa123456");
preparedStatement.setString(4,"123123@qq.com");
//注意：sql.Date java java.sal.Date()
// util.Date 数据库 new Date().getTime()获得时间戳
preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
@Test
public void testDelete(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符
String sql = "Delete from users where id = ?";//占位符
preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//手动给参数赋值
preparedStatement.setInt(1,4);
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
@Test
public void testUpdate(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用?占位符
String sql = "update users set `id` = 0 where id = ?";//占位符
preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//手动给参数赋值
preparedStatement.setInt(1,4);
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
查
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestPreparedStatementQuery {
@Test
public void testPrepareStatementQuery() throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,0);//传递参数
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("name = "+resultSet.getString("name"));
}
}catch (Exception e){
e.printStackTrace();
}
}
}
防止SQL注入
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import java.sql.*;
public class SQLDIPreparedStatement {
public static void main(String[] args) {
//login("duzhuan","123456");//正常输入
login("'' or 1=1","'' or 1=1");
// select * from users where `name` = '' or '1=1' and `password` =''or'1=1'
}
// 登陆业务
public static void login(String username,String password){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符
//假设其中存在转义字符，引号会被直接转义
String sql = "select * from users where `name`= ? and `password`= ?";
statement = connection.prepareStatement(sql);
statement.setString(1,username);
statement.setString(2,password);
resultSet = statement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
6.使用IDEA连接数据库
可能出现的问题的解决方法链接：
连接成功后可以选择数据库:
7.事务
要么都成功，要么都失败
ACID原则
原子性：要么都成功，要么都失败
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库中
隔离性的问题：
脏读：一个事务读取到另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致
代码实现
- 开启事务
connection.setAutoCOmmit(false) - 一组业务执行完毕，提交事务
- 可以在catch语句中显式定义回滚语句，但是默认失败就会回滚
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1
{
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//关闭数据库的自动提交，自动会开启事务
connection.setAutoCommit(false);
String sql1 = "update account set money = money - 100 where `name` = 'a'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//int x = 1/0;//报错
String sql2 = "update account set money = money + 100 where `name` = 'b'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//业务完毕，提交事务
connection.commit();
System.out.println("操作成功");
} catch (SQLException e) {
//如果不写，也默认回滚
try {
connection.rollback();//如果失败，就回滚
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
8.数据库连接池
池化技术----执行完毕----释放
连接 -- 释放 十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
常用连接数：假设10个
最小连接数：5、10、100 按常用连接数来 按以上假设，假设设10个
假设还有5个资源
最大连接数：业务最高承载上线，按照以上假设，是15个
设有100个，排队等待
等待超时：100ms
编写连接池，实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要连接数据库的代码了
DBCP
pom.xml添加依赖
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
dbcpconfig.properties
#连接设置 这里的名字，是DBCP数据源中定义好的
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username = root
password = qq123456
#下面的东西可以默认不设置
#初始化连接
initialSize = 10
#最大连接数量
maxActive = 50
#最大空闲连接
maxIdle = 20
#最小连接数量
minIdle = 5
#超时等待时间以毫秒为单位 60000ms/10000 = 60s
maxWait = 60000
#JDBC驱动建立连接时附带的连接属性的格式必须为【属性名=property;】
#注意："user"与"password"两个属性会被明显传递，因此这里不需要包含他们
connectionProperties=useUnicode=true;characterEncoding=UTF8;serverTimezone=UTC
#指定由连接池所创建的连接的自动提交(auto-commit)状态
defaultAutoCommit=true
# driver default指定由连接池所创建的连接的只读(read-only)状态
# 如果没有设置该值，则"setReadOnly"方法将不被调用，（某些驱动不支持只读模式，如：Informix）
defaultReadOnly =
# driver default指定由连接池所创建的连接的事务级别（TransactionIsolation）
# 可用值为下列之一：（详情见javadoc）NONE,READ_UNCOMMITTED,REPEATABLE_READ,SERIALIZABLF...
defaultTransactionIsolation=READ_UNCOMMITTED
工具类JdbcUtils_DBCP
package com.huangdekai.javaWebLearn.utils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static{
try {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式 --》 创建对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//从数据源中获取连接
}
//释放连接资源
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试：
package com.huangdekai.javaWebLearn;
import com.huangdekai.javaWebLearn.utils.JdbcUtils;
import com.huangdekai.javaWebLearn.utils.JdbcUtils_DBCP;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestDBCP {
@Test
public void testPrepareStatementQuery() throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_DBCP.getConnection();
String sql = "select * from users where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,0);//传递参数
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("name = "+resultSet.getString("name"));
}
} catch (Exception e){
e.printStackTrace();
} finally {
JdbcUtils_DBCP.release(connection,preparedStatement,null);
}
}
}
C3P0
依赖:
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
.....
结论
无论使用什么数据源，本质是一样的，DataSource接口不会变，方法就不会变
浙公网安备 33010602011771号