/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/--第一部分
/* 先说一下，关于数据库的关系操作，主要的有如下
         selection           ：   σ          ，  σp（E1） ，  p是E1属性上的谓词
cartesian production  :     χ
           union              :     ∪         ，  E1 ∪  E2
           project             :     π          ， πS（E1）  ， S是E1中某些属性的列表
    set difference         :      -          ,    参数数目一致
    set intersection      :     ∩
          rename            :     ρ           ，  ρx（E1）   ， x 是E1结果的新名字
         nature join        :     ⋈           ，
         division             :     ÷          ,     善用除法，就是把与被除数相同的摘出来
       assignment        :     <-          , temp1 <- R χ S    ,   result =  πR∪S(temp1)
           except            :     -
 
还有聚集函数 ζ ，  G1,G2,G3.... ζ F1(A1),F2(A2)......(E） 聚集运算
department_nameζsum(salary)(instructor)    返回一组元组的集合; 类似于group by
count-distinct ： distinct可以去掉重复值   ； average  求平均 等等 
*/
 /* -----------------------------------------------------------------------------------------------------------------------------------------------------------------*/--第二部分
/*  再来说一下，关于SQL的初中高级操作，以命令来一步步阐述,
　　数据库命令部分大小写，最后以分号作为结尾
*/
 我们这里主要先说明一下最最基本的操作，后续更新中高级以及常用的一些命令，以及一些Java+sql，或python+sql
 
首先登录MySql
1. MySql 在CMD命令行登录， mysql -u  user_name  -p  password
2. 选择数据库，    use database_name;
  
/*----------------------------------------------------------------------------------------------------------------------------*//*基础操作*/
/*基础操作*/ /*开始我们一些基础教程*/
/*以表A，表B为例
-----A------     ------B--------
-id--name--     -cname---grade-- 
-1---li----     -math-----4----
-2---bi----     -DB-------3----
-----------     ---------------
*/
/*1. SELECT， 相当于关系代数的映射*/
SELECT * /*选择所有，或者Column_name1,Column_name2*/  /*select distinct column_numa1 ， 不重复*/
FROM table_name1,table_name2... /*这里是笛卡尔积*/  /*也可以是自然连接 table_name1 nature join table_name2 */
WHERE id = '1' ;  /*当然这个是最最简单的一个查询，后边会逐渐复杂起来*/

/*2. CREATE，创建操作*/
/*先创建一个数据库*/
CREATE DATABASE [if not exists] db_name
[[DEFAULT] CHARACTER SET <charset_name>] /*指定数据库的字符集，目的是为了避免数据库存储中数据乱码，若不指定则为默认字符集*/
[[DEFAULT] collate <校对规则名>]; /*指定字符集的默认校对规则*/

/*字符集是用来定义mysql存储字符串的方式，校对规则定义了比较字符串的方式*/

/*创建一个表格*/
CREATE table [if not exists] table_name (
   column_name1 varchar(10),
   column_name2 numberic(12,2),
   column_name2 int,
   PRIMARY KEY (column_name1,column_name2), /*设置主键*/
   INDEX idex_name(column_name1,column_name2), /*设置索引*/
   FOREIGN KEY (column_name1) references tablename(column_name2) /*设置外键*/
)engine=InnoDB DEFAULT CHARSET=utf8; /*engine 设置储存引擎，charset设置编码*/

/*先说一下 if not exists , 如果不存在则创建*/
/*建表时还可使用temporary 关键字，那么所创建的临时表只会在当前会话中可见，会话结束后，该数据库就会自动删除*/
/*使用like 可以来基于其他表的定义来创建一个空表。  create table new_tbl like orig_tbl; */
/*再说一下外键和引擎
mysql 有两种常用的引擎类型，MyISAM 和 InnoDB(这个是默认的)。只有InnoDB类型支持外键约束，InnoDB中外键约束定义的语法如下：

[constraint [symbol]] foreign key [index_name] (index_col_name,...) 
references tbl_name (index_col_name,...) [on delete reference_option]  [on update reference_option]
reference_option 有 restrict（默认） | cascade | set null | no action |SET DEFAULT（InnoDB目前不支持）
1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。
2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。
3. NO ACTION: InnoDB拒绝删除或者更新父表。
4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

*/

