学mysql还用学sql吗_mysql学习-sql根本使用_mysql

mysql学习--sql基本使用

上图为数据库操作分类：

以下的操作参考(mysql必知必会)

创建数据库 执行脚本建表：

mysql> create database mytest; Query OK, 1 row affected (0.07 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use myte ERROR 1049 (42000): Unknown database 'myte' mysql> use mytest; Database changed mysql> show tables; Empty set (0.00 sec) mysql> source /home/huangcd/mysql_scripts/create.sql

mysql> source /home/huangcd/mysql_scripts/populate.sql

mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | customers | | orderitems | | orders | | productnotes | | products | | vendors | +------------------+ 6 rows in set (0.02 sec)

删除重复行：

mysql> select vend_id from products;

+---------+

| vend_id |

+---------+

|    1001 |

|    1001 |

|    1001 |

|    1002 |

|    1002 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1003 |

|    1005 |

|    1005 |

+---------+

14 rows in set (0.00 sec)

mysql> select distinct vend_id from products;

+---------+

| vend_id |

+---------+

|    1001 |

|    1002 |

|    1003 |

|    1005 |

+---------+

4 rows in set (0.04 sec)

返回结果中不多于5行：

mysql> select prod_name from products

-> limit 5;

+--------------+

| prod_name    |

+--------------+

| .5 ton anvil |

| 1 ton anvil  |

| 2 ton anvil  |

| Detonator    |

| Bird seed    |

+--------------+

5 rows in set (0.00 sec)

从第5行开始的5行：

mysql> select prod_name

-> from products

-> limit 5,5;

+--------------+

| prod_name    |

+--------------+

| Carrots      |

| Fuses        |

| JetPack 1000 |

| JetPack 2000 |

| Oil can      |

+--------------+

5 rows in set (0.00 sec)

order by 子句对一个或者多个列进行输出排序：

mysql> select prod_name

-> from products

-> order by prod_name;

+----------------+

| prod_name      |

+----------------+

| .5 ton anvil   |

| 1 ton anvil    |

| 2 ton anvil    |

| Bird seed      |

| Carrots        |

| Detonator      |

| Fuses          |

| JetPack 1000   |

| JetPack 2000   |

| Oil can        |

| Safe           |

| Sling          |

| TNT (1 stick)  |

| TNT (5 sticks) |

+----------------+

首先按price排序，只有price相同时，才按照名称排序：

mysql> select prod_id, prod_price, prod_name

-> from products

-> order by prod_price, prod_name;

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| FC      |       2.50 | Carrots        |

| TNT1    |       2.50 | TNT (1 stick)  |

| FU1     |       3.42 | Fuses          |

| SLING   |       4.49 | Sling          |

| ANV01   |       5.99 | .5 ton anvil   |

| OL1     |       8.99 | Oil can        |

| ANV02   |       9.99 | 1 ton anvil    |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| DTNTR   |      13.00 | Detonator      |

| ANV03   |      14.99 | 2 ton anvil    |

| JP1000  |      35.00 | JetPack 1000   |

| SAFE    |      50.00 | Safe           |

| JP2000  |      55.00 | JetPack 2000   |

+---------+------------+----------------+

14 rows in set (0.05 sec)

默认排序方式是升序，若用降序必须用DESC关键字：

mysql> select prod_id, prod_price, prod_name from products order by prod_price DESC;

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| JP2000  |      55.00 | JetPack 2000   |

| SAFE    |      50.00 | Safe           |

| JP1000  |      35.00 | JetPack 1000   |

| ANV03   |      14.99 | 2 ton anvil    |

| DTNTR   |      13.00 | Detonator      |

| TNT2    |      10.00 | TNT (5 sticks) |

| FB      |      10.00 | Bird seed      |

| ANV02   |       9.99 | 1 ton anvil    |

| OL1     |       8.99 | Oil can        |

| ANV01   |       5.99 | .5 ton anvil   |

| SLING   |       4.49 | Sling          |

| FU1     |       3.42 | Fuses          |

| FC      |       2.50 | Carrots        |

| TNT1    |       2.50 | TNT (1 stick)  |

+---------+------------+----------------+

14 rows in set (0.02 sec)

mysql> select prod_id, prod_price, prod_name from products order by prod_price DESC, prod_name;

+---------+------------+----------------+

| prod_id | prod_price | prod_name      |

+---------+------------+----------------+

| JP2000  |      55.00 | JetPack 2000   |

| SAFE    |      50.00 | Safe           |

| JP1000  |      35.00 | JetPack 1000   |

| ANV03   |      14.99 | 2 ton anvil    |

| DTNTR   |      13.00 | Detonator      |

| FB      |      10.00 | Bird seed      |

| TNT2    |      10.00 | TNT (5 sticks) |

| ANV02   |       9.99 | 1 ton anvil    |

| OL1     |       8.99 | Oil can        |

| ANV01   |       5.99 | .5 ton anvil   |

| SLING   |       4.49 | Sling          |

| FU1     |       3.42 | Fuses          |

欢迎大家阅读《mysql学习-sql根本使用_mysql》,跪求各位点评，by 搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~