建表
主键
主键:必须为自增长,自增长不一定为主键。自增长是指已有最大编号加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;