mysql基础操作

一、选择数据库

连接到mysql服务器时，是没有选中任何数据库的，需要手动选择打开：

USE databaseName;

二、了解数据库/表相关信息

SHOW DATABASES ;//查看所有数据库
SHOW TABLES;//查某个数据库下的所有表，必须先选择了数据库
SHOW COLUMNS FROM tableName;//查询某个表中的所有字段,一个字段占一行，中包含字段的基本描述信息
SHOW STATUS;//显示广泛的服务器状态信息
SHOW CREATE DATABASE databaseName;//显示创建特定数据库的sql语句
SHOW CREATE TABLE tableName;//显示特定数据表的建表sql语句
SHOW GRANTS;//显示授予用户（所有用户或全部用户）的安全权限
SHOW ERRORS/WARNINGS;//显示服务器错误或警告信息
select version();//查询数据库版本号
...................................................................................

DESC tbName;//查看表的定义，输入信息不够全
SHOW CREATE TABLE tbName;//查看更全的建表语句

三、创建/删除数据库

CREATE DATABASE dbName;//创建数据库
//例：
CREATE DATABASE myDataBase
...................................................................................


DROP database dbName;//删除数据库

四、创建/删除表

CREATE TABLE tbName(
    column1 type constraints,
    column2 type constraints,
    column3 type constraints
); //创建表
//例：
CREATE TABLE tbName (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(200) NOT NULL DEFAULT '' COMMENT '名称',
  `config_type` varchar(50) NOT NULL DEFAULT '' COMMENT '配置类型',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  `state` tinyint(4) NOT NULL DEFAULT '1' COMMENT '启用状态0否,1是',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
   CURRENT_TIMESTAMP COMMENT '更新时间',
  `operator` int(11) NOT NULL DEFAULT '0' COMMENT '操作人',
  `upper_limit` bigint(20) NOT NULL DEFAULT '0' COMMENT '上限金额,单位:分',
  `lower_limit` bigint(20) NOT NULL DEFAULT '0' COMMENT '下限金额,单位:分',
  `remark` varchar(2000) NOT NULL DEFAULT '' COMMENT '备注信息',
  PRIMARY KEY (`id`),
  KEY `idx_type_version` (`config_type`,`version`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='xx表';
...................................................................................


DROP TABLE tbName;//删除表

五、修改表

一、修改某列类型
ALTER TABLE tableName MODIFY [COLUMN] column_definition [FIRST|AFTER column_name]
例如：
ALTER TABLE myTable MODIFY [COLUMN] user_name varchar(200);//将user_name类型修改为varchar(200)

二、增加表字段
ALTER TABLE tableName ADD [COLUMN] column_definition [FIRST|AFTER column_name]
例如：
ALTER TABLE myTable ADD [COLUMN] age int(100);//增加age字段,其中COLUMN关键字可写可不写

三、删除表字段
ALTER TABLE tableName DROP [COLUMN]column_name;
例如：
ALTER TABLE myTable DROP COLUMN age;//删除age字段

四、字段改名
ALTER TABLE tableName CHANGE [COLUMN] old_colomn  column_definition [FIRST|AFTER colmn_name]
例如：
ALTER TABLE myTable CHANGE [COLUMN] age age1 int(4);//将age改名为age1,同时修改类型为int（4）
注意：
change和modify都可以修改修改表定义，不同的是change后面需要写两次列名，不方便，但是change的优点是可以修改列名称，modify不能。

五、修改字段排列顺序
前面的修改语法中，都有一个可选项[FIRST | （AFTER column_name）]，指定在某列前面还是后面做修改，ADD默认加在表的最后，MODIFY和CHANGE不改变列的位置。
例如：
ALTER TABLE myTable ADD COLUMN age int(2) AFTER name;//在name列后增加age
ALTER TABLE myTable MODIFY COLUMN sex int(1) FIRST ;//修改sex放在表的第一列
注意：CHANGE|FIRST|AFTER COLUMN这些字段只适用于mysql。其他数据不一定适用。

六、修改表名
ALTER TABLE tbName RENAME [TO] new_tableName;
例如：
ALTER TABLE myTable RENAME [TO] yourTable;//将表名myTable修改为yourTable

六、操作表

一、插入数据：
INSERT INTO tableName (filed1,filed2,...,filedn) VALUES(value1,value2,...,valuen);
也可以不指定字段名，此时VALUES后面的字段值必须与表的字段顺序一一对应：
INSTER INTO tableName VALUES(filed1,...,filedn);
可空字段、不可空但是有默认值字段、自增字段可以不在insert后的字段列表中出现，VALUES后面只写对应字段名的值。这些没写的字段自动设置为NULL、默认值、自增的下一个数字。
还可以一次性插入多条数据，没条记录之间用逗号分割
INSERT INTO tableName (field1,field2,...,fieldn) 
VALUES(value1,value2,..,valuen),
(value1,value2,..,valuen),
(value1,value2,..,valuen);

二、修改数据
UPDATE tableName SET field1=value1,field2=value2,...,field3=value3;
mysql中可以同时更新多个表中的数据:多表更新的语法更多用于根据一个表的字段动态更新另一个表的字段。
UPDATE t1,t2,...,tn set t1.field1=value1,t2.field3=value2 ,...,tn.fieldn=valuen [WHERE CONDITION];

三、删除数据
DELETE FROM tableName [WHERE CONDITION];
mysql中可以一次性删除多个表的数据：
DELETE  t1,t2,..,tn FROM t1,t2,...,tn [WHERE CONDITION]
如果from后面的表名用别名，则delete后面也要用相应的别名，否则会报错。
例如：
DELETE a,b FROM user a,order b WHERE a.user_id=b.user_id;
四、查询数据
SELECT * FROM tableName [WHERE CONDITION] [ORDER BY field [DESC|ASC],field2[DESC|ASC]]  [LIMIT offset,row_count];
去重：
SELECT DISTINCT user_name From user;
聚合
SELECT * FROM tableName [WHERE CONDITION] [GROUP BY field1,field2,..,fieldn [WITH ROLLUP]] [HAVING where_conditon]
这里的WITH ROLLUP是可选参数，表明是否对分类聚合后的结果再进行汇总
having是聚合后过虑，where是聚合前过滤，尽量聚合前先过滤，减少聚合结果的数据量。
UNION和 UNION ALL的区别是，UNION ALL把所有记录整合一起返回，UNION 需要去重。

七、控制语句

1、授权
GRANT SELECT,INSERT ON dataBaseName.* TO "user" IDENTIFIED BY "123";
例如：
GRANT SELECT,INSERT ON myDataBase.* TO "lili" IDENTIFIED BY "123";//创建一个数据库用户yinyuxia,♟把数据库中所有表的select和insert权限复授权给她。
...................................................................................

2、收回权限
REVOKE SELECT ,INSTERT ON dataBaseName.* FROM "user";
例如：
收回插入权限：
REVOKE INSERT ON myDataBase.* FROM "lili";

八、索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX indexName [USING index_type]
ON tableName(index_column_name,...)
index_column_name:
col_name[(length)][ASC|DESC]    //创建索引
例：
CREATE INDEX idx_city_name ON city (city_id(20))
...................................................................................

DROP INDEX idx_name ON tableName;//删除索引

1. 添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2. 添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3. 添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4. 添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5. 添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

九、常用查询

查询remark中的某个字段值
select * from user where json_extract(remark,'$.age')= 28;
select * from user where remark->'$.age'=28;