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;


拼接字符查询


select concat('11','22','33');


+------------------------+

| concat('11','22','33') |

+------------------------+

| 112233 |

+------------------------+



mysql 删除字段的值某个字符串

update 表 set ZD=replace(ZD,'123','') where ZD like '%123%'



本文由站长原创或收集,不代表本站立场,如若转载,请注明出处:https://pds.ac.cn/jishu/59.html

本文 暂无 评论

回复给

欢迎点评

联系我们

站长QQ:770083771

站长邮件:770083771@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code