用mysql语句做数据库操作_mysql数据库常用的基本SQL语句--数据库数据操作

紧接上一篇博客，上一篇介绍了有关数据库表的一些有关操作，这一篇介绍在数据库里面对于数据增、删、改、查的一些语句。

一、增、删、改、查数据库数据

现在有个数据库表stu_tbl,字段如下。

mysql> DESC stu_tbl;

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

| Field | Type | Null | Key | Default | Extra |

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

| name | varchar(20) | NO | PRI | NULL | |

| id | int(11) | NO | | NULL | |

| score | tinyint(3) unsigned | NO | | NULL | |

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

3 rows in set (0.00 sec)

(1)INSERT插入数据

mysql> INSERT INTO stu_tbl VALUES ('wang',1001,90),('li',1002,87);

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO stu_tbl VALUES ('zhao',1003,90),('he',1004,90);

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

(2)SELECT查询数据

DISTINCT ：查找唯一的不同的值

*：表示所有

WHERE：可以添加条件，没有条件，则显示所有行

1、可以使用的操作符

2、除非你使用 LIKE 来比较字符串，否则MySQL的WHERE子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的。

选择所有的数据

mysql> SELECT * FROM stu_tbl;

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

| name | id | score |

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

| he | 1004 | 90 |

| li | 1002 | 87 |

| wang | 1001 | 90 |

| zhao | 1003 | 90 |

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

4 rows in set (0.00 sec)

选择score是90的同学的姓名

mysql> SELECT name,score FROM stu_tbl WHERE score=90;

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

| name | score |

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

| he | 90 |

| wang | 90 |

| zhao | 90 |

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

3 rows in set (0.00 sec)

选择成绩大于89的同学

mysql> SELECT * FROM stu_tbl WHERE score > 89;

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

| name | id | score |

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

| he | 1004 | 90 |

| wang | 1001 | 90 |

| zhao | 1003 | 90 |

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

3 rows in set (0.00 sec)

选择，成绩相同的只显示一个

mysql> SELECT DISTINCT score FROM stu_tbl ;

+-------+

| score |

+-------+

| 90 |

| 87 |

+-------+

2 rows in set (0.00 sec)

(3)UPDATE更新数据

mysql> UPDATE stu_tbl SET score=100 WHERE name='wang';

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM stu_tbl;

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

| name | id | score |

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

| he | 1004 | 90 |

| li | 1002 | 87 |

| wang | 1001 | 100 |

| zhao | 1003 | 90 |

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

4 rows in set (0.00 sec)

(4)DELETE删除数据

mysql> DELETE FROM stu_tbl WHERE name='li';

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM stu_tbl;

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

| name | id | score |

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

| he | 1004 | 90 |

| wang | 1001 | 100 |

| zhao | 1003 | 90 |

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

3 rows in set (0.00 sec)

二、运算符AND和OR的使用

(1)AND使用

mysql> SELECT * FROM stu_tbl;

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

| name | id | score |

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

| du | 1003 | 90 |

| he | 1004 | 90 |

| wang | 1001 | 100 |

| zhang | 1004 | 90 |

| zhao | 1003 | 90 |

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

5 rows in set (0.00 sec)

mysql> SELECT * FROM stu_tbl WHERE score=90 AND id=1003;

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

| name | id | score |

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

| du | 1003 | 90 |

| zhao | 1003 | 90 |

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

2 rows in set (0.00 sec)

(2)OR使用

mysql> SELECT * FROM stu_tbl;

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

| name | id | score |

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

| du | 1003 | 90 |

| he | 1004 | 90 |

| wang | 1001 | 100 |

| zhang | 1004 | 90 |

| zhao | 1003 | 90 |

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

5 rows in set (0.01 sec)

mysql> SELECT * FROM stu_tbl WHERE score=90 OR score=100;

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

| name | id | score |

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

| du | 1003 | 90 |

| he | 1004 | 90 |

| wang | 1001 | 100 |

| zhang | 1004 | 90 |

| zhao | 1003 | 90 |

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

5 rows in set (0.00 sec)

三、运算符ORDER BY使用，DESC是降序

mysql> SELECT * FROM stu_tbl ORDER BY id;

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

| name | id | score |

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

| wang | 1001 | 100 |

| du | 1003 | 90 |

| zhao | 1003 | 90 |

| he | 1004 | 90 |

| zhang | 1004 | 90 |

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

5 rows in set (0.00 sec)

mysql> SELECT * FROM stu_tbl ORDER BY id DESC;

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

| name | id | score |

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

| he | 1004 | 90 |

| zhang | 1004 | 90 |

| du | 1003 | 90 |

| zhao | 1003 | 90 |

| wang | 1001 | 100 |

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

5 rows in set (0.00 sec)

先指定id字段降序，再指定name字段降序。

mysql> SELECT * FROM stu_tbl ORDER BY id DESC,name DESC;

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

| name | id | score |

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

| zhang | 1004 | 90 |

| he | 1004 | 90 |

| zhao | 1003 | 90 |

| du | 1003 | 90 |

| wang | 1001 | 100 |

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

5 rows in set (0.00 sec)