MySQL

[mysqld]
basedir=F:\java\MySQL  #基本目录
datadir=F:\java\MySQL\data\  #数据目录
port=3306  #端口
skip-grant-tables   # 跳过密码验证

create database [if not exists] 数据库名;
-- 如果表名或者字段名是一个特殊的符号，就要带``

drop database [if exists] 数据库名; 

use 数据库名;

show databases --查看所有的数据库

 -- char(1):
 --字符串类型，小括号中指定的是字符数，固定字符长度，不足指定长度，自动使用空格，尾部补空格(查出来的时候就自动去掉空格)，会额外占用空间，长度范围：1~255
-- varchar(10):
--字符串类型，小括号中指定的是字符数，可变装都字符串，可以是存储小于指定长度的字符串，不可以超出长度（不会自动扩充），长度范围：1~65535

create table [if not exists] `表名`(
   '字段名1' 列类型 [属性][索引][注释],
   '字段名2' 列类型 [属性][索引][注释],
  #...
   '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];


-- 快速建表,直接将查询的数据建成一张表
-- crate table table_name (select_SQL)
create table employees_temp11(select * from employees where department_id in(50,60));

-- auto_increment 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加，英文的逗号，最后一个不用加
-- primary key 主键，一般一个表只有一个唯一的主键

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email

-- 创建表之前, 一定要先选择数据库
USE school;

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;

-- 查看创建数据库的语句
SHOW CREATE DATABASE school;

-- 查看创建表的语句
SHOW CREATE TABLE student;

-- 显示表结构
DESC student;  

-- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

charset=utf8
--不设置的话，回是mysql默认的字符编码（不支持中文）

-- 修改表名 :
--ALTER TABLE 旧表名 RENAME AS 新表名；
ALTER TABLE teacher RENAME AS teacher1;

-- 添加字段 :
--ALTER TABLE 表名 ADD 字段名 列属性[属性]；
ALTER TABLE teacher1 ADD age INT(12);

-- 修改字段 : 
--ALTER TABLE 表名 MODIFY 字段名 列类型[属性]；
ALTER TABLE teacher1 MODIFY age VARCHAR(12);	 -- 修改约束（注意，如果修改字段类型，一定要兼容之前的类型）
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型 [属性]
ALTER TABLE teacher1 CHANGE age age1 INT(12);    -- 字段重命名

-- 删除字段 : 
--`ALTER TABLE 表名 DROP 字段名；`
ALTER TABLE teacher1 DROP age1;

-- 删除表（如果表存在再删除）:
--DROP TABLE [IF EXISTS] 表名；
DROP TABLE IF EXISTS teacher1;
--所有的创建和删除操作尽量加上判断，以免报错

-- 年级表 (id\年级名称)
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
-- 1、定义外键
	-- KEY 外键名 (字段名)
-- 2、给这给外键添加约束(执行索引)
	-- constraint 外键名 foreign key (被引用表的字段名) references 被引用表 (被引用字段)
-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student2` (
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
    `gradeid` INT(10) DEFAULT NULL COMMENT '年级',
    `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
    `borndate` DATETIME DEFAULT NULL COMMENT '生日',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
   -- KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- ALTER TABLE 表
--add constraint 约束名(外键名) foreign key (作为外键的列) references 被引用表 (被引用字段);
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

-- 插入语句（添加）
-- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

INSERT INTO `student`(`name`) VALUES ('张三'); --插入单个字段值

INSERT INTO `student`(`name`,`address`,`sex`) VALUES ('张三','admin','男'); --插入多个字段值

INSERT INTO `student`(`name`,`address`,`sex`) 
VALUES ('李四','pppppp','女'),('王五','tttttt','男'); -- 插入多条信息


-- 将同一个数据库中的数据 一个表中的数据 导入到另外一个表中
-- 将grades_temp表中的年级数据，迁移或者备份到grades_his
-- 表结构一样
insert into grade_his select * from grade_temp;
-- 表结构不一样，指定字段名
insert into grade_his(grade_id,grade_name)  select grade_id,grade_name from grade_temp;

-- 不同数据库转储数据
-- 将数据插入到指定数据库中
-- 插入到test数据库的grade_his表(grade_id,grade_name)字段中
insert into test.grade_his(grade_id,grade_name)  select grade_id,grade_name from grade_temp;

-- UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];

