mysql多表 子查询_mysql多表子查询

SELECT * FROM stu;

#添加多行数据

INSERT INTO stu VALUES(1,'aaa'),(2,'ccc'),(3,'小明');

DROP TABLE stu_temp;

CREATE TABLE stu_temp

(

)

SELECT * FROM stu_temp;

#1:stu_temp表 不能存在的

#2:一边取数据一边创建数据表

#3:stu_temp 约束(主键)是从stu中拿不出来的

CREATE TABLE stu_temp(SELECT * FROM stu);

SELECT * FROM info;

#4：只需要某些数据表的基本结构，不需要数据！

# 查询过程中 where条件不成立，查询的就是结构，否则就是数据+结构

CREATE TABLE info_temp(SELECT * FROM info WHERE 1=2);

SELECT * FROM info WHERE 1=1;

SELECT * FROM info_temp;

CREATE TABLE info_temp3(SELECT id,age FROM info);

SELECT * FROM info_temp3;

DELETE FROM info_temp3;

SELECT * FROM info;

#通过age排序 1：升序 2：降序

SELECT * FROM info ORDER BY age ASC;

SELECT * FROM info ORDER BY age DESC;

SELECT * FROM info WHERE age>18 ORDER BY age DESC;

SELECT t.`id` AS 编号,t.`name` 姓名,t.`age` 年龄 FROM info t;

# 只要出现+，会发生两个字段之间的运算

SELECT t.id+'.'+t.`age` AS 连接后 FROM info t;

SELECT * FROM borrow WHERE returndate IS NULL;

#not : 不是的

SELECT * FROM borrow WHERE returndate IS NOT NULL;

#聚合函数

SELECT * FROM score;

#最高分 max()

SELECT MAX(t.`sexam`) AS 最高分 FROM score t;

#最低分 min()

SELECT MIN(t.`sexam`) AS 最低分 FROM score t;

#平均分 avg()

SELECT AVG(t.`sexam`) AS 平均分 FROM score t;

#总行数

SELECT COUNT(*) AS 行数 FROM score;

#总和

SELECT SUM(t.`sexam`) AS 总分成绩 FROM score t;

#字符串函数

#1：拼接字符串函数

SELECT CONCAT('jack','and','rose','一起吃个饭！') AS 字符串;

SELECT * FROM student;

#数据表中，是不区分大小写

SELECT CONCAT(t.`StudentName`,t.`LoginPwd`,t.`Address`) 字符串 FROM student t;

SELECT * FROM student;

#select 都是虚拟结果，不会对原始数据表产生影响

#2:insert函数 替换

SELECT INSERT(t.`Address`,2,3,'abc') AS 替换 FROM student t;

#3：大小写转换

SELECT UPPER(LOWER(UPPER(t.`Email`))) 转换 FROM student t;

#4:截取字符串

SELECT SUBSTRING('abcefghgk',2);

SELECT SUBSTRING('abcefghgk',2,3);

#日期函数

#1:当前日期

SELECT CURDATE();

#2:当前时间

SELECT CURTIME();

#3:当前日期和时间

SELECT NOW();

#4:一年中的第几周

SELECT WEEK(NOW());

#5:年份

SELECT YEAR(NOW());

#6:月份

SELECT MONTH(NOW());

SELECT * FROM student;

SELECT YEAR(t.`BornDate`) AS 年份,MONTH(t.`BornDate`) AS 月份 FROM student t;

SELECT DATEDIFF(NOW(),'2002-02-02');

#数据表中计算年龄

SELECT DATEDIFF(NOW(),a.`BornDate`)/365 AS 生日 FROM student a;

SELECT FLOOR(DATEDIFF(NOW(),a.`BornDate`)/365) AS 生日 FROM student a;

#8:计算日期增量

SELECT ADDDATE(NOW(),50000);

SELECT ADDDATE(NOW(),500000);

#数学函数

#向上取整

SELECT CEIL(RAND()*10);

#向下取整

SELECT FLOOR(RAND()*10);

#limit:分页

SELECT * FROM student;

/*

*/

#显示第1页

SELECT * FROM student LIMIT 0,3;

#显示第2页？(当前页-1)*每页显示条数

SELECT * FROM student LIMIT 3,3;

SELECT * FROM student LIMIT 6,3;

#查询比“谢挺疯”年龄大的学生信息

#用一个sql查询出来，需要子查询

#子查询执行的原理：

#先执行子语句，执行完成后，把查询的结果给父查询

#从上往下写，先要结果，后要过程

SELECT * FROM student t WHERE t.`BornDate`

);

SELECT * FROM student;

SELECT * FROM score;

SELECT * FROM SUBJECT;

/*

查询参加最近一次Logic Java考试成绩的学生的最高分和最低分

1:最近

2:Logic Java

*/

SELECT MAX(sexam) 最高分,MIN(sexam) 最低分 FROM score WHERE subjectno=(

AND sdate =(

);

#优化后：数据库性能优化

SELECT MAX(sexam) 最高分,MIN(sexam) 最低分 FROM score WHERE sdate =(

);

SELECT * FROM student;

SELECT * FROM score;

SELECT * FROM SUBJECT;

#查询“Logic Java”课程考试成绩为60分的学生名单

#in多个结果返回给父级查询

SELECT * FROM student WHERE studentno IN (

)

#not:取反

SELECT * FROM student WHERE studentno NOT IN (

)

#查询参加“Logic Java”课程最近一次考试的在读学生名单

SELECT * FROM student WHERE studentno IN(

)

SELECT * FROM student WHERE studentno NOT IN(

)