数据库

最新推荐文章于 2025-08-09 18:20:04 发布

原创最新推荐文章于 2025-08-09 18:20:04 发布 · 328 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #math #null #date #数据库 #user

1. 操作数据(查询)

1.1. DISTINCT

语法

SELECT [DISTINCT] 列名[, 列名]...FROM 表名

注意事项

*可以替代列名, 表示所有列, 但是通常我们为了提高代码的可读性, 不使用*

DISTINCT为过滤重复记录

如果DISTINCT后面跟多列, 是过滤掉多列合并之后的重复

练习

查询employee表中所有记录

select id,username,gender,birthday,position,salary,resumefrom user;

查询employee表中所有人的薪水

select username,salary from user;

查询employee表中包含哪些岗位

1.2. 列名表达式

语法

SELECT 列名|表达式[,列名|表达式]... FROM 表名

注意事项

表达式只是显示时起作用, 不会改变数据库中的值

练习

导入student.sql

source .....

查询所有学生的总分

selectname,chinese+math+english from student;

查询employee表, 将所有员工薪水*2显示

selectusername,salary*2 from user;

1.3. AS

SELECT 列名 AS 别名 FROM 表名

注意事项

起别名时AS可以省略

不会改变数据库中的值

练习

查询所有学生总分, 别名为总分

selectname '姓名',chinese+math+english'总分' fromstudent;

1.4. WHERE

语法

SELECT 列名 FROM 表名 [WHERE 条件语句]

WHERE子句中的运算符

比较运算符

>, <, >=, <=, =, <>

注意不等于和Java中不同, 是<>

BETWEEN ... AND ...

某一区间内的值, 从 ... 到 ...

IN(列表)

在列表之中, 例: in(1,2,3) 代表1或2或3

LIKE(表达式)

模糊查询, %代表多个字符, _代表单个字符

IS NULL

判断是否为NULL

逻辑运算符

AND &&

与, 两边都为TRUE结果为TRUE

OR ||

或, 一边为TRUE结果就为TRUE

NOT !

非, 将表达式结果取反

练习

查询英语分数在80-90分之间的

selectname,english from student where english>=80 and english<=90;

查询语文分数为81,82,83的学生

select name,english from student where english in(80,90,82);

查询所有姓张的学生的成绩

select name,english,math,chinese from student where name like '张%';

查询除了姓张和姓李的学生总分

selectname,english,math,chinese

from student

where name notlike '张%'

and name notlike '李%';

selectname,english,math,chinese

from student

where name like'张%'

or name like '李%';

1.5. ORDER BY

语法

SELECT 列名 FROM 表名 ORDER BY 列名ASC|DESC;

注意事项

ORDERBY 指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名

ASC为升序, DESC为降序

ORDERBY应在查询语句的结尾

练习

对数学成绩排序后输出

selectname,math from student order by math;

查询总分, 从高到低显示

selectname '姓名',chinese+math+english'总分' fromstudent order by 总分 desc;

选择所有姓张的学生的英语成绩, 并从高到低排序

selectname,english from student where name like '张%' order by english desc;

查询学生成绩, 按照语文从高到低排序, 如果语文相同, 按照英语从高到低排序

select* from student order by chinese desc,english desc;

1.6. COUNT函数

语法

SELECT COUNT(*)|COUNT(列名) from 表名[WHERE 条件语句]

注意事项

COUNT(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

COUNT(*)则是统计表中有多少条数据

COUNT(DISTINCT列名) 统计不重复的记录数

如果加上WHERE子句, 则是统计满足条件的记录

练习

统计student表中有多少条记录

selectcount(*) from student;

统计学生语文成绩大于80的有多少人

selectcount(*) from student where chinese>80;

统计总分大于250的有多少人

selectcount(*) from student where english+math+chinese>250;

统计参加英语考试的有多少人

selectcount(english) from student;

1.7. SUM函数

语法

SELECT SUM(列名) FROM 表名 [WHERE 条件语句];

注意事项

计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

练习

计算所有学生的数学成绩总和

selectsum(math) from student;

显示所有学生的语文成绩总和, 数学成绩总和, 英语成绩总和

selectsum(chinese),sum(math),sum(english) from student;

计算所有学生的分数总和

selectsum(chinese)+sum(math)+sum(english) from student;

统计英语平均分

selectsum(english)/count(*) from student;

selectsum(english)/count(english) from student;

1.8. AVG函数

语法

SELECT AVG(列名) FROM 表名 [WHERE 条件语句];

注意事项

计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

AVG()统计平均数不包含NULL值

练习

计算英语平均分

selectavg(english) from student;

计算总分平均分, MySQL不支持组函数嵌套使用.

selectsum(english+math+chinese)/count(*) from student;

1.9. MAX / MIN函数

语法

SELECT MAX(列名) FROM 表名 [WHERE 条件语句];

SELECT MIN(列名) FROM 表名 [WHERE 条件语句];

注意事项

获取指定列最高/最低值, NULL不参与统计

练习

统计总分最高分和最低分

selectmax(english+math+chinese),min(english+math+chinese) from student;

1.10. GROUP BY(重点)

语法

SELECT 列名 FROM 表名 GROUP BY 列名[HAVING 条件语句]

注意事项

按照某列归类

HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数

SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名

HAVING中可以使用组函数, WHERE不能.

练习

导入order.sql

对订单表归类, 显示购买过哪些商品

selectproduct from orders group by product;

selectdistinct product from orders;

对订单表归类, 显示购买过哪些商品, 并显示每种购买了几个, 以及总价

selectproduct,count(product),sum(price) from orders group by product;

查询总价大于5000的商品有哪几类

selectproduct,count(product),sum(price) sum_price from orders group by product havingsum_price>5000;

2. 函数

2.1. 时间函数

注意date,datetime, timestamp之间的区别

ADDTIME(原时间, 增加值) 在某个时间上增加一段时间

selectaddtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0');

CURRENT_DATE() 当前日期

selectcurrent_date();

CURRENT_TIME() 当前时间

selectcurrent_time();

CURRENT_TIMESTAMP() 当前时间戳

selectcurrent_timestamp();

DATE(时间) 返回制定时间的日期部分

selectdate('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL增加值类型) 在指定日期上对某个字段增加

selectdate_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) 计算两个日期之间的差值

selectdatediff('2000-02-14', '2001-02-14');

NOW() 当前时间

selectnow();

selectyear('2011-02-14 23:00:00');

selecthour('2011-02-14 23:00:00');

2.2. 字符串函数

CHARSET(字符串) 返回字符串字符集

selectcharset(name) from student;

CONCAT(字符串1[,字符串2]... ) 连接字符串

selectconcat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串) 查找子字符串出现位置, 注意序号从1开始

selectinstr('abc', 'a');

UCASE(字符串) 将字符串转为大写

selectucase('aBc');

LCASE(字符串) 将字符串转为小写

selectlcase('aBc');

LEFT(字符串, 长度) 从字符串左边取指定长度个字符

selectleft('aBc',2);

LENGTH(字符串) 计算字符串长度

selectlength('aBc');

REPLACE(字符串, 搜索字符串,替换字符串) 将字符串中指定字符串替换为其他字符串

selectreplace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2) 逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值

selectstrcmp('abcc', 'abde');

selectstrcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[,个数]) 从字符串中截取

selectsubstring('abcdef', 3);

selectsubstring('abcdef', 3, 2);

LTRIM(字符串) 去掉左边空白

selectltrim(' abc ');

selectconcat('--', ltrim(' abc '), '--');

RTRIM(字符串) 去掉右边空白

selectconcat('--', rtrim(' abc '), '--');

TRIM(字符串) 去掉左右两边空白

selectconcat('--', trim(' abc '), '--');

2.3. 数学函数

ABS(数字) 求绝对值

selectabs(10);

selectabs(-10);

BIN(十进制数) 将十进制转换为二进制

selectbin(5);

HEX(十进制数) 将十进制转换为十六进制

selecthex(10);

CONV(数字, 原进制, 目标进制) 转换进制

selectconv(12, 10, 16);

selectconv(12, 10, 2);

selectconv(12, 16, 2);

CEILING(小数) 向上取整

selectceiling(3.4);

FLOOR(小数) 向下取整

selectfloor(3.4);

ROUND(小数) 四舍五入

selectround(3.4);

select round(3.5);

FORMAT(小数, 保留位数) 保留小数位

selectformat(3.1415926, 2);

LEAST(值,值[,值]...) 取最小值

selectleast(1,2,3,4);

selectleast('a', 'b', 'c', 'd');

GREATEST(值,值[,值]...) 取最大值

selectgreatest(1,2,3,4);

selectgreatest('a', 'b', 'c', 'd');

MOD(数字, 数字) 取余

selectmod(3,2);

select3%2;

RAND() 生成随机数, 14位小数, 0 <= n <= 1

selectrand();

3. 表的约束

约束的作用

限定某一列上的数据, 阻止非法数据录入, 提高程序健壮性.

3.1. 唯一约束 unique

unique约束的字段在整张表中唯一, 不可重复, 不包括多个NULL

创建表时设置唯一

create table test (

idint,

name varchar(20) unique

);

删除唯一约束

show create table test; 发现唯一索引名叫name

alter table test dropindex name;

添加唯一约束

alter table test changename name varchar(20) unique;

3.2. 非空约束 not null

not null约束的字段不能为空

创建表时设置非空

create table test1 (

idint,

name varchar(20) not null

);

删除非空约束

alter table test1 changename name varchar(20);

添加非空约束

alter table test1 changename name varchar(20) not null;

3.3. 主键约束 primary key

通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.

primary key用来标识一个字段, 这个字段是非空且唯一的.

创建表时设置主键

create table test2(

id int primary key,

name varchar(20)

);

删除主键

alter table test2 dropprimary key;

在制定列上添加主键

alter table test2 changeid id int primary key;

alter table test2 addprimary key(id);

设置主键自动增长

create table test3(

id int primary key auto_increment,

name varchar(20)

);

删除自增长

alter table test3 changeid id int;

设置自增长

alter table test3 changeid id int auto_increment;

UUID主键

128位的2进制, 32位16进制加上4个-

java.util.UUID.randomUUID().toString()

3c2372a4-da2a-4470-b17a-f2e50ac79636

3.4. 外键约束foreign key

foreignkey约束某一列的值是参照另外一列

创建表时添加外键约束

create tablehusband(

id int primary key,

name varchar(20) not null

);

create tablewife(

id int primary key,

name varchar(20) not null,

husband_id int,

constrainthusband_id_fk foreign key(husband_id) references husband(id)

);

wife表的husband_id的值必须是husband表中的id

被外键引用的记录不能删除, 如果想要删除某条被引用的记录, 需要找到引用这条记录的记录, 解除关联

被外键引用的表不能删除, 如果想要删除被引用的表, 需要删除所有引用此表的外键

删除外键约束

alter table wife dropforeign key husband_id_fk;

添加外键约束

alter table wife add constraint husband_id_fkforeign key(husband_id) references husband(id)