MySQL数据库——基础篇2

我们上一篇讲了sql语句中的DDL(数据定义语言)、DML（数据操作语言），这篇主要讲讲DQL(数据查询语言)。

---

---

DQL(Data Query Language，数据查询语言)

• 概念
  查询数据库数据，如SELECT语句
  简单的单表查询或多表的复杂查询和嵌套查询

• 特点
  数据库语言中最核心、最重要的语句
  使用频率最高的语句

• SELECT语法`

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
    [ left|out|inner  join  table_name2 ]    #联合查询
	[ WHERE  … ]   	#指定结果需满足的条件
	[ GROUP BY …]	#指定结果按照哪几个字段来分组
	[ HAVING …]	#过滤分组的记录必须满足的次要条件
	[ ORDER BY… ]	#指定查询记录按一个或者多个条件排序
	[ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  #指定查询的记录从哪条至哪条

我们先利用上篇知识创建一个新的text数据库，下边建立course, sc, student, teacher四张表。表中数据随便加点，然后下边案例全在此数据库下进行演示。

course表：

sc表：

student表：

teacher表：

指定查询字段

• 查询表中所有的数据列结果，采用“*”符号 （效率低，不推荐）

select   *   from student;

• 指定查询的结果数据列
  如只查询student表中的姓名、性别

SELECT Sname, sage  FROM  student;

如区分连接查询时两个表有同名的字段（指定该字段属于哪个表）

SELECT student.sname,course.cname
FROM student,course;

AS字句

• 作用：
  可给数据列取一个新别名
  可给表取一个新别名
  可把经计算或总结的结果用另外一个新名称来代替
• 语法：（注意：AS可以省略不写）

SELECT   S   AS  '学号'   FROM   student;
SELECT   a.S   FROM   student AS  a;

DISTINCT关键字的使用

• 作用：
  去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条
• 语法：

SELECT     DISTINCT   字段名1, 字段名2...     FROM   表名

WHERE条件语句

• 作用：
  用于检索数据表中符合条件的记录
  搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假
• 语法：

SELECT sname FROM student WHERE s=1;

BETWEEN AND范围查询

• 作用：
  BETWEEN AND范围查询
• 语法：

SELECT  字段列1，字段2 ,…FROM 表名 WHERE   字段x  BETWEEN  值1 AND  值2  

• 其实等同于>=和<=联合使用：

#查询分数表中成绩在80和90之间的所有记录
SELECT score FROM sc WHERE score BETWEEN 80 AND 90;
等同于:
SELECT score FROM sc WHERE score >= 80 AND score <= 90;

LIKE模糊查询

• 用法：
  在WHERE子句中，使用LIKE关键字进行模糊查询
  与“%”一起使用，表示匹配0或任意多个字符
  与“_”一起使用，表示匹配单个字符
• 语法：

#查询所有姓“李**”的学生信息
SELECT  Sname FROM student  
WHERE sname LIKE "李%";

使用IN进行范围查询

• 用法：
  使用IN进行范围查询
• 语法：

SELECT  字段列1，字段2 ,…FROM 表名 WHERE   字段x  IN  ( 值1,值2,值3…)  

• 注意：
  使用IN进行查询方式，更为简洁，效率更高
  查询的字段x的值，至少与括号中的一个值相同
  多个值之间用英文逗号隔开

连接查询（多表查询）

如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询

内连接（INNER JOIN）

• 用法： 在表中至少一个匹配时，则返回记录
• 语法：

SELECT   字段1,字段2,…  FROM  table_1 
INNER  JOIN   table_2    ON  table_1.字段x   =  table_2.字段y;

• 注意：
  INNER JOIN 与 JOIN 是相同的；
  如table_1中的行在table_2中没有匹配，则不返回；

外连接 ( OUT JOIN )

1. 左连接（LEFT JOIN）
   从左表（table_1）中返回所有的记录，即便在右（table_2）中没有匹配的行

SELECT   字段1,字段2,… FROM table_1 
LEFT  [ OUTER ]   JOIN   table_2    ON  table_1.字段x   =  table_2.字段y;

2. 右连接 ( RIGHT JOIN)
   从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行

SELECT   字段1,字段2,… FROM table_1 
RIGHT   [ OUTER ]   JOIN   table_2    ON  table_1.字段x   =  table_2.字段y;

等值和非等值的连接查询

• 用法：
  与单表查询类似，都是SELECT语句
  把多个表放到FROM后，并用逗号隔开
  可使用AS关键字取别名，便于引用
  如无重名查询字段则可省略数据表的指定
• 语法：

#非等值连接查询
SELECT  Sname, sorse   FROM  student, sc;  
#等值查询
SELECT  sname, sorse  FROM  student,  sc 
WHERE  student.s = sc.s;  

联合查询（UNION）

• 用处： 去除重复值（注意： 两个表结构要相同）
• 语法：

SELECT * FROM student
UNION
SELECT * FROM newstudent;

排序查询（ORDER BY）

• 用法：
  对SELECT语句查询得到的结果，按某些字段进行排序
  与DESC（降序）或ASC（升序）搭配使用，默认为ASC
• 语法：

SELECT * FROM sc   ORDER BY score DESC;  

分页查询（LIMIT）

• 用法：
  LIMIT m,n 或 LIMIT n OFFSET m
  限制SELECT返回结果的行数
  m 制定第一个返回记录行的偏移量
  n 制定返回记录行的最大数目
  m不指定则偏移量为0，从第一条开始返回前n条记录
• 语法：

SELECT * FROM score LIMIT 5       #返回前5条记录（从1开始5条）
SELECT * FROM score LIMIT 5,10    #返回6-15条记录 (从6开始10条)

子查询

• 用法：
  在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句
  嵌套查询可由多个子查询组成，求解的方式是由里及外
  子查询返回的结果一般都是集合，故而建议使用 IN 关键字
• 语法例句：
  查询语文成绩在80分以上的学生信息
  （学生信息在student表中，分数在score表中，他们之间有学生学号s连接;
  课程名在course表中，他和score表之间有课程号c连接，所以通过连接键一层一层查询）

SELECT * 
FROM student 
WHERE student.s in (
SELECT sc.s 
FROM sc 
WHERE  sc.score >= 80 AND 
sc.c = (
SELECT course.c 
FROM course 
where course.Cname  = '语文'))

分组查询（GROUP BY）

• 用法：
  使用GROUP BY关键字对查询结果分组
  对所有的数据进行分组统计
  分组的依据字段可以有多个，并依次分组
  与HAVING结合使用，进行分组后的数据筛选
• 语法例句：
  查询各课及格以上的平均分
  （给课程名分组，条件为分数>=60，连接课程表和分数表，显示课程名和平均分）

SELECT course.cname,AVG(sc.score) '平均分'
FROM course
JOIN sc
ON course.c = sc.c
GROUP BY course.cname
HAVING AVG(sc.score) >= 60;

上边我们求平均分的时候用到了AVG(sc.score)，这其实是MySQL中自带的函数，我们来简单介绍一些：

MySQL函数

分类：

• 统计函数
• 数学函数
• 字符串函数
• 日期和时间函数
• 系统函数
• 加密函数

统计函数

• COUNT( ) 返回满足SELECT条件的记录总和数，如 SELECT COUNT(*)…

SELECT COUNT(1) FROM student

• SUM( ) 返回数字字段或表达式列作统计，返回一列的总和

SELECT SUM(sc.score) FROM sc

• AVG( ) 通常为数值字段或表达列作统计，返回一列的平均值

SELECT AVG(sc.score) FROM sc

• MAX( ) 可以为数值字段、字符字段或表达式列作统计，返回最大的值

SELECT MAX(sc.score) FROM sc

• MIN( ) 可以为数值字段、字符字段或表达式列作统计，返回最小的值

SELECT MIN(sc.score) FROM sc

数学函数

• SELECT ABS(-32) ——绝对值
• SELECT MOD(15,7) ——MOD(n,m) 返回n被m除的余数
• SELECT FLOOR(1.23)—— 返回不大于 1.23 的最大整数值（向下取整）
• SELECT CEIL(1.23)——返回不小于1.23的最小整数值（向上取整）
• SELECT ROUND(1.48) ——四舍五入

字符串函数

• ASCII(str) 返回字符串str最左面的字符的ASCII代码值

SELECT ASCII('1')

• CONCAT(str1,str2,str3…) 返回来自参数连接的字符串

SELECT CONCAT('My','S','QL')

• LENGTH(str) 返回字符串长度

 SELECT LENGTH('text') 

• LOCATE(substr,str) 返回字符串substr在字符串str第一个出现的位置 如果substr不是在str中 返回0

SELECT LOCATE('bar','foobarbar')

• INSTR(str,substr) 返回substr在字符串str中的第一个出现位置

SELECT INSTR('foobarbar','bar')

• LEFT(str,len) 返回字符串str的最左面len个字符

SELECT LEFT('foobarbar',5)

• RIGHT(str,len) 返回字符串str的最右面len个字符

SELECT RIGHT('foobarbar',5)

• SUBSTRING(str,pos) 从字符串str的起始位置 pos返回一个字符串

SELECT SUBSTRING('foobarbar',1)

• TRIM(str) 去除前后空格

SELECT TRIM('   s   ')  去除前后空格

• REPLACE(字符串,‘需要替换的值’,‘新值’) 替换

SELECT REPLACE('  A b CD ','A','T')

• REVERSE(str) 颠倒字符

SELECT REVERSE('123456')

• INSERT(str,n,m,s) 字符串str从n索引开始m个字符用s替换

SELECT INSERT('123456789',5,3,'AB')

日期和时间函数

• NOW() 展示现在时间

• DAYOFWEEK(NOW()) 返回日期date的星期索引（1-星期天，2-星期一，…7-星期六）

• WEEKDAY(NOW()) 返回日期date的星期索引（0-星期一，1-星期二…6-星期天）

• DAYOFMONTH(‘1998-02-08’) 返回date的月份中的日期 在1-31之间

• MONTH(NOW()) 返回月份 1-12

• YEAR(NOW()) 返回年 范围 1000 -9999

• HOUR(NOW()) 返回小时 范围 0-23

• MINUTE(NOW()) 返回分钟 0-59

• SECOND(NOW()) 返回 秒 0-59

• DAYNAME(NOW()) 返回星期的名字

• QUARTER(‘98-04-01’) 返回一年中的季度 范围 1-4

系统函数

• VERSION() 获取MySQL的版本

• CONNECTION_ID() 返回连接数

• DATABASE() 返回当前数据库名称

• USER() 返回当前用户

• CHARSET() 返回字符编码集

加密函数

• PASSWORD(‘123456’) 不可逆加密
• MD5(‘123456’) 不可逆加密
• AES_ENCRYPT(‘明文’,‘key’) 加密函数，可逆
• AES_DECRYPT(‘密文’,‘key’) 解密函数，可逆

最后简单说一下事务吧

MySQL的事务处理

• 概念：
  事务就是将一组SQL语句放在同一批次内去执行
  如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
  注意： MySQL事务处理只支持InnoDB和BDB数据表类型（上文说过用ENGINE设置表的存储类型）
• 属性：（事务必须具备以下四个属性，简称ACID 属性）
  原子性（Atomicity）
  一致性（Consistency）
  隔离性（Isolation）
  持久性（Durability）
• 实现方法：

1. SET AUTOCOMMIT
   使用SET语句来改变自动提交模式
   （MySQL中默认是自动提交，使用事务时应先关闭自动提交 ）

SET AUTOCOMMIT  = 0;    # 关闭自动提交模式
SET AUTOCOMMIT  = 1;    # 开启自动提交模式

2. START TRANSACTION
   开始一个事务,标记事务的起始点
3. COMMIT
   提交一个事务给数据库
4. ROLLBACK
   将事务回滚，数据回到本次事务的初始状态
5. SET AUTOCOMMIT = 1;
   开启MySQL数据库的自动提交

演示：
我们创建一个商店数据库，下边建立一个 account表格存储用户名和用户余额，添加数据：

CREATE DATABASE shop;

CREATE TABLE account(
	id int(11) not null auto_increment PRIMARY key ,
	name VARCHAR(50) not null,
	cash DECIMAL(9,2) NOT null
) ENGINE = INNODB,charset = utf8;

INSERT INTO account(id,`name`,cash) VALUES (1,'A',1000),(2,'B',2000);

展示数据：

开始事务处理：我们让A给B500块，A给了就少500，B多500。A不够就整个事务失败，回到本次事务的初始状态。现在我们看A有1000块是够的，所以两个人的余额都会产生变化。

SET autocommit = 0;
START TRANSACTION;
UPDATE account SET cash = cash - 500 WHERE name = 'A';
UPDATE account SET cash = cash + 500 WHERE name = 'B';
-- ROLLBACK; -- 回滚
COMMIT; -- 提交
SET autocommit=1;

可以看到，我们提交事务后，再显示表的数据就会发生变化。A把自己的500给了B；
我们再回滚一下看看，是不是回到事务之前的初始状态：

可以看到，这次我们没有提交，而是回滚，A的钱没有再减少500块，B也没有再增加500块。我们的操作没有被执行。
所以：手动提交后正确就执行，不正确就不执行自己回滚
           手动回滚后不管正确与否都会回滚。