MySQL教程

标签：教程 -- 数据库 sql student MySQL null SELECT

1、初识MySQL#

JavaEE：企业级Java开发 Web

前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，给前端传递数据））

数据库（存数据，Txt，Excle，word）

学好数据库：

操作系统，数据结构与算法。

离散数学，数字电路，体系结构，编译原理。

1.1 什么是数据库#

数据库（DB,DataBase）

概念：数据仓库，软件，安装在操作系统（window，Linux，mac）之上

作用：存储数据，管理数据

1.2 数据库分类#

关系型数据库：（SQL）

MySQL，Oracle，SqlServer，DB2，SQLlite
通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL） Not Only

Redis，MongDB
非关系型数据库，以对象存储，通过对象自身的属性来决定

DBMS（数据库管理系统）

数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
MySQL，本质：数据库管理系统

1.3 MySQL安装#

1.下载后得到zip压缩包.
2.解压到自己想要安装到的目录，本人解压到的是D:\Environment\mysq-5.7.19
3.添环境变量

1.我的电脑>属性>高级>环境变量
2.选择PATH,在其后面添加：你的mysql安装文件下面的bin文件夹
3.在D:\Environment\mysql--5.7.19下新建my.ini文件
4.编辑my.ini文件，注意替换路径位置
[mysqld]
basedir=E:\Environment\mysql-8.0.27\
datadir=E:\Environment\mysql-8.0.27\data\ 
port=3306
skip-grant-tables
5.启动管理员模式下的CMD,并将路径切换至mysql下的bin目录，然后输入mysqld -install(安装mysql)
6、初始化数据库文件
mysqld --initialize-insecure --user=mysql
7.然后启动mysql,进入mysql管理界面(密码为空)
net start mysql 启动MySQL
mysql -u root -p 连接MySQL
8.进入界面后更改root密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
(最后输入flush privileges;刷新权限)
9.修改my.ini文件,注释最后一句 #skip-grant-tables
10.重启mysql即可正常使用
net stop mysql
net start mysql
11.连接上测试出现以下结果就安装好了

1.4 在安装MySQL过程中的一些错误

sc delete mysql，清空服务

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

当修改密码出现报错时可使用下面两句任意一句话来进行修改

SET PASSWORD = '123456'
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

1.5 SQLyog#

Ultimate版本
注册名：kuangshen
注册码：8d8120df-a5c3-4989-8f47-5afc79c56e7c

1、新建一个数据库

连接数据库时的一些错误：

错误：1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client.

再次安装MySQL后，使用 Navicat 或者 MySQL yog都会出现下面这个异常。

连接数据库之后把下面的两条指令逐条复制粘贴到控制台就可以了 ( *'123456'为你的数据库登录密码。* )。

alter user 'root'@'localhost' identified with mysql_native_password by '123456';

flush privileges;

1.6 连接数据库#

命令行连接

ctrl + c 强行终止

mysql -uroot -p123456	-- 连接数据库

update mysq1.user set authentication_string=password('123456') where user='root' and Host ='loca1host';	-- 修改用户密码

flush privileges; -- 刷新权限
--------------------------------------------
--所有的语句都使用;结尾
show databases;-- 查看所有的数据库

mysql> use schoo1 -- 切换数据库use 数据库名
Database changed

show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息

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

exit;	-- 退出连接
-- 单行注释(sQL的本来的注释)
/*(sq1的多行注释)
he1lo
world
*/

数据库语言：CRUD 增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库#

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

mysql关键字不区分大小写

2.1 操作数据库#

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2、删除数据库

DROP DATABASE [IF EXISTS] westos

3、使用数据库

-- tab	键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
USE`school`

4、查看数据库

SHOW DATABASE -- 查看所有的数据库，记得+"s"

学习思路：

对照sqlyog可视化历史记录查看sql
固定的语法或关键字必须要强行记住

2.2 数据库的数据（列）类型#

数值

tinyint 1个字节
smallint 2个字节
mediumint 3个字节
int 4个字节
bigint 8个字节
float 4个字节
double 8个字节（存在精度问题）
decimal 字符串形式的浮点数（用于金融计算等，不存在精度问题）

字符串

char 固定大小的字符串 0~255
varchar 可变字符 0~65535 常用的变量 String
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

时间日期

date YYYY-MM-DD，日期格式
time HH : mm : ss 时间格式
datetime YYYY-MM-DD HH : mm : ss 最常用的时间格式
timestamp 时间戳，1970.1.1到现在的毫秒数
year 年份表示

null

没有值，未知
注意，不要使用null进行运算，结果为null

2.3 数据库的字段属性（重点）#

unsigned：

无符号的整数
声明该列不能声明为负数

zerofill：

0填充的
不足的位数用0来填充，int（3），5 , 005

自增：

通常理解为自增，自动在上一条记录的基础上 +1（默认）
通常用来设计唯一的主键--index，必须是整数类型
可以自定义设计主键自增的起始值和步长

非空：

假设设置为 not null ，如果不给它赋值，就会报错
null，如果不填写值，默认就是null

默认：

设置默认的值
sex，默认值为男，如果不指定该列的值，则会有默认的值

int（11）也就是int（M），M指示最大显示宽度，不是说限制你只能插入规定长度的数据，最大有效显示宽度是255，显示宽度与存储大小或类型包含的值的范围是无关的

如int（1），你可以插1，也可以插10,100

但是varchar（20）就不一样，输入的字符必须在20个字符内

2.4 创建数据库#

-- 目标:创建一个schoo1数据库
-- 创建学生表(列,字段)使用sQL创建
-- 学号int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime),家庭住址, 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'密码',
	`sex` 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 -- 查看创建数据库的语句
SHOw CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

2.5 数据表的类型#

-- 关于数据库引擎
/*
INNODB 	默认使用~
MYISAM	早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持（表锁定）	支持
外键索引	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

MYISAM 节约空间，速度较快
INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

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

INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

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

CHARSET=utf8

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

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

character-set-server = utf8

2.6 修改删除表#

修改

-- 修改表名:ALTER TABLE旧表名 RENAME AS新表名
ALTER TABLE teacher RENAME As teacher1
-- 增加表的字段: ALTER TABLE 表名 ADD字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段(重命名，修改约束!)
-- ALTER TABLE表名 MODIFY 字段名 列属[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字列 属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)	-- 字段重名名

-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

最终结论:

change用来字段重命名，不能修改字段类型和约束。

modify不用来字段重命名，只能修改字段类型和约束。

删除

-- 删除表（表存在）
DROP TABLE IF EXISTS teacher1

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

注意点：

字段名使用 `` 包裹
注释使用 -- 或者 /**/
sql关键字不敏感，建议小写
符号用英文

3、MySQL数据管理#

3.1、外键#

在创建表的时候，增加约束（复杂）

CREATE TABLE grade(
`gradeid` INT(5) AUTO_INCREMENT NOT NULL COMMENT'年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT'年级姓名',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的外键要去引用年级表的gradeid
-- 定义外键
-- 给这个外键添加约束（执行引用），reference 引用

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(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

创建表成功后，添加外键约束

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 into 表名 (字段1，字段2，...) values（值1），(值2,...)

-- 插入单行
INSERT INTO `student` (`name`,pwd,sex) VALUES ('赵','xyz','男')

-- 插入多行
INSERT INTO `student`(`name`,pwd,sex) VALUES('钱','asd','男'),('孙','qwe','男')

-- 省略字段
INSERT INTO `student` VALUES (4,'李','zxc','男','2000-01-01','北京','email')

注意事项：

字段和字段之间使用英文逗号隔开
字段是可以省略的，但是后面的值必须要一一对应，不能少
可以同时插入多条数据，values 后面的值使用英文逗号隔开即可

3.4、修改#

语法：update 表 set 列 = 值 where 条件

UPDATE `student` SET `name` = '李四'  WHERE pwd = 'ls'

-- 不指定条件的情况下
UPDATE `student` SET `name` = '张三'

-- 修改多个属性
UPDATE `student` SET `name` = '李四', birthday = '2002-02-04', address = '上海' WHERE `pwd` = 'ls'

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

操作符会返回布尔值

操作符	含义	范围	结果
=	等于
<>或！=	不等于
>
<
<=
>=
between...and...	在某个范围内	[2,5]
and		5>1 and 1>2	false
or		5>1 or 1>2	true

注意：

colnum_name 是数据库的列，尽量带上``
条件，筛选的条件，如果没有指定，则会修改所有的列
value，是一个具体的值，也可以是一个变量

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '孙' and sex = '女'

3.5、删除#

delete 命令

语法：delete from 表名 where 条件

-- 删除数据（会全删，一般不这么写）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id = 4

TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空student表
TRUNCATE `student`

delete 和 TRUNCATE 的区别

相同点：
都能删除数据，都不会删除表结构

不同点：

TRUNCATE 重新设置自增列，计数器会归零
TRUNCATE 不会影响事务

delete删除的问题，重启数据库，现象

INNODB 自增列会从1开始（存在内存中，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

4、DQL查询数据#

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}];   

                                       -- 指定查询的记录从哪条至哪条

