MySQL数据库学习
1.初识MySQL
1.1数据库的定义
数据库(DataBase,简称DB)，是一个按数据结构来存储和管理数据的计算机软件系统。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
概念 : 长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据”仓库”
作用 : 保存,并能安全管理数据(如:增删改查等),减少冗余…
1.2MySQL简介
MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
概念 : 是现在流行的开源的,免费的 关系型数据库
历史 : 由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
特点 :
- 免费 , 开源数据库
- 小巧 , 功能齐全
- 使用便捷
- 可运行于Windows或Linux操作系统
- 可适用于中小型甚至大型网站应用
RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：
- 1.数据以表格的形式出现
- 2.每行为各种记录名称
- 3.每列为记录名称所对应的数据域
- 4.许多的行和列组成一张表单
- 5.若干的表单组成database
1.3关系型数据库
(SQL)
- MySQL , Oracle , SQL Server , SQLite , DB2 , …
- 关系型数据库通过外键关联来建立表与表之间的关系
1.4非关系型数据库
(NOSQL)
- Redis , MongoDB , …
- 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定
1.5连接数据库
打开MySQL命令窗口
-
在DOS命令行窗口
-
在窗口中进入
安装目录\mysql\bin
- 可设置环境变量
连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !
update user set password=password('123456')where user='root'; 修改密码
flush privileges; 刷新数据库
show databases; 显示所有数据库
use dbname； 打开某个数据库
show tables; 显示数据库mysql中所有的表
describe user; 显示表mysql数据库中user表的列信息
create database name; 创建数据库
use databasename; 选择数据库
exit; 退出Mysql
? 命令关键词 : 寻求帮助
# 表示注释
（1）结构化查询语句SQL
结构化查询语句分类 :
| 名称 | 解释 | 命令 |
|---|---|---|
| DDL 数据定义语言 | 定义和管理数据对象，如数据库，数据表等 | CREATE、DROP ALTER |
| DML 数据操作语言 | 用于操作对象中所包含的数据 | INSERT UPDATE DELETE |
| DQL 数据查询语言 | 用于查询数据表结构 | SELECT |
| DCL 数据控制语言 | 用来管理数据库的语言，包括管理权限及数据更改 | GRANT COMMIT ROLLBACK |
（2）命令行操作数据库
创建数据库 : create database [if not exists] 数据库名;
删除数据库 : drop database [if exists] 数据库名;
查看数据库 : show databases;
使用数据库 : use 数据库名;
CREATE DATABASE java;
DROP DATABASE IF EXISTS java;
CREATE DATABASE IF NOT EXISTS class01;
DROP DATABASE IF EXISTS class01;
USE `class01` ;
SHOW DATABASES -- 查看所有的数据表
1.6创建数据表
属于DDL的一种
语法 :
create table [if not exists] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
#...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];
说明 : 反引号用于区别MySQL保留字与普通字符而引入的 (键盘esc下面的键).
（1）数据值和列类型
列类型 : 规定数据库中该列存放的数据类型
-
数值类型
-
字符串类型
-
日期和时间型
-
NULL值
- 理解为”没有值”或”未知值”
- 不要用NULL进行算术运算 , 结果仍为NULL
（2）数据字段属性
UnSigned :
- 无符号的整数
- 声明该数据列不允许负数 .
ZEROFILL :
- 0填充的
- 不足位数的用0来填充 , 如int(3),5则为005
Auto_InCrement :
- 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
- 通常用于设置主键 index,必须为整数类型。
- 可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL :
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
DEFAULT :
- 默认的
- 用于设置默认值
- 例如,性别字段,默认为”男” , 否则为 “女” ; 若无指定该列的值 , 则默认值为”男”的值
# 创建学生表(列,字段)
# 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
# 创建表之前 , 一定要先选择数据库
CREATE TABLE IF NOT EXISTS `student02` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '同学' COMMENT '姓名',
`sex` VARCHAR(2) 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
# 查看数据库的定义
SHOW CREATE DATABASE school;
# 查看数据表的定义
SHOW CREATE TABLE student;
# 显示表结构
DESC student;
# 设置严格检查模式(不能容错了)
SET sql_mode='STRICT_TRANS_TABLES';
MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等…
常见的 MyISAM 与 InnoDB 类型
| 名称 | MyISAM | InnoDB |
|---|---|---|
| 事务处理 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约2倍 |
- 适用MyISAM : 节约空间及相应速度
- 适用InnoDB : 安全性 , 事务处理及多用户操作数据表
1.7数据表的存储位置
- MySQL数据表以文件方式存放在磁盘中
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .
- 注意 :
- InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的.ibd文件，
- MyISAM类型数据表对应三个文件 :
- * . frm — 表结构定义文件
- * . MYD — 数据文件(data)
- * . MYI — 索引文件(index)
1.8修改数据库
(1)修改表( ALTER TABLE )
-
修改表名 :
ALTER TABLE 旧表名 RENAME AS 新表名
-
添加字段 :
ALTER TABLE 表名 ADD 字段名 列属性[属性]
-
修改字段 :
-
ALTER TABLE 表名 MODIFY 字段名 列类型[属性] -
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
-
-
删除字段 :
ALTER TABLE 表名 DROP 字段名
-- 修改表名 `ALTER TABLE 旧表名 RENAME AS 新表名`
ALTER TABLE student03 RENAME AS 03student;
-- 增加字段 ALTER TABLE 表名 ADD字段名 列属性[属性]
ALTER TABLE 03student ADD age INT(2);
-- 修改表的字段01 ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 03student MODIFY age VARCHAR(4);-- 修改约束
-- 修改表的字段02 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
ALTER TABLE 03student CHANGE age setage INT(2); -- 字段重命名
-- 删除字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE 03student DROP setage;
(2)删除数据表
-
语法 :
DROP TABLE [IF EXISTS] 表名- IF EXISTS为可选 , 判断是否存在该数据表
- 如删除不存在的数据表会抛出错误
小结：
1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！
2. 每个库目录存在一个保存当前数据库的选项文件db.opt。
3. 注释：
单行注释 # 注释内容
多行注释 /* 注释内容 */
单行注释 -- 注释内容 (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）)
4. 模式通配符：
_ 任意单个字符
% 任意多个字符，甚至包括零字符
单引号需要进行转义 \'
5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。
6. SQL对大小写不敏感
7. 清除已有语句：\c
2.MySQL数据管理
2.1 外键管理（物理外键）
外键概念
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。(引用别人的表：从表 ;被引用的表：主表)
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。
创建外键
方式一：在创建表的时候添加外键
-- 创建年级表
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
-- 给这个外键添加约束（执行引用）
-- 创建学生表
CREATE TABLE `person` (
`studentid` INT(4) NOT NULL COMMENT '学号', -- comment 注释，备注
`studentname` VARCHAR(20) NOT NULL DEFAULT '学生姓名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别', -- 微整型
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
PRIMARY KEY (`studentid`), -- 设置该表主键
KEY `FK_gradeid` (`gradeid`), -- 添加外键，`FK_gradeid`（FOREIGN KEY） 连接两个表之间的关系
-- constraint 添加约束条件
-- FOREIGN 外来的 ， FOREIGN KEY（字段）
-- REFERENCES 引用，REFERENCES `表名`（字段名）
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
方式二：创建表时没有外键，通过修改表添加外键
ALTER TABLE `person`
ADD CONSTRAINT `FK_gradeid`FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)
-- 代码过长可用回车换行，单行执行可不在句尾添加;
ALTER TABLE 表名
ADD CONSTRAINT 约束名 ADD CONSTRAINT（作为外键的列）REFERENCES 被引用的表名（字段名）;
备注：被引用的表不能直接删除，要先删除引用它的表才能正确删除
引用别人的表：从表
被引用的表：主表
# 删除外键
ALTER TABLE person DROP FOREIGN KEY FK_gradeid;
# 发现执行完上面的,索引还在,所以还要删除索引
# 注:这个索引是建立外键的时候默认生成的
ALTER TABLE person DROP INDEX FK_gradeid;
以上两种方式都是物理外键，数据库级别的外键，一般不建议使用，避免数据库过多造成困扰
最佳实现：
数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想使用多张表的数据，想使用外键（通过程序去实现）
2.2 数据库数据管理
数据库意义
- 数据存储
- 数据管理
管理数据库数据方法
- 通过SQLyog等管理工具管理数据库数据
- 通过DML语句管理数据库数据
DML语言
- DML(数据操作语言)
- 用于操作数据库对象中所包含的数据
- 包括 :
- INSERT (添加数据语句)
- UPDATE (更新数据语句)
- DELETE (删除数据语句)
添加数据
INSERT命令
- 语法 :
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES(（'值1'）,（'值2'）,（'值3'）,...)
/*
注意：
- 字段或值之间用英文逗号隔开.
- '字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.
- 可同时插入多条数据 , values 后用英文逗号隔开.
*/
创建一个表：
-- DML(数据操作语言) 练习
-- 创建课程表
CREATE TABLE `subject` (
`subjectid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '课程序号',
`subjectname` VARCHAR(50) NOT NULL COMMENT '课程名称',
PRIMARY KEY (`subjectid`) -- 设置主键
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入一个字段
INSERT INTO `subject`(subjectname) VALUES ('java');
-- 一个字段插入多个值
INSERT INTO `subject`(subjectname) VALUES ('c语言'),('HTML5');
-- 为多个列同时插入字段
INSERT INTO `subject`(`subjectname`,`subjectrank`,`subjectstar`) VALUES('java','hard','8')
-- 为多个列插入多个字段
INSERT INTO `subject`(`subjectname`,`subjectrank`,`subjectstar`)
VALUES('java','hard','8'),('C++','just','6'),('HTML5','easy','4')
错误收藏栏：
-- 由于主键自增，我们可以省略（如果不写表的字段，它就会一一匹配）
INSERT INTO `subject` VALUES ('c++');
1 queries executed, 0 success, 1 errors, 0 warnings
查询：INSERT INTO `subject` VALUES ('c++')
错误代码： 1136
Column count doesn't match value count at row 1
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
修改数据
UPDATE命令
- 语法 :
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
注意 :
- column_name 为要更改的数据列
- value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
- condition 为筛选条件 , 如不指定则修改该表的所有列数据
update 修改谁 (条件) set 原来的值=新值
-- 修改`subjectname`
UPDATE `subject` SET `subjectname`='计科课表'WHERE `subjectid`=1;
-- 不指定条件下会改变所有表
UPDATE `subject` SET `subjectname`='新思路'
-- 修改多个属性，用逗号,隔开
UPDATE `subject` SET `subjectname`='java',`subjectrank`='hard',`subjectstar`='8'WHERE `subjectid`=2;
-- 通过多个条件定位数据
UPDATE `subject` SET `subjectname`='java'WHERE `subjectrank`='hard'AND `subjectstar`='8';
修改数据：
-- DML(数据操作语言) 练习
-- 创建课程表
CREATE TABLE `subject` (
`subjectid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '课程序号',
`subjectname` VARCHAR(50) NOT NULL COMMENT '课程名称',
`subjectrank`VARCHAR(50) NOT NULL COMMENT '课程难度',
`subjectstar`INT(10) NOT NULL COMMENT '课程评分',
PRIMARY KEY (`subjectid`) -- 设置主键
) ENGINE=INNODB DEFAULT CHARSET=utf8
UPDATE `subject` SET `subjectname`='计科课表'WHERE `subjectid`=1;
-- 不指定条件下会改变所有表
UPDATE `subject` SET `subjectname`='新思路'
-- 修改多个属性，用逗号,隔开
UPDATE `subject` SET `subjectname`='java',`subjectrank`='hard',`subjectstar`='8'WHERE `subjectid`=2;
-- 通过多个条件定位数据
UPDATE `subject` SET `subjectname`='java'WHERE `subjectrank`='hard'AND `subjectstar`='8';
删除数据
DELETE命令
-
语法
DELETE FROM 表名 [WHERE condition];
注意 : condition为筛选条件 , 如不指定则删除该表的所有列数据
TRUNCATE命令
- 用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;
- 语法 : TRUNCATE [TABLE] table_name;
注意 : 区别于DELETE命令
- 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
- 不同 :
- 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响
-- 1.删除全部数据
DELETE FROM `subject` --不会影响自增
-- 2.删除指定数据
DELETE FROM `subject`WHERE `subjectid`=1;
-- TRUNCATE
截断表;截除;删除记录;语句
-- 清空 `subject` 表
TRUNCATE `subject` --自增会归零
3.Mysql：使用DQL查询数据
3.1DQL语言
DQL( Data Query Language 数据查询语言 )
- 查询数据库数据 , 如SELECT语句
- 简单的单表查询或多表的复杂查询和嵌套查询
- 是数据库语言中最核心,最重要的语句
- 使用频率最高的语句
-- 创建学生信息表
DROP TABLE IF EXISTS `student_table`;
CREATE TABLE `student_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number`
VARCHAR(20) DEFAULT NULL COMMENT '学生编号',
`name`
VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`birthday` VARCHAR(20) DEFAULT NULL COMMENT '出生年月日',
`sex`
INT(1) DEFAULT NULL COMMENT '性别(0 男 1 女 2 未知)',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='学生信息表';
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190901','小王
','1995-01-01',0);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190902','小张
','1994-03-06',1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190903','小李
','1995-02-07',1);
INSERT INTO student_table(number,NAME,birthday,sex) VALUES('B190904','小红
','1997-08-22',0);
-- 创建课程信息表
DROP TABLE IF EXISTS `course_table`;
CREATE TABLE `course_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number` VARCHAR(20) DEFAULT NULL COMMENT '课程编号',
`name` VARCHAR(20) DEFAULT NULL COMMENT '课程名称',
`teacherNumber` VARCHAR(20) DEFAULT NULL COMMENT '教师编号',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='课程信息表';
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('100','英语','T001');
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('101','高数','T002');
INSERT INTO course_table(number,NAME,teacherNumber) VALUES('102','思修','T003');
-- 创建成绩信息表
DROP TABLE IF EXISTS `score_table`;
CREATE TABLE `score_table` (
`id`
BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`st_number` VARCHAR(20) DEFAULT NULL COMMENT '学生编号',
`co_number` VARCHAR(20) DEFAULT NULL COMMENT '课程编号',
`score`
INT(20) DEFAULT NULL COMMENT '课程分数',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='成绩信息表';
INSERT INTO score_table(st_number,co_number,score) VALUES('B190901','100',80);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190901','101',90);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','100',95);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','101',60);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190902','102',70);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','100',90);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','101',79);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190903','102',85);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190904','100',60);
INSERT INTO score_table(st_number,co_number,score) VALUES('B190904','101',70);
-- 创建教师信息表
DROP TABLE IF EXISTS `teacher_table`;
CREATE TABLE `teacher_table` (
`id` BIGINT(64) NOT NULL AUTO_INCREMENT COMMENT 'id',
`number` VARCHAR(20) DEFAULT NULL COMMENT '教师编号',
`name` VARCHAR(20) DEFAULT NULL COMMENT '教师姓名',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='教师信息表';
INSERT INTO teacher_table(number,NAME) VALUES('T001','张三');
INSERT INTO teacher_table(number,NAME) VALUES('T002','张四');
INSERT INTO teacher_table(number,NAME) VALUES('T003','王五');
指定查询字段
查询表结果时 , 可指定查询结果的数据列
- 查询表中所有的数据列结果 , 采用 ” * “符号;
- 如 :
SELECT * FROM student; - 但是效率低 , 不推荐 。
- 如 :
-- 指定查询字段
-- 查询全部的学生
SELECT * FROM `student_table`
-- 查询指定字段
SELECT `number`,`name`,`birthday` FROM `student_table`
AS子句作为别名
AS子句作用 :
- 可给数据列取一个新别名
- 可给表去一个新别名
- 可把经计算或总结的结果用另一个新名称来代替
-- 给结果起别名 AS 可以给字段起别名，也可以给表起别名
SELECT `number` AS '学生学号' ,`name` AS 学生姓名,`birthday`AS 出生日期 FROM `student_table`AS 学生信息表
经我验证，发现 AS 别名可以加单引号，也可以不加.
-- 函数 Concat(a,b)
-- 单个拼接
SELECT CONCAT('姓名: ',`name`)AS 学生姓名 FROM `student_table`
-- 多个拼接
SELECT CONCAT('姓名: ',`name`)AS 学生姓名,CONCAT('学号: ',`number`) AS 学生信息 FROM `student_table`
SQL练习：
- 查询出每门课都大于等于 80 分的学生学号和姓名？
-- 查询出每门课都大于等于 80 分的学生学号和姓名
SELECT a.number,a.name
FROM student_table a
LEFT JOIN score_table AS b ON b.st_number=a.number
GROUP BY b.st_number HAVING MIN(b.score)>=80;
- 查询课程英语的成绩比课程高数的成绩高的所有学生的学号？
-- 查询课程英语的成绩比课程高数的成绩高的所有学生的学号
SELECT a.st_number FROM(SELECT st_number,score FROM score_table WHERE co_number='100') a,
(SELECT st_number,score FROM score_table WHERE co_number='101') b
WHERE a.score>b.score AND a.st_number=b.st_number;
- 查询平均成绩大于 60 分的同学的学号和平均成绩？
-- 查询平均成绩大于 60 分的同学的学号和平均成绩
SELECT a.st_number AS '学号',AVG(a.score) AS '平均成绩'
FROM (SELECT st_number,score FROM score_table) a
GROUP BY a.st_number HAVING AVG(a.score)>60;
- 查询所有同学的学号、姓名、选课数、总成绩 ？
-- 查询所有同学的学号、姓名、选课数、总成绩
SELECT a.st_number AS 学号, b.name AS 姓名,
COUNT(a.co_number) AS 选课数, SUM(a.score) AS 总成绩
FROM score_table a,student_table b
WHERE a.st_number = b.number
GROUP BY a.st_number, b.name
- 查询姓“张”的老师的个数？
-- 查询姓“张”的老师的个数
SELECT COUNT(*) FROM teacher_table WHERE NAME LIKE '张%';
- 查询同时学过英语课程和思修课程的同学的学号、姓名？
-- 查询同时学过英语课程和思修课程的同学的学号、姓名
SELECT number, NAME FROM student_table
WHERE number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =100)
AND number IN (SELECT st_number FROM score_table sc WHERE sc.co_number =102);
- 查询学过”王五“老师所教课程的所有同学的学号、姓名？
-- 查询学过”王五“老师所教课程的所有同学的学号、姓名
SELECT a.number,a.name FROM student_table a,score_table b
WHERE a.number = b.st_number AND b.co_number
IN(SELECT c.number FROM course_table c,teacher_table d WHERE c.teacherNumber = d.number AND
d.name ='王五');
- 查询各科成绩最高分和最低分？
-- 查询各科成绩最高分和最低分
SELECT ct.name AS 课程, MAX(st.score) AS 最高分,MIN(st.score) 最低分
FROM score_table st
LEFT JOIN course_table ct ON ct.number=st.co_number
GROUP BY st.co_number;
去重distinct
--distinct:去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条
-- 查询全部的学生
SELECT * FROM `student_table`
-- 查询全部的成绩
SELECT *FROM `score_table`
-- 查询有哪些同学参加了考试
SELECT `st_number` FROM `score_table`
-- 去掉重复项
SELECT DISTINCT `st_number` FROM `score_table`
数据库中的列（表达式）
-- 数据库的列（表达式）
SELECT VERSION() -- 查询系统版本（函数） -- 5.7.19
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式） -- 299
SELECT @@auto_increment_increment -- 查询自增的步长（变量）-- 1
-- 学生考试成绩+1分查看
SELECT `st_number`,`score` +1 AS '学生提分后成绩' FROM `score_table`
-- 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量...
-- select 表达式 from 表
3.2where条件语句
-
用于检索数据表中 符合条件 的记录
-
搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.
-
搜索条件的组成
- 逻辑操作符
- 比较操作符
- 用于模糊查询 .
-
注意 :
- 数值数据类型的记录之间才能进行算术运算;
- 相同数据类型的数据之间才能进行比较;
-- 查询学生成绩
SELECT `st_number`,`score`FROM `score_table`
-- 查询成绩在60-80的学生
SELECT `st_number`,`score`FROM `score_table`
WHERE `score`>=60 AND `score`<=80
-- 等价
SELECT `st_number`,`score`FROM `score_table`
WHERE `score`>=60 && `score`<=80
-- 模糊查询（区间）
SELECT `st_number`,`score`FROM `score_table`
WHERE `score` BETWEEN 75 AND 100
-- 除了`st_number`= B190903 号学生之外的同学的成绩
SELECT `st_number`,`score`FROM `score_table`
WHERE NOT `st_number`= 'B190903'
模糊查询
-- 模糊查询
-- 查询姓新的同学
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '新%'
-- like结合使用的通配符：%（代表0~任意个字符） _(代表一个字符)
-- 查询姓新的同学后面只有一个字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '新_'
-- 查询姓新的同学后面只有两个字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '新__'
-- 查询名字中含有新字的
SELECT `number`,`name`FROM`student_table`
WHERE `name` LIKE '%新%'
-- 查询学号为 B190903，B190904，B190905的学生姓名
SELECT `number`,`name`FROM`student_table`
WHERE `number`IN('B190903','B190904','B190905')
-- 查询未知性别的同学
SELECT `number`,`name`,`sex`FROM`student_table`
WHERE `sex`IN('2')
-- 查询生日日期没有填写的同学
SELECT `name`FROM `student_table`
WHERE `birthday` IS NULL
-- 查询生日日期没有填写的同学
SELECT `name`FROM `student_table`
WHERE `birthday`=''OR`birthday`IS NULL
-- 查询生日日期已经填写的同学
SELECT `name`FROM `student_table`
WHERE `birthday` IS NOT NULL
连接查询
join 对比
联表查询
连接查询
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join
(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join
(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
等值连接和非等值连接
#查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
# 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
# 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
# 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
#查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
#思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
来自网络截图:
| 操作符名称 | 功能描述 |
|---|---|
| INNER JOIN | |
| LEFT JOIN | |
| RIGHT JOIN |
Column 'StudentNo' in field list is ambiguous
字段列表中的“StudentNo”列不明确
-- 联表查询练习
-- 查询参加了考试的同学（姓名，学号，科目编号，成绩）
SELECT *FROM`student`
SELECT *FROM `result`
/*
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询（7种）
3.确定交集（数据相同）
4.判断的条件 学生表中的 `StudentNo`= 成绩表中的 `StudentNo`
*/
-- join 连接的表 on (判断的条件) 连接查询
-- where 等值查询
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` AS s -- 两张表都有`StudentNo`,为避免模棱两可，取别名加以区分
INNER JOIN `result` AS r
-- 添加判断条件，通过共有的`StudentNo`建立连接关系
WHERE s.`StudentNo`=r.`StudentNo`
-- right join=============
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s -- 取别名AS可以省略，空格分隔
RIGHT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
-- lift join=============
SELECT s.`StudentNo`, `StudentName`,`SubjectNo`,`StudentResult`
FROM `student` s -- 取别名AS可以省略，空格分隔
LEFT JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
自连接
categoryid pid categoryName
2 1 信息技术
3 1 软件开发
4 3 数据库
5 1 美术设计
6 3 web开发
7 5 ps技术
8 2 办公信息
父类
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
| 2 | 8 | 办公信息 |
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
# 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
# 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
# 创建一个表
-- categoryid 类别
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
# 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES
('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
# 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
# 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
#思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
# 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`
# 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid
# 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
排序和分页
/*============== 排序 ================
语法 : ORDER BY --以...排序
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。 -- 升序 ascending order
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。 --降序 descending order
*/
# 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
# 按成绩降序排序
# 按成绩升序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
#ORDER BY StudentResult DESC , studentno
ORDER BY StudentResult ASC , studentno
/*============== 分页 ================
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好处 : (用户体验,网络传输,查询压力)
推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
......
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]
*/
# 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='C语言-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- LIMIT 5,5
#查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'AND`StudentResult`>80 -- and `StudentResult`<=100
ORDER BY StudentResult DESC
LIMIT 0,10
子查询
/*============== 子查询 ================
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/
# 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
#方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;
#方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;
#查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
#方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80
#方法二:使用连接查询+子查询
#分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
#在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'
)
#分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
#方法三:使用子查询
/*
练习题目:
查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
使用子查询,查询郭靖同学所在的年级名称
*/
4.Mysql：MySQL函数
官方文档 : https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
常用函数
分类: 数学函数 , 字符串函数 , 日期和时间函数 , 系统信息函数
# 数学函数 (这里只列出一些常用的)
SELECT ABS(-8); /*绝对值*/
-- absolute value
SELECT CEILING(9.4); /*向上取整*/
-- 天花板 上限 上升限度
SELECT FLOOR(9.4); /*向下取整*/
-- 地板 楼层 自由体操
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
-- 产生均匀分布随机数
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
-- 符号
SELECT SIGN(-4);
SELECT SIGN(8);
#字符串函数
SELECT CHAR_LENGTH('新思路团队都是大佬'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','相信','自己'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我想进新思路',2,1,'一定努力'); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('WANWAN'); /*小写*/
SELECT UPPER('wanwan'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('在新思路能学习到很多东西','很多东西','新知识'); /*替换字符串*/
SELECT SUBSTR('新思路是一个很棒的团队',4,8); /*截取字符串,开始和长度*/
SELECT REVERSE('Java EE'); /*反转，逆向*/
#日期和时间函数
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
/*获取年月日,时分秒*/
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
#系统信息函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
# 查询姓李的同学,改成立
SELECT REPLACE(studentname,'李','立') AS 新名字
FROM student WHERE studentname LIKE '李%';
聚合函数
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| vag() | 求平均值 |
| max() | 求最大值 |
| min() | 求最小值 |
#聚合函数
/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
-- 返回记录的总数
SELECT COUNT(*) FROM student;
-- 效率低，不建议使用
SELECT COUNT(1) FROM student;
-- 推荐
SELECT SUM(StudentResult) AS 总和 FROM result;
-- 求一列的总和
SELECT AVG(StudentResult) AS 平均分 FROM result;
-- 求一列的平均分
SELECT MAX(StudentResult) AS 最高分 FROM result;
-- 求一列的最高分
SELECT MIN(StudentResult) AS 最低分 FROM result;
-- 求一列的最低分
# 查询不同课程的平均分,最高分,最低分
# 前提:根据不同的课程进行分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;
/*
where写在group by前面.
要是放在分组后面的筛选
要使用HAVING..
*/
count分析
执行效果：
1. count(1) and count(*)
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！
从执行计划来看，count(1)和count(*)的效果是一样的。 但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*)，sql会帮你完成优化的 因此：count(1)和count(*)基本没有差别！
2. count(1) and count(字段)
两者的主要区别是
（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
3. count(*) 和 count(1)和count(列名)区别
执行效果上：
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优。
MD5 加密
MD5 百度百科:https://baike.baidu.com/item/MD5/212708?fr=aladdin
简介：
MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
原理：
MD5算法的原理可简要的叙述为：MD5码以512位分组来处理输入的信息，且每一分组又被划分为16个32位子分组，经过了一系列的处理后，算法的输出由四个32位分组组成，将这四个32位分组级联后将生成一个128位散列值。
总体流程如下图所示，每次的运算都由前一轮的128位结果值和当前的512bit值进行运算
sql:MD5测试
CREATE TABLE testMD5(
`id` INT (6)NOT NULL AUTO_INCREMENT COMMENT '序号',
`name` VARCHAR (4) NOT NULL DEFAULT '姓名'COMMENT'姓名测试',
`age` INT(3) DEFAULT NULL,
`birthday` VARCHAR (60)NOT NULL DEFAULT '时间'COMMENT'出生日期',
`password`VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET =utf8
DROP TABLE`testmd5`
INSERT INTO `testmd5` VALUE(1,'张三','21','1999-04-23','123456')
INSERT INTO `testmd5` VALUE(2,'李四','22','1998-03-13','321431')
INSERT INTO `testmd5` VALUE(3,'王五','23','1997-06-18','425633')
INSERT INTO `testmd5` VALUE(4,'小张','20','2000-09-13','327268')
INSERT INTO `testmd5` VALUE(5,'小李','20','2001-12-13','526825')
INSERT INTO `testmd5` VALUE(6,'小王','20','2001-12-13',MD5('424145'))
-- 加密`password`
UPDATE `testmd5`SET `password`=MD5(`password`)WHERE `id`=1
UPDATE `testmd5`SET `password`=MD5(`password`)
-- 加密全部`birthday`
UPDATE `testmd5`SET `birthday`=MD5(`birthday`)
UPDATE `testmd5`SET `birthday`=MD5(`birthday`)WHERE `name`='小张'
--
-- UPDATE `testmd5`SET `testmd5`=MD5(`testmd5`)
-- > Unknown column 'testmd5' in 'field list'-->“字段列表”中的未知列“testmd5”
-- 如何校正？ 将用户传来的信息进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5`WHERE `name`='小张'AND`age`='20'AND`birthday`='2000-09-13'AND`password`='327268'
SELECT * FROM `testmd5`WHERE `name`='张三'AND`birthday`='1999-04-23'
SELECT * FROM `testmd5`WHERE `name`='李四'AND`birthday`='1999-04-23'
mysql之自定义函数
什么是函数：
函数存储着一系列SQL语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。
但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的SQL语句集合。
函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。
5.事务ACID理解
参考博客：https://blog.csdn.net/dengjili/article/details/82468576
CSDN:https://blog.csdn.net/weixin_42181824/article/details/82261988
MySQL事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
事务的ACID原则
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。（如果隔离失败，则会失败，导致产生 脏读，不可重复读，幻读等）
事务的理解：模拟转账
-- =========事务的理解========
-- 1.创建一个数据库
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
-- 2.创建一个`account`表
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 3. 添加数据
INSERT INTO `account`(`name`,`cash`)
VALUES ('A',800),('B',200)
-- 4.模拟转账事务
SET autocommit = 0 -- 关闭自动提交
START TRANSACTION -- 开启一组事务
-- 5.开始转账 A 转账200给B
UPDATE `account` SET `cash`=`cash`-200 WHERE `name`='A'
UPDATE `account` SET `cash`=`cash`+200 WHERE `name`='B'
-- 6. 提交与回滚
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
-- 7.恢复默认值
SET autocommit = 1 ;-- 恢复默认值
补充说明
-- =========事务的理解========
-- mysql 是默认开启事务提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 开启事务
START TRANSACTION -- 标记这个事务的开始，从这以后的sql语句都在同一个事务中
INSERT **
INSERT **
-- 提交，持久化（成功）
COMMIT;
-- 回滚，回到原来的样子（失败）
ROLLBACK;
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解内容
SAVEPOINT 保存点名-- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点
数据库索引
推荐博文:https://www.cnblogs.com/chenshishuo/p/5030029.html
作用 :
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
分类 :
-
主键索引 (Primary Key)
唯一标识，不可重复，只能有一个列作为主键 -
唯一索引 (Unique)
避免出现重复列，唯一索引可以重复，多个列都可以标识唯一索引 -
常规索引 (Index/key)
默认的是index，key关键字可以设置 -
全文索引 (FullText)
在特定的数据库引擎下才有，可实现快速定位数据 作用 : 快速定位特定数据 注意 : 只能用于MyISAM类型的数据表 只能用于CHAR , VARCHAR , TEXT数据列类型 适合大型数据集
索引简介
-- ========索引练习=========
-- 1.在创建表的时候使用
-- 2.创建完表后增加索引
-- 3.创建完表后修改索引
-- 显示所有的索引信息
SHOW INDEX FROM `student_table`
-- 增加一个全文索引 （索引名）列名
ALTER TABLE `indextest`.`student_table`ADD FULLTEXT INDEX `name`(`name`);
-- EXPLAIN 分析sql执行得情况
EXPLAIN SELECT * FROM `student_table`; -- 全文索引
EXPLAIN SELECT * FROM `student_table` WHERE MATCH (`name`)AGAINST('李')
索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
用户管理
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER ZhengWanfu IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)->root
SET PASSWORD = '654321'
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR ZhengWanfu =PASSWORD('654321')
-- 重命名
RENAME USER ZhengWanfu TO ZhengWanfu2
-- 用户授权 ALL PRIVILEGES 全部的权限 除了GRANT（给别人授权）
GRANT ALL PRIVILEGES ON *.* TO ZhengWanfu2
-- 查询权限
SHOW GRANTS FOR ZhengWanfu2 -- 查看指定用户权限
SHOW GRANTS FOR root@127.0.0.1
-- There is no such grant defined for user 'root' on host '127.0.0.1'
-- 主机“127.0.0.1”上没有为用户“root”定义此类授予
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM ZhengWanfu2
-- 删除用户
DROP USER ZhengWanfu2
MySQL备份
-
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
-
MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件和相关配置文件
6.数据库的设计
规范化数据库设计
为什么需要设计数据库 ?
答:当数据库比较复杂时我们需要设计数据库
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤
-
收集信息
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
-
标识实体[Entity]
-
- 标识数据库要管理的关键对象或实体,实体一般是名词
-
标识每个实体需要存储的详细信息[Attribute]
-
标识实体之间的关系[Relationship]
绘制E-R图和数据库模型图
E-R图即数据库设计的图形化表达方式 , 也称为实体-关系图
关系模式 : 用二维表的形式表示实体和实体间联系的数据模型即关系模式R(U)
绘制数据库模型图的步骤 :
- 新建数据库模型图
- 添加实体
- 添加数据列及相应的属性
- 添加实体之间的映射关系
三大范式
推荐博客：https://www.cnblogs.com/xietianjiao/p/10972285.html
问题 : 为什么需要数据规范化?
不合规范的表设计
- 信息重复
- 更新异常
- 插入异常
- 无法正确表示信息
- 删除异常
- 丢失有效信息
第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式(2nd NF)
第二范式要求每个表只描述一件事情
第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
规范化和性能的关系
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
7.JDBC(重点)
数据库驱动
驱动包下载地址：http://mvnrepository.com/artifact/mysql/mysql-connector-java
版权声明：本文为wanwanyuan原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。