mysql命令大全--复习笔记2

简介:
建立数据库
建表(主键,外键)
修改表名,列名及类型,默认值
增删列

1.使用CREATE语句创建YGGL1数据库

 create database YGGL1;

2.在YGGL1数据库存在的情况下，使用CREAT DATEBASE语句新建数据库YGGL1,会被提示该数据库已存在,若加上IF NOT EXISTS关键词创建YGGL1,正确.

create database YGGL1;

create database if not exists YGGL1;

3.在YGGL1数据库中创建表Departments,Salary

use YGGl;
create table Departments
 (
 departmentID char(3) not null primary key,
 departmentName char(20) not null,
 note text(16) null
 )engine=innodb;


create table salary
(
	employmeeID char(6) not null primary key,
	Income	 float(8) 	not null, 
	Outcome float(8) not null
)engine = innodb;

4.在YGGL1数据库中创建表employees

create table employees
 (
 Employees char(6) not null primary key,
name char(10) not null,
education char(4) not null,
birthday date not null,
sex char(2) not null,
workyear tinyint(1) null,
address varchar(20) null,
phonenumber char(12) null,
departmentID char(3) not null,
foreign key(departmentID) references  Departments(departmentID) 
)engine=innodb;

上方的外键是在建表的同时加上的.
建表之后可以修改外键:
添加外键约束: alter table 从表 add foreign key(外键字段) references 主表(主键字段);
删除外键约束: alter table 表名 drop foreign key 外键名;

5.创建一个和Employees表结构相同的空表Employees0,Employees00;

create table Employees0 as select * from Employees where 1=2;

create table Employees00  like Employees0;

表已创建完成

格式相同.

6.（1）删除表employmees0,employmees00。

drop table employees0;
drop table employees00;

7.修改表名:创建表Employees00并改名为Employees;

show tables;
create table Employees00  like Employees;
alter table employees00 rename employees123;
show tables;

alter table employees123 rename to employees12;
alter table employees12 rename  employees123;

8.修改列名及类型:

查看表的属性

desc employees123;
show create table employees123;
show columns from employees123;

修改列名(同时可以修改长度,类型)

alter table employees123 change Employees Employees00 char(7);
desc employees123;
alter table employees123 change Employees00 Employees00 tinyint(1);
desc employees123;
alter table employees123 change Employees00 Employees char(6);
desc employees123;

修改列的属性

desc employees123;
alter table employees123 modify Employees varchar(7);
desc employees123;
alter table employees123 modify Employees char(6);
desc employees123;

desc employees123;
alter table employees123 alter sex set default 1;
desc employees123;

desc employees123;

alter table employees123
modify sex  char(2) not null default 2;

desc employees123;

只改默认值

alter table employees123
alter sex set default 1;

desc employees123;

改回去

alter table employees123
modify sex  char(2) null;

desc employees123;

9.增删列

alter table employees123
add lie char(11);

desc employees123;

alter table employees123
drop lie;

desc employees123;