DataWhale- MySQL --Task2

2.1 MySQL 基础 （二）- 表操作

学习内容

1. MySQL表数据类型

• 整数类型
• 浮点数类型、定点数类型
• 日期和时间类型
• 字符串类型和二进制数据类型。
• 不同数据类型决定了数据的存储格式、有效范围和相应的限制。
• 详见blog： https://blog.csdn.net/lipengcn/article/details/51111667

3. 用SQL语句创建表
   语句解释
   设定列类型 、大小、约束
   设定主键6

   -- 创建表
   CREATE TABLE  IF NOT EXISTS employees (
       `emp_no`     INT       UNSIGNED      NOT NULL  auto_increment,#自增
       birth_date  DATE            NOT NULL,
       first_name  VARCHAR(14)     NOT NULL,
       last_name   VARCHAR(16)     NOT NULL,
       gender      ENUM ('M','F')  NOT NULL,    
       hire_date   DATE            NOT NULL,
       PRIMARY KEY (emp_no)  # 设置主键
   ) ENGINE=InnoDB
   DEFAULT CHARACTER SET = utf8;
    
   详见我以前blog: https://www.jianshu.com/p/a6739134c0c0
   

4. 用SQL语句向表中添加数据
   语句解释
   多种添加方式（指定列名；不指定列名）

   -- 指定列名
   INSERT INTO table_name  (field1, field2,...fieldN)  
   VALUES  (valueA1,valueA2,...valueAN),
   (valueB1,valueB2,...valueBN),
   (valueC1,valueC2,...valueCN)......; 
   -- 不指定列名
   INSERT INTO table_name  
   VALUES  (valueA1,valueA2,...valueAN),
   (valueB1,valueB2,...valueBN),
   (valueC1,valueC2,...valueCN)......; 
   

5. 用SQL语句删除表
   语句解释
   DELETE
   DROP
   TRUNCATE
   不同方式的区别

   -- DELETE，TRUNCATE
   delete from tablename;  #删除表记录, 记录日志
   truncate table tablename;  #删除表记录， 不记录日志
   -- drop
   drop table employ;  # 删除表
   
   		最直观是： 
   	1.TRUNCATE TABLE是非常快的 
   	2.TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值 
   	
   	……………………………………………………………………………………………… 
   	注意:这里说的delete是指不带where子句的delete语句 
   	相同点 
   	truncate和不带where子句的delete, 以及drop都会删除表内的数据 
   	
   	不同点: 
   	1. truncate和 delete只删除数据不删除表的结构(定义) 
   	    drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态. 
   	2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发. 
   	   truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger. 
   	3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动 
   	  显然drop语句将表所占用的空间全部释放 
   	  truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage;   truncate会将高水线复位(回到最开始). 
   	4.速度,一般来说: drop>; truncate >; delete 
   	5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
   	使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大. 
   	想删除表,当然用drop 
   	想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete. 
   	如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据 
   	    ```
   

6. 用SQL语句修改表
   修改列名
   修改表中数据
   删除行
   删除列
   新建列
   新建行

   	    ----oracle与mysql-----
   	-----------------------------------------
   	对字段操作        |        操作方法
   	----------------------------------------------------
   	更新字段名     
   	oracle: alter table table_name rename column column_old to column_new;
   	mysql:  alter table table_name change col_old col_new  varchar(30)(类型)
   	
   	添加字段    
   	oracle: alter table table_name add column_name varchar(10);
   	mysql:  alter table test add  column name varchar(10);  
   	          alter table 表名 add 属性名1  数据类型 [完整性约束条件] [first | after 属性名2];
   	
   	删除字段
   	oracle:  alter table table_name drop column column_name;
   	mysql:  alter table test drop  column name;
   	
   	添加字段并附值 
   	oracle:     alter table table_name add column_name number(1) default 1;
   	mysql:  alter table test add  column name varchar(10);
   	
   	修改字段值   
   	oracle:    update table_name set filedname=value where filedname=value;
   	mysql:    同上
   	
   	修改字段数据类型    
   	oracle: alter table tablename modify filedname varchar2(20);
   	mysql:  alter table test modify address char(10)  ;
   	        ||alter table test change address address char(40) ;
   	修改表名：  
   	oracle： alter table oldTableName rename to NewTableName; 
   	mysql:   alter table oldTableName rename [to] NewTableName;
   

知识1. mysql 不支持select into 语法

• 替换语句

  Create table new_email (Select * from email);
  

• 支持insert into new_table select * from old_table ;

知识2. 开窗函数

• 语法

select  count(*)  over(partition by column_name1  order by column_name2)   from table_name

详见：https://www.cnblogs.com/lihaoyang/p/6756956.html

知识3. case when

• 简单Case函数

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

–Case搜索函数

CASE WHEN sex = '1' THEN '男' 
WHEN sex = '2' THEN '女' 
ELSE '其他' END  

详见： http://www.cnblogs.com/aipan/p/7770611.html

作业

题一

创建如下所示的 courses 表 ，有: student (学生) 和 class (课程)。
例如,表:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math
A	Math

编写一个 SQL 查询，列出所有超过或等于5名学生的课。
应该输出:

class
Math
Note:
学生在每个课中不应被重复计算。

解答

项目四：交换工资（难度：简单）

创建一个 salary 表，如下所示，有m=男性 和 f=女性的值 。
例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

解答

2.2 MySQL 基础 （三）- 表联结

学习内容

MySQL别名
INNER JOIN
LEFT JOIN
CROSS JOIN
自连接
UNION
以上几种方式的区别和联系

作业

项目五：组合两张表 （难度：简单）

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar
PersonId 是上表主键

表2: Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

简单

项目六：删除重复的邮箱（难度：简单）

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

Id	Email
1	a@b.com
2	c@d.com

简单

引用

1. http://www.cnblogs.com/aipan/p/7770611.html
2. https://www.cnblogs.com/lihaoyang/p/6756956.html
3. http://www.cnblogs.com/aipan/p/7770611.html
4. https://blog.csdn.net/ys_code/article/details/79497294
5. https://www.cnblogs.com/jun9207/p/5035736.html
6. https://www.jianshu.com/p/a6739134c0c0