Mysql
1.安装
解压压缩包到所想放置的目录
配置环境变量path
放置ini文件,修改文件内安装路径和数据存储路径
bin目录下管理员打开cmd
//初始化
mysqld -initialize --console
//安装
mysqld install
//启动服务
net start mysql
//进入mysql
mysql -uroot-p+密码
//修改密码
alter user 'root'@localhost' identified with mysql_native_password by '666';
2.数据库概述
- 是长期存储在计算机内有组织,有共享,统一管理的数据集合
- DB(database):存储数据的仓库,其中的数据是有组织有关联的
- DBMS(database management system)数据库管理系统,管理DB的
- SQL (structure query language) 结构化查询语言,专门与DB通信的语言,所有DBMS(MySQL,Oracle, SQLserver等)都支持;
存储位置的不同进行分类:
1,基于磁盘的存储,MySQL,Oracle,SQLServer
2,基于内存的存储,redis非常适合做缓存,
从数据间是否存在关系进行分类:
- 关系型数据库:MySQL,Oracle,SQLServer
- 非关系型数据库:redis,mongodb nosql(not only sql)
数据库中每个表有不同的列和行组成,每一行表示一个单位 类似于java中的对象 每一列表示一个属性
3.字段的数据类型
1,整型
- tinyint(2) 等同于byte的取值范围 -128-127
- tinyint(1) 0 1 等同于java语言 boolean
- int(n) n: 查询的时候单元格宽度 int(11) 显示宽度和数据类型的取值范围是无关的。如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。
- bigint(n): long id 时间:毫秒数
2,小数
- float(M,D),M称为精度,表示总共 的位数;D表达标度,表示小数的位数 12.345
- double(m,d):
- decimal(m,n): BigDecimal DECIMAL 的存储空间并不是固定的,而由精度值 M 决定,占用 M+2 个字节
3,字符型
- char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节
- VARCHAR 和 TEXT 类型是变长类型,其存储需求取决于列值的实际长度,一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串
- MySQL不区分字符和字符串,单引号和双引号类似;
4,日期型
- date: 年月日
- datetime: 年月日 时分秒
- timestamp: 年月日 时分秒 时间戳
4.SQL语句
结构化查询语言,sql语句不区分大小写
DDL(Data Definition Language)
:数据定义语言 create drop alter用来定义数据库对象:库,表,列
DML(Data Manipulation Language)
: insert delete update 用来定义数据库记录(数据)
DQL(Data Query Language)
: select 数据库查询语言 用来查询记录
DCL(Data Control Language)
:commit rollback 定义访问权限和安全级别
4.1DDL语句
1.创建表 多个单词使用下划线分割
CREATE TABLE student(
sid INT,
sname VARCHAR(10),
sgender CHAR(1),
age INT,
score FLOAT(4,2),
birthday TIMESTAMP,
createtime date
)
2,alter修改表结构
-- 新增一个字段
ALTER TABLE student ADD updatetime DATE;
-- 删除一个字段
ALTER TABLE student DROP updatetime;
-- 修改表字段的数据类型
ALTER TABLE student MODIFY sgender VARCHAR(1);
-- 修改表名
ALTER TABLE student RENAME to stu;
3,drop
-- 删除数据库的命令
DROP DATABASE db_test;
-- 删除数据表
DROP TABLE tb_test;
4.2DML语句
1.insert插入一行记录
--对所有字段依次赋值 VALUES和VALUE一样
insert INTO stu VALUES(1,'anne','m',16,'60','2000-10-09','2000-10-22 11:33:20');
--一次插入多条记录
insert into stu values(1,'anna','m',16,'60','2000-10-09','2000-10-22 11:33:20'),(2,'lisa','n',15,'50','1999-10-2','2020-04-04');
--指定字段插入
insert into stu(sid,sname)values(3,'tom');
2.删除行记录
--指定条件删记录
DELETE FROM stu where sid = 3;
3.更新记录内容
--修改表记录内容
UPDATE stu SET age = 19,score = 99 WHERE sid = 1;
4.3DQL语句
1.基本语法
select selection_list(查询的字段)
from table(表名称)
where condition(筛选的条件)
group by grouping_cloumns(分组条件)
having condition(分组后的记录进行条件筛选)
order by cloumns(对结果进行排序)
limit (对记录总数进行限定);
- select 后面可以是表中的字段 常量值 表达式 函数 ;查询的结果是一个虚拟的表格
2.基础查询
--*查询所有的行和列
select * from stu;
-- 指定字段进行查询
SELECT sid,sname FROM t_stu;
3.按条件查询
按条件表达式筛选,> < = != <= >= <>
逻辑表达式 && || and or not
模糊查询 like between and (not between and ) in (列表中的值不支持通配符) is null(not is null)
--指定某一个人条件进行查询
SELECT * FROM stu WHERE sid=2;
--AND是两个条件都要满足
SELECT * FROM stu WHERE sid=2 AND sage=18;
--OR是两个条件满足一个就行
SELECT * FROM stu WHERE sid=2 or score>60;
--查询sid为1,6,8的记录 属于某个集合
SELECT * FROM stu WHERE sid in (1,6,8);
--查询sid不属于1,5,7的记录
SELECT * FROM stu WHERE sid not in (1,5,7);
--查询记录某个字段为null
SELECT * FROM stu WHERE sgender is null;
SELECT * FROM stu WHERE updatetime is NOT null;
--查询成绩在70-80之间的记录
SELECT * FROM stu WHERE score BETWEEN 70 AND 80;
--性别非男的记录
SELECT * FROM stu WHERE sgender <>'m';
SELECT * FROM stu WHERE sgender !='m';
4.模糊查询,处理字符类型
%:表示0或多个字符
_:任意一个字符
--名字由三个字母组成
select * from stu where sname like '___';
--名字由j开头
select * from stu where sname like 'j%';
--第二个字母为a的学生记录
select * from stu where sname like'_a%';
--查询名字中包含字母a的记录
select * from stu where sname like '%a%';
5.去重查询&起别名
--查询学生表中的所有性别 DISTINCT去重
select distinct sgender from stu;
--给查询出来的字段起别名 as可以省略 使用空格
select age as 年龄,sname 姓名 from stu;
6.排序order by子句可以跟单个字段 多个字段 表达式 函数 别名
--查询所有学生记录 按成绩进行降序排序
--缺省是ASC升序(默认)
select * from stu order by score desc;
select * from stu order by score;
--查询所有学生记录,首先按照成绩排序,如果成绩相同。按照姓名进行排序
select * from stu orer by score desc,sname asc;
7.组函数/聚合函数/分组函数
用作统计使用 又称为聚合函数或者统计函数或者组函数
- 聚合函数是用来做纵向运算的函数
- count:统计指定列不为null的记录行数;一般使用count(*)统计行数
- max:计算指定列的最大值,如果指定列是字符串类型那么使用字符串排序运算
- min:计算指定列的最小值 如果指定列是字符串类型 那么使用字符串排序运算
- sum:计算指定列的数值和,如果指定列类型不是数值类型 那计算结果为0
- avg:计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0
- sum , avg一般处理数值型
- max,min,count可以处理任意数据库类型
- 分组函数都忽略了null值,可以和distinct搭配使用
组函数可以出现多个,但是不能嵌套;如果没有group by 子句,结果集中所有行数作为一组
--查询stu表中有生日的人数,统计指定列不为null的记录行数
select count(birthday) a from stu;
--select 200+null
select count(sid)总人数 from stu;
select count(*) 总人数 from stu;
select count(1) 总人数 from stu;
--查询表中成绩大于60的人数
select count(score>60) from stu;
select * from stu where score>60;
--统计月薪与佣金之和大于2500的人数 ifnull(表达式1,表达式2) 如果表达式1为null 那么取表达式2的值否则取表达式1的值
select* from emp where sal+ifnull(comm,0)>2500;
--查询有佣金且有领导的人数;
select count(comm) from emp where mgr is not null;
--查询所有学生的成绩和
select sum(score) 总成绩 from stu;
--查询所有学生的成绩和,年龄和
select sum(score) 总成绩,sum(sage) 年龄和 from stu;
--查询所有员工月薪+佣金和
select sum(sal+ifnull(comm,0)) 总工资 from emp;
select sum(sal)+sum(ifnull(comm,0)) 总佣金 from emp;
--统计所有学生的平均成绩
select avg(score) 平均成绩 from emp;
--查询最高成绩和最低成绩
select max(score),min(score) from emp;
8.group by 分组查询
查询出来的字段要求是group by 后的字段,查询字段中可以出现组函数
group by 后面可以跟聚合函数 可以起别名
--查询每个年龄段的总成绩和成绩
select sum(score) ,age from stu group by age;
--查询每个年龄段的年龄和人数
select count(*),age from stu group by age;
--查询每个年龄段的年龄以及每个年龄段成绩超过60分的人数
select age count(1) from stu where score>60 group by age;
按多个字段分组,后面字段一致的为一组
--按成绩分组
select count(*),score from stu group by score;
--按成绩和年龄分组
select count(*) ,score,age from stu group by score,age;
9.having 子句
where是对分组前进行过滤;having是对分组后进行过滤
where中不能出现分组/聚合函数,having中可以出现
where是比分组先执行的, having是在分组之后执行的;
having后面可以跟别名
--查询工资总和大于8000的部门编号以及工资总和
select deptno , sum(sal)from emp group by deptno having sum(sal)>8000;
--having 中使用别名
selec t deptno,sum(sal) 总薪资 from emp group by deptno having 总薪资>8000;
--查询部门员工个数大于3的,having中使用了别名
select count(1) cc, deptno from emp group by deptno having cc>3;
10.limit
--第一位表示起始索引位置,第二位表示总的长度;分页中会使用
select * feom emp limit 1,5;
select * from emp limit 5 ; 等价于select * from emp limit 0,5;
select 完整语法使用
--查询部门编号 工资总数allsum 工资大于1500 按部门分组 总工资大于7000,按总工资降序排序
select deptno,coount(1),sum(sal) allsum from emp where sal>1500 group by deptno having sum(sal)>7000 order by allsum desc limit 2;
11.多表查询/关联查询
内连接
- 多表等值连接的结果是多表的交集部分,N表连接,至少需要N-1个连接条件,没有顺序要求,一般起别名
- 非等值连接,只要不是等号连接的都是非等值连接
外连接,有主表有从表,主表肯定会显示完整的内容
- 左外连接,以左表为主
- 右外连接,以右表为主
--查询员工信息,要求显示员工号,姓名,月薪,部门名称
--笛卡儿积(a,b)(123) --(a,1)(a,2)(a,3)(b,1)(b,2)(b,3)-->会生成一个中间表
--多表查询,关联条件使用的是等号
--查询员工信息,要求显示员工号,姓名,月薪,部门名称 等值连接
select empno,ename,sal,emp,deptno,dmame from emp,dept where emp.deptno=dept,deptno;
--给表起别名
select empno,ename,sal,a.deptno,dname from emp a, dept b where a.deptno=b.deptno;
--非等值连接
--查询员工信息,要求显示:员工号,姓名,月薪,薪水的级别
select empno,ename,sal,grade from emp e,salgrade sa where e.sal between sa.losal and sa.hisal;
--外连接
--查询员工信息,要求显示员工号,姓名,月薪,部门名称 使用内连接和等值连接等同
select empno,ename,sal,emp.deptno,dname from emp inner join dept on emp.deptno= dept.deptno;
--左外连接 左边的表内容全部显示 ,右边的表没有的以null进行填充
select empno,ename,sal,emp.deptno,dname from emp left join dept on emp.deptno =dept.deptno;
--右外连接,右表内容全部显示,左表没有的以null进行填充
select empno,ename,sal,emp.deptno,dname from from emp right join dept on emp.deptno=dept.deptno;
on后面的条件和where条件的区别:
ON条件:是过滤两个连接表笛卡儿积形成中间表的约束条件
where条件:在没有on 的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或者多表连接中是限制链接形成最终中间表的返回结果的约束
建议:
ON只进行连接操作,where只过滤中间表的记录
12.自连接
通过别名,将同一张表视为多张表;同一张表中某个字段要去关联另一个字段
--查询员工姓名和员工的老板的名称
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
13.子查询
--查询工资为20号部门平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp group by deptno having deptno =20);
5.约束
为了数据的正确性
1.非空约束 一定要给值才能插入
create table a (
id int not null,
sname varchar(20)
)
--提示错误filed 'id' doesn't have a default value
--未给值
2.唯一性约束
create table b(
id int not null unique
)
3.默认约束 给一个默认值
create table c(
id int not null unique default 6,
sname varchar(3)
)
4.主键约束,主键列自动增长,无需赋值,表格必须要有一个主键
create teble stu(
--主键自增
sid int primary key auto_increment,
sname varchar(10) not null,
sgender char(1),
score float(4,1) not null,
birthday timestamp,
stuud vachar(30) unique
)
5.外键约束
外键代表着另外一张表的主键
alter table stu add constraint fk_cid foreign key(courseid) references course(id);
标识列:自增长列
一个表只能有一个标识列
set auto_increment_ increment=2;
--查看标识列的起始和步长
show varitables like '%auto_increment%';
4.常用函数
注意:MySQL中的+就只有运算符的功能;会试图将字符型数值转换为数值型的在进行操作,转换失败则转换为0,若其中有null则结果为null;字符串可以使用concat函数拼接;
1.字符函数:
- length(str)得到的是字节个数 utf8中中文是三个字节
- concat()拼接字符串
- upper/lower(str)
- substr,substring截取字符串MySQL的索引是从1开始的,截取的是字符长度
- instr(str,substr)返回上字串第一次出现的索引,找不到则为0
- trim()
- replace替换全部符合的
sql中的索引你是从1开始的
--字符串连接
select concat('java','sun','aa');
--length()
selec t length(sname),sname from stu;
--指定位置插入
select insert ('javasun',5,3,'oracle');
--字符长度
select char_length('java');
--左填充
select ipad('java',9,'bb');
--右填充
select rpad ('java',9,'bb');
--去除前后空格
select trim (' ja va ');
--重复指定次数
seled=ct repeat('ja',4);
--字符串替换
select replace('javaoror','or','sun');
--截取字串
select substring(’javasun',5,3);
2.数学函数
- round() 四舍五入
- ceil()向下取整
- floor()向下取整
- truncate()截断
- mod()取余
--去绝对整
select abs(-32);
--向上取最小整数
select ceil(3.2);
--向下取最大整数
select floor(3.2);
select floor(score),score from stu;
--取余数
select mod(21.3);
--得到0-1之间的随机值
select ranbd();
--有两位小数的四舍五入值
select round(5.7888.,2);
--截断,小数位保持2位
select truncate(5.679888,2);
3.日期函数
- now()返回日期+时间
- curdate()返回系统日期不包含时间
- curtime()返回当前时间,不包含日期
- year()年 获取指定的年 month() mothname()
- str_to_date 将字符通过指定的格式转换为日期
- date_format将日期转换为字符
- datediff返回两个日期相差天数
--当前日期,当前时间,日期和时间
select curdate(),curtime(),now();
--指定日期是一年中的第几周
select week(now());
--返回指定日期的年份
select year(now());
--返回指定时间的小时
select hour(now(),hour(curtime()));
--返回date的月份名
select monthname(now());
--计算时间相隔的天数
select datediff('2021-1-15','2021-1-12');
select datediff(now(),'2021-1-10');
--把日期按指定的格式进行转换
select date_format(now(),'%y-%m-%d %h:%i:%s');
--把字符串转日期
select str_to_date('2021-1-12','%y-%m-%d');
--将毫秒数转换为时间格式
select from_unixtime(646545353);
4.其他函数
- version()版本号
- user()当前账号
- if(exp1,exp2,exp3)如果exp1为true,取exp2的值 否则取exp3的值
select version();
select user();
select database();
select if(10>2,'10','2')a;
5.数据库范式
- 第一范式(1NF)用来确保每列的原子性,要求每列(或者每个属性值)都是不可再分的最小数据单元(也称为最小的原子单元)。
- 第二范式(2NF)在第一范式的基础上再进一层,要求表中的每列都与主键相关,即要求实体的唯一性,如果第一个表满足第一范式,并且除了逐渐以外的其他列全部都依赖于该主键,那么该表满足第二范式。
- 第三范式(3NF)在第二范式的基础上再进一层,第三范式是确保每列都和主键列直接相关,而不是间接相关,即限制列的冗余性。如果一个关系满足第一范式,并且除了主键以外的其他列都依赖于主键列,列于列之间不存在相互依赖关系则满足第三范式。
数据库存储引擎:在MySQL中的数据使用不同的存储技术存储在文件或内存中
--查看MySQL中所用到的存储引擎
show engines;
innodb是支持事物的
而myisam,memory不支持事务
6.数据库事务
1.什么是事务
-
Transaction
-
事务:一个最小的不可再分的工作单元;通常一个事物对应着一个完整的业务(例如银行账户转账业务,该业务员就是一个最小的工作单元)
-
一个完整的业务需要批量的DML(insert,update,delete)语句共同联合完成
-
事务只和DML语句有关,或者说DML语句才有事务。这个和业务逻辑有关,业务逻辑不同,DML语句的个数不同。
2.事务四大特性ACID
-
原子性(Atomicity):事务不可分割的最小工作单元,事务内的操作要么全做,要么全不做。事物具有要么成功,要么全部失败,折就是原子性。
-
一致性(Consistency):在事务执行前数据库的数据处于正确的状态,需事务执行完之后数据库的书库依然处于正确的状态,及数据完整性约束没有被破坏;如A和B转账,不管转账是否成功,转账之后A和B的帐户总额和转账之前是相同的
-
隔离性(Isolation):当多个事务处于并发访问同一个数据库资源时,事务之间相互影响,不同的隔离级别决定了各个事务对数据资源访问的不同行为
-
持久性(Durability):事务一旦执行成功,他对数据库的数据的改变是不可逆的
在业务逻辑层,一个service层可能需要执行一次或多次增删改操作,如果这期间发生了异常,数据库事务不回滚的话数据库的数据就会不完整,举个例子,订单信息中包含订单明细,现在在保存订单的业务逻辑方法中,先保存订单成功了,再保存订单明细,如果保存订单明细的过程中失败了,肯定希望之前的保存订单数据回滚
3.和事务相关的语句
- 开启事务 start transaction
- commit:提交
- rollback:回滚
4.事务何时开启何时结束
开始标志:任何一条DML语句(insert,update,delete)执行,标志着事务的开启
结束标志:
提交:成功的结束,将所有DML语句操作历史和底层硬盘数据来一次同步
回滚:失败的结束,将所有DML语句操作历史记录全部清空
5.事务和底层数据库的关系
在事务进行过程中,未结束之前,DML语句是不会更改底层数据,只是奖励是操作记录一下,在内存中完成记录。只有在事务结束的时候,而且是成功的结束的时候,才会修改底层硬盘文件中的数据。如果执行失败,则清空所有的历史操作记录,不会对底层硬盘文件中数据做出任何修改
--开启事务
START TRANSACTION;
INSERT INTO stu(sid,sname) VALUES(5,'jerry');
--此命令执行会出错?
INSERT into stu(sid,sname) VALUES(6,'TOM','aa');
--提交 出错之后会rollback
COMMIT;
6.在MySQL中事务的提交和回滚
在MySQL中,默认情况下,事务是自动提交的,也就是说,只要执行一条DML语句就开启了事务,并且提交了事务,自动提交机制是可以关闭的,可以由程序控制何时提交。
7.事务的隔离级别
不同事务之间具有隔离性,隔离级别分为四个:
读未提交:read uncommitted(读到未提交的数据)
事务A和事务B,事务A未提交的数据,事务B可以读到
这里读到的数据叫做“脏数据”(已经被改动的数据)
这种隔离级别最低,这种级别一般是在理论上存在,数据库隔离级别一般都高于该级别
读已提交:read committed(读到已提交的数据)
事务A和事务B,事务A提交的数据,事务B才可以读取到
这种隔离级别高于读未提交
这种级别可以避免“脏数据”
这种隔离级别会导致“不可重复读取”,(事务B两次读取到的数据不一致)
这是Oracle默认隔离级别
可重复读:repeatable read
事务A和事务B,事物A提交之后的数据,事务B读取不到
事物B是可重复读取数据
这种隔离级别高于读已提交
换句话说,对方提交之后的数据我还是读取不到
这种隔离级别可以避免”不可重复读取“,达到可重复读取
这是MySQL默认级别
虽然可以达到可重复读取,但是会导致”幻读“(A把所有数据都清空了,B在这个时候修改了其中一条数据,当A结束后发现还存在一条数据,就好像产生了幻觉一样,这就是幻读)
串行化:serializable
事务A和事务B,事务A在操作数据库的时候,事务B只能排队等待
这种隔离级别很少使用,吞吐量太低,用户据体验差(特别的慢)
这种级别可以避免”幻读“,每一次读取的都是数据库中真实存在的数据,事务A与事务B串行而不并发
show variables like 'transaction_isolation';