/*3. as 子句，是rename 的作用,也可以作为连接语句*/
old_name as new_name /*as子句做rename的格式*/

   /*在作比较中很常见*/
select T.name, S.course id
from instructor as T, teaches as S
where T.ID= S.ID;

   /* 也出现在select里*/
select name as instructor_name, course_id /*这里是把select的name的原column_name，重新命名为instructor_name */
from instructor, teaches
where instructor.ID= teaches.ID;
/*我们再说一下，作为as作为连接语句的操作符的例子，常见的是用在create中*/
create table new_tbl AS (SELECT * FROM orig_tbl); 

/*4. INSERT into 插入数值*/
/*一般而言，我们使用 INSERT INTO 都是以下例子所示*/
insert into course (title, course id, credits, dept name)/*当然也可以按照原来的course元组顺序输入，直接 insert into course，这都无所谓*/
values 
('Database_ystems', 'CS-437', 4, 'Comp. Sci.'),
('Database_ystems', 'CS-437', 4, 'Comp. Sci.');
/*更为一般的，我们还有这种查询后，插入的例子*/
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_cred > 144;/*这个就不解释了，就是查询后插入，insert into 就这两种形式*/

/*5. DELETE,,用来删除元组*/
DELETE FROM r; /*常用格式*/

/*delete很简单，我们举个例子就过掉*/
delete from instructor 
where dept_name in (select dept_name from department where building = 'Watson');

/*6. UPDATE  更新，这个功能很强大，因为可以不增加元组的同时，修改值*/
/*通常的格式是 update...set....*/
update table_name1
set....;
/*举个简单的例子*/
update instructor
set salary = salary * 1.05
where salary < (select avg (salary) from instructor);

/*7. SET*/
/*set 用的时候，我们一般是用作 变量的赋值 */
/*举个例子*/
SET max_join_size=DEFAULT;

/*还有一种结构，是集合数据类型，用set定义，举个例子 column_name set(..值..) */
class_name set('music','movie','swimming','footbal')

/*8. case 结构， 两次更新操作 case when then else end*/
/*使用case，有以下两种格式*/
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END 
/*或者*/
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END 
/*这里我们分别举一下例子*/
/*通过case when then 更新数据*/
update hank set name = case id 
when 1 then 'D'
when 2 then 'E'
when 3 then 'F'
end 
where id in(1,2,3);
/*通过case when then else end 查询的例子*/
select case
when sum(credits) is not null then sum(credits)
else 0
end
/*最后补充一点，这里的case只不过是很简单很简单的用法，在后边的sql函数等等里，我们还会用到case，这里就先简单说一下*/

/*9. ALTER*/
/*alter的功能很强大，我们一点点来讲*/
alter table table_name engine = myisam; /*修改存储引擎：修改为myisam*/
alter table table_name drop FOREIGN key keyName;/*删除外键约束：keyName是外键别名*/
alter table tableName modify name1 type1 first|after name2; /*修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，
                                                                                 这应该显而易见，first放在第一位，after放在name2字段后面*/
/*alter还可以  修改字段类型及名称，  删除，添加或修改表字段，   修改字段默认值等， 我们这里举一个简单的例子*/
ALTER TABLE testalter_tbl  DROP i;
ALTER TABLE testalter_tbl ADD i INT; /*添加 i 列，并定义数据类型*/ 
/*以上这两个很常用*/

ALTER TABLE testalter_tbl modify c CHAR(10); /*修改数据类型*/
ALTER TABLE testalter_tbl CHANGE i j BIGINT; /*modify 是修改微小的操作，而change是 修改和原来幅度比较大的 */
/*使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。*/
/*一般可在 修改数据类型，修改字段位置  ---用modify
修改名字 --就用change
*/

/*10. DROP*/
DROP TABLE r; /*删除表*/
drop database database_name; /*删除数据库*/
/*这里，drop 其实没什么好说的，很简单，但是要区分几个概念 
drop delete 与 truncate 有什么取别呢，

DROP（dml）这个操作会放到rollback segement中，事务提交之后才生效;如果有相应的trigger, 执行的时候将被触发
用于删除表,数据库等.将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.

TRUNCATE和DELETE都是删除表数据不删除表的结构.是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。

速度上一般来说: drop> truncate > delete 
最后，要小心使用drop 和truncate,尤其没有备份的时候. 
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大. 
想删除表,当然用drop。想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete. 
*/

