SQL语言

2018-06-26 01:10:43来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

SQL(Structure Query Language)语言是数据库的核心语言,主要介绍其中3大类:数据定义语言DDL,数据操纵语言DML,数据控制语言DCL。

  DDL:数据定义语言,主要用于管理数据库组件,例如表、索引、视图、用户、存储过程,命令有CREATE、ALTER、DROP等;
  DML:数据操纵语言,主要用管理表中的数据,实现数据的增、删、改、查,命令有INSERT, DELETE, UPDATE, SELECT等;
  DCL:数据控制语言,用来授予或回收访问数据库的某种特权,命令有GRANT, REVOKE等;

DDL

 DDL是对数据库内部的对象进行创建、删除、修改的操作语言,它和DML语言的最大区别是:DML 只是对表内部数据的操作,而不涉及到表的定义、结构的修改。

  • 创建数据库

    CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
    注意:创建数据库时可以指明字符集跟排序规则:
      [DEFAULT] CHARACTER SET [=] charset_name (指明字符集)
      [DEFAULT] COLLATE [=] collation_name (排序规则)
      查看支持的所有字符集:SHOW CHARACTER SET
      查看支持的所有排序规则:SHOW COLLATION

    示例:CREATE DATABASE IF NOT EXISTS students CHARACTER SET utf8 COLLATE utf8_general_ci; (创建字符集为utf8的students数据库)
      SHOW CREATE DATABASE students; (查看自己创建的数据库相关信息)
      SHOW DATABASES; (显示所有数据库)
      USE students; (使用students数据库)

  • 修改数据库

    ALTER {DATABASE | SCHEMA} [db_name];
      [DEFAULT] CHARACTER SET [=] charset_name;
      [DEFAULT] COLLATE [=] collation_name;

    示例:ALTER DATABASE students CHARACTER SET gbk COLLATE gbk_chinese_ci; (修改数据库字符集)

  • 删除数据库

    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

    示例:DROP DATABASE IF EXISTS students; (删除数据库)

  • 查看数据库

    SHOW DATABASES LIKE  '';

    示例:SHOW DATABASES LIKE '%db%'; (查看数据库名中包含'db'的数据库)

  • 创建表

    CREATE TABLE [IF NOT EXISTS] tbl_name (create_defination) [table_options]
    其中create_defination可以包含字段、键、索引:
      字段:col_name data_type
      键:
        PRIMARY KEY (col1, col2, ...)
        UNIQUE KEY (col1, col2,...)
        FOREIGN KEY (column)
      索引:
        KEY|INDEX [index_name] (col1, col2, ...)
    其中table_options可以包含存储引擎:
      ENGINE [=] engine_name
      查看数据库支持的所有存储引擎类型:mysql> SHOW ENGINES;
      查看某表的存储引擎类型:mysql> SHOW TABLE STATUS [LIKE] tbl_name;

    示例:CREATE TABLE students(id int auto_increment,name varchar(20) not null,age tinyint unsigned,sex tinyint(1) not null default 1,subject_name varchar(20) not null,subject_no smallint not null,primary key(id)); (创建students表)
      SHOW TABLES; (查看数据库中所有的表)
      DESC students; (查看students表结构)
      SHOW TABLE STATUS like 'students'\G; (查看表信息)

  • 修改表 

    ALTER [ ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification[,alter_specification] ...]
    其中 alter_specification:
      字段:
        添加:ADD [COLUMN] col_name data_type [ FIRST|AFTER col_name ]
        删除:DROP [COLUMN] col_name
        修改:
          CHANGE [COLUMN] old_col_name new_col_name column_definition [ FIRST|AFTER col_name ]
          MODIFY [COLUMN] col_name column_definition [ FIRST|AFTER col_name]
        键:
          添加:ADD { PRIMARY|UNIQUE|FOREIGN } KEY (col1,col2,...)
          删除:
            主键:DROP PRIMARY KEY
            外键:DROP FOREIGN KEY fk_symbol
        索引:
          添加:ADD { INDEX|KEY } [index_name] (col1,col2,...)
          删除:DROP { INDEX|KEY } index_name
          查看表上的索引的信息:mysql> SHOW INDEXES FROM tbl_name;
        表选项:
          ENGINE [=] engine_name
          RENAME new_tbl_name

      示例:ALTER TABLE students RENAME student; (重命名表)
        ALTER TABLE student ADD subject_score smallint; (添加subject_score字段)
        ALTER TABLE student DROP age; (删除age字段)
        ALTER TABLE student modify subject_score smallint not null; (修改subject_score字段数据类型)
        ALTER TABLE student CHANGE sex gender tinyint(1) not null default 1; (变更sex字段为gender字段)
        ALTER TABLE student ADD index id_name (id,name); (添加索引信息id_name)
        SHOW INDEXES from student\G; (查看student表索引信息)

  •  删除表

    DROP TABLE [IF EXISTS] tbl_name[,tbl_name] ...

    示例:DROP TABLE IF EXISTS student; (删除表)

  • 索引管理
    • 创建索引

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

        示例:CREATE index id_name ON student(id,name); (创建id_name索引)

    • 删除索引

      DROP INDEX index_name ON tbl_name

        示例:DROP index id_name ON student; (删除索引id_name)

