数据库基本概念和常用SQL命令

短命女 2022-12-27 11:01 167阅读 0赞

**目录**

数据库基本概念

表和视图

记录record操作

查询select

聚合操作

插入数据INSERT

删除数据DELETE

更新数据UPDATE

数据库操作

查看所有数据库

查看当前使用的数据库

创建数据库

删除数据库

使用数据句库

查看数据库中所有表

表的操作

查看表结构

创建表结构的语法

demo：创建班级和学生表

修改表–添加字段

修改表–修改字段–重命名版

修改表–修改字段–不重命名

删除表–删除字段

删除表

查看表的创建语句–详细过程

--------------------

# 数据库基本概念 #

数据库（DataBase，DB），顾名思义，就是存放数据的仓库，其特点是：数据按照数据模型组织，是高度结构化的，可供多个用户共享并且具有一定的安全性。

**数据库管理系统（DataBase Management System，DBMS）**，是位于用户应用程序和操作系统之间的数据库管理系统**软件**，其主要功能是组织、存储和管理数据，高效地访问和维护数据，即提供数据定义、数据操纵、数据控制和数据维护等功能。常用的数据库管理系统有Oracle、Microsoft SQL Server和MySQL等。

数据库系统（DataBase System，DBS），是指按照数据库方式存储和维护数据，并向应用程序提供数据访问接口的系统。DBS通常由数据库、计算机硬件（支持DB存储和访问）、软件（包括操作系统、DBMS及应用开发支撑软件）和数据库管理员（DataBase Administrator，DBA）四个部分组成。其中，DBA是控制数据整体结构的人，负责数据库系统的正常运行，承担创建、监控和维护整个数据库结构的责任。DBA必须具有的素质是，熟悉所有数据的性质和用途，充分了解用户需求，对系统性能非常熟悉。

SQL语言由以下三部分组成。

（1）数据定义语言（Data Description Language，DDL），用于执行数据库定义的任务，对数据库及数据库中的各种对象进行创建、删除和修改等操作。数据库对象主要包括表、默认约束、规则、视图、触发器和存储过程等。

（2）数据操纵语言（Data Manipulation Language，DML），用于操纵数据库中各种对象，检索和修改数据。

（3）数据控制语言（Data Control Language，DCL），用于安全管理，确定哪些用户可以查看或修改数据库中的数据。

## 表和视图 ##

（1）表（Table）。 表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，如表1为一个学生表。

*  表结构

每个数据库包含若干个表。每个表具有一定的结构，称为表的“型”。所谓表型是指组成表的各列的名称及数据类型，也就是日常表格的“栏目信息”。

*  记录

每个表包含若干行数据，它们是表的“值”，表中的一行称为一个记录（Record）。因此，表是记录的有限集合。

*  字段

每个记录由若干个数据项构成，将构成记录的每个数据项称为字段（Field）。字段包含的属性有字段名、字段数据类型、字段长度及是否为关键字等。其中，字段名是字段的标识，字段的数据类型可以是多样的，如整型、实型、字符型、日期型或二进制型等。

*  关键字

在学生表中，若不加以限制，则每条记录的姓名、专业名、性别和出生时间这四个字段的值都有可能相同，但是学号字段的值对表中所有记录来说则一定不同，即通过“学号”字段可以将表中的不同记录区分开来。

若表中记录的某一字段或字段组合能够唯一标志记录，则称该字段或字段组合为候选关键字（Candidate Key）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primary key）,也称为主键。当一个表仅有唯一一个候选关键字的时候，该候选关键字就是主关键字。如学生表中的学号就是主关键字。

若某字段或字段组合不是数据库中A表的关键字，但它是数据库中另外一个表即B表的关键字，则称该字段或字段组合为A表的外关键字（Foreign key）。 例如，设学生数据库有三个表，即学生表、课程表和学生成绩表，

其结构分别如下：

学生表（学号，姓名，专业名，性别，出生时间）

课程表（课程号，课程名，学分）

学生成绩表（学号，课程号，分数） （用下画线表示的字段或字段组合为关键字。）

由此可见，单独的学号和课程号都不是学生成绩表中的关键字，但他们分别是学生表和课程表中的关键字，因此它们都是学生成绩表的外关键字。

（2）视图（View）。 视图是从一个或多个表（或视图）导出的表。 视图与表不同，它是一个虚表，即对视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义操作与视图相关联的基本表。视图一经定义后，就可以像表一样被查询、修改、删除和更新。使用视图具有便于数据共享、简化用户权限管理和屏蔽数据库的复杂性等优点。 例如，对于以上所述学生数据库，可创建“学生选课”视图，该视图包含学号、姓名、课程号、课程名、学分和成绩字段。

# 记录record操作 #

## 查询select ##

完备的SELECT语句很复杂，其主要的子句如下：

SELECT [DISTINCT] [别名.]字段名或表达式 [AS 列标题]	/* 指定要选择的列或行及其限定 */
    						
    FROM  table_source                          			/* FROM子句，指定表或视图 */
    [ WHERE  search_condition ]                			/* WHERE子句，指定查询条件 */
    						
    [ GROUP BY group_by_expression ]               
                                       					/* GROUP BY子句，指定分组表达式 */
    [ ORDER BY order_expression [ ASC | DESC ]]  
                                      					/* ORDER BY子句，指定排序表达式和顺序 */

其中，**SELECT和FROM子句是不可缺少的**。

(a) SELECT子句指出查询结果中显示的字段名，以及字段名和函数组成的表达式等。可用**DISTINCT**去除重复的记录行；**AS**列标题指定查询结果显示的列标题。当要显示表中所有字段时，可用通配符“\*”代替字段名列表。

(b) WHERE子句定义了查询条件。WHERE子句必须紧跟FROM子句，其基本格式为：

WHERE <search_condition>

其中，search\_condition为查询条件，常用格式为：

> \{ \[ NOT \] <predicate> | (<search\_condition> ) \} \[ \{ AND | OR \} \[ NOT \] \{ <predicate> | (<search\_condition>) \} \] \} \[ ,…n \]

其中的predicate为判定运算，结果为TRUE、FALSE或UNKNOWN，格式为：

{ expression { = | < | <= | > | >= | <> | != | !< | !> } expression 										/* 比较运算 */
 | string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_ character' ]	 							/* 字符串模式匹配 */
 | expression [ NOT ] BETWEEN expression AND expression	/* 指定范围 */
 | expression IS [ NOT ] NULL 			/* 是否空值判断 */
 | expression [ NOT ] IN ( subquery | expression [,…n] )	/* IN子句 */
 | expression { = | < | <= | > | >= | <> | != | !< | !> } { ALL | SOME | ANY } ( subquery ) 						/* 比较子查询 */
 | EXIST ( subquery ) 			/* EXIST子查询 */
 }

下面用示例说明使用SQL语句对Student数据库进行的各种查询。

（1）查询Student数据库。查询students表中每个同学的姓名和总学分。

USE Student SELECT name,totalscore FROM students

（2）查询表中所有记录。查询students表中每个同学的所有信息。

SELECT * FROM students

（3）条件查询。查询students表中总学分大于或等于120的同学的情况。

SELECT * FROM students WHERE totalscore >=  120

（4）多重条件查询。查询students表中所在系为“计算机”且总学分大于或等于120的同学的情况。

SELECT * FROM students WHERE department='计算机' AND totalscore >= 120

（5）使用**LIKE**谓词进行模式匹配。查询students表中姓“王”且单名的学生情况。

SELECT * FROM students WHERE name LIKE '王_'

（6）用BETWEEN…AND指定查询范围。查询students表中不在1999年出生的学生情况。

SELECT * FROM students WHERE birthday NOT BETWEEN '1999-1-1' and '1999-12-31'

（7）空值比较。查询总学分尚不确定的学生情况。

SELECT * FROM students   WHERE totalscore IS NULL

（8）自然连接查询。查找计算机系学生姓名及其“C程序设计”课程的考试分数情况。

SLELCT name,grade FROM students, courses,grades,
         WHERE department = '计算机' AND  coursename= ' C程序设计' 
         AND students.studentid = grades.studentid  AND courses.courseid = grades.coursesid

（9）IN子查询。查找选修了课程号为101的学生情况。

SELECT * FROM students
    	WHERE studentid IN
    		  ( SELECT studentid FROM courses WHERE courseid = '101' )

在执行包含子查询的SELECT语句时，系统首先执行子查询，产生一个结果表，再执行外查询。本例中，首先执行子查询：

SELECT studentid FROM courses, students,grades WHERE courseid = '101' 
     	  AND students.studentid = grades.studentid  AND courses.courseid =
     	     grades.coursesid

得到一个只含有studentid列的结果表，courses中courseid列值为101的行在该结果表中都有一行。再执行外查询，若students表中某行的studentid列值等于子查询结果表中的任意一个值，则该行就被选择到最终结果表中。

（10）比较子查询。这种子查询可以认为是IN子查询的扩展，它是表达式的值与子查询的结果进行比较运算。查找课程号206的成绩不低于课程号101的最低成绩的学生学号。

SELECT studentid FROM grades
 	 WHERE courseid = '206' AND grade !< ANY 
 	 ( SELECT grade FROM grades
 		WHERE courseid = '101'
 		 )

（11）EXISTS子查询。EXISTS谓词用于测试子查询的结果集是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXISTS刚好相反。查找选修206号课程的学生姓名。

SELECT name FROM students
          WHERE EXISTS
             ( SELECT * FROM grades
                     WHERE studentid = students.studentid AND courseid = '206' 
    		)

(12)查找选修了全部课程的同学姓名（查找没有一门功课不选修的学生）。

SELECT name FROM students
       WHERE NOT EXISTS
           ( SELECT * FROM courses
                 WHERE NOT EXISTS
                 		( SELECT * FROM grades
                       	WHERE studentid= students.studentid
                              AND courseid=courses.courseid
    		 		)
           )

（13）查询结果分组。将各课程成绩按学号分组。

SELECT studentid,grade FROM grades
        GROUP BY studentid

（14）查询结果排序。将计算机系的学生按出生时间先后排序。

SELECT * FROM students
       WHERE department = '计算机'
         ORDER BY birthday

## 聚合操作 ##

在对表数据进行检索时，经常需要对结果进行汇总或计算，如在学生成绩数据库中求某门功课的总成绩、统计各分数段的人数等。聚合函数用于计算表中的数据，返回单个计算结果。常用的聚合函数见表13.2。

<table> 
 <tbody> 
 <tr> 
 <td> 函&nbsp;&nbsp;数&nbsp;&nbsp;名 </td> 
 <td> 说&nbsp;&nbsp;&nbsp;&nbsp;明 </td> 
 </tr> 
 <tr> 
 <td> AVG </td> 
 <td> 求组中值的平均值 </td> 
 </tr> 
 <tr> 
 <td> COUNT </td> 
 <td> 求组中项数，返回int类型整数 </td> 
 </tr> 
 <tr> 
 <td> MAX </td> 
 <td> 求最大值 </td> 
 </tr> 
 <tr> 
 <td> MIN </td> 
 <td> 求最小值 </td> 
 </tr> 
 <tr> 
 <td> SUM </td> 
 <td> 返回表达式中所有值的和 </td> 
 </tr> 
 <tr> 
 <td> VAR </td> 
 <td> 返回给定表达式中所有值的统计方差 </td> 
 </tr> 
 </tbody> 
</table>

本例对students表执行查询，使用常用的聚合函数。

（1）求选修课程101的学生的平均成绩。

SELECT AVG(grade) AS ' 课程101平均成绩'
        FROM grades   
        WHERE courseid = '101'

（2）求选修课程101的学生的最高分和最低分。

SELECT MAX(grade) AS '课程101最高分' , MIN(grade) AS '课程101最低分'
        FROM grades   
        WHERE courseid = '101'

（3）求学生的总人数。