/*11. ADD*/
/*add 常见的情形还是与 alter add增加表的字段操作，下边举几个例子*/
alter table table_name add index index_name (字段名1[，字段名2 …]);/*增加索引*/
ALTER TABLE table_name ADD column_name1 column_type1; /*增加字段*/
alter table table_name add primary key (字段名);/*加主键*/

/*12. modify*/
/*modify是用来扩充alter table的功能的，上边看alter时已提过，这里不再过多区分 modify与 change的异同了*/
alter table student modify name varchar(20);

/*13. 字符操作 lower(S) upper(S)  trim(S)*/
lower(S) /*function lower 是转化字符串S 为小写*/
upper(S) /*function upper 是转化字符串S 为大写*/
trim(S)  /*function trim 是删除字符串S 末尾的空格*/
/* 另外，更为重要的有 

    % ：使用 % 来匹配任何子字符串
    _ : 使用 _ 来任何字符

  intro% ： 是说 匹配任何以'intro' 开头的字串
  %comp% : 是说 匹配 任何包含 'comp' 的字串
  _ _ _  : 是说匹配任何只有三个字符的字串
  _ _ _% : 是说匹配最少含有三个字符的字串

  这些常用来在where中做判断，可以使用like操作符对字符串执行模式匹配
*/  

/*14. LIKE*/
/*用上述来举个例子*/
select dept_name
from department
where building like '%Watson%';

/*15. escape*/
/*我们再来考虑转义字符，在sql里，允许自定义转义字符。*/
/*使用 escape 来自定义转义字符*/
like 'ab\%cd%' escape '\'  /*意思是 “ab%cd” */
like 'ab\\cd%' escape '\'  /*意思是 “ab\cd”*/ 

/*16. ORDER BY*/
/* ORDER BY 用来对关系中元组显示顺序进行控制 */
/* 有 desc 降序，   和  asc  升序 ，如： */
select *
from instructor
order by salary desc, name asc;

/*17. between and 就是限定where中比较范围的，没什么好说的*/
select name
from instructor
where salary between 90000 and 100000;
/*等价于*/
select name
from instructor
where salary <= 90000 and salary >= 100000;
/*当然还可以用 not between.. and  来取补*/

/*18. = >= <= <>*/
/*比较运算符可以在元组上使用，并且是字典序，如(a1, a2) <= (b1, b2)*/
/*有时候， where中的比较子句还可以使用更灵活的形式*/
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology') /*使用这种元组形式，使查询语句更简洁*/

/*19. set operation :union intersect except*/
/*  
   union 就是将两个查询结果返回到一个表里，自动消除重复
   intersect ，将两个查询结果取 ∩ 交 ，自动消除重复
   expect ， 将两个查询结果自动取 差 ， 自动效处重复。

   如果以上操作想保留重复值，即在关键词后加 all ,如 union all
*/

(select course id
from section
where semester = ’Fall’ and year= 2009)
union all
(select course id
from section
where semester = ’Spring’ and year= 2010);

/*20. NULL and or not*/
/*几个运算*/
and : true and unknown ->  UNKNOWN
      false and unknown -> FALSE
      unknown and unknow -> UNKNOWN
or  : true or unknown ->  TRUE
      false or unknown -> UNKNOWN
      unknown or unknow -> UNKNOWN
not : not unknown -> UNKNOWN 

/*举一个where中应用的例子， 用is null  ;   is not null  ;  is unknown  ; in not unknown 来约束*/
select name
from instructor
where salary is null;
/*最后说一点，null = null 返回 unknown*/

/*21. aggregate function: avg min max sum count some every distinct*/
/*这里就不再对求均值；求最小；求最大；求和；计数做详细讲解，没什么好说的 */
/*distinct 前边有说过， 这里聚集函数不再多说，都很简单*/
/*另外 some 和 every 可以应用于布尔值集合 */

/*22. GROUP BY*/
/*上边说了聚集函数，但是我们一般希望将他们应用在不一样的组里，
  所以需要分组后应用聚集函数，再返回一个表，而这就是 group by*/
