xunming的专栏

自律给我自由

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查询优化

6、Navicat查询超时的问题

在Navicat上到一定时长之后,直接报超时,默认30秒。经历了一段时间的查找之后,终于找到了在Navicat上可以设置的地方。
右键对应的连接,选择”Edit Connection…”,进入”Advanced”选项卡,设置”Socket Timeout(sec)”对应的秒数即可。
https://blog.csdn.net/babyfish13/article/details/54289480

阅读更多
版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/diyangxia/article/details/78088323
文章标签: mysql win7 安装mysql
个人分类: Database
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

MySql的使用心得

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