MySQL安装
- yum install mysql-server -y
- service mysqld start
- 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; #刷新权限