MYSQL数据库知识

一、ADO主要包括Connection,Recordset和Command三个对象

主要功能如下:

Connection对象: 打开或连接数据库或数据文件;

Recordset对象: 存取数据库的内容

Command对象: 对数据库下达行动查询指令（SELECT,INSERT,UPDATE,DELETE）,或调用存储过程。

二、系统数据库:

master数据库:是数据库管理系统中最重要的数据库.该数据库包含:所有用户的登录信息,用户所在的组,所有系统的配置选项,服务器中本地数据库的名称和信息等.

model数据库:是数据库管理系统中创建的一个数据库模板,希望所有的数据库中都有一个相同的数据表,创建的数据库以model数据库中的数据为模板进行创建.

msdb数据库:是一个windows服务,该服务用来运行制定计划任务.

tmpdb数据库:是一个临时数据库,用于存放临时对象和中间结果,当关闭SQL Server时,该数据库中的内容被清空,当重新启动后,该数据库被重建.

主数据库:mdf

次数据文件:ndf

日志文件:ldf

三、SQL示例代码

/* 


  * 绘制E-R图
  *               姓名                          姓名
  *                |                             |
  *            +--------+                    +--------+
  *    年龄<<--| 教师表 | -->>性别   年龄<<--  | 学生表 | -->> 性别
  *            +--------+                    +--------+
  *                 \                   /--------/
  *                  \1                N
  *                   /\              /\
  *                  /  \            /  \
  *                 |授课|          |上课|
  *                  \  /            \  /
  *                   \/              \/
  *                    N +--------+ 1_/
  *                     \| 班级表 |/
  *                      +--------+  
  *
  */


-- 创建数据库
Create Database DB_DATA
on primary   -- 定制数据库主文件的属性
(
	name	 = 'DB_DATA'   
	,filename = 'c:\database\DB_DATA.mdf' -- 主文件的文件路径
	,size = 10MB  -- 主文件的起始字节数
	,maxSize = 20MB -- 主文件的最大字节数
	,FileGrowth=5% -- 主文件的增长大小,可百分比，也可使用字节数
)
log on -- 定制日志文件的属性，参数的含义和主文件的参数一样
(
	name	 = 'DB_DATALog'   
	,filename = 'c:\database\DB_DATALog.ndf' 
	,size = 10MB
	,MaxSize = 20MB
	,FileGrowth=10%
)


use DB_DATA
go



/** 非空约束 : not null      语法: 直接使用
  * 主键约束 : primary key   语法: 直接使用
  * 默认约束 : default       语法: default(默认值)
  * 检查约束 : check         语法: check(表达式)
  * 外键约束 : foreign key   语法: constraint 约束名 foreign key references 其他表名(其他表字段)
  * 唯一约束 : unique        语法：直接使用
  */

-- 创建教师表
Create Table Teacher
(
	id  int primary key identity(0,1), -- int 类型的id，主键约束， identity是一个默认的增量，从0开始，每添加一行记录就会增加1
	姓名 char(20) not null,   -- char类型的姓名， 非空约束
	性别 char(2) check(性别='男' or 性别='女'),  -- char类型的性别，检查约束，
	年龄 int default(18) -- int 类型的年龄， 默认约束
)

-- 为Teacher添加约束
-- Constraint：约束关键字，
-- DK_默认约束 ： 约束的标识符
Alter Table Teacher
 Add Constraint DK_默认约束 default('男') for 性别  -- 默认约束

 
Alter Table Teacher 
 Add Constraint CK_检查约束 check(年龄>=18 and 年龄 <= 45) -- 检查约束


-- 创建班级表
Create Table Class
(
	id  int primary key identity(0,1),
	班级名 char(20) ,
	班级人数 int,
	教师id int 
)

-- 为班级表添加约束
Alter Table Class
 Add Constraint  UK_唯一约束 unique(班级名) -- 唯一性约束

-- -- 所属班级的id ,属于外键，为班机表添加外键约束
Alter Table Class
 Add constraint FK_CLassToTeacher foreign key(教师id)  references Teacher(id)


-- 创建学生表
Create Table Student
(
	id int primary key identity(0,1),   -- 学生id
	姓名 char(20) not null ,    
	性别 char(2) default('男') check(性别='男' or 性别='女'), 
	年龄 int default(18) check(年龄>=12 and 年龄 <= 50), 
    
    -- 所属班级的id ,属于外键
	班级id int constraint FK_StudentToClass foreign key references Class(id)
)


-- 插入数据到教师表
insert into Teacher
(姓名,性别,年龄)
values('杨老师','男',35),
('张老师','男',40),
('李老师','男',30),
('王老师','男',28),
('韦老师','男',18)

Select * from Teacher


Insert into Class
(班级名,班级人数,教师id)
Values('17期',23,1)

-- 插入数据到班级表
-- 声明要使用的变量
Declare @杨老师id int
Declare @张老师id int
Declare @李老师id int
Declare @王老师id int
Declare @韦老师id int
-- 获取对应老师的id 
select @杨老师id=id from Teacher where 姓名='杨老师'
select @张老师id=id from Teacher where 姓名='张老师'
select @李老师id=id from Teacher where 姓名='李老师'
select @王老师id=id from Teacher where 姓名='王老师'
select @韦老师id=id from Teacher where 姓名='韦老师'


-- 开始插入数据
insert into Class
(班级名,班级人数,教师id)
values('18期',6, @杨老师id),
('19期',7,@张老师id),
('20期',8,@王老师id),
('21期',5,@杨老师id)



-- 插入信息到学生表
-- 声明要用到的变量
declare @班级18ID int
declare @班级19ID int
declare @班级20ID int
declare @班级21ID int

-- 得到对应班级别的id 
-- 使用select语句查询表中的18期，并将查询好的记录中的id字段的值赋值给局部变量
Select @班级18ID = id From Class where 班级名='18期'
Select @班级19ID = id From Class where 班级名='19期'
Select @班级20ID = id From Class where 班级名='20期'
Select @班级21ID = id From Class where 班级名='21期'

-- 开始插入数据
insert into Student
(姓名,性别,年龄,班级id)
values('杨二狗','男',18,@班级18ID),
('芙蓉','女',28,@班级18ID),
('赵四','男',20,@班级18ID),
('麻五','男',41,@班级18ID),
('工人1','女',23,@班级18ID) ,
('工人2','男',13,@班级19ID) ,
('工人3','男',41,@班级19ID) ,
('工人4','女',42,@班级19ID) ,
('工人5','男',36,@班级19ID) ,
('工人6','男',34,@班级19ID) ,
('工人7','男',24,@班级19ID) ,
('工人8','女',16,@班级21ID) ,
('工人9','女',37,@班级20ID) ,
('大神1','男',41,@班级20ID) ,
('大神2','男',42,@班级20ID) ,
('大神3','男',34,@班级20ID) ,
('大神4','女',33,@班级20ID) ,
('大神5','男',27,@班级20ID)


-- 普通查询
Select * From Student



-- 查询指定的列
select 姓名,性别,年龄 From Student



-- 在查询时，对字段进行改名输出
Select 姓名 as  name , Sex = 性别 , Age = 年龄 From Student

-- 使用等于号也能够完成和上一行相同的功能
Select name = 姓名 , Sex = 性别 , Age = 年龄 From Student



-- 在查询时,过滤部分内容,只输出符合条件的内容
Select * From Student
	where 年龄>=20 and 年龄<=25 and 性别 = '女'



-- 过滤查询，输出名字中姓大的，并且年龄在30到40岁之间的学生。
Select * From Student
	where 姓名 like '大%' and 年龄>=30 and 年龄<= 40 

-- _ 只通配一个字符
-- [abcd] 
-- 过滤查询， 输出20，25，30岁的学生
Select * From Student
	where 年龄 in(20,25,30) 


-- 多表查询 : 简单地将两个表的所有内容输出(输出的条目数一共是表1条目数 * 表2条目数)
Select * From Student , Class


-- 多表过滤查询 (输出的条目数是表1的条目数,靠近 from 关键字的表是表1,也叫作左表)
Select * From Student , Class
	where Student.班级Id = Class.id


-- 多表查询，只输出指定字段
select Student.姓名,Student.性别,Student.年龄,Class.班级名,Class.班级人数,Teacher.姓名,Teacher.性别,Teacher.年龄 
	from Student,Class,Teacher
		where Student.班级Id = Class.id and Class.教师id = Teacher.id


-- 嵌套查询 ， 只输出20期的学生，且
-- 先执行最内层的查询，最内层的查询把班级名对应的id找到
select * From Student 
	where Student.班级Id in 
	(
		select id From Class where 班级名 =  '20期'
	)

-- 下面这行代码等同于上一条语句
select * From Student 
	where Student.班级Id in(4)


-- 多表查询
-- 查询所有学生的信息（本人信息，所属班级信息，班级所属教师信息
select Student.姓名,Student.性别,Student.年龄,Class.班级名,Class.班级人数,Teacher.姓名,Teacher.性别,Teacher.年龄 
	from Student,Class,Teacher
		where Student.班级Id = Class.id and Class.教师id= Teacher.id


-- 排序输出:按姓名的拼音排序输出
select * From Student
	Order By Student.姓名




-- 排序输出:按年龄排序输出,升序
select * From Student
	Order By  Student.年龄 ASC

-- 排序输出:按年龄排序输出,降序
select * From Student
	Order By  Student.年龄 DESC



-- 分组输出(用于配合聚合函数)
-- 统计表中各个班级id出现的次数
select 班级Id , count(*) From Student 
	group by 班级Id