sql必知必会的简单总结

今天药忘吃喽~ 2021-10-19 07:20 434阅读 0赞

看了sql必知必会，简单总结一下

## 一基本概念 ##

*  数据库database：保存有组织数据的一组文件或一个文件
 *  数据库管理系统dbms:有mysql,sql server,access等
 *  表Table:同一类型数据的结构化清单
 *  模式：描述数据在表中如何存储，包含怎样的信息等内容
 *  列：表的一个字段，表由一个和多个字段组成。列都有自己的数据类型，定义了该列可以存储哪些数据种类。
 *  行：表中数据是按行存储的，每一行是一个记录
 *  主键：每一行都应该有一列可以唯一的标识自己。任意两行的主键值不相同，每一行至少有一个主键。主键的值不允许修改更新。
 *  注释语句:

\--：--之后的文本就是注释，可以嵌在语句内  
\#:\#之后的一行都是注释  
/*注释*/：多行注释

## 二检索数据 ##

*  检索单个列：`select 关键字 from 表名；`
 *  检索多个列：`select 关键字，关键字，关键字 from 表名；`
 *  检索所有列：`select * from 表名；`
 *  检索不同的值：`select distinct 关键字 from 表名；`只返回有不同值的列
 *  限制结果：

（1）sql server和access数据库：`select top 数字 关键字 from 表名；`返回前数字行数据  
（2）mysql和sqlite数据库：  
`select 关键字 from 表名 limit 数字；`返回不超过数字行数据  
`select 关键字 from 表名 limit 数字1 offset 数字2；`返回从数字2开始的后数字1行数据  
`select 关键字 from 表名 limit 数字2，数字1；`返回从数字2开始的后数字1行数据

## 三排序检索数据 ##

*  按单个列排序：`select 关键字 from 表名 order by 列名；`
 *  按多个列排序：  
    （1）`select 关键字，关键字，关键字 from 表名 order by 列名1，列名2；`即检索的数据先按列名1排序，拥有相同列名1的值的，再按列名2的值排序  
    （2）`select 关键字，关键字，关键字 from 表名 oeder by 2,3;`这里即检索的数据先按第二个关键字的列排序，再按第三个关键字的列排序
 *  指定排序方式：默认以升序排列，使用desc指定该列降序排序，如想多个列都进行降序，每个列名后都应加上该关键字。

## 四过滤数据 ##

### 1、简单过滤 ###

*  使用：例如：select name,price from product where price=2;即从prduct中选出price为2的数据
 *  注意：如果有order by，where应该放在其前面
 *  操作符：除了常用的<,>,!=等还用is null(为空值)between 数字 and 数字(在指定两个值之间)

### 2、高级过滤 ###

（1）可以使and,or进行连接，但and的优先级更高，可以通过()对and,or进行优先分组，如：`select name,price from product where price=2 and name<>'aa';`即从prduct中选出price为2且name不为aa的数据  
（2）in:可以用来指定条件范围，如`select name,price from product where name in ('aa','bb');`即选出name为aa或bb的数据  
（3）not:否定后面的语句，如`select name,price from product where not price=3;`即选出price不等于3的数据

### 3、通配符进行过滤 ###

通配符搜索只能用于文本字段,通配符是用来匹配值的一部分特殊字符,这种过滤需要使用like  
（1）%：任何字符出现任何次数(在access中使用\*)，如：`select name,price from product where name like 'aa%'；`即检索product中name是以aa开头的任何字符串的数据。  
注意：%可以匹配0个字符，但不匹配Null。另外如abc空格是不能通过a%c匹配的，因为有空格，所以应用a%c%匹配  
（2）\_（在access中使用？）：与%相似，只匹配一个字符  
（3）\[\]（只有access和sqlserver支持）：指定字符集，指定匹配位置的匹配的一个字符。如：`select name,price from product where name like '[as]dd';`即选出name值是以a或s开头的以dd结尾的数据。另外可以用^进行否定（access用！），如：`select name,price from product where name like '[^as]dd';`即选出name值不是以a或s开头的，但是以dd结尾的数据

## 五、计算字段 ##

用来对检索出的数据计算，转换，格式化，虽然客户端也可以处理，但数据库处理更快

### 1、格式化 ###

（1）拼接字段||（oracle和sqlite等使用） 或+（sql server和access使用）：如：`select name+'('+price+')' from product;`输出形如： apple (3.2)  
（2）mysql使用函数concat，如`select concat(name,'(',price,')' )from product;`  
（3）别名：as  
上述处理过的数据只是显示出来，不能用于客户端程序中，用as 关键字，处理的结果保存在以该关键字命名的列中，方便应用程序利用  
如`select name+'('+price+')' as name_price from product;`

### 2、算术计算： ###

支持加减乘除，且可以用括号表示优先级  
如：`select name，price，num，price*num as sum from product;`返回形如，

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>price</th> 
   <th>num</th> 
   <th>sum</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>apple</td> 
   <td>3.5</td> 
   <td>4</td> 
   <td>14</td> 
  </tr> 
 </tbody> 
</table>

## 六使用函数处理字段 ##

### 文本处理函数： ###

不同的dbms的函数不尽相同

*  length()返回字符串长度
 *  rtrim()去掉字符串右边空格
 *  ltrim()去掉字符串左边空格
 *  lower()字符串转换为小写，access为lcase()
 *  upper()字符串转换为大写，access为ucase()
 *  left()返回字符串左边的字符
 *  right()返回字符串右边的字符

### 日期和时间处理函数： ###

各dbms不同

### 数值处理函数： ###

差不多一致

## 七汇总数据 ##

