数据库基础知识整理

来源：互联网发布：宜宾县网络电视台编辑：程序博客网时间：2024/06/05 05:32

一：基础概念

数据：对客观事物的符号表示，描述客观事物
数据库：存储数据的仓库，相关数据的集合
数据库管理系统（DBMS） MySQL， SQL Server， Oracle
数据库应用系统（Database Application System）

其中最重要的管理信息系统（Manpricement Information System，MIS）有几种：

    * 企业资源计划系统（ERP）    * 供应链管理系统（SCM）    * 客户关系管理系统（CRM）    * 知识管理系统（KM）

数据库系统优点：

* 数据结构化 * 共享性高* 独立性高* 安全性， 并发性， 可恢复性高

二：基础概念

现实世界事物 —（信息化）—> 概念模型 — (数据化)—> 数据模型

信息世界中的基本概念

* 实体(Entity)           学生* 属性（Attribute）      学号，姓名* 属性值（Value）        ID 1128，张三 * 实体型（Entity Type）  学生（学号，姓名）      实体与属性共同构成的* 实体集（Entity Set）   100个学生               同型实体的集合* 域（Domain）           性别的域（“男”，“女”）* 码（Key）              关键字，能唯一标识一个实体的属性或者属性集* 联系（Relationship）   事务内部或者事务之间

联系的类型
* 1:1 实体集A中的一个实体最多与实体集B中的一个对应，反之亦然。（观众和座位，丈夫和妻子）

* 1:n  A中一个对应B中多个， B中一个最多对应A中一个。  （公司与职员， 省和市）* m:n  多对多， （学生与课程）* 实际上，一对一是一对多的特例， 一对多是多对多的特例

E-R图（Entity-Relationship Approach）用以描述概念模型：实体集矩形，属性椭圆，联系菱形
数据模型三要素：数据结构（系统的静态特性），数据操作（系统的动态特性），数据完整性约束（数据及其联系的制约和依存规则）

关系模型的数据结构

* 关系（Relation）      一张二维表* 元组（Tuple）         一行，一个记录* 属性（Attribute）     一列，一个字段* 主码（Primary Key） 可唯一标识元组的属性或者属性集* 域名（Domain）      属性的取值范围* 分量（Element）     每一行对应的列的属性值， （元组中的一个属性值，如学号，姓名等）*   关系模式（Relationnal Schema） 关系名（属性1，属性2，属性3......） 学籍表（学号，姓名，性别，年龄，专业）

每一个分量必须是不可分的数据项，不可以表中还含有表

三：基础概念

组成：硬件，操作系统，DBMS，数据库应用开发平台，数据库应用程序

三级数据模式，两级映象功能：

* 外模式（用户级）* 逻辑模式（概念级）* 内模式（物理级，唯一）* 外模式/模式， 模式/内模式  （进行数据操作时改变映射即可，保证数据独立性）

数据库设计步骤

* 需求分析* 概念设计 （信息分析和定义）* 逻辑设计 （设计实现）* 物理设计 （物理数据库设计）* 实施* 运行，维护

在关系模型中，实体以及实体间的联系都是用关系来表示的。
关系模型的完整性规则：实体完整性（主码），参照完整性（外码），用户自定义完整性（外码约束，控制了主码躲在关系中的数据修改，因为别人参照你，你要是没了，外码表就没有参照了）
集合运算与关系运算
- 交，并，差，广义笛卡儿积
- 选择，投影，连接，除（等值连接选取属性值相等的元组，自然连接还要进一步取消重复列）

查询优化

* 先做选择运算     （减少中间结果）* 投影和选择同时做 （比避免重复扫描）* 提取公共子表达式 （避免重复查询，保存中间结果）* 笛卡儿积和选择操作合并为连接运算* 建立索引，预处理

范式

* 第一范式（1NF）：无重复的列* 第二范式（2NF）：属性完全依赖于主键* 第三范式（3NF）：属性不依赖于其他非主属性

三：SQL

表结构如下：

    1. tb_items 商品详情表：   name 商品名称，  price 商品价格，  number 商品库存，  city 商品产地    2. tb_customers 客户信息表：   cust_id ID编号，  cust_name 姓名，  cust_contact 联系方式    3. tb_orders 订单表：   order_id 订单号， order_date 订单时间， cust_id 外键指向客户信息表    4. tb_orderitems 订单详情表:   order_id 外键，  items_id 外键,  quantity 订单数量

