mysql基础用法汇总

-- 新建表
create table member(
id int(10) not null auto_increment primary key,
uname varchar(10) not null default '',
sex tinyint(1) not null default 1 comment '性别:1-男,2-女',
score mediumint(5) not null default 0 comment '分数'
)engine innodb;

-- 修改表名
alter table member rename to members;

-- 查看表结构
desc member;

-- 复制表
create table member_copy like member;

-- 删除表
drop table member_copy;

-- 删除字段
alter table member drop address;

-- 修改字符集
alter table member default character set utf8; -- 插入string报错的话用下面的命令
ALTER TABLE member CONVERT TO CHARACTER SET utf8mb4;

-- 修改字段名和类型
alter table member change addr(旧) address(新) varchar(250) not null default '';

-- 修改字段类型
alter table member modify column score tinyint(3) not null default 0 comment '分数';

-- 添加单个字段
alter table member add column address varchar(250) not null default '' comment '地址' after score;

-- 添加多个字段
alter table member add (
type tinyint not null default 1 comment '反馈类型',
device varchar(60) not null default '' comment '设备号'
);

-- 添加索引
create index idx_sex_score on member (sex,score);

-- 查看索引
show index from member;

-- 删除索引
drop index idx_sex_score on member;


-- 插入单条数据
insert into member (uname,sex,score) values ('王五',2,85);
insert into member (uname,sex,score) values ('张三',1,90);
insert into member (uname,sex,score) values ('赵六',2,95);

-- 插入多条记录
insert into member values (5,'Alice', 1, 75),(6,'Jack',2,84);

-- 修改数据
update member set uname = '李红',score = 100 where id = 1;

-- 删除数据
delete from member where id = 4;