Mysql的DDL:对表的操作
DDL语句建库建表
SQL(脚本)语法不区分大小写
SQL语句结束后要加分号‘ ;’
写完的语句要及时保存;
SQL的错误提示不准确;
DDL操作数据库:
caeate database 数据库名字 //创建数据库;
drop database 数据库名字 //删除数据库;
show databases //查看所有数据库;
use 数r据库名字 //选择数据库
use 库名字(要操作使用某个库时,首先要选择)
DDL操作表
新建表:
create table 表名 (
‘字段名1’ 字段类型[属性] [索引] [注释],
‘字段名2’ 字段类型[属性] [索引] [注释],
‘字段名n’ 字段类型[属性] [索引] [注释]
)
注释格式 comment “注释内容“
表的内容没有结束要用英文逗号隔开','结束时不用加符号
查看表结构:
- describe 表名 //describe 可简写为desc
- show create table 表名 //显示创建的表
修改表名:
alter table 表名 rename as 新表名
例;
alter table class rename as class1;
删除表
drop table 表名;
例:drop table class1;
修改字段属性:
alter table 表名 modify 字段名 字段属性 //只修改字段属性,例:alter table class modify classid int(20);
修改字段名和属性:
alter table 表名 change 旧字段名 新字段名 属性 //可同时修改字段名和字段属性,
例:alter table class change classname1 classname varchar(100);
新增字段:
alter table 表名 add 字段 属性,
例:alter table class add gz decimal(20);
删除字段。
alter table 表名 drop 字段;
例:alter table class1 drop gz;
MySql 的DML:对字段的操作
对表字段的增删改
新增
日期要以字符串的形式写入
insert into 表名(字段)values(参数)
insert into student(sid,sname,birthday,ssex,classid) values(1,'张三','1998-06-17','男',1);
insert into student values(2,'李四','1999-07-21','男',2);
insert into student(sname,ssex,classid) values('小芳','女',1);
主键是自增的话,全字段的两种写法。
insert into student values(default,'李四','1999-07-21','男',2);
insert into student values(null,'李四','1999-07-21','男',2); //主键参数不能为null,所以自增;
一次添加多条数据,用逗号隔开
insert into student(sname,ssex) values('张哥哥','男'),('张妹妹','女'),('张弟弟','男');
不常用的新增方式
1,拷表 insert into 要写入的表 select 字段 from 被拷贝的表
insert into stu select sname,ssex from student; //两只表都必须存在
2,新建表拷贝 create table 新表 select 字段名 from student
create table stu1 select sname,ssex from student; //新建表的时候
修改数据
updata 表名 set 字段=修改值;
where 判断 where 左边是赋值,右边是判断;
update stu1 set sname='张六' where sname='张三';
范围修改
1,update student set ssex='女' where sid>=2 and sid<=6;不能用&符号
2,update student set ssex='男' where sid between 2 and 6; between 后面要跟范围小的,and后面跟范围大的
整表删除内容
delete from 表名
条件删除内容
delete from 表名 where
delete from stu where ssex='女';
清空表数据
truncate 表名;
MySqld的DQL:对表内容的查询
单表查询
select * from 表名 //查询表中所有内容
select 字段1,字段2 from 表名; //查询表中的某字段
所有的查询结果都是虚拟表
字段起别名
1,select 字段 as '别名 '(字符串) ; //正常
2,字段 '别名 '(字符串); //简化
3,字段 别名; //简化
起了 别名之后,对于这条sql语句来说只能用别名
select * from student;
select sname, ssex from student;
select sname as '姓名',ssex as '性别' from student;
select sname '姓名',ssex '性别' from student;
select sname 姓名,ssex 性别 from student;
去重查询
select distinct 字段 from 表;
例:select distinct ssex,sname from student;
完全去重,表中数据完全一样的会被去掉,只要有一项不一样就会被保留;
带条件的查询;where句子
select * from 表 where
select * from student where sid between 1 and 7;
select * from student where birthday < '1990-1-1';-- 日期越小的年龄越大
模糊查询 like
模糊符号'%':表示任意多的任意字符;
模糊符号'_':一个任意字符;
insert into student (sname) values('真大');
insert into student (sname) values('大的很');
select * from student where sname like '大%';
select * from student where sname like '__大';
IN 在自定特定范围内查找(数字,字符串,日期)
select * from student where sid in(1,3,5,7,9,10,20,50);
对null的判断
判断是: is null;判断不是:is not null //null不参与运算;
select * from student where birthday is null;
select * from student where birthday is not null;
聚合函数
count(字段)--统计个数;
count 不统计null,统计字段时,建议统计非空字段;
count();k括号里可以填*,字段,常量(数字,字符串);
select count(*) from student ;
select count(1) from student ;
select count(birthday) from student ;
数字相关的聚合函数
avg(字段)--统计平均值
select avg(score) from sc;
sum(字段)--统计和
select sum(score) from sc;
min(字段)--统计最小值
select min(score) from sc;
max(字段)--统计最大值
select max(score) from sc;
组合统计
例:select count(*) 考试次数,avg(score) 平均分,sum(score) 总成绩,
max(score) 最高分,min(score) 最低分 from sc;
分组:
group //合并相同的,聚合不同的;
group by 字段1,字段2,--多字段分组
例:select count(sid),classid from student group by classid;
select avg(score) 平均分,sum(score) 总成绩,max(score)
最高分,min(score) 最低分,sid from sc group by sid;
having 判断
having不能单独出现,必须配合 group by;
having 分组聚合之后的判断;
写在group后面
where只能写在group前面,且不能做聚合判断;
SELECT sid,avg(score) from sc group by sid having avg(score)>80; select sid,avg(score) from sc where score<60 group by sid ;
select sid,avg(score) from sc where score<60 group by sid ;
排序 order by
多规则排序按照order by 后面的字段,先写先排,
多个排序用逗号隔开
升序asc
降序desc
select * from sc order by score desc,sid desc ;
分页 limit
(页码-1)*步长,步长;
-- 不能写入表达式,不支持运算
select * from student limit 4,2;
4:从第几页开始;
2:显示几条数据;
select avg(score) 平均成绩,sid 学号 from sc where score >60 group by sid order by sum(score) desc limit 1,1;
多表联查
-- 非等值联查
-- 笛卡尔积 字段数*字段数
select * from student ;
-- 等值联查
-- 联多个表用and
select * from student ,class where student.classid=class.classid;
select student.* from student,class ,sc,course,teacher where student.classid=class.classid
and sc.Sid=student.Sid and course.Tid=teacher.Tid and sc.Cid=course.Cid and Tname='张三';
内联数据
表1 inner join 表二 on 筛选 //内联
先得到结果,筛选符合条件的数据去拼接大表,得到一张结果集,再跟后面的内联数据拼;
一定注意内联数据的先后顺序;
select * from student inner join class on student.classid=class.classid;
外联查询
left join on 左外联;
主表在left join on 的左边;
看清楚主表
select * from student left join class on student.classid=class.classid;
select * from class right join student on class.classid=student.classid;
select * from class left join student on class.classid=student.classid;
select * from student right join class on class.classid=student.classid;
#right join on 右外联;
并集 UNION
将两张表的内容拼接 并成一个虚拟表
并的内容跟数据类型没关系
上下查询列数量必须一致
取别名只有表头的表有效
select * from student left join class on student.classid=class.classid where class.classid is null ;
union
select * from class left join student on class.classid=student.classid where student.classid is null ;
union 有去重的效果,当两个表并集有重复的内容且没有条件语句时,就会去重
若要不去重,只需在 union后面加上 all
select * from student left join class on student.classid=class.classid ;
union all
select * from class left join student on class.classid=student.classid ;
子查询
子查询必须写在括号里;
子句作为 父句的条件
where 子类作为条件判断被父使用;
from 子变为表被 父使用
select * from student where student.sid = (select max(sid) from student);
select * from student where sid in(
select max(sid) from student group by classid);
select * from student where sid not in(
select sid from sc where cid =(
select cid from course where tid=(
select Tid from teacher where teacher.Tname='张三')));
select * from student where sid not in(
select student.Sid from student
left join sc on student.Sid=sc.Sid
left join course on sc.Cid=course.Cid
LEFT join teacher on course.Tid=teacher.Tid
where tname='张三'
);
-- 每个班级人数和班级名字
select classname,人数 from class left join (
select classid,count(0) 人数 from student group by classid
)m on class.classid=m.classid;
-- 每个班的平均成绩 班级名称,平均成绩
select classname ,avg(score) from class left join
student on class.classid =student.classid
left join sc on student.sid=sc.sid
group by class.classid
select * from class left join(
select classid,avg(score)from sc left join
student on student.sid=sc.Sid group by classid)s
on class.classid=s.classid;
exists 子的结果 决定父的执行与否;
any some用 or all用and
控制SQL语句
#if(v,v1,v2)
v:逻辑表达式 boolean
v1:v的boolean为 true时输出v1
v2:v的boolean为false时输出v2;
ifnull(v,v1)
v:字段
v1:如果字段的值是一个null,v1就会显示;
select sid,sname,if(ssex='男',1,2) 性别 from student;
简单case
#case when then else end;
select sid,sname,case ssex
when '男' then 1
when '女' then 0
else '神秘'
end 性别,birthday from student;
搜索case
select sid,case
when score > 90 then 'A'
when score <90 and score>80 then 'B'
when score <80 and score>70 then 'C'
when score <70 and score>60 then 'D'
when score <60 then '不及格'
else '缺考'
end
from sc;