• 欢迎光临~

MySQL

开发技术 开发技术 2022-07-19 次浏览

MySQL:

学习视频-狂神说MySQL:视频链接

MySQL

1、初识MySQL

JavaEE:企业级Java开发

前端(页面:展示,数据!)

后台(连接点:连接数据库JDBC,链接前端(控制,控制视图跳转,和给前端传递数据))

数据库(存数据,Txt,Excel,word)

程序员等级

只会写代码,学好数据库,基本混饭吃;

操作系统,数据结构与算法!当一个不错的程序猿!

离散数学,数字电路,体系结构,编译原理。+实战经验,高级程序猿优秀的程序猿

1.1、为什么学习数据库

  1. 岗位需求

  2. 现在的世界,大数据时代~,得数据库者得天下

  3. 被迫需求:存数据

  4. 数据库是所有软件体系中最核心的存在 DBA

1.2、什么是数据库

  1. 数据库(DB,DataBase)

  2. 概念:数据仓库,软件,安装在操作系统(windows,linux,mac,…)之上!SQL,可以存储大量的数据。500万!

  3. 作用:存储数据、管理数据

1.3、数据库分类

关系型数据库:SQL

(Excel)行、列

  • MySQL,Oracle、Sql Server , DB2,SQLlite

  • 通过表和表之间,行和列之间的关系进行数据的存储,学员信息表,考勤表…

非关系型数据库:(NoSQL)not only

{key: value}

  • Redis、MongDB

  • 非关系型数据库,对象存储,通过对象自身的属性来决定。

DBMS(数据库管理系统)

  • 数据库管理系统 ( DataBase Management System )

  • 数据库的管理软件 , 科学有效的管理我们的数据。维护和获取数据

  • MySQL,数据库管理系统!

MySQL

为什么要说这个呢?
因为我们要学习的MySQL应该算是一个数据库管理系统.

1.4、MySQL简介

  • MySQL是一个关系型数据库管理系统

  • 历史 : 由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。

  • MySQL是最好的RDBMS(Relational Database Management System,关系型数据库管理系统)应用软件之一。

  • 开源的数据库软件~

  • 体积小、速度快、总体拥有成本低,招人成本比较低,所有人必须会~

  • 中小型网站、或者大型网站,集群!

  • 官网 : https://www.mysql.com/

  • 5.7稳定

  • 8.0稳定

安装建议:

  1. 尽量不用使用exe,注册表不好删

  2. 尽可能使用压缩包安装~

1.5、安装MySQL

  • 安装教程

1、下载及配置

1.下载安装
  1. 下载所需版本的MySQL

  2. 得到zip压缩包

  3. 解压

  4. 安装包放到自己的电脑环境目录下D:Program Filesmysql-5.7.32

2.配置环境变量
  1. 我的电脑->属性->高级->环境变量

  2. 选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹

3.新建MySQL配置文件 my.ini
  1. 在D:Program Filesmysql-5.7.32目录下新建my.ini文件

  2. 编辑 my.ini 文件 ,注意替换路径位置

    [mysqld]
    basedir=D:Program Filesmysql-5.7.32
    # data目录不需要新建
    datadir=D:Program Filesmysql-5.7.32data 
    port=3306
    skip-grant-tables
    # root
    
5.安装mysql
  • 启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,然后输入mysqld –install (安装mysql)
6.初始化数据文件
  • 输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
7.重启mysql
  • 输入命令 net start mysql 重启mysql
8.进入mysql管理界面
  • 使用命令 mysql –u root –p 进入mysql管理界面(密码可为空)
9.更改root密码
  • sql语句后面一定要加分号;
update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost';
10.刷新权限
flush privileges;
11.修改 my.ini 文件
  • 删除最后一句:skip-grant-tables
12.重启mysql
exit	# 退出mysql
net stop mysql	# 停止MySQL服务
net start mysql	# 启动MySQL服务
13.连接测试
  • 连接上,如果测试出现以下结果,就说明已经安装成功

MySQL

清空服务,不要随意使用
sc delete mysql

2、MySQL的几种错误情况:

  • cmd安装MySQL出现:The current server installed

    • 因为:之前安装过MySQL数据库,卸载时没有清理彻底
  • Mysql2013连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'

    • 因为:mysql开启了DNS的反向解析功能
  • 解决报错:错误1130- Host xxx is not allowed to connect to this MariaDb server

    • 原因:权限问题(确保服务器安全组3306端口已经开放)

1.6、安装SQLyog

1、下载安装注册

  • SQLyog12.0.8.0中文破解版(接近页面底部有下载链接,可下载安装注册)

  • 即便有了可视化工具,可是基本的DOS命名大家还是要记住!

  • SQLyog:可手动操作,管理MySQL数据库的软件工具

    • 特点 : 简洁 , 易用 , 图形化

2、打开连接数据库

MySQL

3、新建一个数据库 school

MySQL

4、新建一张表 student

字段:id,name,age,

MySQL

5、查看表

  • 右键student表→打开表

6、尝试添加多条记录

  • 右键student表→打开表→添加数据

1.7、连接数据库

命令行连接!

mysql -uroot -proot -- 连接数据库
---------------------------------
-- 所有的sql语句都使用分号;结尾
update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
---------------------------------
show databases; -- 查看所有的数据库

use school; -- 切换数据库
Database changed
---
show tables; -- 查看数据库中所有的表
describe student; -- 显示表的所有的信息

create database tangguo; -- 创建一个数据库

exit; -- 退出连接

-- 单行注释(sql本来的注释)
/*(sql的多行注释)*/

数据库 xxx 语言 CRUD增删改查! CV 程序猿 API 程序猿 CRUD 程序猿!(业务)

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

MySQL的关键字不区分大小写

2.1、操作数据库(了解)

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2.删除数据库

DROP DATABASE [IF EXISTS] westos;

3.使用数据库

-- 如果你的表名或者字段名是一个特殊字符,需要带``
USE `school`;

4.查看数据库

SHOW DATABASES; -- 查看所有的数据库

学习思路:

  • 对照 SQLyog可视化历史记录查看sql

  • 固定的语法或关键字必须要强行记住!

2.2、数据库的列类型

数值

  • tinyint 十分小的数据 1个字节

  • smallint 较小的数据 2个字节

  • mediumint 中等大小 3个字节

  • int 标准的整数 4个字节(常用)

  • bigint 较大的数据 8个字节

  • float 浮点数 4个字节

  • double 浮点数 8个字节 (精度问题)

  • decimal 字符串形式的浮点数,金融计算的时候,一般使用decimal

字符串

  • char 字符串固定大小 0-255

  • varchar 可变字符串 0-65535(常用的 S变量tring)

  • tinytext 微型文本 2^8-1

  • text 文本串 2^16-1 (保存大文本)

时间日期

java.util.Date

  • date YYYY-MM-DD 日期

  • time HH:mm:ss 时间格式

  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式

  • timestamp 时间戳,1970.1.1到现在的毫秒数

  • year 年份表示

null

  • 没有值,未知

  • 注意,不要使用null进行运算,结果为null

2.3、数据库的字段类型(重点)

unsigened:

  • 无符号的整数

  • 声明了该列,不能声明为负数

zerofill:

  • 0填充的

  • 不足的位数,使用0来填充

    • int(3):5 --- 003

自增:

  • 通常理解为自增,自动在上一条记录的基础上+1(默认)

  • 通常用来设计唯一的主键~ index,必须是整数类型

  • 可以自定义设置主键自增的起始值和步长

非空 NULL,not Null

  • 假设设置为 not null,如何不给他赋值,就会报错!

  • NULL,如果不填写,默认就是NULL!

默认:

  • 设置默认的值!

  • gender,默认值为男,如果不指定该列的值,则会有默认的值!

拓展:未来可能用

-- 每一个表都必须存在以下五个字段!未来做项目用的,表示一个记录存在的意义
id	-- 主键
`version`	-- 乐观锁
is_delete	-- 伪删除
gmt_create	-- 创建时间
gmt_update	-- 修改时间

2.4、创建数据库表(重点)

创建表

-- 目标:创建一个schoo1数据库
-- 创建学生表(列,字段) 使用SQL 创建
-- 学号int,登录密码varchar(20),姓名,性别varchar(2),出生日期(datetime),家庭住址,emai1

-- 注意点,使用英文(),表的 名称 和 字段 尽量使用 `` 括起来
-- AUTO_ INCREMENT 自增
-- 字符串使用 单引号 括起来!
-- 所有的语句后面加 ,(英文的),最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键!
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 '密码',
    `gender` VARCHAR(2) 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;

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

常用命令

SHOW CREATE DATABASE school -- 查看创建school数据库的语句
SHOW CREATE TABLE student 	-- 查看student数据表的定义语句
DESC student -- 显示表的结构

2.5、数据表的类型

数据库引擎

-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM 早些年使用的
*/
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大,约为MYISAM的两倍

