MySQL数据操作语句
1.总纲
DDL -数据定义语句** create/drop/alter **
create: 创建
drop:删除
alter:修改
DML -数据操作语句 ** insert / delete /update /select **
insert :插入数据
delete :删除数据
update :更新数据
select:查找数据
DCL - 数据控制语句 ** grant/revoke **
grent: 授权
revoke: 取消授权
2. DDL -数据定义语句
2.1 create: 创建
- 创建数据库school
create database school default charset utf8mb4;
- 创建表
create table tb_student
(
stuid int not NULL,
stuname VARCHAR(20) not NULL,
stusex bit DEFAULT 1,
stubirth date,
PRIMARY KEY (stuid)
);
2.2 drop:删除
-- 删除数据库
drop database school
drop database if exists school
-- 删除表
drop table if exists tb_student;
2.3 alter:修改
- 修改表- 增加/修改/删除列
-- 增加
alter table tb_student add column colid int;
alter table tb_student add column stuaddr varchar(255);
-- 修改
alter table tb_student change column stuaddr stuaddr varchar(511);
-- 删除
alter table tb_student drop column stuaddr;
3. DML -数据操作语句
3.1 insert :插入数据
- 插入数据 - 完整输入/选择输入/批量输入
insert into tb_student values (1001,'阳光检',1,'1999-2-2','太阳');
insert into tb_student (stuid, stuname) values (1003,'天残');
insert into tb_student (stuid ,stuname, stusex) values
(1005, '一号', default),
(1006, '二号', 0),
(1007, '三号', 1);
3.2 delete :删除数据
-- 截断表 ---危险操作 >>>慎重,慎重,慎重
-- truncate table tb_student;
-- 删除学号为1002 的学生
delete from tb_student where stuid=1002;
-- 删除所有女生
delete from tb_student where stsex=0;
3.3 update :更新数据
-- 更新操作
update tb_student set stuaddr='四川成都' where stuid = 1003 or stuid =1004;
update tb_student set stuaddr='四川成都' where stuid in (1006,1007);
update tb_student set stubirth = '2000-2-29',stuaddr='太阳以南'
where stuid=1005;
3.4 select:查找数据
4. DCL - 数据控制语句
4.1 grent: 授权
-- 给远程访问权限
create user 'root'@'%' identified by '123456';
-- 更新 flush 使设置生效
flush privileges;
-- 给远程root所有文件全局最大权限
grant all privileges on *.* to 'root'@'%' with grant option;
4.2 revoke: 取消授权
5. 其他
5.1 show 显示
-- 显示数据库
show databases;
-- 显示数据库
show databases;
5.2 between in = 筛选条件
-- between 两者直接 闭区间(包含1001,1006)
-- in 多个
-- = 单个
update tb_student set colid =1 where stuid between 1001 and 1006;
update tb_student set colid =2 where stuid in (1008,1009);
update tb_student set colid =3 where stuid=1007;
5.3 关系 - 添加key约束
- 主键
- 外键
- 主键约束
-- 创建老师表
create table tb_teacher
(
teaid int not null comment '工号',
teaname varchar(20) not null comment '姓名',
teasex bit default 1 comment '性别',
teabirth date comment '生日',
teatitle varchar(10) default '助教' comment '职称',
colid int not null comment '所在学院'
-- 创建时添加 约束
-- 添加主键约束
-- primary key (teaid),
-- 添加外键约束
-- foreign key (colid) references tb_college (colid)
);
-- 添加主键约束
alter table tb_teacher add constraint pk_teacher_teaid
primary key (teaid);
-- 添加外键约束
alter table tb_teacher add constraint fk_teacher_colid
foreign key (colid) references tb_college (colid);
添加唯一约束
ｓｅｌｅｃｔ
数据类型
