–window shell 解决中文乱码问题
chcp 65001
–删除数据库
drop database bd17Day02;
–创建数据库bd17Day02并设置该数据库使用字符集为utf8
create database bd17Day02 default character set utf8;
–显示所有库
show databases;
–查看数据库bd17Day02创建详细过程
show create database bd17Day02;
–使用bd17Day02;
use bd17Day02;
–查看当前使用的库名称
select database();
–创建表
CREATE TABLE tb_stu (
id int(11) DEFAULT NULL COMMENT ‘编号’,
name varchar(50) DEFAULT NULL COMMENT ‘姓名’,
gender int(1) DEFAULT NULL COMMENT ‘性别 1代表男 2代表女’
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–显示当前库所有的表
show tables;
–查看创建详细过程
show create table tb_stu;
–删除数据表
drop table tb_stu;
–查看表结构
describe tb_stu;
–插入数据
insert into tb_stu (id,name,gender) values (1,“李昊哲”,1),(2,“李爽”,2);
insert into tb_stu (id,gender) values (3,1),(4,2);
–查看表的所有数据
select * from tb_stu;
–约束
–默认约束DEFAULT
create table tb_login (
id int(11) DEFAULT NULL COMMENT ‘编号’,
account varchar(50) DEFAULT NULL COMMENT ‘账号’,
password varchar(50) DEFAULT ‘123456’ COMMENT ‘密码’
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_login (id,account,password) value (1,‘admin’,‘admin’);
insert into tb_login (id,account) value (2,‘lhz’);
select * from tb_login;
–非空约束NOT NULL
–如果表存在则删除该表
drop table if exists tb_login;
create table tb_login (
id int(11) NULL COMMENT ‘编号’,
account varchar(50) NULL COMMENT ‘账号’,
password varchar(50) NULL DEFAULT ‘123456’ COMMENT ‘密码’,
id_card varchar(50) NOT NULL COMMENT ‘身份证’
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_login (id,account,id_card) value (1,‘lhz’,‘22040219845624’);
insert into tb_login (id,account) value (2,‘admin’);
select * from tb_login;
–唯一性约束unique
drop table if exists tb_login;
create table tb_login (
id int(11) NOT NULL COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
id_card varchar(50) NULL COMMENT ‘身份证’,
UNIQUE KEY id_card (id_card)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_login (id,account,id_card) value (1,‘lhz’,‘22040219845624’);
insert into tb_login (id,account,id_card) value (2,‘admin’,‘22040219845624’);
select * from tb_login;
–主键|复合主键约束primary key
–主键 = 非空 + 唯一
drop table if exists tb_login;
CREATE TABLE tb_login (
id int(11) NOT NULL COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop table if exists tb_login;
CREATE TABLE tb_login (
id int(11) COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_login (id,account) value (1,‘lhz’);
insert into tb_login (id,account) value (1,‘admin’);
insert into tb_login (account) value (‘admin’);
–主键自增长
drop table if exists tb_login;
CREATE TABLE tb_login (
id int(11) AUTO_INCREMENT COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_login (account) values (‘lhz01’),(‘lhz02’),(‘lhz03’),(‘lhz04’),(‘lhz05’);
CREATE TABLE tb_login (
id int(11) AUTO_INCREMENT COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
insert into tb_login (account) values (‘lhz01’),(‘lhz02’),(‘lhz03’),(‘lhz04’),(‘lhz05’);
select * from tb_login;
–主键|复合主键约束primary key
drop table if exists tb_order;
CREATE TABLE tb_order (
order_id int(11) COMMENT ‘订单号’,
order_flow int(11) COMMENT ‘流水号’,
fruit_id int(11) NOT NULL COMMENT ‘水果编号’,
fruit_price int(11) NOT NULL COMMENT ‘水果单价’,
quantity int(11) NOT NULL COMMENT ‘购买数量’,
PRIMARY KEY (order_id,order_flow )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_order
(order_id,order_flow,fruit_id,fruit_price,quantity)
values
(1000,1,1,1,1),(1000,2,2,2,2),(1000,3,3,3,3),(1001,1,1,1,1),(1001,2,2,2,2);
select * from tb_order;
–外键约束foreign key
–物理外键约束foreign key
drop table if exists tb_login;
drop table if exists tb_emp;
create table tb_emp (
id int(11) auto_increment comment ‘员工编号’,
nickname varchar(50) not null comment ‘员工姓名’,
primary key (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_login (
id int(11) AUTO_INCREMENT COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
eid int(11) NOT NULL COMMENT ‘员工编号’,
PRIMARY KEY (id),
constraint fk_login_emp foreign key (eid) references tb_emp (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–逻辑外键约束foreign key
drop table if exists tb_login;
drop table if exists tb_emp;
create table tb_emp (
id int(11) auto_increment comment ‘员工编号’,
nickname varchar(50) not null comment ‘员工姓名’,
primary key (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_login (
id int(11) AUTO_INCREMENT COMMENT ‘编号’,
account varchar(50) NOT NULL COMMENT ‘账号’,
password varchar(50) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
eid int(11) NOT NULL COMMENT ‘员工编号’,
PRIMARY KEY (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tb_emp (nickname) values (‘李昊哲’),(‘phoenix’);
insert into tb_login (account,eid) values (‘lhz’,1),(‘phoenix’,2);
select * from tb_login;
select * from tb_emp;
select l.account,l.password,l.eid,e.id,e.nickname from tb_login as l inner join tb_emp as e;
select l.account,l.password,l.eid,e.id,e.nickname from tb_login as l inner join tb_emp as e where 1 and l.eid = e.id;
select l.account,l.password,e.nickname from tb_login as l inner join tb_emp as e where 1 and l.eid = e.id;
–备份与还原
–备份
mysqldump bd17Day02 -h localhost -u root -p > C:/tangcco/tcbd1017/mysql/bd17day02.sql
mysqldump bd17Day02 -h localhost -u root -p > ~/bd17day02.sql
–还原
mysql -h localhost -u root -p bd17Day02 < C:/tangcco/tcbd1017/mysql/bd17day02.sql
–还原
mysql -uroot -p
drop database bd17Day02;
create database bd17Day02 default character set utf8;
use bd17Day02;
select database();
source C:/tangcco/tcbd1017/mysql/bd17day02.sql
–修改表名称
alter table tb_login rename to tb_auth;
–修改字段数据类型
alter table tb_auth modify password varchar(150);
–修改字段约束
alter table tb_auth modify password varchar(150) not null default ‘123456’;
–修改字段名称
alter table tb_auth change password auth_string varchar(50);
–修改字段约束
alter table tb_auth change auth_string password varchar(50) not null default ‘123456’;
alter table tb_auth modify password varchar(150) not null default ‘123456’;
alter table tb_auth change password password varchar(50) not null default ‘123456’;
–添加字段
alter table tb_emp add gender int(1) default 1;
alter table tb_emp add birthday date first;
alter table tb_emp add uuid varchar(32) after id;
–删除字段
alter table tb_emp drop birthday;
–修改字段位置
alter table tb_emp modify uuid varchar(32) first;
alter table tb_emp modify uuid varchar(32) after id;
–修改存储引擎
alter table tb_stu engine=MyISAM;
–删除外键约束
alter table tb_auth drop foreign key fk_login_emp;
–删除表存在主外键关了的表的两种方式
–方式一 先删除外键然后在删除表
–方式二 先删除从表再删除主表
