Day06 homework - skill question 6

 1. establish test database

 create database test character set utf8;

2. stay test Create in the database student surface ,(id Set as primary key self growth )

create table student(
id int primary key auto_increment,
name varchar(20),
score int,
address varchar(50),
useremail varchar(20)
)

desc student;

 3. towards student Add records to the table

insert into student value(1,' Zhang San ',98,' Beijing ','[email protected]'),(2,' Li Si ',88,' Shanghai ','[email protected]'),(3,' Wang Wu ',78,' Guangzhou ','[email protected]'),(4,' Zhao Liu ',68,' Shenzhen ','[email protected]'),(5,' Sun Qi ',58,' Hangzhou ','[email protected]'),(6,' Xiaohong ',48,' Beijing ','[email protected]'),(7,' Little black ',99,' Shanghai ','[email protected]'),(8,' Little green ',100,' Hangzhou ','[email protected]'),(9,' Small powder ',60,' Hangzhou ','[email protected]'),(10,' Lilac ',70,' heilongjiang ','[email protected]')

4. Use sql Statement to query the table id,name, and address All the contents of the field

select id,name,address from student;

5. Use sql Statement to query all students in the table id,name,score

select id,name,score from student;

6. change useremail The data type of the field is varchar(50)

alter table student modify column useremail varchar(50);
desc student;

7. Add a field to the table , The field name is “pingjia”, Field type is varchar(20)

alter table student add pingjia varchar(20);
desc student;

8. Change the score of the classmate whose name is Zhang San to 92

update student set score=92 where name=' Zhang San ';
select * from student;

9. If 80 It's a passing line , Find out the details of all the students who failed

select * from student where score <80;

 ​​​​​​​

10. Name is “ Xiaohong ” Students' scores are on the original basis +20

update student set score=score+20 where name=' Xiaohong ';
select * from student;

   ​​​​​​​

11. Use keywords in, Inquire about id The value is 1 or 5 or 7 Basic information of students

select * from student where id in(1,5,7);

12. Inquire about id Values in 4 to 9 Basic information of all students

select * from student where id between 4 and 9;

13. The query name is Xiaohong and the score is greater than 60 Basic information of students

select * from student where name=' Xiaohong ' or score>60;

 ​​​​​​​

14.  The query name is Xiao Hong or the score is greater than 90 Basic information of students

select * from student where name=' Xiaohong ' or score>90;

15.  Inquire about score The field value is NULL Basic information of students

select * from student where score is null;

16.  Inquire about name Not Zhang San's classmate id,name, and score

select id,name,score from student where not name=' Zhang San ';

17. Ascending by address , The grades are in descending order

select * from student order by address,score desc;

18. De duplicate the city

select distinct address from student;