获得一个数据库所有表的记录总数
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = '数据库名' order by table_rows desc;
数据是估算的可能不精确,但相差不大。
查看一个数据库的表信息
SHOW TABLE STATUS [FROM `dbname`] [LIKE 'pattern'];#LIKE是对表名进行筛选 show create database <database_name>;#查看建库语句
查看系统变量
#查看全局变量 show global variables like '%char%';#编码 #查看本地/当前会话变量 show local/session variables like 'autocommit';
修改账户密码
mysqladmin -uroot -p原密码 password 新密码
注:不需要登陆mysql执行。
创建数据库
CREATE DATABASE `database_name` DEFAULT CHARSET=utf8 COLLATE utf8_general_ci
对于charset的选择,一般用utf8,若要支持emoji表情,使用utf8mb4。对于collate,一般utf8_general_ci,另外还有utf8_unicode_ci,general速度比较快,unicode则是校对得比较准确。当然了,也可以用utf8mb4_general_ci/utf8mb4_unicode_ci,ci是case insensitive的缩写,意思是不区分大小写。cs(case sensitive)则区分。以上规则同样适用于建表。
修改字段
ALTER TABLE `table_name` MODIFY `column_name` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明';#MODIFY,不修改字段名称,每个字段信息必须至少都得包含数据类型(int) ALTER TABLE `table_name` MODIFY `column_name1` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明1',MODIFY `column_name2` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明2';#多个字段,需要多个MODIFY ALTER TABLE `table_name` CHANGE `column_name` `new_column_name` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明';#CHANGE,修改字段名称 alter table `table_name` comment = '表的注释' #修改表的注释
添加字段
ALTER TABLE `table_name` ADD `column_name` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '字段说明' [AFTER `xxx_column_name`];
修改表名
ALTER TABLE `table_name` rename `new_table_name`;
索引相关
#显示表的索引 SHOW INDEX IN|FROM `table_name`; #创建索引 ALTER TABLE `table_name` ADD INDEX|UNIQUE|PRIMARY KEY| `index_name` (column list); CREATE [UNIQUE] INDEX `index_name` ON `table_name` (column_list); #重建索引 REPAIR TABLE `table_name` QUICK; #删除索引 DROP INDEX `index_name` ON `table_name`; ALTER TABLE `table_name` DROP INDEX|UNIQUE|PRIMARY KEY `index_name`;
存储过程和自定义函数
SHOW PROCEDURE|FUNCTION STATUS [LIKE 'pattern'];#查看,LIKE是对名称进行筛选 DROP PROCEDURE|FUNCTION IF EXISTS procedure_name|function_name;#删除
存储过程和函数都是属于数据库所有。
开放mysql外网连接
为了开发方便,经常需要外网连接mysql。默认my.cnf启用了bind-address值为127.0.0.1,这样其他IP就都连不上了,会报“2003 – Can’t connect to MySQL server on ‘x.x.x.x’ (10038)”这个错误。关掉这个配置或者将值置为0.0.0.0。如果写了多行(一行一个),是以最后一个为准的,也就是无法支持多个IP。
但即使是这样,依然报“1130 – Host ‘x.x.x.x’ is not allowed to connect to this MySQL server”,原因是用户的权限问题。一般的做法是新建一个用户,并授权。
create user 'xiaomlove'@'%' identified by '账户密码';#创建用户 grant all privileges on *.* to 'xiaomlove'@'%';#授权 flush privileges;#刷新授权表
或者直接改已有用户的Host为%估计也是可以的。这样就可以使用新账户进行外网连接了。
如果使用root账号,默认也是只能本机连接,会报”host xxx… not allow to”,一样要执行上边语句开放root账户的外网连接权限。
delete子查询
有时候需要通过子查询来获得删除条件,比如:
DELETE FROM `table_name` WHERE id IN (SELECT id FROM `table_name` GROUP BY `type`);
以上语句是有问题的,会报”[Err] 1093 – You can’t specify target table `table_name` for update in FROM clause”。总结起来原因就是,DELETE后边的第一层子句不能有WHERE、GROUP BY之类的条件,嵌套一层即可。
DELETE FROM `table_name` WHERE id IN ( SELECT id FROM (SELECT id FROM `table_name` GROUP BY `type`) a);
改造后,where子句相当于是”SELECT id FROM a”,没有WHERE/GROUP BY这样的条件了。
复制记录
INSERT INTO `table_name_1` SELECT * FROM `table_name_2` WHERE id = 'XXX';#不同表或同表无键冲突 INSERT INTO `table_name_1` col_1, col_2 [col_3, ...] SELECT col_1, col_2 [col_3,...] FROM `table_name_2` WHERE id = 'XXX';#不同表或同表有键冲突 INSERT INTO `table_name_1` col_1, col_2 [col_3, ...] SELECT 115, col_2 [col_3,...] FROM `table_name_2` WHERE id = 'XXX';#特定字段(col_1)使用固定值(115) CREATE `table_name_1` AS SELECT * FROM `table_name_2` WHERE id > 'xxx';#创建表时从已有表插入数据
批量更新——不同条件对应不同的值
一般的UPDATE语句,都是同一条件使符合条件的记录更新到同一个值。如何让不同条件的记录更新为不同的值呢,比如ID为1的记录的name更新为“姓名1”,ID为2的记录的name更新为“姓名2”。这种情况下,可以使用CASE WHEN语句:
UPDATE `table_name` SET name = CASE WHEN `id` = '1' THEN 'name1' WHEN `id` = 2 THEN 'name2' END, SET age = CASE WHEN `id` = '1' THEN 'age1' WHEN `id` = 2 THEN 'age2' END WHERE id IN (1, 2) LIMIT 2
切记:对于更新和插入,批量操作一般都要远远快于单条操作。
查询——不同值对应不同返回字段
SELECT s_type, baby_status, count(*) FROM ( SELECT id, s_type, s_time, ( CASE WHEN s_time >= 0 AND s_time < 90 THEN '0-90' WHEN s_time >= 90 AND s_time < 180 THEN '91-180' WHEN s_time >= 180 AND s_time < 270 THEN '181-270' WHEN s_time >= 270 AND s_time < 366 THEN '270-366' ELSE '其他' END ) AS 'baby_status' FROM lmb_theme_content WHERE content_type = 6 AND s_type = 3 ) a GROUP BY baby_status
连表更新&删除
update record_attribute_tag_relationships relationship inner join lmb_record record on relationship.record_id = record.id set relationship.album_id = record.album_id, relationship.album_uid = record.album_uid, relationship.create_time = record.publish_time; delete record.* from lmb_record record inner join record_attribute_tag_relationships relationship on record.id = relationship.record_id where record.record_time >= 1473868800 and record.record_time < 1473955200 and record.content_type = 3 and relationship.attribute_id in (3361); #以上语句仅能删除record表的数据,若要删除连表的多个表的数据,不能使用别名 delete lmb_record, record_attribute_tag_relationships from lmb_record left join record_attribute_tag_relationships on lmb_record.id = record_attribute_tag_relationships.record_id where lmb_record.uid = $uid and lmb_record.content_type = 3
字段注释乱码查看
有时候数据表的数据正常,字段的comment(注释)却乱码,一般是因为建表时候的编码选择不正确导致的。Mysql的默认编码使用的是latin1,最后一个是1、2、3中的1,执行set names latin1之后,再
show full columns from `table_name`
如果还不行,需要知道建时候到底使用了什么编码。
密码重置
关闭mysql: killall -TERM mysqld。
安全模式启动mysql: mysqld_safe –skip-grant-tables –skip-networking。
这时候会占用当前窗口,另开一个终端窗口,登陆:mysql -uroot。
修改密码:use mysql;, update user set password=password(“新密码”);。退出exit。
然后退出安全模式的mysql(第一句关闭mysql的语句一样可用),正常启动即可。
注:在mysql 5.7中,可能找不到mysqld_safe,可以参照以下步骤:
1. Stop mysql: systemctl stop mysqld 2. Set the mySQL environment option systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" 3. Start mysql usig the options you just set systemctl start mysqld 4. Login as root mysql -u root 5. Update the root user password with these mysql commands mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword') -> WHERE User = 'root' AND Host = 'localhost'; mysql> FLUSH PRIVILEGES; mysql> quit 6. Stop mysql systemctl stop mysqld 7. Unset the mySQL envitroment option so it starts normally next time systemctl unset-environment MYSQLD_OPTS 8. Start mysql normally: systemctl start mysqld Try to login using your new password: 7. mysql -u root -p
参见:http://stackoverflow.com/questions/33510184/change-mysql-root-password-on-centos7
值不同时使用更多查询条件
假如有小数数和分钟数两个条件,当分钟数为0时,只需要小时数满足即可,若分钟数大于0,分钟数也需要满足。
SELECT * FROM `table_name` WHERE hour = xxx AND CASE WHEN minute > 0 THEN minute = xxx ELSE TRUE END;
数据绑定——where
$sql = "select * from `users` where `id` = ? and (`name` like ? or `email` like ?) limit 10 offset 0"; $users = DB::select($sql, ['12', '%c%', '%c%']);
插入大数据
在使用source语句进行大数据插入时(一条语句上万条记录)时,容易报错:ERROR 2006 (HY000): MySQL server has gone away。这时可修改max_allowed_packet的值,将之改大,比如16M。
查看mysql服务器状态
控制台连接服务器后,直接执行status命令即可。
启动mysql
首先设置启用用户,默认不允许使用root用户。可以在my.cnf中的[mysqld]部分中添加user=mysql来设置。若在service或init.d中均没有启动脚本,安装目录中support-files文件夹下的mysql.service即是启动脚本,将之复制到/etc/init.d/即可使用。
find_in_set不能匹配多个的解决方案
存储数据有多个时采用逗号分割,匹配时若只需要匹配一个,直接使用诸如find_in_set(‘weibo’, clients)即可。但是如果要匹配多个呢,如找出客户端包含weibo, qq的,find_in_set(‘weibo,qq’, clients)是没有这样的语法的。使用like肯定是不行,这时候可以使用正则。如: concat(‘,’, clients, ‘,’) regexp ‘,(weibo|qq),’。参考自这里。