SQL语言

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权限)