SQL基础总结及补充

一、基本操作(易忘)

1.数据库重命名(旧版)：

		 rename database 旧名 to 新名 ;

2.修改表：

添加：

alter table 表名 add 列名 类型(长度) default 默认值 约束;

修改列名：

alter table 表名 change 旧列名 新列名 类型(长度) 约束;

删除列：

alter table 表名 drop 列名;

修改表名：

rename table 旧表名 to 新表名; 

3.更新表：

更新所有记录：

update 表名 set 字段名=值, 字段名=值, .... ;

更新指定记录：

update 表名 set 字段名=值, 字段名=值, ....  where 条件 ; 

4.删除表：

drop table 表名 ;  # 删除一整个表

delete from 表名 [where 条件] ; # 删除表中某列的某行数据，不加条件表示删除所有数据

truncate table 表名 ; # 清空整个表的数据

update 表名 set 字段 = null 或 ‘’ ; # 清空一列的数据

5.全外连接(MySQL不支持)：

full outer join...

6.模糊查询：

语法：

select 字段 from 表名 where 字段 like ‘匹配内容’ ;

匹配条件 % ：任意个任意内容 _ ：单个任意内容
eg：张%：表示以张开头的任意内容 _张%：表示第二个字为张的任意内容

7.分页查询：

语法：

select 字段1，字段2，……  from 表名 limit M, N;

  M: 表示从第几条索引开始(0 - …) N: 表示共查询多少条数据

8.插入语句：insert into select

从一个表中复制数据，插入到另一个已存在的表中
语法：

insert into table1  select 语句

把使用select语句查询到的数据存到table1中。
注意：table1的列名需一一对应，主键的值如果无意义可以用null来表示

9.约束：

主键约束：primary key 
删除主键：alter table 表名 drop primary key ;
外键约束：foreign key

添加外键：

1.建表后添加：

alter table 表名add [constraint 外键名称(一般以_fk结尾)] 
foreign key (从表字段名) references 主表(主表主键名)

2.建表时添加：

 CREATE TABLE products(
    主键名 VARCHAR(32) PRIMARY KEY ,
[constraint 外键名] foreign key (从表外键名) references 
主表(主表的主键名)
);	

删除外键：

1. 知道外键名：

 alter table 表名 drop foreign key 外键名 ;

2.不知道外键名：

show create table 表名 ;  -- 查询外键名   # 1.
alter table 表名 drop foreign key 外键名  # 2.

10.索引：index

1.给某个表的某个字段创建索引：

create index索引名(一般在前面加inx_) on 表名(字段名) ;

2.给某个表的某个字段添加索引：

alter table 表名 add index 索引名(字段名) ;

3.在查询中使用强制索引：

select * from 表名 force index(索引名) where 条件 ;

二、SQL语句分类：

数据定义语言DDL：

用来定义数据库对象：数据库，表，列等。
关键字：create，alter，drop等

1、DDL数据库操作：

语法：

create database 库名;  #创建数据库
create database if not exists 库名;  #如果该库名不存在时创建
create database 库名 set character 编码集;  #创建数据库并设计

编码集：

show databases( ) ;   #查看所有数据库
drop database 库名;   #删除指定数据库 
use database;         #选择数据库
select database( );   #查看正在使用的数据库

1、DDL数据表操作：

语法：

 create table 表名 (
			字段名  类型(长度)  约束,			#创建表
			…………
	)
show tables;       #查看数据库中的所有表
desc table;        # 查看表结构
drop from 表名;    #删除表
alter table 表名 add / change / drop  …… ;   #增/改/删除列 
	(如果要添加的列名是操作符的话，需要用 ` 列名 ` 来添加)
rename table旧表名 to 新表名;

数据操作语言DML：

用来对数据库中表的记录进行更新。
关键字：insert，delete，update等

1、DML数据库操作语言：

语法：

Insert into 表名 (字段1, 字段2,……)  values(值1, 值2,……)
	—— 向表中插入数据 
update 表名 set 字段名=值, 字段名=值,…  where 条件;   
	——更新符合条件的记录的指定字段
delete from 表名 where 条件;    
	——删除符合条件的数据

DQL数据查询语言：

用来查询数据库中表的记录关键字：select，from，where等

