mysqlベースのsql

次に、よく使われるsqlの書き方を示します.

--    
create table stud(
	sno varchar(15) primary key,
	sname varchar(15) not null,
	age int,
	saddress varchar(15)
);

--     
insert into stud values('1001','JACK',20,'  ');
insert into stud values('1002','Tom',24,'    ');
insert into stud values('1003','  ',24,'    ');
insert into stud values('1004','  ',15,'    ');
insert into stud values('1005','   ',80,'  ');
insert into stud values('1006','  ',75,'  ');
insert into stud values('1007','    ',10,'  ');

--     
select * from stud;


--     
select sno as   ,sname as   ,age as   ,saddress as   
from stud;

-- select    
--   >=  <=：
select * from stud where age>=20 and age<=30;
--   between       ：
select * from stud where age BETWEEN 20 and 30;
--       20      30   :
select * from stud where age=20 or age=30;
-- in     
select * from stud where age in(20,30);
-- not in     
select * from stud where age not in(20,30);
-- like       ，'%'    
select * from stud where sname like ' %';
-- like       ，'_'     ，      ，     
select * from stud where sname like ' _';
--          
select * from stud where sname like '% %';
--        ' '      30 
select * from stud where sname like '% %' and age>30;


--        
--    
alter table stud add column sex char(1) default '1';
select * from stud;
--    
alter table stud drop column sex;
select * from stud;

--       
insert into stud values('1009','  ',null,'    ');
select * from stud;
update stud set age=20 where age is null;
select * from stud;
update stud set sname='  ',saddress='  '
where sno='1002';


--     
delete from stud where sname='  ';


--     
create view myview
as 
select * from stud where age>20;
select sname,sno,age from myview where age<40;

--     (*  1           null    ，      null)
select count(1) as total from stud;
--   age     
select count(age) from stud;
--    stud    age    
select avg(age) as averageage from stud;
--             
select floor(avg(age)) as average from stud;
-- SUM(expr)
select sum(age) from stud;
-- MAX(expr)
select max(age) from stud;
-- MIN(expr)
select min(age) from stud;
--                 
select sname,age from stud where age=(
	select min(age) from stud
);
select sname,age from stud where age in(
	select min(age) from stud
);
--          10  
insert into stud values('1010','  ',10,'  ');
--     
select age from stud where age in(
	select min(age) from stud
);
-- DISTINCT
select distinct age from stud where age in(
	select min(age) from stud
);

--   
--   
select * from stud order by age asc;
--   
select * from stud order by age desc;


-- EXISTS     exists (select * from stud where age=20) ---    age=20 ，   true、
--    exists(...)              null ，   null   false，    true；
--     stud  age=20  ，        sname，age。
select sname,age from stud where EXISTS(
	select age from stud where age>20
);

--   
--       
select saddress,avg(age) as     
from stud
group by saddress;
--   sum  
select saddress,sum(age) as     
from stud
group by saddress;
--   having  
select saddress,sum(age) as      
from stud group by saddress
having sum(age)>30


--        
-- Ltrim(str)	       
-- Rtrim(str)        
-- trim(str) 	       
-- Left(str,n)     n   
-- Right(Str,n)     n   
-- Substring(str,begin,end)       
-- Reverse(str)        
-- Lower(str)      
-- Upper(str)      
-- CONCAT(str1,str2,...)        
-- INSTR(str,substr)   substr s   ，    0


-- left
select * from stud where left(saddress,2)='  '
--   
select concat(sno,'-->',sname,'-->',age,'-->',saddress) as       from stud

-- INSTR(str,substr): 1    
select sname,INSTR(sname,' ') from stud