- sql中查找为空的记录,使用IS NULL和IS NOT NULL关键字
select * from database.table where row1 IS NULL
select * from database.table where row1 IS NOT NULL
- 获取数据表的列名等信息,也可以直接使用select * from information_schema.'COLUMNS’查看information.schema.'COLUMNS’表中都有什么列名称
select column_name from information_schema.'COLUMNS' where table_name = 'sql_table_name' and table_schema = 'db_name'
- 从数据库中查找数据,select的用法
select security_id from t_securities order by rand() limit 3
select * from t_order limit 20,10;
- 获取表中该列最大的值
select max(value_version) from t_config_item
- 根据数据表中的值作字典转换,如数据库中保存’101’实际意义为上交所,'102’实际意义为深交所
select security_id,if(market_id='101','上海','深圳') from t_securities
- 插入时,因为外键插入失败,可以设置不校验外键
SET foreign_key_checks=0;
- 设置、删除表索引
1.PRIMARY KEY(主键索引)
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (`column` )
3.INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
5.多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )