mysql学习笔记整理(DDL,DML,约束,查询)

最新推荐文章于 2023-03-17 21:12:24 发布

翊木

最新推荐文章于 2023-03-17 21:12:24 发布

阅读量270

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/Xiaojia_guniang/article/details/107885552

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql基础用法笔记

注释:
#注释 – 注释
/*
多行注释
*/

– 查询所有库

show databases;

– 使用某个库 (java是库名)

use java;

– 查看所有表

show table;

– 创建数据库 create database + 库名

create database java;

– 修改数据库的编码格式 (java是库名)

alter database java character set utf8;

– 查询数据库编码格式

show variables like 'character%';

– 删除数据库 drop database 库名;(谨慎使用!谨慎使用!谨慎使用!)

drop database java02;

建表语句
create table 表名(
字段1 数据类型(长度) [约束]
字段2 数据类型(长度) [约束]
…
);
创建学生表,存储学号,姓名,年龄,生日,分数(注意是(),不是{})

create table stu(
	sid int(11),
	sname varchar(10),
	sage int, -- int类型默认长度11,所有可以不指定长度,当然,也可以自己指定
	sbirthday date, -- 日期不需要指定长度
	score double(5,2) -- 总长度5,包含小数点2位
);

数据类型
int 整形长度11
char 字符串
varchar 字符串,可变化的字符串,
date 日期
datetime 日期时间
timestamp 时间戳
float 浮点型
double 浮点型 double(长度 ,小数点个数) double(5,2)123.45
blob 二进制数据

– 添加列 : alter table 表名 add 列名类型(长度);
– 给stu添加性别字段(一般只有男或女,所以可以指定字符串长度固定为一)

alter table stu add sex char(1);

– 删除列:alter table 表名 drop 列名;

alter table stu drop sex;

– 列更名: alter table 表名 change 旧列明新列名;

alter table stu change sage stugae varchar(10);

– 表改名(将stu改成stu2)

rename table stu2 to stu;

– 表删除(慎用+1)

drop table stu;

– 查看表结构/查看建表语句

show create table stu;

在这里插入图片描述
比较小,而且都在一行查看并不方便,也可以在表上鼠标右键点击-----选择对象信息------在下面的DDL里查看

老是报错1055

show variables like 'sql_mode';
set sql_mode='';
set sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';

可以解决但是治标不治本,下一次打开还是会报错
在my.ini的[mysqld]里加一句,然后重启可以解决,不会关就简单粗暴重启电脑(嗯,我就是这样干的,小白萌新伤不起)
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
网上搜了点解释:

STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE：这个模式影响了是否允许日期中的月份和日包含0。如果开启此模式，2016-01-00是不允许的，但是0000-02-01是允许的。它实际的行为受到strict mode是否开启的影响1。
NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期。它实际的行为受到 strictmode是否开启的影响2。
-ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
(说实话,没怎么看懂,大概知道就是某个模块不允许单独写什么东西,具体不清楚,别问,问就是你对)
增加数据- 插入
insert into 表名(字段1,字段2,…) values (值1,值2,…);
1 插入的字段个数任意,顺序与原表无关
2 值的顺序与字段顺序一致
3 插入的数字直接写数字即可,字符串和日期需要使用单引号
4 插入空值时,传入null
5 插入全表数据时,还可以省略写字段,默认插入全表数据
insert into stu values(值1,值2,值3…);
那么后续的值顺序与原表一致

– 插入学生学号,姓名,年龄

insert into stu(sid,sname,sage) values(1,'张三',18);
insert into stu(sage,sname,sid,sbirthday)
 values(18,'李四',2,'2020-08-06');
insert into stu(sage,sname,sid,sbirthday)
 values(19,'王五',3,null);
insert into stu(sid,sname,sage,sbirthday,score)
 values(4,'赵六',20,null,96.2);
insert into stu values(5,'周七',17,'2020-08-06',95);
insert into stu values(6,'周七',17,'2020-08-06',95);
insert into stu values(7,'周七',17,'2020-08-06',95);

