一、数据库
1.MySQL 的三层结构
-
所谓安装 MySQL 数据库，就是在主机上安装了一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。
-
一个数据库中可以创建多个表，以保存数据(信息)。
-
数据库管理系统、数据库和表的关系
1）数据在数据空中的存储方式
- 表一行称为一条记录 ——> 在 Java 程序中一条记录往往用对象来表示
2）SQL 语句分类
2.Java 操作 MySQL
- 1.创建一个商品，选用适当的数据类型
- 2.添加两条数据
- 3.删除表
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
@SuppressWarnings({"all"})
public class JavaMysql {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//这里老韩给大家演示一下 java 程序如何操作Mysql
//加载类，得到mysql连接
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hsp_db02", "root", "hsp");
//创建一个商品hsp_goods表, 选用适当的数据类型
//添加2条数据
//删除表goods
//这里可以编写sql 【create , select , insert , update ,delete ...】
//String sql = "create table hsp_goods ( id int, name varchar(32), price double, introduce text)" ;
//String sql = "insert into hsp_goods values(1, '华为手机', 2000, '这是不错的一款手机')" ;
String sql = "drop table hsp_goods" ;
//得到statement对象，把sql 语法发送给mysql执行
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
//关闭连接
statement.close();
connection.close();
System.out.println("成功~");
}
}
3.创建数据库
#演示数据库的操作
#创建一个名为 hsp_db01 的数据库
CREATE DATABASE hsp_db01;
#删除数据库指令
DROP DATABASE hsp_db01;
#创建一个使用 utf-8 字符集的数据库
CREATE DATABASE hspdb_02 CHARACTER SET utf8
#创建一个使用 utf8 的字符集，并带校对规则的数据库
CREATE DATABASE hspdb_03 CHARACTER
SET utf8 COLLATE utf8_bin
#下面是一条查询 SQL语句
SELECT * FROM t1 WHERE NAME = 'tom';
4.查看数据库
#显示数据库语句
SHOW DATABASES
#查看前面创建的数据库的定义信息
SHOW CREATE DATABASE hsp_db01
#在创建数据库、表的适合，为了规避关键字，可以使用反引号解决
#和波浪线在一起的那个点
CREATE DATABASE `CREATE`
5.备份、恢复数据库
-
备份数据库(注意：在 DOS 中执行)
mysqldump -uroot -p -B 数据库1 数据库2 数据库n > 文件名.sql
-
恢复数据库(注意：进入SQLyog再执行)
Source 文件名.sql
#备份，要在 DOS 下执行 mysqldump 指令
mysqldump -u root -p -B hsp_db02 hsp_db03 > d:\\bak.sql
#回复数据库(注意：进入MySQL命令行再执行)
source d:\\bak.sql
-
备份数据库的表
mysqldump -u root -p 数据库 表1 表2 表n > d://文件名
二、表
1.创建表
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规则 engine 引擎
- field：指定列名， datatype 指定列的类型(字段类型)
- character set ：如果不指定为所在数据库字符集
- collate：若不指定则为梭子啊数据库的校对规则
- engine：引擎
2.MySQL 数据类型(列类型)
1）数值型（INT）的基本使用
-
说明：在足够满足需求的情况下，尽量选择占用空间小的类型
-
应用实例
#演示整型的基本使用
#说明：表的字符集、校验规则、存储引擎这里先设为默认 -128 - 127
#如果没有指定 unsigned 则这个 TINYINT 就是有符号的
CREATE TABLE `t3`(
id TINYINT
);
CREATE TABLE `t4`(
id TINYINT UNSIGHED #无符号的 0-255
);
INSERT INTO t3 VALUES(-128);#这是非常简单的添加语句
2）数值型（BIT）的使用
#演示 bit 类型的使用
#1. BIT(m) m 在 1 - 64
#2.添加数据的范围给定的位数，如 255 就是 11111111 共八位
#3.显示按照 bit
#4.查询的时候仍然可以用数来查询
CREATE TABLE `t5` (num BIT(8));
3）数值型（小数）的使用
- FLOAT / DOUBLE【UNSIGNED】
FLOAT 单精度 DOUBLE 双精度 - DECIMAL[M, D] 【UNSIGNED】
- 可以支持更加精确的小数位，M 是小数位数(精度)的总数，D 是小数点后面的位数
- 如果 D 是 0 则该值没有小数点或者分数部分，M 最大是 65。D 最大是 30。如果 D 被省略的话则默认是 0，如果 M 被省略则默认是 10
- 建议：如果希望小数的精度高，推荐使用 decimal
#演示 decimal 类型、float 类型、double 类型的使用
#创建表
CREATE TABLE `t6`(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL（30,20）
);
#添加数据
INSERT INTO t6 VALUES(88.123566678, 88.5531654541651, 88.6455321678);
4）字符串的基本使用
- CHAR(size)
- 固定长度的字符串 最大 255 字符
- VARCHAR(size) 0 - 21844
- 可变长度字符串 最大 65535 字节【 utf8 编码最大 21844 字符 其中 1- 3 个字节用于记录大小】，65535 - 3 字节按照三个字节分组 65532 / 3 = 21844
#演示字符串的使用 char varchar
#注释的快捷键 shift + ctrl + c，取消注释 shift + ctrl + r
CREATE TABLE t9(
`name` CHAR(255));
)
CREATE TABLE t10(
`name` VARCHAR(21844)
);
-
字符串的使用细节
-
细节1：
char(4) 这个 4 表示的是字符数(最大是255)，不是字节数，不管是中文还是字母都要放四个，按照字符计算varchar(4) 这个 4 表示的是字符数，不管是字母还是中文都以定义好的表的编码来存储数据，如 utf8 下的 4 个字符就是 12 个字节
不管是中文还是英文字母都是最多存放 4 个，是根据使用的字符集来计算的。
-
细节2：
char(4) 是定长(固定的大小)也就是说，即使你插入的是 ‘aa’ ，也会占用分配的 4 个字符的空间varchar(4) 是变长(变化的大小)，就是说，如果插入的是‘aa’实际占用的空间不是 4 个字符，而是按照实际占用的空间来分配的，varchar 本身还需要 1-3 个字节来记录存放内容长度。
-
细节3：
什么时候使用 char ，什么时候使用 varchar？
1.如果数据是定长，推荐使用 char，比如 md5 的密码，邮箱，手机号，身份号码2.如果一个字段的长度是不确定的，我们使用 varchar，比如留言、文章
查询速度 char > varchar -
细节4
在存放文本时，可以用 Text 数据类型，可以将 TEXT 列是为 VARCHAR 列，注意 TEXT 不能有默认值，大小为 0 - 2^16 字节
如果希望存入更多的字符，可以选择 MEDIUMTEXT 0 - 2^24 或者
LONGTEXT 0 - 2^32
#演示字符串类型的使用细节 #char(4) 和 varchar(4) 这个4表示的是字符，而不是字节, 不区分字符是汉字还是字母 CREATE TABLE t11( `name` CHAR(4)); INSERT INTO t11 VALUES('韩顺平好'); SELECT * FROM t11; CREATE TABLE t12( `name` VARCHAR(4)); INSERT INTO t12 VALUES('韩顺平好'); INSERT INTO t12 VALUES('ab北京'); SELECT * FROM t12; #如果varchar 不够用，可以考虑使用mediumtext 或者longtext, #如果想简单点，可以使用直接使用text CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT); INSERT INTO t13 VALUES('韩顺平教育', '韩顺平教育100', '韩顺平教育1000~~'); SELECT * FROM t13; -
5）日期类型的基本使用
#演示日期类型的基本使用
CREATE TABLE `birthday6`(
t1 DATE,
t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP);
INSERT INTO birthday6(t1, t2)
VALUES('2022-11-11', '2022-11-11 10:10:10')
SELECT * FROM birthday6;
)
- 细节说明：TimeStamp 在 Insert 和 update 的时候会自动的更新。
6）创建表的练习
#创建表的练习
CREATE TABLE `emp`(
`Id` INT,
`name` VARCHAR(255),
`sex` CHAR(1),
`birthday` DATE,
`entry_date` DATETIME,
`job` VARCHAR(255),
`Salary` DOUBLE,
`resume` TEXT);
3.修改和删除表
1）修改表 —— 基本介绍
-
使用 ALTER TABLE 语句来追加、需要改或者删除列
-
添加列
ALTER TABLE tablename ADD colum datatype, column datatype; -
修改列
ALTER TABLE tablename MODIFY column datatype,column datatype; -
删除列
ALTER TABLE tablename DROP column; #查看表的结构: desc 表名;
-
2）应用实例
#添加一个 image 列，类型为 varchar，在 resume 后面
ADD image VARCHAR(255) NOT NULL DEFAULT''
AFTER RESUME
CHANGE `name` `username` VARCHAR(64) NOT NULL DEFAULT '';
#修改 job 列，使其长度为 60
ALTER TABLE `emp` MODIFY `job` VARCHAR ( 60 );
#删除 sex 列
ALTER TABLE `emp` DROP sex;
#将表名改为 employee
RENAME TABLE emp TO employee;
#将列表字符集修改为 utf8
ALTER TABLE `employee` CHARACTER SET utf8;
#列名 name 修改为 user_name
ALTER TABLE employee
CHANGE `name` `username` VARCHAR(64) NOT NULL DEFAULT '';
三、数据库 CRUD 语句
CRUD：creat、read、update、delete，增删改查
1.Insert 语句
1）使用 INSERT 语句向表中插入数据
INSERT INTO table_name(column, column, column)
VALUES (value, value, value)
-
案例演示
#练习insert 语句 -- 创建一张商品表goods (id int , goods_name varchar(10), price double ); -- 添加2条记录 CREATE TABLE `goods` ( id INT , goods_name VARCHAR(10), -- 长度10 price DOUBLE NOT NULL DEFAULT 100 ); -- 添加数据 INSERT INTO `goods` (id, goods_name, price) VALUES(10, '华为手机', 2000); INSERT INTO `goods` (id, goods_name, price) VALUES(20, '苹果手机', 3000); SELECT * FROM goods;
2）insert 细节说明
-
插入的数据应该与字段的数据类型相同或者可以转换，如 ‘10’ 是可以转换成 int 的
-
数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中
-
在 values 中列出的数据位置必须与加入到列的排列位置顺序相对应
-
字符和日期型数据应包含在单引号中
-
列可以插入空值【前提是该字段允许为空】，insert into table value(null)
-
INSERT INTO table_name VALUES 后面可以跟多个括号来添加多个记录
-
如果是给表中的所有字段添加数据，可以不写前面的字段名称
-
默认值的使用：如果不给某个字段值的时候，如果有默认值会添加，否则报错
-
案例演示
#说明insert 语句的细节 -- 1.插入的数据应与字段的数据类型相同。 -- 比如 把 'abc' 添加到 int 类型会错误 INSERT INTO `goods` (id, goods_name, price) VALUES('韩顺平', '小米手机', 2000); -- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。 INSERT INTO `goods` (id, goods_name, price) VALUES(40, 'vovo手机vovo手机vovo手机vovo手机vovo手机', 3000); -- 3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。 INSERT INTO `goods` (id, goods_name, price) -- 不对 VALUES('vovo手机',40, 2000); -- 4. 字符和日期型数据应包含在单引号中。 INSERT INTO `goods` (id, goods_name, price) VALUES(40, vovo手机, 3000); -- 错误的 vovo手机 应该 'vovo手机' -- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null) INSERT INTO `goods` (id, goods_name, price) VALUES(40, 'vovo手机', NULL); -- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录 INSERT INTO `goods` (id, goods_name, price) VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800); -- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称 INSERT INTO `goods` VALUES(70, 'IBM手机', 5000); -- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错 -- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给null -- 如果我们希望指定某个列的默认值，可以在创建表时指定 INSERT INTO `goods` (id, goods_name) VALUES(80, '格力手机'); SELECT * FROM goods; INSERT INTO `goods2` (id, goods_name) VALUES(10, '顺平手机'); SELECT * FROM goods2;
2.update 语句
1）使用 update 语句修改表中数据
#修改列中所有的元素为一个量
UPDATE tab_name
SET col_name = variable;
#修改列中的一行为某个量
UPDATE tab_name
SET col_name = variable
WHERE user_name = 'name';
-
案例演示
-- 演示update语句 -- 要求: 在上面创建的employee表中修改表中的纪录 -- 1. 将所有员工薪水修改为5000元。[如果没有带where 条件，会修改所有的记录，因此要小心] UPDATE employee SET salary = 5000 -- 2. 将姓名为 小妖怪 的员工薪水修改为3000元。 UPDATE employee SET salary = 3000 WHERE user_name = '小妖怪' -- 3. 将 老妖怪 的薪水在原有基础上增加1000元 INSERT INTO employee VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000, '给大王捶背', 'd:\\a.jpg'); UPDATE employee SET salary = salary + 1000 WHERE user_name = '老妖怪' -- 可以修改多个列的值 UPDATE employee SET salary = salary + 1000 , job = '出主意的' WHERE user_name = '老妖怪' SELECT * FROM employee;
2）使用细节
- update 语法可以用新值更新原有表行中的各列
- set 字句指示要修改哪些列和要给予哪些值
- where 字句指定应更新哪些行。如果没有 where 字句，则更新所有的行，使用时应注意
- 如果需要修改多个字段，可以使用 SET 字段1 = 值1, 字段2 = 值2……；
3.delete 语句
1）使用 delete 语句删除表中的数据
DELETE FROM tab_name
#如果不加 where 就会删除表中所有的数据
[WHERE where_definition]
-
案例演示
-- delete 语句演示 -- 删除表中名称为’老妖怪’的记录。 DELETE FROM employee WHERE user_name = '老妖怪'; -- 删除表中所有记录, 老师提醒，一定要小心 DELETE FROM employee;
2）使用细节
-
如果不使用 where 字句，将删除表中的所有数据
-
delete 不能删除某一列的值，可以通过 update 将其设置为 null 或者 ‘ ’
-
使用 delete 语句仅删除记录而不删除表的本身，如果要删除表，使用 drop table 语句
-
案例演示
-- Delete语句不能删除某一列的值（可使用update 设为 null 或者 ''） UPDATE employee SET job = '' WHERE user_name = '老妖怪'; SELECT * FROM employee -- 要删除这个表 DROP TABLE employee;
4.select 语句
1）基本语法
#查出表中的所有数据
-- DISTINCE 可选，指显示结果的时候是否去掉重复的数据
SELECT [DISTINCT] *
FROM tab_name;
#查出表中指定列名的数据
SELECT [DISTINCT] column1, column2, column3...
FROM tab_name;
2）案例演示
SELECT * FROM student;
-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT english FROM student;
-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english FROM student;
3）课堂练习
-
使用表达式对查询的列进行计算
SELECT * expression FROM tab_name; -
在 select 语句中可使用 as 语句
SELECT col_name as other_name from col_name; -
案例演示
-- select 语句的使用 -- 统计每个学生的总分 SELECT `name`, (chinese + english + math) FROM student; -- 在所有学生总分加10分的情况 -- SELECT 后选择的是列或者对列做操作，最后显示出来作为列名 SELECT `name`, (chinese + english + math + 10) FROM student; -- 使用别名表示学生分数。 SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score FROM student;- 效果
- 效果
3）在 WHERE 字句中常用的运算符
-
案例演示
-- select 语句 -- 查询姓名为赵云的学生成绩 -- WHERE 是选择行，或者对行中按照元素进行限制，显示出来作为行名 SELECT * FROM student WHERE `name` = '赵云' -- 查询英语成绩大于90分的同学 SELECT * FROM student WHERE english > 90 -- 查询总分大于200分的所有同学 SELECT * FROM student WHERE (chinese + english + math) > 200 -- 查询math大于60 并且(and) id大于4的学生成绩 SELECT * FROM student WHERE math >60 AND id > 4 -- 查询英语成绩大于语文成绩的同学 SELECT * FROM student WHERE english > chinese -- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生. -- 赵% 表示 名字以韩开头的就可以，模糊查询 SELECT * FROM student WHERE (chinese + english + math) > 200 AND math < chinese AND `name` LIKE '赵%' -- 查询英语分数在 80－90之间的同学。 SELECT * FROM student WHERE english >= 80 AND english <= 90; SELECT * FROM student WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间 -- 查询数学分数为89,90,91的同学。 SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91; SELECT * FROM student WHERE math IN (89, 90, 91); -- 查询所有姓李的学生成绩。 SELECT * FROM student WHERE `name` LIKE '韩%' -- 查询数学分>80，语文分>80的同学
4）使用 order by 子句查询排序结果
#升序
SELECT col_name1, col_name2, col_name3
FROM tab_name
ORDER BY col_name asc;
#降序
SELECT col_name1, col_name2, col_name3
FROM tab_name;
ORDER BY col_name desc;
-
案例演示
-- 演示order by使用 -- 对数学成绩排序后输出【升序】。 SELECT * FROM student ORDER BY math; -- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序 SELECT `name` , (chinese + english + math) AS total_score FROM student ORDER BY total_score DESC; -- 对姓韩的学生成绩[总分]排序输出(升序) where + order by SELECT `name`, (chinese + english + math) AS total_score FROM student WHERE `name` LIKE '韩%' ORDER BY total_score;
5）MySQL 表查询 —— 加强
-
使用 where 字句
？如何查找 1992.1.1 后入职的员工
SELECT ename, hiredate FROM `emp` WHERE hiredate > '1992-1-1'在 mysql 中，日期类型是可以直接比较！！！
-
如何使用 like 操作符(模糊查询)
-
%:表示 0 到 多个任意字符
-
_：表示单个任意字符
？如何显示首字符为 S 的员工的姓名和工资SELECT ename FROM emp WHERE ename LIKE 'S%'; #显示第三个字符为 O 的员工的姓名 SELECT ename FROM emp WHERE ename LIKE '__S%';
-
-
显示出上级为 空 的员工的姓名
SELECT DISTINCT ename FROM `emp` WHERE mgr IS NULL; -
显示出表的结构
DESC emp; -
分页查询
-
实际开发的时候不能把符合记录的全部表返回出来，这就涉及到分页查询
-
按照雇员的 id 号升序取出，每页显示 3 条记录，请分别显示第 1、2、3页。
-
基本语法：
#表示从 start 行开始取，取出 rows 行 SELECT ... LIMIT start, rows-- 分页查询 -- 第一页 SELECT * FROM emp ORDER BY empno LIMIT 0, 3 -- 第二页 SELECT * FROM emp ORDER BY empno LIMIT 3, 3 -- 第三页 SELECT * FROM emp ORDER BY empno LIMIT 6, 3
-
-
使用 order by 字句
-
？如何按照工资的从高到低的顺序显示雇员的信息
SELECT * FROM emp ORDER BY sal ASC; -
？按照部门号升序而雇员的工资降序排列，显示雇员信息
SELECT * FROM emp ORDER BY deptno ASC, sal DESC;
-
四、函数
1.统计函数
1）合计 / 统计函数 —— count
#返回满足条件的记录的总行数
SELECT COUNT(*) FROM tab_name
[WHERE where_definition;]
#满足条件的某列有多少个，但是会排除为 null 的情况
SELECT COUNT(col_name) FROM tab_name
[WHERE where_definition;]
-
案例演示
-- 演示mysql的统计函数的使用 -- 统计一个班级共有多少学生？ SELECT COUNT(*) FROM student; -- 统计数学成绩大于90的学生有多少个？ SELECT COUNT(*) FROM student WHERE math > 90 -- 统计总分大于250的人数有多少？ SELECT COUNT(*) FROM student WHERE (math + english + chinese) > 250 -- count(*) 和 count(列) 的区别 -- 解释 :count(*) 返回满足条件的记录的行数 -- count(列): 统计满足条件的成员有多少个，但是会排除 为null的情况 CREATE TABLE t15 ( `name` VARCHAR(20)); INSERT INTO t15 VALUES('tom'); INSERT INTO t15 VALUES('jack'); INSERT INTO t15 VALUES('mary'); INSERT INTO t15 VALUES(NULL); SELECT * FROM t15; SELECT COUNT(*) FROM t15; -- 4 SELECT COUNT(`name`) FROM t15;-- 3
2）合计函数 —— sum
- sum 函数返回满足 where 条件的行的和，一般使用在数值列
#将单个列的元素全部加起来
SELECT SUM(col_name1), SUM(col_name2), SUM(col_name3) FROM tab_name
[WHERE where_definition]
#将多个列的元素加起来求和
SELECT SUM(clo_name1 + col_name2 + col_name3) FROM tab_name
[WHERE where_definition]
-
案例演示
-- 演示sum函数的使用 -- 统计一个班级数学总成绩？ SELECT SUM(math) FROM student; -- 统计一个班级语文、英语、数学各科的总成绩 SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student; -- 统计一个班级语文、英语、数学的成绩总和 SELECT SUM(math + english + chinese) FROM student; -- 统计一个班级语文成绩平均分 SELECT SUM(chinese)/ COUNT(*) FROM student; SELECT SUM(`name`) FROM student;
3）合计函数 —— avg
-
AVG 函数返回满足 WHERE 条件的一列的平均值
SELECT AVG(col_name1), AVG(col_name2), AVG(col_name3) FROM tab-name [WHERE where_definition]; -
案例演示
-- 演示avg的使用 -- 练习： -- 求一个班级数学平均分？ SELECT AVG(math) FROM student; -- 求一个班级总分平均分 SELECT AVG(math + english + chinese) FROM student;
4）合计函数 —— max / min
-
MAX / MIN 函数返回满足 WHERE 条件的一列的最大值 / 最小值
SELECT MAX(col_name1), MAX(col_name2), MAX(col_name3) FROM tab_name [WHERE where_definition]; -
案例演示
-- 演示max 和 min的使用 -- 求班级最高分和最低分（数值范围在统计中特别有用） SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student; -- 求出班级数学最高分和最低分 SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre FROM student;
5）group by 分组子句
-
使用 group by 子句对列进行分组
SELECT col_name1, col_name2, col_name3 FROM tab_name GROUP BY col_name; -
使用 having 字句对分组后的结果进行过滤
SELECT col_name1, col_name2, col_name3 FROM tab_name GROUP BY col_name HAVING ...; -
应用案例
# 演示group by + having GROUP by用于对查询的结果分组统计, (示意图) -- having子句用于限制分组显示结果. -- ?如何显示每个部门的平均工资和最高工资 -- 老韩分析: avg(sal) max(sal) -- 按照部分来分组查询 SELECT AVG(sal), MAX(sal) , deptno FROM emp GROUP BY deptno; -- 使用数学方法，对小数点进行处理 SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno FROM emp GROUP BY deptno; -- ?显示每个部门的每种岗位的平均工资和最低工资 -- 老师分析 1. 显示每个部门的平均工资和最低工资 -- 2. 显示每个部门的每种岗位的平均工资和最低工资 SELECT AVG(sal), MIN(sal) , deptno, job FROM emp GROUP BY deptno, job; -- ?显示平均工资低于2000的部门号和它的平均工资 // 别名 -- 老师分析 [写sql语句的思路是化繁为简,各个击破] -- 1. 显示各个部门的平均工资和部门号 -- 2. 在1的结果基础上，进行过滤，保留 AVG(sal) < 2000 -- 3. 使用别名进行过滤 SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000; -- 使用别名 SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000;-
结果显示
-
显示的是按照分组的最低和最高工资，即按照 deptno 分组
-
GROUP BY deptno, job; 按顺序显示出来
-
***HAVING avg_sal < 2000;***是对分组后的语句进行筛选
-
-
使用分组函数和分组字句 group by
-
显示每种岗位的雇员总数和平均工资
SELECT COUNT(*), job, AVG(sal) FROM emp GROUP BY job; -
显示雇员的总数，以及获得补助的雇员数
#COUNT 会排除字段为空的情况 SELECT COUNT(*), COUNT(comm) FROM emp -
显示经理的总人数
SELECT COUNT(*), COUNT(IF(job = 'MANAGER',job, NULL)) FROM emp -
显示雇员工资的最大差额
SELECT (MAX(sal) - MIN(sal)) AS max_sal_sub FROM emp
-
2.字符串函数
外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传
-
案例演示
-- 演示字符串相关函数的使用 ， 使用emp表来演示 -- CHARSET(str) 返回字串字符集 SELECT CHARSET(ename) FROM emp; -- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列 SELECT CONCAT(ename, ' 工作是 ', job) FROM emp; -- INSTR (string ,substring ) 返回substring在string中出现的位置,没有返回0 -- dual 亚元表, 系统表 可以作为测试表使用 SELECT INSTR('hanshunping', 'ping') FROM DUAL; -- UCASE (string2 ) 转换成大写 SELECT UCASE(ename) FROM emp; -- LCASE (string2 ) 转换成小写 SELECT LCASE(ename) FROM emp; -- LEFT (string2 ,length ) 从string2中的左边起取length个字符 -- RIGHT (string2 ,length ) 从string2中的右边起取length个字符 SELECT LEFT(ename, 2) FROM emp; -- LENGTH (string ) string长度[按照字节] SELECT LENGTH(ename) FROM emp; -- REPLACE (str ,search_str ,replace_str ) -- 在str中用replace_str替换search_str -- 如果是manager 就替换成 经理 SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp; -- STRCMP (string1 ,string2 ) 逐字符比较两字串大小 SELECT STRCMP('hsp', 'hsp') FROM DUAL; -- SUBSTRING (str , position [,length ]) -- 从str的position开始【从1开始计算】,取length个字符 -- 从ename 列的第一个位置开始取出2个字符 SELECT SUBSTRING(ename, 1, 2) FROM emp; -- LTRIM (string2 ) RTRIM (string2 ) TRIM(string) -- 去除前端空格或后端空格 SELECT LTRIM(' 韩顺平教育') FROM DUAL; SELECT RTRIM('韩顺平教育 ') FROM DUAL; SELECT TRIM(' 韩顺平教育 ') FROM DUAL; -- 练习: 以首字母小写的方式显示所有员工emp表的姓名 -- 方法1 -- 思路先取出ename 的第一个字符，转成小写的 -- 把他和后面的字符串进行拼接输出即可 SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name FROM emp; SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name FROM emp;
3.数学函数
-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL;
-- CEILING (number2 ) 向上取整, 得到比num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的8, 转成 2进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是 8 是16进制的8, 转成 2进制输出
SELECT CONV(16, 16, 10) FROM DUAL;
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- 结果为 78.13，，decimal_places 是保留的位数
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL;
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 老韩说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
4.时间日期
- now 函数与 时间戳函数的区别
- now 函数的值是不变的，时间戳的值当对标进行修改的时候会改变
-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME ( ) 当前时间
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 创建测试表 信息表
CREATE TABLE mes(
id INT ,
content VARCHAR(30),
send_time DATETIME);
-- 添加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL;
-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id, content, DATE(send_time)
FROM mes;
-- 请查询在10分钟内发布的新闻, 思路一定要梳理一下.
SELECT *
FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
SELECT *
FROM mes
WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用mysql 的sql语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活80岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以date,datetime timestamp
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW())
FROM DUAL;
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
--
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;
SELECT * FROM mysql.user \G
5.流程控制
1）加密函数和系统函数
-- 演示加密函数和系统函数
-- USER() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- DATABASE() 查询当前使用数据库名称
SELECT DATABASE();
-- MD5(str) 为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- 演示用户表，存放密码时，是md5
CREATE TABLE hsp_user
(id INT ,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user
VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdn
SELECT * FROM hsp_user -- SQL注入问题
WHERE `name`='韩顺平' AND pwd = MD5('hsp')
-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密
SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC
-- select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user
2）流程控制语句
# 演示流程控制语句
# IF(expr1,expr2,expr3) 如果expr1为True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2) 如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5
SELECT CASE
WHEN TRUE THEN 'jack' -- jack
WHEN FALSE THEN 'tom'
ELSE 'mary' END
-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
-- 老师说明，判断是否为null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)
FROM emp;
SELECT ename, IFNULL(comm, 0.0)
FROM emp;
-- 2. 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS 'job'
FROM emp;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
五、MySQL 查询增强及多表查询
1.MySQL 查询增强
1）使用 where 字句
？如何查找 1992.1.1 后入职的员工
SELECT ename, hiredate FROM `emp`
WHERE hiredate > '1992-1-1'
在 mysql 中，日期类型是可以直接比较！！！
2）如何使用 like 操作符(模糊查询)
-
%:表示 0 到 多个任意字符
-
_：表示单个任意字符
？如何显示首字符为 S 的员工的姓名和工资SELECT ename FROM emp WHERE ename LIKE 'S%'; #显示第三个字符为 O 的员工的姓名 SELECT ename FROM emp WHERE ename LIKE '__S%';
3）显示出上级为 空 的员工的姓名
SELECT DISTINCT ename FROM `emp`
WHERE mgr IS NULL;
4）显示出表的结构
DESC emp;
5）分页查询
-
实际开发的时候不能把符合记录的全部表返回出来，这就涉及到分页查询
-
按照雇员的 id 号升序取出，每页显示 3 条记录，请分别显示第 1、2、3页。
-
基本语法：
#表示从 start 行开始取，取出 rows 行 SELECT ... LIMIT start, rows-- 分页查询 -- 第一页 SELECT * FROM emp ORDER BY empno LIMIT 0, 3 -- 第二页 SELECT * FROM emp ORDER BY empno LIMIT 3, 3 -- 第三页 SELECT * FROM emp ORDER BY empno LIMIT 6, 3
-
6）多子句查询——数据分组的总结
- 如果 SELECT 语句同时包含有 group， by， having， order by， limit 那么他们的顺序就是上面所列出来的顺序
- 应用案例：请统计各部门的平均工资，并且是大于 1000 的，并且按照平均工资从高到底排序
7）MySQL 多表查询
#先看这样一段代码
SELECT *
FROM emp, dept;
- 在默认情况下，当两个表查询的时候规则为
- 从第一张表中，取出一行 和 第二张表中的每一行进行组合，返回结果
- 一共返回的记录数 = 第一行表行数 * 第二行表的行数
- 这样的多表查询返回的结果，称为笛卡尔集
- 解决这个问题的关键就是要写出正确的过滤条件 WHERE
8）自连接
自连接是在同一张表的链接查询
select worker.ename, boss.ename
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno
- 运用了自连接
- 把同一张表当成两张表使用
- 需要给表取别名： 表名 表别别名
如：FROM emp worker, emp boss - 列名不明确，可以指定列的别名
9）子查询
子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询，分为单行子查询和多行子查询。
-- 子查询的演示
-- 请思考，如何显示与 SMITH 同一部门的员工
/*
先把查询到 SMITH 的部门号得到
把上面的 SELECT 语句当作一个子查询来使用
*/
# 单表查询
SELECT deptno
FROM emp
WHERE ename = 'SMITH';
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH';
);
-- 如何查询和部门 10 的工作相同的雇员的
-- 名字、岗位、工资、部门号，但是不含 10 号部门自己的雇员
SELECT DISTINCT *
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
);
-- 可以替换成下面的 any
SELECT DISTINCT ename, sal, job
FROM emp
WHERE job = ANY (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
)
4）显示出表的结构
DES C emp;
5）分页查询
-
实际开发的时候不能把符合记录的全部表返回出来，这就涉及到分页查询
-
按照雇员的 id 号升序取出，每页显示 3 条记录，请分别显示第 1、2、3页。
-
基本语法：
#表示从 start 行开始取，取出 rows 行 SELECT ... LIMIT start, rows-- 分页查询 -- 第一页 SELECT * FROM emp ORDER BY empno LIMIT 0, 3 -- 第二页 SELECT * FROM emp ORDER BY empno LIMIT 3, 3 -- 第三页 SELECT * FROM emp ORDER BY empno LIMIT 6, 3
-
![[外链图片转存中...(img-2kWwBoLk-1695200979280)]](https://img-blog.csdnimg.cn/668c112caa024c17bb29d03a08b3585e.png)
6）多子句查询——数据分组的总结
- 如果 SELECT 语句同时包含有 group， by， having， order by， limit 那么他们的顺序就是上面所列出来的顺序
- 应用案例：请统计各部门的平均工资，并且是大于 1000 的，并且按照平均工资从高到底排序
7）MySQL 多表查询
#先看这样一段代码
SELECT *
FROM emp, dept;
![[外链图片转存中...(img-QzSAAJD0-1695200979280)]](https://img-blog.csdnimg.cn/09aad206f6ad4c73bb299c49eb54e0a7.png)
- 在默认情况下，当两个表查询的时候规则为
- 从第一张表中，取出一行 和 第二张表中的每一行进行组合，返回结果
- 一共返回的记录数 = 第一行表行数 * 第二行表的行数
- 这样的多表查询返回的结果，称为笛卡尔集
- 解决这个问题的关键就是要写出正确的过滤条件 WHERE
8）自连接
自连接是在同一张表的链接查询
select worker.ename, boss.ename
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno
- 运用了自连接
- 把同一张表当成两张表使用
- 需要给表取别名： 表名 表别别名
如：FROM emp worker, emp boss - 列名不明确，可以指定列的别名
9）子查询
子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询，分为单行子查询和多行子查询。
9）子查询
子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询，分为单行子查询和多行子查询。
-- 子查询的演示
-- 请思考，如何显示与 SMITH 同一部门的员工
/*
先把查询到 SMITH 的部门号得到
把上面的 SELECT 语句当作一个子查询来使用
*/
# 单表查询
SELECT deptno
FROM emp
WHERE ename = 'SMITH';
SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH';
);
-- 如何查询和部门 10 的工作相同的雇员的
-- 名字、岗位、工资、部门号，但是不含 10 号部门自己的雇员
SELECT DISTINCT *
FROM emp
WHERE job IN (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
);
-- 可以替换成下面的 any
SELECT DISTINCT ename, sal, job
FROM emp
WHERE job = ANY (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
)
10）子查询临时表
-- 查询ecshop中各个类别中，价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询
select cat_id , max(shop_price)
from ecs_goods
group by cat_id
-- 这个最后答案
select goods_id, ecs_goods.cat_id, goods_name, shop_price
from (
SELECT cat_id , MAX(shop_price) as max_price
FROM ecs_goods
GROUP BY cat_id
) temp , ecs_goods
where temp.cat_id = ecs_goods.cat_id
and temp.max_price = ecs_goods.shop_price
11）all 和 any 操作符
-
显示工资比部门 30 所有员工的工资高的员工的姓名，工资和部门号。
-- ALL he ANY 的使用 -- 请思考：显示工资比部门 30 所有员工的工资高的员工的姓名，工资和部门号 SELECT ename, sal, deptno FROM emp WHERE sal > ALL( SELECT sal FROM emp WHERE deptno = 30 ) -- 也可以这样写 SELECT ename, sal, deptno FROM emp WHERE sal > ( SELECT MAX(sal) FROM emp WHERE deptno = 30 )- any 和 all 的用法相同，但筛选出来的是只要大于其中某一个即可，可以转换成 min 写法
12）多列子查询
-
请思考如何查询与 SMITH 的部门和岗位完全相同的所有雇员，并且不含其本人。
-
多行子查询指的是子查询返回的是多列
-
注意一定处理的是多列，多行是无法处理的(加入 DISTINCT 修饰)
SELECT * FROM emp WHERE (deptno, job) = ( SELECT DISTINCT deptno, job FROM emp WHERE ename = 'SMITH' )
13）表复制和去重
-
自我复制数据(蠕虫复制)
-
有时，为了对某个 sql 语句进行效率测试，我需要海量数据时，可以使用次法为表创建海量数据。
CREATE TABLE my_tab ( id INT, `name` VARCHAR(32), sal DOUBLE, job VARCHAR(32), deptno INT ); DESC my_tab -- 先将 emp 表中的数据插入到 my_tab 表中 INSERT INTO my_tab (id, `name`, sal, job, deptno) SELECT empno, ename, sal, job, deptno FROM emp; -- 实现自我复制 INSERT INTO my_tab SELECT * FROM my_tab;
-
-
如何删除掉一张表中重复的数据
-- 实现自我复制 INSERT INTO my_tab SELECT * FROM my_tab; CREATE TABLE my_tab01 LIKE emp -- 构建有重复记录的表 INSERT INTO my_tab01 SELECT * FROM emp; SELECT * FROM my_tab01; -- 考虑如何去重 /* 思路 1.先创建一张临时表，该表的结构 与需要去重的表一样 2.把需要去重的表通过 distinct 处理 后复制到新表中 3.清除掉需要去重的表的数据 4.将新表中的数据复制到该表中 5.删除掉新表 */ CREATE TABLE Atemp LIKE my_tab01; INSERT INTO Atemp SELECT DISTINCT * FROM my_tab01 DELETE FROM my_tab01 INSERT INTO my_tab01 SELECT * FROM Atemp; DROP TABLE Atemp SELECT * FROM my_tab01
14）合并查询
有时在实际的应用中，为了合并多个 select 语句的查询的结果，可以使用集合操作符号 union
SELECT ename, sal, job FROM emp WHERE sal > 2500
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'
-- UNION ALL 就是将两个查询的结果合并，会去重
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'
-- 只用 UNION 的话会自动去重
15）MySQL 表外连接
提出一个问题，前面学习的查询是利用 where 字句对两张表或者多张表，形成的笛卡尔表进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的不显示。
比如：列出部门名称和这些部门的员工的名称和工作，同时要求显示出那些没有员工的部门
-
外连接
-
左外连接：如果左侧的表完全显示我们就说是左外连接
-
右外连接：如果右侧的表完全显示我们就说是右外连接
-
即使没有完全匹配的记录也会完全显示出来
-- 显示出所有的部门的成员，并且要求将没有成员的部门也显示来 SELECT ename,dname FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno ORDER BY dname
-
六、MySQL 约束
约束用于确保数据库数据满足特定的商业规则，在 MySQL 中约束包括五种，分别为：not null, unique, primary key, foreign key, check
1.primary key 主键
字段名 字段类型 primary key
用于唯一的标识表行的数据，当定义主键约束后，该列不能重复
如
CREATE TABLE t1(
id INT PRIMARY KEY
)
-- 修饰后该表的 id 就不能重复了
1）主键的细节讨论
-
主键不能重复且不能为空
-
一张表最多只能有一个主键，但可以是复合主键
-- 演示复合主键的使用 -- 添加后，只有 id 和 `name` 均相同的时候，才违反主键规则 CREATE TABLE t1 ( id INT, `name` VARCHAR(32), email VARCHAR(32), PRIMARY KEY (id, `name`) ); -
主键指定的方式有两种，
一种是直接再字段名后面指定
字段名 字段类型 PRIMARY KEY，
另一种是再表定义的最后写上
PRIMARY KEY(字段名) -
使用 DESC 查询表的节后，可以看到主键的情况
2.unique 唯一
当定义了唯一约束后，该列值是不能重复的
-- unique 的使用
CREATE TABLE t21 (
id INT UNIQUE,
name VARCHAR(32)
);
- unique 的细节讨论
- 如果没有指定 NOT NULL，则 unique 字段可以有多个 null
- 一个表可以有多个 unique 字段
- 如果一个字段是 unique not null 其效果类似于主键
3.foreign key 外键
假如有一个学生表和一个班级表，班级表中有班级 id 学生表中也有班级 id 来标识这个学生的班级信息，如果我们希望学生的班级 id 必须是存在于 班级表中的，即输入的 id 均包含于班级 id 则需要使用到外键约束。
-- 外键所在的表称为本表
FOREIGN KEY(本表字段名) REFERENCES
主表名 (主键名或 unique 字段名);
CREATE TABLE mY_class (
id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
CREATE TABLE my_student (
id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT,
FOREIGN KEY (class_id) REFERENCES my_class(id)
);
1）外键细节说明
- 外键指向的表的字段，要求是 primary key 或者是 unique。
- 表的类型是 innodb，这样才支持外键。
- 外键字段的字符类型要和主键字段的字符类型一致(长度可以不同)。
- 外键字段的值必须，必须在主键的字段中出现过，或者为 null (前提是外键的字段允许为 null，即为 unique 的情况)。
- 一旦建立主外键的关系，数据就不能随意的删除了。
4.check
用于强制行数据必须满足的条件，假定在 sal 上定义了 check 约束，并且要求 sal 列值在 1000 ~ 2000 之间如果不再 1000 到 2000 之间就会提示出错
提示
- 数据不满足约束但还是生效的，可以添加进去
5.自增长
在某张表中，存在一个 id 列，我们希望在添加记录的时候，该列从 1 开始，自动的增长，怎么处理？
字段名 整型 primary key auto_increment;
| 添加 自增长的字段方式 |
|---|
| insert into xxx(字段1， 字段2, ……) values(null, ‘值’, ……); |
| insert int xxx(字段2, ……) values (‘值’, ……); |
| insert int xxx values (null, ‘值’, ……); |
- 自增长的细节
- 一般来说自增长是和 primary key 配合使用的
- 自主增长也可以单独使用，但是需要配合一个 unique
- 自增长修饰的字段为整数型的，虽然小数也可以但是非常非常少这样使用
- 自增长默认从 1 开始，你也可以下命令修改
ALTER TABLE tab_name auto_increment = xxx;
七、索引
说起提升数据库的性能，索引是最物美价廉的东西。不用加内存，不用该程序，不用调 sql，查询速度就可能提高百倍千倍。
1.索引的原理
- 没有索引为什么会慢？
使用索引为什么会快？形成一个索引的数据结构，比如二叉树 - 索引的代价
- 磁盘占用
- 对 dml(update delete insert)语句的效率影响
2.索引的类型
- 主键索引，主键自动为索引，称为主键索引
- 唯一索引(unique)
- 普通索引(index)
- 全文索引
开发中考虑使用：全文搜索 solr 和 elasticsearch
3.添加索引
-- 演示 mysql 索引的使用
-- 创建索引
CREATE TABLE t25 (
id INT,
`name` VARCHAR(32)
);
-- 查询表是否有缩影
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25;
-- 添加普通索引
CREATE INDEX id_index ON t25(id);
-- 如何选择
-- 如果某列的值，是不会重复的，则优先考虑 unique 索引，否则使用普通索引
-- 另一种添加不同索引的方法
ALTER TABLE t25 ADD INDEX id_index(id);
-- 添加主键索引
ALTER TABLE t25 ADD PRIMARY KEY(id);
4.删除索引
DROP INDEX id_index ON t25;
-- 删除所有的索引
DROP INDEX ON t25;
5.查询索引
-- 1.方式
SHOW INDEX FROM t25
-- 2.方式
SHOW INDEXEX FROM t25
-- 3.方式
SHOW KEYS FROM t25
-- 4.方式
DESC t25
6.索引小结
- 较为频繁的作为条件的字段可以创建索引
- 唯一性太差的字段不适合单独创建缩影
- 更新非常频繁的字段不适合创建索引
- 不会出现在 WHERE 中的字段不该创建索引
八、MySQL 事务
事务用于保证数据的一致性，它由一组相关的 dml 语句组成，改组的 dml 语句要么全部成功，要么全部失败。如：转账就需要用事务来处理，用以确保数据的一致性
1.事务和表
当执行事务操作时，MySQL 语句会在表上加锁，防止其他用户改表的数据，这对用户来讲非常重要
1）回退事务
先介绍一下保存点，保存点是事务中的点，用于取消部分事务，当结束事务的时候，会自动删除该事务所定义的所有保存点，到执行回退事务的时候，会回退到指定的点。
2）提交事务
利用 commit 语句可以提交事务，当执行了该语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效，当使用 commit 语句结束事务后，其它会话可以查到事务变化后的新数据。
2.事务细节讨论
- 如果不开始事务，默认情况下，dml 操作是自动提交的，不能回滚
- 如果开启一个事务，即使没有创建保存点，也可以使用回滚来回退到事务开始
- 在还没有提交的时候可以创建多个保存点
- 在事务没有提交之前选择回退到那个保存点
- MySQL 的事务机制需要 innodb 的引擎才可以吃用，myisam 不可以
- 开始一个事务 start transction 或者 set autocommit = off
3.MySQL 事务隔离的级别
1）事务隔离级别的介绍
- 多个连接开启各自事务操作数据空中数据库时，数据库系统要负责隔离操作，以保证各个连接中获取数据的准确性。
- 如果不考虑隔离性，可能会引发以下问题
- 脏读
- 不可重复读
- 幻读
2）脏读和幻读
3）事务隔离级别
说明：V 是可能出现，x 是不会出现
4）代码
-- 查看当前会话的隔离级别
SELECT @@tx_isolation;
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation;
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read
-- 设置系统当前的隔离级别
SET GLOBAL TRANSCATION ISOLATION LEVEL REPEATABLE READ;
-- mysql 默认的隔离级别是 repeatable read，一般情况下，没有特殊的要求，没必要修改，因为该级别可以满足绝大部分的项目需求
4.MySQL 事务 ACID
1）事务 ACID 的特性
- 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生 - 一致性(Consistency)
事务必须使数据库从一个一致性状态变换到另一个一致性状态 - 隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所感染，多个并发事务之间要相互隔离 - 持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来及时数据库发生故障也不应该对其有任何影响。
九、MySQL 表类型和存储引擎
1.MySQL表类型和存储引擎的分类
- MySQL表的类型由存储类型决定，主要包括 myisam，innodb，memory等。
- MySQL数据库主要支持六种类型，分别是 CSV，Memory， ARCHIVE，
MRG_MYISAM， MYISAM， InnoBDB 
2.主要存储类型/表类型的特点
3.细节说明
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- innodb 存储引擎，是前面使用过.
-- 1. 支持事务 2. 支持外键 3. 支持行级锁
-- myisam 存储引擎
CREATE TABLE t28 (
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t28 VALUES(1, 'jack');
SELECT * FROM t28;
ROLLBACK TO t1
-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)) ENGINE MEMORY
DESC t29
INSERT INTO t29
VALUES(1,'tom'), (2,'jack'), (3, 'hsp');
SELECT * FROM t29
-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB
4.如何选择表的存储引擎
1）细节说明
- 如果你的应用不需要事务，处理的只是基本的 CRUD 操作，那么 MyISAM 是不二的选择，速度快
- 如果还需要支持事务，就选择 InnoDB
- Memory 存储引擎就是将数据存储在内存中，由于没有磁盘的等待，速度极快，但是由于是内存的存储引擎，所做的任何修改在服务器重启后都将小时，经典的用法：用户的在线状态
1）修改存储引擎
ALATER TABLE `表名` ENGINE = 存储引擎;
十、视图
看一个需求：emp 表的列的信息很多，有些信息是个人的重要信息，如果我们希望这些重要信息无法被人查询到，有什么办法？
1.视图的基本概念
-
视图是一个虚拟表，其内容由查询定义。同真实的表一样，试图包含列，其数据来自对应的真实表。
-
视图和基表的关系示意图
2.视图的基本使用
-- 创建视图
CREATE VIEW 视图名 AS SELECT语句
-- 修改视图
ALTER VIEW 视图名 AS SELECT语句
-- 展示视图的创建信息
SHOW CREATE VIEW 视图名
-- 删除视图
DROP VIEW 视图名1，视图名2
-- 视图的使用
-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
-- 查看视图
DESC emp_view01
SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;
1）视图使用的细节
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件，后缀为 .frm
- 视图的数据变化会影响到基表，基表的数据变化也会影响到视图
-- 视图的细节
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图 会影响到基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369
SELECT * FROM emp; -- 查询基表
SELECT * FROM emp_view01
-- 修改基本表， 会影响到视图
UPDATE emp
SET job = 'SALESMAN'
WHERE empno = 7369
-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01
CREATE VIEW emp_view02
AS
SELECT empno, ename FROM emp_view01
SELECT * FROM emp_view02
3.视图的最佳实践
-- 视图的课堂练习
-- 针对 emp ，dept , 和 salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]
/*
分析: 使用三表联合查询，得到结果
将得到的结果，构建成视图
*/
CREATE VIEW emp_view03
AS
SELECT empno, ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.deptno = dept.deptno AND
(sal BETWEEN losal AND hisal)
DESC emp_view03
SELECT * FROM emp_view03
十一、MySQL 管理
1.MySQL 用户
MySQL 中的所有用户，都存储在系统数据库 mysql 中的 user 表中
1）对 user 表中重要字段的说明
- host：允许登录的位置，localhost 表示该用户只允许本地登录，也可以指定 ip 地址
- user：用户名
- authentic_string：密码，是通过 mysql 的 password( ) 函数加密之后的密码
2）创建用户
#创建用户并同时指定密码
CREATE user '用户名' @'允许登录的位置' identified by '密码'
3）删除用户
DROP user '用户名' @'允许登录的位置'
-- Mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用
-- 1. 创建新的用户
-- 解读 (1) 'hsp_edu'@'localhost' 表示用户的完整信息 'hsp_edu' 用户名 'localhost' 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码
-- *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'
SELECT `host`, `user`, authentication_string
FROM mysql.user
-- 2. 删除用户
DROP USER 'hsp_edu'@'localhost'
-- 3. 登录
-- root 用户修改 hsp_edu@localhost 密码, 是可以成功.
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('123456')
-- 修改自己的密码, 没问题
SET PASSWORD = PASSWORD('abcdef')
-- 修改其他人的密码， 需要权限
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456')
2.MySQL 权限
1）给用户授权
GRANT 权限列表 ON 库.对象名 TO '用户名'@'登陆位置' identified by '密码'
说明：
-
权限列表，多个权限用逗号隔开
grant select on ……
grant select, delete, create on ……
grant all 权限 on 表示赋予该用户在该对象上的所有权限 -
特别说明
-
identified by 可以省略，也可以写出
- 如果用户存在，就是修改用户的密码
- 如果该用户不存在，就是创建用户
2）回收用户授权
REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登陆位置'
3）权限生效指令
-- 如果权限没有生效，可以执行下面的指令
FLUSH PRIVILEGES;
-- 演示 用户权限的管理
-- 创建用户 shunping 密码 123 , 从本地登录
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'
-- 使用root 用户创建 testdb ,表 news
CREATE DATABASE testdb
CREATE TABLE news (
id INT ,
content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
-- 给 shunping 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT
ON testdb.news
TO 'shunping'@'localhost'
-- 可以增加update权限
GRANT UPDATE
ON testdb.news
TO 'shunping'@'localhost'
-- 修改 shunping的密码为 abc
SET PASSWORD FOR 'shunping'@'localhost' = PASSWORD('abc');
-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'shunping'@'localhost'
REVOKE ALL ON testdb.news FROM 'shunping'@'localhost'
-- 删除 shunping
DROP USER 'shunping'@'localhost'
4）MySQL 管理细节
- 在创建用户的适合，如果不指定 Host，则为 %，%表示所有的 IP 都有连接权限，
- 你也可以这样指定
create user ‘xxx’@‘192.318.1.%’ 表示该用户在 这样的 ip 下是可以登录 MySQL 的 - 在删除的用户时，如果 host 不是 %，需要明确指定 ‘用户’@‘host值’
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