UPDATE `student` SET `name`='subei',`birthday`=CURRENT_DATE WHERE id = 5;

-- DELETE FROM 表名 [WHERE condition];

-- 删除表数据(不带where条件的delete)
DELETE FROM `student`; -- 删除所有的数据

--清空 student表
--truncate table 表名
truncate table `student`;
-- 或 truncate 表名
truncate `student`;

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

-- 语法： select 字段... from 表
-- 查询全部的学生  select 字段 from 表
select * from student;

-- 查询指定字段
select `studentno`,`studentname` from student;

-- 别名，给结果其一个名字   as  可以给字段起别名，也可以给表起别名
select `studentno` as '学号',`studentname` as '学生姓名' 
from student as s

-- 函数 concat(a,b)
select concat('姓名：',studentname) as '新名字' from student;

select distinct `studentno` from result; -- 去重

-- select 表达式 from 表
select version() -- 查询系统版本（函数）
select 100*3-1 '计算结果' -- 用来计算（表达式）
select @@auto_increment_increment  -- 自增步长（变量）

-- 学员考试 +1 分查看
select `studentno`,`studentresult`+1 as '提分后' from result;

-- 用表达式
-- 查询员工表中，年薪大于9W的所有员工信息
select *
from employees
where Salary*12 > 90000;

-- 尽量不要在列上进行计算，因为如果列上有索引，会影响索引失效，导致查询速度降低
select *
from employees
where Salary > 90000/12;

-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _（代表一个字符）
select `studentno`,`studentname` from student
where studentname like '刘%';

-- 查询姓刘的同学，名字后面只有一个字
select `studentno`,`studentname` from `student`
where studentname like '刘_';


-- 查询名字中间有奋字的同学 %嘉%
select `studentno`,`studentname` from `student`
where studentname like '%奋%';

-- ============ in ================
-- 查询 1001，1002，1003号学员
select `studentno`,`studentname` from `student`
where studentno in (1001,1002,1003,1004);

-- 查询在北京的学生
select `studentno`,`studentname` from `student`
where address in ('安徽','河南洛阳');

-- ======= null not null ======
-- 查询地址为空的学生 null ''
select `studentno`,`studentname` from `student`
where address = '' or address is null;

-- 查询有出身日期的学生  不为空
select `studentno`,`studentname` from `student`
where borndate is not null;

-- 查询没有出生日期的同学  为空
select `studentno`,`studentname` from `student`
where borndate is null;

在大表中使用模糊查询，如果当前查询列有索引，要避免使用左模糊查询，会导致索引失败，影响查询速度;

-- inner join 
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno


-- right join
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on s.studentno = r.studentno


-- left join
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno

-- =========p20 联表查询 join============
-- 查询参加了考试的同学(学号，姓名，科目编号，分数)
select * from student
select * from result

/*
 思路
 1.分许需求，分析查询的字段来自哪里，(连接查询)
 2. 确定使用娜总联接查询？ 7 种
 确定交叉点(这两个表种那个数据是相同的)
 判断条件： 学生表种的 studentno = 成绩表 studentno
*/

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

-- inner join 
-- 等值连接
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno


-- right join
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on s.studentno = r.studentno


-- left join
-- (查询了所有同学，不考试的也会查出来)
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno


-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student s
left join result r
on s.studentno = r.studentno
where studentresult is null;


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

select s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno = r.studentno
inner join subject sub
on r.subjectno = sub.subjectno

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表，再慢慢增加

-- from a left join b  以左边的a为基准
-- from a right join b  以右边的b为基准

