1.查看当前所有存在的数据库
show databases;
2.创建新的数据库
create databases database_name;
其中 database_name 为自己所要创建数据库的名称 ,下面创建一个名为 test123 的数据库
3.查看数据库的定义
4.删除数据库
删除数据库是将已经存在的数据库从磁盘空间上清除,清除以后,数据库中的所有数据也将一同被删除。
drop database database_name;
5.查看支持的引擎类型
Support 列中的值表示是否支持该种引擎,YES 表示可以使用,NO 表示不能, DEFAULT 表示该引擎为当前默认存储引擎。
6.创建表
数据表属于数据库,所以在创建数据表之前,要先使用 use database_name; 来指定在哪个数据库中进行,如果没有选择数据库,那么将会抛出 “No database selected”的错误。
create teble <表名>
(
字段名1,数据类型 [列级别约束条件] [默认值],
字段名2,数据类型 [列级别约束条件] [默认值],
......
[表级别约束条件]
);
使用 create teble 创建表时,必须指定以下信息:
- 要创建的表的名称,不区分大小写,不能使用 SQL 语言中的关键字,如 DROP、ALTER、INSERT 等。
- 数据表中每一个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开。
字段名称 | 数据类型 | 备注 |
---|---|---|
id | INT(11) | 员工编号 |
name | VARCHAR(25) | 员工名称 |
deptId | INT(11) | 所在部门编号 |
salary | FLOAT | 工资 |
创建 tb_emp1 表,SQL 语句为:
create table tb_emp1
(
id int(11),
name varchar(25),
deptId int(11),
salary FLOAT
);
7.查看表是否创建成功
8.使用主键约束——单字段主键
- 在定义列的同时指定主键
字段名 数据类型 primary key [默认值]
create table tb_emp2
(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float
);
- 在 定义完所有列之后指定主键
[constraint <约束名>] primary key [字段名]
create table tb_emp3
(
id int(11),
name varchar(25),
deptId int(11),
salary float,
primary key (id)
);
以上两种执行后的结果一样,都在 id 字段上设置主键约束。
9.使用主键约束——多字段联合主键
primary key [字段1,字段2,字段3,...字段n]
将上表中的 id 和 deptId 作为联合主键,SQL 语句如下:
create table tb_emp4
(
id int(11),
name varchar(25),
deptId int(11),
salary float,
primary key(name,deptId)
);
10.使用外键约束
外键用来在两个表的数据之间建立连接,它可以使一列或者多列。一个表可以有一个或多个外键。一个表的外键和可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。
外键首先是一个表中的一个字段,它可以不是本表中的主键,但对应另外一个表的主键。外键主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行。外键的作用是保证数据的一致性、完整性。例如,部门表 tb_dept 的主键是 id,在员工表 tb_emp5 中有个键 deptId 与这个 id 关联。
主表(父表)就是相关联字段中主键所在的那个表就是主表,如部门表中的部门 id。从表就是相关联字段中外键所在的那个表就是从表,如在员工表中的部门 id。
[constraint <外键名>] foreign key 字段名1 [,字段名2,...] references <主表名> 主键列1 [,主键列2,...]
“外键名”为定义的外键约束的名称,一个表中不能有相同名称的外键;“字段名”表示子表需要添加外键约束的字段列;“主表名”即被字表外键所依赖的表的名称;“主键列”表示主表中定义的主键列,或者列组合。
字段名称 | 数据类型 | 备注 |
---|---|---|
id | INT(11) | 部门编号 |
name | VARCHAR(22) | 部门名称 |
location | VARCHAR(50) | 部门地址 |
create table tb_dept1
(
id int(11) primary key,
name varchar(22) not null,
location varchar(50)
);
定义数据表 tb_emp5,让它的键 deptId 作为外键关联到 tb_dept1 的主键 id,SQL 语句如下:
create table tb_emp5
(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
);
以上语句成功后,在表 tb_emp5 上添加了名称为 fk_emp_dept1 的外键约束,外键名称为 deptId,其依赖于表 tb_dept1 的主键 id。
11.使用非空约束
字段名 数据类型 not null
12.使用唯一性约束
唯一性约束(Unique Constraint)要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
- 在定义完列之后直接指定唯一约束
字段名 数据类型 unique
定义数据表 tb_dept2,指定部门的名称唯一,SQL 语句如下
create table tb_dept2
(
id int(11) primary key,
name varchar(22) unique,
location varchar(50)
);
- 在定义完所有列之后直指定唯一约束
[constraint <约束名>] unique(<字段名>)
定义数据表 tb_dept3,指定部门的名称唯一,SQL 语句如下
create table tb_dept3
(
id int(11) primary key,
name varchar(22),
location varchar(50),
constraint sth unique(name)
);
UNIQUE 和 PRIMARY KEY 的区别:一个表中可以有多个字段声明为 UNIQUE,但只能有一个 PRIMARY KEY 声明;声明为 PRIMARY KEY 的列不允许有空值,但是声明为 UNIQUE 的字段允许空值(NULL)的存在。
13.使用默认约束
默认约束(Default Constraint)指定某列的默认值。如男性同学较多,性别可以默认为“男”。如果插入一条新记录时没有对该字段赋值,那么系统会自动为这个字段赋值为“男”。
字段名 数据类型 DEFAULT 默认值
定义数据表 tb_emp7,指定员工的部门编号为 1111,SQL 语句如下
create table tb_emp7
(
id int(11) primary key,
name varchar(25) not null,
deptId int(11) default 1111,
salary float
);
14.设置表的属性值自动增加
在每次插入新记录时,系统自动生成字段的主键值。可以通过为表的主键添加 AUTO_INCREMENT 关键字来实现。默认的,在 MySQL 中 auto_increment 的初始值为 1,每新增一条记录,字段值自动加 1。一个表只能有一个字段使用 auto_increment 约束,且该字段必须为主键的一部分。auto_increment 约束的字段可以是任何整数类型(TINYINT、SMALLIN、INT、BIGINT 等)。
字段名 数据类型 AUTO_INCREMENT
定义数据表 tb_emp8,指定员工编号自动递增
create table tb_emp8
(
id int(11) primary key auto_increment,
name varchar(25) not null,
deptId int(11),
salary float
);
创建好后,执行如下的插入语句
insert into tb_emp8(name,salary) values('zhangsan',20000),('lisi',2200),('wangwu',30000);
注意,上述插入语句并未输入 id 的值,但系统已经自动添加,使用 select 进行查看
15.查看表的基本结构
查看表的字段信息,包括:字段名、字段数据类型、是否为主键、是否有默认值等。
describe 表名;
或者简写为
desc 表名;
查看表 tb_emp8 的表结构
- Field:表示字段名
- Type:表示字段的数据类型
- Null:表示该列是否可以存储 NULL 值
- Key:表示该列是否已编制索引。PRI 表示该列是表主键的一部分;UNI 表示该列是 UNIQUE 索引的一部分;MUL 表示在列中某个给定值允许出现多次。
- Default:表示该列是否有默认值,如果有的话值是多少。
- Extra:表示可以获取的与给定列有关的附加信息,例如 auto_increment 等。
16.查看表详细结构
SHOW CREATE TABLE 语句可以用来显示创建表时的 CREATE TABLE 语句,语法格式如下:
SHOW CREATE TABLE <表名>;
根据上图可知,show create table 语句不仅可以查看表创建时的详细语句,而且还可以查看存储引擎和字符编码。
还可以在 表名后添加参数 “\G”,这样的显示结果会更加直观。
17.修改表名
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
其中 TO 为可选参数,使用与否都不影响结果。
将数据表 tb_dept3 改为 tb_deptment3,SQL 语句如下:
18. 修改字段的数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中“表名”指要修改数据类型的字段所在的表的名称,“字段名”指需要修改的字段,“数据类型”值修改后字段的新数据类型。
将数据表 tb_dept1 中 name 字段的数据类型由 VARCHAR(22) 改为 VARCHAR(30)。
19.修改字段名
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
其中 “旧字段名” 指修改前的字段名;“新字段名” 指修改后的字段名;“新数据类型” 指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成和原来一样即可,但数据类型不能为空。
将数据表 tb_dept1 中的 location 字段名为 loc,数据类型保持不变,SQL 语句如下
alter table tb_dept1 change location loc varchar(50);
20.添加字段
添加一个新的字段,一个完整字段包括字段名、数据类型、完整性约束。
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];
新字段名为需要添加的字段的名称;“FIRST” 为可选参数,其作用是将新添加的字段设置为表的第一个字段;“AFTER” 为可选参数,其作用是将新添加的字段添加到指定的 “已传在字段名” 的后面;如果两个参数都没有,则默认将新添加的字段设置为数据表的最后列。
- 添加无完整性约束条件的字段
在数据表 tb_dept1 中添加一个没有完整性约束的 INT 类型的字段 managerId(部门经理编号),SQL 语句如下:
alter table tb_dept1 add managerId INT(10);
- 添加有完整性约束条件的字段
在数据表 tb_dept1 中添加一个不能为空的 VARCHAR(12) 类型的字段 column1, SQL 语句如下:
alter table tb_dept1 add column1 VARCHAR(12) not null;
- 在表的第一列添加一个字段
在数据表 tb_dept1 中添加一个 INT 类型的字段 column2,SQL 语句如下:
alter table tb_dept1 add column2 INT(11) FIRST;
- 在表的指定列之后添加一个字段
在数据表 tb_dept1 中 name 列后添加一个 INT 类型的字段 column3,SQL 语句如下:
alter table tb_dept1 add column3 INT(11) after name;
21.删除字段
ALTER TABLE <表名> DROP <字段名>;
删除数据表 tb_dept1 表中的 column2 字段
22.修改字段的排列位置
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
“字段1” 指要修改位置的字段,“数据类型” 指 “字段1” 的数据类型,“FIRST” 为可选参数,指将 “字段1” 修改为表的第一个字段,“AFTER 字段2” 指将 “字段1” 插入到 “字段2” 后面。
- 修改字段为表的第一个字段
将数据表 tb_dept1 中的 column 字段修改为表的第一个字段,SQL 语句如下:
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
- 修改字段到表的指定列之后
将数据表 tb_dept1 中的 column1 字段插入到 loc 字段后面,SQL 语句如下:
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER loc;
23.更改表的存储引擎
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
将数据表 tb_deptment3 的存储引擎修改为 MyISAM。
24.删除表的外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
首先创建表 tb_emp9,创建外键 deptId 关联 tb_dept1 表的主键 id,SQL 语句如下:
CREATE table tb_emp9
(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept foreign key (deptId) references tb_dept1(id)
);
使用 show create table 查看 tb_emp9 的结构,结果如下:
由上图可以看出已经成功添加了表的外键,下面删除外键约束,SQL 语句如下:
alter table tb_emp9 drop foreign key fk_emp_dept;
可以看到 tb_emp9 中已经不存在 FOREIGN KEY ,原来的外键 fk_emp_dept 的外键约束删除成功。
25.删除没有被关联的数据表
DROP TABLE [IF EXISTS] 表1,表2,....表n;
"IF EXISTS" 用于在删除前判断删除的表是否存在,加上该参数后,再删除表时,如果表不存在,SQL 语句可以顺利执行,但是会发出警告。
删除数据表 tb_dept2 ,SQL 语句如下:
DROP TABLE IF EXISTS tb_dept2;
26.删除被其他表关联的主表
数据表之间存在外键管理的情况下,如果直接删除父表,结果会显示失败。原因是直接删除,将破坏表的参照完整性。如果必须要删除,可先删除与它关联的字表,再删除父表,只是这样同时删除了两个表中的数据。但有的情况下可保留子表,这是如要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表。
在数据库中创建链两个关联表,首先,创建表 tb_dept2,SQL 语句如下:
CREATE TABLE tb_dept2
(
id int(11) primary key,
name varchar(22),
location varchar(50)
);
接下来创建表 tb_emp,SQL 语句如下:
create table tb_emp
(
id int(11) primary key,
name varchar(25),
deptId int(11),
salary float,
constraint fk_emp_dept foreign key (deptId) references tb_dept2(id)
);
使用 show create table 命令查看表 tb_emp 的外键约束,结果如下:
以上执行结果创建了两个关联表 tb_dept2 和表 tb_emp,其中 tb_emp 表为子表,具有名称为 fk_emp_dept 的外键约束,tb_dept2 为父表,其主键 id 被字表 tb_emp 所关联。
删除被数据表 tb_emp 关联的数据表 tb_dept2
首先直接删除父表 tb_dept2,输入删除语句如下:
由上图可看出存在外键约束时,主表不能直接删除。
接下来,解除关联字表 tb_emp 的外键约束,SQL 语句如下:
ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;
语句执行成功后,将取消表 tb_emp 和 tb_dept2 之间的关联关系,此时,可以输入删除语句,将原来的父表 tb_dept2 删除,SQL语句如下:
DROP TABLE tb_dept2;
最后通过 show tables; 查看数据表列表,结果如下: