-- DML(数据操作语言)
-- 用于操作数据库对象中所包含的数据
-- 包括
-- INSERT INTO 表名(列名1,列名2,列名3,列名4...)VALUES(值1,值2,值3,值4.....) ( 添加数据语句 )
-- UPDATE 表名 SET 列名1=值1,列名2=值2.... WHERE 条件 ( 更新数据语句 )
-- DELETE FROM 表名 WHERE 条件 ( 删除数据语句 )
-- 添加语句
INSERT INTO grade(gradeName)VALUES('大四');
INSERT
INTO student(studentNo,loginPwd,studentName,sex,gradeId,phone,address,bornDate,email)
VALUES(30016,'123456','测试名称','男',14,'15311111111','学生宿舍','2020-01-01','15.....@163.com')
UPDATE student SET studentName='学生姓名',sex='女',identityCard='111111111111111111' WHERE studentNo = 30016
UPDATE student SET email = '10004@QQ.com' ,loginPwd = '000000' WHERE studentNo = 10004
-- SELECT 列名1,列名2,列名3,列名4...... *(所有列) FROM 表名 WHERE 条件
-- WHERE 列名 BETWEEN 小数值 AND 大数值 区间条件
SELECT * FROM result WHERE studentResult BETWEEN 70 AND 100
SELECT * FROM student WHERE bornDate BETWEEN '1994-08-08' AND '2000-01-01'
-- 查询 年级 为4 并且 性别为女 出生时间在 '1994-08-08' 到 '2000-01-01'
-- AND 并且
SELECT * FROM student WHERE gradeId = 4 AND sex= '女' AND bornDate BETWEEN '1994-08-08' AND '2000-01-01'
-- 查询 地址在学生宿舍 或者 在 北京市海淀区
-- OR 或者
SELECT * FROM student WHERE address = '学生宿舍' OR address = '北京市海淀区'
-- 将数据表subject中ClassHour大于110且GradeID为1的课时都减少10
UPDATE `subject` SET classHour = classHour-10 WHERE classHour < 110 AND gradeId = 6
SELECT * FROM grade
-- 年级表 和 学生表 有主外键关系 (学生表中的年级编号数据必须来自于 年级表中的年级编号)
-- 要删除主键表中的数据 就先删除子表(外键表)中的相关数据
DELETE FROM grade WHERE gradeId = 4
-- TRUNCATE 命令
-- 用于完全清空表数据,但表结构、索引、约束等不变
-- 区别于DELETE命令
-- 相同
-- 都能删除数据、不删除表结构,但TRUNCATE 速度更快
-- 不同
-- 使用TRUNCATE TABLE重新设置 AUTO_INCREMENT 计数器
-- 使用TRUNCATE TABLE不会对事务有影响
TRUNCATE newgrade_test;
DQL(数据查询语言)
-- SELECT [ALL | DISTINCT]
-- { * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
-- FROM table_name [ as table_ alias ]
-- [ left|out|inner join table_name2 ] #联合查询
-- [ WHERE … ] #指定结果需满足的条件
-- [ GROUP BY …] #指定结果按照哪几个字段来分组
-- [ HAVING …] #过滤分组的记录必须满足的次要条件
-- [ ORDER BY… ] #指定查询记录按一个或者多个条件排序
-- [ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条
SELECT * FROM student;
SELECT studentNo,studentName,sex
FROM student;
-- AS 别名
SELECT stu.studentNo,
stu.studentName,
stu.gradeId AS 'stuGradeID',
grade.gradeID AS gradeID,
grade.gradeName 'grade Name'
FROM student stu,grade
WHERE grade.gradeID = stu.gradeId
-- 查询 年级下的学生
SELECT grade.gradeName,
student.studentNo,
student.studentName
FROM grade,student
WHERE grade.gradeID = student.gradeId
SELECT grade.gradeName,
student.studentNo,
student.studentName
FROM grade
INNER JOIN student -- 内连接查询
ON grade.gradeID = student.gradeId -- 描述两表之间的关系列
SELECT grade.gradeName,
student.studentNo,
student.studentName
FROM grade LEFT JOIN student -- 左外连接查询 -- 以左表为基础 查询 右表 如果查询不到数据 返回NULL
ON grade.gradeID = student.gradeId -- 描述两表之间的关系列
SELECT grade.gradeName,
student.studentNo,
student.studentName
FROM student RIGHT JOIN grade -- 右外连接查询 -- 以右表为基础 查询 左表 如果查询不到数据 返回NULL
ON grade.gradeID = student.gradeId -- 描述两表之间的关系列
-- 查询 学生邮箱为NULL的学生
SELECT * FROM student WHERE email IS NULL
SELECT * FROM student WHERE email IS NOT NULL
-- 取出重复值 去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
SELECT DISTINCT studentName FROM student
SELECT subjectNo,
subjectName,
classHour/10 '天 數',
gradeId
FROM `subject`
-- 并集 交集 差集
CREATE TABLE newGrade
AS
SELECT * FROM grade
-- 并集 不去除重复值
SELECT gradeName FROM grade
UNION ALL
SELECT gradeName FROM newGrade
-- 并集 去除重复值
SELECT gradeName FROM grade
UNION
SELECT gradeName FROM newGrade
-- 交集 两表中都存在的数据
SELECT
newgrade.gradeName as newgrade,
grade.gradeName as grade
FROM
grade
JOIN newgrade
USING(gradeName)
-- 交叉连接
SELECT *
FROM grade cross JOIN newGrade;
SELECT * FROM grade,newGrade
-- 差集
SELECT *
FROM grade
LEFT JOIN newgrade
on newgrade.gradeName = grade.gradeName
where newgrade.gradeName is null
UNION --
SELECT *
FROM newgrade
LEFT JOIN grade
on newgrade.gradeName = grade.gradeName
where grade.gradeName is null
-- like : 模糊查询 通配符: _ :一个字符 % :任意长度的字符
SELECT * FROM student WHERE studentName LIKE '%李%'
-- IN进行范围查询
SELECT * FROM student WHERE studentNo in (10000,10001,10002,20000)
-- 子查询
SELECT * FROM student WHERE studentNo in (SELECT studentNo FROM result WHERE studentresult >60)
-- 排序
-- SELECT * FROM result ORDER BY studentresult DESC(大-小) ASC(默认值 小-大)
SELECT * FROM result ORDER BY examdate,studentResult DESC
SELECT result.* ,
student.studentName
FROM result
JOIN student
on student.studentNo = result.studentNo
ORDER BY examdate,studentResult DESC
SELECT result.*,
(SELECT studentName FROM student WHERE studentNo = result.studentNo ) as 学生姓名
FROM result
ORDER BY examdate,studentResult DESC
SELECT result.* ,
student.studentName
FROM result,student
where result.studentNo = student.studentNo
ORDER BY result.examdate,result.studentResult DESC