mysql学习--mysql必知必会
时间：2022-03-15 13:34
?
上图为数据库操作分类：
?
?
下面的操作參考（mysql必知必会）
创建数据库 运行脚本建表：
mysql> create database mytest;
Query OK, 1 row affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use myte
ERROR 1049 (42000): Unknown database ‘myte‘
mysql> use mytest;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> source /home/huangcd/mysql_scripts/create.sql
mysql> source /home/huangcd/mysql_scripts/populate.sql
?
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+------------------+
6 rows in set (0.02 sec)
删除反复行：
mysql> select vend_id from products;
+---------+
| vend_id |
+---------+
|??? 1001 |
|??? 1001 |
|??? 1001 |
|??? 1002 |
|??? 1002 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1003 |
|??? 1005 |
|??? 1005 |
+---------+
14 rows in set (0.00 sec)
mysql> select distinct vend_id from products;
+---------+
| vend_id |
+---------+
|??? 1001 |
|??? 1002 |
|??? 1003 |
|??? 1005 |
+---------+
4 rows in set (0.04 sec)
?
?返回结果中不多于5行：
mysql> select prod_name from products
??? -> limit 5;
+--------------+
| prod_name??? |
+--------------+
| .5 ton anvil |
| 1 ton anvil? |
| 2 ton anvil? |
| Detonator??? |
| Bird seed??? |
+--------------+
5 rows in set (0.00 sec)
?从第5行開始的5行：
mysql> select prod_name
??? -> from products
??? -> limit 5,5;
+--------------+
| prod_name??? |
+--------------+
| Carrots????? |
| Fuses??????? |
| JetPack 1000 |
| JetPack 2000 |
| Oil can????? |
+--------------+
5 rows in set (0.00 sec)
order by 子句对一个或者多个列进行输出排序：
mysql> select prod_name
??? -> from products
??? -> order by prod_name;
+----------------+
| prod_name????? |
+----------------+
| .5 ton anvil?? |
| 1 ton anvil??? |
| 2 ton anvil??? |
| Bird seed????? |
| Carrots??????? |
| Detonator????? |
| Fuses????????? |
| JetPack 1000?? |
| JetPack 2000?? |
| Oil can??????? |
| Safe?????????? |
| Sling????????? |
| TNT (1 stick)? |
| TNT (5 sticks) |
+----------------+
首先按price排序，仅仅有price同样时，才依照名称排序：
mysql> select prod_id, prod_price, prod_name
??? -> from products
??? -> order by prod_price, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name????? |
+---------+------------+----------------+
| FC????? |?????? 2.50 | Carrots??????? |
| TNT1??? |?????? 2.50 | TNT (1 stick)? |
| FU1???? |?????? 3.42 | Fuses????????? |
| SLING?? |?????? 4.49 | Sling????????? |
| ANV01?? |?????? 5.99 | .5 ton anvil?? |
| OL1???? |?????? 8.99 | Oil can??????? |
| ANV02?? |?????? 9.99 | 1 ton anvil??? |
| FB????? |????? 10.00 | Bird seed????? |
| TNT2??? |????? 10.00 | TNT (5 sticks) |
| DTNTR?? |????? 13.00 | Detonator????? |
| ANV03?? |????? 14.99 | 2 ton anvil??? |
| JP1000? |????? 35.00 | JetPack 1000?? |
| SAFE??? |????? 50.00 | Safe?????????? |
| JP2000? |????? 55.00 | JetPack 2000?? |
+---------+------------+----------------+
14 rows in set (0.05 sec)
默认排序方式是升序，若用降序必须用DESCkeyword：
mysql> select prod_id, prod_price, prod_name from products order by prod_price DESC;
+---------+------------+----------------+
| prod_id | prod_price | prod_name????? |
+---------+------------+----------------+
| JP2000? |????? 55.00 | JetPack 2000?? |
| SAFE??? |????? 50.00 | Safe?????????? |
| JP1000? |????? 35.00 | JetPack 1000?? |
| ANV03?? |????? 14.99 | 2 ton anvil??? |
| DTNTR?? |????? 13.00 | Detonator????? |
| TNT2??? |????? 10.00 | TNT (5 sticks) |
| FB????? |????? 10.00 | Bird seed????? |
| ANV02?? |?????? 9.99 | 1 ton anvil??? |
| OL1???? |?????? 8.99 | Oil can??????? |
| ANV01?? |?????? 5.99 | .5 ton anvil?? |
| SLING?? |?????? 4.49 | Sling????????? |
| FU1???? |?????? 3.42 | Fuses????????? |
| FC????? |?????? 2.50 | Carrots??????? |
| TNT1??? |?????? 2.50 | TNT (1 stick)? |
+---------+------------+----------------+
14 rows in set (0.02 sec)
mysql> select prod_id, prod_price, prod_name from products order by prod_price DESC, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name????? |
+---------+------------+----------------+
| JP2000? |????? 55.00 | JetPack 2000?? |
| SAFE??? |????? 50.00 | Safe?????????? |
| JP1000? |????? 35.00 | JetPack 1000?? |
| ANV03?? |????? 14.99 | 2 ton anvil??? |
| DTNTR?? |????? 13.00 | Detonator????? |
| FB????? |????? 10.00 | Bird seed????? |
| TNT2??? |????? 10.00 | TNT (5 sticks) |
| ANV02?? |?????? 9.99 | 1 ton anvil??? |
| OL1???? |?????? 8.99 | Oil can??????? |
| ANV01?? |?????? 5.99 | .5 ton anvil?? |
| SLING?? |?????? 4.49 | Sling????????? |
| FU1???? |?????? 3.42 | Fuses????????? |
?
?价格最高的一个：
mysql> select prod_price
-> from products
-> order by prod_price desc
-> limit 1;
+------------+
| prod_price |
+------------+
| 55.00 |
+------------+
1 row in set (0.12 sec)
?
?
?
mysql> select prod_name, prod_price
-> from products
-> where prod_price between 5 and 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
5 rows in set (0.09 sec)
?空值检查：
mysql> select prod_name
-> from products
-> where prod_price is null;
Empty set (0.01 sec)
mysql> select cust_id
-> from customers
-> where cust_email is null;
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
2 rows in set (0.00 sec)
?
?or操作符：
mysql> select prod_name,prod_price from products where vend_id =1002 or vend_id = 1003;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.05 sec)
?
?in操作符：
mysql> select prod_name, prod_price
-> from products
-> where vend_id in (1002, 1003)
-> order by prod_name;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Detonator | 13.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.07 sec)
in最大的长处是能够包括其它select子句。
?
not操作符：
mysql> select prod_name, prod_price
-> from products
-> where vend_id not in (1002, 1003)
-> order by prod_name;
+--------------+------------+
| prod_name | prod_price |
+--------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
+--------------+------------+
5 rows in set (0.00 sec)
?
?在搜索子句中使用通配符，必须使用like操作符。like指示后跟的搜索模式是通配符而不是直接相等匹配。
?
?
?%通配符：随意字符出现随意次数
mysql> select prod_id, prod_name
-> from products
-> where prod_name like ‘jet%‘
-> ;
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.05 sec)
mysql> select prod_id, prod_name from products where prod_name like ‘%anvil%‘;
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
3 rows in set (0.00 sec)
_通配符：匹配随意单个字符
mysql> select prod_id, prod_name from products where prod_name like ‘_ton anvil‘;
Empty set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name like ‘_ ton anvil‘;
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
?
mysql必知必会前八章如上。
?
?
