开发者社区> 陈卸甲> 正文

MySQL的基础操作及进阶(巨详细)

简介: MySQL的基础操作及进阶(巨详细)
+关注继续查看


本实例使用的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;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23524 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
22218 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
16589 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
21935 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
18582 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
11974 0
+关注
62
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载