MySQL基础

已于 2022-08-24 14:20:09 修改
阅读量1.5k 收藏 3
点赞数
分类专栏： MySQL 文章标签： mysql
于 2022-03-11 18:57:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dark159735/article/details/123327557
版权

参考文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

数据类型

类型具体类型
整数类型BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型FLOAT、DOUBLE、DECIMAL
字符串类型CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型Date、DateTime、TimeStamp、Time、Year
其他数据类型BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、MultiPolygon、GeometryCollection，JSON

1.整数类型

  • tinyint(m)：1个字节(-128~127)
  • smallint(m)：2个字节(-32768~32767)
  • mediumint(m)：3个字节(-8388608~8388607)
  • int(m) ：4个字节(-2147483648~2147483647)
  • bigint(m)：8个字节(-9223372036854775808~9223372036854775807)

取值范围如果加了unsigned，则最大值翻倍；m表示宽度，不足宽度的前面用0填充，需要配合ZEROFILL使用

2.浮点数类型

  • float(m,d)：单精度浮点型，8位精度(4字节) ，总个数m，小数点后d位
  • double(m,d)：双精度浮点型，16位精度(8字节)，总个数m，小数点后d位
  • decimal(m,d) ：定点数，总个数m（0<=m<=65）,小数点后d位（0<=m<=30），d<m

3.字符串类型

  • char(m)：固定长度，最多255个字符，m个字节
  • varchar(m)：可变长度，最多65535个字符，m+1个字节
  • tinytext：可变长度，最多255个字符
  • text：可变长度，最多65535个字符
  • mediumtext：可变长度，最多2的24次方-1个字符
  • longtext：可变长度，最多2的32次方-1个字符
  • TINY BLOB：可变长度，最多255个字符
  • BLOB：可变长度，最多65535个字符
  • MEDIUM BLOB：可变长度，，最多2的24次方-1个字符
  • LONG BLOB可变长度，，最多2的32次方-1个字符
  • BINARY(m)：固定长度，m个字节
  • VARBINARY(m)：可变长度，m+1个字节

1._TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写
2._TEXT可以指定字符集
3.text的索引似乎不起作用
4.比指定类型支持的最大范围大的值将被自动截短

4.日期类型

  • Date：YYYY-MM-DD格式
  • DateTime：YYYY-MM-DD HH:MM:SS格式
  • TimeStamp：YYYY-MM-DD格式，自动存储记录修改时间
  • Time：HH:MM:SS格式
  • Year：YYYY格式

5.其他数据类型

  • ENUM：字符串枚举，enum(‘male’,‘female’,‘?’)
  • SET：集合字符串，定义了集合之后，不能插入集合元素之外的字符串,set(‘string1’,‘string2’,‘string3’)
  • Geometry：地理空间位置
  • GeometryCollection：地理空间位置集合
  • Point：坐标值
  • MultiPoint：坐标集合
  • LineString：线
  • MultiLineString：线集合
  • Polygon：多边形
  • MultiPolygon：多边形集合
  • JSON：JSON格式的存储

字符串

1.CONCAT(sl，s2，…)

直接拼接一个或者多个字符串
在这里插入图片描述
注意：若有任何一个参数为 NULL，则返回值为 NULL。

2.CONCAT_WS(separator,str1,str2,…)

分隔符拼接字符串，不会因为参数为NULL值而返回NULL ，但如果分隔符为 NULL，则结果为 NULL
在这里插入图片描述

3.group_concat

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

在这里插入图片描述
4.repeat()

复制字符串
在这里插入图片描述
5.REPLACE(str,from_str,to_str)
将所有str中所有出现的字符串from_str替换to_str，REPLACE()搜索时执行区分大小写的匹配。

SELECT REPLACE('aabbbaa', 'bb', '')
--> aabaa

column列

1.修改列
在这里插入图片描述

ALTER table settlement_port
CHANGE COLUMN `add_sub_item_data` `add_item_data` json DEFAULT NULL COMMENT '增项json';

 alter  table employee_family modify  column is_main  TINYINT DEFAULT 0 COMMENT '是否为紧急联系人';

2.添加列

ALTER table settlement_port
ADD COLUMN `sub_item_data` json DEFAULT NULL COMMENT '减项json' AFTER `add_item_data`;

3.列约束

-- 表中性别字段设置约束，数值为男或女。
ALTER table Swordsmen
add CONSTRAINT ck_sGender check(sGender='男' or sGender='女');

-- 为表添加外健。
ALTER TABLE MenKungfu
add CONSTRAINT fk_MenKungfu_sID_Swordsmen FOREIGN key(sID)
REFERENCES Swordsmen(sID);

-- 删除约束
ALTER table Swordsmen DROP CONSTRAINT ck_sGender;

时间

1.时间格式化

SELECT  DATE_FORMAT(NOW(),'%Y-%m-%d');
--> 2022-03-15

