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

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

上一篇连接：http://blog.csdn.net/u010889616/article/details/48278939

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

现在有个数据库表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)