1. mysql安装连接
1.1 安装mysql5.7
1、解压 2、把这个包放到自己的电脑环境目录下 3、配置环境变量 4、新建mysql配置文件 ini
[mysq1d]
# 目录一定要换成自己的
basedir=D:\Environment mysq1-5.7.19\
datadir=D:\Environment\mysq1-5.7.19\data\
port=3306
skip-grant-tables
5、启动管理员模式下的CMD,运行所有的命令 6、安装mysql服务 7、初始化数据库文件 8、启动mysql,进去修改密码~ 9、进入mysql 通过命令行(-p后面不要加空格),修改密码 (sql语句后面一定要加分号!) 10、注掉 ini 中的跳过密码 11、重启mysql。连接测试,如果连接成功就OK了!
1.2. 连接数据库
命令行连接!
mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root'and Host ='localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
---------------------------------------------
-- 所有的语句都使用; 结尾
show databases; -- 查看所有的数据库
mysql> use school-- 切换数据库 use 数据库名
Database changed
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
create database westos; -- 创建一个数据库
exit; --退出连接
-- 单行注释(SQL 的本来的注释)
/* (sg1的多行注释)
hello
22
23asdas
24dasdas
*/
2. 操作数据库
2.1 操作数据库(了解)
1、创建数据库
CREATE DATABASE IF NOT EXISTS westos;
2、删除数据库
DROP DATABASE IF EXISTS westos
3、使用数据库
-- tab 键的上面,如果你的表名或者字段名是一个特殊字符,就需要带``
USE `school`
4、查看数据库
SHOW DATABASES -- 查看所有的数据库
2.2 数据库的列类型
1、数值
-
tinyint 十分小的数据 1字节
-
smallint 较小的数据 2字节
-
mediumint 中等大小的数据 3字节
-
int 标准的整数 4个字节
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节
-
decimal 字符串形式的浮点数 用于金融计算
2、字符串
-
char 字符串固定大小的 0-255
-
varchar 可变字符串 0-65535 常用的变量 String
-
tingtext 微型文本 2^8-1
-
text 文本串 2^16-1 保存大文本
3、时间日期
-
date YY-MM-DD 日期格式
-
time HH:MM:SS 时间格式
-
datetime yy-mm-dd hh:mm:ss 最常用的日期格式
-
timestamp 时间戳 1970.1.1 到现在的毫秒数 较为常用
-
year 年份表示
4、null
-
没有值,未知
-
$\textcolor{red}{}$注意,不要使用null进行运算,结果为null
2.3 数据库的字段属性(重点)
Unsigned:
-
无符号的整数
-
声明了该列不能声明为负数
zerofill:
-
0填充的
-
不足的位数,使用0来填充, int (3) , 5 -- 005
非空:null not null
-
假设设置为 not null ,如果不给它赋值,就会报错!
-
NUII ,如果不填写值,默认就是null!
默认:
-
设置默认的值!
-
sex,默认值 为 男,如果不指定该列的值,则会有默认的值!
2.4 创建数据库表(重点)
-- 目标: 创建一个school数据库
-- 创建学生表(列,字段) 使用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倍 |
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹对应一个数据库 本质还是文件的存储!
-
InnoDB 在数据库表中只有一个*.frm 文件,以及上级目录下的ibdata1 文件
-
MYISAM对应文件(了解)
-
*.frm 表结构的定义文件
-
*.MYD 数据文件(data)
-
*.MYI 索引文件(index)
-
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是mysql默认的字符集编码~(不支持中文)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6 修改删除表
修改
-- 修改表 ALTER TABLE 旧表名 RENAME AS 新表名 ALTER TABLE student RENAME AS student1 -- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE student1 ADD age INT(11) -- 修改表的字段 (重命名,修改约束) -- ALTER TABLE 表名 MODIFY 字段名 列属性 ALTER TABLE student1 MODIFY age VARCHAR(11) -- 修改约束 -- ALTER TABLE 表名 MODIFY 旧字段名 新字段名 列属性 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 '密码', `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
数据库级别的外键不建议使用
3.2 DML语言(记)
DML语言:数据操作语言
-
Insert
-
update
-
delete
3.3 添加 INSERT
# 语法格式 1. 插入一行数据 INSERT INTO 基本表名(列名1,列名2,...,列名n) VALUES(数据1,数据2,...,数据n); 2. 同时插入多行数据 INSERT INTO 基本表名(列名1,列名2,...,列名n) VALUES (数据11,数据12,...,数据1m), (数据21,数据22,...,数据2m), ...... (数据n1,数据n2,...,数据nm); # 示例1:给基本表emp插入一行记录:empno=1001,empname='小明',sex='男' INSERT INTO emp(empno,empname,sex) VALUES(1001,'小明','男'); # 示例2:同时给emp插入两行数据 INSERT INTO emp(empno,empname,sex) VALUES (1002,'小红','女'), (1003,'小亮','男');
注意事项:
-
字段和字段之间使用 英文逗号 隔开
-
字段是可以省略的,但是后面的值必须要要一一对应,不能少
-
可以同时插入多条数据,VALUES 后面的值,需要使用,隔开即可 ()
3.4 修改 UPDATE
# 语法格式 UPDATE 基本表名 SET 目标列1=目标值1,目标列2=目标值2,...,目标列n=目标值n [where 条件语句] # 示例:将emp表中empnp=1001的empname修改为二明 UPDATE emp SET empname='二明' where empno=1001;
where条件语句是用来限定修改哪一行记录的数据的,如果不加where语句,那么会将目标列所有的值都修改为同一个值。
条件:where 子句 运算符 ID等于某个值,大于某个值,在某个区间内修改
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | a=a | True |
<>或 != | 不等于 | a!=b | True |
> | 大于 | 1>2 | True |
< | 小于 | 1<2 | True |
= | 大于等于 | 3>=2 | True |
<= | 小于等于 | 2<=3 | True |
between…and, | 在某个范围内,闭合区间 | [2,5] | True |
and | 和 | 2<3 and 1< 4 | True |
or | 或 | 2<3 or 3 >5 | True |
3.5 删除 DELETE、TRUNCATE
DELETE FROM 基本表名 [where 条件语句]
DELETE操作用于删除基本表中的数据。
TRUNCATE TABLE 基本表名;
TRUNCATE 操作用于删除整个表的数据,其过程是先将这个表DROP掉,然后再建立一个相同的表格。
delete 和 TRUNCATE的区别
-
相同点:都能删除数据,都不会删除表结构
-
不同点:
-
truncate:清空,自增列会归零。不会影响事务
-
delete:清空, 自增列不归零
-
4. DQL查询数据(最重点)
4.1 DQL语句完整语法格式
SELECT 要查询的列名 FROM 基本表名 # 要查询的基本表名字 WHERE 限定条件 # 条件限定语句,用来筛选符合条件的记录 GROUP BY 分组列 # 按照分组列分组,比如说按照部门列分组,分组之后一个部门是一条记录 HAVING 限定条件 # 分组之后的条件,可以按照这个条件筛选分组之后的结果 ORDER BY 排序规则 # 按照排序规则对结果集进行排序 LIMIT 开始下标,结果数量; # 显示指定条数的结果记录
4.2 基础查询
(1)查询表中单个字段
select 查询列表 from 表名
(2)查询多个字段
select 查询列表,查询列表,...,查询列表 from 表名
(3)查询所有字段
select * from 表名
(4)查询常量值
select 100; select 'jack';
(5)查询表达式
select 100*2; select 50%2;
(6)查询函数
select version();
(7)起别名
as 来给字段起别名的好处:
-
便于理解
-
如果查询的字段有重名的情况,可以使用别名来区分
-
as可以省略掉
SELECT 100*50 AS "结果" ;
(8)去重(distinct)
# 查询员工表中所有 部门编号 (不要重复) select DISTINCT department_id from employees;
(9)CONCAT 连接
# 拼接字段内容 SELECT CONCAT( last_name, first_name ) AS "结果" FROM employees;
4.3 条件查询
语句:select 查询列表 from 表名 where 筛选条件;
分类:
-
按条件表达式筛选 > >= < <= = != <>
-
按逻辑表达式筛选 &&(and) ||(or) ! (not)
-
模糊查询 1>、like 2>、between and 3>、in 4>、is null
(1)按条件表达式
例如:查询工资大于12000的员工信息
select * from employees where salary>12000;
(2)按逻辑表达式
例如:查询工资在10000到15000之间的员工姓、工资
select last_name,salary from employees where salary>=10000 and salary<=15000;
# 案例:查询部门编号不在 90-110 之间 或者 工资高于 15000 的员工信息 SELECT * FROM employees WHERE NOT ( department_id >= 90 AND department_id <= 110 ) OR salary >= 15000;
(3)模糊查询
①简单通配符
% : 任意多个字符(包含0个字符) _ : 任意单个字符
②转义符
\ escape
③like 关键字
练习一 (%):
# 案例:查询 first_name 为 d 开头的员工信息 SELECT * FROM employees WHERE first_name LIKE 'd%';
练习二 (% _):
# 案例:查询 first_name 中第一个字符为d,第三个字符为n的员工信息 SELECT * FROM employees WHERE first_name LIKE 'd_n%';
练习三 (% _ \ ):
# 案例:查询 last_name 中第二个字符为_的员工信息 SELECT * FROM employees WHERE last_name LIKE '_\_%';
练习四 (% _ escape ):
此时 $ 相当于 \ (推荐)
# 案例:查询 last_name 中第二个字符为_的员工信息 SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
④between and 关键字
# 案例:查询 工资在 10000-20000 的员工信息 SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000 ;
⑤in 关键字
# 案例:查询 工作编号 为 AD_VP,SA_MAN,PR_REP 的员工信息 SELECT * FROM employees WHERE job_id IN ("AD_VP" , "SA_MAN" , "PR_REP") ;
⑥is null 关键字
# 案例:查询 奖金率 为 null的员工编号,奖金率 SELECT employee_id, commission_pct FROM employees WHERE commission_pct IS NULL ;
⑦ is not null 关键字
# 案例:查询 奖金率 不为 null的员工编号,奖金率 SELECT employee_id, commission_pct FROM employees WHERE commission_pct IS NOT NULL ;
(4)排序查询
DESC: 降序排序 (从高到低) ASC: 升序排序 (从低到高)
练习一 (DESC , ASC):
# 案例:查询员工信息,并按照工资从高到低排序 SELECT * FROM employees ORDER BY salary DESC;
(可以不加ASC,默认排序方式为ASC)
# 案例:查询员工信息,并按照工资从低到高排序 SELECT * FROM employees ORDER BY salary ASC;
练习二 (添加筛选条件):
# 案例:查询员工编号>=90的员工信息,按入职时间的先后进行排序 SELECT * FROM employees WHERE employee_id >= 90 ORDER BY hiredate ASC;
练习三 (按表达式的别名排序):
# 案例:按年薪的高低显示员工的信息和年薪 SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;
练习四 (按函数排序):
# 案例:按姓的长度显示员工的姓和工资 SELECT LENGTH( last_name ) 姓的长度, last_name, salary FROM employees ORDER BY LENGTH( last_name ) DESC;
练习五 (按多个字段主次排序):
# 案例:将员工信息先按工资降序排序,再按员工编号升序排序 SELECT * FROM employees ORDER BY salary DESC, employee_id ASC;
(5)常见函数
单行函数 — 字符函数:
① length() 字符长度
SELECT LENGTH('abcde')
② concat() 字符拼接
SELECT CONCAT("aaa","bbb","ccc") 结果;
③ upper() ,lower() 大写,小写
# 将姓变大写,名变小写,然后进行拼接 SELECT CONCAT(UPPER( last_name ),LOWER( first_name )) 姓名 FROM employees;
④ substr() (全称:substring) 截取
ps : 下标从 1 开始
练习一 :
# 截取李四,从第四个开始一直到最后 SELECT SUBSTR("张三和李四",4) 结果;
练习二 :
# 截取张三 SELECT SUBSTR("张三和李四",1,2) ;
练习三 (concat,substr,upper,lower):
# 案例:姓中首字符大写,其他字符小写,然后用_拼接,显示出来 SELECT CONCAT( UPPER(SUBSTR( last_name, 1, 1 )), "_", LOWER(SUBSTR( last_name, 2 ))) 结果 FROM employees;
⑤ trim()
默认去除前面和后面的空格 前面指的是:第一个不为空格的字符之前的所有空格 后面指的是:最后一个不为空格的字符之后的所有空格
练习一 (去空格):
# 去除前面和后面的空格 SELECT TRIM(" a a a ") 结果 ;
练习二 (去指定字符):
前面 aa 的个数是奇数,所以剩下一个a
SELECT TRIM("aa" FROM "aaaaaaaaabbbaaaaaaaa") 结果 ;
⑥ lpad() 左填充长度
用指定的字符实现 左填充 指定长度
SELECT LPAD("aaa",6,"b") 结果 ;
⑦ rpad() 右填充长度
用指定的字符实现 右填充 指定长度
SELECT RPAD("aaa",6,"b") 结果 ;
⑧ replace() 置换
用 cc 去 替换 aa
SELECT REPLACE("aaaabbaaaa","aa","cc") 结果 ;
单行函数 — 数字函数 :
① round() 四舍五入
# 四舍五入 SELECT ROUND(5.5) ;
# 四舍五入 (第二个参数表示保留的小数位数) SELECT ROUND(5.55,1) ;
② ceil() 向上取整
# 向上取整 SELECT CEIL(2.00002) ;
③ floor() 向下取整
# 向下取整 SELECT floor(2.99999) ;
④ truncate() 截断
# 截断,保留几位小数(不进位) SELECT TRUNCATE(2.9999,1) ;
⑤ mod() 取余
# 取余 SELECT MOD(10,3) ;
单行函数 — 日期函数:
① now() 返回当前系统日期+时间
SELECT NOW() ;
② curdate() 返回当前系统日期,不包含时间
SELECT CURDATE() ;
③ curtime() 返回当前时间,不包含日期
SELECT CURTIME() ;
④ str_to_date() 字符转日期
SELECT STR_TO_DATE("21-2-2021","%d-%c-%Y") ;
⑤ year() ,month() ,day() 分别获取年月日
SELECT YEAR(NOW()) ;
单行函数 — 流程控制函数:
① if()
参数一:表达式 参数二:表达式为真时的结果 参数三:表达式为假时的结果
SELECT IF(1<2,"Yes","No") ;
② case()
/* 案例:查询员工的工资 当部门号为30 , 显示工资为2倍 当部门号为40 , 显示工资为3倍 当部门号为50 , 显示工资为4倍 其他部门,显示工资不变 */ SELECT salary 原工资,department_id 部门号, CASE department_id WHEN 30 THEN salary*2 WHEN 40 THEN salary*3 WHEN 50 THEN salary*4 ELSE salary END 新工资 FROM employees;
分组函数
常见的分组函数:sum()求和,avg()平均,max()最大值,min()最小值,count()个数 特点: 1:sum() 和 avg() 一般用于处理数值型 2:max(),min(),count() 可以处理任何类型 3:以上分组函数都忽略null值 4:可以和distinct进行搭配使用 5:count()函数,一般使用count(*)统计行数 6:和分组函数一同查询的字段要求是group by后的字段 (分组函数一般用来统计,只有一个结果,而查询的字段有多个结果,表格不规则)
分组函数 — 简单使用 :
① sum(),avg(),max(),min(),count()
SELECT SUM( salary ) 总工资, AVG( salary ) 平均工资, MAX( salary ) 最高工资, MIN( salary ) 最低工资, COUNT( salary ) 工资份数 FROM employees;
分组函数 — 搭配distinct :
① sum() + distinct
SELECT SUM( salary ) 总工资, SUM( DISTINCT salary ) 去重后的总工资 FROM employees;
② count() + distinct
SELECT COUNT( department_id ) 部门数, COUNT( DISTINCT department_id ) 去重后的部门数 FROM employees;
(6)分组查询 (group by)
1:分组函数作条件时要放在having子句中 2:能用分组前筛选的,就优先考虑分组前筛选 3:group by 子句支持单个字段分组,多个字段分组,表达式和函数分组(不常用) 4:也可以添加排序 (排序放在整个分组查询的最后)
① 简单使用 group by
#查询每个工种的最高工资 SELECT job_id 工种, MAX( salary ) 最高工资 FROM employees GROUP BY job_id
② 添加分组前筛选
练习一 :
#案例1:查询邮箱中包含a字符的每个部门的平均工资 SELECT department_id 部门, AVG( salary ) 平均工资 FROM employees WHERE email LIKE "%a%" GROUP BY 部门
练习二 :
#案例2:查询每个领导手下有奖金的员工的最高工资 SELECT manager_id 领导, MAX( salary ) FROM employees WHERE commission_pct IS NOT NULL GROUP BY 领导
③ 添加分组后筛选 (having)
个人理解: 案例一:查询哪些部门的员工个数>2 当前面的结果(即每个部门的员工个数)不为表中的字段时 需要先查询出前面的结果后才可以找出哪些员工个数>2,此时需要使用having ps:一开始错误的觉得可以直接放在where中。 where只能对表中已存在的字段进行操作(不知道理解的对不对) 而此时每个部门的员工个数在表中并不存在,需要先进行统计,再找>2的 mysql中这些关键字是按照如下顺序进行执行的:from, Where, Group By Having, Order by。 从这看出,假如在where中找员工个数大于2的,那么 还没进行分组,找的就不是每个部门的了,即使没报错结果也不对了。(xia bi bi)
练习一 :
#案例1:查询哪些部门的员工个数>2 SELECT department_id 部门, COUNT(*) 员工个数 FROM employees GROUP BY 部门 HAVING 员工个数 > 2
练习二 :
#案例二:查询每个工种有奖金的最高工资>12000的工种编号和最高工资 SELECT job_id 工种, MAX( salary ) 最高工资 FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING 最高工资 > 12000
练习三 :
/*案例三:查询领导编号>102的每个领导手下的员工最低工资>5000 的领导编号是哪个,以及其最低工资 */ SELECT manager_id 领导编号, MIN( salary ) 最低工资 FROM employees WHERE manager_id > 102 GROUP BY 领导编号 HAVING 最低工资 > 5000
④ 按函数分组
#案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些 SELECT LENGTH( last_name ) 员工姓名长度, COUNT(*) 员工个数 FROM employees GROUP BY 员工姓名长度 HAVING 员工个数 > 5
⑤ 按多个字段分组
#案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示 SELECT AVG( salary ) 平均工资, department_id 部门编号, job_id 工种 FROM employees GROUP BY 部门编号, 工种 ORDER BY 平均工资 DESC
(7)连接查询 join on
① 等值连接 (sql92标准)
练习一 :
# 案例一:查询员工名和对应的部门名 SELECT last_name, department_name FROM employees e, departments d WHERE e.department_id = d.department_id
练习二 :
# 案例二:查询员工名,工种编号,工种名 SELECT last_name, e.job_id, job_title FROM jobs j, employees e WHERE j.job_id = e.job_id
练习三 (加筛选):
# 案例三:查询有奖金的员工名,部门名 SELECT last_name, d.department_id FROM employees e, departments d WHERE commission_pct IS NOT NULL AND e.department_id = d.department_id
练习四 (加筛选):
# 案例四:查询城市名中第二个字符为o的部门名和城市名 SELECT department_name, city FROM departments d, locations l WHERE d.location_id = l.location_id AND city LIKE "_o%"
练习五 (加分组):
# 案例:查询每个城市的部门个数 SELECT COUNT(*), city FROM departments d, locations l WHERE d.location_id = l.location_id GROUP BY city
练习六 (加排序):
#案例:查询每个工种的工种名和员工的个数,并且按员工个数降序 SELECT job_title 工种名, COUNT(*) 员工个数 FROM jobs j, employees e WHERE j.job_id = e.job_id GROUP BY 工种名 ORDER BY 员工个数 DESC
练习七 (三表连接):
#案例:查询员工名,部门名和所在的城市 SELECT last_name 员工名, department_name 部门名, city 城市 FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id
② 非等值连接 (sql92标准)
#案例:查询员工的工资和工资级别 SELECT salary 工资, grade_level 工资级别 FROM employees e, job_grades j WHERE salary BETWEEN j.lowest_sal AND j.highest_sal
③ 自连接 (sql92标准)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
先从 employees表 中找到员工名以及他所对应的领导编号 然后再通过领导编号找到领导名 (相当于要查询两次employees表,所以将一张表起两个别名,当作两张表来操作)
#案例:查询员工名和上级的名称 SELECT e.employee_id 员工编号, e.last_name 员工名, m.employee_id 上级编号, m.last_name 上级名 FROM employees e, employees m WHERE e.manager_id = m.employee_id
① 等值连接 (sql99标准)(了解)
练习一 :
# 案例:查询员工名,部门名 SELECT last_name 员工名, department_name 部门名 FROM employees e INNER JOIN departments d ON e.department_id = d.department_id
练习二 (加筛选):
# 案例:查询名字中包含e的员工名和工种名 SELECT last_name 员工名, j.job_title 工种名 FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE last_name LIKE "%e%"
练习三 (添加分组加筛选):
# 案例:查询部门个数>3的城市名和部门个数 SELECT city, COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.location_id = l.location_id GROUP BY city HAVING 部门个数 >3
练习四 (加排序):
# 案例:查询哪个部门的部门员工个数>3的部门名和员工个数,并按个数降序 SELECT department_name 部门名, COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.department_id = e.department_id GROUP BY 部门名 HAVING 员工个数 > 3 ORDER BY 员工个数 DESC
练习五 (三表连接):
# 案例:查询员工名,部门名,工种名,并按部门名降序 SELECT last_name 员工名, department_name 部门名, job_title 工种名 FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY 部门名 DESC
② 非等值连接 (sql99标准)
练习一 :
# 案例:查询员工的工资级别 SELECT salary 工资, grade_level 工资级别 FROM employees e INNER JOIN job_grades j ON salary BETWEEN j.lowest_sal AND j.highest_sal
练习二 (加筛选,排序,分组) :
# 案例:查询员工个数>20的工资级别,并且按工资级别降序 SELECT grade_level 工资级别, COUNT(*) 个数 FROM employees e INNER JOIN job_grades j ON salary BETWEEN j.lowest_sal AND j.highest_sal GROUP BY 工资级别 HAVING 个数 > 20 ORDER BY 工资级别 DESC
外连接的应用场景和特点
应用场景:用于查询一个表中有,另一个表中没有的记录 (需要不匹配的记录null时) 特点: 1、 外连接的查询结果为主表中的所有记录 如果 从表 中有和他匹配的,则显示匹配的值 如果 从表 中没有和他匹配的,则显示null 外连接查询结果 = 内连接结果 + 主表 中有而 从表 中没有的记录 2、 左外连接,left join 左边的是主表 右外连接,right join 右边的是主表
① 左(右)外连接
练习一 :
# 案例:查询男朋友不在boys表中的女生名 SELECT b.NAME, bo.id, bo.boyName FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.id IS NULL
练习二 :
# 案例:查询哪个城市没有部门 SELECT city, d.* FROM locations l LEFT JOIN departments d ON l.location_id = d.location_id WHERE department_id IS NULL
练习三 :
# 查询部门名为SAL或IT的员工信息 SELECT d.department_id 部门编号,department_name 部门名, e.* FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE department_name IN ("SAL","IT")
② 交叉连接
展示结果类似两表的笛卡尔乘积
SELECT b.*, bo.* FROM beauty b CROSS JOIN boys bo
kuang-联表查询 JoinOn
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- Right Join -- student name=studen1 name 查询年龄为18的人 SELECT s.name,age,s.sex FROM student s RIGHT JOIN student1 s1 ON s.name = s1.name WHERE age=18
联表分组过滤
-- 查询不同课程的平均分,最高分,最低分,平均分大于80 -- 核心:(根据不同的课程分组) SELECT SubjectName,AVG(StudentResut) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) As 最低分 FROM result r INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo GROUP BY r.subjectNo -- 通过什么字段来分组 HAVING 平均分>80
(8)子查询
① where 后面的标量子查询
标量子查询:结果集只有一行一列
练习一 :
# 查询工资比 Abel 高的员工信息 SELECT * FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = "Abel" )
练习二 :
# 案例:返回job_id 与 141号员工相同,salary 比143号员工多的员工姓名,job_id 和工资 SELECT last_name,job_id,salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) AND salary > ( SELECT salary FROM employees WHERE employee_id = 143 )
练习三 :
#案例:返回公司工资最少的员工的last_name,job_id和salary SELECT last_name,job_id,salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees )
练习四 :
#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资 SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 )
② where 后面的列子查询 (in,any,some,all)
列子查询:结果集只有一列多行
练习一 :
#列子查询(多行子查询) # 案例1:返回location_id是1400或1700的部门中的所有员工姓名 SELECT last_name FROM employees e WHERE department_id IN ( SELECT department_id FROM departments d WHERE location_id IN(1400,1700) )
练习二 :
#案例2:返回其它工种中比job_id为"IT_PROG"部门任一工资低的员工的员工号,姓名,job_id 以及 salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ANY( SELECT salary FROM employees WHERE job_id = "IT_PROG" ) AND job_id <> "IT_PROG"
③ where 后面的行子查询
行子查询:结果集只有一行多列
#行子查询(结果集一行多列或多行多列) # 案例:查询员工编号最小并且工资最高的员工信息 SELECT * FROM employees WHERE (employee_id,salary) = ( SELECT MIN(employee_id),MAX(salary) FROM employees )
④ select 后面的标量子查询
# 案例:查询每个部门的员工个数 SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) 个数 FROM departments d
⑤ from 后面的表子查询
表子查询:结果集一般为多行多列
#案例:查询每个部门的平均工资的工资等级 SELECT ag.*,g.grade_level FROM ( SELECT department_id,AVG(salary) sa FROM employees GROUP BY department_id ) ag INNER JOIN job_grades g ON ag.sa BETWEEN g.lowest_sal AND g.highest_sal
(9)分页查询 (limit)
分页查询的应用场景和特点
应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求 limit offset,size offset:要显示条目的起始索引(起始索引从0开始) 若为0可以省略 size:要显示的条目个数 特点: 1、 limit语句放在查询语句的最后面 2、 公式: 当要显示的页数为page, 每页的条目数为size limit (page-1)*size , size 例如size=10 page 起始 0 0 1 10 2 20
练习一 :
# 案例1:查询前五条员工信息 SELECT * FROM employees LIMIT 5
练习二 :
# 案例2:查询第11条 - 第25条员工信息 SELECT * FROM employees LIMIT 10,15
练习三 :
# 案例3:查询有奖金的员工信息,并且工资较高的前10名显示出来 SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10
(10)联合查询 (union)
联合查询:将多条查询语句的结果合并为一个结果 语法: 查询语句1 union 查询语句2 union 查寻语句3 ..... 特点: 1、要求多条查询语句的查询列数是一致的 2、要求多条查询语句的每一列的类型和顺序最好一致 3、union关键字默认去重,如果使用union all可以包含重复项
5. MySQL函数
5.1 常用函数
-- 时间和日期函数 (记住) SELECT CURRENT_DATE() -- 获当前日期 SELECT CURDATE() -- 当前日期 SELECT NOW() -- 获取当前的时间 SELECT LOCALTIMEO -- 本地时间 SELECT SYSDATE() -- 系统时间 SELECT YEAR(NOW()) SELECT MONTH(NOW()) SELECT DAY(NOW()) SELECT HOUR(NoW()) SELECT MINUTE(NOW()) SELECT SECOND(NOW()) -- 系统 SELECT SYSTEM_USER( )SELECT USER() SELECT VERSION()
5.2 聚合函数(常用)
AVG() SUM() MAX() MIN() COUNT() -- 计数
-- 都能够统计 表中的数据 (想查询一个表中有多少个记录,就使用这个count()) SELECT COUNT(`BornDate`) FROM student; -- Count(字段),会忽略所有的 null值 SELECT COUNT(*) FROM student; -- Count (*) ,不会忽略null值,本质 计算行数 SELECT COUNT(1) FROM result; -- Count (1).不会忽略忽略所有的 null值,本质 计算行数
5.3 数据库级别的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=UtE8 -- 明文密码 INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2, 'lisi','123456'), (3,'wangwu','123456') -- 加密 UPDATE testmd5 SET pwd=MD5 (pwd) WHERE id = 1 UPDATE testmd5 SET pwd=MD5 (pwd)-- 加密全部的密码 -- 插入的时候加密 INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
6. 事务
事务原则:ACID原则 原子性,一致性,持久性 ,隔离性 (脏读,幻读......)
参考博客链接:https://blog.csdn.net/dengjili/article/details/82468576/
原子性 (Atomicity) 要么都成功,要么都失败
一致性 (Consistency) 事务前后的数据完整性要保证一致,1000
持久性(Durability) --- 事务提交 事务一旦提交则不可逆,被持久化到数据库中
隔离性 (Isolation) 事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干务之间要相互隔离
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; -- 恢复默认值 show variables like 'autocommit'; -- 查看是否开启自动提交
7. 索引
MysQL官方对索引的定义为: 索引(lndex)是帮助MySL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
7.1 索引的分类
-
主键索引(PRIMARY KEY )
-
唯一的标识,主键不可重复,只能有一个列作为主键
-
-
唯一索引(UNIQUE KEY)
-
避免重复的列出现,唯一索引可以重复,多个列都可以标识唯一索引
-
-
常规索引(KEY/INDEX)
-
默认的,index。key 关键字来设置
-
-
全文索引(FullText)
-
在特定的数据库引擎下才有,MyISAM(5.5版前MySQL的默认数据库引擎,不支持事务处理)
-
快速定位数据
-
-- 索引的使用 -- 1.在创建表的时候给字段增加索引 -- 2.创建完毕后,增加索引 -- 显示所有的索引信息 SHOW INDEX FROM student; -- 增加一个全文索引 (索引名)列名 ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`); -- EXPIAIN 分析sq1执行的状况 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('元宝');
8. 权限管理和备份
8.1 用户管理
用户表:mysql.user
本质:对这张表进行增删改查
-- 新建用户 CREATE USER yuanbao IDENTIFIED BY '123456'; -- 修改密码(修改当前用户密码) SET PASSWORD=PASSWORD('123456') -- 修改密码(修改指定用户密码) SET PASSWORD FOR yuanbao=PASSWORD('123456') -- 用户重命名 RENAME USER yuanbao TO yuanbao2 -- 用户授权 ALL PRIVILEGES 所有的权限,库,表 GRANT ALL PRIVILEGES ON *.* TO yuanbao2 -- 查询权限 SHOW GRANTS FOR yuanbao2 -- 查看指定用户权限 SHOW GRANTS FOR root@localhost -- 查看管理员权限 -- 撤销权限 REVOKE ALL PRIVILEGES ON *.* FROM yuanbao2
8.2 MySQL备份
备份方式:
-
直接拷贝物理文件
-
用可视化工具导出数据
-
命令行 mysqldump 导出
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物磁盘位置:/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物磁盘位置:/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql # 导入 # 登录的情况下,切换到指定的数据库 # source 备份文件 source d:/a.sql mysql -u用户名 -p密码 库名< 备份文件
9. 规范数据库设计
==当数据库比较复杂就需要设计==
-
第一范式(1NF)
-
1NF的定义为:符合1NF的关系中的每个属性都不可再分
-
可以理解为:指数据库中表的每一列都是不可分割的基本数据项,同一列的数据不能有多个值。
-
-
第二范式(2NF)
-
2NF在1NF的基础之上,消除了非主属性对于码的部分函数依赖
-
每张表只描述一件事情
-
-
第三范式(3NF)
-
3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖
-
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关
-
10. JDBC(重点)
10.1 数据库驱动
10.2 JDBC
没有什么是加一层解决不了的
java.sql
javax.sql
还需要导入一个数据库 mysql-connector-j-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,'zhangsan','123456','[email protected]','1980-12-04'), (2,'lisi','123456','[email protected]','1981-12-04'), (3,'wangwu','123456','[email protected]','1979-12-04')
-
创建一个普通项目
-
导入数据库驱动
-
编写测试代码
package com.yuanbao.lesson01; import java.sql.*; //第一个JDBC程序 public class JdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法!加载驱动 //2.用户信息和url String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username="root"; String password="123456"; //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(); } }
步骤总结:
-
加载驱动
-
连接数据库DriverManager
-
获取执行sql的对象 Statement
-
获得返回的结果集
-
释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver()); Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法!加载驱动 Connection connection = DriverManager.getConnection(url, username, password); // connection 代表数据库 // 数据库设置自动提交 // 事务提交 // 事务回滚 connection.rollback(); connection.commit(); connection.setAutoCommit();
URL
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; //mysql -- 3306 //jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3 //oracle -- 1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 PreparedStatement执行SQL的对象
String sql="SELECT * FROM users"; //编写SQL statement.executeQuery();//查询操作返回 ResultSet(结果集) statement.execute();//执行任何sql statement.executeUpdate();//更新、插入、删除,都用这个,返回一个受影响的行数
ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型
resu1tSet.getObject(); // 在不知道列类型的情况下使用 //如果知道列的类型就使用指定的类型 resu1tSet.getString(); resu1tSet.getInt(); resu1tSet.getFloat(); resu1tSet.getDate(); resu1tSet.getObject(); ...
遍历,指针
resu1tSet.beforeFirst(); // 移动到最前面 resu1tSet.afterLast(); // 移动到最后面 resu1tSet.next(); //移动到下一个数据 resu1tSet.previous();//移动到前一行 resu1tSet.absolute(row); //移动到指定行
释放资源
//6.释放连接 resultSet.close(); statement.close(); connection.close(); //耗资源!用完关掉!
10.4 statement对象
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = conn.createStatement(); String sql = "insert into user(....) values(....)"; int num = st.executeUpdate(sql); if(num>0){ System.out.printIn("插入成功!!!"); }
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement st = conn.createStatement(); String sql = "delete from user where id=1"; int num = st.executeUpdate(sql); if(num>0){ System.out.printIn("删除成功!!!"); }
CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement st = conn.createStatement(); String sql = "update user set name ='' where name =''"; int num = st.executeUpdate(sql); if(num>0){ System.out.printIn("修改成功!!!"); }
CRUD操作-read
使用executeQuery(String sql)方法完成数据查询操作,示例操作:
Statement st = conn.createStatement(); String sql = "select * from user where id=1"; int num = st.executeQuery(sql); while(rs.next()){ //根据获取列的数据类型,分别调用rs的相应方法映射到java对象中 }
代码实现
-
提取工具类
-
编写增删改的方法,
executeUpdate
package com.yuanbao.lesson02; import com.yuanbao.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 conn=null; Statement st=null; ResultSet rs=null; try { conn= JdbcUtils.getConnection();//获取数据库连接 st=conn.createStatement();//获得SQL的执行对象 String sql="INSERT users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" + "VALUES (4,'yuanbao','123456','[email protected]','1998-02-11')"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
package com.yuanbao.lesson02; import com.yuanbao.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 conn=null; Statement st=null; ResultSet rs=null; try { conn= JdbcUtils.getConnection();//获取数据库连接 st=conn.createStatement();//获得SQL的执行对象 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(conn,st,rs); } } }
package com.yuanbao.lesson02; import com.yuanbao.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 conn=null; Statement st=null; ResultSet rs=null; try { conn= JdbcUtils.getConnection();//获取数据库连接 st=conn.createStatement();//获得SQL的执行对象 String sql="update users set name ='dongdong' where name ='zhangsan'"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("修改成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
-
查询
executeQuery
package com.yuanbao.lesson02; import com.yuanbao.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();//获得SQL的执行对象 String sql="select * from users where id=1"; //SQL 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.yuanbao.lesson02; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //SQL注入 public class SQLInjection { public static void main(String[] args) { //login("dongdong","123456"); 正常登陆 login("'or '1=1","123456"); } public static void login(String username,String password){ Connection conn=null; Statement st=null; ResultSet rs=null; try { conn= JdbcUtils.getConnection(); st=conn.createStatement();//获得SQL的执行对象 //SELECT * FROM users WHERE `NAME`='dongdong' AND `PASSWORD`='123456' //SELECT * FROM users WHERE `NAME`=''or '1=1' AND `PASSWORD`=''or '1=1' String sql="SELECT * FROM users WHERE `NAME`='"+username+"' AND `PASSWORD`='"+password+"'"; //SQL rs = st.executeQuery(sql);//查询完毕会返回一个结果集 while(rs.next()){ System.out.println(rs.getString("NAME")); System.out.println(rs.getString("PASSWORD")); System.out.println("====================="); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
10.5 PrepareStatement对象
PrepareStatement 可以防止SQL 注入,效率更高!
1. 新增
package com.yuanbao.lesson03; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.*; import java.util.Date; public class TestInsert { public static void main(String[] args) { Connection conn=null; PreparedStatement pst=null; ResultSet rs=null; try { conn = JdbcUtils.getConnection(); //区别 //使用? 占位符代替参数 String sql="INSERT users(id,`NAME`,`PASSWORD`,`email`,`birthday`)VALUES (?,?,?,?,?)"; pst= conn.prepareStatement(sql); //预编译SQL:先写SQL,然后不执行 //手动给参数赋值 pst.setInt(1,4);//id pst.setString(2,"yuanbao");//name pst.setString(3,"123");//密码 pst.setString(4,"[email protected]");//邮箱 //注意:sql.Date 数据库用的 java.sql.Date // Util.Date Java用的 new Date().getTime() 获得时间戳 pst.setDate(5,new java.sql.Date(new Date().getTime())); //执行 int i = pst.executeUpdate(); if (i>0){ System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pst,rs); } } }
2. 删除
package com.yuanbao.lesson03; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.*; public class TestDelete { public static void main(String[] args) { Connection conn=null; PreparedStatement pst=null; ResultSet rs=null; try { conn = JdbcUtils.getConnection(); //区别 //使用? 占位符代替参数 String sql="delete from users where id=?"; pst= conn.prepareStatement(sql); //预编译SQL:先写SQL,然后不执行 //手动给参数赋值 pst.setInt(1,4); //执行 int i = pst.executeUpdate(); if (i>0){ System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pst,rs); } } }
3. 修改
package com.yuanbao.lesson03; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestUpdate { public static void main(String[] args) { Connection conn=null; PreparedStatement pst=null; ResultSet rs=null; try { conn = JdbcUtils.getConnection(); //区别 //使用? 占位符代替参数 String sql="update users set name =? where id =?"; pst= conn.prepareStatement(sql); //预编译SQL:先写SQL,然后不执行 //手动给参数赋值 pst.setString(1,"zhangsan"); pst.setInt(2,1); //执行 int i = pst.executeUpdate(); if (i>0){ System.out.println("修改成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pst,rs); } } }
4. 查询
package com.yuanbao.lesson03; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //区别 //使用? 占位符代替参数 String sql = "select * from users where id=?"; pst = conn.prepareStatement(sql); //预编译SQL:先写SQL,然后不执行 //手动给参数赋值 pst.setInt(1, 1); //执行 rs = pst.executeQuery(); while (rs.next()){ System.out.println(rs.getString("NAME")); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn, pst, rs); } } }
5. 防止SQL注入
package com.yuanbao.lesson03; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.*; public class SQLInjection { public static void main(String[] args) { //login("zhangsan","123456"); 正常登陆 login("''or 1=1","123456");//技巧 } public static void login(String username,String password){ Connection conn=null; PreparedStatement pst=null; ResultSet rs=null; try { conn= JdbcUtils.getConnection(); //PreparedStatement 防止SQL注入的本质:把传进来的参数当做字符 //假设其中存在转义字符,就直接忽略。 比如说 ' 会被直接转义 String sql="SELECT * FROM users WHERE `NAME`=? AND `PASSWORD`=?"; //SQL pst=conn.prepareStatement(sql);//预编译 pst.setString(1,username); pst.setString(2,password); rs = pst.executeQuery();//查询完毕会返回一个结果集 while(rs.next()){ System.out.println(rs.getString("NAME")); System.out.println(rs.getString("PASSWORD")); System.out.println("====================="); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,pst,rs); } } }
10.6 使用IDEA连接数据库
==连接成功后,可以选择数据库==
10.7 事务
==要么都成功,要么都失败==
ACID原则
事务原则:ACID原则 原子性,一致性,持久性 ,隔离性 (脏读,幻读......)
代码实现
-
开启事务
conn.setAutoCommit(false);
-
一组业务执行完毕,提交事务
-
可以在catch 语句中显示的定义 回滚语句,但默认失败就会回滚
package com.yuanbao.lesson04; import com.yuanbao.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestTransaction2 { 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 account set money=money-100 where name='A'"; st = conn.prepareStatement(sql1); st.executeUpdate(); int x=1/0;//报错 String sql2 = "update 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 最大连接数: 15 等待超时: 100ms
编写连接池,实现一个接口 DataSource
开源数据源实现 (拿来即用)
DBCP C3PO Druid: 阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了!
DBCP
需要用到的jar包
commons-dbcp2-2.9.0.jar
commons-pool2-2.11.1.jar
C3P0
需要用到的jar包
c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar
结论
无论使用什么数据源,本质都一样。DataSource接口不会变,方法就不会变
标签:--,employees,MySQL,查询,sql,id,SELECT From: https://www.cnblogs.com/Kodakku/p/17274055.html