DB_003_关系数据库标准语言（SQL）

SQL是什么？

　　SQL的全称是：Structured Query Language，是一种结构化查询语言，主要用于管理关系型数据库系统，对数据库进行增删改查操作。也是数据库脚本文件的扩展名。更多了解详见百度百科：https://baike.baidu.com/item/%E7%BB%93%E6%9E%84%E5%8C%96%E6%9F%A5%E8%AF%A2%E8%AF%AD%E8%A8%80/10450182?fromtitle=sql&fromid=86007&fr=aladdin

SQL语言特点：

1. 可以联机交互
2. 也可嵌入到高级程序c、c++、java、python等语言汇种，不同的语言中，使用方式基本相同。
3. 简单易学，操作灵活。核心语句只有9条：create、alter、select、insert、delete、update、grant、revoke
4. 是一种非过程化的语言

SQL语言操作分类：

1. 数据库定义语言。DDL，用于定义数据库的逻辑结构，含表、索引、视图
2. 数据操纵语言。DML，用于数据的查询、更新
3. 数据库控制语言。DCL，用于实现数据完整性和完全性、一致性控制的SQL语句

SQL语句创建数据库：

1. 创建空数据库，命名；
2. 打开SQL视图；
   1. 创建-》查询-》查询视图，功能区左侧，显示SQL视频，数据定义项
   2. 设计-》SQL视图button/数据定义
   3. SQL语句：每个单词间至少有一个空格或必要的逗号
3. 执行SQL
4. 保存SQL。快速工具栏保存
5. 使用已存的SQL，新建

实践_定义表结构：

　　1 create table语法：

create table 《表名》（

列名 《数据类型》，

列名n 《数据类型》，

primary key (列名)，

foreign key （列名） reference 表名 （主键列名））

1. 数据类型

　　2 alter修改表结构：

增加新列：alter table 表名 add 新列名 数据类型

修改某列的属性：alter 表名 alter column  要求改的列名  修改后的数据类型

删除列：alter table 表名 drop column 要删除的列名

　　3 删除表：

drop table 表名

　　4 创建索引：

create index 索引名 on 表名 （列名）

创建唯一索引：

　　create unique index 索引名 on  表名 （列名）

创建后，怎么查看索引？？？？？，如下：

　　选中表区-》字段区，可左右选中某个字段进行查看

遇到的问题：：

1 创建入库单表，有一个外键厂商编号，无法关联到厂商表，删除关系后，可以正常创建

2 创建入口明细，商品编号的外键也会报错，删除关系OK

！！数据库的增删改：

 

1. 增=插入数据。三种方式：
   1. 在表中插入数据：insert into 表名 values (值1，值2，，，值n)。列名与列值的顺序必须一一对应
   2. 在表中插入部分列的值：insert into 表名（列名1，列名2）values （列名1值，列名2值）
   3. 将values子句写成一个查询语句：insert into 表名（列名1） select 列名1 from 表名
2. 改=更新数据。
   1. 语法：update 表名 set 列名1=列名1值，列名2=列名2值 where 条件表达式
3. 删=删除数据。
   1. 语法1删除部分数据行：delete from 表名 where 条件表达式
   2. 语法2删所有数据：delete  from 表名 
   3. 注意：：删除仅删除数据，不删除表结构

！！SQL查询语句：

 语法：select  列名 from 表名 where 条件表达式。

1. select后的子句，有以下几种情况：
   1. 指定列名查询：select 列1,列2 from 表名 where 表达式
   2. *指定查询条件的所有列信息：select * from 表名 where 表达式
   3. 指定表达式或函数，并利用as 关键字指定输出结果的新列名
      1. SELECT 姓名,year(date())-year(出生年月) as 年龄 from 职工 where 性别="男"
      2. 
      3. year()函数。是一个内部函数，参数是一个日期类型，返回日期型数据的年份.如：year（"1994/8/15"）返回1994。date（）是当前日期的函数
