MYSQL数据库

MYSQL数据库

第四章 DML和事务处置处罚

MySQL的存储引擎

存储引擎的类型

MyISAM、InnoDB 、Memory、CSV等9种
MyISAM与InnoDB类型重要区别

名称InnoDBMyISAM事务处置处罚支持不支持数据行锁定支持不支持外键束缚支持不支持全文索引不支持支持表空间巨细较大**,约2****倍**较小 适用场所 使用MyISAM: 不需事务，空间小，以查询访问为主 使用InnoDB: 多删除、更新操作，安全性高，事务处置处罚及并发控制
检察当前默认存储引擎

SHOW VARIABLES LIKE ‘storage_engine%’；
修改存储引擎

修改my.ini配置文件
default-storage-engine= InnoDB
设置表的存储引擎

CREATE TABLE 表名( #省略代码)ENGINE=存储引擎;
示例： CREATE TABLE myisam ( id INT(4))ENGINE=MyISAM;
数据表的存储位置

MyISAM类型表文件：*.frm：表结构定义文件 .MYD：数据文件. MYI：索引文件
InnoDB类型表文件：*.frm：表结构定义文件 ibdata1文件
存储位置因操作体系而异，可查my.ini
DML语句——插入单条数据记录

INSERT INTO 表名 [(字段名列表)] VALUES (值列表);
注意

字段名是可选的，如省略则依次插入全部字段
多个列表和多个值之间使用逗号分隔
值列表和字段名列表一一对应
如插入的是表中部分数据，字段名列表必填
示例： INSERT INTO student(loginPwd,studentName,gradeId,phone,bornDate)VALUES(‘123’,‘黄小平’,1,‘13956799999’,‘1996-5-8’);
DML语句——插入多条数据记录

INSERT INTO 新表（字段名列表） VALUES(值列表1),(值列表2),……,(值列表n);
示例： INSERT INTO subject(subjectName,classHour,gradeID)VALUES(‘Logic Java’,220,1),(‘HTML’,160,1),(‘Java OOP’,230,2);
为克制表结构发生变革引发的错误，建议插入数据时写明详细字段名！
DML语句——将查询结果插入新表

示例： CREATE TABLE phoneList( SELECT studentName,phone FROM student);
如新表已存在，将会报错！
将查询结果插入新表

CREATE TABLE 新表(SELECT 字段1，字段2…… FROM 原表)；
如新表已存在，不能重复创建

编写SQL语句实现从学生表提取姓名、手机号两列数据存储到通讯录表中
CREATE TABLE phoneList(SELECT studentName,phoneFROM student);
数据更新

更新数据记录
UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件];
示例： UPDATE student SET sex = ‘女’;UPDATE student SET address = ‘北京女子职业技术学校家政班’WHERE address = ‘北京女子职业技术学校刺绣班’;
数据删除

删除数据记录
DELETE FROM 表名 [WHERE条件];
TRUNCATE TABLE 表名;
TRUNCATE语句删除后将重置自增列，表结构及其字段、束缚、索引保持稳定，执行速度比DELETE语句快
示例： DELETE FROM student WHERE studentName = ‘王宝宝’;TRUNCATE TABLE student;
事务

什么是事务

事务(TRANSACTION)是作为单个逻辑工作单位执行的一系列操作
多个操作作为一个整体向体系提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单位
事务的特性

事务必须具备以下四个属性，简称ACID 属性
1.原子性（Atomicity）
事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行
2.同等性（Consistency）
当事务完成时，数据必须处于同等状态
3.隔离性（Isolation）
并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
4.长期性（Durability）
事务完成后,它对数据库的修改被永久保持
如何创建事务

MySQL中支持事务的存储引擎有InnoDB和BDB
开始事务 BEGIN ;或START TRANSACTION;
提交事务 COMMIT ;
回滚（打消）事务 ROLLBACK ;
主动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务
关闭默认提交状态后，可手动开启、关闭事务
关闭/开启主动提交状态
SET autocommit = 0|1;
值为0：关闭主动提交
值为1：开启主动提交
注意： 关闭主动提交后，从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句竣事该事务
演示示例4：关闭主动提交
SET autocommit=0; 关闭主动 提交，以下视为一个事务
/–转账:张三的账户减少500元,李四的账户增长500元–/
UPDATE bank SET currentMoney=currentMoney-500
WHERE customerName=‘张三’;
UPDATE bank SET currentMoney=currentMoney+500
WHERE customerName=‘李四’;
COMMIT; 提交事务
UPDATE bank SET currentMoney=currentMoney-1000
WHERE customerName=‘张三’;
ROLLBACK; 回滚事务
SET autocommit = 1; 开启主动 提交，规复默认状态
第五章 SQL查询(一)

