mysql学习--mysql必知必会

时间:2022-06-01 20:47:16

  mysql学习--mysql必知必会

上图为数据库操作分类:

 

 

下面的操作參考(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学习--mysql必知必会mysql学习--mysql必知必会

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaHVhbmcyMDA5MzAzNTEz/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" alt="" />

 

 

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必知必会前八章如上。