mysql====== Exercises (1)

MySQL Exercises

Create a student table

Mysql> CREATE TABLE Student (

-ID Int (TEN) not null unique primary key,

-Name varchar (a) NOT NULL,

Sex varchar (4),

Brith year,

Deparment varchar (20),

Address varchar (50)

);

Query OK, 0 rows affected (0.03 sec)

View the structure of the student table

Mysql> desc Student

;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| ID | Int (10) | NO | PRI |       NULL | |

| name | varchar (20) |     NO | |       NULL | |

| sex | varchar (4) |     YES | |       NULL | |

| Brith | Year (4) |     YES | |       NULL | |

| Deparment | varchar (20) |     YES | |       NULL | |

| Address | varchar (50) |     YES | |       NULL | |

+-----------+-------------+------+-----+---------+-------+

6 rows in Set (0.00 sec)

Create a score table

Mysql> CREATE TABLE Score (

-ID Int (TEN) not null unique primary key,

stu_id varchar (20),

C_name varchar (20),

-Grade int (10)

);

Query OK, 0 rows affected (0.02 sec)

View the structure of the score table

Mysql> DESC score;

+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| ID | Int (10) | NO | PRI | NULL |

| stu_id | varchar (20) |     YES | |       NULL | |

| C_name | varchar (20) |     YES | |       NULL | |

| Grade | Int (10) |    YES | NULL |

+--------+-------------+------+-----+---------+-------+

4 rows in Set (0.00 sec)

Inserting data into the student table

Mysql> select * from student;

+-----+--------+------+-------+-----------+--------------+

| ID | name | sex | Brith | Deparment | Address |

+-----+--------+------+-------+-----------+--------------+

| 901 | Boss Zhang |  Male | 1985 | Computer Systems | Haidian District, Beijing |

| 902 | Double Dick |  Male | 1987 | Computer Systems | Beijing Changping District |

| 903 | Zhang San |  Women | 1990 | Chinese Department | Hunan Province Yongzhou |

| 904 | John Doe |  Male | 1990 | English Department | Liaoning Province Fuxin |

| 905 | Harry |  Women | 1991 | English Department | Xiamen, Fujian |

| 906 | Wangliuqi |  Male | 1998 | Computer Systems | Hunan Province Hengyang |

+-----+--------+------+-------+-----------+--------------+

6 rows in Set (0.03 sec)

Inserting data into the score table

Mysql> select * from score;

+----+--------+--------+-------+

| ID | stu_id | C_name | Grade |

+----+--------+--------+-------+

|    1 | 901 |    Computer | 98 |

|    2 | 901 |    English | 80 |

|    3 | 902 |    Computer | 65 |

|    4 | 902 |    English | 88 |

|    5 | 903 |    English | 95 |

|    6 | 904 |    Computer | 70 |

|    7 | 904 |    English | 92 |

|    8 | 905 |    English | 90 |

+----+--------+--------+-------+

8 rows in Set (0.01 sec)

==============================================================

Topic 1

Querying all records in the student table

Mysql> select * from student;

Topic 2

Query the second to fourth record of the student table

Mysql> SELECT * FROM student limit 1, 3;

Topic 3

Check the student table for information about the student ID (name), and faculty (Deparment) for all students.

Mysql> select id,name,deparment from student;

Topic 4

Query the computer department and the English department's student information from the student table

Mysql> SELECT * FROM student where deparment= ' computer Department ' or deparment= ' English Department ';

Topic 5

Find information about students aged 26-30 and older from the student table

Mysql> Select Id,name,sex,brith as age, deparment,address

From student

where 2017-brith between and 30;

Topic 6

Find out how many people are in each faculty from the student table

Mysql> select Deparment,count (ID)

From student

Group BY Deparment;

Topic 7

Query the highest score for each account from the score table

Mysql> Select C_name, max (grade)

From score

Group BY C_name;

Topic 8

Query John Doe Exam (c_name) subjects and exam results (grade)

Mysql> Select C_name,grade

From score

, where stu_id= (

Select ID from Student

Where name= ' John Doe '

);

Topic 9

Query all students ' information and test scores in the form of links

Mysql> Select Student.id,name,sex,brith,deparment,address,c_name,grade

From student, score

where student.id=score.stu_id;

Topic 10

Calculate the total grade of each student in descending order

Mysql> Select Student.id,name,sex,brith,deparment,address,sum (Grade)

From Student,score

--Where student.id=score.stu_id

Group BY name

Order by sum (grade) desc;

Topic 11

Calculate the average score for each account

Mysql> Select Student.id,name,sex,brith,deparment,address,avg (Grade)

From Student,score

--Where student.id=score.stu_id

Group by ID;

Topic 12

Query student information for computers with a score of less than 95

Mysql> SELECT * FROM student

--Where ID in (

Select stu_id from Score

where c_name= ' computer ' and grade<95);

Topic 13

Check student information for both computer and English exams

Mysql> SELECT * FROM student

--Where Id=any

--(select stu_id from Score

, where stu_id in (

Select stu_id from

Score where c_name= ' computer ')

and C_name= ' English ');

Topic 14

Query the student's number from the student table and the score table, then merge the query results

Mysql> Select ID from student

Union

Select stu_id from Score;

Topic 15

Check the name of the student surnamed Zhang or Wang surname, department and exam subjects and results

Mysql> Select Student.id,name,sex,brith,deparment,address,c_name,grade

From Student,score

--Where (name like '% ' or name like ' King% ')

and student.id=score.stu_id;

Topic 16

Enquiries are in Hunan students ' names, age, faculties and subjects and scores

Mysql> Select Student.id,name,sex,2017-brith as Age,deparment,c_name,grade

From Student,score

Where address like ' Hunan% '

and

student.id=score.stu_id;

This article is from the "It Life" blog, so be sure to keep this source http://dingxue.blog.51cto.com/12290895/1973552

mysql====== Exercises (1)