Mysql笔记

数据库

 

查看数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE test;

删除数据库

DROP DATABASE test;

切换数据库

USE test;

查看表

SHOW TABLES;

查看表结构

DESC students;

查看创建表的SQL语句

SHOW CREATE TABLE students;

删除表

DROP TABLE students;

修改表，新增列

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

修改表，修改列

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

删除列

ALTER TABLE students DROP COLUMN birthday;

创建索引

ALTER TABLE students ADD INDEX idx_score (score);

创建唯一索引

ALTER TABLE studentsADD UNIQUE INDEX uni_name (name);

只创建唯一约束

ALTER TABLE students ADD CONSTRAINT uni_name UNIQUE (name);

 

创建组合索引

CREATE UNIQUE uk_appkey_nameen ON alibpms_corp_pm_node_info (`node_name_en`, `is_deleted`, `app_key`) USING BTREE COMMENT '唯一索引';

--删除索引

 

drop index uk_resouce_proc on alibpms_corp_pm_resource_proc_binding ;

 

uk_resouce_proc resource_uuid(60), process_id, process_code(60), type, is_deleted Unique 0 A 0 0 0  0 

---------------------------------------------------------

 

 

实用SQL语句

https://www.liaoxuefeng.com/wiki/1177760294764384/1246617682185952

插入或替换

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

插入或更新

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

插入或忽略

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

创建快照，即复制一份当前表的数据到一个新表

CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

 

写入查询结果集：即查询结果进行插入

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

强制使用索引

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

修改表字段

 ALTER TABLE alibpms_corp_pm_template MODIFY content VARCHAR(5000);

DDL报错

show full ddl;
remove ddl 1258242051408031744;