SQL基础

概念

SQL: 结构化查询语言(Structured Query Language)

mysql配置程序

F:\mysql\server\bin\MySQLInstanceConfig.exe

数据库的删除命令

drop 表索引，表的存储过程，表结构和数据都会删除

delete 删除部分表记录(可以配合where条件)

truncate 删除表的所有记录

SQL语法

SQL语句可以在单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL不区别大小写，建议关键字使用大写
如SELECT*FROM booktable;

SQL语句分类

1. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
  > 创建、删除、修改：库、表结构！！！
DDL：数据库或表的结构操作(*****)

2. DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
  > 增、删、改：表记录
DML：对表的记录进行更新（增、删、改）(*****)

3. DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DCL：对用户的创建，及授权！

4. DQL*****（Data Query Language）：数据查询语言，用来查询记录（数据）。
DQL：对表的记录的查询（*****，难点）

DML(对表的记录进行更新（增、删、改）)

1. 插入数据

* INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);
   > 在表名后给出要插入的列名，其他没有指定的列等同与插入null值。所以插入记录总是插入一行，不可能是半行。
   > 在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应
* INTERT INTO 表名 VALUES(列值1, 列值2)
   > 没有给出要插入的列，那么表示插入所有列。
   > 值的个数必须是该表列的个数。

2. 修改数据

* UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]
* 条件(条件可选的)：
   > 条件必须是一个boolean类型的值或表达式：UPDATE t_person SET gender='男', age=age+1 WHERE sid='1';
   > 运算符：=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND

例句：
WHERE age >= 18 AND age <= 80
WHERE age BETWEEN 18 AND 80

WHERE name='zhangSan' OR name='liSi'
WHERE name IN ('zhangSan', 'liSi')
WHERE age IS NULL, 不能使用等号
WHERE age IS NOT NULL

DQL:数据查询语言，用来查询记录（数据）

一、基本查询

1. 字段(列)控制

1) 查询所有列
   SELECT * FROM 表名;
   SELECT * FROM emp;
   --> 其中“*”表示查询所有列

2) 查询指定列
   SELECT 列1 [, 列2, ... 列N] FROM 表名;
   SELECT empno, ename, sal, comm FROM 表名;

3) 完全重复的记录只一次
   当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时很少会有这种情况，但只查询一列（或几列）时，这总可能就大了！
   SELECT DISTINCT * | 列1 [, 列2, ... 列N] FROM 表名;
   SELECT DISTINCT sal FROM emp;
   --> 保查询员工表的工资，如果存在相同的工资只显示一次！

4) 列运算
   I 数量类型的列可以做加、减、乘、除运算
     SELECT sal*1.5 FROM emp;
     SELECT sal+comm FROM emp;

   II 字符串类型可以做连续运算
     SELECT CONCAT('$', sal) FROM emp;

   III 转换NULL值
     有时需要把NULL转换成其它值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这时希望把NULL当前0来运算。
     SELECT IFNULL(comm, 0)+1000 FROM emp;
     --> IFNULL(comm, 0)：如果comm中存在NULL值，那么当成0来运算。

   IV 给列起别名
     你也许已经注意到了，当使用列运算后，查询出的结果集中的列名称很不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了
     SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;
   --> 其中AS可以省略
例句：
查询指定列
SELECT empno,ename,sal 工资,comm FROM emp;

查询不重复记录
SELECT DISTINCT job,sal,ename FROM emp;

查询结果做运算
SELECT*,sal*2 实际工资 FROM emp;

IFNULL条件转换NULL值
SELECT*,sal+IFNULL(comm,0) 实际绩效工资 FROM emp;

CONCAT字符串连接
SELECT CONCAT("我是",ename) FROM emp;
SELECT CONCAT('我是',ename) FROM emp;

用IS NOT NULL做NULL条件判断，不能用!=NULL或<>NULL
SELECT*FROM emp WHERE comm IS NOT NULL;

给列起别名
SELECT ename AS 姓名,job AS 工作岗位 FROM emp;
或
SELECT ename 姓名,job 工作岗位 FROM emp;

2.条件控制

1) 条件查询
    与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录。
    * SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
    * SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
    * SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');
2) 模糊查询
    当你想查询姓张，并且姓名一共两个字的员工时，这时就可以使用模糊查询
    * SELECT * FROM emp WHERE ename LIKE '张_';
    --> 模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。
    --> 上面语句查询的是姓张，名字由两个字组成的员工。
    * SELECT * FROM emp WHERE ename LIKE '___'; /*姓名由3个字组成的员工*/

    如果我们想查询姓张，名字几个字可以的员工时就要使用“%”了。
    SELECT * FROM emp WHERE ename LIKE '张%';
    --> 其中%匹配0~N个任意字符，所以上面语句查询的是姓张的所有员工。
    SELECT * FROM emp WHERE ename LIKE '%阿%';
    --> 千万不要认为上面语句是在查询姓名中间带有阿字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到。
    SELECT * FROM emp WHERE ename LIKE '%';