常规使用操作:

  • MYISAM 节约空间,速度较快,

  • INNODB 安全性高,事务的处理,多表多用户操作

在物理空间存在的位置

  1. 所有的数据库文件都存在data目录下

  2. 本质还是文件的存储!

  3. MySQL 引擎在物理文件上的区别

  • innoDB 在数据库表中,只有一个*.frm文件,以及上级目录下的ibdata1文件

  • MYISAM 对应的文件

    • *.frm 表结构的定义文件

    • *. MYD 数据文件(data)

    • *.MYI 索引文件(index)

设置数据库表的字符集编码

CHARTSET=utf8

不设置的话,会是mysql默认的字符集编码(不支持中文!)

MySQL的默认编码是Latin1,不支持中文

可以在my.ini中配置默认的编码

character-set-server=utf8

2.6、修改删除表

修改

-- 修改表名 ALTER TABLE 旧表名 AS 新表名
ALTER TABLE student RENAME  AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段(重命名,修改约束)
ALTER TABLE student1 MODIFY age VARCHAR(11)  -- 修改约束
ALTER TABLE student1 CHANGE age age1 INT(1)  -- 字段重命名

-- 删除表的字段
ALTER TABLE student1 DROP age1

删除

-- 删除表
DROP TABLE IF EXISTS student1

所有的创建和删除操作尽量加上判断,以免报错

注意点:

  • `` 字段名,使用这个包裹

  • 注释 – /**/

  • sql 关键字大小写不敏感,建议写小写

  • 所有的符号全部用英文!

3、MySQL数据管理

3.1、外键(了解)

方式一:

  • 在创建表的时候,增加约束(麻烦,比较复杂)
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
-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束(执行引用) references 引用
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 '密码',
    `gender` varchar(2) 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(`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

删除有外键关系的表的时候,必须先删除引用的表(从表),再删除被引用的表(主表)

方式二:

  • 创建表成功后添加外键
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

-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束(执行引用) references 引用
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 '密码',
    `gender` varchar(2) 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`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)

以上的操作都是物理外键,数据库级别的外键,不建议使用!(避免数据库过多造成困扰,这里了解即可)

最佳实践

  • 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)

  • 我们想使用多张表的数据,想使用外键(程序去实现)

3.2、DML语言(全部记住)

数据库意义:数据存储,数据管理

DML语言:数据操作语言

  • Insert 增

  • update 改

  • delete 删

3.3、添加

insert

-- 插入语句(添加)
-- insert into 表名([字段1],[字段2]) values ('值11','值21'),('值12','值22')
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主键自增我们可以省略(如何不写表的字段,他就会一一匹配)
-- 一般写插入语句,我们必须要保证数据和字段一一对应。

-- 插入多个值
INSERT INTO `grade` (`gradename`) VALUES('大三'),('大二'),('大一');
-- 插入多个字段
INSERT INTO `student`(`name`,`pwd`,`gender`,`gradeid`) VALUES ('张三','zhangsan','男',1);
INSERT INTO `student`(`name`,`pwd`,`gender`,`gradeid`) VALUES ('李四','lisi','男',1),('王五','wangwu','女',1);

语法:insert into 表名([字段1],[字段2]) values ('值11','值21'),('值12','值22');

注意事项:

  1. 字段和字段之间用逗号分开

  2. 字段可以省略,但是后面的值必须一一对应,不能少

  3. 可以同时插入多条数据,VALUES后面的值需要使用,隔开即可values(),(),...

INSERT INTO `student`(`name`,`pwd`,`gender`) VALUES ('李四','lisi','男'),('王五','wangwu','女')

3.4、修改

update 修改谁(条件) set 原来的值=新值

-- 修改学员名字
UPDATE `student` SET `name`='糖果' WHERE id=1;

-- 不指定条件的情况下,会改动所有表
UPDATE `student` SET `name`='思雨';

-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='糖果',`email`='2715274537@qq.com' WHERE id=1;

-- 语法:
-- UPDATE 表名 set column_name=value,[column_name=value,...] where 条件