什么是查询

查询产生一个虚拟表
看到的是表情势显示的结果，但结果并不真正存储
每次执行查询只是从数据表中提取数据，并按照表的情势显示出来
查询语法

SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]]
列名称表 名过滤条件排序条件SELECT SCode**,StudentName,**AddressFROM****StudentsWHERE****SSEX = 0ORDER BY****SCode SELECT SCode,StudentName,Address
FROM Students
WHERE SSEX = 0
ORDER BY SCode
示例：
查询全部的行和列

SELECT * FROM Students
SELECT * FROM result
查询部分列

SELECT StudentName, Address FROM Students
查询部分行和列

SELECT StudentName ,Grade FROM StudentsWHERE Address = ‘北京’ 查询 “北京”地区的学生姓名和年级
SELECT StudentName,Address FROM StudentsWHERE sex=1 查询女学生姓名和地址
数据查询-列别名

使用AS命名列
SELECT StudentNo AS 学生编号,StudentName AS 学生姓名,
Address AS 学生地址
FROM Students
WHERE Address <> ‘河南新乡’
数据查询-空行、常量列

查询空行
SELECT StudentName FROM Students WHERE Email IS NULL
原来有数据，但数据被清除的列如何查询？
使用常量列
SELECT 姓名=StudeentName,地址= Address , ‘北京新兴桥’ AS 学校名称 FROM Students
Null 和 ‘ ’的区别
null是空值，表示未被赋值。
‘ ’是空字符串，表示非空，只不外值是一个非空字符串而已。
分页查询-limit

在我们使用查询语句的时候，常常要返回前几条大概中心某几行数据，这个时候怎么办呢？
LIMIT 子句可以被用于欺压 SELECT 语句返回指定的记录数
LIMIT 担当一个或两个数字参数。参数必须是一个整数常量
第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数量
初始记录行的偏移量是 0(而不是 1)
数据查询-限制行数

显示第一页,每页显示5行数据
SELECT StudentName, Address
FROM Students limit 0,5
显示第二页,每页显示5行数据
SELECT StudentName, Address
FROM Students limit 5,5
通配符

一类字符，代替一个或多个真正的字符
与LIKE关键字一起使用
通配符解释示例符合条件的值_一个字符A LIKE ‘C_’cCS**、Cd等**%恣意长度的字符串B LIKE ‘CO%’CONST**、COKE等** WHERE Address LIKE ‘%山东%’
WHERE StudentName LIKE ‘姜_’
模糊查询

用IS NULL查询空值
模糊查询—BETWEEN

查询某一列中内容在特定范围内的记录
SELECT StudentNo, studentResult FROM result WHERE studentResult BETWEEN 60 AND 80
用BETWEEN查询特定范围内的记录
模糊查询— IN

查询某一列中内容与所列出的内容列表匹配的记录
SELECT StudentName AS 学生姓名,Address AS 地址 FROM Students WHERE Address IN (‘北京’,‘广州’,‘上海’)
查询单列排序

升序分列 ASC

SELECT StudentNo,StudentResult FROM Result ORDER BY StudentResult
降序分列 DESC

SELECT StudentNo,StudentResult FROM StudentResult ORDER BY StudentResult DESC
按多列排序

SELECT StudentNo AS 学生编号, SubjetctId AS 课程ID,
StudentResult AS 成绩
FROM Result
WHERE StudentResult > 60
ORDER BY SubjetctId, StudentResult
MySQL 中的函数

