2、MySQL数据库小结

一、标准SQL语句

1. DQL查询语句：由select关键字完成，是SQL语句中最复杂，功能最丰富的语句
    

2. DML(Data Manipulation Language，数据操作语言)：主要由insert、update、delete 三个关键字完成
    
3. DDL(Data Definition Language，数据定义语言)：主要由create、alter、drop、truncate 四个关键字完成
    
4. DCL(Data Control Language，数据控制语言)：主要由 grant、revoke 两个关键字完成
    
5. 事务控制语句：主要由 commit、rollback、savepoint 三个关键字完成

二、MySQL事务

四个特性：

1. 原子性(atomicity)
事务是应用中最小的执行单位，是不可再分的最小逻辑执行体。
            
2. 一致性(consistency)
        事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。
        例如银行在两个账户之间转账：从A账户向B账户转入1000元，系统先减少A账户的1000元，再给B账户增加1000元。
       如果全部执行成功，数据库处于一致性状态。如果只执行完A账户金额的减少操作，没有增加B账户的金额，
       数据库就处于不一致性状态。一致性是通过原子性保证的。
            
3. 隔离性(isolation)
       各个事务的执行互不干扰，任何一个事务的内部操作对其他事务都是隔离的。
       并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能互相影响。
            

4. 持续性(durability)
事务一旦提交，对数据库所做的任何改变都要保存进物理数据库。

三、数据库设计三大范式

1、第一范式（确保每列保持原子性）

如果所有字段值都是不可分割的原子值，则满足第一范式。

2、第二范式（确保表中每列都与主键相关）

在第一范式的基础上保证每一列都与主键相关，而不能只与主键某一部分相关（联合主键）。即一个数据库表中，一个表中只存一种数据，不可以把多种数据保存在一张数据库表中，减少数据库冗余。

3、第三范式（确保每列都与主键直接相关，而非间接相关）

四、数据库约束

• NOT NULL：非空约束，指定某列不能为空。
• UNIQUE：唯一约束，指定某列或者几列组合不能重复。
• PRIMARY KEY：主键，指定该列的值可以唯一地表示该条记录。
• FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。
• CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

五、常用SQL语句

1、创建表语法

DROP TABLE IF EXISTS `student`;

create table `student`(
                `studentNo` int(4) not null comment '学号' primary key, #非空，主键
                `loginPwd` varchar(20) not null comment '密码',
                `studentName` varchar(50) not null comment '学生姓名',
                `sex` char(2) default '男' not null comment '性别', #非空，默认值“男”
                `gradeId` int(4) unsigned comment '年级编号', #无符号数
                `phone` varchar(50) comment '联系电话',
                `address` varchar(255) default '地址不详' comment '地址', #默认值“地址不详”
                `birthday` datetime comment '出生日期',
                `email` varchar(50) comment '电子邮箱',
                `identityCard` varchar(18) unique key comment '身份证号'  #唯一约束  
 )comment="学生表";

2、修改表语法

增加列

alter table user
add
 (
 aaa varchar(20) default 'XXX',
 bbb varchar(255)
 );

修改列定义

alter table user_info
modify user_name varchar(10) not null,
modify user_pwd varchar(20) not null;

alter table user_info
change user_pwd（old） user_password（new） varchar(20) not null;

删除列语法

alter table user_info
drop user_address;

重命名数据表

alter table user_info
rename to user;

删除表语法

drop table 表名;

删除表全部数据

truncate 表名;

3、视图（是一个或多个数据表中数据的逻辑显示，并不能存储数据）

create or replace view view_test
as 
select teacher_name from teacher
with check option;		#指定不允许修改此视图的数据

drop view view_test;

4、DML语句语法

insert into 语句语法

insert into 表名[(列名列表...)] values(值列表...);

insert into student(student_name)
#子查询的值来插入
select teacher_name from teacher;

update 语法

update student set student_name='傻根'
where student_name='小明';

delete 语法

delete from student where student_id > 3;

5、事务

