3.3 模糊查询
如,当想查询姓名中包含某个字母的学生时,需要使用模糊查询
关键字 like
通配符; _任意一个字符 %任意0~n 个字符
3.3.1 查询名字有4个字母组成的学生信息
一个匹配任意一个字母,4个代表有4个任意字母组成
SELECT *FROM stu WHERE username LIKE ‘__‘;
查询名字由4个字母组成 并且最后一个字母为1的学生信息
SELECT *FROM stu WHERE username LIKE ‘___1’;
查询姓名以T开头的学生的信息
SELECT *FROM stu WHERE username LIKE ‘t%’;
查询姓名中第2个字母为o的学生的信息
SELECT *FROM stu WHERE username LIKE ‘_o%’;
查询姓名中包含o的学生的信息
SELECT *FROM stu WHERE username LIKE ‘%o%’;
3.4 字段控制查询
3.4.1 去除重复记录
SELECT DISTINCT age FROM stu;
3.4.2 查看学生年龄和成绩之和
注意 因为相加的两列都是数值类型 所以可以做数学运算
若有一列不是 则会出错
SELECT *,age + score FROM stu;
3.4.3 判空
ifnull(score,0)若为null就返回0,否则返回score
SELECT *,age +ifnull(score,0)FROM stu;
3.4.4 别名
给列添加别名
SELECT *, age +ifnull(score,0)AS total FROM stu;
as可以省略
SELECT *, age +ifnull(score,0) total FROM stu;
3.5排序
asc(确认)升序
desc 降序
查询所有学生信息 按年龄升序排序
SELECT *FROM stu ORDER BY age ASC ;
SELECT *FROM stu ORDER BY age ;
查询所有学生信息 按年龄降序排序
SELECT *FROM stu ORDER BY age DESC ;
查询所有学省信息 按年龄降序排序 若年龄相同 则按分数排序
SELECT *FROM stu ORDER BY age DESC ,score ASC ;
3.6 聚合函数
用来做纵向运算
count() -统计指定列 不为null的记录行数
max () - 计算指定列的最大值,若为字符串则使用字符串比较规则
min () -计算指定列的最小值,若为字符串则使用字符串比较规则
sum () _计算指定列的数值和, 若不是数值类型则结果为0;
avg () _计算指定列的平均值, 若不是数值类型则结果为0;
3.6.1 count
查询表中的记录数 并给列起别名cnt
SELECT count(*) AS cnt FROM stu;
查询表中有分数的记录数 并给列起名cnt
null 不算
SELECT count(score) AS cnt FROM stu;
查询表中分数大于90 分的人数 并分别给起别名 cnt
SELECT count(*) AS cnt FROM stu WHERE score>60;
查询表中分数与年龄之和大于120的学生人数
SELECT count(*) AS cnt FROM stu WHERE ifnull(score,0) + ifnull(age,0) >50;
分别统计有分数的人数 有生日的人数
SELECT count(score),count(birthday) FROM stu;
3.6.2 sum() 和avg
纵向求年龄和
SELECT sum(age) FROM stu;
查询所有学生年龄和以及分数和
SELECT sum(age),sum(score)FROM stu;
查询所有学生年龄+分数和
SELECT sum(age + ifnull(score ,0)) FROM stu;
统计所有学生的平均年龄
SELECT avg(age) FROM stu;
3.6.3max()和 min()
c查询最高工资和最低工资
SELECT max(score),min(score)FROM stu;
第二天
分组查询
当需要分组查询时,需要使用GROUP BY子句
如查询每个年龄的分数和,要使用到年龄分组
注意 凡是和聚合函数同时出现的列名一定要在group by 之后
查询每个年龄的年龄值和每个年龄的分数和
SELECT age,sum(score)FROM stu GROUP BY age;
查询每个年龄的年龄值和每个年龄的人数
SELECT age,count(age)FROM stu GROUP BY age;
查询每个年龄的年龄值和每个年龄分数大于56的人数
SELECT age,count(*)FROM stu WHERE score>56 GROUP BY age;
having 子句
having 与where 对比
1having 是在分组之后对数据进行过滤
where 是在分组之前对数据进行过滤
#2having 后面可以使用聚合函数
#where 后面不可以使用聚合函数
where 是对分组前进行筛选 如果某行几轮没有满足where子句的条件
那么这行记录不会参与分组
having 是对分组后的数据进行的约束
查询分数总和大于100的年龄以及具体分数
SELECT age,sum(score) FROM stu GROUP BY age HAVING sum(score) >100;
limit方言
limit方言是用来限定查询结果的起始行,以及总行数的
查询stu表中的前5行记录
SELECT *FROM stu LIMIT 0,5;
查询stu表中3到7 行记录
SELECT *FROM stu LIMIT 2,5;
数据的完整性
作用:保证用户输入的数据保存到数据库中是正确有效的
约束:主键约束 (primary key);
唯一约束(unique)
自动增长列(auto_increment)
主键约束 (primary key);
特点:数据唯一,且不能为NULL;
添加主键的第一种方式
CREATE TABLE person(
id INT PRIMARY KEY ,
name VARCHAR(50)
);
添加主键的第二种方式
比第一种方式的优势在于:可以创建联合主键
CREATE TABLE person1(
id INT ,
name VARCHAR(50),
PRIMARY KEY (id)
);
添加主键的第三种方式
CREATE TABLE person2(
id INT ,
name VARCHAR(50)
);
ALTER TABLE person2 ADD PRIMARY KEY (id);
第三种添加方式的好处是
可以做到后来的补充添加
弊端是,若添加数据之前有数据,需要数据满足约束 否则无法添加约束
INSERT INTO person(id,name )VALUES(1,’tom’)
INSERT INTO person(id,name )VALUES(1,’tom1’)
唯一约束(unique)
CREATE TABLE person (
id INT,
name VARCHAR(50) UNIQUE
);
主键与唯一约束的区别:
1,对于一个表来说,主键只能有一个
唯一却可以多个
2,主键约束的字段不能为null
唯一可以
自动增长列(auto_increment)
给主键添加的属性,自动增长的数值,列只能是整数类型
注意,若删除之前增长的序号,后面再添加的序号不会重新开始
而是会接着被删除的那一列序号
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
INSERT INTO teacher(name) VALUES (‘tom’);
INSERT INTO teacher(name) VALUES (‘tom2’);
SELECT *FROM teacher;
#非空约束: not null
CREATE TABLE teacher1(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
非空约束的字段不能插入空值
INSERT INTO teacher1 (name) VALUES (NULL );
默认值约束: default
CREATE TABLE teacher11(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
sex VARCHAR(10)DEFAULT ‘man’
);
INSERT INTO teacher11(name) VALUES (‘tom’ );
INSERT INTO teacher11 (name,sex) VALUES (‘tom’,’woman’ );
INSERT INTO teacher11 (name,sex) VALUES (‘tom’,DEFAULT );
SELECT *FROM teacher11;
SHOW DATABASES ;
DROP DATABASE mydb1;
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
INSERT INTO emp VALUES(7369,’SMITH’,’CLERK’,7902,’1980-12-17’,800,NULL,20);
INSERT INTO emp VALUES(7499,’ALLEN’,’SALESMAN’,7698,’1981-02-20’,1600,300,30);
INSERT INTO emp VALUES(7521,’WARD’,’SALESMAN’,7698,’1981-02-22’,1250,500,30);
INSERT INTO emp VALUES(7566,’JONES’,’MANAGER’,7839,’1981-04-02’,2975,NULL,20);
INSERT INTO emp VALUES(7654,’MARTIN’,’SALESMAN’,7698,’1981-09-28’,1250,1400,30);
INSERT INTO emp VALUES(7698,’BLAKE’,’MANAGER’,7839,’1981-05-01’,2850,NULL,30);
INSERT INTO emp VALUES(7782,’CLARK’,’MANAGER’,7839,’1981-06-09’,2450,NULL,10);
INSERT INTO emp VALUES(7788,’SCOTT’,’ANALYST’,7566,’1987-04-19’,3000,NULL,20);
INSERT INTO emp VALUES(7839,’KING’,’PRESIDENT’,NULL,’1981-11-17’,5000,NULL,10);
INSERT INTO emp VALUES(7844,’TURNER’,’SALESMAN’,7698,’1981-09-08’,1500,0,30);
INSERT INTO emp VALUES(7876,’ADAMS’,’CLERK’,7788,’1987-05-23’,1100,NULL,20);
INSERT INTO emp VALUES(7900,’JAMES’,’CLERK’,7698,’1981-12-03’,950,NULL,30);
INSERT INTO emp VALUES(7902,’FORD’,’ANALYST’,7566,’1981-12-03’,3000,NULL,20);
INSERT INTO emp VALUES(7900,’MILLER’,’CLERK’,7782,’1982-01-23’,1300,NULL,50);
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept VALUES(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept VALUES(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept VALUES(40, ‘OPERATIONS’, ‘BOSTON’);
员工表
SELECT *FROM emp;
部门表
SELECT * FROM dept;