devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL大总结_mysql总结

作者：IT小白 | 2024-03-21 04:22:37

赞

踩

mysql总结

目录

一、MySQL数据库基础

1，数据库的操作

1.1、显示当前数据库

1.2 、创建数据库

1.3、使用数据库

1.4、删除数据库

2、常用数据类型

2.1、数值类型

2.2、字符串类型

 2.3、日期类型

3、表的操作

3.1、创建表

3.2、查看表结构

3.4、删除表

4、总结

4.1、操作数据库

4.2、常用数据类型

4.3、操作表

5、综合练习

二、MySQL表的增删改查基础

1、CRUD

2、新增（Create）

2.1、单行数据 + 全列插入

2.2、多行数据 + 指定列插入

3、查询（Retrieve）

3.1、全列查询

3.2、查询字段为表达式

3.3、别名

3.4、去重：DISTINCT

3.5、排序：ORDER BY

3.6、条件查询：WHERE

3.7、分页查询：LIMIT

4、修改（Update）

5、删除（Delete）

三、MySQL表的增删改查进阶

1、数据库约束

1.1、约束类型

1.2、NULL约束

1.3、UNIQUE：唯一约束

1.4、DEFAULT：默认值约束

1.5、PRIMARY KEY：主键约束

1.6、FOREIGN KEY：外键约束

1.7、CHECK约束（了解）

2、表的设计

2.1、三大范式

3，新增

4、查询

4.1、聚合查询

4.2、联合查询

四、MySQL索引与事务

1、MySQL索引

1.1、概念

1.2、作用

1.3、使用场景

1.4、使用

1.5、MySQL面试 6 问

2、事务

2.1、什么是事务？ 

2.2、redo log 与 undo log介绍

2.3、事务的实现

五、MySQL中JDBC编程

1、数据库编程的必备条件

2、Java的数据库编程：JDBC

3、JDBC工作原理

4，环境搭建

5、MySQL中的JDBC编程

5.1，五步流程

5.2、添加信息

 5.3、查询信息

5.4、删除信息

MySQL是程序员必须要掌握的，其实语法结构相比于其他的数据结构、Java、C++来说应该算是简单的，只要多多练习，拿下它应该不成问题。本文是将我学过的面试笔试重点的知识进行归纳总结，如果你认为写的不错的话，求点赞，求收藏，感谢！！！

一、MySQL数据库基础

1，数据库的操作

1.1、显示当前数据库

show databases;

1.2 、创建数据库

语法：

  1. CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, 
  2. create_specification] ...]

示例：

创建名为 db_test1 的数据库

create database db_test1;

如果系统没有 db_test2 的数据库，则创建一个名叫 db_test2 的数据库，如果有则不创建 

create database if not db_test1;

1.3、使用数据库

进行表的操作前的重要的一步

use 数据库名;

1.4、删除数据库

说明: 数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除

DROP DATABASE [IF EXISTS] db_name;

2、常用数据类型

2.1、数值类型

分为整型和浮点型：

数值类型可以指定为无符号（unsigned），表示不取负数。1字节（bytes）= 8bit。 对于整型类型的范围： 1. 有符号范围：-2^（类型字节数*8-1）到2^（类型字节数*8-1）-1，如int是4字节，就 是-2^31到2^31-1 2. 无符号范围：0到2^（类型字节数*8）-1，如int就是2^32-1。

2.2、字符串类型

 2.3、日期类型

3、表的操作

use db_test;

3.1、创建表

语法：

  1. CREATE TABLE table_name (
  2.  field1 datatype,
  3.  field2 datatype,
  4.  field3 datatype
  5. );

示例：

  1. create table stu_test(
  2.     id int,
  3.     name varchar(20) comment '姓名',
  4.     password varchar(20) comment '密码',
  5.     age int,
  6.     sex varchar(1),
  7.     birthday timestamp,
  8.     amout decimal(13,2),
  9.     resume text
  10. );

3.2、查看表结构

desc 表名;

3.4、删除表

语法格式：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例：

  1. -- 删除 stu_test 表
  2. drop table stu_test;
  3. -- 如果存在 stu_test 表，则删除 stu_test 表
  4. drop table if exists stu_test;

4、总结

4.1、操作数据库

  1. -- 显示
  2. show databases;
  3. -- 创建
  4. create database xxx;
  5. -- 使用
  6. use xxx;
  7. -- 删除
  8. drop database xxx;

4.2、常用数据类型

  1. INT：整型
  2.  
  3. DECIMAL(M, D)：浮点数类型
  4.  
  5. VARCHAR(SIZE)：字符串类型
  6.  
  7. TIMESTAMP：日期类型

4.3、操作表

  1. -- 查看
  2. show 表;
  3. -- 创建
  4. create table 表名(
  5.  字段1 类型1,
  6.  字段2 类型2,
  7.  ...
  8. );
  9. -- 删除
  10. drop talbe 表名;

5、综合练习

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供 应商provider)

客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证 card_id)

购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

  1. create database shop;
  2. use shop;
  3. create table goods(
  4.     good_id int comment '商品编号',
  5.     good_name varchar(30) comment '商品名称',
  6.     unitprice int comment '单价',
  7.     category varchar(30) comment '商品类别',
  8.     provider varchar(64) comment '供应商名称'
  9. );
  10.  
  11. create table if not exists customer
  12. (
  13.    customer_id  int comment '客户编号',
  14.    name varchar(32) comment '客户姓名',
  15.    address  varchar(256) comment '客户地址',
  16.    email  varchar(64) comment '电子邮箱',
  17.    sex bit comment '性别',
  18.    card_id varchar(18) comment '身份证'
  19. );
  20.  
  21.  
  22. create table if not exists purchase
  23. (
  24.    order_id  int comment '订单号',
  25.    customer_id int comment '客户编号',
  26.    goods_id  int comment '商品编号', 
  27.    nums  int comment '购买数量'
  28. );

 