SELECT  DATE_FORMAT( '2022-09-15 09:33:45' ,    '%Y-%m-%d %H:%i:%S' );
-> 2022-09-15 09:33:45

2.时间差

SELECT DATEDIFF('2022-11-30 23:59:59','2022-12-31');
--> -31

3.返回星期索引，星期日：1，星期一：2 …

SELECT DAYOFWEEK('2022-03-23');
--> 4

条件控制

SELECT if(1>2,'true','false');
--> false

SELECT IFNULL(NULL,1);
--> 1
SELECT IFNULL(1,0);
-> 1

-- expr1 = expr2返回null，否则返回expr1
SELECT NULLIF(1,1);
-> NULL
SELECT NULLIF(1,2);
-> 1


SELECT 
 (CASE s_sex
	WHEN '男' THEN 1
	WHEN '女' THEN 0
	ELSE '*'
END) 'sex'
FROM student

视图

作用：
1.简单，用户不需要指定条件
2.安全，视图可以授权

-- ④创建视图用于查询侠客所属门派，列出侠客姓名，门派名称以及入门派时间
CREATE VIEW view_xk as
SELECT sname,dName,dEnterDate
FROM Swordsmen s join Department d
on s.dID=d.dID;

-- 查看视图
desc view_xk;

-- 删除视图
drop  view view_xk;

视图更新：
视图可更新，视图中的行与基础表中的行必须存在一对一的关系。如果视图包含下面任何一项则不可更新：
1.聚合函数或窗口函数
2.DISTINCT
3.GROUP BY
4.HAVING
5.UNION 或UNION ALL

变量

1.系统变量：是MySQL服务器提供，属于服务器层面，分为全局变量(GLOBAL)、会话变量(SESSION)
在这里插入图片描述

-- 查看所有系统变量
SHOW VARIABLES;

-- 模糊搜索所有系统变量
SHOW SESSION VARIABLES like '%auto%';

-- 查看所有会话系统变量
SHOW SESSION VARIABLES;


-- 查看所有全局系统变量
SHOW GLOBAL VARIABLES;

-- 查看所有全局系统变量的值
SELECT @@GLOBAL.autocommit;



-- 设置会话系统变量
SET SESSION autocommit=0

-- 设置全局系统变量，服务器重启会恢复原样，如果想永久设置，需要配置my.cnf文件
SET GLOBAL autocommit=1

2.用户变量：用户自定义的变量，用户变量不需要提前声明，只需要’@var’就可以使用，作用域为当前连接。

-- 定义用户变量两种方式，推荐':'
set @name = 'zhangsan' , @age := 10;

-- 将查询结果赋值给用户变量
SELECT COUNT(*) into @count FROM users;

-- 使用
SELECT @name,@age,@count;

3.局部变量：局部变量是在定义的局部生效，访问之前需要DECLARE声明。

delimiter //
CREATE PROCEDURE p1()
BEGIN 
  -- 声明局部变量
	DECLARE user_count int DEFAULT 0;
	-- 给局部变量赋值
	set user_count =10;
	-- 将查询结果给局部变量赋值
	SELECT COUNT(*) into user_count FROM users; 
	
	SELECT user_count;
end //;

CALL p1();

DROP PROCEDURE  p1;

将变量设为null就是删除，因为不存在的变量查询出来也是null

存储过程

作用：存储过程是事先经过编译在数据库中的一段sql语句，将多段sql封装到一个存储过程中，可以减少服务器与应用之间的传输次数。

-- ⑤创建存储过程，查询指定武功所使用的武器
delimiter //
CREATE PROCEDURE sp_kw(kn VARCHAR(15))
BEGIN
SELECT kWeapon 
FROM kungfu 
WHERE kName=kn;
END//

-- 调用存储过程
CALL sp_kw('打狗棍法');

-- 删除存储过程
DROP PROCEDURE sp_kw;

-- 查看jinyong2db这个数据库的存储过程
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'jinyong2db';

-- 查看sp_kw这个存储过程的sql
SHOW CREATE PROCEDURE sp_kw;

-------------------------华丽分割线-------------------------------

-- IN 该参数作为入参
-- OUT 作为存储过程返回值
-- INOUT 既可以作为入参，也可以作为出参

-- 存储过程条件判断
delimiter //
CREATE PROCEDURE p_score(in score int ,out result VARCHAR(10))
BEGIN 
	IF score<60 
			THEN set result:='不及格';
	ELSEIF score=60 
	    THEN set result:='666';
	ELSE set result:='优秀';
  END IF;
end //

CALL p_score(60,@result);

SELECT @result;

DROP PROCEDURE  p_score;

-------------------------华丽分割线-------------------------------

-- 换算
delimiter //
CREATE PROCEDURE p_convert(inout score DOUBLE)
BEGIN 
	SET score :=score/100;
end //

