数据库
数据库DataBase,简称DB。运行在操作系统上,按一定的数据结构,保存数据的仓库。是一个电子化的文件柜。数据库管理系统DataBase Manger System,简称DBMS。是一种操作和管理数据库的大型软件,用于建立,使用和维护数据库。
关系数据库的特点:
·优点:
- 易于维护:都是使用表结构储存数据,格式一致
- 使用方便:SQL语句通用,可用于不同关系型数据库
-
复杂查询:可以通过SQL语句在多个表之间查询出复杂数据
·缺点:
- 读写性能差,尤其是还是数据的高效读写
- 固定的表结构,灵活度少欠
- 高并发读写时,硬盘I/O决定了读写速度
非关系型数据库的特点:
- 保存数据的格式多样
- 对于海量数据的读写性能高
- 不支持复杂查询
操作数据库
- 查看所有数据库
show databases;
- 切换数据库
use 数据库名;
- 查看当下所有表
show tables;
- 创建一个数据库
create database 数据库名;
- 删除一个数据库
drop database 数据库名;
操作数据
- 创建数据表,并且添加约束
create table goods(
goods_id int not null primary key auto_increment,
goods_name varchar(10) not null,
goods_price double not null
)
create table client(
client_id int not null primary key auto_increment,
client_name varchar(10) not null,
client_telephone varchar(11) not null,
client_address text
)
create table consume(
consume_id int not null primary key auto_increment,
client_id int not null ,
goods_id int not null,
quantity int not null,
foreign key (client_id) references client(client_id),
foreign key (goods_id) references goods(goods_id)
)
- 删除数据表
drop table 表名;
- 修改数据表
对表重命名
alter table 旧表名 rename to 新表名;
添加新字段
alter table 表名 add column 字段名 数据类型 字段特征;
修改字段
alter table 表名 change 旧字段名 新字段名 数据类型 字段特征;
删除字段
表名 drop 字段名;
- 数据添加insert
insert into 表名 values('值1','值2'...)
- 数据修改update
update 表名 set 字段 = 值;
- 数据删除delete
delete from 表名;
-- 或
truncate table 表名;
数据查询select
count(字段名) | 统计数量 |
sum(字段名) | 求和 |
avg(字段名) | 平均 |
max(字段名) | 最大 |
min(字段名) | 最小 |
指定值 | =,!=,<> |
指定范围 | >,<,>=,<=,betwen值and值 |
指定集合 | [not] in (元素1,元素2,,,) |
模糊查询(%表示未知长度字符串,_表示一个字符) | like'%值' |
空置 | is[not]null |
多条件 | 使用and,or,&,|串联条件 |
abs(值或字段) | 绝对值 |
pow(值或字段) | 次幂 |
sqrt(值或字段) | 开平方 |
round(值或字段) | 四舍五入 |
ceil(值或字段) | 向上取整 |
floor(值或字段) | 向下取整 |
length(
字符串或字段
)
|
得到字符串长度
|
trim(
字符串或字段
)/ltrim(
字符串或字段
)/rtrim(
字符串
或字段
)
|
去除字符串首尾
/
首
/
尾空格
|
left(
字符串或字段
,n)/right(
字符串或字段
,n)
|
从字符串左
/
右开始截取
n
个字符
|
substr(
字段或字符串
,start)
|
从
start
开始截取至末尾
|
substr(
字段或字符串
,start,length)
|
从
start
开始截取
length
个字符
|
lcase(
字符串或字段
)/ucase(
字符串或字段
)
|
转换小写
/
大写
|
instr(
字符串
1,
字符串
2)/locate(
字符串
2,
字符串
1)
|
得到字符串
2
在字符串
1
中出现的顺
序
|
reverse(
字符串或字段
)
|
翻转字符串
|
concat(
字符串
1,
字符串
2...)
|
拼接所有字符串
|
replace(
字符串或字段
,
旧字符串
,
新字符串
)
|
将字符串中的旧字符串替换为新字
符串
|
now()
|
当前日期时间
|
current_date()/curdate()
|
当前日期
|
current_time()/curtime()
|
当前时间
|
year(
日期
)
/month(
日期
)/day(
日期
)
|
得到年
/
月
/
日部分
|
datediff(
时间
1,
时间
2)
|
计算时间
1
与时间
2
相隔的天数
|
timediff(
时间
1,
时间
2)
|
计算时间
1
与时间
2
相隔的时分秒
|
TIMESTAMPDIFF(
时间单位
,
时间
1,
时间
2)
|
计算时间
1
与时间
2
之间相隔的指定时间单位
|
- 查询所有字段
-
select * from 表名;
- 查询指定字段
select 字段名1,字段名2... from 表名;
- 字段重命名
select 字段1 as '重命名',字段2 '重命名'... from 表名;
- 查询指定条数
-- 查询前N条记录
select * from 表名 limit N;
-- 查询从索引N开始的M条记录
select * from 表名 limit N,M;
-- 每页显示size条,第page页
select * from 表名 limit (page-1)*size,size
- 去重复
select distinct 字段名 from 表名;
- 条件查询
select * from 表名 where 条件;
- 排序
排序可以是升序或降序
默认不写是升序asc
降序需要写desc
排序时如果有条件,where条件写在表名之后,排序之前
多字段排序时,在order by之后写多个字段及排序规则,用逗号隔开
按字段顺序优先排序
select * from 表名 where 条件 order by 排序字段 [ASC/DESC],排序字段 [ASC/DESC]...
- 分组
select 分组字段,统计函数 from 表名 group by 分组字段
- group_concat()函数
group_concat(字段1或字符串,字段2或字符串...)
select group_concat(字段1,字段2...) from 表名 group by 分组字段
-- 根据图书类型分组,查看每组下的图书名和作者
select group_concat(book_name,'--',book_author) from book_info group by type_id
- 行列转换
select stu_id,stu_name,
sum(if(c_name='高等数学',cj,null)) as '高等数学',
sum(if(c_name='思修',cj,null)) as '思修',
sum(if(c_name='大学英语',cj,null)) as '大学英语',
sum(if(c_name='大学体育',cj,null)) as '大学体育'
from score
group by stu_id
连接查询
- 交叉连接,笛卡尔积
select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;
- 内连接
select * from 表1,表2 where 表1.字段=表2.字段;
select * from 表1 inner join 表2 on 表1.字段=表2.字段;
-- 如查询图书类型表(类型编号、类型名称)和图书详情表(图书编号、类型编号、图书名称)
select * from 图书类型表 t1 ,图书详情表 t2 where t1.类型编号=t2.类型编号;
select * from 图书类型表 t1 inner join 图书详情表 t2 on t1.类型编号=t2.类型编号;
- 左连接
-- 在保证左表数据显示完整的情况下,关联右表中的数据,没有关联的数据用null表示
select * from 表1 left join 表2 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表,表2称为右表,会完整显示表1中的数据
- 右链接
-- 在保证右表数据显示完整的情况下,关联左表中的数据,没有关联的数据用null表示
select * from 表2 right join 表1 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表,表2称为右表,会完整显示表2中的数据
JDBC
java数据库连接Java Database Connectivity,用于Java程序连接不同的数据库。实际在Java中定义的相关数据库连接时所需的接口,不同的数据库对其进行了实现。
核心接口:
- Connection:用于设置连接的数据库的地址,账号,密码
- PreparedStatement:用于预处理,执行sql语句
- ResultSet:用于接收查询后的数据
例:
关系模型
实体关系模型也称为ER模型。实体Entity:一个表就是一个实体。关系Releationship:实体与实体之间的关系。用矩形表示实体,用椭圆形表示实体的属性,用菱形表示实体之间的关系,用直线连接各个图形。
- 一对一:一个国家有一个领导人
- 多对一/一对多:一个班级有多个学生,一个学生不能有多个班级
- 多对多:一个学生学习多门课程,一门课程可以被多个学生学习
数据库设计规范:
- 第一范式1NF:字段不可再分。
- 第二范式2NF:在满足1NF的基础上,消除部分依赖。
- 第三范式3NF:在满足2NF的基础上,消除传递依赖。
视图view
- 创建视图
- 使用视图
- 删除视图
事务transaction
一组sql执行单元,要么全部执行,要么全部不执行。
事务的特性ACID
- 原子性Atomicity:事务是最小的执行单元
- 一致性Consistency:事务执行前后,数据整体保持一致
- 隔离性Isolation:各个事务之间应当互不干涉
- 持久性Durability:事务一旦提交,改变是永久的
事务并发出现的问题:脏读,不可重复读,幻读。
事务隔离级别:
事务相关指令:
- 查看事务自动提交
select @@autocommit
- 修改事务自动提交
set@@autocommit=0/1 0表示不开启动,1表示开启
- 手动开启事务
start transaction;
- 提交
commit;
- 回滚
rollback;
- 查询当前事务隔离级别
select @@transaction_isolation
- 设置事务隔离级别
set[sesion|global]transaction isolation level[Read Uncommitted | Read Committed | Repeatable Read | Serializable]
难点:
内连接,左连接,右链接的区分。通常是通过主表的主键字段关联从表的外键字段。如果两张表中关联的字段名一致,一定要通过"表名.字段名"进行区分,通常给表重命名。如果使用inner join,带条件时需要加入where子句;如果使用,隔开各个表,带条件时使用and拼接条件。内连接只会显示两张表中有关联的数据。左链接在左表数据显示完整的情况下,关联右表的数据。右链接在右表数据显示完整的情况下,关联左表的数据