--> 这个条件等同与不存在，但如果姓名为NULL的查询不出来！

例句：
条件查询

按工资范围查询
SELECT*FROM emp WHERE sal >= 2000 AND sal <=20000;
SELECT*FROM emp WHERE sal BETWEEN 2000 AND 20000;

按职位查询
SELECT * FROM emp WHERE job IN('销售员','经理') ORDER BY job;
关键字：IN  或  NOT IN

模糊查询

姓张，且为两个字
SELECT*FROM emp WHERE ename LIKE '张_';

姓张，字数不限
SELECT*FROM emp WHERE ename LIKE'张%';

查询以 牛 字结尾的姓名
SELECT*FROM emp WHERE ename LIKE '%牛';

查询姓名中带 牛 字的，无论是开头，中间，还是结尾带 牛 字的，都会被查询出来
SELECT*FROM emp WHERE ename LIKE'%牛%';

二、排序

1) 升序
      SELECT * FROM WHERE emp ORDER BY sal ASC;
      --> 按sal排序，升序！
      --> 其中ASC是可以省略的
2) 降序
      SELECT * FROM WHERE emp ORDER BY comm DESC;
      --> 按comm排序，降序！
      --> 其中DESC不能省略
3) 使用多列作为排序条件
      SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;
      --> 使用sal升序排，如果sal相同时，使用comm的降序排

例句：
按sal升序排列，若sal相同，按comm降序排列
SELECT*FROM emp ORDER BY sal ASC,comm DESC;

三、聚合函数(用来做某列的纵向运算的)

 聚合函数用来做某列的纵向运算，在条件中不能出现聚合函数。
1) COUNT
      SELECT COUNT(*) FROM emp;
      --> 计算emp表中所有列都不为NULL的记录的行数
      SELECT COUNT(comm) FROM emp;
      --> 云计算emp表中comm列不为NULL的记录的行数
2) MAX
      SELECT MAX(sal) FROM emp;
      --> 查询最高工资
3) MIN
      SELECT MIN(sal) FROM emp;
      --> 查询最低工资
4) SUM
      SELECT SUM(sal) FROM emp;
      --> 查询工资合
5) AVG
      SELECT AVG(sal) FROM emp;
      --> 查询平均工资
      
例句：
查询总人数(注意，下句中数字1和*的意思相同，都表示所有。不论是1还是2，3等，都一样的意思)
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;

查询工资sal总额
SELECT SUM(sal) FROM emp;

查询平均工资
SELECT AVG(sal) FROM emp;
查询最高工资的员工
SELECT MAX(sal),ename FROM emp;

查询最低工资的员工
SELECT MIN(sal),ename FROM emp;

查询以上所有
SELECT COUNT(*) 总人数,COUNT(sal) 工资总和,AVG(sal) 平均工资,MAX(sal) 最高工资,MIN(sal) 最低工资 FROM emp;

三、分组查询

分组查询是把记录使用某一列进行分组，然后查询组信息。
例如：查看所有部门的记录数。
    SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
    --> 使用deptno分组，查询部门编号和每个部门的记录数
    SELECT job, MAX(SAL) FROM emp GROUP BY job;
    --> 使用job分组，查询每种工作的最高工资

  组条件
  以部门分组，查询每组记录数。条件为记录数大于3
  SELECT deptno, COUNT(*) 
FROM emp 
GROUP BY deptno HAVING COUNT(*) > 3;

关键字的先后顺序：
select
from
where         （分组前）条件关键字
group by      分组关键字
having        (分组)后置条件关键字
order by      排序关键字

例句：

查询各部门人数
SELECT deptno 部门编号,COUNT(*) 总人数 
FROM emp 
GROUP BY deptno;

查询各岗位人数
SELECT job 工作岗位,COUNT(*) 人数 
FROM emp 
GROUP BY job;

查询各岗位中工资大于20000的人数-------------------(分组前的条件)
SELECT job,COUNT(*) 人数 
FROM emp 
WHERE sal>=2000 
GROUP BY job;

查询各岗位中工资大于2000，且人数不少于3---------------(分组后的条件)
SELECT job 工作岗位,COUNT(*) 
FROM emp 
WHERE sal>=2000 
GROUP BY job HAVING COUNT(*)>=3;

HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

四、 limit子句(方言)

