创建和查看数据库 2
修改和查看数据库字符集 2
创建表create table +表名 5
删除表drop table +表名 6
展示创建表格 show create table customer; 8
复制表格create table staffer_bak like staffer; 9
复制表结构+数据create table staffer_bak2 as /select * from staffer; 9
修改表结构 help create table 10
修改字段alter table staffer_bak add email varchar(50); 10
删除字段alter table staffer_bak drop email; 12
添加表字段主键约束 13
添加外键约束 14
查看表的数据 15
删除主键 15
删除外键 16
添加唯一键 17
删除唯一键 18
修改字段名 18
改存储引擎 19
改表的名字 20
删除数据表 21
Create schema db_shop2; #创建名字为db_shop2的数据库
Create database db_shop2; #创建一个名为db_shop2的数据库
Show databases; #查看当前这个服务器的所有数据库
Show create database db_shop2; #查看db_shop2数据库的定义信息
Alter database db_shop2 charset=utf8mb4 collate utf8mb4_0900_ai_ci; #将db_shop2的字符集修改成utf8mb4
Show create ddatabase db_shop2; #查看db_shop2的字符集
Show character set; #查看所有字符集类型
查看当前服务器存储引擎配置参数“character_set_server”值
查看当前服务器存储引擎配置参数“character_set_database”值
mysql -u root –p
123456
show databases;
help create table;
创建表create table +表名
create database db_shop;
show databases;
use db_shop;
show tables;
create table department(
id int not null auto_increment primary key comment '部门编号',
dept_name varchar(20) not null unique comment '部门名称',
dept_phone char(13) comment '部门电话',
dept_meno varchar(100)comment '部门职能');
show tables;
desc department;
mysql> create table staffer(
-> id int not null auto_increment primary key,
-> dept_id int not null,
-> staff_name varchar(20) not null,
-> sex enum('F','M') default 'F',
-> birthday date,
-> phone char(11),
-> salary decimal(8,1) check(salary>0 and salary<30000),
-> staff_memo varchar(100) comment '员工备注',
-> foreign key(dept_id) references department(id)
-> );
Query OK, 0 rows affected (0.04 sec)
删除表drop table +表名
create table department(
id int not null auto_increment primary key comment '员工号',
dept_id int not null comment '部门编号',
staff_name varchar(20) not null comment '姓名',
sex enum('F','M') default 'F' comment '性别',
birthday date comment '生日',
phone char(11) comment '联系电话',
salary decimal(8,1) check(salary>0 and salary<30000) comment '薪水',
staff_memo varchar(100) comment '员工备注',
foreign key(dept_id) references department(id)
);
展示创建表格 show create table customer;
复制表格create table staffer_bak like staffer;
create table staffer_bak like staffer;
desc staffer;
desc staffer_bak;
复制表结构+数据create table staffer_bak2 as /select * from staffer;
create table staffer_bak2 as
select * from staffer;
修改表结构 help create table
修改字段alter table staffer_bak add email varchar(50);
alter table staffer_bak add email varchar(50);
desc staffer_bak;
alter table staffer_bak
modify email varchar(50) after salary;
删除字段alter table staffer_bak drop email;
添加表字段主键约束
alter table staffer_bak2
add primary key(id);
添加外键约束
alter table staffer_bak2
add foreign key(dept_id) references department(id);
desc staffer_bak2;
查看表的数据
删除主键
删除外键
show create table staffer_bak2; 先查看再删除
alter table staffer_bak2 drop foreign key staffer_bak2_ibfk_1;
desc staffer_bak2;
添加唯一键
mysql> create table department_bak as
-> select * from department;
mysql> desc department_bak;
mysql> desc department;
alter table department_bak add unique(dept_name);
desc department_bak;
show create table department_bak;
删除唯一键
修改字段名
desc staffer_bak;
mysql> alter table staffer_bak
-> rename column birthday to birth;
desc staffer_bak;
改存储引擎
mysql> alter table staffer_bak2 engine=myisam;
改表的名字
alter table staffer_bak rename to satff_bak;
删除数据表
