Mysql使用过程中经常用到的SQL

1.创建表

1.1普通表

create table if not exists center_point_tmp_fqw(
id int(11) comment '全局唯一id',
org_id varchar(20) default null comment '组织id',
center_point mediumblob comment '中心点',
primary key (id)
) engine=innodb default charset=utf8;

1.2主键字段自增表

create table if not exists center_point_tmp_fqw(
id int(11) not null auto_increment comment '全局唯一id',
org_id varchar(20) default null comment '组织id',
center_point mediumblob comment '中心点',
primary key (id)
) engine=innodb  auto_increment=1 default charset=utf8;

2.表数据导入

--1.字段之间用空格分割，同时每行记录每个字段都不为空

load data local infile "/data/1.txt" into table test1;

--2.一行记录只有一个字段，末尾没有分割符，或者每行记录的字段不全，有字段为空的情况

load data local infile "/data/2.txt" into table test2
fields terminated by ' '
lines terminated by '\r\n';

3.表数据插入

3.1插入数据不同方式

insert into tablename set fields=new-value where fields=old-value;
insert into tablename(列名称1,列名称2,列名称3) values(value1,value2,value3);

3.2向自增表插入数据

不指定初始id的值，会自动为id赋值

insert into center_point_tmp_fqw(org_id,center_point)
values('110003','123.5,22.6');

指定初始id的值，id值会从初始id值开始累加

set @bbb:=0;
insert into center_point_tmp_fqw(id,org_id,center_point)
select @bbb:=@bbb+1 as id,org_id,center_point 
from center_point_tmp1_fqw
;

4.表数据更新

update tablename set fields=new-value where fields=old-value;

update tablename set fields1=new-value,fields2=new-value;

--将表中一个字段的值赋值给另一个字段

update tablename set fields1=fields2;

5.表字段新增

alter table tablename add fields fields类型 defalut null;

6.表字段重命名

alter table tablename change fields newfields newfields类型;

7.表字段删除

alter table tablename drop fields;

8.修改表名

alter table tablename rename to newtablename;

9.在shell中如何对mysql表操作

#mysql连接信息
while read line;do
done < /mysql_connect.sh
mysql_ip=$ip
mysql_user=$user
mysql_pwd=$pwd
while read line;do
done < /mysql_connect.sh
mysql_ip_new=$newip
mysql_user_new=$newuser
mysql_pwd_new=$newpwd

单个sql语句

/bin/mysql -u${mysql_user} -p${mysql_pwd} -h${mysql_ip} databasename -Ne '单个sql语句' 
> /data/a.txt

多个sql语句

/bin/mysql -u${mysql_user} -p${mysql_pwd} -h${mysql_ip} <<EOF
use databasename;
'多个sql语句,每个语句之间用分号分隔';
EOF

10.按字符串字段进行排序，字段值是数字

select * from tablename order by fields1+0 desc;

11.命令行中执行.sql文件

source /data/test.sql

12.显示上一条语句执行的报错信息

show warnings

13 show 命令

--查看表名模糊匹配
show tables like '%表名%'

--查看所有存储过程
show procedure status where Db='databasename';
--查看存储过程创建过程
show create procedure 存储过程名;
--查看正在执行的sql语句,其中如果command为waid
--说明表被锁住了，info为执行某条语句的信息，id为进程。
--可以kill掉锁表的进程，直接在mysql命令行执行：kill id;
show processlist;

14.不同编码字节占用情况

utf8编码

1个字符占1个字节，1个汉字占3字节

gbk编码

1个字符占1个字节，1个汉字占2个字节

15.创建索引

建表时创建索引

create table t1(
id int not null auto_incremnet ,
org_id varchar(32) default not null ,
points mediumblob default null
primary key(id),
index org_id(org_id)
)engine=innodb auto_increment=0 default charset=utf8;

单独创建索引

alter table t1 add index org_id(org_id);

16.单独创建主键

--自增主键
alter table t1 add id int(32) primary key auto_increment;

--联合主键
alter table t1 add primary key(column1,column2);

17.information_schema表相关

--统计数据库表字段个数：统计数据库qq_data中以aaa开头的表所有字段数
select count(*) from information_schema.columns where upper(TBALE_SCHEMA)='qq_data' and upper(TBABLE_NAME) like 'aaa%';
--查看表基础信息，如创建时间，最后更新时间等
SELECT * FROM information_schema.tables WHERE table_schema ='数据库名' AND table_name ='表名';

18.mysqldump命令

${mysql安装路径}/bin/mysqldump -u 用户名 -p 密码 -h 主机ip
--compact  压缩模式，产生更少的输出去掉注释和头尾等结构
--compress  在客户端和服务器之间启用压缩传递所有信息
库名
--tables tablename >${要导出的路径}/tablename.sql

19.更改创建表时的默认字符集

alter table t1 CONVERT to character set utf8;

20.mysql创建分区表

--list 分区
create table test(
id int primary key 
)partition by list(id)(
partition p0 values in (10),
partition p2 values in (20));
alter table test add partition (partition p3  values in (30));
--range 分区 
create table test(
id int primary key 
)partition by range(id)(
partition p0 values less than (10),
partition p2 values less than (20) );
alter table test add partition (partition p3  values less than (30));