数据库基础

常用数据类型

注意： MySQL不用时可关闭，win+R----> services.msc----->找到MySQL 禁用。
  在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。
*整数：int ，分二无符号和有符号，长度无意义。
*小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，整数占3位。
*字符串：varchar，范围(0~65533)，一个中文或一个字母都占一个字符。
*日期时间：datetime，范围(1000-01 00:00:00 ~ 9999-12-31 23:59:59)

约束

*主键(primary key)：唯一的标识一条记录，一般设置为无符号int型且可自动生成，物理上存储的顺序。
*非空(not null)：此字段不允许填写空值。
*唯一(unique)：此字段的值不允许重复。
*默认值(default)：当不填写此信息时会使用默认值，如果填写时已填写为准。
*外键(foreign key)：维护两个表之间的关联关系。

一些重要的SQL命令

SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

数据库管理系统的主要功能

• 数据定义功能：(Data Define Language,DDL)定义数据的模式及有关的约束条件等。
• 数据操作功能：(Data Manipulation Language,DML)实现对数据库的基本操作
• 数据库运行管理功能
• 数据库的建立和维护功能
• 数据通信接口及数据组织
• 存储和管理功能

SQL语句(结构化查询语言)

*创建表（Create）
CREATE TABLE 表名（
字段名 类型 约束，
字段名 类型 约束
）
eg:
CREATE TABLE Student（
id int unsigned primary key auto_increment,
name varchar(10) DEFAULT ‘无名’,
//注：defalut’无名‘为默认值
age int unsigned NOT NULL,
//注：int默认为有符号，+unsigned变为无符号，int不用设置长度，not null不能为空。
height decimal(5,2)
）

*删除表（Drop）
DROP TABLE IF EXISTS Student
//注：一般会加在创建表前，但是DROP TABLE IF EXISTS Student ;
后面得加上"；"。

*修改表（Alter）
ALTER TABLE Student

*数据操作—增删改查

1.查询数据：

select * from 表名
//查询所有数据，*代表所有列

select name,age from 表名
//查询name,age

select name as 姓名，age as 年龄 from 表名
//给列名起别名，可以在显示列改变列名，并不会改变实际数据。

select c.name,s.age from Student as s, Class as c
//给表名起别名，用于有多个表有相同的列名时，可以根据表名的别名. 列名来查询数据。

select distinct name from Student
//查询学生的性别有哪几种
//distinct意思是你所查询的字段中重复的去掉

select 字段1，字段2 from 表名 where 条件
//条件分为：
1.比较运算:
=、>、<、>=、<=、!=或<>

2.逻辑运算:
and、or、not(非)

3.模糊运算:
ike、%表示任意多个任意字符、_表示一个任意字符、[charlist] 字符列中的任何单一字符、【^charlist] 或 [!charlist]不在字符列中的任何单一字符

eg: select * from Student
//查询所有学生数据

eg: select * from Student where name like ‘孙%’
//查询姓孙的学生

eg：select * from Student where name like ‘孙 _’
//查询姓孙且名字是一个字的学生

eg：select * from Student where name like ‘%白%’
//查询姓名含白的学生

4.范围运算:
in 表示在一个非连续的范围内， between… and …表示在一个连续的范围内

eg: select * from Student where age between 18 and 20
select * from Student where age >=18 and age <=20
//查询年龄为18到20 的学生

eg: select * from Student where hometown in(‘北京’，‘上海’，‘深圳’)
select * from Student where hometown =‘北京’ or hometown ='上海 or hometown =‘深圳’
//查询家乡是北京或上海或广东的学生

5.空判断:
null与‘ ’是不同的， null 是空， 用 is null 查询

eg: select * from Student where card is null
//查询没有填写身份证的人

eg：select * from Student where card=‘ ’
//查询填写空字符串身份证的人

6.排序:（order by）
语法:
select * from 表名 order by 列1 asc|desc, 列2 asc | desc,…
*将数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推。
*默认按照列值从小到大排序,等同于asc。
*asc从小到大排序，即升序。
*desc从小到大排序，即降序。

*若要姓名按中文排序，order by convert (name using gbk)

eg: select * from Student order by age desc, studentNo
//查询所有学生的信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序。

7.聚合数据:
为了快速得到统计数据。

select count( ) from 表名
//查询记录总数，count(*)代表一行记录任意字段有值，就会统计在内；

count(列表)代表只统计这个字段的个数，如果有null值不会被统计。

*select max(age) from 表名 where sex=‘女’；
//查询女生的最大年龄

*select min(列名)/sum(列名)/avg(列名) from 表名

8.分组（group by… having）:
*按照字段分组，表示此字段相同的数据会被放到一个组中。

*分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中。
*可以对分组后的数据进行统计，做聚合运算。

*语法： select 列1，列2，聚合… from 表名 group by 列1，列2…

eg: select sex,count(*) from Student group by sex
//查询各种性别的人数

8.1分组后的数据查询：
语法： select 列1，列2，(聚合…) from 表名 group by 列1，列2… having 列1，聚合…

*having后面的运算符和where相同
*having是对group by 的结果进行筛选
*where是对from后面指定的表进行数据筛选，属于对原始数据的筛选

eg: select sex,count() from Student group by sex having sex=‘男’
select count() from Student where sex=‘男’
//两个语句都是 查询男生总人数

