自贡哪家做网站的好,网站的基础服务,定制网站开发,引流人脉推广软件目录 一、基本信息查看1. 表描述二、表操作1. 查看建表语句2.查看表3. 创建表4. 更新表5. 删除表6. 重命名表三、索引操作1. 查看索引2. 创建索引3. 修改索引4. 删除索引四、记录操作1. 查询记录2. 添加记录3. 更新记录4. 删除记录一、基本信息查看 1. 表描述 DESCRIBE table_n… 目录 一、基本信息查看1. 表描述二、表操作1. 查看建表语句2.查看表3. 创建表4. 更新表5. 删除表6. 重命名表三、索引操作1. 查看索引2. 创建索引3. 修改索引4. 删除索引四、记录操作1. 查询记录2. 添加记录3. 更新记录4. 删除记录 一、基本信息查看 1. 表描述 DESCRIBE table_name; 二、表操作 1. 查看建表语句 SHOW CREATE TABLE table_name; 2.查看表 SHOW TABLES; 3. 创建表 CREATE TABLE table_name (c_id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 唯一ID,c_is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT 逻辑删除,c_update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 更新时间,c_create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY (c_id)
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENTtable_name CREATE TABLE IF NOT EXISTS table_name (c_id bigint(20) unsigned NOT NULL COMMENT 唯一ID,c_uid char(11) NOT NULL DEFAULT COMMENT uid列,c_char char(11) NOT NULL DEFAULT COMMENT char列,c_varchar1 varchar(30) NOT NULL DEFAULT COMMENT varchar列,c_varchar2 varchar(300) CHARACTER SET utf8mb4 NOT NULL DEFAULT COMMENT varchar列单独设置字符集,c_text text CHARACTER SET utf8mb4 NOT NULL COMMENT text列单独设置字符集,c_tinyint tinyint(1) NOT NULL DEFAULT 0 COMMENT tinyint当枚举用(0A 1B 2C),c_enum enum(0,1) NOT NULL DEFAULT 0 COMMENT enum枚举用,c_json json NOT NULL COMMENT json类型列,c_is_deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT 逻辑删除,c_update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 更新时间,c_create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY (c_id),KEY inx_c_uid (c_uid) USING BTREE,UNIQUE KEY udx_c1_c2 (c_char,c_tinyint)
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENTtable_name; 4. 更新表 增加列ALTER TABLE table_name ADD c_column tinyint(1) not null DEFAULT 0 COMMENT 新增列; 修改列ALTER TABLE table_name MODIFY COLUMN c_varchar1 VARCHAR ( 300 ) NOT NULL DEFAULT COMMENT 标识符 AFTER c_varchar2; 删除列ALTER TABLE table_name DROP COLUMN c_column; 5. 删除表 DROP TABLE if EXISTS table_name; 6. 重命名表 RENAME TABLE table_name TO table_name_2; 三、索引操作 1. 查看索引 SHOW INDEX FROM table_name [FROM db_name] 2. 创建索引 语法CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON table_name (index_col_name, ...) CREATE UNIQUE INDEX idx_uid ON table_name (c_uid);-- TEXT和BLOB要指定长度
CREATE UNIQUE INDEX idx_text ON table_name (c_text(6)); ALTER TABLE方式ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type] ALTER TABLE table_name ADD PRIMARY KEY ( c_uid )ALTER TABLE table_name ADD INDEX udx_c1_c2 ( c_char(n), c_tinyint ) USING BTREE; 3. 修改索引 ALTER TABLE table_name DROP INDEX udx_c1_c2,
ADD INDEX udx_c1_c2 ( c_char, c_tinyint, c_is_deleted ) USING HASH; 4. 删除索引 ALTER TABLE table_name DROP PRIMARY KEY;DROP INDEX idx_name ON table_name;ALTER TABLE table_name DROP INDEX udx_c1_c2; 四、记录操作 1. 查询记录 简单查询子句顺序SELETC、 FROM、 WHERE、 GROUP BY、 HAVING、 ORDER BY、 LIMIT SELECT * FROM table_name WHERE ... ORDER BY c_column1,c_column2 DESC; 模糊查询特殊字符要转义% _SELECT * FROM table_name WHERE c_column LIKE CONCAT(first,second,third);SELECT * FROM table_name WHERE c_column LIKE CONCAT(%,_,%); 分组查询 WHERE过滤行, HAVING过滤分组SELECT GROUP_CONCAT(c_column) FROM table_name WHERE c_is_deleted0 GROUP BY c_column1, c_column2 HAVING COUNT(c_column3) 2;-- 可能报错incompatible with sql_modeonly_full_group_by
SELECT GROUP_CONCAT(c_column) FROM table_name WHERE c_is_deleted0 GROUP BY c_column1, c_column2 HAVING COUNT(c_column3) 2 ORDER BY c_order DESC;SELECT GROUP_CONCAT(c_id),COUNT(*) FROM table_name GROUP BY c_type WITH ROLLUP;分页查询 检索出来的第一行为行0而不是行1。-- 查询两条记录开始行1
SELECT * FROM table_name LIMIT 1, 2; 计算字段和函数1计算字段 SELECT 11 AS result;SELECT CONCAT(first, c_column, third) AS c_alias_name FROM table_name;SELECT CONCAT(first, TRIM(c_column), third) AS c_alias_name FROM table_name; 2处理函数 SELECT UUID() AS uid;SELECT UPPER(a);SELECT DATE(c_create_time) AS date, TIME(c_create_time) AS time FROM table_name; 3聚集函数 SELECT COUNT( * ),MIN( c_column ),MAX( c_column ),AVG( c_column )
FROM table_name;SELECT SUM( c_column1 * c_column2 )
FROM table_name; 子查询SELECT *
FROM table_name1
WHERE c_column IN ( SELECT c_column FROM table_name2WHERE c_is_deleted 1 );-- 计算字段作为子查询
SELECT table_name1.c_name,( SELECT COUNT( * ) FROM table_name2 WHERE table_name2.c_key table_name1.c_key ) AS nums
FROM table_name1
WHERE c_is_deleted 0; 联结查询(1) 自联结 SELECT t1.c_id, t1.c_name
FROM table_name AS t1, table_name AS t2
WHERE t1.c_id t2.c_id
AND t2.c_key ; (2) 自然联结 SELECT t1.c_id, t1.c_name
FROM table_name1 AS t1, table_name2 AS t2
WHERE t1.c_id t2.c_id
AND t2.c_key ; (3) 外部联结 SELECTtable_name1.c_id,table_name1.c_name,table_name2.c_name
FROM table_name1
LEFT JOIN table_name2 ON table_name2.c_key table_name1.c_key
WHERE ...; 查询结果case when then else end用法(1) 语法 --简单Case函数
CASE sex WHEN 1 THEN 男 WHEN 2 THEN 女 ELSE 其他
END --Case搜索函数,
CASE WHEN sex 1 THEN 男 WHEN sex 2 THEN 女 ELSE 其他
END (2) 示例 SELECT name,
CASE name WHEN sam THEN yong WHEN lee THEN handsome ELSE good
END AS alias
FROM table_name;SELECT name,
CASEWHEN birthday 1981 THEN old WHEN birthday 1988 THEN yong ELSE ok
END AS yorn
FROM table_name; 2. 添加记录 简单插入INSERT INTO table_name ( column1, column2 ) VALUES ( value1, value2 ); 批量插入INSERT INTO table_name ( column1, column2 )
VALUES( value1, value2 ),( value1, value2 );INSERT SELECT插入INSERT INTO table_name ( column1, column2 )SELECT column1, column2 FROM table_name WHERE c_id1; 3. 更新记录 UPDATE table_name
SET c_varchar1 string,c_update_time CURRENT_TIMESTAMP
WHERE c_id 1; 4. 删除记录 DELETE方式DELETE FROM table_name WHERE c_id1; TRUNCATE方式-清空表-慎用 -- 删除原表再新建表TRUNCATE TABLE table_name转载于:https://www.cnblogs.com/flylinran/p/10171508.html
