小练习(二)

题一:

mysql> create database day0913_homework_02;
Query OK, 1 row affected (0.00 sec)

mysql> use day0913_homework_02;
Database changed
mysql> create table shopping_info(
    -> id int primary key auto_increment,
    -> people char(5) not null,
    -> product char(5) not null,
    -> count int not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into shopping_info values
    -> (null,'A','甲',2),
    -> (null,'B','乙',4),
    -> (null,'C','丙',1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select people from shopping_info where count >=2;
+--------+
| people |
+--------+
| A      |
| B      |
+--------+
2 rows in set (0.00 sec)

mysql> select product from shopping_info where count =2;
+---------+
| product |
+---------+
| 甲      |
+---------+
1 row in set (0.00 sec)

mysql> select people from shopping_info where count =4;
+--------+
| people |
+--------+
| B      |
+--------+
1 row in set (0.00 sec)

mysql> update shopping_info set count = 10 where people ='A';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from shopping_info;
+----+--------+---------+-------+
| id | people | product | count |
+----+--------+---------+-------+
|  1 | A      | 甲      |    10 |
|  2 | B      | 乙      |     4 |
|  3 | C      | 丙      |     1 |
+----+--------+---------+-------+
3 rows in set (0.00 sec)

mysql> delete from shopping_info where count =1;
Query OK, 1 row affected (0.01 sec)

mysql> select *from shopping_info;
+----+--------+---------+-------+
| id | people | product | count |
+----+--------+---------+-------+
|  1 | A      | 甲      |    10 |
|  2 | B      | 乙      |     4 |
+----+--------+---------+-------+
2 rows in set (0.00 sec)

--------------------------------------------------------------------------

题二

mysql> create table student(
    -> id int primary key auto_increment,
    -> name char(5) not null,
    -> course char(5) default '语文',
    -> score int not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into student(name,score) values
    -> ('张三',81),
    -> ('李四',90),
    -> ('王五',49);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    81 |
|  2 | 李四   | 语文   |    90 |
|  3 | 王五   | 语文   |    49 |
+----+--------+--------+-------+
3 rows in set (0.00 sec)

mysql> select name from student where score >=60;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
+--------+
2 rows in set (0.00 sec)

mysql> select name from student where score >=90;
+--------+
| name   |
+--------+
| 李四   |
+--------+
1 row in set (0.00 sec)

mysql> select name,course from student where score =49;
+--------+--------+
| name   | course |
+--------+--------+
| 王五   | 语文   |
+--------+--------+
1 row in set (0.00 sec)

mysql> update student set score = 60 where name ='王五';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from student where name = '李四';
Query OK, 1 row affected (0.01 sec)

mysql> select *from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    81 |
|  3 | 王五   | 语文   |    60 |
+----+--------+--------+-------+
2 rows in set (0.00 sec)

---------------------------------------------------------------------------------

题三

mysql> create table student_2(
    -> id int primary key auto_increment,
    -> name char(5) not null,
    -> password int not null,
    -> age int not null,
    -> reg_time timestamp not null,
    -> weight float(60,2) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student_2 values
    -> (null,'hgq',123,20,now(),50),
    -> (null,'zkq',123,20,now(),40);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from student_2;
+----+------+----------+-----+---------------------+--------+
| id | name | password | age | reg_time            | weight |
+----+------+----------+-----+---------------------+--------+
|  1 | hgq  |      123 |  20 | 2018-09-13 19:38:10 |  50.00 |
|  2 | zkq  |      123 |  20 | 2018-09-13 19:38:10 |  40.00 |
+----+------+----------+-----+---------------------+--------+
2 rows in set (0.00 sec)