mysql 常用举例

drop table student;
create table student
(sno   int   not null unique,
 sname varchar(8) not null,
 ssex  char(2)    not null,
 sage int ,
 sdept varchar(20));
 
 select * from student;
 alter table student drop unique(sno);
 alter table student modify sno primary key;
 alter table student add constraints con_s check(ssex in('F', 'M'));
 
 insert into  student values ('0001', 'zhangwen', 'F', 24, 'math');
 insert into  student values ('0201', 'zhangzi', 'F', 24, 'jsj');
 insert into  student values ('0002', 'wangjing', 'M', 22, 'math');
 insert into  student values ('0301', 'lilu', 'M', 23, 'wuli');
 insert into  student values ('0003', 'wujun', 'M', 24, 'math');
 insert into  student values ('0202', 'wangli', 'F', 23, 'jsj');
 insert into  student values ('0203', 'jiangmin', 'M', 23, 'jsj');
 insert into  student values ('0302', 'lixiang', 'F', 23, 'wuli');
 insert into  student values ('0303', 'jinmin', 'F', 24, 'wuli');
 insert into  student values ('0304', 'jinmin', 'F', null, 'wuli');
 insert into  student values ('0204', 'jinmin', 'F', 22, '');
 delete from student where sno = '0204';
 update student set sname = 'luyong' where sno = '0304';
 insert into student values('0004','meinv','M',22,'math');
 insert into student values('0006','beauty','M',21,'math');
 ----------------------------------------------
 drop table course;
 create table course(cno int primary key,
                     cname varchar2(10) not null,
                     ccredite float(2));
                    
alter table course add constraints con_c check (ccredite in ('2.0', '1.5', '2.5', '3.5'));  
update  course set ccredite = '1.5' where cno = '1111001';              
select * from course;

alter table course modify ccredite number(2,1) default null;

insert into  course values ('1001001', 'shuxuefenxi', '2.0');
insert into  course values ('1001002', 'gaidengdaishu', '2.0');
insert into  course values ('1111001', 'zhengzhi', '1.5');
insert into  course values ('1111002', 'yingyu', '2.0');
insert into  course values ('1001011', 'shibian', '2.5');
insert into  course values ('1001012', 'fubian', '3.5');
insert into  course values ('1002001', 'c++', '3.5');
insert into  course values ('1002002', 'java', '2.5');
insert into  course values ('1003001', 'lixue', '2.5');
insert into  course values ('1003002', 'weidianzi', '3.5');

 
----------------------------------------------
drop table sc;
create table sc(sno int ,
                cno int ,
                grade varchar2(10),
                constraints sc_s foreign key(sno) references student(sno),
                constraints sc_c foreign key(sno) references course(cno));

 alter table sc rename column grade to score; 
 alter table sc drop constraints sc_c;
 alter table sc add constraints sc_c foreign key(cno) references course(cno);
 
 select * from sc;
 
insert into sc values('0001', '1001001','80"');          
insert into sc values('0001', '1001002','85"');
insert into sc values('0001', '1111001','77"');
insert into sc values('0001', '1111001','88"');
insert into sc values('0002', '1002001','61"');          
insert into sc values('0002', '1001002','83"');
insert into sc values('0002', '1111001','97"');
insert into sc values('0002', '1111001','81"');
insert into sc values('0003', '1001001','92"');          
insert into sc values('0003', '1002002','75"');
insert into sc values('0003', '1111001','67"');
insert into sc values('0003', '1111001','79"');

insert into sc values('0201', '1111001','65"');
insert into sc values('0201', '1111001','91"');
insert into sc values('0201', '1002001','98"');
insert into sc values('0201', '1001001','88"');
insert into sc values('0202', '1001001','73"');
insert into sc values('0202', '1111001','85"');
insert into sc values('0202', '1002001','93"');
insert into sc values('0202', '1001001','78"');
insert into sc values('0203', '1111001','88"');
insert into sc values('0203', '1111001','99"');
insert into sc values('0203', '1001001','81"');
insert into sc values('0203', '1001001','74"');

insert into sc values('0301', '1111001','75"');
insert into sc values('0301', '1111001','96"');
insert into sc values('0301', '1003001','86"');
insert into sc values('0301', '1003002','90"');
insert into sc values('0302', '1111001','72"');
insert into sc values('0302', '1111001','86"');
insert into sc values('0302', '1002001','96"');
insert into sc values('0302', '1003002','98"');
insert into sc values('0303', '1111001','85"');
insert into sc values('0303', '1111001','69"');
insert into sc values('0303', '1003001','79"');
insert into sc values('0303', '1002002','80"');

