SQL DML 和 DDL
可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
查询和更新指令构成了 SQL 的 DML 部分：
- SELECT - 从数据库表中获取数据
- UPDATE - 更新数据库表中的数据
- DELETE - 从数据库表中删除数据
- INSERT INTO - 向数据库表中插入数据
SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
SQL 中最重要的 DDL 语句:
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更（改变）数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引（搜索键）
- DROP INDEX - 删除索引
--20180110简单SQL脚本学习-- 相关关键字： CREATE（创建） COMMENT ON（添加注释） COLUMN（列） FLASHBACK（回退） GROUP BY（分组） ORDER BY（排序） MODIFY（修改） --一、表的相关操作脚本 --1.创建数据表 --1.1常规创建表格 CREATE TABLE STU_INFO( STU_ID VARCHAR(15) NOT NULL, STU_NAME VARCHAR(120), STU_CLASS VARCHAR(120), STU_SEX VARCHAR(10) ); --1.2通过复制的方式创建表 CREATE TABLE STU_INFO_TEMP AS SELECT * FROM STU_INFO; --1.3通过复制的方式但只是复制表结构不复制表的数据 CREATE TABLE STU_INFO_TEMP AS SELECT * FROM STU_INFO WHERE 1=2; --2.给表和字段添加注释 COMMENT ON TABLE STU_INFO IS '2018年1月10号SOULSJIE学习SQL脚本创建的数据表 '; COMMENT ON COLUMN STU_INFO.STU_ID IS '学生学号'; COMMENT ON COLUMN STU_INFO.STU_NAME IS '学生的姓名'; COMMENT ON COLUMN STU_INFO.STU_CLASS IS '学生所属班级'; COMMENT ON COLUMN STU_INFO.STU_SEX IS '学生的性别'; --3.修改表结构 --2.1添加表字段 ALTER TABLE STU_INFO ADD STU_ADD VARCHAR(120); --2.2修改表字段 ALTER TABLE STU_INFO MODIFY (STU_SEX VARCHAR(6)); --2.3删除表字段 ALTER TABLE STU_INFO DROP (STU_ADD); --2.4修改表名 ALTER TABLE TESTS RENAME TO STU_INFO; --2.5修改列名 ALTER TABLE STU_INFO RENAME COLUMN STU_CLASS TO STU_BANJI; --4.删除数据表 DROP TABLE STU_INFO_TEMP; --5.恢复被DROP的数据表 FLASHBACK TABLE STU_INFO TO BEFORE DROP; --6.表的截断 关键字 truncate 清空表中的数据，并且还原数据占用的内存空间 TRUNCATE TABLE STU_INFO; --7.表的清空 关键字 delete 清空表中的数据，但是不还原数据占用的内存空间 DELETE FROM STU_INFO; --二.数据操作脚本 --1.1插入单条数据 --MySQL的写法和Oracle写法一样 INSERT INTO STU_INFO (STU_ID, STU_NAME, STU_CLASS, STU_SEX) VALUES ('Q', 'Q', 'Q', 'Q'); --1.2插入多条记录数据 --MySQL的写法INSERT INTO TABLE_NAME (COLUMN_1，COLUMN_2...,COLUMN_N) VALUES(VALUE_1，VALUES_2,...,VALUES_N),(VALUES2_1，VALUES2_2...,VALUES2_N); --例子：在mysql中插入多条数据INSERT INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS) VALUES ('TE','TE','15'),('TT','TT','16'); --但是在Oracle中插入多条数据写法有不同如下： INSERT ALL INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180301','张三','3','男') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180302','李四','3','女') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180303','张嘉佳','3','男') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180304','李丽丽','3','女') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180305','汪汪汪','3','男') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180306','呵呵哈','3','女') SELECT 1 FROM DUAL; --2.删除数据 DELETE FROM STU_INFO WHERE STU_ID='02'; DELETE FROM STU_INFO WHERE 1=1; --3.修改数据 UPDATE STU_INFO SET STU_NAME='JIE',STU_SEX='女' WHERE STU_ID='03'; --4.简单查询数据 --4.1查询所有数据 SELECT * FROM STU_INFO; --4.2多条件查询 and关键字 逻辑与的关系 SELECT * FROM STU_INFO WHERE STU_CLASS='3' AND STU_SEX='男'; --4.3 or关键字 逻辑或的关系 SELECT * FROM STU_INFO WHERE STU_CLASS='1' OR STU_CLASS='2'; --4.4 group by 分组 注意：在同时使用order by和group by时order by应该放在末尾 SELECT STU_ID FROM STU_INFO GROUP BY STU_ID ORDER BY STU_ID ASC; --4.5 order by 排序 不写降序（desc）和升序(asc)时默认为升序 SELECT * FROM STU_INFO ORDER BY STU_CLASS DESC --4.6 distinct 去重复 SELECT DISTINCT STU_ID FROM STU_SCORE; --5.复杂数据查询 利用group by 进行统计查询 --5.1 AVG 求各个课程的平均值 SELECT S.KECHENG_BIANHAO 课程编号, AVG(S.KECHENG_CHENGJI) 平均分 FROM STU_SCORE S GROUP BY S.KECHENG_BIANHAO; --5.2 min 求各个课程的最小值 SELECT S.KECHENG_BIANHAO 课程编号, MIN(S.KECHENG_CHENGJI) 最小值 FROM STU_SCORE S GROUP BY S.KECHENG_BIANHAO; --5.3 max 求各个课程的最大值 SELECT S.KECHENG_BIANHAO 课程编号, SUM(S.KECHENG_CHENGJI) 最小值 FROM STU_SCORE S GROUP BY S.KECHENG_BIANHAO; --5.4 sum 求各科课程的总分 SELECT S.KECHENG_BIANHAO 课程编号, SUM(S.KECHENG_CHENGJI) 最小值 FROM STU_SCORE S GROUP BY S.KECHENG_BIANHAO; --5.5 count 总计数目 SELECT S.KECHENG_BIANHAO 课程编号, COUNT(*) 记录条数 FROM STU_SCORE S GROUP BY S.KECHENG_BIANHAO; --5.6 连接查询 查询每个学生的总分 SELECT I.STU_NAME 姓名, SUM(S.KECHENG_CHENGJI) 总分 FROM STU_SCORE S JOIN KECHENG_INFO K ON S.KECHENG_BIANHAO = K.KECHENG_BIANHAO JOIN STU_INFO I ON S.STU_ID = I.STU_ID GROUP BY I.STU_NAME; --5.7求C语言课程的最低分 SELECT MIN(S.KECHENG_CHENGJI) 最低分 FROM KECHENG_INFO K JOIN STU_SCORE S ON K.KECHENG_BIANHAO = S.KECHENG_BIANHAO WHERE K.KECHENG_NAME = 'C语言'; SELECT * FROM STU_SCORE; SELECT * FROM KECHENG_INFO; --5.8左连接 LEFT JOIN 满足on之后的条件的数据才会出现在结果集中 SELECT * FROM STU_SCORE S LEFT JOIN KECHENG_INFO K ON S.KECHENG_BIANHAO = K.KECHENG_BIANHAO; --5.9右连接 RIGHT JOIN 不满足on之后的条件的数据也会出现在结果集中 SELECT * FROM STU_SCORE S RIGHT JOIN KECHENG_INFO K ON S.KECHENG_BIANHAO = K.KECHENG_BIANHAO; --5.10 全连接 FULL JOIN 是左连接和右链接两个结果的并集 SELECT * FROM STU_SCORE S FULL JOIN KECHENG_INFO K ON S.KECHENG_BIANHAO = K.KECHENG_BIANHAO; -------------------------临时脚本----------------------------------- --例子中用到表的创建、添加数据的SQL脚本 --- -------------------------------创建学生信息表 CREATE TABLE STU_INFO( STU_ID VARCHAR(15) NOT NULL, STU_NAME VARCHAR(120), STU_CLASS VARCHAR(120), STU_SEX VARCHAR(10) ); COMMENT ON TABLE STU_INFO IS '2018年1月10号SOULSJIE学习SQL脚本创建的数据表 '; COMMENT ON COLUMN STU_INFO.STU_ID IS '学生学号'; COMMENT ON COLUMN STU_INFO.STU_NAME IS '学生的姓名'; COMMENT ON COLUMN STU_INFO.STU_CLASS IS '学生所属班级'; COMMENT ON COLUMN STU_INFO.STU_SEX IS '学生的性别'; --向学生信息表中添加数据 INSERT ALL INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180301','张三','3','男') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180302','李四','3','女') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180303','张嘉佳','3','男') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180304','李丽丽','3','女') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180305','汪汪汪','3','男') INTO STU_INFO (STU_ID,STU_NAME,STU_CLASS,STU_SEX) VALUES ('180306','呵呵哈','3','女') SELECT 1 FROM DUAL; -------------------------------创建辅助数据表--成绩表 CREATE TABLE STU_SCORE( STU_ID VARCHAR(12) NOT NULL, KECHENG_BIANHAO VARCHAR(50), KECHENG_CHENGJI VARCHAR(50) ); COMMENT ON TABLE STU_SCORE IS 'soulsjie，学习连接查询创建的学生成绩表'; COMMENT ON COLUMN STU_SCORE.STU_ID IS'学生学号'; COMMENT ON COLUMN STU_SCORE.KECHENG_BIANHAO IS'课程编号'; COMMENT ON COLUMN STU_SCORE.KECHENG_CHENGJI IS'课程成绩'; --向成绩表中添加数据 INSERT ALL INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180301','0001','60') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180301','0002','62') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180302','0001','99') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180302','0002','99') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180303','0001','45') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180303','0002','15') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180304','0001','44') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180304','0002','55') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180305','0001','66') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180305','0002','55') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180306','0001','66') INTO STU_SCORE (STU_ID,KECHENG_BIANHAO,KECHENG_CHENGJI) VALUES ('180306','0002','88') SELECT 1 FROM DUAL; --给成绩表添加注释 COMMENT ON TABLE STU_SCORE IS 'soulsjie,学习连接查询创建的学生成绩表'; COMMENT ON COLUMN STU_SCORE.STU_ID IS '学生学号'; COMMENT ON COLUMN STU_SCORE.KECHENG_BIANHAO IS '课程的编号'; COMMENT ON COLUMN STU_SCORE.KECHENG_CHENGJI IS '课程的成绩'; -------------------------------创建辅助数据表--选课表 CREATE TABLE KECHENG_INFO ( KECHENG_BIANHAO VARCHAR(12) NOT NULL, KECHENG_NAME VARCHAR(50) ); COMMENT ON TABLE KECHENG_INFO IS 'soulsjie，学习连接查询创建的课程信息表'; COMMENT ON COLUMN KECHENG_INFO.KECHENG_BIANHAO IS '课程的编号'; COMMENT ON COLUMN KECHENG_INFO.KECHENG_NAME IS '课程名'; --添加选课表中的数据 INSERT ALL INTO KECHENG_INFO (KECHENG_BIANHAO,KECHENG_NAME) VALUES ('0001','C语言') INTO KECHENG_INFO (KECHENG_BIANHAO,KECHENG_NAME) VALUES ('0002','JAVA') SELECT 1 FROM DUAL;