sql基础1-4

sql基础

1.简介

1.1分类

数据库管理系统DBMS分类所依据的数据库种类：

1. 层次型数据库Hierarchical DB（已很少使用）
2. 关系型数据库Relational DB（应用最广），使用结构化查询语言Structured Query Language(sql)
3. 面向对象数据库Object Oriented DB
4. xml数据库
5. 键值存储系统key-Value Store

我们需要了解的是关系型数据库管理系统RDBMS

1.2 数据库结构

最常见的系统结构是客户端/服务器。

RDB必须以行为单位进行数据读写。

1.3 sql种类

sql语句分为3类：

1. Data Definition Language, eg. create, drop, alter
2. Data Manipulation Language, eg. select, insert, update,delete
3. Data Control Language,eg. commit, rollback, grant, revoke.

1.4 sql基本规则

• 以分号结尾
• 不区分大小写
• 常数书写方式固定
• 单词用半角空格或换行分隔

1.5 库和表的创建

1.5.1 eg.

create database shop;
create table goods
(id	char(4)		 not null,
 name		varchar(100) not null,
 price		integer		,
 reg_date	date		,
 primary key (id));

--注释
/*
注释
*/

库名，表名，列名使用字母数字下划线，开头只能是字母。

1.5.2 数据类型

integer

存储整数。

char

储存定长字符串，如char(4)输入‘abc’，实际存储’abc_’

varchar

储存可变长字符串，varchar(4)输入’abc’,那就是存储’abc’

date

储存年月日，oracle还包含时分秒

1.5.3 约束

列约束not null意思是必须有输入。

表约束primary key (id)是给id列设置主键约束，id列含特定数据，可用来取出特定商品数据。

1.6 表的删除和更新

drop table goods;

删除后无法恢复

--添加列
alter table 表名 add column (列名);

oracle,sql server
alter table 表名 add (列名);

--删除列
alter table 表名 drop column 列名;

oracle,sql server
alter table 表名 drop 列名;

插入数据

sql server，postgreSQL
begin transaction;
insert into 表名 values ();
commit;

mysql
start transaction;

oracle,db2不使用begin transaction

2.查询

2.1 select

select 列名,..., from 表名;
select * from 表名;
select 列名 as 别名 ,..., from 表名;    --别名可用带双引号的汉语
select 常数 as 列名 from 表名;

--删除重复
select distinct column_name from table_name;
--多个列时，distinct只用在第一列之前。

select column,..., from table_name where condition;

2.2 算数运算符和比较运算符

select price*2 as "price_x2" from goods;
--包含null的计算，结果都是null
-- + - * /

--比较运算注意不等号 <>
/*
=null, <>null 无法取出记录，需要运算符 is null,is not null. 
*/

2.3 逻辑运算符

not price>1000 
price<=1000

--and,or
--and优先于or，但最好记着加括号。
--sql逻辑运算是三值逻辑，除真假以外还有第三种值，unknown。
1 and null --unknown
null and null --unknown
1 or null  --1
0 or null  --unknown
--所以创建表时有些值要设定not null。

3.聚合与排序

3.1聚合查询

聚合函数	描述
count	计算行数
sum	计算数值列的和
avg	计算数值列的均值
max	求任意列中数据最大值
min	求任意列中数据最小值

select count(*) from table_name;  --包含null
select count(column_name) from table_name; --不包含null
--count()特有的特性：同一个表，参数不同，输出不同。

select sum(column_name) from table_name; --不含null
select avg(column_name) from table_name; --和与个数都不含null
--max,min几乎适用于所有数据类型

--使用聚合函数去除重复数据
select count(distinct column_name) from table_name; 
--所有聚合函数都可使用distinct

3.2 分组

select column_1,column_2,...
from table_name
where ...
group by column_1,column_2,...;
--被group by指定的列称为聚合键或分组列。
/*
执行顺序：from-where-group by-select
*/
/*
使用聚合函数时，select子句中只能有常数，聚合函数，group by指定的列名。
*/

注意：

• 聚合键之外的列名不能写在select中，不包括mysql
• group by中不能写别名，不包括postgreSQL
• group by是无序的
• where中不能有聚合函数

能使用聚合函数的子句有select,having,order by.

3.3 为聚合结果指定条件

select column_1,column_2,...
from table_name
where ...
group by column_1,column_2,...
having result_condition
/*
having可使用常数，聚合函数，group by指定的列名（聚合键）
第三个要注意。
*/

聚合键对应的条件应写在where中。

where------指定行条件

having-----指定组条件

where比having快，因为where先过滤再排序。

3.4 对查询结果进行排序

select column_1,column_2,...
from table_name
where ...
group by ,...
having ...
order by column_1,column_2 [asc][desc];
-- asc可以不写，默认升序。
--对于null，在开头或末尾显示。
--执行顺序：from-where-group by-select-order  by
--由执行顺序的，order by可以使用别名。

order by可以使用：

• select不存在的列
• 聚合函数
• 列编号（select的列从左到右为1，2，3…），该功能将来会被删除。

4.数据更新

4.1 insert

--插入一行数据
insert into table_name (column_1,column_2,...) values (value_1,value_2,...);
--全列插入时可以省略列名
--插入null要注意该列不能设置not null

--多行插入，用于db2,sql,sqlserver,postgreSQL,mysql,不适用于oracle
insert into table_name values (),(),...;

--oracle的多行插入
insert all into table_name values () () () ...

--插入默认值
create table_name 
(id		integer		default 0,  --默认id为0
 primary key(id));
insert into table_name values (default);
--or
insert into table_name values (); --均省略，即隐式插入

/*
如果省略没有默认值的列，该列的值就是null；
如果省略设置not null的列，insert就会出错。
*/

--复制表
create table goods_copy
(id	char(4)		 not null,
 name		varchar(100) not null,
 price		integer		,
 reg_date	date		,
 primary key (id));
insert into goods_copy (id,name,price,reg_date)
select id,name,price,reg_date from goods;
-- group by等语句都可以用

4.2 delete

--drop table完全删除表；delete留下表只删数据。

delete from table_name; --删除全部行
delete from table_name where ...; --删除部分行

/*
delete只能使用where子句，不能使用group by,having,order by.
*/

truncate table_name; --只删除表的全部数据，比delete快

4.3 update

update table_name set column = value;  --改变一列
update table_name set column = value where ...; --改变部分列

eg.
update goods set price=price*10
update goods set price=null

--更新多列
update table_name set column_1 = value_1,column_2=value_2; --所有dbns适用
update table_name set (column_1,column_2) = (value_1,value_2); -postgreSQL,db2

4.4 事务

事务transaction：需要在同一个处理单元中执行的一系列更新处理的集合。

事务开始语句； 
	dml;
	...
食物结束语句（commit提交/rollback取消处理） --两种在所有dbms中通用
--commit需要小心确认语句无误
--即使执行了delete，也可以rollback恢复。
/*
事务开始语句
SQL server,postgreSQL-----begin transaction
mysql --------------------start transaction
oracle,db2----------------无
*/

ACID特性：

原子性Atomicity：事务结束，更新处理要么全执行，要么全不执行。

一致性Consistency：事务中的处理要满足约束。

隔离性Isolation：不同事务间不干扰。

持久性Durability：事务结束后数据得以保存，即使系统故障也能恢复。