SQL

DROP TABLE, TRUNCATE TABLE, DELETE TABLE　三种删除语句的区别

1.DROP TABLE　清除数据并且销毁表，是一种数据库定义语言(DDL Data Definition Language), 执行后不能撤销，被删除表格的(schema)关系，索引，权限等等都会被永久删除。

2.TRUNCATE TABLE　只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据，是一种数据库定义语言(DDL Data Definition Language)，执行后不能撤销。

3.DELETE TABLE　删除（符合某些条件的）数据，是一种数据操纵语言(DML Data Manipulation Language)，执行后可以撤销。

运行速度一般DROP最快，DELETE最慢，但是DELETE最安全。

drop table if EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
#反而比TEUNCATE快
truncate exam_record;

一，建表

#comment 键的注释
# primary key主键设置（一张表只能有一个唯一索引）    unique唯一索引（一张表可以有多个唯一索引）
# auto_increment自增
create table user_info_vip(
    id int(11)  primary key auto_increment comment '自增ID',
    uid int(11) unique not null comment '用户ID',
    nick_name varchar(64) comment '昵称',
    achievement int(11) default 0 comment '成就值',
    level int(11) comment '用户等级',
    job varchar(32) comment '职业方向',
    register_time datetime default current_timestamp comment '注册时间'
) default charset = utf8;

二，添加列或修改列

1、 添加列

Alter table 表名add column 列名 类型；（默认添加到表的最后一列）

[first|after 字段名]可以指定位置

2、 修改列的类型或约束

Alter table 表名 modify column 列名 新类型[新约束]；

3、 修改列名

Alter table 表名 change column 旧列名 新列名 类型；

4、 删除列

Alter table 表名 drop column 列名；

5、 修改表名

Alter table 表名 rename [to] 新表名；

alter table user_info add school varchar(15) after level;
alter table user_info change job profession varchar(10) ;
alter table user_info modify  achievement int(11) default 0;

三，创建索引

#普通索引
alter table examination_info
add index idx_duration(duration);
#唯一索引
alter table examination_info
add unique index uniq_idx_exam_id(exam_id);
#全文索引
alter table examination_info
add fulltext index full_idx_tag(tag);

#查看索引
show index from examination_info;

#删除索引
drop index uniq_idx_exam_id on examination_info ;
drop index full_idx_tag on examination_info;

四，limit用法

select * from table_name limit [offset,] rows;
#参数说明
offset偏移量：指定第一个返回行的偏移量（即从哪一行开始返回）,初始值为0
rows：返回具体行数

五，窗口函数（除聚合函数）

select * from city_s;
id,uid,name,city,salary
1,1,Tom,nanchang,6000
2,1,Jack,changsha,8000
3,2,zxc,jiujiang,8000
4,1,ailce,zhejiang,9000
5,2,ai,zhejiang,6000
6,3,aie,changsha,7000
7,3,ace,zhejiang,8000

对比partition by和group by

select uid,sum(salary) from city_s;
1,23000
2,14000
3,15000
select * ,sum(salary) over (partition by uid) as 'zong' from city_s;
id,uid,name,city,salary,zong
1,1,Tom,nanchang,6000,23000
2,1,Jack,changsha,8000,23000
4,1,ailce,zhejiang,9000,23000
3,2,zxc,jiujiang,8000,14000
5,2,ai,zhejiang,6000,14000
6,3,aie,changsha,7000,15000
7,3,ace,zhejiang,8000,15000

# rank() 分区并排序 若order by 的值一致则占用一个序列 在1值=2值时，113而不是123
#row_number() 分区并排序 若order by 的值一致则随机排序
#dense_rank() 分区并排序 若order by 的值一致则空出一个序列在1值=2值时，112而不是123
select * from (select row_number() over (partition by uid order by salary) as num_row,
                      rank() over (partition by uid order by  salary) as rank_row,
                      dense_rank() over (partition by uid order by salary) as den_row,
                      id,
                      uid,
                      name,
                      city,
                      salary
from city_s)  t;

#percent_rank()计算公式 (rank-1)/(rows-1)  rank=num_row,rows为分区数量
select * from (select row_number() over (partition by uid order by salary) as num_row,
                      percent_rank() over (partition by uid order by salary) as percent,
                      id,
                      uid,
                      name,
                      city,
                      salary
from city_s)  t;

#cume_list()     用途：分组内小于、等于当前rank值(salary)的行数 / 分组内总行数 partition by uid 就是422
#                应用场景：查询小于等于当前工资（salary）的比例
select * from (select row_number() over (partition by uid order by salary) as num_row,
                      cume_dist() over (partition by uid order by salary) as cume,
                      id,
                      uid,
                      name,
                      city,
                      salary
from city_s)  t;
#前后函数 ： lag(expr,n) lead(expr,n)
#用途：返回位于当前行的前n行 LAG(expr,n) 和后n行LEAD(expr,n)的expr的值
#举例：
#查询前一个人的工资和当前人的工资差值
select id,uid,name,city,salary,per_salary,salary - per_salary
from (
     select id,uid,name,city,salary,
            lag(salary,1) over (partition by uid order by salary) as per_salary
    from city_s
         ) t;

#头尾函数 first_value(expr) 查看当前分组中expr的第一个值
#        last_value(expr) 查看当前分组中expr的最后一个值
select * ,
       first_value(salary) over w as first_salary,
       last_value(salary) over w as last_salary
       from city_s
window  w as (partition by uid order by salary);

# 其它函数：NTH_VALUE(expr, n)、NTILE(n)
#  NTH_VALUE(expr, n) ：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
select  * ,
       nth_value(salary,2) over (partition by uid order by salary) as nth_salary
from city_s;

# NTILE(n): NTILE(n)函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，
# 此时就可以用NTILE(n)对数据进行分组（由于记录数不一定被n整除，所以数据不一定完全平均），然后将不同桶号的数据再分配
select * ,ntile(2) over (partition by uid order by salary) as nf
from city_s;