LIMIT用来限定查询结果的起始行，以及总行数。
注意：mysql记录下标从0计数

    例如：查询起始行为第5行，一共查询3行记录
    SELECT * FROM emp LIMIT 4, 3;
    --> 其中4表示从第5行开始，其中3表示一共查询3行。即第5、6、7行记录。

    select * from emp limit 0, 5;

    1. 一页的记录数：10行
    2. 查询第3页
    select * from emp limit 20, 10;

   (当前页-1) * 每页记录数
   (3-1) * 10

查询第17页，每页8条记录
(17-1) * 8, 8

例句：
查询第3页，每页4条记录  
>>>>>>>>>>>>>>>>>>>>>>>> (3-1）*4=8,即，从第9行记录开始查询，第9行下标为8
SELECT * FROM stu LIMIT 8,4;

五、多表查询

多表查询分类：
    * 合并结果集(了解)
    * 连接查询
    * 子查询

合并结果集

合并结果集
    * 要求被合并的表(实际是被合并的结果集)中，列的类型和列数相同（上下合并，而不是横向合并）
    * UNION，去除重复行
    * UNION ALL，不去除重复行

SELECT * FROM cd
UNION ALL
SELECT * FROM ab;

例句：

不去除重复行
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;

去除重复行
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;

内链接（内连接多表查询）

    * 方言：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
    * 标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
    * 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
* 内连接查询出的所有记录都满足条件。

内连接查询的语句逻辑可以参考下面SQL语句推导：
SELECT *
FROM emp,dept;

下列三种SQL语句，建议使用标准版语句，便于数据库的更换；
例句：

标准版（通用）SQL语句
查询每个员工的姓名，工资，部门名称
SELECT e.ename,e.sal,d.dname
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno;

mysql方言版
查询每个员工的姓名，工资，部门名称
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;

自然版
查询每个员工的姓名，工资，部门名称
SELECT e.ename,e.sal,d.dname
FROM emp e NATURAL JOIN dept d

外连接(外连接多表查询)

外连接有一主一次，左外即左表为主
如下面例子：即emp为主，那么主表中所有的记录无论满足条件与否，都会被打印出来。
当不满足条件是，右表部门使用NULL来补位。

左外连接
查询所有员工的姓名，薪水，所属部门(部门名称)
SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

------------用IFNULL做判断后的假名------------------
SELECT e.ename,e.sal,IFNULL(d.dname,'无部门') AS dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

右外连接emp
查询所有部门中员工的姓名，薪水，所属部门(部门名称)
SELECT e.ename,e.sal,d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

全连接
查询所有部门的所有员工的姓名，薪水，所属部门(部门名称)---利用左外连接和右外连接 进行合并结果集 完成全外连接查询（因为mysql不支持全连接查询）

SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
UNION
SELECT e.ename,e.sal,d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

子查询

子查询：一条查询语句中有多个SELECT关键字，就是子查询(也叫嵌套查询)

1.    出现的位置：
select后也可以有，但是不规范，所以不用
    * where后作为条件存在
    * from后作为表存在(多行多列)

  2. 条件
    * (***)单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
    * (**)多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
    * (*)单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)
* (***)多行多列：SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

例句：
1.    查询本公司工资最高的员工信息
        select* from emp 
where sal=
(select max(sal) from emp);

2.    查询emp表中职位和部门与殷天正一样的所有员工的姓名，部门，工作岗位
SELECT b.ename,b.deptno,b.job FROM emp b WHERE (deptno,job) IN
(SELECT deptno,job FROM emp WHERE ename='殷天正')     

3．查询员工中工资比30部门中所有人工资高的员工的姓名，工资
SELECT a.ename,a.sal FROM emp a
WHERE sal > ALL
(SELECT MAX(sal) FROM emp WHERE deptno='30')

3.    查询员工中工资比任何一个30部门中工资高的员工的姓名，工作岗位，工资
SELECT a.ename,a.job,a.sal FROM emp a
WHERE sal> ANY
(SELECT sal FROM emp WHERE deptno='30')

员工表练习

员工表建表SQL：
http://op05inpyd.bkt.clouddn.com/%E5%91%98%E5%B7%A5%E8%A1%A8%E5%BB%BA%E8%A1%A8SQL.sql
员工表查询练习SQL:
http://op05inpyd.bkt.clouddn.com/%E5%91%98%E5%B7%A5%E8%A1%A8SQL%E5%AD%A6%E4%B9%A0.sql

多表查询练习之学生表

参考博客：
http://www.cnblogs.com/qixuejia/p/3637735.html
建表SQL
http://op05inpyd.bkt.clouddn.com/%E5%AD%A6%E7%94%9F%E8%A1%A8%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2%E5%BB%BA%E8%A1%A8%E8%AF%AD%E5%8F%A5.sql
学生表多表查询练习：
SQL文件链接：
http://op05inpyd.bkt.clouddn.com/%E5%AD%A6%E7%94%9F%E8%A1%A8%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2%E7%BB%83%E4%B9%A0%E7%AD%94%E6%A1%88%E8%AF%AD%E5%8F%A5.sql