set @score=100;

CALL p_convert(@score);

SELECT @score;

-------------------------华丽分割线-------------------------------

-- while循环
delimiter //
CREATE PROCEDURE p_while(inout n DOUBLE)
BEGIN 

 WHILE n<100 DO
	set n := n+1;
 END WHILE;

end //

set @n=0;

CALL p_while(@n);

SELECT @n;

-------------------------华丽分割线-------------------------------

-- repeat循环,先执行一次,满足条件退出
delimiter //
CREATE PROCEDURE p_repeat(inout i DOUBLE)
BEGIN 

 REPEAT
	set i := i+1;
 UNTIL i=100 
 END REPEAT;

end //

set @i=0;

CALL p_repeat(@i);

SELECT @i;

-------------------------华丽分割线-------------------------------

-- loop循环,需要在sql逻辑中增加退出循环的条件，否则会死循环。
-- LEAVE ：配合循环使用，退出循环
-- ITERATE ：在循环体中，跳过当前循环
delimiter //
CREATE PROCEDURE p_loop(L DOUBLE)
BEGIN 

DECLARE total int DEFAULT 0;

sum: LOOP
	IF L=100 THEN
		LEAVE sum; 
	END IF;
  IF L%2=0 THEN
		set L := L+1;
		ITERATE sum; 
	END IF;	
	
	set L := L+1;
	set total :=total+L;
END LOOP sum;

SELECT total;
end //

CALL p_loop(@L);

DROP PROCEDURE p_loop;

-------------------------华丽分割线-------------------------------

-- 条件处理程序(handler):可以定义在流程控制结构执行过程中遇到问题时相应处理步骤。
DECLARE handler_action HANDLER for [condition_value] statement;

handler_action
			CONTINUE:继续执行当前程序
			EXIT:终止执行当前程序
condition_value
    SQLSTATE sqlstate_value:状态码，参考mysql官网
		SQLWARNING:所有以01开头的SQLSTATE代码简写
		NOT FOUND:所有以02开头的SQLSTATE代码简写
		SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕捉的SQLSTATE代码简写

-------------------------华丽分割线-------------------------------

-- 游标：用来存储查询结果的数据集合
-- 声明游标：DECLARE 游标名称 CURSOR FOR 查询语句;
-- 打开游标：OPEN 游标名称;
-- 获取游标记录并赋值给变量：FETCH 游标名称 INTO [变量];
-- 关闭游标：CLOSE 游标名称;

delimiter //
CREATE PROCEDURE p_cursor(in Hot DOUBLE)
BEGIN 

	DECLARE c_name VARCHAR(100);
	DECLARE c_addr VARCHAR(100);
	DECLARE s_cursor CURSOR for SELECT sName,sAdd FROM songs WHERE sHot >=  Hot;
	DECLARE exit handler for SQLSTATE '02000' CLOSE s_cursor;
	
	DROP table if EXISTS tb_songs;
	CREATE TABLE if not EXISTS tb_songs(
	 `id` int PRIMARY key auto_increment,
	 `tb_name` VARCHAR(100),
	 `tb_addr` VARCHAR(100)
	);
	
	OPEN s_cursor;
	WHILE true DO
		FETCH s_cursor into c_name,c_addr;
		INSERT into tb_songs(tb_name,tb_addr)VALUES(c_name,c_addr);
  END WHILE;
	CLOSE s_cursor;
	
end //

CALL p_cursor(10);

DROP PROCEDURE p_cursor;

存储函数

-- 存储函数,必须要有返回值

delimiter //
CREATE FUNCTION fsum(n int)
RETURNS int DETERMINISTIC
BEGIN 
  DECLARE total int DEFAULT 0;
	
	WHILE n>0 DO
		set total := total+n;
		set n := n-1;
  END WHILE;

	RETURN total;	
end //

SELECT fsum(100);

DROP FUNCTION if EXISTS fsum;

触发器

定义：触发器是在DML语句之前或之后，触发执行触发器中定义的SQL语句集合。

  • insert型触发器：NEW表示将要或者已经新增的数据
  • update型触发器：OLD表示修改之前的数据，NEW表示将要或者已经修改的数据
  • delete型触发器：OLD表示将要或者已经删除的数据

创建：

delimiter //
CREATE TRIGGER trigger_name
BEFORE/AFTER  INSERT/UPDATE/DELETE
ON tab_name FOR EACH ROW -- 行级触发器
BEGIN
  trigger statement;
END; //

-------------------------华丽分割线-------------------------------

-- insert型触发器：users表中插入数据，通过触发器记录到user_logs表中

delimiter //
CREATE TRIGGER trigger_insert
AFTER INSERT on users FOR EACH ROW
BEGIN
	INSERT into user_logs(operation,user_data,create_time) 
	VALUES ('insert',CONCAT('插入：',new.uid,new.uName),NOW());
END; //

