报告错误总结

多半是用了查询语句，但是执行用的是executeUpdate, 要使用executeQuery

对于创建文件来说，它返回的受到影响的行数为0，所以这时候不要用以下语句来判断是否成功

Mysql

进入数据库的指令

mysql -u root -p ，我们的密码是（空的）（window） 123456（linux）

快捷键

shift+ctrl +c
shift+ctrl+r

字符集的一些注意点

基字符集

utf-8等等

这些是数据库排序规则

utf8_general_ci不区分大小写
utf8_bin区分大小写

删除数据库

SELECT {*|字段列名}FROM 数据表名 WHERE 查询条件

你可以用 *来替代所有字段列名
或者自定义字段列名
| 代表或者

LIMIT + 数字表示打印的个数

SELECT
    customer_number
FROM
    orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1
# z

创建

查询

1
2
3

# t1代表的是表的名字，NAME是这个表定义的名称
SELECT * FROM t1 WHERE NAME = 'tom'
s

显示数据库

SHOW DATABASES

注意这里要写上s

显示数据库创建语句

SHOW CREATE DATABASE 名称

名称尽量带上反引号，否则编译器容易误解
如果名称与关键字重合那就必须带上双引号

SHOW CREATE DATABASE

数据库删除语句（慎用）

DROP DATABASE 名称

数据库的备份

必须在dos操作系统下执行

1	mysqldump -u root -p -B wjh_db05 > d:\\pr\\bak1.sql（会自动创建文件）

恢复数据(方法一)

要先进入mysql命令行才能够执行

mysql -u root -p

1	source d:\\bak1.sql

恢复方法二

1	直接手动复制数据库的数据赋值到文件中,放到查询编译器中，执行

仅仅备份数据库中的表

mysqldump -u root -p 数据库然后表的名称

一定要熟悉以上的语法

表

创建表

CREATE table name{
 field1 datatype,
 field2 datatype
}

character set 如果没有指定字符集，就以创建数据库的规则为准
collate 同上
engine 同上

创建方式1：用图形化创建表

创建方式2：用指令创建表

CREATE TABLE `user` (
 id INT,
 `name` VARCHAR(255),
 `password` VARCHAR(255),
 `birthday` DATE)
 CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB

后面的类型可以不写

修改表

增加一个行叫做image

1
2
3

ALTER TABLE emp
  ADD image VARCHAR(32) NOT NULL DEFAULT ''
  AFTER RESUME

修改job列使其长度为60

1
2
3

ALTER TABLE emp
  MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
  AFTER RESUME

删除sex这一行

1 2	ALTER TABLE emp DROP sex

表名改成 employee

1	RENAME TABLE emp T10 employee （第一个是数据库，第二个是要修改的原表名称，第三个是修改后的表名

修改表的字符集为utf8

1	ALTER TABLE employee CHARACTER SET utf8

列名name改成user_name

1 2	ALTER TABLE employee CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''

MySQL的常用数据类型

数值类型

文本类型

二进制数据类型

日期类型

datetime YYYY-MM-DD HH:MM:SS

时间戳

timestamp

列类型

整型

使用规则：在能够满足需求的情况下，尽量选择小的数据类型
必须要在数据范围内的数据才能够被存入到数据中

bit

打印出来是以二进制的形式输出

1
2
3

CREATE TABLE t05(num BIT(8));
INSERT INTO t05 VALUES(254);
SELECT * FROM t05;

数值类型

CREATE TABLE t06(
 nums1 FLOAT,
 nums2 DOUBLE,
 nums3 DECIMAL(30,20));
INSERT INTO t06 VALUES(88.661616,88.6151515115151,88.5555555555555);
SELECT *FROM t06;

输出结果为

DECIMAL的注意事项

字符串类型

1 2	CREATE TABLE t10( `name` VARCHAR(32766))CHARSET gbk;

在数据范围内

1 2	CREATE TABLE t10( `name` VARCHAR(32767))CHARSET gbk;

超过了gbk格式的数据范围

提示输入数值过大

问题1：为什么这里要减去三个字节

回答：因为要用1~3的字节去记录大小

问题2：这里的size是代表字符还是字节

回答：是字符

细节

注意这里面的4是字符而不是字节

CREATE TABLE t11(
 `name` CHAR(4));
INSERT INTO t11 VALUES('abs');
SELECT * FROM t11;

varchar是一个可变长的字符，不会造成空间的浪费，而CHAR只能根据最开始分配的空间来给内存
但是char的查询速度快于varchar，如果数据的定长的话，我们就尽量去使用char
如果varchar不够用，可以使用mediumtext或者longtext，或者可以写text

总结

空间大小上： char > varchar
速度快慢上：char > varchar
如果文本过大就选择text

日期

CREATE TABLE t14(
 birthday DATE , -- 生日
 jobtime DATETIME ,-- 记录年月日 时分秒
 login_time TIMESTAMP 
  NOT NULL DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP);-- 登录时间
