alter table语句追加,修改,或删除列的语法
增加列
1.员工表he的上增加一个titi列,varchar类型(要求在ww后面)
//增加列 mysql> select * from he; +---+------+----+ | a | b | ww | +---+------+----+ | 1 | 10 | | | 2 | 20 | | | 4 | 122 | | +---+------+----+ 3 rows in set (0.00 sec) mysql> alter table he add titi varchar(32) NOT NULL DEFAULT '' -> AFTER ww; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from he; +---+------+----+------+ | a | b | ww | titi | +---+------+----+------+ | 1 | 10 | | | | 2 | 20 | | | | 4 | 122 | | | +---+------+----+------+ 3 rows in set (0.11 sec)
2.修改titi列,使其长度为60
mysql> alter table he modify titi varchar(60) NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.删除sex列
mysql> alter table he drop titi; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from he; +---+------+----+ | a | b | ww | +---+------+----+ | 1 | 10 | | | 2 | 20 | | | 4 | 122 | | +---+------+----+ 3 rows in set (0.00 sec)
4.表名改为employee
mysql> rename table he to employee;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_kaola |
+-----------------+
| employee |
| orders |
| user |
| ww |
| z |
+-----------------+
5 rows in set (0.00 sec)
5.修改表的字符集为utf-8mb4
mysql> alter table employee CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
6.列名name修改为user_name
mysql> alter table employee change ww user_name varchar(64) NOT NULL DEFAULT ''; Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from employee; +---+------+-----------+ | a | b | user_name | +---+------+-----------+ | 1 | 10 | | | 2 | 20 | | | 4 | 122 | | +---+------+-----------+ 3 rows in set (0.00 sec)
insert语句
- 插入的数据应与数据类型相同
- 数据的长度应在列的规定范围内
- 在values中列出的数据位置必须与被加入的列的排列位置相对应
- 字符和日期数据应包含在单引号中
- 列可以插入控制【前提是该字段允许为空】
- insert into table_name (列名..) values (), (), ()形式添加多条记录
- 如果是给表中的所有字段添加数据,可以不写前面的字段名称
- 默认值的使用,当不给某个字段值时,如果默认值就会添加,否则报错
1.创建一张商品表goods(id int,goods_name varchar(10),price double);
mysql> insert into employee (a,b,user_name) values(5,2, 'waewe' );
Query OK, 1 row affected (0.00 sec)
update语句
要求:在上面创建的employee表中修改表中的记录,//不加where默认修改所有
1.将所有员工的薪水修改为5000元
//不加where默认修改所有
mysql> update employee set user_name = 5000;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from employee;
+---+------+-----------+
| a | b | user_name |
+---+------+-----------+
| 1 | 10 | 5000 |
| 2 | 20 | 5000 |
| 4 | 122 | 5000 |
| 5 | 2 | 5000 |
+---+------+-----------+
4 rows in set (0.00 sec)
2.将id为2的员工薪水修改为3000元
mysql> update employee set user_name = 3000 where a = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+---+------+-----------+
| a | b | user_name |
+---+------+-----------+
| 1 | 10 | 5000 |
| 2 | 20 | 3000 |
| 4 | 122 | 5000 |
| 5 | 2 | 5000 |
+---+------+-----------+
4 rows in set (0.00 sec)
3.将id为10的薪水在原有基础上增加1000元
mysql> insert into employee values(10,55,'3000');
mysql> update employee set user_name = user_name + 1000 where a = 10;
mysql> select * from employee;
+----+------+-----------+
| a | b | user_name |
+----+------+-----------+
| 1 | 10 | 5000 |
| 2 | 20 | 3000 |
| 4 | 122 | 5000 |
| 5 | 2 | 5000 |
| 10 | 55 | 4000 |
+----+------+-----------+
5 rows in set (0.00 sec)
3.把一列数据变为空
delete语句
- 如果不使用where字句,将删除表中所有数据
- Delete语句不能删除某一列的值(可使用upadte 设置为null 或者 '')
- 使用delete语句仅删除记录,不删除表本身。如果删除表,使用drop table语。drop table 表名;
1.删除employee表中的 5 信息
mysql> select * from employee; +----+------+-----------+ | a | b | user_name | +----+------+-----------+ | 1 | 10 | 5000 | | 2 | 20 | 3000 | | 4 | 122 | 5000 | | 5 | 2 | 5000 | | 10 | 55 | 4000 | +----+------+-----------+ 5 rows in set (0.00 sec) mysql> delete from employee where a = 10; Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +---+------+-----------+ | a | b | user_name | +---+------+-----------+ | 1 | 10 | 5000 | | 2 | 20 | 3000 | | 4 | 122 | 5000 | | 5 | 2 | 5000 | +---+------+-----------+ 4 rows in set (0.00 sec)
2.删除表中所有的记录
mysql> delete from employee; Query OK, 4 rows affected (0.00 sec) mysql> select * from employee; Empty set (0.00 sec)
继续阅读

我的微信
这是我的微信扫一扫
评论