-- 插入测试
INSERT into users(uName,uPwd,uGender,uBirthDate) VALUES ('大蛇','156323','男',NOW());

-- 查看所有触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER trigger_insert;

-------------------------华丽分割线-------------------------------

-- update型触发器：users表中更新数据，通过触发器记录到user_logs表中

delimiter //
CREATE TRIGGER trigger_update
AFTER UPDATE on users FOR EACH ROW
BEGIN
	INSERT into user_logs(operation,user_data,create_time) 
	VALUES ('update',CONCAT('更新前：',old.uid,old.uName,'-更新后：',new.uid,new.uName) ,NOW());
END; //

-- 更新测试
update users set uName='火麟飞' WHERE uid=7;

-------------------------华丽分割线-------------------------------

-- delete型触发器：users表中删除数据，通过触发器记录到user_logs表中

delimiter //
CREATE TRIGGER trigger_delete
AFTER DELETE on users FOR EACH ROW
BEGIN
	INSERT into user_logs(operation,user_data,create_time) 
	VALUES ('delete',CONCAT('删除数据：',old.uid,old.uName) ,NOW());
END; //

-- 删除测试
DELETE FROM users WHERE uid=5;

事务

-- 查看事务提交，on：自动  off：手动
SHOW VARIABLES LIKE 'autocommit';

--设置事务提交方式，1：自动  0：手动
set @@autocommit=0

-- 开启一个事务START transaction;或	BEGIN; 	
BEGIN; 
UPDATE users SET uName='陆小果' WHERE uID=1	
-- 事务提交commit;  事务回滚rollback; 
commit;

JSON

1.查找指定键的所有值

{
            "clientId": "31",
            "agreementId": "12",
            "settlementName": "大时代",
            "taxRate": 0.25,
            "serviceFeeType": "1",
            "serviceFeeData": null,
            "addItemData": [{
                        "name":"绩效",
                        "value":"10"
                       },{
                        "name":"哈哈哈",
                        "value":"100"
                       }],
             "subItemData":[{
                        "name":"迟到",
                        "value":"12"
                       },
                       {
                        "name":"狡猾",
                        "value":"100"
                       }]           
   }

SELECT 
sp.add_item_data->'$[*].name' as 'addItemData',
sp.sub_item_data->'$[*].name' as 'subItemData'
FROM settlement_port sp

在这里插入图片描述

笛卡尔积

笛卡尔积是多个集合的组合，会产生很多数据，但可以利用笛卡尔积巧妙解决很多问题
在这里插入图片描述

UPDATE

如果更新影响行数为0，要么没有查出数据，要么查出的数据和更新的数据是一样的。

mysql自带的四个数据库

information_schema

information_schema数据库记录了mysql的元数据。其他数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权限等。

TABLES表记录了mysql其他所有数据库的表

SELECT
    TABLE_SCHEMA  '库名',
    TABLE_NAME  '表名',
    TABLE_COMMENT  '表注释'
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'nacos'  AND TABLE_NAME = 'users';

在这里插入图片描述

查询该表的所有字段

SELECT
TABLE_SCHEMA  '库名',
TABLE_NAME  '表名',
COLUMN_NAME  '列名',
COLUMN_KEY 'KEY',
DATA_TYPE  '数据类型',
COLUMN_COMMENT  '注释'
FROM
	information_schema.`COLUMNS`
	
WHERE TABLE_SCHEMA = 'nacos'  AND TABLE_NAME = 'users';

在这里插入图片描述

information_schema

主要用于收集数据库服务器性能参数。

mysql

主要负责存储数据库的用户、权限设置、关键字等。

sys

这个数据库有很多个函数和视图，存储了很多mysql的一些信息，如某个数据库用的的索引使用情况，耗时，用的什么引擎等等。

实战

--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

-- where在查询结果返回前对数据库字段进行约束，不能使用聚合函数；而having是在查询结果返回后对结果字段进行约束，自然可以使用聚合函数
-- where后面的and是对前面笛卡尔积表的条件限制，on后面的and只能是对当前表的条件限制
-- 聚合函数一定要配合group by使用

-- union 合并行会去重，union all合并不会去重。
-- 合并的数据必须具有相同列。
-- union 效率比 in 高


-- 创建临时表
CREATE TEMPORARY TABLE temp_t AS
(
    SELECT *  FROM Student
);
SELECT *
FROM temp_t;
-- 删除临时表
DROP TEMPORARY  TABLE temp_t;

-- 查看名字包含某字符的同学
SELECT *
FROM student stu
WHERE stu.s_name like '%赵%';

-- 多字段去重
SELECT
sp.receiving_company,
sp.receiving_account,
sp.receiving_bank,
FROM settlement_port sp
GROUP BY CONCAT(sp.receiving_company,sp.receiving_account,sp.receiving_bank)

