SQL基础语句（一看就会版）
2020-06-27 本文已影响0人
CRUD_科科
DDL Data Definition Language
数据定义语言，在定义或改变表结构、数据类型、表之间的链接等等使用，一般使用视图工具创建导出
-- 关键字大写
-- 创建表 如果表明是关键字要加反引号
CREATE TABLE `student`
-- PRIMARY KEY AUTO_INCREMENT 创建时设定主键
(
id INT(11) NOT NULL,
name VARCHAR(64) NOT NULL,
age INT(11) DEFAULT NULL,
city VARCHAR(64) DEFAULT '北京'
)
SELECT * FROM student;
CREATE TABLE score
(
student_id INT(11) NOT NULL,
grade FLOAT NOT NULL
)
-- 增加/修改/删除字段（增加列）
ALTER TABLE student ADD COLUMN idcard VARCHAR(64) NULL;
ALTER TABLE student MODIFY city VARCHAR(128) DEFAULT '上海';
ALTER TABLE student DROP idcard;
-- 如何增加约束
-- 为表增加主键
ALTER TABLE student ADD PRIMARY KEY (id);
-- 增加唯一约束
ALTER TABLE student ADD UNIQUE INDEX uq_student_idcard(idcard);
-- 增加默认约束
ALTER TABLE student MODIFY city VARCHAR(64) DEFAULT '山西';
-- 主外键
ALTER TABLE score ADD constraint fk_score_student_id FOREIGN KEY (student_id) REFERENCES studb.student(id);
DML Data Manipulate Language
数据库操纵语言，要熟练手写
-- 插入数据
-- 1.每次插入一行数据，不能只插入一部分数据，插入的数据是否有效将按照整行的完整性来要求检验
-- 2.每个数据值的数据类型、精度、位数必须与要应列明精确匹配
-- 3.不能为标识符指定值
-- 4.如果某字段设置为不能为空，则必须插入数据
-- 5.插入数据是必须符合检查性约束的要求
-- 6.有缺省值的列，可以使用DEFAULT关键字来代替实际插入的值
INSERT INTO student (name,age,city,idcard)
VALUES ('张三',18,'北京','100');
SELECT * from student;
-- 更新数据
-- 可以一次更新多列，用逗号隔开
-- 可以指定更新的条件，如果有多个条件可以用AND OR NOT来修饰
-- 如果行的数据被更新过了，则不再受影响
UPDATE student SET age=28,city='上海' WHERE id=7 AND name='张三';
-- 删除
-- 删除是整行删除，不需要提供列明
-- 如果要删除的表是主表的话，要先删除子表
DELETE FROM student WHERE id=7;
-- TRUNCATE 截断 清空表
-- TRUNCATE 会重置标识种子（清空断号保留）DELETE会保留断号（id）
-- TRUNCATE不会写入日志，DELETE会写入日志，可以恢复
TRUNCATE TABLE student;
DELETE from student;
-- 查询
-- 查询在北京的同学信息id和name 并且按照ID正序排序ASC;(降序DESC)
SELECT id,name FROM student WHERE city='北京' ORDER BY id ASC;
-- 别名 列明+AS+别名 AS可以省略
SELECT id,name,city AS home FROM student WHERE city='北京' ORDER BY id DESC;
-- 查询空列 IS NULL不能用=
SELECT * FROM student WHERE level IS NULL;
-- 常量列 (固定没有的列)
SELECT id,name,'中国' AS country FROM student WHERE city='北京' ORDER BY id ASC;
-- 分页 LIMIT 索引(index),条数；如果只写一个值，代表查询几条
SELECT * from student
LIMIT 2,3
-- 查询同学都来自哪些不同的城市 DISTINCT 相同的只出现一次
SELECT DISTINCT city
FROM student;
-- 加法 +只能用来+数字 字符串相连 CONCAT(str1,str2,...)
SELECT 1+1;
SELECT 1+'1';
SELECT 1+'a';
SELECT 'a'+'b';
SELECT CONCAT('a','b');
准备数据
ALTER TABLE `student`
ADD COLUMN `province` varchar(50) NULL AFTER `city`,
ADD COLUMN `birthday` date NULL AFTER `province`,
ADD COLUMN `gender` int(11) NULL AFTER `birthday`,
ADD COLUMN `email` varchar(50) NULL AFTER `gender`;
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('1', '郭靖', '1', '1', '济南', '山东省', '1982-09-03', 1, '1@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('2', '黄蓉', '2', '2', '济南', '山东省', '1982-09-03', 0, '2@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('3', '杨过', '3', '3', '终南山', '陕西省', '1979-09-03', 1, '3@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('4', '小龙女', '4', '4', '终南山', '陕西省', '1970-09-03', 0, '4@qq.com');
INSERT INTO `student`(id,name,age,idcard,city,province,birthday,gender,email) VALUES ('5', '欧阳锋', '5', '5', '白驼山', '新疆', '1989-09-09', 1, '5@qq.com');
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `score` VALUES ('1', '1', '100');
INSERT INTO `score` VALUES ('1', '2', '90');
INSERT INTO `score` VALUES ('1', '3', '70');
INSERT INTO `score` VALUES ('2', '1', '100');
INSERT INTO `score` VALUES ('2', '2', '90');
INSERT INTO `score` VALUES ('2', '3', '80');
INSERT INTO `score` VALUES ('3', '1', '100');
INSERT INTO `score` VALUES ('3', '2', '90');
INSERT INTO `score` VALUES ('3', '3', '80');
-- 查询
-- 查询所有山东省的同学
SELECT * FROM student WHERE province='山东省';
-- 查询所有山东省的男同学
SELECT * FROM student WHERE province='山东省' AND gender=1;
-- 查询没有填写邮箱的同学姓名和身份证号
SELECT name,idcard FROM student WHERE email IS NULL;
-- 把学生的成绩升序排列
SELECT * FROM score ORDER BY grade ASC;
-- 多列排序
-- 课程编号升序 成绩降序
SELECT * FROM score ORDER BY course_id ASC,grade DESC;
-- 模糊查询 LIKE
-- %放前面代表不管前面有几个，以杨结尾，放后面代表不管后面有几个以杨开头
-- _代表一个字符
SELECT * FROM student WHERE `name` LIKE '杨%' OR `name` LIKE '郭_';
SELECT * FROM student WHERE `name` LIKE '杨_';
字符串函数
-- 字符串函数
-- CONCAT(str1,str2,...)
-- CONCAT_WS(separator,str1,str2,...) 第一个是分隔符,相当于js中join('-')
SELECT CONCAT(province,city) FROM `student`;
SELECT CONCAT_WS('-',province,city) FROM `student`;
-- FORMAT(X,D) 数字格式化
-- FORMAT(X,D) 数字格式化 保留两位小数，前面位数用逗号隔开
SELECT FORMAT(1000000,2);
-- LOWER(str) 转小写字母
SELECT LOWER('Yang');
-- UPPER(str) 转大写字母
SELECT UPPER('Yang');
-- LEFT(str,len) 取左边开始的len个字符
SELECT LEFT('YangLei',4);
-- RIGHT(str,len) 反之
SELECT RIGHT('YangLei',3);
-- LENGTH(str) 去字符长度
SELECT LENGTH('YangLei');
-- SUBSTR 相当于js中substring
-- 1.SUBSTR(str FROM pos FOR len)从第几位开始截取几个
SELECT SUBSTR('YangLei' FROM 5 FOR 3);
-- 2.SUBSTR(str FROM pos)从第几位开始截取到末尾
SELECT SUBSTR('YangLei' FROM 5);
-- 3. SUBSTR(str,pos) 从第几位开始截取
SELECT SUBSTR('YangLei',4);
-- 4. SUBSTR(str,pos,len)从第几位开始截取几个
SELECT SUBSTR('YangLei',5,3);
-- indexof
SELECT INSTR('YangLei','Lei');
-- 姓名首字母大写，其他字母小写，并且用_拼接
SELECT CONCAT_WS('_',UPPER(SUBSTRING('bOB',1,1)),LOWER(SUBSTR('bOB',2)));
-- 如何去除空格
SELECT TRIM(' bob ');
SELECT LTRIM(' bob ');
SELECT RTRIM(' bob ');
-- padding补齐位数
SELECT LPAD(1,2,0);
SELECT RPAD(11,2,0);
-- 替换
SELECT REPLACE('bob','b','o'),REPLACE('bob','o','b');
数学函数
-- 数学函数
-- CEIL(X) 向上取整
SELECT CEIL(12.23123);
-- FLOOR(X) 向下取整
SELECT FLOOR(2.333);
-- MOD 取模
SELECT MOD(5,2);
-- POWER(X,Y) 幂运算
SELECT POWER(2,8);
-- ROUND(X) 四舍五入
SELECT ROUND(1.4);
SELECT ROUND(1.4454,2);
-- `TRUNCATE`(X,D) 数字截取
SELECT TRUNCATE(1.666,1);
日期函数
-- 日期函数
-- NOW() 当前日期和时间
SELECT NOW();
-- CURDATE()当前日期
SELECT CURDATE();
-- CURTIME()当前时间
SELECT CURTIME();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DATE(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 字符串转为日期
SELECT STR_TO_DATE('05-01-2020','%m-%d-%y');
-- DATE_ADD(date,INTERVAL expr unit) 日期的变化
SELECT DATE_ADD(NOW(),INTERVAL 365 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR);
-- DATEDIFF(expr1,expr2) 计算时间差
SELECT DATEDIFF('2019-1-1',NOW());
-- DATE_FORMAT(date,format) 日期格式化
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
其他函数
-- 其他函数
SELECT CONNECTION_ID();
SELECT DATABASE();
SELECT VERSION();
select LAST_INSERT_ID();
SELECT USER();
SELECT MD5('123456');//摘要算法
SELECT PASSWORD('123456');//修改当前用户的密码
SELECT User,Password from mysql.user;
流程控制函数
-- if 如果0>1返回1，否则返回0
SELECT IF(0>1,1,0);
-- CASE
CASE 要判断的字段或表达式
WHEN 常量1 THEN 返回的值
WHEN 常量2 THEN 返回的值
ELSE 返回的值
END
SELECT
CASE grade
WHEN 100 THEN '满分'
WHEN 90 THEN '良'
ELSE '一般'
END
FROM score ORDER BY course_id ASC;
SELECT
CASE
WHEN grade>90 THEN '满分'
WHEN grade>80 THEN '良'
ELSE '一般'
END
FROM score ORDER BY course_id ASC;
自定义函数
-- 自定义函数是对MySQL的扩展，使用方式和内置函数相同
-- 函数必须要有参数和返回值
-- 函数可以接收任意类型的值，也可以接收这些类型的参数
-- 函数体由合法的SQL语句组成
-- 函数体可以是简单的SELECT语句或INSERT语句，如果是复合结构要用BEGIN...END
-- 函数体也可以包含声明，循环和流程控制
-- 返回值只能有一个
-- 函数名要跟着()表示参数
CREATE FUNCTION ZNOW() RETURNS VARCHAR(128)
RETURN DATE_FORMAT(NOW(),'%Y年-%m月-%d日 %h时:%i分:%s秒');
SELECT ZNOW();
CREATE FUNCTION ZNOW2(date VARCHAR(128)) RETURNS VARCHAR(128)
RETURN DATE_FORMAT(date,'%Y年-%m月-%d日 %h时:%i分:%s秒');
SELECT ZNOW2('1994-01-30');
-- 函数体可能不止一行
CREATE FUNCTION ADD_USER(name VARCHAR(64)) RETURNS INT
BEGIN
INSERT INTO student (name) VALUES(name);
RETURN LAST_INSERT_ID();
END
模糊查询
-- 通配符
-- 代替一个或多个真正的字符，与LIKE 关键字一起使用
-- _表示一个任意字符
-- %表示任意长度的字符串
-- BETWEEN AND
-- 查询某一列在指定的规范内的记录，包括两个边界
SELECT * FROM score WHERE grade BETWEEN 80 AND 100;
-- in
-- 查询某一列中的值在列出的内容列表中
select * from student where city in ('北京','济南','西安')
聚合函数
对一组值进行计算，并返回计算后的值，一般用来统计数据
-- SUM(expr)
-- 累加所有行的值
SELECT SUM(grade) FROM score;
SELECT SUM(grade) FROM score WHERE student_id=1;
-- AVG([DISTINCT] expr)
-- 计算所有行的平均值
SELECT AVG(grade) FROM score;
-- MAX、MIN
select MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;
-- COUNT计算值 此列不为null的记录数
-- * 就是所有列不为null记录数
select COUNT(*) from student;
select COUNT(1) from student;
select COUNT(name) from student;
select COUNT(NULL) from student;
分组
-- 单列分组
-- 查询每个学生的平均分
SELECT student_id,AVG(grade)
FROM score
GROUP BY student_id;
-- 统计每门课程的最高分，并按分数从高到低排序
SELECT course_id,MAX(grade)
FROM score
GROUP BY course_id
ORDER BY MAX(grade) DESC;
-- 多列分组
-- 统计各省男女的总人数
SELECT province,gender,COUNT(*)
FROM student
GROUP BY province,gender
-- 分组的筛选
-- 查看人数大于1的省份
SELECT province,COUNT(*)
FROM student
GROUP BY province
HAVING COUNT(*)>1;
-- 查询所有分数小于90的次数>1次的同学
SELECT student_id,COUNT(*) 没达到优的次数
FROM score
WHERE grade<90
GROUP BY student_id
HAVING COUNT(*)>1;
子查询
- 子查询就是指出现在其它SQL语句中的SELECT语句,必须始终出现在圆括号中
- 子查询可以包含多个关键字或条件
- 子查询的外层查询可以是: SELECT、INSERT、UPDATE、SET等
- 子查询可以返回常量、一行数据、一列数据或其它子查询
-- 查询年龄大于平均年龄的学生
SELECT * FROM student
WHERE age > (SELECT AVG(age) FROM student);
-- ANY SOME ALL
-- ANY 任何一个
-- SOME 某些
-- ALL 全部
-- 年龄大于陕西省任何一个学生年龄的学生
SELECT *
FROM student
WHERE age > ANY (SELECT age FROM student WHERE province='陕西省');
-- 年龄大于陕西省所有学生年龄的学生
SELECT *
FROM student
WHERE age > ALL (SELECT age FROM student WHERE province='陕西省');
-- IN NOT IN
-- 查询有考试成绩的学生
SELECT *
FROM student
WHERE id IN (SELECT student_id FROM score);
-- EXISTS NOTEXISTS
SELECT *
FROM student
WHERE EXISTS (SELECT * FROM score WHERE score.student_id = student.id);
表连接
-- 内部连接
-- 查询所有学生成绩
SELECT * FROM student INNER JOIN score ON student.id=score.student_id;
-- 左外连接,基于内连接 如果左边表没有对应字段的值，会自动补null
SELECT * FROM student LEFT JOIN score ON student.id=score.student_id;
-- 右外连接 基于内连接
SELECT * FROM student RIGHT JOIN score ON student.id=score.student_id;
-- 外连接 mysql不支持
SELECT * FROM student OUTER JOIN score ON student.id=score.student_id;
-- 多表连接
-- 查看学生姓名，课程名，成绩
SELECT student.`name`, score.grade, course.course
FROM student,score,course
WHERE student.id=score.student_id AND score.course_id=course.id;
SELECT student.name,score.grade,course.course
FROM student INNER JOIN score ON student.id=score.student_id
INNER JOIN course ON course.id=score.course_id;
-- 无限分类【自身连接】
-- 创建商品分类表 parent_id为0代表顶级分类
CREATE TABLE category(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(64),
parent_id INT
)
INSERT INTO category(id,name,parent_id)
VALUES (1,'数码产品',0),(2,'服装',0),(3,'食品',0),(4,'iPad',1),(5,'李宁',2),(6,'康师傅',3);
-- 查询所有顶级分类下的类别数量 （处理方式：同一个表起别名c1 c2）
SELECT c1.id,c1.name,COUNT(*) 二级分类
FROM category c1 INNER JOIN category c2 ON c1.id=c2.parent_id
WHERE c1.parent_id = 0
GROUP BY c1.id;
-- 把所有父id变为名称
SELECT c1.id,c1.name,c2.name 父分类名称 FROM
category c1 INNER JOIN category c2 ON c2.id=c1.parent_id;
-- 删除重复记录(面试常问)
-- 子查询找要删除的ID
SELECT * FROM category c1 LEFT JOIN
(SELECT id,`name` FROM category GROUP BY name HAVING COUNT(*)>1) c2
ON c1.`name`=c2.`name`
WHERE c1.id!=c2.id;
-- 通过IN找要删除的ID
-- 用IN NOT IN实现
SELECT * FROM category c1
WHERE c1.name IN
(SELECT name FROM category GROUP BY name HAVING COUNT(*)>1)
AND c1.id NOT IN
(SELECT MIN(id) FROM category GROUP BY name HAVING COUNT(*)>1)
-- 真正删除
DELETE FROM category
WHERE name IN
(SELECT name FROM (SELECT name FROM category GROUP BY name HAVING COUNT(*)>1) c1)
AND id NOT IN
(SELECT id FROM (SELECT MIN(id) id FROM category GROUP BY name HAVING COUNT(*)>1) c2)
-- 多表更新
CREATE TABLE province(id int PRIMARY KEY AUTO_INCREMENT,name varchar(50))
INSERT INTO province(name) SELECT DISTINCT province FROM student;
SELECT * FROM province;
-- 更新student省份把省份变为id
UPDATE student INNER JOIN province ON student.province=province.name
SET student.province=province.id;
ALTER TABLE student CHANGE COLUMN `province` `province_id` INT(50) NULL DEFAULT NULL AFTER city;
