题目要求:
- 1.创建mydb2库,指定字符集为utf8,并使用
- 2.创建员工表emp,字段:编号id,姓名name,工资salary,部门department
- 3.插入奖金字段comm 在部门名的前面
- 4.修改工资salarty到最后
- 5.插入刘关张三人,工资分别为5000,4000,3000,部门三国部,编号123
- 6.插入取经四人组,工资分别是800,700,600,500,部门取经部,编号4567
- 7.修改唐僧名字为玉帝哥哥
- 8.插入工作地点字段在部门名的后面
- 9.修改三国部的地点在蜀国
- 10.删除刘备
- 11.修改工资低于700的工资为1000
- 12.删除取经部的员工
- 13.删除所有表
- 14.删除所有库
1.创建mydb2库,指定字符集为utf8,并使用
mysql> create database mydb2 character set utf8; -创建mydb2库,指定字符集为utf8
Query OK, 1 row affected (0.00 sec)
mysql> use mydb2; -并使用
Database changed
如需获取所有库,查看是否已创建,可以看到mydb2库已创建
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mydb1 |
| mydb2 |
| mysql |
| performance_schema |
| person |
| test |
+--------------------+
8 rows in set (0.00 sec)
2.创建员工表emp,字段:编号id,姓名name,工资salary,部门department
注意:需要在使用库的情况下创建表
mysql> create table emp(id int,name varchar(30),salary int,department varchar(30))engine=myisam charset=utf8;
Query OK, 0 rows affected (0.01 sec)
3.插入奖金字段comm 在部门名的前面
mysql> use mydb2; -使用mydb2库
Database changed
mysql> alter table emp add comm int after department; -在表中创建字段comm并放在部门后面
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp; -查看表
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
| department | varchar(30) | YES | | NULL | |
| comm | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
4.修改工资salarty到最后
mysql> alter table emp modify salary int after comm; -修改已有字段的位置用modify关键字
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp; -可以看到操作成功,salary被换到最后位置
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| department | varchar(30) | YES | | NULL | |
| comm | int(11) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
5.插入刘关张三人,工资分别为5000,4000,3000,部门三国部,编号123
6.插入取经四人组,工资分别是800,700,600,500,部门取经部,编号4567
千万注意大小写字符转换
mysql> insert into emp values -添加多行数据
-> (1,'刘备','三国部',0,5000),
-> (2,'关羽','三国部',0,4000),
-> (3,'张飞','三国部',0,3000),
-> (4,'唐僧','取经部',0,800),
-> (5,'悟空','取经部',0,700),
-> (6,'悟净','取经部',0,600),
-> (7,'悟能','取经部',0,500);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select*from emp; -遍历表中信息
+------+------+------------+------+--------+
| id | name | department | comm | salary |
+------+------+------------+------+--------+
| 1 | 刘备 | 三国部 | 0 | 5000 |
| 2 | 关羽 | 三国部 | 0 | 4000 |
| 3 | 张飞 | 三国部 | 0 | 3000 |
| 4 | 唐僧 | 取经部 | 0 | 800 |
| 5 | 悟空 | 取经部 | 0 | 700 |
| 6 | 悟净 | 取经部 | 0 | 600 |
| 7 | 悟能 | 取经部 | 0 | 500 |
+------+------+------------+------+--------+
7 rows in set (0.00 sec)
7.修改唐僧名字为玉帝哥哥
mysql> update emp set name="玉帝哥哥" where name="唐僧";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select*from emp;
+------+----------+------------+------+--------+
| id | name | department | comm | salary |
+------+----------+------------+------+--------+
| 1 | 刘备 | 三国部 | 0 | 5000 |
| 2 | 关羽 | 三国部 | 0 | 4000 |
| 3 | 张飞 | 三国部 | 0 | 3000 |
| 4 | 玉帝哥哥 | 取经部 | 0 | 800 |
| 5 | 悟空 | 取经部 | 0 | 700 |
| 6 | 悟净 | 取经部 | 0 | 600 |
| 7 | 悟能 | 取经部 | 0 | 500 |
+------+----------+------------+------+--------+
7 rows in set (0.00 sec)
8.插入工作地点字段在部门名的后面
这里我忘记把他设置在department后面了
mysql> alter table emp add adddress varchar(30);
Query OK, 7 rows affected (0.05 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> desc emp;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| department | varchar(30) | YES | | NULL | |
| comm | int(11) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
| adddress | varchar(30) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
9.修改三国部的地点在蜀国
mysql> update emp set adddress="蜀国" where department="三国部";
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select*from emp;
+------+----------+------------+------+--------+----------+
| id | name | department | comm | salary | adddress |
+------+----------+------------+------+--------+----------+
| 1 | 刘备 | 三国部 | 0 | 5000 | 蜀国 |
| 2 | 关羽 | 三国部 | 0 | 4000 | 蜀国 |
| 3 | 张飞 | 三国部 | 0 | 3000 | 蜀国 |
| 4 | 玉帝哥哥 | 取经部 | 0 | 800 | NULL |
| 5 | 悟空 | 取经部 | 0 | 700 | NULL |
| 6 | 悟净 | 取经部 | 0 | 600 | NULL |
| 7 | 悟能 | 取经部 | 0 | 500 | NULL |
+------+----------+------------+------+--------+----------+
7 rows in set (0.00 sec)
10.删除刘备
mysql> delete from emp where name="刘备";
Query OK, 1 row affected (0.01 sec)
mysql> select*from emp;
+------+----------+------------+------+--------+----------+
| id | name | department | comm | salary | adddress |
+------+----------+------------+------+--------+----------+
| 2 | 关羽 | 三国部 | 0 | 4000 | 蜀国 |
| 3 | 张飞 | 三国部 | 0 | 3000 | 蜀国 |
| 4 | 玉帝哥哥 | 取经部 | 0 | 800 | NULL |
| 5 | 悟空 | 取经部 | 0 | 700 | NULL |
| 6 | 悟净 | 取经部 | 0 | 600 | NULL |
| 7 | 悟能 | 取经部 | 0 | 500 | NULL |
+------+----------+------------+------+--------+----------+
6 rows in set (0.00 sec)
11.修改工资低于700的工资为1000
mysql> update emp set salary=1000 where salary<700;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select*from emp;
+------+----------+------------+------+--------+----------+
| id | name | department | comm | salary | adddress |
+------+----------+------------+------+--------+----------+
| 2 | 关羽 | 三国部 | 0 | 4000 | 蜀国 |
| 3 | 张飞 | 三国部 | 0 | 3000 | 蜀国 |
| 4 | 玉帝哥哥 | 取经部 | 0 | 800 | NULL |
| 5 | 悟空 | 取经部 | 0 | 700 | NULL |
| 6 | 悟净 | 取经部 | 0 | 1000 | NULL |
| 7 | 悟能 | 取经部 | 0 | 1000 | NULL |
+------+----------+------------+------+--------+----------+
6 rows in set (0.00 sec)
12.删除取经部的员工
mysql> delete from emp where department="取经部";
Query OK, 4 rows affected (0.00 sec)
mysql> select*from emp;
+------+------+------------+------+--------+----------+
| id | name | department | comm | salary | adddress |
+------+------+------------+------+--------+----------+
| 2 | 关羽 | 三国部 | 0 | 4000 | 蜀国 |
| 3 | 张飞 | 三国部 | 0 | 3000 | 蜀国 |
+------+------+------------+------+--------+----------+
2 rows in set (0.00 sec)
-
13.删除所有表
-
mysql> drop table emp; Query OK, 0 rows affected (0.01 sec)
-
14.删除所有库
mysql> drop database mydb2;
Query OK, 0 rows affected (0.02 sec)
mysql> show databases; -可以看到,库已删除
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mydb1 |
| mysql |
| performance_schema |
| person |
| test |
+--------------------+
7 rows in set (0.00 sec)
错误处理:
无法写入错误
可能是库和表的编码名不一致导致的,如创建库时没有指定或用了默认编码,创建表时编码格式不一样。一般报错会在 " ' " 的后面提示报错位置,检查是否有拼写错误
再者就是中英文符号使用错误,重新输入就好
小技巧,上下方向键可以快速切换之前输入过的语句
下面附赠笔记