mysql:数据表的基本操作

4.1.1、登陆mysql数据库：

mysql -h localhost -u root -p

4.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_department3

4.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类型的字段managerId

alter 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;