linux下Mysql基本语句

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 names you can turn off this feature t

2、o get a quicker startup with -a database changed mysql 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) 创建表mys

3、ql 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_test001 1 修改字段定义mysql alter table fx_swap_point_test001 modify front_date varchar(10);

4、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: 0 3 删除上面添

5、加到字段mysql alter table fx_swap_point_test001 drop count; query ok, 0 rows affected (0.01 sec) records: 0 duplicates: 0 warnings: 0 4 modify 与 change 都可以修改表定义，change 需要后面写两次列名，可以修改列名。modify not 。mysql alter table fx_swap_point_test001 change count001 count002 decimal(8,6); query ok, 0 rows affected (0

6、.03 sec) records: 0 duplicates: 0 warnings: 0 5 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)在整个表前面my

7、sql 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: 0 6 更改表名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

8、_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,o

9、pen_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,s

10、ystem,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,

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

12、,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(

13、) ) , (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,

14、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: zhenku