SELECT COUNT(*) AS '学生总数'
       	FROM students

## **插入数据INSERT** ##

INSERT语句可添加一条或多条记录至一个表中。 INSERT语句有两种语法形式。 语法1：

INSERT INTO target [IN externaldatabase] (fields_list)			//(a)
    {DEFAULT VALUES|VALUES(DEFAULT|expression_list)}			//(b)

语法2：

INSERT INTO target [IN externaldatabase] fields_list
    {SELECT...|EXECUTE...}

其中，

(a) target是欲追加记录的表（Table）或视图（View）的名称，externaldatabase是外部数据库的路径和名称。

(b) expression\_list：需要插入的字段值表达式列表，其个数应与记录的字段个数一致，若指定要插入值的字段fields\_list，则应与fields\_list的字段个数相一致。 使用第1种形式将一个记录或记录的部分字段插入表或视图中。第2种形式的INSERT语句插入来自SELECT语句或来自使用EXECUTE语句执行的存储过程的结果集。

例如，用以下语句向students表添加一条记录：

INSERT INTO students
      	VALUES('170206','罗亮', 0 ,'1/30/1998', 1, 150)

## **删除数据DELETE** ##

DELETE语句用于从一个或多个表中删除记录。 DELETE语句的语法格式如下：

DELETE FROM table_names
    [WHERE...]

例如，用以下语句从students表中删除姓名为“罗亮”的记录：

DELETE FROM students
      	WHERE name = '罗亮'

## **更新数据UPDATE** ##

UPDATE语句用于更新表中的记录。 UPDATE语句的语法格式如下：

UPDATE table_name SET Field_1=expression_1[,Field_2=expression_2...] [FROM table1_name|view1_name[,table2_name|view2_name...]] [WHERE...]

其中，Field是需要更新的字段，expression表示要更新字段的新值表达式。 例如，以下语句将计算机系学生的总分增加10：

UPDATE students SET totalscore = totalscore +10 WHERE department = '计算机'

# 数据库操作 #

*  ## 查看所有数据库 ##

show databases;

*  ## 查看当前使用的数据库 ##

select database();

*  ## 创建数据库 ##

create databases 数据库名 charset=utf8;

*  ## 删除数据库 ##

drop database 数据库名

*  ## 使用数据句库 ##

use database 数据库名

*  ## 查看数据库中所有表 ##

show tables;

# 表的操作 #

*  ## 查看表结构 ##

desc 表名

*  ## 创建表结构的语法 ##

create table table_name(
    字段名 数据类型 可选的约束条件）；

## demo：创建班级和学生表 ##

create table classes(
        id int unsigned auto_increment primary key not null,
        name varchar(10)
    );

create table students(
        id int unsigned primary key auto_increment not null,
        name varchar(20) default '',
        age tinyint unsigned default 0,
        height decimal(5,2),
        gender enum('男','女'),
        cls_id int unsigned default 0
    )

>  *  not null 意思是给字段不允许为空，NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
>  *  primary key 设置当前字段为主关键字，PRIMARY KEY 约束唯一标识数据库表中的每条记录。
>  *  **Auto-increment 会在新记录插入表中时生成一个唯一的数字。**
>     
>     我们通常希望在每次插入新记录时，自动地创建主键字段的值，我们可以在表中创建一个 auto-increment 字段。

*  ## 修改表–添加字段 ##

alter table 表名 add 列名 类型
    demo：alter table students add birthday datetime;

*  ## 修改表–修改字段–重命名版 ##

alert table 表名 change 原名 新名 类型及约束
    demo：alter table syudents change birthday birth  datetime not null;

*  ## 修改表–修改字段–不重命名 ##

alter table 表名 modify 列名 类型及约束
    demo : alter table students modify birth date not null;

*  ## 删除表–删除字段 ##

alter table 表名 drop 列名
    demo :later table students drop birthday;

*  ## 删除表 ##

drop table 表名
    demo:drop table students;

*  ## 查看表的创建语句–详细过程 ##

show create table 表名
    demo : show create tabele students;