修改数据:更新
修改单个字段:
update 表名 set 字段 = 值 [where 字段=值]
修改多个字段
update 表名 set 字段=值,字段2=值2 [where 字段=值]限定条件 (改全表)
注意:不加后面的where 过滤条件,更新全表
加上where后的条件,只更新符合条件的
根据where 后的条件.找不到符合条件的数据就不操作
设置学生表年龄为18
– 将学号为1的学生年龄改为20

update stu set sage = 20 where sid=1;

– 将学号为4的学生姓名改为张飞,生日改为2020-01-01

update stu set sname='张飞' ,sbirthday='2020-01-01' where sid=4;
update stu set sage=12 where score!=95;
update stu set sage=15 where score>=95;

删除
delete from 表 [where 字段=值]
注意:不加后面的where 过滤条件,删除全表
加上where后的条件,只删除符合条件的

– 删除学号为5的学生信息

delete from stu where sid=7;
delete from stu where sname='周七';

– 删全表(delect表还在内容没了,drop表都没有了)

delete from stu;

约束该列数据存储时的限制
– 在创建的时候指定该列的限制条件,后续也可以更改
主键约束
自增
唯一
不为空
默认值
注释
主键 primary key:设置主键的列,不为空,且唯一
一般是给id设置主键

– 主键设置方式1

create table s1(
	id int primary key,
	name varchar(10)
);

– 主键设置方式2
联合主键:联合的是一个整体,整体重复才算重复,单个列数据重复不算重复

create table s2(
	id int,
	name varchar(10),
	age int,
	birthday date,
	-- primary key(id)
	primary key(id,name) -- 联合主键
);

– 自增:列值自动增长,一般配合主键使用 auto_increment
– 当主键设置了自增,再插入数据时不设置主键也可以运行成功,会从头按顺序赋值,每次自增一
有时即使运行不成功数据已经自增就会报错,类似id已被占用,或者没有按照原表顺序进行递增

create table s3(
	id int primary key auto_increment,
	name varchar(10)
);
insert into s3 (id, name) values (5,'zs') ; #手动插入不受影响
delete from s3 where id=5; #已删除的id不会再被使用,会默认继续自增

唯一 unique :让该列数据唯一,不重复

create table s4(
	id int primary key auto_increment,
	name varchar(10) unique
);

不为空: 该列的值,在插入时不能为空 not null

create table s5(
	id int primary key auto_increment,
	name varchar(10) unique,
	age int not null
);
-- 报错 字段“age”没有默认值 [Err] 1364 - Field 'age' doesn't have a default value
insert into s5 (name) values ('zs');

默认值:插入数据时,没有指定该列,就会填充默认值

create table s6(
	id int primary key auto_increment,
	name varchar(10) unique default 'admin',
	age int not null default 0 -- 当不指定age时默认是0
);
insert into s6 (name) values ('zs');
insert into s6 (id) values (2);

注释 comment :在建表时给列指定注释,如果有约束 , 放在约束后

create table s8(
	id int primary key auto_increment comment '主键',
	name varchar(10) unique default 'admin' comment '名字',
	age int not null default 0  comment '年龄' -- 当不指定age时默认是0
);
insert into s8 (id) values (1);
drop table s1,s2,s3,s4,s5,s6,s7,s8;
drop table stu,emp;

查询 select:从表中查出数据,返回的是一张虚拟表
基本查询:select 字段1,字段2,…from 表名;
1)字段顺序与原表无关
2)字段个数根据需求定
3)返回的虚拟表字段顺序与原表无关,与select后的顺序有关

查询所有列,简写成*

select * from stu;

查询指定列:
查询学生学号和姓名
查询学生学号和成绩

select sid,sname from stu;
select sid,score from stu;

条件查询:返回符合条件的数据
条件查询需要在基础查询语句后,使用 where 字段运算符值;
条件查询关系符号 > < >= <= != =
and
or
in(set) set集合,属于集合,返回 ,不属于,不返回
not in
between and

– 查询学号1001的学生信息

select * from stu where sid=1001;

– 查询学生成绩大于60的学生id,姓名,成绩

select sid,sname,score from stu where score>60
order by score desc;

– 查询学生性别不是男生的学生信息

