数据库增删查改及创建数据表 - PHP培训九期线上班

数据库增删查改

1. 增加操作

INSERT INTO 表名 (字段1，字段2) VALUES (值1，值2);

例如：

INSERT INTO user (id,user,password) VALUES (5,'zhangsan','123465');

另外一种写法：

INSERT INTO 表名 字段1 = 值1,字段2 = 值2;

2.删除操作

DELETE FROM 表名 WHERE 条件1 = 值1;

条件也可以是 与、或

DELETE FROM 表名 WHERE 条件1 = 值1 AND 条件2 = 值2;
DELETE FROM 表名 WHERE 条件1 = 值1 OR 条件2 = 值2;

例如：

DELETE FROM user WHERE id = 1;
DELETE FROM user WHERE user = 'zhangsan' AND password = 123465;
DELETE FROM user WHERE user = 'zhangsan' OR password = 123465;

3.查询操作

SELECT 查询字段（全部查询为*） FROM 表名 [WHERE];

例如：

SELECT * FROM user;
SELECT * FROM user WHERE user = 'zhangsan';

4.更新操作

UPDATE 表名 SET 字段1 = 值1,字段2 = 值2 WHERE 条件1 = 值;

例如：

UPDATE user SET user = 'wangwu','password' = 'admin' WHERE id = 5;

创建数据表

CREATE TABLE user(
    id int unsigned not null auto_increment,
    username varchar(50) not null comment "用户名",
    password varchar(32) not null comment "用户密码",
    email varchar(50) not null comment "邮箱",
    iphone varchar(11) not null comment "手机号",
    name varchar(50) not null comment "昵称",
    avatar varchar(200) not null comment "头像",
    gender enum('男','女') not null comment "性别",
    create_time int not null comment "添加时间",
    update_time int not null comment "修改时间",
    primary key (id)
)engine = InnoDb default charset = utf8 comment "用户表";