数据库基本操作SQL语句

#删除test数据 防止重复创建 DROP DATABASE IF EXISTS test; #创建数据库test 设置编码为utf-8 CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; #查看已有的数据库 SHOW DATABASES; #切换数据库为test USE test; #创表前对表是否存在进行判断 存在则删除 DROP TABLE IF EXISTS paragraph; #创建表 CREATE TABLE paragraph ( id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘每一话’, chapter_id int(11) NOT NULL COMMENT ‘章节id’, content varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ‘内容’, img varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ‘背景图片’, order_num int(11) NOT NULL COMMENT ‘排序’, create_time datetime(0) NOT NULL COMMENT ‘创建时间’, update_time datetime(0) NOT NULL COMMENT ‘修改时间’, is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT ‘是否软删除’, PRIMARY KEY (id) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 71 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = ‘段落’ ROW_FORMAT = Dynamic; #查看表结构 DESC paragraph; #改变paragraph添加新列new_job类型varchar长度10; ALTER TABLE paragraph ADD COLUMN new_job varchar(10); #修改表名 ALTER TABLE paragraph RENAME test; #删除字段 ALTER TABLE test DROP COLUMN new_job; #修改表列类型（改类型） ALTER TABLE test MODIFY content int(11); #修改表列名和类型 ALTER TABLE test CHANGE content con int(10); #修改表列名和类型 ALTER TABLE test CHANGE COLUMN content con int(10);

#删除多余字段方便后面操作 ALTER TABLE test DROP COLUMN is_deleted; ALTER TABLE test DROP COLUMN create_time; ALTER TABLE test DROP COLUMN order_num; ALTER TABLE test DROP COLUMN update_time; ALTER TABLE test DROP COLUMN img; ALTER TABLE test DROP COLUMN chapter_id; #新增一条数据 INSERT INTO test (id,con) VALUES (1,1); #查询全表数据 SELECT * FROM test; #新增一条数据 INSERT INTO test VALUES (1,1); #添加一条数据 INSERT INTO test (con) VALUES (1); #添加测试数据 INSERT INTO test (con) VALUES (2),(123),(32),(123),(123),(1223),(3123),(123),(32); #查询id为1的数据 SELECT * FROM test WHERE id = 1; #查询con为123的数据 SELECT * FROM test WHERE con = 123; #查询con为123并且id为73的数据 SELECT * FROM test WHERE con = 123 AND id=73; #查询con为123或者con为1的数据 SELECT * FROM test WHERE con = 123 OR con=1; #查询con为123或者con为1或者为2的数据 SELECT * FROM test WHERE con IN(1,2,123); #查询con不为123或者con不为1或者不为2的数据 SELECT * FROM test WHERE con NOT IN(1,2,123); #查询指定字段 SELECT id,con FROM test; #查询指定字段 SELECT id FROM test; #查询插入 INSERT INTO test (con) SELECT con FROM test; #删除id为1的数据 DELETE FROM test WHERE id=1; #添加伪删除字段 ALTER TABLE test ADD COLUMN state tunyint(1); #修改id为2的con为123456 UPDATE test SET con=123456 WHERE id = 2; #设置所有状态为1 UPDATE test SET state=1; #伪删除 UPDATE test SET state=0 WHERE id=2; #伪删除查询 SELECT * FROM test WHERE state = 1;