mysql

• MySQL命令：
  1. 登录：mysql -u username -p，然后输入password
  2. 获取帮助：help contents，? string functions字符串函数
  3. 数据库：show databases，use db_name，show tables，desc tbl_name；  
  4. 导入导出：使用MySQL客户端转储或运行sql文件
  5. 锁定数据库：flush tables with read lock，解锁：unlock tables
  6. 切换字符集：charset gb2312，show character set查看支持的字符集
• SQL语句
  1. 增删改查：
     select 列 from 表 where 条件 group by 分组 having 分组条件 order by 排序 limit 条数
     insert into 表(列) values(值)，insert into 表(列) select语句
     delete from 表 where 条件 order by 排序 limit 条数
     update 表 set 列=值 where 条件 order by 排序 limit 条数
  2. 表、视图、索引
     create database 库 default character set utf8;
     create table 表(列定义)[表选项]，alter table 表 add column 列定义
     create view 视图 as 查询，drop view 视图
     create index 索引 on table(列)
• 函数Procedure/Function
  1. 时间处理：可存储为15位字符串（毫秒），substr(time,1,12)取前12位（秒），转换为可读的 from_unixtime(substr_time)，再转换成天to_days(unix_time)，计算与某一时间的天数差并分成时间片 floor(diff_days/7)，分组后显示组内最小时间min(unix_time)。对于date类型时间，可直接用 datediff(unix_time,’2010-05-07 15:30:00’)，然后按时间片分组。
  2. 字符串：包含instr(str,substr)!=0，截断substr(time,1,12)。
  3. 正则匹配regexp和替换replace：\.*?+^$ [[:digit:]，例如下句可去掉末尾的换行符\n或\r
     update table set column=replace(column,'\n','') where column regexp '.*\n';
• 高级设计
1. InnoDB数据库支持事务，此时varchar类型有利于减少存储，
   Text大量内容尽量单独存表并经常优化OPTIMIZE TABLE，查主表时网络会省很多
   float和double浮点数表示范围较大但精度有问题，货币时尽量可使用decimal和numeric定点数
2. 
   
• 忘记管理员密码或误删管理员
  1. mysqld-nt --skip-grant-tables;不进行权限检查直接连接(Linux对应有mysql_safe，运行后再mysql登入)
  2. use mysql;这是用户及管理数据库
  3. select host,user,password from user where user='root';需要新建管理员时记住原来的密码
  4. update user set password=password('12345') where user='root';直接修改root密码
  5. INSERT INTO `user` VALUES ('%', 'root', '*4ACFE3202A5FF5CF467898FC58AAB1D615029441', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');添加管理员数据，密码可通过update语句重设，或直接传入password('12345')
     使用grant语句也可以添加用户，比insert语句要简便些，之后用mysql客户端管理用户更方便。
     grant all privileges on *.* to root@'%' identified by 'admin' with grant option;
  6. grant all privileges on *.* to root@'%' identified by '12345';对所有数据库所有表拥有所有权限
  7. flush privileges;权限修改立即生效(下面的with grant option或许会用到)
  8. 权限管理：
     grant select on db.book to user@'host';给普通用户查询数据库中book表的权限
     grant all privileges on db.* to dbadmin@'host';给普通数据库管理员整个数据库的权限
     grant all privileges on *.* to dba@'host';给高级数据库管理员所有权限
     show grants;查看自己的权限show grants for user@'host';查看别人权限
     最高管理员拥有所有权限，且能够给别人赋予权限with grant option，收回权限则revoke
     GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '3e5169875ba3ab98' WITH GRANT OPTION
• 修改字符集：
  1. 直接修改时不能影响已有数据
     alter database character set ***和alter table tablename character set ***
  2. 导出表结构latin1转gbk：-d表示只导出结构
     mysqldump -uroot -p --default-character-set=gbk -d databasesename > createdb.sql
  3. 手工修改createdb.sql中表结构定义中的字符集为新的字符集:%s/latin1/gbk/g
  4. 导出所有数据：--quick每次检索一行有利于大表转储，--extended-insert多行values语法使转储文件更小，--no-create-info不导出创建表语句，--default-character-set按原有字符串导出数据避免乱码
     mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql
  5. 手工修改data.sql，将SET NAMES latin1 修改成SET NAMES gbk
  6. 创建新数据库：
     create database newdatabasename default charset gbk;
  7. 创建表并导入数据：
     mysql -uroot -p newdatabasesname < createdb.sql
     mysql -uroot -p newdatabasename < data.sql
     以上两句不管用，我用的是进入数据库后执行脚本
     mysql -uroot -padmin，use db，source createdb.sql，source data.sql
  8. 搜音客数据库gb2312转换utf8
     mysqldump -usoyinke -p1234 --default-character-set=utf8 -d soyinke_02 > tables.sql
     mysqldump -usoyinke -p1234 --quick --no-create-info --default-character-set=gb2312 soyinke_02 > data.sql
     vi tables.sql，:%s/gb2312/utf8/g，:wq
     mysql -usoyinkes -p1234，show databases;
     create database soyinke default charset utf8; use soyinke;
     source > tables.sql
     source > data.sql
• MySQL数据库文件瘦身
  1. 备份数据库：--quick处理大表转储，--force遇到错误继续，--routines转储过程函数，--add-drop-database添加 drop和add语句，--all-databases转储所有数据库，--default-character-set编码设置
     mysqldump -uroot -p --quick --force --routines --add-drop-database --all-databases --add-drop-table > all.sql
  2. 停止数据库服务：service mysqld会提示命令选项，如{start|stop|status|condrestart|restart}
     service mysqld stop
  3. 删除大文件及数据库文件夹(保留mysql)：通常先备份等成功后再删除，find / -name ibdata1查找目标
     mv /var/lib/mysql/ibdata1 backup
     mv /var/lib/mysql/db_folder backup备份数据库文件夹，它会影响后面重建数据库
  4. 启动数据库服务：
     service mysqld start
  5. 重建数据库：
     mysql -uroot -p < all.sql
• 主从数据库设置：
  1. 步骤及原理：主-从mysql根据二进制日志保持同步，从mysql解析日志并生成sql语句执行
     • 检查主-从服务器mysql版本一致：mysql -V
     • 添加从用户权限grant replication slave on *.* to user@host identified by 'passwd' 
     • 配置主服务器，并重启mysql服务：service mysql stop|start
     • 锁定表，并同步数据库文件，可以tar+scp或mysqldump
       flush tables with read lock;锁定表，只允许读
       mysqldump -uroot -padmin db > file导出数据库db到文件
     • 查看主状态show master status\G;记住二进制日志偏移量
     • 解锁表，恢复主库访问：unlock tables;
     • 配置从服务器，覆盖tar数据并启动服务，或启动服务后导入mysqldump数据
       source > file或mysql -uroot -padmin db > file
       create database soyinke_02 character set=utf8
     • 启动从mysql服务，查看从状态show slave status\G;
     • 需要时调整二进制日志偏移量
       change master to master_host='ip',master_use='slave',master_password='pwd',master_log_file='mysql-bin.000001',master_log_pos=1050;
     • 开始主从同步slave start|stop;
       slave start|stop io_thread|sql_thread，出现问题时调整偏移量并启动IO和SQL线程,
  2. 主服务器配置
     server-id=1
     log-bin=mysql-bin
     binlog-do-db=soyinke_02
     binlog-ignore-db=mysql #忽略数据库，可多次配置
     log-slave-updates #记录新增记录到二进制日志文件
     slave-skip-errors
  3. 从服务器配置
     server-id=2
     log-bin=mysql-bin
     master-host=192.168.7.159
     master-port=3306
     master-user=slave
     master-password=123456
     replicate-do-db=soyinke_02
     replicate-ignore-db=mysql
     log-slave-updates
     slave-skip-errors
• SQL语句扩展
  1. HQL：Hibernate的面向对象查询语句，Session、HibernateTemplate、HibernateDaoSupport
     • 模型通过.hbm.xml或注解@Entity等配置，调用getHibernateTemplate().find(hql)等函数
       select book from Book book where id=?
     • Hibernate不支持与&等操作，可以扩展MySQLInnoDBDialect注册SQLFunction等来实现
  2. JPQL：JPA的数据库查询语句，EntityManager、JpaTemplate、JpaDaoSupport
  3. ibatis和MyBatis：模板化的SQL语句，SqlMapClient、SqlMapClientTemplate
     • 自定义模型对象属性到数据库表列的映射关系，field驼峰命名，column以下划线_分隔
       <resultMap id="book" class="com.model.Book">
       <result property="id" column="book_id"/>
       <result property="name" column="book_name"/>
       </resultMap>
     • 查询时指定返回映射、参数类型：<![CDATA[ sql ]]>内可直接用与&，否则需转义
       <select id="findById" resultMap="book" parameterClass="string">
       select * from book where id=#id# and (state &amp; 1) != 0
       </select>
       返回int查询数量，调用getSqlMapClientTemplate.queryForObject("namespace.id")
       <select id="countByAuthor" resultClass="int" parameterClass="com.model.Book">
       select count(*) from book where author=#author#
       </select>
       常用的分页查询
       <select id="getPager" resultMap="book" parameterClass="java.util.HashMap">
       select book.* from book as book limit $start$,$length$
       </select>
     • 添加数据：通常提取列为sql片段，别名模型类getSqlMapClientTemplate().insert("ns.id",book)
       <sql id="columns">id,name,author,state</sql>
       <sql id="columnsVar">#{id},#{name},#{author},#{state}</sql>
       <typeAliases><TypeAlias alias="book" class="com.model.Book"/></TypeAliases>
       <insert id="add" parameterType="book">
       insert into book( <include refid="columns"/> ) values ( <include refid="columnsVar"/> )
       </insert>
       

通过 为知笔记 发布