不同的数据库管理系统，数据类型不完全一致
数字类型
- 整数类型:INT(m)和BIGINT(m)
m表示数字的长度(位数)
INT(5):该字段保存一个5位整数。此时该字段存的为数字18，实际存储:00018
- 浮点类型:DOUBLE(m,n)
m表示整体数字的位数
n表示小数的位数
DOUBLE(5,3):该字段可以保存一个5位数，其中3位是小数。最大可以保存到:99.999
实际插入数据时如果精度超过了可保存的范围时，会自动进行四舍五入:
XXX表有一个DOUBLE(5,3)的字段:
INSERT INTO XXX VALUES(12.8576)此时该字段实际保存的值为:12.858
字符类型
- CHAR(n):定长字符串。
n:长度，单位是字符。CHAR(10),该字段可以存放最多10个字符
最大长度:255个字符
表中每条记录该字段在磁盘上占据的空间是固定的，如果实际保存的字符不足指定的长度时，会在后面补充若干个空格来达到长度。
name CHAR(10)
INSERT INTO person (name) values('张三'):实际该记录中name的值:'张三 '(后面有8个空格)
优点:由于长度固定，因此查询速度快
缺点:磁盘占用存在浪费的情况 - VARCHAR(n):变长字符串
n:长度，单位是字节。VARCHAR(10),该字段保存的字符转换的字节最多10个。
最大长度为:65535个字节
优点:实际字段值占用多少字节就在磁盘上开辟多少字节。
缺点:长度不固定，因此查询性能慢 - TEXT(n):可变长字符
n:长度，单位是字符。最大值为65535 - DATE：可以保存年月日
- TIME：保存时分秒
- DATETIME：保存年月日时分秒
- TIMESTAMP: 时间戳，记录UTC时间。从1970-01-01 00:00:00到其表示的时间之间经过的毫秒
例
插入日期时，可以使用字符串格式，格式应当为:'yyyy-MM-dd hh:mm:ss'
- MM表示两个数字的"月"，mm表示两位数字的"分"
INSERT INTO userinfo VALUES(1,'张三','1992-08-02 11:23:56',5000.98);
如果日期类型使用的是DATETIME，那么在不指定时分秒时，默认为00:00:00
INSERT INTO userinfo VALUES(2,'李四','1989-06-30',9000);
DATETIME不能忽略年月日
INSERT INTO userinfo VALUES(3,'王五','13:25:33',12000);
插入DOUBLE类型数字时，超过精度部分则会四舍五入
INSERT INTO userinfo VALUES(3,'王五','1986-03-15',6000.857);
整数部分超过时会报错
INSERT INTO userinfo VALUES(4,'赵六','1986-03-15',100000);
DQL
DQL用于检索表中数据的语言。
基本语法：
SELECT 子句
FROM 子句
JOIN... ON... 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
ORDER BY 子句
一条DQL语句必须包含的两个子句分别为:SELECT子句和FROM子句
- SELECT 子句用于选定表中的字段，选定的字段会被包含在查询的结果集中
- FROM 子句用于指定查询的表
查看student表中所有记录，每条记录所有字段都要展示在结果集中。
SELECT * FROM student;
"*"在SELECT子句中表达的意思时查看表中所有字段。
SELECT子句中可以仅指定个别字段进行查询
查看每个学生的名字，年龄，性别
SELECT name,age,gender
FROM student
WHERE子句
WHERE子句可以添加过滤条件，那么此时仅查询出满足该子句过滤条件的记录
比较运算符:=,>,>=,<,<=,<>
例
- 查看所有大队长
- 查看年龄在30岁以上的老师的名字和年龄(含30岁)
在WHERE子句中可以使用OR,AND来连接多个过滤条件
- AND:与。都为真时才为真
- OR:或。都为假时才为假
例如
- 查看学生中7岁的大队长都有谁?
SELECT name,age,job
FROM student
WHERE age=7 AND job='大队长'; - 查看7岁的大队长和中队长都有谁？
AND的优先级高于OR
SELECT name,age,job
FROM student
WHERE age=7 AND job='大队长' OR job='中队长';
上述SQL查询的语义是:查看7岁的大队长和所有部分年龄的中队长
通过添加"()"来提高OR的优先级
实际写法应当是:
SELECT name,age,job
FROM student
WHERE age=7 AND (job='大队长' OR job='中队长'); - IN(列表) 等于列表之一
查看职位是大队长,中队长或小队长的学生?
SELECT name,job
FROM student
WHERE job='大队长' OR job='中队长' OR job='小队长';
等价
SELECT name,job
FROM student
WHERE job IN('大队长','中队长','小队长'); - NOT IN(列表) 不等于列表中的内容
查看职位除了大队长，中队长，小队长的其他学生?
SELECT name,job
FROM student
WHERE job<>'大队长' AND job<>'中队长' AND job<>'小队长';
等价
SELECT name,job
FROM student
WHERE job NOT IN('大队长','中队长','小队长'); - BETWEEN...AND... 在两者之间
查看年龄在9到11岁之间的学生都有谁?
SELECT name,age
FROM student
WHERE age>=9 AND age<=11;
等价
SELECT name,age
FROM student
WHERE age BETWEEN 9 AND 11; - DISTINCT 去重操作。去除结果集中指定字段值重复的行。
DISTINCT关键字必须紧跟在SELECT关键字之后
查看学校的学生有多少种职位?
SELECT DISTINCT job
FROM student;
可以多字段去重，去除指定的字段值的组合重复的行
查看学校同年龄同职位都有多少种?(去除年龄与职位组合相同重复的行)
SELECT DISTINCT age,job
FROM student;
- LIKE：模糊查询
LIKE中有两个通配符
- _ 下划线表示1个字符
- % 百分号表示任意个字符(0---多次)
组合示意
- %X% 字符中含有X的
- %X 字符以X结束
- X% 字符以X开始
- _X _ 在三个字符中第二个字符为X
- %X_ 字符中倒数第二个为X
- X_Y 三个字符中第一个为X，第三个为Y
- X%Y 字符中第一个是X，最后一个是Y
例
- 查看所有姓张的同学都是谁? 理解为:名字是以"张"开头的。匹配字符应写为:'张%'
SELECT name
FROM student
WHERE name LIKE '张%'; - 查看名字中含有王的同学都是谁? 匹配字符:'%王%'
SELECT name
FROM student
WHERE name LIKE '%王%'; - 查看三个字名字且最有一个字是"平"的同学都有谁? 理解:前两个字是任意字符 匹配字符:'__平'
SELECT name
FROM student
WHERE name LIKE '__平'; - 查看"李"姓的三个字的名字的同学都有谁? 匹配字符:'李__'
SELECT name
FROM student
WHERE name LIKE '李__'; - 查看职位是"语文什么表"的同学都有谁? 匹配字符:'语文%表'
SELECT name,job
FROM student
WHERE job LIKE '语文%表'; - 判断NULL值。
- IS NULL 判断某个字段的值是否为NULL
- IS NOT NULL 判断某个字段的是不为NULL
- 不能直接搭配"="或"<>"来判断NULL值！！！
查看奖金(comm)为空的老师都有谁?
SELECT name,salary,comm
FROM teacher
WHERE comm=NULL;
上述SQL是查询不到任何数据的!!
正确写法:
SELECT name,salary,comm
FROM teacher
WHERE comm IS NULL;
查看有奖金的老师都有谁?
SELECT name,salary,comm
FROM teacher
WHERE comm IS NOT NULL;
ORDER BY子句排序
ORDER BY 子句只能是DQL中的最后一个子句(原因是该子句是最后执行的子句)。
作用:对结果集按照指定字段值的升序或降序进行排序
排序方式:
- 升序:ASC 默认不写就是升序
- 降序:DESC
- 多字段排序时:会首先按照第一个字段排序，然后当第一个字段值相同的记录中再按照第二个字段排序以此类推
例:
- 查看老师的工资排名? 按照工资进行降序(从大到小)
SELECT name,salary,comm
FROM teacher
ORDER BY salary DESC;
- 查看老师的奖金?按照升序(从小到大)排序
SELECT name,salary,comm
FROM teacher
ORDER BY comm ASC;
或者
SELECT name,salary,comm
FROM teacher
ORDER BY comm; - 按照日期字段排序时，升序为从早到晚
按照生日从远到近排序学生
SELECT name,birth
FROM student
ORDER BY birth; - 按照老师的工资降序后再按照奖金的升序排
SELECT name,salary,comm
FROM teacher
ORDER BY salary DESC,comm;
如果第一个字段的值没有重复值，则第二个字段排序无效 - 按照学生的年龄的升序再按照生日的降序排序
SELECT name,age,birth
FROM student
ORDER BY age,birth DESC;
别名
别名在SELECT子句中可以被应用于字段上，函数上或表达式上
别名也可以被应用于其他子句中，比如在FROM子句中为表取别名
- 查看老师的年薪是多少?
SELECT name,salary*12
FROM teacher;
上述DQL查询结果集中，第二个字段的字段名直接使用是该表达式，可读性差。
SELECT name,salary*12 sal
FROM teacher;
可以为表达式取别名，此时查询结果集第二个字段名会使用该别名。可读性好。 - 定义别名支持的语法
- 字段名 别名
SELECT name,salary*12 sal FROM teacher; - 字段名 AS 别名
SELECT name,salary*12 AS sal FROM teacher; - 字段名 AS '别名' AS可以省略
SELECT name,salary*12 AS 'sal' FROM teacher; - 字段名 AS "别名" AS可以省略
SELECT name,salary*12 AS "sal" FROM teacher;
聚合函数
聚合函数用来统计的。可以将一个查询结果集中的多条记录统计为一条。
聚合函数:
- MIN() 求指定字段在结果集中所有记录里的最小值
- MAX() 求指定字段在结果集中所有记录里的最大值
- AVG() 求指定字段在结果集中所有记录里的平均值
- SUM() 求指定字段在结果集中所有记录里值的总和
- COUNT() 不是对字段值的统计，而是对结果集中记录数的统计。统计结果集的记录数
聚合函数的使用时，首要任务是先将需要统计的所有记录查询出来，之后再加以统计。
例:
- 查看老师的平均工资是多少?
- 先列出所有老师的工资
SELECT salary FROM teacher; - 对salary加上聚合函数，将结果集中该字段值的所有记录统计平均数
SELECT AVG(salary) FROM teacher;
- 查看学校老师的最高工资，最低工资，平均工资和工资总和分别是多少?
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM teacher; - 查看负责课程编号1的老师的平均工资是多少?
- 查看负责课程编号1的老师的工资?
SELECT salary FROM teacher WHERE subject_id=1; - 对上述结果集应用聚合函数统计结果
SELECT AVG(salary) FROM teacher WHERE subject_id=1;
- 查看所有老师的奖金总和和平均奖金是多少?
聚合函数忽略NULL值，这个在AVG和COUNT中表现最为明显。
SELECT SUM(comm),AVG(comm)
FROM teacher;
上述DQL查询后实在奖金总和基础上除以15得到的平均值，而非18.因为有3个老师奖金为NULL
可以通过搭配IFNULL来解决:
SELECT SUM(comm),AVG(IFNULL(comm,0))
FROM teacher; - 查看学校一共多少个老师?
很多数据库都对COUNT(*)进行过优化，因此统计记录数通常就用它。
SELECT COUNT(*) FROM teacher; - 查看负责课程编号1的老师一共几人?
SELECT COUNT(*) FROM teacher WHERE subject_id=1; - 查看班级编号(class_id)为1的班里有多少个学生?
SELECT COUNT(*) FROM student WHERE class_id=1; - 查看学校年龄最大的大队长生日是哪天?
寻找student表中所有大队长的生日中距离现在最远的
SELECT MIN(birth) FROM student WHERE job='大队长';