mysql——命令

命令

启动

# 启动暂停服务
net start/stop [服务名]
# 连接
mysql -h [ip] -P [端口] -u[username] -p[password]

导入导出

# 导出数据库的所有数据表。
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
# 导出一个表
mysqldump -u 用户名 -p 数据库名 表名>导出的文件名
# 选择一个数据库之后，还原整个数据库。
source file.sql;

# 导出为csv
SELECT * FROM [TABLE] INTO OUTFILE '[FILE]' [FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'];  
# 导入csv
LOAD DATA INFILE '[FILE]' INTO TABLE [TABLE] [FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'];

sql

分类：

DDL

数据库

# 新增
-- 默认字符集 utf8mb4
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARACTER SET 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE [IF EXISTS] 数据库名
# 查询全部数据库
SHOW DATABASES
# 查询当前数据库
SELECT DATABASE()
# 使用
USE 数据库名;
# 修改
ALTER DATABASE 数据库名 CHARACTER SET 'utf8';

# 查看所支持的存储引擎
show engines;
# 查看默认的存储引擎
show  variables  like '%storage_engine';
# 修改存储引擎
alter table [tablename] type|engine=[enginename];

表

# 查看所有表
SHOW TABLES [FROM 数据库名];
# 查询表结构
DESC [表名];
describe [tablename];
# 查询建表语句
SHOW CREATE TABLE 表名
#建表 
# auto_increment自增
create table [表名] ( 
	[字段名1] [类型1] [is null] [key] [default value] [extra] [comment '注释'],
	[字段名2] [类型2] [is null] [key] [default value] [extra] [comment '注释'],
	...
	[字段名n] [类型n] [is null] [key] [default value] [extra] [comment '注释'],
	primary key(字段名1),
	constraint 外键名 foreign key(外键字段名) references 主表(主表列名)
)[comment '注释'] [engine] [charset];
# 改表名
rename table [tablename] to [newtablename];
alter table [tablename] rename to [newtablename];
# 删表
DROP TABLE [IF EXISTS] 表名;
# 删表中数据（删表再重建表）
TRUNCATE TABLE 表名;

# 临时表
create temporary table [表名] ( 
	[字段名1] [类型1] [is null] [key] [default value] [extra] [comment],
	...
);

# 内存表
create table [表名] ( 
	[字段名1] [类型1] [is null] [key] [default value] [extra] [comment],
	...
)engine=memory;

列

# 新增列
# first 增加到第一列
ALTER TABLE [表名] ADD COLUMN [字段名] [类型(长度)] [comment '注释'] [after 字段名],ADD COLUMN ...;
# 删
ALTER TABLE table_name DROP COLUMN column1, DROP COLUMN column2, ...;
# 改
alter table [tablename] change [columnname] [newcolumnname] [类型(长度)] [comment '注释'] [约束];
alter table [tablename] modify [columnname] [类型(长度)];

自增

# 取消自增
alter table [tablename] change [columnname] [columnname] [type];
# 添加自增
alter table [tablename] modify [columnname] [type] auto_increment;
alter table [tablename] change [columnname] [columnname] [type] auto_increment;
# 修改自增长起始值
alter table [tablename] auto_increment=[value];

索引

# 增
alter table [tablename] add index [indexname](字段名1,字段名2…);
# 查看
show index from [tablename];
# 删
alter table [tablename] drop index [indexname];

外键

ALTER TABLE 表名 ADD constraint 外键名 foreign key(外键字段名) references 主表(主表列名) on update 行为 on delete 行为
ALTER TABLE 表名 DROP foreign key 外键名

DML

# 插入
# replace into ≈ insert into 删除旧的插入新的
insert into tablename (column1,column2,...) values(value1,value2,...),values(value1,value2,...),values(value1,value2,...);
insert into tablename values(value1,value2,...)

insert into tablename1 (column1,column2,...) select column1,column2,... from tablename2;
insert into tablename1 select * from tablename2;

insert into tablename set column1=value1,column2=value2...

# 删除
delete from [tablename] where [condition];

# 更新
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

DQL

/* 
执行顺序
开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果
聚合函数：count max min avg sum
*/
SELECT 列名称1 [AS 别名1], 列名称2 [AS 别名2] ...
FROM [表名称] 
WHERE[条件,不可用聚合函数] 
GROUP BY [分组字段列表，重复的只显示第一行]
HAVING [分组后条件列表,分组后的数据进行条件查询,先where后having,可用聚合函数] 
ORDER BY [排序字段列表, DESC降序，ASC升序，rand()随机] 
LIMIT [分页];

in：子查询 select  *  from  tablename  where  [字段名]  in [数据集/子查询]
LIMIT ：LIMIT [<offset>,] <row_count> | <row_count> OFFSET <offset>

distinct

去重,只能放在所选列的开头，其他列用GROUP BY

 SELECT distinct [列名称] ...

统计

count(distinct case where 条件 then 字段 end)
count(distinct xx,if(use_status=1,true,null))

执行效率

explain [select statement];
desc [select statement];

union

合并两个或多个select语句的结果集，并消去联合后表中的重复行。
结果集的列数相同（字段类型不要求）即可。union 后字段的名称以第一条 SQL 为准。
union只能作用于select结果集，不能直接作用于数据表，
union all保留重复行
排序

(select ... order by) union [all] (select ... order by) order by
内层查询的排序不起效，外层的order by起效
如需内层起效，给内层order by加limit

join

join只能作用于数据表，不能直接作用于select结果集

-- 内连接：a∩b
select * from a,b where a.id=b.id;
select * from a inner join b on a.id=b.id;

-- 外连接：
-- 左外a
select * from a left join b on a.id=b.id;
-- 右外b
select * from a right join b on a.id=b.id;

# 自连接：a连接a
select * from a left/right/inner join a on a.id1=a.id2;

DCL

函数

约束