「MySQL基礎」老韩30時間

06 MYSQL的三层结构#

image-20230811163323520

image-20230811163705834

08 创建数据库#

image-20230811163838045

image-20230811170850915

09 查询数据库#

image-20230811170953510

image-20230811172534945

10 备份 数据库#

image-20230811172821787

11 创建表#

image-20230811174107795

image-20230811175409818

列类型：

image-20230811175441894

MySQL列类型：即MySQL的数据类型

img

image-20230811183155735

14 bit类型#

image-20230811183241370

# 演示BIT使用
# instruction
# 1 bit(m) m 在1-64
# 2 add data range 按照给定位数来确定； 比如m = 8,表示一个字节：0-255
# 3 show 按照bit 
# 4 查询时候，仍然可以按照数来查询； 
CREATE TABLE t05 (num bit(8));
INSERT INTO t05 VALUES(255);
SELECT * FROM t05;

15 小数型#

image-20230811184204274

image-20230811184320153

image-20230811185704951

16 列类型值字符串#

image-20230811190018689

# 字符串类型使用 CHAR VARCHAR
# 注释快捷键:shift + ctrl + c,注释 
# CHAR(SIZE)
# 固定长度字符串 最大255 字符
# VARCHAR(SIZE)  0-65535字节；
# 可变长度字符串 最大65532字节 utf8编码最大21844字符，1-3个字节用于记录大小；
# 如果表的编码是utf8 VARCHAR(SIZE)= (65535-3)/3=21844
# 如果表的编码是gbk VARCHAR(SIZE)= (65535-3)/2=32766
CREATE TABLE t09 (
				`name` CHAR(255));
CREATE TABLE t10 (
				`name` VARCHAR(21844));

