Mysql复习--添加字段,删除字段,一次多行插入,修改字段名和类型,分组查询,建类似表,

时间:2022-09-16 13:09:33

 今天重新复习了下:仅是用到的部分,

添加字段,删除字段,一次多行插入,修改字段名和类型,分组查询,建类似表(记录的插入步骤已省略)

mysql> alter  table score modify  cno char(10); ----修改字段类型

Query OK, 0 rows affected (2.35 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

mysql> alter  table score change column id stu_id int;  ----修改字段名称

Query OK, 0 rows affected (2.48 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

mysql> show columnsfrom score;  ---显示表结构

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

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

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

| stu_id | int(11)  | YES  |     | NULL   |       |

| cno   | char(10) | YES  |     | NULL   |       |

| degree | float    | YES |     | NULL    |      |

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

3 rows in set (0.00 sec)

 

mysql> select   * from score;   ----所有记录

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

| stu_id | cno  | degree |

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

|     1 | 101  |     80 |

|     2 | 101  |     82 |

|     3 | 101  |     92 |

|     4 | 201  |     83 |

|     5 | 202  |     85 |

|     6 | 302  |     80 |

|     7 | 303  |     81 |

|     8 | 303  |     83 |

|     9 | 303  |     84 |

|    10 | 303  |     85 |

|    11 | 303  |     87 |

|    12 | 303  |     89 |

|    13 | 302  |     89 |

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

13 rows in set (0.00 sec)

 

mysql> select   * from score where degree between 80 and 83; --成绩在[80,83]区间的,闭区间

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

| stu_id | cno  | degree |

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

|     1 | 101  |     80 |

|     2 | 101  |     82 |

|     4 | 201  |     83 |

|     6 | 302  |     80 |

|     7 | 303  |     81 |

|     8 | 303  |     83 |

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

6 rows in set (0.00 sec)

 

mysql> select  *  fromscore where cno like '3%'; -----查询cno(课程号)’3’开头的记录

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

| stu_id | cno  | degree |

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

|     6 | 302  |    80 |

|     7 | 303  |     81 |

|     8 | 303  |     83 |

|     9 | 303  |     84 |

|    10 | 303  |     85 |

|    11 | 303  |     87 |

|    12 | 303  |     89 |

|    13 | 302  |     89 |

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

8 rows in set (0.00 sec)

 

mysql> select  cno ,avg(degree)  from score group  by cno; ---查询每个课程的平均分

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

| cno | avg(degree)       |

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

| 101 | 84.66666666666667 |

| 201 |                83 |

| 202 |                85 |

| 302 |              84.5 |

| 303 | 84.83333333333333 |

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

5 rows in set (2.19 sec)

 

mysql> select  cno ,max(degree)  from score group  by  cno;----查询每个课程的最高分

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

| cno | max(degree) |

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

| 101 |          92 |

| 201 |          83 |

| 202 |          85 |

| 302 |          89 |

| 303 |          89 |

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

5 rows in set (0.37 sec)

 

mysql> select  stu_id,cno ,max(degree)  from score group  by cno; -----这个stu_id字段需要包含在group by子句中

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clauseand c

ontains nonaggregated column'db_test.score.stu_id' which is not functionally de

pendent on columns in GROUP BY clause; thisis incompatible with sql_mode=only_f

ull_group_by

mysql> select  stu_id,cno ,max(degree)  from score group  by stu_id, cno;

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

| stu_id | cno  | max(degree) |

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

|     1 | 101  |          80 |

|     2 | 101  |          82 |

|     3 | 101  |          92 |

|     4 | 201  |          83 |

|     5 | 202  |          85 |

|     6 | 302  |          80 |

|     7 | 303  |          81 |

|     8 | 303  |          83 |

|     9 | 303  |          84 |

|    10 | 303  |          85 |

|    11 | 303  |          87 |

|    12 | 303  |          89 |

|    13 | 302  |          89 |

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

13 rows in set (0.00 sec)

 

mysql> select  cno ,max(degree)  from score where cno like '3%' group  by cno;--查询以’3’开头的课程的最高分

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

| cno | max(degree) |

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

| 302 |          89 |

| 303 |          89 |

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

2 rows in set (0.00 sec)

 

mysql> select  cno ,avg(degree)  from score where cno like '3%' group  by cno;--查询以3开头的课程的平均分(不设置选修人数条件)

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

| cno | avg(degree)       |

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

| 302 |              84.5 |

| 303 | 84.83333333333333 |

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

2 rows in set (0.00 sec)

 

mysql> select  cno ,max(degree)  from score where cno like '3%' group  by cno ha

ving count(*) >5;--- 查询Score表中至少有5名学生选修的并以3开头的课

程的最该分数。

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

| cno | max(degree) |

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

| 303 |          89 |

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

1 row in set (0.00 sec)

 

mysql> select  cno ,avg(degree)  from score where cno like '3%' group  by cno ha

ving count(*) >5;-- 查询Score表中至少有5名学生选修的并以3开头的课

程的平均分数。

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

| cno | avg(degree)       |

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

| 303 | 84.83333333333333 |

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

1 row in set (0.00 sec)

 

mysql> create table score2 like score;  ----score2中不含有记录

Query OK, 0 rows affected (0.25 sec)

mysql> select  *  fromscore2;

Empty set (0.00 sec)

 

mysql> create table score3 as select cno,degree from score ;----score3中含有记录

Query OK, 13 rows affected (2.55 sec)

Records: 13 Duplicates: 0  Warnings: 0

 

mysql> select  *  fromscore3;

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

| cno | degree |

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

| 101 |     80 |

| 101 |     82 |

| 101 |     92 |

| 201 |     83 |

| 202 |     85 |

| 302 |     80 |

| 303 |     81 |

| 303 |     83 |

| 303 |     84 |

| 303 |     85 |

| 303 |     87 |

| 303 |     89 |

| 302 |     89 |

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

13 rows in set (0.00 sec)

 

mysql> create viewscore_view as select   *  from score; ----创建视图

Query OK, 0 rows affected (2.25 sec)

 

mysql> select  * from  score_view;

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

| stu_id | cno  | degree |

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

|     1 | 101  |     80 |

|     2 | 101  |     82 |

|     3 | 101  |     92 |

|     4 | 201  |     83 |

|     5 | 202  |     85 |

|     6 | 302  |     80 |

|     7 | 303  |     81 |

|     8 | 303  |     83 |

|     9 | 303  |     84 |

|    10 | 303  |     85 |

|    11 | 303  |     87 |

|    12 | 303  |     89 |

|    13 | 302  |     89 |

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

13 rows in set (0.00 sec)

 

mysql> show  tables;

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

| Tables_in_db_test |

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

| book              |

| employee          |

| employee2         |

| employee5         |

| score             |

| score2            |

| score3            |

| score_view        |

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

8 rows in set (0.00 sec)

 

mysql> create table student(  ---建表

   -> id  int auto_incrementprimary key,

   -> name varchar(20),

   -> address varchar(200)

   -> );

Query OK, 0 rows affected (2.56 sec)

 

mysql> select  *  fromstudent;

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

| id | name     | address |

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

|  1| Tom      | NULL    |

|  2| Jack     | NULL    |

|  3| John     | NULL    |

|  4| Dave     | NULL    |

|  5| Jackson  | NULL    |

|  6| Sinodeng | NULL    |

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

6 rows in set (0.00 sec)

 

mysql> select   * from score;

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

| stu_id | cno  | degree |

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

|     1 | 101  |     80 |

|     2 | 101  |     82 |

|     3 | 101  |     92 |

|     4 | 201  |     83 |

|     5 | 202  |     85 |

|     6 | 302  |     80 |

|     7 | 303  |     81 |

|     8 | 303  |     83 |

|     9 | 303  |     84 |

|    10 | 303  |     85 |

|    11 | 303  |     87 |

|    12 | 303  |     89 |

|    13 | 302  |     89 |

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

13 rows in set (0.00 sec)

 

mysql> select a.id, a.name,b.cno,b.degree from student a,score b where a.id=b.s

tu_id;-----查询

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

| id | name     | cno | degree |

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

|  1| Tom      | 101  |    80 |

|  2| Jack     | 101  |    82 |

|  3| John     | 101  |    92 |

|  4| Dave     | 201  |    83 |

|  5| Jackson  | 202  |    85 |

|  6| Sinodeng | 302  |     80 |

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

6 rows in set (2.18 sec)

 

mysql> insert into student(name,address )values('Tom','Beijing'),('Blak','Nanji

ng'),('Watt','Hefei');-----这样一次插入多行

Query OK, 3 rows affected (2.36 sec)

Records: 3 Duplicates: 0  Warnings: 0

 

mysql> select  *  fromstudent;

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

| id | name     | address |

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

|  1| Tom      | NULL    |

|  2| Jack     | NULL    |

|  3| John     | NULL    |

|  4| Dave     | NULL    |

|  5| Jackson  | NULL    |

|  6| Sinodeng | NULL    |

|  7| Tom      | Beijing |

|  8| Blak     | Nanjing |

|  9| Watt     | Hefei   |

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

9 rows in set (0.00 sec)

 

mysql> alter  tablestudent add column postcode char(8);-----添加字段

Query OK, 0 rows affected (2.77 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

mysql> select  *  fromstudent;

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

| id | name     | address | postcode |

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

|  1| Tom      | NULL    | NULL    |

|  2| Jack     | NULL    |NULL     |

|  3| John     | NULL    | NULL    |

|  4| Dave     | NULL    | NULL    |

|  5| Jackson  | NULL    | NULL    |

|  6| Sinodeng | NULL    | NULL     |

|  7| Tom      | Beijing | NULL     |

|  8| Blak     | Nanjing | NULL     |

|  9| Watt     | Hefei   | NULL    |

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

9 rows in set (0.00 sec)

 

mysql> alter table student drop column postcode;----删除字段

Query OK, 0 rows affected (0.65 sec)

Records: 0 Duplicates: 0  Warnings: 0

 

mysql> insert   into score(stu_id,cno,degree)values(1,'101',80);  ----插入重复行

Query OK, 1 row affected (2.27 sec)

 

mysql> select  * from  score union  -----union不包含重复行

    -> select  * from  student;

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

| stu_id | cno      | degree |

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

|     1 | 101      | 80     |

|     2 | 101      | 82      |

|     3 | 101      | 92      |

|     4 | 201      | 83      |

|     5 | 202      | 85      |

|     6 | 302      | 80      |

|     7 | 303      | 81      |

|     8 | 303      | 83      |

|     9 | 303      | 84     |

|    10 | 303      | 85      |

|    11 | 303      | 87      |

|    12 | 303      | 89      |

|    13 | 302      | 89      |

|     1 | Tom      | NULL    |

|     2 | Jack     | NULL    |

|     3 | John     | NULL    |

|     4 | Dave     | NULL    |

|     5 | Jackson  | NULL    |

|     6 | Sinodeng | NULL    |

|     7 | Tom      | Beijing |

|     8 | Blak     | Nanjing |

|     9 | Watt     | Hefei   |

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

22 rows in set (0.00 sec)

 

mysql> select  * from  score union all

    -> select  * from  student; -----union all包含重复行

 

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

| stu_id | cno      | degree |

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

|     1 | 101      | 80      |

|     2 | 101      | 82      |

|     3 | 101      | 92      |

|     4 | 201      | 83      |

|     5 | 202      | 85      |

|     6 | 302      | 80      |

|     7 | 303      | 81      |

|     8 | 303      | 83      |

|     9 | 303      | 84      |

|    10 | 303      | 85      |

|    11 | 303      | 87      |

|    12 | 303      | 89      |

|    13 | 302      | 89      |

|     1 | 101      | 80      |

|     1 | Tom      | NULL    |

|     2 | Jack     | NULL    |

|     3 | John     | NULL    |

|     4 | Dave     | NULL    |

|     5 | Jackson  | NULL    |

|     6 | Sinodeng | NULL    |

|     7 | Tom      | Beijing |

|     8 | Blak     | Nanjing |

|     9 | Watt     | Hefei   |

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

23 rows in set (0.00 sec)

 

mysql>

mysql> select top 6 * from score; -----错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '6 * f
rom score' at line 1
mysql> select *  from score limit 6 ;  -----返回6条记录
+--------+------+--------+
| stu_id | cno  | degree |
+--------+------+--------+
|      1 | 101  |     80 |
|      2 | 101  |     82 |
|      3 | 101  |     92 |
|      4 | 201  |     83 |
|      5 | 202  |     85 |
|      6 | 302  |     80 |
+--------+------+--------+
6 rows in set (0.00 sec)


mysql> select *  from score limit 2, 6 ;-----从2+1开始,返回6条记录
+--------+------+--------+
| stu_id | cno  | degree |
+--------+------+--------+
|      3 | 101  |     92 |
|      4 | 201  |     83 |
|      5 | 202  |     85 |
|      6 | 302  |     80 |
|      7 | 303  |     81 |
|      8 | 303  |     83 |
+--------+------+--------+
6 rows in set (0.00 sec)


mysql> select  rand(); ----返回0到1之间的随机数,区间[0,1)
+--------------------+
| rand()             |
+--------------------+
| 0.8795744105051965 |
+--------------------+
1 row in set (0.00 sec)


mysql> select nullif(1,2) -----nullif(arg1,arg2):如果2个参数相等,则返回null,否则,返回参数1 ;
+-------------+
| nullif(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.05 sec)


mysql> select nullif(2,2);
+-------------+
| nullif(2,2) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)