MySql常用命令和基本操作

MySQL安装

1. yum install mysql-server -y
2. service mysqld start
3. mysql

MySql客户端工具，推荐使用的是官方的MySql Workbench，结合使用Navicat和mysql命令。

Navicat简单易用，但是对于一些数据量比较大的操作支持不好，必要时还得用mysql命令。MySql Workbench对大量数据操作支持比较好。

新建数据库：lot_test
设置“字符集”: “utf8 – UTF-8 Unicode”，如果需要存储一些特殊字符，使用utf8mb4（utf8的超集，支持emoji等特殊字符）
设置“排序规则”: “utf8_general_ci”
ci表示“case insensitive”，大小写不敏感。

排序规则

utf8_general_ci（case insensitive）-- varchar类型查询的时候大小写不敏感。
utf8_bin – 大小写敏感。

常用命令

#建立连接
mysql -uroot -p
******
mysql -h{hostIP} -uroot -p #连接远端mysql服务

#常用操作
show databases;
use db_name;
show tables;
desc/describe lot_keyvalue;#查看lot_keyvalue表结构
show create table tbl_xxx \G#查看表tbl_xxx建表语句
show index from table_name; #查看表的索引
explain select ... #查看语句的执行计划，而不执行语句
#建库
CREATE DATABASE my_db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
#删库
drop database db_name;

# 查看某个库底下有多生张表
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'db_name'

#替换操作
update test_tb set address=replace(address,'大道','路') where id=2;

\q #退出mysql
exit #退出mysql

#source命令导入*.sql数据文件:
use qx_test;
set names utf8;
source D:/test/sql/data.sql#注意末尾没有";"
需要注意的是,本地mysql配置可能packet容量设置不够大(往往只有几M至几十M),导入大文件可能报1231系列错误:
ERROR 1231 (42000) Variable 'time_zone' can't be set to the value of 'NULL'
......
可以修改ini文件改成
max_allowed_packet = 1024M

# 修改表名
alter table xxxa rename xxxb;

#修改表
ALTER TABLE `%1$s` ADD COLUMN `%3$s` INT AFTER `%2$s`;
ALTER TABLE `%1$s` ADD COLUMN `%3$s` BIGINT AFTER `%2$s`;
ALTER TABLE `%1$s` ADD COLUMN `%3$s` VARCHAR(255) AFTER `%2$s`;
String sql = "ALTER TABLE `%1$s` ADD COLUMN `%3$s` INT NULL AFTER `%2$s`, "
		+ "ADD COLUMN `%4$s` VARCHAR(47) NULL AFTER `%3$s`, "
		+ "ADD COLUMN `%5$s` INT NULL AFTER `%4$s`, "
		+ "ADD COLUMN `%6$s` INT NULL AFTER `%5$s`;";
sql="ALTER TABLE `%1$s` ADD INDEX `%2$s` (`%2$s` ASC);";

#清空表
TRUNCATE TABLE `tbl_name`;

导出数据

mysqldump -uroot -p xxxx lot_member_table --where="_company_server_id=428 and _id>=23083076" > data_1450.sql

导入数据

# 先设置编码格式，防止导入的中文乱码
set NAMES 'utf8';
source xxx.sql

需要注意的是source后面跟导入文件路径,末尾是不要加英文分号的,加了反而导不进去

分组/去重

# 查询某列的值不唯一的数据
SELECT count(*) from lot_member GROUP BY _member_phone HAVING COUNT(*)>1;
SELECT _id,_member_phone,count(*) from lot_member GROUP BY _member_phone HAVING COUNT(_member_phone)>1;

# 根据某列去重后的数据行数
SELECT count(DISTINCT _member_phone) from lot_member;

JOIN连接/SUM求和/CONCAT连接字符串

update display_shop_task_submit set _ai_task_id = CONCAT("MockTaskId",_create_time);

#列求和
SELECT sum(_count) from tbl_test WHERE _product_id=135;

#查询
select * from lot_product where _id>10 limit 20;
select _id,_product_name,_begin_time,_end_time from _product where _company_id=40;

#联合查询
SELECT a.* from tbl_test a,lot_company b WHERE a._company_id=b._id && b._company_name LIKE '长沙%';

#left join
SELECT
	a._member_name as _member_name,
	a._member_phone AS _member_phone,
	b._unit_name AS mi_unit_name,
	b._member_province AS mi_member_province,
	b._member_city AS mi_member_city,
	b._member_county AS mi_member_county
FROM
	`tbl_member` a
LEFT JOIN `tbl_member_info` b ON a._id = b._member_id 
WHERE a._member_phone='13899990000' 
ORDER BY
	a._id DESC;

mysql内建函数 group_concat支持将多个列进行组合。

DML/DDL/DCL

DML（data manipulation language）：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

DDL（data definition language）：
DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 (不受事务回滚限制)
–不能对DDL语句进行回滚。

DCL（Data Control Language）：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

数据库管理相关命令

#查看数据库服务器的设置参数
show status; #或者:\s
show status like 'Handler_read%';#查看索引被使用到的次数统计

#查看编码设置
show variables like“character_set_%”;

#查看事务的隔离级别
SELECT @@tx_isolation;

# 给mysql/user表增加用户：
# insert是无效的，密码要经过加密
# 将所有库的所有表的所有权限，赋给root用户（%指明支持从任何远程IP发起的访问），密码为123456
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges; #刷新权限