关于SHOW的一些用法

    1. SHOW DATABASES                    显示数据库     2. USE db_shopping                   选择该数据库    3. SHOW TABLES                       显示表    4. SHOW COLUMNS FROM tb_items        显示该表字段    5. SHOW CREATE DATABASE db_shopping  显示创建该数据库时候的SQL语句    6. SHOW CREATE TABLE tb_items        显示创建该表时候的SQL语句

创建、操作表

    1. 创建orders表，并设置主外键约束        CREATE TABLE orders （            order_id  int NOT NULL AUTO_INCREMENT,            order_date datetime NOT NULL,            cust_id int NOT NULL,            PRIMARY KEY (order_id)            FOREIGN KEY (cust_id) REFERENCES customers(cust_id)            )    2. 更新表 添加操作人员列        ALTER TABLE orders ADD operator_id int(11)    3. 更新表 定义外键        ALTER TABLE orderitmes         ADD CONSTRAINT fk_orderitems_orders        FOREIGN KEY (order_id) REFERENCES orders (order_id)    4. 重命名表        RENAME TABLE orders3 TO orders1    5. 删除表 （删除整个表而不仅仅是内容）        DROP TABLE customers2

增、删、改数据

    1. 插入 （一台苹果笔记本记录）        INSERT INTO items（id，name,city,price,number）VALUES(NULL,'MAC笔记本电脑','美国',9288,100)        （id值为NULL因为主键设计的是自动增量(AUTO_INCREMENT),数据库自动帮你插入数值）    2. 更新行 将笔记本电脑库存改为200 （需要where过滤，不然会把所有商品的库存都改为200）        UPDATE items SET number = 200 WHERE name = 'MAC笔记本电脑'    3. 更新多个列         UPDATE items SET number = 300, price = 9888 WHERE name = 'MAC笔记本电脑'    4. 删除 （删除苹果笔记本记录）        DELETE FROM items WHERE name = 'MAC笔记本电脑'    5. TRUNCATE TABLE table_name 删除所有内容，速度更快（实际是删除原来的表重新创建一个而不是逐行删除）

SELECT的大致用法

    1. SELECT DISTINCT name FROM tb_items                          从该表name列中选择唯一的行（重复值只保留一个）    2. SELECT name FROM tb_items LIMIT 5                           从该表name列中选择不多于5行    3. SELECT name FROM tb_items LIMIT 5，5                        选择该表name列，从第5行开始往后查询5行（不包括第5行）    4. SELECT name FROM tb_items ORDER BY price                    选择name，按price排序输出    5. SELECT name FROM tb_items ORDER BY price，number            选择name，先按price排序，相同price的，再按number排序输出    6. SELECT name FROM tb_items ORDER BY price DESC               选择name，按price降序输出    7. SELECT name FROM tb_items ORDER BY price DESC，number       选择name，先按price降序排列，再按number升序最终输出    8. SELECT name FROM tb_items ORDER BY price DESC LIMIT 1       选择name，按number降序，输出第一行（即价格最高的） ORDER BY 和 LIMIT的顺序不能错

    9. SELECT name, price FROM tb_items WHERE city !="北京" ORDER BY price DESC LIMIT 3         选择产地不在北京的商品名称和价格，按价格降序排列出前三个（最贵的前三个产地不是北京的商品名称和价格）    10. SELECT name FROM tb_items WHERE price BETWEEN 300 AND 1500             选择价格在300到1500之间的（包含300和1500）商品名称    11. SELECT name FROM tb_items WHERE city IS NULL                       空值测试，检查具有NULL的列（选择商品产地为空的商品名称）    12. SELECT name FROM tb_items WHERE city IN ("北京", "广州")           选择商品产地北京或者广州的商品名称    13. SELECT name FROM tb_items WHERE city = "北京" OR city = "广州"  和上一条效果一样    14. SELECT name FROM tb_items WHERE city NOT IN ("北京", "广州")          NOT,否定后面跟的所有条件 (选择商品产地既不是北京也不是广州的商品名称)     15. SELECT name FROM tb_items WHERE price LIKE '%999'          通配符"%"，任何字符出现的任意次数，选择价格以999结尾的商品的名称    16. SELECT name FROM tb_items WHERE price LIKE '%888%'   选择价格中间含有888的商品的名称    17. SELECT name FROM tb_items WHERE name LIKE 'iphone_'          通配符"_"，匹配单个字符，不多不少，所以iphone5会出现，iphone6s不会    18. SELECT name FROM tb_items WHERE price LIKE '999'          匹配整个列值，完全是999才行

    19. SELECT name FROM tb_items WHERE price REGEXP '999'                正则表达式REGEXP，匹配包含999的文本，而不需要%通配符    20. SELECT name FROM tb_items WHERE price REGEXP '.99'                匹配任意一个字符，有点像 LIKE 中的通配符'_'，但是最后还是按照部分列值进行匹配而不是全部列值    20. SELECT name FROM tb_items WHERE price REGEXP '99|88'              正则表达式的OR匹配，选择价格中含有99，或者88文本的    21. SELECT name FROM tb_items WHERE number REGEXP '[456]00'           选择商品库存是400或者500或者600的商品的名称    22. SELECT name FROM tb_items WHERE number REGEXP '[^456]00'          选择商品库存不是这些的商品名称，与上一条相反，**相当于NOT**    23. SELECT name FROM tb_items WHERE number REGEXP '[1-10]00'          范围匹配，选择商品库存在100至1000之间的商品的名称 (字母亦可 [a-z])    24. SELECT name FROM tb_items WHERE name REGEXP '\ \='          匹配特殊字符'=' 需要两个反斜杠    25. **REGEXP和LIKE的区别**：    LIKE匹配整个串，REGEXP匹配子串，利用定位符，通过**'^'**开始每个表达式，**'$'**结束每个表达式，可以让 LIKE 和 REGEXP效果一样    '^'定位文档开头，'$'定位文档结束    26. SELECT name FROM tb_items WHERE nameREGEXP '^[0-9]'  选择名称以数字开头的商品的名称      （对比22注意此时'^'在[]外部）

