SQL 实用操作

nidr

　　----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:

SELECTTOP10product,descr,email

FROMproducts


　　ORACLE:

SELECTproduct,descr,email

FROMproducts

WHEREROWNUM<=10

　　
　　MySQL:

SELECTproduct,descr,email

FROMproducts

LIMIT10




　　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