Linux下Mysql的查询用法

一、Mysql的delete删除语法 1.删除数据库

①使用drop删除

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| huawei             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database huawei;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
5 rows in set (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.删除数据库的表

①使用drop语法

mysql> show tables;
+------------------+
| Tables_in_zabbix |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> drop table student;
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

二、查询数据

语法：select _column,_column from _table [where Clause] [limit N][offset M]

1.基本查询

select * from 表名

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    7.20 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> select s_id from fruits;
+------+
| s_id |
+------+
|  101 |
|  101 |
|  102 |
|  105 |
|  102 |
+------+
5 rows in set (0.00 sec)

2.条件查询

mysql> select s_id,f_name,f_price from fruits where f_price  insert into fruits(f_id,s_id,f_name,f_price) values ('bs2',105,'melon','7.2') ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruits(f_id,s_id,f_name,f_price) values  ('t1',102,'banana','10.3') ;
Query OK, 1 row affected (0.05 sec)

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    7.20 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
5 rows in set (0.00 sec)

四、修改数据库表内容

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    7.20 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> UPDATE  fruits SET f_price='8.5'  WHERE f_id='a1';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    8.50 |
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    7.20 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+
5 rows in set (0.00 sec)