SQL常用

use db_sqlserver;
go
create table db_table6
(
订单编号 int primary key,
订单号 varchar(50) unique,
职工号 varchar(50) references db_table5(职工号),
订购日期 datetime,
销售金额 int
)
use db_sqlserver;
go
create table db_table8
(
职工编号 int primary key,
职工号 varchar(50) unique,
仓库编号 int identity(1,1) primary key,
基本工资 int check(基本工资>=800 and 基本工资<=2100),
加班工资 int,
奖金 int,
扣率 int,
应发工资 as (基本工资 + 加班工资 + 奖金 - 扣率)
)

insert into [db_sqlserver].[dbo].[db_table9](仓库号, 面积) values(‘400‘, 1600);

===============================
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url="jdbc:odbc:stuDB";
conn=DriverManager.getConnection(url,login,password);
statement=conn.createStatement();
查询
void print(){
sqlQuery="select * from Student";
try {
rs=statement.executeQuery(sqlQuery);
} catch (SQLException e) {
return;
}
showData(rs);
}

void showData(ResultSet rs){
text="Sno\tSname\tSsex\tSage\tSdept\n";
try {
while(rs.next()){
text+=rs.getString(1)+"\t";
text+=rs.getString(2)+"\t";
text+=rs.getString(3)+"\t";
text+=rs.getInt(4)+"\t";
text+=rs.getString(5)+"\n";
}
} catch (SQLException e) {
e.printStackTrace();
}
printEntry.setText(text);
}

Add、更新、del
sqlQuery="INSERT INTO Student values(‘"+sno+"‘,‘"+sname+"‘,‘"+ssex+"‘,‘"+sage+"‘,‘"+sdept+"‘)" ;
sqlQuery="Update Student set Sno=‘"+sno+"‘,Sname=‘"+sname+"‘,Ssex=‘"+ssex+"‘,Sage=‘"+sage+"‘,Sdept=‘"+sdept+"‘ where Sno=‘"+sno+"‘" ;
sqlQuery="Delete FROM Student where Sno=‘"+sno+"‘" ;
statement.executeUpdate(sqlQuery);

求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下。
SELECT SUM(column_name)
FROM table_name

SELECT SUM(SAL) AS BOYSAL
FROM TEACHER
WHERE TSEX=‘男‘

SELECT COUNT(*) AS TOTALITEM
FROM TEACHER

SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME,
COUNT(SAL) AS TOTAL_SAL
FROM TEACHER

SELECT MAX (column_name) / MIN (column_name)
FROM table_name

SELECT TNAME, DNAME, TSEX, SAL, AGE
FROM TEACHER
WHERE AGE＝(SELECT MAX (AGE) FROM TEACHER)

SELECT MIN (CTEST) AS EARLY_DATE,
MAX (CTEST) AS LATE_DATE
FROM COURSE

SELECT AVG (column_name)
FROM table_name

SELECT AVG (AGE) AS AVGCOMPUTER_AGE
FROM TEACHER
WHERE DNAME = ‘计算机‘

SELECT AVG ([ALL/DISTINCT] column_name)
FROM table_name
说明：[ALL/DISTINCT]在缺省状态下，默认是ALL关键字，即不管是否有重值，处理所有数据。其他聚合函数的用法与此相同。

SELECT COUNT(DISTINCT SAL) AS DISTINCTSAL_COUNT
FROM TEACHER

在所有5种聚合函数中，除了COUNT(*)函数外，其他的函数在计算过程中都忽略NULL值，即把NULL值的行排除在外，不进行分析

SELECT COUNT(*) AS num_items,
MAX(SAL) AS max_sal,
Min(AGE) AS min_age,
SUM(SAL)/COUNT(SAL) AS avg_sal,
AVG(DISTINCT SAL) AS disavg_sal
FROM TEACHER