命令行操作表数据
插入表数据
一旦创建了数据库和表,下一步就是向表里插入数据。通过INSERT或REPLACE语句可以向表中插入一行或多行数据。
语法格式:
INSERT [LOW_PRIORITY | DELAYED |HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
| SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
说明:
● tb1_name:被操作的表名。
● col_name:需要插入数据的列名。如果要给全部列插入数据,列名可以省略。如果只给表的部分列插入数据,需要指定这些列。对于没有指出的列,它们的值根据列默认值或有关属性来确定,MySQL处理的原则是:
(1)具有IDENTITY属性的列,系统生成序号值来唯一标志列。
(2)具有默认值的列,其值为默认值。
(3)没有默认值的列,若允许为空值,则其值为空值;若不允许为空值,则出错。
(4)类型为timestamp的列,系统自动赋值。
● VALUES子句:包含各列需要插入的数据清单,数据的顺序要与列的顺序相对应。若tb1_name后不给出列名,则在VALUES子句中要给出每一列(除IDENTITY和timestamp类型的列)的值,如果列值为空,则值必须置为NULL,否则会出错。VALUES子句中的值:
(1)expr:可以是一个常量、变量或一个表达式,也可以是空值NULL,其值的数据类型要与列的数据类型一致。例如,列的数据类型为int,插入的数据是‘aaa’就会出错。当数据为字符型时要用单引号括起。
(2)DEFAULT:指定为该列的默认值。前提是该列原先已经指定了默认值。
如果列清单和VALUES清单都为空,则INSERT会创建一行,每个列都设置成默认值。
INSERT语句支持下列修饰符:
● LOW_PRIORITY:可以使用在INSERT、DELETE和UPDATE等操作中,当原有客户端正在读取数据时,延迟操作的执行,直到没有其他客户端从表中读取为止。
● DELAYED:若使用此关键字,则服务器会把待插入的行放到一个缓冲器中,而发送INSERT DELAYED语句的客户端会继续运行。如果表正在被使用,则服务器会保留这些行。当表空闲时,服务器开始插入行,并定期检查是否有新的读取请求(仅适用于MyISAM、MEMORY和ARCHIVE表)。
● HIGH_PRIORITY:可以使用在SELECT和INSERT操作中,使操作优先执行。
● IGNORE:使用此关键字,在执行语句时出现的错误就会被当做警告处理。
● ON DUPLICATE KEY UPDATE…:使用此选项插入行后,若导致UNIQUE KEY或PRIMARY KEY出现重复值,则根据UPDATE后的语句修改旧行(使用此选项时DELAYED被忽略)。
● SET子句:SET子句用于给列指定值,使用SET子句时表名的后面省略列名。要插入数据的列名在SET子句中指定,col_name为指定列名,等号后面为指定数据,未指定的列,列值指定为默认值。
从INSERT的语法格式可以看到,使用INSERT语句可以向表中插入一行数据,也可以插入多行数据,插入的行可以给出每列的值,也可只给出部分列的值,还可以向表中插入其他表的数据。
使用INSERTINTO…SELECT…,可以快速地从一个或多个表中向一个表插入多个行。语法格式如下:
INSERT [LOW_PRIORITY | HIGH_PRIORITY][IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ON DUPLICATE KEY UPDATE col_name=expr, ... ]
若例3.1中的数据行已经插入,其中学号为主键(PRIMARY KEY),现在想再插入下列一行数据:
081101,刘华,通信工程,1,1991-03-08,48,NULL,NULL
若使用INSERT语句,执行结果如下:
使用REPLACE语句:
另外,MySQL还支持图片的存储,图片一般可以以路径的形式来存储,即插入图片可以采用直接插入图片的存储路径。当然也可以直接插入图片本身,只要用LOAD_FILE函数即可。
向XSCJ数据库的表XS(表中列包括学号、姓名、专业、性别、出生日期、总学分、照片、备注)中插入如下的一行:
081101,王林,计算机,1,1990-02-10,50 ,NULL,NULL
使用下列语句:
USE XSCJ
INSERT INTO XS
VALUES('081101', '王林' , '计算机', 1, '1990-02-10',50, NULL,NULL);
若表XS中专业的默认值为“计算机”,照片、备注默认值为NULL,插入上例那行数据可以使用以下命令:
INSERT INTO XS (学号, 姓名, 性别, 出生日期, 总学分)
VALUES('081101', '王林', 1, '1990-02-10', 50);
与下列命令效果相同:
INSERT INTO XS
VALUES('081101', '王林', DEFAULT, 1, '1990-02-10', 50,NULL,NULL);
当然,也可以使用SET子句来实现:
INSERT INTO XS
SET 学号='081101', 姓名='王林', 专业=DEFAULT, 性别=1,出生日期='1990-02-10', 总学分=50;
向XS表中插入一行数据:
081102,程明,计算机,1,1991-02-01,50,picture.jpg,NULL
其中,照片路径为D:\IMAGE\ picture.jpg。
使用如下语句:
INSERT INTO XS
VALUES('081102', '程明', '计算机', 1, '1991-02-01',50, 'D:\IMAGE\picture.jpg', NULL);
下列语句是直接存储图片本身:
INSERT INTO XS
VALUES('081102', '程明', '计算机', 1, '1991-02-01',50, LOAD_FILE('D:\IMAGE\picture.jpg'),
NULL);
1. 使用DELETE语句删除数据
从单个表中删除,语法格式:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROMtbl_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
说明:
● QUICK修饰符:可以加快部分种类的删除操作的速度。
● FROM子句:用于说明从何处删除数据,tbl_name为要删除数据的表名。
● WHERE子句:where_definition中的内容为指定的删除条件。如果省略WHERE子句则删除该表的所有行,WHERE子句的详细定义在4.2.3节介绍。
● ORDER BY子句:各行按照子句中指定的顺序进行删除,此子句只在与LIMIT联用时才起作用。ORDERBY子句和LIMIT子句的具体定义将在SELECT语句中介绍。
● LIMIT子句:用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。
假设数据库mydata中有一个表table1,table1中有如下数据:
姓名年龄 职业
张三 42教师
李四 28工人
要删除张三的信息可使用如下语句:
USE mydata
DELETE FROM table1
WHERE 姓名='张三';
将XSCJ数据库的XS表(具体数据参照附录A)中总学分小于50的所有行删除,使用如下语句:
USE XSCJ
DELETE FROM XS
WHERE 总学分<50;
说明:本书所举例子中的XSCJ数据库里的XS、KC、XS_KC表的数据均以附录A中所列的数据样本为准。如本例无特殊说明,本例所做的修改不在其他例子中体现。所以如果例中语句涉及删除或修改原始数据时,读者需要小心使用,删除之后要及时将数据恢复。在第8章中将会讨论如何备份和恢复数据。
从多个表中删除行,语法格式:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]
或:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHEREwhere_definition]
说明:对于第一种语法,只删除列于FROM子句之前的表中对应的行。对于第二种语法,只删除列于FROM子句之中(在USING子句之前)的表中对应的行。作用是,可以同时删除多个表中的行,并使用其他的表进行搜索。
2. 使用TRUNCATE TABLE语句删除表数据
使用TRUNCATETABLE语句将删除指定表中的所有数据,因此也称其为清除表数据语句。
语法格式:
TRUNCATE TABLE table- name
说明:由于TRUNCATETABLE语句将删除表中的所有数据,且无法恢复,因此使用时必须十分小心。
TRUNCATE TABLE 在功能上与不带 WHERE子句的DELETE语句(如DELETE FROM XS)相同,二者均删除表中的全部行。但 TRUNCATE TABLE比DELETE速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。而TRUNCATETABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。使用 TRUNCATE TABLE,AUTO_INCREMENT计数器被重新设置为该列的初始值。
对于参与了索引和视图的表,不能使用 TRUNCATE TABLE删除数据,而应使用DELETE语句。
假设有3个表t1、t2、t3,它们都含有id列。要删除t1中id值等于t2的id值的所有行和t2中id值等于t3的id值的所有行,使用如下语句:
DELETEt1, t2
FROM t1, t2, t3
WHERE t1.id=t2.id ANDt2.id=t3.id;
或:
DELETEFROM t1, t2
USING t1, t2, t3
WHERE t1.id=t2.id ANDt2.id=t3.id;
要修改表中的一行数据,可以使用UPDATE语句,UPDATE可以用来修改一个表,也可以修改多个表。
修改单个表,语法格式:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
说明:
● SET子句:根据WHERE子句中指定的条件对符合条件的数据行进行修改。若语句中不设定WHERE子句,则更新所有行。col_name1、col_name2…为要修改列值的列名,expr1、expr2…可以是常量、变量或表达式。可以同时修改所在数据行的多个列值,中间用逗号隔开。
将XSCJ数据库的XS表(以附录A为准)中的所有学生的总学分都增加10。将姓名为“罗林琳”的同学的备注改为“转专业学习”,学号改为“081251”。
UPDATE XS
SET 总学分 = 总学分+10;
UPDATE XS
SET学号 ='081251' ,备注 ='转专业学习'
WHERE 姓名 = '罗林琳';
SELECT 学号, 姓名, 总学分, 备注
FROM XS;
查询结果为:
这样,可以发现表中所有学生的总学分已经都增加了10,姓名为“罗林琳”的同学的备注改为“转专业学习”,学号改为了“081251”。
修改多个表,语法格式:
UPDATE [LOW_PRIORITY] [IGNORE]table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
说明:table_references中包含了多个表的联合,各表之间用逗号隔开。
表tb1和表tb2中都有两个字段id INT(4),pwd CHAR(4),其中id为主键。当表tb1中id值与tb2中id值相同时,将表tb1中对应的pwd值修改为“AAA”,将表tb2中对应的pwd值改为“BBB”。
UPDATEtb1 , tb2 SET tb1.pwd='AAA' , tb2.pwd='BBB'
WHERE tb1.id=tb2.id;
SHOW和DESCRIBE语句
1.SHOW语句
SHOW TABLES或SHOW TABLES FROM DATABASE_NAME:显示当前数据库中所有表的名称。
SHOW DATABASES:显示MySQL中所有数据库的名称。
SHOW COLUMNS FROM table_name FROMdatabase_name或SHOW COLUMNSFROM database_name.table_name:显示表中列的名称。
SHOW GRANTS FOR user_name:显示一个用户的权限,显示结果类似于GRANT命令。
SHOW INDEX FROM table_name:显示表的索引。
SHOW STATUS:显示一些系统特定资源的信息,例如,正在运行的线程数量。
SHOW VARIABLES:显示系统变量的名称和值。
SHOW PROCESSLIST:显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有PROCESS权限,就可以查看所有人的进程,包括密码。
SHOW TABLE STATUS:显示当前使用或者指定的DATABASE中的每个表的信息。信息包括表类型和表的最新更新时间。
SHOW PRIVILEGES:显示服务器所支持的不同权限。
SHOW CREATE DATABASE database_name:显示创建某一个数据库的CREATE DATABASE语句。
SHOW CREATE TABLE table_name:显示创建一个表的CREATE TABLE语句。
SHOW EVENTS:显示所有事件的列表。
SHOW INNODB STATUS:显示InnoDB存储引擎的状态。
SHOW LOGS:显示BDB存储引擎的日志。
SHOW WARNINGS:显示最后一个执行的语句所产生的错误、警告和通知。
SHOW ERRORS:只显示最后一个执行语句所产生的错误。
SHOW [STORAGE] ENGINES:显示安装后的可用存储引擎和默认引擎。
SHOW PROCEDURE STATUS:显示数据库中所有存储过程基本信息,包括所属数据库、存储过程名称、创建时间等。
SHOW CREATE PROCEDURE sp_name:显示某一个存储过程的详细信息。
2.DESCRIBE语句
DESCRIBE语句用于显示表中各列的信息,结果等于SHOW columns from语句。
语法格式:
{DESCRIBE | DESC} tb1_name [col_name | wild]
说明:
DESC是DESCRIBE的简写,二者用法相同。
col_name可以是一个列名称,或一个包含‘%’和‘_’的通配符的字符串,用于获得对于带有与字符串相匹配的名称的各列的输出。没有必要在引号中包含字符串,除非其中包含空格或其他特殊字符。
用DESCRIBE语句查看XS表的列的信息。
USE XSCJ
DESCRIBE XS;
结果为:
查看XS表学号列的信息。
USE XSCJ
DESC XS 学号;
结果为: