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

布满荆棘的人生 2022-08-05 07:51 314阅读 0赞

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

上一篇连接：[http://blog.csdn.net/u010889616/article/details/48278939][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、可以使用的操作符
> > 
> > ![Center][]  
> >

> > ![Image 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)

[http_blog.csdn.net_u010889616_article_details_48278939]: http://blog.csdn.net/u010889616/article/details/48278939
[Center]: /images/20220731/8a9f1cedb73f4732b7fd11d05af23a64.png
[Image 1]: