mysql common database statement exercise
- 2020-05-09 19:23:54
- OfStack
Be free and at leisure, I found an exercise to write at work. The contact topic is sql server 2000 and below is mysql
1 word explanation (2 points/piece) 34 points
Data data Database database RDBMS relational database management system GRANT authorization
REVOKE cancels permission DENY denies permission DECLARE defines the variable PROCEDURE stored procedure
Transaction Transaction triggers TRIGGER continues continue only 1 unqiue
The primary key primary key identifies the column identity and the foreign key foreign key checks check
Constraint constraint
--------------------------------------------------------------------
1) create a student table, including the following information, student number, name, age, gender, home address and contact number
create table student
(
Student id int,
Name varchar (10),
Age int,
Gender varchar (4),
Home address varchar(50),
Contact number varchar(11)
);
--------------------------------------------------------------------
2) modify the structure of the student table and add 1 column of information and education background
alter table student add column varchar(6);
--------------------------------------------------------------------
3) modify the structure of the student table by deleting 1 column of information and home address
alter table student drop column home address; // note that drop is used here instead of delete
--------------------------------------------------------------------
4) add the following information to the student table:
Student id, name, age, sex, contact number, educational background
1A22 male 123456 primary school
2B21 male 119 middle school
3C23 male 110 high school
4D18 female 114 university
insert into student (student number, name, age, sex, contact number, education) values(1,"A",22," male ","123456"," primary school ");
insert into student (student number, name, age, sex, contact number, education) values(1,"B",21," male ","119"," middle school ");
insert into student (student number, name, age, sex, contact number, education) values(1,"C",23," male ","123456"," high school ");
insert into student (student number, name, age, sex, contact number, education) values(1,"D",23," female ","114"," university ");
--------------------------------------------------------------------
5) modify the data in the student table and change the educational background of students whose phone number begins with 11 to "junior college"
update student set education =" junior college "where contact number like "11%";
--------------------------------------------------------------------
6) delete the data in the student table. Delete the record whose name begins with C and whose gender is' male '
delete from student where name like "C" and gender =" male ";
--------------------------------------------------------------------
7) query the data in the student table, and display the names and student Numbers of all the students under the age of 22 with a diploma of "junior college"
select name, student number from student where age < 22 and education =" junior college ";
--------------------------------------------------------------------
8) query the data in the student table, query all the information, and list the records in the top 25%
select top 25 percent * from student ; ????
select * from student limit 25%;????
This is a problem. In sql 2000 it should be select top 25 percent * from student;
--------------------------------------------------------------------
9) find out the name, gender and age of all the students in descending order
select name, sex, age from student order by age desc;
--------------------------------------------------------------------
10) query all average ages by gender
select avg as average age from student group by gender;
select avg(age) from student group by gender;
select avg(age) average age from student group by gender;
--------------------------------------------------------------------
3) say the following aggregation number: avg,sum,max,min, count,count(*)
AVG: average
SUM: sum
MAX: maximize
MIN: find the minimum
COUNT(*): returns the number of all rows
COUNT returns a record value that meets the specified criteria
1 word explanation (2 points/piece) 34 points
Data data Database database RDBMS relational database management system GRANT authorization
REVOKE cancels permission DENY denies permission DECLARE defines the variable PROCEDURE stored procedure
Transaction Transaction triggers TRIGGER continues continue only 1 unqiue
The primary key primary key identifies the column identity and the foreign key foreign key checks check
Constraint constraint
--------------------------------------------------------------------
1) create a student table, including the following information, student number, name, age, gender, home address and contact number
create table student
(
Student id int,
Name varchar (10),
Age int,
Gender varchar (4),
Home address varchar(50),
Contact number varchar(11)
);
--------------------------------------------------------------------
2) modify the structure of the student table and add 1 column of information and education background
alter table student add column varchar(6);
--------------------------------------------------------------------
3) modify the structure of the student table by deleting 1 column of information and home address
alter table student drop column home address; // note that drop is used here instead of delete
--------------------------------------------------------------------
4) add the following information to the student table:
Student id, name, age, sex, contact number, educational background
1A22 male 123456 primary school
2B21 male 119 middle school
3C23 male 110 high school
4D18 female 114 university
insert into student (student number, name, age, sex, contact number, education) values(1,"A",22," male ","123456"," primary school ");
insert into student (student number, name, age, sex, contact number, education) values(1,"B",21," male ","119"," middle school ");
insert into student (student number, name, age, sex, contact number, education) values(1,"C",23," male ","123456"," high school ");
insert into student (student number, name, age, sex, contact number, education) values(1,"D",23," female ","114"," university ");
--------------------------------------------------------------------
5) modify the data in the student table and change the educational background of students whose phone number begins with 11 to "junior college"
update student set education =" junior college "where contact number like "11%";
--------------------------------------------------------------------
6) delete the data in the student table. Delete the record whose name begins with C and whose gender is' male '
delete from student where name like "C" and gender =" male ";
--------------------------------------------------------------------
7) query the data in the student table, and display the names and student Numbers of all the students under the age of 22 with a diploma of "junior college"
select name, student number from student where age < 22 and education =" junior college ";
--------------------------------------------------------------------
8) query the data in the student table, query all the information, and list the records in the top 25%
select top 25 percent * from student ; ????
select * from student limit 25%;????
This is a problem. In sql 2000 it should be select top 25 percent * from student;
--------------------------------------------------------------------
9) find out the name, gender and age of all the students in descending order
select name, sex, age from student order by age desc;
--------------------------------------------------------------------
10) query all average ages by gender
select avg as average age from student group by gender;
select avg(age) from student group by gender;
select avg(age) average age from student group by gender;
--------------------------------------------------------------------
3) say the following aggregation number: avg,sum,max,min, count,count(*)
AVG: average
SUM: sum
MAX: maximize
MIN: find the minimum
COUNT(*): returns the number of all rows
COUNT returns a record value that meets the specified criteria