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)