一、认识MySQL
1、认识
MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),使用最常用的数据库管理语言–结构化查询语言(SQL)进行数据库管理。
MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。
MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下,MySQL是管理内容最好的选择。
(百度百科)
2、下载
目前:5.7版本用的人比较多且较为稳定
尽量下载ZIP压缩包,解压后配置一下就能用了,删除也方便,如果是安装的删除就比较麻烦了。
有位博主安装教程还不错 教程
二、操作数据库
1.操作数据库 (了解)
1.1 创建
-- CREATE DATABASE [IF NOT EXISTS] 数据库名;
CREATE DATABASE [IF NOT EXISTS] student;
1.2 删除
-- DROP DATABASE [IF EXISTS] 数据库名;
DROP DATABASE [IF EXISTS] student;
1.3 使用数据库
-- USE `数据库名`;
USE `school`;
1.4 查看数据库
SHOW DATABASES -- 查看所有的数据库
2.列类型
2.1 数值
类型名 | 说明 | 字节数 |
---|---|---|
tinyint | 十分小的数据 | 1 |
smallint | 较小的数据 | 2 |
mediumint | 中等大小的数据 | 3 |
int | 标准的整数 | 4 |
bigint | 较大的数据 | 8 |
float | 单精度浮点数 | 4 |
double | 双精度浮点数 | 8 |
decimal | 字符串类型的浮点数(金融计算时候使用) | 不定 |
2.2 字符串
类型名 | 说明 | 范围 |
---|---|---|
char | 字符串固定小小的 | 0-255 |
varchar | 可变字符串 | 0-65535 |
tinytest | 微型文本 | 2^8-1 |
test | 文本串(保存大文本) | 2^16 -1 |
2.3 时间日期
类型名 | 格式 | 说明 |
---|---|---|
date | YYYY-MM-DD | 日期格式 |
time | HH:mm:ss | 时间格式 |
datetime | YYYY-MM-DD HH:mm:ss | 最常用的时间格式 |
timestamp(时间戳) | 1970.1.1到现在的毫秒数 | |
year | 表示年份 |
2.4 null
没有值,未知
不要用NULL进行运算,结果为NULL
3.字段类型
3.1 Unsigned
- 无符号整数
- 声明了该列的值就不能为负数(若为负数,则全部自动变成0)
3.2 zerofill
- 0填充的
- 不足位数,使用0来填充
- 例如 int(3),若插入数据为1,则自动变成 001
注意:int(3) 并不是指定int的长度,而是指定填充的位数
3.3 自增
- 通常理解为自增,自动在上一条记录的基础上自动+1(默认)
- 通常用来设计唯一的主键~index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
3.4 非空 NULL not null
- 如果设置为NOT NULL,如果没有给它赋值,则会报错!
- NULL,如果不填写值,默认值就是NULL
3.5 默认
- 设置默认的值!
4.创建数据库的表(重点)
/*
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列属性 [约束],
`字段名` 列属性 [约束],
`字段名` 列属性 [约束],
[PRIMARY KEY(`主键字段名`)]
)[约束];
*/
-- COMMENT '注释'
-- DEFAULT '默认值'
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;
5.引擎(了解)
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定(跟锁相关) | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为MYISAM的2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户的操作
5.修改表字段
5.1 查看表结构
-- SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE school; -- 查看创建数据库的语句
--SHOW CREATE TABLE `表名`;
SHOW CREATE TABLE `student`;-- 查看创建表的语句
-- DESC `表名`;
DESC `student`; -- 查看表结构,等价于 describe `student`;
5.2 修改
-- 修改表名:ALTER TABLE `旧表名` RENAME AS `新表名`;
ALTER TABLE `teacher` RENAME AS `teach`;
-- 增加表的字段:ALTER TABLE `表名` ADD 字段名 列类型[];
ALTER TABLE `teach` ADD age INT(3);
-- 修改表的字段(重命名,修改约束)
-- ALTER TABLE `表名` MODIFY 字段名 列属性[];
ALTER TABLE `teach` MODIFY age VARCHAR(11); -- 修改约束
-- ALTER TABLE `表名` CHANGE 旧字段名字 新字段名字 列属性[]
ALTER TABLE `teach` CHANGE age age1 INT(1); -- 字段重命名
-- 结论
-- change用来字段重命名,不能修改字段类型和约束
-- modify不用来字段重命名,只能修改字段类型和约束
-- 删除表的字段:
-- ALTER TABLE `表名` DROP 字段名;
ALTER TABLE `teach` DROP age1;
5.3 删除
-- 删除表
-- DROP TABLE [IF EXISTS] 表名;
DROP TABLE IF EXISTS teach;
注意点:
`` 字段名,必须用这个包裹起来!
注释: – /**/
sql关键字大小写不敏感,建议用小写!
所有符号都用英文
三、MySQL数据管理
1、外键(了解)
外键名一般为 FK_ 开头
方式一:在创建表时,增加约束(较为麻烦)
/*
KEY `外键名`(`引用表的字段名`), -- 定义一个外键
CONSTRAINT `外键名` FOREIGN KEY(`引用表的字段名`) REFERENCES `被引用表名`(`被引用表字段名`) -- 添加约束
*/
--删除表
DROP TABLE IF EXISTS `student`;
DROP TABLE IF EXISTS `grade`;
-- 被引用表
CREATE TABLE `grade`(
`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`name` VARCHAR(10) COMMENT '姓名',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 引用表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`gid` INT(5) NOT NULL COMMENT '年级id',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
PRIMARY KEY(`id`),
KEY `FK_gid`(`gid`),
CONSTRAINT `FK_gid` FOREIGN KEY(`gid`) REFERENCES `grade`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
方式二:创建表成功后,增加约束
CREATE TABLE `grade`(
`id` INT(5) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10),
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`gid` INT(5) NOT NULL,
`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;
-- 表中删除添加外键语句
ALTER TABLE `student`
ADD CONSTRAINT `KF_gid` FOREIGN KEY(`gid`) REFERENCES `grade`(`id`);
/* 语法
ALTER TABLE `外键所在的表`
ADD CONSTRAINT `外键名` FOREIGN KEY(`外键字段`) REFERENCES `被引用表`(`主键字段`);
*/
添加外键后
Cannot add foreign key constraint的可能原因
1.主键和外键的数据类型、数据长度或约束不同
2.某个表里已经有记录了
3.两个表的引擎不一样,查看表引擎:show table status from 数据库名 where name=‘表名’;
4.外键不能为主键
5.被参考的字段必须为主键
以上操作都是物理外键,是数据库级别的外键,不建议使用!(避免数据库太多造成困扰,例如删表)
最佳实现:
- 数据库就是单纯的表,只有行(数据)和列(字段)
- 当多张表项使用外键时,可以用程序去实现
2、DML语言(全部记住)
DML语言:数据操作语言
- Insert
- update
- dalete
-- 用到的表
CREATE TABLE `grade`(
`id` INT(5) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10),
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
2.1 添加
INSERT INTO 表名
([字段2
,字段2
,字段3
,…]) VALUES(‘值1’,‘值1’,‘值1’,…),(‘值1’,‘值1’,‘值1’,…),…;
-- 插入指定字段的值(其他字段必须可以为NULL或者有默认值或自增)
INSERT INTO `grade`(`name`) VALUES('大四');
-- 插入值且省略字段(此时必须与表中的字段一一对应且顺序一致)
INSERT INTO `grade` VALUES('2','大三');
-- 插入多行的值
INSERT INTO `grade`
VALUES('3','大二'),
('4','大一');
2.2 修改
UPDATE 表名
SET column_name1
=‘value1’[,SET column_name2
=‘value2’,…] WHERE [筛选条件];
-- 修改所有行的值
UPDATE `grade` SET `name`='研一';
-- 修改指定行的值
UPDATE `grade` SET `name`='研一' WHERE `id`=1;
重点:筛选条件
2.3 删除
DELETE FROM 表名
WHERE [筛选条件];
-- 删除所有数据(不同于清空表,不建议使用)
-- 例如删除后自增没有还原。删除前表的最后一个自增为10,删除后插入第一个数据,该值变成11,而不是从1开始。
DELETE FROM `grade`;
-- 删除指定行(一个元组)
DELETE FROM `grade` WHERE id='5';
TRUNCATE 命令
TRUNCATE TABLE 表名
;(该表不能被引用)
作用:完全清空一个数据库表,表的结构和索引约束不会改变
-- 清空grade表
TRUNCATE `grade`;
delete 和 truncate 的区别
- 相同:都能删除数据,都不会删除表的结构
- 不同:
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
CREATE TABLE `test`(
`id` INT(3) AUTO_INCREMENT,
`coll` VARCHAR(5) NOT NULL,
PRIMARY KEY(`id`)
);
INSERT INTO `test`(`coll`) VALUES('aa'),('bb'),('cc'); --插入完查看表
DELETE FROM `test`; -- 删除完重新执行插入,再查看表
TRUNCATE TABLE `test`;-- 清空完重新执行插入,再查看表
了解:DELETE删除的问题
,重启数据库,现象:
- InnoDB 自增列会从1开始(存在内存当中,断电即失)
- MyISAM 继续从上一个子增量开始(存在文件中,不会丢失)
四、DQL查询数据(核心!)
1、DQL
(Data Query Language: 数据查找语言)
- 所有的查询操作都用它。SELECT
- 数据库中最核心的语言,最重要的语句
SELECT完整语法
SELECT [ALL|DISTINCT]
{*|table.*|table.field1[as alias1] [,table.filed2[as alias2]][...]}
FROM table_name [as table_alias]
[left|right|inner join table_name2 [as table_alias2]] -- 联合查询
[WHERE ...] -- 指定结果满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count|row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
-- 注意[]代表可选 {}代表必选
用到的数据库以及表
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
-- 插入成绩数据 这里仅插入了一组,其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
2、指定查询字段
SELECT 字段1
,… from 表
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- ------------------------------------------------------------------------------------------
-- 查询所有
SELECT * FROM `student`;
-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`;
-- 给查询结果起别名(表也可以起别名)
SELECT `studentno` AS 姓名,`studentname` AS 学号 FROM `student`;
-- 函数
SELECT CONCAT('姓名:',`studentname`) AS 新名字 FROM `student`;
去重
DISTINCT 关键字
SELECT DISTINCT `studentno` FROM `result`;
其他好玩的
-- 查看系统版本(函数)
SELECT VERSION();
-- 用来计算(表达式)
SELECT 100*3-5 AS 计算结果;
-- 计算步长(变量)
SELECT @@auto_increment_increment;
-- 所有学生成绩 +1分
SELECT `studentno`,`studentresult`+1 AS 加分后 FROM `result`;
数据库中的表达式:文本值,列,Null,函数,计算结果,系统变量…
3、where 条件子句
作用:检索数据中符合条件的值
搜索的条件是由一个或多个表达式组成!结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都是真,结果为真 |
or || | a or b a||b | 逻辑或,其中一个为真,结果为真 |
Not ! | not a ! a | 逻辑非,真为假,假为真 |
尽量使用英文字母
-- 查询成绩在95-100之间
SELECT studentno,studentresult FROM result WHERE studentresult>=95 AND studentresult <=100;
SELECT studentno,studentresult FROM result WHERE studentresult>=95 && studentresult <=100;
-- 模糊查询
SELECT studentno,studentresult FROM result WHERE studentresult BETWEEN 95 AND 100;
-- or 与 and tongli
-- 查询学号不等于1000的学生
-- 1.查询学号等于1000的学生
SELECT `studentno` FROM student WHERE `studentno`=1000;
-- 2.条件子句结果取反
SELECT `studentno` FROM student WHERE NOT `studentno`=1000;
模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a IS NULL | 如果a为NULL,结果为真 |
IS NOT NULL | a IS NOT NULL | 如果a不是NULL,结果为真 |
BETWEEN AND | 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…}中任意一个,则结果真 |
-- IS NOT NULL
SELECT * FROM `subject`
WHERE `subjectname` IS NOT NULL;
-- IS NULL;
SELECT * FROM `subject`
WHERE `subjectname` IS NULL;
-- BETWEEN AND
SELECT * FROM `subject`
WHERE `subjectno` BETWEEN 1 AND 2;
-- LIKE 通配符 %表示0到任意个字符 _表示一个字符
SELECT * FROM `subject`
WHERE `subjectname` LIKE '高等数学%';
SELECT * FROM `subject`
WHERE `subjectname` LIKE '_等数学-1';
-- IN
SELECT * FROM `subject`
WHERE `subjectno` IN(1,2);
4、连表查询
思路
分析需求,分析查询的字段来自哪几张表(连接查询)
确定使用哪种连接查询(7种)
确定交叉点(两个表种哪些数据是相同的,可以当作用来连接条件的)
判断条件 [表1.]字段1 = [表2.]字段2
(当有字段名一样是,需给表起别名,并且字段的使用都必须指明是哪张表!)
-- 查询学生学号、姓名和成绩
SELECT `studentno` 学号,`studentname` 姓名,`studentresult` 成绩
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`;
-- 报错:Column 'studentno' in field list is ambiguous(因为两张表都有'studentno',所以必须指定是哪张表的)
-- 正确写法:
SELECT s.`studentno` 学号,`studentname` 姓名,`studentresult` 成绩
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`;
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 返回左表中所有的行;如果右表有匹配则连接,若无则值为NULL |
right join | 返回右表中所有的行;如果左表有匹配则连接,若无则值为NULL |
自连接(了解)
自己的表和自己的表连接,核心:把一张表拆成两张表
用到的表
CREATE TABLE category (
categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
pid INT(10) NOT NULL COMMENT '父ID',
categoryName VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY(categoryid)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;
INSERT INTO category(categoryid,pid,categoryName)
VALUES('2','1','信息技术'),('3','1','软件开发'),('4','3','数据库'),
('5','1','美术设计'),('6','3','web开发'),('7','5','PS技术'),('8','2','办公信息');
父表(pid为1,即pid!=categoryid)
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子表(pid=categoryid)
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
SELECT a.`categoryName` 父表,b.`categoryName` 子表
FROM `category` a,`category` b
WHERE a.`categoryid` = b.`pid`;
5、分页和排序
排序
-- 排序:升序ASC 降序DESC
-- ORDER BY 通过那个字段排序 怎么排
-- 用到的表在 四、1
-- 按成绩降序排序
SELECT * FROM result
ORDER BY `studentresult` DESC;
分页
为什么要分页?
1.缓解数据库压力
2.给人更好的体验(瀑布流:一直往下刷刷不完)
-- limit 起始位置,数据条数
-- 第一页 limit 0,3 0 = (1-1)*pageSize
-- 第二页 limit 3,3 3 = (2-1)*pageSize
-- 第n页 limit x,3 x = (n-1)*pageSize
-- [pageSize 每页数据的行数]
-- [limit x,y 是从x+1位置取y条数据(mysql索引从1开始)]
-- [数据总数/pageSize = 总页面数]
-- 按成绩降序排序
SELECT * FROM result
LIMIT 0,3;
SELECT * FROM result
LIMIT 3,6;
6、子查询
where 字段 = (select语句)
本质:在where语句中嵌套一个子查询语句
-- 用到的表在 四、1
-- 查询课程为 高等数学-1 且分数不小于80分的同学
-- 方式一
SELECT s.`studentno`,`studentresult`,`studentname`,`subjectname`
FROM `result` r
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `studentresult`>=80 AND `subjectname`='高等数学-1';
-- 方式二
-- 分数不小于80分的同学
SELECT s.`studentno`,`studentresult`,`studentname`
FROM `result` r
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80;
-- 在上面的基础上增加 查询课程为 高等数学-1
SELECT s.`studentno`,`studentresult`,`studentname`
FROM `result` r
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80 AND r.`subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1'
);
-- 方式三:
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno`=(
SELECT `studentno` FROM `result`
WHERE `studentresult`>=80 AND `subjectno` =(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1'
)
);
-- 子查询中表的字段无法显示在结果集上!
五、MySQL函数
[MySQL官网 函数]https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html)
1、常用函数(并不常用)
-- 数学运算
SELECT ABS(-5); -- 返回绝对值
SELECT CEILING(5.1); -- 向上取整
SELECT FLOOR(5.9); -- 向下取整
SELECT RAND(); -- 返回一个[0,1) 之间的随机数
SELECT SIGN(-10); -- 判断一个数的符号 0返回0,整数返回1,负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('Hello World!'); -- 字符串长度
SELECT CONCAT('Hello ','World!'); -- 拼接字符串
SELECT INSERT('Hello world!',7,5,'SQL'); -- 插入,从某个位置开始插入长度
SELECT LOWER('Hello World!'); -- 变成小写
SELECT UPPER('Hello World!'); -- 变成大写
SELECT INSTR('Hello World!','or'); -- 返回子串第一次出现的索引
SELECT REPLACE('Hello World!','World','sql'); -- 将指定的字符串用另外一个字符串替换
SELECT SUBSTR('Hello World!',6); -- 返回指定子串(4个函数重载)
SELECT REVERSE('Hello World!'); -- 反转字符串
-- 例子:查询姓张的同学的名字,并把张改为璋
SELECT REPLACE(`studentname`,'张','璋') 'name'
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());
2、聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 求平均值 |
MAX() | 求最大值 |
MIN() | 求最小值 |
… | … |
SELECT COUNT(`studentno`) FROM `result`; -- count(字段) 如果值为null会忽略
SELECT COUNT(*) FROM `result`; -- 不会忽略null值 本质:计算行数
SELECT COUNT(1) FROM `result`; -- 不会忽略null值 本质:计算行数
SELECT `subjectname`,COUNT(`subjectname`)
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY `subjectname` -- 分组
HAVING AVG(`studentresult`)>80 -- 分组后的筛选
拓展:使用MD5加密
-
MD5信息摘要算法(英语:MD5 Message-Digest Algorithm),一种被广泛使用的密码散列函数,可以产生出一个128位(16字节)的散列值(hash value),用于确保信息传输完整一致。
-
1996年后该算法被证实存在弱点,可以被加以破解,对于需要高度安全性的数据,专家一般建议改用其他算法,如SHA-2。2004年,证实MD5算法无法防止碰撞(collision),因此不适用于安全性认证,如SSL公开密钥认证或是数字签名等用途。
网上一些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,'zhangsan','123456'),(2,'lisi','123asa6'),(3,'wangwu','4sad26');
-- 此时数据是明文的(即未加密)
-- 使用md5加密
UPDATE `testmd5` SET `pwd` = MD5(pwd);
-- 在插入数据时使用md5加密
INSERT INTO `testmd5` VALUES(6,'wanglihong',MD5('123.com'));
-- 此算法一般的不可破解的
-- 当用户传入密码后,将该密码进行加密后与原来数据库中加密后的密码比较,即可知道密码是否正确
SELECT * FROM `testmd5` WHERE `pwd`=MD5('123.com');
六、事务
1、什么是事务
要么都成功,要么都失败
一组SQL:
- SQL执行 A给B转账 A 1000 -->200 B 200
- 中间如果出现程序中断(如果不是事务):A少了200,而B没有收到。所以需要解决该问题
- SQL执行 B收到A的钱 A 800 --> B 400
将一组SQL放在一个批次中去执行
事务原则:ACID原则 原子性、一致性、隔离性、持久性(脏读、幻读…)
原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(Consistency)
事务前后数据的完整性必须保持一致。
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响
隔离导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
页面统计查询值
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)
七、索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构
提取句子主干,就可以得到索引的本质:索引是数据结构
1、索引的分类
- 主键索引(Primary Key)
- 唯一的标识,主键不可重复,只能有一个列为主键
- 唯一索引(Unique Key)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识唯一索引
- 常规索引(Key|Index)
- 默认的,index,key关键字来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有,MyISAM
- 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建表完成后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM `表名`
-- 增加一个全文索引
ALTER TABLE `表名` ADD FULLTEXT `索引名`(`字段名`);
-- EXPLAIN 分析sql执行情况
EXPLAIN SELECT * FROM `表名`; -- 非全文索引
2、测试索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';
-- 插入100万数据.
DELIMITER $$
-- set global log_bin_trust_function_creators=TRUE;
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据
-- ================== 测试 ===================
-- 创建索引前
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.056 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.079 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.053 sec
-- 创建索引后
CREATE INDEX id_app_user_name ON `app_user`(`name`); -- 9.018 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.001 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0 sec
小数据量不用索引
3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不用加索引
- 索引一般加在常用来查询的字段
索引的数据结构
八、权限管理和备份
1、用户管理
SQLyog可视化操作
命令行操作
-- 创建用户
CREATE USER cc IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('111111');
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR cc = PASSWORD('111111');
-- 重命名 RENAME USER FOR 旧名 TO 新名;
RENAME USER cc TO dd;
-- 用户授权
-- ALL PRIVILEGES 除了传播权限,其他都有
-- [with grant option] 传播权限
GRANT ALL PRIVILEGES ON *.* TO dd;
-- 撤销权限 REVOKE 哪些权限 在哪个库 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM dd;
-- 删除用户
DROP USER dd;
2、备份SQL
为什么要备份?
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件(MySQL的data文件)
-
在SQLyog等可视化软件工具种手动导出
- 在想要导出的表或库中,右键,选择备份/导出,选择备份数据库,转存到sql
- 再根据需要选择
-
使用命令行导出 mysqldump命令
# 导出表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表1 表2 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql
# 导出数据库
# mysqldump -h 主机 -u 用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql
# 导入表
# 登录的情况下,切换到指定的数据库
# source 备份文件
source d:/a.sql;
# 不登录
mysql -u用户名 -p密码 库名<备份文件
九、规范数据库设计
1、为什么需要设计
当数据库比较复杂的时候,外面就需要设计
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会很麻烦,异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计:
- 需求分析:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客为例)
- 收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友链信息)
- 自定义表(心痛信息,某个关键的字,或者一些主字段) key:value
- 标识实体(把需求落地到每个字段)
- 标识实体之间的关系
- 写博客:user --> blog
- 创建分类:user --> category
- 关注:user —> user
- 友链:links
- 评论:user-user-blog
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.19 : Database - blog
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`blog` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `blog`;
/*Table structure for table `blog` */
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
`id` int(10) NOT NULL COMMENT '文章的唯一标识',
`title` varchar(100) NOT NULL COMMENT '文章标题',
`author_id` int(10) NOT NULL COMMENT '编写文章的用户id',
`category_id` int(10) NOT NULL COMMENT '文章分类',
`content` text NOT NULL COMMENT '文章内容',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '修改时间',
`love` int(10) DEFAULT NULL COMMENT '喜欢',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `blog` */
/*Table structure for table `category` */
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
`id` int(10) NOT NULL COMMENT '分类id',
`category_name` varchar(30) NOT NULL COMMENT '分类标题',
`create_name_id` int(10) NOT NULL COMMENT '创建用户的id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `category` */
/*Table structure for table `comment` */
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`id` int(10) NOT NULL COMMENT '评论id',
`blog_id` int(10) NOT NULL COMMENT '所属文章',
`user_id` int(10) NOT NULL COMMENT '评论人',
`content` varchar(2000) NOT NULL COMMENT '评论的内容',
`create_time` datetime NOT NULL COMMENT '评论时间',
`user_id_parent` int(10) NOT NULL COMMENT '回复人的id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `comment` */
/*Table structure for table `links` */
DROP TABLE IF EXISTS `links`;
CREATE TABLE `links` (
`id` int(10) NOT NULL COMMENT '友链id',
`links` varchar(50) NOT NULL COMMENT '网站名称',
`href` varchar(2000) NOT NULL COMMENT '网站链接地址',
`sort` int(10) DEFAULT NULL COMMENT '排序',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `links` */
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL COMMENT '用户唯一id',
`username` varchar(60) NOT NULL COMMENT '用户名',
`password` varchar(60) NOT NULL COMMENT '用户密码',
`sex` varchar(2) NOT NULL COMMENT '性别',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`sign` varchar(200) DEFAULT NULL COMMENT '签名',
`open_id` varchar(1000) NOT NULL COMMENT '微信id',
`avatar` varchar(1000) NOT NULL COMMENT '头像链接地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
/*Table structure for table `user_follow` */
DROP TABLE IF EXISTS `user_follow`;
CREATE TABLE `user_follow` (
`id` int(10) NOT NULL COMMENT '唯一标识',
`user_id` int(10) NOT NULL COMMENT '被关注人的id',
`follow_id` int(10) NOT NULL COMMENT '关注人的id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user_follow` */
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2、三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一项数据和主键直接相关,而不能间接相关。
(规范数据库的设计)
规范性和性能的问题
关联查询的表不得超过三张
- 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
- 在规范性的问题的时候,需要适当考虑一下规范性。
- 故意给某些表增加一些冗余的字段。(从多表查询变成单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
十、JDBC(重点)
1、数据库驱动
驱动:声卡、显卡、数据库
我们的程序通过数据库驱动操作数据库!
2、JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说,只需要掌握JDBC接口操作即可
用到的包
java.sql
javax.sql
导入一个数据库驱动的包:mysql-connector-java-5.1.47.jar
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')
3.1 IDEA创建一个普通Java项目
3.2 在该项目下新建一个lib目录
右键该项目,New --> Directory
把mysql驱动包复制到lib目录下
右键lib目录,选择Add As Library…
3.3 测试代码
新建一个JdbcFirstDemo的类
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接信息,连接数据库(获取连接对象)
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, username, password);
//3.获得数据库对象
Statement statement = connection.createStatement();
//4.对数据库执行CRUD操作
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();
}
//5.释放资源
resultSet.close();
statement.close();
connection.close();
}
}
查询结果:
步骤总结
//1.加载驱动 Class.forName("驱动名") //2.编写连接信息(url,username,pwd),连接数据库(获取连接对象) Connection connection = DriverManager.getConnection(url, username, password); //3.获得数据库对象 Statement statement = connection.createStatement(); //4.对数据库执行CRUD操作 String sql = "SELECT * FROM `users`;"; ResultSet resultSet = statement.executeQuery(sql); //[遍历结果集] while (resultSet.next()){ resultSet.getObject("字段名")); } //5.释放资源 resultSet.close(); statement.close(); connection.close();
4、JDBC对象解释
DriverManager
// 第一种写法不建议使用,因为会注册两次驱动(源码)
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql -- 3306
//主协议:子协议://主机地址:端口号/数据库名?参数1&参数2&参数3
//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid
// 数据库中没有库的概念,只有表
Connection
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//处理事务
connection.commit();
connection.rollback();
connection.getAutoCommit();
Statement & PrepareStatment
// 执行SQL的对象
String sql = "SELECT * FROM `users`;";//编写sql语句
ResultSet resultSet = statement.executeQuery(sql); //查询操作,返回ResultSet
statement.execute(sql); //执行任何SQL
statement.executeUpdate(sql); //更新,插入,修改都是用这个,返回一个受影响的行数
ResultSet
// 查询的结果集
resultSet.getObject("字段名"); //不知道列类型的时候使用
resultSet.getInt(int i); // 知道列类型的时候使用
resultSet.getInt("字段名");
// 遍历(指针)
resultSet.next();//移到下一条记录
resultSet.beforeFirst();//移到第一条记录
resultSet.afterLast();//移到最后一条记录
resultSet.previous();//移动到前一行
resultSet.absolute(int row);//移动到指定行
必须释放资源
resultSet.close();
statement.close();
connection.close();
5、JDBC提取工具类
配置文件
src目录下新建db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root
编写工具类
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 con, 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(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试插入
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection connection = JdbcUtils.getConnection();
Statement statement = connection.createStatement();
String sql = "INSERT INTO `users` VALUES('4','huihui','456123','123@qq.com','2020-12-6')";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
JdbcUtils.release(connection,statement,null);
}
}
6、SQL注入
通过拼接sql语句进行攻击(本来只能用账户密码登录,sql注入之后可以查到所有用户的账户密码)
public class SqlAttack {
public static void main(String[] args) throws Exception {
//login("zhansan","123456"); 正常查询
// SELECT * FROM `users` WHERE `name`='' OR 1=1 OR '1' AND `password`='';
login("'or 1=1 or '1","1");//sql注入,把所有用户都查出来
}
public static void login(String username,String password) throws Exception {
Connection connection = JdbcUtils.getConnection();
Statement statement = connection.createStatement();
// SELECT * FROM `users` WHERE `name`='zhansan' AND `password`='123456';
String sql = "SELECT * FROM `users` WHERE `name`='"+username+"'AND `password`='"+password +"'";
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();
}
JdbcUtils.release(connection,statement,resultSet);
}
}
7、PreparedStatement
可以有效防止SQL注入:把传递进来的参数全部变成字符串,而转义符全部忽略
查询
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection connection = JdbcUtils.getConnection();
// 问号?表示占位符
String sql = "select * from `users` where id = ?";
//预编译SQL语句
PreparedStatement pstm = connection.prepareStatement(sql);
//传递参数
pstm.setInt(1,1);
// 执行sql
ResultSet rs = pstm.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name"));
}
JdbcUtils.release(connection, pstm, rs);
}
}
插入
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection connection = JdbcUtils.getConnection();
// 问号?表示占位符
String sql = "insert into `users` values(?,?,?,?,?)";
//预编译SQL语句
PreparedStatement st = connection.prepareStatement(sql);
//传递参数
st.setInt(1,5);
st.setString(2,"Allen");
st.setString(3,"1230");
st.setString(4,"abc@qq.com");
// 穿入的Date对象是 java.sql包下的
// 该对象需要一个时间戳参数,用java.util包下的Date传递该参数
st.setDate(5,new Date(new java.util.Date().getTime()));
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
// 执行sql
JdbcUtils.release(connection, st, null);
}
}
// 删除和更新也类似,只需要更改sql语句和传入参数即可
IDEA 连接数据库
点击左小角小窗口图标,右侧出现Database边框
1、打开mysql连接
2、输入用户名和密码,测试连接
PS:如果连接失败,可能是时区的问题
3、选择想看的数据库
4、写sql语句
5、其他操作
事务
connection.setAutoCommit(false);//开启事务
connection.commit(); //提交事务
// 默认失败会自动回滚,也可以在catch中显示定义
数据库连接池
为什么要用连接池?
如果每次执行sql语句都需要连接和释放数据库连接,那么这样会很耗费资源,所以这时候就需要用到连接池!
连接池:实现建立好一些连接,放在一个地方。需要与数据库连接时就从池子里取出,用完就放回去,这样就能提高效率!
完结撒花!!!