1.4 数据库和常用SQL语句(正文)——MySQL数据库命令和SQL语句

时间:2024-01-28 17:47:10

前面我们已经讲述了,登录时,我们使用mysql –u root –p命令进行,此时如果设置了密码,则需要输入密码。

输入密码后即进入MySQL的操作界面,此时,命令行窗体左侧显示“mysql>”表示此时可接受mysql命令。

  • (1)列出全部数据库命令

我们使用“show databases;”命令列出当前MySQL管理的全部数据库。(注意:mysql命令以英文;为结束符)。

 

这里可以看到,目前mysql管理的数据库共有4个。

  • (2)切换数据库

我们可以在上述显示的4个数据库中进行切换,表示当前所使用的是哪个数据库。我们使用“use”+数据库名进行数据库切换。如

 

  • (3)列出当前数据库下全部表

我们使用“show tables;”命令,列出当前数据库下全部表名称,如

 

  • (4)创建一个数据库。

使用“create database”+数据库名,来创建一个新的数据库。如,按本节开始的例子,我们创建一个名为“db_edu_sys”的数据库,并切换至该数据库。

 

从提示信息上我们看到,我们成功创建了名为“db_edu_sys”,并将当前数据库切换至该数据库。

  • (5)向数据库中添加表。

我们使用SQL语句“CREATE TABLE”命令进行添加表的操作。

例如,上述例子中,我们建立教师表(Teacher),可以写成如下SQL语句

CREATE TABLE teacher(ID int auto_increment primary key,t_name varchar(30),t_title varchar(50));

 

 

这时,我们再查看数据库中的表,可以看到新建立的教师表(teacher)。

 

  • (6)导入sql文本

有时,对多条SQL语句进行操作时,我们一条一条的写入不是很方便,尤其是在有事务操作和结构化语句时,就更加困难,因此,我们可以将SQL语句事先写好,保存在文本中,然后一次性导入到数据库中,我们使用如下命令对sql文本进行导入。

“source ”+文本文件

如source c:/createtable.sql

(如果不写路径名,则默认为当前路径,即执行“mysql –u root –p”时前面显示的路径。)

我们将学生表、课程表、选课表、教学表写到一个sql文本中,一次导入。这个文本我们命名为createtable.sql,其内容如下:

CREATE TABLE student(ID int auto_increment primary key,s_name varchar(30),s_class varchar(50));

CREATE TABLE course(ID int auto_increment primary key,c_name varchar(30),c_credit varchar(50));

CREATE TABLE teaching(ID int auto_increment primary key,t_ID int,c_ID int);

CREATE TABLE selection(ID int auto_increment primary key,s_ID int,c_ID int);

 

我们运行导入,结果如下:

 

  • (7)查看表结构

我们可以通过“DESCRIBE”+表名,来查看某一张表的表结构,如执行“DESCRIBE teacher”结果如下

 

  • (8)添加数据

向表内添加数据要使用SQL语句“INSERT INTO”,如,我们要向teacher表中添加一条教师的信息,可以写成如下形式:

INSERT INTO teacher(t_name,t_title) VALUES(\'张老师\',\'副教授\');

 

执行后,如下

 

  • (9)查询数据

我们通过SQL语句“SELECT … FROM … WHERE …”进行查询,其中“SELECT”后面是要查询的字段名称,可以多个,也可以用“*”,表示查询全部字段,“FROM”后面是要查询的表名,“WHERE”后面是查询条件,支持“AND”和“OR”的组合。

查询条件中,运算符号可以为“=”(等于),“>”(大于),“<”(小于),“LIKE”(类似于),“<>”(不等于)等,请按照实际情况进行书写。比如,我们要查询出所有姓名不等于空的教师的信息,可以使用如下SQL语句:

SELECT * FROM teacher WHERE t_name <> \'\';

 

  • (10)删除数据

我们使用SQL语句的“DELETE FROM…WHERE”进行数据删除操作。例如,删除ID为1且姓名为张的老师

DELETE FROM teacher WHERE t_name LIKE \'张%\' AND ID = 1;

 

 

  • (11)修改数据

我们使用SQL语句“UPDATE [tablename] SET … WHERE …”来进行修改数据操作,例如,将ID为2的老师姓名改为“王志超”可以写作

UPDATE teacher SET t_name=\'王志超\' WHERE ID=2;

 

 

 

  • (12)导出数据

我们使用“SELECT … FROM [tablename] INTO OUTFILE …”进行数据导出,例如,使用如下语句进行数据导出操作:

SELECT * FROM teacher INTO OUTFILE "E:/mysql-8.0.23/teacher.txt";

 

执行结果如下

 

得到的文件teacher.txt内容如下:

 

 

  • (13)数据库备份与恢复

使用mysqldump对MySQL进行备份。(注意:mysqldump是一个可执行程序,请直接在命令行下运行,不要进入mysql控制台后再运行)

例如,使用下面的命令对指定的数据库进行备份。

mysqldump -uroot -p123456 db_edu_sys > "E:/mysql-8.0.23/db_edu_sys.sql"

 

将“db_edu_sys”数据库备份至“E:/mysql-8.0.23/db_edu_sys.sql”文件中,执行后如下图所示

 

这里会出现一个警告:在命令行上使用密码是不安全的。但是,命令已经执行完毕了,我们到对应的文件夹下可以看到备份出来的文件。部分内容如下:

 

即表明已经备份成功。

恢复数据库时,导入备份文件(参看本节第(6)点)即可,我们进入mysql命令行状态,输入下列语句即可恢复数据:

 USE db_edu_sys

SET SQL_LOG_BIN=0;

source "E:/mysql-8.0.23/db_edu_sys.sql";

 

  • (14)事务

我们来讨论下数据库中一个比较重要的概念——事务,这在数据操作时会经常遇到。

我们将上述5个数据表内容进行了填充,将教师表、学生表、课程表、教学表、选课表内容进一步充实,每个表中都存在了数据。

考虑这样一种情况,有一门课程(例如:课程ID:1,课程名称:高等数学),已被教师(教师ID:2,教师姓名:王志超)领取了教学任务,同时,该课程被学生(学生ID:1,学生姓名:李晓军)选课。由于一些原因,我们准备删除这门课程,那么,我们怎么删除才能保证数据的一致性呢?

如果要删除这门课程,我们首先要删除教师教学任务,还要删除学生的选课情况,最后才能删除这门课程,同时,我们还要保证上述过程必须同时完成,不能只删除了课程,而教学任务和选课情况没有删除,这样就会在教学表和选课表中留下不一致的数据(无法与课程表关联)。

我们将必须一起完成的操作成为一个原子操作,我们使用事务来完成这个操作。即,当整个过程均无误的完成时,我们对事务过程整体进行提交,确保事务操作全部完成;而当事务操作中部分操作出现错误时,我们可以通过“回滚”确保事务全部都没有提交,返回没有执行该事务时的状态,从而确保数据的一致性。

例如,上述操作我们可以通过以下事务代码进行

delimiter $

CREATE PROCEDURE deleteCourse(IN cid int)  -- 声明删除课程的存储过程(函数)参数为课程ID

BEGIN

  DECLARE i_error integer; -- 定义一个变量,用于接收出错信息

  DECLARE CONTINUE handler FOR SQLEXCEPTION SET i_error = 1; -- 一旦出错则将变量设置为1

  START TRANSACTION ;   -- 发起事务

  DELETE FROM teaching WHERE c_ID=cid;   -- 删除教学表中有关该课程数据

  DELETE FROM selection WHERE c_ID=cid;  -- 删除选课表中有关该课程数据

  DELETE FROM course WHERE id=cid;       -- 删除课程表中的该课程

  IF i_error = 1 THEN    -- 判断是否出错

     ROLLBACK;          -- 回滚事务

  ELSE

     COMMIT;             -- 提交事务

  END IF;

 
END $

 

定义了上述存储过程后,在命令行下调用

Call deleteCourse(1);

 

即可执行对应的事务,按照上述分析结论,同时删除3个表对应的课程表编号为1的全部数据。

  • (15)复杂SQL查询

本节的最后,我们介绍复杂一些的SQL查询——多表联合查询。

在实际运用查询时,单独对一个表的查询往往不能满足查询的需求,这时就需要对多表进行联合查询。例如,我们想查询“数值分析”这门课的教师姓名是什么,这就涉及到从“课程表”、“教学表”和“教师表”3个表中联合查询出结果,我们使用“AND”关键字进行查询。

SELECT a.t_name,c.c_name FROM teacher a,teaching b,course c WHERE a.ID = b.t_ID AND b.c_ID = c.ID AND c.c_name=\'数值分析\';

 

再比如,我们想要查询哪些课程都由哪个教师教学,以便学生进行选课,我们可以使用左连接的方式进行查询。

(“左连接”即表示,只有左侧表中有数据,无论右侧表中是否有数据,都可查询出结果,右侧无数据的表使用“NULL”表示。本例中,由于不是所有的课程都由老师领取了教学任务,因此,将“课程表”放在左侧连接教学任务表,没有教师领取任务的课程,教师姓名一栏为“NULL”),SQL查询语句如下:

SELECT a.ID AS 课程编号,a.c_name AS 课程,b.t_name AS 教师 FROM 
course a LEFT JOIN 
(SELECT c.t_name,d.c_ID
 FROM teacher c,teaching d WHERE c.ID=d.t_ID) b 
ON a.ID = b.c_ID;

 

查询结果为:

 

 

本节小结

这一节,我们简单讲述了MySQL数据库的操作和一些基本的SQL语句,因为在后面的章节中,本节的内容会大量使用,请读者熟练掌握。有关SQL语法和相关操作,尤其是多表联合查询、存储过程和事务处理等,内容相对较难,有兴趣的读者可以参考有关数据库的书籍,这里仅为入门级的描述。本节中的数据库和表的设计还很简单,只为了说明问题并便于理解,由于本书后续环节还将使用这里的数据库和表,到时我们会对它们进行优化处理。有关本节所涉及的有关代码、表结构,成书过程中我们会逐渐整理并挂在书后附件对应的网站上,请读者自行下载学习。