-- 查询父子信息 把一张表看为两张一摸一样的表
select a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid` = b.`pid`;

-- 排序： 升序 asc  , 降序 desc

-- order by 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩升序 排序
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult asc;

-- 100 万条数据
-- 为什么要分页？
-- 缓解数据库的压力，给人的体验感更好(瀑布流，无限刷)

-- 分页，每页只显示五条数据
-- 语法：limit 起始数据下标，页面大小
-- 网页应用：当前页，总页数，页面大小
-- limit 0,5   1~5
-- limit 1,5   2~6
-- limit 5,5   6~10

-- 注意limit后面的参数必须是具体值，不能是表达式

select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult asc
limit 5,5;

-- 第一页  limit 0,5   (1-1)*5
-- 第二页  limit 5,5   (2-1)*5
-- 第三页  limit 10,5  (3-1)*5
-- 第N页   limit       (n-1)*pagesize,pagesize
-- pagesize:页面大小
-- (n-1)*pagesize 起始值
-- n当前页
-- 总页数 = 数据总数/页面大小

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
select distinct s.studentno,studentname
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where sub.subjectname = '高等数学-2' and studentresult >= 80

-- 子查询
select distinct s.studentno,studentname,subjectno
from student s
inner join result r
on r.studentno = s.studentno
where studentresult >= 80 and subjectno = (
		select subjectno from subject 
		where subjectname = '高等数学-2'
)

--  子查询
-- 查高等数学-2的科目号->查成绩大于80且是高等数学的学号->查高等数学成绩大于80的学号和姓名
select s.studentno,studentname
from student s
where studentno in(
		select distinct studentno from result
		where studentresult >= 80
		and subjectno =(
			select subjectno from subject
			where subjectname = '高等数学-2'
		)
)

-- 练习
-- 查询C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 使用子查询
select s.studentno,studentname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
where r.subjectno = (
	select subjectno from subject
	where subjectname = 'C语言-1'
)
order by r.studentresult desc
limit 0,5 

select s.stu_no '学号',s.stu_name '姓名'
from students s
where s.stu_no in(
		-- 查询C语言成绩前5 的学生
		select r.stu_no
		from results r
		where r.subj_no = (
				--  查询 C语言-1 的课程编号 5
				select sub.subj_no
				from subjects sub
				where sub.subj_name = 'C语言-1'
		)
		order by r.stu_result desc
		limit 0,5
-- 1001,1000,1006,1005,1017
);
-- 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

-- 解决方法
-- 保证in 里面是一个结果集，先将limit 分页出来的数据 作为一张表，在从这这张表中查数据
select s.stu_no '学号',s.stu_name '姓名'
from students s
where s.stu_no in(
		-- 查询C语言成绩前5 的学生
		select t.* from   (select r.stu_no
		from results r
		where r.subj_no = (
				--  查询 C语言-1 的课程编号 5
				select sub.subj_no
				from subjects sub
				where sub.subj_name = 'C语言-1'
		)
		order by r.stu_result desc
		limit 0,5) as t
-- 1001,1000,1006,1005,1017
);

-- 查询成绩表，使用子查询，根据课程名称，查询所有的学生编号和成绩
-- 1.查询成绩表，使用子查询，根据课程名称，查询所有的学生编号和成绩
select r.stu_no,r.stu_result
from results r
where r.subj_no = (
		--  查询 C语言-1 的课程编号 5
		select sub.subj_no
		from subjects sub
		where sub.subj_name = 'C语言-1'
);

-- 将第一步的查询结果，作为一张表，跟学生表关联
select st.stu_no,st.stu_name,sr.stu_result
from students st,(
		select r.stu_no,r.stu_result
		from results r
		where r.subj_no = (
				--  查询 C语言-1 的课程编号 5
				select sub.subj_no
				from subjects sub
				where sub.subj_name = 'C语言-1'
		)
) sr -- 给结果集去别名，使用该结果集的时候直接使用别名
where st.stu_no = sr.stu_no
order by r.stu_result desc
limit 0,5

-- 查询员工表中，所有管理者的信息
-- 子查询写法:使用 in 子查询，先确定所有的管理者(manager_id中都是管理者)，注意去重

select e.employee_id '编号',e.last_name '姓名',e.department_id '部门编号'
from employees e
where e.employee_id in(
	select distinct manager_id
	from employees
	where manager_id is not null
)


-- 扩展用法1：使用exists,性能优于in
-- 原理：自动将外层的employee_id的值，带入exists子查询中，如果查询到记录，返回true,外层就将此记录保留，否则丢弃
select e.employee_id '编号',e.last_name '姓名',e.department_id '部门编号'
from employees e
where exists (
	select e2.employee_id
	from employees e2
	where e2.manager_id = e.employee_id
)

-- 练习
-- 查询员工表，各个部门中，比员工所在部门平均工资高的员工信息

-- 子查询常规用法
-- 各部门的平均工资
select e.department_id '部门编号',avg(e.salary) '平均工资'
from employees e
group by e.department_id;

-- 将员工表和上一步的部门平均工资结果集，通过部门编号进行连接查询（等值），可以关联为一张表数据，将进行不同字段的比较
select e.employee_id '编号',e.last_name '姓名',e.department_id '部门编号',e.salary '工资',t.avgsalary '部门平均工资'
from employees e,(
  -- 各部门的平均工资  的结果集作为一张表，与其员工表进行查询
	select department_id,avg(salary) as 'avgsalary'
	from employees
	group by department_id
) as t
where e.department_id = t.department_id
and e.salary > t.avgsalary;

-- 子查询高级用法:借助exists的原理，但是效率不是很好
select e1.employee_id '编号',e1.last_name '姓名',e1.department_id '部门编号',e1.salary '工资'
from employees e1
where e1.salary > (
	select avg(salary) 
	from employees e2
	where e2.department_id = e1.department_id
);

-- 分组查询，一般配合分组（多行函数）函数使用，不可以直接使用单行函数使用
-- group by  又去重的效果，也可以作为去重的方法
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
select subjectname,avg(studentresult) '平均分',max(studentresult) ' 最高分',min(studentresult) '最低分'
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
group by r.subjectno -- 通过说明字段分组
having 平均分 > 80

-- 都能够统计  表中的数据 （查询一个表中有多少个记录）
-- 统计查询结果总数，复合条件的记录数，就相当于一个计数器，有一条记录就加1，跟函数传入的值无关
-- 注意对大的表，避免使用count(*)，效率第
-- 推荐使用count(1),或者 count(主键列)
-- 强调：使用count(非主键列)，如果当前列值允许为空，统计时遇到值为空的记录时，不会计数
select count(studentname) from student; -- count(指定字段) 会忽略所有的null值
select count(*) from student;			-- count(*) 不会忽略所有的null

null值 ，本质 计算行数
select count(1) from student;			-- count(1) 不会忽略所有的null值 ， 本质计算行数

select sum(studentresult) as '总和' from result;
select avg(studentresult) as '平均分' from result;
select max(studentresult) as '最高分' from result;
select min(studentresult) as '最低分' from result;

-- 语法： ifnull(数据列，如果为null的默认值),这个时mysql特有的，oracle没有，它里面是nvl()

-- 查询员工表中所有人的年薪 (工资+绩效)*12  注意：有的人没有绩效，绩效为null
-- (salary + commission_pct)*12 当commission_pct为null 时结果为null
-- (salary+salary*ifnull(commission_pct,0))*12 
-- 当commission_pct为null 时 commission_pct 为0，结果就不会为null了；
selecte.last_name '姓名',(salary+salary*ifnull(commission_pct,0))*12 '年薪'
from employees e;

-- md5(字段或指定值)
-- 加密
update testmd5 set pwd=md5(pwd) where id = 1;

-- 加密全部的数据
update testmd5 set pwd=md5(pwd) 

-- 插入的时候加密
insert into testmd5 values (4,'xiaoming',md5('123456'));


-- 如何校验
-- 将用户传递进来的密码，进行MD5加密，然后对比加密后的值
select * from testmd5 where name = 'xiaoming'
and pwd = md5('123456');

--语法： SUM(CASE  行转列的字段 WHEN '行转列的字段匹配的数据' THEN 行专列后的数据来源字段 ELSE 0 END) as '行转列的字段匹配的数据'
/*
查询公司在2015-2018年之间，每年雇用的人数，结果类似下面的格式
		 total	2015	2016	2017	2018
		 74	     4	    9	   30	   31
*/
-- 查询并分组每年的人 得到原数据
select total.*
from
(
select year(hire_date) 'year',count(year(hire_date)) 'num'
from employees e
group by year(hire_date)
) as total
order by total.year;

-- 行转列 得到目标数据
select sum(total.num) 'total',
SUM(CASE  total.`year` WHEN '2000' THEN total.num ELSE 0 END) as '2000',
SUM(CASE  total.`year` WHEN '2006' THEN total.num ELSE 0 END) as '2006',
SUM(CASE  total.`year` WHEN '2007' THEN total.num ELSE 0 END) as '2007',
SUM(CASE  total.`year` WHEN '2009' THEN total.num ELSE 0 END) as '2009',
SUM(CASE  total.`year` WHEN '2010' THEN total.num ELSE 0 END) as '2010',
SUM(CASE  total.`year` WHEN '2011' THEN total.num ELSE 0 END) as '2011',
SUM(CASE  total.`year` WHEN '2013' THEN total.num ELSE 0 END) as '2013',
SUM(CASE  total.`year` WHEN '2014' THEN total.num ELSE 0 END) as '2014',
SUM(CASE  total.`year` WHEN '2015' THEN total.num ELSE 0 END) as '2015',
SUM(CASE  total.`year` WHEN '2016' THEN total.num ELSE 0 END) as '2016',
SUM(CASE  total.`year` WHEN '2017' THEN total.num ELSE 0 END) as '2017',
SUM(CASE  total.`year` WHEN '2018' THEN total.num ELSE 0 END) as '2018',
SUM(CASE  total.`year` WHEN '2019' THEN total.num ELSE 0 END) as '2019'
from
(
	-- 查询并分组每年的人
	select year(hire_date) as 'year',count(year(hire_date)) as 'num'
	from employees e
	group by year(hire_date)
) as total;

-- mysql 是默认开启事务自动提交的
set autocommit = 0; -- 关闭
set autocommit = 1; -- 提交

-- 手动处理事务
set autocommit = 0; -- 关闭自动提交

-- 事务开启
start transaction  -- 标记一个事务的开始，从这个之后的sql都在同一个事务内 
-- 可省略，写了表示立即启动一个事务，不写系统会自动开启一个事务

insert xx
insert xx

-- （成功）提交：持久化（成功）
commit

-- （失败）回滚：回滚到原来的样子

rollback

-- 事务结束
set autommit =1 -- 开启自动提交


savepoint 保存点名 --   设置一个事务的保存点，事务比较长的时候
rollback savepoint 保存点名  -- 回滚到保存点
release savepoint  保存点名 -- 撤销（删除）保存点

-- 模拟转账
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'  -- A增加500

commit; -- 提交事务 注意提交后才看到数据库中的数据发生改变
rollback; -- 回滚

set autocommit = 1;  -- 注意 一定要 恢复自动提交模式

-- 显示所有的索引信息 
show index from student;

-- 增加一个全文索引
alter table `kuangshen_school`.`student` add fulltext index `studentname` (`studentname`);

-- explain 分析sql执行的状况
explain select * from student; -- 非全文索引

explain select * from student where match(studentname) against('刘'); -- 全文索引

-- 建表app_user：
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';

-- 批量插入数据：100w
-- 插入100万条数据
DROP FUNCTION IF EXISTS mock_data;

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION `mock_data`()
RETURNS INT
deterministic
BEGIN
	declare num int default 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) values(CONCAT('用户',i),'xxxxxx@qq.com',CONCAT('18',FLOOR(RAND()*999999999)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
        SET i = i+1;
	END WHILE;
    RETURN i;
END;

-- 查询 执行函数
select mock_data();

-- 删除 函数
drop function mock_date;

-- 没有加索引之前
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 查看耗时 时间: 1.218s
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 时间: 1.228s
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 时间: 1.218s

EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999';

-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 加上索引后查询
SELECT * FROM app_user WHERE NAME = '用户9999'; -- 时间: 0.007s
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999';

-- 创建用户
-- create user 用户名 identified '密码'
create user huayu identified by '123456';

-- 修改密码（修改当前用户密码）
set password = password('123456');

-- 修改密码（修改指定用户密码）
set password for huayu = password('123456');

-- 重命名
-- rename user 原来的名字 to 新的名字
rename user huayu to hauyu2

-- 用户授权 all privileges 全部的权限  库.表
-- all privilege 除了给别人授权，其他权限都有
grant all privileges on *.* to huayu 

-- 查询权限
show grants for huayu; -- 查看指定用户的权限
show grants for root@localhost;

-- 撤销权限 revoke 哪些权限，在哪个库撤销，给谁撤销
revoke all privileges on *.* from huayu;

-- 删除用户
drop user huayu;

-- 使用命令行导出
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库  [表名...] > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

-- 导入
-- 登录的情况下，切换到只当数据库
-- source 备份的文件
source D:/a.sql

-- mysql命令
mysql -u用户名 -p密码 库名<备份的文件

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

package com.huayu.lesson01;

import java.sql.*;

//第一个JDBC数据库
public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.添加驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动

        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/kuangshen_school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8";
        String username = "root";
        String password = "root";

        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL的对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象 去 执行SQL  可能存在加过，查看结果
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql); //返回的结果集,结果集中封装了我们查询的所有结果

        System.out.println("id\tname\tpassword\temail\t\t\t\tbirthday");
        while(resultSet.next()){
            //字段名查询
            System.out.print(resultSet.getObject("id"));
            System.out.print("\t"+resultSet.getObject("name"));
            System.out.print("\t"+resultSet.getObject("password"));
            System.out.print("\t\t"+resultSet.getObject("email"));
            System.out.println("\t\t"+resultSet.getObject("birthday"));

            //小标查询 下表从1开始
//            System.out.print(resultSet.getObject(1));
//            System.out.print("\t"+resultSet.getObject(2));
//            System.out.print("\t"+resultSet.getObject(3));
//            System.out.print("\t\t"+resultSet.getObject(4));
//            System.out.println("\t\t"+resultSet.getObject(5));
        }

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

}

id	name	password	email				birthday
1	zhansan	123456		zs@sina.com			1980-12-04
2	lisi	123456		lisi@sina.com		1981-12-04
3	wangwu	123456		wangwu@sina.com		1979-12-04

 Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection(url, username, password);

Statement statement = connection.createStatement();
//PreparedStatement preparedStatement = connection.prepareStatement();

resultSet.close();
statement.close();
//preparedStatement.close()
connection.close();

Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username, password);

//connection  代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit(false);
connection.commit();
connection.rollback();

 String url = "jdbc:mysql://localhost:3306/kuangshen_school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8";

//(协议)://主机地址:端口号/数据库名?参数1&参数2&参数3
// mysql 默认端口3306
jdbc:mysql://localhost:3306/sid
//如果连接的是本地的Mysql数据库，并且连接使用的端口是3306，那么的url地址可以简写为
jdbc:mysql:///sid
//oralce 默认端口1521
jdbc:oracle:thin:@localhost:1521:sid
//SqlServer写法 默认端口 1433
SqlServer写法：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid

String sql = "select * from users"; //编写sql

statement.executeQuery(); //查询操作返回ResultSet
statement.execute();      //执行任何SQL
statement.executeUpdate();//跟新，插入，删除，返回受影响的行数

//在不知道列类型的清空下使用
resultSet.getObject();
//如果知道列的类型就使用指定类型
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

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

resultSet.close();
statement.close();
//preparedStatement.close()
connection.close();

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/kuangshen_school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8
username=root
password=root

package com.huayu.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static{
        try {
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url, username, password);
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }

    }


}

package com.huayu.lesson02.utils;

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

public class TestInstert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st= null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection(); //获取数据库连接
            st = conn.createStatement();
            String sql = "insert into users (id,`name`,`password`,email,birthday)" +
                    "values (5,'huauyu','123456','123456@qq.com','2000-01-01');";

            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,st,rs);
        }
    }
}

//其他代码一样
String sql = "delete from users where id = 4";

int i = st.executeUpdate(sql);
if(i>0){
    System.out.println("删除成功！");
}

//其他代码一样
 String sql = "update users set `name` = 'huayu',email = '12345@qq.com' where id = 2";

int i = st.executeUpdate(sql);
if(i>0){
    System.out.println("修改成功！");
}

//其他代码一样
 String sql = "select * from users where id = 1";

rs = st.executeQuery(sql);
while (rs.next()){
    System.out.println(rs.getString("name"));
}

package com.huayu.lesson02.utils;

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

public class SQL注入 {
    public static void main(String[] args) {

        //正常登录
//        login("huayu","123456");

        //sql注入
        login(" 'or ' 1=1 "," 'or ' 1=1 ");

    }

    //登录业务
    public static  void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();
            st = conn.createStatement();

            //select * from users where `name` = 'huayu' and `password` = '123456';
            //select * from users where `name` = '' or '1=1' and `password` = '123456';
            String sql = "select * from users where `name` = '"+ username +"' and `password` = '"+password+"'";

            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.print("name:"+rs.getString("name"));
                System.out.println("\tpassword:"+rs.getString("password"));
            }

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,st,rs);
        }
    }

}

// PreparedStatement 是 Statement 的子类
public interface PreparedStatement extends Statement

package com.huayu.lesson03;

import com.huayu.lesson02.utils.JdbcUtil;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = JdbcUtil.getConnection();

            //区别
            //使用问号？ 占用符代替参数
            String sql = "insert into users (id,`name`,`password`,email,birthday) values (?,?,?,?,?);";

            pst = conn.prepareStatement(sql); //预编译SQL,先些sql,然后不执行

            //手动设置参数

            pst.setInt(1,4);
            pst.setString(2,"xiaoyu");
            pst.setString(3,"123456");
            pst.setString(4,"123@qq.com");
            //注意点：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 sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,pst,null);
        }


    }
}

//其他代码一样
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("删除成功！");
}

//其他代码一样
 String sql = "update users set `name` = ?  where id = ? ;";

pst = conn.prepareStatement(sql); //预编译SQL,先些sql,然后不执行

//手动设置参数

pst.setString(1,"huayu");
pst.setInt(2,1);

int i = pst.executeUpdate();
if(i > 0){
    System.out.println("更新入成功！");
}

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

package com.huayu.lesson03;

import com.huayu.lesson02.utils.JdbcUtil;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {

        //正常登录
//        login("huayu","123456");

        //sql注入
        login(" 'or ' 1=1 "," 'or ' 1=1 ");

    }

    //登录业务
    public static  void login(String username,String password){
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();

            //prepareStatement 防止SQL注入的本质，把传递进来的参数当作字符
            //假设其中存在转移字符，单引号 ' 会被直接转义
            String sql = "select * from users where name = ? and password = ?";
            pst = conn.prepareStatement(sql);

            //手动设置参数
            pst.setString(1,username);
            pst.setString(2,password);

            rs = pst.executeQuery();

            while (rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,pst,rs);
        }

    }
}

package com.huayu.lesson04;

import com.huayu.lesson02.utils.JdbcUtil;

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

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtil.getConnection();

            //关闭数据库的自动提交,自动开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money = money-100 where name= 'A';";
            pst =  conn.prepareStatement(sql1);
            pst.executeUpdate();

            String sql2 = "update account set money = money+100 where name= 'B';";
            pst =  conn.prepareStatement(sql2);
            pst.executeUpdate();

            //模拟错误
            //int x = 1/0;

            //业务完毕，提交事务
            conn.commit();
            System.out.println("业务成功完成！");


        } catch (SQLException sqlException) {
            try {
                //如果失败，则默认回滚
                conn.rollback(); //如果失败则回滚事务

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            sqlException.printStackTrace();
        }finally {
            JdbcUtil.release(conn,pst,rs);
        }
    }
}

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED,REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil_DBCP {

    private static DataSource dataSource = null;

    static{
        try {
            InputStream in = JdbcUtil_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式 -- >创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }

    }


}

//只需要将获取连接换一下就好
conn = JdbcUtil_DBCP.getConnection();

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    C3P0的缺省(默认)配置，
    如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource();”这样写
    就表示使用的是C3P0的缺省(默认)配置信息来创建数据源
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy? useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!--
    C3P0的命名配置，
    如果在代码中“ComboPooledDataSource ds = new
    ComboPooledDataSource("MySQL");”这样写就表示使用的是name是MySQL的配置
    信息来创建数据源
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtil_C3P0 {

    private static ComboPooledDataSource dataSource = null;

    static{
        try {
            //代码版配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();

            //创建数据源 工厂模式 -- >创建
            dataSource = new ComboPooledDataSource("MySQL"); //配置文件写入

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        }

    }

}

//获取连接
conn = JdbcUtil_C3P0.getConnection();  //原来是自己实现的，现在用别人实现的