-- 创建表
drop table if exists user;
create table user(
`id` mediumint(5) not null auto_increment comment '主键',
`name` varchar(100) not null default '' comment '用户名',
`sex` tinyint(2) comment '性别:0男;1女',
`age` tinyint(3) unsigned comment '年龄',
`email` varchar(200) not null default '' comment '邮箱',
`password` char(40) not null default '' comment '密码',
`status` tinyint(1) not null default 0 comment '用户状态:0未启用;1启用',
`create_time` int(10) not null default 0 comment '注册时间',
primary key(`id`) using btree,
unique index `name`(`name`) using btree
);
-- 插入
insert into `user` values('','杨康',0,26,'yk@php.com',sha1(123456),1,1543223803),('','欧阳克',0,25,'oyk@php.com',sha1(123456),1,1543223867);
-- 更新
update `user` set `age` = 26,`status` = 0 where id = 4;
-- 查询
select `name`,`sex`,`age`,`email`,`status` from `user` where `status` = 1 order by `age` desc limit 2;
-- 执行简单运算
select 15*2 `res`;
-- 拼接字符串(直接拼接)
select concat(`id`,`name`) `user_str` from `user` where `status` = 1;
-- 拼接字符串(两个拼接的字符串之间有自定义连接符号)
select concat_ws('：',`id`,`name`) `user_str` from `user` where `status` = 1;
select count(`id`) `num` from `user` where `status` = 1;
-- 删除
delete from `user` where `id` = 5;
-- id重新排列
alter table `user` drop `id`;
alter table `user` add `id` int(5) primary key not null auto_increment first;
