MySQL Common database statements

A server/"target=" _blank ">sql/" target= "_blank" >mysql small Exercise Table Query modify tables Add field Delete field Nothing, find an exercise to write at work, contact topic is for SQL Server 2000 of the following is MySQL

One word explanation (2 points/a) 34 points
Data databaseDatabaseRDBMS Relational database management System grant authorization
REVOKE deny deny permission DECLARE define variable procedure stored procedure
Transaction Transaction triggers TRIGGER continue continue unique unqiue
Primary key primary key identity column identity foreign key foreign key check check
Constraint constraint
--------------------------------------------------------------------
1 Create a student table containing the following information, school number, name, age, sex, home address, contact number
CREATE TABLE Student
(
School Number int,
Name varchar (10),
Age int,
Sex varchar (4),
Home address varchar (50),
Contact Tel varchar (11)
);
--------------------------------------------------------------------
2 Modify the structure of the student table, add a list of information, education
ALTER TABLE Student Add column academic varchar (6);
--------------------------------------------------------------------
3 Modify the structure of the student table, delete a column of information, home address
ALTER TABLE student drop column home address; note here with drop rather than delete
--------------------------------------------------------------------
4 Add the following information to the student table:
School Number name age sex contact Telephone degree
1A22 Male 123456 Primary School
2B21 Male 119 Secondary School
3C23 Male 110 High School
4D18 114 University of Women
INSERT into student (school number, name, age, sex, contact telephone, education) VALUES (1, "A", 22, "male", "123456", "primary School");
INSERT into student (school number, name, age, sex, contact telephone, education) VALUES (1, "B", 21, "male", "119", "Middle School");
INSERT into student (school number, name, age, sex, contact telephone, education) VALUES (1, "C", 23, "male", "123456", "High School");
INSERT into student (school number, name, age, sex, contact telephone, education) VALUES (1, "D", 23, "female", "114", "university");
--------------------------------------------------------------------
5 Revise the data of the student's table and change the academic qualifications of the students whose telephone number begins with 11 to "college"
Update student Set degree = "College" where contact telephone like "11%";
--------------------------------------------------------------------
6 Delete the data of the student table, the name begins with C, the record of sex is ' male ' deleted
Delete from student where name like "C" and sex = "male";
--------------------------------------------------------------------
7 Query the data of the student table, will all ages younger than 22 years old, the academic record is "The college", the student's name and the study number shows up
Select name, school number from student where age <22 and education = "college";
--------------------------------------------------------------------
8 Query The Student table data, query all the information, list the top 25% records
Select the percent * from student;????
SELECT * from student limit 25%;????
This one is problematic in SQL 2000 and should be select top percent * from student;
--------------------------------------------------------------------
9 Check out all students ' names, sex, age descending order
Select name, sex, age from student order by age Desc;
--------------------------------------------------------------------
10. Check all the average age by gender group
Select AVG (age) As average age from student group by sex;
Select AVG (age) from student group by sex;
Select AVG (age) average age from student group by sex;
--------------------------------------------------------------------
3 Name The following aggregation number: AVG, SUM, max, Min, Count, COUNT (*)
AVG: Averaging
Sum: Sum
Max: Find the maximum value
Min: Find the minimum value
COUNT (*): Returns all rows
Count returns the record value that satisfies the specified condition -->