mysql基础学习

最新推荐文章于 2024-03-11 11:25:20 发布

幸存者如你

最新推荐文章于 2024-03-11 11:25:20 发布

阅读量274

点赞数

分类专栏： mysql基础学习文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44881066/article/details/115317305

版权

mysql基础学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.数据库概述

1.数据库定义

数据库是一种存储并管理数据的软件系统

存储：持久化

管理：增删改查

DBMS：关系型数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。

DB：数据库（Database）

SQL：结构化查询语言，（Structure Query Language），专门用来操作/访问数据库的通用语言。

MySQL：其中的一款关系型数据库管理系统

2.两种数据库

关系型数据库

特点：

所存储的数据有一定的关系

将数据存储在磁盘上

重在持久化

非关系型数据库

特点：

以key-value或json的方式存储数据

将数据存储在内存中

重在性能，读取速度快

2.数据库数据类型

整型系列：xxxInt

int(M)，必须和unsigned zerofill一起使用才有意义

unsigned：无符号字符，将整形的范围变为从0开始，同时所对应的范围也会发生变化

zerofill：0填充，当设置了整形的宽度时，若为该字段所设置的值不够宽度的要求，则会在数据之前补0，以达到宽度的要求

浮点型系列：float,double 可以不设长度

double(M,D)：表示最长为M位，其中小数点后D位

例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。

定点型系列：decimal

decimal(M,D)：表示最长为M位，其中小数点后D位

字符串类型：char,varchar(M),text

char如果没有指定宽度，默认为1个字符

varchar(M)，必须指定宽度 变长赋值的真实长度决定

日期时间类型：year, date, datetime, timestamp

注意一下每一种日期时间的表示范围

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CJDYyLyp-1617067086724)(E:\JAVA1130\课件\第二阶段数据库\mysql\笔记\数据库\imgs\1560933691657.png)]

3.运算符

1、算术运算符

加：+
减：-
乘：*
除：/   div（只保留整数部分）
模：%   mod

2、比较运算符

大于：>
小于：<
大于等于：>=
小于等于：>=
等于：=   不能用于null判断
不等于：!=  或 <>
安全等于：<=>  可以用于null值判断   is null   <=> null

3、逻辑运算符（建议用单词，可读性来说）

逻辑与：&& 或 and
逻辑或：|| 或 or
逻辑非：! 或 not
逻辑异或：^ 或 xor
xor：只能满足其中的一个条件，不能同时满足，也不能都不满足，结果A∪B-A∩B

4、范围

区间范围：between x  and  y
	    not between x  and  y
集合范围：in (x,x,x)     where id=1 or id=2;
	    not  in(x,x,x)  批量删除
select * from t_emp where salary between x and y;

5、模糊查询（只针对字符串类型，日期类型）

like 'xxx'
如果想要表示0~n个字符，用%  'x%' 全部    '%x%'
如果想要表示确定的1个字符，用_   'x_';
select * from t_employee where ename like '_杰_';

特殊的null值处理
    
#（1）判断时
xx is null
xx is not null
xx <=> null

#(2)计算时
ifnull(xx,代替值)  当xx是null时，用代替值计算
se1ect ifnu11(字段可能为null,0) from t_emp1oyee;

2.sql语法

（1）SQL脚本中如何加注释

单行注释：#注释内容

单行注释：–空格注释内容 其中–后面的空格必须有

多行注释：/* 注释内容 */

（2）ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关，区分大小写）

（3）建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来

（4）数据库和表名、字段名等对象名中间不要包含空格

列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略

alter table a_user add age 年龄 int ；

3.sql分类

根据约束的特点，分为几种：

键约束：主键约束、外键约束、唯一键约束 都有索引
Not NULL约束：非空约束
Check约束：检查约束
Default约束：默认值约束
自增约束
索引是一个单独、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单（类似于新华字典的目录索引页）
事务只对DML有用
三范式：1、列不能拆分 2、唯一标识 3、关系引用主键

1.DDL

数据定义语言，定义库，表结构等，包括create,drop,alter等

