Day06 homework -- skill question 1

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 all contents in the table

SELECT * from student;

5. Use sql Statement to query all score>70 Of my classmates id,name,score

select id,name,score from student where score>70;

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

alter table student MODIFY COLUMN name 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 88

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

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

select * from student where score >=80;

10. 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);

11. Inquire about id Values in 5 to 8 Basic information of all students

select * from student where id between 5 and 8;

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

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

13. 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;