MySQL数据库的基本语法总结

鳄鱼君

发表文章数:643

热门标签

, ,

Vieu四代商业主题

高扩展、安全、稳定、响应式布局多功能模板。

¥69 现在购买
首页 » Python » MySQL数据库的基本语法总结

首先看一下具体的语法总结:

MySQL数据库的基本语法总结

MySQL数据库操作

show databases;  #查看一下所有的数据库

create database animal charset=utf8; #创建数据库animal

use animal #切换到animal数据库中
show tables #查看student数据库中所有的表,现在是没有的

create table dog (
id int auto_incerment primary key ,  #int类型 自增 主键
name varchar(12) not null,  #可变字符串12个字节 不能为空 
);  #dog表就建好了,接下来是插入数据

alter table dog add forme varchar(12);  #增加一个forme体型 可变字符串
alter table dog add birthday datetime; #再增加一个birthday生日 类型为日期

alter table dog drop birthday ; #删除birthday属性
show create table dog; #查看建表时的语句,可以看下自己表中的属性

rename table dog to dog_animal; #修改表名为dog_animal
drop table dog_animal; #删除表dog_animal
 

mysql表操作

#接着上面的操作
show databases; #先查看一卡数据库
use animal; #接着进到animal数据库中
show tables; #查看数据库中的表,我们重新创建一个dog表

create table dog(
id int auto_increment primary key,
name varchar(20) not null,
birthday datetime not null,
gender bit default 0
); #狗应该也有性别吧,默认是0公狗 布尔类型

单条全列插入
insert into dog values(0,'哈士奇','2018-1-30',1);

缺省插入
insert into dog(name,birthday) values('藏獒','2017-6-12');

批量插入
insert into dog values('边境牧羊犬','2003-12-30',0),('吉娃娃','2013-2-30',1),('德国牧羊犬','2009-4-28',0)
insert into dog(name,birthday) values('贵宾犬','2011-3-28'),('松狮','2000-8-10')

逻辑删除 添加一个isdelete字段
update dog set isdelete=1 where name='藏獒'; #把藏獒的isdelete字段修改为1,表示删除用户看不到

物理删除
delete from dog where name='哈士奇'; 

 

使用单条全列插入的时候需要加上默认值(gender性别)0,不然报错。datetime类型必须加上引号,不然报错。show variables like ‘%character%’;查看数据表的字符编码,可能你的狗全部都是乱码,这就是在建数据库animal的时候就设置charset为utf8的原因,狗乱码或者显示??的改一下。语句必须加分号;,不然不会运行。

MySQL查询条件

select * from dog ;   #查看dog表中的所有数据
select * from dog where id>1;  #where后跟查询条件,id大于1的

select * from dog where id=2 and gender=0; #并
select * from dog where name like '哈'; #模糊找名字开头是 哈 的
... #很多都一样 自己练...
 

MySQL-ER模型

就是mysql中的表和表之间可以建立关系,但是我们在删除某一个表的时候,和它有关系的表就会报错,这个应该不常用吧,我们先简单了解一下。三个表,学生(stu)表(id,name,birthday,city) 科目(subjects)表(id,title),成绩(scores)表(id,score)

#学生表
(0,'小红','2000-2-13','河南'),(0,'小明','2001-12-30','上海'),(0,'大白','1998-3-22','郑州')

 create table stu(
 id int auto_increment primary key,
 name varchar(12) not null,
 birthday datetime,
 gender bit default 0,
 isdelete bit default 0,
 city varchar(12)
);
#批量插入数据
insert into stu values(0,'小红','2000-2-13 13:12:30',0,0,'河南'),(0,'小明','2001-12-30 10:12:33',1,0,'上海'),(0,'大白','1998-3-22 16:30:22',0,0,'未来');

#科目表
(0,'数学'),(0,'英语'),(0,'美术'),(0,'音乐')
create table subject(
id int auto_increment primary key,
title varchar(12));

#批量插入数据
insert into subject values(0,'数学'),(0,'英语')(0,'美术')(0,'音乐');


#成绩表 我们通过成绩表关联stu和subjects表
(0,1,1,80),(0,2,2,90),(0,1,2,69),(0,3,2,50)

create table scores(
id int auto_increment primary key,
stuid int,
subid int,
score decimal(5,2),   #7位数,小数点后保留两位 
foreign key(stuid) references stu(id), #关联外键 学生的id 引用stu表的id
foreign key(subid) references subjests(id)#关联外键 课程的id 引用subjects表的id
);

insert into scores values(0,1,1,80),(0,2,2,90),(0,1,2,69),(0,3,2,50);
 

我们通过scores表关联stu和subjects表,然后就可以链接查询,使用inner join 语句,先找好关系,scores表中的stuid是stu中的id,scores中的subid是subjects表的id

1.查找学生的名字,学科的标题,对应的分数

select stu.name,subjects.title,scores.score
from scores
inner join stu on scores.stuid=stu.id
inner join subjects on scores.subid=subjects.id;

注意各个表之间的对应关系,其实也没什么关系

2.学生姓名的平均分
select stu.name,avg(scores.score)
from scores
inner join stu on scores.stuid=stu.id   #关联学生表
group by stu.name;  #根据学生名字分组展示


3.男生姓名的总分
select stu.name,sum(scores.score)
from scores
inner join stu on scores.stuid=stu.id
where stu.gender=0  #筛选男生gender=0的
group by stu.name;