show databases;-- 查看所有数据库
use 数据库名; -- 使用数据库
create databases 数据库 【charset 'gbk' 或者 'utf8'】; -- 创建数据库
drop database 数据库名; -- 删除数据库
show tables; -- 查看所有表；
show tables from 数据库名;-- 查看某数据库中的表
create table 【数据库.】表名(
字段1  数据类型 约束;
); -- 创建表
drop table 【数据库.】表名 --删除表
desc 【数据库.】表名 --查看某个表的结构   describe
alter table 【数据库.】表名 drop 字段名; --删除某一列字段、
alter table 【数据库.】表名 add 字段名 数据类型; --增加某一列字段、
alter table 【数据库名.]表名称 add  字段名 数据类型  first 或者
【after 另一个字段】;-- 增加的位置
alter table 【数据库名.]表名称 modify 字段名 新数据类型； --修改数据类型
alter table 【数据库名.]表名称 modify 字段名 数据类型  first 或者
【after 另一个字段】;   --修改字段位置
alter table 【数据库名.]表名称 change 字段名  新字段名 新数据类型 --修改字段
alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;
-- 查看约束 
show create table 表名;
-- 查看索引
show index table 表明;

1.主键约束 primary key

一个字段作为主键，该字段的值不能为null，不能重复

多个字段作为主键，每个作为主键的字段都可以重复，但是不能同时重复

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	....
);
create table 【数据名.】表名(
	....,
    primary key(复合主键字段列表)如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
);
--在建表后指定主键约束
alter  table 表名称 add primary key(主键字段列表);
alter table 表名称  drop primary key；-- 删除

2.unique key 唯一

唯一键约束列允许为null。索引
同一个表可以有多个唯一约束。
复合唯一字段都可以重复，但是不能同时重复

 create table 【数据名.】表名(
	字段名1 数据类型  unique key ,
	....
);
create table 【数据名.】表名(
	....,
    unique key(复合字段列表)
);
--在建表后指定约束
alter  table 表名称 add unique key(主键字段列表);
alter table 表名称  drop  index 唯一性约束名；-- 删除

3.外键约束 foreign key

表关系存在一对多时；

student(sid,sname,age,sex,cid)–clazz(cid,cname)

多对多时

引入中间表

sid cid

设置两个字段为复合主键

5个约束等级

Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录（级联修改）
Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action, 都是立即检查外键约束（不允许）
Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

create table 【数据名.】从表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】,
	....,
    【constraint 外键约束名】 foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级】
    );
    -- 建表后指定
    alter table 表名 add foreign key(从表主键字段) references 主表名（主键） 【on update xx】【on delete xx】
    ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;  -- 删除

4.非空约束 not null

可以不赋值默认为空字符串但是赋值null 出错

NOT NULL 非空约束，规定某个字段不能为空

5.默认值约束 default

设置默认值 sex char default ‘男’；

使用默认值要么不赋值要么使用default

create table 【数据名.】从表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【not null】【 default '默认值'】,
	....,
	  );
	  
--建表后设置
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL 【default 默认值】;
--删除
ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;

6.自增 auto_increment

主键int 才能

从1开始

默认从最大的值自增添加数据

每次都会增加1

2.DML

insert into 【数据库名.]表名称【(部分字段列表)】 values(值列表),(),(),(); -- 添加数据
update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】;
-- 修改数据  如果没有加where条件，表示修改所有行，这个字段的值
delete from 【数据库名.]表名称 【where 条件】;
-- 删除数据 如果没有where条件，表示删除整张表的数据;
truncate 【数据库名.]表名称;#删除整张表的数据，还可以使用这个语句，效率更高，但是它不能回滚。

用delete删除整张表和用truncate删除整张表的数据的区别？

（1）truncate速度快

（2）truncate无法回滚

truncate因为底层是把表drop掉，然后新建了一张空表。

delete因为底层是一行一行删数据。

简单查询数据(比较复杂)

select * from 【数据库名.]表名称; #查询整张表的所有数据

select 字段列表  from 【数据库名.]表名称;  #查询部分列表

select * from 【数据库名.]表名称 【where 条件】;

select 字段列表  from 【数据库名.]表名称 【where 条件】;
select distinct sex from teacher;

使用distinct可以对查询结果进行去重（冗余）

4.数据库导入导出

mysqldump -hlocalhost -P3306 -uroot -proot 数据库名 > 文件路径/文件名.sql; -- 导出

source 脚本；

5.函数

字符串函数