select dept_name, count (distinct ID) as instr_count
from instructor natural join teaches
where semester = 'Spring' and year = 2010
group by dept_name;
/*在使用group by 的时候，我们需要注意以下，返回的元组个数与分组有关， 比如说我们再返回列表所有导师id就会出错*/
/*你知道id这个量很大，而返回的元组个数远远小于这个数，肯定不对*/
/*就是没有被聚合的select属性，必须出现在group by 子句中*/

/*23. having 子句*/
/*having 子句是用来与group by 配套的*/
/*他可将分组后的每一个组进行相应筛选，谓词判断。比如说哪个部门的平均工资最大，这类型的*/

/*最后，查询类型更完善了，， select...from...where...group by...having...*/
select dept_name, count (distinct ID) as instr_count
from instructor natural join teaches
where semester = 'Spring' and year = 2010
group by dept_name;
having count (ID) >= 2;

/*24. 集合成员 in , not in*/
/*选择在不在集合里边的成员*/
select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in 
(select course_id, sec_id, semester, year from teaches where teaches.ID= 10101);
/*也可以直接枚举*/
select distinct name
from instructor
where name not in ('Mozart', 'Einstein');

/*25. 集合比较 all, some, not exists, contains, not unique*/
/* >some ,  <= some , >= some , =some , <> some  指的是与后边集合中的任一个元素成立关系即可 */
/* <all ,  <= all  , >= all  ,  = all  , <> all  指的是与后边集合中所有的元素关系都成立的*/
/* 测试查询结果是不是空，用exists  ，如果参数子查询是非空，exists返回true ,, 而not exists 空则返回true*/
select course_id
from section as S
where semester = 'Fall' and year= 2009 and
   exists (select * from section as T where semester = ’Spring’ and year= 2010 and
      S.course id= T.course id);
/* 看是不是存在重复的元组 ，用 unique ，不再举例了，跟上边一样*/
select T.course_id
from course as T
where unique (select R.course_id
      from section as R where T.course id= R.course_id and
            R.year = 2009);
/*这个等价于*/
select T.course_id
from course as T
where 1 <= (select count(R.course_id)
      from section as R where T.course id= R.course_id and R.year = 2009);
/*contains 使用方法如下*/
SELECT * FROM table_name WHERE CONTAINS(column_name1,'search contents');
/*在search contents中 可以指定操作逻辑符  如 or, and, and not, near, *, isabout, formsof等  */
/*举几个例子， 这里的'nanjing*' 的星号不具例了很简单，就是匹配含有nanjinng这几个字符的词。  */
SELECT * FROM table_name where HERE CONTAINS( address, '"HEIBEI province" OR beijing' );/*这个不解释了很简单*/
SELECT * FROM table_name where HERE CONTAINS( address, 'nanjing NEAR road' );/*结果返回 南京西路，南京东路这样的*/
SELECT * FROM table_name where HERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' );/*带有权重的查询，表示不同条件有不同的侧重*/
SELECT * FROM table_name where HERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' );/*可返回单词不同的形态，该结果可返回street， streets*/

/*26. lateral 子句*/
/*lateral 关键词的子句，就是为了实现在子查询中 跟外边的表实现关联，可以与with建立临时表等价，我们举几个例子*/
select name, salary, avg_salary
from instructor_I1, lateral (select avg(salary) as avg_salary from instructor_I2 where I2.dept_name= I1.dept_name); 
/*但是需要注意的是，只有少数sql支持了lateral的使用(如IBM DB2)，更常用的还是with子句*/

/*27. with 子句*/
/*with 子句提供了定义临时关系的方法，是一个临时表，用完这个查询就消失，举个例子*/
with dept_total (dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name),
     dept_total_avg(value) as (select avg(value) from dept total)
select dept name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;
/*很好理解这个，with子句很容易看，不在解释了*/

/*28. 标量子查询 Scalar subquery*/
/*标量子查询就是使用子查询之后得到的结果是子查询的只有一行或一列的数据，
  具体包括列子查询/行子查询/表子查询三种；一般是查找一个固定的值，比如max/avg/min等*/
/*标量子查询可以出现在select、where和having子句中。，这里我们举个例子
  也可以定义标量子查询而不定义聚合。通常不可能在编译时确定子查询是否可以在其结果中返回多个元组;如果执行子查询时结果有多个元组，则会出现运行时错误。*/
select dept_name, (select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructors
from department;