1、MYSQL的插入操作
insert语句
语法：
INSERT INTO table [(column [, column...])] –VALUES(value [, value...])，(value [, value...])
eg：
1、创建表emp
mysql> create table emp(
-> id int primary key auto_increment,
-> name char(16) not null,
-> gender enum('男','女') default '男',
-> address char(16));
2、一次插入一条记录（默认）
mysql> insert into emp(id,name) values(20210001,'jim');
Query OK, 1 row affected (0.01 sec)
mysql> insert into emp(name) values('susan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp values(20210003,'joan','女','陕西西安');
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp;
+----------+-------+--------+--------------+
| id | name | gender | address |
+----------+-------+--------+--------------+
| 20210001 | jim | 男 | NULL |
| 20210002 | susan | 男 | NULL |
| 20210003 | joan | 女 | 陕西西安 |
+----------+-------+--------+--------------+
3、一次插入多条记录
mysql> insert into emp(name) values('john'),('smith'),('jane');
4、创建表username
mysql> create table username(name char(16));
5、将查询出的信息插入其他表
mysql> insert into username
-> select name from emp;
mysql> select * from username;
+-------+
| name |
+-------+
| jim |
| susan |
| joan |
| john |
| smith |
| jane |
+-------+
如果需要插入其他特殊字符，应该采用\转义字符做前缀
replace语句
语法1：replace into 表名 [（字段列表）] values （值列表）
语法2：replace [into] 目标表名[(字段列表1)] select (字段列表2) from 源表 where 条件表达式
语法3：replace [into] 表名 set 字段1=值1, 字段2=值2
eg:
mysql> replace into username values('w5');
mysql> replace into username
-> select name from emp
mysql> replace into username
-> set name='l4';
replace 与 insert区别
replace语句向表插入新记录时，若新记录的主键值或者唯一性约束的字段与已有记录相同，则已有记录先被删除（已有记录删除时不能违背外键约束条件），然后再插入新记录。
replace可以将delect与insert合二为一，形成原子操作，无需将delect与insert操作置于事务之中
eg:
mysql> alter table username modify name char(16) unique; 将name字段修改为唯一值，表中不能重复
mysql> select * from username;
+-------+
| name |
+-------+
| jane |
| joan |
| john |
| smith |
| susan |
| z6 |
+-------+
用insert插入报错
mysql> insert into username values('z6');
ERROR 1062 (23000): Duplicate entry 'z6' for key 'name'
用replace插入不报错，先删除再插入
mysql> replace into username values('z6');
Query OK, 1 row affected (0.00 sec)
2、MYSQL的更新操作
update语句
语法：–UPDATE table SET column = value [, column = value] … [WHERE condition];
eg：
mysql> update emp
-> set gender='女'
-> where name='susan';
mysql> update emp
-> set gender='女',address='山东青岛'
-> where name like 'j%';
mysql> update emp
-> set address='辽宁大连'
-> where name regexp '^jo';
3、MYSQL的删除操作
truncat 语句（保留表结构，清空表中所有数据）
语法：TRUNCATE TABLE table_name
完全清空一个表
DDL语句
eg：
mysql> truncate table username;
delete语句 （可以与where进行条件删除）
语法：TRUNCATE TABLE table_name where ...
eg：
mysql> delete from username
-> where name like 's%';
drop 语句（删除数据与表结构）
语法：DROP TABLE table_name;
eg:
mysql> drop table username;
DROP、TRUNCATE、DELETE对比
delete**删除数据，保留表结构，可以回滚，如果数据量大，很慢
truncate** 删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快
drop** 删除数据和表结构,删除速度最快。
4、MYSQL的查询操作
select语句
语法：SELECT {*, column [alias],...} FROM table;
eg：
mysql> select * from emp;
mysql> select id,name from emp;
mysql> select id as 编号,name 姓名 from emp; AS 作用：起别名
+----------+--------+
| 编号 | 姓名 |
+----------+--------+
| 20210006 | jane |
| 20210003 | joan |
+----------+--------+
select语句中的算数表达式
对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ -* /）
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL
eg:
mysql> select 10+2*3/4;
+----------+
| 10+2*3/4 |
+----------+
| 11.5000 |
+----------+
mysql> alter table emp add salary float(6,2) after gender; #在gender列后插入salary列
mysql> update emp
-> set salary=8500.0;
mysql> select salary*14 年薪 from emp;
+-----------+
| 年薪 |
+-----------+
| 119000.00 |
| 119000.00 |
| 119000.00 |
| 119000.00 |
| 119000.00 |
| 119000.00 |
+-----------+
mysql> update emp
-> set salary=salary*1.5
-> where gender='男';
mysql> select 11+12;
+-------+
| 11+12 |
+-------+
| 23 |
+-------+
mysql> select '23'+22;
+---------+
| '23'+22 |
+---------+
| 45 |
+---------+
mysql> select 'a'+180;
+---------+
| 'a'+180 |
+---------+
| 180 |
+---------+
mysql> select 'sw'+'K';
+----------+
| 'sw'+'K' |
+----------+
| 0 |
+----------+
mysql> select null+12;
+---------+
| null+12 |
+---------+
| NULL |
+---------+
select 运算符的优先级
乘法和除法的优先级高于加法和减法
同级运算的顺序是从左到右
表达式中使用括号可强行改变优先级的运算顺序
NULL值的使用
空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
字符串和null进行连接运算，得到也是null.
安全等于<=>
1.可作为普通运算符的=
2.也可以用于判断是否是NULL
eg:
mysql> select * from emp
-> where salary<=>NUll;
+----------+------+--------+--------+---------+
| id | name | gender | salary | address |
+----------+------+--------+--------+---------+
| 20210007 | xyz | 男 | NULL | NULL |
+----------+------+--------+--------+---------+
1 row in set (0.00 sec)
定义字段的别名
改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。
mysql> select gender as 性别
-> from emp;
+--------+
| 性别 |
+--------+
| 男 |
| 女 |
| 女 |
| 女 |
| 男 |
| 女 |
| 男 |
+--------+
重复记录
缺省情况下查询显示所有行,包括重复行
使用DISTINCT关键字可从查询结果中清除重复行
DISTINCT的作用范围是后面所有字段的组合
语法：
SELECT DISTINCT address
FROM emp;
mysql> select distinct address from emp;
+--------------+
| address |
+--------------+
| NULL |
| 辽宁大连 |
| 山东青岛 |
+--------------+
mysql> select distinct salary,gender
-> from emp;
+---------+--------+
| salary | gender |
+---------+--------+
| 9350.00 | 男 |
| 8500.00 | 女 |
| NULL | 男 |
+---------+--------+
限制所选择的记录
使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
SELECT[DISTINCT] {*, column [alias], ...}
FROM table–[WHEREcondition(s)];
WHERE中的字符串和日期值
字符串和日期要用单引号扩起来
字符串是大小写敏感的,日期值是格式敏感的
比较运算符
1、<,=,>
2、between .... and...
eg:
mysql> select name,gender,salary
-> from emp
-> where salary<=9000;
mysql> select name,gender,salary
-> from emp
-> where salary between 8600 and 9500;
in运算符
使用IN运算符获得匹配列表值的记录
mysql> select name,gender
-> from emp
-> where address in('山东青岛','辽宁大连');
+------+--------+
| name | gender |
+------+--------+
| joan | 女 |
| john | 女 |
| jane | 女 |
+------+--------+
like运算符
使用LIKE运算符执行模糊查询
查询条件可包含文字字符或数字
(%) 可表示零或多个字符
( _ ) 可表示一个字符
eg:
mysql> select name
-> from emp
-> where name like 'j%';
+------+
| name |
+------+
| jane |
| joan |
| john |
+------+
mysql> select name from emp where name like '_a%';
+------+
| name |
+------+
| jane |
+------+
is null运算符
查询包含空值的记录
eg:
mysql> select name,address
-> from emp
-> where address is null;
+-------+---------+
| name | address |
+-------+---------+
| z6 | NULL |
| susan | NULL |
| smith | NULL |
| xyz | NULL |
+-------+---------+
逻辑运算符
使用AND运算符：AND需要所有条件都是满足
使用OR运算符：OR只要两个条件满足一个就可以
使用NOT运算符：NOT是取反的意思
eg:
mysql> select name
-> from emp
-> where salary>9000 and address is null;
+-------+
| name |
+-------+
| z6 |
| smith |
+-------+
mysql> select name from emp where salary>9000 or address is null;
+-------+
| name |
+-------+
| z6 |
| susan |
| smith |
| xyz |
+-------+
mysql> select name from emp where not salary>9000 ;
+-------+
| name |
+-------+
| susan |
| joan |
| john |
| jane |
+-------+
对结果集排序
语法：order by 列名[desc],列名…
默认升序排序：asc
降序排序：desc
常用聚合函数
聚合函数对一组值进行运算，并返回单个值。也叫组合函数。
COUNT(*|列名) 统计行数
AVG(数值类型列名) 平均值
SUM (数值类型列名) 求和
MAX(列名) 最大值
MIN(列名) 最小值
除了COUNT()以外，聚合函数都会忽略NULL值
mysql> select max(salary),min(salary),avg(salary),sum(salary) from emp;
+-------------+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) | sum(salary) |
+-------------+-------------+-------------+-------------+
| 9350.00 | 6500.50 | 8457.214286 | 59200.50 |
+-------------+-------------+-------------+-------------+
数值型函数
eg：
mysql> select abs(-5);
+---------+
| abs(-5) |
+---------+
| 5 |
+---------+
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
mysql> select pow(2,3);
+----------+
| pow(2,3) |
+----------+
| 8 |
+----------+
mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
| 1 |
+----------+
mysql> select rand(1);
+---------------------+
| rand(1) |
+---------------------+
| 0.40540353712197724 |
+---------------------+
字符串函数
eg：
mysql> select length('hello world.');
+------------------------+
| length('hello world.') |
+------------------------+
| 12 |
+------------------------+
mysql> select length('中国'); utf8一个汉字3个字节，dbk一个汉字2个字节
+------------------+
| length('中国') |
+------------------+
| 6 |
+------------------+
mysql> select concat('hello',' world');
+--------------------------+
| concat('hello',' world') |
+--------------------------+
| hello world |
+--------------------------+
mysql> select lower('Hello World');
+----------------------+
| lower('Hello World') |
+----------------------+
| hello world |
+----------------------+
mysql> select upper('hello world');
+----------------------+
| upper('hello world') |
+----------------------+
| HELLO WORLD |
+----------------------+
mysql> select left('hello',2);
+-----------------+
| left('hello',2) |
+-----------------+
| he |
+-----------------+
mysql> select right('hello',2);
+------------------+
| right('hello',2) |
+------------------+
| lo |
+------------------+
mysql> select trim(' hello ');
+------------------+
| trim(' hello ') |
+------------------+
| hello |
+------------------+
日期和时间函数
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-01-17 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:57:00 |
+-----------+
mysql> select concat(curdate(),' ',curtime());
+---------------------------------+
| concat(curdate(),' ',curtime()) |
+---------------------------------+
| 2021-01-17 10:57:51 |
+---------------------------------+
mysql> select now(),sysdate();
+---------------------+---------------------+
| now() | sysdate() |
+---------------------+---------------------+
| 2021-01-17 10:59:14 | 2021-01-17 10:59:14 |
+---------------------+---------------------+
mysql> select day(now()),year(now()),month(now());
+------------+-------------+--------------+
| day(now()) | year(now()) | month(now()) |
+------------+-------------+--------------+
| 17 | 2021 | 1 |
+------------+-------------+--------------+
mysql> select dayname(now()),monthname(now());
+----------------+------------------+
| dayname(now()) | monthname(now()) |
+----------------+------------------+
| Sunday | January |
+----------------+------------------+
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 3 |
+-------------+
mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 17 |
+------------------+
mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
| 17 |
+-------------------+
流程控制函数
mysql> select if(2<3,2,3); 若1为真取2,，否则取3
+-------------+
| if(2<3,2,3) |
+-------------+
| 2 |
+-------------+
mysql> select if(2>3,2,3);
+-------------+
| if(2>3,2,3) |
+-------------+
| 3 |
+-------------+
mysql> select ifnull(1,2),ifnull(2<3,2),ifnull(2>3,4);
+-------------+---------------+---------------+
| ifnull(1,2) | ifnull(2<3,2) | ifnull(2>3,4) |
+-------------+---------------+---------------+
| 1 | 1 | 0 |
+-------------+---------------+---------------+
mysql> select case when 2<4 then 'true' else 'false' end;
+--------------------------------------------+
| case when 2<4 then 'true' else 'false' end |
+--------------------------------------------+
| true |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select case when 1<0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1<0 then 'true' else 'false' end |
+--------------------------------------------+
| false |
+--------------------------------------------+
mysql> select name,salary,
-> case salary
-> when salary<8500 then salary*1.2
-> when salary>8500 then salary*0.6
-> else salary
-> end as 工资 from emp;
+-------+---------+---------+
| name | salary | 工资 |
+-------+---------+---------+
| z6 | 9350.00 | 9350.00 |
| susan | 8500.00 | 8500.00 |
| joan | 8500.00 | 8500.00 |
| john | 8500.00 | 8500.00 |
| smith | 9350.00 | 9350.00 |
| jane | 8500.00 | 8500.00 |
| xyz | 6500.50 | 6500.50 |
+-------+---------+---------+
数据分组（group by）
分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。
分组函数忽略空值.
分组函数重要规则:
1、如果使用了分组函数，或者使用GROUP BY 的查询：出现在SELECT列表中的字段，要么出现在组合函数里， 要么出现在GROUP BY 子句中。
2、GROUP BY 子句的字段可以不出现在SELECT列表当中。
3、使用集合函数可以不使用GROUP BY子句，此时所有的查询结果作为一组。
mysql> select gender,avg(salary)
-> from emp
-> group by gender;
+--------+-------------+
| gender | avg(salary) |
+--------+-------------+
| 男 | 8400.166667 |
| 女 | 8500.000000 |
+--------+-------------+
限定组的结果（having子句）
HAVING子句用来对分组后的结果再进行条件过滤。
格式：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BYcolumn];
having与where区别
WHERE是在分组前进行条件过滤， HAVING子句是在分组后进行条件过滤，
WHERE子句中不能使用聚合函数， HAVING子句可以使用聚合函数。
多行数据合并 GROUP_CONCAT
注意：使用 GROUP_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行
mysql> select s_id,group_concat(f_name) from fruits group by s_id;
+------+-------------------------+
| s_id | group_concat(f_name) |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
+------+-------------------------
联合查询
UNION 去重复
UNION ALL 不去重复
mysql> select *from fruits where s_id=101
-> union
-> select *from fruits where s_id=102;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
mysql> select *from fruits where s_id=101 union all select *from fruits where f_name='apple';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
| a1 | 101 | apple | 5.20 |
+------+------+------------+---------+
mysql> select *from fruits where s_id=101 union select *from fruits where f_name='apple';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| c0 | 101 | cherry | 3.20 |
+------+------+------------+---------+
查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/
mysql通配符
%：用来表示任意多个字符，包含0个字符
_ : 用来表示任意单个字符
escape：用来转义特定字符
正则表达式
实现一些很特殊的规则匹配；
正则表达式需要使用REGEXP命令，匹配上返回"1"匹配不上返回"0"，默认不加 条件REGEXP相当于like ‘%%’。
在前面加上NOT相当于NOT LIKE。
单表查询练习题1
CREATE TABLE fruits
(
f_id char(10) NOT NULL,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(8,2) NOT NULL,
PRIMARY KEY(f_id)
);
INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES('a1', 101,'apple',5.2),
('b1',101,'blackberry', 10.2),
('bs1',102,'orange', 11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana', 10.3),
('t2',102,'grape', 5.3),
('o2',103,'coconut', 9.2),
('c0',101,'cherry', 3.2),
('a2',103, 'apricot',2.2),
('l2',104,'lemon', 6.4),
('b2',104,'berry', 7.6),
('m1',106,'mango', 15.6),
('m2',105,'xbabay', 2.6),
('t4',107,'xbababa', 3.6),
('m3',105,'xxtt', 11.6),
('b5',107,'xxxx', 3.6)
【例1】从fruits表中检索所有字段的数据，SQL语句如下：
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
【例2】查询fruits表中f_name列所有水果名称，SQL语句如下：
mysql> select distinct f_name
-> from fruits;
+------------+
| f_name |
+------------+
| apple |
| apricot |
| blackberry |
| berry |
| xxxx |
| orange |
| melon |
| cherry |
| lemon |
| mango |
| xbabay |
| xxtt |
| coconut |
| banana |
| grape |
| xbababa |
+------------+
【例3】例如，从fruits表中获取f_name和f_price两列，SQL语句如下：
mysql> select f_name,f_price
-> from fruits
-> ;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| orange | 11.20 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| mango | 15.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| coconut | 9.20 |
| banana | 10.30 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
16 rows in set (0.00 sec)
【例4】查询价格为10.2元的水果的名称，SQL语句如下：
mysql> select f_name
-> from fruits
-> where f_price='10.2';
+------------+
| f_name |
+------------+
| blackberry |
+------------+
【例5】查找名称为“apple”的水果的价格，SQL语句如下：
mysql> select f_price from fruits where f_name='apple';
+---------+
| f_price |
+---------+
| 5.20 |
+---------+
【例6】查询价格小于10的水果的名称，SQL语句如下：
mysql> select f_name
-> from fruits
-> where f_price<10;
+---------+
| f_name |
+---------+
| apple |
| apricot |
| berry |
| xxxx |
| melon |
| cherry |
| lemon |
| xbabay |
| coconut |
| grape |
| xbababa |
+---------+
【例7】s_id为101和102的记录，SQL语句如下：
mysql> select *
-> from fruits
-> where s_id=101 or s_id=102;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
mysql> select * from fruits where s_id in (101,102);
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
mysql> select *from fruits where s_id regexp '10(1|2)';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> select *from fruits where s_id regexp '10[12]';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
【例8】查询所有s_id不等于101也不等于102的记录，SQL语句如下：
mysql> select *
-> from fruits
-> where s_id not in(101,102);
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a2 | 103 | apricot | 2.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
mysql> select * from fruits where s_id!=101 and s_id!=102;
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a2 | 103 | apricot | 2.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.60 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
【例9】查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下：
mysql> select f_name,f_price from fruits where 2.00<=f_price and f_price<=10.20;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
mysql> select f_name,f_price from fruits where f_price between 2 and 10.2;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
【例10】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下：
mysql> select f_name ,f_price from fruits where 2.00>f_price or f_price>10.20;
+--------+---------+
| f_name | f_price |
+--------+---------+
| orange | 11.20 |
| mango | 15.60 |
| xxtt | 11.60 |
| banana | 10.30 |
+--------+---------+
mysql> select f_name,f_price from fruits where not (2.00<=f_price and f_price<=10.20);
+--------+---------+
| f_name | f_price |
+--------+---------+
| orange | 11.20 |
| mango | 15.60 |
| xxtt | 11.60 |
| banana | 10.30 |
+--------+---------+
【例11】查找所有以’b’字母开头的水果，SQL语句如下：
mysql> select *
-> from fruits
-> where f_name like 'b%';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
mysql> select * from fruits where f_name regexp '^b.*';
mysql> select * from fruits where f_name regexp '^b';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
【例12】在fruits表中，查询f_name中包含字母’g’的记录，SQL语句如下：
mysql> select * from fruits where f_name like '%g%';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.60 |
| t2 | 102 | grape | 5.30 |
+------+------+--------+---------+
mysql> select * from fruits where f_name regexp '.*g.*';
mysql> select * from fruits where f_name regexp 'g';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.60 |
| t2 | 102 | grape | 5.30 |
+------+------+--------+---------+
【例13】查询以’b’开头，并以’y’结尾的水果的名称，SQL语句如下：
mysql> select * from fruits where f_name like 'b%y';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
+------+------+------------+---------+
mysql> select * from fruits where f_name regexp '^b.*y$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
+------+------+------------+---------+
【例14】在fruits表中，查询以字母’y’结尾，且’y’前面只有4个字母的记录，SQL语句如下：
mysql> select *
-> from fruits
-> where f_name like '____y';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 | 104 | berry | 7.60 |
+------+------+--------+---------+
mysql> select * from fruits where f_name regexp '^[a-zA-Z]{4}y$';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 | 104 | berry | 7.60 |
+------+------+--------+---------+
单表查询练习题2
CREATE TABLE customers
(
c_id int NOT NULL AUTO_INCREMENT,
c_name char(50) NOT NULL,
c_address char(50) NULL,
c_city char(50) NULL,
c_zip char(10) NULL,
c_contact char(50) NULL,
c_email char(255) NULL,
PRIMARY KEY (c_id)
);
INSERT INTO customers(c_id, c_name, c_address, c_city,
c_zip, c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin',
'300000', 'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane',
'Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000',
'LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou',
'570000', 'YangShan', 'sam@hotmail.com');
【例15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
mysql> select c_id,c_name,c_email
-> from customers
-> where c_email is null;
+-------+----------+---------+
| c_id | c_name | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL |
+-------+----------+---------+
【例16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值，SQL语句如下：
mysql> select c_id,c_name,c_email from customers where c_email is not null;
+-------+---------+-------------------+
| c_id | c_name | c_email |
+-------+---------+-------------------+
| 10001 | RedHook | LMing@163.com |
| 10002 | Stars | Jerry@hotmail.com |
| 10004 | JOTO | sam@hotmail.com |
+-------+---------+-------------------+
【例17】在fruits表中查询s_id = 101，并且f_price大于等于5的水果价格和名称，SQL语句如下：
mysql> select f_name,f_price
-> from fruits
-> where s_id=101 and f_price>5;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| blackberry | 10.20 |
+------------+---------+
【例18】在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称，SQL语句如下：
mysql> select f_name,f_price
-> from fruits
-> where (s_id=101 or s_id=102) and f_price>5;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| blackberry | 10.20 |
| orange | 11.20 |
| banana | 10.30 |
| grape | 5.30 |
+------------+---------+
mysql> select f_name,f_price from fruits where s_id in(101,102) and f_price>5;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| blackberry | 10.20 |
| orange | 11.20 |
| banana | 10.30 |
| grape | 5.30 |
+------------+---------+
【例19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
mysql> select f_price,f_name
-> from fruits
-> where s_id=101 or s_id=102;
+---------+------------+
| f_price | f_name |
+---------+------------+
| 5.20 | apple |
| 10.20 | blackberry |
| 11.20 | orange |
| 3.20 | cherry |
| 10.30 | banana |
| 5.30 | grape |
+---------+------------+
【例20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：
mysql> select f_price,f_name from fruits where s_id in (101,102);
+---------+------------+
| f_price | f_name |
+---------+------------+
| 5.20 | apple |
| 10.20 | blackberry |
| 11.20 | orange |
| 3.20 | cherry |
| 10.30 | banana |
| 5.30 | grape |
+---------+------------+
【例21】查询fruits表中s_id字段的值，返回s_id字段值且不得重复，SQL语句如下：
mysql> select distinct s_id
-> from fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
【例22】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下：
mysql> select f_name
-> from fruits
-> order by f_name;
+------------+
| f_name |
+------------+
| apple |
| apricot |
| banana |
| berry |
| blackberry |
| cherry |
| coconut |
| grape |
| lemon |
| mango |
| melon |
| orange |
| xbababa |
| xbabay |
| xxtt |
| xxxx |
+------------+
【例23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下：
mysql> select f_name,f_price from fruits order by f_name,f_price;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.60 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
【例24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下：
mysql> select f_name,f_price from fruits order by f_price desc;
+------------+---------+
| f_name | f_price |
+------------+---------+
| mango | 15.60 |
| xxtt | 11.60 |
| orange | 11.20 |
| banana | 10.30 |
| blackberry | 10.20 |
| coconut | 9.20 |
| melon | 8.20 |
| berry | 7.60 |
| lemon | 6.40 |
| grape | 5.30 |
| apple | 5.20 |
| xxxx | 3.60 |
| xbababa | 3.60 |
| cherry | 3.20 |
| xbabay | 2.60 |
| apricot | 2.20 |
+------------+---------+
【例25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下：
mysql> select * from fruits order by f_price desc,f_name;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| m1 | 106 | mango | 15.60 |
| m3 | 105 | xxtt | 11.60 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
| b1 | 101 | blackberry | 10.20 |
| o2 | 103 | coconut | 9.20 |
| bs2 | 105 | melon | 8.20 |
| b2 | 104 | berry | 7.60 |
| l2 | 104 | lemon | 6.40 |
| t2 | 102 | grape | 5.30 |
| a1 | 101 | apple | 5.20 |
| t4 | 107 | xbababa | 3.60 |
| b5 | 107 | xxxx | 3.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | xbabay | 2.60 |
| a2 | 103 | apricot | 2.20 |
+------+------+------------+---------+
【例26】根据s_id对fruits表中的数据进行分组，SQL语句如下：
mysql> select max(f_price),min(f_price),avg(f_price) from fruits group by s_id;
+--------------+--------------+--------------+
| max(f_price) | min(f_price) | avg(f_price) |
+--------------+--------------+--------------+
| 10.20 | 3.20 | 6.200000 |
| 11.20 | 5.30 | 8.933333 |
| 9.20 | 2.20 | 5.700000 |
| 7.60 | 6.40 | 7.000000 |
| 11.60 | 2.60 | 7.466667 |
| 15.60 | 15.60 | 15.600000 |
| 3.60 | 3.60 | 3.600000 |
+--------------+--------------+--------------+
【例27】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来，SQL语句如下：
mysql> select s_id,group_concat(f_name) from fruits group by s_id;
+------+-------------------------+
| s_id | group_concat(f_name) |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
+------+-------------------------+
【例28】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息，SQL语句如下：
mysql> select s_id,count(1) from fruits group by s_id having count(1)>1;
+------+----------+
| s_id | count(1) |
+------+----------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 107 | 2 |
+------+----------+
【例29】根据s_id对fruits表中的数据进行分组，并显示记录数量，SQL语句如下：
mysql> select s_id,count(1) from fruits group by s_id;
+------+----------+
| s_id | count(1) |
+------+----------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
+------+----------+
【例30】根据s_id和f_name字段对fruits表中的数据进行分组， SQL语句如下，
mysql> select s_id,f_name from fruits
-> group by s_id,f_name;
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
| 102 | banana |
| 102 | grape |
| 102 | orange |
| 103 | apricot |
| 103 | coconut |
| 104 | berry |
| 104 | lemon |
| 105 | melon |
| 105 | xbabay |
| 105 | xxtt |
| 106 | mango |
| 107 | xbababa |
| 107 | xxxx |
+------+------------+
单表查询练习题3
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cs_id` int(11) NOT NULL COMMENT '课程编号',
`cs_name` varchar(50) NOT NULL COMMENT '课程名称',
`cs_credit` tinyint(255) unsigned DEFAULT NULL COMMENT '课程学分',
`cs_type` char(12) DEFAULT NULL COMMENT '课程类别',
`cs_depart` char(6) DEFAULT NULL COMMENT '院系名称',
PRIMARY KEY (`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `course`(`cs_id`,`cs_name`,`cs_credit`,`cs_type`,`cs_depart`) values (5200313,'数据库原理及应用',4,'核心专业','信工'),(5203314,'计算机导论',4,'通识教育','信工'),(5219314,'数据结构',5,'专业核心','信工'),(5223013,'大学物理',4,'专业基础','信工'),(5227614,'毕业实习',4,'集中实践','信工'),(5230912,'云计算',2,'共同选修','信工'),(5236212,'机器学习',2,'共同选修','信工'),(5237514,'c语言',4,'专业基础','信工'),(5245112,'区块链',2,'任意选修','信工'),(7200422,'知识产权法',2,'任意选修','文法'),(20201833,'概率论',3,'专业基础','基础'),(20202336,'高等数学',6,'专业基础','基础'),(29299131,'劳动教育',1,'集中实践','学务');
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` bigint(11) unsigned NOT NULL COMMENT '学号',
`stu_name` char(12) NOT NULL COMMENT '姓名',
`stu_sex` enum('男','女') DEFAULT NULL COMMENT '性别',
`stu_age` tinyint(255) unsigned DEFAULT NULL COMMENT '年龄',
`stu_major` char(9) DEFAULT NULL COMMENT '专业',
`stu_college` char(12) DEFAULT NULL COMMENT '学院',
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `student`(`stu_id`,`stu_name`,`stu_sex`,`stu_age`,`stu_major`,`stu_college`) values (201804550101,'郭奎','男',22,'计科','信工学院'),(201804550102,'吕宇航','男',18,'计科','信工学院'),(201804550103,'张豪辉','女',19,'计科','信工学院'),(201804550107,'丁志杰','男',17,'金融学','金贸学院'),(201804550109,'范伟','男',19,'金融学','金贸学院'),(201804550116,'张依婷','女',17,'大数据','信工学院'),(201804550120,'张维','男',19,'计科','信工学院'),(201804550121,'朱柳阳','女',20,'计科','信工学院'),(201804550144,'谭兵炎','男',20,'大数据','信工学院'),(201804550153,'杨志强','男',17,'大数据','信工学院');
1.查询全部课程的信息。
mysql> select * from course;
+----------+--------------------------+-----------+--------------+-----------+
| cs_id | cs_name | cs_credit | cs_type | cs_depart |
+----------+--------------------------+-----------+--------------+-----------+
| 5200313 | 数据库原理及应用 | 4 | 核心专业 | 信工 |
| 5203314 | 计算机导论 | 4 | 通识教育 | 信工 |
| 5219314 | 数据结构 | 5 | 专业核心 | 信工 |
| 5223013 | 大学物理 | 4 | 专业基础 | 信工 |
| 5227614 | 毕业实习 | 4 | 集中实践 | 信工 |
| 5230912 | 云计算 | 2 | 共同选修 | 信工 |
| 5236212 | 机器学习 | 2 | 共同选修 | 信工 |
| 5237514 | c语言 | 4 | 专业基础 | 信工 |
| 5245112 | 区块链 | 2 | 任意选修 | 信工 |
| 7200422 | 知识产权法 | 2 | 任意选修 | 文法 |
| 20201833 | 概率论 | 3 | 专业基础 | 基础 |
| 20202336 | 高等数学 | 6 | 专业基础 | 基础 |
| 29299131 | 劳动教育 | 1 | 集中实践 | 学务 |
+----------+--------------------------+-----------+--------------+-----------+
2.查询信工学院开设的课程名、课程号及学分。
mysql> select cs_id,cs_name,cs_credit from course where cs_depart='信工';
+---------+--------------------------+-----------+
| cs_id | cs_name | cs_credit |
+---------+--------------------------+-----------+
| 5200313 | 数据库原理及应用 | 4 |
| 5203314 | 计算机导论 | 4 |
| 5219314 | 数据结构 | 5 |
| 5223013 | 大学物理 | 4 |
| 5227614 | 毕业实习 | 4 |
| 5230912 | 云计算 | 2 |
| 5236212 | 机器学习 | 2 |
| 5237514 | c语言 | 4 |
| 5245112 | 区块链 | 2 |
+---------+--------------------------+-----------+
3.查询学分超过3学分的课程代码、课程名和开课单位。
mysql> select cs_id,cs_name,cs_depart
-> from course
-> where cs_credit>3;
+----------+--------------------------+-----------+
| cs_id | cs_name | cs_depart |
+----------+--------------------------+-----------+
| 5200313 | 数据库原理及应用 | 信工 |
| 5203314 | 计算机导论 | 信工 |
| 5219314 | 数据结构 | 信工 |
| 5223013 | 大学物理 | 信工 |
| 5227614 | 毕业实习 | 信工 |
| 5237514 | c语言 | 信工 |
| 20202336 | 高等数学 | 基础 |
+----------+--------------------------+-----------+
4.查询计科专业和大数据专业的学生信息。
mysql> select stu_id,stu_name from student where stu_major='计科' or stu_major='大数据';
+--------------+-----------+
| stu_id | stu_name |
+--------------+-----------+
| 201804550101 | 郭奎 |
| 201804550102 | 吕宇航 |
| 201804550103 | 张豪辉 |
| 201804550116 | 张依婷 |
| 201804550120 | 张维 |
| 201804550121 | 朱柳阳 |
| 201804550144 | 谭兵炎 |
| 201804550153 | 杨志强 |
+--------------+-----------+
5.查询不是信工学院的学生姓名和学号。
mysql> select stu_id,stu_name
-> from student
-> where stu_college !='信工学院';
+--------------+-----------+
| stu_id | stu_name |
+--------------+-----------+
| 201804550107 | 丁志杰 |
| 201804550109 | 范伟 |
+--------------+-----------+
6.查询年龄是17,18,19的学生姓名和专业。
mysql> select stu_name,stu_major from student where stu_age in (17,18,19);
+-----------+-----------+
| stu_name | stu_major |
+-----------+-----------+
| 吕宇航 | 计科 |
| 张豪辉 | 计科 |
| 丁志杰 | 金融学 |
| 范伟 | 金融学 |
| 张依婷 | 大数据 |
| 张维 | 计科 |
| 杨志强 | 大数据 |
+-----------+-----------+
7.查询学分在2到4之间课程的信息。 *****
mysql> select cs_name
-> from course
-> where cs_credit>2 and cs_credit<4;
+-----------+
| cs_name |
+-----------+
| 概率论 |
+-----------+
8.查询课程名称中带“数据”的课程名、课程号及开课单位。
mysql> select cs_id,cs_name,cs_depart
-> from course
-> where cs_name like '%数据%';
+---------+--------------------------+-----------+
| cs_id | cs_name | cs_depart |
+---------+--------------------------+-----------+
| 5200313 | 数据库原理及应用 | 信工 |
| 5219314 | 数据结构 | 信工 |
+---------+--------------------------+-----------+
9.查询信工学院的的专业有哪些。
mysql> select distinct cs_type
-> from course
-> where cs_depart='信工';
+--------------+
| cs_type |
+--------------+
| 核心专业 |
| 通识教育 |
| 专业核心 |
| 专业基础 |
| 集中实践 |
| 共同选修 |
| 任意选修 |
+--------------+
10.查询年龄为空的学生信息。
mysql> select *
-> from student
-> where stu_age is null;
Empty set (0.00 sec)
11.查询不是信工学院开设的集中实践课的开课单位和课程名称。
mysql> select cs_name,cs_depart
-> from course
-> where cs_depart !='信工' and cs_type='集中实践';
+--------------+-----------+
| cs_name | cs_depart |
+--------------+-----------+
| 劳动教育 | 学务 |
+--------------+-----------+
12.查询信工学院开设的课程的类型有哪些。
mysql> select distinct cs_type
-> from course
-> where cs_depart='信工';
+--------------+
| cs_type |
+--------------+
| 核心专业 |
| 通识教育 |
| 专业核心 |
| 专业基础 |
| 集中实践 |
| 共同选修 |
| 任意选修 |
+--------------+
13.查询学生所在的专业个数。
mysql> select stu_major,count(stu_major) from student group by stu_major;
+-----------+------------------+
| stu_major | count(stu_major) |
+-----------+------------------+
| 大数据 | 3 |
| 计科 | 5 |
| 金融学 | 2 |
+-----------+------------------+
14.查询信工学院开设的课程的平均学分。
mysql> select avg(cs_credit)
-> from course
-> where cs_depart='信工';
+----------------+
| avg(cs_credit) |
+----------------+
| 3.4444 |
+----------------+
15.查询学生的信息，查询结果按姓名升序排序。
mysql> select *
-> from student
-> order by stu_name;
+--------------+-----------+---------+---------+-----------+--------------+
| stu_id | stu_name | stu_sex | stu_age | stu_major | stu_college |
+--------------+-----------+---------+---------+-----------+--------------+
| 201804550107 | 丁志杰 | 男 | 17 | 金融学 | 金贸学院 |
| 201804550102 | 吕宇航 | 男 | 18 | 计科 | 信工学院 |
| 201804550116 | 张依婷 | 女 | 17 | 大数据 | 信工学院 |
| 201804550120 | 张维 | 男 | 19 | 计科 | 信工学院 |
| 201804550103 | 张豪辉 | 女 | 19 | 计科 | 信工学院 |
| 201804550121 | 朱柳阳 | 女 | 20 | 计科 | 信工学院 |
| 201804550153 | 杨志强 | 男 | 17 | 大数据 | 信工学院 |
| 201804550109 | 范伟 | 男 | 19 | 金融学 | 金贸学院 |
| 201804550144 | 谭兵炎 | 男 | 20 | 大数据 | 信工学院 |
| 201804550101 | 郭奎 | 男 | 22 | 计科 | 信工学院 |
+--------------+-----------+---------+---------+-----------+--------------+
16.查询每个专业的学生的最大年龄、最小年龄和平均年龄，查询结果按平均年龄降序排列。
mysql> select max(stu_age),min(stu_age),avg(stu_age)
-> from student
-> group by stu_college
-> order by avg(stu_age)desc;
+--------------+--------------+--------------+
| max(stu_age) | min(stu_age) | avg(stu_age) |
+--------------+--------------+--------------+
| 22 | 17 | 19.0000 |
| 19 | 17 | 18.0000 |
+--------------+--------------+--------------+
17.查询每个开课单位开设的课程门数的，查询结果按课程门数升序排列。
mysql> select count(cs_name)
-> from course
-> group by cs_depart
-> order by count(cs_name) desc;
+----------------+
| count(cs_name) |
+----------------+
| 9 |
| 2 |
| 1 |
| 1 |
+----------------+
18.查询单位开课门数少于2门的开课单位和课程名称。