关系数据库标准语言SQL01

文章目录

• 数据的维护
• • 插入数据
  • • INSERT语句
    • 利用子查询向表中插入数据
  • 更新数据
  • • UPDATE语句
    • 利用子查询修改记录
  • 删除数据
  • • DELETE语句
    • 利用子查询删除行
• 索引与视图
• • 索引的创建与删除
  • • 创建索引
    • 查看索引
    • 删除索引
    • 使用索引时应注意的问题
  • 视图
  • • 为什么建立视图
    • 创建视图
    • 修改视图
    • 删除视图
    • 使用视图进行DML操作

数据的维护 插入数据 INSERT语句

mysql> SELECT * FROM producers; +----+------+---------+------+ | id | name | prod_id | age| +----+------+---------+------+ |1 | jack | BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy | RYL01|24 | +----+------+---------+------+ 3 rows in set (0.00 sec)mysql> INSERT INTO producers(name,prod_id,age) VALUES('stenfan','BR03',30); Query OK, 1 row affected (0.07 sec)mysql> SELECT * FROM producers; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | +----+---------+---------+------+ 4 rows in set (0.00 sec)

mysql> INSERT INTO producers VALUES(5,'toony',NULL,26); Query OK, 1 row affected (0.07 sec)mysql> SELECT * FROM producers; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|26 | +----+---------+---------+------+ 5 rows in set (0.00 sec)

mysql> INSERT INTO producers(name,prod_id,age) VALUES('stenfan','BR02',30),('jack','BNBG02',29); Query OK, 2 rows affected (0.09 sec) Records: 2Duplicates: 0Warnings: 0mysql> SELECT * FROM producers; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|26 | |6 | stenfan | BR02|30 | |7 | jack| BNBG02|29 | +----+---------+---------+------+ 7 rows in set (0.00 sec)

利用子查询向表中插入数据
复制一张producers表

mysql> CREATE TABLE producers_c -> SELECT * FROM producers; Query OK, 7 rows affected (0.41 sec) Records: 7Duplicates: 0Warnings: 0mysql> SELECT * FROM producers_c; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|26 | |6 | stenfan | BR02|30 | |7 | jack| BNBG02|29 | +----+---------+---------+------+ 7 rows in set (0.00 sec)

先将producers_c表中的记录全部删除，再使用INSERT命令将producers表中的记录插入到producers_c表。我是全部插入的，当然也可以用WHERE语句进行筛选再插入。

mysql> TRUNCATE TABLE producers_c; Query OK, 0 rows affected (0.58 sec)mysql> SELECT * FROM producers_c; Empty set (0.00 sec)mysql> INSERT INTO producers_c -> SELECT * FROM producers; Query OK, 7 rows affected (0.16 sec) Records: 7Duplicates: 0Warnings: 0mysql> SELECT * FROM producers_c; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|26 | |6 | stenfan | BR02|30 | |7 | jack| BNBG02|29 | +----+---------+---------+------+ 7 rows in set (0.00 sec)

更新数据 UPDATE语句
如果不用WHERE子句限定要更新的数据行，则会更新整个表的数据行。
注意：MySQL运行在SAFE_UPDATES模式下，该模式会导致在非主键条件下无法执行UPDATE或DELETE命令，需要执行命令"SET SQL_SAFE_UPDATES=0; "修改数据库模式。

mysql> SET SQL_SAFE_UPDATES=0; Query OK, 0 rows affected (0.00 sec)

更新toony的年龄为27

mysql> UPDATE producers SET age=27 WHERE name='toony'; Query OK, 1 row affected (0.13 sec) Rows matched: 1Changed: 1Warnings: 0

mysql> SELECT * FROM producers; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|28 | |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|27 | |6 | stenfan | BR02|30 | |7 | jack| BNBG02|29 | +----+---------+---------+------+ 7 rows in set (0.00 sec)

将表中所有人的年龄改为25

mysql> UPDATE producers_c SET age=27; Query OK, 7 rows affected (0.06 sec) Rows matched: 7Changed: 7Warnings: 0mysql> SELECT * FROM producers_c; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01|27 | |2 | tom| BR01|27 | |3 | lucy| RYL01|27 | |4 | stenfan | BR03|27 | |5 | toony| NULL|27 | |6 | stenfan | BR02|27 | |7 | jack| BNBG02|27 | +----+---------+---------+------+ 7 rows in set (0.00 sec)

