1.1.2.删除数据库。 语法:drop database 代码: //删除数据库hr drop database hr //如果存在hr数据库,则删除数据库hr IF DB_ID('hr') IS NOT NULL DROP DATABASE TestDB ----------------------------------------------------------- 1.2备份与恢复backup/restore database 1.2.1.添加备份设备 语法:sp_addumpdevice
//子句说明 select子句:指出检索的数据项 from 子句:指出检索的数据表 where 子句:指出检索的数据条件 group by子句:指出检索的数据进行汇总 having子句:指出检索的数据进行汇总之前的条件 order by子句:指出检索的数据条件进行排序 代码: //所有字段方式显示orders全部记录 select * from orders //按字段显示全部记录 select order_num,order_date,amount from orders //按字段显示全部记录,但除掉重复的记录 select order_num,order_date,amount from orders //用sql-expression乘运算计算列 select amount,amount*0.08 as discount_amt from orders //用自定义函数计算指定列 select order_num,order_date,amount,f_amt_to_chn(amount) as 金额 from orders
select选项太多,代码例子就省略... ----------------------------------------------------------- 2.2子查询 Sub Query 语法:select ... from where / having column 测试条件 (Sub Query) //测试条件 比较测试条件(=,,>,=,范围测试条件(betweeen 下限值 and 上限值) 成员测试条件(in,not in) 存在测试条件(exists,not exists) 匹配测试条件(like) 限定测试条件(any,all) 空值测试条件(is null)
代码: //列出没有完成销售目标10%的销售人员清单[select name from salesreps where quota //列出公司的销售目标超过各个销售人员定额总和的销售点[>测试] select city from offices where target > (select sum(quota) from salesreps where rep_office=office) //列出超过销售目标的销售点的业务人员[in测试] select name from salesreps where office in (select office from offies where sales > target) //列出订单大于2500元的产品名称[exists测试] select description from products where exists ( select * from orders where product=prodct_id and amount > 2500.00 ) //列出完成销售目标10%的销售人员清单[any测试] select name from salesreps where (0.1* quota) ----------------------------------------------------------- 2.3连接查询Table Joins 多表连接类型可分为三类(内/外/交叉连接) 主从表或者父子表进行多表连接多以主键和外键进行关联 Outer joins(LEFT OUTER, RIGHT OUTER, and FULL OUTER joins) left outer join:查询的结果以左边表行数为准 right outer join:查询的结果以右边表行数为准
2.3.1.内连接inner join 功能: 语法: SELECT select_list FROM table_1 [INNER] JOIN table_2 ON join_condition_1 [[INNER] JOIN table_3 ON join_condition_2]... 代码: //没有where子句的内连接 SELECT * FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
//有where子句的内连接 SELECT p.ProductID, s.SupplierID, p.ProductName, s.CompanyName FROM Products p INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID WHERE p.ProductID ----------------------------------------------------------- 2.3.2.外连接outer join 功能:包括三种连接LEFT OUTER, RIGHT OUTER, and FULL OUTER joins left outer :查询的结果以左边表行数为准 right outer :查询的结果以右边表行数为准 语法:select ... from table1 [left/right/full outer join ]table2 where ... 代码: //以Customers表行数为标准去连接Orders表 SELECT c.CustomerID, CompanyName FROM Customers c LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.CustomerID IS NULL
----------------------------------------------------------- 2.3.3.交叉连接cross join 功能:以主从表或者父子表之间的主键进行连接,最终以笛卡尔乘积运算的结果 语法:select ... from table1 cross join table2 where ...
代码: //显示结果以表1行数*表2行数 假设Departments为4行记录 假设Jobs为3行记录 下面的显示结果为4*3=12行记录 SELECT deptname,jobdesc FROM Departments CROSS JOIN Jobs //用关键字匹配的交叉连接 oc_head/oc_detail是主从表 oc_head(主键oc_number) oc_detail(主键oc_number,item_number,ship_date)
SELECT h.customerid,d.item_number,d.ship_date from oc_head as h CROSS JOIN oc_detail as d where h.oc_number=d.oc_number ----------------------------------------------------------- 2.4汇总查询Group Query //汇总查询相当于会计报表中的小计汇总的功能
语法: select ... from group by [having search expression]
代码: //求出每名销售人员的销售金额 select rep,sum(amount) from orders group by rep //每个销售点分配了多少销售人员 select rep_office,count(*) from salesreps group by rep_office //计算每名销售人员的每个客户和订单金额 select cust,rep,sum(amount) from orders group by cust,rep //Having子句应用 select rep,avg(amount) from orders having sum(quota) > 3000.00
/**********************************************************/ 3.数据修改DATA MODIFY LANGUAGE 3.1插入数据Insert 3.2修改数据Update 3.3删除数据Delete ----------------------------------------------------------- 3.1插入数据Insert 3.1.1.单行插入 语法:insert into [...] values(...);
代码: //不省略字段清单 insert into salesreps(name,age,empl_no,sales,title,hire_date,rep_office) values('jack toms',36,111,0.00,'sales mgr','10-05-2010',13) //省略字段清单 insert into salesreps values('jack toms',36,111,0.00,'sales mgr','10-05-2010',13) 3.1.2.多行插入 语法:insert into [(...)] values(...)
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn