bada130
- 浏览: 39566 次
- 性别:
- 来自: 北京
-
最新评论
文章列表
-- Oracle分页（效率好）
SELECT *
FROM (SELECT A.*
, ROWNUM RN
FROM (SELECT *
FROM EMP
) A
WHERE ROWNUM <= 200
)
WHERE RN >= 100;
-- Oracle分页（效率不好）
SELECT ...
SELECT TO_CHAR(TO_DATE('20140101', 'YYYYMMDD') + LEVEL - 1, 'YYYY-MM-DD') AS MYDATE
FROM DUAL CONNECT BY LEVEL <= (TO_DATE('20141231', 'YYYYMMDD') - TO_DATE('20140101', 'YYYYMMDD') + 1);
-- 查看当前登陆用户所属的全部表
SELECT * FROM USER_TABLES;
SELECT *
FROM DBA_USERS;
CREATE OR REPLACE PROCEDURE WHERE_CURRENT
IS
CURSOR EMP_LIST IS
SELECT EMPNO
, ENAME
, SAL
FROM EMP
WHERE EMPNO = 1000
FOR UPDATE;
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('***** 更新操作 *****');
...
CREATE OR REPLACE PROCEDURE EXPCURSOR_TEST
(
V_DEPTNO IN DEPT.DEPTNO%TYPE
)
IS
-- 声明CURSOR
CURSOR DEPT_AVG IS
SELECT B.DNAME AS DNAME
, COUNT(A.EMPNO) AS EMPCNT
, ROUND(AVG(A.SAL), 3) AS SAL
FROM EMP A, DEPT B
...
DECLARE
V_CNT NUMBER := 100;
BEGIN
DBMS_OUTPUT.ENABLE;
LOOP
INSERT INTO EMP(EMPNO, ENAME, HIREDATE)
VALUES (V_CNT, 'TEST' || TO_CHAR(V_CNT), SYSDATE);
V_CNT := V_CNT + 1;
EXIT WHEN V_CNT > 110;
END LOO ...
CREATE TABLE [dbo].[aaa](
[aaa] [varchar](255) NULL
)
if exists(select * from information_schema.tables where table_name='aaa')
BEGIN
DROP TABLE aaa
select N'有，所以删除了'
END
else
BEGIN
select N'没有'
END
-- 会员表
CREATE TABLE T_MEMBER (
MEMBER_ID CHAR(02) PRIMARY KEY,
MEMBER_NAME VARCHAR(20)
)
-- 商品表
CREATE TABLE T_PRODUCT (
PRODUCT_ID CHAR(02) PRIMARY KEY,
PRODUCT_NAME VARCHAR(20)
)
-- 订单表
CREATE TABLE T_ORDER (
ORDER_ID INT PRIMARY KEY,
MEMBER_ID CHAR(02),
PRODUCT_ID CHAR(02),
...
1. 打开SSMS(SQL Server Management Studio)，点击表的列目录
2. 用鼠标点击相应表中的列目录后向编辑窗口拖动，列目录中的全部列显示到编辑窗口中
USE Northwind
GO
--第一步：声明游标
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM Northwind.dbo.Employees
WHERE LastName like 'B%'
--第二步：打开游标
OPEN Employee_Cursor
--第三步：FETCH游标
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT F ...
--生成数据库
CREATE TABLE Test1
(
col1 int,
col2 char(10) NULL,
)
GO
--循环插入（插入一百万条数据）
DECLARE @num int
SET @num = 1
WHILE @num <= 1000000
BEGIN
INSERT INTO Test1 VALUES(@num, 'A' + CONVERT(char(10), @num))
SET @num = @num + 1
END
eclipse设置：
Window->Preferences->Java->Editor->Content Assist
SELECT * FROM Employee
100001 张一 10000.00 D001
100002 张二 20000.00 D001
100003 张三 15000.00 D002
100004 张四 18000.00 D003
SELECT * FROM Department
D001 IT
D002 生产
D004 物流
--INNER JOIN(或JOIN)
SELECT E.emp_code, E.emp_name, E.dept_code, D.dept_name
FROM Employee E INNER ...
参考地址：http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins