mysql移动数据的语句是_MySQL的基本语句(一)

创建表

CREATE TABLE tb_name(

id int NOT NULL AUTO_INCREMENT, # 非空、自增声明

name VARCHAR(30) UNIQUE, #添加字段的唯一约束

update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, #设置时间戳默认值为当前时间

last_updated TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,#设置自动添加更新时间

size ENUM( 'small', 'medium', 'large') DEFAULT 'small', #枚举类型声明

PRIMARY KEY (id), #主键声明

FOREIGN KEY (id) REFERENCES db_name(tb_field) #声明外键

);

插入数据

# 插入数据

INSERT INTO tb_name VALUES ('Gates', 'Bill', 'ACC', 'Beijing');

# 插入指定列的数据

INSERT INTO tb_name (tb_field1, tb_field2) VALUES ('Gates', 'Bill');

数据查询

# 基本查询

SELECT * FROM tb_name;

SELECT * FROM tb_name WHERE id = XXX;

# MD5 加密，加密比较

INSERT INTO user(name,password) VALUES("admin",md5("12345"));

SELECT * FROM user WHERE name="admin" AND password=md5("12345");

# 查询最 新 的一条数据

1) SELECT * FROM tb_name ORDER BY date DESC LIMIT 1;

2) SELECT * FROM tb_name WHERE date IN (SELECT max(tb_field) FROM tb_name);

3) SELECT * FROM tb_name WHERE date = (SELECT max(tb_field) FROM tb_name);

# 查询记录总数

SELECT count(*) FROM tb_name;

# 查询前十条数据

SELECT * FROM tb_name LIMIT 10;

# 查询平均值

SELECT AVG(tb_field) FROM tb_name;

SELECT name, AVG(tb_field) FROM tb_name GROUP BY name;

修改数据

数据修改

UPDATE tb_name SET tb_field1 = 'One', tb_field2 = 'Two' WHERE tb_field3 = '111';

属性修改

# 添加列

ALTER TABLE tb_name ADD COLUMN state ENUM('1','0') NOT NULL DEFAULT '1';

# 添加唯一約束

1) ALTER TABLE tb_name ADD UNIQUE (name);

2) CREATE UNIQUE INDEX [索引名称] ON tb_name(tb_field);

# 更改列属性

ALTER TABLE tb_name MODIFY COLUMN tb_field VARCHAR(50);

# 更改列名

ALTER TABLE tb_name RNAME COLUMN tb_fieldA to tb_fieldB;

ALTER TABLE tb_name CHANGE tb_fieldA tb_fieldB varchar(255);

删除数据

# 删除一个记录

DELETE FROM tb_name WHERE name = 'Bill';

# 删除所有行

DELETE FROM tb_name;

# 删除表

DROP TABLE tb_name;

# 删除数据库

DROP DATABASE db_name;

# 删除索引

ALTER TABLE tb_name DROP INDEX [索引名称];

# 删除列

ALTER TABLE tb_name DROP COLUMN column_name;

其他

# 导出数据库

mysqldump -u [用户名] -p [数据库名] > [导出的文件名]

mysqldump -u username -p db_name > db_name.sql;

# 导出数据表

mysqldump -u username -p db_name tb_name> db_name.sql;

# 导入数据库

1) mysql -u username -p db_name < [xxx.sql];

2) use db_name;

source xxx.sql;