SQL语句

2017/9/22 9:54:52 (改)

  1、查询“001”课程比“002”课程成绩高的所有学生的学号
  
  --------------子查询版-------------------------------
  SELECT a.sid 学生编号
  FROM 
  (SELECT sid,score FROM sc WHERE cid='1')a,
  (SELECT sid,score FROM sc WHERE cid='2')b
  WHERE a.score>b.score AND a.sid=b.sid

  -----------------非子查询版---------------------------
  SELECT a.*
  FROM sc AS a
  LEFT JOIN sc AS b ON a.Sid = b.Sid AND a.Cid = '1' AND b.Cid='2'
  WHERE  a.score>b.score

  2、查询平均成绩大于60分的同学的学号和平均成绩
  
  SELECT sid,a.score FROM 
  (SELECT sid,AVG(score) score FROM sc GROUP BY sid)a
  WHERE a.score>60;

  -----------------非子查询版---------------------------
  SELECT sid,AVG(SCORE) 
  FROM sc  
  GROUP BY sid
  HAVING AVG(SCORE) >60;

  3、查询所有同学的学号、姓名、选课数、总成绩
  
  SELECT a.sid,a.sname,b.num,c.sumscore
  FROM student a,
  (SELECT sid,COUNT(cid) num FROM sc GROUP BY sid) b,
  (SELECT sid,SUM(score) sumscore FROM sc GROUP BY sid) c
  WHERE a.sid=b.sid AND b.sid=c.sid;

  -----------------非子查询版---------------------------
  SELECT a.sid,a.sname,COUNT(b.Cid),SUM(b.score)
  FROM student AS a
  LEFT JOIN sc AS b ON a.Sid = b.Sid
  GROUP BY a.Sid;

  4、查询姓“叶”的老师的个数
  
  SELECT COUNT(*) 个数 FROM teacher
  WHERE Tname LIKE '叶%';

  -----------------或-----------------------------------
  SELECT COUNT(1) 个数 FROM teacher
  WHERE Tname LIKE '叶%';
  
  5、查询没学过“叶平”老师课的同学的学号、姓名
  
  有误：    SELECT a.sid ,b.sname FROM sc a,student b
      WHERE cid !=
      (SELECT cid FROM course WHERE tid = 
      (SELECT tid FROM teacher WHERE tname='叶平'))
      AND a.sid=b.sid GROUP BY sid;

  -------------------------改----------------------------------
  SELECT Sid,Sname FROM student
  WHERE sid NOT IN(
  SELECT a.Sid
  FROM student AS a
  LEFT JOIN sc AS b ON b.Sid = a.Sid
  LEFT JOIN course AS c ON c.Cid = b.Cid 
  INNER JOIN teacher AS d ON d.Tid = c.Tid AND d.Tname ='叶平'
  )

  6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
  SELECT a.sid,c.sname FROM student c,
  (SELECT sid FROM sc WHERE cid=1)a,
  (SELECT sid FROM sc WHERE cid=2)b
  WHERE a.sid = b.sid AND c.sid=b.sid
  
  7、查询学过“叶平”老师所教的所有课的同学的学号、姓名
  
  SELECT a.sid,sname FROM sc a,student b WHERE cid = 
  (SELECT cid FROM course WHERE tid = 
  (SELECT tid FROM teacher WHERE tname='叶平'))
  AND a.sid=b.sid
  
  8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
  
  SELECT a.sid,sname FROM student c,
  (SELECT sid,score FROM sc WHERE cid='1')a,
  (SELECT sid,score FROM sc WHERE cid='2')b
  WHERE a.score>b.score AND a.sid  = b.sid AND c.sid=b.sid
  
  9、查询所有课程成绩小于60分的同学的学号、姓名
  
  SELECT b.sid,a.sname FROM student a,
  (SELECT sid FROM sc WHERE score <60)b
  WHERE a.sid=b.sid GROUP BY sname
  
  10、查询没有学全所有课的同学的学号、姓名
  SELECT a.sid,sname FROM student b,
  (SELECT sid,COUNT(cid) anum  FROM sc GROUP BY sid)a
  WHERE a.anum<
  (SELECT COUNT(cname)bnum FROM course)
  AND a.sid=b.sid

MySql的备份与恢复数据库

mysqldump -uroot -p123 exam>C:\mydb1.sql

存储过程

存储过程的优点

增强SQL语句的功能和灵活性；
实现较快的执行速度；
减少网络流量。

创建存储过程

查询中拼接字符串

拼接列与字符串

  select username,password,CONCAT(username,"@test.com") as email from pre_ucenter_members

未完待续…..

概念