MySQL系列：SQL语句详解

一、数据库管理

  1.创建数据库：create  Database  database_name;

CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;

[DEFAULT]  CHARACTER SET [=] charset_name

[DEFAULT]  COLLATE [=] collation_name

查看支持的所有字符集：SHOW CHARACTER SET

查看支持的所有排序规则：SHOW  COLLATION

   注意：database_name命名规范

1)首字母不能是数字、$、空格、特殊字符、保留字

2)最大128字节

3)由字母、数字、下划线、@和$组成

  2.查看数据库

          SHOW  databases

  3.选择数据库

          USE  database_name

  4.删除数据库：DROP  DATABASE  database_name

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

  5.修改数据库

ALTER {DATABASE | SCHEMA}  [db_name]

[DEFAULT]  CHARACTER SET [=] charset_name

[DEFAULT]  COLLATE [=] collation_name

 

 

二、表管理

  1.创建表

      create  table table_name (属性名 数据类型…)

  2.查看表结构

    (1)表定义信息

DESCRIBE    table_name

DESC    table_name

    (2)表详细定义

              SHOW CREATETABLE table_name

  3.删除表

             DROP TABLE table_name

  4.修改表(ALTER TABLE)

    (1)修改表名

ALTER TABLE  老表名  rename 新表名

    (2)增加字段

最后：ALTHE TABLE  表名  ADD  属性名 属性类型

第一位置：ALTHE TABLE表名  ADD  属性名 属性类型  FIRST

指定字段后：ALTHE TABLE  表名ADD  属性名 属性类型   AFTER 属性名

    (3)删除字段

            ALTHE  TABLE  表名 drop 属性名

    (4)修改字段

修改数据类型：ALTER  TABLE  表名  MODIFY  属性名 属性类型

修改字段名：ALTER  TABLE  表名  CHANGE  旧属性名 新属性名 旧类型

同时修改：ALTER  TABLE  表名  CHANGE 旧属性名 新属性名 新类型

修改字段顺序：ALTER  TABLE   表名  MODIFY 属性名1 数据类型 FIRSE|AFTER 属性名2

 

 

 

三、数据管理

  1.插入数据

     (1)INSERT INTO  table_name (field1….field n) VALUES(value1…value n)

    (2) INSERT INTO  table_name (field1….field n)  VALUES(value1…value n)…(value11…value mn)

    (3)插入查询结果

INSERT INTO  table_name1 (field11….field 1n)  SELECT  (field21….field 2n)  FROM table_name2  WHERE…

  2.更新数据

         UPDATE table_name SET field1=value1…fieldn=value n WHERE CONDITION

  3.删除记录

        DELETE  FROM  table_name  WHERE CONDITION

 

 

 

四、表的约束

  1.设置非空约束(NOT NULL,NK)

CREATE  TABLE  table_name (属性名 类型 NOT NULL )；

  2.设置字段默认值(DEFAULT)

CREATE  TABLE  table_name (属性名 类型 DEFAULT 默认值)；

  3.设置唯一约束(UNIQUE,UK)

CREATE  TABLE  table_name (属性名 类型 UNIQUE L )；

  4.设置主键约束(PRIMARY KEY,PK)

单字段主键：CREATE  TABLE  table_name (属性名 类型 PRIMARY KEY )；

多字段主键：

        CREATE  TABLE  table_name (属性名 类型 ,…【CONSTRAINT 约束名】 PRIMARY KEY（属性名，属性名…） )；

  5.设置字段值自动增加(AUTO_INCREMENT)

CREATE  TABLE table_name (属性名 类型 AUTO_INCREMENT,… )；

  6.设置外键约束(FOREIGNKEY,FK)

           CREATE  TABLE  table_name (属性名 类型; 属性名 类型;… CONSTRAINT 外键约束名 FOREIGN （属性名1）REFERENCES 表名（属性名2）)；

 

 

 