语法：
简单查询： 略..
条件查询： 略..
排序查询：select * from 表名 order by 排序字段 ASC/DESC ;	
ASC 升序(默认)    DESC降序
order by 是依据…来排序(默认为升序排序)

1.单表查询：

语法：

select [distinct] 字段 [as 别名] from 表名 
group by 字段 having 条件 
order by 字段 [limit offset, count] ;

逻辑执行顺序

• 1.from
• 2.where
• 3.group by
• 4.having
• 5.select
• 6.order by
• 7.limit

2.排序查询：(略)

3.分页查询：

在sql语句结尾加：limit offset, count  
表示从offset开始(0是取第一个值)，一共取count个数据

4.分组查询：group by 字段 having 条件

聚合函数中select后的非聚合字段必须也要写到group by之后，
having字段后可以跟聚合字段

5.多表查询

内连接查询：1.隐式内连接 2.显示内连接
外连接查询
左外连接查询		
子查询

三、补：

1. group by 和 order by 的区别：
   group by 需和聚合函数一起使用，其select后要跟多个查找对象，且必须含有分组对象，否则的话数据结果没有变化，查询将没有意义，使用group by的话，前面的select后的字段也要添加到group by之后。
   order by 是单纯的排序函数，默认为升序，加DESC后为降序

2. union 和 union all用于两个查询语句的拼接，
   union会自动压缩多个结果集合中的重复结果。
   union all 则将所有的结果全部显示出来，不管是不是重复。

3. 计算字符串长度的函数：
   length( ‘字符串’ ) ：汉字长度为3，字母和数字长度都为1。
   char_length( ‘字符串’ ) ：汉字、字母、数字长度均为1。

4. 保留小数：
   round(小数, 保留位数) 会四舍五入
   format(小数, 保留位数) 会四舍五入
   truncate(小数, 保留位数) 不会四舍五入

5. ifnull：
   如果该值为null时可以自定义值来代替null
   Eg：ifnull ( grade, 0 ) —— 若grade为null则用0来代替

6. 开窗函数：
   语法：

select  字段1,
		字段2,
函数名( ) over( partition by字段1 order by 字段2 ) 别名
from 表名 ……  ; # 其中的 partition by 可省略

7. 日期函数
   curdate( ) 函数：
   返回当前的日期 (年-月-日)
   now ( ) 函数：
   返回当前详细时间 (年-月-日 时:分:秒 )
   curtime( ) 函数：
   返回当前时间 ( 时:分:秒 )

   计算函数：
   ①、timestampdiff(返回类型, 时间1, 时间2) 函数：
   该函数表示两个时间的整数差，返回类型是个参数，可以自定义 (例如：year, month, week, day, hour, minute, second) ；其返回值为： 时间2 – 时间1。
   补：该函数计算年龄时会自动精确到月,日，不用再额外考虑

   ②、datediff(时间1, 时间2) 函数：
   该函数计算 时间2 和 时间1 之间相差多少天（和timestampdiff相减相反）。

   ③、date_format(时间, 参数) 函数：
   该函数可以单独提取日期，例如年、月、日等；
   例：select date_format(now( ), ‘%Y’ ); 输出：2021
   select date_format(now( ), ’00-%m-%d’ ) 输出：00-07-31

   ④、weekofyear (时间) ：返回所填日期的周在这年的位置
   dayofyear(时间) ：返回所填日期的天在这年的位置
   dayofmonth(时间) ：返回所填日期天在这月中的位置
   参数时间的形式为：年-月-日 eg：2021-1-6
   weekofyear (‘2021-1-6’) —— 结果：1
   dayofyear (‘2021-1-6’) —— 结果：6
   dayofmonth(‘2021-1-6 ’) —— 结果：6

   ⑤、month(日期) ：返回该日期对应的月份
   当前日期： 2021-8-1
   eg：select month( now( ) ); —— 8
   year(日期) ：返回该日期对应的年份
   eg：select year( now( ) ); —— 2021
   day(日期) ：返回该日期对应的天数(按月计算)
   eg：select day( now( ) ); —— 1
   week(日期, [参数]); ：返回对应格式的周数
   eg：select week(now(), 5); —— 30
   参数表：