4.1查询指定字段#

-- 查询全部学生
SELECT * FROM `student` 

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`

-- 使用 AS 可以给字段起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student`

-- CONCAT(a,b)
SELECT CONCAT('姓名：',studentname) AS 名字 FROM `student`

去重 distinct

作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条

SELECT DISTINCT `studentno` AS '学号' FROM `result`

数据库的列（表达式）

SELECT VERSION()
-- 学员考试成绩 + 1 分查看
SELECT `studentresult` +1 AS '提分后' FROM `result`

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量

select 表达式 from 表

4.2、where条件子句#

作用：检索数据中符合条件的值

逻辑运算符

运算符	语法
and &&	a and b
or
Not ！	not a ！a

尽量使用英文字母

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=80 AND `studentresult`<=95

-- 模糊查询
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`BETWEEN 80 AND 95

SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` = 1001

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果a为null，结果为真
IS NOT NULL	a is not null	a不为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...其中某一个值中，结果为真

-- 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 '%达%'

-- 查询1001,1002号学员 
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentno` IN (1001,1002)

4.4、联表查询#

JOIN 对比

-- join（连接的表） on （判断的条件） 连接查询
-- where 等值查询

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno

-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN `result` r
ON s.studentno = r.studentno

-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN `result` r
ON s.studentno = r.studentno

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- 思考题(查询了参加考试的同学信息:学号，学生姓名，科目名，分数)
/*思路
1．分析需求，分析查询的字段来自哪些表,student.result. subject（连接查询)
2．确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的)
判断的条件:学生表的中 studentNo.=成绩表studentNo */

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM category a, category b
WHERE a.`categoryid` = b.`pid`

4.5、分页和排序#

排序

-- 排序：升序 ASC ，降序 DESC
-- ORDER BY 通过哪个字段排序

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `subject` sub
ON s.`gradeid` = sub.`gradeid`
INNER JOIN result r
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-2'
ORDER BY `studentresult` DESC

分页

-- 语法：limit 起始值，页的大小
-- 第N页 （n-1）* pagesize，pagesize
-- pagesize ：页面大小
-- （n-1）* pagesize：起始值
-- n：当前页
-- 数据总数/页面大小 = 总页数

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `subject` sub
ON s.`gradeid` = sub.`gradeid`
INNER JOIN result r
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-2'
ORDER BY `studentresult` DESC
LIMIT 0,5

4.6、子查询#

-- 查询科目为高等数学-3且分数> 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` = '高等数学-3'
AND `studentresult` >80

-- 子查询
SELECT 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` = '高等数学-3'
)

4.7、分组和过滤#

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT `subjectname`, AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`
HAVING AVG(`studentresult`) >80

ANY_VALUE()函数说明

MySQL有any_value(field)函数，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。
这样sql语句不管是在ONLY_FULL_GROUP_BY模式关闭状态还是在开启模式都可以正常执行，不被mysql拒绝。
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。

4.8、select小结#

5、MySQL函数#

https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

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 CURRENT_DATE()--获取当前日期
SELECT CURDATE() --获取当前日期
SELECT NOW ()--获取当前的时间
SELECT LOCALTIME()--本地时间
SELECT SYSDATE() --系统时间
SELECTYEAR(NOW ())
SELECT MONTH(NOW())SELECT DAY(NOW ())
SELECT HOUR (NOW () )
SELECT MINUTE (NOW () )
SELECT SECOND (NOW ())

--系统
SELECT SYSTEMUSER()
SELECT USER()
SELECT VERSION()

5.2、聚合函数#

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...

count(1)：统计所有的记录（包括null）。
count(*)：统计所有的记录（包括null）。
count(字段)：统计该"字段"不为null的记录。
count(distinct 字段)：统计该"字段"去重且不为null的记录。

count(1)中的1并不是表示第一个字段，而是表示一个固定值。其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，
就是计算一共有多少个1。count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

