设为首页收藏本站
开启辅助访问

史上最全SQL基础知识总结(理论+举例)

 您所在的位置：网站首页 › sql数据类型表 › 史上最全SQL基础知识总结(理论+举例)

史上最全SQL基础知识总结(理论+举例)

2023-07-27 03:56| 来源: 网络整理| 查看: 265

SQL基础知识 一、SQL 概述1、什么是 SQL2、语法要求 二、SQL 分类三、DDL（Data Definition Language）：数据定义语言基本操作1.操作数据库2.数据类型3.操作表 四、DML（Data Manipulation Language）：数据操作语言1.插入数据2.修改数据3.删除数据 五、DCL（Data Control Language）：数据控制语言1.创建用户2.给用户授权3.撤销授权4.查看用户权限5.删除用户6.修改用户密码（以root身份） 六、DQL（Data Query Language）：数据查询语言创建数据库：Info，在 Info 下创建如下表：1.基础查询2.条件查询3.模糊查询4.字段控制查询5.排序6.聚合函数7.分组查询8.LIMIT：用来限定查询结果的起始行，以及总行数。9.多表连接查询： 表连接分为内连接和外连接。内连接外连接分为左连接和右连接

一、SQL 概述 1、什么是 SQL

==SQL（Structured Query Language）==是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server 等。SQL 标准（ANSI/ISO）有：

SQL-92：1992 年发布的 SQL 语言标准；SQL:1999：1999 年发布的 SQL 语言标签；SQL:2003：2003 年发布的 SQL 语言标签；

这些标准就与 JDK 的版本一样，在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

虽然 SQL 可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我们可以称之为“方言”。例如 MySQL 中的 LIMIT 语句就是 MySQL 独有的方言，其它数据库都不支持！当然，Oracle 或 SQL Server 都有自己的方言。

2、语法要求 SQL 语句可以单行或多行书写，以分号结尾；可以用空格和缩进来来增强语句的可读性；关键字不区别大小写，建议使用大写； 二、SQL 分类 DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；DQL（Data Query Language）：数据查询语言，用来查询记录（数据） 三、DDL（Data Definition Language）：数据定义语言 基本操作

查看所有数据库：show databases；

切换数据库：use mydb1，切换到 mydb1 数据库；

1.操作数据库 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

例如：CREATE DATABASE mydb1，创建一个名为 mydb1 的数据库。如果这个数据已经存在，那么会报错。 例如 CREATE DATABASE IF NOT EXISTSmydb1，在名为 mydb1 的数据库不存在时创建该库，这样可以避免报错。

删除数据库：DROP DATABASE [IF EXISTS] mydb1；

例如：DROP DATABASE mydb1，删除名为 mydb1 的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算 mydb1不存在，也不会的报错。

修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

修改数据库 mydb1 的编码为 utf8。注意，在 MySQL 中所有的 UTF-8 编码都 不能使用中间的“-”，即 UTF-8 要书写为 UTF8。

2.数据类型

MySQL 与 Java、C 一样，也有数据类型MySQL 中数据类型主要应用在列上。 常用类型：

int：整型double：浮点型，例如 double(5,2)表示最多 5 位，其中必须有 2 位小数，即最大值为 999.99；decimal：泛型型，在表单线方面使用该类型，因为不会出现精度缺失问题；char：固定长度字符串类型；(当输入的字符不够长度时会补空格)varchar：固定长度字符串类型；text：字符串类型；blob：字节类型；date：日期类型，格式为：yyyy-MM-dd；time：时间类型，格式为：hh:mm:sstimestamp：时间戳类型； 3.操作表

创建表

CREATE TABLE 表名( 列名 列类型, 列名 列类型, ...... );

例如，创建stu表

CREATE TABLE stu( sid CHAR(6), sname VARCHAR(20), age INT, gender VARCHAR(10) );

查看表的结构

DESC 表名；

删除表

DROP TABLE 表名；

修改表

添加列：给 stu 表添加 classname 列 ALTER TABLE stu ADD (classname varchar(100));

修改列的数据类型：修改 stu 表的 gender 列类型为 CHAR(2) ALTER TABLE stu MODIFY gender CHAR(2);

修改列名：修改 stu 表的 gender 列名为 sex ALTER TABLE stu change gender sex CHAR(2);

删除列：删除 stu 表的 classname 列 ALTER TABLE stu DROP classname;

修改表名称：修改 stu 表名称为 student ALTER TABLE stu RENAME TO student;

四、DML（Data Manipulation Language）：数据操作语言 1.插入数据

语法 1： INSERT INTO 表名(列名 1,列名 2, …) ;

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male'); INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');

语法 2： INSERT INTO 表名 VALUES(值 1,值 2,…); 因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

注意：所有字符串数据必须使用单引用！

2.修改数据

语法： UPDATE 表名 SET 列名 1=值 1, … 列名 n=值 n [WHERE 条件];

UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’; UPDATE stu SET sname=’liSi’, age=’20’WHERE age>50 AND gender=’male’; UPDATE stu SET sname=’wangWu’, age=’30’WHERE age>60 OR gender=’female’; UPDATE stu SET gender=’female’WHERE gender IS NULL UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’; 3.删除数据

语法 1： DELETE FROM 表名 [WHERE 条件];

DELETE FROM stu WHERE sid=’s_1001’003B; DELETE FROM stu WHERE sname=’chenQi’ OR age > 30; DELETE FROM stu;

语法 2： TRUNCATE TABLE 表名;

TRUNCATE TABLE stu;

