Mysql复习--添加字段，删除字段，一次多行插入，修改字段名和类型，分组查询，建类似表，
来源：互联网 发布：北航软件学院导师 编辑：程序博客网 时间：2024/05/02 02:09
今天重新复习了下：仅是用到的部分，
添加字段，删除字段，一次多行插入，修改字段名和类型，分组查询，建类似表（记录的插入步骤已省略）
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)
- Mysql复习--添加字段，删除字段，一次多行插入，修改字段名和类型，分组查询，建类似表，
- Mysql字段操作—添加字段、删除字段、修改字段名、修改字段类型(约束条件) 、重命名表名
- Mysql字段操作—增加字段、删除字段、修改字段名、修改字段类型(约束条件)
- Mysql字段操作—增加字段、删除字段、修改字段名、修改字段类型(约束条件)
- Mysql字段操作—增加字段、删除字段、修改字段名、修改字段类型(约束条件)
- MySQL添加字段，修改字段，删除字段
- mysql修改表名，字段，增加字段，删除字段
- MYSQL - 增加字段、删除字段、修改字段名、修改字段类型(约束条件)
- Mysql中用SQL增加、删除字段，修改字段名、字段类型、注释，调整字段顺序
- Mysql中用SQL增加、删除字段，修改字段名、字段类型、注释，调整字段顺序总结
- oracle怎样修改表名、列名、字段类型、添加表列、删除表列、主键和unique
- MySQL修改字段名、字段类型
- mysql 修改字段类型 删除字段类型
- 通过表名和字段名查询字段类型
- 修改字段名、添加删除字段、改变字段数据类型
- 查询oracle表中的字段名和字段类型
- sql server查询表的字段名和字段类型
- mysql 添加修改删除字段
- POJ 3635 Full Tank? 拆点建图+dijkstra
- 设计模式----python版本
- mac下运行.bin文件
- Socket描述符选项[SOL_SOCKET]
- 排序算法
- Mysql复习--添加字段，删除字段，一次多行插入，修改字段名和类型，分组查询，建类似表，
- Python学习笔记一
- 安装SQL2005 SP4补丁报错：无法安装Windows Installer MSP文件
- iGrimace iOS7-Ios8-ios一键新机 系统 NZT NEO 非V8锤子IG基本操作全教程：v3 v8
- 从svn中检出maven工程,导入到eclipse遇到的问题
- 花10钟看一看少走30年弯路
- leetcode-java-357. Count Numbers with Unique Digits
- windows对象的属性
- 两年多工作心得和体会
