准备练习环境
导入数据库
mysql> source /root/practice.sql库内容：
查询库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| crashcourse |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)进入库
mysql> use crashcourse;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查看表
mysql> show tables;
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+-----------------------+
6 rows in set (0.00 sec)
select命令
数据列限制输出行数
SELECT 列名 FROM 表名 LIMIT 行数；
mysql> SELECT prod_name FROM products LIMIT 5,5; -- DATA ROW 6 TO 10
+--------------+
| prod_name |
+--------------+
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
+--------------+
5 rows in set (0.00 sec)查询列数据
SELECT 字段列表 FROM 库名.表名;
数据列去重 SELECT DISTINCT
查看订单表的所有内容
mysql> select * from orders ;
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2023-09-01 00:00:00 | 10001 |
| 20006 | 2023-09-12 00:00:00 | 10003 |
| 20007 | 2023-09-30 00:00:00 | 10004 |
| 20008 | 2023-10-03 00:00:00 | 10005 |
| 20009 | 2023-10-08 00:00:00 | 10001 |
+-----------+---------------------+---------+
5 rows in set (0.00 sec)
排序 order by 列名 （desc）
根据列的值进行从小到大的排序输出，使用desc 排序结果为从大到小
1、排序结果对整行数据生效
2、使用多列作为排序的依据时，首先使用第一个指定的列对整体表排序，然后对于第一列 中存在的重复的列，使用指定的第二列数据进行小范围排序
mysql> SELECT prod_id,prod_name FROM products ORDER BY prod_name;
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
| FB | Bird seed |
| FC | Carrots |
| DTNTR | Detonator |
| FU1 | Fuses |
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
| OL1 | Oil can |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
14 rows in set (0.00 sec)
where子句
SELECT 列名1，.... FROM 表 WHERE 过滤条件；
比较符号：
= != > >= < <=
空 is null 表头下没有数据
非空 is not null 表头下有数据
mysql服务 使用关键字 null 或 NULL 表示表头没有数据
范围匹配：
in （值列表） //在…里
not in （值列表） //不在…里
between 数字1 and 数字2 //在…之间
查看订单表中订单数量为20005的信息
mysql> select * from orders where order_num = 20005;
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2023-09-01 00:00:00 | 10001 |
+-----------+---------------------+---------+
1 row in set (0.00 sec)
查看客户信息中id在10001到10003之间的信息
mysql> select * from customers where cust_id between 10001 and 10003 ;
+---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
+---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+
3 rows in set (0.01 sec)
模糊匹配
where 字段名 like "表达式";
通配符：
_ 表示 1个字符
% 表示零个或多个字符
查找顾客表中顾客联系人信息为”一个字符[空格]三个字符“的信息
mysql> select * from customers where cust_contact like "_ ___" ;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+------------------+
2 rows in set (0.00 sec)找顾客名字以字母c开头的（没有空格挨着敲）
mysql> select * from customers where cust_name like "c%" ;
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+
1 row in set (0.00 sec)
逻辑比较
多个判断条件
逻辑与 and （&&） 多个判断条件必须同时成立
逻辑或 or （||） 多个判断条件其中某个条件成立即可
逻辑非 not （!） 取反
逻辑与and 优先级高于逻辑或 or
查找id为10001或住址在Sunny Place的顾客
mysql> select * from customers where cust_id = 10001 or cust_address = "1 Sunny Place" ;
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+---------------------+
2 rows in set (0.00 sec)
正则匹配
select 字段名列表 from 库名.表名 where字段名 regexp '正则表达式'；
转义字符
回顾shell学过的元字符(正则符号)
^ 匹配行首
$ 匹配行尾
[] 匹配范围内任意一个
* 前边的表达式出现零次或多次
| 或者
. 任意一个字符
匹配cust_zip 中以0~5数字开头的信息
mysql> select * from customers where cust_zip regexp "^[0-5]";
+---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
+---------+-------------+------------------+-----------+------------+----------+--------------+--------------+---------------------+
4 rows in set (0.00 sec)