#开启事务
START TRANSACTION;
#设置标记点
SAVEPOINT `one`;
#回滚事务
ROLLBACK TO `one`;
#设置自动关闭或开启事务
SET autocommit=0;/1
#提交事务
COMMIT;

6、查询语句

#查询所有60分以上的成绩信息，并按照成绩由低到高的顺序排序，如果成绩相同，再按照学号升序排列
SELECT * from score where score > 60 order by score desc , stu_id asc;
#单表查询SQL语句语法总结：		
select [distinct] 列名1 [as 别名], 列名2 [as 别名], ..., 常量（计算列） [as 别名], ...
from 表名 [as 别名]
[where 查询筛选的条件表达式]
[order by 排序条件];

#case流程控制函数
#如果 科目编号 为1，返回'Java基础'，为2返回'Html网页'，否则返回'其他课程'
select s_id, score, case sub_id
when 1 then 'Java基础'
when 2 then 'Html网页'
else '其他课程'
end 
from score;

-- 生日小于‘2000-1-1’ 是少年，介于‘2000-1-1’ 和 ‘2016-1-1’ 之间是青年，否则是成年
select stu_name, case
when stu_birthday <= '2000-1-1' then '少年'
when stu_birthday between '2000-1-1' and '2016-1-1' then '青年'
else '成年人'
end
from student;

-- 统计所有考试成绩的总分
select sum(score) as 总分 from score;
-- 统计学生表中男生总数
select count(*) as 男生总数 from student where stu_sex='男';
-- 统计考试成绩表中最高分数
select max(score) as 最高分 from score;
-- 统计科目表中课时最少的是多少
select min(sub_hour) as 最少课时 from subject;
-- 统计成绩表中的平均成绩
select avg(score) as 平均分 from score;

-- 统计学生表中，每个年级的男生，女生各是多少人
select grade_id, stu_sex, count(*) as 人数
from student
group by grade_id, stu_sex;

-- 统计每年级学生人数，只显示人数超过50人的结果
select grade_id, count(*) as 年级人数
from student
group by grade_id
having count(*) > 50;

-- 单表查询语法再次总结：
	
select [distinct] *|列名|常量|函数调用|表达式 [as 别名] [,......]
from 表名 [as 别名]
[where 查询条件表达式]  #where语句是用于对源表记录进行筛选，只有符合条件的记录才能放到结果集中
[group by 分组条件1, 分组条件2, ......] #对结果集（表）数据，按照某种条件（通常是列名）进行分组
[having 对组进行筛选的条件表达式] #对group by 分好的小组进行筛选，只有条件为true的小组，得到保留
[order by 排序条件 [asc|desc]] #必须放在整个语句的最后，对已经筛选完的记录按照某列值进行排序
[limit n,[m]]; #限制结果集中数据记录的行数

-- 交叉连接（cross join）：广义笛卡尔积
select stu_name, score 
from student cross join score;

-- inner join ... on 子句连接（最常用的内连接方式）
-- 查询学生姓名，科目名称，成绩，并按成绩从小到大排序
select stu_name, sub_name, score
from student 
inner join score 
on student.stu_id = score.stu_id
inner join subject
on subject.sub_id = score.sub_id
order by score asc;

-- 左外连接：left [outer] join
-- 查询所有学生的信息以及学生考试成绩信息		
select student.stu_id, stu_name , score.*
from student 
left outer join score
on student.stu_id = score.stu_id;

-- 右外连接：right [outer] join
-- 查询所有科目名称以及对应的成绩信息
select score.*, subject.sub_name 
from score
right outer join subject
on score.sub_id = subject.sub_id;

-- 全外连接：full [outer] join
-- 全外连接学生表，成绩表，分数大于70
select stu_name, score
from student 
full join score
where score > 70;

-- 子查询
-- 查询JavaEE科目的所有考试成绩
select score.* 
from score 
where sub_id = (select sub_id from `subject` where sub_name='JavaEE');

-- union 运算
-- 查询所有学生的编号、姓名和所有科目的编号、姓名
select stu_id, stu_name from student
union
select sub_id, sub_name from subject;