数据库基本概念和常用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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

SOC罗三炮

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值