华清远见-重庆中心-数据库个人总结
阶段一
1.MySQL
是一种开放源代码的关系型数据库管理系统,使用最常用的数据库管理语言
2.数据存储形式
-
文件(存储在硬盘)
-
文件储存的格式
-
对数据的处理,可以通过java代码实现
-
缺点:读写速度慢,硬盘本身的特点就是读写慢
-
-
变量(存储在内存)
- 读写速度快,临时数据的存储
-
数据库(数据库管理系统)
-
数据库管理系统(DBMS-database manage system)是一套软件,是一种存储和管理数据表的软件系统;
- 适用于大数据量,支持多人并发操作
-
数据库中的数据是永久存储,数据操作效率高
-
如果要使用数据库,需要做和写什么操作?
-
安装数据库的服务器软件(server-服务器)
-
navicat – client(客户端)
-
配置环境变量path,可以使用命令,操作数据库的数据
-
使用client连接server,然后可以操作服务器上的数据
-
图形化软件的使用
3.数据库分类
-
关系型数据库 : Mysql , sql server , Oracle , db2 …
-
数据以二维表的方式存储
-
实体之间的关联关系
-
支持SQL(结构化查询语言)语言 (近期学习的重点)
-
-
非关系型数据库 : redis(后期要使用) , MongoDB , Hbase…
-
数据按不同的数据类型存储
-
不支持SQL
-
4.ER模型(E - entity实体 , R - realation 关系)
-
ER模型,就是将数据库中的table之间的关系,以图形的方式展示出来。
-
ER模型,就是创建数据库表的依据。
-
矩形代表实体,菱形代表关系,椭圆代表实体的属性。
-
实体之间的关系
-
1对1 : 一个国家有一个总统
-
1对多 : 一个班级有多个学生
-
多对多 : 课程和学生
-
5.Mysql的命令行操作
-
连接到mysql服务器 :
mysql -u root -p
-
使用mysql的命令
-- 是mysql的注释 show databases; -- 显示所有的数据库 use hello; -- 选择要使用的数据库的名字 show tables; -- 显示hello数据库中的所有的表 desc stu; -- 查看stu表的结构 -- Field , 字段 -- Type , 字段的类型 -- key , 键 insert into stu(name , age , gender) values('扭蛋',18,'女'); -- 插入数据的sql语句 select * from stu; -- 查询数据 update stu set age = 22 where name = '扭蛋'; -- 修改数据 delete from stu; -- 删除数据 drop table stu; -- 删除名字叫stu的表 drop database hello; -- 删除名字叫hello的数据库 create database cms; -- 创建数据库 create table test(id int , name varchar(6) , pwd varchar(8)); -- 创建一个名字为test的表
6.SQL(Struct Query Language):结构化查询语言
-
sql语言中,字符串可以使用单引号或者双引号,保证单双引号匹配
-
注释: – 注释内容 , /* */
-
sql语言中,关键字不区分大小写,字段名不区分大小写,数据内容有大小写之分
- 比如: select 同SELECT,NAME 字段,也可以是name,数据“ALICE"和’alice’就有区别
-
sql的使用:
-
创建数据库: create database 数据库名字;
- create database 数据库名字 default character set utf8; – 指定了数据库的编码
-
删除数据库: drop database 数据库名字;
-
创建表:
-
create table 表名 (字段 字段类型 约束条件 , 字段2 … )
-
如果字段名,表名和关键字重复了, 那么就在字段名或表名上添加引号
-
尽量避免和关键字重名,一般表可以加前缀 : cms_stu – 表示学生表, s_name – 表示学生表的字段名
-
约束条件: not null , 表示字段值不能是null
-
int类型的数据,可以设置为自动增加 : auto_increment
-
主键约束:primary key , 主键的特征就是非空且不重复,一般用于实现表中的每行数据不重复
-
练习:创建一个名字叫t_test的表,字段名包括t_id int类型,主键,并且自动增加,t_name字符串类型,不为空,最大长度50,t_age int类型
DROP TABLE IF EXISTS `t_test`; CREATE TABLE `t_test` ( `t_id` int NOT NULL AUTO_INCREMENT COMMENT 'id\r\n', `t_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名字', `t_age` int NULL DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`t_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t1hcQvpq-1676978214894)(D:\A图区\数据库1.png)]
-
-
阶段二
1.MySql中常见的数据类型
数据库 | 类型 | 对应java的 |
---|---|---|
tinyint | 短整型 | byte,short |
int | 整型 | int |
bigint | 长整型 | long |
float | 单精度浮点型 | float |
double | 双精度浮点型 | double |
decimal | 指定总长度,及其小数占的位数 | 比如:decimal(10,3),总长10,小数占3位 |
char(长度) | 定长字符串 | 对应java的String,比如char(5),表示必须是5位的字符串,不够就用空占位 |
varchar(长度) | 可变长字符串 | 对应java的String ,比如varchar(50) ,表示最大50位,字符串的实际长度为准。 |
text | 文本 | |
date | 日期 | yyyy-MM-dd |
time | 时间 | HH:mm:ss |
datetime | 日期时间 | yyyy-MM-dd HH:\mm:ss |
timestamp | 毫秒 | 保存时间的毫秒数,14表示yyyyMMddHHmmss,8表示yyyyMMdd |
2.约束
约束的名字 | 概念 | 关键字 |
---|---|---|
非空约束 | 是否允许字段为null | null表示可以为空,not null表示不能为空 |
主键约束 | 主键(primary key),保证每行数据不重复,并且主键值不能是null,一般会在建表的时候, 给表设计要给id字段 | primary key |
唯一约束 | 保证字段值不重复 | unique |
外键约束 | 在有这个主从关系的表中,给有管理关系的字段,设置为外键约束,这个字段值,只能参考主表中的某个字段的值 | foreigh key references |
默认值 | 可以设置某个字段的默认值,如果没有给这个字段设置数据值,就采用默认值 | default |
4.数据添加–insert
数据添加,都是整行添加,每一行数据叫一个record(记录)
-
给所有字段赋值
-
表名之后,没有指定字段名,说明添加的所有字段的值,values中安表结构的顺序,填写上每个字段对应的值。
-
遇到自增字段,在values中对应的顺序位置用0 , null 或 default让其自动填充
-
遇到有默认值的字段,不能省略不写,在values中对应的顺序位置用default占位
-
遇到允许为空的字段,不能省略不写,在values中对应的顺序位置用null占位
insert into 表名 values(字段值1 , 字段值2 ... ) insert into school values('1002' , '重庆大学' , '1927-2-15' , '沙坪坝 , 12200 ,1230.56)
-
-
给指定字段赋值
-
表名之后,指定字段名,values中只需要设置表名之后指定的字段的value值就可以了,要一 一对应
-
没有默认值的非空字段,必须要设置数据值
-
表名之后的字段顺序必须和values的值的顺序—致
insert
-
-
批量添加
-
用insert into 语句,一次添加多个记录(一个记录 表示 一行)
-
一次如果需要添加多条数据,使用批量添加,效率更高
-
批量添加,也可以在表名之后指定需要添加的字段
-
insert
-
-
5.修改数据 – update
-
修改单个字段的值
-
update
-
-
修改多个字段的值
-
根据条件修改数据(where)
-
指定值
-
指定范围
-
> , < , > , = , <= 表示范围 , and , or 将多个条件进行关联
-
update school set sc_total=6100where sc_code < 1004
-
-
使用between … and , 设置要在区间之内
-
update school set sc_total=6100 where sc_code between 1004 and 1006 -- [1004 , 1006]
-
-
使用 != 或 <>不等于
-
update school set sc_total=6100where sc_code c>1004 -- 不是1004的就修改
-
-
-
指定集合
-
在某个集合中 - in
-
update 表名 set字段名=新值where字段in(值1,值2 ,.....) update school set sc_total=6100 where sc_code in(1003 ,1005 , 1007)--修改的是符合sc_code等于1003,或1005或1007 update school set sc_total=6100 where sc_code=1003 or sc_code=1005 or sc_code=1007 --繁琐
-
-
不在某个集合中 - not in
-
update 表名 set 字段名 = 新值 where 字段 in(值1,值2 ,.....) update school set sc_total=6100 where sc_code not in(1003 ,1005 , 1007)--不修改1003,1005,1007 ,其他都修改
-
-
-
空值 - null
-
is null
-
update 表名 set 字段名 = 新值 where 字段 is nu11 update school set sc_birth='1955-10-2' where sc_birth is nul1 --建校日期为nu11的修改为1955-10-2
-
-
is not null 表示 非空
-
update表名set字段名=新值 where字段is not nu11
-
-
-
阶段三
1.常见的英语单词
create : 创建 , database : 数据库 , table : 表 , data : 数据 , insert : 插入 , update : 更新
, delete : 删除 , select : 查询
query : 查询 , primary key: 主键 , foreign key: 外键 , unique: 唯一 , distinct: 重复 ,
where :(在)…情况 , value : 值 , as : 作为
field : 字段 , record : 记录 , row : 行 , column : 列 , show : 展示 , view : 视图 ,
modify: 修改 , asc: 升序 , desc : 降序
syntax : 语法 , deny : 拒绝 , group : 组 , order : 排序 , count : 计数 , limit : 限定 ,
page : 页码 , default: 默认 , grant : 授权
2.查询–select
-
sql查询表中的总行数
-- count(列名) , count(*) , 计算出查询结果的行数 select count(列名) from 表名 where ... --先根据条件查询出行,然后用count计算出行数 select count(*) from school --查询整个表的总行数 select count(sc_code) from school where sc_address liek'五公里' --地址在五公里的学校的个数
-
分页查询(limit - 只适用于mysql数据库)
-- limit begin , rows 【数据表的第一行为0 , 第二行为1 ... 】 select * from 表名 [where 条件] limit 起始行 , 行数 --通过limit关键字,限定查询的时候从哪行开始查,一共查多少行 select * from school limit 3 , 4 select * from school where sc_area > 800 limit 2 , 3 -- 分页查询的需求: 把school表中的数据(8条),按每页显示3行的方式查询出来 select * from school limit 0,3 select * from school limit 3,3 select * from school limit 6,3 -- 最后一页没有3条,那么有多少,查询多少即可 -- 怎么计算总页码,每页的起始值怎么算,规定了每页显示的行数 select count(*) from school --查询整个表的总行数,把查询结果取出来保存在变量中,作为总行数 int maxrow = 12;//整个表的总行数 int rows = 4;//分页后 每页的行数 int page;//分页后 总的页数 if(maxrow % rows == 0){ page = maxrow / rows; }else{ page = maxrow / rows + 1; } System.out.println("总的页数:"+page); int currentPage = 2;//第二页 eg. 1 , 2 , 3 //每页起始值(每页的第一行)对应的索引 int begin = (currentPage -1) * rows;//eg. begin = 0 , 3 , 6 -- 练习:book_info , 规定每页显示4行数据,你把每页的内容分别查询出来
-
查询排序–order by
-- 排序规则: asc升序 , desc降序 排序是针对某个字段进行排序的 -- order by 字段名1 asc/desc , 字段名2 asc/sesc , 如果是你希望升序排序,asc可以省略 select * from 表名(where 条件) order by 字段名 排序方案,...... select * from school order by sc_code desc --按sc_code 降序排序 -- 练习 school表按 学生人数降序排序,然后按建校日期升序排序 select * from shcool order by sc_total desc , sc_birth asc --可以省略asc --练习:查询学校中包含"四川"的学校,按人数降序 select * from school where sc_name like '%四川%' ORDER BY sc_total desc --练习:查询出人数多余6000的学校,按地址升序排序 select * from school where sc_total > 6000 ORDER BY sc_address asc
-
分组查询–group by
-- group by字段名 -- where 条件 group by 字段名 , 先根据where条件,查询到结果,然后按指定字段分组 -- [where条件] group by 字段名 having 条件 ,按指定字段分组之后,然后可以使用having子句对分组的结果进行筛选 select 分组的字段 , 统计函数 from 表名 [where 条件] group by 字段名 [having 条件] -- 把学校按地址分组 select sc_address from school group by sc_address -- 把school表中的全部数据,按地址进行分组 -- 把学校按地址分组之后,可以统计出每组的学校个数 select sc_address , count(sc_address) as 学校个数 from school group by sc_address order by 学校个数 desc -- 把学校按地址分组之后,可以统计出每组的学校个数,只显示个数比1大的地址 select sc_address , count(sc_address) as 学校个数 from school group by sc_address having 学校个数 > 1 order by 学校个数 desc -- 练习: 按学校的建校日期分组 select sc_birth from school GROUP BY sc_birth -- 练习: 按学校的建校日期分组 , 然后找出每组中最大的学校人数 select sc_birth , max(sc_total) 人数最多 from school group by sc_birth -- 练习: 所有学校中,人数最多的人数 select max(sc_total) from school -- 整个表是一个组 --练习: 建校日期最早的 select min(sc_birth) from school -- 练习: 学校的平均人数 select avg(sc_total) from school --练习: 学校的累计人数 select sum(sc_total) from school --练习:统计出所有包含大学的学校中按日期分组,统计每组的个数,筛选出个数大于1的组 select sc_birth , count(*) as 学校个数 from school where sc_name like '%大学%' group by sc_birth having 学校个数 > 1
3.函数
-
统计函数(聚合函数) – 常用的
select 统计函数(字段名) from 表名
函数名 功能 count(字段名) , count(*) 统计行数 sum(字段名) 求和 avg(字段名) 平均值 max(字段名) 最大值 min(字段名) 最小值 -
数学相关的函数
select 函数(字段名) ... from 表名 select sc_name , round(sc_area) from school -- 对学校面积四舍五入
函数名 功能 round(字段名 或 值) 四舍五入 ceil(字段名) 向上取整 floor(字段名) 向上取整 abs(字段名) 绝对值 pow(字段名) 幂 sqrt(字段名) 平方根 -
字符串函数
select 函数(字段名) ... from 表名 select REVERSE(sc_name) , round(sc_area) from school --学校名字反转 select REVERSE(sc_name) , round(sc_area) , LENGTH(sc_name) from school --计算名字的长度 SELECT CONCAT(sc_name,sc_birth) from school --学校名字和建校日期拼接了
函数 功能 trim(字段名) 去掉字符前后多余的空格 substr(字段名 , start) 截取字符串 lcase(字段名) 转小写 ucase(字段名) 转大写 reverse(字符串) 反转 concat(字段名1 , 字段名2 … ) 拼接 -
时间函数
select 函数(日期相关的字段名) ... from 表名 select now() -- 得到系统时间 select current_date() select sc_name , year(sc_birth) from school-- 找出学校创建的年
函数 功能 now() 当前日期时间 current_date() , curdate() 当前日期 current_time() , curtime() 当前时间 year/month/day(日期类型的字段名) 得到时间分量 datediff(时间,时间) 计算相隔的天数
4.多表查询 – 两张表
-
交叉连接 笛卡尔积
集合A:{a,b},集合B:{1,2,3}
集合A * 集合B = {a1,a2,a3,b1,b2,b3}
将两张表中的数据两两组合,得到的结果就是交叉连接的结果,也称为笛卡尔积
select * from 表1 , 表2 -- cross join将A表的所有行分别与B表的所有行进行连接,返回的记录数为两个表的记录数乘积。 select * from 表1 cross join 表2 -- cross -- inner join组合两个表中的记录,只有公共字段之中有相符的值才进行连接。 select * from 表1 inner join 表2 -- inner -- 两张表数据组成一张表,其中有很多无效数据 select * from t_stu , t_class select * from t_stu cross join t_class select * from t_stu inner join t_class
-
内连接
通过主表主键字段和从表的外键宁段进行等值判断
主表和从表字段名一样,使用"表名.字段名"进行区分;如果表名复杂,可以给表名取表名
如果用where进行内连接,后续还有其他条件的查询,使用and 拼接后续条件
如果用inner join … on ,后续有其他条件,使用where进行条件拼接
内连接只显示两张表中有关联的数据
select * from 表1 , 表2 where 表1.字段 = 表2.字段 --判断相等的字段就是有外键约束的字段 select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 -- on后面的相等字段的判断,也是有外键约束的字段 --查询学生的学生信息和班级信息 select * from t_stu , t_class where t_stu.c_id = t_class.c_id -- * 代表的是两个表的所有字段 select * from t_stu s, t_class c where s.c_id = c.c_id-- 给表取别名 select * from t_stu s , t_class c where s.c_id = c.c_id and s.s_money > 5000 --使用and 拼接查询条件 SELECT * FROM t_stu s INNER JOIN t_class c ON s.c_id = c.c_id where s.s_money > 500 --加上关键字where之后,拼接条件
-
左连接
left join … on :左表数据全部显示,右表只显示有关联的数据。右表中没有和左表关联的数据,右表用null表示
select * from 左表 left join 右表 on 左表.字段 = 右表.字段 -- 左连接 -- 查询学生和班级信息 select * from t_class c left join t_stu s on c.c_id = s.c_id -- 左表内容全显示
-
右连接
select * from 左表 right join 右表 on 左表.字段 = 右表.字段 -- 右连接 -- 查询学生和班t_class ct_class c级信息 select * from t_class c right join t_stu s on c.c_id = s.c_id -- 右表内容全显示
-
练习
-- 1. 查询出学生的名字,班级名,班级编号,工资,对工资进行降序排序 select s.s_name ,c.c_name ,c.c_id ,s.s_money from t_class c , t_stu s where c.c_id = s.c_id order by s.s_money -- 2. 查询出各种班级各自有多少人,显示出班级名字和班级总人数 select c.c_name ,count(c.c_id ) as 人数 from t_class c , t_stu s where c.c_id = s.c_id GROUP BY c.c_id -- 3. 查询出所有工资大于3000的学员姓名,学员工资, 和所在班级名字,编号 select s.s_name, s.s_money ,c.c_name ,c.c_id from t_class c , t_stu s where c.c_id = s.c_id and s.s_money > 3000 -- 4. 查询出各种班级有多少人,显示班级名字,班级总人数,只显示班级人数大于得等于2 的查询结 果 select c.c_name ,count(c.c_id ) as 人数 from t_class c , t_stu s where c.c_id = s.c_id GROUP BY c.c_id HAVING 人数>=2 -- 5. 查询出各种班级有多少人,显示班级名字,班级总人数,只显示班级人数大于等于2 的查询结果 ,并且根据班级名字升序排序 select c.c_name ,count(c.c_id ) as 人数 from t_class c , t_stu s where c.c_id = s.c_id GROUP BY c.c_id HAVING 人数>=2 ORDER BY c.c_name ASC
select s_name , c_name , t_stu.c_id , s_money from t_stu , t_class where t_stu.c_id = t_class.c_id order by s_money desc select c_name , count(*) as 班级总人数 from t_stu , t_class where t_stu.c_id = t_class.c_id group by c_name select s_name , s_money , c_name , t_stu.c_id from t_stu , t_class where t_stu.c_id = t_class.c_id having s_money > 3000 select c_name , count(*) as 班级总人数 from t_stu , t_class where t_stu.c_id = t_class.c_id group by c_name having 班级总人数 >= 2 select c_name , count(*) as 班级总人数 from t_stu , t_class where t_stu.c_id = t_class.c_id group by c_name having 班级总人数 >= 2 order by c_name asc
5.子查询
子查询是在查询语句中,镶嵌了另一个查询语句,也可以镶嵌查询
-
子查询作为where条件
-- 查询出班级名字为"软件技术11班"的所有学员 select * from t_stu where c_id = (select c_id from t_class where c_name="软件技术11班") -- 查询出没有学生的班级 select * from t_class where c_id not in (select c_id from t_stu) -- 方法一:查询出学生的班级c_id,然后不在这些c_id中的班级就是没有学生的班级 select c.c_id ,c_name from t_class c left join t_stu s on c.c_id = s.c_id where s.s_id is nu11 -- 方法二
-
子查询是作为一张临时表
如果把子查询的结果作为一张临时表,必须要把子查询的结果,设置一个别名
--查询出所有男同学,然后在找工资大于5000的 --先用一个查询,查询出所有男生,然后在这个结果中,查询出工资大于5000的 select * from (select ' from t_stu where s_sex='男’) as stuboy where s_money >= 5000
-
子查询作为一个字段
如果子查询的结果作为一个字段,那么要求该子查询每次只能返回一行记录
-- 查询出所有班级名字和学生名字 select s_name , (select c_name from t_class c where c.c_id = s.c_id) as c_name from t_stu s
-
练习
-- 1.把所有班级及其所有学员都显示出来(用两种方式 -- left join , right join) select * from t_class c left join t_stu s on c.c_id = s.c_id select * from t_stu s right join t_class c on c.c_id = s.c_id -- 2.查询出没有学生的班级(思考多种方式) -- is nu11 select c.c_id ,c_name from t_class c left join t_stu s on c.c_id = s.c_id where s.s_id is nul -- not in select c.c_id , c_name from t_class c where c.c_id not in ( select c_id from t_stu) -- notexists : 不存在 (了解) select c.c_id , c_name from t_calss c where not exists (select s.c_id from t_stu s where s.c_id = c.c_id)
阶段四
1.修改表结构
-
drop table , 然后重新建
-
给表增加字段
alert table 表名 add 字段名 字段类型 alert table dept add d_phone varchar(20) -- 给dept表增加一个字符串类型的字段d_phone
-
删除表中的字段名
alert table 表名 drop 字段名 -- 删除字段名 alert table dept drop d_phone -- 删除dept表的d_phone字段名
-
修改表中的字段名
如果被修改的字段本身有数据,需要考虑数据类型是否匹配,谨慎修改
alert table 表名 modify 字段名 新的类型 -- 修改字段的类型 alert table dept modify d_intro int
-
修改字段名和字段类型
alter table 表名 change 原字段名 新字段名 新的类型 alter table dept change d_intro d_note varchar(255) -- 修改了字段名和字段类型
-
修改表的名字
alter table 表名 rename to 新表名 alter table emp rename to employee -- 修改表名
-
增加外键约束
-
删除外键约束
alter table 表名 drop foreign key 约束名字 alter table t_stu drop foreigh key fk_stu_class -- 根据约束的名字,删除约束
-
添加约束
-- 添加唯一约束 alter table 表名 add unique(字段名) -- 添加非空约束 alter table 表名 change 原字段名 新字段名 数据类型 not null -- 添加主键约束 alter table 表名 add primary key(字段名) -- 添加默认值 alter table 表名 字段名 set default 默认值
-
利用工具的设计表功能,完成表结构的修改
2.多表关联的设计原则
1.一对多关系,通过外键约束实现,在多方的表中增加一个外键约束的字段,引用1方的主键值
2.多对多关系,通过通过中间表实现关联关系,中间表中存储两个表的关系(中间表通过外键约束字段,引用两个实体对应的表的主键值)
-
创建学生,课程,中间表
-
三张表的联合查询
-- id为1的学生,选择了哪些课,显示学生的信息和课程的信息 select * from student s,course c , stu_cou sc where s.s_id = sc.s_id and c.c_id = sc.c_id and s.s_id = 1 select * from student s inner join stu_cou sc on s.s_id = sc.s_id inner join course c on c.c_id = sc.c_id where s.s_id=1 -- c_id 为 2 的课程,有哪些学生选择了,显示学生的信息和课程的信息 select * from student s ,course c , stu_cou sc where s.s_id = sc.s_id and c.c_id = sc.c_id and c.c_id = 2 select * from student s inner join stu_cou sc on s.s_id = sc.s_id inner join course c on c.c_id = sc.c_id where c.c_id = 2 -- 查询出选课超过2门的学生的信息(用2张表) select s_name , count(*) as count from student s , stu_cou sc where s.s_id = sc.s_id group by s_name having count>2 -- 查询出大于等于2个学生选择了的课程 select c_name , count(*) as count from stu_cou sc,course c where sc.c_id = c.c_id group by c_name having count >= 2 order by count desc -- 查询出没有选课的学生名字 select * from student s left join stu_cou sc on s.s_id = sc.s_id where sc.id is null -- 统计出没有选课的学生总数 select count(s_name) as 个数 from student s left join stu_cou sc on s.s_id = sc.s_id where sc.id is null -- 找出没有学生选择的课程的名字 select * from course c left join stu_cou sc on c.c_id = sc.s_id where sc.c_id is null -- 统计出没有学生选择的课程的数量