数据库-DML增删改查

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 |
+-------------+-------------+-------------+-------------+

数值型函数

数据库-DML增删改查

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 |
+---------------------+

字符串函数

数据库-DML增删改查

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            |
+------------------+

日期和时间函数

数据库-DML增删改查

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 |
+-------------------+

流程控制函数

数据库-DML增删改查

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。
数据库-DML增删改查

单表查询练习题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门的开课单位和课程名称。
上一篇:HIVE的 “DDL其他语法、DML语法、DQL语法、高阶排序、join相关内容”


下一篇:MySql:数据操纵语言(DML)