数据库的查询、链接以及navicat工具的使用
一、 group by语法
语法
SELECT 列名 FROM 表名 GROUP BY 列名 [HAVING 条件语句]
注意事项
按照某列归类
HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数
SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名
HAVING中可以使用组函数, WHERE不能.
练习
导入order.sql
对订单表归类, 显示购买过哪些商品
select product from orders group by product;
select distinct product from orders;
对订单表归类, 显示购买过哪些商品, 并显示每种购买了几个, 以及总价
select product,count(product),sum(price) from orders group by product;
查询总价大于5000的商品有哪几类
select product,count(product),sum(price) sum_price from orders group by product having sum_price>5000;
二、 函数
1、 时间函数
注意date, datetime, timestamp之间的区别
ADDTIME(原时间, 增加值) 在某个时间上增加一段时间
select addtime('18:23:01', '01:01:01');
select addtime(now(),'3:0:0');
CURRENT_DATE() 当前日期
select current_date();
CURRENT_TIME() 当前时间
select current_time();
CURRENT_TIMESTAMP() 当前时间戳
select current_timestamp();
DATE(时间) 返回制定时间的日期部分
select date('2011-02-14 18:00:00');
DATE_ADD(日期,INTERVAL 增加值 类型) 在指定日期上对某个字增加
select date_add('2011-02-14 23:00:00', interval 10 month);
DATE_SUB(日期,INTERVAL 减少值 类型) 在指定日期上对某个字段减少
select date_sub('2011-02-14 23:00:00', interval 1 year);
DATEDIFF(日期1, 日期2) 计算两个日期之间的差值
select datediff('2000-02-14', '2001-02-14');
NOW() 当前时间
select now();
YEAR|MONTH|DATE|HOUR|MINUTE|SECOND(时间) 获取指定时间某个字段
select year('2011-02-14 23:00:00');
select hour('2011-02-14 23:00:00');
2、 字符函数
ABS(数字) 求绝对值
select abs(10);
select abs(-10);
BIN(十进制数) 将十进制转换为二进制
select bin(5);
HEX(十进制数) 将十进制转换为十六进制
select hex(10);
CONV(数字, 原进制, 目标进制) 转换进制
select conv(12, 10, 16);
select conv(12, 10, 2);
select conv(12, 16, 2);
CEILING(小数) 向上取整
select ceiling(3.4);
FLOOR(小数) 向下取整
select floor(3.4);
ROUND(小数) 四舍五入
select round(3.4);
select round(3.5);
FORMAT(小数, 保留位数) 保留小数位
select format(3.1415926, 2);
LEAST(值,值[,值]...) 取最小值
select least(1,2,3,4);
select least('a', 'b', 'c', 'd');
GREATEST(值,值[,值]...) 取最大值
select greatest(1,2,3,4);
select greatest('a', 'b', 'c', 'd');
MOD(数字, 数字) 取余
select mod(3,2);
select 3%2;
RAND() 生成随机数, 14位小数, 0 <= n <= 1
select rand();
三、 表的约束
主键约束 primary key
通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.
primary key用来标识一个字段, 这个字段是非空且唯一的.
创建表时设置主键
create table test2(
id int primary key,
name varchar(20)
);
删除主键
alter table test2 drop primary key;
在制定列上添加主键
alter table test2 change id id int primary key;
alter table test2 add primary key(id);
设置主键自动增长
create table test3(
id int primary key auto_increment,
name varchar(20)
);
删除自增长
alter table test3 change id id int;
设置自增长
alter table test3 change id id int auto_increment;
UUID主键
128位的2进制, 32位16进制加上4个-
java.util.UUID.randomUUID().toString()
3c2372a4-da2a-4470-b17a-f2e50ac79636
外键约束 foreign key
foreign key约束某一列的值是参照另外一列
创建表时添加外键约束
create table husband(
id int primary key,
name varchar(20) not null
);
create table wife(
id int primary key,
name varchar(20) not null,
husband_id int,
constraint husband_id_fk foreign key(husband_id) references husband(id)
);
wife表的husband_id的值必须是husband表中的id
被外键引用的记录不能删除, 如果想要删除某条被引用的记录, 需要找到引用这条记录的记录, 解除关联
被外键引用的表不能删除, 如果想要删除被引用的表, 需要删除所有引用此表的外键
删除外键约束
alter table wife drop foreign key husband_id_fk;
添加外键约束
alter table wife add constraint husband_id_fk foreign key(husband_id) references husband(id)
四、 表的设计
单独实体:
id | name | age |
1 | 张三 | 18 |
2 | 李四 | 20 |
3 | 王五 | 19 |
一对多,多对一
department employee
id | name |
1 | 开发部 |
2 | 市场部 |
id | name | department_id |
1 | 张三 | 1 |
2 | 李四 | 1 |
3 | 王五 | 2 |
多的一方设置外键
create table department(
id int primary key auto_increment,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
department_id int,
constraint department_id_fk foreign key(department_id) references department(id)
);
insert into department(name) values('开发部');
insert into department(name) values('市场部');
insert into employee(name, department_id) values('张三',1);
insert into employee(name, department_id) values('李四',1);
insert into employee(name, department_id) values('王五',2);
一对一:
foreign key |
unique |
id | name |
1 | 张三 |
2 | 李四 |
id | name | husband_id |
1 | 冰冰 | 2 |
2 | 志玲 | 1 |
独立外键, 没有依赖关系, 两个表的对象都可以独立存在.
create table husband(
id int primary key auto_increment,
name varchar(20)
);
create table wife(
id int primary key auto_increment,
name varchar(20),
husband_id int,
constraint husband_id_fk foreign key(husband_id) references husband(id)
);
insert into husband(name) values('张三');
insert into husband(name) values('李四');
insert into wife(name, husband_id) values('冰冰',2);
insert into wife(name, husband_id) values('志玲',1);
多对多:
foreign key |
foreign key |
student student_teacher teacher
id | name |
1 | 张三 |
2 | 李四 |
3 | 王五 |
student_id | teacher_id |
1 | 1 |
2 | 1 |
2 | 2 |
3 | 2 |
id | name |
1 | 张孝祥 |
2 | 黎活明 |
用一张关系表保存多对多的关系, 有两列分别引用两张表的主键, 并且这两列组合起来成为联合主键
create table student (
id int primary key auto_increment,
name varchar(20)
);
create table teacher (
id int primary key auto_increment,
name varchar(20)
);
create table student_teacher(
student_id int,
teacher_id int,
primary key(student_id, teacher_id),
constraint student_id_fk foreign key(student_id) references student(id),
constraint teacher_id_fk foreign key(teacher_id) references teacher(id)
);
insert into student(name) values('张三');
insert into student(name) values('李四');
insert into student(name) values('王五');
insert into teacher(name) values('zxx');
insert into teacher(name) values('lhm');
insert into student_teacher values(1,1);
insert into student_teacher values(2,1);
insert into student_teacher values(2,2);
insert into student_teacher values(3,2);
多表连接:
多张表连接查询, 一张表外键引用另外一张表, 另外一张表再引用其他表.
自连接:
自己和自己连接, 当前表的外键引用自己的主键.