mysql数据库高级sql语句

mysql> create database tt;

mysql> use tt;

mysql>  create table tt (id int(10) not null primary key auto_increment,name char(20) not null,score decimal(5,2),address varchar(40)default '未知')engine=innodb;

mysql> desc tt;                                                                     
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment |
| name    | char(20)     | NO   |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(40)  | YES  |     | 未知    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql>  insert into tt (name,score,address) values ('wangwu',68,'beijing'),('lisi',9njing');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  insert into tt (name,score,address) values ('zhangsan',74,'beijing'),('shang78,'hangzhou');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

#写入数据
mysql> select * from tt;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | wangwu   | 68.00 | beijing  |
|  2 | lisi     | 90.00 | nanjing  |
|  3 | zhangsan | 74.00 | beijing  |
|  4 | shanghai | 78.00 | hangzhou |
+----+----------+-------+----------+
4 rows in set (0.02 sec)

mysql> select name,score from tt where score>=70;
+----------+-------+
| name     | score |
+----------+-------+
| lisi     | 90.00 |
| zhangsan | 74.00 |
| shanghai | 78.00 |
+----------+-------+
3 rows in set (0.00 sec)

#筛选大于70分的进行升序排序 【对score字段排序】
mysql> select name,score from tt where score>=70 order by score;  #ASC可省略,升序
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 74.00 |
| shanghai | 78.00 |
| lisi     | 90.00 |
+----------+-------+
3 rows in set (0.00 sec)

#筛选大于70分的进行降序排序 【对score字段排序】
mysql> select name,score from tt where score>=70 order by score desc;   #降序
+----------+-------+
| name     | score |
+----------+-------+
| lisi     | 90.00 |
| shanghai | 78.00 |
| zhangsan | 74.00 |
+----------+-------+
3 rows in set (0.00 sec)

mysql> insert into tt(name,score,address) values('tom',66,'wuxi'),('jerry',77,'hangzhou');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tt;                                                                 
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | wangwu   | 68.00 | beijing  |
|  2 | lisi     | 90.00 | nanjing  |
|  3 | zhangsan | 74.00 | beijing  |
|  4 | shanghai | 78.00 | hangzhou |
|  5 | tom      | 66.00 | wuxi     |
|  6 | jerry    | 77.00 | hangzhou |
+----+----------+-------+----------+
6 rows in set (0.01 sec)

mysql> select id,name,score from tt where score>=70 order by score  ASC;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  3 | zhangsan | 74.00 |
|  6 | jerry    | 77.00 |
|  4 | shanghai | 78.00 |
|  2 | lisi     | 90.00 |
+----+----------+-------+
4 rows in set (0.00 sec)

mysql> select id,name,score from tt where score>=70 order by score  DESC;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  2 | lisi     | 90.00 |
|  4 | shanghai | 78.00 |
|  6 | jerry    | 77.00 |
|  3 | zhangsan | 74.00 |
+----+----------+-------+
4 rows in set (0.00 sec)

mysql> update tt set score=66 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,name,score from tt where score>=70 order by score desc;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  4 | shanghai | 78.00 |
|  6 | jerry    | 77.00 |
|  3 | zhangsan | 74.00 |
+----+----------+-------+
3 rows in set (0.00 sec)

#更改表中数据如下
mysql> select * from tt;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | wangwu   | 80.00 | beijing  |
|  2 | lisi     | 66.00 | nanjing  |
|  3 | zhangsan | 80.00 | beijing  |
|  4 | shanghai | 78.00 | hangzhou |
|  5 | tom      | 80.00 | wuxi     |
|  6 | jerry    | 77.00 | hangzhou |
+----+----------+-------+----------+
6 rows in set (0.00 sec)

mysql> select id,name,score from tt where score>=70 order by score desc,id desc ;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  5 | tom      | 80.00 |
|  3 | zhangsan | 80.00 |
|  1 | wangwu   | 80.00 |
|  4 | shanghai | 78.00 |
|  6 | jerry    | 77.00 |
+----+----------+-------+
5 rows in set (0.00 sec)

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 护眼 设计师:闪电赇 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值