-- 查询每个同学的最高学历
SELECT
e.id,
e.employee_id,
e.major_name,
e.school_name,
e.academic_degree
FROM employee_education e 
WHERE
(select COUNT(*) from employee_education b 
                 where b.employee_id=e.employee_id and
                 e.academic_degree<=b.academic_degree)<=1

-- 找出老6及她的兄弟
SELECT *
FROM student stu
WHERE stu.s_name  Regexp '老[1-9]';

-- 查看王姓的同学
SELECT *
FROM student stu
WHERE stu.s_name like '王%';
或
SELECT *
FROM student stu
WHERE stu.s_name like '王_';

SELECT *
FROM student stu
WHERE (stu.s_name,stu.s_sex)=('赵雷','男');

-- 先按性别分组，再按生日分组，最后汇总
SELECT 
s_sex,s_birth,count(s_sex)
FROM student stu
GROUP BY s_sex,s_birth WITH ROLLUP;

-- 统计有多少种姓氏
SELECT COUNT(DISTINCT LEFT(s_name,1))
FROM student;

-- 查看生日在范围内的同学
SELECT *
FROM student
WHERE  s_birth BETWEEN '1990-01-01' and '1991-12-01';

-- 查询字符串中某个字符的重复次数
SELECT (LENGTH('aabaa') - LENGTH(REPLACE('aabaa', 'b', ''))) / LENGTH('b')

-- 行转列
SELECT 
      name, 
      MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
      MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
      MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
      MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
FROM  
(
SELECT 
st.s_name 'name',
co.c_name 'Subject',
sc.s_score 'Score'
FROM student st
LEFT JOIN score sc on st.s_id=sc.s_id
LEFT JOIN course co on co.c_id=sc.c_id
) list
GROUP BY name

-- 使用WITH创建临时表代替student、score、course表，查询学生学科对应的分数
WITH
  	t1 AS (SELECT * FROM student),
  	t2 AS (SELECT * FROM score),
	t3 AS (SELECT * FROM course)
SELECT t1.s_name,t3.c_name,t2.s_score 
FROM t1
LEFT JOIN t2 on t1.s_id=t2.s_id
LEFT JOIN t3 on t2.c_id=t3.c_id;


-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 
SELECT st.s_name,sc1.s_score as '语文',sc2.s_score as '数学' 
FROM student st 
LEFT JOIN score sc1 on sc1.s_id=st.s_id and sc1.c_id='01'
LEFT JOIN score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
WHERE sc1.s_score>sc2.s_score;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.s_id,st.s_name,AVG(sc.s_score) as '平均成绩'
FROM student st
LEFT JOIN score sc on st.s_id=sc.s_id
group by st.s_id HAVING AVG(sc.s_score)>=60;


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT st.s_id,st.s_name,COUNT(sc.c_id) as '选课总数',SUM(case when sc.s_score is null then 0 else sc.s_score end) as '总成绩'
FROM student st
LEFT JOIN score sc on st.s_id=sc.s_id
GROUP BY st.s_id

-- 6、查询"李"姓老师的数量
SELECT COUNT(t.t_name) as '李姓老师'
FROM teacher t
WHERE t.t_name like '李_%'

-- 7、查询学过"张三"老师授课的同学的信息
SELECT DISTINCT st.*
FROM student st 
LEFT JOIN score sc on sc.s_id=st.s_id
LEFT JOIN course co on co.c_id=sc.c_id
LEFT JOIN teacher te on te.t_id=co.t_id
WHERE te.t_name='张三';

-- 求差集 8、查询没学过"张三"老师授课的同学的信息 

SELECT st2.*
FROM student st2
WHERE st2.s_id not in
(SELECT DISTINCT st.s_id
FROM student st 
LEFT JOIN score sc on sc.s_id=st.s_id
LEFT JOIN course co on co.c_id=sc.c_id
LEFT JOIN teacher te on te.t_id=co.t_id
WHERE te.t_name='张三');

-- 求交集 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT st1.*
FROM student st1 
LEFT JOIN score sc1 on sc1.s_id=st1.s_id and sc1.c_id='01'
WHERE st1.s_id in 
(SELECT st2.s_id
FROM student st2 
LEFT JOIN score sc2 on sc2.s_id=st2.s_id and sc2.c_id='02')


-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT st1.*
FROM student st1 
LEFT JOIN score sc1 on sc1.s_id=st1.s_id and sc1.c_id='01'
WHERE st1.s_id not in 
(SELECT st2.s_id
FROM student st2 
LEFT JOIN score sc2 on sc2.s_id=st2.s_id and sc2.c_id='02')

