mysql数据库02(必备sql语句）

1.必备sql语句

示列

代码：

create table roles(

id int not null primary  key auto_increment,
role  varchar(24)     
)default charset=utf8;


create table info(
  id int not null primary  key auto_increment,
  name  varchar(16),
   age int,
    role_id int
)default charset=utf8;

insert into roles(role) values ("教师"),("医生"),("学生");

insert into info(name,age,role_id)  values("张三",14,1);
insert into info(name,age,role_id)  values("李四",25,2);
insert into info (name,age,role_id) values("张三2",58,3);
insert into info(name,age,role_id)  values("张三3",56,2);
insert into info(name,age,role_id)  values("张三4",34,2);
insert into info (name,age,role_id) values("张三5",34,2);
insert into info(name,age,role_id) values("张三6",12,3);
insert into info(name,age,role_id)  values("张三7",34,2);
insert into info(name,age,role_id)  values("张三8",42,1);

 1.1条件

根据条件搜索结果。

select * from info where age >20;
select * from  info where id >3;
select * from  info where id >=3;
select * from  info where id <=3;
select * from  info where id between 2 and 4; /* 大于等于2并且小于等于4*/
select * from  info where  role_id <>1;/*不等于*/
select * from  info where role_id !=1;/*不等于*/
select * from  info where age =19;
/*查找 id=2 or id =4 or id =6 的 */

select * from info where name = '张三' and age = 19;
select * from info where name = '张三' or age = 49;

select * from info where age <20 or age < 49 and role_id=2;/*先算age=49 and role_id=2  在算 or */
select * from info where (age <20 or age <49) and role_id=2;/*先算括号里的*/


select * from info where id not  in (1,4,6);

select * from info where  exists (select * from roles where id=2);
select * from info where not exists (select * from roles where id=2);

/*从info找id>2,然后从中找age>10  as把字段名命名为 xx 只是在查看的时候改了*/
select from (select * from info where id>2) as T where age > 10;
select T.age from (select * from info where id>2) as T where age > 10;

select * from info where info.id > 5;

 1.2通配符

一般用于模糊搜索。

/* %:匹配任意个字符 除 NUll 空格
   _ :匹配一个字符  除 NUll 空格
*/

select * from info where name like "张%";
select * from info where name like "张_";

1.3 映射

想要获取的列。

select id, name				from info;
select id, name as NM 		from info;
select id, name as NM, 123  from info;

select 
	id,
	name,
	666 as role_id,
	( select max(id) from roles ) as mid, /*max/min/sum*/
	( select min(id) from roles) as nid, -- max/min/sum
	age
from info;

 

select 
	id,
	name,
	
	case role_id when 1 then "教师" when 2 then "学生" else "医生" end v3,

	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

 

 1.4排序

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 顺序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。

select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%张" order by age asc,id desc;

 1.5取部分

select * from info limit 5;   										-- 获取前5条数据
select * from info order by id desc limit 3;						-- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3;			-- 先排序，再获取前3条数据


select * from info limit 3 offset 2;	-- 从位置2开始，向后获取前3数据,默认第一条数据的位置时0

数据库表中：1000条数据。

select * from info limit 10 offset 0; -- 第一页：



select * from info limit 10 offset 10;-- 第二页：
select * from info limit 10 offset 20;-- 第三页：
select * from info limit 10 offset 30;-- 第四页：
     .....

1.6分组 （group by)

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

select age,count(1) from info group by age;

 

 

select role_id,count(id) from info group by role_id having count(id) > 2;

 having 过滤分组  只能和 group by 一块出现

 

select age,count(1)  as a from info group by age order by a;

 

1.7左右连表

将多张表联合起来查询 

 主表 left outer join 从表 on 主表.x = 从表.id  

select * from info left outer join roles on info.role_id = roles.id;

 

select * from roles left outer join info on  roles.id= info.role_id;

 

从表 right outer join 主表 on 主表.x = 从表.id

 

select  * from info right outer join roles on  roles.id=info.role_id;

select  * from roles right outer join info  on info.role_id = roles.id;
select  * from roles right  join info  on info.role_id = roles.id;

 

 为了跟直接的感受他们的区别在roles添加数据

 

 简写：可省略 outer

3.内连接

表  inner join 表  on 条件

select * from info inner join roles on info.role_id=roles.id;