本实例使用的MySQL 8.0.23版本
目录
基础操作
进阶操作
基础操作
create table if not exists db1.t8(
id int unsigned ,
age int unsigned,
address varchar(30),
gender enum('男','女') default '女',
primary key(id))engine =innodb default charset =utf8;
drop table db1.t8;
-- 修改表名
rename table db1.t9 to db1.t8 ;
alter table db1.t8 rename db1.t9;
-- 同时修改多个表名
rename table db1.t1 to db1.tt, db1.t3 to db1.tt1;
-- 查询表结构
describe db1.t8;
show columns from db1.t8;
-- 修改字段类型及位置 modify修改
alter table db1.t8 modify address varchar(50) after gender;
-- 增加一个字段 及字段的位置
alter table db1.t8 add column name varchar(30) not null after id;
alter table db1.t8 add column id int;
-- 添加的字段在第一行
alter table db1.t8 modify column id int first ;
-- 删除主键，及添加主键，注意只有主键能自增且自增唯一
alter table db1.t8 drop primary key ,add primary key(uid);
alter table db1.t8 modify column uid int not null auto_increment ;
alter table db1.t8 add column uid int unsigned first ;
-- 36位id号
select uuid();
create table db1.teacher(
tid int unsigned auto_increment,
name varchar(10),
primary key (tid))engine =innodb default charset =utf8;
-- 删除表中所有数据
delete from db1.teacher;
-- 清除数据且恢复主键排序
truncate db1.teacher;
insert into db1.teacher values(null,'王老师'),(null,'张老师'),(null,'马老师'),(null,'赵老师');
create table db1.student(
sid int unsigned auto_increment,
name varchar(10),
stid int unsigned,
primary key (sid));
-- 修改id id要唯一
update db1.teacher set tid=1 where tid=5;
select * from db1.student;
select * from db1.teacher;
-- 删除表
drop table db1.student;
-- 添加表的外键关联 及关联名称 不设置名字 后来不好改建议设
alter table db1.student add constraint stfk foreign key (stid) references db1.teacher(tid);
-- 删除表外键
alter table db1.student drop constraint stfk;
insert into db1.student value (null,'张三',1);
-- left join 左查询
select st.sid,st.name,th.name from db1.student st left join db1.teacher th on th.tid = st.stid;
insert into db1.teacher value (null,'陈老师');
show variables like '%secure%';
select * from db1.student;
insert into db1.student set name='乱舞狂刀',stid=4;
-- replace 与insert相同，但是replace指定主键后会覆盖值，而insert指定主键后会报错
replace into db1.student(sid, name, stid) value (2,'逆天而行',3);
replace into db1.student values (null,'随风起舞',2);
replace into db1.student values (null,'寒风凛凛',2);
replace into db1.student values (null,'大吃四方',2);
insert into db1.student value (null,'穷凶极恶',1);
insert into db1.student value (null,'死亡之眼',4);
insert into db1.student value (null,'鬼魅森林',1);
-- delete 只能一条一条的删除 效率低 灵活 可以加条件 truncate 截断数据跟结构 恢复主键排序 只能截断数据 不触发删除触发器
delete from db1.student where sid=3;
truncate db1.student;
-- limit a,b a为从哪开始，b为查询几个数据 实现分页
-- 第一页
select s.sid 学号,s.name 年龄,t.name 任课老师 from db1.student s left join db1.teacher t on s.stid=t.tid limit 0,4;
-- 第二页
select s.sid 学号,s.name as 年龄,t.name as 任课老师 from db1.student s left join db1.teacher t on s.stid=t.tid limit 4,4;
delete from db1.teacher where tid=1;
alter table db1.student drop constraint stfk;
drop index stfk on db1.student;
select sid,name,stid from db1.student;
-- 添加关联，删除关联（删除一个与之关联的都删除），修改关联（修改一个其他的跟着变化）
alter table db1.student add constraint stfk foreign key(stid) references db1.teacher(tid) on delete cascade on update cascade ;
delete from db1.teacher where tid=2;
-- order by 排序 默认是asc升序 desc降序
select * from db1.student order by stid desc ;
-- concat拼接 ifnull判断为空输出什么 count统计查出的数据 group by根据字段分组必须与聚合函数联用
select concat(ifnull(stid,'无'),'任课老师'),count() from db1.student group by stid order by count();
进阶操作
本地数据的导入导出
需要进行配置my.ini
[mysqld]
允许执行load data infile '' 本地数据导入
local-infile=1
允许select * from xx into outfile '' 查询数据导出
secure_file_priv=''
[mysql]
允许执行load data local infile '' 本地数据导入
local-infile=1
-- 导入本地数据到数据表
load data local infile 'F:/a.txt' into table db1.student
-- 字符集设置 字段分隔符，每个字段被什么字符包围，默认是空字符 terminated结束
charset utf8 fields terminated by ','
-- 转义符，默认是\
escaped by '\'
-- 记录分隔符，如字段本身也含\n吗，那么应该先去除，否则local data会当作另一行记录导入 注意硬换行\r\n
lines terminated by '\n'
-- 每一行文本按顺序导入，建议不要省略
(sid,name,stid);
查询进阶操作
在查询中尽量使用联接查询，效率高
-- 添加字段
alter table db1.student add column addr varchar(30) not null default '郑州';
-- 删除字段
alter table db1.student drop column addr;
-- 左连接查询
select sid,stid,s.name,s.addr,t.name from db1.student s left join db1.teacher t on s.stid = t.tid;
create table db1.s1(
id tinyint unsigned auto_increment,
name varchar(30),
gender enum('男','女'),
score tinyint unsigned,
course varchar(10),
primary key (id));
insert into db1.s1 values(null,'张三','男',90,'计科'),(null,'李四','男',91,'计科'),(null,'王五','男',82,'软工'),
(null,'李六','女',70,'计科'),(null,'张三丰','女',50,'会计'),(null,'杨过','女',30,'软工');select * from db1.s1;
-- 分组统计
select course 学科,max(score) 最高分,min(score) from db1.s1 group by course ;
-- 成绩评定
select a.name, a.score, max(a.score) from
(select s.name,s.score,if(s.score>90,'优秀',if(s.score>80,'良好',if(s.score>60,'及格','补考'))) grade from db1.s1 s ) as a
group by grade;
use db1;
select course 学科,min(score) 最低分,max(score) 最高分 from s1 group by course;
-- 成绩评定方式二
select aa.grade,count(grade) from
(select id,name,gender,score,course,if(score>90,'优秀',if(score>80,'良好',if(score>60,'及格','补考'))) grade from s1) as aa
group by aa.grade order by count(*) asc ;
-- case when ... then... else... end 相当于if语句
select aa.course,count(aa.grade) from
(select course,score,case when score>90 then '优秀' when score>80 then '良好' when score>60 then '及格' else '补考' end grade from s1) as aa
group by course;
-- 联合查询
select 1 union select 3 union select 4;
-- 多表查询
select * from (select 1,2,3) a ,(select 2,3,4) b;
select s.sid,s.name,t.name from student s,teacher t where s.stid=t.tid;
-- 内连接查询 三种效果相同
select * from student s inner join student s1;
select * from student s join student s1;
select * from student s join student s1 on true;
-- 使用on过滤 不需要两种字段相同 using必须两个字段相同 连接查询比普通的多表查询效率高
select * from student s join student s1 on s.stid=s1.name;
select * from student join student using(stid);
select * from student;
select s.sid,s.name,t.name from student s left join teacher t on s.stid=t.tid;
-- 用查询的结果创建一个表 该表无主键
create table st select sid,name from student;
select * from st;
select id,name,course,score,sum(score) over (order by name)from s1;
-- date_add 时间操作
select date_add(now(),interval -1 hour);
-- between 范围查询 包含两个端点
select sid ,name,stid,addr from student where stid between 1 and 3;
-- 自然连接(等值连接,表的字段名称必须相同,去除重复行)
select sid ,name from student natural join teacher ;
-- 添加索引 索引大大提高了查询的速度 但是降低了插入修改的速度 相当于一个只有索引字段和索引值的表
alter table student add index index_name (name);
-- 添加不重复索引
alter table student add unique index index_addr (addr);
-- 删除索引
alter table student drop index index_name;
create view v_stu as
select s.sid 学号,s.name 姓名,t.name 任课老师,s.addr 住址 from student s left join teacher t on s.stid=t.tid;
select * from v_stu;
触发器设置
用触发器实现插入数据时默认为uuid
触发器是在表中的 删除表触发器也会删除
-- 设置uuid为主键 使用8.0.13以后的版本 可设为默认值
create table t_goods(
id char(36) not null primary key,
name varchar(10),
price decimal(6,1));
drop table t_goods;
-- 设置触发器 插入前给id设为uuid；
delimiter $
create trigger tg before insert on t_goods for each row
begin
set new.id=uuid();
end $delimiter ;
drop trigger tg;
insert into t_goods set name='优盘',price=128;
replace into t_goods (name,price) value ('鼠标',58);
insert into t_goods value (uuid(),'显卡',9999);
select * from t_goods;
事件操作
优点 一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。 可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。
缺点 定时触发，不可以调用。
create table ta( id int unsigned primary key auto_increment,
name varchar(15) ,
t datetime);
delimiter $
-- 建立事件5秒后开始 一秒执行一次插入语句
create event et on schedule every 1 second starts current_timestamp + interval 5 second do
insert into ta set name='aa' ,t=now();
delimiter ;
delimiter $
-- 事件15秒后开启 10秒执行一次清空语句
create event rm on schedule every 10 second starts current_timestamp + interval 15 second
do truncate ta;delimiter ;
drop event rm;
drop event et;
-- 查看所有事件
show events;
-- 修改事件的名称
alter event ta rename to ta1
-- 删除事件
drop event if exists e01;
-- 事件暂停禁用，但不删除
alter event rm disable;
-- 事件启用
alter event rm enable;
