mysql常用命令/语句学习一

最新推荐文章于 2024-09-03 02:45:22 发布

zyc_love_study

最新推荐文章于 2024-09-03 02:45:22 发布

阅读量2k

点赞数 3

分类专栏： Mysql相关

本文链接：https://blog.csdn.net/zyc_love_study/article/details/74989942

版权

Mysql相关专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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)