五、索引管理

  1.创建普通索引

    (1) CREATE TABLE table_name (

    属性名 数据类型，属性名 数据类型，……属性名 数据类型，

                 INDEX | KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）);

           注意：

                1) INDEX 或KEY参数用来指定字段为索引

                2) 不同存储引擎定义了表的最大索引数和最大索引长度

                3) Mysql所支持的存储引擎对每个表至少支持16个索引，总索引长度至少256

    (2) CREATE INDEX  索引名 ON 表名（属性名1【（长度）】【ASC|DESC】）);

    (3)ALTER TABLE  table_name  ADD INDEX | KEY 索引名（属性名1【（长度）】【ASC|DESC】）

 

  2.创建唯一索引

    (1) CREATE TABLE table_name (

属性名 数据类型，属性名 数据类型，……属性名 数据类型，

            UNIQUE INDEX | KEY【索引名】（属性名1【（长度）】【ASC|DESC】）);

    (2) CREATE UNIQUE INDEX  索引名 ON表名（属性名1【（长度）】【ASC|DESC】）);

    (3) ALTER TABLE  table_name  ADD UNIQUE INDEX | KEY 索引名（属性名1【（长度）】【ASC|DESC】）

 

  3.创建全文索引

    (1) CREATE TABLE table_name (

属性名 数据类型，属性名 数据类型，……属性名 数据类型，

            FULLTEXT INDEX | KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）);

    (2) CREATE FULLTEXT INDEX  索引名 ON表名（属性名1【（长度）】【ASC|DESC】）);

    (3) ALTER TABLE  table_name   ADD FULLTEXT INDEX | KEY 索引名（属性名1【（长度）】【ASC|DESC】）

 

  4.创建多列查询

    (1) CREATE TABLE table_name (

       属性名 数据类型，属性名 数据类型，……属性名 数据类型，

                    INDEX | KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）

                      …….

        属性名n【（长度）】【ASC|DESC】）);

    (2) CREATE INDEX  索引名

            ON 表名（属性名1【（长度）】【ASC|DESC】）

          …….

属性名n【（长度）】【ASC|DESC】）);

    (3)ALTER TABLE  table_name

            ADD INDEX | KEY 索引名（属性名1【（长度）】【ASC|DESC】）

                       …….

属性名n【（长度）】【ASC|DESC】）);

  5.查询索引

EXPLAIN

  6.删除索引

         DROP  INDEX  index_name ON table_name

 

 

 

六、视图管理

  1.创建视图

           CREATE VIEW view_name AS 查询语句

  2.查看视图

     (1)名称：USE 库； SHOW TABLES;不仅会显示表名,还会显示视图名

     (2)详细信息：SHOW TABLE  STATUS 【FROM 库】【LIKE ‘关键字’】

     (3)设计信息：DESCRIBE|DESC view_name

     (4)系统数据库：information_schema中视图信息的表格views

  3.删除视图

          DROP VIEW view_name1, view_name1……

  4.修改视图

     (1)替换的创建：create OR REPLACE view view_name AS 查询语句

     (2)修改语句：ALTER VIEW view_name AS 查询语句

 

 

 

七、触发器

  1.创建有一条语句的触发器

       Create  TRIGGER  trigger_name   BEFORE|AFTER  trigger_EVENT  ON  table_name  FOR EACH ROW  trigger_STMT

            trigger_EVENT：触发事件，即触发执行的条件（DELETE、INSERT、UPDATE）

            FOR EACH ROW：任何一条记录上的操作满足触发事件都会触发该触发器

            trigger_STMT：激活触发器后执行语句

  2.创建包含多条执行语句的触发器

        Create  trigger  trigger_name

           BEFORE|AFTER  trigger_EVENT

              ON  table_name  FOR EACH ROW

 BEGIN

 trigger_STMT

 END

  3.查看触发器

        SHOW TRIGGERS;

  4.删除

       DROP TRIGGER   trigger_name;

 

 

 

