SQL基础及基本MySQL语法

SQL基础

SQL是Structure Query Language（结构化查询语言）的缩写，使用关系模型的数据库应用语言，实现了关系数据库中的信息检索。

SQL语句主要分为3类：

• DDL(Data Definition Languages)语句：数据定义语言，主要用于对数据段、数据库、表、列、索引等数据库对象的定义。常用关键字包括：create、drop、alter等。
• DML(Data Manipulation Languages)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用关键字包括：insert、delete、update和select等。
• DCL(Data Control Languages)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的关键字包括：grant、revoke等。

DDL语句

1. 数据库相关

1. 创建数据库：CREATE DATABASE dbname;

   在安装MySQL时系统自动创建了4个数据库:

   • information_schema：主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
   • performance_schema：用于监控MySQL Server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
   • mysql：存储了系统的用户权限信息。
   • test：系统自动创建的测试数据库，任何用户都可以使用。

2. 删除数据库：DROP DATABASE dbname;

2. 表相关

1. 创建表：

   CREATE DATABASE tablename(
       column_name_1 column_type_1 constraints,
       column_name_2 column_type_3 constraints,
       ......
       column_name_n column_type_n constraints,
   );
   

2. 查看表定义：DESC tablename;

3. 删除表：DROP TABLE tablename;

4. 修改表：

   1. 修改表类型：ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST|AFTER col_name];
   2. 增加表字段：ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST|AFTER col_name];
   3. 删除表字段：ALTER TABLE tablename DROP [COLUMN] col_name;
   4. 字段改名(可同时修改字段类型)：ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name];
   5. 表改名：ALTER TABLE tablename RENAME[TO] new_tablename;

DML语句

1. 插入记录

   INSERT INTO tablename
   (field1, field2, ......, fieldn)
   VALUES
   (value1, value2, ......, valuen);
   #另外同时可以同时插入多条记录
   

2. 更新记录

   #更新单个表中数据
   UPDATE tablename SET field1=value1, field2=value2, ......, fieldn=valuen [WHERE CONDITION];
   
   #更新多个表中数据
   UPDATE t1, t2...tn SET t1.field=exper1, tn.fieldn=exprn [WHERE CONDITION];
   

3. 删除记录

   DELETE FROM tablename [WHERE CONDITION];
   
   #删除多个表中的数据
   DELETE t1, t2...tn FROM t1,t2..tn [WHERE CONDITION];
   

4. 查询记录

   基本语法：SELECT * FROM tablename [WHERE CONDITION];

   (1) 查询不重复记录：SELECT DISTINCT col_name FROM tablename;

   (2) 条件查询：SELECT col_name1, col_name2...col_namen FROM tablename WHERE CONDITION;

   (3) 排序和限制：SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2[DESC|ASC], ... fieldn[DESC|ASC]];

   (4) 聚合

   SELECT [field1, field2, ...fieldn] fun_name
   FROM tablename
   [WHERE where_condition]
   [GROUP BY field1, field2, ...fieldn
   [WITH ROLLUP]]
   [HAVING where_condition];
   
   # fun_name表示聚合函数常用有sum,count,max,min
   # WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总。
   # HAVING 关键词表示对分类后的结果再进行条件过滤。
   

   注意：having和where的区别在于having是对聚合后的结果进行条件过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，尽可能用where先过滤记录，这样应为结果集见效，将会大大提高聚合的效率，最后根据逻辑看是否用having进行过滤。

   (5) 表连接：从大类上可以分为内连接和外连接，外连接又分为左连接和右连接。内连接仅选出两张表中互相匹配的记录，外连接会选出其它不匹配的记录。

   • 左连接：包含所有左边的表中的记录甚至是右边表中没有和它匹配的记录。
   • 右连接：包含所有右边的表中的记录甚至是左边表中没有和它匹配的记录。

   基本语法：SELECT col_name FROM t1 [INNER|LEFT|RIGHT] JOIN t2 ON CONDITION;

   (6) 子查询：子查询的关键字主要包括：in、not in、=、!=、exists、not exists等。

   基本语法：SELECT * FROM tablename WHERE col_name [IN|NOT IN|=|!=|EXISTS|NOT EXISTS] (subquery);

   (7) 记录联合：将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示。

   SELECT * FROM t1
   UNION|UNION ALL
   SELECT * FROM t2
   ......
   UNION|UNION ALL
   SELECT * FROM tn;
   
   #其中UNION ALL是将结果集直接合并在一起，而UNION是将结果集DISTINCT后进行显示
   

DCL语句

主要是DBA用来管理系统中的对象权限时所使用，一般开发人员使用较少。

例：

#创建用户并分配权限
GRANT select, insert ON test.* TO 'user1'@'localhost' IDENTIFIED BY '123';

#收回权限
REVOKE insert ON test.* FROM 'user1'@'localhost';