CONCAT(S1,S2,…,Sn) 连接S1,S2,…,Sn为一个字符串

LEFT(s,n) 返回字符串s最左边的n个字符

RIGHT(s,n) 返回字符串s最右边的n个字符

数学

ROUND(x,y) 返回参数x的四舍五入的有y位的小数的值

TRUNCATE(x,y) 返回数字x截断为y位小数的结果 truncate

日期函数

CURDATE() 或 CURRENT_DATE() current 返回当前日期

CURTIME() 或 CURRENT_TIME() 返回当前时间

NOW() SYSDATE() 返回当前系统日期时间

YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time) 返回具体的时间值

DATE_FORMAT(time,%Y%m%d %H%i%s)

date_format(time,%Y%m%d %T)

/*
字符串函数：
CONCAT(S1,S2,......,Sn):连接S1,S2,......,Sn为一个字符串
LEFT(s,n):返回字符串s最左边的n个字符
RIGHT(s,n):返回字符串s最右边的n个字符

*/
#将hello和world拼接
SELECT 'hello'+'world';-- 结果为0，在mysql中+只能实现加法运算
SELECT CONCAT('hello','world');
#获取每一个员工的姓氏
SELECT LEFT(ename,1) FROM t_employee;
#查询每个员工的姓名，真实薪资salary*(1+kpi)，同时对员工的名称保密，显示结果为杨**
SELECT CONCAT(LEFT(ename,1),'**'),salary*(1+IFNULL(kpi,0)) FROM t_employee;
#查询每个员工的姓名，电话，真实薪资salary*(1+kpi)，同时对员工的信息保密，名称显示结果为杨**，电话显示185****9977
SELECT CONCAT(LEFT(ename,1),'**'),CONCAT(LEFT(tel,3),'****',RIGHT(tel,4)),salary*(1+IFNULL(kpi,0)) FROM t_employee;

/*
数学函数
ROUND(x,y)：返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)：返回数字x截断为y位小数的结果
*/
#查询每个员工的真实薪资，四舍五入保留两位小数
SELECT CONCAT(LEFT(ename,1),'**'),CONCAT(LEFT(tel,3),'****',RIGHT(tel,4)),ROUND(salary*(1+IFNULL(kpi,0)),2) FROM t_employee;
#查询每个员工的真实薪资，截断保留两位小数
SELECT CONCAT(LEFT(ename,1),'**'),CONCAT(LEFT(tel,3),'****',RIGHT(tel,4)),TRUNCATE(salary*(1+IFNULL(kpi,0)),2) FROM t_employee;
#查询每个员工的每天的薪资
SELECT ename,ROUND(salary/30,2) FROM t_employee;
SELECT ename,TRUNCATE(salary/30,2) FROM t_employee;

/*
日期函数
CURDATE()或 CURRENT_DATE()：返回当前日期，年月日
CURTIME()或 CURRENT_TIME()：返回当前时间，时分秒
NOW()/ SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()：返回当前系统日期时间，年月日+时分秒
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)：分别获取某个时间的年月日时分秒
*/
#查询当前日期
SELECT CURDATE(),CURRENT_DATE();
#查询当前时间
SELECT CURTIME(),CURRENT_TIME();
#查询当前的日期+时间
SELECT NOW(),SYSDATE();
#查询当前时间的年月日时分秒
SELECT YEAR(NOW()),MONTH(NOW());
#查询每个员工的入职时间
SELECT ename,YEAR(NOW())-YEAR(hiredate) 入职时间 FROM t_employee;
#查询今天生日的员工
SELECT ename FROM t_employee WHERE MONTH(NOW())=MONTH(birthday) AND DAY(NOW())=DAY(birthday);

流程函数

IF(value,t ,f) 如果value是真，返回t，否则返回f

SELECT  eid,IF(salary>2000,'真','假') FROM t_employee;
SELECT IF(20>222,'1','2');

IFNULL(value1, value2) 连着的如果value1不为空，返回value1，否则返回value2