select * from stu where sex!='男';

– 查询学生性别为女，并且年龄小于50的记录

select * from stu where sex='女' and age<50;

– 查询学生学号为1001，或者姓名为李四的记录

select * from stu where sname='李四' or sid=1001;

– 查询学号为1001，1002，1003的记录

select * from stu where sid=1001 or sid=1002 or sid=1003;
select * from stu where sid in(1001,1002,1003);

– 查询学号不是1001,1002,1003的记录

select * from stu where sid!=1001 and sid!=1002 and  sid!=1003;
select * from stu where sid not in(1001,1002,1003);

– 查询学生年龄在20到40之间的学生记录

select * from stu where age>=20 and age<=40;
select * from stu where age between 20 and 40;

– 笛卡尔乘积表

select * from stu,class;

模糊查询 where like 字段 ‘%值_’
%匹配任意个数的字符
%张 ==>以张结尾,前面任意
张% ==>以张开头,后面任意
%三% 只要内容包含三即可
_匹配一个任意字符
*/
– 查询姓名以“张”开头的学生记录

select * from stu where sname like '张%';

查询姓名以“张”结尾的学生记录

select * from stu where sname like '%张';

_和%区别在%不限制个数,_限制,一个_代表一个字

select * from stu where sname like '_张';    #只能输某张
select * from stu where sname like '张_';     #只能输张某

– 查询姓名中包含“三”的学生记录

select * from stu where sname like '%三%'

排序查询基础查询后使用order by 字段排序类型;
排序类型: 升序 asc ,降序 desc
默认是升序可以不写
ps:没有必须放在条件查询后
*/
– 查询所有学生记录，按年龄升序排序

select * from stu order by age asc;

– 查询所有学生记录，按年龄降序排序

select * from stu order by age desc;

– 查询所有学生记录，按年龄升序排序,如果年龄相同时，按编号降序排序

select * from stu order by age asc ,sid desc;

– 查询成绩大于60成绩学生id,姓名,成绩,并降序

select sid,sname,score from stu where score>60 order by score desc;

聚合函数:计算多行数据,返回一个值
count:计算指定列非空(不是null,但是空字符串,不是null,也计数)行数的数量
sum:计算指定列数值数据的和 (不计算null值,略过)
如果有字符串参数求和,结果为零
max:返回指定列最大值
min:返回指定列最小值
按照数字大小返回,如果是字符串按照字符顺序
avg:计算指定列平均值
如果有值为null不参与计算
所有聚合函数不能出现在where后
select 聚合函数 from 表名[条件][排序];
– 查询stu表中记录数：

select count(sid) from stu;

– 查询stu表中有成绩的人数：

select count(score) from stu;
select count(sex) from stu;
update stu set sex=null where sid=1009;
select count(sex) from stu;

– 查询stu表中成绩大于60的人数：

select count(*) from stu where score>60

– 查询所有学生成绩和：

select sum(score) from stu;
select sum(sname) from stu;

– 统计所有学生平均成绩

select avg(score) from stu;

– 查询最高成绩和最低成绩：

select max(score) ,min(score) from stu;
select max(sname) as 'd',min(sname) as 'm' from stu;

给查询的列取别名:字段后使用as ‘别名’,as可以省略
给查询的表取别名:表名后使用as ‘别名’,as可以省略
别名可以在条件中使用
– 查询学生编号为1001的信息 [表名取别名,条件中使用别名]

select * from stu s where s.sid = 1001;

– 查询学生成绩和姓名,并且根据成绩降序

select sname,score s from stu order by s desc;

去重查询:distinct(列)
– 查询年龄不重复的人的信息

select distinct(age),sid,sname from stu;

– 一般不会单独使用会配合count使用
– 查询年龄不重复的共有多少人

select count(distinct(age)) from stu;

分组查询 :使用group by 字段 ,对数据进行分组
特点:
1.与聚合函数一起出现在select后的字段,要出现在group by之后
2.分几组返回的虚拟表数据就有几行
3.一般情况下分组只会和聚合一起使用,因为查询其他字段无意义

select * from stu[条件] [group by] [排序]
– 查询男生多少人,女生多少人

