MySQL基础命令

——登陆方式

mysql -uroot -p*

—数据库术语
DQL DML DDL DCL TCL

–终止命令

\c

–查看版本

mysql --version 
SELECT VERSION()  ;

–查看当前库

SELECT DATABASE()   ;

–跨库查表

SHOW TABLES FROM *  ；

–查看建表语句

SHOW CREATE TABLE *  ; 

–单行函数

UPPER()  LOWER()  TRIM()  SUBSTR(str,start,length)   LENGTH()  
STR_TO_DATE()   DATE_FORMAT()     ROUND()  RAND()   IFNULL()   
TO_DAYS() ;

–日期格式正则

%Y-%m-%d %H:%i:%s

–MySQL数据类型

VARCHAR
CHAR
INT
BIGINT
FLOAT
DOUBLE
DATE 
BLOB
CLOB
其它

–MySQL建表规范

表名:t_*
VARCHAR的长度最好是2的倍数,方便存储中文

–编码查看

SHOW VARIABLES LIKE '%char%'  ;

–获取系统当前时间:插入数据时可以设置系统默认时间

SELECT NOW()  ;

–MySQL级联操作

ON DELETE CASCADE 
ON UPDATE CASCADE 

–对于取得最大值,不使用分组统计函数的情况下可以结合ORDER BY子句和LIMIT子句完成*

–MySQL存储引擎

SHOW ENGINES\G
*MyISAM
   少量的更新大量的读取,支持压缩
** InnoDB
    缺省值,支持事务、级联操作
***MEMORY
     速度最快,但无法持久化

–MySQL事务
*ACID原则:原子性、一致性、隔离性、持久性
**MySQL默认支持自动提交
* START TRANSACTION ; SET AUTOCOMMIT = OFF
**配置隔离级别:1.修改配置文件

                  -----------------------my.ini↓-----------------------------
                                  [mysqld]
                  transaction-isolation=READ-COMMITTED
                  -----------------------my.ini↑-----------------------------

                  ---------------------可选参数↓-------------------------------
                  #READ-UNCOMMITTED
                  #READ-COMMITTED
                  #REPEATABLE-READ
                  #SERIALIZABLE
                  ---------------------可选参数上-------------------------------

                               2.命令行设置
                     -------------------------查看参数---------------------------------
                                 mysql> select @@tx_isolation;
                        +-----------------+
                        | @@tx_isolation  |
                        +-----------------+
                        | REPEATABLE-READ |
                        +-----------------+
                               mysql> SHOW VARIABLES LIKE '%iso%' ;
                        +---------------+-----------------+
                        | Variable_name | Value           |
                        +---------------+-----------------+
                        | tx_isolation  | REPEATABLE-READ |
                        +---------------+-----------------+

                   -------------------------修改参数---------------------------------
                SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ;

                  ---------------------可选参数↓-------------------------------
                  #READ UNCOMMITTED
                  #READ COMMITTED
                  #REPEATABLE READ
                  #SERIALIZABLE
                  ---------------------可选参数↑-------------------------------

—MySQL索引
*创建索引 CREATE (UNIQUE) INDEX * ON *(*) ;
**删除索引 DROP INDEX * ON * ;
*什么情况下使用索引:
1.该字段很少被更新 ;
2.经常出现在WHERE子句中 ;

**案例:

 mysql> SELECT * FROM emp WHERE ename LIKE '%S%' ;
        +-------+-------+---------+------+------------+------+------+--------+
        | EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
        +-------+-------+---------+------+------------+------+------+--------+
        |  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800 | NULL |     20 |
        |  7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975 | NULL |     20 |
        |  7788 | SCOTT | ANALYST | 7566 | 1981-03-13 | 3000 | NULL |     20 |
        |  7876 | ADAMS | CLERK   | 7788 | 1987-03-13 | 1100 | NULL |     20 |
        |  7900 | JAMES | CLERK   | 7698 | 1981-03-12 |  950 | NULL |     30 |
        +-------+-------+---------+------+------------+------+------+--------+
        5 rows in set (0.05 sec)

        mysql> CREATE INDEX emp_ename_indx ON emp(ename) ;
        Query OK, 14 rows affected (0.05 sec)
        Records: 14  Duplicates: 0  Warnings: 0

        mysql> SELECT * FROM emp WHERE ename LIKE '%S%' ;
        +-------+-------+---------+------+------------+------+------+--------+
        | EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
        +-------+-------+---------+------+------------+------+------+--------+
        |  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800 | NULL |     20 |
        |  7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975 | NULL |     20 |
        |  7788 | SCOTT | ANALYST | 7566 | 1981-03-13 | 3000 | NULL |     20 |
        |  7876 | ADAMS | CLERK   | 7788 | 1987-03-13 | 1100 | NULL |     20 |
        |  7900 | JAMES | CLERK   | 7698 | 1981-03-12 |  950 | NULL |     30 |
        +-------+-------+---------+------+------------+------+------+--------+
        5 rows in set (0.00 sec)

–导入导出命令

  导出:mysqldump * > * -uroot -pmysqladmin
   导入:source *

–数据库设计三范式

  *第一范式:主键  ; 字段不可分 ;
  **第二范式:第一范式 ; 多对多 ;
  ***第三范式:第一范式 ;一对多 ;