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

相关文章