准备练习环境
导入数据库
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)
别名/合并
定义别名使用 as 或 空格
mysql> select vend_id,vend_name as vname,vend_address as vaddress ,vend_city as -> vcity from vendors order by vend_name; +---------+----------------+-----------------+-------------+ | vend_id | vname | vaddress | vcity | +---------+----------------+-----------------+-------------+ | 1003 | ACME | 555 High Street | Los Angeles | | 1001 | Anvils R Us | 123 Main Street | Southfield | | 1004 | Furball Inc. | 1000 5th Avenue | New York | | 1005 | Jet Set | 42 Galaxy Road | London | | 1006 | Jouets Et Ours | 1 Rue Amusement | Paris | | 1002 | LT Supplies | 500 Park Street | Anytown | +---------+----------------+-----------------+-------------+ 6 rows in set (0.01 sec)
拼接 concat()
mysql> select concat(name,"-",uid) as 用户信息 from tarena.user where uid <= 5; +--------------+ | 用户信息 | +--------------+ | root-0 | | bin-1 | | daemon-2 | | adm-3 | | lp-4 | | sync-5 | +--------------+ 6 rows in set (0.00 sec)