insert into sc values('0304', '1002002','');
insert into sc values('0204', '1003001','');
delete from sc where sno = '0204';
--------------------------------------------------
select * from student;
select * from course;
select * from sc;
select sno,sname,ssex,sage,sdept from student where sage like '%%' or sdept like '%%';
select * from student where sname like '%ang%';
select * from student where sname like '_ang%';
select * from student where sname like '[ZLJ]%';
select rownum,sc.sno,sc.score from sc where rownum < 6 minus select  rownum,sc.sno,sc.score from sc where rownum <2;
select * from(select sc.* ,rownum rn from sc) where rn >=2 and rn <6;
select sno,sname,sage from (select sno,sname,sage from student order by dbms_random.random) where rownum<=3;
select rownum,sno,sname,sage from   (select rownum rn,sno,sname,sage from  (select sno,sname,sage from student order

by sage)  where rownum<=5) where rn>=1;
select rowid ,s.* from student s where sage>22;
--select top 50 percent * from sc;
select * from course where ccredite in('2.0','3.5');
select * from student where sage not between 23 and 24;
select sname ,sdept,cname,score from student s, course c, sc where s.sno = sc.sno and c.cno = sc.cno order by cname;
select cno ,count(sno) from sc group by cno;
select sname, sum(ccredite) from student s, course c, sc where s.sno = sc.sno and c.cno = sc.cno group by sname;
select sname,ccredite,score,sdept from student s, course c, sc where s.sno = sc.sno and c.cno = sc.cno and sdept <>

'jsj';
select sname,sage,score from student s inner join sc on s.sno = sc.sno order by sage;
select sname,sage,score from student s , sc where s.sno = sc.sno order by sage;
select sname,sage,score from student s left join sc on s.sno = sc.sno order by sage;
select sname,sage,score from student s full join sc on s.sno = sc.sno order by sage;
select sname,sage,score from student s right join sc on s.sno = sc.sno order by sage;
select sname from student union select cname from course;
--------------------------------------
select   ascii('A') A, ascii('a') a,ascii('0')  zero, ascii('') space from dual;  --65    97   48
select   chr(54740)   zhao,chr(65)   chr65   from   dual;  --赵   A
select   concat('010-','88888888')||'转23'   zwz电话   from   dual;   --010-88888888转23
select   initcap('smith')   Aname   from   dual;
select   instr('oracle   traning','ra',1,2)   instring   from   dual;  -- 11  搜索字符串，返回其位置
select   sname,length(sname),sage,length(to_char(sage))   from   student;
select   lower('AaBbCcDd')AaBbCcDd   from   dual;
select   upper('AaBbCcDd')   upper   from   dual;
select   lpad(rpad('gao',10,'*'),17,'^') pasLR from   dual;  --RPAD   在列的右边粘贴字符,LPAD   在列的左边粘贴字符
select   ltrim(rtrim('   gao   qian   jing   ',' '),' ') delLR  from   dual;  --LTRIM   删除左边出现的字符串,
select   substr('13088888888',3,8)   from   dual;   --08888888
select   replace('he   love   you','he','i')   from   dual;    --i   love   you
select   abs(100),abs(-100)   from   dual;
select   acos(-1)   from   dual;   --余弦
select   cos(-3.14159265358)   from   dual;
select   asin(0.5)   from   dual;  --正弦
select   atan(1)   from   dual;
select   ceil(3.1415927)   from   dual;   --返回大于或等于给出数字的最小整数
select   exp(2),exp(1)   from   dual;  --返回一个数字e的n次方
select   floor(2345.67)   from   dual;   --对给定的数字取整数
select   ln(1),ln(2),ln(2.7182818)   from   dual;
select   log(2,1),log(2,4)   from   dual;
select   mod(10,3),mod(3,3),mod(2,3)   from   dual;   --返回一个n1除以n2的余数
select   power(2,4),power(3,3)   from   dual;   --16   27
select   sqrt(64),sqrt(10)   from   dual;    --8    3.16227766016838
select   sign(123),sign(-100),sign(0)   from   dual;   --1   -1   0
select   trunc(124.1666,-2)   trunc1,trunc(124.16666,2)   from   dual;   --100   124.16
select   round(55.5),round(-55.4),trunc(55.5),trunc(-55.5)   from   dual;   --56   -55   55   -55
select   to_char(add_months(to_date('199912','yyyymm'),2),'yyyymmdd')   from   dual;   --20000201
select   to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymmdd')   from   dual;   --19991001
select   to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')   from   dual;  --2009.08.14    2009.08.15
select   last_day(sysdate)   from   dual;   --2009-8-31 15:13:08
select * from dual;