*  avg():返回某列的平均值
 *  count()：返回某列的行数
 *  count（\*）对表的行数进行技术，不管是否是NULL,count(列名)对特定列的行数进行统计，忽略NULL
 *  max():返回列中最大值
 *  min()：返回列中最小值
 *  sum()：返回指定列值的和

## 八分组数据 ##

*  group by：例如`select name,count(*) as number from product group by name;`即先对数据按name分组，再计算改组有多少行。
 *  大多数sql实现不允许group by列带有长度可变得数据类型
 *  所有NULL被分成一组
 *  group by出现在where之后，order by之前
 *  having:与where相似，不过是对分组进行过滤

## 九子查询 ##

### 1、利用子查询过滤 ###

例如，product表有生产人员id列，name列，price列，people表有生产人员id列，姓名people\_name列。查找某产品(apple)的生产人员的姓名：`select people_name from people where id in(select id from product where name='apple');`

### 2、作为计算字段使用子查询 ###

如：`select name, (select count(*) from orders where orders.id=customer.id)as orders from customers;`

## 十联结表 ##

用一条语句来查询多个表的数据

### 1、内联结， ###

如：`select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;`  
其中vend\_name是vendors表的列，后两个是products的列，两个表通过vend\_id联系  
相同的：`select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;`

### 2、高级联结 ###

（1）使用别名：如：`select vend_name,prod_name,prod_price from vendors as v,products as p where v.vend_id=p.vend_id;`  
（2）自联结：如：`select name,price from products as p1,products as p2 where p1.price=p2.price and p2.name='apple';`即找出和apple价格相同的数据，查询使用了两个相同的表  
（3）外联结：联结将一个表的行与另一个表的行相关联，但有时需要包含没有关联行的那些行。左关联：left,右关联：right,全关联：full。如：`elect vend_name,prod_name,prod_price from vendors left inner join products on vendors.vend_id=products.vend_id;`

## 十一组合查询 ##

union：在多条语句间使用union。union会自动剔除重复行，union连接的各语句必须是查询相同的列

## 十二插入数据 ##

1、insert into values(各列值)。其中into可省，但是插入数据时最好给出列名。如向表product插入数据：`insert into product(id,name,price) values('1','apple','3.5');`  
2、插入检索出的数据：`insert into product(id,name,price) select id,name,price from product2;`即将从product2检索出的数据插入product中  
3、复制表:`select * into product from product2;`将product2的内容复制到product中，如果只想复制几列，给出列名即可。mysql等的语法稍有不同，`create table product as select * from product2;`

## 十三 ##

*  更新数据:`update product set price='4' where name='apple';`即将apple的价格更新为4
 *  删除数据:`delete from product where name='apple';`即删除名字为apple的这一行

## 十四创建和操纵表 ##

1、创建表：  
`create table( 列名 数据类型 Notnull或默认的null default 值， ) default 值（用来指定默认值，可选）,不同dbms创建表不一样`  
2、删除表：drop table 表名  
3、更新表：alter

## 十五视图 ##

视图是虚拟的表，只包含动态检索数据的查询，access不支持视图  
1、使用视图简化复杂联结  
如；`create view 视图名 as select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;`  
使用视图：`select vend_name,prod_name from 视图名 where vend_id='aaa';`  
2、重新格式化检索出的数据  
如：`select name+'('+price+')' from product;`这样的数据可以保存到视图中  
`create view 视图名 as select name+'('+price+')' from product;`只要输入select \* from 视图名；即输出上句的效果  
3、与上同理可以使用视图过滤不想要的数据，使用视图与计算字段

## 十六存储过程 ##

存储过程好像一个函数，包含多条语句，有参数，返回值等。dbms不同创建存储过程不同。创建时一般都有create peocedure 名字，调用时一般使用到execute

## 十七控制事务处理 ##

事务处理能确保一组sql操作要么完全被执行，要么完全不被执行，确保数据库的完整性。事务处理关键在于将sql分解为块，并明确规定数据何时回退，何时不回退。  
（1）事务处理块的标识  
`sql server:begin transaction ...commit transaction mysql:start transaction oracle:set transaction`  
（2）回退语句  
rollback  
（3）保留点  
保留点取能够唯一标识的名字，在回退时，dbms知道回退到何处。  
创建保留点： `Mysql和oracle等 ：savepoint 名字;sql server:save transaction 名字；`  
回退保留点：`mysql等：rollback to 名字；sql server: rollback transaction 名字；`

## 十八 ##

*  主键：可以在创建表时，使用primary key标识主键，也可以通过alter table 表名 add constraint primary key (列名)设置主键
 *  外键：为了保证引用完整性，外键是表中的一列，其值必须是另一表的主键值，可以在创建表时，使用references 另一表名（列名）标识，也可以通过`alter table 表名 add constraint foreign key (列名) references 另一表名（列名）`
 *  检查约束：用来保证某一列中的数据满足指定的条件  
    如：`crreate table 表名（ number integer not null check(number >4) ）`即插入的Number值必须满足大于4

## 十九数据类型 ##

### 1、串数据类型： ###

*  char ：1~255个字符的定长字符串，长度必须在创建时规定
 *  ncgar：char的特殊形式 ，支持多字节或unicode字节
 *  nvarchar:text的特殊形式，支持多字节或unicode字节
 *  text:变长文本

### 2、数值数据类型 ###

*  bit:单个二进制，0或1
 *  deciml:定点
 *  float:浮点
 *  int:四字节整数
 *  smallint:二字节整数
 *  tinyint:一字节整数

### 3、日期时间 ###

*  date:日期值
 *  time:时间值

转载于:https://www.cnblogs.com/Qi-Lin/p/11334021.html