mysql语句

常用sql语句

1、查询某个库信息

查看mysql某个库中所有表的大小和记录数

SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='database_name' order by length desc

说明：
TABLE_NAME ：表名字；
DATA_LENGTH ： 数据大小；
INDEX_LENGTH ：索引大小；
TABLE_ROWS ： 记录数量；
TABLE_SCHEMA ： 数据库名字； ENGINE：所使用的存储引擎；
information_schema：是mysql自带的，它提供了访问数据库元数据的方式，元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。
一个表占用空间的大小，相当于是 数据大小 + 索引大小；

2、查询进程：

show full processlist;

看到很多waiting的process，说明已经有卡住的proces了。查看status是否有ID被锁，如果被锁，杀死ID：

kill 3366

3、查询正在运行的事务

SELECT * FROM information_schema.INNODB_TRX

trx_mysql_thread_id字段为事务执行的线程

用户管理

1. 创建用户

create user '用户名'@'网络地址' identified by ’密码‘;

2. 用户授权

grant all on *.* to '用户名'@'网络地址';

3、查询用户权限

 show grants for 用户名;

库管理

1. 创建库

默认字符集为UTF8

create database 库名 charset utf8;

2. 查看库

show create database 库名\G

3. 修改库

只能修改数据库的默认字符编码

alter database 库名 charset gbk;

4. 删除库

删除数据库，所有的表及数据都会被删除，不可恢复

drop database 库名;

表管理

1.创建表

create table 表名(
name varchar(10),
gender char(1)
) engine=innodb charset=utf8;

2. 查看表

查看表结构

desc tb1;

查看建表语句

show create table tb1\G

3. 修改表

改表名

rename table 源表名 to 目标表名;

改表属性：
修改存储引擎(innodb或myisam)
修改字符编码(gbk或utf8)

alter table user
engine=myisam charset=gbk;

加字段
first 第一个
after 指定字段之后
默认新加字段排在已有字段后面

alter table 表名 add
新加字段 字段类型 first;

alter table 表名 add
新加字段 字段类型 after 指定字段;

alter table 表名 add(
新加字段1 字段类型,
新加字段2 字段类型
);

改字段名

alter table 表名
change 旧字段 新字段 字段类型;

改字段属性

alter table 表名
modify 字段 新字段类型;

改字段顺序

alter table 表名
modify 字段 字段类型 after 指定字段;

删除字段
删除的字段，对应数据也会删除，不可恢复

alter table user
drop column 字段;

4. 删除表

数据不可恢复

drop table 表名;
drop table if exists 表名;

5.截断表

数据不可恢复

truncate table user;

drop、truncate、delete的区别：
drop为DDL，操作立即生效，不能回滚。删除的是整个表（结构和数据）；
truncate为DDL，操作立即生效，不能回滚。清空表数据，不会触发触发器；
delete为DML，删除的每行数据都要记录在redo和undo表空间中以便进行回滚。

SQL

Structured Query Language结构化查询语言

1.DDL

数据定义语言
建库、建表

2.DML

数据操作语言
增删改

insert into 表名(字段1,...) values(字段值1,...)

delete from 表名 where 字段='字段值';

update set 字段1='字段值' where 字段2='字段值';

3.DQL

数据查询语言

select * from 表名 where 字段='字段值';

技术术语

数据库：database、schema
表：table
数据：row、record
字段：field

存储引擎

innodb（默认）
支持事务；
支持外键；
支持行级锁；
优势：有较多修改操作

myisam
不支持事务；
不支持外键；
支持表级锁；
优势：很少修改的表，大量查询效率高