数据表是数据库中最重要的组成部分,是其他对象的基础。数据表有行、列之分,行称为记录,列称为字段。
一、创建数据表
CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type,
...
)
例如,用户id唯一,禁止重复,使用自动编号AUTO_INCREMENT,默认情况下起始值为1,每次的增量为1,自动编号的字段必须定义为主键PRIMARY KEY,否则系统报错;用户名禁止为空,设置为NOT NULL,禁止重复,设置为唯一约束UNIQUE KEY;性别使用默认约束,默认值为3;年龄无负值,所以使用无符号值。
CREATE TABLE firsttable(
id SAMLLINT UNSIGNED AUTO_INCREAMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3',
age TINYINT UNSIGNED,
score Float(5,2)
);
二、查看数据表
添加FROM可以查看其他数据库中的数据表,省略则查看当前数据库中的数据表。使用FROM不会更改当前打开的数据库。
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];
例如,查看mysql数据库中的所有数据表
SHOW TABLES FROM mysql;
三、查看数据表结构
SHOW COLUMNS FROM table_name;
四、数据表插入记录
1、方法一
如果省略掉列名称,就需要对所有的字段赋值,否则系统会提示错误。设置为主键的字段,赋值不允许重复,否则系统报错。设置为自增的字段,可以设置为NULL或DEFAULT
INSERT [INTO] table_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...;
例如,id自增,可以设置为NULL;年龄可以设置为表达式;性别设置了默认值,可以设置为DEFAULT
INSERT firsttable VALUES(NULL,'NIE',DEFAULT,18+1,99.50);
若只为部分字段赋值,则需要写上列名,设置为NOT NULL的字段必须赋值,否则系统会提示错误。还可以一次性增加多条记录。
INSERT firsttable (username,age) VALUES('KEITH',20),('JOY',22);
2、方法二
一次只能插入一条记录,使用较少,与方法一的区别在于,此方法可以用于子查询
INSERT [INTO] table_name SET col_name={expr|DEFAULT},...;
例如
INSERT firsttable SET username='YANG',age=16;
3、方法三
此方法可以将查询结果插入到指定数据表
INSERT [INTO] table_name [(col_name,...)] SELECT ...;
五、记录查找
SELECT expr,... FROM table_name;
例如,查找firsttable表中的所有记录。*表示字段的过滤,而不是记录的过滤。
SELECT * FROM firsttable;
六、显示索引
SHOW INDEXES FROM firsttable;
七、创建具有外键约束的数据表
父表和子表必须使用相同的存储引擎InnoDB;外键列和参照列必须具有相同的数据类型和符号位,且必须创建索引。
例如,以firsttable为父表,创建secondtable子表,参照列为firsttable中的id,外键列为secondtable中的uid。
CREATE TABLE secondtable(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uid SMALLINT UNSIGNED,
FOREIGN KEY (uid) REFERENCES provinces (id)
);
八、给数据表添加列
添加一列
ALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];
例如,
ALTER TABLE firsttable ADD province VARCHAR(20) AFTER age;
添加多列,不能设置位置关系,只能添加到所有列的最后面。
ALTER TABLE table_name ADD [COLUMN] (col_name column_definition,...);
九、删除数据表多余的列
ALTER TABLE table_name DROP [COLUMN] col_name;
例如,删除一列
ALTER TABLE firsttable DROP province;
删除多列
ALTER TABLE firsttable DROP score,DROP sex;
十、修改列定义
ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name];
例1,修改列的位置,将username移到第一列
ALTER TABLE firsttable MODIFY username VARCHAR(20) NOT NULL UNIQUE KEY FIRST;
例2,修改列的数据类型,将id设置为TINYINT
ALTER TABLE firsttable MODIFY ID TINYINT UNSIGNED AUTO_INCREAMENT PRIMARY KEY;
注意:当由大类型改为小类型时,有可能造成数据的丢失。
十一、修改列名称(尽量少使用)
ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
例如,将score改为grade
ALTER TABLE firsttable CHANGE score grade TINYINT;
十二、修改数据表名称(尽量少使用)
ALTER TABLE old_tb_name RENAME [TO|AS] new_tb_name;
/*或*/
RENAME TABLE old_tb_name TO new_tb_name [,old_tb_name2 TO new_tb_name2,...];
十三、单表更新记录
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition];
例如,把表中小于18岁的年龄全部增加1岁,若省略条件将更新所有的记录。
UPDATE firsttable SET age=age+1 WHERE age<18;
十四、单表删除记录
DELETE FROM table_name [WHERE where_condition];
例如
DELETE FROM firsttable WHERE id=1;