mysql的常用命令/语句太多, 会分几篇来简介. 本篇简介的是: select命令(巨常用,后面还会讲到) update命令 insert into命令 delete/drop命令 rename命令 where子语句 like子语句 mysql排序
一: select命令
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;1.查询所有数据:
mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | NULL | | Chirpy | Gwen | bird | f | 1997-12-09 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+-------+ 9 rows in set (0.00 sec)2.查询行
mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+-------+ | Bowser | Diane | dog | m | 1979-08-31 | NULL | +--------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec)3.查询列:
mysql> select name, sex from pet; +----------+------+ | name | sex | +----------+------+ | Fluffy | f | | Claws | m | | Buffy | f | | Fang | m | | Bowser | m | | Chirpy | f | | Whistler | NULL | | Slim | m | | Puffball | f | +----------+------+ 9 rows in set (0.00 sec)注意: 增加关键字DISTINCT检索出每个唯一的输出记录：
mysql> select distinct owner from pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Benny | | Diane | +--------+ 4 rows in set (0.00 sec) mysql> select owner from pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +--------+ 9 rows in set (0.00 sec)二: update命令 需要更改某条记录的值, UPDATE只更改指定的记录，不需要重新转载数据表。
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]例:
mysql> update pet set sex = 'f' where name = "Chirpy"; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0三: insert into 命令 在上一篇mysql数据库/数据表的创建/查看 里面也提到过了, 像数据表中插入新定义的记录. insert into 还可以加上select命令完已存在的数据表中插入多个记录.
mysql> select * from pet1; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 1 row in set (0.00 sec) mysql> insert into pet1 select * from pet where sex = "f"; Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from pet1; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Chirpy | Gwen | bird | f | 1997-12-09 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+-------+ 5 rows in set (0.00 sec)四: delete/drop命令
DELETE FROM table_name [WHERE Clause]利用where语句删除满足条件的若干个记录, 如果没有where语句,则删除整个数据表. drop命令是直接用来删除数据表/数据库的.
drop database database-name drop table table-name而如果是用
DELETE FROM table_name DELETE FROM database_name这是清空的意思. 并不会删除数据库/数据表本身.
五: rename命令
RENAME DATABASE db_name TO new_db_name; RENAME TABLE db_name.table1 TO db_name.table2;六: where子语句 可用于select/delete/update命令中, 指定任何条件. 例:
mysql> SELECT * FROM pet WHERE birth > '1998-1-1'; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec)可以组合条件: 使用AND逻辑操作符, 或者一个OR操作符;
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1997-12-09 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+-------+---------+------+------------+-------+ 3 rows in set (0.00 sec)AND和OR可以混用，但AND比OR具有更高的優先級。 如果使用两个操作符，需要使用括号指明如何条件分组
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 2 rows in set (0.00 sec)七: like子语句
SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'你可以使用LIKE子句代替等号 =。 LIKE 通常与%一同使用，类似于一个元字符的搜索。 例:
mysql> select * from pet where name like "%y"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Chirpy | Gwen | bird | f | 1997-12-09 | NULL | +--------+--------+---------+------+------------+-------+ 3 rows in set (0.00 sec)八: mysql排序 将查询结果行排序后输出结果
SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。 可以添加 WHERE…LIKE 子句来设置条件。 例:
mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Bowser | 1979-08-31 | | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Chirpy | 1997-12-09 | | Whistler | 1997-12-09 | | Puffball | 1999-03-30 | +----------+------------+ 9 rows in set (0.00 sec)