二、MySQL表的增删改查基础

1、CRUD

注释：在SQL中可以使用“--空格+描述”来表示注释说明 

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

2、新增（Create）

语法：

  1. INSERT [INTO] table_name
  2.  [(column [, column] ...)] 
  3.  VALUES (value_list) [, (value_list)] ...
  4.  
  5. value_list: value, [, value] ...

案例：

  1. create table student(
  2.     id int,
  3.     sn int comment '学号',
  4.     name varchar(20) comment '姓名',
  5.     qq_mail varchar(20) comment 'QQ邮箱'
  6. );

2.1、单行数据 + 全列插入

  1. insert into student values(1,1000,'擦洗',NUll);
  2. insert into student values(2,1001,'好还是', '111111');

2.2、多行数据 + 指定列插入

  1. insert into student(id,sn,name) values
  2. (3,1002,'李白'),
  3. (4,1003,'曹操');

3、查询（Retrieve）

语法：

  1. SELECT
  2.  [DISTINCT] {* | {column [, column] ...} 
  3.  [FROM table_name]
  4.  [WHERE ...]
  5.  [ORDER BY column [ASC | DESC], ...]
  6.  LIMIT ...

案例：

  1. create table exam_result(
  2.     id int,
  3.     name varchar(20),
  4.     chinese decimal(3,1),
  5.     math decimal(3,1),
  6.     english decimal(3,1)
  7. );

  1. insert into exam_result values
  2. (1,'唐三藏', 67, 98, 56),
  3. (2,'孙悟空', 87.5, 78, 77),
  4. (3,'猪悟能', 88, 98.5, 90),
  5. (4,'曹孟德', 82, 84, 67),
  6. (5,'刘玄德', 55.5, 85, 45),
  7. (6,'孙权', 70, 73, 78.5),
  8. (7,'宋公明', 75, 65, 30);

3.1、全列查询

select * from exam_result;

select id,name,chinese from exam_result;

select chinese,math,english from exam_result;

3.2、查询字段为表达式

select id,name,10 from exam_result;

select id,name,english + 10 from exam_result;

3.3、别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;
select id,name,chinese+math+english as total from exam_result;

3.4、去重：DISTINCT

select distinct math from exam_result;

3.5、排序：ORDER BY

语法：

  1. -- ASC 为升序（从小到大）
  2. -- DESC 为降序（从大到小）
  3. -- 默认为 ASC
  4. SELECT ... FROM table_name [WHERE ...] 
  5.  ORDER BY column [ASC|DESC], [...];

1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

 select name,chinese from exam_result order by chinese;

select name,chinese from exam_result order by chinese desc;

 3. 使用表达式及别名排序

select name,chinese+math+english total from exam_result order by total desc;
select name,chinese+math+english from exam_result order by chinese+math+english desc;

 4. 可以对多个字段进行排序，排序优先级随书写顺序

  1. -- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
  2. select name,math,english,chinese from exam_result order by math desc,chinese,english;

3.6、条件查询：WHERE

比较运算符：

逻辑运算符：

注：

1. WHERE条件可以使用表达式，但不能使用别名。

2. AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

基本查询：

  1. -- 查询英语不及格的同学及英语成绩 ( < 60 )
  2. select name, english from exam_result where english < 60;

  1. -- 查询语文成绩好于英语成绩的同学
  2. select name,chinese,english from exam_result where chinese > english;

  1. -- 查询总分在 200 分以下的同学
  2. select name,chinese+math+english total from exam_result where chinese+math+english < 200;

AND与OR：

select * from exam_result where chinese > 80 and english > 80;

select * from exam_result where chinese > 80 or english > 80;

select * from exam_result where chinese > 80 or math > 70 and english > 80;
select * from exam_result where (chinese > 80 or math > 70) and english > 80;

范围查询：

1. BETWEEN ... AND ...

  1. -- 查询语文成绩在 [80, 90] 分的同学及语文成绩
  2. select name,chinese from exam_result where chinese between 80 and 90;

2. IN

  1. -- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
  2. select name,math from exam_result where math in(58,59,98,99);

模糊查询：LIKE

  1. -- 匹配到孙悟空、孙权
  2. select name from exam_result where name like '孙%';

  1. --_匹配严格的一个任意字符 匹配到孙权
  2. select name from exam_result where name like '孙_';

3.7、分页查询：LIMIT

语法：

  1. -- 起始下标为 0
  2. -- 从 0 开始，筛选 n 条结果
  3. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
  4. -- 从 s 开始，筛选 n 条结果
  5. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
  6. -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
  7. SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

案例：按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

  1. -- 第 1 页
  2. select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;

  1. -- 第 2 页
  2. select id,name,math,english,chinese from exam_result order by id limit 3 offset 3;

4、修改（Update）

语法：

  1. UPDATE table_name SET column = expr [, column = expr ...]
  2.  [WHERE ...] [ORDER BY ...] [LIMIT ...]

案例：

update exam_result set math = 80 where name = '孙悟空';
  1. -- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
  2. update exam_result set math = 60,chinese = 70 where name = '曹孟德';

5、删除（Delete）

 语法：

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
  1. -- 删除孙悟空同学的考试成绩
  2. delete from exam_result where name = '孙悟空';

三、MySQL表的增删改查进阶

1、数据库约束

1.1、约束类型

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

DEFAULT - 规定没有给列赋值时的默认值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。

1.2、NULL约束

创建表时，可以指定某列不为空：

  1. drop table if exists student;
  2. create table student(
  3.     id int not NULL,
  4.     sn int,
  5.     name varchar(20),
  6.     qq_mail varchar(10)
  7. );

1.3、UNIQUE：唯一约束

指定sn列为唯一的、不重复的：

  1. drop table if exists student;
  2. create table student(
  3.     id int not NULL,
  4.     sn int unique,
  5.     name varchar(20),
  6.     qq_mail varchar(10)
  7. );

1.4、DEFAULT：默认值约束

指定插入数据时，name列为空，默认值unkown：

  1. drop table if exists student;
  2. create table student(
  3.     id int not NULL,
  4.     sn int unique,
  5.     name varchar(20) default 'unkown',
  6.     qq_mail varchar(10)
  7. );

1.5、PRIMARY KEY：主键约束

指定id列为主键：

  1. drop table if exists student;
  2. create table student(
  3.     id int not NULL primary key,
  4.     sn int unique,
  5.     name varchar(20) default 'unkown',
  6.     qq_mail varchar(10)
  7. );

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大 值+1。

  1. -- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
  2. id int primary key auto_increment,

1.6、FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键，语法：

foreign key (字段名) references 主表(列)

创建班级表classes，id为主键：

  1. -- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
  2. drop table if exists classes;
  3. create table classes(
  4.     id int primary key auto_increment,
  5.     name varchar(20),
  6.     `desc` varchar(100)
  7. );

创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键， classes_id为外键，关联班级表id

  1. drop table if exists student;
  2. create table student(
  3.     id int primary key auto_increment,
  4.     sn int unique,
  5.     name varchar(20) default 'unkown',
  6.     qq_mail varchar(20),
  7.     classes_id int,
  8.     foreign key (classes_id) references classes(id)
  9. );

1.7、CHECK约束（了解）

MySQL使用时不报错，但忽略该约束：

  1. drop table if exists test_user;
  2. create table test_user(
  3.     id int,
  4.     name varchar(20),
  5.     sex varchar(1),
  6.     check(sex = '男' or sex = '女')
  7. );

2、表的设计

2.1、三大范式

一对一

 一对多

 多对多

  创建课程表

  1. drop table if exists test_user;
  2. create table test_user(
  3.     id int,
  4.     name varchar(20),
  5.     sex varchar(1),
  6.     check(sex = '男' or sex = '女')
  7. );

创建学生课程中间表，考试成绩表

  1. drop table if exists score(
  2.     id int primary key auto_increment,
  3.     score decimal(3,1),
  4.     student_id,
  5.     course_id,
  6.     foreign key (student_id) references student(id),
  7.     foreign key (course_id) references course(id)
  8. );

3，新增

插入查询结果

语法：

insert  into table_name column select...

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的 学生数据复制进来，可以复制的字段为name、qq_mail

  1. -- 创建用户表
  2. DROP TABLE IF EXISTS test_user;
  3. CREATE TABLE test_user (
  4. id INT primary key auto_increment,
  5. name VARCHAR(20) comment '姓名',
  6. age INT comment '年龄',
  7. email VARCHAR(20) comment '邮箱',
  8. sex varchar(1) comment '性别',
  9. mobile varchar(20) comment '手机号'
  10. );
  11.  
  12. --把学生表中的所有数据复制到用户表
  13. insert into test_user(name,email) select name,qq_mail from student;

4、查询

4.1、聚合查询

聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

 COUNT

  1. -- 统计班级共有多少同学
  2. select count(*) from student;
  3. select count(0) from student;
  1. -- 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
  2. select count(qq_mail) from student;

SUM

  1. -- 统计数学成绩总分
  2. select sum(math) from exam_result;
  1. -- 不及格 < 60 的总分，没有结果，返回 NULL
  2. select sum(math) from exam_result where math < 60;

AVG

  1. -- 统计平均总分
  2. select avg(chinese + math + english) 平均总分 from exam_result;

MAX

  1. -- 返回英语最高分
  2. select max(english) from exam_result;

MIN

  1. -- 返回 > 70 分以上的数学最低分
  2. select min(math) from exam_result where math > 70;

①GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查 询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函 数中。

select column1, sum(column2), .. from table group by column1,column3;

准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

  1. create table emp(
  2. id int primary key auto_increment,
  3. name varchar(20) not null,
  4. role varchar(20) not null,
  5. salary numeric(11,2)
  6. );
  1. insert into emp(name, role, salary) values
  2. ('李白','服务员', 1000.20),
  3. ('杜甫','游戏陪玩', 2000.99),
  4. ('孙悟空','游戏角色', 999.11),
  5. ('猪无能','游戏角色', 333.5),
  6. ('沙和尚','游戏角色', 700.33),
  7. ('隔壁老王','董事长', 12000.66);

查询每个角色的最高工资、最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

②HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) < 1500;