4.学科分组显示平均分
select subjects.title,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.title;

5.学生名字,学科名字显示平均分,最大值
select stu.name,subjects.title,avg(scores.score),max(scores.score)
from scores
inner join stu on scores.stuid=stu.id
inner join subjects on scores.subid=subjects.id
group by stu.name;
 

mysql链接查询还有个left join 和right join ,都差不多,什么意思呢,就是我们上面的stu表(3条数据),subjects表(4条数据),scores表(4条数据)的数据的数目是不一样的,使用左关联跟右关联显示的结果是不一样的,不再演示,自己玩去吧。

select * from scores
left join stu on scores.stuid=stu.id;

select * from scores
right join stu on scores.stuid=stu.id; 

MySQL视图

视图就是把sql语句封装起来,像我们上面那么长的查询语句,很麻烦,有点需求都是重复的,当然我们上面的查询都是不重复的,就是解释一下视图,看代码就好理解了。

create view viewstu as    #创建视图viewstu,sql语句就是上面最后一个
select stu.name,subjects.title,avg(scores.score),max(scores.score)
from scores
inner join stu on scores.stuid=stu.id
inner join subjects on scores.subid=subjects.id
group by stu.name;

我们重复查看结果的时候,就可以使用
select * from viewstu;

删除shitu
drop view viewstu;
 

MySQL事务

mysql事务的四大特性ACID,A原子性:所有的操作在数据中是不可分割的,必须全部完成或者均不执行。C一致性:几个并行执行的事务,必须按照某一顺序执行,结果相一致。I隔离性:事务与事务之间是隔离的。D持久性:对于任意已经提交的事务,系统会保持不丢失的状态,数据库出现故障也不会丢失。

show databases; #查看所有的数据库
use animal; #按照上面的接着 进入animal数据库
show tables; #查看所有的表
select * from dog; #先查看一下dog中的数据
begin;  #开启事务   重启一个终端  进入mysql,查看dog表的信息
insert into dog(name,birthday) valuses('蝴蝶犬','2009-12-30'); #在缓存区插入的数据
#进入刚刚重开的终端,查看dog表  是没有这条信息的
commit;  #提交事务
#提交事务之后再次查看,才会显示刚才插入的数据
rollback; #事务回滚
#事务回滚之后再次查看dog表  刚才插入的数据消失了

mysql中的事务就这么意思,但是我在windows上测试的时候这个事务不起作用,插入数据之后,在另一个终端直接就显示出来,事务回滚也不行,目前不知道什么原因。

mysql查询效率

mysql跟mongodb一样,在成万的数据中查找需要创建索引,这样可以提高这个查询的效率,但是查完就需要删除索引,所以可以提高查询的效率,但是不删除的话影响我们对数据的操作。

创建索引
create index suoyin on subjects(title(4)); 

显示索引
show index from subjects;

删除索引
drop index suoyin on subjects;
 

创建索引就是为了提高查询的效率,可以测试一下查询的时间是多少:

验证时间
set profiling=1;
create index suoyin on subjects(title(3)); 创建索引查询
show profiles;  显示查询语句的时间 

MySQL自关联

我们在将ER模型的时候,有三张表,数据就几条,在效率方面我们完全可以放到一张表中去,这个叫做mysql自关联。网上查询省市区的例子不错,但是没有这个sql文件,所以我找了一份,感兴趣的可以下载下来做个练习。

sql文件预览
insert into cities values(110000,"北京",100000);
insert into cities values(110100,"北京市",110000);
insert into cities values(110101,"东城区",110100);
insert into cities values(110102,"西城区",110100);
insert into cities values(110105,"朝阳区",110100);
insert into cities values(110106,"丰台区",110100);
insert into cities values(110107,"石景山区",110100);
insert into cities values(110108,"海淀区",110100);
insert into cities values(110109,"门头沟区",110100);
insert into cities values(110111,"房山区",110100);
insert into cities values(110112,"通州区",110100);
insert into cities values(110113,"顺义区",110100);
insert into cities values(110114,"昌平区",110100);
insert into cities values(110115,"大兴区",110100);
insert into cities values(110116,"怀柔区",110100);



创建一个城市表(id,name,pid)
create table cities(
id int auto_increment primary key,
name varchar(50) not null,
pid int,
foreign key(pid) references cities(id)   #自关联pid 引用cities的id
);

#查询一级行政区 省,直辖市 pid=100000
select * from cities where pid=100000;

#查询二级行政区 市 pid=id 观察前面的数据知
select c.* from cities as c
inner join cities as province on c.pid=province.id   #取cities别名province的id等于别名c的pid
where province.name='河南省');  #c是个别名

 

导入sql文件使用source 文件名.sql,文件必须在当前目录下,先创建好表在导入文件。

MySQL备份和恢复

备份
mysqldump -u root -p 数据库名字 表的名字 > 备份文件的绝对路径 

恢复
mysql -u root -p db_name < 备份文件的绝对路径
 

未经允许不得转载:作者:鳄鱼君, 转载或复制请以 超链接形式 并注明出处 鳄鱼君
原文地址:《MySQL数据库的基本语法总结》 发布于2020-02-10

分享到:
赞(0) 赏杯咖啡

评论 抢沙发

1 + 4 =


文章对你有帮助可赏作者一杯咖啡

支付宝扫一扫打赏

微信扫一扫打赏

Vieu4.6主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。
切换注册

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录
切换登录

注册