数据库操作----找了MySQL和SQL Sever两个的基础语句

登入数据库：mysql -uroot -p+密码 (SQL Sever登入： osql -U 用户名 -P 密码) 2 显示已存在的数据库：show databases; 3 使用某个数据库：use+数据库名; 4 显示某个数据库下已存在的关系表：show tables; 5 6 查看某个关系表所有数据：select * from tableName; 7 查看某个关系表部分字段数据：select 字段1，字段2，...，字段n from tableName; 8 查看n条记录：select ... from ... limit n; 9 查看第i条到第j条之间的记录：select ... from ... limit i,j; 10 11 使用order by 来对记录进行排序，默认从小到大 12 >select stuName, stuScore from student order by stuScore desc; //（从大到小） 13 >select stuName, stuScore from student order by stuScore asc; //（从小到大） 14 15 去重: distinct 16 >select distinct stuCourse from student; 17 > select count(distinct stuCourse) from student; 18 19 20 聚合函数(常用于GROUP BY从句的SELECT查询中) 21 AVG(col)返回指定列的平均值 22 COUNT(col)返回指定列中非NULL值的个数 23 MIN(col)返回指定列的最小值 24 MAX(col)返回指定列的最大值 25 SUM(col)返回指定列的所有值之和 26 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 27 > select stuCourse, sum(stuScore) from student where stuCourse = "Chinese"; 28 > select stuCourse, sum(stuScore) from student group by stuCourse; 29 > select stuCourse, avg(stuScore) from student group by stuCourse; 30 > select stuCourse, max(stuScore) from student group by stuCourse; 31 > select stuCourse, min(stuScore) from student group by stuCourse; 32 > select avg(stuAge) as avg_age from student; 33 > select group_concat(stuName, stuCourse) from student; 34 35 条件限制：where : =, !=, >, >=, <, <=, and, not, or 36 , between...and..., not between...and... 37 , is null, is not null, like(%, _), not like(%, _) 38 , in(项1, 项2, …), not in(项1, 项2, …) 39 , exists, not exists 40 , any, some, all 41 42 %: 匹配任意个字符 43 _：匹配单个字符 44 45 创建数据库：create database 数据库名; 46 创建关系表：create table tableName(字段1 类型 限制，字段2 类型 限制，...,字段n 类型 限制); 47 显示关系表的格式：show create table tableName; 48 更新数据：update tableName set 字段1 = 字段1值, 字段2 = 字段2值, ..., 字段n = 字段n值; 49 50 插入数据：insert tableName(字段1，字段2，...，字段n) values(字段1值，字段2值，...，字段n值); 51 52 生成一个关系表的备份：create table baktableName (select * from tableName); 53 使用备份的数据对新表插入数据 54 > insert book select * from bookBak; 55 56 生成一个数据库的备份：mysqldump -uUserName -p dbname > bakefile; 57 使用数据库的备份恢复数据库：mysql -uUserName -p dbname < bakefile; 58 59 使用交互方式恢复数据库： 60 > create database dbName; (如果不存在这个数据库) 61 > use dbName; 62 > source bakeFile; 63 64 删除一个数据库：drop database dbname; 65 删除关系表中所有数据：delete from tableName; 66 删除关系表中符合条件的数据：delete from tableName where...; 67 68 给关系表增加一个字段：alter table tableName add 字段名 说明(default ‘H‘); 69 给关系表删除一个字段：alter table tableName drop 字段名; 70 给关系表修改一个字段：alter table tableName modify 字段名 说明; 71 给关系表修改一个字段：alter table tableName change 旧字段名 新字段名 说明(类型...); 72 73 设置字段值唯一，即该字段的值不允许有重复的: unique 74 > create table tmp(id int unique not null, name varchar(32)); 75 76 主键：唯一标识一条记录，主键可以是单个字段，也可以是多个字段合成的 77 > alter table student modify stuId varchar(32) primary key not null; 78 79 > create table book(bookId bigint auto_increment primary key not null, bookName varchar(32) not null); 80 > create table book(bookId bigint auto_increment not null, bookName varchar(32) not null, primary key(bookId)); 81 82 > create table score(stuId varchar(32) not null, 83 > bookId bigInt not null, 84 > score float, 85 > primary key(stuId, bookId)); 86 87 多表查询 88 > select score.bookId, stuName, bookName from score, student, book where score.bookId = book.bookId and score.stuId = student.stuId; 89 > select score.bookId, stuName, bookName, score from score, student, book where score.bookId = book.bookId and score.stuId = student.stuId; 90 91 外键 92 > alter table score add constraint stuId_cons foreign key(stuId) references student(stuId); 93 > alter table score add constraint bookId_cons foreign key(bookId) references book(bookId); 94 95 > create table tecbook(tecId varchar(32) not null, bookId bigint not null, primary key(tecId, bookId), foreign key(tecId) references teacher(tecId), foreign key(bookId) references book(bookId)); 96 97 98 99 学生表student(stuId,stuName,stuAge,stuSex) 100 mysql> create table student(stuId varchar(32) primary key not null, 101 -> stuName varchar(32) not null, 102 -> stuAge int, 103 -> stuSex char); 104 105 教师表 teacher(tecId,tecName) 106 mysql> create table teacher(tecId varchar(32) primary key not null, 107 -> tecName varchar(32) not null); 108 109 课程表course(courseId,courseName,tecId) 110 mysql> create table course(courseId bigint primary key auto_increment not null, 111 -> courseName varchar(32) not null, 112 -> tecId varchar(32) not null, 113 -> foreign key(tecId) references teacher(tecId) on update cascade on delete cascade); 114 115 成绩表score(stuId,courseId,score,note) 116 mysql> create table score(stuId varchar(32) not null, 117 -> courseId bigint not null, 118 -> score float, 119 -> note varchar(64), 120 -> primary key(stuId, courseId), 121 -> foreign key(stuId) references student(stuId) on delete cascade on update cascade, 122 -> foreign key(courseId) references course(courseId) on delete no action on update cascade); 123 124 125 级联操作： 126 foreign key(column) references tableName(column) 127 [[on delete | on update] [cascade | no action | set null | restrict]]; 128 129 130 查询每个学生每门课的成绩 131 > select stuName, courseName, score from student, course, score where student.stuId = score.stuId 132 and course.courseId = score.courseId; 133 134 查询每个学生的总成绩 135 > select stuName, sum(score) from student, course, score where student.stuId = score.stuId 136 and course.courseId = score.courseId group by stuName; 137 138 对每个学生的总成绩排序 139 > select stuName, sum(score) as sum_score from student, course, score where student.stuId = score.stuId 140 and course.courseId = score.courseId group by stuName order by sum_score desc; 141 142 查询每个学生的平均成绩 143 > select stuName, avg(score) from student, course, score where student.stuId = score.stuId 144 and course.courseId = score.courseId group by stuName; 145 146 查询平均成绩>80的学生 147 > select stuName, avg(score) as avg_score from student, course, score where student.stuId = score.stuId and course.courseId = score.courseId group by stuName having avg_score > 80; 148 Note: having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。 149 150 查询“3004”课程比“3002”课程成绩高的所有学生的学号 151 > select t1.stuId from (select stuId, score from score where courseId = 3002) as t1, 152 (select stuId, score from score where courseId = 3004) as t2 where t1.score < t2.score 153 and t1.stuId = t2.stuId; 154 155 > select stuId from score s1 where courseId = 3002 and score < 156 (select score from score s2 where courseId = 3004 and s1.stuId = s2.stuId); 157 158 > select s1.stuId from score s1, score s2 where s1.score < s2.score and s1.stuId = s2.stuId 159 and s1.courseId = 3002 and s2.courseId = 3004; 160 161 查询所有同学的学号、姓名、课数、总成绩 162 > select score.stuId, stuName, count(courseId) as course_num, sum(score) as sum_score from score, student where score.stuId = student.stuId group by stuId; 163 164 查询和likui性别相同的学生信息 165 > select stuId, stuName from student where stuSex = (select stuSex from student where stuName = "likui") 166 and stuName != "likui"; 167 168 查询没学过“mozi”老师课的同学的学号、姓名 169 > select distinct score.stuId, stuName from score, student where score.stuId = student.stuId 170 and score.stuId != 171 ( 172 select stuId from score where courseId in 173 ( 174 select courseId from course where tecId = 175 (select tecId from teacher where tecName = "mozi") 176 ) 177 ); 178 179 查询每个老师所教课程平均分从高到低显示 180 > select c.courseId, c.courseName, avg(sc.score) avgScore, t.tecName from course c, score sc, teacher t where c.courseId = sc.courseId and c.tecId = t.tecId group by(sc.courseId) order by avgScore DESC; 181 182 > select course.tecId, teacher.tecName, t1.courseId, t1.avg_score from (select courseId, avg(score) as avg_score from score group by courseId) as t1, course, teacher where t1.courseId = course.courseId and course.tecId = teacher.tecId; 183 184 删除学习“laozi”老师课的score表记录 185 > delete from score where courseId in (select courseId from course 186 where tecId = (select tecId from teacher where tecName = "laozi")); 187 188 查询两门及以上不及格课程的同学的学号及其平均成绩 189 > select stuId, avg(score) from score where stuId in (select stuId from score where score < 60 group by stuId having count(*) >= 2) group by stuId; 190 191 > select stuId, avg(score) from score where stuId in (select stuId from (select stuId, count(*) as blow from score where score < 60 group by stuId having blow >= 2)as t1) group by stuId; 192 193 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 194 > select courseId, max(score), min(score) from score group by courseId; 195 196 查询学过“3001”并且也学过编号“3003”课程的同学的学号、姓名 197 > select stuId, stuName from student where stuId in (select s1.stuId from score s1, score s2 where s1.courseId = 3001 and s2.courseId = 3003 and s1.stuId = s2.stuId); 198 199 查询“3001”课程的分数比“3001”课程平均分低的学生stuId, stuName 200 > select stuId, stuName from student where stuId in(select stuId from score where score < (select avg(score) from score where courseId = 3001) and courseId = 3001); 201 202 查询“3001”课程的分数比“3001”课程平均分低的学生stuId, stuName, score 203 > select score.stuId, stuName, score from score, student where score < (select avg(score) from score 204 where courseId = 3001) and courseId = 3001 and score.stuId = student.stuId; 205 206 查询“3001”课程的分数比“3001”课程平均分低的学生stuId, stuName, score, avg_score 207 > select score.stuId, stuName, (select avg(score) from score where courseId = 3001) as avg_score, score from score, student where score < (select avg(score) from score where courseId = 3001) and courseId = 3001 and score.stuId = student.stuId; 208 209 210 view: 是视图，是一张虚表，表中没有任何数据 211 > create view score_view as select score.stuId, stuName, count(courseId) as course_num, sum(score) as sum_score from score, student where score.stuId = student.stuId group by stuId; 212 213 > select * from score_view; 214 215 存储过程： 216 delimiter $$ 217 create procedure proName(in | out | inout) 218 begin 219 commands; 220 end 221 $$ 222 223 224 mysql> delimiter // 225 mysql> create procedure pro_new() 226 -> begin 227 -> select * from student; 228 -> select * from course; 229 -> select * from teacher; 230 -> end 231 -> // 232 mysql> delimiter ; 233 mysql> call pro_new; 234 235 236 237 mysql> delimiter // 238 mysql> create procedure addTec(in id varchar(32), in name varchar(32)) 239 -> begin 240 -> insert teacher(tecId, tecName) values(id, name); 241 -> end 242 -> // 243 244 mysql> delimiter ; 245 mysql> call addTec("2010", "daai"); 246 247 248 mysql> delimiter // 249 mysql> create procedure pp(out op int) 250 -> begin 251 -> set op = 90; 252 -> end 253 -> // 254 255 mysql> delimiter ; 256 mysql> set @opp = 0; 257 mysql> select @opp; 258 mysql> call pp(@opp); 259 mysql> select @opp; 260 261 262 1, if-then-else 分支 263 if condition then 264 commands; 265 [elseif condition then 266 commands;] 267 [else 268 commands;] 269 end if; 270 271 mysql> delimiter // 272 mysql> create procedure sala_pro4(in level int) 273 > begin 274 > if level = 2 then 275 > update teacher set tecSalary = 5000 where tecLevel = level; 276 > elseif level = 3 then 277 > update teacher set tecSalary = 7000 where tecLevel = level; 278 > elseif level = 4 then 279 > update teacher set tecSalary = 9000 where tecLevel = level; 280 > else 281 > select * from teacher; 282 > end if; 283 > end// 284 285 > show create procedure proName; 286 > show procedure status; 287 > drop procedure proName; 288 289 290 2，case 分支 291 case expression 292 when value1 then 293 commands; 294 [when value2 then 295 commands;] 296 [else 297 commands;] 298 end case; 299 300 mysql> create procedure tec_pro(in level int) 301 > begin 302 > case level 303 > when 1 then 304 > update teacher set tecSalary = 4000 where tecLevel = 1; 305 > when 2 then 306 > update teacher set tecSalary = 4500 where tecLevel = 2; 307 > when 3 then 308 > update teacher set tecSalary = 6000 where tecLevel = 3; 309 > when 3 then 310 > update teacher set tecSalary = 6000 where tecLevel = 3; 311 > when 4 then 312 > update teacher set tecSalary = 8000 where tecLevel = 4; 313 > else 314 > select * from teacher; 315 > end case; 316 > end// 317 318 319 while 循环 320 [loopName:] while condition do 321 commands; 322 end while [loopName]; 323 324 mysql> create procedure show_pro(in id bigint) 325 > begin 326 > declare var int; 327 > set var = 0; 328 > while var < 6 do 329 > select * from course where courseId = id + var; 330 > set var = var + 1; 331 > end while; 332 > end// 333 334 335 repeat-until 循环 336 [loopName:] repeat 337 commands; 338 until condition 339 end repeat [loopName]; 340 341 mysql> create procedure ins_pro2(in name varchar(32)) 342 > begin 343 > declare tim int; 344 > set tim = 1; 345 > repeat 346 > insert usename(Name, Time, Level) values(name, tim, tim); 347 > set tim = tim + 1; 348 > until tim > 8 349 > end repeat; 350 > end// 351 352 353 354 loop 循环 355 loopName: loop 356 commands; 357 end loop loopName; 358 359 360 函数创建： 361 create function(...) returns valueType 362 begin 363 commands; 364 return value; 365 end 366 367 mysql> create function test(n int) returns text 368 > begin 369 > declare i int default 0; 370 > declare s text default ‘‘; 371 > myloop: loop 372 > set i = i + 1; 373 > set s = concat(s, "*"); 374 > if i >= n then 375 > leave myloop; 376 > end if; 377 > end loop myloop; 378 > return s; 379 > end// 380 381 mysql> select test(4); 382 mysql> set @tt = test(3); 383 384 385 1，游标定义：declare cursorName cursor for select... 386 2，打开游标：open cursorName 387 3，使用fetch cursorName into var1,var2,...命令去遍历select结果数据表里的数据记录 388 4，关闭游标：close cursorName(游标会在对它们做出声明的 begin-end 语句块执行终了时自动随之结束) 389 备注：若使用fetch命令把select结果数据表的记录都读完了会出发一个错误：no data to fetch。 390 对于该错误可以定义一个错误处理器来捕获。出错处理条件一般使用 not found 即可 391 392 mysql> create procedure cur_pro2() 393 > begin 394 > declare result varchar(256) default ‘‘; 395 > declare name varchar(32); 396 > declare done int default 0; 397 > declare cur_book cursor for select courseName from course; 398 > declare continue handler for not found set done = 1; 399 > open cur_book; 400 > repeat 401 > fetch cur_book into name; 402 > set result = concat(result, name); 403 > until done 404 > end repeat; 405 > select result; 406 > close cur_book; 407 > end//

下面是SQL Sever的语法，其实基本上差不多，这两天我所用到，大概也就一个递增、一个级联不同

一、基础

1、说明：创建数据库
CREATE DATABASE database-name 
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
— 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:\mssql7backup\MyNwind_1.dat’
— 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表： 
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col) 
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement 
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句


选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ —like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出
一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），
不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。
当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。
当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f f