DML数据操作语句和基本的DQL语句

Mysql对数据的增删改

#DML

--   新增

-- insert into 表名（字段名，字段名...)

-- values/value (值，值...)

-- 日期 使用字符串的形式书写日期格式（yyyy-MM-dd HH:mm:ss）

-- 全字段插入

-- 方法1

insert into student (sid,sname,birthday,ssex,classid)

values(9,'张三','2006-1-1','男',1) ;

-- 方法2

insert into student values(null,'小张','2001-1-1','女',2);-- 不能为空，给空存入默认值

insert into student values(default,'小张','2001-1-1','女',2);-- 给默认值

-- 部分字段插入

insert into student(sname,ssex) values('张同学','女');

-- 设置性别非空，默认值：未知

alter table student modify ssex varchar(10) not null default '未知';

insert into student(sname) values('陈同学');

insert into student(sname,ssex) values('张同学','女');

-- 一次性添加多条数据

-- **方法1

-- insert into 表名（字段名...） values (值...),(值...)...

insert into student (sname,ssex)

values ('小红','男'),('小绿','男'),('小黄','女');

-- 方式2(不常用IO高)

-- insert into select
-- 插入和被删除的表都必须存在

create table newstu(

       xingming varchar(10),

       xingbie varchar(10),

       classid int

);


insert into newstu(xingming,xingbie,classid)

select sname,ssex,classid from student;



-- 方法3(不常用IO高)

-- create table select

-- 被插入表不能存在 被插入表没有约束（约束不能带走）

create table stu1

select sid,sname,birthday from student;

-- 修改

-- update 表名 set 字段名=值，字段名=值

-- 【where 子句条件】

-- where 子句 中的条件是对表中每一条数据进行判断，后不能跟聚合函数

-- 判断成立执行父句

update stu1 set birthday='1678-2-2'

where sname='小红';



update newstu set classid=200

where xingbie !='男'; --  null 不是类型不能被改

-- 不等于 1.！=  2.<>

update newstu set xingbie='保密'

where classid<190;



update newstu set xingbie='外星人'

where classid>=10 and classid<90;



update newstu set xingbie='地球人'

where classid=60 or classid=70 or classid=80 ;-- 改变值于原值相同，不改降低IO省资源



update newstu set xingbie='火星人'

where classid>=10 and classid=<50;



update newstu set xingbie='水星人'

where classid between 10 and 50;  -- 同上10-50闭区间

-- 删除

-- delete from 表名 【where 子句】

delete from newstu

where xingbie='水星人';

-- 清空表,截断表

-- truncate 表名 (效果同不写where的delete)

-- delete 只删 数据（抹掉数据，空间还在）

-- truncate 删（数据，索引）（阶段，只留下表结构）

-- drop 全删（数据，索引，表结构）

truncate stu1;

DQL语言

        DQL(Data Query Language 数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多表联查。

查询语句语法规则

SELECT [DISTINCT]

{*|表1.*|[ 表1.字段1 [as 字段别名1]

[, 表1.字段2[as 字段别名2]][, …]]}

FROM 表1 [ as 表别名 ]

[ left|right|inner join 表2 on 表之间的关系 ]

[ WHERE ]

[ GROUP BY ]

[ HAVING]

[ ORDER BY]

[ LIMIT {[ 位置偏移量,] 行数 }] ;

DISTINCT：设定DISTINCT可以去掉重复记录。

AS：表名或者字段名过长时，可以用AS关键字起别名，方便操作。

GROUP BY：按组分类显示查询出的数据。

HAVING：GROUP BY分组时依赖的分组条件。

ORDER BY：将查询出来的结果集按照一定顺序排序完成。

LIMIT：限制显示查询结果的条数。

#DQL

-- 所有查询都会得到一份虚拟表 （在内存） 且永远不会发生变化

select 123;

-- 从表中查询数据

-- select 字段名，字段名 from 表名

-- 全字段查询

select sid,sname,birthday,ssex,classid from student;

select * from student; -- 效果同上

-- 部分字段查询

select sname,ssex from student;

-- 字段名取别名(3种写法)

select sname as '姓名',birthday '生日',ssex 性别 from student;

-- 添加字段

select sname,'xx' 学校 from student;

-- 去重(可以看数据种类,可联合去重)

-- distinct

select distinct ssex from student;

-- 带条件查询

-- 【where 子句】

select * from student where sid=5;

select * from student where sid<>5;

select * from student where classid=1 and ssex='女';

select * from student where birthday<'1990-1-1';

-- in 在某个特定范围

select * from student where sid=3 or sid=5; -- 不推荐，or会使索引失效

select * from student where sid in(3,5); -- 推荐

-- like 模糊查询

-- 模糊符号

-- % 任意多任意字符

-- _  一位任意符

insert into student(sname)

values('大红'),('小红'),('红红');

select * from student where sname like '%红%';

select * from student where sname like '红_';

-- null

null 查null

-- is

is 是一个什么 is null,is not null

select * from student where birthday is null;

-- 聚合函数

-- 把多个值变为一个值

-- count() 统计个数 除null外所有类型

-- select count(字段\常量\*) from student ;

select count(sid) from student; -- 主键

select count(classid) from student;-- 不统计null



select count(*) from student;

select count(1) from student;

select count('a') from student; -- 不推荐

-- sun avg min max 计算数值类型（含日期）

select sum(score) from sc;

select avg(score) from sc;

select max(score) from sc;

select min(score) from sc;



select count(*),sum(score),avg(score),max(score),min(score)   from sc;

-- 分组 group by

-- 统计男女同学各多少人

select ssex,count(1) from student group By ssex;

-- 统计各班有多少人

select classid,count(1) from student group by classid;

-- 统计每个同学的总分和平均分

select sid,sum(score),avg(score) from sc group by sid;

-- 上一步结果上 统计平均分不及格学生

-- having 筛选分组聚合后的数据 类似where

select sid,sum(score),avg(score) from sc

group by sid

having avg(score)<60;



select sid,sum(score),avg(score) from sc

where score<60 -- 已经过滤

group by sid

having avg(score)<60;

-- order by 排序 默认按主键升序

-- 升序 asc 默认

-- 降序 desc

select * from student order by classid desc;

select * from sc order by score desc,Cid asc;



-- limit 分页 默认0开始

-- select * from student limit 位置，步长

-- 位置（（页码-1）*步长））不能用 应用层解决

select * from student limit 0,3;



-- 找到成绩及格的总分数排名第二的sid

select 

sid,sum(score),avg(score) from sc

group by sid

having avg(score)>60

order by sum(score) desc

limit 1,1;