MySQL >> 学习笔记-1 : 数据库操作[ 1 ]

本篇记录，数据库\表操作的增加、删除、修改。查询操作单独作为一篇来写。

操作数据库

显示|创建|删除 数据库

显示数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE db_name;

删除数据库

DROP DATABASE db_name;

数据库存储引擎

显示MySQL支持的存储引擎

SHOW ENGINES \G

查看当前库的存储引擎

SHOW VARIABLES LIKE '%engine%';

Innodb与MyISAM对比

• Innodb
  • 默认数据库存储引擎
  • 支持事务、回滚、自增序列、外键
  • 表结果存放在.frm文件中
  • 数据和索引存放在表空间中
  • 读写效率稍差，占用空间大
• MyISAM
  • 表结构存在.frm文件中
  • 数据存储在.MYD文件中
  • 索引存储在.MYI文件中
  • 数据操作速度快，占用空间小，不支持事务和并发
  • MySQL系统库使用该引擎
    
    

操作数据表

创建表

创建表的语法形式

CREATE TABLE 表名(
属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
......
);

完整性约束条件

名称	语法	解释
主键	PRIMARY KEY	标识该属性是本表的主键，可以唯一的标识对应的元组
外键	FOREIGN KEY	标识该属性为本表的外键，是与之联系的某表的主键
不为空	NOT NULL	标识该属性不能为空
唯一值	UNIQUE	标识该属性的值是唯一的
自增加	AUTO_INCREMENT	标识该属性的值是自动增加的
默认值	DEFAULT	为该属性设置默认值

示例

CREATE TABLE example(
id INT PRIMARY KEY AUTO_INCREMENT,
course_id INT UNIQUE,
name VARCHAR(20) NOT NULL,
sex BOOLEAN NOT NULL,
addr VARCHAR(50) DEFAULT 'BEIJING',
CONSTRAINT example_fk FOREIGN KEY(course_id)
REFERENCES example2(course_id)
);

主键

作用
帮助MySQL以最快的速度找到表中的某一条信息

语法

属性名 数据类型 PRIMARY KEY

满足条件

• 主键必须是唯一的
• 表中任意两条记录的主键字段的值不能相同
• 主键的值是非空值

字段
单字段

CREATE TABLE example(id INT PRIMARY KEY);

多字段

CREATE TABLE example(
id INT,
course_id INT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(id, course_id)
);

外键

作用
建立该表与其父表的关联关系，父表中删除某条信息时，子表中与之对应的信息也必须有相应的改变

语法

CONSTRAINT 外键别名 FOREIGN KEY(属性名1,属性名2...) 
REFERENCES 父表名(父表主键1, 父表主键2...) 

满足条件

• 必须依赖于数据库中已存在的父表的主键
• 本表的外键必须与父表的主键数据类型一致(数据宽度，是否正数，是否设置unsigned)
• 本表的外键必须与父表的主键存储引擎一致

示例

CREATE TABLE example(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT,
crouse_id INT,
CONSTRAINT c_fk FOREIGN KEY(stu_id,crouse_id)
REFERENCES example2(stu_id,crouse_id)
);

非空约束

语法

属性名 数据类型 NOT NULL

作用

• 指定字段的值不能为空值(NULL)
• 如果新插入的记录该值为空，则数据库系统报错

唯一约束

语法

属性名 数据类型 UNIQUE

作用
唯一性约束将保证所有记录中该字段的值不能重复出现

自动增加

语法

属性名 数据类型 AUTO_INCREMENT

作用
用于为表中插入的新记录自动生成唯一的ID

注意

• 一个表中只能有一个AUTO_INCREMENT约束，且该字段必须是主键的一部分
• AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLINT、INT、BIGINT等)
• 默认情况下，该字段时从1开始自增的

示例

CREATE TABLE example(
id INT PRIMARY KEY AUTO_INCREMENT
);

默认值

语法

属性名 数据类型 DEFAULT 默认值

作用
在创建表时可以指定表中字段的默认值，当插入的新记录没有为该字段赋值时，数据库系统会自动为该字段插入默认值。

查看表结构

describe语句

语法

DESCRIBE 表名  	等价于	DESC 表名

作用
查看表的基本定义：字段名、字段数据类型、是否为主键或默认值等。

show语句

语法

SHOW CREATE TABLE 表名

作用
查看表的详细定义：字段名、完整性约束条件、默认引擎、字符编码等

修改表

修改表名

语法

ALTER TABLE 旧表名 RENAME 新表名;

示例

ALTER TABLE example RENAME user;

修改字段名

语法

ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型(不修改数据类型时，保持原样);

示例

ALTER TABLE user CHANGE name stu_name varchar(20);

修改字段数据类型

语法

ALTER TABLE 表名 MODIFY 属性名 新数据类型;

示例

ALTER TABLE user MODIFY name VARCHAR(30);

修改字段的排列位置

语法

ALTER TABLE 表名 MODIFY 属性名 数据类型 [FIRST|AFTER 属性名2];

示例

1) 字段修改到第一个位置
ALTER TABLE user MODIFY name VARCHAR(20) FIRST;

2) 字段修改到指定位置
ALTER TABLE user MODIFY sex BOOLEAN AFTER age;

增加字段

语法

ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束] [FIRST|AFTER 属性名2];

示例

1) 增加无完整性约束的字段
ALTER TABLE user ADD phone VARCHAR(20);

2) 增加有完整性约束的字段
ALTER TABLE user ADD age INT(4) NOT NULL;

3) 表的第一位置增加字段
ALTER TABLE user ADD num INT(8) PRIMARY KEY FIRST;

4)表的指定位置之后增加字段
ALTER TABLE user ADD address VARCHAR(50) NOT NULL AFTER age;

删除表

删除字段

语法

ALTER TABLE 表名 DROP 字段;

示例

ALTER TABLE user DROP id;

删除外键约束

语法

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

示例

ALTER TABLE example DROP FOREIGN KEY c_fk;

修改默认存储引擎

语法

ALTER TABLE 表名 ENGINE=新存储引擎名称;

示例

ALTER TABLE user ENGINE=MyISAM;

删除无其他表关联的表

语法

DROP TABLE 表名;

示例

DROP TABLE example;

删除有关联的表

语法

1) 删除关联表外键
ALTER TABLE 子表名 DROP FOREIGN KEY 外键别名;

2) 删除父表
DROP TABLE 父表名;

示例

1) ALTER TABLE example2 DROP FOREIGN KEY ex_fk;

2) DROP TABLE example;