一、简述MySQL
MySQL 是最流行的关系型数据库管理系统之一,由Oracle公司开发、发布和支持。关系型数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。 为了提高存储速度,结构化数据是有组织的存放在物理文件中。数据库、表格、视图、行和列等对象的逻辑模型提供了灵活的编程环境。你可以在指向不同表格的不同数据字段中设置对应关系的规则,如一对一、一对多、唯一、必须和可选。数据库强制执行这些规则,因此,在一个设计良好的数据库中,应用程序永远不会看到不一致的、重复的或者孤立的、过期或者丢失的数据。
备注: 1、SQL:Structure Query Language(结构化查询语言)简称SQL。 2、、MySQL所使用的 SQL 语言是用于访问数据库的最常用【标准化语言】 3数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。
数据库的特点:
- 持久化存储数据;其实数据库就是一个文件系统
- 方便存储和管理数据
- 使用了统一的方式操作数据库。(sql语句)
二、连接数据库
这里使用的数据库软件是 navicat 数据库版本是 8.0.26 使用navicat连接数据库
三、创建数据库(了解)
说明1: 存储引擎:存储引擎是服务于存储服务的,通过存储引擎将数据保存。就跟计算机如何将数据保存到磁盘中一样,在数据库中,存储引擎的意思就是通过何种引擎【将数据存储在磁盘中】。 常用的有两种:InnoDB和MylSAM ——InnoDB:MySQL 8.0中的默认存储引擎。 InnoDB是用于MySQL的事务安全(兼容ACID)的存储引擎,具有提交,回滚和崩溃恢复功能以保护用户数据。 InnoDB行级锁定(无需升级为更粗粒度的锁定)和Oracle风格的一致非锁定读取可提高多用户并发性和性能。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I / O。为了保持数据完整性, InnoDB还支持FOREIGN KEY引用完整性约束。【总结:安全性好,事务处理及用户操作数据表】 ——MyISAM:这些表占用的空间很小。 表级锁定 限制了读/写工作负载中的性能,因此它通常用于Web和数据仓库配置中的只读或只读工作负载中。【节约空间及响应速度】 mysql常用引擎
说明2:在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。 字符集(character set):定义了字符以及字符的编码。 ——utf8:utf8 是 Mysql 中的一种字符集,只支持最长三个字节的 UTF-8字符,也就是 Unicode 中的基本多文本平面。 ——utf8mb4.:mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。 如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.
字符序(collation):定义了字符的比较规则。 utf8mb4_unicode_ci比较准确 utf8mb4_general_ci 速度比较快。通常情况下 utf8mb4_general_ci 的准确性就够我们用的了 utf8和utf8mb4的区别
这里我们选择InnoDB引擎、utf8mb4和utf8mb4_general_ci
操作数据库(了解)(后面可以使用可是化操作来创建数据库) 1、【创建】数据库——【create】 database if not exits +数据库名 2、【删除】数据库:【drop】 database +[if exists] +数据库名; 3、【使用/切换】数据库 :【use】 +数据库名 ; 4、【查看】 所有的数据库 :【show】 +databases;
四、创建数据表(设置字段)
1、删除表 drop table [if exists] 表名 2、创建表(并设置存储引擎) 语法:
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释]
`字段名` 列类型 [属性] [索引] [注释]
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
例1:
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(32) COLLATE utf8mb4_bin NOT NULL,
`password` varchar(32) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
例2:
【 表的字段属性说明】
- Unsigned:无符号的整数,
·声明该列后【不能为负数】 - zerofill:【0填充的】
·不足的位数,使用0填充,int(3), 5—005 - Auto_InCrement :【自增】:
·自动在上一条记录上+1(默认) ·用来设计唯一的主键~index,必须是整数类型 ·可以自定义设计主键自增的起始值和步长 - NULL和NOT NULL 空与非空
默认为NULL,即没有插入该列的数值 如果设置为NOT NULL,则该列必须有值,如果不填写值 就会报错 - DEFAULT :默认的
·用于设置默认值 ·例如,性别字段,默认为"男”,否则为“女”;若无指定该列的值﹐则默认值为"男"的值 - comment’…’ ----注释信息…
- 主键primary key (`id`)一般一个表只有1个,放到最后面
- COLLATE :对于mysql中那些字符类型的列,如VARCHAR,CHAR,TEXT类型的列,都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。COLLATE通常是和数据编码(CHARSET)相关的,一般来说每种CHARSET都有多种它所支持的COLLATE,并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci,GBK编码的默认COLLATE为gbk_chinese_ci,utf8mb4编码的默认值为utf8mb4_general_ci。
附加说明
- 字段名称尽量使用` `括起来
- 字符串用单引号‘’括起来
- 所有的语句后面加(英文的,) 最后一个不用加
附:【可视化操作】查看创建的数据库/表语句
1、查看创建的数据库语句 show create database +数据库名 【建议:如果想跟我创建一样的数据库可以使用下面的语句(创建跟我一样的后面的举例也运行来看看效果)】
CREATE DATABASE `db001`
2、查看创建的数据表语句(同上) show create table +表名 【建议:创建跟我一样的数据表,方便后面查看运行效果】
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`age` int DEFAULT NULL,
`password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`Math` int DEFAULT '0',
`English` int DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
五、alter table修改表的字段(列)
1、修改表名 alter table 旧表名 【rename as】 新表名 2、添加字段 alter table 表名 【add】 字段名 +列属性
alter table 表名 add age int(11)
3、修改表的字段 ——3.1修改表的约束类型(列属性) alter table 表名 【modify】 字段名 新的列属性[]
alter table 表名 modify age varchar(11)
——3.2修改表的字段名 alter table 表名 【change】 旧名字 新名字 (新的)列属性[]
alter table 表名 change age age1 int(10)
说明:字段重命名包括修改约束、但是一般只用来重命名 4、删除表的字段 alter table 表名 【drop】 字段名(列)
六、 MySQL数据管理
DML:数据操作语言
DML(database manage language):数据库操作语言 作用:操作表中的值/数据
0、建议:插入我准备的数据
插入我准备的数据,后面可以同样运行看看效果
1、插入数据【insert into】
insert into 表名(字段名1,字段2,字段3) values(‘值1’),(‘值2’),(‘值3’),(…) 注意事项:
- 字段与字段之间使用英文逗号,末尾不能以逗号结尾
- 可以插入多条数据value(), (), ()
- 如果不写表的字段,从主键开始,默认一一匹配
INSERT INTO `user`(`name`) VALUE('小明'),('小红'),('小白')
INSERT INTO `user`(`name`,`password`) VALUE('小黑','123'),('小紫','456')
2、更新/修改数据
语法:【update】 表名 【set 】列名=新的值 where [条件] 注意:必须添加条件,否则会将对应表的字段中所有的数据修改 【条件】:where +条件 条件的返回值为Boolean类型 如果为ture才执行 where可以 通过多个条件定位数据,无上限 &&(and) 与 ||(or)
比较 = < > <= >= 不等于 :<> 或 != 在某个范围内 [2,5] :between …and … 与: and 、&& 或 :or 非:NOT 或 !
update `user` set `name`='长江七号' where id = 1
update `user` set `name`='长江六号' ,`password`= '8888' where id = 2
3、删除数据
语法:【delete】 from 表名 where 条件 注:如果不添加条件,会导致对应表所有的数据被删除
--删除指定数据
DELETE FROM `user` WHERE id = 3
--删除该数据表的所有数据
DELETE FROM `user`
DQL 数据库查询语言【最重要】
Select完整语法
select [distinct]
字段名1 [as +别名], ...
from 表名
DQL是数据库中最核心的语言,最重要的语句,使用频率最高
基础查询:
语法: select +字段 from +表名 备注:*代表全部字段,如果想查询多个字段可以用 , 隔开
1、查询多个字段
SELECT * FROM `user`
SELECT `id`,`name`FROM `user`
2、字段注释
以后假如查询的字段非常多,一般会使用注释来说明(这样就会显得想要查询的字段非常清晰,下面只是列举两个)
3、相同数据去重 distinct
SELECT DISTINCT username FROM user 备注:去重只会【去除完全重复的数据】 1、如果你查询的是username字段,不会显示重复的名字 2、如果你查询的是全部字段,仍然会显示两个重复的名字(因为他们的id不同)
4、起别名 as
可以给表、给字段起别名 作用 : 1、可以显示到查询的数据上面 2、如果涉及重复使用且多个表的查询,可以通过表的别名来使用表
SELECT username as 用户名, `password` as 密码 from user as u
5、显示的结果拼接字符串——函数 :concat(a,b)
可以用于拼接字符串并且显示到查询出来的数据中 注”字符串用’‘ 包围
6、计算多列的值
备注1:两个字段相加 不可以在左右两边加上`` 否则会报错,因为这是一个表达式,而不是字段
SELECT `name`,`Math`,`English`,Math + English as 总分 FROM `user`
备注2:null与任何值计算都为null,但是我们想要的总分是100 ——使用函数ifnull(a,b) a代表需要判断是否为null的数据 b代表替换值
SELECT `name`,`Math`,`English`,Math + IFNULL(English,0)as 总分 FROM `user`
条件查询:
语法 : where +条件
比较 = < > <= >= 不等于 :<> 或 != 在某个范围内 [2,5] :between …and …
与: and 、&& 或 :or in(集合) 非:NOT 或 ! like
–备注:null不能使用等号去判断 is null 判断为null is not null 判断不为null
1、常规运算符查询
SELECT * FROM `user` WHERE age > 5
SELECT * FROM `user` WHERE age >= 5
SELECT * FROM `user` WHERE age = 5
SELECT * FROM `user` WHERE age != 5
SELECT * FROM `user` WHERE age >= 5 && age <= 7
SELECT * FROM `user` WHERE age >= 5 and age <= 7
SELECT * FROM `user` WHERE age BETWEEN 5 and 7
SELECT * FROM `user` WHERE age = 5 or age = 7 or age = 9
SELECT * FROM `user` WHERE age in (5,7,9)
SELECT * FROM `user` WHERE English = null
SELECT * FROM `user` WHERE English is null
2、like(模糊查询)
【占位符】 % 代表0到任意个字符 _ 代表一个字符
查询以小开头的用户名 查询 以小开头(小后面只有1个字) 查询 以小开头(小后面只有2个字) 查询名字是2个字的人
SELECT * FROM user WHERE name like '__'
排序查询
语法 :order by +子句 order by排序字段1排序方式1,排序字段2排序方式2…
排序方式: ASC:升序,默认的。 DESC:降序。
注意: *如果有多个排序条件,则当前边的条件值―样时,才会判断第二条件。
SELECT * FROM user ORDER BY Math
SELECT * FROM user ORDER BY Math ASC
SELECT * FROM user ORDER BY Math DESC
SELECT * FROM user ORDER BY Math DESC, English asc
聚合函数
聚合函数:将一列数据作为一个整体,进行纵向的计算。
- count :计算个数
- max :计算最大值
- min :计算最小值
- sum:计算和
- avg :计算平均值
*注意:聚合函数的计算,会排除null值。 解决方案︰ 1、选择不包含非空的列进行计算(id或者*) 2、IFNULL函数
select count(id) FROM user
select count(*) FROM user
select max(math) FROM user
select min(math) FROM user
select avg(math) FROM user
select sum(math) FROM user
select count(ifnull(English,0)) FROM user
分组group by和过滤 having
分组查询: 1.语法︰group by 分组字段;
SELECT sex ,avg(math) FROM `user` GROUP BY sex
SELECT sex ,avg(math) ,count(id) as 人数 FROM `user` GROUP BY sex
SELECT sex ,avg(math) ,count(id) as 人数 FROM `user` where math >60 GROUP BY sex
SELECT sex ,avg(math) ,count(id) as 人数 FROM `user` where math >60 GROUP BY sex having 人数 > 2
注意:分组之后查询的字段:分组字段、聚合函数
where和 having 的作用差不多,那where和 having 的区别? 1、 where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来 2、where后不可以跟聚合函数,having可以进行聚合函数的判断。
分页查询
limit 开始的索引, 每页查询的条数
备注:开始的索引=(当前的页码- 1)*每页显示的条数
SELECT * FROM `user` limit 0,3
SELECT * FROM `user` limit 3,3
SELECT * FROM `user` limit 6,3
联表查询
准备工作:导入数据
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20)
);
INSERT INTo dept(NAME)VALUES ('开发部'),('巿场部'),('财务部');
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),
gender CHAR(1),
salary DOUBLE,
join_date Date,
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES dept(id)
);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (1, '孙悟空', '男', 7200, '2013-02-24', 1);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (2, '猪八戒', '男', 3600, '2010-12-02', 2);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (3, '唐僧', '男', 9000, '2000-08-08', 2);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (4, '白骨精', '女', 5000, '2015-10-07', 3);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (5, '蜘蛛精', '女', 4500, '2011-03-14', 1);
INSERT INTO `emp`(`id`, `NAME`, `gender`, `salary`, `join_date`, `dept_id`) VALUES (6, '金蝉子', '男', 4000, NULL, NULL);
如果我们直接查询两个表,会出现两个表所有的id组成情况 (笛卡尔积:有两个集合A,B .取这两个集合的所有组成情况。) 因此,要完成【多表查询】,需要【消除无用的数据】
1、内连接
1.1 隐式内连接:使用where条件消除无用数据
1.2 显式内连接:[inner] join
语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件
2、外连接
这里用主表的概念比较好理解 left 就是主表在左边 right主表在右边 左外连接:查询的是左表所有数据以及其交集部分。 语法: SELECT 字段列表 FROM 表1 left [outer] join 表2 on 条件
演示:
SELECT * FROM emp
SELECT * FROM emp left join dept on dept_id = dept.id
右外链接:查询的是右表所有数据以及其交集部分。 语法: SELECT 字段列表 FROM 表1 right outer join 表2 on 条件
补充:约束
概念︰对表中的数据进行限定,保证数据的正确性、有效性和完整性。 分类:
-
非空约束:not null -
唯一约束:unique -
主键约束: primary key(非空且唯一),一般是id 自动增长 -
外键约束:foreign key(不推荐数据库使用,了解即可) 表的拆分(部门表和员工表) 为了避免删除部门表导致员工表的dep_id失效,会添加外键约束 作用:(dept_id和部门表产生关联) 目的:(当员工表的人和部门表相关id产生关联时,不得删除) 备注:加上constraint 外键名是为了给这个约束命名,后面的修改和删除比较方便 ,也可以不给约束命名
foreign key(员工的外键) referance 关联/引用 的表名(字段名)
阿里巴巴的JAVA规范: 最佳实践: 数据库就是单纯的表 只用来行(数据)和列(字段) 我们想使用多张表的数据,想使用外键(利用程序去实现) 现在都是有外键列,但不设置外键约束,至于保证数据的正确性,就通过程序逻辑保证了
|