MySQL3:基础总结训练

训练

1、实现以下功能：

• 创建以下要求的数据表

列名	类型	备注
id	int	不为空 & 自增 & 主键
name	varchar(32)	不为空
password	varchar(64)	不为空
gender	char(1)	不为空，仅能输入：男、女
email	varchar(64)	可以为空
amount	decimal(10,2)	不为空 & 默认值为：0
ctime	datetime	时间

• 插入任意几条数据
• 将id>3的所有性别修改为 男
• 查询amount>1000的所有用户
• 在原先的amount的基础上+1000
• 删除性别为女的所有数据

2、代码：

• 创建数据表：

create table mgsys(
    id int not null primary key auto_increment, 
    name varchar(32) not null,
    password varchar(64) not null,
    gender char(1) not null default '男'check(gender in ('男', '女')),
    email varchar(64),
    amount decimal(10,2) not null default 0,
    ctime datetime
)default charset=utf8;

• 类似功能：

mysql> insert into mgsys(id,name,password,gender,email,amount) values(001,"joywon","12345","男","123@gmail.com",12345.67);
Query OK, 1 row affected (0.01 sec)


mysql> select * from mgsys;
+----+--------+----------+--------+---------------+----------+-------+
| id | name   | password | gender | email         | amount   | ctime |
+----+--------+----------+--------+---------------+----------+-------+
|  1 | joywon | 12345    | 男     | 123@gmail.com | 12345.67 | NULL  |
+----+--------+----------+--------+---------------+----------+-------+
1 row in set (0.01 sec)


mysql> update mgsys set ctime="2022-02-15 16:00:00";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from mgsys;
+----+--------+----------+--------+---------------+----------+---------------------+
| id | name   | password | gender | email         | amount   | ctime               |
+----+--------+----------+--------+---------------+----------+---------------------+
|  1 | joywon | 12345    | 男     | 123@gmail.com | 12345.67 | 2022-02-15 16:00:00 |
+----+--------+----------+--------+---------------+----------+---------------------+
1 row in set (0.00 sec)


mysql> insert into mgsys(id,name,password,gender,email,amount,ctime)
    -> values(002,"name1","11111","女","dsa@gmail.com",1232.12,"2022-01-01 12:00:00"),
    -> (003,"name2","11111","女","dsa@gmail.com",3123.12,"2022-01-02 12:00:00");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from mgsys;
+----+--------+----------+--------+---------------+----------+---------------------+
| id | name   | password | gender | email         | amount   | ctime               |
+----+--------+----------+--------+---------------+----------+---------------------+
|  1 | joywon | 12345    | 男     | 123@gmail.com | 12345.67 | 2022-02-15 16:00:00 |
|  2 | name1  | 11111    | 女     | dsa@gmail.com |  1232.12 | 2022-01-01 12:00:00 |
|  3 | name2  | 11111    | 女     | dsa@gmail.com |  3123.12 | 2022-01-02 12:00:00 |
+----+--------+----------+--------+---------------+----------+---------------------+
3 rows in set (0.01 sec)


mysql> select * from mgsys where id>=2;
+----+-------+----------+--------+---------------+---------+---------------------+
| id | name  | password | gender | email         | amount  | ctime               |
+----+-------+----------+--------+---------------+---------+---------------------+
|  2 | name1 | 11111    | 女     | dsa@gmail.com | 1232.12 | 2022-01-01 12:00:00 |
|  3 | name2 | 11111    | 女     | dsa@gmail.com | 3123.12 | 2022-01-02 12:00:00 |
+----+-------+----------+--------+---------------+---------+---------------------+
2 rows in set (0.01 sec)


mysql> select name from mgsys where amount>3000;
+--------+
| name   |
+--------+
| joywon |
| name2  |
+--------+
2 rows in set (0.01 sec)


mysql> update mgsys set amount=amount+1000;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0


mysql> select * from mgsys;
+----+--------+----------+--------+---------------+----------+---------------------+
| id | name   | password | gender | email         | amount   | ctime               |
+----+--------+----------+--------+---------------+----------+---------------------+
|  1 | joywon | 12345    | 男     | 123@gmail.com | 13345.67 | 2022-02-15 16:00:00 |
|  2 | name1  | 11111    | 女     | dsa@gmail.com |  2232.12 | 2022-01-01 12:00:00 |
|  3 | name2  | 11111    | 女     | dsa@gmail.com |  4123.12 | 2022-01-02 12:00:00 |
+----+--------+----------+--------+---------------+----------+---------------------+
3 rows in set (0.00 sec)


mysql> delete from mgsys where name="name1";
Query OK, 1 row affected (0.01 sec)


mysql> select * from mgsys;
+----+--------+----------+--------+---------------+----------+---------------------+
| id | name   | password | gender | email         | amount   | ctime               |
+----+--------+----------+--------+---------------+----------+---------------------+
|  1 | joywon | 12345    | 男     | 123@gmail.com | 13345.67 | 2022-02-15 16:00:00 |
|  3 | name2  | 11111    | 女     | dsa@gmail.com |  4123.12 | 2022-01-02 12:00:00 |
+----+--------+----------+--------+---------------+----------+---------------------+
2 rows in set (0.00 sec)