MySQL基础：增删改查

建表

主键

主键：必须为自增长，自增长不一定为主键。自增长是指已有最大编号加1。

• 自增长：auto_increment
• 非空：NOT NULL
• 默认值：DEFAULT
• 唯一性约束：UNIQUE

创建表：

create table if not exists user(
id tinyint unsigned auto_increment key,
username varchar(20) not null unique,
password char(32) not null,
email varchar(50) not null default '12345@qq.com',
age tinyint unsigned default 18
);

修改表结构

修改表名

alter table user7 rename to user0;
alter table user0 rename user7;

添加字段

alter table user7 add addr char(18);
alter table user7 add id_addr char(18) not null unique;

添加多个字段：

alter table user7
add test4 char(18) not null unique first,
add test5 char(18) not null unique first,
add test6 char(18) not null unique first;

删除字段

# 删除user7表中的 test6 字段
alter table user7 drop test6;

删除多个字段

alter table user7
drop test5,
drop test4,
drop test3,
drop test2,
drop test;

修改字段

# 将 email 修改为 varchar(200)
alter table student modify email varchar(50) not null default '123456@qq.com';

修改字段出现的位置：

alter table student modify email varchar(50) not null default '123456@qq.com' first;

修改字段名称：

alter table student change email email2 varchar(50) not null default '123456@qq.com';

增

# 
insert into user values(1, 'king', 'king', 'king@qq.com', 20);
# 另一种方法
insert user(username, id, password, email, age) values('king2', 2,'king2', 'king2@qq.com', 20);

一次增加多个数据：

insert user values(3, 'u3', 'u3', 'u3@qq.com', 23),
(4, 'u4', 'u4', 'u4@qq.com', 24),
(5, 'u5', 'u5', 'u5@qq.com', 25),
(6, 'u6', 'u6', 'u6@qq.com', 26);

根据set 增加字段：

insert user set id=8,username='test2',password='thisistest',email='123@qq.com',age=48;

更改

UPDATE user set age=5;
# 更新第一条记录
UPDATE user set age=100 where id=1;
# 队id 大于三的数据的年龄 加10
UPDATE user set age=age+10 where id>3;

删除

DELETE FROM user WHERE id=1;

清空表：

TRUNCATE user0;

查询

# 库名.表名
SELECT * FROM maizi.user;

设置别名：

select id,username FROM user as a;
# 将id 显示为编号， username 显示为用户名
select a.id as '编号',a.username as '用户名' FROM user as a ORDER by a.username;

按照范围查询：

# 范围内
SELECT id as '编号', username as '用户名' FROM user where id BETWEEN 3 and 10;
# 范围外
SELECT id as '编号', username as '用户名' FROM user where id NOT BETWEEN 3 and 10;
# 指定集合
SELECT * FROM user where id IN(1,3,5,6,7);

模糊查询：

# 含有 张
SELECT * FROM user WHERE username LIKE '%张%';
# 张 开头
SELECT * FROM user WHERE username LIKE '张%';
# 占位符
# 用户名有3位
SELECT * FROM user WHERE username LIKE '___';

分组查询：

SELECT * FROM user GROUP BY age;
# 多字段分组
select * FROM user GROUP BY age, id;
# 
select * FROM user WHERE id>=5 GROUP BY age;
# 从user表中 按照性别分组 查询 id sex 统计username 计算总数 
select id,sex GROUP_CONCAT(username) as users, COUNT(*) as totaoUsers FROM user GROUP BY sex;

常用的函数：

• COUNT
• MAX
• MIN
• AVG
• SUM

ROLLUP 统计总和：

# 增加一行总数统计
SELECT id,sex,
COUNT(*) as totalUsers,
MAX(age) as max_age,
MIN(age) as min_age,
FROM users
GROUP BY sex WITH ROLLUP;

having 二次筛选：
须跟在GROUP BY 后

# 三组 男 女 保密
SELECT sex, GROUP_CONCAT(username) as users,
COUNT(*) as totalUsers,
MAX(age) as max_age,
SUM(age) as sum_age,
GROUP BY sex; 

# 组中人数大于2
SELECT sex, GROUP_CONCAT(username) as users,
COUNT(*) as totalUsers,
MAX(age) as max_age,
SUM(age) as sum_age,
GROUP BY sex,
HAVING COUNT(*) > 2;