数据库学习——数据定义、单表查询

一、数据定义

1.模式的定义与删除

1.1 数据库定义

（1）查询所有数据库：show databases

（2）查询当前数据库：select database()


（3）创建数据库：

create database [if not exists] 数据库名 
[default charset 字符集] 
[collate 排序规则];

例如：

CREATE DATABASE IF NOT EXISTS MyDatabase  
DEFAULT CHARACTER SET utf8mb4  
COLLATE utf8mb4_unicode_ci;

(4) 删除： drop database [if exists] 数据库名
(5) 使用： use 数据库名

1.2 定义模式与删除模式

（1）
create schema <模式名> authorization <用户名>
然而，需要注意的是，并非所有的数据库系统都明确区分“模式”和“数据库”的概念。在某些系统中（如PostgreSQL），模式被用作数据库内部组织对象的一种方式，而在其他系统（如MySQL）中，模式通常与数据库是同义词，即一个数据库就是一个模式。
对于支持CREATE SCHEMA语句的数据库系统（如PostgreSQL），你可以使用AUTHORIZATION子句来指定新模式的所有者（即用户名）
（2）
drop schema <模式名> <cascade|restrict>

• cascade:级联，删除模式的同时把该模式中所有的数据库对象全部删除
• restrict:限制，如果该模式中定义了下属的数据库对象(表、视图等),则拒绝该删除语句的执行；仅当该模式中没有任何下属的对象时才能执行

例如：drop schema zhang cascade
删除模式zhang，同时该模式中定义的表tab1也被删除

基本表的定义、删除与修改

1.基本表的定义

/* 建立“学生”表student，学号是主码，姓名取值唯一*/
create table student
( Sno char(9) primary key, /* 列级完整性约束，Sno是主码*/
 Sname char(20) unique,  /* Sname取唯一值*/
 Ssex char(2),
 Sage smallint,
 Sdept char(20)
 );
 create table course
( Cno char(4) primary key,
 Cname char(40),
 Cpno char(4),
 Ccreadit smallint,
 foreign key(Cpno) references Course(Cno)  
 /* Cpno是外码，
 被参照表是Course，被参照列是Cno*/
 );
 create table SC
( Sno char(9),
 Cno char(4),
 grade smallint,
 primary key(Sno,Cno),
 /* 主码由两个属性构成，必须作为表级完整性进行定义*/
 foreign key(Sno) references student(Sno),
 /* 表级完整性约束条件，Sno是外码，被参照表是Student*/
 foreign key (Cno) references course(Cno)
 );

2.数据类型

3.模式与表

• 每一个基本表都属于某一个模式
• 一个模式包含多个基本表
• 定义基本表所属模式
  
• 创建基本表时，若没有指定模式，系统根据搜索路径来确定该对象所属模式
• 关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式
• 若搜索路径中的模式名都不存在，系统将给出错误

4.修改基本表

例子：

/* 向student表增加“入学时间”列，其数据类型为日期型*/
alter table student add S_entrance date;

不管基本表原来是否有数据，新增加的列一律为空值
例子2

/* 将年龄的数据类型由字符型改为整数*/
alter table student 
alter column Sage int;

/* 增加课程名称必须取唯一值的约束条件*/
alter table course 
add unique(Cname)

5.删除基本表

二、数据查询

• select 子句：指定要现实的属性列
• from子句：指定查询对象（基本表或视图）
• where子句：指定查询条件
• group by子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数
• having短语：只有满足指定条件的组才予以输出
• order by 子句：对查询结果表按指定列值的升序或降序排序

1.单表查询

查询仅涉及一个表

1.1 选择表中的若干列

/* 查询全体学生的学号和姓名 */
select ID,name
from student

1.1.1 查询全部列

select *
from student

1.1.2 查询经过计算的值

/* 查询全体学生的姓名及出生年份 */
select name, 2014-age
from student

1.2 选择表中的若干元组

1.2.1 消除取值重复的行

如果没有指定distinct关键字，则缺省为all

select ID  /* 等价于  select all ID */
from takes;

指定distinct关键字，去掉表中重复的行

select distinct ID  
from takes;

1.2.2 查询满足条件的元组

（1）比较大小

/* 查询计算机科学系全体学生名单*/
select name
from student
where dept_name='Com. Sci. '

1.2.3 确定范围

1.2.4 确定集合

谓词： in<值表> ,not in <值表>

select ID,name
from student
where dept_name in ('Comp. Sci.','History');

1.2.4 字符匹配

1.2.5 涉及空值的查询

谓词：is null 或 is not null
is 不能用‘=’代替

[例3.36]  某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
	  SELECT Sno，Cno
      FROM    SC
      WHERE  Grade IS NULL

[例3.37]  查所有有成绩的学生学号和课程号。
      SELECT Sno，Cno
      FROM     SC
      WHERE  Grade IS NOT NULL;

1.2.6 多重条件查询

逻辑运算符：and和or来连接多个查询条件
and的优先级高于or
可以用括号改变优先级

[例3.38]  查询计算机系年龄在20岁以下的学生姓名。
      SELECT Sname
       FROM  Student
       WHERE Sdept= 'CS' AND Sage<20;

[例3.27]  查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。
SELECT Sname, Ssex
FROM     Student
WHERE  Sdept IN ('CS ','MA ','IS')
可改写为：
SELECT Sname, Ssex
FROM     Student
WHERE  Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';

1.3 order by 子句

• 可以按一个或多个属性列排序
• 升序：asc 降序：desc 缺省值为升序
• 对于空值，排序时显示的次序由具体系统实现来决定（空值默认为最大）

[例3.39]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
        SELECT Sno, Grade
        FROM    SC
        WHERE  Cno= ' 3 '
        ORDER BY Grade DESC;

[例3.40]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
        SELECT  *
        FROM  Student
        ORDER BY Sdept, Sage DESC;  

1.4 聚集函数

• 统计元组个数：count(*)
• 统计一列中值的个数：count([distict|all]<列名>)
• 计算一列值的总和：sum([distict|all]<列名>)
• 计算一列值的平均值： avg([distict|all]<列名>)
• 求一列中的最大值和最小值：max([distict|all]<列名>) min([dictict|all]<列名>)

[例3.41]  查询学生总人数。
    SELECT COUNT(*)
    FROM  Student; 
[例3.42]  查询选修了课程的学生人数。
     SELECT COUNT(DISTINCT Sno)
     FROM SC;
[例3.43]  计算1号课程的学生平均成绩。
          SELECT AVG(Grade)
          FROM    SC
          WHERE Cno= ' 1 ';

[例3.44]  查询选修1号课程的学生最高分数。
   SELECT MAX(Grade)
   FROM SC
   WHERE Cno='1';
[例3.45 ] 查询学生201215012选修课程的总学分数。
 	   SELECT SUM(Ccredit)
       FROM  SC,Course
       WHERE Sno='201215012' AND 
       SC.Cno=Course.Cno; 

1.5 group by子句

细化聚集函数的作用对象

• 如果未对查询结果分组，聚集函数将作用于整个查询结果
• 对查询结果分组后，聚集函数将分别作用于每个组
• 按指定的一列或多列值分组，值相等的为一组

[例3.46]  求各个课程号及相应的选课人数。
SELECT Cno，COUNT(Sno)
     FROM    SC
     GROUP BY Cno; 

[例3.47]  查询选修了3门以上课程的学生学号。 
 SELECT Sno
     FROM  SC
     GROUP BY Sno
     HAVING  COUNT(*) >3;       

HAVING短语与WHERE子句的区别：

• 作用对象不同
• WHERE子句作用于基表或视图，从中选择满足条件的元组
• HAVING短语作用于组，从中选择满足条件的组。