1、# sql 基本语句 #sql 基本语句交互方式演示10.28.8.59连接mysql -uroot -p mysql h - or help创建及删除数据库mysql create database test01;Query OK, 1 row affected (0.02 sec)show-mysql show databases;选择数据库mysql use HKMAIN;Reading table information for completion of table and column namesYou can turn off this feature to get a quic
2、ker startup with -ADatabase changedmysql show tables;+-+| Tables_in_HKMAIN |+-+| FX_ACCOUNT_STATUS_MAIL | FX_AFFILIATE | FX_WITHDRAWAL_TRIGGER | UNIQUE_ID_GENERATOR |+-+221 rows in set (0.00 sec)删除数据库mysql drop database test01; Query OK, 0 rows affected (0.00 sec)创建表mysql show create table FX_SWAP_P
3、OINT G;修改成 test 进行演示查看表定义desc 。 。查看更全面的表定义show create table删除表mysql drop table FX_SWAP_POINT_TEST;Query OK, 0 rows affected (0.01 sec)(可以命令行演示，grep 过滤)#修改表进行表结构的更改 FX_SWAP_POINT_TEST0011 修改字段定义mysql alter table FX_SWAP_POINT_TEST001 modify FRONT_DATE varchar(10);Query OK, 0 rows affected (0.01 sec)R
4、ecords: 0 Duplicates: 0 Warnings: 0修改 front_date 字段， 将 varchar(8)修改为 varchar(10)2 增加表字段新增字段 COUNT，类型 decimal(12,6)mysql alter table FX_SWAP_POINT_TEST001 add COUNT decimal(12,6);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 03 删除上面添加到字段mysql alter table FX_SWAP_POINT_TEST001
5、 drop COUNT;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 04 modify 与 change都可以修改表定义， change 需要后面写两次列名，可以修改列名。modify not。mysql alter table FX_SWAP_POINT_TEST001 change COUNT001 COUNT002 decimal(8,6);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 05 fi
6、rst|after column增加 COUNT009 字段 decimal(8,6),位置在 FRONT_DATE 之后mysql alter table FX_SWAP_POINT_TEST001 add COUNT009 decimal(8,6) after FRONT_DATE;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0增加 FIRST_001 字段 varchar(9)在整个表前面mysql alter table FX_SWAP_POINT_TEST001 add FIRST_00
7、1 varchar(9) first;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 06 更改表名mysql alter table FX_WITHDRAWAL_TRIGGER rename FX_WITHDRAWAL_TRIGGER_BAK;Query OK, 0 rows affected (0.00 sec)mysql alter table FX_WITHDRAWAL_TRIGGER_BAK rename to FX_WITHDRAWAL_TRIGGER; Query OK, 0 rows
8、affected (0.01 sec)插入数据例子 HKMAIN.FX_PRODUCT 初始化insert into FX_PRODUCT (PRODUCT_ID, PRODUCT_TYPE, LEVERAGE_LEVEL_ID, DISPLAY_SORT_ORDER, CURRENCY_PAIR, MAX_QUANTITY,MIN_QUANTITY,LOT_SIZE,TRADE_DEPOSIT_USD,TRADE_DEPOSIT_HKD,OPEN_BUY_CONSTRAINT,OPEN_SELL_CONSTRAINT,ORDER_CANCEL_CONSTRAINT,ORDER_CHANGE_
9、CONSTRAINT,CLOSE_BUY_CONSTRAINT,CLOSE_SELL_CONSTRAINT,USABLE_FLAG,UPDATE_STAFF_ID,INPUT_STAFF_ID,LOSSCUT_COMMISSION,ACTIVE_FLAG,INPUT_DATE,UPDATE_DATE )VALUES (A001, 0, 1 ,100,EUR/USD,3000000,10000 ,10000 ,183220,60000 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A002, 0, 1 ,100,GBP/USD,3000000,
10、10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A003, 0, 1 ,100,USD/JPY,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A004, 0, 1 ,100,USD/CHF,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A005,
11、 0, 1 ,100,AUD/USD,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A006, 0, 1 ,100,NZD/USD,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A007, 0, 1 ,100,USD/CAD,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTE
12、M,1,1,now(),now() ) ,(A008, 0, 1 ,100,EUR/JPY,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A009, 0, 1 ,100,GBP/JPY,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A010, 0, 1 ,100,AUD/JPY,3000000,10000 ,10000 ,183220,183220
13、,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A011, 0, 1 ,100,CAD/JPY,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A012, 0, 1 ,100,GBP/CHF,3000000,10000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A013, 0, 1 ,100,AUD/NZD,3000000,1
14、0000 ,10000 ,183220,183220 ,0,0,0,0,0,0,1,SYSTEM,SYSTEM,1,1,now(),now() ) ,(A014, 0, 1 ,100,USD/HKD,3000000,10000 ,10000 ,183220,183220 ,0,0,3,3,1,1,1,SYSTEM,SYSTEM,1,1,now(),now() ) ;不指定字段名称的时候，values 后面的顺序应该和字段的排列顺序一样。更新数据mysql update FX_CUSTOMER set EN_GIVEN_NAME1=test where CUSTOMER_ID=00000002;
15、Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0* 1. row *CUSTOMER_ID: 00000002ACCOUNT_TYPE: 2DOCUMENT_SEND_STATUS: 1REGION: 3ACCOUNT_CURRENCY: HKDEN_SURNAME: zhenkunEN_GIVEN_NAME1: testUPDATE_DATE: 2011-04-13 13:08:361 row in set (0.00 sec)ERROR: No query specified删除表中数据delete from tablename where conditionselect CUSTOMER_ID from FX_CUSTOMER G;delete from FX_CUSTOMER where CUSTOMER_ID=;
