1 数据库
1.1什么是数据库
数据库(DB)
概念:数据库仓库
作用:存储数据,管理数据
1.2 数据库分类
关系型数据库:(SQL)
- Mysql,Oracle,SqlSever,DB2,SQlite
- 通过表和表之间,行和列之间的关系进行数据的存储:学员得信息表
非关系型数据库:(NoSQL) not Only
- Redis,MongDB
- 非关系型数据库,对象存储,通过对象的自身的属性来决定。
DBMS(数据库管理系统)
-
数据库的管理软件:科学有效的管理我们的数据,维护和获取数据
-
MySQL,数据库管理系统
1.3 MySQL
mysql是一个关系型数据库管理系统
前世:MysqlAB公司
今生:Oracle旗下公司
开源的数据库软件
体积小,速度快,拥有成本低,所有都要会
中小型网站,和大型网站,集群
安装建议:
- 尽量不要用exe
- 尽量使安装包
每一个sqlyog的执行都能在sqiyog历史记录查看
创建一个表
查看表
查看表(要刷新)
1.3 链接数据库
命令行
mysql -uroot -p123456 --(注释行)链接数据库
update mysql.user set authentication_string=password('123456')where user='root' and Host = 'localhost';--修改用户密码
flush privileges;
-------------------------------------------------
--所有的语句都使用;结尾
mysql> show databases;
--查看数据库
mysql> use school
Database changed
--切换数据库use 数据名
show tables;--查看数据库所有的表
mysql> describe student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(100) | YES | | NULL | |
| age | int(3) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--显示数据库中所有的表的信息
mysql> create database westos
-> ;
Query OK, 1 row affected (0.00 sec)
--创建一个数据库
exit;--退出链接
--单行注释(sql本来注释)
/*
多行注释
*/
数据库xxx语言CRUD增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
2 操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql 不区分大小写
2.1 操作数据库(了解)
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos
- 删除数据库
DROP DATABASE [IF EXISTS] hello
- 使用数据库
USE `school`
- 查看数据库
SHOW DATABASES--查看所有数据库
学习思路:
- 对照sqlyog可视化历史记录查看sql
- 固定的语法或关键字必须要记住
2.2 数据库的数据类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的字节 3个字节
- int 标准的整数 4个字节 常用的
- big 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节(精度问题)
- decimal 字符串形式的浮点数 金融计算的时候,一般是使用decimal
字符串
- char 字符串固定大小的 0~255
- varchar 可变字符串 0~65535 常用的 String
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保持大文本
时间日期
java.util.Date
- date YYY-MM-DD,日期格式
- time HH:mm:ss,时间格式
- datetime YYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳, 1970.1.1 到现在的毫秒数!也很常用
- year 年份表示
null
- 没有值,未知
2.3 数据库的字段属性(重点)
unsigned:
无符号的整数
声明了该列不能声明为负数
zerofill:
- 0填充的
- 不足的位数,使用0填充
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键~inder,必须是整数类型
- 可以自定义设计主键自增的起始值和增长
非空NULL not null
- 假设设置为not null,如果不给它赋值,就会报销
- null,如果不填写值,默认就会null
默认
- 设置默认的值
- sex 默认值为男
扩展:听听就好
2.4 创建数据库表
-- 目标:创建一个school数据库
-- 创建学生表(列,字段) 使用sql创建
-- 学号int 登入密码varchar(20)姓名,性别varcher(2),出生日期(datatime),家庭住址,email
-- 注意点,使用英文(),表的名称和字段尽量使用``括起来
-- 字符串使用 单引号括起来
-- 所有的语句后面加,(英文的),最后一个不要加`student``student`
-- PRIMARY KEY 主键,一般一个表只有一个主键唯一的
CREATE TABLE IF NOT EXISTS `student` (
`id`INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
`name`VARCHAR(30)NOT NULL DEFAULT'匿名' COMMENT'姓名',
`pwd` VARCHAR(20)NOT NULL DEFAULT'123456' COMMENT'密码',
`sex` VARCHAR(10)NOT NULL DEFAULT'男'COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email`VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
格式
CREATE TABLE [IF NOT EXISTS] '表名'(
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释]
)[表的类型][字符集]][注释]
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
2.5 数据表的类型
-- 关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
---|---|---|
事务的支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约 | 不支持 | 支持 |
全文索引 | 支持 | 支持 |
表空间的大小 | 较小 | 较大,大约前方的两倍 |
常规使用操作:
- MYISAM 节约空间,速度块
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都在data文件下,一个文件夹就对应一个数据库
本质还是文件的存储
mysql引擎在物理文件上的区别
- innodb在数据库表只有一个*.frm文件,以及上级目录下的ibdata1文件。
- MYISAM对应文件
*.frm - 表结构的定义文件
*.MYD - 数据文件(data)
*.MYI - 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是mysql默认(latin1)的字符集编码不识别中文
在my.ini中配置默认的编码
character-set-server=utf8
2.6 修改删除
修改
-- 修改表 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teachar1 RENAME AS teacher1
-- 增加表的字段
`student``teacher1`
-- 修改表的字段(重命名,修改约束)
-- 修改约束 ALTER TABLE 表名 MODIFY 字段 约束
ALTER TABLE teacher1 MODIFY age VARCHAR(10)
-- 字段重命名 ALTER TABLE 表名CHANGE 字段 新字段 约束
ALTER TABLE teacher1 CHANGE age age1 INT(1)
-- 删除表的字段
ALTER TABLE teacher1 DROP age1
删除
-- 删除表
DROP TABLE IF EXISTS teacher1
所有的创建和删除操作尽量加上判断,以免报错
注意点:
- ``字段名,使用这个包裹
- 注释 -- /**/
- sql关键大小写不敏感。建议写小写
- 所有符号用英文
3 MySQL数据管理
3.1 外键(了教即可)
方式一,在创建表的时候添加约束
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'年纪id',
`gradename`VARCHAR(50) NOT NULL COMMENT'年纪名称',
PRIMARY KEY(`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
-- 学生表的gradeid字段要去引用年纪表的gradeid
-- 定义外键key
-- 给这个外键添加一个约束(执行引用)references
CREATE TABLE IF NOT EXISTS `student` (
`id`INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
`name`VARCHAR(30)NOT NULL DEFAULT'匿名' COMMENT'姓名',
`pwd` VARCHAR(20)NOT NULL DEFAULT'123456' COMMENT'密码',
`sex` VARCHAR(10)NOT NULL DEFAULT'男'COMMENT'性别',
`gradeid` INT(10) NOT NULL COMMENT'学生的年级',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家`student`庭地址',
`email`VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid`(`gradeid`),
CONSTRAINT `FK_gradeid`FOREIGN KEY (`gradeid`)REFERENCES `grade`(`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
删除有外键关系的表的时候,必须先删除引用别人的表(从表),在删除被引用的表(主表)
方式二
CREATE TABLE IF NOT EXISTS `student` (
`id`INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
`name`VARCHAR(30)NOT NULL DEFAULT'匿名' COMMENT'姓名',
`pwd` VARCHAR(20)NOT NULL DEFAULT'123456' COMMENT'密码',
`sex` VARCHAR(10)NOT NULL DEFAULT'男'COMMENT'性别',
`gradeid` INT(10) NOT NULL COMMENT'学生的年级',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家`student`庭地址',
`email`VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'年纪id',
`gradename`VARCHAR(50) NOT NULL COMMENT'年纪名称',
PRIMARY KEY(`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
-- 修改表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
-- alter table 表 add constraint 约束名 foreign key(`作为外键的列`) references 那个表(那个字段)
都是物理外键,不建议使用
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行和列
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2 DML语言(全部记住)
数据库意义:数据存储,数据管理
DML语言:数据操作语言
- insert
- update
- delete
3.3 添加
insert
-- 插入数据(添加)
-- insert into 表名(字段1,字段2,字段3)value(`值1`,`值2`,`值3`)
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主键自增我们可以省(如果不写表的字段,他就会一一匹配)
-- 一般写插入语句,我们一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade` (`gradename`)
VALUES('大二'),('大一')
INSERT INTO `student`(`name`)VALUES('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('张三','123456','男')
INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`)
VALUES('王五','123456','男','1'),
('李四','990110','女','2')
语法:insert into 表名(字段1,字段2,字段3)value(值1
,值2
,值3
)
注意事项:
-
字段和字段之间使用英文逗号隔开
-
字段是可以省略的,但是后面的值要一一对应,不能少
-
可以同时插入多条数据,values后面的值需要使用逗号隔开即可
-
处理时没有给默认值的一定要去定义添加****
-
INSERT INTO `student`(`name`,`pwd`,`sex`,`gradeid`) VALUES('王五','123456','男','1'), ('李四','990110','女','2') INSERT INTO `student`(`name`,`pwd`,`gradeid`)VALUES('周子龙','zzl990110',1) INSERT INTO `student`(`name`,`pwd`,`gradeid`,`address`)VALUES('小明','zzl990110',2,'安徽')
3.4 删除
delete
语法:delete from 表名 [where 条件]
-- 删除指定数据
DELETE FROM `student` WHERE id=1;
TRUNATE命令
作用:完全清空一个数据库鸟,表的结构和索引表
-- 清空 student 表
DELETE FROM `student`
TRUNCATE `STUDENT`
delete的truncate区别
相同点:都能删除数据,都不会删除表结构
不同:
TRUNCATE 重新设置,自增列 计数器会规0
TRUNCATE不影响事务
了教即可:DELETE删除的问题,重起数据库,现象
innDB 自增列会重1开始(存在内存当中的,断电即失)
MyISAM 继续从上一个自增量开始(存在文件中的,不会丢失)
-- 测试delete和truncate区别
CREATE TABLE `test`(
`id`INT(4) NOT NULL AUTO_INCREMENT,
`coll`VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;
INSERT INTO `test`(`coll`)VALUES('1'),('2'),('3')
DELETE FROM `test`-- 不会影响自增
TRUNCATE TABLE `test` -- 自增会规0
3.5 修改
update 修改谁(条件)set原来的值=新值
-- 修改学员的名字
UPDATE `student` SET`name`='狂神'WHERE id = 1;
-- 不指定条件下,会改动所有表
UPDATE `student` SET`name`='长江七号'
-- 语法
-- update 表名 set conlunm_name = value where 条件
-- 修改多个属性,逗号隔开
UPDATE `student` SET`name`='狂神',`email`='[email protected]'WHERE id = 1;
条件:where子句运算符id等于某个值,大于某个值,在某个区间内修改
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | ||
<>或者!= | 不等于 | ||
> | |||
< | |||
>= | |||
<= | |||
between x and x | 在哪个区间[2,5] | ||
and | 我和你 | ||
or | 我或你 |
-- 通过多个条件定位
UPDATE `student` SET`name`='长江七号'WHERE `name`='狂神' AND sex='男'
语法:update 表名 set conlunm_name = value,[colnum_name,.....] where 条件
注意:
- colnum_name是数据库的列,尽量带上``
- 条件,筛选的条件,如果没有指定则会修改所有的列
- value,具体的一个值也可以是一个变量
4 DQL查询数据(最重点)
4.1 DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它Select
- 简单的查询,复杂的查询它都能做~
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
4.2 指定查询字段
-- 查询全部学生信息
SELECT * FROM student
-- 查询全部成绩
SELECT * FROM grade
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 别名,给结构起一个名字 AS 也可以给字段起别名,也可以给表起别名
SELECT `studentno`AS 学号,`studentname`AS 名字 FROM student AS s
-- 函数 concat(a,b)
SELECT CONCAT('姓名: ',`studentname`) AS 新名字 FROM student
语法:SELECT 字段。。。FROM 表
有的时候,列名字不是那么的见名之意,需要我们起别名:AS
语法:字段名 AS 别名 FROM 表名 AS 别名
去重 distinct
作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询一下有那些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentno` AS 学号 FROM result
-- 发现重复数据,去重 distinct
SELECT DISTINCT `studentno` FROM result
数据库的列
-- 查询系统的版本
SELECT VERSION()
SELECT 100*3-23 AS 计算结果
-- 学员考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 AS 新成绩表 FROM result
数据库中的表达式:文本值,列,NULL,函数.....
select 表达式 from 表
4.3 where条件子句
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
not ! | not a ! a | 逻辑非 |
尽量使用英文符号
SELECT `studentno`,`studentresult` FROM result
-- 查询考试成绩在95到100之间的
WHERE studentresult>=95 AND studentresult<=100
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno`=1000
模糊查询
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为NULL则结果为真 |
is not null | a is not null | 如果操作符不为null,结果为真 |
BETWEEN | a between a and c | 若a在b和c之间,则结果为真 |
LIKE | a like b | SQL匹配,如果a匹配b,则结果为真 |
IN | a in(a1,a2,a3.....) | 假设a在a1,或者a2........其中的某一个值中,结果为真 |
-- ======================模糊查询================================
-- 查询姓小的同学
-- like结合%(代表0到任意个字符) _(一个字符)
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentname`LIKE '小%'
-- 查询姓小的同学后面只有一个字的
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentname`LIKE '小_'
-- 查询名字中有明的同学
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentname`LIKE '%明%'
-- ===========================in==================================
-- 查询 1001,1002,1003号学员
SELECT `studentno`,`studentname`FROM `student`
WHERE `studentno` IN (1,2,3);
-- 查询在安徽的学生
SELECT `studentno`,`studentname`FROM `student`
WHERE `address` IN ('安徽');
-- =========================null not null=========================
-- 查询地址为空的学生null
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`=''OR`address` IS NULL
-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname`FROM `student`
WHERE `borndate`IS NULL
4.4 联表查询
join 对比
-- =================联表查询join==========================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/*
思路
1.需求分析,分析查询字段来自哪些表,(连接查询)
2.确定哪种连接查询?
3.确定交叉点(这两个表哪个数据相同)
4.判断的条件:学生表的studentNo = 成绩表的studentNo
*/
-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
-- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个,就会返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
-- join on 连接查询
-- where 等值查询
-- 思考题(查询了参加考试的同学信息:1学号,2学生姓名,3科目名,分数)
SELECT s.studentno,studentname,sub.subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
-- 我要查询哪些数据 select....
-- 从哪几个表中查FROM表xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两种表然后在慢慢增加
-- From a left join b
-- From a right join b
自连接
自己的表和自己的表连接,核心:一张表拆成两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryId | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps设计 |
查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术技术 | ps技术 |
-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
自己的表和自己的表连接,核心:一张表拆成两张一样的表即可
4.5 分页和排序
排序
-- 排序:升序ASC,降序DESC
-- ORDRER BY 通过哪个字段排序,怎么排
-- 查询的结果根据,成绩降序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON `subjectname`='数据库'
ORDER BY studentresult DESC
分页
-- 语法:limit 起始位置,页面的大小
-- 为什么要分页
-- 缓解数据库的压力
-- 分页,每页只显示五条数据
-- LIMIT 1,5
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON `subjectname`='数据库'
ORDER BY studentresult DESC
LIMIT 1,5
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit X,5 (N-1)*pageSize,pageSize
-- [pageSize:页面大小]
-- [(N-1)*pageSize起始值]
-- [数据总数/页面大小 = 总页数]
练习
-- 思考
-- 查询Java第一学年,课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
INNER JOIN `subject` AS sub
ON subjectname = 'Java'
INNER JOIN result AS r
ON s.studentno = r.studentno AND studentresult>80
ORDER BY studentresult DESC
LIMIT 0,10
4.6 子查询
-- ======================where================================
-- 1.查询数据库考试的所有考试结果(学号,科目名称,成绩),降序排列
SELECT `studentno`,`subjectname`,`studentresult`
FROM result AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname = '数据库'
ORDER BY studentresult DESC
-- 方式二,使用子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE studentno = (SELECT subjectno FROM `subject` WHERE subjectname = '数据库')
ORDER BY studentresult DESC
-- 查询所有数据库的学生的学号
SELECT subjectno FROM `subject` WHERE subjectname = '数据库'
-- 分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE`studentresult`>=80
-- 在这个基础上增加一个科目,高等数学
SELECT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE`studentresult`>=80 AND `subjectno` =
(SELECT subjectno FROM `subject`
WHERE `subjectname` = '高等数学')
-- 查询课程为高等数学且分数不小于80的同学的学号和姓名
SELECT s.`studentno`,`studentname`
FROM student AS s
INNER JOIN `result` AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学' AND `studentresult`>=80
4.7 分组以及过滤
-- 查询不同课程的平均分,最高分,最低分
-- 核心:(根据不同的课程分组)
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>80
5 MYSQL函数
5.1 常用函数
-- =================常用函数==================
-- 数学运算
SELECT ABS(-8)
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(20) -- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- select insert (字段,从哪个位置,替换多少个,替换字段)
SELECT LOWER('ZZL') -- 转小写
SELECT UPPER('zzl') -- 转大写
SELECT INSTR('zzl','l') -- 查看字符串位置
SELECT REPLACE('我爱你','爱','不爱')
SELECT SUBSTR('即使再小的帆也能远航',4,6) -- select substr(字符串,起始位置,截取数量)
SELECT REVERSE('即使再小的帆也能远航') -- 反转
-- 查询性小的同学
SELECT REPLACE(`studentname`,'小','大')AS 新名字 FROM `student`
WHERE `studentname` LIKE '小%'
-- 查询当前的日期(记住)
SELECT CURRENT_DATE() -- 读取当前日期
SELECT CURDATE() -- 读取当前日期
SELECT NOW() -- 读取现在时间
SELECT LOCALTIME() -- 读取本地时间
SELECT SYSDATE() -- 读取系统时间
SELECT YEAR(NOW()) -- 年
-- 系统
SELECT VERSION() -- 版本
5.2 聚合函数
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
....... |
-- ====================聚合函数==================================
SELECT COUNT(studentname) FROM student -- count(指定列),会忽略所有的null值
SELECT COUNT(*) FROM `student` -- COUNT(*),不会忽略null,本质 计算行数
SELECT COUNT(1) FROM student -- COUNT(1),不会忽略null,本质计算行数
-- 都能统计
SELECT SUM(`studentresult`) AS 总合 FROM result
SELECT AVG(`studentresult`) AS 平均值 FROM `result`
SELECT MAX(`studentresult`) AS 最大值 FROM result
SELECT MIN(`studentresult`) AS 最小值 FROM result
5.3 数据库级别的MD5加密
什么是MD5
主要增强算且MD5不可逆
-- ============测试MD5=================
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(50) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan',123456),(2,'zzl',123456),(3,'xg',123456)
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd)
-- 解密(没有解密)
-- 插入时候加密
INSERT INTO testmd5 VALUES(4,'zhangsan',MD5(123456))
UPDATE testmd5 SET `name`='xiaoming' WHERE id=4
-- 如何校验:将用户传递进来的密码,进行md5加密,然后对比加密后的值
SELECT *FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')
5.4 select小结
6 事务
6.1 什么是事务
要么都成功,要么都失败
将一组sql放在一批次中去执行
事务原则:ACID原则(原子性,一致性,隔离性,持久性)(脏读)
一致性
持久性
隔离性
情况一:
脏读:指一个事务读取了另外一个事务未提交的数据
情况二:
不可重复读
情况三:
幻读
原子性
要么都成功要么都失败
一致性
事务前后的数据完整性要保持一致
持久性
事务一旦提交就不可逆,被持久化到数据库中
隔离性
互相隔离
隔离所导致的一些问题上面写
执行事务
-- =========================事务========================
-- mysql是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认)
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交:持久化(成功)
COMMIT
-- 回滚:回到的原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点
事务模拟
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `accout`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name`VARCHAR(20) NOT NULL,
`money`DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO accout(`name`,`money`)
VALUES('A',2000.00),('B',1000.00)
-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务(一组事务)
UPDATE accout SET money=money-500 WHERE `name` = 'A'
UPDATE accout SET money=money+500 WHERE `name` = 'B'
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值