MYSQL语法
- DDL 数据定义语言 : 对库,表的操作 ,主要是
create , alter , drop
等 - DML 数据操作语言 :主要是对表中数据的作,
insert,delete,update
- DQL 数据查询语言 : 主要是对表中数据的查询,
select
- DCL 数据控制语言 : 主要是权限控制 ,
grant
等
数据定义DDL
注释
--注释
# 注释
/*
注释
*/
-- 查询所有库
show databases;
-- 使用某个库
use java28;
-- 查看所有表
show tables;
创建数据库
# create database 库名;
create database java2802;
修改编码格式
alter database java28 character set utf8;
查询数据库编码格式
show variables like 'character%';
删除数据库
drop database java2802;
创建表
格式:
create table 表名(
字段1 数据类型(长度) [约束],
字段2 数据类型(长度) [约束],
...
);
# 创建学生表,存储学号 姓名 年龄 生日 分数
create table stu(
sid int(11) ,
sname varchar(10),
sage int , # int可以不给长度,默认11
sbirthday date, # date 不需要给长度
score double(5,2) # 总长度5,小数点后类位
);
数据类型:
int
整型 默认长度11char
字符串varchar
字符串,可变化的字符串,date
日期datetime
日期时间timestamp
时间戳float
浮点型double
浮点型 -->double(长度,小数点个数)
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 sage varchar(3);
alter table stu change sage s_age varchar(3);
改表名
# rename table 旧表名 to 新表名;
rename table stu to student;
删除表
# drop table 表名;
drop table stu;
查看表结构/查看建表语句
# show create table 表名;
# 选中表 --> 选中对象 --> 查看ddl
show create table stu;
添加列
# 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 sage varchar(3);
改表名
# rename table 旧表名 to 新表名;
rename table stu to student;
删除表
# drop table 表名;
drop table stu;
查看表结构/查看建表语句
# show create table 表名;
# 选中表 --> 选中对象 --> 查看ddl
show create table stu;
DML 数据操作 -对表中数据的增删改
增
插入 - 增加数据
insert
into
表名 (字段1,字段2,…) values
(值1,值2,…);
- 插入字段个数任意
- 插入字段顺序任意
- 插入的数字直接写数字即可;
字符串\日期需要用单引号’'括住,日期格式为yyyy-MM-dd - 插入空值时,传入null
- 插入全表数据,还可以省略写字段,默认插入全表数据
insert into 表名 values (值1,值2,...);
顺序与表的一致
# 插入 学生 学号,姓名,年龄
insert into stu (sid,sname,sage) values (1,'亮仔',20);
insert into stu (sname,sid,sage) values ('亮',2,20);
insert into stu (sid,sname,sage,score) values (3,'亮仔',20,null);
insert into stu (sid,sname,sage,sbirthday,score) values (4,'亮仔',20,'2000-01-01',100);
insert into stu values (5,'亮仔',20,'2000-01-01',100);
改
修改 - 更新数据
update 表名 set 字段1=值1,字段2=值2 [where 字段=值];
注意 :
不加后面的where过滤条件,为更新全表
加后面的过滤条件,只更新符合条件的
如果where后的过滤条件找不到,就不更新
# 将学号为1的年龄改为18
# 将学号为4的姓名改为张飞,生日改为2020-01-01
update stu set sage=18 where sid=1;
update stu set sname='张飞',sbirthday='2020-01-01',sage=0 where sid=4;
删
删除
delete from 表名 [where 字段=值];
注意:不加where条件,会清空表,慎用!
# 删除学号为4的
delete from stu where sid=4;
# 删除年龄为20的
delete from stu where sage=20;
# 清空表
delete from stu;
约束
约束该列数据存储时的限制
在创建表时指定该列的限制条件 , 后续也可以改变
主键
主键(primary key
) :
设置主键的列,不能为空,且唯一
一般是给id列设置主键
联合主键 :
两或多个主键
需要所有主键完全一致 , 才是重复
只要有一个不同 , 就不是重复
# 主键设置方法一 :
create table s1(
id int primary key,
name varchar(10)
);
# 主键不能为空,插入时,必须给定主键值
# insert into s1(name) values('张三'); # 报错
insert into s1(id,name) values(1,'张三');
# 主键不可重复
# insert into s1(id,name) values(1,'李四'); #报错
# 主键设置方式二 :
create table s2(
id int ,
primary key(id),
name varchar(10) ,
birthday date
);
# 主键设置方式三 : 设计表时,添加主键
#设计联合主键
create table s3(
id int ,
name varchar(10) ,
primary key(id,name),
# id name 是联合主键
birthday date
);
自增
自增(auto_increment
) :
- 列值自动增长,一般配合主键使用
- 当主键设置了自增,在插入时就可以不指定主键值,使其自增,每次自增1,默认值为0
- 设置自增后,执行插入语句,无论插入数据是否成功,自增都会执行一次
- 当删除最后一行数据后,再次使用自增,数据仍会以被删除的数据进行增加
- 当设置自增的数据后,会以设置的数据向后增加
- 可以在图形界面进行设置
create table s4(
id int primary key auto_increment ,
name varchar(10)
);
insert into s4(name) values('张三'); # 执行三次
delete from s4 where id=3;
insert into s4(name) values('张三');
外键 foreign key
外键对多表的联合查询没有影响, 以前如何使用 内连接,外连接,子查询 等都可以继续使用;但是对数据的增删改都会有一些影响。
选项参数:
- CASCADE,级联删除/级联更新,即主表delete或update了被其他表引用的数据,对应子表的数据也被delte或update;
- SET NULL,当主表delete或update了被其他表引用的数据,对应子表的数据被设为null,注意子表的外键不能设为not null;
- RESTRICT,主表不允许delete或update被其他表引用的数据;当没有指定任何[ON DELETE reference_option]和[ON UPDATE reference_option],默认是采用RESTRICT;
- NO ACTION,在MySQL中,等效于RESTRICT;
具体可以看 —— MySQL的外键约束FOREIGN KEY
唯一
唯一(unique
) : 让该列数据唯一,不可重复
图形界面未找到设置方法
create table s5(
id int primary key auto_increment ,
name varchar(10) unique
);
insert into s5(name) values('zs');
insert into s5(name) values('zs'); # 报错
insert into s5(name) values('z1');
不为空
不为空(not null
) : 插入时该列的值不可为空
可在图形界面进行设置
create table s6(
id int primary key auto_increment ,
name varchar(10) unique ,
age int not null
);
# insert into s6(name) values('张三'); # 报错,age为空
insert into s6(name,age) values('张三',18);
默认值
默认值 default
: 在插入数据时,若没有指定该列值,就会填充默认值
可在图形界面进行设置
create table s7(
id int primary key auto_increment ,
name varchar(10) unique ,
age int not null default 0 # 当不设置age值时,默认为0
);
insert into s7(name) values('张三');
注释/备注
注释(comment
) : 在建表时给列指定注释,若有注释放在约束后面
create table s8(
id int primary key auto_increment comment '主键id' ,
name varchar(10) unique comment '姓名' ,
age int not null default 0 comment '年龄' # 当不设置age值时,默认为0
);
……
DQL ----- 查询
stu表
class表
基本查询
查询(select
) : 返回从表中查询到的数据,返回的是一张虚拟表
基本查询:
select 字段1,字段2,... from 表名;
1) 字段顺序与原表无关
2) 字段个数根据需求而定
3) 返回的虚拟表顺序与原表无关,与select后的字段顺序有关
# 查询所有列 , 可以将字段简写为 " * ",代表所有
select sid sname,age,gender,score,cid,groupLeaderId from stu;
select * from stu;
# 查询指定列
# 查询学生 学号和性别
select sid,gender from stu;
# 查询学生 学号和成绩
select sid,score from stu;
条件查询
条件查询 : 返回符合条件的数据
条件查询需要在基础查询语句后 使用 where 字段 运算符 值
条件查询的关系符号 :
= 、> 、< 、>= 、<= 、!=
and
: 多个条件并列,且同时满足,数据才会返回or
: 多个条件,满足一个,数据即可返回in(set)
: set是指in后集合,数据不重复;满足集合中的,数据返回not in(set)
: set是指in后集合,数据不重复;不满足集合中的,数据返回between 值1 and 值2
: 返回值1,值2之间的数据,包括值1,值2
# 查询学号为"1001"的学生信息
select * from stu where sid=1001;
# 查询学生成绩大于60的学生id 姓名 成绩
select sid,sname,score from stu where score>60;
# 查询性别非男的学生记录
select * from stu where gender!='男';
# 查询学生性别为女,并且年龄小于50的记录
select * from stu where gender='女' and age<50;
# 查询学生学号为1001,或者姓名为李四的记录
select * from stu where sid=1001 or sname='李四';
# 查询学号为1001,1002,1003的记录
select * from stu where sid in(1001,1002,1003);
select * from stu where sid between 1001 and 1003;
# 查询学号不是1001,1002,1003的记录
select * from stu where sid not in(1001,1002,1003);
# 查询学生年龄在20到40之间的学生记录
select * from stu where age between 20 and 40;
模糊查询
模糊查询(like
) : 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 '__张';
# 查询姓名中包含“三”的学生记录
select * from stu where sname like '%三%';
聚合函数
聚合函数 : 计算多行数据,返回一个值
使用聚合函数时,可以一起查询字段,但只能返回该字段的第一行,不推荐使用;但是可以与分组一起使用
所有聚合函数不能出现在where后面
count(字段)
: 计算指定列非空(不是null)行数的数量(""空字符串不是null)
count 括号内可以是语句。如sum(字段)
: 计算指定列数值数据的和;不计算null;如果有字符串参与求和,结果为0max(字段)
: 返回指定列的最大值min(字段)
:返回指定列的最小值 按照数字大小返回 如果是字符串,按照字符顺序排序avg(字段)
: 计算指定列的平均值;如果有值为null,就不参与求和与平均值计算
select 聚合函数 from 表名 [条件] [排序]
# 查询stu表中记录数:
select count(sid) from stu;
# 查询stu表中有成绩的人数:
select count(score) from stu;
select count(gender) from stu;
# 查询stu表中成绩大于60的人数:
select count(sid) from stu where score>60;
# 查询所有学生成绩和:
select sum(score) from stu;
# 统计所有学生平均成绩
select avg(score) from stu;
# 查询最高成绩和最低成绩:
select max(score) as '最大值',min(score) '最小值' from stu;
别名
- 给查询的列取别名 : 给字段后使用"
as
" 别名,as
可以不写 - 给查询的表取别名 : 给表名后使用"
as
" 别名,as
可以不写 - 别名可以在条件中使用
# 查询学生编号为一的学生
select * from stu s where s.sid = 1001;
# 查询学生成绩和姓名,并根据成绩降序
select sname '姓名',score '成绩' from stu order by '成绩' desc;
去重
去重查询 distinct(列) :
一般不单独使用 , 和count配合使用
# 查询年龄不重复的学生信息
# select distinct(age),sid,sname from stu; # 没有效果
# 查询年龄不重复的共有多少人
select count(distinct(age)) from stu;
分组查询
分组查询 : 使用 group by 字段
对数据进行分组
特点:
- 与聚合函数一起出现在select后的字段,要出现在group by后
- 分成几组,返回的虚拟表数据就有几行,每一行代表一个分组,为改分组的第一个
- 一般情况下,分组会和聚合函数一起用,因为查询其他字段无意义
分组前过滤用where;分组后过滤用having,与where类似,用于条件过滤,后面使用聚合函数
格式 : select 字段 from 表名 [条件] [group by 字段] [排序]
# 查询男生多少人,女生多少人
select * from stu group by gender;
# 以性别分组,查询每组的总人数 年龄最大值 年龄最小值
select gender,count(sid),sum(score),max(age),min(age) from stu group by gender;
# 查询stu表中每个班级的班级编号和每个班级的成绩和:
select cid,sum(score) from stu group by cid;
# 查询stu表中每个班级的班级编号以及每个班级的人数:
select cid,count(sid) from stu group by cid;
# 查询stu表中成绩总和大于200的班级编号以及成绩和:
select cid,sum(score) sum from stu group by cid having sum>200;
# 查询stu表中成绩总和大于200的班级编号以及成绩和并根据成绩总和降序
select cid,sum(score) sum from stu group by cid having sum>200 order by sum desc;
limit
限制查询/限制输出 ==> 分页
特点
limit
放在查询语句最后
格式
limit
起始下标,条数;数据下标从0开始,即第一条下标为0
作用
- 只会让查询到的数据 从指定下标开始,输出指定条数
# 查询stu表前两条数据
select * from stu limit 0,2;
# 查询stu表第5-6条的数据
select * from stu limit 4,2;
# 查询stu表成绩大于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 limit 3,3;
# 第三页
select * from stu limit 6,3;
# 第四页
select * from stu limit 9,3;
# 第pageNo页,每页条数为pageSize
select * from stu limit (pageNo-1)*pageSize,pageSize;
流程控制函数
case
when 条件1 then 执行语句1
...
when 条件2 then 执行语句2
when 条件3 then 执行语句3
else 执行语句4
end
- 几个
when
语句,与java中的if... else if...
类似,满足条件,执行then语句 ; 不满足条件,执行下一个when
语句 ; 一旦执行了某个then
语句,case
结构结束,不在执行 - 有
else
,如果when
都不满足,执行else
[case when then end]
整体当做一个字段,出现在select
后,可以取别名
select case when 1<0 then '大于0'
when 0<1 then '小于0'
end as 结果 from dual;
# 输出学生 id 姓名 成绩 以及学生成绩等级(0-59差 60-79中 80-90良 91-100优)
select sid 学号,sname 姓名,score 成绩,
case
when score<60 then '差'
when score<80 then '中'
when score<91 then '良'
else '优'
end 成绩等级
from stu;
IF(expr1,expr2,expr3)
如果expr1
返回值为true
,这if
返回值为expr2
;否则返回expr3
isnull(字段)
判断字段是否为null,
为null
返回1
,不为null
返回0
select if(1>0,'大','小') from dual;
# 查询学生id 姓名 成绩,如果成绩为null,显示缺考
select sid,sname,if(isnull(score)=0,score,'缺考') 成绩 from stu;
IFNULL(expr1,expr2)
如果expr1
不为null
,ifnull
返回值为expr1
;否则返回值为expr2
# 查询学生id 姓名 成绩,如果成绩为null,显示缺考
select sid,sname,ifnull(score,'缺考') 成绩 from stu;
书写顺序
select
字段/聚合函数 from
表名 [where
判断字段 group by
字段 having
判断聚合函数 order by
字段 asc/desc
limit
起始下标,长度]
执行顺序
- from 先找到表
- where 进行字段判断
- group by 进行分组
- having 进行聚合函数判断
- select 根据字段或聚合函数查询表
- limit 根据查询到的数据对输出进行限制
多表联查
竖向拼接(联合查询|合并结果集) – 连接查询的一种
竖向拼接(联合查询) :
union : 将两表数据联合输出,取除重复数据
union all : 将两表的数据全部输出,无论重复不重复
使用注意事项 :
-
要拼接的两虚拟表的字段数量要一致
-
要拼接的两虚拟表字段的数据类型要一致
select s.sid,s.sname from stu s union all select c.cid,c.cname from class c;
连接查询-横向拼接
内连接
语法:
select 字段1,字段2,… from 表1 inner join 表2 on 表1.字段 = 表2.字段;
说明 : on 后的字段是 两表的关联关系字段
特点 :
- 将两表地数据横向拼在一张虚拟表中
- 只会保留符合on后关联关系的数据,其他不保留
- 如果没有on后的条件会出现笛卡尔积,即出现一些不符合需求的数据
所以一定要有on后的条件,过滤掉其他数据
简写格式
select 字段 from 表1,表2 WHERE 表1.关联字段 = 表2.关联字段;
内连接
SELECT
*
FROM
stu
INNER JOIN class ON stu.cid = class.cid;
简化
SELECT
*
FROM
stu,
class
WHERE
stu.cid = class.cid;
使用别名
SELECT
*
FROM
stu s
INNER JOIN class c ON s.cid = c.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;
SELECT
*
FROM
stu s
INNER JOIN class c ON s.cid = c.cid
WHERE
s.score > 60
ORDER BY
c.cid;
外连接
内连接只会保存符合on后关联条件的数据,但是会损失一些数据
如,内连接stu表和class表时,stu表中sid为1011的数据,以及class表中cid为5的数据
但是,外连接会保留一些不符合条件的数据
左外连接:
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 s
RIGHT OUTER JOIN class c ON s.cid = c.cid;
SELECT
*
FROM
stu s
RIGHT JOIN class c ON s.cid = c.cid;
左外连接
SELECT
*
FROM
stu s
LEFT OUTER JOIN class c ON s.cid = c.cid;
SELECT
*
FROM
stu s
LEFT JOIN class c ON s.cid = c.cid;
查询学生id,姓名,班级名称,地址,如果对应学生,没有班级的学生信息保留
SELECT
s.sid,
s.sname,
IFNULL(c.cname, "没有"),
IFNULL(c.caddress, "没有")
FROM
stu s
LEFT JOIN class c ON s.cid = c.cid;
查询成绩大于60的学生id,姓名,成绩,班级名称,地址 ,并按照班级编号排序 , 没有班级的学生信息保留
SELECT
s.sid,
s.sname,
s.score,
c.cname,
c.caddress
FROM
stu s
LEFT JOIN class c ON s.cid = c.cid
WHERE
s.score > 60
ORDER BY
c.cid;
子查询 - 嵌套查询
将一个select语句结果作为一个新的表或者作为一个条件使用,
即 查询出的虚拟表可以放在from后作为被查询的表 也可以放在where后作为判断条件
注意 : 当子查询作为条件的时候 ,需要注意where后条件判断的值得个数是否与判断符合是否匹配。如,“=”
两边的数据个数都是一个;“in”
括号里面可以有多个数据。
查询男生成绩大于80的id,姓名,成绩
SELECT
sid,
sname,
score
FROM
stu
WHERE
score > 80
AND gender = "男";
使用子查询
SELECT
*
FROM
(
SELECT
sid,
sname,
score
FROM
stu
WHERE
gender = "男"
) a
WHERE
a.score > 80;
查询Java班级的学生信息
SELECT
*
FROM
stu
WHERE
cid = (
SELECT
cid
FROM
class
WHERE
cname = "Java"
);
查询与张三同一个班级的学生。
SELECT
*
FROM
stu
WHERE
cid = (
SELECT
cid
FROM
stu
WHERE
sname = "张三"
);
成绩高于3号班级所有人的学生信息
SELECT
*
FROM
stu
WHERE
score > (
SELECT
MAX(score)
FROM
stu
WHERE
cid = 3
);
有2个以上直接组员的学生信息
SELECT
s.*
FROM
stu s,
(
SELECT
groupLeaderId,
COUNT(groupLeaderId) number
FROM
stu
GROUP BY
groupLeaderId
) g
WHERE
s.sid = g.groupLeaderId
AND g.number > 2;
SELECT
*
FROM
stu
WHERE
sid IN (
SELECT
groupLeaderId number
FROM
stu
GROUP BY
groupLeaderId
HAVING
COUNT(groupLeaderId) > 2
);
求1008学生编号、姓名、组长编号和组长姓名
SELECT
s.sid,
s.sname,
g.sid,
g.sname
FROM
stu s,
(
SELECT
*
FROM
stu
WHERE
sid IN (
SELECT
groupLeaderId
FROM
stu
GROUP BY
groupLeaderId
)
) g
WHERE
s.groupLeaderId = g.sid
AND s.sid = 1008;
查询每个学生成绩大于40且成绩总和小于300的班级编号以及成绩和并根据成绩和降序
(没有用子查询)
SELECT
cid,
SUM(score)
FROM
stu
GROUP BY
cid
HAVING
MIN(score) > 40
AND SUM(score) < 300
ORDER BY
SUM(score) DESC;
DCL —— 数据控制语言
DCL(Data Control Language),即数据控制语言,用来定义数据库的访问权限和安全级别,及创建用户。
主要包括创建用户、给用户授权、对用户撤销授权、查询用户授权和删除用户等。