mysql实用指令

连接

mysql -h192.168.1.113 -uroot -P3306 -phaha

-p后面接密码

-u后面接用户名

MARK:

后面所有用到的sql命令。。比如下面的建表命令:

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

`id` 后面要接空格不能接table键的

建表

CREATE TABLE `tableA` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

`name` varchar(1024) NOT NULL DEFAULT '' COMMENT '用户名',

`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态：0为无效，1为生效',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

删除表
Drop table tableA;

插入数据

INSERT INTO tableA(id, name, status) VALUES(1, "wawa", 1);

修改字段属性：

-- 修改字段属性
-- ALTER TABLE tb_name MODIFY 字段名称 字段类型 [完整性约束条件]
-- 将email字段 VARCHAR(50)修改成VARCHAR(200)
-- 注意，修改时如果不带完整性约束条件，原有的约束条件将丢失，如果想保留修改时就得带上完整性约束条件
ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT 'a@a.com';

-- 将card移到test后面
ALTER TABLE user10 MODIFY card CHAR(10) AFTER test;

-- 将test放到第一个，保留原完整性约束条件
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;

修改字段名称和属性：

-- 将test字段改为test1
-- ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';

更新数据

update tableA set name='roro' where id=1;

查询

select * from tableA;

查询数学运算

select count(*) from tableA where sfiled1='aaa' and mod(ivalue,100)>0; 取模运算..

数值运算符函数
SELECT CEIL(3.01) ==>4 /*有n.xx 都是n+1 进一取整向上取整
SELECT FLOOR(3.99) ==>3 /*取n.xx 都是n 舍一取整向下取整
SELECT 3 DIV 4 ==> 0 /*整数除法，取商
SELECT 3/4 ==>0.75 /*除法
SELECT 2.1 MOD 2 ==>0.1 /*取余数(取模) 被除数是整数,小数都可以
SELECT 21 % 2 ==>1 /*取余数 mod=%
SELECT POWER(3,4) ==>81 /*3的4次方幂运算
SELECT ROUND(3.1415926,4) ==>3.1416 /*四舍五入，保留4位小数
SELECT TRUNCATE(123.89,1) ==>123.8 /*截取小数点后的位数
SELECT TRUNCATE(123.89,0) ==>123 /*截取小数点后的位置,0位为整数部分
SELECT TRUNCATE(123.89,-1) ==>120 /*截取-1,从个位起去掉后面的数值替换为0

聚合函数
只有一个返回值
AVG() - 平均值
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
COUNT() - 计数
SELECT COUNT(goods_id) as counts FROM tdb_goods;
MAX() - 最大值
SELECT MAX(goods_price) as counts FROM tdb_goods;
MIN() - 最小值
SUM() - 求和
SELECT SUM(goods_price) as counts FROM tdb_goods;

查看表结构:

desc tablename;

show create table tablename;

如果要清空表中的所有记录，可以使用下面的两种方法：

　　DELETE FROM table1
　　TRUNCATE TABLE table1

无则插入有则更新..

假设有个表mypro 有id,ts,card,age 4个字段

insert into mypro values(12306,100000,3,0) on duplicate key update age= 3, ts=2222;

时间字段为DATATIME格式时，查询语句如下:

select uid, UNIX_TIMESTAMP(create_time), order_id, pay from gold_list where create_time > FROM_UNIXTIME(iBgnTm) and create_time < FROM_UNIXTIME(iEndTm);

关于索引

假如表结构如下:

CREATE TABLE `goldlist` (
`uid` bigint(20) DEFAULT NULL,
`pay` double(20,2) NOT NULL,
`status` tinyint(4) NOT NULL,
`createtime` int(11) DEFAULT NULL,
PRIMARY KEY (`uid`),
KEY `status_index` (`status`,`createtime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

那么select * from goldlist where createtime>1494321000;的性能和select * from goldlist where createtime>1494321000 and status=1;之间性能的差距是几十倍甚至于上百倍..

其中 status_index是联合索引查询的时候需要将status和createtime填全才能起到加速作用..

另外如果status的取值是多个。。比如1,2,3,4都是不同含义，而预想查询的是>3的范围。。那么语句可以写成select * from goldlist where createtime>1494321000 and status in(3,4);千万别写成status>2或者是status>=3;

添加索引:

ALTER TABLE `goldlist` ADD INDEX status_index( `status`,`createtime` );

取最新的问题:

select EventId, UserId, EventType, max(EventId) as b from GuessData group by UserId order by b desc;

EventId是db里自增的数据序号值，因为group by只会取最旧的一个，要取最新的话只能用max()

UPDATE和REPLAC的区别

UPDATE和REPLACE基本类似，但是它们之间有两点不同。　　
首先查重，
1. 当没有找到匹配记录（不存在重复记录）时：
UPDATE什么都不做，
REPLACE会做insert动作。
2. 当找到匹配记录（存在重复记录）时：
UPDATE可以更新记录的一部分字段。
REPLACE将已存在记录彻底删除(DELETE)，再插入新的记录（INSERT)。也就是说，将所有的字段都更新为新记录的字段了。

去重语法 DISTINCT

比如 PLAN_NUMBER和PRODUCT_NAME字段去重，语句：SELECT DISTINCT PLAN_NUMBER,PRODUCT_NAME FROM psur_list;

添加表字段

alter table table1 add transactor varchar(10) not Null;

alter table table1 add id int unsigned not Null auto_increment primary key

修改某个表的字段类型及指定为空或非空
alter table 表名称 change 旧字段名称新字段名称字段类型 [是否允许非空];
alter table 表名称 modify 字段名称字段类型 [是否允许非空];

alter table 表名称 modify 字段名称字段类型 [是否允许非空];

修改某个表的字段名称及指定为空或非空
alter table 表名称 change 字段原名称字段新名称字段类型 [是否允许非空]

如果要删除某一字段

可用命令：ALTER TABLE mytable DROP 字段名;

重命名表

alter table 旧名 rename 新名;

用终端连接太慢时候处理

今天使用mysql -hhostname -uusername -ppassword -Pport的方式进入数据，连接成功。

然后运行了use dbname命令，出现了下面一段话。

第一次见到这个语句，所以查了一下。

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

出现问题的原因是:: 我们进入mysql 时，没有使用-A参数；当我们打开数据库，即use dbname时，要预读数据库信息，当使用-A参数时，就不预读数据库信息。

当数据库中表非常多，如果预读数据库信息，将非常慢，可能会卡住，如果数据库中表非常少，将不会出现问题。

正确的打开方式是： mysql -hhostname -uusername -ppassword -Pport -A的方式进入数据库。