SQL常用指令

SQL常用指令

这是本人学习LintCode上面关于SQL的学习笔记，其网页上面有相应的教程和练习，链接如下SQL学习网址。有时间或者感兴趣的同学可以去上面更好的学习。

SQL对大小写不敏感，此外习惯性用 ``来包含表名、列名防止和操作关键字重复
首先我们定义一个学生表为示例

id	name	class	grade	country	age
1	Tom	2-1	A	US	20
2	Jack	2-1	A	US	23
3	Cassini	2-1	A	CN	22
4	Jerry	2-1	B	CN	24
5	Rus	2-1	A	UK	20
6	Yuki	2-1	D	JP
7	Max	2-2	A	HO	21
8	Hom	2-2	A	UK	27

查找、更新、删除

--从student表中查找name列的所有信息--
select ` name `
from `student;

--从student表中查找name，id列的所有信息--
select ` name `,`id`//中间用逗号隔开即可
from `student;

--从student表中查找所有信息--
select *
from `student;

--从student表中查找成绩信息（且成绩信息不重复）所有信息--
select distinct `grade`//distinct用于去掉重复数据
from `student`;

--从student表中查找grade='A'的学生的所有信息--
select *
from`student`
where `grade`='A';

--往student中插入一个新的学生信息--
insert into `student`
values(9，"zhangsan","2-3",'D',"CN");//每个值的属性需要与对应表中的列名属性相匹配

--往student中插入一个新的学生指定信息--
insert into `student`(`name`,`class`,`country`)
values("lisi","2-3","CN");

--更新学生成绩--
update `student`
set `grade`='C'
where `name`="Yuki";//将Yuki的成绩改该为C;

--指定删除数据--
delete from `student`
where `name`="Yuki";//Yuki因为擅自改成绩被开除了

--删除所有数据--
delete from `student`;//Yuki一怒之下删了所有同学的数据

各类运算符和限定条件

常用的比较运算符有 =（等于） 、!=（不等于）、 <>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于），其中 != 和 <> 在特殊情况下用法是不同的。

--查找成绩为B及以上的同学信息--
select *
from `student`
where `grade`>=B;

--查找国籍为CN的同学们的姓名--
select `name`
from `student`
where `country`="CN";

--查找国籍为CN且成绩为A同学的姓名--
select `name`
from `student`
where `country`="CN" and `grade`='A';

--查找国籍为CN或JP的同学的姓名--
select `name`
from `student`
where `country`="CN" or `country`="JP";

--查找成绩及格的同学的信息--
select *
from `student`
where not `grade`='D';//用NOT排除指定条件的人

--查找国籍为CN或JP的同学的姓名--
select `name`
from `student`
where `country` in("CN","JP");

--查找国籍除了CN或JP的同学的姓名--
select `name`
from `student`
where `country` not in("CN","JP");

--查找班级是2-1和2-3之间的同学姓名--
//请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
//在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
//在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
//在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。
//因此，请检查您的数据库是如何处理 BETWEEN 操作符！
select `name`
from `student`
where `class` between '2-1'and'2-2';

--查询无成绩的同学信息--
select *
from `student`
where `grade`is null;

--查询开头字母为J的同学信息--
select *
from `student`
where `name` like J%;//like是一种模糊查询的方式 
//%	替代 0 个或多个字符
//_	替代一个字符
//[charlist]字符列中的任何单一字符
//或 [!charlist]不在字符列中的任何单一字符

--对表内数据基于成绩进行排序--
select *
from `student`
order by `grade`;
//ASC|DESC分别表示升序和降序，其中order by 默认基于升序排序

--对表内数据基于成绩和班级进行排序--
select *
from `student`
order by `grade`,`class`;//列属性表示排序的优先级

--输出排序表的前三个学生信息--
select *
from `student`
order by `grade`,`class`
limit 3;

//where 限定符在order by之前，而limit则又在limit之后
--输出成绩最好的第一个CN学生--
select *
from `student`
where `country`='CN';
order by 'grade',
limit 1;

各类算数函数的使用

--对表内数据中的年龄求均值且将其命名为avg_age输出--
select avg(`age`) as `avg_age`//直接使用avg()包含列名即可，as可以理解为其别名
from `student`;

--对表内数据中的年龄求最大值且将其命名为max_age输出--
select max(`age`) as `avg_age`//直接使用max()包含列名即可，as可以理解为其别名
from `student`;

--对表内数据中的年龄求均值且将其命名为min_age输出--
select min(`age`) as `avg_age`//直接使用min()包含列名即可，as可以理解为其别名
from `student`;

--对表内数据中的年龄求均值且将其命名为sum_age输出--
select sum(`age`) as `avg_age`//直接使用sum()包含列名即可，as可以理解为其别名
from `student`;

--对函数进行四舍五入的操作--
selcect round(`column_name`, `decimals`) 
from `table_name`;
//column_name 为要舍入的字段
//decimals 规定要返回的小数位数
//ROUND() 函数始终返回一个值。当 decimals 为正数时，
//column_name 四舍五入为 decimals 所指定的小数位数。当 
//decimals 为负数时，column_name 则按 decimals 所指定的在小数点的左边四舍五入。
//特别的，如果 length 是负数且大于小数点前的数字个数，ROUND() 函数将返回 0

--举例--
select round(1.99)//round(x)返回一个四舍五入后的整数
// 返回值为2

select round(1.89,1)//返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0，结果将没有小数点或小数部分。
//返回值为1.9

select round(1.89,0)
//返回值为2

select round(-1.9,-2)
//返回值为0

--判断数值是否为空--
//ISNULL() 函数用于判断字段是否为 NULL，
//它只有一个参数 column_name 为列名，根据column_name 列中的字段是否为 NULL 值返回 0 或 1。
//如果 column_name 列中的某个字段是 NULL 则返回 1，不是则返回 0
select isnull(`age`)
from `student`
//返回1;//id为6的同学年龄为空

//IFNULL() 函数也用于判断字段是否为NULL，
//但是与 ISNULL() 不同的是它接收两个参数
//第一个参数 column_name 为列名
//第二个参数 value 相当于备用值。
//COALESCE(column_name, value) 函数也用于判断字段是否为NULL，其用法和 IFNULL() 相同。
select isnull(`age`,999)
from `student`
//返回999;//id为6的同学年龄为空

--统计个数count()函数--
select count(`age`)
from `student`
//返回7，因为有一行中age为null

select count(`id`)
from `student`
//返回8

select count(*)
from `student`
//返回8,只有整行都为null时才不会被统计进去，重复行也会被统计入内

时间获取和截取

--使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间--
//NOW() 可以用来返回当前日期和时间 格式：YYYY-MM-DD hh:mm:ss
//CURDATE() 可以用来返回当前日期 格式：YYYY-MM-DD
//CURTIME() 可以用来返回当前时间 格式：hh:mm:ss
//在使用 NOW() 和 CURTIME() 时，如果要精确的秒以后的时间的话，可以在（）中加数字，加多少，就表示精确到秒后多少位
//比如 NOW(3) 就是精确到毫秒，表示为： 2021-03-31 15:27:20.645
insert into `table`
values (NOW(3));//往表中插入现在的时间

--使用 DATE()、TIME() 函数提取日期和时间--
//DATE()用于截取年-月-日，TIME()用于截取时-分-秒
select date(now())as`year-month-day`,time(now())as`hour-min-second`
from table;

--使用 EXTRACT() 函数提取指定的时间信息--
SELECT EXTRACT(unit FROM date)
FROM `table`
//unit 参数是需要返回的时间部分，如 YEAR 、MONTH 、 DAY 、 HOUR 、MINUTE 、SECOND

--DATE_FORMAT() 用法--
//我们在 SQL 中使用 DATE_FORMAT() 方法来格式化输出 date/time。
//需要注意的是 DATE_FORMAT() 函数返回的是字符串格式。
select DATE_FORMAT(`created_at`,'%Y-%m-%d %H:%i:%s')as `DATE_FORMAT`
from `table`;//我们按照年-月-日 时：分：秒 返回一个时间

具体的关于date_format()的格式非常多，可以参考这个博客【MySQL】 DATE_FORMAT() 格式化时间

时间的修改和计算

--DATE_SUB() 函数--
//DATE_SUB() 函数是常用的时间函数之一，用于从日期减去指定的时间间隔。
SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name
//date 指代希望被操作的有效日期
​//expr 是希望添加的时间间隔
//type 是具体的数据类型
//（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

--DATE_SUB() 函数--
//DATE_SUB() 函数是常用的时间函数之一，用于从日期减去指定的时间间隔。
//它与 DATE_ADD() 函数具有相似的用法。

--使用时间函数 DATEDIFF() 和 TIMESTAMPDIFF() 计算日期差--
SELECT DATEDIFF(时间1,时间2) AS date_diff //默认datediff只能计算天数
from `table`

SELECT TIMESTAMPDIFF (类型,时间1,时间2) AS year_diff
//可以指定计算的日期类型，
//可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等
from `table`

约束

我们会在数据库中设立相应的规则，如果不符合规则，便会阻止其发生

--NOT NULL 非空约束--
alter table`student`
modify `name` string not null//添加非空约束
modify `name string` null;//取消非空约束

--UNIQUE 唯一约束--
alter table`student`
add unique(`name`)//为姓名添加唯一约束
drop index `name`//取消唯一约束

--PRIMARY KEY 主键约束--
//PRIMARY KEY 约束唯一标识数据库表中的每条记录
//PRIMARY KEY = UNIQUE + NOT NULL
//主键约束和唯一约束的区别
//1.NOT NULL UNIQUE 可以将表的一列或多列定义为唯一性属性，而 PRIMARY KEY 设为多列时，仅能保证多列之和是唯一的，具体到某一列可能会重复。
//2.PRIMARY KEY 可以与外键配合，从而形成主从表的关系，而 NOT NULL UNIQUE 则做不到这一点
alter table`student`
add primary key(`name`)//为姓名添加主键约束
//为多个列定义主键约束
constraint p-key-name primary key(`id`,`name`)//主键p-key-name由id和name组成
//如果使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）

--FOREIGN KEY 外键约束--
//一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 
//这里我们定义一个新的表-课程表`courses`，假设其中包含课程姓名、选课老师姓名、上课时间信息
create table `courses`
(
`c_id` int NOT NULL,
`c_name` string NOT NULL,
`t_id` int,
PRIMARY KEY (c_id),
FOREIGN KEY (t_id) REFERENCES `teacher`(id)
//我们将教师表中的老师id作为了课程表的外键约束)


--CHECK 检查约束--
//CHECK 约束用于限制列中的值的范围，评估插入或修改后的值。满足条件的值将会插入表中，否则将放弃插入操作。 
alter table `courses` 
add check (`student_count` > 0);
drop check(`student_count` > 0)
--DEFAULT 默认约束--
//默认值（Default）”的完整称呼是“默认值约束（Default Constraint）
alter table `student`
alter `class` set default '2-1'//默认学生均为2-1班级

联结

--联结--
//将两个及以上的表通过某种关联关系联结起来
//通常使用主键和外键联结

courses (课程表)

列名	类型	注释
id	int unsigned	主键
name	varchar	课程名称
student_count	int	学生总数
created_at	date	创建课程时间
teacher_id	int	讲师 id

teachers (教师表)

id	int	主键
name	varchar	讲师姓名
email	varchar	讲师邮箱
age	int	讲师年龄
country	varchar	讲师国籍

--联结实现--
`table1`.`commmon_field`=`table2`.`common_field`
//我们将教师表和课程表联结起来
`teachers`.`id`=`courses`.`teacher_id`
//其中teachers称为左表，课程表为右表，根据其在等号左右来定义
--联结语句--
--inner join:内联结如果表中有至少一个匹配，则返回行--

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;
//INNER JOIN 中 INNER 可以省略不写
--left join:左联结。即使右表中没有匹配，也从左表返回所有行--
--right join:右联结。即使左表中没有匹配，也从右表返回所有的行--
--full join:全连接。只要其中一个表存在匹配，则返回行--

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT | RIGHT | FULL  (OUTER) JOIN table2
        ON CONDITION;
        
--cross join:笛卡尔积。两个表数据一一对应，返回结果的行数等于两个表行数的乘积--
//隐式交叉连接：不需要使用 CROSS JOIN 关键字，只要在 SELECT 语句的 FROM 语句后将要进行交叉连接的表名列出即可，这种方式基本上可以被任意数据库系统支持。
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`,`table2`;
//显式交叉连接：与隐式交叉连接的区别就是它使用 CROSS JOIN 关键字，用法与 INNER JOIN 相似。
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
CROSS JOIN `table2`;