目录
3.5、MySQL表的增查改删(CRUD——create、retrieve、update、delete)
1、数据库的操作
1.1、查看当前数据库
show databases;
三个注意点:
- SQL里面不区分大小写
- 注意语句要以英文分号;结尾
- 注意database后面有s
1.2、创建数据库
create database [if not exists] 数据库名;
两个注意点:
- database后面没有s
- [ ] 中的内容是可选项(可以不写)
1.3、使用数据库
use 数据库名;
1.4、删除数据库
drop database 数据库名;
2、常用的数据类型
2.1、数值类型
数据类型 | 大小(单位:字节) | 描述 |
bit[M] | M指定位数,默认为1 | 二进制数,M范围从1到64,存储数值范围从0到2^M-1 |
tinyint | 1 | 十分小的数据 |
smallint | 2 | 较小的数据 |
int | 4 | 标准的整数 |
bigint | 8 | 较大的数据 |
float(M,D) | 4 | 单精度,M指定长度,D指定小数位数(会发生精度丢失) |
double(M,D) | 8 | 双精度 |
decimal(M,D) | M/D最大值+2 | 双精度,M指定长度,D表示小数点位数(精确数值) |
numeric(M/D) | M/D最大值+2 | 双精度,M指定长度,D表示小数点位数(精确数值) |
2.2、字符串
数据类型 | 大小(单位:字节) | 描述 |
char | 0-255 | 字符串固定大小 |
varchar(SIZE) | 0-65535 | 可变长度字符串 |
text | 0-65535 | 长文本数据 |
mediumtext | 0-16777215 | 中等长度文本数据 |
blob | 0-65535 | 二进制形式的长文本数据 |
2.3、日期类型
数据类型 | 大小(单位:字节) | 描述 | 格式 |
date | 4 | 日期格式 | YYYY-MM-DD |
time | 4 | HH:MM:SS | |
datetime | 8 | 范围从1000到9999年,不会进行时区的检索及转换 | YYYY_MM_DD HH:MM:SS |
timestamp | 4 | 范围从1970到2038年,自动检索当前时区并进行转换(毫秒数) | |
year | 4 | 年份 |
3、操作表
3.1、查看当前库中的表
show tables;
3.2、创建表
create table 表名(
字段1 类型1,
字段2 类型2,
...
);
3.3、查看指定的表的结构
desc 表名;
3.4、删除表
drop table 表名;
3.5、MySQL表的增查改删(CRUD——create、retrieve、update、delete)
3.5.1、增——create
例:
create table student (
id int,
num int comment '学号',
name varchar(20),
);
(1)、单行数据+全列插入
insert into student values(1,18,'张三');
insert into student values(2,19,'李四');
(2)、多行数据+指定列插入
insert into student values(3,20,'张三三'),(4,21,'李小四');
进阶版新增:
插入查询结果
insert into 表1 select * from 表2;
-- 将表2的查询结果复制到表1中
- 通过控制条件,来插入自己想要的数据,并不是一定要全部插入过去
- 查询的结果的列要和插入的表的列相匹配(个数、类型)才可以
3.5.2、查——retrieve
1、全列查询
select * from 表;
2、指定列查询
select 字段1,字段2...from 表;
3、查询表达式字段
select 字段1+100,字段2+字段3 from 表;
4、别名
select 表达式 as 别名 from 表名;
5、去重distinct
select distinct 字段 from 表;
6、排序order by
select * from 表 order by 排序字段;
-- 默认升序,或加asc
select * from 表 order by 排序字段 asc;
-- 降序加desc
select * from 表 order by 排序字段 desc;
7、条件查询
- 比较运算符
- between ... and...
- in
- is null
- like
- and
- or
- not
- 分页查询:limit
-- 条件查询
select * from 表 where 条件;
-- 分页查询,从第M条记录开始,查询N条记录
select * from 表名 limit N offset M;
select name from student where name like '张%';
-- NULL和其他值进行运算,结果还是NULL
select name ,mail from student where mail is null;
--从s开始,筛选n条结果
select ... from table_name [where ...] [order by ... ] limit s,n;
select id,name,math from student order by id limit 3 offset 0;
进阶版查询:
(1、聚合查询
将行和行放到一起进行计算
(1)、聚合函数
函数 | 说明 |
count([distinct] expr) | 计算查询到的数据的数量(计算行数) |
sum([distinct] expr) | 计算查询到的数据的总和,非数字无意义 |
avg([distinct] expr) | 计算查询到的数据的平均值,非数字无意义 |
max([distinct] expr) | 计算查询到的数据的最大值,非数字无意义 |
min([distinct] expr) | 计算查询到的数据的最小值,非数字无意义 |
-- count,统计人数
select count(*) from student;
-- sum,统计数学成绩总分
select sum(math) from student;
-- avg,求平均总分
select avg(chinese + math ) 平均总分 from student;
-- max ,数学最高分
select max(math) from student;
-- min,语文最低分
select min(chinese) from student;
注:如果在求和时,遇到NULL,则直接跳过不参与运算
(2)、分组查询
group by 列名;
根据查询结果,进行分组,把值相同的记录,分为一组
然后针对每一组,分别进行聚合
-- gender->性别——分组(男、女)
select gender from student group by gender;
-- 记录男女分别有多少人
select gender,count(*) from student group by gender;
-- 查询男女生各自的平均数学成绩
select gender,avg(math) from student group by gender;
(3)、having 条件筛选
-- 分组之前条件筛选使用 where
select gender ,avg(math) from student where name != 'zhangsan' group by gender;
-- 分组后条件筛选使用having
select gender,avg(math) from student group by gender having avg(math)>75;
(2、联合查询 ---多表查询
笛卡尔积,其实就是将多个表进行简单的排列组合
如图:
注:当表比较大的时候,多表查询就会非常低效
观察上图,会发现其组合中有很多不合理的数据,因此需要连接条件
联合查询 = 笛卡尔积 + 连接条件 + 其他条件
计算笛卡尔积:
方法一:
select * from student, class;
select * from student, class where student.classId = class.classId;
方法二:
select * from student join class;
select & from student join class on student.classId = class.classId;
-- 查询每个同学所在的班级
select student.name,class.name from student,class where student.classId = class.classId;
(1)、内连接-只返回两个表中连接字段相等的行
select 字段 from 表1 as 别名1 , 表2 as 别名2 where 连接条件 and 其他条件;
select 字段 from 表1 as 别名1 [inner] join 表2 as 别名2 on 连接条件 and 其他条件;
例:
select name ,score from student join score on student.id = score.studentId and name = '小王';
(2)、外连接
两张表的信息不是完全对应的(如果使用内连接,结果为两张表信息的交集)
-- 左外连接,表1完全显示
select 字段 from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
(3)、自连接
自连接是指在同一张表连接自身进行查询
思路就是把“行”转成“列”
(4)、子查询
子查询也叫做嵌套查询
- 关键字:[not] in
- 关键字:[not] exists
例:
select * from score where courseId in (select id from course where name = '语文' or name = '英语');
select * from score sco where exists (select sco.id from course cou where (name = '语文' or name = '英语' ) and cou.id = sco.courseId);
(5)、合并查询
-- union:去除重复数据
select ... from ... where 条件
union
select ... from ... where 条件
-- union all:不去重
select ... from ... where 条件
union all
select ... from ... where 条件
注:使用union和union all 时,前后查询的结果字段需要一致
SQL查询中各个关键字的执行先后顺序:
from>on>join>where>group by>with>having>select>distinct>order by>limit
3.5.3、改——update
update 表 set 字段1=value1,字段2=value2... where 条件;
3.5.4、删——delete
delete from 表 where 条件;
4、 表的设计
4.1、一对一
4.2、一对多
4.3、多对多
5、数据库的字段属性(约束)
unsigned
- 无符号
- 声明该列不能为负数
- 尽量不使用
zerofill
- 0填充的
- 不足为数的用0来填充,如int(3),5则为005
Auto_InCrement
- 通常理解为自增,自动在上一条记录基础上+1
- 通常用来设计唯一的主键,必须是整数类型
- 可定义起始值和步长
- 当前表设置步长(auto_increment=50):只影响当前表
- SET @@auto_increment_increment=5;影响所有使用自增的表(全局)
null
- 创建表时,指定某列可以保持为空,不论行中其他列是否已经被填充
- null的精确说法是“无”,而不是空字符或0
create table person ( id int null);
not null
- 将一个列定义为not null,将不允许向该列插入null值
create table person( id int not null);
unique
- 被赋予unique属性的列将确保所有值都有不同的值,只是null值可以重复
- 使用unique约束,数据库就会自动的给对应的列,创建索引
email varchar(30) unique;
default
- default属性确保在没有任何值可用的情况下,赋予某个常量值 (MySQL不允许插入函数或表达式值)
- 该属性无法用于blob或text列
- 如果此列已经指定了null属性,没有指定默认值时,默认值为null,否则默认值将依赖于字段的数据类型
subscribed enum('0','1') not null default '0';
name varchar(30) default 'unnknown';
primary key
- primary key 属性用于确保指定行的唯一性
- 指定为主键的列中,值不能重复,也不能为空
- 主键分类:
- 单字段主键——如果输入到数据库中的每行都已经有不可修改的唯一标识符,一般会使用单字段主键(此主键一旦设置不能再修改了)
- 多字段主键——如果记录中任何一个字段都不可能保证唯一性,就可以使用多字段主键。多个字段联合起来确保唯一性
- 对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大值+1
id int primary key auto_increment;
foreign key
- 外键用于关联其他表的主键或唯一键
- foreign key (字段名) references 主表(列)
- 创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键,class_id为外键,关联班级表id
-
create table person ( id int primary key auto_increment, sn int unique, name varchar(20) default 'unknown', mail varchar(20), class_id int, -- 当前表的列 另一张表(列) foreign key(class_id) references classes(id) );
拓展1:每个表,都必须存在以下五个字段
名称 | 描述 |
id | 主键 |
version | 乐观锁 |
isDelete | 伪删除 |
gmtCreate | 创建时间 |
gmtUpdate | 修改时间 |
拓展2:分布式系统下,生成唯一ID的算法:(实现方式大同小异),
生成公式=(时间戳 + 机房编号 / 主机编号 + 随机因子) = > 计算哈希值
6、索引
6.1、概念:
6.2、作用
- 索引的效果:索引对于提高数据库的性能有很大帮助,加快查询效率
- 索引的代价:1、空间;2、增删改效率降低
- 索引的核心数据结构:B+树(N叉搜索树)
6.3、使用
6.3.1、查看索引
show index from 表名;
6.3.2、创建索引
create index 索引名 on 表名(字段名);
6.3.3、删除索引
drop index 索引名 on 表名;
面试题:
1、索引是干什么的?解决了什么问题?
2、索引付出的代价
3、索引背后的数据结构
7、事务
7.1、概念
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事务被顺利执行。
即开始事务后执行的所有SQL都是一个整体,一个单元,要么都执行,要么都不执行。这里不执行的意思就row back即回滚,就是回到事务执行前的状态。在不同环境中,都可以有事务。对应在数据库中,就是数据库事务。
7.2、使用
7.2.1、开启事务
start transaction;
7.2.2、执行多条SQL语句
7.2.3、回滚或提交:
-- 全部失败
rollback;
--全部成功
commit;
7.3、事务的特性:
1. 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位,事务的根本所在(事物存在的意义),能够把SQL打包成一个整体,事务中的操作要么 都发生,要么都不发生(如果执行过程中出错,则自动回滚)。
2. 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态 ,即事务执行前后,数据处在“一致”的状态(数据能对的上,合情合理)。
3. 隔离性(Isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个 事务内部的操作及使用的数据对并发的其他事务是隔离的,并发 执行的各个事务之间不能互相干扰。
4. 持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是 永久性的,接下来的其他操作和数据库故障不应该对其有任何影 响,即事务的改动都是写到硬盘的,不会随着程序的重启而丢失。
7.4、事务的隔离级别
1.read uncommitted:允许读未提交的数据。并发程度最高,隔离性最低,可能存在 脏读/不可重复读/幻读 问题
2.read committed:只能读提交后的数据,相当于写加锁。并发程度降低,隔离性提高,解决了脏读,可能存在 不可重复读/幻读 问题
3.repeatable read:相当于读和写都加锁-可重复读。并发程度再降低,隔离性再提高,解决了脏读/不可重复读/,可能存在 幻读 问题 (MySQL默认的事务的隔离级别)
4.serializable:严格执行串行化-序列化。并发程度最低,隔离性最高,解决了脏读/不可重复读/幻读 问题,效率最低。
注:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读:
- 网上很多文章都说一个事务执行范围查询,两次select得到的条数不同,就叫幻读,这是错误的!!!
- 幻读并不是说两次读取获取的结果集不同,幻读是指某一次select操作得到的结果所表征的数据状态无法支持后续的业务操作,具体例子:select某记录是否存在,不存在,准备插入此记录,但执行insert时发现此记录已存在,无法插入,此时就发生了幻读。
- 还一种理解思路,就是因为先采用了"快照读",然后又用了"当前读",发现结果不同。比如先select是快照读,然后update、insert等,这时会用到当前读,发现操作出现未预料结果。
面试题:
1、事务是干什么的(从原子性切入)?
2、事务的其他特性还有啥?
3、隔离性,在并发执行事务下,会有哪些问题,以及如何解决?
4、MySQL的隔离级别有哪些?,和上面的问题如何对应?
8、JDBC的基础操作
(1)、创建数据源对象
DataSource dataSource = new MysqlDataSource();
(2)、建立与数据库的连接
由于mySQL是服务器与客户端连接形式的数据库软件, 需要建立客户端与服务器的联系, 但并不是所有数据库软件都是这种形式, 为了通用性, JDBC自带的DataSource类型并没有与服务器建立连接的方法, 所以需要向下转型, 使用MysqlDataSource建立与服务器的连接
((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java0909?characterEncoding=UTF-8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword("0410");
Connection connection = dataSource.getConnection();
注1:
注2:root指用户名,一般都是root
注3:0410是密码,根据自己设定的填写
(3)、书写sql语句, 并对sql语句进行预处理
String sql = "insert into student values(1,'小龙')";
PreparedStatement statement = connection.prepareStatement(sql); //对sql语句进行预处理
(4)、进行sql操作
//有两个方法
//executeUpdate 对应插入删除修改语句,返回值表示这次SQL操作影响到的行数
//executeQuery 对应查询语句
int n = statement.executeUpdate();
ResultSet resultSet = statement.executeQuery();
(5)、释放资源, 先创建的后释放
statement.close();
connection.close();
基本的增删改查举例:
(1)、增:
public static void main(String[] args) throws SQLException {
DataSource dataSource = new MysqlDataSource();
//数据库的位置
//1、创建数据源对象
((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java0909?characterEncoding=utf8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword("0410");
//2、让代码和数据库服务器建立连接
Connection connection = dataSource.getConnection();
//System.out.println(connection);
//【用户输入】
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要插入的学号:");
int id = scanner.nextInt();
System.out.println("请输入要插入的姓名:");
String name = scanner.next();
//3、构造要执行的SQL语句
String sql = "insert into student values(?,?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1,id);
statement.setString(2,name);
System.out.println("statement1"+statement);
//4、执行 SQL [ 发送请求 & 读取相应】
int n= statement.executeUpdate();
System.out.println("n="+n);
//5、完成之后,释放相关资源
statement.close();
connection.close();
}
(2)、删
public static void main(String[] args) throws SQLException {
//1、创建数据库
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java0909?characterEncoding=utf8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword("0410");
//2、连接数据库
Connection connection = dataSource.getConnection();
//3、构造SQL语句
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要删除的学号:");
int id = scanner.nextInt();
String sql = "delete from student where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1,id);
//4、执行SQL
int n = statement.executeUpdate();
System.out.println("n="+n);
//5、释放资源
statement.close();
connection.close();
}
(3)、改
public static void main(String[] args) throws SQLException {
//1、创建数据源
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java0909?characterEncoding=utf8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword("0410");
//2、和数据库建立连接
Connection connection = dataSource.getConnection();
//3、构造SQL语句
Scanner scanner= new Scanner(System.in);
System.out.println("请输入要修改的同学学号:");
int id = scanner.nextInt();
System.out.println("请输入要将同学的名字修改为:");
String name = scanner.next();
String sql = "update student set name = ? where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(2,id);
statement.setString(1,name);
//4、执行SQL
int n = statement.executeUpdate();
System.out.println("n="+ n);
//释放资源
statement.close();
connection.close();
}
(4)、查
public static void main(String[] args) throws SQLException {
//1、创建数据库
DataSource dataSource = new MysqlDataSource();
((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/java0909?characterEncoding=utf8&useSSL=false");
((MysqlDataSource)dataSource).setUser("root");
((MysqlDataSource)dataSource).setPassword("0410");
//2、建立连接
Connection connection = dataSource.getConnection();
//3、构造SQL
String sql = "select * from student where id =?";
PreparedStatement statement = connection.prepareStatement(sql);
System.out.println("请输入要查询的学号:");
Scanner scanner = new Scanner(System.in);
int id = scanner.nextInt();
statement.setInt(1,id);
//4、执行SQL
// 查询语句要使用executeQuery来完成
// 返回的结果是ResultSet 结果集 ,里面是一个表,这样的数据结构
//一个表里有很多行,每一行有很多列
ResultSet resultSet = statement.executeQuery();
//5、遍历结果集合
while (resultSet.next()) {
//每次循环,就能够获取到resultSet中的一行,进一步的就可以拿到每一列
id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println("id= "+ id+" name="+ name);
}
//6、释放资源
resultSet.close();
statement.close();
connection.close();
}
下期见!!!