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

root
233
文章
0
评论
2021年6月22日23:45:48 评论 3493字阅读11分38秒

 

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)

 

 

 

 

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2021年6月22日23:45:48
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
Mysql体系结构 MySQL

Mysql体系结构

Mysql体系结构 数据库: 物理操作系统文件或其他形式文件类型的集合   数据库实例: 由数据库后台进程/线程以及一个共享内存区域组成 共享内存可以被运行的后台 进程/线程所共享 数据库实...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: