3.语句的增删改查

一、语法

 1 having子句：有group by才能having子句，只有满足“条件表达式”中指定的条件的才能够输出。
 2 
 3 group by子句通常和count()、sum()等聚合函数一起使用。
 4 
 5 order by子句：按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出，默认是按照“asc”来排序，即升序。
 6 
 7 
 8 
 9 模糊查询:where like %%;
10 查询空值:where is null;
11 数据去重:distinct
12 与:and
13 或:or
14 
15 分组:group by
16 SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id
17 
18 MYSQL中可以在GROUP BY中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来
19 
20 
21 SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id
22 查询总条数:select count(*) from 表名;
23 
24 
25 select 字段名1,字段名2或[*] from  表名;
26 
27 --插入数据(有id序列)
28 insert into 表名(字段名1,字段名2,..) values(值1,值2,...);
29 
30 --修改数据
31 update 表名 set 列名=新值 where 字段名=字段值;
32 
33 --删除数据
34 delete from 表名 where 字段名=字段值;

基本操作语法

二、具体操作

  1 create table students(
  2     id int unsigned not null auto_increment primary key,
  3     name varchar(20) not null,
  4     pwd varchar(20) not null,
  5     age int unsigned default 0,
  6    sex char(4) default '女'
  7  );
  8 
  9 --插入语句
 10 --INSERT INTO 表名(列名1,列名2,列名3,..) values(值1,值2,值3,..);
 11 
 12 insert into students(name,pwd,age) values('holly','123',18);
 13 insert into students(name,pwd,age) values('巴永华','123',18);
 14 
 15 --修改
 16 UPDATE 表名 SET 列名='新值' where 列名=值 [and 列名=值 ...]
 17 update students set pwd='123456' where id=1;
 18 
 19 --创建teacher表
 20 create table teacher(
 21     id int unsigned not null primary key,
 22     name varchar(20) not null,
 23     pwd varchar(20) not null,
 24     classid int(10) not null
 25  );
 26 
 27 --插入数据
 28 insert into teacher(id,name,pwd,classid) values(1,'周波徐','123',1);
 29 insert into teacher(id,name,pwd,classid) values(2,'张浩','123',1);
 30 insert into teacher(id,name,pwd,classid) values(3,'黄东东','123',2);
 31 insert into teacher(id,name,pwd,classid) values(4,'唐龙','123',2);
 32 
 33 --条件查询
 34 --SELECT * FROM 表名 WHERE 列名=值;
 35 select * from teacher where id=1;
 36 
 37 
 38 --查询所有
 39 --SELECT * FROM 表名
 40 select * from teacher;
 41 
 42 select * from teacher where classid=1;
 43 select * from teacher;
 44 
 45 update teacher set pwd='123456' where id=3;
 46 select * from teacher;
 47 
 48 delete from teacher where id=4;
 49 select * from teacher;
 50 
 51 insert into teacher(id,name,pwd,classid) values(4,'张冉','123',2);
 52 insert into teacher(id,name,pwd,classid) values(5,'徐光东','123',2);
 53 
 54 --使用聚合函数统计条数
 55 select count(*) from teacher;
 56 select count(1) from teacher;
 57 
 58 --1.根据编号分组查询
 59 select classid from teacher group by classid;
 60 
 61 --2.先根据classid分组,分组后查询classid=2 (分组后的条件写在having后)
 62 select classid from teacher group by classid having classid=2;
 63 
 64 --3.按照id降序查询
 65 select id,name from teacher order by id desc;
 66 
 67 --4.按照id升序(默认升序)
 68 select id,name from teacher order by id asc;
 69 
 70 --5.条件降序
 71 select name,id from teacher where id<4 order by id desc;
 72 
 73 --6.模糊查询,查以"张"开头(右模糊)
 74 select * from teacher where name like '张%';
 75 
 76 
 77 --7.模糊查询,查以"东"结尾(左模糊)
 78 select * from teacher where name like '%东';
 79 
 80 --8.模糊查询,查询包含"光"关键字的(左右模糊==全模糊)
 81 select * from teacher where name like '%光%';
 82 
 83 --8.1 去重查询
 84 select distinct id,name from teacher;
 85 
 86 --9.创建班级表
 87 create table classes
 88 (
 89   id int unsigned not null auto_increment primary key,
 90   name varchar(20) not null
 91 );
 92 
 93 --10.给班级表插入数据
 94 insert into classes(name) values('TB13');
 95 insert into classes(name) values('TB24');
 96 
 97 --11.查询classes数据
 98 select * from classes;
 99 
100 --12.teacher和classes联查
101   select c.id,c.name,t.id,t.name,t.pwd,t.classid
102   from teacher t,classes c
103   where t.classid=c.id;
104 
105 select c.id,c.name,t.id,t.name,t.pwd,t.classid
106  from classes c,teacher t
107  where t.classid=c.id;
108 
109 select *
110  from teacher t,classes c
111  where t.classid=c.id;
112 
113 --给列添加别名
114 select c.id cid,c.name cname,t.id tid,t.name tname,t.pwd
115  from classes c,teacher t
116  where t.classid=c.id;
117 
118 select c.id as cid,c.name as cname,t.id as tid,t.name as tname,t.pwd
119  from classes c,teacher t
120  where c.id=t.classid;
121 
122 --函数
123 --求最大值
124 select max(id) from teacher;
125 select max(id) as 'id最大值'from teacher;
126 select max(id) 'id最大值'from teacher;
127 
128 --最小值
129 select min(id) from teacher;
130 select min(id) as 'id最小值'from teacher;
131 select min(id) 'id最小值'from teacher;
132 
133 --平均值
134 select avg(id) from teacher;
135 select avg(id) as 'id平均值'from teacher;
136 select avg(id) 'id平均值'from teacher;
137 
138 --去重空格
139 select trim(' 张冉 ');
140 
141 --获取系统时间
142 select now();
143 
144 --将日期和时间分开查询
145 SELECT UTC_DATE(),UTC_TIME();

具体操作案例