SELECT IFNULL(NULL,'1');
SELECT IFNULL('2','1');
SELECT eid,IFNULL(kpi,0) FROM t_employee;
-- case  when  then [else] end
SELECT  eid, 
    CASE
         WHEN kpi IS NULL  THEN '菜鸟'
         WHEN kpi <0.5 AND kpi >0  THEN '良好'
         WHEN kpi >0.5  THEN '大神'
         ELSE '其他'
    END 憨憨
 FROM  t_employee;
 --  switch case
 SELECT last_name, job_id, salary,
     CASE job_id 
         WHEN 'IT_PROG'  THEN  1.10*salary
         WHEN 'ST_CLERK' THEN  1.15*salary
         WHEN 'SA_REP'   THEN  1.20*salary
         ELSE    salary 
     END   "实发工资"
FROM   employees;

其他函数

database() 返回当前数据库名

version() 返回当前数据库版本

user() 返回当前登录用户名

password(str) 返回字符串str的加密版本，41位长的字符串

md5(str) 返回字符串str的md5值，也是一种加密方式

分组函数（聚合函数，多行函数）
avg():获取平均值
sum():获取总和
max():获取最大值
min():获取最小值
count():获取总记录数，总条数

若通过某个字段查询总记录数，只能获取该字段不为null的记录数

#查询所有员工的平均薪资
SELECT AVG(salary) FROM t_employee;
#查询该公司一个月所需要支付的总薪资
SELECT SUM(salary) FROM t_employee;
#查询该公司最高薪资
SELECT MAX(salary) FROM t_employee;
#查询薪资最高的员工信息
SELECT * FROM t_employee WHERE salary = (SELECT MAX(salary) FROM t_employee);
#查询薪资大于平均薪资的员工信息
SELECT * FROM t_employee WHERE salary > (SELECT AVG(salary) FROM t_employee);
#查询员工表的总记录数
SELECT COUNT(*) FROM t_employee;-- 30
SELECT COUNT(2) FROM t_employee;-- 30
SELECT COUNT(eid) FROM t_employee;-- 30
SELECT COUNT(kpi) FROM t_employee;-- 若通过某个字段查询总记录数，只能获取该字段不为null的记录数
SELECT SUM(1)  FROM t_employee;

6.关联查询

1、内连接：实现 A∩B inner join

左外连接 left join

3.右外连接 right join

4.用union代替全外连接 A∪B 必须是左外连接并右外连接

还有自连接自己连接自己

/*
关联查询
1、A∩B:有员工的部门和有部门的员工
2、A
3、B
4、A-A∩B
5、B-A∩B
6、A∪B
7、A∪B-A∩ B或（A-A∩B）∪（B-A∩B）
假设A为t_employee，B为t_department
*/
#1、查询员工表和部门表中所有的数据，即有员工的部门和有部门的员工，A∩B
SELECT * FROM t_employee,t_department WHERE t_employee.did = t_department.did;
SELECT * FROM t_employee INNER JOIN t_department ON t_employee.did = t_department.did;
#2、查询所有的员工以及所在的部门信息，不管员工有没有部门，A
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did;
SELECT * FROM t_department RIGHT JOIN t_employee ON t_employee.did = t_department.did;
#3、查询所有的部门以及所拥有的员工信息，不管部门有没有员工，B
SELECT * FROM t_department LEFT JOIN t_employee ON t_employee.did = t_department.did;
SELECT * FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did;
#4、查询没有部门的员工，A-A∩B
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did WHERE t_employee.did IS NULL;
#5、查询没有员工的部门，B-A∩B
SELECT * FROM t_department LEFT JOIN t_employee ON t_employee.did = t_department.did WHERE t_employee.did IS NULL;
#6、查询所有的员工和部门信息，A∪B --必须左外连接  并 右外连接
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did
UNION
SELECT * FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did;
#7、查询没有部门的员工和没有员工的部门，A∪B-A∩ B或（A-A∩B）∪（B-A∩B）
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did WHERE t_employee.did IS NULL
UNION
SELECT * FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did WHERE t_employee.did IS NULL;
#8、查询所有的员工信息以及对应的领导信息
/*
city(id,pid,cityname)
1	0	河南省
2	0	河北
3	0	山东
4	1	郑州
5	1	新乡
*/

7.7大子句顺序