CREATE TABLE t10 (
				`name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;

17 字符串类型的使用细节#

image-20230811191624466

image-20230811192157993

类似可伸缩的盒子，文章用varchar

真实：AA+3个字节

image-20230811192428233

image-20230811192604188

image-20230811193041532

# 字符串类型的使用细节
# char(4) & VARCHAR(4)这个4表示：字符 ，而不是字节
CREATE TABLE t11 (
				`name` CHAR(4));
INSERT INTO t11 VALUES('韩顺平好');
SELECT * FROM t12;

CREATE TABLE t12 (
				`name` CHAR(4));
INSERT INTO t12 VALUES('AB平好');

SELECT * FROM t12;

# 如果 char不够用，可以是同mediumtext or LONGTEXT;
# 如果想简单点，可以直接使用text 
CREATE TABLE t13 (content TEXT, context2 MEDIUMTEXT, content3 LONGTEXT);
INSERT INTO t13 VALUES('韩顺平教育', '韩顺平教育100', '韩顺平教育1000~~~' );
SELECT * FROM t13;

18 日期类型#

image-20230811193827787

# TIME TYPE 
CREATE TABLE t14 (
				birthday date,
				job_time DATETIME,
				login_time TIMESTAMP
					NOT NULL DEFAULT CURRENT_TIMESTAMP 
					ON UPDATE CURRENT_TIMESTAMP); 
# 登录时间，如果希望login_time列自动更新，需要配置
SELECT * FROM t14;
INSERT INTO t14(birthday, job_time)
			 VALUES('2022-11-11','2022-11-11 10:10:10');
# 如果更新了t14的某条记录，login_TIME也会以当前时间进行更新

image-20230811195606891

19 创建表 练习#

image-20230811195748923

# CREATE TABLE EXERCISE 
CREATE TABLE `emp` (
	id INT,
	`name` VARCHAR(32),
	sex CHAR(1),
	birthday DATE,
	entry_date datetime,
	job VARCHAR(32),
	salary DOUBLE,
	`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
	# ADD DATA 
	INSERT INTO `emp`
		VALUES(100, '小妖怪', '男', '2000-11-11', '2010-10-10 11:11:11','巡山的', 3000, '大王叫我来巡山');

# rename
RENAME TABLE emp to empployee
# add COLUMN
ALTER TABLE empployee
	ADD	image VARCHAR(32) NOT NULL DEFAULT ''
			AFTER RESUME;
DESC empployee
# drop COLUMN: SEX
ALTER TABLE empployee
	DROP SEX;
	DESC empployee
# charset: utf8
ALTER TABLE empployee CHARACTER SET utf8
# COLUMN name CHANGE
ALTER TABLE empployee
	CHANGE `name` `username` VARCHAR(32) NOT NULL DEFAULT '';

DESC empployee

20 修改表#

image-20230811201346731

image-20230811201423919

5 CRUD

1 单表查询#

image-20230811210057100

### INSERT 
# 创建表goods（id int , goods_name varchar(10)）,
CREATE TABLE `goods` (
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE
	);
-- ADD DATA
INSERT INTO `goods` (id, goods_name, price)
	VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
	VALUES(20, '苹果手机', 3000);
SELECT * FROM `goods`;

image-20230812145246498

练习题2#

image-20230812145303038

22 INSERT注意事项#

image-20230812153644288

id ： ‘abc’、‘123’会尝试转换成INT。123可以

image-20230812154526939

image-20230812155300646

image-20230812162718904

image-20230812162710519

23 UPDATE#

image-20230812162812058

  • WHERE为空时候，所有记录进行修改；
# SHOW UPDATE
CREATE TABLE `employee` (
	id INT,
	`name` VARCHAR(32),
	sex CHAR(1),
	birthday DATE,
	entry_date datetime,
	job VARCHAR(32),
	salary DOUBLE,
	`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO `employee`
	VALUES(100, '小妖怪', '男', '2000-11-11', '2010-10-10 11:11:11','巡山的', 3000, '大王叫我来巡山');
SELECT * FROM `employee`
# 1 UPDATE salary = 5000
UPDATE `employee` SET salary = 5000;
SELECT * FROM `employee`;
# 2 UPDATE employee  = 10000 where name = 小妖怪
UPDATE `employee` SET salary = 100000 WHERE `name` = '小妖怪';
SELECT * FROM `employee`;
# 3 UPDATE name = '老妖怪' WHERE salary + 1000
INSERT INTO `employee`
	VALUES(200, '老妖怪', '男', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 20000, '给大王捶背的');
UPDATE `employee`
	SET salary = salary + 100000
	WHERE name = '老妖怪';
SELECT * FROM `employee`;

UPDATE注意事项#

image-20230812170723964

24 DELETE#

image-20230812170759525

# SHOW DELETE
# DELETE  WHERE name = '老妖怪';
DELETE FROM `employee`
	WHERE name = '老妖怪';
SELECT * FROM `employee`
# DELETE ALL RECORD
DELETE FROM `employee`;
SELECT * FROM `employee`

DELETE注意事项#

image-20230812172148879

25 SELECT语句【重要】#

image-20230812175517330

image-20230812180202054

# SELECT  【重点 难点】
CREATE TABLE student(
id int not null DEFAULT 1,
`name` VARCHAR(20) not null DEFAULT '',
chinese FLOAT not null DEFAULT 0.0,
english FLOAT not null DEFAULT 0.0,
math FLOAT not null DEFAULT 0.0
);

INSERT INTO student (id,name,chinese,english,math) VALUES (1,'韩顺平',89,78,90);
INSERT INTO student (id,name,chinese,english,math) VALUES (2,'张飞',67,98,56);
INSERT INTO student (id,name,chinese,english,math) VALUES (3,'宋江',87,78,77);
INSERT INTO student (id,name,chinese,english,math) VALUES (4,'关羽',88,98,90);
INSERT INTO student (id,name,chinese,english,math) VALUES (5,'赵云',82,84,67);
INSERT INTO student (id,name,chinese,english,math) VALUES (6,'欧阳锋',55,85,45);
INSERT INTO student (id,name,chinese,english,math) VALUES (7,'黄蓉',75,65,30);
INSERT INTO student (id,name,chinese,english,math) VALUES (8,'韩信',45,65,99);
# 1 all grades
SELECT * FROM student;
# 2 name english 
SELECT `name`, english FROM STUDENT;
# 3 filter duplicated records 
SELECT `name`, english FROM student;

image-20230812180827053

image-20230812180857140

# SELECT 
# total grade+10
SELECT `name`, (chinese+english+math+10) as 'Total Grade' FROM student;
# name:'名字'
SELECT `name` as '名字', (chinese+english+math+10) as Total_Grade FROM student;

image-20230812183953405

image-20230812184752517

# 查询名字为赵云的同学
SELECT * FROM student
	WHERE `name` = '赵云';
# english grade >90
SELECT * FROM student
	WHERE english > 90;
# total scores > 200
SELECT  *  FROM student
	WHERE (chinese + english + math) > 200;

# practice 1
SELECT * FROM student
	WHERE math > 60 
	AND id > 4;
# practice 2
SELECT * FROM student
	WHERE english > chinese;
# practice 3
SELECT * FROM student
	WHERE (chinese + english + math) > 200
	AND math < chinese
	AND `name` LIKE '赵%';

image-20230812190650210

# 1 SELECT english 80-90
SELECT * FROM student
	WHERE english BETWEEN 80 AND 90;
# 2 select math in(89,90,91)
SELECT * FROM student
	WHERE math in(89, 90, 91);
# 3 SELECT name = '李'
SELECT * FROM student
	WHERE name LIKE '张%'
# 4 math > 80; chinese > 80;
SELECT * FROM student
	WHERE math > 80
	AND chinese > 80;
	
SELECT * FROM student
	ORDER BY math ASC;

基本语法4 ORDER BY

image-20230812200541987

# ORDER BY
SELECT *  FROM student
	ORDER BY math DESC;

# SELECT total_score descend 
SELECT `name`, (chinese+english+math) as total_score 
	FROM student
	ORDER BY total_score DESC;
#  韩姓总分排序 
SELECT `name`, (chinese+english+math) as total_score 
	FROM student
	WHERE `name` LIKE '韩%'
	ORDER BY total_score ASC;

2 SQL查询加强（多表查询）#

image-20230813163246876

image-20230813163631084

image-20230813164512254

image-20230813165036829

分页查询#

image-20230813170213906

# 1 分页查询 
# 2 按雇员的ID升序取出；每页显示3条记录 

-- 第一页 
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,12
-- 推导公式 
-- SELECT * FROM emp 
-- ORDER BY empno
-- 	LIMIT 每页显示记录数 * （第几页-1）, 每页显示记录数；
SELECT * FROM emp 
	ORDER BY empno DESC
	LIMIT 20,5

分组查询#

image-20230813170455683

# GROUP BY 的使用 
-- 1 SHOW每种岗位的雇员总数、平均工资
SELECT job, COUNT(*), AVG(sal) FROM emp
	GROUP BY job;

-- 2 SHOW 雇员总数，获得补助的雇员数 
SELECT COUNT(*), COUNT(comm)   -- count(列)，值为NULL则不会被统计
	 FROM emp;
SELECT COUNT(*), COUNT(comm)   -- count(列)，值为NULL则不会被统计
	 FROM emp; 
-- 拓展：没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 'abc', null)) FROM emp;
SELECT COUNT(*), (COUNT(*)-COUNT(comm))   -- count(列)，值为NULL则不会被统计
	 FROM emp; 
-- 3 管理者总人数   -- 尝试写，修改，尝试写，正确的写法
SELECT COUNT(DISTINCT mgr)
	FROM emp;
-- 4 工资的最大差额
SELECT max(sal)-min(sal) FROM emp;

数据分组总结：#

image-20230813172043383

# 统计各部门groupby的平均工资，并且大于1000，并且按照平均工资 从高到低排列，取出前两列
SELECT deptno, avg(sal) AS avg_sal
	FROM emp
	GROUP BY deptno 
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0, 2

多表查询#

image-20230813172540480

image-20230813172701426

image-20230813174752606

# 多表查询
# ?显示雇员名，雇员工资及所在部门的名字【笛卡尔集】
-- 1 雇员名 工资来自emp
-- 2 部门名名字 来自dept
-- 3 需求 对emp 和dept
-- 4 当我们需要指定显示某个表的列的时候，需要 table.column
SELECT ename, sal, dname, emp.deptno
	FROM emp, dept
	WHERE emp.deptno = dept.deptno;
# 老韩小技巧： 多表查询的条件不能少于 表的个数-1，否则会出现笛卡尔集 
# 如何显示部门号为10的部门名，员工名和工资
SELECT ename, sal, dname, emp.deptno
	FROM emp, dept
	WHERE emp.deptno = dept.deptno
	AND emp.deptno = 10; 
# 显示各个员工的姓名，工资及其工资的级别 
-- 姓名 工资： 来自emp
--  工资级别来自salgrade
-- 先写简单，加入过滤条件
SELECT ename, sal, grade
	FROM emp, salgrade
	WHERE (sal > salgrade.losal) AND (sal < salgrade.hisal)
	
SELECT ename, sal, grade
	FROM emp, salgrade
	WHERE sal BETWEEN losal AND hisal;

自连接#

image-20230813180925408

image-20230813181952215

# 多表查询的 自连接 
# 显示公司员工和上级的名字；
-- 老韩分析
-- 员工和上级在同表：emp
-- 通过emp的mgr列关联 
# 自连接特点： 1 把同一张表当两张表；
# 					2 需要给表取别名；
#						3 列明不明确可以指定列的别名；
SELECT worker.ename AS '职员', boss.ename AS '上级'
	FROM emp worker, emp boss  -- 169 = 13 * 13
	WHERE worker.mgr = boss.empno;
SELECT * FROM emp;

多行子查询#

image-20230813182351326

# SUBQUERY
-- 如何显示与smith同部门的所有员工信息；
SELECT deptno 
	FROM emp
	WHERE ename = 'SMITH';
	SELECT * 
		FROM emp
		WHERE deptno = (
								SELECT deptno 
							FROM emp
							WHERE ename = 'SMITH')
-- 查询和部门10的工作相同的雇员的
-- 名字，岗位，工资，部门号，但是不含10自己的 
-- 子查询
SELECT DISTINCT job
	FROM emp
		WHERE deptno = 10
		
SELECT ename, job, sal, deptno
	FROM emp
		WHERE job IN ( SELECT DISTINCT job
	FROM emp
		WHERE deptno = 10)
		AND deptno <> 10;

子查询的临时表#

image-20230813184028495

image-20230813185701296

# SUBQUERY
-- 如何显示与smith同部门的所有员工信息；
SELECT deptno 
	FROM emp
	WHERE ename = 'SMITH';
	SELECT * 
		FROM emp
		WHERE deptno = (
								SELECT deptno 
							FROM emp
							WHERE ename = 'SMITH')
-- 查询和部门10的工作相同的雇员的
-- 名字，岗位，工资，部门号，但是不含10自己的 
-- 子查询当作临时表，可以解决很多问题
SELECT DISTINCT job
	FROM emp
		WHERE deptno = 10
		
SELECT ename, job, sal, deptno
	FROM emp
		WHERE job IN ( SELECT DISTINCT job
	FROM emp
		WHERE deptno = 10)
		AND deptno <> 10;

-- 查询ecshop中各个类别中

all & any :所有 & 任何#

同时，分别可以替代max & min；

image-20230813185732290

image-20230814223318880

# ALL & ANY

SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > ALL(
	SELECT sal 
		FROM emp 
		WHERE deptno = 30
	);
	
-- 也可以这样写 
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > MAX(
	SELECT sal 
		FROM emp 
		WHERE deptno = 30
	);

# 2 SHOW ANY 比其中一个高即可；
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > ANY(
	SELECT sal 
		FROM emp 
		WHERE deptno = 30
	);
	
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > (
	SELECT MIN(sal) 
		FROM emp 
		WHERE deptno = 30
	);

多列子查询#

image-20230814223634130

即：返回多个列；

查询一些人，同smith的部门岗位相同；

练习题：

image-20230814224514649

# 多列子查询
-- 分析：1 得到部门岗位
SELECT deptno, job
	FROM emp 
	WHERE ename = 'ALLEN'
-- 分析: 2 上面的查询结果作为子查询来使用，并且使用多列子查询进行匹配；
SELECT *
	FROM emp 
	WHERE (deptno, job) = (
					SELECT deptno, job
					FROM emp 
					WHERE ename = 'ALLEN'
	      ) AND ename <> 'ALLEN';

# 练习：和宋江数学，英语，语文成绩完全相同的同学；
SELECT * 
	FROM student
	WHERE (math,english,chinese) = (
	SELECT math, english, chinese 
		FROM student
		WHERE `name` = '宋江'
	)

子查询练习#

image-20230814225115682

# 子查询练习 
-- 1 GET 部门号 和 平均工资 
SELECT deptno, AVG(sal) AS AVG_SAL
	FROM emp
	GROUP BY deptno
-- 2 上面的结果作为子查询 
SELECT ename, sal, temp.AVG_SAL, emp.deptno
	FROM emp, (SELECT deptno, AVG(sal) AS AVG_SAL
						FROM emp
						GROUP BY deptno) temp
	WHERE emp.deptno = temp.deptno
	AND emp.sal > temp.AVG_SAL

image-20230814230524672

# 子查询练习 
-- 1 GET 部门号 和 平均工资 
SELECT deptno, AVG(sal) AS AVG_SAL
	FROM emp
	GROUP BY deptno
-- 2 上面的结果作为子查询 
SELECT ename, sal, temp.AVG_SAL, emp.deptno
	FROM emp, (SELECT deptno, AVG(sal) AS AVG_SAL
						FROM emp
						GROUP BY deptno) temp
	WHERE emp.deptno = temp.deptno
	AND emp.sal > temp.AVG_SAL

# 查询每个部门的最高工资
SELECT ename, sal, temp.MAX_SAL, emp.deptno
	FROM emp, (
						SELECT deptno, MAX(sal) AS MAX_SAL
						FROM emp
						GROUP BY deptno
						) temp
	WHERE emp.deptno = temp.deptno
	AND emp.sal > temp.MAX_SAL;

# 3 查询每个部门的信息（包括：部门名，编号，地址）和人员数量
-- 1 部门名 FROM dept
-- 2 各个部门的人员数量 -> 构建一个临时表 
SELECT COUNT(*), deptno 
	FROM emp 
	GROUP BY deptno;
	
SELECT dname, dept.deptno, loc, tmp.per_num AS '人数'
	FROM dept, (
						SELECT COUNT(*) as per_num, deptno 
							FROM emp 
							GROUP BY deptno
	) tmp 
	WHERE tmp.deptno = dept.deptno
-- 还有一种写法
-- 多表的列名不重复时候才可以直接写* 
SELECT tmp.* , dname, loc
	FROM dept, (
						SELECT COUNT(*) as per_num, deptno 
							FROM emp 
							GROUP BY deptno
	) tmp 
	WHERE tmp.deptno = dept.deptno

表复制 和 去重#

image-20230815223303248

# 表复制
-- 有时候，为了某个sql进行效率测试，
CREATE TABLE my_tab01 
	( id INT,
		`name` VARCHAR(32),
		sal DOUBLE,
		job VARCHAR(32),
		deptno INT	
	);
	DESC my_tab01;
	
	-- 演示如何自我复制 
	-- 1 先把emp 表记录复制到my_tab01
	INSERT INTO my_tab01 
		(id, `name`, sal, job, deptno)
		SELECT empno, ename, sal, job, deptno FROM emp;
SELECT * FROM my_tab01;

-- 2 自我复制 
	INSERT INTO my_tab01 
		SELECT * FROM my_tab01;
	SELECT COUNT(*) FROM my_tab01;
	
	-- 如何删除掉一张表的重复记录
	-- 1 创建一张表 my_tab02;
	-- 2 让my_tab-2 有重复记录;
	CREATE TABLE my_tab02 LIKE emp; -- 将emp结构复制到my_tab02 
	DESC my_tab02;
	
	INSERT INTO my_tab02
			SELECT * FROM emp;
	SELECT * FROM my_tab02;
	-- 3 考虑去重 
	/*
		思路：
			1 先创建一张临时表 my_tmp,结构同my_tab02 一样 
			2 把my_tmp的记录 通过distinct关键字处理后，复制到my_tmp
			3 清除my_tab02记录；
			4 把my_tab02表的记录复制到my_tab02 
			5 drop 掉 临时表my_tmp 
  */
-- 1 先创建一张临时表 my_tmp,结构同my_tab02 一样 
CREATE TABLE my_tmp LIKE my_tab02;
-- 2 把my_tmp的记录 通过distinct关键字处理后，复制到my_tmp
INSERT INTO my_tmp 
	SELECT DISTINCT * FROM my_tab02;
-- 3 清除my_tab02记录；
DELETE FROM my_tab02;
-- 4 把my_tab02表的记录复制到my_tab02 
INSERT INTO my_tab02 
	SELECT * FROM my_tmp;
-- 5 drop 掉 临时表my_tmp 
DROP TABLE my_tmp;
SELECT * FROM my_tab02;

合并查询#

image-20230815224806962

image-20230815225208226

外连接#

image-20230815225734399

image-20230815230513466

image-20230815231147807

image-20230815231906892

image-20230815232244191

# 外连接 
-- 列出部门名称和部门员工名称和工作，要求 显示出那些没有员工的部门 
-- 1 多表查询 
SELECT dname, ename, job
	FROM emp, dept
	WHERE emp.deptno = dept.deptno
	ORDER BY dname;
	
	-- 创建 stu
	CREATE TABLE stu (
		id INT,
		`name` VARCHAR(32)
	);
	INSERT INTO stu VALUES(1, 'jack'),(2, 'tom'),(3, 'kity'),(4, 'nono');
	SELECT * FROM stu;
	-- 创建 exam
		CREATE TABLE exam (
		id INT,
		grade INT
	);
	INSERT INTO exam VALUES(1, 56),(2, 76),(11, 8);
	SELECT * FROM exam;
	-- 使用左连接
	-- 显示所有人的成绩，没有成绩，也要显示人的姓名和id
	SELECT stu.id, `name`, grade 
		FROM stu, exam 
		WHERE stu.id = exam.id;
	-- 改成左外连接
	SELECT `name`, stu.id, grade 
		FROM stu LEFT JOIN exam 
		ON stu.id = exam.id;
		
-- 使用右外连接 
-- 即：右表exam和 左表没有匹配的记录，也会把右表没有匹配的记录显示出来；
	SELECT `name`, stu.id, grade 
		FROM stu RIGHT JOIN exam 
		ON stu.id = exam.id;
		
# 练习
SELECT dname, ename, job
	FROM dept LEFT JOIN emp 
	ON dept.deptno = emp.deptno;

6 函数

a 统计/合计函数#

COUNT(*) & COUNT(column)#

image-20230813005414093

SUM#

image-20230813123554170

AVG#

image-20230813123618895

# count table student
SELECT COUNT(*) FROM student;
# count math > 90
SELECT COUNT(*) FROM student
	WHERE math > 90;
# count total_score > 250

# count(*) AND COUNT(COLUMN)的区别
# count(*)返回满足条件的记录的行数；
# COUNT(COLUMN)统计满足条件的列有多少个；但是会排除为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  非空ID

# 演示 SUM函数 的使用
# 1 class math scores 
SELECT SUM(math) FROM student;
# 2 total_chinese total_english total_math 
SELECT SUM(chinese) as total_chinese,
			 SUM(math) as total_math,
			 SUM(english) AS total_english FROM student;
# 3 total scores
SELECT SUM(math+chinese+english) FROM student;
# 4 ave chinese scores
SELECT SUM(chinese) / COUNT(*) FROM student;
SELECT AVG(chinese) as avg_chinese FROM student;

# SHOW max & min
SELECT MIN(math+chinese+english),
	MAX(math+chinese+english) FROM student;
# 求出班级数学最高分
SELECT MIN(math)AS MIN_MATH,
	MAX(math) AS MAX_MATH FROM student;

GROUP BY#

image-20230813124513211

CREATE TABLE dept(
    deptno MEDIUMINT NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT '',
    loc VARCHAR(13) NOT NULL DEFAULT ''
);
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'),
    (20,'RESEARCH','DALLAS'),
    (30,'SALES','CHICAGO'),
    (40,'OPERATIONS','BOSHTON');  
# employee 
create table emp(
	empno mediumint  unsigned not null default 0,
	ename varchar(20) not null default '',
	job varchar(9) not null default '',
	mgr mediumint unsigned,
	hiredate date not null, 
	sal decimal(7,2) not null,
	comm decimal(7,2),  -- 奖金
	deptno mediumint unsigned not null default 0
);
-- add data 
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
    (7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
    (7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),
    (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
    (7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),
    (7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
    (7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
    (7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,NULL,20),
    (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
    (7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
    (7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
    (7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
    (7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
SELECT * FROM emp;
SELECT * FROM emp;
# 工资级别表
CREATE TABLE salgrade(
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,  -- 工资级别
    losal DECIMAL(17,2) NOT NULL,									-- 该级别的最低工资
    hisal DECIMAL(17,2) NOT NULL									-- 该级别的最高工资
);
INSERT INTO salgrade 
VALUES(1,700,1200),
    (2,1201,1400),
    (3,1401,2000),
    (4,2001,3000),
    (5,3001,9999);
SELECT * FROM salgrade;


# SHOW GROUP BY and HAVING
# HAVING 子句用于限制分组显示结果
# 1 如何显示每个部门的平均工资和最高工资；
SELECT 
	AVG(sal) AS AVG_SAL, MAX(sal) AS TOP_SALARY, deptno
	FROM emp
	GROUP BY deptno;
# 显示每个部门的每种岗位的平均工资和最低工资
SELECT
	AVG(sal) AS AVG_SAL, MIN(sal) as MIN_SAL, deptno, job
	FROM emp
	GROUP BY deptno, job;
# 显示平均工资低于2000的部门号和 它的平均工资// 别名
-- 基本思路：化繁为简，各个击破；
-- 使用别名
SELECT deptno, AVG(sal) AS AVG_SAL
	FROM emp
		GROUP BY deptno
		HAVING AVG_SAL < 3000;

b 字符串函数#

image-20230813135445615

-- SHOW STRING相关函数的使用
-- CHARSET(str)
SELECT CHARSET(ename) FROM emp;
-- CONCAT(str1,str2,...) 连接字符串
SELECT CONCAT(ename, 'job is ', job) FROM emp;
-- INSERT 
-- dual 亚元表， 系统表 可以作为测试表使用；
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
-- UCASE(str) 转换成大写
SELECT UCASE(ename) FROM emp; 
-- LCASE(str) 转换成小写
SELECT LCASE(ename) FROM emp; 
-- LEFT(str,len)
SELECT LEFT(ename, 3) FROM emp;
-- LENGTH(str)
SELECT LENGTH('韩顺平') FROM emp; 
-- REPLACE(str,from_str,to_str)
-- 如果是mgr替换经理
SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp;
-- STRCMP(expr1,expr2) 逐字符比较两字符串大小
-- a大于h，所以 = 1； j>h 所以 -1；
SELECT STRCMP('hsp','asp') FROM DUAL;
SELECT STRCMP('hsp','jsp') FROM DUAL;
-- SUBSTRING(str FROM pos FOR len)
-- exp: 从ename列的第一个位置开始取出两个字符；
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- LTRIM(str) RTRIM(str) TRIM([remstr FROM] str) 去掉前端空格后者后端或者前后
SELECT LTRIM('   韩顺平教育') FROM DUAL;
SELECT RTRIM('   韩顺平教育    ') FROM DUAL;
SELECT TRIM('   韩顺平教育    ') FROM DUAL;

# 练习：首字母小写的方式显示所有员工emp的姓名；
SELECT 
	CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTR(ename, 2, LENGTH(ename)) ) 
	FROM emp;
# 练习讲解
-- 方法1 
-- 思路：取出ename第一个字符，转成小写；
-- 同后面进行拼接 
SELECT CONCAT(lcase(SUBSTRING(ename,1,1)), substring(ename,2 )) AS NEW_NAME
	FROM emp;
-- 方法2 
SELECT CONCAT(lcase(LEFT(ename,1)), substring(ename,2 )) AS NEW_NAME
	FROM emp;

c 数学函数#

image-20230813142916190

# SHOW MATH FUNCTION

-- ABS(NUM) absolute_number
SELECT ABS(-10) FROM DUAL;
-- BIN(N) decimal_number
SELECT BIN(10) FROM DUAL;
-- CEILING(num2) 向上取整
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(N, ,to_base)
SELECT CONV(8, 10, 2)FROM DUAL;
SELECT CONV(16, 16, 10)FROM DUAL;
-- FLOOR(X)乡下取整
SELECT FLOOR(1.1) FROM dual;
-- FORMAT(X,D) 保留小数位数,会四舍五入
SELECT FORMAT(78.125458, 2) FROM DUAL;
SELECT FORMAT(avg(sal),2), max(sal), deptno
   FROM emp
	 GROUP BY deptno;
-- HEX()

-- LEAST()
SELECT least(0,1, -10, 4) FROM DUAL;
-- MOD(N,M)
SELECT MOD(10,3) FROM DUAL;
-- RAND()
-- 老韩说明：
-- 1  使用rand每次返回随机数；
-- 2  使用rand(seed)返回随机数，（0，1）
SELECT RAND(6) FROM DUAL;

时间函数#

image-20230813150103744

image-20230813150204062

image-20230813150717682

# 日期时间相关函数 
-- CURRENT_DATE
SELECT CURRENT_DATE() FROM DUAL;
SELECT CURRENT_TIME() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
SELECT CURRENT_USER() FROM DUAL;

-- 创建测试表 
CREATE TABLE mes(
	id INT,
	content VARCHAR(30),
	send_time datetime
);

INSERT INTO mes 
	VALUES(1, 'Beijing News', CURRENT_TIMESTAMP());
INSERT INTO mes 
	VALUES(2, 'SHANGHAI News', NOW());
INSERT INTO mes 
	VALUES(2, 'Guangzhou News', NOW());
	SELECT NOW() FROM mes;
-- 显示所有新闻日期，发布日期只显示 日期，不用显示时间 
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 30 minute);
-- 请在mysql的sql中求出2011-11-11和1990-1-1相差多少天；
SELECT DATEDIFF('2011-11-11','1990-01-01') FROM DUAL;
-- 活了多少天 
SELECT DATEDIFF(NOW(),'1994-03-29')/365 FROM DUAL;
-- 活到90岁，共多少天
-- INTERVAL 80 YEAR,month,day,hour,minute,second
SELECT datediff(DATE_ADD('1986-11-11 11:11:11', INTERVAL 80 YEAR), NOW());
SELECT DATEDIFF(,'1994-03-29') FROM DUAL;

SELECT TIMEDIFF('10:10:10','11:11:11') FROM DUAL ;

image-20230813153514009

-- YEAR/MONTH/DAY/DATETIME()
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL
SELECT YEAR('2013-10-10') FROM DUAL;
-- unix timestamp() 秒数
SELECT UNIX_TIMESTAMP()/(24*3600*365) FROM DUAL;
-- FROM_UNIXTIME(unix_timestamp):可以将秒数[时间戳] 转换成指定格式的日期
-- 意义：在开发中，可以存放一个INT，然后表示时间，通过FROM_UNIXTIME进行转化；
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %h:%i:%s') FROM DUAL;

image-20230813154821774

d 加密函数#

image-20230813160833153

# 加密函数 
-- USER()
SELECT USER() FROM DUAL;
-- DATABASE()
SELECT DATABASE() FROM DUAL;
-- MD5() 为字符换算出一个 MD5 32 的字符串 常用用户密码加密；
-- root 密码是：hsp -> md5 -> 在数据库中存放的是加密后的密码 
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;
-- SHOW user table, pw: md5 
CREATE TABLE users (
	id INT,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	pwd char(33) NOT NULL DEFAULT ''
);
INSERT INTO users
	VALUES(100, '韩顺平', MD5('hsp'));
SELECT *        -- SQL注入问题
	FROM users 
	WHERE `name` = '韩顺平'
	AND pwd = MD5('hsp');
	
-- PASSWORD(str) -- 加密函数 $A$005$bTP+?x
-- PASSWORD()已经废止；
SELECT PASSWORD('hsp') FROM DUAL;
-- select * FROM mysql.user \g ALTER
SELECT * FROM mysql.user

e 流程控制函数#

image-20230813160924699

image-20230813161049812

# SHOW 流程控制函数 
# IF(expr1,expr2,expr3)
SELECT IF(TRUE, 'BEIJING', 'SHANGHAI') FROM DUAL;
SELECT IF(FALSE, 'BEIJING', 'SHANGHAI') FROM DUAL;
# IFNULL(expr1,expr2)
SELECT IFNULL(NULL , '韩顺平教育') FROM DUAL;
# SELECT 
-- CASE WHEN exp1 THEN exp2 
--      when exp3 THEN exp4 
-- SELECT CASE
--   WHEN FALSE THEN 'jack'
-- 	WHEN FALSE THEN 'tom'
-- FROM table_references
# 1 查询emp, comm = null, 显示 0.0
-- 判断为空，IS NULL
SELECT ename, IF(comm IS NULL, 0.0, comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0) FROM emp;
-- emp中job是clerk，显示职员，如果是manager显示经理
SELECT 
  ename,
	 (SELECT 
		CASE WHEN job = 'clerk' THEN '职员'
		WHEN job = 'manager' THEN '经理' 
		WHEN job = 'salesman' THEN '销售人员' 
		ELSE job END) AS 'job', job
	FROM emp;

7 MYSQL约束

a 基本介绍#

image-20230815232942949

b 主键#

image-20230815233012549

c 主键细节讨论#

image-20230815233653648

d 外键#

image-20230816003941909

image-20230816004252918

存在外键约束；

e 外键约束细节#

image-20230816004522148

外键指向主表某列，该列必须是主键或者UNIQUE

image-20230816005950912

image-20230816010014111

image-20230816010242702

前提：外键字段允许为空；👇🏻

image-20230816010452940

# 外键SHOW

-- 1 创建主表 my_class 
CREATE TABLE my_class (
	id INT PRIMARY KEY, -- 班级编号
	`name` VARCHAR(32) NOT NULL DEFAULT''
);
-- 创建 从表 my_stu
CREATE TABLE my_stu (
	id INT PRIMARY KEY, -- 学生编号
	`name` VARCHAR(32) NOT NULL DEFAULT'',
	class_id INT, -- 学生所在班级编号
	-- 下面指定外键关系
	FOREIGN KEY (class_id) REFERENCES my_class(id)
)
-- 测试数据 
INSERT INTO my_class
	VALUES(100, 'java'), (200, 'web');
SELECT * FROM my_class;
INSERT INTO my_stu
	VALUES(1, 'tom', 100);
INSERT INTO my_stu
	VALUES(2, 'jack', 200);
INSERT INTO my_stu
	VALUES(3, 'tom', 300);  -- 这里会失败，班级300不存在
INSERT INTO my_stu
	VALUES(5, 'king', NULL); -- 可以
-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class
	WHERE id = 100;

CHECK#

image-20230816010821581

# CHECK SHOW
-- MySQL15.7目前还不支持，只能语法校验，但不会生效
-- 了解
-- 学习oracle，sql server,这两个DB是真生效

-- 测试
CREATE TABLE t23 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK ( sal > 1000 AND sal < 2000 )
);
-- 添加数据
INSERT INTO t23
	VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23；

练习：商店表设计#

image-20230816011546862

# 60 使用约束的练习
CREATE DATABASE shop_db;

-- 商品goods
CREATE TABLE goods_01 (
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(64) NOT NULL DEFAULT'',
	unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 
				CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
	category VARCHAR(64) NOT NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT '');
	
-- 客户 
CREATE TABLE customer(
	customer_id CHAR(8) PRIMARY KEY, -- 程序元自己决定
	`name` VARCHAR(64) NOT NULL DEFAULT '',
	address VARCHAR(64) NOT NULL DEFAULT '',
	email VARCHAR(64) UNIQUE NOT NULL,       -- 外键约束
	sex ENUM('男', '女') NOT NULL, -- 这里使用了【枚举】，填写时候二选一，生效
	card_id CHAR(18)
);

-- 购买purchase
CREATE TABLE purchase (
	order_id INT UNSIGNED PRIMARY KEY,
	customer_id CHAR(8),						
	goods_id INT NOT NULL DEFAULT 0, -- 外键约束在后；
	nums INT NOT NULL DEFAULT 0 ,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),	
	FOREIGN KEY (goods_id) REFERENCES goods_01(goods_id)
);
DESC goods;
DESC customer;
DESC purchase;

f 自增长#

image-20230816091104911

image-20230817005042571

指定了自增长，一般就按照自增长；

image-20230817005342636

索引

a 优化速度#

image-20230817005447266

b 索引机制#

image-20230817093559076

SELECT操作更多；

image-20230817093831381

c 创建索引#

image-20230817110459059

image-20230817110541381

d 删除索引#

image-20230817113714493

image-20230817113758222

image-20230817113814538

# 演示mysql的索引 的使用
-- 创建索引

CREATE TABLE t25 (
	id INT,
	`name` VARCHAR(32));
	
-- 查询表是否有索引
SHOW INDEXS FROM t25;

-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 唯一索引：唯一约束 ID不重复
-- ID主键索引，身份证唯一索引，姓名普通索引

-- 添加普通索引
DROP TABLE t25
CREATE  INDEX id_index ON t25 (id);
-- 如何选择
-- 1 如果某列的值，是不会重复，则优先使用 UNIQUE，否则普通索引

-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)

-- 添加主键索引
CREATE TABLE t26 (
	id INT,
	`name` VARCHAR(32)
	);
ALTER TABLE t26 ADD PRIMARY KEY (id);

-- 删除索引
DROP INDEX id_index ON t25;

-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;
SHOW INDEX FROM t26;
SHOW INDEX FROM t25;

-- 修改索引： 先删除，再添加新的索引

-- 查询索引
-- 1 方式
SHOW INDEX FROM t25
-- 2 方式
SHOW INDEXS FROM t25;
-- 3 方式3
SHOW KEYS FROM t25;

-- 4 方式 但信息不全
DESC t25;

e 创建索引规则#

image-20230817114005999

事务

a 基本概念#

image-20230817114237024

image-20230817114845401

b 事物操作#

image-20230817115431873

image-20230817120439868

image-20230817120759540

c 事务细节讨论#

image-20230817120823459

-- 讨论 事务细节
-- 如果不开始事务，默认情况下，dml操作室自动提交的，不能回滚；
INSERT INTO t27 VALUES(300, 'milan'); -- 自动提交
SELECT * FROM t27;

-- 2 如果开始一个事物，没有创建保存点，可以执行rollback
-- 默认就是回退到你事务开始的状态
START TRANSACTION;
INSERT INTO t27 VALUES(400, 'king');
INSERT INTO t27 VALUES(500, 'king');
ROLLBACK; -- 直接回到事务开始的状态 -> 300, milan

-- 3 可设置多个savepoint
-- 4 你可以在事务没有提交前。选择回退到哪个保存点
-- 5 InnoDB 存储引擎支持事务，MyISAM不支持
-- 6 开始事务方式 start TRANSACTION / set autocommit = off

d 事务 隔离级别#

image-20230817121954427

image-20230817122202405

image-20230817122525726

e 隔离级别演示#

image-20230817122704204

image-20230817123104587

f 设置隔离级别#

image-20230817125326152

image-20230817125408618

image-20230817125620734

image-20230817125750242

10 存储引擎

image-20230817143411030

image-20230817143421040

image-20230817145021881

image-20230817145419805

image-20230817145435387

image-20230817145605719

11 视图

a 视图原理#

image-20230817145803550

image-20230817145936681

image-20230817150552500

b 视图使用细节#

image-20230817150701055

image-20230817151305521

c 视图应用案例#

image-20230817153026995

image-20230817153439168

# 视图应用案例
-- emp, deptno, sal 三表
-- 分析：使用三表联合查询; 
-- 得到结果，构造视图
CREATE VIEW emp_view03
	AS
	SELECT empno, emp.ename, dname, grade
		FROM emp, dept, salgrade
		WHERE emp.deptno = dept.deptno
		AND (sal BETWEEN losal AND hisal);
DESC emp_view03;

12 MySQL管理

image-20230817154549303

image-20230817154756736

a MySQL权限管理#

image-20230817160903215

-- 1 MySQL用户管理
-- 原因：项目开发时候，可以根据不同开发人员，设定对应的用户权限；
-- 所以MySQl数据库管理人员(root)，可创建不同用户权限，供开发人员使用;

-- 1 创建新的用户;
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456';
SELECT `host`, `user`, `authentication_string`
FROM mysql.user;
-- 2 删除
DROP USER 'hsp_edu'@'localhost' 
-- 3 登录 USER 'hsp_edu'@'localhost' 

-- root 用户 修改hsp_edu@localhost 密码，能够成功
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('1234')


# 79 修改自己的密码
SET PASSWORD = PASSWORD('123456') 

-- 修改其他人的密码，需要权限
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

b MySQL权限管理#

image-20230817162040822

image-20230817214212177

image-20230817214947748

image-20230817214957693

3个权限管理细节#

image-20230817221514182

# 权限管理细节

CREATE USER jack;

SELECT `host`, `user` FROM mysql.user;

-- 你也可以这样指定
-- CREATE USER 'xxx'@'192.168.1.%' 表示xxx用户在192.168.1.1登录mysql

CREATE USER 'smith'@'192.168.1.%'
-- 删除用户时候，如果host不是%， 需要明确指定 '用户'@'host值'

DROP USER jack -- 默认是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%';

13 MySQL作业

image-20230817223028387

image-20230817223045425

image-20230818052838374

image-20230818053320099

image-20230818053406666

image-20230818053458523

image-20230818053538255

image-20230818053902085

作业3#

image-20230818054008413

作者：asking66666

出处：https://www.cnblogs.com/asking66666/p/17642837.html

版权：本作品采用「署名-非商业性使用-相同方式共享 4.0 国际」许可协议进行许可。

posted @   awsaslife  阅读(23)  评论(0)  编辑  收藏  举报
相关博文：
· ABAP 7.40記述方法（一部）
· 機能名：プログラムステップ数カウント
· MySQL基础
· 数据库和JDBC基础内容
· Java学习-第一部分-第三阶段-第三节：MySQL基础
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
more_horiz
keyboard_arrow_up light_mode palette
选择主题
menu
点击右上角即可分享
微信分享提示