两者之间的区别： 虽然 TRUNCATE 和 DELETE 都可以删除表的所有记录，但有原理不同。DELETE的效率没有 TRUNCATE 高！ TRUNCATE 其实属性 DDL 语句，因为它是先 DROP TABLE，再 CREATE TABLE。 而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

五、DCL（Data Control Language）：数据控制语言 1.创建用户

语法： CREATE USER ‘用户名’@地址 IDENTIFIED BY '密码';

CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’; CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’; 2.给用户授权

语法： GRANT 权限 1, … , 权限 n ON 数据库.* TO ‘用户名’@地址;

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost; GRANT ALL ON mydb1.* TO user2@localhost; 3.撤销授权

语法： REVOKE 权限 1, … , 权限 n ON 数据库.* FROM ‘用户名’@地址;

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost; 4.查看用户权限

语法： SHOW GRANTS FOR ‘用户名’@地址;

SHOW GRANTS FOR user1@localhost; 5.删除用户

语法： DROP USER ‘用户名’@地址;

DROP USER ‘user1’@localhost; 6.修改用户密码（以root身份）

语法 ： use mysql; alter user '用户名'@localhost identified by '新密码';

六、DQL（Data Query Language）：数据查询语言

数据库执行 DQL 语句不会对数据进行改变，而是让数据库发送结果集给客户端。

语法： select 列名 ----> 要查询的列名称 from 表名 ----> 要查询的表名称 where 条件 ----> 行条件 group by 分组列 ----> 对结果分组 having 分组条件 ----> 分组后的行条件 order by 排序列 ----> 对结果分组 limit 起始行, 行数 ----> 结果限定

创建数据库：Info，在 Info 下创建如下表：

☆学生表：stu： 在这里插入图片描述 在这里插入图片描述

CREATE TABLE stu ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) ); INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male'); INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female'); INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male'); INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female'); INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male'); INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female'); INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male'); INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female'); INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male'); INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female'); INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

☆雇员表：emp 在这里插入图片描述

CREATE TABLE emp( empno INT, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), comm decimal(7,2), deptno INT ) ; INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20); INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20); INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

☆部门表：dept 在这里插入图片描述

CREATE TABLE dept( deptno INT, dname varchar(14), loc varchar(13) ); INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept values(20, 'RESEARCH', 'DALLAS'); INSERT INTO dept values(30, 'SALES', 'CHICAGO'); INSERT INTO dept values(40, 'OPERATIONS', 1.基础查询

查询所有列 SELECT * FROM 表名; (* ：通配符，表示所有列)

SELECT * FROM stu;

查询指定列 SELECT 列名 1, 列名 2, …列名 n FROM 表名;

SELECT sid, sname, age FROM stu; 2.条件查询

条件查询介绍 条件查询就是在查询时给出 WHERE 子句，在 WHERE 子句中可以使用如下运算符及关键字：

=、!=、、=；BETWEEN…AND；IN(set)；IS NULL；AND；OR；NOT；

举例说明 查询性别为女，并且年龄小于 50 的记录 SELECT * FROM stu WHERE gender='female' AND age=20 AND age 2500;

统计月薪与佣金之和大于 2500 元的人数： SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500; 查询有佣金的人数，以及有领导的人数： SELECT COUNT(comm), COUNT(mgr) FROM emp;

SUM 和 AVG：当需要纵向求和时使用 sum()函数。

查询所有雇员月薪和： SELECT SUM(sal) FROM emp;

查询所有雇员月薪和，以及所有雇员佣金和： SELECT SUM(sal), SUM(comm) FROM emp;

查询所有雇员月薪+佣金和： SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

统计所有员工平均工资： SELECT SUM(sal), COUNT(sal) FROM emp; 或者 SELECT AVG(sal) FROM emp;

MAX 和 MIN 查询最高工资和最低工资： SELECT MAX(sal), MIN(sal) FROM emp;

7.分组查询

分组查询

当需要分组查询时需要使用 GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

查询每个部门的部门编号和每个部门的工资和：

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

查询每个部门的部门编号以及每个部门的人数：

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

查询每个部门的部门编号以及每个部门工资大于 1500 的人数：

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

HAVING 子句

查询工资总和大于 9000 的部门编号以及工资和：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

注意，WHERE 是对分组前记录的条件，如果某行记录没有满足 WHERE 子句的条件，那 么这行记录不会参加分组；而 HAVING 是对分组后数据的约束。

8.LIMIT：用来限定查询结果的起始行，以及总行数。

查询 5 行记录，起始行从 0 开始 SELECT * FROM emp LIMIT 0, 5; 注意，起始行从 0 开始，即第一行开始！

查询 10 行记录，起始行从 3 开始 SELECT * FROM emp LIMIT 3, 10;

分页查询 如果一页记录为 10 条，希望查看第 3 页记录应该怎么查呢？  第一页记录起始行为 0，一共查询 10 行；  第二页记录起始行为 10，一共查询 10 行；  第三页记录起始行为 20，一共查询 10 行；

9.多表连接查询： 表连接分为内连接和外连接。

他们之间最主要的区别：内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录。

例如： 以下是员工表 staff 和职位表 deptno: 在这里插入图片描述

内连接 select staff.name,deptname from staff,deptno where staff.name=deptno.name;

在这里插入图片描述

外连接分为左连接和右连接

左连接：包含左边表中所有的记录，右边表中没有匹配的记录显示为 NULL。 右连接：包含右边表中所有的记录，左边表中没有匹配的记录显示为 NULL。

外连接(左连接)：

select staff.name,deptname from staff left join deptno onstaff.name=deptno.name;

在这里插入图片描述 外连接（右连接）：

select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;

在这里插入图片描述



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3