练习初级

1.安装mysql数据库

2.创建一个mydb1数据库，并查看。

3.创建一个mydb2数据库，指定字符编码为gb2312;

4.创建一个mydb3数据库，指定字符编码为utf8,并且指定校验规则;

update练习

drop table employee;
create table employee
(
id int,
name varchar(20),
sex varchar(10),
birthday date,
salary float,
resume text
);

insert into employee values (1,'zhangsan','nan','1980-11-25',2000,'good body');
insert into employee values(2,"lisi","nv","1980-04-25",1000,"good body");
insert into employee values(3,"xiaohong","nv","1978-11-25",4000,"good girl");

5将所有员工薪水修改为5000元。

6将姓名为’zhangsan’的员工薪水修改为3000元。

7将姓名为’lisi’的员工薪水修改为4000元,sex改为female。

8将xiaohong的薪水在原有基础上增加1000元。

update employee set salary=salary+1000 where name='xiaohong';

select 练习

create table student(
id int,
name varchar(20),
chinese float,
english float,
math float
);

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

9查询表中所有学生的信息。

select * from student;

10查询表中所有学生的姓名和对应的英语成绩。;

select name,english from student;

11过滤表中重复数据。

select distinct english from student;   

12统计每个学生的总分。

select name,(chinese+english+math) as zongfen from student;

13在所有学生总分数上加10分特长分。

select name,(chinese+english+math)+10 from student;

14使用别名表示学生分数。

select name xingming,(chinese+english+math) zongfen from student;

15查询姓名为李一的学生成绩

select * from student where name='李一';

16查询英语成绩大于90分的同学

select name,english from student where english>90;

17查询总分大于200分的所有同学

select name,(chinese+english+math) from student where ((chinese+english+math) >200); 
select name,(chinese+english+math) as zf from student where (chinese+english+math)>200; 

18查询英语分数在 80－90之间的同学。

select name,english from student where english>80 && english < 90;

select name,english from student where english between 80 and 90;

19查询数学分数为89,90,91的同学。

select name,math from student where math=89 or math = 90 or math =91;

select name,math from student where not math in(95,90,77);

20查询所有姓李的学生英语成绩。

select name,english from student where name like '李%';

21查询数学分>80并且语文分>80的同学。

select * from student where math > 80 and chinese >80;

// select * from stu where math >80 or chinese > 80;

22查询英语>80或者总分>200的同学

    select name,(chinese+english+math) from student where english>80 or (chinese+english+math)>200;

23对数学成绩排序后输出。

    select name,math from student  order by math desc; 

24对总分排序后输出，然后再按从高到低的顺序输出

select name,(chinese+english+math) zf from student order by zf desc;

25对姓李的学生成绩排序输出

select name ,(chinese+english+math) zf from student where name like '李%' order by zf desc;