sql

把大学学的的sql再次捡起来.

MySQL

这篇介绍比较详细.SQL介绍
其他类似软件：

• 关系型数据库：sqllite、db2、oracle、access、sql server、MySQL
• 非关系型数据库：MongoDB、redis

创建

数据库
	create database db1(库名);
	drop database db1;

数据表
   - 先创建部门表tb2
	create table tb2(
		tid int not null auto_increment primary key,
		department_name char(10)
	)engine=innodb default charset=utf8;
	 
   - 创建用户表tb1, tb1中会有外键
	create table tb1(
		id int not null auto_increment primary key,
		name char(10),
		department_id int,
		constraint fk_tb1_tb2 foreign key (department_id) references tb2(tid)
	)engine=innodb default charset=utf8;
- note:
      - engine=innodb 支持事务回滚
      - 外键名fk_tb1_tb2不能重复,通常设置为fk_表1(_列名1)_表2(_列名2)
      - 外键:可以一对一、一对多、多对多
      - 主键:一个表只能有一个主键,这个主键可以由一列或多列组成

- 多个列作为主键时
       - create table tb2(
  		nid int not null auto_increment,
  		pid int default null,
  		num int default null,
  		primary key (nid,pid)
 	)engine=innodb default charset=utf8;
  
       - create table tb1(
  		id int not null auto_increment primary key,
  		name char(10),
 		id1 int,
 		id2 int,
  		constraint fk_tb1_tb2 foreign key (id1,id2) references tb2(nid,pid)
 	)engine=innodb default charset=utf8;
- 清空表: delete from tb1;
         truncate table tb1;
- 删除表: drop table tb1;
数据行
   - 增 insert into tb1(name,age) valuse('alex',18);
   - 删 delete from tb1 where id > 2;
   - 改 update tb1 set name ＝ 'alex' where id>1;

关于自增

- 对于自增列，必须是索引（含主键）
- 对于自增可以设置步长和起始值
	alter create tb1 auto_increment=10;     # 将填补1自增列的起始值设置为10
	show create table tb1 \G;    #查看创建的表tb1,可以看到自增列当前的值
- 自增步长:
	- 基于会话级别:
	   show session variables like 'auto_inc%';
           set session auto_increment_increment=2;    # 设置步长
           set session auto_increment_offset=10;      #设置起始值
	- 基于全局级别:
	   show global variables like 'auto_inc%';
           set global auto_increment_increment=2;
           set global auto_increment_offset=10;

带约束的查询

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

条件可以有

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;     # 查询第num_offset开始的num_limit 行数据

消除重复 : 使用distinc关键字

SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

唯一索引

	create table t1(
		id int,
		num int,
		unique uq1 (num)
	)

• 不能重复,可以为空
• 主键:不能重复且不能为空
• 主键和唯一索引都是能加速查找

连表查询

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id
LEFT JOIN another_table2
    ON ...
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

• left join：左连接，左边表全部显示
• right join：右连接，右边表全部显示，左边可能出现空值
• inner join：内连接，不显示出现空值null的一行
• full join：全连接，两边的表的行都要保留，左右两边都可能出现空值

分组

select num,nid from 表  where nid > 10 group by num,nid order nid desc
select num,nid,count(score),sum(score),max(score),min(score) from 表 group by num,nid

select num from 表 group by num having max(id) > 10

• 对于聚合函数结果进行二次筛选时必须用having

sql文件导入

- 进入需要存放的数据库中
	use db2;
- 导入文件
	source D:/mysql-8.0.19-winx64/data/db2/db2.sql

条件语句:

- 1. case when score.num > 60 then 1 else 0 END

- 2. if(isnull(score.num),0,score.num)  
    - 等价于python
	if score.num is null:
	    score.num = 0
	else:
	    score.num = score.num

sql练习题
练习题参考答案