-- 11、查询没有学全所有课程的同学的信息
SELECT st1.*
FROM student st1 
LEFT JOIN score sc1 on sc1.s_id=st1.s_id
GROUP BY st1.s_id
HAVING COUNT(*)<(
	SELECT DISTINCT COUNT(course.c_name) FROM course
)

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT st1.*
FROM student st1
LEFT JOIN score sc2 on sc2.s_id=st1.s_id
WHERE st1.s_id !='01' AND sc2.c_id in
(SELECT sc1.c_id
FROM student st2
LEFT JOIN score sc1 on sc1.s_id=st2.s_id
WHERE  st2.s_id='01')

-- 集合对比 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select  st.* from student st 
left join score sc on sc.s_id=st.s_id
group by st.s_id
having group_concat(sc.c_id) = 
(
select  group_concat(sc2.c_id) from student st2
left join score sc2 on sc2.s_id=st2.s_id
where st2.s_id ='01'
)

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 3.排除
SELECT *
FROM student st
WHERE st.s_id not in(
-- 2.查询上过张三老师的课的学生id
SELECT sc.s_id
FROM score sc
LEFT JOIN course co on co.c_id=sc.c_id
LEFT JOIN teacher t on t.t_id=co.t_id
WHERE t.t_name='张三'
)


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.s_id,st.s_name,avg (case when sc.s_score is null then 0 else sc.s_score end) as '平均分数'
FROM student st
LEFT JOIN score sc on sc.s_id=st.s_id
WHERE sc.s_score<60
GROUP BY st.s_id
HAVING COUNT(*)>=2

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT stu.*
FROM student stu
INNER JOIN score sc on sc.s_id=stu.s_id and sc.c_id='01' and sc.s_score<60
GROUP BY stu.s_id

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select st.s_id,st.s_name,avg(sc4.s_score) "平均分",sc.s_score "语文",sc2.s_score "数学",sc3.s_score "英语" from student st
left join score sc  on sc.s_id=st.s_id  and sc.c_id="01"
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id="02"
left join score sc3 on sc3.s_id=st.s_id and sc3.c_id="03"
left join score sc4 on sc4.s_id=st.s_id
group by st.s_id 
order by avg(sc4.s_score) desc

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT co.c_id as '课程ID',
co.c_name as '课程name',
MAX(sc.s_score) as '最高分',
MIN(sc.s_score) as '最低分',
AVG(sc.s_score) as '平均分',
((SELECT COUNT(s_id) FROM score WHERE s_score>=60 AND c_id=co.c_id)/(SELECT COUNT(s_id) FROM score WHERE  c_id=co.c_id)) as '及格率'
FROM course co
LEFT JOIN score sc on sc.c_id=co.c_id
GROUP BY co.c_id


-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
-- 加@score是为了防止用union all 后打乱了顺序

select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 
from (select c.c_name,sc.* from course c 
left join score sc on sc.c_id=c.c_id
where c.c_id="01" order by sc.s_score desc) c1 ,
(select @i:=0) a
union all 
select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 
from (select c.c_name,sc.* from course c 
left join score sc on sc.c_id=c.c_id
where c.c_id="02" order by sc.s_score desc) c2 ,
(select @ii:=0) aa 
union all
select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 
from (select c.c_name,sc.* from course c 
left join score sc on sc.c_id=c.c_id
where c.c_id="03" order by sc.s_score desc) c3;
set @iii=0;


-- 20、查询学生的总成绩并进行排名
SELECT stu.s_name,SUM(sc.s_score) '总分'
FROM student stu
LEFT JOIN score sc on sc.s_id=stu.s_id
GROUP BY stu.s_id
ORDER BY SUM(sc.s_score) DESC

-- 21、查询不同老师所教不同课程平均分从高到低显示 
SELECT t.t_name,co.c_name,AVG(sc.s_score)
FROM teacher t
LEFT JOIN course co on co.t_id=t.t_id
LEFT JOIN score sc on sc.c_id=co.c_id
GROUP BY t.t_id
ORDER BY AVG(sc.s_score) DESC

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select a.* from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id="01"
order by sc.s_score desc LIMIT 1,2 ) a
union all
select b.* from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id="02"
order by sc.s_score desc LIMIT 1,2) b
union all
select c.* from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id="03"
order by sc.s_score desc LIMIT 1,2) c


-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT 
c.c_id,c.c_name,
((SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id and sc.s_score<=100 and sc.s_score>85)/
(SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id)) '100-85',
((SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id and sc.s_score<=85 and sc.s_score>70)/
(SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id)) '85-70',
((SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id and sc.s_score<=70 and sc.s_score>60)/
(SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id)) '70-60',
((SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id and sc.s_score<=60 and sc.s_score>0)/
(SELECT COUNT(0) FROM score sc WHERE c.c_id=sc.c_id)) '60-0'
FROM course c 

-- 24、查询学生平均成绩及其名次 
set @i=0;
SELECT 
a.*,
@i:=@i+1 '排名'
FROM
(SELECT 
st.s_id,
st.s_name,
ROUND((case when avg(sc.s_score) is NULL THEN 0 else AVG(sc.s_score) END),2)'平均成绩'
FROM student st 
LEFT JOIN score sc on sc.s_id=st.s_id 
GROUP BY st.s_id ORDER BY sc.s_score DESC) a


