MySQL系列：SQL语句详解

spell

　　一、数据库管理
　　1.创建数据库：create  Database database_name;
　　CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;
　　[DEFAULT]  CHARACTER SET [=] charset_name
　　[DEFAULT]  COLLATE [=] collation_name
　　查看支持的所有字符集：SHOWCHARACTER 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)增加字段
　　最后：ALTHETABLE  表名  ADD  属性名 属性类型
　　第一位置：ALTHE TABLE表名 ADD  属性名 属性类型  FIRST
　　指定字段后：ALTHETABLE  表名ADD  属性名 属性类型   AFTER 属性名
　　(3)删除字段

　　>　　(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 SETfield1=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 UNIQUEINDEX  索引名 ON表名（属性名1【（长度）】【ASC|DESC】）);

　　(3)>　　3.创建全文索引
    (1) CREATE TABLE table_name (
　　属性名 数据类型，属性名 数据类型，……属性名 数据类型，
　　FULLTEXT INDEX |KEY 【索引名】（属性名1【（长度）】【ASC|DESC】）);
　　(2) CREATE FULLTEXTINDEX  索引名 ON表名（属性名1【（长度）】【ASC|DESC】）);

　　(3)>　　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 VIEWview_name AS 查询语句
　　2.查看视图
　　(1)名称：USE 库； SHOW TABLES;不仅会显示表名,还会显示视图名
　　(2)详细信息：SHOW TABLE  STATUS 【FROM 库】【LIKE ‘关键字’】
　　(3)设计信息：DESCRIBE|DESC view_name
　　(4)系统数据库：information_schema中视图信息的表格views
　　3.删除视图
　　DROP VIEWview_name1, view_name1……
　　4.修改视图
　　(1)替换的创建：create ORREPLACE 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_nametype
　　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 ONdatabasename.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> SETPASSWORD=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 ONdatabasename.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