mysql学习笔记（持续更新）

十番打

已于 2023-03-12 20:29:20 修改

阅读量173

点赞数

文章标签： mysql

于 2023-02-23 22:15:26 首次发布

本文链接：https://blog.csdn.net/qq_39984234/article/details/129191415

版权

mysql登录以及用户创建等

mysql --version 查看mysql版本 以及安装位置
mysql -uroot -p; 登录
select user,host from mysql.user; 查看所有用户
flush privileges;
mysql -h 主机名 -P 端口号 -u 用户名 -p密码  -p和密码之间不能有空格 其他随便
create user 'user01' @'localhost' identified by 'user01'; 添加用户
drop user ‘user01’@’localhost’; 删除用户
rename user 'user01'@'localhost' to 'User01'@'localhost'; 修改用户名

常用命令

show databases; --查看当前所有的数据库
use 数据库名; 	--打开指定的数据库
show tables; 	--查看所有的表
desc 表名 ;		--显示表的信息
create database 数据库名; --创建一个数据库
exit 			-- 退出连接

操作数据库

create database if not exists 数据库名; --创建数据库名
drop database if exists 数据库名; --删除数据库
use 数据库名 --使用数据库
show databases; --查看数据库
SHOW CREATE DATABASE 数据库名; --查看数据库的创建信息

在这里插入图片描述

创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) 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

修改数据库

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teachers;

增加表中数据

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');

-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');

-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1);

修改表中数据

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;

-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';

-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

在这里插入图片描述

删除表中数据（注意一定要加where）

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

查询语句

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

基础查询

-- 查询全部学生
SELECT * 
FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` 
FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 
FROM student AS 学生表;

-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 
FROM student;

-- 查询系统版本(函数)
SELECT VERSION();

-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;

-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` 
FROM result;

条件查询

-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` 
FROM result
WHERE `StudentResult`>=95 
AND `StudentResult`<=100;

-- &&
SELECT `StudentNo`,`StudentResult` 
FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;

-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` 
FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;

-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` 
FROM result
WHERE `StudentNo`!=1000;

-- NOT
SELECT `StudentNo`,`StudentResult` 
FROM result
WHERE NOT `StudentNo`=1000;

-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` 
FROM student
WHERE `StudentName` LIKE '%d%';

-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` 
FROM student
WHERE `StudentName` LIKE '%d_';

-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` 
FROM student
WHERE `StudentNo` IN (1000,1001);

分组查询

-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

连接查询

在这里插入图片描述

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;

-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;

-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='课程设计';

子查询

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;

-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='课程设计'
)
ORDER BY StudentResult DESC;

常用函数

在这里插入图片描述

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串

-- 时间日期函数
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 SYSTEM_USER();
SELECT USER();
SELECT VERSION();

mysql命令

☆☆查询语句select实战

基础查询

查询员工12个月的工资总和，并起别名为ANNUAL SALAR
SELECT employee_id , last_name,salary * 12 "ANNUAL SALARY" 
FROM employees; 

查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id 
FROM employees;

查询工资大于12000的员工姓名和工资
SELECT last_name, salary 
FROM employees 
WHERE salary > 12000;

查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id 
FROM employees 
WHERE employee_id = 176;

显示表 departments 的结构，并查询其中的全部数据
DESC departments; 
SELECT * FROM departments;

运算符

选择工资不在5000到12000的员工的姓名和工资
SELECT last_name, salary 
FROM employees 
WHERE salary < 5000 OR salary > 12000;

选择在20或50号部门工作的员工姓名和部门号
SELECT last_name, department_id 
FROM employees 
WHERE department_id = 20 OR department_id = 50;

选择公司中没有管理者的员工姓名及job_id
SELECT last_name, job_id 
FROM employees 
WHERE manager_id IS NULL;


选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name, salary, commission_pct 
FROM employees 
WHERE commission_pct IS NOT NULL;

选员工姓名的第三个字母是a的员工姓名
SELECT last_name 
FROM employees 
WHERE last_name LIKE '__a%';

选择姓名中有字母a和k的员工姓名
SELECT last_name 
FROM employees 
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';


显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT employee_id,first_name,last_name 
FROM employees 
WHERE first_name LIKE '%e';


显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT last_name,job_id 
FROM employees 
where department_id in (80,90,100); 

显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、 管理者id
SELECT last_name,salary,manager_id 
FROM employees 
WHERE manager_id IN (100,101,110);

排序与分页

查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序显示
SELECT last_name,department_id,salary * 12 annual_sal 
FROM employees 
ORDER BY annual_sal DESC,last_name ASC;

选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第 21到40位置的数据
SELECT last_name,salary 
FROM employees 
WHERE salary NOT BETWEEN 8000 AND 17000 
ORDER BY salary DESC LIMIT 20,20;

 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT last_name,email,department_id 
FROM employees #where email like '%e%' 
WHERE email REGEXP '[e]' 
ORDER BY LENGTH(email) DESC,department_id ASC;

在这里插入图片描述

多表查询

