SQL 实用操作

----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2

ALTER TABLE [dbo].[phone]
ADD   inactive_date DATETIME NULL

ALTER TABLE [dbo].[person]
ALTER COLUMN [lastname] VARCHAR(35) NULL

CREATE TABLE jobs
(
   job_id  smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   job_desc varchar(50) NOT NULL DEFAULT
   min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
   max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)
)

不能给View添加primary key，以前表里面的关键字到view里面也就不是关键字了。

删除表格全部数据： DELETE FROM tablename;

从文本文件导入数据到表格

Here is an example of creating a data file and then importing it with LOAD DATA syntax.

mulder@modwest:/$ echo 'a b c d' > testfile

mulder@modwest:/$ mysql
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 3979754 to server version: 4.0.15-Max-log

mysql> CREATE TABLE `test` (
    -> `fe` VARCHAR( 2 ),
    -> `fi` VARCHAR( 2 ),
    -> `fo` VARCHAR( 2 ),
    -> `fum` VARCHAR( 2 )
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> load data LOCAL infile '/testfile' into table test fields terminated by ' ' lines terminated by '/n'; 
Query OK, 1 row affected (0.02 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from test;
+------+------+------+------+
| fe   | fi   | fo   | fum  |
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+

1 row in set (0.00 sec)

穿插一个oracle操作："SELECT TABLE_NAME FROM USER_TABLES" 用来察看一个表，而MySQL下就是简单的"show databases" and "show tables"

将Mysql的查询结果直接输出到CSV文件：

mysql -uexampleuser -pletmein exampledb -B -e "select * from /`person/`;" | sed 's//t/","/g;s/^/"/;s/$/"/;s//n//g' > filename.csv

导入mysql dump数据：

mysql -h localhost -u root -p dblp < dblp-2009-02-07.sql

SQL SELECT TOP N equivalent in ORACLE and MySQL

Something I needed today... I wish this was standarized, but it is not, so here are some examples:

SQL Server:

SELECT TOP 10 product, descr, email 
FROM products 

ORACLE:

SELECT product, descr, email
FROM products 
WHERE ROWNUM <= 10

MySQL:

SELECT product, descr, email
FROM products
LIMIT 10

Rename a database:

mysqldump 
-
u username 
-
p 
-
v olddatabase 
>
 olddbdump
.
sql

mysqladmin 
-
u username 
-
p create newdatabase

mysql 
-
u username 
-
p newdatabase 
<
 olddbdump
.
sql

参考：
http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/
http://www.sqlinfo.net/sqlserver/sql_server_Alter_Add_column.php
http://www.electrictoolbox.com/article/mysql/delete-all-data-mysql/
http://www.modwest.com/help/kb6-253.html

http://tlug.dnho.net/?q=node/209

http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name