mysql -h localhost -u root -p4.1.2、选择数据库
use test;4.1.3、展示所有的数据库
show databases;4.1.4、创建数据库
create database test_db;4.1.5、展示创建的数据库的信息
show create database test_db \G;4.1.6、删除数据库
drop database test_db;4.1.7、创建数据库表
create table tb_emp1 ( id int(11), name varchar(25), deptid int(11), salary float );
定义员工表2,其主键为id
create table tb_emp2 ( id int(11) primary key, name varchar(25), deptid int(11), salary float );或者
create table tb_emp3 ( id int(11), name varchar(25), deptid int(11), salary float, primary key(id) );定义数据库表tb_emp4,创建多字段联合主键sqk语句
create table tb_emp4 ( name varchar(25), deptid int(11), salary float, primary key(name,deptid) );定义数据库表tb_emp5,并在tb_emp5表上创建外键约束
创建一个部门表
create table tb_dept1 ( id int(11) primary key, name varchar(22) not null, location varchar(50) );定义数据库表tb_emp5,让它的键deptid作为外键关联到tb_dept1的主键id
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) );[CONSTRAINT <外键名>] FOREIGN KEY 列名1 [ ,列名2,…]
REFERENCES <主表名> 主键列1 [ ,主键列2,…]
唯一性约束
定义部门表tb_dept2,指定部门的名称唯一,sql语句为:
create table tb_dept2 ( id int(11) primary key, name varchar(22) unique, location varchar(50) );定义数据库表tb_dept3,指定部门的名称唯一,sql语句为:
create table tb_dept3 ( id int(11) primary key, name varchar(22), location varchar(50), constraint sth unique(name) );使用默认约束
定义数据库表tb_emp7,指定员工的部门编号默认为1111,sql语句为:
create table tb_emp7( id int(11) primary key, name varchar(25) not null, deptid int(11) default 111, salary float, constraint fk_emp_dept3 foreign key(deptid) references tb_dept1(id) );设置表的属性值自动增加
在数据库应用中,经常希望在每次插入新记录时,系统就会自动生成字段的主键值。
可以通过为表主键添加AUTO_INCREMENT关键字来实现。一个表只能有一个字段使用AUTO_INCREMENT约束,
且该字段必须为主键的一部分。
字段名 数据类型 AUTO_INCREMENT
定义数据库表tb_emp8,指定员工的编号自动递增,sql语句为:
create table tb_emp8 ( id int(11) primary key auto_increment, name varchar(25) not null, deptid int(11), salary float, constraint fk_emp_dept5 foreign key(deptid) references tb_dept1(id) ); insert into tb_emp8 (name,salary) values ('aaa',1000),('bbb',2000),('ccc',3000)4.1.8、展示所有的表
show tables;4.2查看数据库表结构
4.2.1 查看表基本结构,该语句可以查看表的字段信息,其中包括:字段名、字段数据类型、是否为主键、是否有默认值等。
例4.11分别使用describe和desc查看表tb_dept1和表tb_emp1的表结构,sql如下:
describe tb_dept1; desc tb_emp1;4.2.2 查看表详细结构语句SHOW CREATE TABLE,语句可以用来显示数据表的创建语句。
例4.1.2使用SHOW CREATE TABLE查看表tb_emp1的详细信息,SQL语句如下:
show create table tb_emp1; show create table tb_emp1 \G;4.3修改数据库表
4.3.1修改表名:ALTER TABLE <旧表名> RENAME [TO] <新表名>;
例:4.3.1.1将数据库表tb_dept3改名为tb_department3
alter table tb_dept3 rename tb_department34.3.2 修改字段的数据类型
:ALTER TABLE <表名> MODIFY <字段名> <数据类型> (可能造成数据的丢失)
例:4.3.2 将数据库表tb_dept1中name字段的数据类型由varchar(22)的修改为varchar(30)
alter table tb_dept3 modify name varchar(30)
4.3.3 修改字段名
:ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
例4.3.3 :将数据库表tb_dept1中的location字段名称改为loc,同时将数据类型变为varchar(60)
alter table tb_dept1 change location loc varchar(60);
4.3.4 添加字段:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];
例4.3.4.1在数据库表tb_dept1种添加一个没有完整性约束的int类型的字段managerIdalter table tb_dept1 add managerId int(10);例4.3.4.2在数据库表tb_dept1种添加一个不能为空的varchar(12)类型的字段column1
alter table tb_dept1 add column1 varchar(12) not null;例4.3.4.3在数据表tb_dept1中添加一个INT类型的字段column2,SQL语句如下:
ALTER TABLE tb_dept 1 ADD column2 INT(11) FIRST;例4.3.4.4在数据表tb_dept1中name列后添加一个INT类型的字段column3,SQL语句如下:
ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;4.3.5 删除字段:ALTER TABLE <表名> DROP <字段名>;
例:4.3.5删除数据表tb_dept1表中的column2字段。
alter table tb_dept1 drop column2;
4.3.6 修改字段的排列位置:
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
例:4.3.6.1将数据表tb_dept中的column1字段修改为表的第一个字段,SQL语句如下:ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;例:4.3.6.2将数据表tb_dept1中的column1字段插入到location字段后面,SQL语句如下:
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;4.3.7 更改表的存储引擎
:ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
例4.3.7将数据表tb_deptment3的存储引擎修改为MyISAM。
ALTER TABLE tb_deptment3 ENGINE=MyISAM;4.3.8 删除表的外键约束
:ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
对于数据库中定义的外键,如果不再需要,可以将其删除。外键一旦删除,就会解除主表和从表间的关联关系。
例4.3.8删除数据表tb_emp9中的外键约束
ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;4.4 删除数据表
4.4.1 删除没有被关联的表:
MySQL中,使用DROP TABLE可以一次删除一个或多个没有被其它表关联的数据表。
格式:DROP TABLE [IF EXISTS]表1, 表2, . . . 表n;
例4.4.1删除数据表tb_dept2,SQL语句如下:
DROP TABLE IF EXISTS tb_dept2;
4.4.2 删除被其它表关联的主表
在数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败,原因是直接删除,将破坏表的参照完整性。如果必须要删除,可以先删除与之关联的子表,再删除父表。但是这样同时删除了两个表中的数据。但有的情况下可能要保留子表,这时如要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表。
在数据库中创建两个关联表,首先,创建表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) );删除被数据表tb_emp关联的数据表tb_dept2。
首先直接删除父表tb_dept2,输入删除语句如下:
DROP TABLE tb_dept2;ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
可以看到,如前所述,在存在外键约束时,主表不能被直接删除。
接下来,解除关联子表tb_emp的外键约束,SQL语句如下:
ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;语句成功执行后,将取消表tb_emp和表tb_dept2之间的关联关系,此时,可以输入删除语句,将原来的父表tb_dept2删除,SQL语句如下:
DROP TABLE tb_dept2;