表的引用方式：

       tbl_name

       db_name.tbl_name

                    

第二种创建方式：

       复制表结构；

                                  

第三种创建方式：

       复制表数据；

                    

索引管理：

索引是特殊的数据结构；主要是为了加速数据操作,不理于写操作

                           

              索引：要有索引名称；索引数据是及时创建的 不能修改 所以只能穿件或删除

                           

              创建：

                     CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX  index_name  [BTREE|HASH]  ON tbl_name (col1, col2,,...)

                           

              删除：

                     DROP  INDEX index_name ON tbl_name

DML：INSERT， DELETE， UPDATE， SELECT

                           

       INSERT INTO：

              INSERT  [INTO]  tbl_name  [(col1,...)]  {VALUES|VALUE}  (val1, ...),(...),...

                                  

              注意：

                     字符型：引号；

                     数值型：不能用引号；

SELECT：

              (1) SELECT  *  FROM  tbl_name;

              (2) SELECT  col1, col2, ...  FROM  tbl_name;

                     显示时，字段可以显示为别名；

                            col_name  AS  col_alias

              (3)  SELECT  col1, ...  FROM tbl_name  WHERE clause;

                     WHERE clause：用于指明挑选条件；

                            col_name 操作符 value：

                                   age > 30;

操作符(1) ：

                                   >, <, >=, <=, ==, !=

                                                       

                            组合条件：

                                   and

                                   or

                                   not

                                                       

                            操作符(2) ：

                                   BETWEEN ...  AND ...

                                   LIKE 'PATTERN'

                                          通配符：

                                                 %：任意长度的任意字符；

                                                 _：任意单个字符；

                                   RLIKE  'PATTERN'

                                          正则表达式对字符串做模式匹配；

                                   IS NULL

                                   IS NOT NULL

(4) SELECT col1, ... FROM tbl_name  [WHERE clause]  ORDER BY  col_name, col_name2, ...  [ASC|DESC];

                            ASC: 升序；

                            DESC： 降序；

DELETE：

                                   DELETE   FROM  tbl_name  [WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]

                                  

                                   (1) DELETE  FROM  tbl_name  WHERE where_condition

                                   (2) DELETE  FROM  tbl_name  [ORDER BY ...]  [LIMIT row_count]

                                  

                            UPDATE：

                                   UPDATE [LOW_PRIORITY] [IGNORE] table_reference  SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

                                  

                     用户账号及权限管理：

                           

                            用户账号：'username'@'host'

                                   host：此用户访问当前mysql服务器时，允许其通过哪些主机远程创建连接；

                                          表示方式：IP，网络地址、主机名、通配符(%和_)；

                                         

                                   禁止检查主机名：my.cnf

                                          [mysqld]

                                          skip_name_resolve = ON

                                         

                            创建用户账号：

                                   CREATE  USER   'username'@'host'  [IDENTIFIED BY  'password'];

                                  

                            删除用户账号：

                                   DROP USER  ’user‘@’host' [, user@host] ...

 

                            授权：

                                   权限级别：管理权限、数据库、表、字段、存储例程；

                                  

                                   GRANT  priv_type,...  ON  [object_type]  db_name.tbl_name  TO  'user'@'host'  [IDENTIFIED BY  'password'];

                                         

                                          priv_type： ALL  [PRIVILEGES]

                                          db_name.tbl_name：

                                                 *.*：所有库的所有表；

                                                 db_name.*：指定库的所有表；

                                                 db_name.tbl_name：指定库的特定表；

                                                 db_name.routine_name：指定库上的存储过程或存储函数；

                                         

                                          [object_type]

                                                 TABLE

                                                 FUNCTION

                                                 PROCEDURE

                                         

                                   查看指定用户所获得的授权：

                                          SHOW GRANTS FOR  'user'@'host'

                                         

                                          SHOW GRANTS FOR CURRENT_USER;

                                         

                                   回收权限：

                                          REVOKE  priv_type, ...  ON  db_name.tbl_name  FROM  'user'@'host'; 

                                   注意：MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中；

                                          (1) GRANT或REVOKE命令等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效；

                                          (2) 其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可；

                                         

                     加固mysql服务器，在安装完成后，运行mysql_secure_installation命令；

                           

                     图形管理组件：

                            phpMyAdmin

                                   运行于lamp；

                            Navicat

                            Mysql-Front

                            ToadForMySQL

                            SQLyog