常用的几类函数
字符串函数：用于控制返回给用户的字符串
函数名描　　述示　　例CONCAT(s1,s2…,sn)将s1,s2…,sn毗连成字符串SELECT CONCAT('a‘,'b‘,‘c’)LENGTH返回传递给它的字符串长度SELECT LENGTH(‘MySQL 课程**')****返回：**12UPPER把传递给它的字符串转换为大写**SELECT UPPER(‘MySQL课程’)****返回：**MYSQL 课程LTRIM清除字符左边的空格SELECT LTRIM (’ 周智宇 **')**返回：周智宇 （反面的空格保留）RTRIM清除字符右边的空格SELECT RTRIM (’ 周智宇 **')**返回： 周智宇（前面的空格保留）RIGHT从字符串右边返回指定命目的字符**SELECT RIGHT(‘买卖提.吐尔松’,3)**返回：吐尔松REPLACE更换一个字符串中的字符**SELECT REPLACE(‘莫乐可切.杨可’,‘可’,‘兰’)返回：莫乐兰切.**杨兰INSERT(str,x,y,instr)将字符串str从第x位置开始，y个字符长的子串更换为字符串instr，返回结果SELECT INSERT(‘AbcdeFG’,2,4,‘我的音乐我的世界’)返回：A我的音乐我的世界EFG 日期函数：用于操作日期值
函数名描　　述示　　例**CURDATE****NOW()**CURTIME取得当前的体系日期取得当前的日期和时间取得当前的体系时间**SELECT CURDATE()**返回：今天的日期DATE_ADD将指定的数值添加到指定的日期部分后的日期SELECT DATE_ADD(‘2014-4-4’,INTERVAL 2 MONTH)返回：以当前的日期格式返回2014-06-04DATE_SUB从日期减去指定的时间隔断**SELECT DATE_SUB(‘2014-5-4’,INTERVAL 2 DAY)****返回：**2014-05-02DATE_FORMART依照指定的fmt格式格式化日期date值**Select DATE_FORMAT(CURDATE(),‘%m-%d-%y’)****返回：**10-08-15 数学函数：用于对数值举行代数运算
函数名描　　述示　　例RAND返回从 0 到 1 之间的随机 float 值**SELECT RAND( )****返回：**0.79288062146374ABS取数值表达式的绝对值**SELECT ABS(-43)****返回：**43CEILING取大于或即是指定命值、表达式的最小整数**SELECT CEILING(43.5)****返回：**44FLOOR取小于或即是指定表达式的最大整数**SELECT FLOOR(43.5)****返回：**43POWER取数值表达式的幂值**SELECT POWER(5,2)****返回：**25ROUND将数值表达式四舍五入为指定精度**SELECT ROUND(43.543,1)****返回：**43.5SIGN对于正数返回**+1****，对于负数返回****-1****，对于0则返回****0**SELECT SIGN(-43)返回：-1SQRT取浮点表达式的平方根**SELECT SQRT(9)****返回：**3 聚合函数

sum,avg,max,min,count
对一组值举行盘算，并返回盘算后的值 ，具有统计数据的作用
总分 sum

1. SELECT SUM(student_result) FROM result;
2. SELECT student_no,SUM(student_result) FROM result
3. WHERE        student_no='bq0010';

复制代码

平均分 avg

1. SELECT AVG(student_result) FROM result;

复制代码

最大值 max 最小值 min

1. SELECT MAX(student_result),MIN(student_result) FROM result;

复制代码

计数 count

1. SELECT COUNT(*) FROM student;
2. SELECT COUNT(*) FROM student WHERE grade_id=1;

复制代码

更换查询

更换查询结果中的数据在对表举行查询时，偶然对所查询的某些列盼望得到的是一种概念而不是详细的数据。
使用查询中的CASE表达式，格式为：
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
……
ELSE 表达式
END
示例：

1. SELECT result_id,student_result,
2. CASE
3. WHEN student_result<60 AND student_result>=0 THEN '不及格'
4. WHEN student_result>=60 AND student_result<=80 THEN '及格'
5. WHEN student_result>=80 AND student_result<=100 THEN '优秀'
6. ELSE '作弊'
7. END AS '成绩等级'
8. FROM result

复制代码

分组查询

SELECT …… FROM <表名> WHERE ……GROUP BY ……
group by 有一个原则,就是 select 反面的全部列中,没有使用聚合函数的列,必须出如今 group by 反面
注意： SELECT列表中只能包罗：1、被分组的列2、为每个分组返回一个值的表达式，如聚合函数

1. SELECT grade_id,COUNT(*) FROM student GROUP BY grade_id;
3. SELECT grade_id,sex,COUNT(*) FROM student GROUP BY grade_id,sex
4. ORDER BY grade_id;

复制代码

分组筛选

SELECT …… FROM <表名>WHERE ……GROUP BY ……HAVING……
示例：

1. SELECT COUNT(*) AS 人数,gradeId AS 年级*
3. *FROM Students*
5. GROUP BY gradeId
7. HAVING COUNT(*)>15

复制代码

1. SELECT grade_id,COUNT(*) FROM student WHERE sex='男'
2. GROUP BY grade_id HAVING COUNT(*)>3;

复制代码

WHERE与HAVING对比

WHERE子句：
用来筛选 FROM 子句中指定的操作所产生的行
GROUP BY子句：
用来分组 WHERE 子句的输出
HAVING子句：
用来从分组的结果中筛选行

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。