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)
默认排序方式是升序,若用降序必须用DESC关键字:
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必知必会前八章如上。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。