8. substring( ) ：
   语法： substring( str , X, Y) —— str是字符串；表示从X开始(包含X)截取，共截取Y个字符。
   eg：substring( ‘abcdef’ , 2, 4 ) 结果为：bcde

9. create table表名 as select ……
   作用：将select查询的数据保存到新建的表中。

10. insert ignore：当出现主键或唯一索引重复后插入失败。
    例：insert ignore into 表名(字段名……) values(数据……)
    replace into：当出现主键或唯一索引重复后，删除原先的数据，将新数据插入。
    例：replace into 表名(字段名……) values(数据……)

11. 创建表并添加数据：
    create table 表名 [as] select …… ;

12. concat 和 group_concat 的使用
    concat : 将多个字符串连接成一个字符串
    语法:

concat(str1, str2)

group_concat : 将group by产生的同一个分组中的值连接起来，返回一个字符串结果; 默认以‘ , ’分隔.
语法:

group_concat ( [distinct] 要连接的字段 
[order by 排序字段 asc/desc  ] [separator '分隔符'] )

13. exists 和not exists
    exists属于相关子查询, 也就是说子查询的条件依赖于外层父查询的某个属性值, 例如:
    select Sname from Student
    where exists (
    select *
    from SC
    where Sno=Student.Sno and Cno =‘1’);
    所说的依赖就是 : Sno = Student.Sno . 这里要说明一下, 这个查询过程是 : 与外查询依赖, 执行时先从父查询中取一个元组, 然后根据 ‘ Sno=Student.Sno ’ 条件处理内查询, 得到结果后再取第二个,结果反复, 如果内查询结果为空则返回 False , 非空则为 True ; not exists则相反

14. 开窗函数
    语法 :
    函数名(列) over( [ 分区 ] [ 排序 ] )
    所有的聚合函数都可以做开窗函数, 开窗函数的关键字是 over
    如果 over 后的括号为空, 则表示开窗函数会对结果集中的所有行进行聚合运算
    例 :

select name, salary, count(*) over() 工资小于5000员工数
  	from person
 	where fsalary < 5000

含义 : 对查询结果的每一行都返回所有符合条件的行的条数, 因为over 后
的括号内为空, 则对结果的所有行进行聚合运算(此处为在每行后都打印统计
出的“工资小于5000员工数”)

15. cash when 转换工具：
    cash when方法可以对需要的内容进行转换，也可以对行、列进行互换。位置在select之后，from之前
    语法：

select  ****
		cash
		when 列名 = 值 then ‘替换值’ [‘else ‘不等时的值’]
		when 列名 = 值 then ‘替换值’ [‘else ‘不等时的值’]
						…………
	  (else可以统一放最后的end前)  end  [ as 列别名 ]
	  

实例：行转列
代码：

```sql
create table account
(
    id       int primary key auto_increment,
    dt       date,
    pay_type varchar(10),
    money    double
);


insert into account
values (null, '2021-07-25', '1', 6);
insert into account
values (null, '2021-07-25', '2', 15);
insert into account
values (null, '2021-07-25', '3', 600);
insert into account
values (null, '2021-07-25', '2', 25);
insert into account
values (null, '2021-07-25', '1', 14);
insert into account
values (null, '2021-07-26', '1', 3);
insert into account
values (null, '2021-07-26', '2', 7);
insert into account
values (null, '2021-07-26', '2', 10);
insert into account
values (null, '2021-07-27', '1', 15);
insert into account
values (null, '2021-07-27', '2', 25);
insert into account
values (null, '2021-07-27', '2', 10);
insert into account
values (null, '2021-07-27', '3', 4600);
insert into account

行转列：
values (null, '2021-07-27', '2', 600);
	select dt, sum(t.公交) 公交, sum(t.地铁) 地铁, sum(t.飞机) 飞机
from
(select dt,
       case
            when pay_type = 1 then sum(money)
            when pay_type = 2 then 0
            when pay_type = 3 then 0
            else 0
            end 公交,
       case
            when pay_type = 1 then 0
            when pay_type = 2 then sum(money)
            when pay_type = 3 then 0
            else 0
            end 地铁,
        case
            when pay_type = 1 then 0
            when pay_type = 2 then 0
            when pay_type = 3 then sum(money)
            else 0
            end 飞机
from account
group by dt,pay_type) t group by dt;