-- 都能够统计表中的数据(想查询一个表中有多少个记录，就使用这个count( ))
SELECT COUNT(`BornDate`) FROM student; -- Count(字段)，会忽略所有的null值
SELECT COUNT(*） FROM student; -- count(*)，不会忽略 null值，本质计算行数
SELECT COUNT(1)FROM result; -- Count(1)，不会忽略null值,本质计算行数
-- 当要统计的数量比较大时，发现count(*)花费的时间比较多，相对来说count(1)花费的时间比较少。            
-- 如果你的数据表没有主键，那么count(1)比count(*)快 ；如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快  。

-- 2、如果你的表只有一个字段的话那count(*)就是最快的。  

-- 3、如果count(1)是聚索引,id,那肯定是count(1)快,但是差的很小的。因为count(*),自动会优化指定到那一个字段。
所以没必要去count(1)，用count(*),sql会帮你完成优化。此时count(1)和count(*)基本没有区别! 

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

5.3、数据库级别的MD5加密#

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

-- 明文密码
INSERT INTO `grade` VALUES(6,'123456')
-- 插入时加密
INSERT INTO `grade` VALUES(7,MD5('123456'))
-- 插入后加密
UPDATE `grade` SET `gradename` = MD5(gradename) WHERE `gradeid` = 6
UPDATE `grade` SET `gradename` = MD5(gradename) -- 全部加密
WHERE `gradeid` =  6
-- 校验：将用户传入过来的密码，进行md5加密，比对加密后的值
SELECT * FROM `grade` WHERE `gradeid` = 6 AND `gradename` = MD5('123456')

6、事务#

6.1、什么是事务#

事务：要么都成功，要么都失败

——————

1、SQL执行 A 给 B 转账

2、SQL执行 B 收到 A 的钱

——————

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

事务原则：ACID 原子性一致性隔离性持久性

原子性（Atomicity）：

要么都成功，要么都失败

一致性（Consistency）：

事务前后数据完整性要保持一致

持久性（Durability）:

事务一旦提交则不可逆，被持久化到数据库中

隔离性（Isolation）：

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其它事务的操作数据所干扰，事务之间要相互隔离

隔离所导致的一些问题

脏读：

指一个事务读取了另一个事务未提交的数据

不可重复读：

在另一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）

虚读（幻读）：

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

执行事务

-- MySQL默认开启事务自动提交
SET autocommit = 0  -- 关闭
SET autocommit = 1  -- 开启

-- 手动处理事务
SET autocommit = 0 

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在一个事务内
UPDATE XXX
UPDATE XXX
-- 提交，持久化（成功）
COMMIT
-- 回滚（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交

SAVEPOINT 保存点名 -- 设置一个事务的保存点

7、索引#

索引是帮助MySQL高效获取数据的数据结构

7.1、索引的分类#

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，可以重复
常规索引（KEY/INDEX）
- 默认的，index，key关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有

-- 显示全部索引
SHOW INDEX FROM `student`

-- 创建一个索引可以用
-- CREATE INDEX 索引名 on 表（字段）

-- 添加一个全文索引 
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('周')

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

7.2、索引原则#

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree：InnoDB的默认数据结构

8、权限管理和备份#

8.1、用户管理#

SQL yog可视化管理

SQL 命令操作

用户表：mysql.user

本质：读这张表进行增删改查

-- 创建用户
CREATE USER xy IDENTIFIED BY '111111'
DROP USER xy	-- 删除

-- 修改密码
SET PASSWORD = PASSWORD('123456')	-- 当前用户
SET PASSWORD FOR xy = PASSWORD('123123')

-- 重命名
RENAME USER xy TO xy1

-- 用户授权 ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES 除了能给别的用户授权，其它都能干
GRANT ALL PRIVILEGES ON *.* TO xy

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM xy

8.2、MySQL备份#

MySQL数据库备份的方式

直接拷贝物理文件
在SQLyog 这种可视化工具中手动导出
使用命令行导出 mysqldump 命令行使用
- 导出：mysqldump -h 主机 -u用户名 -p 密码数据库表名 >物理磁盘位置/文件名
- 导入：登录的情况下，切换到指定的数据库，source 备份文件

#mysq1dump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
#mysq1dump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D:/b.sql
#mysq1dump -h主机 u用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql

#登录的情况下，导入
mysql -uroot -p123456
use school
#source备份文件
source d:/a.sql
#不登录的情况下，导入
mysq1 -u用户名 -p密码 库名<备份文件

9、规范数据库设计#

9.1概述#

槽糕的数据库设计：

数据冗余，浪费空间
数据库的插入和删除麻烦，异常（屏蔽使用物理外键）
程序性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性
方便开发系统

数据库的设计：

分析需求，分析业务和需要处理的数据库的需求
概要设计：设计关系图E-R图

9.2、三大范式#

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

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

规范性和性能问题

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

考虑商业化的需求和目标，（成本，用户体验）数据库的性能更加重要
在规范性能的问题的时候，需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

举例说明：

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

举例说明：

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

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

举例说明：

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

这样以来，就满足了第三范式的要求。

ps:如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。

10、JDBC（重点）#

java.sql

javax.sql

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

10.1、第一个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','[email protected]','1980-12-04'),
(2,'lisi','123456','[email protected]','1981-12-04'),
(3,'wangwu','123456','[email protected]','1979-12-04')

1、创建项目

2、导入数据库驱动

3、编写测试代码

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 用户信息和url
        // useUnicode = true    支持中文字符集
        // character=utf8   设置字符集编码
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";

        // 连接数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);

        // 创建执行sql的对象 Statement
        Statement statement = connection.createStatement();

        // 执行sql，可能存在结果，查看返回的结果集
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql);

        while(resultSet.next()){
            System.out.println("id = " + resultSet.getObject("id"));
            System.out.println("naem = " + resultSet.getObject("NAME"));
            System.out.println("password = " + resultSet.getObject("PASSWORD"));
            System.out.println("email = " + resultSet.getObject("email"));
            System.out.println("birth = " + resultSet.getObject("birthday"));
            System.out.println("============================");
        }

        // 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

