目录
数据库
创建数据库
create database databaseName;
切换到某个 数据库下
use databaseName;
表
创建表
create table tableName(dataName dataType);
例如:
create table test(date int);
创建数据
insert into person (number,name,birthday) values (1,'haha',20160509);
更新表中数据
update person set name="xixi" where name="heihei";
查看表结构
desc tableName;
重命名表
alter table t1 rename t2;
删除重复数据
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
查看前n行数据
select * from tableName limit n;
列
增加列
alter table user add COLUMN new1 VARCHAR(20) DEFAULT NULL; //增加一列,默认为空
alter table user add COLUMN new2 VARCHAR(20) NOT NULL; //增加一列 ,默认不能为空
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first;//在开头添加列
修改列
alter table user MODIFY new1 VARCHAR(10); //修改一个字段的类型
alter table user CHANGE new1 new4 int; //修改一个字段的名称
主键
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);//需要确保添加主键的列不能为空。添加主键
Alter table tablename Drop Primary key;//删除主键
删除列
alter table t2 drop column c;
重命名列
alter table t1 change a b integer;
mysql导入csv文件:
load data infile '/tmp/test.csv'
into table test_info
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';
参考链接:
https://blog.csdn.net/csdn10086110/article/details/89500065