-- 更新多行数据(表中需要有唯一索引(唯一密钥或自增主键,其他字段要有默认值))
-- 创建表
DROP TABLE t;
CREATE TABLE `t` (
`id`INT(11)NOT NULL AUTO_INCREMENT,
`t1`VARCHAR(50)NOT NULL DEFAULT '',
`t2`VARCHAR(50)NOT NULL DEFAULT '',
`t3`VARCHAR(50)NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `t1` (`t1`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
-- 插入数据
INSERT INTO t VALUES(NULL,'a',1,1),(NULL,'b',1,1);
-- 根据唯一密钥,修改数据
INSERT INTO `t` (`id`,`t3`)VALUES(1,44),(2,55)
ON DUPLICATE KEY UPDATE `t3`=VALUES(`t3`);
-- 根据主键id,修改数据(replace和insert所作的工作完全相同,区别是当碰到有主键或唯一索引的列发生冲突时,对冲突的这一行,在insert前会先对这行数据执行delete操作。效果是这一行中没有被指定值的列会被更新成本列的默认值,如果所有的列都不冲突,此语法和简单的insert into语法效果一样。)
REPLACE INTO t (`id`,`t3`) VALUES(1,200);

条件:where 子句 运算符 id 等于 某个值,大于某个值,在某个区间内修改...

操作符返回布尔值

操作符 含义 范围 结果
= 等于 5=6 false
!= 或<> 不等于 5!=6或5<>6 true
> 大于
< 小于
>= 大于等于
<= 小于等于
between A and B 在某个范围内,闭合区间 between 2 and 5 [2,5]
and && 5>3 and 5<6 true
or || 5<4 or 5>7 false
-- 通过多个条件定位数据
UPDATE `student` SET `name`='糖果' WHERE `name`='思雨' AND `gender`='女';

语法: UPDATE 表名 set column_name=value,[column_name=value,...] where 条件

注意:

  • column_name 是数据库的列,尽量带上``

  • 条件,是筛选的条件,如果没有指定,则会修改所有的列

  • value 是一个具体的值,也可以是一个变量

    UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='糖果' AND gender='女';
    
  • 多个设置的属性之间,使用英文逗号隔开

    UPDATE `student` SET `name`='糖果',`birthday`=CURRENT_TIME WHERE `id`=3;
    

3.5、删除

delete 命令

语法 delete from 表名 [where 条件]

-- 删除数据 (避免这样写,会全部删除)
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` where id= 1;

truncate命令

作用:完全清空一个数据库表,表的结构和索引不会变!

-- 清空 student 表
TRUNCATE `student`;

delete 和 truncate区别

  • 相同点: 都能删除数据,都不会删除表结构

  • 不同:

    • TRUNCATE 重新设置自增列 计数器会归零

    • TRUNCATE 不会影响事务

-- 测试delete 和 truncate 区别
CREATE TABLE `test`(
    `id` INT(4) NOT NULL AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('a'),('b'),('c');

DELETE FROM `test`; -- 不会影响自增
TRUNCATE TABLE `test`; -- 自增会归零

了解即可:delete删除的问题 重启数据库,现象

  • InnoDB 自增列会从1开始(存在内存当中,断电即失)

  • MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)

4、DQL查询数据(最重点)

4.1、DQL

(Data Query Language) :数据查询语言

  • 所有的查询操作都用它 Select

  • 简单的查询,复杂的查询它都能做~

  • 数据库中最核心的语言

  • 使用频率最高的语言

创建查询数据

CREATE DATABASE IF NOT EXISTS `school`;

USE `school`;

-- 表grade
DROP TABLE IF EXISTS `grade`;-- 删除旧表
-- 新建表
CREATE TABLE `grade` (
  `GradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`GradeId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `grade` (`GradeName`) VALUES('大一'),('大二'),('大三'),('大四'),('预科班');

-- 表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 '考试日期',
    `ExamResult` INT(4) NOT NULL COMMENT '考试成绩',
    KEY `StudentNo`(`StudentNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`ExamResult`) 
VALUES(1000,1,'2016-10-10 16:00:00',95),
	(1000,2,'2017-10-10 16:00:00',92),
	(1000,3,'2018-10-10 16:00:00',73),
	(1000,4,'2019-10-10 16:00:00',68),
	(1000,5,'2016-10-10 16:00:00',62),
	(1000,6,'2017-10-10 16:00:00',60),
	(1000,7,'2018-10-10 16:00:00',98),
	(1000,8,'2019-10-10 16:00:00',100),
	(1000,9,'2016-10-10 16:00:00',97),
	(1000,10,'2017-10-10 16:00:00',65),
	(1000,11,'2018-10-10 16:00:00',65),
	(1000,12,'2019-10-10 16:00:00',73),
	(1000,13,'2016-10-10 16:00:00',69),
	(1000,14,'2017-10-10 16:00:00',83),
	(1000,15,'2018-10-10 16:00:00',61),
	(1000,16,'2019-10-10 16:00:00',65),
	(1001,1,'2016-10-10 16:00:00',60),
	(1001,2,'2017-10-10 16:00:00',87),
	(1001,3,'2018-10-10 16:00:00',85),
	(1001,4,'2019-10-10 16:00:00',60),
	(1001,5,'2016-10-10 16:00:00',73),
	(1001,6,'2017-10-10 16:00:00',63),
	(1001,7,'2018-10-10 16:00:00',80),
	(1001,8,'2019-10-10 16:00:00',74),
	(1001,9,'2016-10-10 16:00:00',72),
	(1001,10,'2017-10-10 16:00:00',74),
	(1001,11,'2018-10-10 16:00:00',75),
	(1001,12,'2019-10-10 16:00:00',86),
	(1001,13,'2016-10-10 16:00:00',68),
	(1001,14,'2017-10-10 16:00:00',89),
	(1001,15,'2018-10-10 16:00:00',80),
	(1001,16,'2019-10-10 16:00:00',66),
	(1002,1,'2016-10-10 16:00:00',69),
	(1002,2,'2017-10-10 16:00:00',77),
	(1002,3,'2018-10-10 16:00:00',85),
	(1002,4,'2019-10-10 16:00:00',72),
	(1002,5,'2016-10-10 16:00:00',93),
	(1002,6,'2017-10-10 16:00:00',88),
	(1002,7,'2018-10-10 16:00:00',66),
	(1002,8,'2019-10-10 16:00:00',81),
	(1002,9,'2016-10-10 16:00:00',90),
	(1002,10,'2017-10-10 16:00:00',76),
	(1002,11,'2018-10-10 16:00:00',96),
	(1002,12,'2019-10-10 16:00:00',68),
	(1002,13,'2016-10-10 16:00:00',91),
	(1002,14,'2017-10-10 16:00:00',99),
	(1002,15,'2018-10-10 16:00:00',83),
	(1002,16,'2019-10-10 16:00:00',96),
	(1003,1,'2016-10-10 16:00:00',68),
	(1003,2,'2017-10-10 16:00:00',97),
	(1003,3,'2018-10-10 16:00:00',93),
	(1003,4,'2019-10-10 16:00:00',87),
	(1003,5,'2016-10-10 16:00:00',85),
	(1003,6,'2017-10-10 16:00:00',70),
	(1003,7,'2018-10-10 16:00:00',99),
	(1003,8,'2019-10-10 16:00:00',78),
	(1003,9,'2016-10-10 16:00:00',85),
	(1003,10,'2017-10-10 16:00:00',69),
	(1003,11,'2018-10-10 16:00:00',84),
	(1003,12,'2019-10-10 16:00:00',85),
	(1003,13,'2016-10-10 16:00:00',88),
	(1003,14,'2017-10-10 16:00:00',73),
	(1003,15,'2018-10-10 16:00:00',79),
	(1003,16,'2019-10-10 16:00:00',97),
	(1004,1,'2016-10-10 16:00:00',65),
	(1004,2,'2017-10-10 16:00:00',68),
	(1004,3,'2018-10-10 16:00:00',88),
	(1004,4,'2019-10-10 16:00:00',77),
	(1004,5,'2016-10-10 16:00:00',93),
	(1004,6,'2017-10-10 16:00:00',67),
	(1004,7,'2018-10-10 16:00:00',64),
	(1004,8,'2019-10-10 16:00:00',75),
	(1004,9,'2016-10-10 16:00:00',82),
	(1004,10,'2017-10-10 16:00:00',94),
	(1004,11,'2018-10-10 16:00:00',80),
	(1004,12,'2019-10-10 16:00:00',60),
	(1004,13,'2016-10-10 16:00:00',76),
	(1004,14,'2017-10-10 16:00:00',99),
	(1004,15,'2018-10-10 16:00:00',82),
	(1004,16,'2019-10-10 16:00:00',69),
	(1005,1,'2016-10-10 16:00:00',96),
	(1005,2,'2017-10-10 16:00:00',95),
	(1005,3,'2018-10-10 16:00:00',79),
	(1005,4,'2019-10-10 16:00:00',84),
	(1005,5,'2016-10-10 16:00:00',91),
	(1005,6,'2017-10-10 16:00:00',88),
	(1005,7,'2018-10-10 16:00:00',87),
	(1005,8,'2019-10-10 16:00:00',100),
	(1005,9,'2016-10-10 16:00:00',65),
	(1005,10,'2017-10-10 16:00:00',99),
	(1005,11,'2018-10-10 16:00:00',74),
	(1005,12,'2019-10-10 16:00:00',60),
	(1005,13,'2016-10-10 16:00:00',94),
	(1005,14,'2017-10-10 16:00:00',86),
	(1005,15,'2018-10-10 16:00:00',61),
	(1005,16,'2019-10-10 16:00:00',97),
	(1006,1,'2016-10-10 16:00:00',63),
	(1006,2,'2017-10-10 16:00:00',61),
	(1006,3,'2018-10-10 16:00:00',95),
	(1006,4,'2019-10-10 16:00:00',89),
	(1006,5,'2016-10-10 16:00:00',71),
	(1006,6,'2017-10-10 16:00:00',67),
	(1006,7,'2018-10-10 16:00:00',75),
	(1006,8,'2019-10-10 16:00:00',77),
	(1006,9,'2016-10-10 16:00:00',82),
	(1006,10,'2017-10-10 16:00:00',86),
	(1006,11,'2018-10-10 16:00:00',74),
	(1006,12,'2019-10-10 16:00:00',74),
	(1006,13,'2016-10-10 16:00:00',83),
	(1006,14,'2017-10-10 16:00:00',89),
	(1006,15,'2018-10-10 16:00:00',96),
	(1006,16,'2019-10-10 16:00:00',82),
	(1007,1,'2016-10-10 16:00:00',80),
	(1007,2,'2017-10-10 16:00:00',77),
	(1007,3,'2018-10-10 16:00:00',100),
	(1007,4,'2019-10-10 16:00:00',85),
	(1007,5,'2016-10-10 16:00:00',64),
	(1007,6,'2017-10-10 16:00:00',93),
	(1007,7,'2018-10-10 16:00:00',82),
	(1007,8,'2019-10-10 16:00:00',81),
	(1007,9,'2016-10-10 16:00:00',81),
	(1007,10,'2017-10-10 16:00:00',80),
	(1007,11,'2018-10-10 16:00:00',98),
	(1007,12,'2019-10-10 16:00:00',87),
	(1007,13,'2016-10-10 16:00:00',67),
	(1007,14,'2017-10-10 16:00:00',73),
	(1007,15,'2018-10-10 16:00:00',92),
	(1007,16,'2019-10-10 16:00:00',88),
	(1008,1,'2016-10-10 16:00:00',80),
	(1008,2,'2017-10-10 16:00:00',90),
	(1008,3,'2018-10-10 16:00:00',97),
	(1008,4,'2019-10-10 16:00:00',98),
	(1008,5,'2016-10-10 16:00:00',97),
	(1008,6,'2017-10-10 16:00:00',76),
	(1008,7,'2018-10-10 16:00:00',98),
	(1008,8,'2019-10-10 16:00:00',77),
	(1008,9,'2016-10-10 16:00:00',67),
	(1008,10,'2017-10-10 16:00:00',84),
	(1008,11,'2018-10-10 16:00:00',82),
	(1008,12,'2019-10-10 16:00:00',60),
	(1008,13,'2016-10-10 16:00:00',87),
	(1008,14,'2017-10-10 16:00:00',72),
	(1008,15,'2018-10-10 16:00:00',64),
	(1008,16,'2019-10-10 16:00:00',62),
	(1009,1,'2016-10-10 16:00:00',80),
	(1009,2,'2017-10-10 16:00:00',62),
	(1009,3,'2018-10-10 16:00:00',92),
	(1009,4,'2019-10-10 16:00:00',90),
	(1009,5,'2016-10-10 16:00:00',79),
	(1009,6,'2017-10-10 16:00:00',86),
	(1009,7,'2018-10-10 16:00:00',64),
	(1009,8,'2019-10-10 16:00:00',84),
	(1009,9,'2016-10-10 16:00:00',84),
	(1009,10,'2017-10-10 16:00:00',91),
	(1009,11,'2018-10-10 16:00:00',90),
	(1009,12,'2019-10-10 16:00:00',66),
	(1009,13,'2016-10-10 16:00:00',62),
	(1009,14,'2017-10-10 16:00:00',91),
	(1009,15,'2018-10-10 16:00:00',97),
	(1009,16,'2019-10-10 16:00:00',79),
	(1010,1,'2016-10-10 16:00:00',81),
	(1010,2,'2017-10-10 16:00:00',74),
	(1010,3,'2018-10-10 16:00:00',74),
	(1010,4,'2019-10-10 16:00:00',95),
	(1010,5,'2016-10-10 16:00:00',77),
	(1010,6,'2017-10-10 16:00:00',76),
	(1010,7,'2018-10-10 16:00:00',99),
	(1010,8,'2019-10-10 16:00:00',68),
	(1010,9,'2016-10-10 16:00:00',81),
	(1010,10,'2017-10-10 16:00:00',82),
	(1010,11,'2018-10-10 16:00:00',91),
	(1010,12,'2019-10-10 16:00:00',89),
	(1010,13,'2016-10-10 16:00:00',100),
	(1010,14,'2017-10-10 16:00:00',76),
	(1010,15,'2018-10-10 16:00:00',91),
	(1010,16,'2019-10-10 16:00:00',85),
	(1011,1,'2016-10-10 16:00:00',68),
	(1011,2,'2017-10-10 16:00:00',61),
	(1011,3,'2018-10-10 16:00:00',68),
	(1011,4,'2019-10-10 16:00:00',94),
	(1011,5,'2016-10-10 16:00:00',70),
	(1011,6,'2017-10-10 16:00:00',91),
	(1011,7,'2018-10-10 16:00:00',87),
	(1011,8,'2019-10-10 16:00:00',88),
	(1011,9,'2016-10-10 16:00:00',96),
	(1011,10,'2017-10-10 16:00:00',60),
	(1011,11,'2018-10-10 16:00:00',69),
	(1011,12,'2019-10-10 16:00:00',62),
	(1011,13,'2016-10-10 16:00:00',10),
	(1011,14,'2017-10-10 16:00:00',98),
	(1011,15,'2018-10-10 16:00:00',95),
	(1011,16,'2019-10-10 16:00:00',85),
	(1012,1,'2016-10-10 16:00:00',78),
	(1012,2,'2017-10-10 16:00:00',10),
	(1012,3,'2018-10-10 16:00:00',82),
	(1012,4,'2019-10-10 16:00:00',70),
	(1012,5,'2016-10-10 16:00:00',62),
	(1012,6,'2017-10-10 16:00:00',79),
	(1012,7,'2018-10-10 16:00:00',71),
	(1012,8,'2019-10-10 16:00:00',89),
	(1012,9,'2016-10-10 16:00:00',78),
	(1012,10,'2017-10-10 16:00:00',80),
	(1012,11,'2018-10-10 16:00:00',66),
	(1012,12,'2019-10-10 16:00:00',69),
	(1012,13,'2016-10-10 16:00:00',85),
	(1012,14,'2017-10-10 16:00:00',92),
	(1012,15,'2018-10-10 16:00:00',67),
	(1012,16,'2019-10-10 16:00:00',75),
	(1013,1,'2016-10-10 16:00:00',99),
	(1013,2,'2017-10-10 16:00:00',69),
	(1013,3,'2018-10-10 16:00:00',91),
	(1013,4,'2019-10-10 16:00:00',97),
	(1013,5,'2016-10-10 16:00:00',83),
	(1013,6,'2017-10-10 16:00:00',73),
	(1013,7,'2018-10-10 16:00:00',74),
	(1013,8,'2019-10-10 16:00:00',67),
	(1013,9,'2016-10-10 16:00:00',81),
	(1013,10,'2017-10-10 16:00:00',99),
	(1013,11,'2018-10-10 16:00:00',85),
	(1013,12,'2019-10-10 16:00:00',96),
	(1013,13,'2016-10-10 16:00:00',91),
	(1013,14,'2017-10-10 16:00:00',82),
	(1013,15,'2018-10-10 16:00:00',71),
	(1013,16,'2019-10-10 16:00:00',84),
	(1014,1,'2016-10-10 16:00:00',82),
	(1014,2,'2017-10-10 16:00:00',85),
	(1014,3,'2018-10-10 16:00:00',73),
	(1014,4,'2019-10-10 16:00:00',78),
	(1014,5,'2016-10-10 16:00:00',84),
	(1014,6,'2017-10-10 16:00:00',93),
	(1014,7,'2018-10-10 16:00:00',77),
	(1014,8,'2019-10-10 16:00:00',65),
	(1014,9,'2016-10-10 16:00:00',84),
	(1014,10,'2017-10-10 16:00:00',75),
	(1014,11,'2018-10-10 16:00:00',68),
	(1014,12,'2019-10-10 16:00:00',75),
	(1014,13,'2016-10-10 16:00:00',100),
	(1014,14,'2017-10-10 16:00:00',67),
	(1014,15,'2018-10-10 16:00:00',92),
	(1014,16,'2019-10-10 16:00:00',98),
	(1015,1,'2016-10-10 16:00:00',84),
	(1015,2,'2017-10-10 16:00:00',84),
	(1015,3,'2018-10-10 16:00:00',76),
	(1015,4,'2019-10-10 16:00:00',83),
	(1015,5,'2016-10-10 16:00:00',84),
	(1015,6,'2017-10-10 16:00:00',97),
	(1015,7,'2018-10-10 16:00:00',92),
	(1015,8,'2019-10-10 16:00:00',87),
	(1015,9,'2016-10-10 16:00:00',81),
	(1015,10,'2017-10-10 16:00:00',91),
	(1015,11,'2018-10-10 16:00:00',68),
	(1015,12,'2019-10-10 16:00:00',68),
	(1015,13,'2016-10-10 16:00:00',71),
	(1015,14,'2017-10-10 16:00:00',94),
	(1015,15,'2018-10-10 16:00:00',89),
	(1015,16,'2019-10-10 16:00:00',96),
	(1016,1,'2016-10-10 16:00:00',100),
	(1016,2,'2017-10-10 16:00:00',93),
	(1016,3,'2018-10-10 16:00:00',75),
	(1016,4,'2019-10-10 16:00:00',70),
	(1016,5,'2016-10-10 16:00:00',86),
	(1016,6,'2017-10-10 16:00:00',68),
	(1016,7,'2018-10-10 16:00:00',80),
	(1016,8,'2019-10-10 16:00:00',68),
	(1016,9,'2016-10-10 16:00:00',79),
	(1016,10,'2017-10-10 16:00:00',72),
	(1016,11,'2018-10-10 16:00:00',76),
	(1016,12,'2019-10-10 16:00:00',99),
	(1016,13,'2016-10-10 16:00:00',74),
	(1016,14,'2017-10-10 16:00:00',68),
	(1016,15,'2018-10-10 16:00:00',89),
	(1016,16,'2019-10-10 16:00:00',96),
	(1017,1,'2016-10-10 16:00:00',63),
	(1017,2,'2017-10-10 16:00:00',84),
	(1017,3,'2018-10-10 16:00:00',86),
	(1017,4,'2019-10-10 16:00:00',63),
	(1017,5,'2016-10-10 16:00:00',75),
	(1017,6,'2017-10-10 16:00:00',76),
	(1017,7,'2018-10-10 16:00:00',80),
	(1017,8,'2019-10-10 16:00:00',73),
	(1017,9,'2016-10-10 16:00:00',68),
	(1017,10,'2017-10-10 16:00:00',72),
	(1017,11,'2018-10-10 16:00:00',95),
	(1017,12,'2019-10-10 16:00:00',68),
	(1017,13,'2016-10-10 16:00:00',88),
	(1017,14,'2017-10-10 16:00:00',67),
	(1017,15,'2018-10-10 16:00:00',90),
	(1017,16,'2019-10-10 16:00:00',72);

-- 表student
DROP TABLE IF EXISTS `student`;-- 删除旧表
-- 新建表
CREATE TABLE `student` (
  `StudentNo` INT(4) NOT NULL COMMENT '学号',
  `LoginPwd` VARCHAR(20) DEFAULT NULL COMMENT '登录密码',
  `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  `Gender` 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;
-- 插入数据
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Gender`,
	`GradeId`,`Phone`,`Address`,`BornDate`,`Email`,`IdentityCard`) 
VALUES(1000,'123456','糖果-1',1,1,'13500000000','河南','1997-01-01 00:00:00',
       'test@qq.com','412721199701019000'),
	(1001,'111111','糖果-2',1,2,'13500000001','河南','1997-01-02 00:00:00',
     'test@qq.com','412721199701029001'),
	(1002,'222222','糖果-3',1,3,'13500000002','湖南','1997-01-03 00:00:00',
     'test@qq.com','412721199701039002'),
	(1003,'666666','糖果-4',1,4,'13500000003','河北','1997-01-04 00:00:00',
     'test@qq.com','412721199701049003'),
	(1004,'123456','思雨-1',1,1,'13500000004','北京','1997-01-05 00:00:00',
     'test@qq.com','412721199701059004'),
	(1005,'444444','思雨-2',1,2,'13500000005','上海','1997-01-06 00:00:00',
     'test@qq.com','412721199701069005'),
	(1006,'123456','思雨-3',1,3,'13500000006','深圳','1997-01-07 00:00:00',
     'test@qq.com','412721199701079006'),
	(1007,'111111','浩洋-1',2,1,'13500000007','杭州','1997-01-08 00:00:00',
     'test@qq.com','412721199701089007'),
	(1008,'123456','浩洋-2',2,2,'13500000008','四川','1997-01-09 00:00:00',
     'test@qq.com','412721199701099008'),
	(1009,'555555','浩洋-3',2,3,'13500000009','福建','1997-01-10 00:00:00',
     'test@qq.com','412721199701109009'),
	(1010,'111111','淑敏-1',1,1,'13500000010','新疆','1997-01-11 00:00:00',
     'test@qq.com','412721199701119010'),
	(1011,'123456','淑敏-2',1,2,'13500000011','河南','1997-01-12 00:00:00',
     'test@qq.com','412721199701129011'),
	(1012,'222222','淑敏-3',1,3,'13500000012','北京','1997-01-13 00:00:00',
     'test@qq.com','412721199701139012'),
	(1013,'777777','龙阳-1',2,1,'13500000013','河北','1997-01-14 00:00:00',
     'test@qq.com','412721199701149013'),
	(1014,'123456','龙阳-2',2,2,'13500000014','四川','1997-01-15 00:00:00',
     'test@qq.com','412721199701159014'),
	(1015,'111111','龙阳-3',2,3,'13500000015','大连','1997-01-16 00:00:00',
     'test@qq.com','412721199701169015'),
	(1016,'123456','阿银-1',1,1,'13500000016','深圳','1997-01-17 00:00:00',
     'test@qq.com','412721199701179016'),
	(1017,'333333','阿银-2',1,2,'13500000017','上海','1997-01-18 00:00:00',
     'test@qq.com','412721199701179017');

-- 表subject
DROP TABLE IF EXISTS `subject`;-- 删除旧表
-- 新建表
CREATE TABLE `subject` (
  `SubjectNo` INT(10) 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 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO `subject` (`SubjectNo`,`SubjectName`,`ClassHour`,`GradeId`) 
VALUES(1,'高等数学-1',110,1),(2,'高等数学-2',110,2),(3,'高等数学-3',110,3),(4,'高等数学-4',110,4),
	(5,'C语言-1',110,1),(6,'C语言-2',110,2),(7,'C语言-3',110,3),(8,'C语言-4',110,4),
	(9,'JAVA-1',110,1),(10,'JAVA-2',110,2),(11,'JAVA-3',110,3),(12,'JAVA-4',110,4),
	(13,'数据库结构-1',110,1),(14,'数据库结构-2',110,2),(15,'数据库结构-3',110,3),(16,'数据库结构-4',110,4),
	(17,'C#基础',130,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}]; -- 指定查询的记录从那条至哪条

MySQL

4.2、指定查询字段

查询

-- 查询
SELECT 字段 FROM 表
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student;
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名,as可以省略
SELECT `StudentNo` AS 学号,`StudentName`AS 学生姓名 FROM student AS S;
-- 函数 Concat(a,b) 拼接a与b
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student;

语法: SELECT 字段,... FROM 表

-- 有时候,列名字不是那么见名知意。我们起别名 AS
字段名 AS 别名;
表名 AS 别名;

去重 distinct

作用:去除select语句查询出来的结果中重复的数据,重复的数据只显示一条

-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM `result`;-- 查询全部的考试成绩
SELECT `StudentNo` FROM `result`;-- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM `result`;-- 发现重复数据,去重

数据库的列(表达式)

SELECT VERSION(); -- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果; -- 用来计算(表达式)
SELECT @@auto_increment_increment; -- 查询自增的步长(变量)
-- 学员考试成绩+1 分 查看
SELECT `StudentNo`,`ExamResult`+1 AS '提分后' FROM `result`;
数据库中的表达式:
  • 表达式:文本值,列,Null , 函数,计算表达式,系统变量…

  • select 表达式 from 表

4.3、where 条件子句

作用:

检索数据中符合条件的值:操作符

搜索的条件由一个或者多个表达式组成!结果 布尔值

逻辑运算符

  • 尽量使用英文字母
运算符 语法 结果
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
Not != not a !a 逻辑非
-- 查询考试成绩在95分到100分之间
SELECT `StudentNo`,`ExamResult` FROM `result` 
WHERE `ExamResult`>=95 AND `ExamResult`<=100;
-- and	&&
SELECT `StudentNo`,`ExamResult` FROM `result` 
WHERE `ExamResult`>=85 && `ExamResult`<=100;
-- 模糊查询(区间)
SELECT `StudentNo`,`ExamResult` FROM `result` 
WHERE `ExamResult` BETWEEN 95 AND 100;

-- 除了1000号学生之外的同学成绩
SELECT `StudentNo`,`ExamResult` FROM `result` 
WHERE NOT `StudentNo`=1000;
-- !=	not
SELECT `StudentNo`,`ExamResult` FROM `result` 
WHERE `StudentNo`!=1000;

模糊查询:比较运算符

运算符 语法 描述
IS NULL a is null 如果操作符为null,结果为真
IS NOT NULL a is not null 如果操作符为not null,结果为真
BETWEEN a between b and c 若a在b 和c之间,则结果为真
LIKE a like b SQL匹配,如果a 匹配到b,则结果为真
IN a in (a1,a2,a3…) 假设a = a1,a2,a3其中的某一个值,则结果为真
-- 查询姓糖的同学
-- like结合 %(代表0到任意字符)  _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `StudentName` 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('北京','河南');

-- ==========NULL NOT NULL==========
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `Address`='' OR `Address` IS NULL;

-- 查询有出生日期的同学  不为空
SELECT `StudentNo`,`StudentName` FROM `student` 
WHERE `BornDate` IS NOT NULL;

4.4、联表查询

JOIN 对比

MySQL

MySQL

-- ==========联表查询 join ==========
-- 查询参加考试的同学 (学号,姓名,课程编号,分数)
SELECT * FROM `student`;
SELECT * FROM `result`;

/* 思路
1. 分析需求,分析查询的字段来自哪些表
2. 确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件: 学生表中 studentNo = 成绩表中 studentNo
*/
-- JION(连接的表) ON (判断的条件)连接查询
-- where	等值查询
SELECT s.StudentNo,StudentName,SubjectNo,ExamResult 
FROM student AS s 
INNER JOIN result AS r 
WHERE s.studentNo=r.studentNo;

-- Right Join 
SELECT s.studentNo,studentName,SubjectNo,ExamResult 
FROM student s 
RIGHT JOIN result r 
ON s.studentNo = r.studentNo;

-- LEFT Join
SELECT s.studentNo,studentName,SubjectNo,ExamResult 
FROM student s 
LEFT JOIN result r 
ON s.studentNo = r.studentNo;
操作 描述
Inner join 如果表中至少有一个匹配,就返回行
left join 会从左表中返回所有的值,即使右表中没有匹配
right jion 会从右表中返回所有的值,即使左表中没有匹配
-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,ExamResult 
FROM student s 
LEFT JOIN result r 
ON s.studentNo = r.studentNo 
WHERE ExamResult IS NULL;
-- 思考题(查询了参加考试的同学信息:学号,学生姓名,科目名,分数)
/* 思路
1. 分析需求,分析查询的字段来自哪些表,student、result、subject(连接查询)
2. 确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件: 学生表中 studentNo = 成绩表中 studentNo
*/
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`ExamResult`
FROM `student` s 
RIGHT JOIN `result` r 
ON s.`StudentNo`=r.`StudentNo` 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo`;

-- 我要查询哪些数据 SELECT ...
-- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一z多张表查询,先查询两章表,然后再慢慢增加
-- FROM a LEFT JOIN b   左为准
-- FROM a RIGHT JOIN b	右为准

自连接(了解)

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 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,'办公信息');

自己的表跟自己的表连接,核心:一张表拆为两张一样的表

父类

categoryid categoryName
2 信息技术
3 软件开发
5 美术设计

子类

pid categoryid categoryName
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 ps技术

操作:查询父类对应子类关系

父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术
-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' 
FROM `category` AS a,`category` AS b 
WHERE a.`categoryid`=b.`pid`;

-- 查询学员所属的年级(学号,学生的姓名,年级名称)
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM `student` s,`grade` g
WHERE s.`GradeId`=g.`GradeId`;
-- 查询学员所属的年级(学号,学生的姓名,年级名称)
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM `student` s
INNER JOIN `grade` g
ON s.`GradeId`=g.`GradeId`;
-- 查询科目所属的年级(科目名称,年级名称)
SELECT `SubjectName`,`GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeId`=g.`GradeId`;
-- 查询了参加 数据库结构-1 考试的同学信息:学号,学生姓名,科目名,分数
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`ExamResult`
FROM `student` s 
INNER JOIN `result` r 
ON s.`StudentNo`=r.`StudentNo` 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo`;
WHERE `SubjectName`=数据库结构-1;

4.5、分页和排序

排序

-- ==========排序order by==========
-- 排序:升序ASC  降序DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询的结果根据 成绩降序 排序
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`ExamResult`
FROM `student` s 
INNER JOIN `result` r 
ON s.`StudentNo`=r.`StudentNo` 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='数据库结构-1'
ORDER BY `ExamResult` DESC;

分页

-- ==========分页limit==========
-- 为什么要分页
-- 缓解数据库压力,给人的体验更好,瀑布流

-- 分页,每页显示五条数据
-- 语法:limit 当前页, 页面的大小
-- 网页应用:当前,总的页数,页面的大小
-- limit 0,5
-- limit 5,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`ExamResult`
FROM `student` s 
INNER JOIN `result` r 
ON s.`StudentNo`=r.`StudentNo` 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='数据库结构-1'
ORDER BY `ExamResult` DESC
LIMIT 0,5;
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit (n-1)*pageSize,pageSize

-- 【n:当前页】
-- 【(n-1)*pageSize:起始值】
-- 【pageSize:页面大小】
-- 【数据总数/页面大小 = 总页数】

语法 limit(查询起始下标,pagesize)

思考题

-- 查询JAVA-1课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`ExamResult`
FROM `student` s 
INNER JOIN `result` r 
ON s.`StudentNo`=r.`StudentNo` 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='JAVA-1' AND `ExamResult`>=80
ORDER BY `ExamResult` DESC
LIMIT 0,10;

4.6、子查询

where (这个值是计算出来的)

本质:在where语句中嵌套一个子查询语句

-- ========== where ==========

-- 1、查询 数据库结构-1的所有考试结果(学号,科目编号,成绩) 降序排列
-- 方式一:使用连接查询
SELECT `StudentNo`,`SubjectName`,`ExamResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '数据库结构-1'
ORDER BY `ExamResult` DESC;

-- 方式二:使用子查询(由里及外)
SELECT `StudentNo`,`SubjectNo`,`ExamResult`
FROM `result`
WHERE `SubjectNo`=(
    SELECT `SubjectNo` FROM  `subject` 
    WHERE `SubjectName` = '数据库结构-1'
)
ORDER BY `ExamResult` DESC;

-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `ExamResult`>=80;

-- 在这个基础上 增加一个科目 ,高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `ExamResult`>=80
AND `SubjectNo`=(
	SELECT `SubjectNo` FROM `subject` 
	WHERE `SubjectName` = '高等数学-2'
);

-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`='高等数学-2' AND `ExamResult`>=80;

-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN(
    SELECT `StudentNo` FROM `result` WHERE `ExamResult` >80 AND `SubjectNo`=(
	SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='高等数学-2'
    )
);

-- 练习:查询 C语言-1 前5名同学的成绩的信息(学号,姓名,分数)
-- 使用子查询~
SELECT s.`StudentNo`,`StudentName`,`ExamResult`
FROM `student` s
INNER JOIN `result` r
WHERE s.`StudentNo`=r.`StudentNo` AND `SubjectNo`=(
    SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='C语言-1'
)
ORDER BY `ExamResult` DESC
LIMIT 0,5

4.7、分组

-- 查询不同课程的平均分,最高分,最低分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT `SubjectName`,AVG(`ExamResult`) 平均分,
	MAX(`ExamResult`) 最高分,MIN(`ExamResult`) 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
GROUP BY `SubjectName` -- 通过什么字段来分组
HAVING 平均分>80

4.8、select小结

MySQL

5、MySQL函数

  • 官网:MySQL参考手册

5.1、常用函数

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回0~1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0→0 负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('我的家乡521') -- 返回字符串长度
SELECT CONCAT('我','521') -- 拼接字符串
SELECT INSERT('java',1,2,'12345') -- 从某(第1)个位置替换某(2)个长度
SELECT UPPER('abc') -- 转大写
SELECT LOWER('ABC') -- 转小写
SELECT INSTR('tangguo','g') -- 返回子串第一次出现的索引,索引从1开始
SELECT REPLACE('坚持就能成功,坚持坚持坚持!','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功,坚持',4,6) -- 返回指定的字符串(源字符串,截取的位置,截取的长度)
SELECT REVERSE('妈妈,爱我') -- 反转

-- 查询姓 糖 的同学 ,改成甜
SELECT REPLACE(`StudentName`,'糖','甜') 
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 SYSTEM_USER() -- MySQL用户
SELECT USER() -- MySQL用户
SELECT VERSION() -- MySQL版本

5.2、聚合函数(常用)

函数名称 描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
...
-- ========== 聚合函数 ==========
-- 都能够统计 表中的数据(想查询一个表中有多少个记录,就使用count())
SELECT COUNT(`StudentName`) FROM `student`; -- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM `student`; -- count(*),不会忽略 null值,本质 计算行数
SELECT COUNT(1) FROM `student`; -- count(1),不会忽略 null值,本质 计算行数

SELECT SUM(`ExamResult`) AS 总和 FROM `result`
SELECT AVG(`ExamResult`) AS 平均分 FROM `result`
SELECT MAX(`ExamResult`) AS 最高分 FROM `result`
SELECT MIN(`ExamResult`) AS 最低分 FROM `result`

5.3、数据库级别MD5加密(拓展)

什么是MD5?

主要增强算法复杂度不可逆性。

MD5不可逆,具体的MD5值是一样的

MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值

-- ========== 测试MD5加密 ==========
CREATE TABLE `testmd5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三','111'),(2,'李四','222'),(3,'王五','333')

-- 加密
UPDATE `testmd5` SET `pwd`=MD5(pwd) WHERE id =1;
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) -- 加密全部密码

-- 插入时加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('444'))

-- 如何校验,将用户传递过来的密码,进行MD5加密,然后对比加密后的值
SELECT * FROM `testmd5` WHERE `name`='小明' AND pwd=MD5('444')

6、事务

6.1、什么是事务(ACID)

要么都成功,要么都失败

1、SQL执行,A给B转账 A 1000→200 B 200

2、SQL执行,B收到A的钱 A 800 — B 400

将一组SQL放在一个批次中执行

事务原则 :

  • ACID原则:原子性,一致性,持久性,隔离性(脏读,不可重复读,幻读…)

  • 参考博客链接

事务管理(ACID)
原子性(Atomicity)
  • 要么都成功,要么都失败
一致性(Consistency)
  • 事务前后的数据完整性要保持一致(如:A、B两人,事务前后的钱总和不变)
持久性(Durability)--- 事务提交
  • 事务一旦提交就不可逆转,被持久化到数据库中
隔离性(Isolation)
  • 事务产生多并发时,互不干扰
事务的隔离级别

隔离所产生的一些问题

脏读:
  • 指一个事务读取了另外一个事务未提交的数据。
不可重复读:
  • 在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读)
  • 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。(一般是行影响,多了一行)

6.2、执行事务

-- mysql 是默认开启事务自动提交的
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启(默认的)

-- 手动处理事务
SET autocommit =0 -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内

INSERT XX
INSERT XX

-- 提交:持久化(成功!)
COMMIT
-- 回滚:回到原来的样子(失败!)
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 撤销保存点

MySQL

6.3、模拟场景

-- 转账
DROP DATABASE `shop`;
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.00);

-- 模拟转账:事务
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; -- 恢复默认值

7、索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

索引的本质:索引是数据结构。

7.1、索引的分类

在一个表中,主键索引只能有一个,唯一索引可以有多个

索引分类

主键索引(PRIMARY KEY)
  • 唯一的标识,主键不可重复,只能有一个列作为主键
唯一索引(UNIQUE KEY)
  • 避免重复的列出现,唯一索引可以重复,多个列都可以标识唯一索引
常规索引(KEY / INDEX)
  • 默认的,index,key关键字来设置
全文索引(FULLTEXT)
  • 在特点的数据库引擎下才有,MyISAM

  • 快速定位数据

基础语法(索引)

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引

-- 显示所有的索引信息
SHOW INDEX FROM 表

-- 增加一个索引
ALTER TABLE 表 ADD FULLTEXT INDEX 索引名(字段名)

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`StudentName`) AGAINST('糖果'); -- 全文索引

7.2、测试索引sql

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 DEFAULT '' COMMENT '用户昵称',
    `age` TINYINT(4) DEFAULT NULL COMMENT '密码',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='app用户表'

-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写,标志
CREATE FUNCTION mock_data() -- 创建函数mock_data()
RETURNS INT -- 返回值int
BEGIN -- 函数体开始
    DECLARE num INT DEFAULT 1000000; -- 定义变量num
    DECLARE i INT DEFAULT 0; -- 定义变量i
    
    WHILE i<num DO -- 判断条件
	-- 循环体
	-- 插入语句
	INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
	VALUES(
	    CONCAT('用户',i),'2715274537@qq.com',
	    CONCAT('18',FLOOR(RAND()*900000000+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'; -- 接近半秒
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 查询992269条记录

-- 索引名d命名方式:id_表名_字段名
-- create [FULLTEXT] index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON `app_user`(`name`);
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.001s
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 查询一条记录

索引在数据量小的时候,用处不大,但是在大数据的时候,区别十分明显

7.3、索引原则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上!

索引的数据结构

Hash 类型的索引

Btree: 默认INNODB的数据结构

阅读:MySQL索引背后的数据结构及算法原理

8、权限管理和备份

8.1、用户管理

SQLyog 可视化管理

MySQL

SQL 命令操作

用户表:mysql.user

本质:对这张表进行增删改查

创建用户
-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER tangguo IDENTIFIED BY '123456';
修改密码
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('root')

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR tangguo = PASSWORD('root')
重命名
-- 重命名:RENAME USER 原名字 TO 新名字
RENAME USER tangguo TO tangguo2
用户授权
-- 用户授权:ALL PRIVILEGES 全部的权限,库.表
-- ALL PRIVILEGES 除了给别人授权,其他都能干
GRANT ALL PRIVILEGES ON *.* TO tangguo2
查询权限
-- 查询权限
SHOW GRANTS FOR tangguo2  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

-- ROOT用户权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
撤销权限
-- 撤销权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM tangguo2
删除用户
-- 删除用户
DROP USER tangguo2

8.2、MySQL备份

为什么要备份:

  • 保证重要的数据不丢失

  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件

  • 在SQLyog这种可视化工具中手动导出

    • 在想要导出的表或者库中,右键选择备份和导出

MySQL

  • 使用命令行导出 mysqldump 命令行使用
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student >D:Markdown学习sqla.sql

# mysqldump -h 主机 -u 用户名 -p密码 数据库 表1 表2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student result >D:Markdown学习sqlb.sql

# mysqldump -h 主机 -u 用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school >D:Markdown学习sqlc.sql

# 导入
# 登录的情况下,切换到指定的数据库
# source 备份文件
source d:/a.sql

mysql -u用户名 -p密码 库名< 备份文件

假设你要备份数据库,防止数据丢失。把数据库给朋友!sql文件给别人即可

9、规范数据库设计

9.1、为什么需要设计

当数据库比较复杂的时候,我们就需要设计了

糟糕的数据库设计:

  • 数据冗余,浪费空间

  • 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】

  • 程序的性能差

良好的数据库设计:

  • 节省内存空间

  • 保证数据库的完整性

  • 方便我们开发系统

软件开发中,关于数据库的设计

  • 分析需求:分析业务和需要处理的数据库的需求

  • 概要设计:设计关系图 E-R图

设计数据库的步骤(个人博客)

  1. 收集信息,分析需求

    • 用户表(用户登录注销,用户的个人信息,写博客,创建分类)

    • 分类表(文章分类,谁创建的)

    • 文章表(文章的信息)

    • 评论表

    • 友链表(友链信息)

    • 自定义表(系统信息,某个关键的字,或者某些主字段)

    • 说说表(发表心情…id ,content ,create_time)

  2. 标识实体(把需求落地到每个字段)

  3. 标识实体之间的关系

    • 写博客:user–>blog

    • 创建分类:user–>category

    • 关注:user–>user

    • 友链:links

    • 评论:user–>user–>blog

9.2、三大范式

为什么需要数据规范化?

  • 信息重复

  • 更新异常

  • 插入异常

    • 无法正常显示异常
  • 删除异常

    • 丢失有效的信息

三大范式(记住)

第一范式(1NF)

  • 原子性:保证每一列不可再分

第二范式(2NF)

  • 前提:满足第一范式

  • 每张表只描述一件事情

第三范式(3NF)

  • 前提:满足第一范式 和 第二范式z

  • 第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

(规范数据库的设计)

规范性和性能的问题

关联查询的表,不得超过三张表

  • 考虑商业化的需求和目标(成本和用户体验) 数据库的性能更加重要

  • 在规范性能的问题的时候,需要适当的考虑一下,规范性

  • 故意给某些表加一些冗余的字段。(从多表查询 变成 单表查询)

  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

10、JDBC(重点)

10.1、数据库驱动

驱动:声卡,显卡,数据库

MySQL

我们的程序会通过数据库驱动,和数据库打交道!

10.2、JDBC

SUN 公司为了简化开发人员(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,JDBC

这些规范的实现由具体的厂商去做

对于开发人员来说,我们只需要掌握JDBC接口的操作即可!

MySQL

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

10.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,'zhansan','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');

1、创建一个普通项目

2、导入数据库驱动

MySQL

3、编写测试代码

  • 连接数据库,参数useSSL的报错:Communications link failure
package com.tang.lesson01;

import java.sql.*;

//我的第一JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法
        //2.用户信息和url
        // useUnicode=true 支持中文编码
        // characterEncoding=utf 字符集编码
        // useSSL=true 使用安全的连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "root";
        //3.连接成功,获取数据库对象。Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行SQL的对象,Statement执行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("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("==========================");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结:

  1. 加载驱动

  2. 连接数据库 DriverManager

  3. 获取执行SQL的对象 Statement

  4. 获得返回的结果集

  5. 释放连接

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection= DriverManager.getConnection(url,name,password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();//事务回滚
connection.commit();//事务提交
connection.setAutoCommit();//设置自动提交

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
// mysql 默认端口 3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3

// Oracle 默认端口 1521
//jdbc:oralce:thin:@localhost:1521:sid

statement 执行SQL的对象 PrepareStatement 执行SQL的对象

String sql="SELECT * FROM users";//编写Sql

statement.executeQuery();//执行查询,返回一个结果集
statement.execute();//执行 增、删、改、查
statement.executeUpdate();//执行 增、删、改,返回一个受影响的行数
statement.executeBatch();//执行多个sql语句

ResultSet 查询的结果集,封装了所有的查询结果

获得指定的数据类型

ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果

resultSet.getObject();//在不知道列类型下使用
//如果知道列的类型,就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
...

遍历,指针

resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

释放资源

//6. 释放连接
resultSet.close();
statement.close();
connection.close();//耗资源,用完关掉

10.4、statement对象

Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sq|语句, executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发生查询语句,executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-insert

使用executeUpdate(String sql)方法完成数据添加操作,示例操作:

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功!!!");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作,示例操作:

Statement statement = connection.createStatement();
String sql = "delete from user where id =1";
int num = statement.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功!!!");
}

CURD操作-update

使用executeUpdate(String sql)方法完成数据修改操作,示例操作:

Statement statement = connection.createStatement();
String sql = "update user set name='' where name=''";
int num = statement.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功!!!");
}

CURD操作-select

使用executeUpdate(String sql)方法完成数据查询操作,示例操作:

Statement statement = connection.createStatement();
String sql = "select * from  user where id =1";
ResultSet rs= statement.executeQuery(sql);
if(rs.next()){
    //根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}

代码实现

1、提取工具类

package com.tang.lesson02.utils;

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("dp.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 conn, Statement st, ResultSet rs){
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

dp.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root

2、编写增、删、改的方法,exectueUpdate

-------------------- 增 --------------------
package com.tang.lesson02;

import com.tang.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 st = null;
        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //获取sql的执行对象
            st = connection.createStatement();
            String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) " +
                    "VALUES(4,'tangguo','123456','2715274537@qq.com','1997-01-01')";
            int i = st.executeUpdate(sql);
            if (i>0) System.out.println("插入成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}
-------------------- 删 --------------------
package com.tang.lesson02;

import com.tang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement st = null;
        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //获取sql的执行对象
            st = connection.createStatement();
            String sql = "DELETE FROM `users` WHERE `id` = 4";
            int i = st.executeUpdate(sql);
            if (i>0) System.out.println("删除成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}
-------------------- 改 --------------------
package com.tang.lesson02;

import com.tang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement st = null;
        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //获取sql的执行对象
            st = connection.createStatement();
            String sql = "UPDATE `users` SET `password`='aaa' WHERE `id`=4";
            int i = st.executeUpdate(sql);
            if (i>0) System.out.println("修改成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

3、查询 executeQuery

package com.tang.lesson02;

import com.tang.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 conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "SELECT * FROM `users` WHERE `id` = 1";
            rs = st.executeQuery(sql);//查询完毕会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入问题

sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or

package com.tang.lesson02;

import com.tang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQL注入 {
    public static void main(String[] args) {
        //SQL注入
        login("' or '1=1","' or '1=1");
    }
    //登录业务
    public static void login(String name,String password){
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;
        
        try {
            conn = JdbcUtils.getConnection();//获取连接

            st = conn.createStatement();//获取SQL执行对象
            String sql = "select * from users where `name`='"+ name +"' AND `password`='"+ password +"'" ;
            rs=st.executeQuery(sql);//查询完毕返回结果集

            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("====================");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.5、PreparedStatement对象

PreparedStatement 可以防止SQL注入 ,效率更高。

更新都一样

  1. 新增

    package com.tang.lesson03;
    
    import com.tang.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class TestInsert {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement st = null;
            try {
                //获取数据库连接
                conn = JdbcUtils.getConnection();
                //区别
                //使用?占位符代替参数
                String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) " +
                        "VALUES(?,?,?,?,?)";
                st = conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
                //手动给参数赋值
                st.setInt(1,4);//id
                st.setString(2,"tang");//name
                st.setString(3,"123");//password
                st.setString(4,"2715274537@qq.com");//email
                //注意点:sql.Date    数据库    java。sql.Date()
                //       util.Date   java    new Date().getTime() 获得时间戳
                // st.setDate(5,new Date(new java.util.Date().getTime()));//birthday
                st.setDate(5,new Date(1997-01-01));//birthday
                //执行
                int i = st.executeUpdate();
                if (i>0) System.out.println("插入成功!");
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(conn,st,null);
            }
        }
    }
    
  2. 删除

    package com.tang.lesson03;
    
    import com.tang.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class TestDelete {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement st = null;
            try {
                //获取数据库连接
                conn = JdbcUtils.getConnection();
                //区别
                //使用?占位符代替参数
                String sql = "DELETE FROM `users` WHERE `id` = ?";
                st = conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
                //手动给参数赋值
                st.setInt(1,4);//id
                //执行
                int i = st.executeUpdate();
                if (i>0) System.out.println("删除成功!");
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(conn,st,null);
            }
        }
    }
    
  3. 修改

    package com.tang.lesson03;
    
    import com.tang.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class TestUpdate {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement st = null;
            try {
                //获取数据库连接
                conn = JdbcUtils.getConnection();
                //区别
                //使用?占位符代替参数
                String sql = "UPDATE `users` SET `password`=? WHERE `id`=?";
                st = conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
                //手动给参数赋值
                st.setString(1,'123456');//password
                st.setInt(2,4);//id
                //执行
                int i = st.executeUpdate();
                if (i>0) System.out.println("修改成功!");
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(conn,st,null);
            }
        }
    }
    
  4. 查询

    package com.tang.lesson03;
    
    import com.tang.lesson02.utils.JdbcUtils;
    
    import java.sql.*;
    
    public class TestSelect {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();//获取数据库对象
                String sql = "SELECT * FROM `users` WHERE `id` = ?";//编写SQL
                st = conn.prepareStatement(sql);//预编译SQL
                st.setInt(1,1);//传递参数
                //执行
                rs = st.executeQuery();//查询完毕会返回一个结果集
                while (rs.next()){
                    System.out.println(rs.getString("name"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.release(conn,st,rs);
            }
        }
    }
    
  5. 防止SQL注入

    package com.tang.lesson03;
    
    import com.tang.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    public class SQL注入 {
        public static void main(String[] args) {
            login("zhangsan","123456");
            // login("'' or 1=1","'' or 1=1");
        }
        //登录业务
        public static void login(String name,String password){
            Connection conn =null;
            PreparedStatement st = null;
            ResultSet rs =null;
    
            try {
                conn = JdbcUtils.getConnection();//获取连接
                //PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符
                //假设其中存在转义字符(例如:'),会被直接转义
                String sql = "select * from users where `name`=? AND `password`=?";//Mybatis
                st = conn.prepareStatement(sql);//预编译SQL
                st.setString(1,name);
                st.setString(2,password);
                rs=st.executeQuery();//查询完毕返回结果集
    
                while (rs.next()){
                    System.out.println(rs.getString("name"));
                    System.out.println(rs.getString("password"));
                    System.out.println("====================");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
        }
    }
    

防止SQL注入本质,传递字符 带有“ ”,转义字符会被转义

10.6、使用IDEA连接数据库

  • IDEA连接MySQL数据库,图形化操作,参考链接:IDEA连接MySQL数据库

连接MySQL数据库

MySQL

连接成功后,可以选择数据库

MySQL

双击数据库

MySQL

更新数据

MySQL

编写sql代码的地方

MySQL

10.7、JDBC事务

要么都成功,要么都失败

ACID原则

原子性:要么全部完成,要么都不完成

一致性:总数不变

隔离性:多个进程互不干扰

持久性:一旦提交不可逆,持久化到数据库了

隔离性的问题:

脏读: 一个事务读取了另一个没有提交的事务

不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变

虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致

代码实现

  1. 开启事务conn.setAutoCommit(false);

  2. 一组业务执行完毕,提交事务

  3. 可以在catch语句中显示的定义回滚,但是默认失败会回滚

package com.tang.lesson04;

import com.tang.lesson02.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 conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库自动提交功能,自动会开启事务
            conn.setAutoCommit(false);

            String sql1 = "update jdbcstudy.account set money = money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            
            int x = 1/0;//报错

            String sql2 = "update jdbcstudy.account set money = money+100 where name='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();
            //业务完毕,提交事务
            conn.commit();
            System.out.println("成功!");
        } catch (SQLException e) {
            //如果失败,则默认回滚事务
            /* try {
                conn.rollback();//如果失败,则回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            } */
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.8、数据库连接池

数据库连接 --- 执行完毕 --- 释放

连接 --- 释放,十分浪费资源

池化技术: 准备一些预先的资源,过来就连接预先准备好的

常用连接数 10个

最少连接数:10个

最大连接数 : 15 业务最高承载上限

等待超时:100ms

编写连接池,实现一个接口 DateSource

开源数据源实现(拿来即用)

  • DBCP

  • C3P0

  • Druid: 阿里巴巴

使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了!

DBCP

需要用到的jar包

  • commons-dbcp2-2.9.0

  • commons-pool2-2.11.1

  • commons-logging-1.2

dbcpconfig.properties

# 连接设置,这里面的名字,是DBCP数据源中定义好的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root
# 初始化连接
initialSize=10

# 最大连接数量
maxActive=50

# 最大空闲连接
maxIdle=20

# 最小空闲连接
minIdle=5

# 超时等待时间以毫秒为单位/s
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:“user” 与 “password” 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8

#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP工具类

package com.tang.lesson05.utils;

import org.apache.commons.dbcp2.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 conn, Statement st, ResultSet rs){
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

TestDBCP测试代码

package com.tang.lesson05;

import com.tang.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //区别
            //使用?占位符代替参数
            String sql = "INSERT INTO jdbcstudy.users(`id`,`name`,`password`,`email`,`birthday`) " +
                    "VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
            //手动给参数赋值
            st.setInt(1,5);//id
            st.setString(2,"tan");//name
            st.setString(3,"1234");//password
            st.setString(4,"2715274537@qq.com");//email
            //注意点:sql.Date    数据库    java。sql.Date()
            //       util.Date   java    new Date().getTime() 获得时间戳
            // st.setDate(5,new Date(new java.util.Date().getTime()));//birthday
            st.setDate(5,new Date(1997-01-01));//birthday
            //执行
            int i = st.executeUpdate();
            if (i>0) System.out.println("插入成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

C3P0

需要用到的jar包

  • c3p0-0.9.5.5

  • mchange-commons-java-0.2.20

c3p0-config.xml配置文件(名字不要变)

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <!--
            C3P0的缺省(默认)配置,
            如果在代码中是:
                ComboPooledDataSource cp = new ComboPooledDataSource();
                这样写就表示使用default-config的配置
        -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/student?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!-- 当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化时获取连接数,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
        <property name="initialPoolSize">10</property>
        <!-- 最大空闲时间,多少秒内未使用则连接被丢弃。若为0则永不丢弃。default: 0  -->
        <property name="maxIdleTime">30</property>
        <!--连接池中保留的最小连接数。Default: 5 -->
        <property name="minPoolSize">5</property>
        <!--连接池中保留的最大连接数。Default: 20 -->
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
        C3P0的命名配置,
        如果在代码中是:
            ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");
            这样写就表示使用name是MySQL的配置
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="root">root</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>


    <!-- 所以下面想用什么配置就写什么配置   -->
</c3p0-config>

JdbcUtils_C3P0工具类

package com.tang.lesson05.utils;

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;

public class JdbcUtils_C3P0 {
    private static DataSource dataSource = null;
    static {
        try {
            //创建数据源
            dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

TestC3P0测试代码

package com.tang.lesson05;

import com.tang.lesson05.utils.JdbcUtils_C3P0;
import com.tang.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils_C3P0.getConnection();//原来是用自己的实现,现在用别人的实现
            //区别
            //使用?占位符代替参数
            String sql = "INSERT INTO jdbcstudy.users(`id`,`name`,`password`,`email`,`birthday`) " +
                    "VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
            //手动给参数赋值
            st.setInt(1,6);//id
            st.setString(2,"tin");//name
            st.setString(3,"12345");//password
            st.setString(4,"2715274537@qq.com");//email
            //注意点:sql.Date    数据库    java。sql.Date()
            //       util.Date   java    new Date().getTime() 获得时间戳
            // st.setDate(5,new Date(new java.util.Date().getTime()));//birthday
            st.setDate(5,new Date(1997-01-01));//birthday
            //执行
            int i = st.executeUpdate();
            if (i>0) System.out.println("插入成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(conn,st,null);
        }
    }
}

结论

无论使用什么数据源,本质是不变的,DateSource接口不会变,方法就不会变!

后期学习Druid

apache学习官网

MySQL

自夸比狂神讲的好,没看不知道

程序员灯塔
转载请注明原文链接:MySQL
喜欢 (0)