步骤总结：

1、加载驱动

2、连接数据库DriverManager

3、获得执行sql的对象 Statement

4、获得返回的结果集

5、释放连接

DriverManager

Class.forName("com.mysql.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";

// 协议://主机地址:端口号/数据库名?

//oralce --1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement

//Statement 执行sql的对象
statement.executeQuery();//查询操作返回 ResultSet
statement.execute(;//执行任何sQL
statement.executeupdate();//更新、插入、删除。都是用这个，返回一个受影响的行数

ResultSet

Resultset查询的结果集，封装了所有的查询结果

可以获得指定的数据类型

resultSet.getObject()	//在不知道列的具体类型时使用
// 如果知道就使用指定的类型

遍历，指针

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

10.2、statement对象#

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

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

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

编写工具类（利用反射机制），创建配置文件（db.properties里面存放Driver和URL等信息）

工具类：

package lesson01;

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");

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

代码：

package lesson01;

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

public class ST_insert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values(4,'xy','4654656','[email protected]','2020-01-01')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入问题

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

//登录业务
//当用户输入的用户名和密码为下述情况时，会返回数据库中所有数据，导致数据泄露
Login("'or'1=1","'or'1=1")

10.3、PreparedStatement对象#

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

防注入的本质：把传递进来的参数当做字符，假设其中存在转义字符，就直接忽略，比如说 ‘ 会被直接转义

package lesson01;

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

public class PST_insert {
    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);
            st.setString(2,"xy");
            st.setString(3,"536365");
            st.setString(4,"[email protected]");

            //注意点： sql.Date 数据库
            // java.util.Date new Date().getTime()获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);
        }
    }
}

10.4、事务#

代码实现

1、开启事务：conn.setAutoCommit(flase);

2、一组业务执行完毕，提交事务

3、可以在catch语句中显示的定义回滚语句，但默认失败就会回滚

10.5、数据连接池#

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

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

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

最小连接数（一般就是常用连接数）

最大连接数（业务承载能力上限）

等待超时（超出业务承载能力上限后，排队等待的最长时间）

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

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4、 commons-pool-1.6

C3P0

需要用到的jar包

c3p0-0.9.5.5、 mchange-common-java-0.2.19

结论

无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变

标签：教程,--,数据库,sql,student,MySQL,null,SELECT
From： https://www.cnblogs.com/foguang/p/18352153

1、初识MySQL#

1.1 什么是数据库#

1.2 数据库分类#

1.3 MySQL安装#

1.4 在安装MySQL过程中的一些错误

1.5 SQLyog#

1.6 连接数据库#

2、操作数据库#

2.1 操作数据库#

2.2 数据库的数据（列）类型#

2.3 数据库的字段属性（重点）#

2.4 创建数据库#

2.5 数据表的类型#

2.6 修改删除表#

3、MySQL数据管理#

3.1、外键#

3.2、DML语言#

3.3、添加#

3.4、修改#

3.5、删除#

4、DQL查询数据#

4.1查询指定字段#

4.2、where条件子句#

4.4、联表查询#

4.5、分页和排序#

4.6、子查询#

4.7、分组和过滤#

4.8、select小结#

5、MySQL函数#

5.1、常用函数#

5.2、聚合函数#

5.3、数据库级别的MD5加密#

6、事务#

6.1、什么是事务#

7、索引#

7.1、索引的分类#

7.2、索引原则#

8、权限管理和备份#

8.1、用户管理#

8.2、MySQL备份#

9、规范数据库设计#

9.1概述#

9.2、三大范式#

10、JDBC（重点）#

10.1、第一个JDBC程序#

10.2、statement对象#

10.3、PreparedStatement对象#

10.4、事务#

10.5、数据连接池#

相关文章

赞助商

阅读排行