DataBase - 开发中常用的SQL语句

因为兼职写过一段时间后端，多多少少接触了一些数据库方面的知识，此篇主要记录一些常见的sql语句基础，陆续补全 ~

兄弟篇

• MySQL下载、安装、配置
• 初次使用MySQL中遇到的一系列问题
• 开发中常用SQL语句
• MySQL安全模式

关于约束方面仅看目录即可，更多的还是要留意基础语句、统计相关、排序方式，因为这些经常在项目中见到

常见约束

根据约束数据列限制，约束可分为

• 单列约束：每个约束只约束一列
• 多列约束：每个约束约束多列数据

not null：非空约束，指定某列不为空

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上

Null类型特征： 所有的类型的值都可以是null，包括int、float等数据类型（空字符串“”是不等于null，0也不等于null）

采用table加上了非空约束（也可用alter来修改、增加非空约束 ）

create table temp( 
	id int not null, 
	name varchar(255) not null default ‘abc’, 
	sex char null 
)

增加非空约束

 alter table temp modify sex varchar(2) not null; 

取消非空约束

 alter table temp modify sex varchar(2) null; 

取消非空约束，增加默认值

 alter table temp modify sex varchar(2) default ‘abc’; 

unique： 唯一约束，指定某列和几列组合的数据不能重复

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引；

unique(name, password)表示用户名和密码组合不能重复

create table temp ( 
	id int not null, 
	name varchar(25), 
	password varchar(16), 
	constraint uk_name_pwd unique(name, password) 
); 

添加唯一约束

 alter table temp add unique(name, password); 

修改唯一性约束

 alter table temp modify name varchar(25) unique; 

删除约束

 alter table temp drop index name;

primary key：主键约束，指定某列的数据不能重复、唯一

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；
如果是多列组合的主键约束， 那么这些列都不允许为空值，并且组合的值不允许重复。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

MySQL的主键名总是PRIMARY， 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引

列模式

create table temp( 
　　id int primary key, 
　　name varchar(25) 
); 

create table temp2( 
　　id int not null, 
　　name varchar(25), 
　　pwd varchar(15), 
　　constraint pk_temp_id primary key(id) 
); 

组合模式

create table temp2( 
　　id int not null, 
　　name varchar(25), 
　　pwd varchar(15), 
　　constraint pk_temp_id primary key(name, pwd) 
);

alter删除主键约束

 alter table temp drop primary key; 

alter添加主键

 alter table temp add primary key(name, pwd);

alter修改列为主键

 alter table temp modify id int primary key; 

设置主键自增（auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了）

create table temp( 
　　id int auto_increment primary key, 
　　name varchar(20), 
　　pwd varchar(16) 
); 

foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 也就是说从表的外键值必须在主表中能找到或者为空。 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据， 然后才可以删除主表的数据。还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录， 那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。

创建外键约束

通常先建主表，然后再建从表，这样从表的参照引用的表才存在

主表

　　create table classes( 
　　id int auto_increment primary key, 
　　name varchar(20) 
　　); 

从表

create table student( 
　　id int auto_increment, 
　　name varchar(22), 
　　constraint pk_id primary key(id), 
　　classes_id int references classes(id) 
); 

表级别创建外键约束

create table student( 
　　id int auto_increment primary key, 
　　name varchar(25), 
　　classes_id int, 
　　foreign key(classes_id) references classes(id) 
); 

上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n， 其中student是表名，n是当前约束从1开始的整数。

指定约束名称

create table student( 
　　id int auto_increment primary key, 
　　name varchar(25), 
　　classes_id int, 
　　constraint fk_classes_id foreign key(classes_id) references classes(id) 
); 

多列外键组合，必须用表级别约束语法

create table classes( 
　　id int, 
　　name varchar(20), 
　　number int, 
　　primary key(name, number) 
); 

create table student( 
　　id int auto_increment primary key, 
　　name varchar(20), 
　　classes_name varchar(20), 
　　classes_number int, 
　　foreign key(classes_name, classes_number) references classes(name, number) 
); 

删除外键约束

 alter table student drop foreign key student_ibfk_1; 
 alter table student drop foreign key fk_student_id; 

增加外键约束

 alter table student add foreign key(classes_name, classes_number) references classes(name, number); 

自引用、自关联（递归表、树状表）

create table tree( 
　　id int auto_increment primary key, 
　　name varchar(50), 
　　parent_id int, 
　　foreign key(parent_id) references tree(id) 
); 

级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on delete cascade 或on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null

create table student( 
　　id int auto_increment primary key, 
　　name varchar(20), 
　　classes_name varchar(20), 
　　classes_number int, /*表级别联合外键*/ 
　　foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
); 

check：检查，指定一个表达式，用于检验指定数据

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；

下方check约束要求age必须大于20，但没有任何作用。同时你会发现创建table的时候没有任何错误或警告

