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

参考：
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

发表于 @ 2009年04月27日　04:32:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:免费的网页制作软件 | 新一篇:ARP协议以及集线器，交换机，路由器的组合