SQL基础

Posted on | In
  • SQL，Structure Query Language，结构化查询语言，是关系型数据库的应用语言。

一、分类

  1. DDL（Data Defintion Language ）语句：数据定义语言，定义了不同的数据段、数据库、表、列、索引等数据库对象，常用的语句的关键字包括 create、drop、alter等。
  2. DML（Data Manipulation Language）语句：数据操纵语句，用于增删改查数据库记录，并检查数据的完整性。关键词： insert、delete、update、select。
  3. DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的许可。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。关键词：grant、revoke。

二、DDL语句

DDL语句一般情况下是由数据库管理员（DBA）使用，与DML的最大区别是DML只对表内数据进行操作，而不设计表的定义、结构和修改。以下命令均为在指定数据库内操作，因此不用指明是在哪里数据库里新增等，若没有指定，则需要加上数据库的名称。

  1. 创建数据库 - CREATE DATABASE dbname; （CREATE DATABASE 也可小写 ）
  2. 删除数据库 - DROP DATABASE dbname；

  3. 创建表 -

    1
    2
    3
    4
    CREATE TABLE tablename（ column_name_1 column_type_1_constraints,
                             column_name_2 column_type_2_constraints,
                             ...
                             column_name_n column_type_n_constraints）
    • 查看表结构 —— DESC tablename
    • 更全面地查看表定义 —— show create table tablename \G; 这条命令可以看到创建表的SQL语句,其中“\G
      ”的意思是使得记录能够按照字段竖向排列，也就是改变我们看到的格式。
  4. 删除表 - DROP TABLE tablename
  5. 修改表-
  • 修改表类型（字段的类型）
    1
    ALTER TABLE tablename MODIFY[COLUMN] column_definition [FIRST|AFTER col_name]

例如，在mysql环境下执行，alter table emp modify ename varchar(20)。

  • 增加表字段

    1
    ALTER TABLE table_name ADD[COLUMN] column_definition [FIRST|AFTER col_name]

    例如， alter table emp add column age int(3);

  • 删除表字段 -
    ALTER TABLE table_name DROP [COLUMN] col_name;例如， alter table emp drop column age；
  • 字段改名 -
    1
    ALTER TABLE table_name CHANGE[COLUMN] old_col_name column_definition[FIRST|AFTER column_name]

例如，alter table emp change age age1 int(4);
PS: chang与modify都可以修改列，但是change即可以修改列的名称，也可以修改列的类型，而modify只能修改列的类型。

  • 修改字段的顺序 -
    例如，alter table emp add age int(2) after name; alter table emp change age age1 int(4) first;
  • 修改表名 -
    ALTER TABLE table_name RENAME [TO] new_tablename,例如， alter table emp rename emp1;

三、DML 语句

DML语句是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作。

1- 插入记录

插入单条记录：
INSERT INTO table_name(field1, field2, … fieldn) VALUES(value1, value2, …valuen);例如，insert into emp(ename, hire,sal,deptno) values(‘zzx1’, ‘2000-1-1’, ‘2000’,1); 也可以不用指定字段名称，但是需要values后面的循序与字段的顺序一致。

插入多条记录：
INSERT INTO tablename (field1, field2, … fieldn)
VALUES
(record1_value1, record2_value2, … record1_valuen),
(record2_value1, record2_value2, … record2_valuen),
…
(recordn_value1, recordn_value2, … recordn_valuen);
例如insert into dept VALUES(5, “35”),(3,”232”);

2- 更新记录

更新记录：
UPDATE tablename SET filed1= value1,field2= value2, … filedn=valuen [WHERE CONDITION],例如，update emp set sal=4000 where ename=‘lisa’;`

更新多个表中的记录：
UPDATE t1,t2,…tn set t1.field=value1,tn.field=valuen [WHERE CONDITION]

3- 删除记录

DELETE FROM tablename [WHERE CONDITION]
例如，delete from emp where ename=‘lisa’;

删除多个表中的记录：
DELETE t1,t2,..tn FROM t1,t2,…tn [WHERE CONDITION]
PS: 不论是单表还是多表，不加where条件都会把表的所有记录删除；

4- 查询记录

SELECT FROM tablename [WHERE CONDITION]
例如,select from emp;,为查询emp这个表中的所有字段。如果想要部分字段的话，需要分别写出各个字段的名字，例如 select ename from emp;

（1）查询重复记录
用到“distinct”,例如 select distinct slaer from emp;

（2）条件查询
关键词：where，例如 select * from emp where name=Lisa;
其中“=”也可以是> < >= <= != 多条件还可以使用or和and等逻辑运算符进行多条件联合查询。

（3）排列顺序和限制
语法如下：SELECT * FROM tablename [WHERE CONDTION] [ORDER BY field1 [DESC/ASC],field2 [DESC/ASC],… fieldn [DESC/ASC]]。 不写关键字默认我ASC(升序排序)。ORDER BY 后面可以跟不同的字段，每个字段可以有不同的排序。

对于排序后的记录，如果只希望显示一部分，这时就需要用到LIMIT关键字。语法如下：SELECT … [LIMIT offset_start,row_count]。 例如select * from emp order by sal limit 3; select * from emp order by sal limit 1,3;

limit 经常和 order by 一起配合使用来进行记录的分页显示。

（4）聚合
用于汇总操作，例如统计。语法如下：

1
2
3
4
5
6
SELECT [field1,field2,…fieldn] fun_name
FROM tablename
[WHERE where_condition]
[GROUP BY field1,field2,…fieldn
 [WITH ROLLUP]]
[HAVING where_condtion]

其中：

  • fun_name 表示要做的聚合操作，也就是聚合函数，常用的有sum count max min
  • GROUP BY 表示要进行分类聚合的字段，比如按照部分分类统计员工数量，部门就应该写在group by 后面
  • WITH ROLLUP是可选语法，表明是否对分类聚合后的结果进行再汇总
  • HAVING 表示对分类后的结果再进行条件的过滤。PS：having和where都是过滤，where是在聚合前过滤，having是对聚合后的结果过滤。

统计总人数： select count(1) from emp;
统计各个部门的人数： select deptno,count(1) from emp group by deptno;
既统计部门人数又统计总人数：select deptno.count(1) from emp group by deptno with rollup;
统计部门人数大于1的部门： select deptno.count(1) from emp group by deptno having count(1)>1;
统计公司所有员工的公司薪水总额、最高和最低薪水：select sum(sal),max(sal),min(sal) from emp;

（5）表连接
表连接分为内连接和外链接，内连接仅仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录，最常用的是内连接。
例如，查询所有雇员的名字和所在的部门名称，因为雇员名称和部门分别放在表emp、dept中，因此需要使用表连接来查询。select ename,deptno from emp,dept where emp.deptno=dept.deptno;

（6）子查询
某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这时就会用到子查询。关键字：in、not in、=、exists、not exists等,例如， select * from emp where deptno in(select deptno from dept)。
PS：表连接在很多情况下用于优化子连接。

四、DCL语句

DCL语句主要是DBA用来管理系统中的对象权限时使用，开发人员很少使用，例如

1
mysql >  grant all PRIVILEGES on db_name.* to 'username'@'xxx.xxx.xx.x' identified by 'password' WITH GRANT OPTION;

grant 为放出权限，select为收回权限。