-- 25、查询各科成绩前三名的记录，并集
select a.* from (
 select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
 left join score sc on sc.s_id=st.s_id
 inner join course c on c.c_id=sc.c_id and c.c_id='01'
 order by sc.s_score desc LIMIT 0,3) a
union all 
select b.* from (
 select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
 left join score sc on sc.s_id=st.s_id
 inner join course c on c.c_id=sc.c_id and c.c_id='02'
 order by sc.s_score desc LIMIT 0,3) b
union all
select c.* from (
 select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
 left join score sc on sc.s_id=st.s_id
 inner join course c on c.c_id=sc.c_id and c.c_id='03'
 order by sc.s_score desc LIMIT 0,3) c


-- 26、查询每门课程被选修的学生数 
select c.c_id,c.c_name,count(1) from course c 
left join score sc on sc.c_id=c.c_id
inner join student st on st.s_id=c.c_id
group by st.s_id



-- 27、查询出只有两门课程的全部学生的学号和姓名，求交集
select st.s_id,st.s_name from student st 
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id 
group by st.s_id having count(1)=2


-- 28、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
select c.c_id,c.c_name,avg(sc.s_score) '平均成绩' from course c
inner join score sc on sc.c_id=c.c_id  
group by c.c_id order by avg(sc.s_score) desc,c.c_id asc

-- 29、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select st2.s_id,st2.s_name,c2.c_name,sc2.s_score from student st2
left join score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id 
where st2.s_id in(
select st.s_id from student st 
left join score sc on sc.s_id=st.s_id 
group by st.s_id having min(sc.s_score)>=70)
order by s_id


-- 30、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select st.s_id,st.s_name,sc.c_id,sc.s_score from student st 
left join score sc on sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
where (
select count(1) from student st2 
left join score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id
where sc.s_score=sc2.s_score and c.c_id!=c2.c_id 
)>1


-- 31、查询每门功成绩最好的前两名 
select st.s_name,co.c_name,a.s_score from score a
LEFT JOIN student st on st.s_id=a.s_id
LEFT JOIN course co on co.c_id=a.c_id
 where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 order by a.c_id


