用户和权限操作

查看用户

  1.   select user, host from mysql.user;

查看用户权限

  1.   select * from mysql.user where user='root'\G;

设置用户权限

  1.   grant ALL on *.* to 'root'@'localhost' identified by 'root' [with grant option];
  2.   revoke ALL, grant option on *.* from 'root'@'localhost';

开启远程访问

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf bind-address 修改为 0.0.0.0

建库操作

建库

  1.   create database if not exists my_db default charset utf8 COLLATE utf8_general_ci;

常用function

sequence

  1. DELIMITER $$
  2. CREATE DEFINER=`root`@`localhost` FUNCTION `nextval`(`seq_name` varchar(100)) RETURNS bigint(20)
  3. BEGIN
  4.     DECLARE cur_val bigint;
  6.     UPDATE
  7.         ushare_sequence
  8.     SET
  9.         name = seq_name
  10.     WHERE
  11.         name = seq_name; 
  13.     SELECT
  14.         cur_value INTO cur_val
  15.     FROM
  16.         sequence_table_name
  17.     WHERE
  18.         name = seq_name;
  20.     IF cur_val IS NOT NULL THEN
  21.         UPDATE
  22.             sequence_table_name
  23.         SET
  24.             cur_value = IF (
  25.                 (cur_value + increment) > max_value OR (cur_value + increment) < min_value,
  26.                 IF (
  27.                     cycle = TRUE,
  28.                     IF (
  29.                         (cur_value + increment) > max_value,
  30.                         min_value,
  31.                         max_value
  32.                     ),
  33.                     NULL
  34.                 ),
  35.                 cur_value + increment
  36.             )
  37.         WHERE
  38.             name = seq_name;
  39.     END IF;
  40.     RETURN cur_val;
  41. END$$
  42. DELIMITER ;

对应表

  1. CREATE TABLE `sequence_table_name` (
  2.   `name` varchar(100) NOT NULL,
  3.   `increment` int(11) NOT NULL DEFAULT '1' COMMENT='步长',
  4.   `min_value` int(11) NOT NULL DEFAULT '1' COMMENT='最小值',
  5.   `max_value` bigint(20) NOT NULL DEFAULT '9223372036854775807' COMMENT='最大值',
  6.   `cur_value` bigint(20) DEFAULT '1' COMMENT='当前值',
  7.   `cycle` tinyint(1) NOT NULL DEFAULT '0' COMMENT='是否循环',
  8.   PRIMARY KEY (`name`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='序列表';

binlog日志

  1. mysqlbinlog -d ushare --start-datetime='2018-08-27 09:00:00' --stop-datetime='2018-08-27 09:10:00'

基础

Chapter 1

SQL, structured query language

  1. writen in c and c++

  2. provides transactional and untransactional storage engine

  3. uses very fast b-tree disk table and index compression

  4. uses a very fast thread-based memory allocation system

  5. executes very fast joins using an optimized nested-loop join

  6. implements in-memory hash tables, which are used as temporary tables

  7. for a function call, no spaces are permitted between the function name and the “(” that follows it.

  8. Support for up to 64 indexes per table. Each index may consist of 1 to 16 columns or parts of columns.

  9. MySQL is named after co-founder Monty Widenius’s daughter, My.

Chapter 3

  1. database names and table names are case sensitive.

  2. Store birth date rather than age

  3. NULL

    1. NULL is a special value that cannot be compared using usual comparison operators. The result of any arithmetic comparison with NULL is also NULL.

    2. Two NULL values are regarded as equal in a group by

  4. MOD(something, 12) returns a number between 0 and 11

  5. pattern character ‘_’ is regarded as a letter.

  6. regexp or rlike

  7. ONLY_FULL_GROUP_BY, cannot execute aggregation without group by

Chapter 4

  1. memory table

  2. database/schema, table, event, view, function/procedure, trigger

  3. mysqlslap, test the performance

  4. (4.6.7.3) Using mysqlbinlog to Back Up Binary Log Files

mysqlslap —delimiter=”;” —create=”CREATE TABLE a (b int);INSERT INTO a VALUES (23)” —query=”SELECT * FROM a” —concurrency=50 —iterations=200

安装

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server