o**s
发帖数: 1 | 1 各位高手：
我想实现下面的SQL块语句
begin
if TABLE是否存在
delete from TABLE;
else
create TABLE;
...
end
谢了 |
|
o**k
发帖数: 5 | 2 try to select * into a temp table from
employee where salary > 10000
is there any way to create this temp table?
rather than create a temp table space.? |
|
h****r
发帖数: 2056 | 3 in Oracel, I meet a problem when I create a table cause the
table name is
above 32, is there a way we can reset the table name to the
value I want?
thanks for any suggestion. |
|
g***n
发帖数: 5 | 4 在UNIX环境下往ORACLE 建TABLE，是不是只有用SQL的CREATE呀？
有没有GUI的TABLE GEMERATOR 可以快速建表？那里有DOWNLOAD？
建了TABLE以后，怎么样把他IMPORT 到ORCALE中去？
多谢了。 |
|
m***r
发帖数: 294 | 5 Here, I got one foreign key data_id in table PLAN reference to two table
PROGRAM_TEXT(primary key data_id) and PROGRAM_MEDIA(primary key data_id)
how to write sql?
Create Table Plan
(
....
foreign key (Data_id) references PROGRAM_MEDIA(Data_id) ON DELETE CASCADE,
foreign key (Data_id) references PROGRAM_TEXT(Data_id) ON DELETE CASCADE
)
It doesn't work!!!!
pls help me
email: j*******[email protected] |
|
m***r
发帖数: 294 | 6 Here, I got one foreign key data_id in table PLAN reference to two table
PROGRAM_TEXT(primary key data_id) and PROGRAM_MEDIA(primary key data_id)
how to write sql?
Create Table Plan
(
....
foreign key (Data_id) references PROGRAM_MEDIA(Data_id) ON DELETE CASCADE,
foreign key (Data_id) references PROGRAM_TEXT(Data_id) ON DELETE CASCADE
)
It doesn't work!!!!
pls help me
email: j*******[email protected] |
|
F******y
发帖数: 1988 | 7 Hello,
I did successfully install oracle9i in redhat linux7.3.
And I did copy my old database tables from my MSSQL server to Oracle9i by
using the ODBC administrative tool in windows.
From the client side(Oracle9i client), i can connect to my oracle database,
and I can see the all the tables schema I copied there. But when I use "select
* from tUser" to get data, it give me error"table or views doest not exist"
I also use another oracle client tool (TOAD) to try, the error is the same.
But I can |
|
b****e
发帖数: 15 | 8 1)
How to Merge two tables into one new table. All the design are exactly same
2)
How to merge 4 columns "month","day","year","hour" in previous table into the
1 column "date" in the new column
thanks. |
|
i*****t
发帖数: 220 | 9 Please tell me how to drop a table in Oracle. I used in this way but have
error:
SQL> drop table MESSAGE;
drop table MESSAGE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04052: error occurred when looking up remote object
What is wrong with it? Thanks. |
|
f***g
发帖数: 10 | 10 should define a deault not null value, example:
create table #tmp (aaa int not null primary key)
insert into #tmp values(1)
insert into #tmp values(2)
alter table #tmp
add bbb int default 0 not null
the statement
"alter table #tmp
add ccc int not null"
will fail |
|
c*****t
发帖数: 1879 | 11 What is the sql command for inserting values into a table?
The table has just one column called id, which is double type.
The id is unique. If I insert bunch of id's into the table,
some of might be duplicate. What to do in that case?
Thanks |
|
c*****t
发帖数: 1879 | 12 That's not what I meant. I meant that I have a existing SQL table
called Queue. It has a single column id. Given this table, I now
have a bunch of ids to put into the table. Yet, these ids may be
duplicated. The question is how to avoid the duplicate and at
the same time be as efficient as possible.
Thanks |
|
p*****e
发帖数: 58 | 13 document table had docID, author table has authorID, then use a link table
document_author which has docID and authorID. |
|
w***y
发帖数: 6251 | 14 我只用一个也是有错：（
DROP TABLE test CASCADE;
ERROR: parser: parse error at or near "CASCADE"
DROP TABLE test RESTRICT;
ERROR: parser: parse error at or near "RESTRICT"
DROP TABLE test;
DROP |
|
s***e
发帖数: 284 | 15 create a new table without the column you try to delete
then copy all data to new table and delete the old table |
|
b*****y
发帖数: 26 | 16 Question # 1:
Why build a customized B-tree/Hash table ?
How is it different from the B-tree implementation in a database server?
Why the B-tree/Hash table implemented in mysql server is NOT good ?
How can a customized B-tree/Hash table benefit ?
Somebody cannot drive a car from Boston to S.F. in one hour
does not necesserily mean you can do it if you drive by yourself.
Question # 2:
How upgrading hardware will make the application faster ...
say from 5 minutes per query to 1 minute per query ? |
|
b*****y
发帖数: 26 | 17 Can you give us the reason why you would consider BDB ?
I would consider BDB as the worse choice. Here is my reason:
According to the features provided by BDB,
http://dev.mysql.com/doc/refman/5.0/en/bdb-characteristics.html
* It provides transactional tables.
Transaction is not need at all for me for data mining.
What is more, I need to pay the price for having transactions,
and I cannot disable transaction feature.
* Each table has to have a primary key, which is stored with the table data
(to |
|
y*********d
发帖数: 1013 | 18 I want to delete some fields for a table in Access, but get the following
prompt:
"Table tblCustomers is a linked table with some properties that can't be
modified. Open it anyway?"
I opened it anyway, but can't deleted fields
who knows what's problem with that?
thanks in advance |
|
c*a
发帖数: 806 | 19 建了一个database link, works well
但是如果想将remote database 拷贝到local database
only a couple of tables, but large size
create table tbl_name from select * from tbl@remote;
不行，可能table size 太大
如果可以copy的话，could be（import/export), 如何进行incremental backup? |
|
k*******1
发帖数: 22 | 20 "Neither the DELETE statement without a WHERE clause nor the TRUNCATE
TABLE statement affect the schema structure of the table or related
objects."
这里的“the schema structure of the table or related objects”是指什么啊？ |
|
m******9
发帖数: 104 | 21 I am new to MSSQL. Can anyone tell me how to backup a single table? It is
system table msdb. I'll need to backup few table from msdb.
Thank you |
|
S*****0
发帖数: 538 | 22 这个没问题，刚试过。 估计,是#test table 需要先定义，不然，ＳＱＬ Server
paser 会遇到SELECT ...INTO #test 就定义一次＃test, 导致错误 。
IF OBJECT_ID(N'tempdb.dbo.#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test(col1 int)
IF (2>1)
INSERT INTO #test(col1)
SELECT 1 as col1
ELSE
BEGIN
INSERT INTO #test(col1)
SELECT 2 as col1
END |
|
m******1
发帖数: 98 | 23 不好意思，又要麻烦大家了。
我有两个ACCESS Table，
Table1， 有190 patients, 有20个column.
Table 2, 有90 patients, 有 10个column.
这些column里面 有些一样，有些不一样。 patients 也是有几个重复。
想问问这种情况下能不能把两个table 合并到一起呢？
一个朋友说，是不可能并到一个的。用Query也不行。
谢谢！ |
|
y****9
发帖数: 144 | 24
tree.
log
YOu are probably right. this maybe the first time I need to think in big O
notation after school. Even in school I was not goot at algorithm anaysis.
If we put therotical algorithm complexity anaysis aside, from a practical
point of view,Is it safe to say the speed of select based on PK index is
almost same regardless of table lengh? I just checked the bigest table in
one of my Oracle db, it has 2 billion row. the consistent gets is 4.
So it should be index root block, branch block... 阅读全帖 |
|
y****9
发帖数: 144 | 25 Original question:
mySQL中"select by primary/unique key的速度" 与 "number of rows in a table= N
" 有关吗？是O(LOG(N))的关系吗？
If we understand the original question as the relationship between the cost
of index access and the table length, my opinion is that they do not have
direct relashionship. Big-O notation is used to analyze algorithm's
efficiency and complexity, it seems not appropriate to be used to describe
such a relathiship
based on Jonathan Lewis's CBO book, the cost of a simple B... 阅读全帖 |
|
S*******s
发帖数: 13043 | 26 if I have a table like this
CREATE TABLE myTable(
id integer,
category char(10),
value float
)
and want to get a cross table pivoting the category. a usual approach using
subquery and outter join is like following
SELECT myTable.id, t1.value as c1, t2.value as c2, t3.value as c3
FROM myTable
LEFT JOIN ( SELECT value FROM myTable WHERE category = 'category 1' ) c1 ON
myTable.id=c1.id
LEFT JOIN ( SELECT value FROM myTable WHERE category = 'category 2' ) c2 ON
myTable.id=c1.id
LEFT JOIN ( SELECT va... 阅读全帖 |
|
h****t
发帖数: 22 | 27
首先声明，我是作应用程序开发的，在这个版上贴query是在班门弄斧，让大家见笑了。
CREATE TABLE table1(id int,InheritedFromID int)
CREATE TABLE table2(id int,prop1 nvarchar(50),prop2 nvarchar(50))
CREATE TABLE table3(id int,prop1 nvarchar(50),prop2 nvarchar(50))
insert into table1 (id,InheritedFromID) values(1,0)
insert into table1 (id,InheritedFromID) values(2,1)
insert into table1 (id,InheritedFromID) values(3,2)
insert into table2(id,prop1,prop2) values(1,'a0','a1')
insert into table2(id,prop1,prop2) values(1,'a2','a3')
insert into... 阅读全帖 |
|
g***l
发帖数: 18555 | 28 CTE就不能UPDATE吧，反正我从不UPDATE CTE，就跟VIEW一样，一旦产生了就不能改了
，而且是用完就扔。你的这个用TABLE VARIABLE会好一些，
Common Table Expressions, or CTE, are a new construct introduced in
Microsoft SQL Server 2005 that offer a more readable form of the derived
table that can be declared once and referenced multiple times in a query. |
|
g***l
发帖数: 18555 | 29 CTE就是在内存里暂时存一下，尤其是这种自己UPDATE自己的，搞不好就乱套，写到
TABLE里存进DISC,一般是这样用CTE的，记住是在内存里，随用随扔，如果要UPDATE CTE就再产生一个CTE，扔掉以前那个，
;WITH CTE_1
(
SELECT BLAH BLAH
FROM
TABLE A
),
CTE_2
(
SELECT BLAH BLAH
FROM
CTE_1
JOIN OTHERS
),
CTE_3
(
SELECT BLAH BLAH
FROM
CTE_2
JOIN ...
),
UPDATE
TABLE A
FROM
CTE_3
WHERE |
|
g***l
发帖数: 18555 | 30 我还是没搞懂你想干什么，什么叫rank不同table里面的数据，你举个列子好不好，我
觉得你这个SP本身问题就很大，你有多少TABLE要RANK，RANK完了放在哪里？总不能就
扔在MANAGEMENT STUDIO里再COPY出来吧。为什么不能COMBINE呢
无非是这样么，RANK然后GROUP BY TABLENAME不就行了，这样就避免了有多少TABLE,
RANK出来放在哪里的问题。
TABLENAME DATA RANK
TABLE1 XXXX RANK
TABLE1 XXXX2 RANK
TABLE2 XXXX RANK
TABLE2 XXXX2 RANK |
|
k**g
发帖数: 1558 | 31 问题是这个不是production的table,我的SP的Permission可以alter table, 只是
update table的时候出错。你知道我的syntax错在哪里了吗？
我承认我们公司的sql就是野鸡，但是只要让我完成这个rank就行了，我们也没有复杂
的数据库。 |
|
i****a
发帖数: 36252 | 32 SQL 2008
Is it possible to copy a table from DB1 to DB2? I am not talking about
select insert, but I want to copy the whole table over as quickly as
possible.
There are couple large tables need to be copied a new database. insert will
take forever. I am checking if I can use RedGate's SQL Backup and object
level restore in some way. In the mean time, any suggestions? |
|
x*****g
发帖数: 764 | 33 为什么我用create statement创建的table不马上显示出来。table其实已经在那儿了，
因为已经可以往里写数据了，但是总是要quit server以后，再重新start后才看到
table挂到那里。我是个数据库盲，正在努力学习，谢谢。 |
|
c*********e
发帖数: 16335 | 34 一次导入的是目前有的历史数据，这个数据量非常大，需要一次性导入fact table。此
后的数据只需要每天／星期／月update进fact table就可以了。 |
|
B*****g
发帖数: 34098 | 35 我的无根据理解：
oracle：table variable是一个object
sql server：table variable是一个table |
|
s**********o
发帖数: 14359 | 36 如果输出的是个TABLE的话，你就最后弄个
SELECT ALL COLUMN NAMES FROM TABLE VARIABLE（或者TEMP TABLE） |
|
n****e
发帖数: 1403 | 37 MySQL table either insert or drop/truncate table running forever ?
engine=innodb,
innodb_file_per_table
What is the possible reason?
Thanks |
|
|
s**********o
发帖数: 14359 | 39 FACT TABLE就是一堆KEY和你的MEASURES了，就是要分析的对象了
比如产量，销售量，销售额等等
DIMENSION TABLE就是DENORMALIZED的数据了，
比如ACCOUNT, PRODUCT，CUSTOMER每一个都是一个DIMENSION |
|
y*****g
发帖数: 677 | 40 都说得对，还是举例子好：
FACT table:
SALES (day_key, salerep_key, product_key, order_amount);
dimension tables:
DAY,
SALEREP,
PRODUCT
PRODUCT
|
|
\ /
DAY --> SALES <--SALEREP |
|
|
s**********o
发帖数: 14359 | 42 FACT TABLE就是一堆KEY和你的MEASURES了，就是要分析的对象了
比如产量，销售量，销售额等等
DIMENSION TABLE就是DENORMALIZED的数据了，
比如ACCOUNT, PRODUCT，CUSTOMER每一个都是一个DIMENSION |
|
y*****g
发帖数: 677 | 43 都说得对，还是举例子好：
FACT table:
SALES (day_key, salerep_key, product_key, order_amount);
dimension tables:
DAY,
SALEREP,
PRODUCT
PRODUCT
|
|
\ /
DAY --> SALES <--SALEREP |
|
M*********e
发帖数: 190 | 44 题目问的有问题吧。
product 和 customer是dimension table。
sale应该是fact table吧。可是题目只给了两个dimension的key，没有给其他的column。
不make sense。 |
|
l******9
发帖数: 579 | 45 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: compare two large tables SQL
发信站: BBS 未名空间站 (Thu May 8 19:33:54 2014, 美东)
I am comparing two tables to make sure they are same row by row and column
by column on SQL server.
SELECT *
FROM t1, t2
WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
AND t1.column3 = t2.column3 AND t1.column4 != t2.column4
The tables are vey large, more than 100 million.
I got error:
ERROR [HY000] ERROR:... 阅读全帖 |
|
l******9
发帖数: 579 | 46 I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
col1 INT
col2 INT
col3 INT
col4 DOUBLE PRECISION
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
Table1 is :
col1 col2 col3 col4
80 790 3498 18654.064361
81 589 3182 2138518.05404
80 ... 阅读全帖 |
|
w****n
发帖数: 266 | 47 What is the best way to copy 2 millions rows from A table in the test server
to B table in the production server? A and B tables are identical. How long
will that take? thanks. |
|
l******9
发帖数: 579 | 48 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: generate unique integer ID from columns in SQL table
发信站: BBS 未名空间站 (Fri Nov 14 17:36:46 2014, 美东)
I need to generate a new column in a table in a sql database.
the given table is:
id1 value1 value2 value3 value4
9465 387 801 1990 20
All columns are integer. All columns are integer. value1 and value2 are
always 3 digits, value3 are year value, value4 is not more than 3 digits.
I need to g... 阅读全帖 |
|
l******9
发帖数: 579 | 49 I would like to import a .csv file into a SQL Server 2008R2 database table
on Win 7.
It has one column and one row.
File1:
name
runeocnu 430274 sjunosnv djduenvop 934m ljfonm3
I got error:
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
conversion for column "NAME" returned status value 4 and status text "Text
was truncated or one or more characters had no match in the target code page
.".
(SQL Server Import and Export Wizard)
I have created the destination table
CRE... 阅读全帖 |
|
s**********o
发帖数: 14359 | 50 比如，把浏览网页的记录做成PARTITION TABLE，时间，IP,看了啥是一个记录
按照年份或者月份做PARTITION比如2015年的一个FILEGROOUP, 2014年的一个
FILEGROOUP，2013，2012，2011等等。现在你说删掉一半，一般就是按照年份删吧，把
2011 2012年的删掉，把FILEGROUP去掉就行了。但你要说，把IP是102.-190。的全部删
掉，那只能去TABLE上删掉，跟PARTITION就没啥关系了，INDEX 就DBCC REINDEX一下就
得了。PARTITION的目的就是为了BACKUP容易，查询起来容易。你要DROP PARTITION就
说明你的PARTITION建的目的不对，没弄好，一般都不会去删东西。TABLE PARTITION
针对的都是海量数据，比如色情网站一天的浏览量就上亿，要存个几年的数据就会
巨大无比，按时间PARTITION，历史数据一般也不会去删除。 |
|