-- 32、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select sc.c_id,count(1) from score sc
left join course c on c.c_id=sc.c_id
group by c.c_id having count(1)>5
order by count(1) desc,sc.c_id asc
愛沢かりん
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql数据类型及运算符
hjyhjy0的博客
12-01 474
数据类型 mysql数据类型介绍 一、数据类型简介 1. 数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段 插入的数据内容 2. 不同的数据类型也决定了 MySQL 在存储它们的时候使用的方式，以及在使用它们的时候选择什么运算符 号进行运算 3. 数值数据类型： 整数： TINYINT 、SMALINT 、MEDIUMINT 、INT 、BIGINT 、 浮点数： FLOAT 、DOUBLE 、 定点数： DECIMAL 4. 日期/时间类型：YEAR 、TIME
mysql基础
smart_potato_的博客
03-24 627
#连接mysql mysql -h127.0.0.1 -p3306 -uroot -p #查看数据库 show databases; #创建数据库 create database xxx; #删除数据库 drop database xxx; #使用数据库 use xxx; #显示数据库中的表 show tables; #创建表 create t...
Programming tools
dark159735的博客
08-11 4792
还挺好用，就是界面不是很简洁。
xxl-job
dark159735的博客
03-26 3605
一.简介 XXL-Job：大众点评的分布式任务调度平台，是一个轻量级分布式任务调度平台, 其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线，开箱即用。 源码地址：https://gitee.com/xuxueli0323/xxl-job 文档地址：https://www.xuxueli.com/xxl-job/ 特性 简单灵活 提供Web页面对任务进行管理，管理系统支持用户管理、权限控制； 支持容器部署； 支持通过通用HTTP提供跨平台任务调度； 丰富的任务管理功
MySQL学习（二）—— 常用API
qq_50615776的博客
03-15 834
描述：分配或初始化与mysql_real_connect()相适应的MYSQL对象。如果mysql是NULL指针，该函数将分配、初始化、并返回新对象。否则，将初始化对象，并返回对象的地址。如果mysql_init()分配了新的对象，当调用mysql_close()来关闭连接时。将释放该对象。返回值：初始化的MYSQL*句柄。如果无足够内存以分配新的对象，返回NULL。错误：在内存不足的情况下，返回NULL。
第三方库认识: MySQL数据库API详解
最新发布
m0_61476090的博客
02-06 592
返回连接成功后的句柄，若失败则返回NULL。以上是MySQL数据库API中常用函数的介绍及使用方法，通过深入理解这些函数，我们能够更加灵活地编写与MySQL数据库交互的C语言程序。此函数用于初始化MySQL操作句柄，若参数为NULL，则会动态分配句柄空间进行初始化。这两个函数用于获取结果集中的行数和列数，分别返回uint64_t类型和unsigned int类型的值。传入MySQL句柄和数据库名称，若成功则返回0，失败返回非0。传入MySQL句柄和SQL语句字符串，若成功则返回0，失败返回非0。
MySQL基础PDF版
05-16
2. **MySQL基础** - **DBMS**：数据库管理系统（DataBase Management System），如MySQL，用于科学地组织和存储数据，高效地获取和维护数据。 - **SQL语句分类**：主要包括DDL（数据定义语言）、DML（数据操作语言...
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf可能是一份教程或教材，涵盖了MySQL的基础知识，包括安装、配置、基本操作以及一些核心概念。在这里，我们将深入探讨MySQL的一些关键点。 1. **安装与配置**: MySQL的安装通常涉及下载安装包，...
MySQL基础.docx
03-16
这篇文档主要针对初学者，涵盖了MySQL的基础知识，包括数据库的基本概念、MySQL的安装与使用、SQL语句的学习以及一些高级特性。 首先，理解数据库的重要性至关重要。数据库能够将数据持久化存储在本地，提供结构化...
MySQL基础 javaweb
01-02
MySQL基础javaweb 在 javaweb 开发中，MySQL 是一个非常重要的组件，它提供了数据持久化存储和管理的功能。在本章节中，我们将学习 MySQL 的基础知识，包括 MySQL 的安装、登录、基本操作、CRUD 操作等。 数据库...
mysql mysql基础介绍
09-24
MySQL 基础介绍 MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它使用 SQL（Structured Query Language）作为其操作语言。MySQL 具有高性能、可靠性和易于使用的特点，支持大量的并发用户连接，可以处理...
MySQL基础PDF文件
12-15
MySQL基础PDF文件涵盖了关于MySQL数据库的入门知识，包括数据库的概念、安装配置、基本语法以及SQL语句的操作。MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），它以其免费、功能强大和高效的特点，成为了...
MySQL基础1
08-04
总结起来，MySQL基础涵盖了从安装、管理到查询和操作数据库的全过程，是任何想要进行数据管理的开发者必须掌握的关键技能。通过学习和实践，我们可以有效地利用MySQL存储、检索和管理数据，提高工作效率。
flyway数据库版本控制
dark159735的博客
01-27 5479
一.Flyway简介 Flyway是一款数据库迁移（migration）工具。简单点说，就是在你部署应用的时候，帮你执行数据库脚本的工具。Flyway支持SQL和Java两种类型的脚本，你可以将脚本打包到应用程序中，在应用程序启动时，由Flyway来管理这些脚本的执行，这些脚本被Flyway称之为migration。 二.使用 一.导入依赖 <!-- flyway--> <dependency> <groupId>org.flywaydb</groupId&g
媒介（中介）设计模式
dark159735的博客
10-04 715
假设一家公司没有总经理，财务部，市场部，研发部三个部门可以直接联系，这样的关系会非常的乱，市场部直接找财务部发工资，拨款，市场部项目直接交个研发部开发，研发部直接找财务部拨款。这样会非常乱，这就需要一个中介协调三者关系。 中介设计模式：处理多对多网状关系，使之成为一个星型关系。 具体代码演示： 1.中介接口： public interface Mediator { void register(String dname,Department department); void..
Mysql接口API相关函数详细使用说明——mysql_init,mysql_real_connect,mysql_query,mysql_close等相关
木牛的博客
04-11 2854
初始化检索，以便于后面一行一行的读取结果集，而它本身并没有从服务器读取任何数据，这种方式较之第一种速度更快且所需内存更少，但它会绑定服务器，阻止其他线程更新任何表，而且必须重复执行。读取数据，直至返回NULL，否则未读取的行会在下一次查询时作为结果的一部分返回，故经常我们使用mysql_store_result。如果mysql_init()分配了新的对象，应当在程序中调用mysql_close() 来关闭连接，以释放对象。返回的数据称为“数据集”，在C的API里对应的就是。这个结构代表返回行的一个查询的。
【MySQL】——常用接口API即相关函数说明
sjp11的博客
08-03 2545
你必须使用mysql_real_query()而不是mysql_query()，因为二进制代码数据可能包含“\0”字符，而且，mysql_real_query()()将mysql_query()查询的全部结果读取到客户端，分配1个MYSQL_RES结构（上面有介绍），并将结果置于该结构中。读取数据，直至返回NULL，否则未读取的行会在下一次查询时作为结果的一部分返回，故经常我们使用。一旦完成了对结果集的操作，必须调用mysql_free_result()初始化检索，以便于后面一行一行的读取结果集，而它。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

愛沢かりん

感谢您对我的支持

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值