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. 数据库相关
-
创建数据库:
CREATE DATABASE dbname;
在安装MySQL时系统自动创建了4个数据库:
- information_schema:主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- performance_schema:用于监控MySQL Server在一个较低级别的运行过程中的资源消耗、资源等待等情况。
- mysql:存储了系统的用户权限信息。
- test:系统自动创建的测试数据库,任何用户都可以使用。
-
删除数据库:
DROP DATABASE dbname;
2. 表相关
-
创建表:
CREATE DATABASE tablename( column_name_1 column_type_1 constraints, column_name_2 column_type_3 constraints, ...... column_name_n column_type_n constraints, );
-
查看表定义:
DESC tablename;
-
删除表:
DROP TABLE tablename;
-
修改表:
- 修改表类型:
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST|AFTER col_name];
- 增加表字段:
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST|AFTER col_name];
- 删除表字段:
ALTER TABLE tablename DROP [COLUMN] col_name;
- 字段改名(可同时修改字段类型):
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name];
- 表改名:
ALTER TABLE tablename RENAME[TO] new_tablename;
- 修改表类型:
DML语句
-
插入记录
INSERT INTO tablename (field1, field2, ......, fieldn) VALUES (value1, value2, ......, valuen); #另外同时可以同时插入多条记录
-
更新记录
#更新单个表中数据 UPDATE tablename SET field1=value1, field2=value2, ......, fieldn=valuen [WHERE CONDITION]; #更新多个表中数据 UPDATE t1, t2...tn SET t1.field=exper1, tn.fieldn=exprn [WHERE CONDITION];
-
删除记录
DELETE FROM tablename [WHERE CONDITION]; #删除多个表中的数据 DELETE t1, t2...tn FROM t1,t2..tn [WHERE CONDITION];
-
查询记录
基本语法:
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';