SELECT *FROM t14;
INSERT INTO t14(birthday,jobtime)
 VALUES('2022-11-11','2022-11-11 10:10:10');

不用输入时间自动跳出来登陆时间

需要输入

1	NO NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

实战

CREATE TABLE `emp`(
 id INT,
 `name` VARCHAR(32),
 sex CHAR(1),
 birthday DATE,
 entry_date DATETIME,
 job VARCHAR(32),
 salary DOUBLE,
 `resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
# 添加的时间
INSERT INTO `emp`
 VALUES(100,'小妖怪','男','2000-11-11',
 '2010-11-10 11:11:11','巡山的',3000,'大王叫我来巡山');
SELECT *FROM `emp`

CRUD

insert 语句

# 注意写上字符集以及校对规则和引擎
CREATE TABLE `good`(
 id INT,
 goods_name VARCHAR(15),
 price DOUBLE)CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO `good` (id,goods_name,price)
 VALUES(10,'华为手机',2000);
INSERT INTO `good` (id,goods_name,price)
 VALUES(20,'苹果手机',3000);
SELECT* FROM good;

#上诉的insert语句也可以简化写为（但是必须和你写的表的顺序一致）
INSERT INTO `good` 
 VALUES(20,'苹果手机',3000);

注意点

插入的数据应当和字段类型相同。

错误示范：

同时我们用单引号括起来的是一个数字，那么它会进行转型，这种时候就是正确的

数据的长度应该在列的范围内

错误示范：

在values中列出的数据位置必须与被加入的列的排列位置相对应
字符和日期形数据应该包含在单引号中
列可以插入空值（前提是该字段允许为空），insert into table value (null)
insert into tab_name(列名..)

1	INSERT INTO `goods` v（填入内容）（填入内容）（填入内容）

如果是给表中的所有字段添加数据，可以不写前面的字段名称,但是不能少一条字段
默认值，当不给某个字段值时，如果有默认值就会添加默认值，否则报错

# 这个时候就是可以的
CREATE TABLE 'goods'(
  id INT,
     'label' VARCHAR(10),
     'price' DOUBLE 
 )
INSERT INTO `goods`(id,goods_name)
  VALUES(80,'格力手机');
# 当表的创建格式为以下的情况的时候,编译器就会报错
CREATE TABLE 'goods'(
  id INT,
     'label' VARCHAR(10),
     'price' DOUBLE NOT NULL DEFAULT ''
 )

delete语句

1 2	delete from xxx(the name of list) where user_name = "yyy" # 从表名为 xxx 的表中删除掉名字为 yyy 的的那一列

# 删除表中的名称为yy的user_name
DELETE FROM xxx
  WHERE user_name = 'yyy';
# 删除表中为user_name的数据
DELETE FROM XXX

# 删除表中邮箱相同的,并且只保留它的id最小值
DELETE p1 from 
        Person p1,Person p2
        where 
        p1.email=p2.email AND p1.id>p2.id
        
DELETE 
FROM
 Person 
WHERE
 id NOT IN (SELECT * FROM ( SELECT min( id ) AS id FROM Person GROUP BY email ) t)
    
# delete不能删除某一列的数据
# 如果需要删掉某一行的一个字段，我们需要利用update进行替换
UPDATE employee SET job = '' WHERE user_name = '老妖怪'

# 删除表是用drop而不是delete

因为delete语句不能删除某一列的值（可使用update 设为null)

1	UPDATE xxx(表名称) SET x = '' WHERE user_name = '老妖怪'

update语句

将所有的员工薪水修改为5000

1	UPDATuE employee SET salary = 5000

将姓名为小妖怪的员工修改为3000

1	UPDATE employee set salary = 3000 where user_name = '小妖怪'

将老妖怪的薪水增加3000

1	UPDATE employee set salary = salary + 3000 where user_name = '老妖怪'

set可以根据需要修改多个字段的值

set xxx = xxxx yyy = yyyy

CASE语句

# 将性别为男的改成女的，女的改成男的
UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

select 语句

SELECT  *FROM `student`;
# 去重查询（必须查出来的每一个列都相同）
select DISTINCT english from student

#如果两个人的英语成select DISTINCT english from student绩相同但是名字不一样
select DISTINCT english name from student
#这个时候，就不会去重

案列

SELECT `name` (chinese+math+english)from student
# 计算学术三个科目的总分
SELECT `name` as '名字',(chinese+math+english) as '总分'
from student
# as可以替换名字

运算符

自己到力扣做题就懂了

如果想要离散的空间查询需要用到IN

1 2	select * from student where math in(89,90,91) # 代表着查询数学成绩为 89, 90 ,91的学生

模糊匹配

select *from student where math like '韩%'
# 一定要加上%

# 需要注意的是这里没有讲清楚实际上 :
#如果这里写的是 %韩% 的话那么代表查找存在韩的数据
# 如果是韩% 代表查找以韩开头的数据
# 如果是%韩 , 代表查找 以韩为结尾d

排序

select * from student 
  order by math(默认升序)
select * from student 
  order by math asc(升序) // 默认为升序
select * from student 
  order by math desc(减序)

# 排序的组合操作
#如果我们想要部门按照部门号升序，然后按照工资降序
select *from emp 
 order by deptno asc, sal desc

函数(与select配合的)

count

查询的结果有多少行

1 2	select count(*)from student where math>90 # 统计数学大于90的学生

count(*) 返回满足条件的行数
count(列):统计满足条件某列有多少个，但是会排除为null的情况

sum

返回满足where条件的行的和一般使用在数值列中（注意他只能对数值起作用）

1 2	select sum(math) from student; # 统计一个班所有人的数学总成绩

avg

求平均值，同上对数值起作用

1	select avg(math) from student

max/min

求最大值或者最小值

1 2	select max(math) from student select min(math) from student

group

1
2
3

select avg(sal),max(sal), deptno from employee group by deptno
# 按照部门分组查询平均工资和最高工资
# 需要注意是，一个部门有很多人，每个人的工资不一样

最后的输出结果为

having（和group共同使用）

相当于where条件筛选，他和group是好兄弟

select avg(sal) as avg_sal,deptno
 from emp group by deptno
 having avg_sal<3000
# 筛选平均工资小于3000的部门9

# 筛选至少合作过三次的导演
SELECT actor_id, director_id FROM ActorDirector 
    GROUP BY actor_id, director_id
    HAVING COUNT(*) >= 3;
# GROUP BY 就是分组，这样子写的话，就是把一对导演和演员分成一组

字符串相关函数

1 2	select charset(ename) from emp; 返回charset（name）的表

1	select concat(ename,'工作是',job) from emp;

练习以首字母小写的方式显示所有员工emp表的姓名

# 方法一
select CONCAT(Lcase(SUBSTRING(ename,1,1)),SUBSTRING(ename,2)) as new_name
 from emp;
## 注意这里的下标从0开始

# 方法二
select concat(lcase(Left(ename,1)),substring(ename,2)) as new_name;

数学函数

时间函数

CREATE TABLE mes(
 id INT,
 content VARCHAR(30),
 send_time DATETIME);
INSERT INTO mes
 VALUES(1,'北京新闻',CURRENT_TIMESTAMP);
SELECT* FROM mes;

# 计算时间差的时候其实除了使用DATEDIFF还可以 使用between xxx and xxx（时间用''b）

请写出以上的所有mysql语句

1
2
3

#如果改成20min之内就是
select * from mes
where date_add(send_time,INTERVAL 20 MINUTE)>=NOW();

year/month/date（）

分别返回年/月/日

unix_timestamp返回的是1970-1-1到现在的秒数

1	select unix_timestamp(now()) from xxx

from_unixtime

1
2
3

# '%Y-%m-%d'(年月日的格式)
select from_unixtime(1618483484,%Y-%m-%d) from dual
# '%Y-%m-%d %H:%i:%s'(int转换位年月日的格式)

加密函数

select user() from dual;
# 返回的是用户ip地址
select database()
# 查询当前使用数据库名称
select MD5('HSP')from dual 
# 在数据库中存放的是加密后的密码（长度是32位）
select password('hsp')from dual
# 加密函数，mysql数据库的用户密码就是password函数加密

流程控制函数

多分支的任务需求

# 如果第一个exp1为真就返回exp2，否则返回expr3
select if(TRUE,'北京','上海')from dual;
#输出                         北京


# 看上表规则
select if(NULL,'北京'，'上海')
#输出                         上海


# 多分支
# 判断是否为空要使用is null/is not null
select  ename  IF(comm is null，0.0,comm);
#或者写出
select  ename  IFNULL(comm，0.0);


#多分支
select ename, (select case
               when job = 'cherk' then '职员'
               when job = 'manager' then '经理'
               when job = 'salesman' then '销售人员'
               when job end) as 'job'
               from emp

模糊匹配

select ename from list ename Like "__0%"
# 查询ename第三个数字是0的表
select ename from list ename like "s%"
# 查询ename以s作为开头的ename

分页查询

# grammer select * from * limit xxxx
select *from emp order by empno
select * from emp
  order by empno 
  limit 0, 3;
select * from emp 
  order by empno 
  limit 3, 3;
select * from emp
  order by empno
  limit 6,

多表查询

多表查询的时候，就是列拼接在一起，然后相乘得到行数
当有两个表相同的字段我们需要做出区分
自连接

问题

# 显示员工和他上级的名字
# 把同一张表当作两张表使用
# 需要给表取别名，表名，表别名
# 列名不明确，可以指定列的别名
select worker.ename as '职员表',boss.ename As '上级表'
    from emp worker, emp boss# 注意这个可以不用加上as 
                where worker.mgr = boss.empno;

mysql子查询

单行和多行的子查询

单行子查询：返回单行
多行子查询：返回多行
- all ： 所有的条件
- any： 存在一个条件

问题1

# 子条件
select deptno 
  from emp
  where ename = 'SMITH'
# 然后把他嵌套进去
select* from emp 
  where deptno =(
            select deptno 
   from emp
   where ename = 'SMITH'
        )

问题2

# 薪水大于所有查询子条件的元素 
select ename,sal,deptno 
  from emp
  where sal>ALL(
         select sal
              from emp
           where deptno = 30
        )
 # 方法二
select ename,sal,deptno 
  from emp
  where sal(
         select MAX(sal)
              from emp
           where deptno = 30
        )
#  求薪水最小的就反过来

select sal , empno 
 from emp
 where sal > any(
     select sal 
         from emp
         where dep = 30;
    )
当前元素比dep= 30的任一一个元素的val要大
select sal ,empno
 from emp
 where sal > (
     select min(sal) 
         from emp
         where dep = 30;
    );

子查询的临时表

任务：我们需要查询每一个商品类别中最高价格的商品信息

# 每一个商品种类最高价格的查询
select cat_id, MAX(shop_price)
  from ecs_goods
  Group BY cat_id
# 实现
select goods_id ecs_goods.cat_id, goods_name,shop_price
  from(
          select cat_id ,MAX(shop_price) as max_price
             from ecs_goods
             group by cat_id
        )temp# 这个就被叫做临时表
        , ecs_goods
        where temp.cat_id = ecs_goods.cat_id
        AND temp.max_price = ecs_goods.shop_price 

# 从员工表中找到一个工资比经理高的员工
 select a.Name as Employee 
 from Employee a, (select Salary,Id from Employee) b
 where a.ManagerId=b.Id and a.Salary > b.Salary 
 
 
 # 注意在写完临时表后必须给临时表命名命名格式是 在括号后面直接写 表的名字，或者是写上as 表名
 select  k.c as employee 
                from (
                    select a.name as c from employee as a,employee as b 
                    where a.managerId = b.id and a.salary>b.salary
                )as `k`

多列子查询

#子表
select deptno, job
  from emp
  where ename = 'SMITH'
select *from emp 
  //类似于python的列表， 可以进行多段匹配
  where(deptno,job)=(
            select deptno, job
   from emp
   where ename = 'SMITH'
        )AND ename !='SMITH';

练习一:查找每一个部门工资高于本部门平均工资的人的资料

# 找到每一个部门,的平均工资
select deptno AVG(sal) as avg_sal
  from emp
  group by deptno
  
#  将该表作为一个临时表
select ename, sal, temp.avg_sal,emp.deptno from emp,(
  select deptno AVG(sal) as avg_sal
  from emp
  group by deptno 
  )temp where emp.deptno = temp.deptno AND emp.sal>temp.avg_sal;

练习二: 查找每一个部门工资最高的人的详细资料

# 弄出一个子查询(每一个部门最高工资的人的)
select dentno, MAX(sal) as max_sal
      from emp
                        group by deptno
                      
                      
# 然后当作临时表进行查询
select ename,emp.deptno,max_sal
   from emp ,(
                select dentno, MAX(sal) as max_sal
                from emp
                group by deptno
            )temp where emp.deptno = temp.deptno AND emp.sal = temp.max_sal

练习三: 查询每一个部门的信息,包括部门名,编号,地址和人员数量

--1. 部门名 来自 dept表
select count(*),deptno
  from emp
  group by deptno

select dname, dept, deptno, loc ,tmp.per_num as'人数'
  from  (
            select count(*) as per_num,deptno
            from emp
            group by deptno
        ) tmp, dept
        where tmp.deptno = dept.deptno

# select 也可以改写为tmp.*,这样代表tmp的所有字段
select tmp.*, dname,loc
  from dept,(
            select count(*) as per_num,deptno
            from emp
            group by deptno
        ) tmp
        where tmp.deptno = dept.deptno

表的复制

目的:

# 复制一个表的数据到另一个表
insert into my_tab01
  (id,`name`,sal,job,deptno)
  select empno,ename,sal,job,deptno from emp'
# 自我复制
insert into my_tab02
  select * from my_tab02

题目如何去掉一个表的重复记录

 # 把表的结构复制到emp表中,这样就不用重复打那么多字了(注意是表的结构)
 create table my_tab02 like emp;
 
# 构造重复行 
 insert into my_tab02
  select * from my_tab02
 # 现在就有很多重复的行,那如何去重呢
 
 # 方法就是创建一个临时表,
 # 挑选这个表的distinct * from,插入到另一个表中
 # 然后删除原表,新表改成原表的名字
 
create table my_tmp like my_tab02
insert into my_tmp 
  select distinct * from my_tab02;
delect from my_tab02;
insert into my_tab02 
   select * from my_tmp;
   
# 或者我们也可以写成这样，来修改名字
# [old] rename to [new]
alter table my_tab02 rename to my_tmp

合并查询

union all将两个查询结果合并，不会去重
union就是两个结果合并，回去重，事实上很像or

表的外连接

如果要搞一个表的相同两个集合

表的自连接

method1 :
select 
 *
 from emp.worker , emp.boss
 where worker.mg
SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
相当于from两个表连接在一起

问题

发现两个表合并不能很好的实现

引出我们的外连接

引出我们的外连接问题

# 即便左表没有和右表连接的地方，也会出现左表,且左表为null
select 'name' stu.id,
  from stu LEFT JOIN exam
  ON stu.i d = exam.id
  
# 左连接如下写法
select 'name' stu.id,
  from stu RIGHT JOIN exam
  ON stu.id = exam.id

问题

1
2
3

select eptno.id,ename,work 
  from company left join emp
  ON where company.id = emp.id

mysql的约束

查看约束

1
2
3

#使用dec+表名
dec t17
# 就会显示约束的情况

主键

主键列的值是不可以重复的

# 在字段后面添加primary key就是把一个字段变成主键
create table t17(
  id int primary key,
  `name` varchar(32),
  emain varchar(32));

主键插入的元素不可以为null
一个表只能有一个主键，但可以复合主键

# 只有当id和name都和表中的一个数据相同的时候，才不能插入
create table t17(
  id int,
  `name` varchar(32),
  emain varchar(32)
  primary key(id,`name`));--这就是复合主键

定义主键的方式二

create table t19(
  id int,
     `name` varchar(32),
     emain varchar(32),
     primary key(`name`)--把定义写在后面
 )

unique

create table t19(
    id int unique,--id不可以重复
    `name` varchar(32),
    ename varchar(32),
)

如果unique没有增加not null约束，那么你就可以添加null的元素，而且null可以填写多个（当然非null不能填写多个）
一张表中可以有多个unique字段
primary key和unique的区别在于，primary key在一个表中只能存在一次，但是unique可以有多个，其他的特征都是相同的

表的外键

如果我们要求每一个学生所在的班级编号是存在的班级编号，我们就可以把class_id做成外键约束

相互约束的表格，必须得先删除左边的表，才能删除右边的表

# 创建主表
CREATE TABLE my_class(
  id INT PRIMARY KEY,
     `name` VARCHAR(32) NOT NULL DEFAULT '');
 
# 创建从表
CREATE TABLE my_stu(
  id INT PRIMARY KEY,
     `name`  VARCHAR(32) NOT NULL DEFAULT '',
     class_id INT,
     # 制定外键关系
     FOREIGN KEY(class_id) REFERENCES my_class(id))
  )
  // 第一个是key（从表的属性）re（主表（属性））
  
INSERT INTO my_class
 VALUES(100,'java'),(200,'web');   
    #成功插入
INSERT INTO my_stu
 VALUES(1,'tom',100),(2,'jack',200);
 #成功插入
INSERT INTO my_stu
 VALUES(3,'hsp',300);
 #插入失败

# 4
insert into my_stu
  values(5, 'king', NULL);
# 注意这是不会报错的
# 5
delete from my_class
  where id == 5;
# 外键约束失败了， 数据不能随意删除

check

mysql15.7,只做语法校验，不会生效

oracle ,sql server便会生效

create table t23(
  id int primary key,
  `name` varchar(32),
  sex varchar(6) check(sex in('man','woman'))),
  sal double check(sal>1000 AND sal<2000)
  );

ENUM

1 2	sex enum('男','女')not null # 表示sex只可能是男生或者女生

自增长

CREATE TABLE t24
 (id INT PRIMARY KEY AUTO_INCREMENT,
 email VARCHAR(32) NOT NULL DEFAULT '',
 `name` VARCHAR(32)NOT NULL DEFAULT '');
DESC t24
INSERT INTO t24 
 VALUES(NULL,'jack@qq.com','jack');
 
# 方法二
# 注意这里必要写参量
insert into t24
 (email, 'name') values('jack@qq.com', 'jack');
SELECT* FROM t24
# 出现null的时候会自动变成1

细节
1. 一般来说自增长需要和primary key配合使用
2. 自增长也可以单独使用（但是需要配合一个unique）
3. 自增长修饰的字段为整数形的（虽然小数也可以但是非常非常少这样使用）
4. 自增长默认从1开始，你也可以通过如下命令修改
  1
  2
  alter table t24 auto_increment = 100
  # 下次开始的时候就是从100开始
5. 如果你添加数据时，给自增长字段指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据

索引

索引优化速度

当表的元素特别多的时候，在没有创建索引时，我们的查询一条记录会非常慢
创建索引

# create index 索引的名称 on 表名 (列名)
create index empno_index on emp (empno)
# 创建索引之后，在select贼快,但是在创建索引的过程中需要一些时间，并且内存会变大
select * from emp where ename = 'axJxC';

索引机制

索引的原理

使用索引后，就会形成一个数据结构，比如二叉树。这个有序的二叉树就会存储这些数据

坏处
1. 占用磁盘空间
2. 会对update delete insert造成一定的影响

但是因为select在业务开发中的使用次数多于update delete insert之和,所以这个东西还是特别使用的

索引的类型

查询一个表是否有索引
添加唯一索引

1	create unique index id_index on t25(id);

删除索引

# 删除索引
drop index id_index on t25

# 删除主键索引
alter table drop primary key

查询索引

# 方式1
show index from 表名

# 方式二
show indexes from 表名

# 方式三
DESC b

主键索引

主键就是一种索引

create table t11(
id int primary key,-- 主键，同时也是索引
name varchar(32))
);
create table t12(
id int unique,-- 主键，同时也是索引
name varchar(32))
);

普通索引

这是一种最常用的索引。

唯一索引

CREATE TABLE t25(
 id INT,
 `name` VARCHAR(32));
INSERT INTO t25
 VALUES(20,"java");
SHOW  INDEXES FROM t25 
# 如果某列值是不重复，那么优先考虑唯一索引，否则考虑普通索引
CREATE UNIQUE INDEX id_index ON t25(id);

# 添加索引方式2
alter table t25 add index id_index (id)


# 添加主键索引
alter table t26 add primary key (id)

全文索引

我们常用solr和elasticsearch（ES）。

mysql事务

事务的理解

现在需要把多个数据视为一个整体。将多个dml（delete，update，insert）当做一个整体，要么全部成功，要么全部失败

create table t27{
 id int,
 `name` varchar(32)
}
start transaction 
savepoint a
insert into t27 values(200,'javk');
rollback a
# commit 就会真的生效了，还会把所有的回滚机制回退。 
commit

1	start transaction 也可以写成 set autocommit = off;

默认情况下会自动提交事务，但是不会保持。
必须使用innODB引擎才能使用支持事务

mysql事务隔离级别

更好理解的方式

知乎的一篇博客（讲的也可以）

大白话讲解脏写、脏读、不可重复读和幻读 - 知乎 (zhihu.com)

隔离级别的详细说明

脏读就是在没有commit的时候，一个事务被修改了，另一端也可以被查询到
不可重复读和幻读是，你commit了，当这个事务在commit之前被修改了，那么另一端就可以看到

可串行化在事物没有提交的时候，就会卡住不会动

查看mysql的隔离级别

# 查看隔离级别
select @@tx_isolation

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

查看系统隔离级别

1 2	# 查看系统隔离级别 suselect @@global.tx_isolation

修改一个控制台的隔离级别设置

set session transaction isolation level 隔离级别
# 被设置为读未提交的隔离级别
比如：set session transaction isolation level read uncommitted

修改系统隔离级别

1 2	# 设置系统当前隔离级别 set global transaction isolation level 隔离级别

mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改

mysql表的类型和存储引擎

事务安全型比如说是InnoDB
非事务安全性就是其他的引擎

表锁的级别高一些，锁的更多
细节说明：

myisam引擎

速度快，不支持外键，和事务

create table t28(
  id int,
  `name` varchar(32))engine myisam;
start transaction;
savepoint t1;
insert into t28 values(1,'jack');
select* from t28;
# 这里就会报错。因为myisam引擎不支持回滚。
rollback to t28

memory引擎

数据存储在内存中

执行速度很快，但是关闭数据库的时候，就会出现删除记录，但是会保留结构。，但是表的结构还是存在的

create table t28(
  id int,
  `name` varchar(32))engine memory;
insert int t29(
   (1,'tom'),(2,'jack'),(3,'hsp') );
select* from t29

三种引擎的抉择

当你不需要进行事务处理的时候就优先选择myisam.

否则就选择innoDB，如果代表用户的状态的时候，用户的状态频繁改动，我们就可以选择memory引擎（比如用户在线的状态）。
修改存储引擎

1 2	# 将表的存储引擎修改为innoDB alter table `t29` engine = innoDB

视图原理

视图和对应的真实表的关系
视图的总结
- 视图是根据基本来创建的，视图是虚拟的表
- 视图也有列，数据来自基表的映射（相当于一个指针）。
- 通过视图可以修改基表的数据
- 基表的改变，也会影响到视图的数据
而且基表不能直接查看，只能通过这个视图来访问

视图的基本使用

查看视图的view语句

create VIEW emp_view01
  AS
  SELECT empno, ename,job,deptno from emp;
# 查看视图
DESC emp_view01;

查看创建视图的指令

修改视图

update emp_view01
  set job = "MANAGER"
  WHERE empno = 7369;
# 发现基表也发生了变化，而且该表基表，视图也会发生改变

视图最佳实践

这样就可以创建一个视图，然后以后可以直接拿来反复用

create view `view_032`
  as
  select empno, ename, dname,grade 
  from emp, dept,salgrade
  where emp.deptno = dept.deptno AND
  (sal Between losal AND hisal)

映射到多张表的方法，就是映射到多个表格之间的笛卡尔积。

mysql数据库

当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限
所以，mysql数据库管理人员，根据需要创建不同的用户名，赋给相应的权限，供人员使用

# 创建一个hsp_edu的用户，他可以获得管理数据库的部分权限
CREATE USER 'hsp_edu'@'%' IDENTIFIED BY '123';
# 删除用户
DROP user `名称`@`localhost`;

登陆

点击那个绿色的充电器符号，然后就可以改变你的身份为wjh

修改密码

1	set password for `wjh`@`localhost` = password('123456');

给用户授权

1
2
3

grant 权限列表 on 库.对象名 to `用户名`@`登录位置`
# 直接赋给一个用户全部权限
grant all on *.* to `用户名`@`登录位置`

练习题

# 1
create user `wjh`@`localhost` indentified by '123456';
# 2
 # 使用root 用户创建testdb,表为news
create database testdb
create table news(
  id int,
  content varchar(32));
# 添加一个测试数据
insert into news values(100,'数据库');
grant select ,insert 
 on testdb.news
 to `wjh`@`localhost`
# 但是在wjh的用户，就不能更改这个用户的数据

# 表示xxx用户在192.168.1.* 的ip可以登录mysql
create user `smith`@`192.168.1.%`

# 在删除用户的时候，如果host不是%，需要明确制定，用户@host值
drop user jack-- 默认就是drop user `jack`@`%`
drop user `smith`@`192.168.1.%`表示xxx用户在192.168.1.*d

练习题

查找第n高的数据·

select DISTINCT
 salary as xx
from
 employee
order by salary desc
limit 1 offset 1
// offset代表跳过多少个元素
// 但是会遇到会空的情况， 我们可以利用子表查询,当为空的时候，就会返回一个null
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

我们还可以使用ifnull函数

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

求第N高的薪水

# 先让N减去1
set N := N - 1;

select 
  distinct salary
from 
  Employee
order by 
  salary desc
limit 1 offset N

# distinct 和 group by一个元素都可以起到去重的效果。

# 子查询实现
    select 
      distinct salary
      from employee e 
      where 
      (select count(distinct salary) from employee where salary > e.salary) = N

Java - 基础知识补充

Properties的作用

java - jdbc

导入 mysql - jdbc 的jar文件

jdbc概述

该图展现了java连接数据库的模式

jdbc 编写的步骤

连接

jdbc:mysql://localhost::3306/hsp_db02

”jdbc:mysql”是固定的，代表的是协议

3306 代表监听的端口
hsp_db02 代表你要连接的数据库

第一个jdbc代码

package com.wjh.jdbc;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 这是第一个jdbc程序
 */
public class jdbc01 {
    public static void main(String[] args) throws SQLException {
        // 1. 注册驱动
        Driver driver = new Driver();

        // 2。得到链接
        String url = "jdbc:mysql://localhost:3306/hsp_db02?useUnicode=true&characterEncoding=utf8";
        // 将用户名和密码放入到Properties 对象
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "");
        Connection connect = driver.connect(url, properties);
        // 3；执行sql
        String sql = "insert into actor values(null, '刘德华', '男', '1970-11-11', '110')";
        // 用于执行静态sql语句并返回其生成对象
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql); // dml语句 返回的就是影响的行数,只有在dml的时候才会返回影响的行数，如果是创建的话，就不会

        System.out.println(rows > 0 ? "成功":"失败");
        // 4.关闭连接资源
        statement.close();
        connect.close();
    }
}

上面代码需要注意的点：要在url里面结尾加上?useUnicode=true&characterEncoding=utf8
可以通过修改sql,换成delete ， update 等语句
通过反射类来加载，可以减少依赖性

1 2	Class<?> aClass = Class.forName("com.mysql.jdbc.Driver"); Driver driver = (Driver)aClass.newInstance();

利用DriverManager.registerDriver方法我们可以更好的打开数据库数据，而不像之前那样搞键值对

public static void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException{
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)aClass.newInstance();
        String url = "jdbc:mysql://localhost:3306/hsp_db02?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String passwd = "";
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection(url, user, passwd);
        System.out.println("第三种方式=" + connection);
 }

进一步的，我们发现，其实可以不需要注册驱动(这是因为底层有一个静态代码块，自动帮你写了) （这个方式获取链接是最重要）

public static void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException{
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/hsp_db02?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String passwd = "";
        DriverManager.registerDriver(driver);
        Connection connection = DriverManager.getConnection(url, user, passwd);
        System.out.println("第三种方式=" + connection
                          );
 }

然后因为jdbc的优化， /META-INF / service/java.sql.Driver 里面有注册，所以反射的那一段都可以不要，但是建议还是选择方法4.
进一步的优化方法4：我们可以自定义密码用户名(用户自己配置密码用户名 driver)

现在src目录下创建一个mysql.properties文件，然后输入这些内容进去

注意这里不要添加空格和双引号

然后函数写法如下。

public static void connect05() throws IOException, ClassNotFoundException, SQLException {
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\mysql.properties"));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String driver = properties.getProperty("driver");
    String url = properties.getProperty("url");
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println("方式5 " + connection);
}

Resultset

对于executeQuery（）来说，结尾关闭的文件还有resultSet.close();

遍历的方式1

底层的源码如下

statement/ mysql注入

statement存在一个sql注入问题。
sql注入黑客的原理，因为可以把最后一位变得永远是真，然后把所有数据都偷出来

输入数据

PreparedStatement_

层次结构图

使用它的好处

Scanner scanner = new Scanner(System.in);
      System.out.println("请输入用户的名字");
      String admin_name = scanner.nextLine();
      System.out.println("请输入用户的密码");
      String admin_pwd = scanner.nextLine();
// 导入数据
      Properties properties = new Properties();
      properties.load(new FileInputStream("src\\mysql.properties"));
      String user = properties.getProperty("user");
      String password = properties.getProperty("password");
      String driver = properties.getProperty("driver");
      String url = properties.getProperty("url");
// 反射机制， 注册数据
      Class.forName(driver);
// 执行sql语句
      String sql = "SELECT name, pwd from admin where name =? and pwd =?";
      Connection connection = DriverManager.getConnection(url, user, password);
      // ? 代表占位符
      PreparedStatement preparedStatement = connection.prepareStatement(sql);
      preparedStatement.setString(1, admin_name);
      preparedStatement.setString(2, admin_pwd);
// 这里现在不用填入sql了（statement的时候要）
      ResultSet resultSet = preparedStatement.executeQuery();
      if(resultSet.next()){
          System.out.println("登录成功");
      } 
      else{
          System.out.println("登录失败");
      }

在使用了preparedStatement的时候就不要在execute的时候这一行使用的括号中写sql
dml语句

String sql = "update admin set pwd = ? where name = ?";
Connection connection = DriverManager.getConnection(url, user, password);
// ? 代表占位符
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(2, admin_name);
preparedStatement.setString(1, admin_pwd);
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "成功": "失败");

jdbc api的复习

补充 setObject （占位符的索引，占位符的值）
ResultSet（结果集）
- next（）向下移动一行（一开始返回的是第一行的前面一个元素）
- previous（）向上移动一行
- getInt（）还可以通过字符来进行索引resultset.getInt(“id”) 通过列名来获取值

封装JDBCUtil

Alt + Insert: 可以快速调用常用指令

alt + Enter可以try-catch包括住

注意方法才可以抛出，代码块里面必须使用try-catch

package com.wjh.jdbc.utils;

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

public class JDBCUtils {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;
    // 在static 代码块去初始化
    static {
        try{
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        }catch(IOException e){
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url, user, password);
        }
        catch (SQLException e){
            throw new RuntimeException(e);
        }
    }
    public static void close(ResultSet set, Statement statement,Connection connection){
      try {
          if(set != null){
              set.close();
          }
          if(statement != null){
              statement.close();
          }
          if(connection != null){
              connection.close();
          }
      }catch(SQLException e){
        throw new RuntimeException(e);
      }

    }
}

JDBCUtil_DML

package com.wjh.jdbc.utils;

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

public class JDBCUtil_DML {
    public static void main(String[] args) {
        testDML();
    }
    public static void testDML(){
        // connect
        Connection connection = null;
        //create sql
        String sql = "update actor set name =? where id =?";
        PreparedStatement preparedStatement = null;
        try{
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"周星驰");
            preparedStatement.setInt(2, 4);
            preparedStatement.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            JDBCUtils.close(null,preparedStatement, connection);
        }

    }
}

JDBC的事务介绍

1 2	connection.setAutoCommit(false); // 开启了事务(开了之后就不会一执行sql，就提交）) connection.rollback();

我们可以把回滚操作，放到有一半的业务出现异常catch语句中的时候，当出事的时候，就回滚。

批处理操作

可以提高效率

preparedStatement.addBatch(); // 批量处理数据
// 如果要使用批量处理， url后面一定要加上?rewriteBatchedStatements=true
// 每当满1000条的时候就一起扔到服务器里面
// 速度大大的提高


for(int i = 0;i  < 5000;++i){
    preparedStatement.setString(1, "jack");
    preparedStatement.setString(2, "666");
    preparedStatement.addBatch(); // 批量处理数据
    if((i + 1)% 1000 == 0){
        preparedStatement.executeBatch();
        preparedStatement.clearbatch();
    }
}

事实上，他就是创建了一个arraylist数组，把所有数据存到里面，然后一次性执行

executeBatch（）函数，这样相比于正常的方法只用调用5次executeBatch

数据库连接池

传统连接

如果又多个人，连接一个数据库，会变得很慢

这是因为每次，连接数据库，然后关闭，都要验证ip地址，用户名和密码

而且，如果程序出现异常，不能正常关闭，将会导致数据库的内存泄露。最终导致重启数据库

开始新的连接

通过再缓存池里面放入一定数量的连接，每次只需从缓冲池里面取出一个，使用完以后再放回去

它的本质就是一个等待队列，再放回去的时候，不会释放对象。

数据库连接池的种类

连接池 - C3P0

// 创建一个数据源对象
comboxxxx = new comboxxxx;
// 通过配置文件mysql.properties 获取相关的信息。
//略
    
com.setdriverxxxclass(driver);
com.set(url);
com.set(user);
com.setpass(password);

// 设置初始化连接数
comxxx.poolsize(10);
comxxx.setMaxsize(50);
Connection connection = comxxx.getconnection();