Mysql之DML练习

最新推荐文章于 2024-07-25 16:06:56 发布

North433

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量107

点赞数 2

文章标签： mysql android adb

本文链接：https://blog.csdn.net/weixin_64341957/article/details/131610150

版权

创建数据表：

mysql> create table employee(
    -> id int primary key AUTO_INCREMENT,
    -> name varchar(20) not null,
    -> gender enum("男","女") default "男",
    -> salary float);
ERROR 1067 (42000): Invalid default value for 'gender'
mysql> create table employee( id int primary key AUTO_INCREMENT, name varchar(20) not null, gender enum("m","f") default "m", salary float);
Query OK, 0 rows affected (0.00 sec)

mysql> desc employee;
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int(11)       | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)   | NO   |     | NULL    |                |
| gender | enum('m','f') | YES  |     | m       |                |
| salary | float         | YES  |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+

插入数据：

mysql> select * from employee;
+----+----------+--------+--------+
| id | name     | gender | salary |
+----+----------+--------+--------+
|  1 | zhangsan | m      |   2000 |
|  2 | lisi     | m      |   1000 |
|  3 | wangwu   | f      |   4000 |
+----+----------+--------+--------+
3 rows in set (0.00 sec)

将所有员工薪水修改为5000元：

mysql> update employee
    -> set salary = 5000;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from employee;
+----+----------+--------+--------+
| id | name     | gender | salary |
+----+----------+--------+--------+
|  1 | zhangsan | m      |   5000 |
|  2 | lisi     | m      |   5000 |
|  3 | wangwu   | f      |   5000 |
+----+----------+--------+--------+
3 rows in set (0.00 sec)

将姓名为张三的员工薪水修改为3000元：

mysql> update employee set salary = 3000 where name = "zhangsan";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

将姓名为李四的员工薪水修改为4000元，gener改为女：

mysql> update employee set salary = 4000,gender="f" where name = "lisi";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+----------+--------+--------+
| id | name     | gender | salary |
+----+----------+--------+--------+
|  1 | zhangsan | m      |   3000 |
|  2 | lisi     | f      |   4000 |
|  3 | wangwu   | f      |   5000 |
+----+----------+--------+--------+
3 rows in set (0.00 sec)

将王五的薪水在原有基础上增加1000元：

mysql> update employee set salary = salary + 1000 where name = "wangwu";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+----------+--------+--------+
| id | name     | gender | salary |
+----+----------+--------+--------+
|  1 | zhangsan | m      |   3000 |
|  2 | lisi     | f      |   4000 |
|  3 | wangwu   | f      |   6000 |
+----+----------+--------+--------+
3 rows in set (0.00 sec)