关于字段

    27. SELECT name, price, number， price * number AS total_price FROM tb_items WHERE city = '北京'         AS使用别名，相当于在一个新的列中来显示（这个列并没有被创建在表中）    28. SELECT Concat(name, '  (', price,')') FROM items WHERE city = '北京'          利用**Concat（）关键字** 将商品名称和价格拼接在一个字符串里面，用括号和空格分隔开     29. SELECT Concat('商品名称:', name, '   商品产地:', city, '   商品价格:', price）AS '商品信息' FROM items WHERE price BETWEEN 1000 AND 4000 ORDER BY price        将价格在1000到4000之间的商品的名称，产地，价格按照价格从低到高的显示在’商品信息‘这一个列中    30. UPDATE tb_items SET number = number + 100   所有商品库存增加100

关于函数

    31. 函数没有SQL的可移植性强。不同DBMS的函数实现不相互支持。 四种基本的（1.文本函数  2.数值函数  3.时间函数  4.系统函数）    32. SELECT Upper（name） FROM tb_items   大写字母来显示商品名称    33. Left（） 返回串左边的字符， Length（） 返回串的长度， LTrim（）去掉左边的空格    34. SELECT name, price FROM tb_items WHERE Date(order_date) = '2016-09-01'         选择订单时间在这一天的商品名称和价格      Date（）返回日期格式为'yyyy-mm-dd'    35. SELECT name， price FROM tb_items WHERE Year(order_date) = 2016 AND Month(order_date) = 9;        选择订单时间在2016年9月的所有商品名称和价格 （不用考虑这个月有多少天）    36. 作用在行组上，返回某一列的汇总信息（**聚集计算语句**）            * AVG()    返回某列的平均值            * COUNT()  返回某列的行数            * MAX()    返回某列的最大值            * MIN()    返回某列的最小值            * SUM()    返回某列值的和    37. SELECT AVG(price) AS avg_price FROM tb_items              返回平均价格 （忽略值为NULL 的行）    38. SELECT AVG(price) AS avg_price FROM tb_items WHERE city = '北京'               返回产地在北京的商品平均价格    39. SELECT COUNT(*) AS num_items FROM tb_items                 返回总商品数，不管是否有空值    40. SELECT COUNT(city) AS num_items FROM tb_items              返回含有产地信息的商品总数，产地为空的会被忽略

关于分组

    41. SELECT city， COUNT(*) AS num_items FROM tb_items GROUP BY city;          按城市分组，查询每个城市的商品种类  （GROUP BY 必须在 WHERE 之后， ORDER BY 之前）    42. SELECT city, COUNT(*) AS num_items FROM tb_items GROUP BY city HAVING COUNT(*) >=3          按城市分组，聚集计算商品种类，过滤出种类在三种以上的城市    43. WHERE 与 HAVING 的区别 在于 WHERE 过滤行， HAVING 过滤分组 （WHERE在数据分组前过滤，HAVING在数据分组后过滤）    44. SELECT city, COUNT(*) AS num_items FROM tb_items WHERE number >= 1000 GROUP BY city HAVING COUNT(*) >= 2        选择具有两个商品以上（含），且库存在1000（含）以上的产地，先过滤库存（WHERE），分完组之后，再过滤个数（HAVING）    45. 语句顺序：   SELECT... FROM... WHERE... GROUP BY... HAVING... ORDER BY... LIMIT...

关于子查询

    46. 购买了3号商品的顾客姓名和联系方式  (子查询一般和IN连着用)            SELECT cust_name, cust_contact             FROM tb_customers WHERE cust_id IN (SELECT cust_id             FROM tb_orders WHERE order_id IN (SELECT order_id             FROM tb_orderitems WHERE items_id = 3));    47. 每个客户的id,姓名，城市，联系方式，以及总计订单数量 （完全限定的列名使用）            SELECT cust_id,                         cust_name,                         cust_city,                         cust_contact,                         (SELECT COUNT(*) FROM tb_orders WHERE orders.cust_id = customers.cust_id) AS total_orders                         FROM tb_customers ORDER BY total_orders;

关于连接

    48. 选择1001号订单的客户详情和商品详情            SELECT * FROM tb_customers, tb_orders, tb_orderitems, tb_items where tb_customers.cust_id = tb_orders.cust_id AND tb_orders.order_id = tb_orderitems.order_id AND tb_orderitems.items_id = tb_items.id AND tb_orders.order_id = 1001    49. 内部连接（等值连接）：   选择每个订单的客户姓名和联系方式 (**INNER JOIN ...  ON ...** 和where作用一样)            SELECT tb_orders.order_id, tb_orders.order_date, tb_customers.cust_name, tb_customers.cust_contact FROM tb_orders INNER JOIN tb_customers ON tb_customers.cust_id = tb_orders.cust_id     50. 选择订单号为1008，订单时间，和订单客户名            SELECT tb_orders.order_id, tb_orders.order_date, tb_customers.cust_name FROM tb_customers INNER JOIN tb_orders ON tb_orders.cust_id = tb_customers.cust_id WHERE tb_orders.order_id = 1008    51. 自联结和表别名：   查询下了1001号订单的客户下的所有单             SELECT o1.order_id, o1.cust_id FROM tb_orders AS o1, tb_orders AS o2 WHERE o1.cust_id = o2.cust_id AND o2.order_id = 1001   （此处select后面跟的和where后面跟的不能是同一个表，需要分开o1和o2）    52. 外部连接：   查询所有客户的订单信息，包括没有订单的客户 (没有订单的客户，订单号那一格显示为null)             SELECT tb_customers.cust_id, tb_customers.cust_name, tb_orders.order_id FROM tb_customers LEFT OUTER JOIN tb_orders ON tb_customers.cust_id = tb_orders.cust_id              ()    53. 查询所有客户，统计每个客户所下的订单数 (COUNT()函数)            SELECT tb_customers.cust_id, tb_customers.cust_name, COUNT(tb_orders.order_id) AS            orders_num FROM tb_customers, tb_orders WHERE tb_customers.cust_id = tb_orders.cust_id GROUP BY            tb_customers.cust_id    54. 组合查询：  将两个select语句并在一起合成单个结果集 UNION   （每个select都要包含相同的列、表达式或者聚集函数）            SELECT id,name,city,price FROM tb_items WHERE price >= 2000            UNION            SELECT id,name,city,price FROM tb_items WHERE city='广州'            ORDER BY price            自动取出重复的行，如果不想，则改成UNION ALL,   ORDER BY排序只能跟在最后一个SELECT语句之后

阅读全文

0 1