/*
select的七大子句：
1、from：从那张表中查询
2、on：用在关联查询中，去除笛卡尔积
3、where：将表中的字段作为筛选条件
4、group by：按照某个字段进行分组，要和分组函数结合使用
5、having：在查询的结果中再次进行筛选，只有出现在select查询列表中的信息才可以作为筛选条件，一般使用分组函数为条件
6、order by：按照某个字段进行排序，默认按照升序，即ASC表示升序；DESC表示降序
7、limit：分页，由每页显示的条数pageSize和当前的页码pageNo决定
limit index,pageSize
pageSize：每页显示的条数
pageNo：当前的页码
index：当前页的起始索引,index=(pageNo-1)*pageSize
count：总记录数
totalPage：总页数
if(count % pageSize == 0){
	totalPage = count / pageSize;
}else{
	totalPage = count / pageSize + 1;
}

*/
#测试group by，当不使用分组函数时没有意义

#查询每个部门的平均薪资，按照did进行分组，获取每一组的平均薪资
SELECT did,AVG(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did;
#查询每个部门的最高薪资
SELECT did,MAX(salary) FROM t_employee WHERE did IS NOT NULL GROUP BY did;
#按照性别查询平均薪资
SELECT sex,AVG(salary) FROM t_employee GROUP BY sex;
#查询每个部门中男女的最高薪资
SELECT did,sex,MAX(salary) FROM t_employee GROUP BY sex,did;
#查询每个部门平均薪资大于15000的  在查询结果中使用having 操作
SELECT did,AVG(salary) FROM t_employee GROUP BY did HAVING AVG(salary)>15000;
#按照薪资大小将所有员工信息输出  desc asc
SELECT * FROM t_employee ORDER BY salary DESC; 
#对员工信息进行分页
SELECT * FROM t_employee LIMIT 3,3;

8.子查询

1.where 条件是子查询

①子查询是单值结果，那么可以对其使用（=，>等比较运算符）

②子查询是多值结果，那么可对其使用（

【not】in(子查询结果)

all集合所有元素都符合

any 最边的一个值就行（小于最大值，大于最小值）

）

2、from型

必须给子查询取别名，即临时表名，表的别名不要加“”和空格。

临时表的字段就是子查询的字段

3、exists型

*/
#查询薪资最高的员工
SELECT * FROM  t_employee WHERE salary =(SELECT MAX(salary) FROM t_employee); 
#查询大于每个部门平均薪资的员工信息
SELECT * FROM t_employee WHERE salary> ANY(SELECT AVG(salary) FROM t_employee GROUP BY did);  
#查询每个部门的信息以及部门的平均薪资
SELECT  t_department.*,avgSalary.avgs 平均薪资 FROM t_department 
INNER JOIN 
(SELECT did,AVG(salary) avgs FROM t_employee GROUP BY did)  avgSalary ON t_department.did=avgSalary.did;
#查询有员工的部门
SELECT * FROM t_department WHERE
EXISTS(SELECT did FROM t_employee WHERE t_employee.did=t_department.did);

SELECT * FROM t_department WHERE did
IN(SELECT DISTINCT did FROM t_employee WHERE did IS NOT NULL);//会导致数据库引擎放弃索引进行全表扫描

#查看当前登录的用户
SELECT USER();
/*

mysql中的用户有两个部分组成
root代表登录名
192.168.1.12表示支持哪个ip访问mysql
root@192.168.1.12：表示登录名root，只允许ip为192.168.1.12的机器访问mysql
root@localhost：表示登录名root，只允许本机访问mysql
root@%：表示登录名root，允许所有的ip访问mysql
*/
#创建用户
CREATE USER 'root'@'%' IDENTIFIED BY '123456'; identified
#在本机访问指定ip的mysql
-- mysql -h192.168.11.71 -P3306 -uroot -p123456
#查看某个用户的权限
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS FOR 'root'@'%';
#分配权限
GRANT DROP,SELECT ON *.* TO 'root'@'%';  #全局
GRANT 权限列表 ON 数据库名.* TO '用户名'@'主机IP地址'; #某个库
GRANT 权限列表 ON 数据库名.表格 TO '用户名'@'主机IP地址'; #某个库的某个表
GRANT 权限列表 ON 表名.* TO '用户名'@'主机IP地址';  #某个表的字段
#收回权限
REVOKE DROP,SELECT ON *.* FROM 'root'@'%';  #全局
REVOKE 权限列表 ON 数据库名.* FROM '用户名'@'主机IP地址'; #某个库
REVOKE 权限列表 ON 数据库名.表格 FROM '用户名'@'主机IP地址'; #某个库的某个表
REVOKE 权限列表 ON 表名.* FROM '用户名'@'主机IP地址';  #某个表的字段

9.事务

1、事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

事务的ACID属性

A 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

C一致性事务必须是数据库从一个一致性状态变换到另一个一致性变换。

I隔离性一个事务的执行不能别其他事务干扰，即一个事务的操作及使用数据对并发的其他事务是隔离的，并发执行的各个事务之前互不干扰。

D 持久性 一但事务被提交，它对数据库的操作是永久性的。

2.开启和结束事务

1、mysql默认是自动提交，执行一句就提交一句。

即默认情况下，每一条sql都是一个独立的sql。

2、我想要手动提交事务：

连接connction

（1）set autocommit=false; 全局事务

接下来所有语句都必须手动提交，否则就不会永久生效

（2）start transaction; 局部事务

commit; 或 rollback; 或发生异常;

事务的回滚只对DML语句有效，对于DDL语句无效。

操作事务的方式
1、通过set autocommit=false将事务的自动提交关闭
在此之后所有的sql都必须手动提交或回滚
若不提交或回滚，则默认回滚
在此情况下，从set autocommit=false到下一个commit或rollback之间的操作为一个事务
或从上一个commit或rollback到下一个commit或rollback之间的操作为一个事务
但是set autocommit=false只在当前的一次连接中有效
并且在当前的连接中只要设置了set autocommit=false，之后所有的操作都必须手动提交或回滚
2、START transaction也可以开启一个事务
在START transaction和下一个commit或rollback之间为一个事务
但是不影响之后的sql的自动提交

事务的隔离级别：在并发的情况下，事务与事务之间的隔离程度（影响程度）
事务的隔离级别有四种：
read-uncommitted：允许A事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、幻读问题
read-committed：只允许A事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现不可重复读、幻读问题
repeatable-read：确保事务可以多次从一个字段中读取相同的值
在这个事务持续期间，禁止其他事务对这个字段进行更新  对操作的数据加锁 其他不加锁
可以避免脏读和不可重复读。但是幻读问题仍然存在
注意：mysql中使用了MVCC多版本控制技术，在这个级别也可以避免幻读。
serializable：确保事务可以从一个表中读取相同的行，相同的记录。在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作
所有并发问题都可以避免，但性能十分低下。
悲观锁 不可读  乐观锁 可读
select @@global.transaction_isolation; 
set transaction_isolation='';

2.JDBC

独立于任何数据库管理系统的api

/*使用JDBC
1.注册驱动  使用数据库提供的jar包。
2.把驱动加载到内存中
Class.forName("com.mysql.jdbc.Driver");
3.建立数据库连接
  url：使用统一资源定位符连接mysql
  统一资源定位符：协议://ip:port/资源
  连接mysql的统一资源定位符-->jdbc:mysql://localhost:3306/数据库名
  访问服务器中资源的统一资源定位符-->http://localhost:8080/资源
  username：mysql的登录名
  password：mysql的登录密码
  */
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");

4.创建预编译
 1.statement

 Statement statement = connection.createStatement();
//使用 executeUpdate执行增删改
//使用executeQuery 查
statement.executeUpdate("insert into t_employee values(null,'name','adas')");
 Statement预编译对象的缺点：
     * 1、拼接字符串非常麻烦
     * 2、会造成sql注入：可以通过一些特殊的值使sql的条件成立
     * select * from t_user where username = '"+username+"' and password = '"+password+"';
     * username = a' or '1'='1
     * password = b' or '2' = '2
     * 3、不能处理blob类型的字段

 2.preparedStatement
  优点：
     * 1、通过占位符为某个字段进行赋值，并且在为字符串类型的字段赋值时，可以自动加单引号
      ps.setInt(int paramCount, int paramValue);
      ps.setString(int paramCount, String paramValue);
      ps.setObject(int paramCount, Object paramValue);//建议使用这种方式
     * 2、防止sql注入
     * 3、能够处理blob类型的字段
PreparedStatement ps = connection.prepareStatement("insert into t_employee values(?,?,?,?)");
  /*ps.setString(1,username);
    ps.setString(2,password);
    ps.setInt(3,age);
    ps.setString(4,sex);*/
ps.setObject(1,username);
ps.setObject(2,password);
ps.setObject(3,age);
ps.setObject(4,sex);  //前面为第几个占位符，后面为设置的值value
ps.executeUpdate();

//查询功能的返回值为ResultSet，可以通过rs.next()判断是否由下一个数据，并将指针指向下一个数据
   rs.getXxx(int columnCount):通过列数获取数据
   rs.getXxx(String columnName):通过列名（字段名）获取数据
   rs.getObject()

//最后关闭流
ps.close();

//模糊查询
String sql = "select * from t_user where uname like concat('%',?,'%')";
String sql = "select * from t_user where uname like '%"+?+"%'" ;
//批量删除
String strs = "1,2,3";
String sql = "delete  from t_user where uid in ("+strs+")"; 
int[] ins = {1,2,3};
String sql = "delete  from t_user where 1!=1;
    //循环拼接  1!=1 or   1 = 1 and

1.获取自增值

* JDBC在添加时可以获取自动增长的id
* JDBC默认不允许获取自增的id
* 只有在prepareStatement(String sql, int i)中
* 只有将i设置Statement.RETURN_GENERATED_KEYS，即1才可以获取

/*（1）PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

 * （2）添加sql执行完成后,通过PreparedStatement的对象调用getGeneratedKeys()方法来获取自增长键值，遍历结果集
 * ResultSet rs = pst.getGeneratedKeys();*/

2.批处理

/*（1）在url中要加一个参数
 *     rewriteBatchedStatements=true
 *     那么我们的url就变成了  jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
 *     这里的?，表示?后面是客户端给服务器端传的参数，多个参数直接使用&分割
 * （2）调用方法不同
 * pst.addBatch();
 * int[] all = pst.executeBatch();
  */


 Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC&&rewriteBatchedStatements=true","root","root");
            ps = connection.prepareStatement("insert into t_user values (null,?,'123456')");
            for (int i = 0; i < 10000; i++) {
                ps.setString(1,i+"");
                ps.addBatch();
            }
            ps.executeBatch();

3.开启事务

/* Connection的对象.setAutoCommit(false)
 * (2)成功：
 * Connection的对象.commit();
 * 失败：都是使用出现异常判断   可以自定义异常  如库存不足
 * Connection的对象.rollback();
 * 补充说明：
 * 为了大家养成要的习惯，在关闭Connection的对象之前，把连接对象设置回自动提交
 * (3)Connection的对象.setAutoCommit(true)
 */


Class.forName("com.mysql.cj.jdbc.Driver");
         connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC","root","root");
         connection.setAutoCommit(false);//设置为手动
         ps = connection.prepareStatement("delete from t_user where uid = 5");
         ps.executeUpdate();
         ps = connection.prepareStatement("delete from t_users where uid = 8");
         ps.executeUpdate();
        System.out.println("成功");
        connection.commit();//提交事务
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println("失败");
        try {
            connection.rollback();//回滚事务
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }


/* JDBC中处理事务的过程：
     * try{
     *      connection.setAutoCommit(false);//关闭自动提交事务
     *      //实现功能的整个过程
     *      //成功，需要提交事务
     *      connection.commit();
     * } catch(){
     *      //失败，需要回滚事务
     *      connection.rollback();
     * }
     */

4.使用德鲁伊连接池

druiddatasource

/**
     * druid：德鲁伊数据源
     * 数据库连接池
     * 需要设置
     * driverClassName:驱动类名称
     * url:连接地址
     * username:登录mysql的用户名
     * password:登录mysql的密码
     * initialSize:数据库连接池初始化连接的个数
     * maxActive:数据库连接池所支持的最大连接数量
     * maxWait:当连接全部分配完之后，再次获取连接所需要等待的最大时间
     * 通过druid获取的Connection对象，关闭的close()并不是将连接断开释放掉，而是将连接返还给druid
     */
  //获取德鲁伊连接池
             dds = new DruidDataSource();
             dds.setDriverClassName("com.mysql.cj.jdbc.Driver");
             dds.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=UTC");
             dds.setUsername("root");
             dds.setPassword("root");
             dds.setInitialSize(5);
             dds.setMaxActive(16);
             dds.setMaxWait(100000);

   dds.getConnection();

使用properties 读取配置文件

/**
 * Properties继承了Hashtable，因此其中也是键值对的结构
 * Properties可以操作后缀为properties的文件
 * 后缀为properties的文件中存储的数据也必须为键值对结构
 * 但是文件中只能存储文本，Properties在操作后缀为properties的文件时，只能操作字符串类型的键值对
 * Properties常用方法：
 * void setProperty(String key, String value)
 * String getProperty(String key);
 * load():读取properties文件中的数据
 * store()：向properties文件中输出数据
 */

public class JDBCUtils {
       //数据库连接池
    private static DataSource dataSource;
    static {
        try {
           Properties   properties = new Properties();//使用properties读取配置文件
            //InputStream inputStream = new FileInputStream("文件地址");//文件输入节点流
            InputStream inputStream =JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");//使用当前类的类对象获取类加载器读取文件
            properties.load(inputStream);//读入文件
           dataSource = DruidDataSourceFactory.createDataSource(properties);//使用德鲁伊数据库连接池工厂创建连接池
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取连接
        public static Connection getConnection(){
            Connection connection = null;
            try {
                connection = dataSource.getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }
        //关闭连接
    public static void closeConnection(Connection connection){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

使用dbutils工具类

 //类中 私有化变量 queryRunner 
private QueryRunner queryRunner = new QueryRunner();

queryRunner 的query方法实现查询

 /**
     * 查询一条数据转换为实体类对象
     * @param tclass：将数据库中的数据转换为实体类的Class对象
     * @param sql：要执行的sql语句
     * @param params：按照顺序为sql中占位符所赋的值
     * @return
     */


//查询所有信息 使用list返回
//Class<T> tClass  引入泛型的类对象
public  List<T>  selectAll(Class<T> tClass,String sql,String... params){
    Connection connection = null;
    List<T> list = null;
    try {
    connection = JDBCUtils.getConnection();
       // new BeanListHandler<>(tClass)  使用beanHandler 将数据库中的数据转换为实体类的Class对象
     list = queryRunner.query(connection, sql, new BeanListHandler<>(tClass), params);
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        JDBCUtils.closeConnection(connection);
    }
   return list;

}

//查询单个T实体类的信息
public  T selectById(Class<T> tClass,String sql,String... params){
    T t = null;
    Connection connection = null ;
    try {
        connection = JDBCUtils.getConnection();
        queryRunner.query(connection,sql,new BeanHandler<>(tClass),params);
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        JDBCUtils.closeConnection(connection);
    }
    return t;
}

//未分组情况下获取分组函数的结果，只有单条函数

public Object getSingleData(String sql, Object... params){
        Connection connection = null;
        Object o = null;
        try {
            connection = JDBCUtils.getConnection();
            o = queryRunner.query(connection, sql, new ScalarHandler<>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(connection);
        }
        return o;
    }

使用queryRunner 的update方法实现插入删除更新操作

public int update(String sql,Object... params){
    Connection connection = null;
    int t = 0;
    try {
      //使用queryRunner 的 update方法实现更新插入删除操作
     connection = JDBCUtils.getConnection();
         t = queryRunner.update(connection,sql, params);
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        JDBCUtils.closeConnection(connection);
    }
return t;
}



//ResultSetMetaData resultSetMetaData = resultSet.getMetaData();返回对象的列的数量，类型和属性

  public static void main(String[] args) throws Exception {

        Class<User> clazz = null;
        User user = clazz.newInstance();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();//获得总列数
        while (rs.next()){
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);//根据列数获取列名，即字段名，即属性名
                Field filed = clazz.getDeclaredField(columnName);
                filed.setAccessible(true);
                filed.set(user, rs.getObject(i));
            }
        }

    }

幸存者如你

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql基础学习

1.数据库概述1.数据库定义数据库是一种存储并管理数据的软件系统存储：持久化管理：增删改查DBMS：关系型数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。DB：数据库（Database）SQL：结构化查询语言，（Structure Query Language），专门用来操作/访问数据库的通用语言。MySQL：其中的一款关系型数据库管理系统2.两种数据库关系型数据库特点：所存储的数据有一定的关系
复制链接

扫一扫