mysql的增删改查

 增

 1.创建数据库

 create database Wuliu character set utf8;

创建一个名叫Wuliu的数据库字，字符集设置为utf8

2.创建数据表

mysql> use wuliu
Database changed
mysql> create table if not exists user (
    -> id int not null primary key comment '用户Id',
    -> name varchar(15) not null comment '用户名',
    -> nick_name varchar(15) not null comment '昵称',
    -> order_information text not null comment '订单信息'
    -> );
Query OK, 0 rows affected (0.03 sec)

3.在表中插入数据

mysql> insert into user (id,name,nick_name,order_information)
    -> values ('1','lxw','zzz','已支付');
Query OK, 1 row affected (0.01 sec)

//查看一下
mysql> select * from user;
+----+------+-----------+-------------------+
| id | name | nick_name | order_information |
+----+------+-----------+-------------------+
|  1 | lxw  | zzz       | 已支付            |
+----+------+-----------+-------------------+
1 row in set (0.00 sec)

 你也可以一次插入多行数据

mysql> insert into user (id,name,nick_name,order_information)
    -> values ('2','lsd','yyy','未支付'),
    -> ('3','lss','zzz','已支付');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

现在的表格内容 

mysql> select * from user;
+----+------+-----------+-------------------+
| id | name | nick_name | order_information |
+----+------+-----------+-------------------+
|  1 | lxw  | zzz       | 已支付            |
|  2 | lsd  | yyy       | 未支付            |
|  3 | lss  | zzz       | 已支付            |
+----+------+-----------+-------------------+
3 rows in set (0.00 sec)

删

删除表，这里我就不演示了

DELETE FROM user

删除表内部分数据

mysql> delete from user where id = 1;
Query OK, 1 row affected (0.01 sec)

当前表格数据

mysql> select * from user;
+----+------+-----------+-------------------+
| id | name | nick_name | order_information |
+----+------+-----------+-------------------+
|  2 | lsd  | yyy       | 未支付            |
|  3 | lss  | zzz       | 已支付            |
+----+------+-----------+-------------------+
2 rows in set (0.00 sec)

改

1.修改表名

mysql> alter table users rename to user;
Query OK, 0 rows affected (0.01 sec)

ALTER TABLE 表名 RENAME [TO|AS] 新表名

 2.更新数据

mysql> update user set name='ljn' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

//更新后数据
mysql> select * from user;
+----+------+-----------+-------------------+
| id | name | nick_name | order_information |
+----+------+-----------+-------------------+
|  2 | lsd  | yyy       | 未支付            |
|  3 | ljn  | zzz       | 已支付            |
+----+------+-----------+-------------------+
2 rows in set (0.01 sec)

查

mysql> select name from user where id = 2;
+------+
| name |
+------+
| lsd  |
+------+
1 row in set (0.00 sec)