-- 新建表
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;
mysql基础用法汇总
于 2020-04-23 15:16:02 首次发布