MySql数据库--第三章：高级查询（一）

a129_d0226
CREATE TABLE demo01(
	id INT(4)PRIMARY KEY NOT NULL,
	NAME VARCHAR(10)
);

#修改表名：alter table 旧表名 rename 新表名

ALTER TABLE demo01 RENAME demo02;

#添加字段：alter table 表名 add 字段 类型 属性

ALTER TABLE demo02 ADD PASSWORD VARCHAR(10) NOT NULL;

#查看表结构

DESC demo02;

#修改字段: alter table 表名 change 旧字段 新字段 类型 属性；

ALTER TABLE demo02 CHANGE NAME username VARCHAR(10) NOT NULL;

#删除字段：alter table 表名 drop 字段;

ALTER TABLE demo02 DROP address;

myschool

添加主外键

#添加主键约束：alter table 表名 add constraint 主键名 primary key 表名(字段);

ALTER TABLE grade ADD CONSTRAINT pk_GradeID PRIMARY KEY grade(GradeId);

#添加外键约束
#如何将student表的gradeId字段和grade表的gradeId字段建立外键关联
#alter table 表名 add constraint 外键名 foreign key(外键字段) references 关联表名(关联字段);

ALTER TABLE student ADD CONSTRAINT fk_gradeId FOREIGN KEY(gradeId) REFERENCES grade(gradeId);

#查询默认存储引擎

SHOW VARIABLES LIKE 'storage_engine%';

插入数据记录

#学生表中添加一条记录（插入单行数据）: insert into 表名(字段列表) values(值列表)
#注意：值列表每个值与字段列表与字段列表一一对应

INSERT INTO student(loginPwd,studentName,sex,gradeId,phone,address,bornDate,email,identityCard)
	VALUES('126','刘飞','男',1,'12334243464','武汉市汉口','1997-04-16','liufei@qq.con.com','31444');

#年级表中添加多条记录（插入多行数据）

INSERT INTO grade(gradeName) VALUES('A129'),('131');

ALTER TABLE SUBJECT ADD CONSTRAINT sub_gradeId FOREIGN KEY(gradeID) REFERENCES grade(gradeId);

#向科目表中添加多条信息 年级必须在于年级表中

INSERT INTO SUBJECT (subjectName,classHour,gradeId) VALUES('Lgoic java',200,1),('java面向对象',256,2),('ssm框架',128,3);

#将查询的数据存放到新表中:
#第一种：事先创建表 INSERT INTO 新表(字段1，字段2，…) SELECT 字段1，字段2，…FROM 原表;

 INSERT INTO stuaddress(stuName,StuAddress) SELECT studentName,address FROM student

#第二种：表临时创建 create table 新表(select 字段1，字段2，。。。 from 原表)

 CREATE TABLE stuAddressTemp(SELECT studentName,address FROM student)

#查询结果排序 查询 年级编号gradeId为1的所有学生信息，结果按学号升序排列

 SELECT * FROM student
 WHERE gradeid=1
 ORDER BY studentNo ASC

LIMIT子句

#使用limit限制展示结果集（应用：分页）

  语法
	SELECT <字段名列表>
	FROM   <表名或视图>
	[WHERE <查询条件>]
	[GROUP BY <分组的字段名>]
	[ORDER BY <排序的列名>[ASC 或 DESC]]
	[LIMIT[位置偏移量，]行数];

#查询结果排序 查询 年级编号gradeId为1的所有学生信息，结果按学号升序排列
#需求1 ：限制展示前3条记录 limit 3 默认从第一行记录开始 全称limit 0,3 0表示起始行记录（下标从0开始） 3表示显示记录数

 SELECT * FROM student
 WHERE gradeid=1
 ORDER BY studentNo ASC
 LIMIT 3;

#需求2： 限制展示 从第n条记录开始,展示3条记录 limit n-1, 3

 SELECT * FROM student
 WHERE gradeid=1
 ORDER BY studentNo ASC
 LIMIT 1,3;

#聚合函数

#1.获取科目平均学时

 SELECT AVG(classHour) AS 平均课时
 FROM SUBJECT
 

#2.返回课时150以上的科目数量

 SELECT COUNT(*) AS 课时150以上的科目数量
 FROM SUBJECT
 WHERE classHour>=150

#3. Max() 返回课时最高的科目课时

 SELECT MAX(classHour) AS 课时最多科目
 FROM SUBJECT

#4.MIN() 返回课时最低的科目课时

 SELECT MIN(classHour) AS 课时最少科目
 FROM SUBJECT
 

#5.SUM() 汇总所有科目的课时总数

 SELECT SUM(classHour) AS 课时总数
 FROM SUBJECT

字符串拼接 CONCAT()

#1.字符串拼接 CONCAT()

SELECT CONCAT('中华','人民','共和国') AS 字符串拼接测试

#2.在现有字符串中替换部分字符串 INSERT(字符串，m，n，字符串2)
#字符串1表示操作的字符串，
m表示删除的起始位置，从MySql指令中字符串中的字符下标从1开始
n表示删除的字符个数，字符串2表示新增替换后的字符串，位置从m开始

SELECT INSERT('中华人民共和国',3,2,'的') AS 部分字符串替换测试

#3.截取字符串 SUBSTRING(字符串,m,n)
#m 截取的起始位置 n 截取的个数

SELECT SUBSTRING('中华人民共和国',5,3) AS 字符串截取测试

#4.其他字符串函数 upper()转换成大写 LOWER()转换成小写

SELECT UPPER('Add') AS 全部转换大写;
SELECT LOWER('Add') AS 全部转换小写；

时间日期函数

#获取 当前日期 2021-03-02 CUR（当前的）

SELECT CURDATE() AS 当前日期;

#获取 当前时间 14:48:28

SELECT CURTIME() AS 当前时间;

#获取 日期和时间 2021-03-02 14:48:07

SELECT NOW() AS 日期和时间;

#获取日期所在的周数（第几周）

SELECT WEEK(NOW()) AS 周数;

#获取日期中的年份

SELECT YEAR ('2019-01-01') AS 那一年;

#获取时间中的小时

SELECT HOUR(NOW()) AS 小时;

#获取时间中的分钟

SELECT MINUTE(NOW()) AS 分钟;

#获取两个日期相差的天数 DATEDIFF(date1,date2) date1减date2
#回顾 SQL SERVER 中对应的语法 select DateDiff(DD,date1,date2) DD这里表示天数，MM表示月份,yy表示年

SELECT DATEDIFF(NOW(),'1999-03-16') AS 两个日期相差的天数;

#date加上n天后的日期 ADDDATE(date,n);

SELECT ADDDATE(NOW(),5);

数学函数

#CEIL() 向上取整 SELECT CEIL(2.1) 结果是3

SELECT CEIL(2.1);

#FOOL() 向下取整 SELECT FLOOR(2.6) 结果是2

SELECT FLOOR(2.6);

#Rand() 取0-1之间的随机数

SELECT RAND() AS '0-1之间的随机数';

例题

#向科目表添加三条信息，用一条insert语句

INSERT INTO SUBJECT(subjectName,classHour,gradeId) VALUES('HTML',160,1),('java OOP',230,1);

#将学生表中学号为2的学生的邮箱修改为zhangsan2@163.com,密码改为000

UPDATE student SET email='zhangsan2@163.com',loginpwd='000' WHERE studentNo=2;

#将科目表中课时数大于200且年级编号为1的科目的课时减少10（修改 update）

UPDATE SUBJECT SET classHour=classHour-10
WHERE classHour>200 AND GradeId=1

#将所有的年级编号为1的学员姓名，性别，出生日期，手机号码信息保存到新表student_grade1中
#第一种方法：student_grade1表格事先创建好 insert into 新表（字段。。。） select 字段 from 原表

INSERT INTO student_grade1(stuName,Sex,Birthday,PhoneNo)SELECT studentName,Sex,bornDate,Phone FROM student;

#第二种方法：student_grade2临时创建 create table 新表（select 字段 from 原表）

CREATE TABLE student_grade2(SELECT studentName,Sex,bornDate,Phone FROM student);

子查询

#查看年龄比“李四”大的学生，要求显示这些学生的信息
#获取李四的出生日期

SELECT bornDate FROM student WHERE studentName='李四';

#实现
#第一种方法

SELECT studentName AS 姓名,sex AS 性别,bornDate AS 出生日期,phone AS 手机号
FROM student
WHERE bornDate > (SELECT bornDate FROM student WHERE studentName='李四');

#第二种方法 使用 DateDiff(date1,date2) 返回的时间差去比较

SELECT studentName AS 姓名,sex AS 性别,bornDate AS 出生日期,phone AS 手机号
FROM student
WHERE DATEDIFF(bornDate,(SELECT bornDate FROM student WHERE studentName='李四'))<0;

#查询"Logic Java"课程至少一次考试刚好等于60分的学生
#–第一种方法 表连接

SELECT stu.studentName,r.studentResult,s.subjectName FROM student AS stu
INNER JOIN result AS r ON r.studentNo=stu.studentNo
INNER JOIN SUBJECT AS s ON s.subjectNO=r.subjectNO
WHERE s.subjectName='Logic Java' AND r.studentResult=60

#–第二种方法 子查询 注意子查询的时候条件限定 最好用IN替换

SELECT studentName FROM student AS stu WHERE stu.studentNo IN (
	SELECT r.studentNo FROM result AS r
	INNER JOIN SUBJECT AS s ON s.subjectNO=r.subjectNo
	WHERE r.studentResult=60 AND s.subjectName='Logic Java'	
);

注释：子查询与表连接的区别
1.子查询比较灵活，方便，常作为增删改查的筛选条件，适合于操纵一个表的数据
2.表连接更适合于查看多表的数据