目录
1 创建数据表
1.1 使用create table语言创建数据表
CREATE [TEMPORARY] TABLE [IF NOT EXIST] <表名>
[([<字段定义>],…|[<索引定义>])]
[table_option][select_statement]
TEMPORARY:若使用该关键字,则创建的是临时表。
IF NOT EXIST:用于判断数据库中是否已经存在同名的表。
例:使用SQL命令在teaching数据库中建立学生表s
CREATE TABLE `s` (
`sno` char(10) NOT NULL COMMENT '学号',
`sn` varchar(45) NOT NULL COMMENT '姓名',
`sex` enum('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
`age` int NOT NULL COMMENT '年龄',
`maj` varchar(45) NOT NULL COMMENT '专业',
`dept` varchar(45) NOT NULL COMMENT '院系',
PRIMARY KEY (`sno`)
)
2 定义表的约束
数据的完整性是指保护数据库中数据的正确性、有效性和相容性,防止错误的数据进入数据库造成无效操作
约束主要包括NULL/NOT NULL约束、UNIQUE约束、PRIMARY KEY约束、FOREIGN KEY约束和CHECK约束
2.1 NULL/NOT NULL约束
<字段名> <数据类型> [NULL|NOT NULL]
例:中学号sno设置为NOT NULL约束
'sno' CHAR(10) NOT NULL COMMENT '学号',
2.2 UNIQUE约束
UNIQUE约束指所有记录中字段的值不能重复出现,用于保证数据表在某一字段或多个字段的组合上取值必须唯一
1.一个表中可以允许有多个UNIQUE约束,UNIQUE约束可以定义在多个字段上。
2.使用UNIQUE约束的字段允许为NULL值。
3.UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引,默认为非聚集索引。
2.21 列约束
<字段名> <数据类型> UNIQUE
例:姓名sn设置为UNIQUE约束
'sn' VARCHAR(45) UNIQUE COMMENT '姓名',
2.22 表约束
UNIQUE(<字段名>[{,<字段名>}])
例:姓名sn设置为UNIQUE约束
'sn' VARCHAR(45) COMMENT '姓名',
UNIQUE ('sn')
2.3 PRIMARY KEY约束
PRIMARY KEY约束用于定义基本表的主码,起唯一标识作用,保证数据表中记录的唯一性。一张表只能有一个PRIMARY KEY约束,可以作用于一个字段,也可以作用于多个字段的组合。
1.在一个基本表中只能定义一个PRIMARY KEY约束,但可定义多个UNIQUE约束。
2.对于指定为PRIMARY KEY的一个字段或多个字段的组合,其中任何一个字段都不能出现NULL值,而对于UNIQUE所约束的唯一码,则允许为NULL,但是只能有一个空值。
3.不能为同一个字段或一组字段,既定义UNIQUE约束,又定义PRIMARY KEY约束。
2.31 列约束
<字段名> <数据类型> PRIMARY KEY
例:定义学号sno为表的主码
'sno' CHAR(10) NOT NULL PRIMARY KEY COMMENT '学号',
2.32 表约束
[CONSTRAINT <约束名>] PRIMARY KEY (<字段名>[{,<字段名>}])
例:定义学号sno和课程号cno为表的主码
'sno' CHAR(10) NOT NULL COMMENT '学号',
'cno' CHAR(10) NOT NULL COMMENT '课程号',
PRIMARY KEY ('sno', 'cno')
2.4 FOREIGN KEY约束
FOREIGN KEY约束用于在两个数据表A和B之间建立连接。通过FOREIGN KEY约束可以保证两表间的参照完整性。
[CONSTRAINT <约束名>] FOREIGN KEY (<从表A中字段名>[{,<从表A中字段名>}])
REFERENCES <主表B表名> (<主表B中字段名>[{,<主表B中字段名>})
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
RESTRICT:拒绝对主表B的删除或更新操作。若有一个相关的外码值在主表B中,则不允许删除或更新B表中主要码值。
CASCADE:在主表B中删除或更新时,会自动删除或更新从表A中对应的记录。
SET NULL:在主表B中删除或更新时,将子表中对应的外码值设置为NULL。
NO ACTION:NO ACTION和RESTRICT相同,InnoDB拒绝对主表B的删除或更新操作。
例:定义学号sno和课程号cno为表的外码
CREATE TABLE 'sc' (
'sno' CHAR(10) NOT NULL COMMENT '学号',
'cno' CHAR(10) NOT NULL COMMENT '课程号',
'score' DECIMAL(5,2) COMMENT '成绩',
FOREIGN KEY ('cno') REFERENCES 'c' ('cno'),
FOREIGN KEY ('sno') REFERENCES 's' ('sno')
)
主表B必须是数据库中已经存在的数据表,或者是当前正在创建的数据表
必须为主表B定义主码,且主码不能包含空值,但允许在外码中出现空值
从表A的外码中字段的数目和数据类型必须和主表B的主码中字段的数目和对应字段的数据类型相同
2.5 CHECK约束
CHECK约束用来检查数据表中字段值所允许的范围
CHECK (<条件>)
MySQL可以使用简单的表达式来实现CHECK约束,也可以使用复杂的表达式作为限定条件。若将CHECK约束子句置于所有字段的定义以及主码约束和外码定义之后,则这种约束也称为CHECK的表约束。
例:定义成绩score的取值范围为0~100之间。
CREATE TABLE 'sc' (
'sno' CHAR(10) NOT NULL,
'cno' CHAR(10) NOT NULL,
'score' DECIMAL(5,2) CHECK(score>=0 AND score <=100),
PRIMARY KEY ('sno', 'cno')
)
3 修改数据表
MySQL使用SQL语句中的ALTER TABLE语句来修改表名、修改字段数据类型、修改字段名、添加和删除字段、更改表的存储引擎等。
3.1 ADD用于增加新字段和完整性约束
ALTER TABLE <表名>
ADD [<新字段名>、<数据类型>] [<完整性约束定义>] [FIRST|AFTER已有字段名]
例:在学生表s中增加一个字段班号class_no
ALTER TABLE s
ADD class_no VARCHAR(6);
例:在学生表s中增加字段班号和住址。
ALTER TABLE s
ADD (class_no VARCHAR(6), address NVARCHAR(20));
添加多个字段时不能指定位置关系,只能添加在数据表的末尾。
添加多个字段时必须用小括号括起来。
在增加NOT NULL约束时,语法结构不同于其他完整性约束。
ALTER TABLE <数据表名>
CHANGE [COLUMN] <字段名>
<字段名> <数据类型> NOT NULL;
3.2 RENAME 方式用于修改表名
ALTER TABLE <旧表名>
RENAME [TO] <新表名>;
例:把学生表s的名称改为student
ALTER TABLE s
RENAME student;
修改表名并不修改数据表结构,因此,修改表名后的数据表结构与修改表名之前一样。
3.3 CHANGE方式用于修改字段名
ALTER TABLE <表名>
CHANGE <旧字段名> <新字段名> <新数据类型>;
例:把学生表s中字段名称sn改为sname
ALTER TABLE s
CHANGE sn sname VARCHAR(45);
即使不需要修改字段的数据类型,也不能省略<新数据类型>,只需把数据类型设置为与原字段一致即可
3.4 MODIFY方式可用于修改字段数据类型和字段排序
ALTER TABLE <表名>
MODIFY <字段名1> <数据类型> [FIRST|AFTER 字段名2];
例:把学生表s中姓名sn的数据类型由VARCHAR(45)改为CHAR(30)
ALTER TABLE s
MODIFY sn CHAR(30);
在修改字段数据类型时,“数据类型”指修改后字段的新数据类型。
在修改字段排序时,若使用FIRST,则将“字段名1”修改为表的第一个字段;若使用AFTER,则将“字段名1”插入“字段名2”后面。
在修改字段排序时,“数据类型”不可省略。
3.5 ENGINE用于修改表的存储引擎
ALTER TABLE <表名>
ENGINE= <修改后存储引擎名>;
例:把学生表s的存储引擎改为MyISAM
ALTER TABLE s
ENGINE=MyISAM
若被修改表有外码,则存储引擎不能由InnoDB修改为MyISAM,因为MyISAM不支持外码。
3.6 DROP方式可用于删除字段和完整性约束。
删除字段
ALTER TABLE <旧表名>
DROP <字段名>
例:删除学生表s中新添加的字段class_no和address
ALTER TABLE s
DROP class_no, DROP address;
删除完整性约束
ALTER TABLE <表名>
DROP CONSTRAINT <约束名>;
例:删除学生表s中的CHECK约束s_chk
ALTER TABLE s
DROP CONSTRAINT s_chk;
4 删除数据表
使用SQL语句DROP TABLE删除一个或多个表
DROP TABLE [IF EXISTS] <表名>;
例:删除学生表s
DROP TABLE IF EXISTS s;
5 查看数据表
SHOW TABLES;
DESCRIBE(DESC)和SHOW CREATE TABLE语句查看数据表结构
通过DESCRIBE(DESC)语句可以查看表的字段信息,通过SHOW CREATE TABLE语句可以查看创建表时的详细语句。
DESCRIBE/DESC <表名>;
SHOW CREATE TABLE <表名>;
例:分别使用DESCRIBE(DESC)和SHOW CREATE TABLE语句查看学生表s的结构。
DESCRIBE s;
SHOW CREATE TABLE s;
6 添加数据
INSERT|REPLACE INTO <表名> [(<字段名1>[,<字段名2>…])]
VALUES(<值>);
例:在学生表s中添加一条学生记录
INSERT INTO s(sno, sn, age, sex,maj, dept)
VALUES('s9', '郑冬', 21, '女', '计算机','信息学院');
添加多行记录
INSERT|REPLACE INTO <表名>[(<字段名1>[,<字段名2>…])]
VALUES(<值列表1>[,<值列表2>…])
例:选课关系表sc中添加3条选课记录('s8, 'c1')、('s8, 'c2')、('s8, 'c5')
INSERT INTO sc(sno, cno)
VALUES('s8', 'c1'),
('s8', 'c2'),
('s8', 'c5');
必须用逗号将各个数据分开,字符型数据要用单引号括起来
如果INTO子句中没有指定字段名,则新添加的记录必须在每个字段上均有值,且排序一致
使用REPLACE语句添加记录时,如果要添加的新记录的主码或UNIQUE约束的字段值已存在于表中,则需删除已有记录后再添加新纪录
INTO子句中没有出现的字段,新添加的记录在这些字段上将被赋NULL值。但在表定义时有NOT NULL约束的字段不能取NULL值,添加记录时必须给其赋值。
7 修改数据表中数据
UPDATE <表名>
SET <字段名>=<表达式>[,<字段名>=<表达式>]…
[WHERE <条件>]
修改一行记录
例:把刘杨老师转到工学院
UPDATE t
SET dept= '工学院'
WHERE tn= '刘杨';
修改多行记录
例:把所有学生的年龄增加1岁
UPDATE s
SET age=age+1;
8 删除数据表中数据
DELETE
FROM <表名>
[WHERE <条件>]
删除一行记录
例:删除成绩为90.5分的记录
DELETE
FROM sc
WHERE score = 90.5;
删除多行记录
例:删除所有教师的授课记录
DELETE
FROM tc;