MySQL基础用法之数据表的基本操作

      数据表是数据库中最重要的组成部分，是其他对象的基础。数据表有行、列之分，行称为记录，列称为字段。

一、创建数据表

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;