MySql常用内容

连接字符串

Server=address;Database=dataBaseName;Uid=username;Pwd=password;

更多连接方式

查询指定schema下的所有表

查询表名和表注释

select table_name,table_comment from information_schema.tables where table_schema='schemaName'

查询表的字段信息

SELECT
    C.TABLE_SCHEMA AS '库名',
    T.TABLE_NAME AS '表名',
    T.TABLE_COMMENT AS '表注释',
    C.COLUMN_NAME AS '列名',
    C.COLUMN_COMMENT AS '列注释',
    C.ORDINAL_POSITION AS '列的排列顺序',
    C.COLUMN_DEFAULT AS '默认值',
    C.IS_NULLABLE AS '是否为空',
    C.DATA_TYPE AS '数据类型',
    C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    C.NUMERIC_PRECISION AS '数值精度(最大位数)',
    C.NUMERIC_SCALE AS '小数精度',
    C.COLUMN_TYPE AS 列类型,
    C.COLUMN_KEY 'KEY',
    C.EXTRA AS '额外说明'
FROM
    information_schema.`TABLES` T
LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE
    T.TABLE_SCHEMA = 'schemaName' 
AND T.TABLE_NAME='tableName'
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;

查询建表sql

show create table teacher

检查表是否存在

方法1

SELECT * 
FROM information_schema.tables
WHERE table_schema = 'yourdbName' 
    AND table_name = 'testtable'
LIMIT 1;

（默认table_schema 即数据库名称）
方法2

SELECT count(*)
FROM information_schema.tables
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')

参考资料：
Check if table exists without using “select from”

存在表则删除

drop table if exists tableXXX 

参考资料：
如果存在表则删除表然后创建Mysql

获得表的自增长id当前最大值

select max(id) from tableName

参考资料：
Mysql获取最大自增ID（auto_increment）的五种方式及其特点

获取表的字段列表

string conn= "Server=address;Database=dataBaseName;Uid=username;Pwd=password;";
using(var _con= new MySqlConnection(conn))
{
	var lst = new List<string>();
	var sql = "select * from " + tableName + " where 1<0";
	using (var cmd = new MySqlCommand(sql, _con))
	{
		using (var r = cmd.ExecuteReader())
		{
			for (int i = 0; i < r.FieldCount; ++i)
			{
				lst.Add(r.GetName(i));
			}
		}
	}
}

查询空间数据

WKT格式

SELECT ST_AsText(g) FROM geom;

WKB格式

SELECT ST_AsBinary(g) FROM geom;

参考资料:
11.4.8 Fetching Spatial Data

批量更新数据

update targetTableXXX ,(select * from tempTableXXX ) b set targetTableXXX.BM=b.BM where b.TEMP_ID=targetTableXXX.ID

--子查询Table2和Table3的数据，以更新Table1
UPDATE Table1 , 
(  select Table2.MainPro, sum(Table3.Pro1) Pro1Sum, count(Table3.Pro2) Pro2Count from Table2 LEFT JOIN Table3 on Table2.MainPro like CONCAT(Table3.Pro3,'%') GROUP BY Table3.Pro3  ) b 
set Table1.Pro1 = ..., Table1.Pro2 = ...
where  Table1.MainPro=b.MainPro

索引

创建普通索引

ALTER TABLE tableName ADD INDEX indexName (indexColumnName);

创建唯一索引

CREATE UNIQUE INDEX indexColumnName ON tableName(indexName);

删除普通索引&唯一索引

ALTER TABLE tableName drop INDEX indexName;

外键

创建外键

--eg.学校和教师，一对多关系
--则parentTableName为学校，parentColumnName为学校表的学校ID
--childTableName为教师，childColumnName为教师表的学校ID
--sql语句在教师表添加外键
ALTER TABLE childTableName ADD CONSTRAINT foreignKeyName FOREIGN KEY(childColumnName) REFERENCES parentTableName(parentColumnName);

删除外键

alter table childTableName drop foreign key foreignKeyName;

字段

向已有表新增字段

alter table teacher add  `字段名`  类型  COMMENT '注释' 
--eg.
alter table teacher add  `test`  bigint  DEFAULT NULL  COMMENT 'test' 

删除字段

alter table tableName drop column columnName
--eg.
alter table teacher drop column test

修改字段

alter  table 表名 modify column  `字段名`  类型 COMMENT '注释' 
--eg.
alter  table teacher modify column  `test`  varchar (200)  DEFAULT NULL  COMMENT '534534534' 

主键

新增主键（原表没有主键）

ALTER TABLE tableName ADD PRIMARY KEY(id);
--复合主键
ALTER TABLE tableName ADD PRIMARY KEY(id,id2);

新增主键（原表有主键）

ALTER TABLE tableName DROP PRIMARY KEY;
ALTER TABLE tableName ADD PRIMARY KEY(id);

特殊情况：原表有主键，且主键为自增长，则不能使用上述方式，正确写法如下：

alter table tableName drop primary key, add primary key(id,id2);

参考资料:
ALTER TABLE to add a composite primary key

分区

检查是否已存在分区

select *
from INFORMATION_SCHEMA.`PARTITIONS`
where table_schema='schemaName'
and table_name='tableName'