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

目录

 数据库基本概念

表和视图

记录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为一个学生表。

学    号	姓    名	专  业  名	性    别	出 生 时 间
170201	王  一	计算机	男	1998/10/01
170202	王  巍	计算机	女	1999/02/08
170302	林  滔	电子工程	男	1998/04/06
170303	江为中	电子工程	男	2001/12/08

• 表结构

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

• 记录

每个表包含若干行数据，它们是表的“值”，表中的一行称为一个记录（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子查询 */
}

 (c) GROUP BY子句和ORDER BY子句分别对查询结果进行分组和排序。

下面用示例说明使用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。

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

本例对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;