利用子查询修改记录
把复制表的年龄先全部改为空值，再根据原表修改

mysql> UPDATE producers_c -> SET age=(SELECT age FROM producers WHERE name='lucy') -> WHERE name='lucy'; Query OK, 1 row affected (0.09 sec) Rows matched: 1Changed: 1Warnings: 0mysql> SELECT * FROM producers_c; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |1 | jack| BNBG01| NULL | |2 | tom| BR01| NULL | |3 | lucy| RYL01|24 | |4 | stenfan | BR03| NULL | |5 | toony| NULL| NULL | |6 | stenfan | BR02| NULL | |7 | jack| BNBG02| NULL | +----+---------+---------+------+ 7 rows in set (0.00 sec)

删除数据 DELETE语句
删除名字为jack的记录

mysql> DELETE FROM producers WHERE name='jack'; Query OK, 2 rows affected (0.10 sec)mysql> SELECT * FROM producers; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|27 | |6 | stenfan | BR02|30 | +----+---------+---------+------+ 5 rows in set (0.00 sec)

删除表中的所有记录

TRUNCATE TABLE producers_c; 或 mysql> DELETE FROM producers_c; Query OK, 7 rows affected (0.11 sec)mysql> SELECT * FROM producers_c; Empty set (0.00 sec)

利用子查询删除行
把数据重新插入回去

mysql> INSERT INTO producers_c -> SELECT * FROM producers; Query OK, 5 rows affected (0.07 sec) Records: 5Duplicates: 0Warnings: 0mysql> SELECT * FROM producers_c; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |2 | tom| BR01|25 | |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|27 | |6 | stenfan | BR02|30 | +----+---------+---------+------+ 5 rows in set (0.00 sec)

mysql> SELECT * FROM products; +----+---------+---------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+---------------------+------------+ |1 | BNBG01| Fish bean bag toy|3.49 | |2 | BNBG02| Bird bean bag toy|3.49 | |3 | BNBG03| Rabbit bean bag toy |3.49 | |4 | BR01| 8 inch teddy bear|5.99 | |5 | BR02| 12 inch teddy bear|8.99 | |6 | BR03| 18 inch teddy bear|11.99 | |7 | RGAN01| Raggedy Ann|4.99 | |8 | RYL01| King doll|9.99 | |9 | RYL02| Queen dool|9.99 | +----+---------+---------------------+------------+ 9 rows in set (0.00 sec)

mysql> DELETE FROM producers_c -> WHERE prod_id=(SELECT prod_id FROM products WHERE prod_price=5.99); Query OK, 1 row affected (0.07 sec)mysql> SELECT * FROM producers_c; +----+---------+---------+------+ | id | name| prod_id | age| +----+---------+---------+------+ |3 | lucy| RYL01|24 | |4 | stenfan | BR03|30 | |5 | toony| NULL|27 | |6 | stenfan | BR02|30 | +----+---------+---------+------+ 4 rows in set (0.00 sec)

索引与视图 索引的创建与删除 引入索引的目的是为了加快查询的速度。为表建立索引，既能减少查询操作的时间开销，又能减少I/O操作的开销。
创建索引
创建索引的方法有两种
1.系统自动建立，当用户在一个表上建立主键或唯一约束时，系统会自动创建唯一索引。
2.手工建立。
为producers_c表按生产者名字name建立索引，索引名为producers_name_idx

mysql> CREATE INDEX producers_name_idx ON producers_c(name); Query OK, 0 rows affected (0.50 sec) Records: 0Duplicates: 0Warnings: 0

mysql> CREATE INDEX producers_name_age_idx ON producers_c(name,age); Query OK, 0 rows affected (0.42 sec) Records: 0Duplicates: 0Warnings: 0

查看索引

SHOW INDEX FROM producers_c;

文章图片

删除索引

mysql> DROP INDEX producers_name_age_idx ON producers_c; Query OK, 0 rows affected (0.17 sec) Records: 0Duplicates: 0Warnings: 0

