数据库

数据库基础

SQL语句分为三类：

• DDL：数据定义语言，常用的关键字包括：create、drop、alter等（针对数据库内部的对象）
• DML：数据操纵语言，增删改查，常用关键字：add、delete、update、select等（针对表内部的数据）
• DCL：数据控制语言，定义了访问权限和安全级别，主要的关键字包括：grant、revoke主要是DBA用来管理系统中的对象权限时所使用的

---

一、DDL（数据定义语言）

• 创建、查看、删除数据库
  $mysql –u root –p；
  mysql> create database test1;
  mysql> show databases；
  mysql>use test1；
  mysql>show tables；
  mysql>drop database test1;

• 创建、查看、删除一个名为emp的表
  mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
  mysql> desc emp；
  mysql>show create table emp \G; 全面地查看表定义信息（\G是使得记录能够按照字段竖着排列）
  mysql>show tables；
  mysql>drop table emp;

• 修改表
  mysql>alter table emp modify ename varchar(20); 修改表格的字段定义
  mysql>desc temp;
  mysql> alter table emp add column age int(3); 在emp表格上增加字段
  mysql>alter table emp drop column age;
  mysql>alter table emp change age age1 int(4)；
  mysql>alter table emp rename emp1 ;
  mysql>alter table emp add birth date after ename; 修改字段的排列顺序（first /after column_name）
  mysql>alter table emp modify age int(3) first; 修改字段age，放在最前面

二、DML（数据操纵语言）

• 向表内插入记录（insert into）
  mysql>insert into emp (ename,hiredate,sal,deptno) values(‘zzxl’,’2001-01-01’,’2000’,1);
  或者
  mysql>insert into emp values(‘Lisa’,’2003-02-01’,’3000’,2);
  或者
  mysql>insert into emp (ename,sal) values(‘dony’,’1000’);
  或者一次插入两条记录
  mysql>insert into dept values(5,’dept5’),(6,’dept6’);
  mysql> show databases；

• 删除记录（delete）
  mysql>delete from emp where ename=’dony’;
  或者一次删除多个表的数据
  mysql>select * from emp;
  mysql>select * from dept;
  mysql>delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
  ps:如果from后面用表的别名，那么delete后面也要用表的别名，否则会提示错误

• 查询记录（select）
  mysql>select * from emp; 将表emp的记录全部取出
  mysql>select distinct deptno from emp; 查询不重复的记录
  mysql>select * from emp where deptno=1; 条件查询
  或者
  mysql>select * from emp where deptno=1 and sal<=3000;

• 排序和限制（order by）
  关键字desc表示降序排列，asc**重点内容**表示升序，默认是升序排列
  mysql>select * from emp order by sal;
  mysql>select * from emp order by deptno,sal desc;
  ps:对于deptno相同的两条记录，可以按照sal降序排列
  mysql>select * from emp order by sal limit 3; 只显示排序后【0,3】行记录
  mysql>select * from emp order by sal limit 1,3; 显示排序后【1,3】行记录

• 聚合
  语法：select [field1,field2,……fieldn] fun_name
  From tablename
  [where where_contition]
  [group by field1,field2,…..fieldn
  [with rollup]]
  [having where_contition]
  Fun_name表示聚合函数，常用的有sum（求和）、count（*）（记录数）、max、min等
  Group By表示要进行分类聚合的字段
  With rollup是可选语法，表明是否对分类聚合后的结果进行再汇总
  Having表示对分类后的结果再进行有条件的过滤
  例：
  mysql>select count(1) from emp; 统计公司的总人数
  mysql>select deptno count(1) from emp group by deptno; 统计各部门人数
  mysql>select deptno count(1) from emp group by deptno with rollup; 既统计各部门人数，又统计总人数
  mysql>select deptno count(1) from emp group by deptno having count(1)>1; 统计人数大于1的部门
  mysql>select * from emp;
  mysql>select sum(sal)、max(sal)、min(sal) from emp; 统计公司所有员工的薪水总额、最高和最低薪水

• 表连接——同时显示多个表中的字段
  内连接：
  mysql>select * from emp;
  mysql>select * from dept;
  mysql>select ename,deptname from emp,dept where emp.deptno=dept.deptno; 查询所有雇员的名字和所在部门的名称

  • 子查询
    关键字主要包括：in,not in,=,!=,exits,not exits等
    例如：
    从emp表中查询出所有部门在dept表中的所有记录
    mysql>select * from emp;
    mysql>select * from dept;
    mysql>select * from emp where deptno in (select deptno from dept);
    ps:当子查询记录数唯一时，可以使用=代替in
    如：
    mysql>select * from emp where depyno = (select deptno from dept limit 1);
    某些情况下，子查询可以转化为表连接
    mysql>select * from emp where deptno in (select deptno from dept);
    等同于
    mysql>select emp.* from emp,dept where emp.deptno=dept.deptno;
    表连接在很多情况下用于优化子查询

  • 记录联合
    关键字：union（删除重复之后合并显示）、union all（将结果直接合并显示出来）
    例:
    将emp和dept表中的部门编号的集合显示出来
    mysql>select * from emp;
    mysql>select * from dept;
    mysql>select deptno from emp
    ->union all
    ->select deptno from dept;
    将结果去掉重复之后显示：
    mysql>select deptno from emp
    ->union
    ->select deptno from deptno;

三、DCL（数据控制语言）

例,创建一个数据库用户z1，具有对sakila数据库中所有表的select/insert权限：
mysql>grant select,insert on sakila.* to ‘z1’@’localhost’ identified by ‘123’;
Query OK……
mysql>exit
Bye
$mysql –uz1 –p123
mysql>use sakila
mysql>insert into emp values(‘bzshen’,’2005-04-01’,3000,’3’);
mysql>select * from emp;

由于权限变更，需将z1的insert权限收回，只能对数据进行select操作：
mysql>mysql –uroot
mysql>revoke insert on sakila.* from ‘z1’@’localhost’;
mysql>exit
$mysql –uz1 –p123
mysql>use sakila
mysql>insert into emp values(‘bzshen’,’2005-04-01’,3000,’3’);
Error……