9.分页（获取部分行 Limit ）
*当数据量过大时，在一页中看数据是一件非常麻烦的事请

语法： select * from 表名 Limit start，count
*从start开始，获取count条数据
*start索引从“0”开始。
eg: select *from Student Limit 0，3
//查询前三行学生信息

10.连接查询
等值查询
语法：select * from 表1，表2 where 表1.列=表2.列
select * from Student,Score where Student.studentno=Score.studentno
*此方式会产生笛卡尔积，生成的记录总数=表1的总数 * 表2 的总数,后通过where语句 过滤掉不要的信息。
*会产生临时表

内连接 inner join

• INNER JOIN：如果表中有至少一个匹配，则返回行.
• 不会产生临时表，不会产生笛卡尔积，性能高,生成的是交集。

eg: select * from Student
  inner join Score on Student.studentno=Score.studentno
//此语句产生的最终结果与等值连接相同，但是内连接是会先看on后面的条件，如果符合再输入到最终结果。

左连接 left join

• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行.
• join 前面的表称为左表，后面的表称为右表。
• 左连接会取左表的所有结果，右表无值时填null.

eg: select * from Student
  left join Score on Student.studentno=Score.studentno
//查询所有学生的成绩，包括没有成绩的学生

右连接 right join

• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行.
• 左边表和右边表按条件匹配，后取右边表的所有数据。

==MySQL不支持全连接 full join ==

• FULL JOIN：只要其中一个表中存在匹配，则返回行。
• 不支持全连接解决方式:
• left join + union (去除重复数据) + right join
  select * from a left join b on a.name = b.name
  union
  select * from a right join b on a.name = b.name
  //全连接

Joins的七种用法

11.合并操作符（union）

• SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
• UNION 内部的每个 SELECT 语句选取的列必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
• 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
• UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

select class from Student
union
select class from Score
order by class;
//从 “Student” 和 “Score” 表中选取所有不同的class（只有不同的值),按班级排序。

• UNION 不能用于列出两个表中所有的class。如果一些Student和Score来自同一个班级，每个班级只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

select class from Student
union all
select class from Score;
//使用 UNION ALL从 “Student” 和 “Score” 表中选取所有的class（也有重复的值）。

11.自关联：
*例如，省市区三者关联，省下面有多个区。可以将省市区创建在一个表中。

12.标量子查询
select avg(age) from Student
___得出结论avg的年龄是多少，为后一个句子提供依据，例如：21
select * from Student where age >21
//查询大于平均年龄的学生
也可以用下面的标量子查询
select * from Student where age =（select avg(age) from Student）

2.添加数据：insert into

1.给所有字段设置数据
insert into 表名 values(…)
eg: insert into Student values(0, '小明’，15，167.32)
//注：此id主键列设置为自动增长，添加数据时需要占位，通常用0或者default或者null来占位，占位成功后以实际数据为准
//插入一个学生，设置所有字段的信息

2.给指定字段设置数据
insert into 表名(字段1,…) values (值1,…)
eg: insert into Student (name) values (‘小红’) /
/插入一个学生，只设置姓名

3.可以一条语句插入多个记录数据
insert into Student(name) values(‘小军’)，values(‘小强’)；

3.修改数据：update

update 表名 set 列1=值1… where 条件
eg：update Student set name=‘李军’，age=18 where id=3
//修改id为3 的学生的数据，姓名改为‘李军’，年龄改为18。

4.删除数据：delete

delete语句用于删除表中的行。

delete from 表名 where 条件
eg：delete from Student where id=4
//删除id为4 的学生的数据

RDBMS
Relational Database Management System 关系型数据库系统，核心是以二维表存储数据。
常见的关系型数据库系统有：Oracle、MySQL

事务

事务是一种机制，是由一组数据库操作命令作为一个整体向系统提交或撤销操作的请求、要么都执行，要么都不执行，是一个不可分割的工作逻辑单位。具有原子性、一致性、隔离性、持久性。

数据库存储过程

数据库中的存储过程是一组为了完成特定功能，利用transcact-SQL语言编写的程序。一次编译之后可以执行多次，不必重复编译。

数据库中触发器

触发器是一个特殊的存储过程，它的执行不有程序调用，也不是手工启动，而是由事件触发。

视图

视图是一个虚拟表，其内容由查询定义。视图中的查询语句不能有重复的字段名，如果有可以起别名。视图的作用类似于筛选。视图通常用来集中、简化和自定义每个用户对数据库的不同认识。
创建视图：
create view view_name （列名）
as select ,
from 表名
where ,
修改视图
alter view view_name (列名)
as select ,
from ,
where ,
删除视图
drop view view_name
查询视图
select * from view_name

索引

有两种基本类型：聚集索引和非聚集索引。

• 聚集索引，表中行的物理存储顺序与索引键的逻辑(索引)顺序相同。由于真正的物理存储只有一个，因此一个表只能包含一个聚集索引。

• 非聚集索引，不影响数据行的物理存储顺序，数据行的物理存储顺序与索引键的逻辑（索引）顺序并不一致。每个表可以有多个非聚集索引。

• 优势，
  类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。
  通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 劣势
        实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
  　　虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。
  　　因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
  都会调整因为更新所带来的键值变化后的索引信息。
  建立索引会占用磁盘空间的索引文件。

• 创建索引，create

• 使用关键字 distinct可以去除重复列