Mysql基础

create table card
    (
    cardid int,
   cardnum varchar(20),
    regDate date
   );

alter table card rename cardinfo;

alter table cardinfo add deldate date;

alter table cardinfo drop regdate;

drop table cardinfo;

select * from emp;

update table emp set salary=5000;

insert into emp values(4,'zs','male','1996-8-8',10000,'2018-08-08','bad boy'),
    (5,'aaa','female','2555-8-8',10000,'2312-9-9','bad girl');

update emp set salary=3000 where name='zs';

update emp set salary=4000,resume='ccc' where name='aaa';

insert into emp values(6,'wu','male','1998-8-8',10000,'1999-9-9','boy');

update emp set salary=salary+1000 where name='wu';

delete from emp where name='zs';

truncate table emp;

（1）create table manager
     (
     mid int,
     mname varchar(20),
     age int,
     sex char(2),
     password varchar(20),
     address varchar(20),
     phone varchar(20)
     );
     
（2）insert into manager values
    (1,'王子',18,'男','123','北京','110'),
    (2,'公主',20,'女','456','上海','220'),
    (3,'太子',23,'男','789','南京','330');
    
（3）update manager set age=24 where mname='王子';

（4）update manager set address='天津' where address in ('南京');
	 update manager set address='天津' where address='南京';
	 
（5）insert into manager values(4,'女子',40,'女','100','河北','440');
	update manager set password='888888' where age>30 and sex='女';

（6）update manager set password='111111';

（7）update manager set phone='7654321' where phone<>'110';
	update manager set phone='7654321' where phone!='110';

（8）update manager set age=18,address='承德',sex='女' where mname='王子';

（9）delete from manager where mname='王子';

（10）insert into manager values('5','刘涛',76,'男','999','南京','777');
	 delete from manager where address='南京' and age>60;

（11）insert into manager values('5','刘涛',76,'男','999','北京','777');
	delete from manager where address<>'北京';
	delete from manager where address!='北京';

（12）insert into manager values('5','刘涛',76,'男','999','上海','777');
	delete from manager where address='北京' or address='上海';

（13）insert into manager values('5','刘涛',76,'男','999','上海',null);
	insert into manager values('5','刘涛',76,'男','999','上海','');
	delete from manager where phone is null or phone='';

（0）create table manager
     (
     mid int,
     mname varchar(20),
     age int,
     sex char(2),
     password varchar(20),
     address varchar(20),
     phone varchar(20)
     );
     
	 insert into manager values(1,'王子',18,'男','123','北京','110'),
     (2,'公主',20,'女','456','上海','220'),
     (3,'太子',23,'男','789','南京','330');

（14）select * from manager where mname='公主';

（15）select mname from manager where age between 18 and 30;

	select mname from manager where age>=18 and age<=30;

（16）select mname,phone from manager;

（17）select * from manager where sex='男' and mname='王子';

（18）select * from manager where address='北京' or address='上海';

（0）create table scores
     (
     stuid int,
     java int,
     mysql int,
     stuname varchar(20)
     );
     
     insert into scores values(1,67,78,'张三'),
     (2,87,55,'李四'),
     (3,66,90,'王五'),
     (4,98,78,'赵六'),
     (5,80,88,'田七');
 
（1）select * from scores order by java desc;

（2）select * from scores order by mysql desc limit 0,3;

（3）select * from scores order by java asc limit 0,1;

（4）select stuname from scores where java>=80 and mysql>=80;

（5）select * from scores where java>=90 or mysql>=90;

（6）select stuname,java,mysql from scores;

（7）select stuname,sum(java+mysql) as all_grade from scores group by stuname;

（0）create table student2
     (
     stuname varchar(20),
     telphone varchar(20),
     address varchar(20),
     subject varchar(20),
     stuno varchar(20),
     score int,
     birthday date
     );

	insert into student2 values
     ('郭敬明','1371234567','北京','java','s1101',89,'1979-04-05'),
     ('张三丰','1372839201','上海','数据库','s1102',67,'1967-09-07'),
     ('赵敏','1387839201','山东','mysql','s1103',99,'1987-09-07');
     
（a）select stuname,telphone,address from student2 where address='山东';

（b）select subject from student2 where subject like '%数据库%';

（c）select * from student2 where telphone like '1387%';

（d）select * from student2 where telphone like '姜%';

（e）select java,score from student2 where stuno='s1101';

（f）select * from student2 where birthday between '1980-1-1' and '1989-12-30';

	select * from student2 where birthday>'1979-12-31';

（g）select stuname from student2 where address in ('北京','上海','广州');

（h）select stuname,telphone,score from student2 order by score desc limit 0,2;

（i）select * from student2 where score between 80 and 90;

	select * from student2 where score>=80 and score<=90;

（0）create table scores2
     (
     grade varchar(10),
     stuno varchar(20),
     examdate date,
     subject varchar(20),
     score int,
     xueqi int
     );

	insert into scores2 values
     ('s1','s1101','2015-02-03','c',89,1),
     ('s2','s1103','2015-03-03','java',90,2),
     ('s3','s1102','2015-07-03','c',100,1);

（1）select count(*) from scores2;

（2）select stuno,sum(score),avg(score) from scores2 where stuno='s1101' and xueqi=1
 	group by stuno;

（3）select max(score),min(score),avg(score) from scores2 where examdate='2015-2-3';

	select max(score),min(score),avg(score) from scores2 where subject='c';

（4）select avg(score) from scores2 where subject='c' and score>=60;

（5）select avg(score) from scores2 where subject='c';

（6）select count(*) from scores2 where subject='java';

（0）create table student
     (
     grade varchar(10),
     name varchar(10),
     xueshi int,
     isexam char(1),
     subject varchar(10),
     score int
     );

	insert into student values
     (1,'张三',10,'是','java',99),
     (1,'李四',10,'否','java',0),
     (2,'王五',20,'是','mysql',88),
     (2,'赵六',20,'是','mysql',77),
     (2,'王五',20,'是','java',99),
     (2,'赵六',20,'否','java',0),
     (1,'张三',10,'是','mysql',88);

（a）select grade,sum(xueshi) as all_xueshi from student group by 
	grade order by all_xueshi asc;

（b）select name,avg(score) from student where isexam='是' group by name;

（c）select subject,avg(score) as avg from student group by subject;

（0） create table student
     (
     subjectname varchar(20),
     stuname varchar(20),
     address varchar(20),
     sex char(2),
     email varchar(30),
     grade varchar(10),
     birthday date,
     examdate date,
     scores int
     );

	insert into student values
     ('java','张三','北京','男','123@qq.com','s1','1990-03-04','2013-5-6',89),
     ('html','李四','上海','男','','s2','1993-08-04','2013-5-6',87),
     ('html','王五','北京','男','123@qq.com','s2','1993-08-04','2013-5-6',90);

（1）select subjectname from student where grade='s2';

（2）select stuname,address from student where grade='s2' and sex='男';

（3）select stuname,grade from student where email is null or email='';

（4）elect stuname,grade from student where grade='s2' 
	and birthday>='1993-1-1' and birthday<='1993-12-31';
	
	select stuname,grade from student where grade='s2' 
	and birthday between '1993-1-1' and '1993-12-31';

（5）select stuname,scores from student where examdate='2013-5-6'
	 and subjectname='html';