Sqlserver 数据库
创建数据库USE master--进入系统数据库GO--在系统数据空中有张表，sysdatabases 存储存在的数据库的名称IF EXISTS(SELECT * FROM sysdatabases WHERE name='studentinfo')DROP DATABASE studentinfo--删除数据库goCREATE DATABASE studentinfo--新建数据ON( NAME='studentinfo',--数据库名称 FILENAME='D:\sql\studentinfo.mdf',--数据库的保存路劲 SIZE=5,--主文件的初始值大小 FILEGROWTH=10%--自增长)log ON--日志文件( NAME='studentinfo_log', FILENAME='D:\sql\studentinfo_log.ldf',--日志文件保存物理路径 SIZE=4, FILEGROWTH=10%)go
建表和添加数据USE studentinfo--找到数据库，并进入go--找到系统表sysobjects，如果存在数据库名称，说明已经存在此数据表IF EXISTS(SELECT * FROM sysobjects WHERE name='stu')DROP table stugoIF EXISTS(SELECT * FROM sysobjects WHERE name='subjects')DROP table subjectsgoIF EXISTS(SELECT * FROM sysobjects WHERE name='marks')DROP table marksgoCREATE TABLE stu--新建数据表( stu_NO varchar(4) PRIMARY KEY,--主键 stu_Nmae VARCHAR(10) NOT NULL--姓名，不能为空)go--一次多行插入INSERT INTO stuSELECT '0001','马昌东' UNIONSELECT '0002','范超' UNIONSELECT '0003','杨柳' UNIONSELECT '0004','程都' UNIONSELECT '0005','曹超' goCREATE TABLE subjects--创建科目表( s_id INT IDENTITY(1,1) PRIMARY KEY,--主键，自增长 s_name VARCHAR(10) NOT NULL--科目名称，不能为空)go--一次多行插入科目名称INSERT INTO subjectsSELECT 'java' UNIONSELECT 'html' UNIONSELECT 'ps' UNIONSELECT 'sql' UNIONSELECT '.net' goCREATE TABLE marks--创建成绩表( m_id INT IDENTITY(1,1) PRIMARY KEY,--主键，自增长 --外键，是stu表的外键，stu表下stu_No内容一致 m_stu_NO VARCHAR(4) FOREIGN KEY REFERENCES stu(stu_NO) , --外键，是subjects表的外键，subjects表下s_id内容一致 m_s_id INT FOREIGN KEY REFERENCES subjects(s_id) , m_sorce INT NOT NULL --成绩分数，不能为空)go--一次多行插入成绩分数，注意跟stu表--以及subjects表中的主外键关系的内容要一致INSERT INTO marksSELECT '0001',1,80 UNIONSELECT '0001',2,78 UNION SELECT '0001',3,79 UNION SELECT '0001',4,81 UNIONSELECT '0001',5,83 UNION SELECT '0002',1,80 UNIONSELECT '0002',2,78 UNION SELECT '0002',3,79 UNION SELECT '0002',4,81 UNIONSELECT '0002',5,83 UNION SELECT '0003',1,80 UNIONSELECT '0003',2,78 UNION SELECT '0003',3,79 UNION SELECT '0003',4,81 UNIONSELECT '0003',5,83 UNION SELECT '0004',1,80 UNIONSELECT '0004',2,78 UNION SELECT '0004',3,79 UNION SELECT '0004',4,81 UNIONSELECT '0004',5,83 UNION SELECT '0005',1,80 UNIONSELECT '0005',2,78 UNION SELECT '0005',3,79 UNION SELECT '0005',4,81 UNIONSELECT '0005',5,83 goCREATE TABLE classes --创建班级表( c_id INT IDENTITY(1,1) PRIMARY KEY,--主键，自增长 c_name VARCHAR(10) NOT NULL,--学生姓名，不能为空 c_subjects VARCHAR(10) NOT NULL,--学习科目，不能为空 c_marks INT NOT NULL--学习分数，不能为空) go
联表查询-多表联查，得到查询结果后呢，讲它插入到classes表中--切记，表中字段的类型要和查询显示出来的类型要同意INSERT INTO classesSELECT a.stu_Nmae,b.s_name,c.m_sorceFROM stu AS a,subjects AS b,marks AS cWHERE a.stu_NO=c.m_stu_NO AND b.s_id=c.m_s_idgoSELECT * FROM dbo.classes
置换表SELECT c_name AS 学生姓名,--起别名--表置换，查询到c_subjects为java时，将c_marks起别名为javaSUM(CASE WHEN c_subjects='java' THEN c_marks END)AS 'java',SUM(CASE WHEN c_subjects='html' THEN c_marks END)AS 'html',SUM(CASE WHEN c_subjects='sql' THEN c_marks END)AS 'sql',SUM(CASE WHEN c_subjects='ps' THEN c_marks END)AS 'ps',SUM(CASE WHEN c_subjects='.net' THEN c_marks END)AS '.net'FROM dbo.classes GROUP BY c_name --分组，根据姓名来分组查询