MySQL——第四次作业

创建数据表grade：

CREATE TABLE grade(
    id INT NOT NULL,
    sex CHAR(1),
    firstname VARCHAR(20) NOT NULL,
    lastname VARCHAR(20) NOT NULL,
    english FLOAT,
    math FLOAT,
    chinese FLOAT
);

向数据表grade中插入几条数据：

INSERT INTO grade
VALUES (1,'m','John','Smith',88.0,85.0,82.0),
(2,'f','Adam','Smith',76.0,78.0,90.0),
(3,'m','Allen','William',88.0,92.0,95.0),
(4,'m','George','William',62.0,58.0,72.0),
(5,'f','Alice','Davis',89.0,94.0,98.0),
(6,'m','Kevin','Miller',77.0,88.0,99.0),
(7,'f','Helen','Davis',79.0,83.0,91.0),
(8,'m','Andrew','Johnson',81.0,86.0,88.0);

1、查询所有字段 

mysql> select * from grade;
+----+------+-----------+----------+---------+------+---------+
| id | sex  | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
|  1 | m    | John      | Smith    |      88 |   85 |      82 |
|  2 | f    | Adam      | Smith    |      76 |   78 |      90 |
|  3 | m    | Allen     | William  |      88 |   92 |      95 |
|  4 | m    | George    | William  |      62 |   58 |      72 |
|  5 | f    | Alice     | Davis    |      89 |   94 |      98 |
|  6 | m    | Kevin     | Miller   |      77 |   88 |      99 |
|  7 | f    | Helen     | Davis    |      79 |   83 |      91 |
|  8 | m    | Andrew    | Johnson  |      81 |   86 |      88 |
+----+------+-----------+----------+---------+------+---------+
8 rows in set (0.00 sec)

2、查询grade表中的id,firstname,lastname字段

mysql> select id,firstname,lastname from grade;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | John      | Smith    |
|  2 | Adam      | Smith    |
|  3 | Allen     | William  |
|  4 | George    | William  |
|  5 | Alice     | Davis    |
|  6 | Kevin     | Miller   |
|  7 | Helen     | Davis    |
|  8 | Andrew    | Johnson  |
+----+-----------+----------+
8 rows in set (0.00 sec)

3、查询grade表中id大于4的学生姓名

mysql> select firstname,lastname from grade
    -> where id > 4;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Alice     | Davis    |
| Kevin     | Miller   |
| Helen     | Davis    |
| Andrew    | Johnson  |
+-----------+----------+
4 rows in set (0.00 sec)

4、查询grade表中女生的记录

mysql> select * from grade
    -> where sex='f';
+----+------+-----------+----------+---------+------+---------+
| id | sex  | firstname | lastname | english | math | chinese |
+----+------+-----------+----------+---------+------+---------+
|  2 | f    | Adam      | Smith    |      76 |   78 |      90 |
|  5 | f    | Alice     | Davis    |      89 |   94 |      98 |
|  7 | f    | Helen     | Davis    |      79 |   83 |      91 |
+----+------+-----------+----------+---------&#