MySQL SELECT：数据表查询语句
SELECT 的语法格式如下： SELECT {* | <字段列名>} [ FROM <表 1>, <表 2>… [WHERE <表达式> [GROUP BY <group by definition> [HAVING <expression> [{<operator> <expression>}…]] [ORDER BY <order by definition>] [LIMIT[<offset>,] <row count>] ]
其中，各条子句的含义如下： {*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。 <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。 WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。 GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。 [ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。 [LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。
查询表中所有字段
mysql> select * from titles limit 10; +--------+-----------------+------------+------------+ | emp_no | title | from_date | to_date | +--------+-----------------+------------+------------+ | 10001 | Senior Engineer | 1986-06-26 | 9999-01-01 | | 10002 | Staff | 1996-08-03 | 9999-01-01 | | 10003 | Senior Engineer | 1995-12-03 | 9999-01-01 | | 10004 | Engineer | 1986-12-01 | 1995-12-01 | | 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 | | 10005 | Senior Staff | 1996-09-12 | 9999-01-01 | | 10005 | Staff | 1989-09-12 | 1996-09-12 | | 10006 | Senior Engineer | 1990-08-05 | 9999-01-01 | | 10007 | Senior Staff | 1996-02-11 | 9999-01-01 | | 10007 | Staff | 1989-02-10 | 1996-02-11 | +--------+-----------------+------------+------------+ 10 rows in set (0.00 sec) mysql>
MySQL使用DISTINCT过滤重复数据
DISTINCT 关键字的语法格式为：
SELECT DISTINCT <字段名> FROM <表名>;
mysql> select distinct title from titles; +--------------------+ | title | +--------------------+ | Senior Engineer | | Staff | | Engineer | | Senior Staff | | Assistant Engineer | | Technique Leader | | Manager | +--------------------+ 7 rows in set (0.34 sec) mysql>
mysql> select count(distinct title) from titiles; ERROR 1146 (42S02): Table 'employees.titiles' doesn't exist mysql> select count(distinct title) from titles; +-----------------------+ | count(distinct title) | +-----------------------+ | 7 | +-----------------------+ 1 row in set (0.24 sec) mysql>
MySQL LIMIT：限制查询结果的条数
mysql> select emp_no,last_name from employees limit 3,10; //第四条记录开始5 条 +--------+-----------+ | emp_no | last_name | +--------+-----------+ | 10004 | Koblick | | 10005 | Maliniak | | 10006 | Preusig | | 10007 | Zielinski | | 10008 | Kalloufi | | 10009 | Peac | | 10010 | Piveteau | | 10011 | Sluis | | 10012 | Bridgland | | 10013 | Terkki | +--------+-----------+ 10 rows in set (0.00 sec) mysql>
MySQL ORDER BY：对查询结果排序
ORDER BY 关键字主要用来将查询结果中的数据按照一定的顺序进行排序。其语法格式如下：
ORDER BY <字段名> [ASC|DESC]
mysql> select * from employees order by hire_date limit 20; +--------+------------+-------------+--------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+-------------+--------------+--------+------------+ | 110183 | 1953-06-24 | Shirish | Ossenbruggen | F | 1985-01-01 | | 111400 | 1959-11-09 | Arie | Staelin | M | 1985-01-01 | | 111692 | 1954-10-05 | Tonny | Butterworth | F | 1985-01-01 | | 110085 | 1959-10-28 | Ebru | Alpin | M | 1985-01-01 | | 110511 | 1957-07-08 | DeForest | Hagimont | M | 1985-01-01 | | 110022 | 1956-09-12 | Margareta | Markovitch | M | 1985-01-01 | | 111035 | 1962-02-24 | Przemyslawa | Kaelbling | M | 1985-01-01 | | 110303 | 1956-06-08 | Krassimir | Wegerle | F | 1985-01-01 |
MySQL WHERE：条件查询数据
在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用 WHERE 关键字来指定查询条件。
使用 WHERE 关键字的语法格式如下：
WHERE 查询条件
查询条件可以是：
- 带比较运算符和逻辑运算符的查询条件
- 带 BETWEEN AND 关键字的查询条件
- 带 IS NULL 关键字的查询条件
- 带 IN 关键字的查询条件
- 带 LIKE 关键字的查询条件
mysql> select * from employees where last_name='Peac' limit 20; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 13976 | 1958-05-28 | Bedir | Peac | F | 1997-02-15 | | 14402 | 1963-10-02 | Mooi | Peac | M | 1988-11-03 | | 15299 | 1962-02-06 | Jeong | Peac | F | 1991-12-16 | | 17842 | 1961-11-16 | Suebskul | Peac | M | 1996-08-15 | | 17897 | 1959-05-12 | Shakhar | Peac | F | 1991-10-16 | | 21531 | 1964-04-08 | Reinhard | Peac | F | 1991-05-24 | | 29215 | 1961-08-06 | Jinxi | Peac | M | 1990-06-14 | | 30801 | 1958-09-16 | Mihalis | Peac | F | 1986-07-23 | | 31384 | 1953-10-22 | JoAnne | Peac | F | 1993-10-23 | | 32415 | 1956-01-09 | Shigenori | Peac | M | 1988-10-22 |
MySQL LIKE：模糊查询
mysql> select * from employees where last_name like 'pear%' limit 5; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10274 | 1957-08-23 | Dmitri | Pearson | F | 1991-04-21 | | 10562 | 1963-04-17 | Shuky | Pearson | M | 1996-07-06 | | 11414 | 1952-08-08 | Eishiro | Pearson | M | 1989-01-02 | | 12094 | 1955-05-02 | Weiru | Pearson | F | 1989-04-27 | | 13332 | 1954-12-02 | Nidapan | Pearson | F | 1987-09-24 | +--------+------------+------------+-----------+--------+------------+ 5 rows in set (0.00 sec)
mysql> select * from employees where first_name like '%krish%' limit 5; +--------+------------+----------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+----------------+-----------+--------+------------+ | 10098 | 1961-09-23 | Sreekrishna | Servieres | F | 1985-05-13 | | 11318 | 1964-09-09 | Gopalakrishnan | Unno | F | 1986-01-25 | | 12498 | 1963-05-06 | Radhakrishnan | Socorro | M | 1985-03-29 | | 12536 | 1960-04-21 | Radhakrishnan | Baez | F | 1988-11-01 | | 12630 | 1954-04-23 | Sreekrishna | Falco | M | 1989-06-03 | +--------+------------+----------------+-----------+--------+------------+ 5 rows in set (0.00 sec) mysql>
MySQL BETWEEN AND：范围查询
mysql> select * from employees where emp_no between 400000 and 400004; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 400000 | 1963-11-29 | Mitsuyuki | Reinhart | M | 1985-08-27 | | 400001 | 1962-06-02 | Rosalie | Chinin | M | 1986-11-28 | | 400002 | 1964-08-16 | Quingbo | Birnbaum | F | 1986-04-23 | | 400003 | 1958-04-30 | Jianwen | Sidhu | M | 1986-02-01 | | 400004 | 1958-04-30 | Sedat | Suppi | M | 1995-12-18 | +--------+------------+------------+-----------+--------+------------+ 5 rows in set (0.00 sec)
MySQL IS NULL：空值查询
mysql> select * from engine_cost where cost_value is null; +-------------+-------------+------------------------+------------+---------------------+---------+ | engine_name | device_type | cost_name | cost_value | last_update | comment | +-------------+-------------+------------------------+------------+---------------------+---------+ | default | 0 | io_block_read_cost | NULL | 2020-05-26 17:02:38 | NULL | | default | 0 | memory_block_read_cost | NULL | 2020-05-26 17:02:38 | NULL | +-------------+-------------+------------------------+------------+---------------------+---------+ 2 rows in set (0.00 sec)
MySQL使用GROUP BY分组查询
mysql> select * from employees group by emp_no limit 5; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | +--------+------------+------------+-----------+--------+------------+ 5 rows in set (0.00 sec) mysql>
mysql> select gender,count(gender) from employees group by gender; +--------+---------------+ | gender | count(gender) | +--------+---------------+ | M | 179973 | | F | 120051 | +--------+---------------+ 2 rows in set (0.20 sec) mysql>
MySQL INSERT：插入数据（添加数据）
mysql> desc tb_1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(22) | NO | | NULL | | | location | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into tb_1 ( -> id,name,location) -> values(110,'tom','BJ'); Query OK, 1 row affected (0.00 sec) mysql> select * from tb_1; +-----+------+----------+ | id | name | location | +-----+------+----------+ | 110 | tom | BJ | +-----+------+----------+ 1 row in set (0.00 sec) mysql>
MySQL UPDATE：修改数据（更新数据）
UPDATE 语句的基本语法
使用 UPDATE 语句修改单个表，语法格式为：
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]
语法说明如下：
<表名>：用于指定要更新的表名称。SET子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。WHERE子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。ORDER BY子句：可选项。用于限定表中的行被修改的次序。LIMIT子句：可选项。用于限定被修改的行数。
注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。
mysql> select * from tb_1; +-----+------+----------+ | id | name | location | +-----+------+----------+ | 110 | tom | BJ | | 111 | tom | BJ | | 112 | tom | BJ | | 113 | tom | BJ | +-----+------+----------+ 4 rows in set (0.00 sec) mysql> update tb_1 set name='wuli'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from tb_1; +-----+------+----------+ | id | name | location | +-----+------+----------+ | 110 | wuli | BJ | | 111 | wuli | BJ | | 112 | wuli | BJ | | 113 | wuli | BJ | +-----+------+----------+ 4 rows in set (0.00 sec) mysql>
mysql> update tb_1 set name='liwang',location='Shnaghai' where id=112; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb_1; +-----+--------+----------+ | id | name | location | +-----+--------+----------+ | 110 | wuli | BJ | | 111 | wuli | BJ | | 112 | liwang | Shnaghai | | 113 | wuli | BJ | +-----+--------+----------+ 4 rows in set (0.00 sec) mysql>
MySQL DELETE：删除数据
在 MySQL 中，可以使用 DELETE 语句来删除表的一行或者多行数据。
删除单个表中的数据
使用 DELETE 语句从单个表中删除数据，语法格式为：
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
语法说明如下：
<表名>：指定要删除数据的表名。ORDER BY子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。WHERE子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。LIMIT子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
注意：在不使用 WHERE 条件的时候，将删除所有数据。
mysql> select * from tb_2; +----+------+----------+ | id | name | location | +----+------+----------+ | 11 | haha | BJ | | 12 | ha1 | BJ | | 13 | ha2 | BJ | +----+------+----------+ 3 rows in set (0.00 sec) mysql> delete from tb_2; Query OK, 3 rows affected (0.00 sec)
mysql> select * from tb_1; +-----+--------+----------+ | id | name | location | +-----+--------+----------+ | 110 | wuli | BJ | | 111 | wuli | BJ | | 112 | liwang | Shnaghai | | 113 | wuli | BJ | +-----+--------+----------+ 4 rows in set (0.01 sec) mysql> delete from tb_1 where id=110; Query OK, 1 row affected (0.01 sec) mysql> select * from tb_1; +-----+--------+----------+ | id | name | location | +-----+--------+----------+ | 111 | wuli | BJ | | 112 | liwang | Shnaghai | | 113 | wuli | BJ | +-----+--------+----------+ 3 rows in set (0.00 sec) mysql>
MySQL TRUNCATE：清空表记录
TRUNCATE 和 DELETE 的区别 从逻辑上说，TRUNCATE 语句与 DELETE 语句作用相同，但是在某些情况下，两者在使用上有所区别。 DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。 DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。 DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。 DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。 DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。 DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。 总结 当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。
mysql> truncate table tb_3; Query OK, 0 rows affected (0.10 sec) mysql>