【自用复习笔记】SQL基础语法总结_sql自动计算怎么写-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_64410825/article/details/127513028

DDL 操作数据库和表

C(create):创建

创建数据仓库：create database 名称;
创建仓库，当不存在时才执行：create database if not exists 名称;

练习:
1.编写SQL ，在数据库"heiheihei" 不存在时创建它

create database if not exists heiheihei;

2.创建指定名称的数据仓库及编码格式：

create database 名称 character set 编码表;

*这里的编码表不可以是UTF-8

create database heihei2 character set gbk;

R(retrieve): 查询

查询已有的数据库：show database;
查询某个库的创建语句： show create database 数据库名称;

练习：写出SQL，查询库名为db01的创建语句

SHOW DATABASES;
SHOW CREATE DATABASE db01;

U(update):修改

修改数据库编码：alter database 数据库名称 character set 字符集名称;

练习：修改heihei2库的编码为 utf8mb4(UTF-8）

alter database heihei2 character set utf8mb4;
	ALTER DATABASE heihei2 CHARACTER SET utf8mb4;

D(delete):删除

删除数据库:drop database 数据库名称
当数据库存在时，删除它：drop database if exists 数据库名称;

练习：删除乱七八糟的库

drop database if exists heihei2;
DROP DATABASE IF EXISTS db01;

数据库的使用

选择数据库： use 数据库名称;
查看当前选择的数据库： select database();

USE db1;
SELECT DATABASE();

DQL 数据查询语言

完整语法

select 字段列表(多个字段名之间用,隔开) from 表名 where(条件） 条件列表 group by 分组字段 having 分组条件 order by 排序字段 排序方式 limit 分页限定

基础语法1 查询单个表格的单个字段数据

语法： select 字段名称 from 表名;

练习：查询员工表格(emp)的员工职位(job)信息：

select job from emp;

去除重复： 在查询语句字段列表的前面添加关键字 distinct

SELECT job FROM emp;
SELECT DISTINCT job FROM emp;

基础语法2 查询单个表格的多个字段数据

语法：select 字段1，字段2… from 表名;

练习：查询员工表格(emp)的员工姓名(ename)、职位(job)、月薪(sal)信息：

SELECT ename,job,sal FROM emp;

基础语法3 查询单个表格的所有字段信息

语法： select *from 表名;

练习：查询emp表格中的所有字段数据

SELECT * FROM emp;

基础语法4 查询列数据的运算

语法格式：对于查询预计的字段名称可以直接使用四则运算计算
如：
计算emp表格中的员工日薪，要求显示姓名和日薪

SELECT ename,sal/21.75 FROM emp;

– 计算emp表格中的员工年薪（16薪），显示姓名和年薪

SELECT ename,sal*16 FROM emp;

– 计算emp表格中的员工年薪（16个月的月薪sal+12个月的提成comm），显示姓名和年薪

SELECT ename,sal*16+comm*12 FROM emp;

出现问题，原因：null参与任何运算，结果都是null
解决方法：可以使用替换的方式将null替换为指定值
语法： ifnull(参数1，参数2):如果参数1为null,使用参数2替换，如果参数1不为null就用参数1

SELECT ename,sal*16+IFNULL(comm,0)*12 FROM emp;

给查询的字段或表名起别名

语法：在字段名 或 表格名的后面 加上 空格 别名即可

SELECT ename,sal*16+IFNULL(comm,0)*12 y_sal,job FROM emp;

条件查询

语法：

select 字段列表 from 表名 where 条件；
比较运算符：> <  =  >= <=   注意 不等于：<> != 等于=
条件链接符：and(&&)、or(||)、not(!)

例如：1.查询薪资大于1000的员工所有信息
2.查询部门编号deptno 等于10的员工所有信息
3.查询员工薪资在1200（含）-2500（含）之间的员工信息(可以使用between…and简写）
4.查询员工薪资等于800、1250、3000的员工信息(可以使用in简写）

SELECT * FROM emp WHERE sal>1000;
SELECT * FROM emp WHERE deptno =10;
-- 3.
SELECT * FROM emp WHERE sal>=1200 AND sal<=2500;
-- 3.简写
SELECT * FROM emp WHERE sal BETWEEN 1200 AND 2500;
-- 4.
SELECT * FROM emp WHERE sal=800 OR sal=1250 ||sal=3000;
-- 4.简写
SELECT * FROM emp WHERE sal IN (800,1250,3000);

对于null的查询

需要通过is null 或 is not null 完成
练习：查询提成comm等于null的员工信息

SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;

模糊查询查询关键字

语法：字段名 like 占位符;
占位符： _任意一个字符；% 0-n个字符
例如：
1.查询员工的姓名中包含克字的员工信息
2.查询员工姓名中以斯结尾的

SELECT * FROM emp WHERE ename LIKE "%克%";
SELECT * FROM emp WHERE ename LIKE "%斯";

排序查询：对查询的结果按照某列的顺序排列（升序或者降序）

单字段排序：

语法：select 字段列表 from 表名 order by 排序字段排序方式；

排序方式：默认升序，可以不写（ASC)  ;  降序 DESC

练习：查询员工信息，按照薪资sal倒叙排列

SELECT * FROM emp ORDER BY sal DESC;

排序查询：多字段

语法：select 字段列表 from 表名 order by 排序字段1 排序方式,排序字段2 排序方式;
练习：查询员工信息，按照薪资sal倒叙排列，薪资相同按照提成倒叙；

SELECT * FROM emp ORDER BY sal DESC, comm DESC;

聚合函数：

将一列所有数据作为一个整体进行运算，并得到一个结果

统计个数：count(列名)
统计最大值: max(列名)
统计最小值: min(列名)
求和：sum(列名)
求平均值：avg(列名)

例子：1.查询员工总数
2.计算评价薪资(sal)

SELECT COUNT(ename) FROM emp;
SELECT AVG(sal) FROM emp;

注意：

聚合函数不支持 SELECT ename,AVG(sal) FROM emp; 不能与其它普通列同时查询
聚合函数统计null值会忽略

SELECT COUNT(comm) FROM emp;

分组查询

不带条件语法：select 字段列表 from 表名 group by 分组字段；
带条件语法：上面的语法 having 分组条件;

案例：统计员工每个部门的员工数量
注意：
分组查询只能查询分组字段或聚合函数的结果

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

练习：统计员工每个部门的平均薪资

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

where 和 having区别：

where在分组前判断，没满足的不会参与分组；
having 在分组后判断，不满足having条件的不显示

例子：计算每个部门的员工数量，条件是：只统计薪资大于1500的员工
*/

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

分组条件：例子+满足条件的组员小于三个的不显示

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno HAVING COUNT(*)>=3;

练习：查询每个部门的平均薪资，条件是：平均薪资低于2000的不显示；

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>=2000;

分页查询（是方言）

语法：select 字段列表 from 表名 limit 开始的索引，每页查询行数;
常见计算开始索引的公式：（页码-1）*每页显示的条数
练习：查询emp表，要求分页查询，每页4名员工；写出前三页

SELECT * FROM emp LIMIT 0,4;
SELECT * FROM emp LIMIT 4,4;
SELECT * FROM emp LIMIT 8,4;

DDL 数据定义语句

C(create):创建

创建格式

create table 表名(
				列名1 数据类型，
				列名2 数据类型，
				...
				列名n 数据类型
				);

常用数据类型：

int(长度) 整形数形，默认长11位
例：age int (3),
double(长度，小数点后位数）浮点值属性
例：定义一个小数点后2位，小数点前3位的数
x double(5,2)
date 日期，显示格式 “yyyy-MM-dd”
例： “2022-04-18”
datetime 日期时间，显示格式 “yyyy-MM-dd HH:mm:ss”
例： “2022-04-18 14:06:00”
timestamp 时间戳 13位数字，从1970年1月1日开始
例： “2022-04-18 14:06:00”
varchar(长度) 变长字符串更常用
例： name varchar(20)
char(长度) 定长字符串
例： name char(20)

案例：创建学生表，包含：学号id，姓名name，成绩score，入学日期indate

*/
CREATE TABLE student(
	id INT(10),
	NAME VARCHAR(32),
	score DOUBLE (5,2),
	indate DATE
);

回顾：查询入职日期大于等于1987年1月1日的员工（emp)

SELECT * FROM emp WHERE HIREDATE >= "1987-1-1";

练习：创建图书表book，包含图书编号id，图书名称name，图书简介

desecription
CREATE TABLE book(
	id INT(10),
	NAME VARCHAR(128),
	dercription VARCHAR(1024)
);

R（retrieve）查询

查询某个数据库中所有的表名称；
show tables;
查询表结构
desc 表名;

SHOW TABLES;
DESC book;

U(update)修改

1.修改表名: alter table 表名 rename to 新表名;
2.修改表字符编码表： alter table 表名 character set 字符集名称;
3.修改表结构：添加一列： alter table 表名 add 列名数据类型;
4.修改表结构：删除一列： alter table 表名 drop 列名;
5.修改表结构：修改一列： alter table表名 change 原列名新列名数据类型;
alter table表名 modify 原列名数据类型;

练习：修改book为book2

ALTER TABLE book RENAME TO book2;

练习：修改book2字符名称

ALTER TABLE book2 CHARACTER SET gbk;

练习：book2 添加一列 haha vaechar(32)

ALTER TABLE book2 ADD haha VARCHAR(32);
DESC book2;

练习：删掉haha

ALTER TABLE book2 DROP haha;

把name 改掉

ALTER TABLE book2 CHANGE NAME name2 INT;
ALTER TABLE book2 MODIFY name2  VARCHAR(32);

D （delete）删除

删除某表: drop table 表名；
当表存在时，删除某表:drop table if exists 表名;

DROP TABLE IF EXISTS book2;

字段默认值设置

1.创建表格时，设置默认值；

create table 表名(
			列名1 数据类型 default 默认值，
			列名1 数据类型 default 默认值，
			...
			列名n 数据类型 default 默认值
		);

2.修改字段信息时，设置字段默认值
添加列：alter table 表名 add 列名数据类型 default 默认值;
修改列：alter table表名 change 原列名新列名数据类型 default 默认值;
alter table表名 modify 原列名数据类型 default 默认值;

练习：创建person表，包含字段：id,name,age,sex ;
默认值：id 0;name 韩梅梅； age 18 ；sex无默认值

CREATE TABLE person(
	id INT(11) DEFAULT 0,
	NAME VARCHAR(32) DEFAULT "韩梅梅",
	age INT DEFAULT 18,
	sex VARCHAR(32)
);
DESC person;

DML 数据操作语句（重点）

添加数据

语法：
insert into 表名 (列名1，列名2…列名n) values (值1，值2…值n);
向所有列添加：insert into 表名 values (值1，值2…值n);

-- 练习：向person中插入：id=1，张三，19，男

INSERT INTO person VALUES(1,"张三",19,"男");
INSERT INTO person (id,NAME,age,sex) VALUES(1,"张三",19,"男");
SELECT * FROM person;

删除数据

语法：delete from 表名 where 条件;

练习：删除年龄小于20的所有person数据

DELETE FROM person WHERE age<20;

注意：

不添加where条件就是删除所有数据；
删除所有数据建议使用： truncate table 表名 ; 此方法是删除表格再创建新表，比较高效

修改数据

语法： update 表名 set 列名1=值1，列名2=值2，… 列名n= 值n where 条件；

练习：修改id为1的person数据，修改name=李四，age=16

UPDATE person SET NAME="李四",age =16 WHERE id=1;

注意：

不加where条件就是修改所有数据

多表查询

内连接查询

隐式内连接

语法 select 列名列表 from 表名列表 where…

SELECT * FROM emp,dept;

上述语法会产生笛卡尔积，产生56行数据，实际查询部门编号和员工匹配要加限制条件

SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno;

显式内连接

语法
select 字段列表 from 表名1 inner join 表名2 on 条件

SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

外连接查询

语法
左外连接：基表在左边，从表（参考表）在右边
select 字段列表 from 基表表名 left outer join 参考表名 on 条件;
右外连接：基表在右边
select 字段列表 from 参考表名 right outer join 基表表名 on 条件;

SELECT * FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno;
SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno;

注意：表的顺序

子查询

子查询：在一条查询语句中嵌套另一个查询语句
1.子查询结果单行单列：可以应用于查询条件（例子1）
2.子查询结果多行单列：可以使用in 应用于查询条件（例子2）
3.子查询结果多行多列：可以将查询结果当作一个虚拟报表进行查询（例子3）

例子1：

查询emp表格中的最小sal 
查询薪资最小的人

SELECT MIN(sal) FROM emp ;
SELECT * FROM emp WHERE sal=800;
-- 使用子查询：
SELECT * FROM emp WHERE sal =(SELECT MIN(sal) FROM emp);

例子2

查询dept表的部门编号字段deptno，找到部门名称dname=会计 或销售 的编号
根据会计或销售部门编号，查询员工信息

SELECT deptno FROM dept WHERE dname="会计" OR dname = "销售";
SELECT * FROM emp WHERE deptno IN(10,30);
-- 使用子查询
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname="会计" OR dname = "销售");

例子3

合并表emp 和dept 中的销售信息

SELECT * FROM emp WHERE deptno=30;
SELECT * FROM dept ,(SELECT * FROM emp WHERE deptno=30) emp30 WHERE dept.deptno = emp30.deptno;

视图

是select语句查询的结果；
作用：可以当作表格进行数据的增删改查
注意：对视图内容进行修改时，会影响到原表格
（原因：视图不占用空间，每次使用视图其实是在间接操作原表格的数据）

语法

创建视图：

create view 自定义名称 as 查询语句；
replace view 自定义名称 as 查询语句；

删除视图：

drop view if exists 视图名称;
drop view 视图名称;

修改视图：会影响原表格

SELECT emp.empno,emp.ename,emp.sal,dept.dname,dept.loc FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 创建视图
CREATE VIEW myemp AS SELECT emp.empno,emp.ename,emp.sal,dept.dname,dept.loc FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 使用视图
SELECT * FROM myemp ORDER BY sal;
UPDATE myemp SET sal =801 WHERE sal =800;
UPDATE myemp SET sal =800 WHERE sal =801;
-- 删除视图
DROP VIEW myemp;

约束

约束：为了保证表中数据的正确、有效、完整，需要对表中的数据进行限定。会对性能造成影响

约束的五种类型

1.主键约束：保证唯一且不为空，相当于非空约束+唯一约束，关键词primary key 一个表只有一个主键
2.非空约束：值不能为null ,关键字：not null
3.唯一约束：值不能重复（非空情况下），关键字：unique
4.检查约束（旨在部分数据库有，mysql没有 Oracle有）
5.外键约束：完整性约束

案例

2.非空约束例子

2.1创建表格时，给表格的某个字段添加非空约束；

create table 表名(
    		字段名 数据类型 not null,
    		...
    	);

创建book 包含id\name id不为空

CREATE TABLE book(
	id INT NOT NULL ,
	NAME VARCHAR(32)
	);
ALTER TABLE book MODIFY NAME VARCHAR(32) NOT NULL; 
INSERT INTO book VALUES(1,"金苹果")
ALTER TABLE book MODIFY NAME VARCHAR(32) ; 
INSERT INTO book VALUES(1,NULL)

2.2 通过修改字段添加非空约束

alter table 表名 modify 列名 数据类型 not null;

2.3 删除字段的约束

alter table 表名 modify 列名 数据类型;

3.唯一约束例子

3.1创建表格时，添加唯一约束；

create table 表名(
字段名数据类型 unique,
…
);

3.2 通过修改字段添加唯一约束

alter table 表名 modify 列名 数据类型 unique;

3.3 删除字段的唯一约束（这和非空不同）

alter table 表名 drop index 字段名;

  DROP TABLE book;
    CREATE TABLE book(
    	id INT UNIQUE,
    	NAME VARCHAR(32)
    	);
    INSERT INTO book VALUES(1,"金苹果");
    INSERT INTO book VALUES(2,"银苹果");
    ALTER TABLE book MODIFY NAME VARCHAR(32) UNIQUE; 
    ALTER TABLE book DROP INDEX NAME;
    INSERT INTO book VALUES(4,"苹果");

1.主键约束例子

1.1创建表格时，添加主键约束；

create table 表名(
    		字段名 数据类型 primary key,
    		...
    	);

1.2 通过修改字段添加主键约束

alter table 表名 modify 列名 数据类型 primary key;

1.3 删除字段的主键约束（这和非空不同）

alter table 表名 drop primary key;

1.4 主键约束+自动增长
自动增长：如果某一列是数值，可以使用 auto_increment 实现自动增长填充

CREATE TABLE book(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	NAME VARCHAR(32)
    	);
    INSERT INTO book (NAME) VALUES ("张三");
    INSERT INTO book (NAME) VALUES ("李四");

5.外键约束

一个表可以有多个外键

索引

数据库通过消耗大量的空间来达到加速查询的目的的技术
语法

创建索引;create index 名称 on 表名（字段名）；
	索引命名规范：表名_字段名_index
	删除索引：drop index 名称；

事务

开始事务： start transaction;
事务开启后的DML操作，在提交事务之前不会对数据源产生影响；如果回退则数据还原到事务开启前的状态
提交事务： commit;
对事务中的所有操作进行确认生效
回滚事务： rollback;
对事务中的所有操作进行撤销回退

DCL 管理用户、授权

DBA数据库管理员

管理用户

1.1 添加用户：
语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
1.2删除用户：
语法：DROP USER '用户名'@'主机名';
1.3查询用户：

--1.切换到mysql数据库
USE myql;
 -- 2. 查询user表
SELECT * FROM USER;

* 通配符： % 表示可以在任意主机使用用户登录数据库

权限管理：

1.查询权限：

 SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

授予权限：

 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

-- 给张三用户授予所有权限，在任意数据库任意表上

GRANT ALL ON *.* TO 'zhangsan'@'localhost';

3.撤销权限：

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
  REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

【自用复习笔记】SQL基础语法总结

DDL 操作数据库和表

C(create):创建

R(retrieve): 查询

U(update):修改

D(delete):删除

数据库的使用

DQL 数据查询语言

完整语法

基础语法1 查询单个表格的单个字段数据

基础语法2 查询单个表格的多个字段数据

基础语法3 查询单个表格的所有字段信息

基础语法4 查询列数据的运算

给查询的字段或表名起别名

条件查询

对于null的查询

模糊查询 查询关键字

排序查询 ：对查询的结果按照某列的顺序排列（升序或者降序）

单字段排序：

排序查询 ：多字段

聚合函数：

分组查询

where 和 having区别：

分页查询 （是方言）

DDL 数据定义语句

C(create):创建

创建格式

常用数据类型：

R（retrieve）查询

U(update)修改

D （delete）删除

字段默认值设置

DML 数据操作语句（重点）

添加数据

删除数据

修改数据

多表查询

内连接查询

隐式内连接

显式内连接

外连接查询

子查询

视图

语法

约束

约束的五种类型

案例

2.非空约束例子

3.唯一约束例子

1.主键约束例子

索引

事务

DCL 管理用户、授权

DBA数据库管理员

管理用户

权限管理：

模糊查询查询关键字

排序查询：对查询的结果按照某列的顺序排列（升序或者降序）

排序查询：多字段

分页查询（是方言）