计算机二级MySQL基本知识点(4)

->from mytest.students ->group by student_sex; 扩展：

mysql> select count(*) as '人数', -> case

-> when student_sex='0' then '男' -> else '女' -> end as '性别'

-> from mytest.students group by student_sex;

+------+------+ | 人数 | 性别 | +------+------+ | 1 | 男 | | 6 | 女 | +------+------+

例23：查询数据库mytest的表students中要求输出每个专业学生的平均年龄和专业名称。 MySQL>select student_major, avg(student_age) as '平均年龄' from mytest.students ->group by student_major;

※HAVING子句——用于指定过滤条件

与WHERE子句的区别：WHERE子句主要用于过滤数据行，而HAVING子句主要用于过滤分组。

例24：在数据库mytest中表students，查找专业学生数量大于2的学生总数和专业名称。 MySQL>select student_major, count(*) as '总人数' ->from mytest.students ->group by student_major ->having count(*) >2;

例25：在数据库mytest中表students，按照学生年龄的降序方式，输出学生姓名和年龄。 MySQL>select student_name, student_age from mytest.students ->order by student_age desc;

※LIMIT子句：表示从第<标志位置>+1行开始取<行数>行数据。 比如：limit4,3指的是从第五行开始取三行数据。

例26：在数据库mytest中表students，查找从第五个学生开始的三个学生的id和姓名。 Mysql>select student_id,student_name

->from mytest.students ->limit 4,3;

※UNION语句与联合查询：

联合查询：把来自多个SELECT语句查询的结果组合到一个结果集中，并且作为单个查询结果集返回，这种查询方式称为并或者联合查询。

UNION的语法格式：SELECT?UNION[ALL|DISTINCT]SELECT? All：表示当两个结果集有重复记录的时候，允许重复记录存在；

Distinct：表示去除两个结果集中重复出现的记录，默认是DISTINCT。

例27：在数据库mytest中表students，使用UNION关键字合并专业是“物理专业”和性别是“1”的学生姓名、性别和专业信息。

Mysql>select student_name,student_sex,student_major

->from mytest.students where student_major='物理专业' ->union

->select student_name,student_sex,student_major ->from mytest.students where student_sex='1';

简单而言，UNION的使用方法非常简单，只需给出每条SELECT语句然后在各条SELECT语句之间加上关键字UNION即可。

注意：UNION语句只使用一条ORDER BY 语句或者LIMIT子句，且它们必须置于最后一条，SELECT语句之后。

课后总复习错题

6.在下列有关group by语句的描述中，不正确的是（B）

A.分组条件可以有多个，并且每一个可以分别指定排序方式； B.可以使用WHERE子句对所得的分组进行筛选；

C.GROUP BY可以配合聚合函数一起使用，但GROUP BY子句不能直接使用聚合函数； D.除了聚合函数，SELECT语句中的每个列都必须在GROUP BY子句中给出。 解析：如果对排序需要有条件筛选，应该在属性名后接 having +条件 select 选择的列 from 表

where 查询的条件

group by 分组属性 having 分组过滤的条件 order by 排序属性

limit 起始记录位置，取记录的条数

到了group by那一步已经是对查询的条件进行分组了，所以就算要加条件也不是where，而是having。

7.对于SQL查询：SELECT *from tbl_name where id=(select id from tbl_name)，假设该表中包含id字段，那么该语句正确执行的条件是（C） A.该表中必须有多条记录； B.该表中必须只有一条记录；

C.该表中记录数必须小于等于一条；

D.此SQL语句错误，无论如何都无法正确执行。

解析：当表中记录多于1条记录时，(SELECT id FROM tbl_name)返回的是一个结果集，把结果集赋给id，显然执行语句失败，当记录小于等于1时，返回的是空或者是id值，可以作为条件查询。

9.学生表student如下所示： 学号 021 026 056 101 姓名 林山 张宏 王林 赵松 所在系编号 02 01 02 04 总学分 32 26 22 NULL 下面SQL语句中返回值为3的是（D） A.SELECT COUNT(*) FROM STUDENT;

B.SELECT COUNT(所在系编号) FROM STUDENT;

C.SELECT COUNT(*) FROM STUDENT GROUP BY 学号; D.SELECT COUNT(总学分) FROM STUDENT;

解析：A返回4；B返回4；C返回4四个1；D返回3。

其中，B答案中要写成“SELECT COUNT(DISTINCT 所在系编号) FROM STUDENT;”结果才会返回3； 二、基本操作题：

数据库db_emp中有职工表tb_employee和部门表tb_dept,tb_employee包含的字段有eno（职工号）、ename（姓名）、age（年龄）、title（职务）、salary（工资）和deptno（部门号），tb_dept包含的字段有deptno（部门号）、dname（部门名称）、manager（部门负责人）、telephone（电话）。

（1）用SQL语句完成以下操作：给企业新增加一个“公关部”，部门号为“D4”，电话为“010-82953306”，并任命“Liming”担任部门负责人。 首先建立数据库和表格：

mysql> create database db_emp; mysql> use db_emp Database changed

mysql> create table tb_employee -> (eno char(7) primary key, -> ename char(20), -> age int,

-> title char(20), -> salary int,

-> deptno char(10)); mysql> create table tb_dept

-> (deptno char(10) primary key, -> dname char(20), -> manager char(20), -> telephone char(20)); 第一题过程如下：

mysql> insert into db_emp.tb_dept(deptno,dname,manager,telephone)

-> values('D4','公关部','Liming','010-82953306'); Modify和change的区别：

如果只是要改列的属性类型而不该列的名字，则两者区别如下： Mysql>alter table db_emp.tb_dept

->change column manager manager char(20); Mysql>alter table db_emp.tb_dept

->modify column manager char(20);

原因：因为change 的功能还可以改变列的名字所以第一个manager表示的是原来列名，第二个manager表示的是更改过后的列名，如果没有区别的话，那么两个列名应该是一样的， 都是manager。

（2）用SQL语句将tb_employee表中的salary字段的默认值修改为3500. mysql> alter table db_emp.tb_employee

-> alter column salary set default 3500;

（3）用SQL语句查询“销售部”的员工人数，要求查询结果显示为“总人数”。 首先对两个表格进行信息的输入：

mysql> insert into db_emp.tb_employee(eno,ename,age,title,salary,deptno) -> values('2550','李静',23,'普通职员',3500,'D4'), -> ('2603','李鹏鹏',22,'普通职员',3600,'D4'), -> ('2506','李欢',26,'主任',4500,'D4'), -> ('2410','郭红红',36,'主任',5500,'D3'), -> ('2568','邓英',26,'普通职员',4000,'D3'), -> ('2563','赵佳力',27,'普通职员',default,'D2');

mysql> insert into db_emp.tb_dept(deptno,dname,manager,telephone) -> values('D3','销售部','赵小溪','1562983446'), -> ('D2','后勤部','陈琪琪','1459856325'),

-> ('D4','公关部','Liming','010-82953306'); 第三题过程如下：

mysql> select count(eno) as '总人数' from db_emp.tb_employee

-> where deptno=(select deptno from db_emp.tb_dept where dname='销售部');

※索引：根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，索引实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。 ※两种方式访问数据库表行数据的方式：

①顺序访问：在表中实行全表扫描，从头到尾逐行遍历； ②索引访问：通过遍历索引来直接访问表中记录行的方式。 ※索引的分类：

①B-树索引——基于树形的数据结构查找数据；

②哈希索引——根据索引列对应的哈希值的方法获取表的记录行。 特点：访问速度快，但是建立哈希索引会耗费更多时间； 只支持等值比较，不能使用HASH索引排序。 ※索引在逻辑上分为以下五类： ①普通索引； ②唯一性索引； ③主键； ④空间索引； ⑤全文索引。

※在实际应用中通常分为单列索引和组合索引； ※创建索引的方法：

①用CREATE INDEX语句创建索引：

CERATE <索引名>ON <表名>(<列名>[<长度>][ASC|DESC]) 索引列长度的最大上限为255个字节。

例1：在数据库mytest中表students，根据学生姓名前三个字符，采用默认的索引类型，创建一个升序索引index_students。 Mysql>create index index_students

->on mytest.students(student_name(3) asc); 语句执行成功以后可以查看已经创建的索引：

Mysql>show index from mytest.students;

例2：在数据库mytest表students中，根据student_name和student_id采用BTREE的索引类型，创建一个复合索引index_stud。 Mysql>create index index_stud

->on mytest.students(student_name,student_id) ->using btree;

语句执行成功以后可以查看已经创建的索引： Mysql>show index from mytest.students;

②使用CREATE TABLE语句来创建索引——即在创建表的过程中同时创建。 CONTRAINT PRIMARY KEY(列名) UNION

FOREIGN KEY

例3：在已有的数据库mytest中新建一个课程信息表包含下列字段：课程号、课程名称、上课教室和任课老师姓名。要求在创建该表的同时，将课程号作为主键，并且给课程名称创建索引。

Mysql>use mytest;

MySQL>create table course ->(course_id int not null,

->course_name char(50) not null, ->course_place char(50) null, ->course_teacher char(50) null, ->primary key(course_id),

->index index_course (course_name)); ③使用ALTER TABLE 语句来创建索引：

ALTER TABLE可以在一个已有的表上创建索引。

例4：在数据库mytest表course中，为course_place字段添加一个非唯一的索引，取名为index_place。

MySQL>alter table mytest.course

->add index index_place(course_place);

※查看索引：show index from ??

例5：显示数据库mytest的表course的索引情况。 两种表达方式：

①mysql>show index from mytest.course;

②mysql>show index from course from mytest;

※删除索引：

①使用DROP INDEX 语句：

语法格式——DROP INDEX <索引名>ON<表名>; 例6：删除例4创建的索引index_place。 Mysql>drop index index_place on mytest.course; ②使用ALTER TABLE语句：

例7：删除数据库mytest的表students的索引index_students。

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库计算机二级MySQL基本知识点(4)在线全文阅读。