select sex ,count(*) from stu group by sex;
select sex,count(*) from stu where sex in('男','女') group by sex;

– 查询每个班级的班级编号和每个班级的成绩和：

select cid,sum(score) from stu group by cid;

– 查询每个班级的班级编号以及每个班级的人数：

select cid,count(*) from stu group by cid;

– 查询成绩总和大于200的班级编号以及成绩和：

/*
分组前过滤 用where,注意:where后不能用于聚合函数
分组后过滤 用having,与where类似,用于条件过滤
		having age>20
		having 后可以使用聚合函数
*/

select cid ,sum(score) s  from stu group by cid having s>200;
 -- 	查询成绩总和大于200的班级编号以及成绩和并根据成绩总和降序
select cid,sum(score) s  from stu group by cid 
having s>200 order by s desc;

分页(limit)查询:限制查询/限制输出===>分页
特点:
1)limit 放在查询语句最后
格式:
limit 起始下标,条数; 数据下标从零开始
作用:只会让查询到的数据从起始下标开始,输出指定条数

select * from stu limit 2;
select * from stu limit 5;
select * from stu limit 3,5;
-- 查询出学生第五条至第六条的数据
select * from stu 4,2;
-- 查询成绩大于60的学生信息,降序,前两条
select * from stu where score>60 order by score desc limit 0,2;

每页的条数 pageSize :已知
当前页 pageNo :已知
总条数 total :count计数统计
总页数 pageCount : (total%pageSize==0)?total/pageSize:total/pageSize+1

-- 已知有10条 , 每页展示3条数据
-- 第一页
select * from stu limit 0,3;
-- 第二页
select * from stu limt pageSize*(pageNo-1),3;
select * from stu limt 3*2,3;
select * from stu limt 3*3,3;

流程控制函数
case
when 条件1 then 执行语句1,
when 条件2 then 执行语句2,
when 条件3 then 执行语句3
几个when语句,与Java中的else if 类似,满足条件就执行when语句,
不满足条件,执行下个when语句
一旦执行某个when语句,case结构结束,不再继续执行
如果都不满足执行else后的语句
[case when then end]整体当做一个字段出现再select后,可以取别名
– 输出学生id,姓名,成绩,及成绩等级(0-59差,60-79中,80-90良,90-100优)

select sid '学号',sname '姓名',score '成绩',
case 
	when score<60 then '差' 
	when score<80 then '中'
	when score<90 then '良'
	else '优'
end as '成绩等级' from stu  order by score desc;

– 查询没成绩,显示缺考
– isnull()函数,判断是否为空
– ifnull(字段,’’)如果为null返回’’,不为空返回字段

select sid,sname,if(isnull(score)=0,score,'缺考') '成绩' from stu;

select sid ,sname , ifnull(score,'缺考') '成绩' from stu;

多表查询

/*
竖向拼接(联合查询): UNION union 
	union : 将两表数据联合输出,去除重复数据
	union all : 将两表数据联合输出,不去重
	------------------
使用注意事项:
1) 要拼接的两虚拟表的字段数量要一致
2) 要拼接的两虚拟表字段的数据类型要一致
*/
select *from a union select * from b;
select *from a union all select * from b;
select a.aid,a.aname from a union all  select b.bid,b.bname from b ; 
/*
内连接
select 字段1,字段2,..... from 表1 innner join 表2  on 表1.字段=表2.字段
说明: on 后的字段是 两表的关联关系字段
特点:
    1)将两表数据横向拼接在一张虚拟表中
    2)只会保留符合on后关联关系的数据,其他保留
		3)如果没有on后的条件,会出现笛卡尔积,两表相乘,即会出现一些不符合需求的数据
    所以,一定要有on后的条件,过滤掉其他数据
*/
select * from stu inner  join  class;
select * from stu inner  join  class on  stu.cid=class.cid;
-- 简写
select * from stu,class where stu.cid=class.cid;
-- 使用别名
select * from stu s,class c where s.cid=c.cid;
-- 查询学生id ,姓名,班级名称和地址
select s.sid,s.sname,c.cname,c.caddress from stu s, class c where s.cid=c.cid;
-- 查询成绩大于60的学生id,姓名,成绩,班级名称.地址,并且按照班级编号排序
select s.sid,s.sname,s.score,c.cname,c.caddress from stu s, class c 
where s.cid=c.cid and s.score>60 order by c.cid;
-- 查询学生编号为1007的学生名称、学生成绩、班级名称、班级地址
select s.sname,s.score,c.cname,c.caddress from stu s, class c
 where s.cid=c.cid and s.sid=1007;