create table temp( 
　　id int auto_increment, 
　　name varchar(20), 
　　age int, 
　　primary key(id), 
　　check(age > 20) 
); 

---

基础语句

Here ：请提前关注，便于理解

以下数据均需换成自己的数据库数据

• my_table：虚拟表名
• 键：虚拟字段
• 值：虚拟数据

建表

建表时加唯一约束(此处借鉴即可)

CREATE TABLE `my_table` (
`键1` int(11) NOT NULL AUTO_INCREMENT,  -- 自增
`键2` varchar(18) NOT NULL unique,  -- 唯一性约束
PRIMARY KEY (`键1`)  --主键

已有表加唯一约束

 alter table my_tableadd unique(键);

增、删、改、查

增

新增字段

• 单字段新增

 alter table tableName add 键 varchar(30)  

• 多字段新增

 alter table tableName add columnName varchar(30) NULL, columnPass INT NULL ;

新增数据

• 单字段新增

insert into my_table(键)VALUES(值);

• 多字段新增

insert into my_table(键1,键2) values( 值1,值2);

删

删除字段

 alter table tableName drop column 键
 ALTER TABLE 表名 DROP [COLUMN] 字段名;

删除数据

• 单条件删除

delete  from my_table where  键1 in(值1);

• 多条件删除

delete  from my_table where  键1 in(值1,值2) and 键2 in(值2)   ;

改

修改字段类型

 alter table tablename modify (column datatype [default value][null/not null],….);
 ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度  新默认值  新注释;

修改字段名称

 ALTER TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;	 
 alter table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释'; -- 正常，此时字段名称没有改变，能修改字段类型、类型长度、默认值、注释
 alter table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常，能修改字段名、字段类型、类型长度、默认值、注释
 alter table table1 change column2 column1 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常，能修改字段名、字段类型、类型长度、默认值、注释
 alter table table1 change column1 column2; -- 报错

修改数据

• 单字段修改

update my_table set  键2=值2 where  键1= 值1;

• 多字段修改

update my_table set  键2=值2,键3=值3 where 键1=值1;

查

语法

select 键 from my_table;

查表内所有字段数据

select * from my_table 

查表内部分字段数据

select 键1,键2 from my_table ;

去重

select distinct 键1 from my_table;

查询不重复的记录

select 键1,键2 from my_table group by 键1;

取表内的前五条数据

select * from table limit 5

统计相关

重复数据查询

select 键,count(*) as count from  my_table by 键 having count>1;

查询表内数据总数

select count(*) from my_table ;

总和

select sum(键) from my_table where category_id = 'c001';

平均价格

select avg(键) from my_table where category_id = 'c002';

最大值

select max(键) from my_table;

最小值

select min(键) from my_table;

NULL与非NULL判断

第一种 where XX =‘NULL’ ,XX字段存的值就是NULL这四个字符，
第二种 where XX is null ，XX字段什么也没存，这是数据库的判断语法，
第三种 where isnull（XX），XX字段什么也没存，这是界面开发语言的判断语法

“!=”与“<>”的区别

ANSI标准中是用<>(所以建议用<>)，但为了跟大部分数据库保持一致，数据库中一般都提供了 !=(高级语言一般用来表示不等于) 与 <> 来表示不等于

在SQL语句中，“<>”代表的是不等于，和 " != "是一个意思！

各数据库兼容状态

• 支持 != 和 <>

MySQL 5.1、PostgreSQL 8.3、SQLite、Oracle 10g、Microsoft SQL Server 2000/2005/2008、IBM Informix Dynamic Server 10、InterBase/Firebird

• 仅支持<>

IBM DB2 UDB 9.5、Apache Derby

条件筛选

sum(case when status=1 and pay=1 then 1 else 0 end) pay_count

统计，如果 status 和 pay 都等于 1 统计加 1 不然加 0！pay_count 是别名

排序方式

注意：
1.排序语句：order by + 排序字段 + 排序方式 （排序字段一般为数值型、字母型）
2.数据库排序方式默认为升序，如需动态切换排序方式可在尾部酌情使用 asc、desc
3.order by 语句必须要放在整个sql语句的最后，否在你都跑不起来这个sql

升序

select * from my_table order by 键 asc 

降序

select * from my_table order by 键 desc

去重，并排序(降序)

select distinct 键 from my_table order by 键  desc

根据多列进行排序

可以按照表达式进行排序

如果top后跟的不是数字，而是一个表达式一定要使用()把表达式括起来

top

select top (2*2) * from my_table order by tMath desc 

查询数学成绩最高的5名

select top 5 * from TblScore order by tMath desc

获取数学成绩最低的5名

select top 5 * from my_table order by tMath asc

查询出前35%数据

select top 35 percent * from my_table order by tMath desc

– 没写完，过年回家写

select*,平均分=(tEnglish+tmath)*1.0/2 from TblScore order by 平均分 desc

select*from TblScore order by (tEnglish+tmath)*1.0/2 desc