显示所有员工的姓名，部门号和部门名称
SELECT last_name, e.department_id, department_name 
FROM employees e 
LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id`;


查询90号部门员工的job_id和90号部门的location_id
SELECT job_id, location_id 
FROM employees e, departments d 
WHERE e.`department_id` = d.`department_id` 
AND e.`department_id` = 90;

选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name , department_name , d.location_id , city 
FROM employees e 
LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id` 
LEFT OUTER JOIN locations l ON d.`location_id` = l.`location_id` 
WHERE commission_pct IS NOT NULL;

选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果 类似于下面的格式
employees Emp# manager Mgr# 
 kochhar  101   king   100
 SELECT emp.last_name employees, emp.employee_id "Emp#",
 mgr.last_name manager, mgr.employee_id "Mgr#" 
 FROM employees emp
 LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id;

查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所 在部门的部门名称为’Executive’
SELECT department_name, street_address, last_name, job_id, salary 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
JOIN locations l ON d.`location_id` = l.`location_id` 
WHERE department_name = 'Executive'

查询哪些部门没有员工
SELECT department_id FROM departments d 
WHERE NOT EXISTS 
( SELECT * FROM employees e WHERE e.`department_id` = d.`department_id` )

聚合函数

查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) 
FROM employees;


查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary) 
FROM employees GROUP BY job_id;

选择具有各个job_id的员工人数
SELECT job_id, COUNT(*) 
FROM employees 
GROUP BY job_id;

查询员工最高工资和最低工资的差距
SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE 
FROM employees;

查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没 有管理者的员工不计算在内
SELECT manager_id, MIN(salary) 
FROM employees 
WHERE manager_id IS NOT NULL 
GROUP BY manager_id HAVING MIN(salary) > 6000;

查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal 
FROM employees e RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
GROUP BY department_name, location_id 
ORDER BY avg_sal DESC;

查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_id,MIN(salary) 
FROM departments d LEFT JOIN employees e 
ON e.`department_id` = d.`department_id` 
GROUP BY department_name,job_id;

子查询

查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary 
FROM employees 
WHERE department_id = 
( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' )

查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT employee_id, last_name, salary 
FROM employees 
WHERE salary > ( SELECT AVG(salary) FROM employees )

选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary 
FROM employees 
WHERE salary > ALL ( SELECT salary FROM employees WHERE job_id = 'SA_MAN' );

查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id, last_name 
FROM employees 
WHERE department_id = 
ANY( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' )

查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id 
FROM employees 
WHERE department_id IN 
( SELECT department_id FROM departments WHERE location_id = 1700 )

查询管理者是King的员工姓名和工资
SELECT last_name, salary 
FROM employees 
WHERE manager_id 
IN ( SELECT employee_id FROM employees WHERE last_name = 'King' )

查询工资最低的员工信息: last_name, salary
SELECT last_name,salary 
FROM employees 
WHERE salary = ( SELECT MIN(salary) FROM employees );

查询平均工资最低的部门信息
SELECT * 
FROM departments 
WHERE department_id = 
	( SELECT department_id 
		FROM employees 
		GROUP BY department_id 
		HAVING AVG(salary) <= 
		ALL( SELECT AVG(salary) avg_sal 
		FROM employees 
		GROUP BY department_id ) );

查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT d.*,(SELECT AVG(salary) 
FROM employees 
WHERE department_id = d.department_id) avg_sal 
FROM departments d 
WHERE department_id = ( 
	SELECT department_id 
	FROM employees 
	GROUP BY department_id 
	HAVING AVG(salary) = ( 
		SELECT AVG(salary) avg_sal 
		FROM employees 
		GROUP BY department_id 
		ORDER BY avg_sal LIMIT 0,1 
		)
	)

查询平均工资最高的 job 信息
SELECT * 
FROM jobs 
WHERE job_id = ( 
	SELECT job_id 
	FROM employees 
	GROUP BY job_id 
	HAVING AVG(salary) >= ALL
		( SELECT AVG(salary) 
		FROM employees 
		GROUP BY job_id 
		) 
	);

查询每个部门下的部门人数大于 5 的部门名称
SELECT department_name,department_id 
FROM departments d 
WHERE 5 < ( 
	SELECT COUNT(*) 
	FROM employees e 
	WHERE d.`department_id` = e.`department_id` 
	);

查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT employee_id, last_name, hire_date, salary 
FROM employees 
WHERE manager_id = ( 
	SELECT employee_id 
	FROM employees 
	WHERE last_name = 'De Haan' 
	);

 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
 SELECT employee_id,last_name, department_id, email, salary 
 FROM employees 
 WHERE employee_id IN 
 	(SELECT DISTINCT manager_id 
 	FROM employees 
 	WHERE department_id = 
 		( SELECT department_id 
 		FROM employees e 
 		GROUP BY department_id 
 		HAVING AVG(salary)>=ALL( 
 			SELECT AVG(salary) 
 			FROM employees 
 			GROUP BY department_id 
 			) 
 		) 
 	);