mysql常用命令/语句学习一

mysql的常用命令/语句太多, 会分几篇来简介.
本篇简介的是:
select命令(巨常用,后面还会讲到)
update命令
insert into命令
delete/drop命令
rename命令
where子语句
like子语句
mysql排序

一: select命令

SELECT what_to_selectFROM which_tableWHERE 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: 0mysql> 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-namedrop table table-name

而如果是用

DELETE FROM table_nameDELETE 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_nameWHERE 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)