alter，insert，update，delete基本sql语句

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)