增删改查

1、插入数据

insert into 表名(col1,col2,……) values(val1,val2……); --插入指定列
insert into 表名 values (,,,,); --插入所有列
insert into 表名 values --一次插入多行 
(val1,val2……),
(val1,val2……),
(val1,val2……);

2、修改数据

update tablename 
set 
col1=newval1,  
col2=newval2,
...
...
colN=newvalN
where 条件;

3、删除数据

delete from tablenaeme where 条件;

4、select查询

4.1 条件查询 where

• 条件表达式的意义，表达式为真，则该行取出
• 比较运算符 = ，!=，< > <= >=
• like , not like (‘%’匹配任意多个字符,’_’匹配任意单个字符)
• in , not in , between and
• is null , is not null

  4.2 分组查询 group by

  一般要配合5个聚合函数使用:max,min,sum,avg,count ```sql mysql> select * from sc; +———-+———+————+ | StuID | CID | course | +———-+———+————+ | 1 | 1 | 67 | | 1 | 2 | 89 | | 1 | 3 | 94 | | 2 | 1 | 95 | | 2 | 2 | 88 | | 2 | 4 | 78 | | 3 | 1 | 94 | | 3 | 2 | 77 | | 3 | 3 | 90 | +———-+———+————+ 9 rows in set (0.10 sec)

—查询各门课程的平均分 mysql> select CID, avg(course) from sc group by CID; +———+——————-+ | CID | avg(course) | +———+——————-+ | 1 | 85.3333 | | 2 | 84.6667 | | 3 | 92.0000 | | 4 | 78.0000 | +———+——————-+ 4 rows in set (0.00 sec)

<a name="IxVL7"></a>
### 4.3 筛选查询 having
```sql
mysql> select CID, avg(course) from sc group by CID;
+------+-------------+
| CID  | avg(course) |
+------+-------------+
|    1 |     85.3333 |
|    2 |     84.6667 |
|    3 |     92.0000 |
|    4 |     78.0000 |
+------+-------------+
4 rows in set (0.00 sec)

--筛选出平均分大于80的课程ID和平均分
mysql> select CID, avg(course) from sc group by CID having avg(course)>80;
+------+-------------+
| CID  | avg(course) |
+------+-------------+
|    1 |     85.3333 |
|    2 |     84.6667 |
|    3 |     92.0000 |
+------+-------------+
3 rows in set (0.00 sec)

4.4 排序 order by

mysql> select CID, avg(course) from sc group by CID;
+------+-------------+
| CID  | avg(course) |
+------+-------------+
|    1 |     85.3333 |
|    2 |     84.6667 |
|    3 |     92.0000 |
|    4 |     78.0000 |
+------+-------------+
4 rows in set (0.00 sec)

--按照平均分从大到小的顺序进行排列
mysql> select CID, avg(course) avg from sc group by CID order by avg desc;
+------+---------+
| CID  | avg     |
+------+---------+
|    3 | 92.0000 |
|    1 | 85.3333 |
|    2 | 84.6667 |
|    4 | 78.0000 |
+------+---------+
4 rows in set (0.00 sec)

4.5 限制 limit

mysql> select CID, avg(course) avg from sc group by CID order by avg desc;
+------+---------+
| CID  | avg     |
+------+---------+
|    3 | 92.0000 |
|    1 | 85.3333 |
|    2 | 84.6667 |
|    4 | 78.0000 |
+------+---------+
4 rows in set (0.00 sec)

--筛选出平均分前三高的课程ID和平均分
mysql> select CID, avg(course) avg from sc group by CID order by avg desc limit 3;
+------+---------+
| CID  | avg     |
+------+---------+
|    3 | 92.0000 |
|    1 | 85.3333 |
|    2 | 84.6667 |
+------+---------+
3 rows in set (0.00 sec)