DML

DML 操作是指对数据库中表记录的操作,主要包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select)。

  • 插入数据

  INSERT [INTO] tbl_name [(col1, ...)] { VALUES|VALUE }(val1, ...),(...),...
  注意:字符型数据要加引号,数值型数据不能加引号。

  示例:INSERT INTO student values(1,"Will",1,"C++",0001,70);
    INSERT INTO student VALUE(2,"Will",1,"C",0002,60),(3,"Walter",1,"C++",001,80);
    INSERT INTO student(name,subject_name,subject_no,subject_score) VALUES("Alex","C#",0003,75),("Walter","C",0002,67);
    INSERT INTO student(name,gender,subject_name,subject_no,subject_score) VALUES("Alice",0,"C++",0001,86),("Rose",0,"C#",0003,82);

  • 更新数据

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

  示例:UPDATE student set subject_no=2 WHERE id=4; (修改id=4的数据的subject_no字段数值为2)
    SELECT * FROM student ORDER BY subject_score desc; (降序查看表内容)
    SELECT * FROM student ORDER BY subject_score; (默认升序查看表内容)

  • 查看数据
    • SELECT * FROM tbl_name;
    • SELECT col1,col2,... FROM tbl_name; (注意:字段可以显示为别名,col_name AS col_alias)
    • SELECT col1,... FROM tbl_name WHERE clause;  (注意:WHERE clause用于指明挑选条件,如 age > 30)
      • 常用的操作符有:>, <, >=, <=, ==, !=, and, or, not, BETWEEN...AND..., LIKE 'PATTERN', IS NULL, IS NOT NULL
      • 通配符:%,任意长度的任意字符;_:任意单个字符;RLIKE 'PATTERN':正则表达式对字符串做模式匹配
    • SELECT col1,...FROM tbl_name [WHERE clause] ORDER BY col_name,col_name2,...[ASC|DESC];(ASC:升序;DESC: 降序)
  • 删除数据

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

  示例:DELETE FROM student WHERE subject_score<=70; (删除subject_score<=70的数据)

DCL

用于控制不同数据段直接的许可和访问级别的语句,定义了数据库、表、字段、用户的访问权限和安全级别。

  • 创建用户

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

  示例:CREATE USER 'test'@'192.168.4.%' IDENTIFIED BY '123456'; (创建只允许)
    SELECT User,Host,Password FROM mysql.user; (查看用户)

  • 删除用户

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

  示例:DROP USER 'test'@'192.168.4.%';

  • 授权

  GRANT priv_type,... ON 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:指定库上的存储过程或存储函数
  查看指定用户所获得的授权:
      SHOW GRANTS FOR CURRENT_USER;

  示例:GRANT ALL ON studb.student to 'test'@'192.168.4.%'; (授权192.168.4网段的test用户所有操纵studb数据库的student表的权限)
    SHOW GRANTS FOR 'test'@'192.168.4.%'; (查看test用户的授权请看)

  • 回收权限

  REVOKE priv_type,... ON db_name.tbl_name FROM 'user'@'host';
  注意:MariaDB服务进程启动时,会读取mysql库的所有授权表至内存中:
    (1) GRANT或REVOKE命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表,权限修改会立即生效;
    (2) 其它方式实现的权限修改,要想生效,必须手动运行FLUSH PRIVILEGES命令

  示例:REVOKE DELETE,UPDATE ON studb.student FROM 'test'@'192.168.4.%'; (回收test用户对student表的delete,update权限)

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL数据库初识——初窥MySQL

下一篇:Percona-Tookit工具包之pt-ioprofile