/*
内连接是只保留符合 on 后关联条件的数据,但是这样会损失一些数据
比如 class表的[5 UI 106] 
外连接就可以实现 保留某些不符合on后关联条件的数据
外连接:
	左外连接 :
     select * from 表1 left outer join 表2 on 表1.字段=表2.字段;
关键词左侧表[表1]中如果有数据不满足on后的条件,也可以保留
	右外连接:
     select * from 表1 right outer join 表2 on 表1.字段=表2.字段;
关键词右侧表[表2]中如果有数据不满足on后的条件,也可以保留
*/
-- 左外
select * from stu left outer join class on stu.cid=class.cid;
-- 右外
select * from stu right outer join class on stu.cid=class.cid;
-- 简写:可以省略outer
select * from stu left join class on stu.cid=class.cid;
-- 查询学生id ,姓名,班级名称和地址
select * from stu s left join class c on s.cid=c.cid; -- 学生没有对应班级保留
select * from stu s right join class c on s.cid=c.cid;   -- 班级没有学生也保留
-- 查询成绩大于60的学生id,姓名,成绩,班级名称.地址,并且按照班级编号排序
select s.sid,s.sname,s.score,c.cname,c.caddress from stu s
right  join class c on s.cid = c.cid and score >60 order by c.cid;
select s.sid,s.sname,s.score,c.cname,c.caddress from stu s
left  join class c on s.cid = c.cid where score >60 order by c.cid;

/*
子查询-嵌套查询
将一个sql语句运行结果当做一张表 作为一个新的表去使用 或者当做条件使用
->查询出虚拟表可以放在from后面当表用
->查询出的虚拟表可以放在where后面当条件用
*/
-- 查男生成绩大于60的id,姓名,成绩
select * from stu where sex='男' and score>60;
-- 通过子查询
-- 1)先选出男生
select * from stu where sex='男';
-- 2)从上述表中查询出成绩大于>80的人的信息(放在from后使用)
select * from (select sid,sname,score from stu where sex='男') a where a.score>80;
-- 选出编号为1005的学生信息(放在where后使用,子查询只能返回一个值,在in中可以返回多个值)
select * from stu where sid=(select sid from stu where sname='周七');

-- 	查询与张三同一个班级的学生。
select  *  from  stu where  stu.cid=(select cid from stu where  sname='张三');
-- 	成绩高于3号班级所有人的学生信息
select * from stu where stu.score>(select max(score) from stu where cid=3);
-- 	有2个以上直接组员的学生信息
select groupLeaderId from stu group by groupLeaderId ;
select groupLeaderId from stu group by groupLeaderId having count(*)>2 ;
select * from stu where sid in(1007,1010);
select * from stu where sid in(select groupLeaderId from stu group by groupLeaderId having count(*)>2)

-- 	求1008学生编号、姓名、组长编号和组长姓名
select  groupLeaderId from stu where  sid=1008;
select * from stu where sid = (select  groupLeaderId from stu where  sid=1008) or sid=1008;
select groupLeaderId from (select * from stu where sid=1008) g;

select s.sid ,s.sname,g.sid,g.sname from stu s 
right join (select * from stu where sid = (select  groupLeaderId from stu where  sid=1008) ) g on s.sid = 1008;

select s.sid ,s.sname,g.sid,g.sname from stu s, 
(select * from stu where sid = (select  groupLeaderId from stu where  sid=1008)) g where s.sid=1008

-- 	查询每个学生成绩大于60且成绩总和小于200的班级编号以及成绩和并根据成绩和降序
select cid, sum(score) from stu group by cid  
having sum(score)<200 and min(score)>60 order by sum(score) desc;