MYSQL数据库知识

/*   * 绘制E-R图  *               姓名                          姓名  *                |                             |  *            +--------+                    +--------+  *    年龄<<--| 教师表 | -->>性别   年龄<<--  | 学生表 | -->> 性别  *            +--------+                    +--------+  *                 /                   /--------/  *                  /1                N  *                   //              //  *                  /  /            /  /  *                 |授课|          |上课|  *                  /  /            /  /  *                   //              //  *                    N +--------+ 1_/  *                     /| 班级表 |/  *                      +--------+    *  */-- 创建数据库Create Database DB_DATAon 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_DATAgo/** 非空约束 : 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 TeacherInsert into Class(班级名,班级人数,教师id)Values('17期',23,1)-- 插入数据到班级表-- 声明要使用的变量Declare @杨老师id intDeclare @张老师id intDeclare @李老师id intDeclare @王老师id intDeclare @韦老师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 intdeclare @班级19ID intdeclare @班级20ID intdeclare @班级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