mysql高级操作_MySQL--3--高级操作

/*高级操作*/

/*高级操作中，有主要涉及了JAVA在数据库中的使用，高级聚集函数，数据库中的函数和过程、trigger等等概念*/

/*1. 动态(JDBC AND ODBC)和嵌入式SQL*/

/*JDBC is a Java API for communicating with database systems supporting SQL.

Model for communicating with the database:

Open a connection

Create a “statement” object

Execute queries using the Statement object to send queries and fetch results

Exception mechanism to handle errors*/

/*使用JAVA对数据库进行连接*/

publicstatic void JDBCexample (String dbid , String userid , String passwd )

{

try (Connection conn=DriverManager.getConnection (

"jdbc:oracle:thin@db.yale.edu :2000:univdb", userid , passwd );

Statement stmt=conn.createStatement

)

{

… Do ActualWork….

}

catch ( SQLException sqle ) {

System.out.println ("SQLException : "+sqle);

}

}/*具体的一些操作可以看我的选课系统*/

/*2. function与 Procedures*/

/*Compound statement: begin … end

returns indicates the variable type that is returned (e.g., integer)

return specifies the values that are to be returned as result of invoking the function*/

/*Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call

statement.*/

/*3. 嵌入式EXEC SQL fetch, close等等，可以自行了解*/

/*4. Trriger 创建一个trigger， P183*/

/*A trigger is a statement that is executed automatically by the system as a side effect of a

modification to the database.*/

create trigger timeslot_check1 after insert onsection

referencing new rowas nrow /*old and new 的使用*/

for each row /*for each statement 为每一个受影响的语句执行*/

when (nrow.time_slot_id not in(selecttime_slot_idfrom time_slot )) /*time_slot_id not present in time_slot*/

begin

rollback

end

/*5. While and repeat statements :*/

declare n integer default 0;while n < 10doset n = n + 1

end while

/*repeat 递归*/repeatset n = n 1until n= 0

endrepeat/*6. rank(), over, partition by, limit子句,*/

select ID , rank () over (order by GPA desc) ass_rankfromstudent_gradesorder bys_rank/*“Find the rank of students within each*/

selectID , dept_name

rank ()over (partition by dept_name order by GPA desc )/*null first , null last 也可在order by...desc后边用*/

asdept_rankfromdept_gradesorder bydept_name , dept_rank;/*7. 其他的rank函数*/percent_rank (within partition,if partitioning isdone)

cume_dist (cumulative distribution)

row_number (non deterministicin presence ofduplicates)/*补充*/

/*over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。

其参数：over(partition by columnname1 order by columnname2)

含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。*/

/*8. rollup, cube,*/

/*The cube operation computes union of group by ’s on every subset of the specified attributes*/

select item_name, color, size, sum (number)fromsalesgroup bycube (item_name, color, size)/*The rollup construct generates union on every prefix of specified list of attributes*/

select item_name , color , size , sum (number)fromsalesgroup byrollup (item_name, color, size)/*其他聚集函数

Pivoting: changing the dimensions used in a cross tab is called

Slicing:creating a cross tab for fixed values only. Sometimes called dicing , particularly when values for multiple dimensions are fixed.

Rollup: moving from finer granularity data to a coarser granularity

Drill down: The opposite operation that of moving from coarser granularity data to finer granularity data*/