Mysql

Posted on   |   In
  • 管理用户：
    USE mysql；
    SELECT user FROM user；
  • 创建账户：
    CTEATE USER name IDENTIFIED BY ‘PASSWAORD’；
  • 删除用户 DROP USER name；
  • 设置权限：
    SHOW GRANTS FOR name；
  • 修改权限：
    GRANT SELECT ON …. TO username；
  • sql语句创建库:
    SOURCE 绝对路径+文件名；
    SHOW columns FROM tablename；
    SHOW STATUS;
    SHOW GRANTS;
    SHOW CREATE TABLES;
    SHOW ERRORS;

  • create database and use
    create database [if not exists]database_name;
    show databases;
    show tables;
    drop database [if exists]database_name;
    create table [if not exists] table_name(
    column_name data_type[size] [not null|null] [default value] [auto+increment]
    )engine=engine_name;
    alter table table_name
    change/add/drop/rename column column_name ….

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    A temporary table is only available and accessible by the client who creates the table.
    SELECT 
        column_1, column_2, ...
    FROM
        table_1
    [INNER | LEFT |RIGHT] JOIN table_2 ON conditions
    WHERE
        conditions
    GROUP BY column_1
    HAVING group_conditions
    ORDER BY column_1
    LIMIT offset, length;

  • A temporary table is only available and accessible by the client who creates the table.

    1
    2
    3
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    USING [BTREE | HASH | RTREE] 
    ON table_name (column_name [(length)] [ASC | DESC],...)

  • INNER JOIN
    inner join

  • INTERSET NO DUPLICATE
    The left query produces a result set of (1,2,3)
    The right query returns a result set of (2,3,4)
    The INTERSECT operator returns the distinct rows of both result sets which are (2,3).
    intersect

  • Index

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    NUMERIC TYPES	DESCRIPTION
    TINYINT		A very small integer
    SMALLINT	   A small integer
    MEDIUMINT	  A medium-sized integer
    INT			A standard integer
    BIGINT		 A large integer
    DECIMAL		A fixed-point number
    FLOAT		  A single-precision floating point number
    DOUBLE	 	A double-precision floating point number
    BIT			A bit field

  • DATE functions

    1
    2
    3
    4
    5
    6
    NOW();
    DATE(NOW());//format
    DATE_FORMAT(CURDATE(), '%m/%d/%Y');//foramt_function
    CURRENT_TIME()//HHMMSS
    ADDTIME(CURRENT_TIME(), 023000), 
    SUBTIME(CURRENT_TIME(), 023000);

  • TRANSICTION

    1
    2
    3
    START TRANSACTION
    ....
    COMIT

    if all the action in the tansaction sucessed it will commit,if not it will rollback to start

  • TABLE LOCK
    LOCK TABLE tablename [READ|WRITE];

    read:
    other session can read without lock but this session cao only read but not write;
    write:
    only this session can read and write
    other session cannot read and write untill the lock is released.

UNLOCK TABLES;

1
2
3
4
5
6
7
8
9
10
11
``when a table locks other  session cann`t use it untill it unlocked``
* * A REFERENCRS B  ON []
  when B [] act which will act to A the same
* SELECT column FROM tablename LIMIT num1，num2；
输出从num1行开始的num2行
> **BETWEEN AND 两值之间
[NOT] IN(范围) 制定范围之间
LIKE 字符匹配**
* MYSQL REGEXP

| 或
[ a-z ] 范围所有小写字母
. 任意字符
\ 转义
[: :] 匹配字符类

1
* 重复前字符

* 0或多个匹配
+ 1或多个匹配
？ 0或1个匹配
{n} 指定数目匹配
{n，} 不少于制定数目匹配
{n,m} 匹配数目不超过m

1
* 定位符

^ 文本开始
$ 文本结尾
[[:<:]] 词开始
[[:>:]] 词结束

1
2
3
**LIKE 匹配整串 REGEXP 匹配子串**
mysq以concat()实现拼接，一般sql可以用+或者||实现
* 文本函数

rtrim() 去掉数值右侧空格
Left() 返回串左边字符
Length() 返回串长度
Locate() 找出串中一个子串
Lower() 转换为小写
Ltrim() 去除左边空字符
Rtrim() 去除右边空字符
Right() 返回串右边字符
Soundex() 发音相似
Substring() 返回子串
Upper() 转换为大写

1
2

  1. DISTINCT 只能用于COUNT(),不能用于COUNT(*)
  2. GROUP BY 子句嵌套分组时，数据将在最后规定的分组进行汇总
  3. HAVING 过滤分组子句
  4. WHERE 在数据分组前进行过滤，HAVING 在数据分组后过滤
    1
    2
    3
    4
    等值连接即内连接INNER JOIN  ON 子句筛选
    自链接，使用表别名防止链接错误
    * 全文匹配

FULLTEXT（tablename）:为该列建立索引
MATCH（指定搜索列）AGAINST（搜索文本）[WITH QUERY EXPANSION]/查询扩展
FULLTEX与MATCH参数必须一致
全文匹配自动排序，相似度高的先输出
扩展查询：两次索引查询，第一次查询相似词，第二次用相似词查询并返回结果

1
* IN BINARY MODE

+ 包含，词必须存在
— 排除，词不出现
> 包含，增加等级
< 包含，减少等级
() 词组排列组合
~ 取消一个词的排序值
* 词尾通配符
“” 定义为一个短语

1
* 引擎类型

ENGINE=[]
InnoDB 事务处理，不支持全文搜索
MEMORY 快速MyISAM，内存存储，速度快
MyISAM 支持全文搜索不支持事务处理

1
2
3
4
0. **外键不能跨引擎**
1. **在视图定义中有如下操作不能更新:分组、链接、子查询、并、、聚集函数，distinct**
* 创建存储过程：

CREATE PROCEDURE procedurename(参数)
BEGIN
………;
END;

1
2
* 调用存储过程：CALL preocedurename（@p1，@p2，@p3）；
*参数变量以@开头*

* 备份前FLUSH TABLES; 刷新
* 备份：mysqldump 转储所有内文件到外部文件
* mysqlhotcopy 复制数据库
* BACKUP TABLE /SELECT INTOOUTFILE 转储到外部文件

1
*

SHOW CHARACTER SET； 显示字符集
SHOW COLLATION； 显示校对
ANALYZE TABLE tablename； 分析表状态
CHECK TABLE tabkename； 检查表状态
```