MySQL--必知必会补充知识

最新推荐文章于 2021-08-29 23:23:25 发布

或是深海

最新推荐文章于 2021-08-29 23:23:25 发布

阅读量332

点赞数

分类专栏： MySQL学习笔记文章标签： MySQL学习

本文链接：https://blog.csdn.net/yj_445324989/article/details/102901239

版权

MySQL学习笔记专栏收录该内容

14 篇文章 0 订阅

订阅专栏

首先，先创建所需要的表。

CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL 
);
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

```sql
mysql> select * from products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
| RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
| RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+

CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

mysql> select * from vendors;
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| vend_id | vend_name       | vend_address    | vend_city  | vend_state | vend_zip | vend_country |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| BRS01   | Bears R Us      | 123 Main Street | Bear Town  | MI         | 44444    | USA          |
| BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA          |
| DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA          |
| FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA          |
| FNG01   | Fun and Games   | 42 Galaxy Road  | London     | NULL       | N16 6PS  | England      |
| JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      | NULL       | 45678    | France       |
+---------+-----------------+-----------------+------------+------------+----------+--------------+

CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);
mysql> select * from orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | BR01    |      100 |       5.49 |
|     20005 |          2 | BR03    |      100 |      10.99 |
|     20006 |          1 | BR01    |       20 |       5.99 |
|     20006 |          2 | BR02    |       10 |       8.99 |
|     20006 |          3 | BR03    |       10 |      11.99 |
|     20007 |          1 | BR03    |       50 |      11.49 |
|     20007 |          2 | BNBG01  |      100 |       2.99 |
|     20007 |          3 | BNBG02  |      100 |       2.99 |
|     20007 |          4 | BNBG03  |      100 |       2.99 |
|     20007 |          5 | RGAN01  |       50 |       4.49 |
|     20008 |          1 | RGAN01  |        5 |       4.99 |
|     20008 |          2 | BR03    |        5 |      11.99 |
|     20008 |          3 | BNBG01  |       10 |       3.49 |
|     20008 |          4 | BNBG02  |       10 |       3.49 |
|     20008 |          5 | BNBG03  |       10 |       3.49 |
|     20009 |          1 | BNBG01  |      250 |       2.49 |
|     20009 |          2 | BNBG02  |      250 |       2.49 |
|     20009 |          3 | BNBG03  |      250 |       2.49 |
+-----------+------------+---------+----------+------------+

限制结果

mysql> select prod_name from products limit 5;
+--------------------+
| prod_name          |
+--------------------+
| 8 inch teddy bear  |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Fish bean bag toy  |
| Bird bean bag toy  |
+--------------------+

mysql> select prod_name from products limit 5 offset 5;
+---------------------+
| prod_name           |
+---------------------+
| Rabbit bean bag toy |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+

limit指定返回的行数，limit带的offset指定从哪开始，第一个被检索的行数是第0行，而不是第1行，所以，limit 1 offset 1会检索出第2行。

拼接字段

例如，vendors表包含供应商名和地址信息。加入要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。
此报表需要一个值，而表中的数据存储在两列vend_name和vend_country中。此外，需要用括号将vend_country括起来，那么如何创建这个组合值呢？
Access和SQL Server使用+号，DB2、Oracle、Postgresql、SQLite、Open Office Base使用||。在MySQL中使用concat函数

mysql> select concat(vend_name,'(',vend_country,')') as cs
    -> from vendors
    -> order by vend_name;
+------------------------+
| cs                     |
+------------------------+
| Bear Emporium(USA)     |
| Bears R Us(USA)        |
| Doll House Inc.(USA)   |
| Fun and Games(England) |
| Furball Inc.(USA)      |
| Jouets et ours(France) |
+------------------------+

mysql --trim函数

RTIRM()–去掉字符串右边的空格、LTRIM()–去掉字符串左边的空格、TRIM()–去掉字符串左右两边的空格。

mysql> select rtrim('abc          ') as h;
+------+
| h    |
+------+
| abc  |
+------+
mysql> select ltrim('                 abc') as h;
+------+
| h    |
+------+
| abc  |
+------+
mysql> select trim('          abc           ') as h;
+------+
| h    |
+------+
| abc  |
+------+

执行算术计算

汇总orderitems表总物品的价格（单价乘以订单数量）

mysql> select prod_id,quantity,item_price,quantity*item_price as expanded_price
    -> from orderitems
    -> where order_num =20008;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01  |        5 |       4.99 |          24.95 |
| BR03    |        5 |      11.99 |          59.95 |
| BNBG01  |       10 |       3.49 |          34.90 |
| BNBG02  |       10 |       3.49 |          34.90 |
| BNBG03  |       10 |       3.49 |          34.90 |
+---------+----------+------------+----------------+

使用函数处理数据

函数	语法
提取字符串的组成部分	access用mid(),db2,oracle,postgresql,sqlite用substr(),mysql,sql server用substring()
数据类型转换	access,oracle使用多个函数，每种类型的转换都有一个函数；db2,postgresql使用casr();mariadb,mysql,sql server使用convert()
取当前日期	access使用now();db2,postgresql使用current_date;mariadb,mysql使用curdate(),oracle使用sysdate;sql server使用getdate();sqlite使用date()

文本处理函数

mysql> select vend_name,upper(vend_name) as vend_name_upcase
    -> from vendors
    -> order by vend_name;
+-----------------+------------------+
| vend_name       | vend_name_upcase |
+-----------------+------------------+
| Bear Emporium   | BEAR EMPORIUM    |
| Bears R Us      | BEARS R US       |
| Doll House Inc. | DOLL HOUSE INC.  |
| Fun and Games   | FUN AND GAMES    |
| Furball Inc.    | FURBALL INC.     |
| Jouets et ours  | JOUETS ET OURS   |
+-----------------+------------------+
mysql> select vend_name,lower(vend_name) as vend_name_upcase
    -> from vendors
    -> order by vend_name;
+-----------------+------------------+
| vend_name       | vend_name_upcase |
+-----------------+------------------+
| Bear Emporium   | bear emporium    |
| Bears R Us      | bears r us       |
| Doll House Inc. | doll house inc.  |
| Fun and Games   | fun and games    |
| Furball Inc.    | furball inc.     |
| Jouets et ours  | jouets et ours   |
+-----------------+------------------+

常用的文本处理函数：

函数	说明
left()(或使用子字符串函数)	返回字符串左边的字符
length()(也是用datalength()或len())	返回字符串的长度
lower()(access使用lcase())	将字符串转换为小写
ltrim()	去掉字符串左边的空格
right()(或使用子字符串函数)	返回字符串右边的字符
rtrim()	去掉字符串右边的空格
soundex()	返回字符串的soundex值
upper()(access使用ucase())	将字符串转换为大写

soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节，使得等对字符串进行发音比较而不是字母比较。

例如，customers表中有一个顾客kids place，其联系名为michelle green，但是如果这是错误的输入，此联系名实际上应该是,icheal green，显然，按照正确的联系名搜索不会返回数据。

mysql> select cust_name,cust_contact
    -> from customers
    -> where cust_contact='micheal green';
Empty set (0.00 sec)
-- 使用sondex
mysql> select cust_name,cust_contact
    -> from customers
    -> where soundex(cust_contact)=soundex('micheal green');
+------------+----------------+
| cust_name  | cust_contact   |
+------------+----------------+
| Kids Place | Michelle Green |
+------------+----------------+

插入检索出的数据

例如有一张customers表和一张custnew表，表的结构一样，里面存储的数据分别为：

mysql> select * from customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
| 1000000006 | toy land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | NULL         | NULL               | NULL                  |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
mysql> select * from custnew;
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email       |
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------------+
| 001     | yuang   | yangquan     | taiyuan   | shanx      | 123      | china        | NULL         | 4@qq.com |
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------------+

我们想把custnew表中的数据合并到customers表中，不需要每次读取一行再将它用insert插入，可以如下进行：

mysql> insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
    -> select cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew;

mysql> select * from customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
| 1000000006 | toy land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | NULL         | NULL               | NULL                  |
| 001        | yuang       | yangquan             | taiyuan   | shanx      | 123      | china        | NULL               | 4@qq.com      |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

从一个表复制到另一个表

与insert select将数据添加到一个已经存在的表不同，select into将数据复制到一个新表。

mysql> create table custcopy as select * from customers;
Query OK, 9 rows affected (0.77 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from custcopy;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
| 1000000006 | toy land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | NULL         | NULL               | NULL                  |
| 001        | yuang       | yangquan             | taiyuan   | shanx      | 123      | china        | NULL               | 4@qq.com      |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

更新和删除数据

更新数据

更新（修改）表中的数据，可以使用update语句。可以更新表中的待定行，也可以更新表中的所有行。
基本的update语句有三部分组成：

要更新的表
列名和它们的新值
确定要更新哪些行的过滤条件

例如，客户1000000005现在有了电子邮件地址，因此它的记录需要更新，语句如下：

mysql> update customers set cust_email='kim@thetoystore.com'
    -> where cust_id='1000000005';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | kim@thetoystore.com   |
| 1000000006 | toy land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | USA          | NULL               | NULL                  |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | NULL         | NULL               | NULL                  |
| 001        | yuang       | yangquan             | taiyuan   | shanx      | 123      | china        | NULL               | 4@qq.com      |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

如果没有where子句，则会将所有的电子邮件都更新为kim@thetoystore.com 。
更新多个列的语法少有不同，语法如下：

mysql> update customers
    -> set cust_contact='sam roberts',cust_email='sam@toyland.com'
    -> where cust_id='1000000006';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | kim@thetoystore.com   |
| 1000000006 | toy land      | 123 Any Street       | New York  | NY         | 11111    | USA          | sam roberts        | sam@toyland.com       |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | USA          | sam roberts        | sam@toyland.com       |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | NULL         | sam roberts        | sam@toyland.com       |
| 001        | yuang       | yangquan             | taiyuan   | shanx      | 123      | china        | NULL               | 4@qq.com      |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

要删除某个列的值，可设置它为null（假定表定义允许null值）：

mysql> update customers
    -> set cust_email=null
    -> where cust_id='1000000005';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
| 1000000006 | toy land      | 123 Any Street       | New York  | NY         | 11111    | USA          | sam roberts        | sam@toyland.com       |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | USA          | sam roberts        | sam@toyland.com       |
| 1000000006 | Toy Land      | 123 Any Street       | New York  | NY         | 11111    | NULL         | sam roberts        | sam@toyland.com       |
| 001        | yuang       | yangquan             | taiyuan   | shanx      | 123      | china        | NULL               | 4@qq.com      |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

删除数据

删除数据用delete语句，一种可以删除表中特定的行，一种删除所有的行。

mysql> delete from customers where cust_id='1000000006';
Query OK, 3 rows affected (0.03 sec)

mysql> select * from customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
| 001        | yuang       | yangquan             | taiyuan   | shanx      | 123      | china        | NULL               | 4@qq.com      |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+

如果从表中删除所有行，不要使用delete。可使用truncate table语句，它完成相同的工作，而速度更快（不记录数据的变动）。

不同DBMS获得系统日期的函数

DBMS	函数/变量
Access	now()
DB2	current_date
MySQL	current_date()
Oracle	sysdate
PostgreSQL	current_date
SQL Server	getdate()
SQLite	date(‘now’)

更新表

使用alter table更改表结构，必须给出下面的信息：

在alter table之后给出要更改的表名（该表必须存在，否则将出错）；
列出要做哪些更改

mysql> alter table vendors
    -> add vend_phone char(20);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from vendors;
+---------+-----------------+-----------------+------------+------------+----------+--------------+------------+
| vend_id | vend_name       | vend_address    | vend_city  | vend_state | vend_zip | vend_country | vend_phone |
+---------+-----------------+-----------------+------------+------------+----------+--------------+------------+
| BRS01   | Bears R Us      | 123 Main Street | Bear Town  | MI         | 44444    | USA          | NULL       |
| BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA          | NULL       |
| DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA          | NULL       |
| FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA          | NULL       |
| FNG01   | Fun and Games   | 42 Galaxy Road  | London     | NULL       | N16 6PS  | England      | NULL       |
| JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      | NULL       | 45678    | France       | NULL       |
+---------+-----------------+-----------------+------------+------------+----------+--------------+------------+

事务

首先，要先知道几个术语：

事务（transaction）：指一组SQL语句
回退（rollback）：指撤销指定SQL语句的过程
提交（commit）：指将未存储的SQL语句结果写入数据库表
保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）

事务处理用来管理insert、update、delete语句。不能回退select语句（回退select语句也没有必要），也不能回退create或drop操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

使用rollback

mysql> delete from orders;
Query OK, 5 rows affected (0.03 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

使用commit

一般的SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作时自动进行的。
进行明确的提交用commit语句。

mysql> start transaction
    -> delete orderitems where order_num=12345
    -> delete orders where order_num=12345
    -> commit transation

从系统中删除订单122345.因为涉及更新两个数据库表orders和orderitems，所以使用事务处理块来保证订单不被部分删除。最后的commit语句仅在不出错时写出更改。如果第一条delete起作用，但第二条失败，则delete不会提交。

使用保留点

要支持回退部分事务，必须在事务处理块中的合适位置放占位符。这样，如果需要回退，可以回退到某个占位符。占位符也称为保留点。语句：savepoint delete1,其中，delete1为占位符的名字。要回退到保留点，可以进行如下操作：rollback to delete1
可以在SQL代码中设置任意多的保留点，越多越好。因为保留点越多，你就能越灵活地进行回退。

使用游标

SQL检索操作返回一组称为结果集（SQL查询所检索出的结果）的行，这组返回的行都是与SQL语句相匹配的行（零行或多行）。简单的使用select语句，没有办法得到第一行，下一行或前10行。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。不同的DBMS支持不同的游标选项和特性，常见的一些选项和特性如下：

能够标记游标为只读，使数据能读取，但不能更新和删除
能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）
能标记某些列为可编辑的，某些列为不可编辑的
规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问
指示DBMS对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化

使用游标有几个明确的步骤：

在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的select语句和游标选项。
一旦生命，就必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的DBMS）。

创建游标

我们创建一个游标来检索没有电子邮件地址的所有顾客。

mysql> declare custcursor cursor
    -> for
    -> select * from customers
    -> where cust_email is null

declare语句用来定义和命名游标，这里为custcursor。

索引

索引用来排序数据以加快搜索和排序操作的速度。可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行。但是创建索引前应该注意以下问题：

索引改善检索操作的性能，但降低了数据插入，修改和删除的性能，在执行这些操作时，DBMS必须动态地更新索引
索引数据可能要占用大量的存储空间
并非所有的数据都适合做索引
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引
可以在索引中定义多个列。

例如在products表的产品名列上创建一个索引：

mysql> create index prod_name_ind
    -> on products(prod_name);

prod_name_ind为索引名，必须唯一命名。

或是深海

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL--必知必会补充知识

首先，先创建所需要的表。CREATE TABLE Products( prod_id char(10) NOT NULL , vend_id char(10) NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc...
复制链接

扫一扫