数据库
数据库基础
数据库是保存有组织的容器,而mysql,redis等是管理数据库的软件
表
-
是某种特定数据类型的结构化清单
-
表名
- 每个表都有一个自己的表名
- 在同一个数据库中只能有唯一的一个表名,在不同的数据库中可以用相同的表名
-
模式:描述关于库和表的布局的以及特征的信息
-
列和数据类型
- 表时由一个或多个列组成的
- 一个列表示一个字段
- 注意:把数据分解和合并是很重要的,可以为后期的数据分析提供方便,需要根据具体情况具体分析
- 数据类型:每一个列只能有一个数据类型,用合适的数据类型,可以优化磁盘的使用
- 在确定数据类型时需要注意:数据类型以及名称和你当前的sql兼不兼容,在不同的数据库中使用的数据类型的名称可能不一样.在创建表结构是要注意这些差异
-
行
- 行就是一个记录,或是一个样本
- 记录是行的别名,行才是正确的术语
-
主键
-
就是某一列可以作为行的唯一标识,如果没有主键对表操作增删改查时会非常困难
-
应该总时定义主键
- 任意两行都没有相同的主键值
- 每一行都必须存在主键不能为空(null
- 主键的值不允许修改或更新
- 主键值不能重复用(如果删除后也不能用)
- 如果应用多个列做为主键,这些列的组合也必须满足以上条件(这些列的值可以不唯一)
-
SQL
-
SQL 是专门用来和数据库的交互的一门语言
-
SQL优点
- 几乎所有的数据库(DBMS)都支持SQL
- SQL简单易学,它的语句全部都是由很强的描述性的英语单词组成,而且这些单词的数目不多.
- 虽然SQL看上去简单,其实是一种强有力的语言,灵活运用可以做到很复杂的数据库操作
-
在数据库增删改查时用SQL的关键字进行构成
-
SQL的关键字不能是列表的名
-
SQL语句由子句构成,有些子句是必须的,有些是可选的.一个子句通常是由一个关键字和所提供的数据组成的
检索数据
; 多条语句需要用分号隔开,结束时也需要加上
SQL对大小写不敏感,但是列名和表名可能有所不同(取决于当时DBMS的配置)
使用空格,在SQL中所有的空格是忽略成一个空格的
SELECT xxx
- 利用SELECT从表中检索出xxx这个字段
- 当检索多列时需要用逗号分隔
- 可以用*号查询所有的字段(如果不是特别需要不建议使用,会降低查询的效率),但是可以查询未知的列
FROM
- FROM指出从哪个表中检索数据
DISTINCT
- 去重,直接放在列名的前面,会对全部的列有效,
TOP 或者 LIMIT
- 限制取出的数据的条数,LIMIT number OFFSET start, 从start开始取出number条
使用注释
- 因为我们写的SQL脚本太复杂,需要在SQL语句中进行注释,方便查看
- 可以在适当的文本位置,写一些描述性的说明
- 在我们需要测试一部分SQL脚本时,需要注释一部分代码
- 使用-- 两个连字符)镶嵌在行内,–之后的文本就是注释
- 可以使用# 注释一整行,有可能一些不支持
- 可以使用/* 注释*/多行注释
排序检索数据
在检索时如果没有明确指定排序顺序,则我们不应该假定检索出来的数据顺序是有意义的,
ORDER BY
- OREDER BY +列名
- 如果有多列是按先后顺序的优先级进行排序
- 在使用时要保证是最后一个执行,要不然会报错
- DESC 降序排序,放在需要降序的字段后面
- ASC 升序排序,基本上没有,默认升序
过滤数据
WHERE
-
数据根据WHERE子句中指定的搜索条件进行过滤.WHERE子句在表名(FROM子句)之后给出,WHERE的子句在该关键字之后
-
检查一列是否有指定的值,返回符合要求得到数据行
- = 等于
- <> 不等于
- != 不等于
- <小于
-
大于
-
=大于大于
- !< 不小于
- !>不大于
-
大于
-
= 大于等于
- BETWEEN start AND end 在指定的两个值之间
- IS NULL 为NULL值
高级过滤
组合WHERE子句
-
SQL允许有多个WHERE子句.这些子句有两种使用方法,即以AND子句或OR子句
-
操作符:用来联结和改变WHERE子句的关键字,称之为逻辑操作符
-
AND:满足所有给定的条件
-
OR:满足任意给定条件的行
-
AND的优先级高于OR
-
使用()圆括号可以消除奇异
-
IN操作符,用圆括号包裹,逗号分隔
- 在很多合法选项中,语法更清晰,更直观
- 在组合语句中,求值的顺序更容易管理
- 在IN和OR的一组查询中IN执行的更快
- IN 最大的优点可以包含其他的SELECT语句,能够动态的建立WHERE子句
-
NOT操作符
- 唯一的功能就是否定其后所跟随的任何条件
用通配符进行过滤
通配符:用来代替一部分值的特殊字符串
必须在WHERE子句的LIKE子句
百分号(%)
- 匹配任意个字符
- 在使用时可能会匹配到数据库中自动对齐的空格,尽量不要使用以字符结尾的子句
下划线(_)
- 匹配任意一个字符
方括号([])
- 匹配在方括号中的一个字符集,只能匹配一个所在位置的字符
脱字符(^)
- 一般在方括号里连用,表示不匹配方括号的字符集
使用通配符的代价,因为是模糊搜索,会牺牲性能,需要更长的时间
- 不要过度使用通配符,如果其他关键字可以实现的话就使用其他的关键字
- 在使用搜索模式,尽量不要在开始就使用通配符,这样搜索起来会很慢
- 注意通配符的位置,放错了会返回不需要的数据
创建计算字段
字段(field)计算字段在客户端显示的和列是一样的
拼接字段
-
拼接就是把多个值凑成单个值
-
使用+号或||来连接,中间可以连接一些其他的字符串
-
清除数据左右两边的空格用 TRIM()这个函数
- LRTIM 左边
- RTRIM 右边
别名
- AS
- 导出列
执行算数计算
-
可以直接在语句中计算,尽量使用别名,郝清芬,好调用
-
SQL算数操作符
-
-
-
- /
-
-
-
使用函数处理数据
可移植性:编写的代码可以在多个系统上运行
处理文本字符串的文本函数
-
清除数据左右两边的空格用 TRIM()这个函数
- LRTIM 左边
- RTRIM 右边
-
UPPER()可以将文本转换成大写
-
LOWER() 转成小写
-
LEFT() 返回字符串左边的字符
-
RIGHT()返回字符串右边的字符
-
SOUNDEX()返回字符串的SOUNDEX值
用于数值计算的数据函数
- ABS() 绝对值
- COS() 角度余弦值
- EXP() 返回一个数的指数值
- PI 返回圆周率
- SIN()
- SQRT() 一个数的平方根
- TAM()
处理时间和日期的日期时间函数
- to_number()把提取出来的时间成分转成数值
- to_data () 提取日期成分
返回DBMS信息的系统函数
总汇数据
聚集函数
-
对某些行进行函数运算,返回一个值
-
AVG() 平均值
-
COUNT() 行数
-
MAX() 某列的最大值
-
MIN() 最小值
-
SUM() 某列的和
-
参数
- DISTINCT和ALL是相对的ALL是默认
-
组合聚集可以同时使用
分组数据
使用分组可以将数据分成多个逻辑组,对每组的列进行聚集
和WHERE子句一样
GROUP BY
- 在该子句中可以使用多个列进行嵌套分组,
- 指定的所有都一起计算(所以不能从别的列取数据
- 在GROUP BY的子句中每一个列必须是检索和有效的表达式.,如果在SELECT中使用表达.必须在GROUP BY中指定相同的表达式
- GROUP BY 中一般不允许GROUP BY 中带有长度可变的类型的(如文本字段或备注)
- 除了聚集计算外,SEIECT中的每一列都必须在GROUPBY中给出
- 如果在分组的列中出现NULL或多个NULL会把NULL全部分到一组
- GROUP BY在WHERE之后ORDER BY之前
HAVING
-
WHERE进行行过滤,HAVING是对组过滤,HAVING支持WHERE的所有子句
- SELECT cust_id, COUNT() AS orders FROM Orders GROUP BY cust_id HAVING COUNT() >= 2;
-
可以嵌套先用WHERE对行过滤,在对组过滤
-
想要一个有序的,必须用ORDER BY 来进行排序,不要依赖GROUP BY 的排序
SELCET子句的顺序
- SELCET 要返回的列或表达式 必须使用
- FROM 是指定数据的来源(或者检索的表) 仅在从表中选择数据时使用
- WHETE 对行过滤的 否
- GROUP BY 分组说明 仅在按组计算聚集时用
- HAVING 对组进行过滤,在分组的情况下是WHERE的代替
- ORDER BY 对输出进行排序
子查询
查询(query):所有 SQL语句都是查询,但是此术语一般指SELECT语句
SQL还容许创建子查询,就是在嵌套在其他查询中
- SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = ‘RGAN01’);
嵌套的层次没有限制
作为子查询只能返回单列,返回多列后会报错
在使用子查询从多个表中进行查询时需要完全限制列表名,这样会准确的找到相应的列,进行子查询
联结表
理解关系表
- 当多个行里出现,重复的列信息,这样重复的信息既浪费查询时间,又浪费存储空间
- 当其中的重复的信息出现变动,只需要修改关系表中的信息,不必要修改主表的大量的信息
- 当输入时会出现,不一样的输入,会让我们利用这些信息时,当相同的信息,产生不一致时会产生不必要的麻烦
- 关系数据库就是把信息分到多个表中管理,用某些相同的信息进行互相关联,所以才叫关系数据库
- 总之:关系数据可以有效的储存,方便的处理,因此关系数据库的可伸缩性比非关系数据库好
联结是一种机制,用一条SELECT语句中关联表,因此称之为联结.使用特殊的语法,可以连接多个表返回一组输出
在FROM中引入多个表
再用WHERE过滤对数据进行过滤,比如用两个表的关系列表,进行过滤(用WHERE过滤又叫做等值联结)
WHERE子句的重要性
-
在一条SELECT的语句中联结多个表,相应的关系是在运行中构建的.
-
多个表之间没有真实的物理联结
-
如果没有WHERE子句,第一个表会和第二个表的每一行进行匹配,不管逻辑上是否匹配,所以需要WHERE进行对这个联合表进行过滤,要不然就会一直匹配直到所有的表都匹配完,匹配是相当于笛卡尔积如果设每个表有n行,最后匹配出来的表就有(n1*n2…nn)行
- SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;
-
叉联结:笛卡尔积的联结有叫叉联结
内联结
-
INNER JOIN
- 当使用内连接时需要用ON代替WHERE
创建高级联结
使用表的别名
-
SQL可以给列,计算列,表起别名
-
可以缩短SQL语句的长度
-
容许在一条SELECT语句中使用多次相同的表名
- SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = ‘RGAN01’;
SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = ‘Jim Jones’;
不同类型的联结
-
自联结
- 提示:用自联结而不用子查询 自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查 询语句。虽然终的结果是相同的,但许多 DBMS处理联结远比处理 子查询快得多。应该试一下两种方法,以确定哪一种的性能更好
- 自联结就是用联结表代替子查询
-
自然联结
- 系统不会完成,相同的列只会返回一次
-
外联结
-
联结包含了那些没有在相关表中的行
- SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
-
外连接包含左连接(LETF OUTER JOIN)和右连接(RIGTH)
- 联结以某一个表作主表
-
-
我们一般使用的是内联结,但是外联结也是有效的
-
关于确切的联结语法,应该查看具体的文档,了解DBMS支持哪种语法
-
保证联结的正确的联结条件
-
应该总是提供联结条件的,要不然会出现笛卡尔积
-
在使用联结多个不同的联结方式联结多个表时,需要确保,每个联结的正确性
组合查询
利用UNIN操作多条SELECT语句返回结果集
在一次查询中需要从多个表中获得结构数据
对一个表多次查询,返回一个查询结果
如何有多个WHERE子句,都可以用组合查询
UNION
- UNION必须是有两个以上的SELECT语句组合而成,联结SELECT的是UNION
- UNION中每个查询必须包含相同的列,表达式,聚集函数
- 列数据的类型必须是一样的,或者可以隐含转换的类型
- 在组合查询中会自动去重,如果需要不去重用UNION ALL 联结,需要满足每一个条件的行出现就,只能用NUION ALL
- 在使用UNION需要排序是只需要再最后一个SELECT语句后面ORDER BY,不允许有多个排序
- 用在多个表时可以用列的别名来代替
插入数据
INSERT
-
用来插入行或添加到数据库包
-
插入完整的行
- 语句中要求指定的表和插入到新行的值
- INSERT INTO 要插入的表 VALUES(插入的值)
- 要求每一行都要有插入的值,要和插入的列的次序对应,要不然会以NULL代替,而起必须这个列是可以有空值的,要不然会报错
- INSERT INTO 要插入的表(要插入的列) VALUES(插入的值,插入对应的列,如果不插入用NULL代替,所对应的列必须是可以为空)
-
插入行的一部分
- INSERT INTO 要插入的表(要插入的列,如果有些列选择不插入,就不要写) VALUES(插入的值,插入对应的列)
- 不插入的列必须满足:
该列允许为空
或在表定义中给出该列的默认值,如果没有传插入值就用默认值代替
-
插入每个查询结果
- 用SELECT语句代替VAULES
- 但是要插入的列要和查询到的列对应
-
-
实际上DBMS不会关心数据来自哪里,要满足插入的条件就可以,投入对应的列中
-
INSERT 一次只能插入行
INSERT SELECT 可以一次插入查询到的所有行 -
从一个表复制到另一个表中
- SELECT * INTO CustCopy FROM Customers;
如果只需要几个列,就用列名代替* - CREATE TABLE CustCopy AS SELECT * FROM Customers;
这样也可以做到复制表的 - 任何用到SELECT语句都可以用WHERE和GROUP BY
- 可以利用多个表联合插入
- 不管从多少个表中检索数据,数据都只能插入到一个表中
- SELECT * INTO CustCopy FROM Customers;
-
在使用INSERT时需要一定的权限
更新数据和删除数据
UPDATE
-
更新表中某一行
-
更新表中所有的行
-
UPDATE语句的组成
- 要更新的表名
- 列名和他们的新值
- 确定要更新哪一行的特定条件
- UPDATE Customers SET cust_email = ‘kim@thetoystore.com’ WHERE cust_id = ‘1000000005’;
- 要更新多个列是时只需要用逗号隔开,跟在SET后面
- 删除某个值只需要更新数据为NULL
DLETE
-
从某个表中删除数据
-
从表中删除特定行
- DELETE FROM Customers WHERE cust_id = ‘1000000006’;
-
从表中删除所有的行
-
当遇到外键时如果该行还关联着关系,就会报错
-
DELETE删除的是一行,所以没有通配符
-
在使用更新和删除数据时一定要慎重,不要省WHERE过滤子句
若非真的打算删除,更新每一行,否则绝对要带上WHERE
保证每个表都有一个主键,尽可能都要有
在使用更新和删除时一定要用SELECT测试,以防止WHERE写错
使用强制实施引用完整性的数据库,防止删除与其他表关联的行
如果DBMS有防止不带WHERE子句的更新和删除的语句运行,如果有这特性,应该使用它
创建和操纵表
创建表
-
SQL不仅可以对表的数据进行操作,还可以对表或数据库进行操作,包括创建和处理
-
大多数DBMS有交互式创建和管理表和数据库的工具
-
表可以直接用SQL操作
-
CREATE TABLE 语句
-
表的基础创建
- 表的名字在CREATE TABLE 之后
- 表的名字和定义用逗号隔开
- CREATE TABLE Products ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL, prod_desc VARCHAR(1000) NULL );
- 当创建表的表名与已有的数据库重名时,会报错,需要手动删除,才能创建,防止覆盖
-
对NULL的使用
- 在数据库中就是没有值和空值
- 在定义列的属性是 NOT NULL 是不允许该列出现空字段,如果不指定一般是默认可以为空
- 空串是有值的不是NULL
-
创建表的时候要考虑到未来可能的需求,尽量不要对表的结构进行大的改动
-
可以对已有的的表添加列不过,对所增加的列的类型是有限制的
-
大部分DBMS是不允许删除列和对表的列进行更改
-
大部分DBMS是允许修改表的列名的
-
许多DBMS对已填入数据的列的限制更改,对没有填入的几乎没有限制
-
ALTER TABLE
-
在该语句后面给出需要改动的表名,这个表名必须存在,不然会报错
-
列出要更改的项
- ALTER TABLE Vendors ADD vend_phone CHAR(20);
- ALTER TABLE Vendors DROP COLUMN vend_phone;
-
更改和删除列,增加约束,外键都是类似的语法
-
对复杂的表更改需要的步骤
- 用新的列布局创建一个新的表
- 使用INSERT SELECT 语句,从旧的表中赋值数据到新表中,有必要的话可以用转化函数和计算字段
- 检验包含所需要数据的新表
- 重命名旧表,或删除
- 用旧表名重命名新表
- 根据需要,创建触发器,存储过程,索引外键
-
删除表
- DROP TABLE CustCopy;