4.2、联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

  测试示例：

  1. insert into classes(name, `desc`) values
  2. ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
  3. ('中文系2019级3班','学习了中国传统文学'),
  4. ('自动化2019级5班','学习了机械自动化');
  5.  
  6. create table classes(
  7.     name varchar(20),
  8.     `desc` varchar(200)
  9. );
  1. create table student(
  2.     id int,
  3.     sn varchar(6),
  4.     name varchar(20),
  5.     qq_mail varchar(20),
  6.     classes_id int
  7. );
  8.  
  9. insert into student(id,sn, name, qq_mail, classes_id) values
  10. (1,'09982','黑旋风李逵','xuanfeng@qq.com',1),
  11. (2,'00835','菩提老祖',null,1),
  12. (3,'00391','白素贞',null,1),
  13. (4,'00031','许仙','xuxian@qq.com',1),
  14. (5,'00054','不想毕业',null,1),
  15. (6,'51234','好好说话','say@qq.com',2),
  16. (7,'83223','tellme',null,2),
  17. (8,'09527','老外学中文','foreigner@qq.com',2);
  1. create table course(
  2.     id int primary key auto_increment,
  3.     name varchar(20)
  4. );
  5.  
  6. insert into course(id,name) values
  7. (1,'Java'),(2,'中国传统文化'),(3,'计算机原理'),(4,'语文'),(5,'高阶数学'),(6,'英文');
  8.  
  1. create table score(
  2.     score decimal(3,1),
  3.     student_id int,
  4.     course_id int
  5. );
  6.  
  7. insert into score(score, student_id, course_id) values
  8. -- 黑旋风李逵
  9. (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
  10. -- 菩提老祖
  11. (60, 2, 1),(59.5, 2, 5),
  12. -- 白素贞
  13. (33, 3, 1),(68, 3, 3),(99, 3, 5),
  14. -- 许仙
  15. (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
  16. -- 不想毕业
  17. (81, 5, 1),(37, 5, 5),
  18. -- 好好说话
  19. (56, 6, 2),(43, 6, 4),(79, 6, 6),
  20. -- tellme
  21. (80, 7, 2),(92, 7, 6);

内连接

语法：

  1. select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
  2. select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

案例：

（1）查询“许仙”同学的 成绩

  1. select sco.score from student stu inner join score sco on stu.id=sco.student_id
  2. and stu.name='许仙';
  3. -- 或者
  4. select sco.score from student stu, score sco where stu.id=sco.student_id and
  5. stu.name='许仙';

初学者建议一步一步来

select student.id,student.name ,score.student_id,score.score from student,score;

  1. select student.id,student.name ,score.student_id,score.score from 
  2. student,score where student.id = score.student_id;

  1. select student.id,student.name ,score.student_id,score.score from
  2. student,score where student.id = score.student_id and student.name = '许仙';

（2）查询所有同学的总成绩，及同学的个人信息：

  1. SELECT
  2. stu.sn,
  3. stu.NAME,
  4. stu.qq_mail,
  5. sum( sco.score ) 
  6. FROM
  7. student stu
  8. JOIN score sco ON stu.id = sco.student_id
  9. GROUP BY
  10. sco.student_id;

select student.id ,student.name,score.student_id,score.score from student,score;

  1. select student.id ,student.name,score.student_id,score.score from student,score
  2. where student.id = score.student_id;

  1. select student.id ,student.name,score.student_id,score.score from student,score
  2. where student.id = score.student_id group by student.id;

  1. select student.id ,student.name,sum(score.score) from student,score
  2. where student.id = score.student_id group by student.id;

 （3）查询所有同学的成绩，及同学的个人信息

  1. select student.id,student.name,course.id,course.name,score.student_id,score.score from 
  2. student,course,score;

  1. select student.id,student.name,course.id,course.name,score.student_id,score.score from 
  2. student,course,score where student.id = score.student_id;

  1. select student.id,student.name,course.id,course.name,score.student_id,score.score from 
  2. student,course,score where student.id = score.student_id and course.id = score.course_id;

  1. select student.name,course.name,score.score from 
  2. student,course,score where student.id = score.student_id and course.id = score.course_id;

自连接

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

  1. select * from course where name = 'java';
  2. select * from course where name = '计算机原理';

  1. select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id  
  2. from score s1,score s2;

  1. select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id  
  2. from score s1,score s2 where s1.student_id = s2.student_id;

  1. select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id  
  2. from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id
  3. = 3 and s2.course_id = 1;

  1. select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.course_id  
  2. from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id
  3. = 3 and s2.course_id = 1 and s1.score > s2.score;

子查询

查询与“不想毕业” 同学的同班同学

select classes_id from student where name = '不想毕业';

select name from student where classes_id = (select classes_id from student where name = '不想毕业');

 查询“语文”或“英文”课程的成绩信息

select id from course where name = '语文' or name = '英文';

select * from score where course_id in (select id from course where name = '语文' or name = '英文');

合并查询

查询id小于3，或者名字为“英文”的课程

  1. select * from course where id < 3 or name = '英文';
  2.  
  3. select * from course where id < 3 union select * from course where name = '英文';

四、MySQL索引与事务

1、MySQL索引

1.1、概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构，在 MySQL 中也被称为 Key。

通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

注意事项：有序性是因为一切二分法查找法都要求数据已经是排好顺序的。如果把索引看做 key（虽然 key 数据也是来自于表单中一行记录的某些字段值），那么 value 在 MyISAM 中就是记录的在存储文件中的地址，而在 InnoDB 中 value 直接就是对应的一行数据。

假设我们有一张数据表 workers(员工表)，该表有三个字段（列）,分别是name、age 和address。假设表works有上万行数据，现在需要从这个表中查找出所有名字是‘ZhangSan’的雇员信息，你会快速的写出SQL语句：

select name,age,address from workers where name='ZhangSan'

如果数据库还没有索引这个东西，一旦我们运行这个SQL查询，查找名字为ZhangSan的雇员的过程中，究竟会发生什么？数据库不得不在workes表中的每一行查找并确定雇员的名字（name）是否为‘ZhangSan’。

由于我们想要得到每一个名字为ZhangSan的雇员信息，在查询到第一个符合条件的行后，不能止查询，因为可能还有其他符合条件的行，所以必须一行一行的查找直到最后一行——这就意味数据库不得不检查上万行数据才能找到所有名字为ZhangSan的雇员。这就是所谓的全表扫描，显然这种模式效率太慢。

使用索引的全部意义就是：通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（定义真特么拗口）。大白话意思是索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请牢记这一点：索引是一种数据结构。

1.2、作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。索引所起的作用类似书籍目录，可用于快速定位、检索数据。索引对于提高数据库的性能有很大的帮助。

1.3、使用场景

索引的本质实际上还是存储在磁盘上的数据结构，它可以有的存储结构：

  • 二叉搜索树；

  • 红黑树；

  • Hash 表；

  • B-Tree

  • B+Tree

其中 MySQL 的 InnoDB 支持 B+Tree 以及 Hash 表，下面会具体分析各个数据结构的区别。

哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效生。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在 MySQL 中，只有 Memory 引擎显式支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

下面来看一个例子。假设有如下表：

  1. create table tes(
  2.  fname VARCHAR(50) NOT NULL,
  3.   lname VARCHAR(50) NOT NULL,
  4.   KEY USING HASH(fname)
  5. ) ENGINE = MEMORY;

然后再填入相关数据后，表格有如下数据：

 假设索引使用假想的哈希函数 f()，它返回下面的值(都是示例数据，非真实数据) :

  • f(‘a’) = 23

  • f(‘b’) = 74

  • f(‘p’) = 87

  • f(‘v’) = 24

则哈希索引的数据结构如下：

 注意每个槽的编号是顺序的，但是数据行不是。

下面使用 hash 索引字段进行查询，有：

select lname from tes where fname = 'p';

 

其分为如下的步骤：

MSQL 先计算 ‘p’ 的哈希值；根据哈希值进行寻找对应的地址指针，意味 hash 槽是有序的，因此查询效率很高；读取对应指针上的数据是否为 ‘p’，是则返回，因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。

哈希索引只支持等值比较查询，包括 =、IN()、<=>；不支持任何范围查询，例如 WHEREprice>100。

访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

TREE

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

数据量较大，且经常对这些列进行条件查询。

该数据库表的插入操作，及对这些列的修改操作频率较低。

索引会占用额外的磁盘空间。

在没有GUI工具的情况下，可以使用以下命令查看索引：

上述ad_article表中有两个索引，Key_name中有显示:

-PRIMARY主键索引，Seq_in_index索引序号为1，从1开始，Collation为“A”表示升序（或NULL无分类），对应字段是id

-idx_cid是自建索引，由cid、available、id三个字段组成，分别对应序号1,2,3Index_type=BTREE这块内容很多人不懂其意思，其实通过GUI工具创建索引时也会有BTREE 的显示，先着重了解一下。

在计算机数据结构（不懂数据结构的自行充电）体系中，为了加速查找的速度，常见的数据结构有两种：

-Hash哈希结构，例如Java中的HashMap，这种数据组织结构可以让查询/插入/修改/删除的平均时间复杂度都为O(1);

-Tree 树 结构 ， 这种数据组织结构可以让查询/插入/修改/删除的平均时间复杂度都为O(log(n));不管读还是写，Hash这种类型比Tree树这种类型都要更快一些，那为什么MySQL的开发者既使用Hash类型做为索引，又使用了BTREE呢？

确实用HASH索引更快，因为每次都只查询一条信息（重名的雇员姓名也才几条而已），但实际上业务对于SQL的应用场景是：

-orderby 需要排个序

-groupby 还要分个组

-还要比较大小 大于或小于等等

这种情况下如果继续用HASH类型做索引结构，其时间复杂度会从O(1)直接退化为O(n)，相当于全表扫描了，而Tree的特性保证了不管是哪种操作，依然能够保持O(log(n))的高效率。那MySQL中的BTREE和TREE又有啥联系与区别呢？先来看看传统的二叉树：

二叉树是大家熟知的一种树，用它来做索引行不行，可以是可以，但有几个问题：

-如果索引数据很多，树的层次会很高（只有左右两个子节点），数据量大时查询还是会慢

-二叉树每个节点只存储一个记录，一次查询在树上找的时候花费磁盘IO次数较多

所以它并不适合直接拿来做索引存储，算法设计人员在二叉树的基础之上进行了变种，引入了

BTREE

BTREE

如上图可知BTREE有以下特点：

-不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快

-叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据

-通过中序遍历，可以访问树上所有节点

BTREE被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”，其设计逻辑是这样的：

-内存读写快，磁盘读写慢，而且慢很多

-磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载一些看起

来是冗余的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘读写，提高效率（通常，一页数据是4K）

-局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO效能

B+TREE

早先的MySQL就是使用的BTREE做为索引的数据结构，随着时间推移，B树发生了较多的变种，其中最常见的就是B+TREE变种，现在MySQL用的就是这种，示意如下：

B+TREE改进点及优势所在：

-仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样

-叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到

-范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）

-叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储

-非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

可以来初步计算一下：假设key、子树节点指针均占用4B，则B树一个节点占用4 + 4 = 8B，一页页面大小4KB，则N = 4 * 1024 / 8B = 512，一个512叉的B树，1000w的数据，深度最大 log(512/2)(10^7) 约等于4。对比二叉树如AVL的深度为log(2)(10^7) 约为24，相差了5倍以上。假如一个节点大小是4KB，一个KEY有8字节，一页可以存4000/8=500个KEY，根据N叉树特点,就算一层500叉节点，则：

第一层树：1个节点，1*500KEY , 大小4K

第二层树：500节点 500*500=25万个KEY，500*4K=2M

第三层树：500 * 500节点 500*500*500=1.2亿KEY，500*500*4K=1G

如果没算错，1G空间，只用三层树结构，可以存1.2亿行数据的KEY。所以B+TREE索引只用占用很少的内存空间，却大大提升了查询效率（不论是单个查询、范围查询还是有序性查询），并且还减少了磁盘读写，所以好的算法与数据结构是可以省钱的。

1.4、使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

查看索引

show index from 表名;

案例：查看学生表已有的索引

show index from student;

创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

案例：创建班级表中，name字段的索引

create index idx_classes_name on classes(name);

①创建主键索引

主键索引的定义：InnoDB 中的表单数据本身就要创建为一棵 B+ 树，而这棵排序节点用到的索引就被称为主键索引。

只要有主键，那么主键索引根据的就是主键，我们在创建表和后续修改时都能够通过指定主键来确

定主键索引，如下：

②创建表同时设置主键

  1. create table teacher(
  2. id int(10) auto_increment,
  3. name varchar(20),
  4. age int(10),
  5. phone varchar(11),
  6. primary key (id));--主键设置

③单独设置主键

alter table teacher add primary key (id);

④创建唯一索引

  1. create table teacher(
  2. id int(10) auto_increment,
  3. name varchar(20),
  4. age int(10),
  5. phone varchar(11),
  6. primary key (id),
  7. unique index idx_phone(phone(11)));--唯一索引
  8.  
  9. --单独建唯一索引
  10. create unique index idx_phone on teacher(phone(11));
  11. --删除唯一索引
  12. drop index idexName on tableName;
  13. --修改建唯一索引
  14. alter table teacher add unique idx_phone (phone(11));

⑤创建普通索引

  1. create table teacher(
  2. id int(10) auto_increment,
  3. name varchar(20),
  4. age int(10),
  5. phone varchar(11),
  6. primary key (id),
  7. index idx_phone(phone(11)));
  8. --单独建普通索引
  9. create index idx_phone on teacher(phone(11));
  10. --修改普通索引
  11. alter table teacher add index idx_phone (phone(11));

⑥创建联合索引

  1. create table teacher(
  2. id int(10) auto_increment,
  3. name varchar(20),
  4. phone varchar(11),
  5. primary key (id),
  6. index idx_name_phone (name(20),phone(11)));
  7. --单独建组合索引
  8. create index idx_name_phone on teacher (name(20),phone(11));
  9. --修改组合索引
  10. alter table teacher add index idx_name_phone (name(20),phone(11));

删除索引

drop index 索引名 on 表名;

案例：删除班级表中name字段的索引

drop index idx_classes_name on classes;

1.5、MySQL面试 6 问

①索引是什么？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

②索引有哪些优缺点？

索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

  • 空间方面：索引需要占物理空间

③说一说索引的底层实现？

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

B-Tree索引

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+tree性质：

n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依

关键字的大小自小而大顺序链接。

所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

B+ 树中，数据对象的插入和删除仅在叶节点上进行。

B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

④为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree：

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

虽然可以快速定位，但是没有顺序，IO复杂度高；

基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；

适合等值查询，如=、in()、<=>，不支持范围查询 ；

因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；

Hash索引在查询等值时非常快 ；

因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

⑤如何创建索引？

1、 在执行CREATE TABLE时创建索引

  1. CREATE TABLE user_index2 (
  2.  id INT auto_increment PRIMARY KEY,
  3.  first_name VARCHAR (16),
  4.  last_name VARCHAR (16),
  5.  id_card VARCHAR (18),
  6.  information text,
  7.  KEY name (first_name, last_name),
  8.  FULLTEXT KEY (information),
  9.  UNIQUE KEY (id_card)
  10. );

2、 使用ALTER TABLE命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

3、 使用CREATE INDEX命令创建。

CREATE INDEX index_name ON table_name (column_list);

⑥创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

2、事务

2.1、什么是事务？ 

事务是一组原子性的sql语句，或者说是一个独立的工作单元。事务有四个特性，原子(Atomicity),

一致性(Consistency),隔离型(Isolation)以及持久性(Durability)

2.2、redo log 与 undo log介绍

①redo log

redo log叫做重做日志，用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中。

redo log 有什么作用？

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

总结：

redo log是用来恢复数据的用于保障，已提交事务的持久化特性

②undo log

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

undo log 有什么作用？undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。

总结：

undo log是用来回滚数据的用于保障未提交事务的原子性

2.3、事务的实现

①原子性的实现

什么是原子性：

一个事务必须被视为不可分割的最小工作单位，一个事务中的所有操作要么全部成功提交，要么全部失败回滚，对于一个事务来说不可能只执行其中的部分操作，这就是事务的原子性。

undo log 的生成

每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上

所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。

根据undo log 进行回滚回滚操作就是要还原到原来的状态，undo log记录了数据被修改前的信息以及新增和被删除的数据信息，根据undo log生成回滚语句，比如：

(1) 如果在回滚日志里有新增数据记录，则生成删除该条的语句

(2) 如果在回滚日志里有删除数据记录，则生成生成该条的语句

(3) 如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句

②持久性的实现

事务一旦提交，其所作做的修改会永久保存到数据库中，此时即使系统崩溃修改的数据也不会丢失。

MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO,然而即使是使用SSD磁盘IO也是非常消耗性能的。为此，为了提升性能InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用：

读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；

写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中

③隔离性实现

隔离性是事务ACID特性里最复杂的一个。在SQL标准里定义了四种隔离级别，每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是不可见的。级别越低的隔离级别可以执行越高的并发，但同时实现复杂度以及开销也越大。

Mysql 隔离级别有以下四种（级别由低到高）：

READ UNCOMMITED (未提交读)

READ COMMITED (提交读)

REPEATABLE READ (可重复读)

SERIALIZABLE (可重复读)

只要彻底理解了隔离级别以及他的实现原理就相当于理解了ACID里的隔离型。前面说过原子性，隔离性，持久性的目的都是为了要做到一致性，但隔离型跟其他两个有所区别，原子性和持久性是为了要实现数据的可性保障靠，比如要做到宕机后的恢复，以及错误后的回滚。

五、MySQL中JDBC编程

1、数据库编程的必备条件

编程语言，如Java，C、C++、Python等 数据库，如Oracle，MySQL，SQL Server等 数据库驱动包：不同的数据库，对应不同的编程语言提供了不同的数据库驱动包，如：MySQL提 供了Java的驱动包mysql-connector-java，需要基于Java操作MySQL即需要该驱动包。同样的， 要基于Java操作Oracle数据库则需要Oracle的数据库驱动包ojdbc。

2、Java的数据库编程：JDBC

JDBC，即Java Database Connectivity，java数据库连接。是一种用于执行SQL语句的Java API，它是 Java中的数据库连接规范。这个API由 java.sql.*,javax.sql.* 包中的一些类和接口组成，它为JJava 开发人员操作数据库提供了一个标准的API，可以为多种关系数据库提供统一访问.

3、JDBC工作原理

JDBC 为多种关系数据库提供了统一访问方式，作为特定厂商数据库访问API的一种高级抽象，它主要包 含一些通用的接口类

JDBC访问数据库层次结构:

4，环境搭建

首先在电脑服务当中查看自己MySQL的版本，然后进入maven仓库

 

 因为我自己的版本是5.0之后的，所以我选择5.1.47，大版本要一致

 下载jar即可，记住，jar包不能解压缩

 接下来就在idea自己在根目录下创建文件夹，然后导入jar包

 

 

 

  没有报错一直OK下去就没有啥问题，说明导入成功

5、MySQL中的JDBC编程

5.1，五步流程

建立数据库连接

  1. //MySQL数据连接的URL参数格式如下：
  2. jdbc:mysql://服务器地址:端口/数据库名?参数名=参数值
  1. // 加载JDBC驱动程序：反射，这样调用初始化com.mysql.jdbc.Driver类，即将该类加载到JVM方法
  2. 区，并执行该类的静态方法块、静态属性。
  3. Class.forName("com.mysql.jdbc.Driver");
  4. // 创建数据库连接
  5. Connection connection =
  6. DriverManager.getConnection("jdbc:mysql://localhost:3306/test?
  7. user=root&password=root&useUnicode=true&characterEncoding=UTF-8");

创建操作命令（Statement）

Statement statement = connection.createStatement();

执行SQL语句

  1. ResultSet resultSet= statement.executeQuery(
  2.                 "select id, sn, name, qq_mail, classes_id from student");

处理结果集

  1. while (resultSet.next()) {
  2.             int id = resultSet.getInt("id");
  3.             String sn = resultSet.getString("sn");
  4.             String name = resultSet.getString("name");
  5.             int classesId = resultSet.getInt("classes_id");
  6.             System.out.println(String.format("Student: id=%d, sn=%s, name=%s, 
  7. classesId=%s", id, sn, name, classesId));
  8.        }

释放资源（关闭结果集，命令，连接）

  1. //关闭结果集
  2. if (resultSet != null) {
  3.     try {
  4.         resultSet.close();
  5.    } catch (SQLException e) {
  6.         e.printStackTrace();
  7.    }
  8. }
  9. //关闭命令
  10. if (statement != null) {
  11.     try {
  12.         statement.close();
  13.    } catch (SQLException e) {
  14.         e.printStackTrace();
  15.    }
  16. }
  17. //关闭连接命令
  18. if (connection != null) {
  19.     try {
  20.         connection.close();
  21.    } catch (SQLException e) {
  22.         e.printStackTrace();
  23.    }
  24. }

5.2、添加信息

首先创建一个数据库并且创建一个表

 create database java122;
 create table text(id int,name varchar(5),class_id int);
  1. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  2. import javax.sql.DataSource;
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.SQLException;
  6.  
  7.  
  8. public class TextJDBC {
  9.     //DataSource
  10.     //Connection
  11.     //PrepareStatement
  12.  
  13.  
  14.     public static void main(String[] args) throws SQLException{
  15.         //1、创建DataSource对象
  16.          DataSource dataSource = new MysqlDataSource();
  17.         //设置相关内容
  18.         //URL User password
  19.         //向下转型                     访问数据库   协议名        ip地址 要访问那个地址
  20.         ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false");
  21.         ((MysqlDataSource) dataSource).setUser("root");
  22.         ((MysqlDataSource) dataSource).setPassword("180210");
  23.         
  24.         //2、和数据库连接.进行后续连接
  25.         //connect生命周期较短
  26.         Connection connection = dataSource.getConnection();
  27.         
  28.         //3、拼装SQL语句
  29.         int id = 1;
  30.         String name = "曹操";
  31.         int class_id = 10;
  32.         //?是一个占位符，可以把一个具体的变量的值替换到？
  33.         String sql = "insert into text values(?,?,?)";
  34.         PreparedStatement statement = connection.prepareStatement(sql);
  35.         //1 2 3相当与？的下标
  36.         statement.setInt(1,id);
  37.         statement.setString(2,name);
  38.         statement.setInt(3,class_id);
  39.         System.out.println("statement:" + statement);
  40.  
  41.         //4、执行SQL语句
  42.         int ret = statement.executeUpdate();
  43.         System.out.println("ret:" + ret);
  44.  
  45.         //5、关闭相关资源
  46.         //后创建的先释放，顺序不能错
  47.         statement.close();
  48.         connection.close();
  49.  
  50.     }
  51. }

 5.3、查询信息

  1. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  2.  
  3. import javax.sql.DataSource;
  4. import javax.xml.transform.Source;
  5. import java.sql.Connection;
  6. import java.sql.PreparedStatement;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.util.Scanner;
  10.  
  11. public class Text1 {
  12.  
  13.  
  14.     public static void main(String[] args) throws SQLException {
  15.  
  16.         //1，创建实列
  17.         DataSource dataSource = new MysqlDataSource();
  18.         ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false");
  19.         ((MysqlDataSource)dataSource).setUser("root");
  20.         ((MysqlDataSource)dataSource).setPassword("180210");
  21.  
  22.         //2，数据库连接
  23.         Connection connection = dataSource.getConnection();
  24.  
  25.         //3,构造SQL语句
  26.  
  27.         String sql ="select * from text";
  28.         PreparedStatement statement = connection.prepareStatement(sql);
  29.  
  30.         //4,执行SQL语句
  31.         ResultSet resultSet = statement.executeQuery();
  32.         while (resultSet.next()){
  33.             int id = resultSet.getInt("id");
  34.             String name = resultSet.getString("name");
  35.             int class_id = resultSet.getInt("class_id");
  36.             System.out.println("id: " + id  + "name: " + name  + "class_id: " + class_id);
  37.         }
  38.  
  39.         //5,关闭相关资源
  40.         resultSet.close();
  41.         statement.close();
  42.         connection.close();
  43.     }
  44. }

5.4、删除信息

  1. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  2.  
  3. import javax.sql.DataSource;
  4. import java.sql.Connection;
  5. import java.sql.PreparedStatement;
  6. import java.sql.SQLException;
  7. import java.util.Scanner;
  8.  
  9. public class Textur2 {
  10.  
  11.  
  12.     public static void main(String[] args) throws SQLException {
  13.  
  14.  
  15.         Scanner scanner = new Scanner(System.in);
  16.         System.out.println("请输入要删除学生的姓名：");
  17.         String name = scanner.next();
  18.         //1，创建实列
  19.         DataSource dataSource = new MysqlDataSource();
  20.         ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false");
  21.         ((MysqlDataSource) dataSource).setUser("root");
  22.         ((MysqlDataSource) dataSource).setPassword("180210");
  23.  
  24.         //2，数据库连接
  25.         Connection connection = dataSource.getConnection();
  26.  
  27.         //3,构造SQL语句
  28.         String sql = "delete from text where name = ?";
  29.         PreparedStatement statement = connection.prepareStatement(sql);
  30.         statement.setString(1,name);
  31.  
  32.         //4,执行SQL
  33.         int ret = statement.executeUpdate();
  34.         if (ret == 1){
  35.             System.out.println("删除成功");
  36.         }else {
  37.             System.out.println("删除失败");
  38.         }
  39.  
  40.         //5，关闭资源
  41.         statement.close();
  42.         connection.close();
  43.  
  44.  
  45.     }
  46. }

5.5、修改信息

  1. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  2.  
  3. import javax.sql.DataSource;
  4. import java.sql.Connection;
  5. import java.sql.PreparedStatement;
  6. import java.sql.SQLException;
  7. import java.util.Scanner;
  8.  
  9. public class Text4 {
  10.  
  11.  
  12.     public static void main(String[] args) throws SQLException {
  13.  
  14.  
  15.         //修改信息
  16.         Scanner scanner = new Scanner(System.in);
  17.         System.out.println("请输入学生的id：");
  18.         int id = scanner.nextInt();
  19.         System.out.println("请输入修改学生姓名：");
  20.         String name = scanner.next();
  21.  
  22.         //1，创建实列
  23.         DataSource dataSource = new MysqlDataSource();
  24.         ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java122?characterEncoding=utf-8&useSSL=false");
  25.         ((MysqlDataSource) dataSource).setUser("root");
  26.         ((MysqlDataSource) dataSource).setPassword("180210");
  27.  
  28.         //2，数据库连接
  29.         Connection connection = dataSource.getConnection();
  30.  
  31.         //3,拼装SQL
  32.         String sql = "update text set name = ? where id = ?";
  33.         PreparedStatement statement = connection.prepareStatement(sql);
  34.         statement.setString(1,name);
  35.         statement.setInt(2,id);
  36.         //4,执行SQL
  37.         int set = statement.executeUpdate();
  38.         if (set == 1){
  39.             System.out.println("修改成功");
  40.         }else {
  41.             System.out.println("修改失败");
  42.         }
  43.  
  44.         //5，关闭资源
  45.         statement.close();
  46.         connection.close();
  47.     }
  48. }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/278396
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号