使用索引时应注意的问题
建立索引的目的是为了加快查询的速度，但这可能会降低DML操作的速度。因为每一条DML语句只要涉及索引关键字，DBMS就得调整索引。另外，索引作为一个独立的对象，需要消耗磁盘空间。如果表很大，其索引消耗磁盘空间的量也会很大。
下面给出为表建立索引的各种情况：
(1)表上的INSERT、DELETE、UPDATE操作较少。
(2)一列或多列经常出现在WHERE子句或连接条件中。
(3)一列或多列经常出现在GROUP BY或ORDER BY操作中。
(4)表很大，但大多数查询返回的数据量很少。因为如果返回数据量很大，就不如顺序地扫描这个表了。
(5)此列的取值范围很广，一般为随机分布。例如员工表的年龄列一般为随机分布，即几乎从18岁到60岁所有年龄的员工都有。再如性别列只有“男”和“女”两个不同值，因此无须建立索引。
(6)此列中包含了大量的NULL值。
如果在表上进行操作的列满足上面的条件之一，就可以为该列建立索引。
视图 视图View是由SELECT子查询语句定义的一个逻辑表，只有定义没有数据，是一个“虚表”。
为什么建立视图
1.提供各种数据表现形式，隐藏数据的逻辑复杂性并简化查询语句
2.提供某些安全性保证，简化用户权限的管理
3.对重构数据库提供了一定的逻辑独立性

mysql> CREATE VIEW AVG_product_prices -> AS -> SELECT COUNT(*) AS 总数,MIN(prod_price) AS 最低价,MAX(prod_price) AS 最高价,SUM(prod_price) AS 总和,AVG(prod_price) AS 平均价 -> FROM products; Query OK, 0 rows affected (0.14 sec)mysql> SELECT * FROM AVG_product_prices; +--------+-----------+-----------+--------+-----------+ | 总数| 最低价| 最高价| 总和| 平均价| +--------+-----------+-----------+--------+-----------+ |9 |3.49 |11.99 |62.41 |6.934444 | +--------+-----------+-----------+--------+-----------+ 1 row in set (0.00 sec)

创建视图

CREATE [OR REPLACE] VIEW 视图名[(别名[别名]...)] AS SELECT 语句 [WITH CHECK OPTION];

OR REPLACE:如果所创建的视图已经存在，MySQL系统会重建这个视图。
别名:为视图所产生的列定义的列名
WITH CHECK OPTION:所插入或修改的数据行必须满足视图所定义的约束条件
在子查询语句中不能包含ORDER BY子句

mysql> CREATE VIEW product_prices_B3 -> AS -> SELECT prod_id,prod_name,prod_price FROM products -> WHERE prod_price>3 -> WITH CHECK OPTION; Query OK, 0 rows affected (0.14 sec)mysql> INSERT INTO product_prices_B3 VALUES('MR01','toy dog',2); ERROR 1369 (HY000): CHECK OPTION failed 'learn.product_prices_b3' mysql> INSERT INTO product_prices_B3 VALUES('MR01','toy dog',5); Query OK, 1 row affected (0.07 sec)mysql> SELECT * FROM product_prices_B3; +---------+---------------------+------------+ | prod_id | prod_name| prod_price | +---------+---------------------+------------+ | BNBG01| Fish bean bag toy|3.49 | | BNBG02| Bird bean bag toy|3.49 | | BNBG03| Rabbit bean bag toy |3.49 | | BR01| 8 inch teddy bear|5.99 | | BR02| 12 inch teddy bear|8.99 | | BR03| 18 inch teddy bear|11.99 | | RGAN01| Raggedy Ann|4.99 | | RYL01| King doll|9.99 | | RYL02| Queen dool|9.99 | | MR01| toy dog|5.00 | +---------+---------------------+------------+ 10 rows in set (0.00 sec)

视图约束产品价格大于3，当插入小于3的数据时会报错
修改视图
取消约束条件检查

mysql> CREATE OR REPLACE VIEW product_prices_B3 -> AS -> SELECT prod_id,prod_name,prod_price FROM products -> WHERE prod_price>3 -> ; Query OK, 0 rows affected (0.15 sec)mysql> INSERT INTO product_prices_B3 VALUES('MR02','toy dog',2); Query OK, 1 row affected (0.07 sec)

取消约束条件后，插入数据成功

mysql> SELECT * FROM products -> ; +----+---------+---------------------+------------+ | id | prod_id | prod_name| prod_price | +----+---------+---------------------+------------+ |1 | BNBG01| Fish bean bag toy|3.49 | |2 | BNBG02| Bird bean bag toy|3.49 | |3 | BNBG03| Rabbit bean bag toy |3.49 | |4 | BR01| 8 inch teddy bear|5.99 | |5 | BR02| 12 inch teddy bear|8.99 | |6 | BR03| 18 inch teddy bear|11.99 | |7 | RGAN01| Raggedy Ann|4.99 | |8 | RYL01| King doll|9.99 | |9 | RYL02| Queen dool|9.99 | | 19 | MR01| toy dog|5.00 | | 23 | MR02| toy dog|2.00 | +----+---------+---------------------+------------+ 11 rows in set (0.00 sec)

删除视图

mysql> DROP VIEW product_prices_B3; Query OK, 0 rows affected (0.09 sec)

使用视图进行DML操作
用户可以通过视图对基本表中的数据进行DML的UPDATE、INSERT、DELETE操作。下面先介绍视图的分类，再介绍使用视图进行DML操作的规则。
视图可以分为简单视图和复杂视图，它们的区别如下。
1)简单视图
(1)数据是仅从一个表中提取的。
(2)不包含函数和分组数据。
(3)可以通过该视图进行DML操作。
2)复杂视图
(1)数据是从多个表中提取的。
(2)包含函数和分组数据。
(3)不一定能够通过该视图进行DML操作。
【SQL|关系数据库标准语言SQL02】下面给出通过视图进行DML操作的规则：
(1)可以在简单视图上执行DML操作。
(2)如果在一个视图中包含了分组函数，或GROUP BY子句，或DISTINCT关键字，则不能通过该视图进行DELETE、UPDATE、INSERT操作。
(3)如果在一个视图中包含了由表达式组成的列，则不能通过该视图进行UPDATE、INSERT操作。
(4)如果在一个视图中没有包含引用表中那些不能为空的列，则不能通过该视图进行INSERT操作。

推荐阅读

• 

  vlookup函数最详细的入门教程_快来学习vlookup怎么用吧
• 

  有人说《竹书纪年》毁三观不可信,到底怎么回事？
• 

  癌症|吃什么东西致癌？吃什么东西防癌？医生一次说全了
• 

  关雎作者 关雎介绍
• 

  关于诚信的句子
• 

  网页制作网站竞争优势分析
• 

  事业单位工资改革方案 事业单位工资改革2023
• 

  阴道炎|阴道炎反复发作老不好？你做错了这4件事，90％的女性都在犯
• 

  331土贝举【1期3班】刻意练习day|331土贝举【1期3班】刻意练习day 28
• 

  宝宝内热该怎么办，好苦恼啊！！
• 

  多吃这些食物抵御病毒侵袭
• 

  佳能70dc档位介绍 佳能70d的p档和cp档的区别
• 

  描述文件没有删除按钮
• 

  园艺和园艺技术的区别 什么叫医疗园艺，园艺技术是干什么的
• 

  那些堪称“神器”的PPT制作插件
• 

  华为鸿蒙能连接win10吗，鸿蒙系统连win10
• 

  红枣会产生黄曲霉素吗
• 

  尼康wifiapp 尼康新版wifi软件
• 

  WindowsQQ安卓版,腾讯qq2012安卓版无法下载
• 

  每日舆情监测数据分析,网络舆情数据分析

• SQL|关系数据库标准语言SQL01
• 正则表达式教程（30分钟让你精通正则表达式语法）
• 云栖号技术分享|专访李飞飞 （从清华附中高材生到阿里飞刀，一口井钻出「云原生」）
• java|主题（JAVA 桥接模式）
• SQL|如何实现高性能的数据处理\数据库性能优化
• 架构|Java大牛带你4小时开发一个SpringBoot+vue前后端分离博客项目
• 一文读懂 MySQL Explain 执行计划
• MySQL索引（一）
• MySQL索引（二）索引优化方案你都了解吗