3.语句的增删改查

一、语法

having子句：有group by才能having子句，只有满足“条件表达式”中指定的条件的才能够输出。

group by子句通常和count()、sum()等聚合函数一起使用。

order by子句：按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出，默认是按照“asc”来排序，即升序。



模糊查询:where like %%;
查询空值:where is null;
数据去重:distinct
与:and
或:or

分组:group by
SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id

MYSQL中可以在GROUP BY中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来


SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id
查询总条数:select count(*) from 表名;


select 字段名1,字段名2或[*] from  表名;

--插入数据(有id序列)
insert into 表名(字段名1,字段名2,..) values(值1,值2,...);

--修改数据
update 表名 set 列名=新值 where 字段名=字段值;

--删除数据
delete from 表名 where 字段名=字段值;

二、具体操作

create table students(
    id int unsigned not null auto_increment primary key,
    name varchar(20) not null,
    pwd varchar(20) not null,
    age int unsigned default 0,
   sex char(4) default '女'
 );

--插入语句
--INSERT INTO 表名(列名1,列名2,列名3,..) values(值1,值2,值3,..);

insert into students(name,pwd,age) values('holly','123',18);
insert into students(name,pwd,age) values('巴永华','123',18);

--修改
UPDATE 表名 SET 列名='新值' where 列名=值 [and 列名=值 ...]
update students set pwd='123456' where id=1;

--创建teacher表
create table teacher(
    id int unsigned not null primary key,
    name varchar(20) not null,
    pwd varchar(20) not null,
    classid int(10) not null
 );

--插入数据
insert into teacher(id,name,pwd,classid) values(1,'周波徐','123',1);
insert into teacher(id,name,pwd,classid) values(2,'张浩','123',1);
insert into teacher(id,name,pwd,classid) values(3,'黄东东','123',2);
insert into teacher(id,name,pwd,classid) values(4,'唐龙','123',2);

--条件查询
--SELECT * FROM 表名 WHERE 列名=值;
select * from teacher where id=1;


--查询所有
--SELECT * FROM 表名
select * from teacher;

select * from teacher where classid=1;
select * from teacher;

update teacher set pwd='123456' where id=3;
select * from teacher;

delete from teacher where id=4;
select * from teacher;

insert into teacher(id,name,pwd,classid) values(4,'张冉','123',2);
insert into teacher(id,name,pwd,classid) values(5,'徐光东','123',2);

--使用聚合函数统计条数
select count(*) from teacher;
select count(1) from teacher;

--1.根据编号分组查询
select classid from teacher group by classid;

--2.先根据classid分组,分组后查询classid=2 (分组后的条件写在having后)
select classid from teacher group by classid having classid=2;

--3.按照id降序查询
select id,name from teacher order by id desc;

--4.按照id升序(默认升序)
select id,name from teacher order by id asc;

--5.条件降序
select name,id from teacher where id<4 order by id desc;

--6.模糊查询,查以"张"开头(右模糊)
select * from teacher where name like '张%';


--7.模糊查询,查以"东"结尾(左模糊)
select * from teacher where name like '%东';

--8.模糊查询,查询包含"光"关键字的(左右模糊==全模糊)
select * from teacher where name like '%光%';

--8.1 去重查询
select distinct id,name from teacher;

--9.创建班级表
create table classes
(
  id int unsigned not null auto_increment primary key,
  name varchar(20) not null
);

--10.给班级表插入数据
insert into classes(name) values('TB13');
insert into classes(name) values('TB24');

--11.查询classes数据
select * from classes;

--12.teacher和classes联查
  select c.id,c.name,t.id,t.name,t.pwd,t.classid
  from teacher t,classes c
  where t.classid=c.id;

select c.id,c.name,t.id,t.name,t.pwd,t.classid
 from classes c,teacher t
 where t.classid=c.id;

select *
 from teacher t,classes c
 where t.classid=c.id;

--给列添加别名
select c.id cid,c.name cname,t.id tid,t.name tname,t.pwd
 from classes c,teacher t
 where t.classid=c.id;

select c.id as cid,c.name as cname,t.id as tid,t.name as tname,t.pwd
 from classes c,teacher t
 where c.id=t.classid;

--函数
--求最大值
select max(id) from teacher;
select max(id) as 'id最大值'from teacher;
select max(id) 'id最大值'from teacher;

--最小值
select min(id) from teacher;
select min(id) as 'id最小值'from teacher;
select min(id) 'id最小值'from teacher;

--平均值
select avg(id) from teacher;
select avg(id) as 'id平均值'from teacher;
select avg(id) 'id平均值'from teacher;

--去重空格
select trim(' 张冉 ');

--获取系统时间
select now();

--将日期和时间分开查询
SELECT UTC_DATE(),UTC_TIME();