mysql常用

创建数据库，id为主键，自增，设定编码
create database dz;


create table dz_tiezi(
id int not null primary key auto_increment,
tietle varchar(20) not null,
content longtext not null
)


删除数据表
DELETE FROM table_name WHERE column_name = some_value


删除数据库

drop database 数据库名字

删除重复数据，保留id最小的一行（单个字段判断）

delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);

修改数据表数据

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

查找数据表数据

select * from tablename where 条件

添加数据表数据

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

添加数据表列

ALTER TABLE table_name ADD column_name datatype

向表中增加一个 VARCHAR 列： 

ALTER TABLE distributors ADD COLUMN address VARCHAR(30);

对现存列改名： 

ALTER TABLE distributors RENAME COLUMN address TO city;

对现存表改名： 

ALTER TABLE distributors RENAME TO suppliers;

向表中增加一个外键约束： 

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL

修改主键ID自增归0

alert table tablename auto_increment = 1；

MySQL语法: UPDATE table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

MySQL 示例: update user a, member b set a.name= b.name, a.age= b.age where a.id=b.id

修改数据表字符集编码

alter table tb_anniversary convert to character set utf8;