文章目录
下载安装配置
https://dev.mysql.com/downloads/mysql/ 官网下载
下载完成后打开环境变量配置Path到bin目录下
同时在解压后的mysql目录下新建data目录和mysql.ini文件
mysql.ini 内容如下
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\Program Files\mysql-5.7.24-winx64\mysql-5.7.24-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files\mysql-5.7.24-winx64\mysql-5.7.24-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
然后以管理员模式打开命令提示符
1、初始化
mysqld --initialize-insecure --user=mysql
2、安装mysql服务
mysqld install
3、安装成功后启动mysql
net start mysql
4、登陆
mysql -u root -p
5、进入到mysql模式，设置密码
SET PASSWORD = PASSWORD('123456');
6、退出
exit
取交集
1，crossjoin
2，unionall having count(*) >= 2
3, innerjoin
Mysql数据分组GROUP BY 和HAVING，与WHERE组合使用
BETWEEN 左开右闭
CASE WHEN 及 SELECT CASE WHEN的用法
UnixTime
select from_unixtime(1355272360);
select unix_timestamp('2013-01-01 10:10:10');
update效率（join和where in）
update suzhou set money = money + 5 where cid in (select cid from jiangsu where pid = 10);
优化成如下效率会更高
update suzhou t1 inner join jiangsu t2 on t1.cid = t2.cid set money = money + 5 where t2.pid = 10;
Waiting for table metadata lock
这个错误的产生是由于事务的回滚导致的，就是在进行大批量（一千万）数据插入的时候，突然人为中途停止，那么就会出现rolling back，然后再执行其他语句时，就会卡死，这时执行show full processlist就会发现有个进程在waiting，此时要做的就是等待回滚完毕即可
求差集
select id FROM usertable LEFT JOIN
(select id as i from blog) as t1
ON usertable.id=t1.i where t1.i IS NULL
随机查询多少条数据
select md5 from data_test order by RAND() LIMIT 3401234
字符串截取
left(str,length)：截取左边几个字符
right(str,length)：截取右边几个字符
substr(str,pos,length)：从某个位置截取多少字符
正则匹配
`name` not REGEXP '^[0-9]'
查询name不是以数字开头的记录
mysql 过滤查询
查找某个字符串
FIND_IN_SET('175',rule) != 0
以上sql的作用就是查询rule字段中包含175的记录，这里面要注意的是rule中要包含逗号，因为该函数默认的要求就是以逗号分割，如果是其他符号的话，就需要使用replace函数来替换成逗号
locate('cross',batch) as cvalue
locate函数的作用就是查询batch字段中包含cross的索引，如果cvalue大于0，代表有这个字符串
Mysql字符串字段判断是否包含某个字符串的3种方法
mysql判断一个字符串是否包含某几个字符
复制表结构 like
CREATE TABLE IF NOT EXISTS tb_base_like (LIKE tb_base);
查询字段中不包含*号的记录
select name from myinfo where !FIND_IN_SET('*',name);
//查询gid里不含有数字8的记录，gid是varchar
复制20180506的所有记录到表中
insert into table1(mobile_id,obile,track)
select mobile_id,obile,track from table1 where date= '20180506' ;
Win7（64位）下安装教程
1、https://dev.mysql.com/downloads/mysql/ 下载免安装版zip文件，Windows (x86, 64-bit), ZIP Archive (mysql-5.7.19-winx64.zip) 318.5M
2、下载下来后解压到安装程序目录，比如我解压到了Program Files中
3、配置bin环境变量：
4、新建my.ini文件，内容如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = "E:\Program Files\mysql-5.7.19-winx64"
datadir = "E:\Program Files\mysql-5.7.19-winx64\data"
tmpdir = "E:\Program Files\mysql-5.7.19-winx64\data"
socket = "E:\Program Files\mysql-5.7.19-winx64\data\mysql.sock"
port = 3306
log-error = "E:\Program Files\mysql-5.7.19-winx64\data\mysql_error.log"
# server_id = .....
max_connections = 100
table_open_cache = 256
query_cache_size = 1M
tmp_table_size = 32M
thread_cache_size = 8
innodb_data_home_dir = "E:\Program Files\mysql-5.7.19-winx64\data\"
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 128M
innodb_buffer_pool_size = 128M
innodb_log_file_size = 10M
innodb_thread_concurrency = 16
innodb-autoextend-increment = 1000
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 32M
read_rnd_buffer_size = 32M
max_allowed_packet = 32M
explicit_defaults_for_timestamp = true
sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
5、打开管理员命令行
mysqld -install mysql //添加
mysqld --initialize-insecure
以上代码将会生成一个没有密码的root用户，如果不加-insecure那么root用户的密码是随机生成到，到存在
log-error = “F:\mysql-5.7.12-winx64\data\mysql_error.log”
net start mysql
6、mysql密码重置
在配置文件中[mysqld]下方添加如下，意味着跳过验证，直接进入mysql
skip-grant-tables
7、update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;
执行以上语句后，再把上面那句跳过验证的命令去掉就好。
use mysql;
set password = password("admin123")
8、 MySQL安装或重装后出现无法启动MySQL服务错误：发生系统错误2 系统找不到指定的文件
需要去注册表中LOCAL_MACHINE SYSTEM CYRRENTCONTROLSET SERVICES MYSQL中的ImagePath值改为以下这样
“E:\Program Files\mysql-5.7.19-winx64\bin\mysqld” mysql
9、执行show databases提示：
ERROR 1820 (HY000): You must reset your password using ALTER USER statement befo
re executing this statement.
使用这个 set password = password(“admin123”)
使用心得
1、删除多表数据
ps:多表的表结构相同
方法1
delete a_big,a_middle from a_big inner join a_middle on a_big.date = a_middle.date where a_big.date = '20180130' and a_middle.date = '20180130'
方法2，注意带分号，选中一起执行
delete from a_middle where date = '20180201';
delete from a_small where date = '20180201'
2、多次左连接
3、提升查询或更新效率
a.给where后的字段建立索引，normal和btree，参考提高MySql批量更新的效率，mysql 10万条数据 更新一个字段 需要很长时间，
b.连接数据库时加上&rewriteBatchedStatements=true这个参数
4、批量插入数据
使用存储过程最合适，mysql 使用存储过程批量插数据
5、千万级数据sql优化策略
mysql千万级大数据SQL查询优化
Mysql千万级别数据批量插入只需简单三步
MYSQL 大批量数据插入
6、Navicat查询超时的问题
在Navicat上到一定时长之后，直接报超时，默认30秒。经历了一段时间的查找之后，终于找到了在Navicat上可以设置的地方。
右键对应的连接，选择"Edit Connection…",进入"Advanced"选项卡，设置"Socket Timeout(sec)"对应的秒数即可。
https://blog.csdn.net/babyfish13/article/details/54289480
扫一扫
3105
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
