linux下Mysql基本语句.docx

# 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 quicker 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_POINT 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)Records: 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 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 first|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_001 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 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_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,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, 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,SYSTEM,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 ,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,10000 ,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;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0 * 1. row * CUSTOMER_ID: 00000002 ACCOUNT_TYPE: 2 DOCUMENT_SEND_STATUS: 1 REGION: 3 ACCOUNT_CURRENCY: HKD EN_SURNAME: zhen