关系数据库标准语言SQL02

已于 2022-04-02 20:56:46 修改
阅读量1k 收藏
点赞数
分类专栏： SQL 文章标签： mysql sql 数据库
于 2022-04-02 20:26:01 首次发布
本文为博主原创文章，未经博主允许不得转载。
本文链接：https://blog.csdn.net/weixin_46322367/article/details/123920249
版权

关系数据库标准语言SQL01

文章目录

数据的维护

插入数据

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: 2  Duplicates: 0  Warnings: 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    |   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: 7  Duplicates: 0  Warnings: 0

mysql> 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: 7  Duplicates: 0  Warnings: 0

mysql> 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: 1  Changed: 1  Warnings: 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: 7  Changed: 7  Warnings: 0

mysql> 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: 1  Changed: 1  Warnings: 0

mysql> 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: 5  Duplicates: 0  Warnings: 0

mysql> 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: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX producers_name_age_idx ON producers_c(name,age);
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 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: 0  Duplicates: 0  Warnings: 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操作。

下面给出通过视图进行DML操作的规则：
(1)可以在简单视图上执行DML操作。
(2)如果在一个视图中包含了分组函数，或GROUP BY子句，或DISTINCT关键字，则不能通过该视图进行DELETE、UPDATE、INSERT操作。
(3)如果在一个视图中包含了由表达式组成的列，则不能通过该视图进行UPDATE、INSERT操作。
(4)如果在一个视图中没有包含引用表中那些不能为空的列，则不能通过该视图进行INSERT操作。

数据攻城小狮子
关注
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

数据攻城小狮子

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值