八、存储过程

       CREATE  PROCEDURE procedure_name([procedure_parameter[,…]]) [characteristic…]  routine_body

   注释：

       1) procedure_parameter：存储过程的参数

                [IN|OUT|INOUT] procedure_name type

       2) characteristic：存储过程的特性

       3) routine_body：SQL语句主体，可用BEGIN…END开始和结束

 

 

 

九、用户相关

  1.命令行连接mysql

      mysql –hHOST  –uUSERNAME  –pPASSWD   –e “SQL”

-h：指明所需要连接的主机名称或者地址

-u：指明需要登录的用户名

-p：指明登录该数据库用户的密码

-e：不进入mysql交互界面指明SQL语句

      注意：

1) mysql后接参数可以直接加上数值，无需空格

 `         2) 直接加在-p 参数之后是明文密码，可以不填，在回车后填写不显示格式密码

3) -e参数非登录必须参数

4) 若匿名用户在本机登录该数据库，可以直接输入mysql后回车即可

 

  2.创建普通用户账户，赋予权限

    (1) 命令创建用户

            mysql> CREATE USER username [IDENTIFIED BY [PASSWORD] ‘password’]…

 mysql>  FLUSH  PRIVILEGES;

                关键字PASSWORD实现对密码加密

    (2) 在mysql指定的表中插入用户信息数据

            mysql>  INSERT INTO user(host,user,password) VALUES(‘hostname’,’user’, PASSWORD (‘password’))；

mysql>  FLUSH  PRIVILEGES;

        注意：

  1) 由于该语句是向mysql_user插入用户账号数据，ssl_cipher、x509_issuer、x509_subject无默认值要设置这些字段

  2) 对于Password字段的值一定要用PASSWORD()函数加密

  3) 刷新授权信息后所创建用户才能生效

    (3)GRANT创建账户可以对账户赋权限

             mysql> GRANT priv_type ON databasename.tablename TO username@"host" [IDENTIFIED BY [PASSWORD] ‘password’]…

  mysql> FLUSH  PRIVILEGES;

      参数说明：

                priv_type：表示权限信息，如select、all privileges… ...

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

                databasename.tablename:表示该用户只能对该表的权限

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

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

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

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

                username：所需创建的用户，或者在对已存在的用户进行授权时存在的用户的用户名

                host：允许其通过哪些主机远程创建连接；表示方式：IP，网络地址、主机名、通配符(%和_)；例如"172.16.%.%"

  

  3.用户权限相关

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

   mysql>  SHOW GRANTS FOR  'user'@'host'

    (2)回收权限

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

  注意：

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

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

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

 

  4.修改root密码

    (1) mysqladmin命令修改

             mysqladmin –u username –p password “new_password”

    (2)Root用户对密码修改

           mysql> SET PASSWORD=PASSWORD(“new_password”)

    (3)更新mysql.user表数据

    mysql>  UPDATE user SET password= PASSWORD(“new_password”) WHERE user=”root” AND host=”hostname”;

 

  5.利用超级用户更改普通用户密码

        (1) GRANT priv_type ON databasename.tablename  TO username [IDENTIFIED BY [PASSWORD] ‘new_password’]

        (2) SET PASSWORD FOR ‘username’@’hostname’=PASSWORD(“new_password”)

        (3)UPDATE user SET password= PASSWORD(“new_password”) WHERE user=”” AND host=”hostname”;

        (4)进入普通用户，再SET PASSWORD=PASSWORD(“new_password”)

 

  6.数据库备份

        mysql>    Mysqldump  -u username –p  dbname  [table1…table n]  >  backupname.sql

        mysql>    Mysqldump  -u username –p  dbname1…dbname n >  backupname.sql

        mysql>    Mysqldump  -u username –p  --all –databases >  backupname.sql

 

  7.安全加固

         在安装完成后，运行mysql_secure_installation命令；会设置root用户密码，删除测试数据库，删除匿名用户等，以提高数据库的安全性

 

  8.图形管理组件

      phpMyAdmin(运行于lamp架构)、Navicat、Mysql-Front、ToadForMySQL、SQLyog