2. where子句。
   1. 作用：指定从表中选择行的条件；
   2. 子句一般是：条件表达式
   3. 条件表达式：可以使用逻辑运算符（and、or、not）、比较运算符（>、>=、<、<=、=、<>）、算术运算符
3. from子句。
   1. 最用：指定表，也可指定多个表进行联接运算
   2. where子句给出连接条件
   3. SQL允许from子句中使用表的别名，代替表名，使用在条件表达式中
      1. 实践：select 订单号，客户名称，姓名，签单日期 from 客户 C，订单 D，职工 E where C.客户编号=D.客户编号 and E.职工号=D.职工号 and 签单日期 >=#2015/1/1#
4. 字符串的操作。
   1. Access系统只能用英文双引号，最为字符串的定界符
   2. 字符串的操作符：like、not like 
      • 常用的字符串符号：*表示任意字符串；？表示任何一个字符
        • " A* "表示任何以A开头的字符串
        • " *A* "表示任何包含A的字符串
        • " ？？ "表示只含有2个字符的字符串
        • " ？？* "表示至少包含两个字符的字符串
      • like应用。
        • SELECT 姓名 from 职工 where 姓名 like " 沈* "
        • SELECT 姓名 from 职工 where 姓名 like "??萍???????" < =>select 姓名 from 职工 where 姓名 like "李?生*"   →→特别注意，模糊匹配需要把该字段的长度，用?占位符填充。若输<10个字符，无结果
      • not like应用。
        • 查找所有不是经理的人：
          1. 方式一：SELECT 姓名 from 职工 where 职务 not like "经*"
          2. 方式二：SELECT 姓名 from 职工 where 职务 <> "经理"
   3. 其他特殊运算符：
      • between and 　　在某个范围之间。如：SELECT * from 职工 where 工资 between 3000 and 5000
      • not between and      不在某个范围之间。如：SELECT * from 职工 where 工资not  between 3000 and 5000
      • in     指定字段的值属于指定集合的元素。                                                                                                                                                     实践：select 客户名称,签单日期 from 客户 C,订单 D where C.客户编号=D.客户编号 and 签单日期 in(#2015/5/6#,#2015/5/12#)
      • not in      指定字段的值不属于指定集合的元素　
5. 排序order by。
   1. 作用：对查询结果的指定列排序。
   2. 两种方式：DESC降序   ASC升序。默认升序
   3. 实践：select 客户名称,签单日期 from 客户 C,订单 D where C.客户编号=D.客户编号 and 签单日期 in(#2015/5/6#,#2015/5/12#) order by 签单日期 desc
6. 并操作union
   1. 作用：两次查询结果，列相同的情况下，可进行并操作
   2. 两种形式：不去重union all；去重union
   3. 实践：SELECT 入库单号 as 单号,商品编号,单价 from 入库明细 where 商品编号="P1" union select 出库单号 as 单号,商品编号,单价 from 出库明细 where 商品编号="P1";
7. 聚集函数
   1. 作用：对一组值进行运算，并返回单个值的函数。常与查询语句group by 一起使用
      1. 求均值。SELECT avg(工资) as 平均工资 from 职工 where 职务 = "销售员"
      2. 求总和。SELECT sum(单价*订购数量) as 总金额 from 订单 D,订单明细 M where D.订单号=M.订单号 and 客户编号="C2"
      3. 求最大值和最小值。类型：数值型或其他类型。。
         1. 如数值型：SELECT max(工资) as 最高工资,min(工资) as 最低工资 from 职工       
         2. 如其他类型：SELECT max(出生年月) as 最晚年龄,min(出生年月) as 最早年龄 from 职工
      4. count函数：作用统计表中的行数。有三种格式
      1. count（*）：统计所有行数。SELECT count(*) as 总订单数 from 订单
      2. count（表达式）：统计表达式中的行数。SELECT count(身份证号) AS 有身份证信息的员工数量 FROM 职工;
      3. count（distinct（表达式））：去重后的字段总数。SELECT distinct(职务) AS 职务种类 FROM 职工;
8. 分组。group by和having。执行顺序1.where条件2.group针对结果列分类去重3.having针对结果列的条件判断
   1. group by：功能将在分组表达式上具有条
      1. SELECT 职务,count(职务) as 人数 from 职工 group by 职务
      2. SELECT 商品编号,max(订购数量) as 最大订购量,min(订购数量) as 最小订购量 from 订单明细 group by 商品编号
   2. having子句：where条件在groupby前生效，having条件在group by后生效
      1. SELECT 客户编号,sum(单价*订购数量) as 总金额 from 订单 D,订单明细 M where D.订单号=M.订单号 and 签单日期 between #2015-1-1# and #2015-12-31#  group by 客户编号 having sum(单价*订购数量) >18000
9. 空值Null
   1. SQL中允许使用null表示某个属性值为空
      1. 测试空值的方法。有两种：is null：空值；is not null：非空值
         1. is null：SELECT 职工号,姓名,出生年月 from 职工 where 出生年月 is null
         2. is not null：SELECT 职工号,姓名,出生年月 from 职工 where 出生年月 is not null
   2. 空值的处理原则。
      1. 算术运算+-*/的参数含有空值null，则运算结果为null
      2. 比较运算符><>=<= =<>中作为比较对象，则比较结果为“unknown”
      3. 除此外的其他情况，均为FALSE
      4. 聚集函数对null的处理
         1. sum、avg、max、min函数对参数中的null忽略不计，若所有参数值都是空值，则返回至null
         2. count函数对参数中的null值不计数，若所有参数值都是空值，则返回至0

SQL的联接操作

1.  内连接
   1. 等值连接。属性值相等的条件连接，不能消除重复列
   2. inner join
   3. ？？？？？？（内连接教材是错的。。）
2. 自然连接
   1. 以公共属性值相等为条件的连接，能消除重复列。是最常用的连接方式
   2. 通常的公共属性是一个表的主键和另一个表的外键，这恰好是关系之间的联系方法。SELECT 客户名称,订购数量 from ((客户 A inner join 订单 B (A.客户编号=B.客户编号)) inner join 订单明细 C (C.订单号=B.订单号)) inner join 商品 D (D.订单编号=C.订单编号) where 规格="245";
3. 左外连接
   1. left outer join：左表不相匹配的元组，右表为空值。select A.客户编号,客户名称,订单号 from 客户 A left outer join 订单 B on(A.客户编号=B.客户编号)

       

4. 右外连接
   1. right outer join：右表不匹配，左表值为空。同左连接相反。
5. 自连接
   1. 作用：利用别名实现同一个表的自连接
   2. select r.姓名,r.工资 from 职工 r,职工 s  where r.工资>s.工资 and s.姓名="何乃云"

嵌套查询

1. 又称子查询。子查询的结果也是一个关系。select  from where嵌套在一个子查询语句中 
   1. in：select 客户名称,联系人 from 客户 where 客户编号 in (select 客户编号 from 订单 where 订单号 in(select 订单号 from 订单明细 where 商品编号="P1"))
   2. not in：select 客户编号 from 订单 where 订单号 not in (select 订单号 from 订单明细 where 商品编号="P1")
   3. 聚合函数：select 客户编号 from 订单 where 订单号 in (select 订单号 from 订单明细 where 订购数量=(select max(订购数量) from 订单明细 where 商品编号="P2"))
   4. 算术运算：select 姓名,工资 from 职工 where 工资>(select 工资 from 职工 where 姓名="何乃云" )
2. 在其他语句中使用嵌套查询
   1. select子句嵌入子查询，子查询作为一个表达式。select 职工号,姓名,工资,(select avg(工资) from 职工) as 平均工资,工资-(select avg(工资) from 职工) as 差额 from 职工
   2. update语句中嵌入子查询
      1. update 订单明细 set 单价=单价*0.95 where 订单号 in (select 订单号 from 订单 where 客户编号="C1" )
3. delete语句嵌入子查询
   1. 更具参照实体的完整性，需要铣删除关联关系，再删除改用户。