首页 > 数据库 >MySQL

MySQL

时间：2023-03-30 19:22:30浏览次数：55  
标签：-- employees MySQL 查询 sql id SELECT

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 早些年使用的
*/
 MYISAMINNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约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,'小亮','男');

注意事项:

  1. 字段和字段之间使用 英文逗号 隔开

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

  3. 可以同时插入多条数据，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')

  1. 创建一个普通项目

  2. 导入数据库驱动

  1. 编写测试代码

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();
    }
}

步骤总结：

  1. 加载驱动

  2. 连接数据库DriverManager

  3. 获取执行sql的对象 Statement

  4. 获得返回的结果集

  5. 释放连接

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对象中
}

 

代码实现

  1. 提取工具类

  2. 编写增删改的方法，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);

        }
    }
}

    1. 查询 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原则 原子性，一致性，持久性 ，隔离性 （脏读，幻读......）

代码实现

  1. 开启事务

    conn.setAutoCommit(false);

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

  3. 可以在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

相关文章

  • Mysql 事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制、
    原子性(Atomicity)当前事务的操作要么同时成功，要么同时失败。原子性由undolog日志来实现。一致性(Consistency):使用事务的最终目的，由其它3个特性以及业务代码正确逻辑来实现。隔离性(lsolation):在事务并发执行时，他们内部的操作不能互相干扰，隔离性由MySQL的各种锁以及MVC......
  • 实战SQL优化（以MySQL深分页为例）
    1准备表结构CREATETABLE`student`(`id`intNOTNULLAUTO_INCREMENT,`user_no`varchar(50)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ciNULLDEFAULTNULL,`user_name`varchar(50)CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ciNULLDEFAULTNULL......
  • MySQL常用命令
    常用命令：https://blog.csdn.net/qq_38328378/article/details/80858073最详细的：https://blog.csdn.net/qq_34115899/article/details/81190461......
  • MySQL数据库怎么创建表？MySQL数据库基础知识
    MySQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，目前属于Oracle旗下产品。MySQL最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(RelationalDatabaseManagementSystem，关系数据库管理系统)应用软件之一。MySQL是一种关联数据库管理系统，关联数据库......
  • 22、MySQL主从复制详解及配置
    主从复制的介绍MySQL主从复制是指将一个MySQL服务器的数据复制到其他MySQL服务器上的过程。在主从复制中，一个MySQL服务器（称为“主服务器”或“主节点”）充当源，另一个或多个MySQL服务器（称为“从服务器”或“从节点”）充当目标。主服务器将更新和更改记录到二进制日志（binaryl......
  • MySQL、Redis和Elasticsearch比较
    MySQL是一种关系型数据库管理系统，它被广泛用于存储结构化数据。拥有极高的可靠性和安全性，支持ACID事务，并具有良好的扩展性，可以适应高并发访问的场景。Redis是一种内存......
  • MySql基本的简单sql语句
    SQL语句分类必须记住删除语句DELETEFROM表名WHERE条件插入语句简单的插入语句INSERTINTO表名(字段列表)VALUES(值列表)查询出的数据插入到已存在的表中查询出的数......
  • MySQL导入数据库1118错误解决方案[ERR] 1118 - Row size too large (＞ 8126). Changing
    MySQL导入数据库1118错误解决方案[ERR]1118-Rowsizetoolarge(＞8126).ChangingsomecolumnstoTEXTorBLOB编辑sql文件，在开头设置一下innodb_strict_mode为0SE......
  • 拒绝“爆雷”！GaussDB(for MySQL)新上线了这个功能
    摘要：智能把控大数据量查询，防患系统奔溃于未然。本文分享自华为云社区《拒绝“爆雷”！GaussDB(forMySQL)新上线了这个功能》，作者：GaussDB数据库。什么是最大读取行一直以来，大......
  • MySQL8给已有表新增自增列赋初始值的问题
    错误1：[22001][1138]Datatruncation:InvaliduseofNULLvalue原因：如果你xxx表已有数据，你是无法新增自增列的，需要中转一下。因为自增列需要是key。解决：第一步，给xxx......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99