数据库SQL语句核心知识点

最新推荐文章于 2025-11-13 18:35:37 发布

原创最新推荐文章于 2025-11-13 18:35:37 发布 · 1k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

数据库技术专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了MySQL中各类数据类型，包括数值、字符串、日期时间等，并指导了创建表、修改字段、数据插入、查询与聚合等操作，以及主键、外键和自连接的使用。

在这里插入图片描述

SQL数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。

MySQL支持所有标准SQL数值数据类型。

MySQL支持多种类型，大致可以分为三类

数值类型
字符串类型
日期和时间类型

常用数据类型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型； char(10) 'abc ’
varchar：可变长度字符串类型；varchar(10) ‘abc’
text：字符串类型;
blob：二进制类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

在mysql中，字符串类型和日期类型都要用单引号括起来。‘Myxq’ ‘2020-01-01’

在这里插入图片描述

分类	类型名称	说明
整数类型	tinyInt	很小的整数
	smallint	小的整数
	mediumint	中等大小的整数
	int(integer)	普通大小的整数
小数类型	float	单精度浮点数
	double	双精度浮点数
	decimal（m,d）	压缩严格的定点数 decimal(10,2) 一共10位,小数2位
日期类型	year	YYYY 1901~2155
	time	HH:MM:SS -838:59:59~838:59:59
	date	YYYY-MM-DD 1000-01-01~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 1970₀₁01 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型	CHAR(M)	M为0~255之间的整数定长 char(10) tom
	VARCHAR(M)	M为0~65535之间的整数变长 varchar(10) tom
	TINYBLOB	允许长度0~255字节
	BLOB	允许长度0~65535字节
	MEDIUMBLOB	允许长度0~167772150字节
	LONGBLOB	允许长度0~4294967295字节
	TINYTEXT	允许长度0~255字节
	TEXT	允许长度0~65535字节
	MEDIUMTEXT	允许长度0~167772150字节
	LONGTEXT	允许长度0~4294967295字节
	VARBINARY(M)	允许长度0~M个字节的变长字节字符串
	BINARY(M)	允许长度0~M个字节的定长字节字符串

DDL

创建数据库

create database 数据库名 character set utf8;

修改数据库

alter database 数据库名 charactor set gbk;

创建学生表

在这里插入图片描述

添加一列

ALTER TABLE 表名 ADD 列名数据类型;

查看表的字段信息

DESC 表名;

修改一个表的字段类型

ALTER TABLE 表名 MODIFY 字段名数据类型;

删除一列

ALTER TABLE 表名 DROP 字段名;

修改表名

RENAME TABLE 原始表名 TO 要修改的表名;

查看表的创建细节

SHOW CREATE TABLE 表名;

修改表的字符集为gbk

ALTER TABLE 表名 CHARACTER SET 字符集名称;

修改表的列名

ALTER TABLE 表名 CHANGE 原始列名新列名数据类型;

删除表

DROP TABLE 表名;

DML

查询表中的所有数据

SELECT * FROM 表名;

DML是对表中的数据进行增、删、改的操作

插入操作

INSERT INTO 表名（列名1，列名2 …）VALUE (列值1，列值2…);
注意事项
- 列名与列值的类型、个数、顺序要一一对应。
- 值不要超出列定义的长度。
- 插入的日期和字符一样，都使用引号括起来。
批量插入
- INSERT INTO 表名（列名1，列名2 …）VALUES (列值1，列值2…)，(列值1，列值2…);

更新操作

UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。 WHERE 列名=值
把所有学生的分数改为90
- update students set score=90
把姓名为zs的学生分数改为60
- update student set score=60 where name=‘zs’;
把姓名为李四的年龄改为20和分数改为70
- update student set age=20,score=70 where name=‘ls’;
把wc的年龄在原来基础上加1岁
- update students set age=age+1 where name=‘wc’;
修改数据库密码
- use mysql;
- update user set password=password(‘abc’) WHERE User=‘root’;
- update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;
- flush privileges;刷新MySQL的系统权限相关表
- mysqladmin -u root -p password 12356

删除操作

DELETE FROM 表名【WHERE 列名=值】
TRUNCATE TABLE 表名;
DELETE 与TRUNCATE的区别
- DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
- TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。
  删除的数据不能找回。执行速度比DELETE快。

DQL

查询所有列

SELECT * FROM 表名;

结果集

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
结果集
- 通过查询语句查询出来的数据以表的形式展示我们称这个表为虚拟结果集。存放在内存中。
- 查询返回的结果集是一张虚拟表。

查询指定列的数据

SELECT 列名1，列表2… FROM 表名;

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用一些运算符及关键字：
条件查询运行符及关键字
- =（等于）、!=（不等于）、<>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）；
- BETWEEN…AND；值在什么范围
- IN(set)；
  - 固定的范围值
- IS NULL；（为空） IS NOT NULL（不为空）
- AND；与
- OR；或
- NOT；非
使用
- 查询性别为男，并且年龄为20的学生记录
  - SELECT * FROM students WHERE gender=‘男’ AND age=20;
- 查询学号为1001 或者名为zs的记录
  - SELECT * FROM students WHERE id =‘1001’ OR name=‘zs’;
- 查询学号为1001，1002，1003的记录
  - SELECT * FROM students WHERE id=‘1001’ OR id=‘1002’ OR 1001=‘1003’; (三个都有)
  - SELECT * FROM students WHERE id IN(‘1001’,‘1002’,‘1003’);
  - SELECT * FROM students WHERE id NOT IN (‘1001’,‘1002’,‘1003’);
- 查询年龄为null的记录
  - SELECT * FROM students WHERE age IS NULL;
- 查询年龄在18到20之间的学生记录
  - SELECT * FROM students WHERE age>=18 AND age<=20;
  - SELECT * FROM students WHERE age BETWEEN 18 AND 20;
- 查询性别非男的学生记录
  - SELECT * FROM students WHERE gender !=‘男’;
- 查询姓名不为null的学生记录
  - SELECT * FROM students WHERE name IS NOT NULL;

模糊查询

根据指定的关键进行查询
使用LIKE关键字后跟通配符
通配符
- _ ：任意一个字符
- %：任意0~n个字符
使用
- 查询姓名由5个字母构成的学生记录
  - SELECT * FROM students WHERE name LIKE ‘_____’;
  - 模糊查询必须使用LIKE关键字。其中 “”匹配任意一个字母，5个“”表示5个任意字母。
- 查询姓名由5个字母构成，并且第5个字母为“s”的学生记录
  - SELECT * FROM students WHERE name LIKE ‘____s’;
- 查询姓名以“m”开头的学生记录
  - SELECT * FROM students WHERE name LIKE ‘m%’;
  - 其中“%”匹配0~n个任何字母。
- 查询姓名中第2个字母为“u”的学生记录
  - SELECT * FROM students WHERE name LIKE ‘_u%’;
- 查询姓名中包含“s”字母的学生记录
  - SELECT * FROM stu WHERE name LIKE ‘%s%’;

字段控制查询

去除重复记录
- SELECT DISTINCT name FROM students;
把查询字段的结果进行运算，必须都要是数据型
- SELECT *,字段1+字段2 FROM 表名;
- 列有很多记录的值为NULL，
  因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。
  下面使用了把NULL转换成数值0的函数IFNULL：
- SELECT *,age+IFNULL(score,0) FROM students;
对查询结果起别名
- 在上面查询中出现列名为sx+IFNULL(yw,0)，这很不美观，现在我们给这一列给出一个别名，为total：
- SELECT *, yw+IFNULL(sx,0) AS total FROM score;
- 省略 AS SELECT *, yw+IFNULL(sx,0) total FROM score;

排序

创建表
- CREATE TABLE employee (
  id int(11) NOT NULL,
  name varchar(50) DEFAULT NULL,
  gender varchar(1) DEFAULT NULL,
  hire_date date DEFAULT NULL,
  salary decimal(10,0) DEFAULT NULL,
  performance double(255,0) DEFAULT NULL,
  manage double(255,0) DEFAULT NULL,
  department varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  - INSERT INTO employee VALUES (1001, ‘张三’, ‘男’, ‘1991-7-25’, 2000, 200, 500, ‘营销部’);
    INSERT INTO employee VALUES (1002, ‘李四’, ‘男’, ‘2017-7-5’, 4000, 500, NULL, ‘营销部’);
    INSERT INTO employee VALUES (1003, ‘王五’, ‘女’, ‘2018-5-1’, 6000, 100, 5000, ‘研发部’);
    INSERT INTO employee VALUES (1004, ‘赵六’, ‘男’, ‘1991-6-1’, 1000, 3000, 4000, ‘财务部’);
    INSERT INTO employee VALUES (1005, ‘孙七’, ‘女’, ‘2018-3-23’, 8000, 1000, NULL, ‘研发部’);
    INSERT INTO employee VALUES (1006, ‘周八’, ‘男’, ‘2010-9-8’, 5000, 500, 1000, ‘人事部’);
    INSERT INTO employee VALUES (1007, ‘吴九’, ‘女’, ‘2017-7-5’, 8000, 601, NULL, ‘研发部’);
    INSERT INTO employee VALUES (1008, ‘郑十’, ‘女’, ‘2014-4-6’, 4000, 1801, NULL, ‘人事部’);
对查询的结果进行排序
使用关键字ORDER BY
排序类型
- 升序ASC
  - 从小到大默认
- 降序DESC
  - 从大到小
使用
- 对所有员工的薪水进行排序
  - SELECT *FROM employee ORDER BY age ASC;
- 查询所有学生记录，按年龄降序排序
  - SELECT * FROM employee ORDER BY age DESC;
- 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
  - SELECT * FROM employee ORDER BY salary DESC, id ASC;

聚合函数

对查询的结果进行统计计算
常用聚合函数
- COUNT()：统计指定列不为NULL的记录行数；
- MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
- MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
- SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
- AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
使用
- COUNT
  - 查询employee表中记录数：
    - SELECT COUNT(*) AS total_record FROM employee;
  - 查询员工表中有绩效的人数
    - SELECT COUNT(performance) FROM employee;
  - 查询员工表中月薪大于2500的人数：
    - SELECT COUNT(*) FROM employee WHERE salary > 2500;
  - 统计月薪与绩效之和大于5000元的人数：
    - SELECT COUNT(*) FROM employee WHERE salary+IFNULL(performance,0) > 5000;
  - 查询有绩效的人数，和有管理费的人数：
    - SELECT COUNT(performance), COUNT(manage) FROM employee;
- SUM和AVG
  - 查询所有雇员月薪和：
    - SELECT SUM(salary) FROM employee;
  - 查询所有雇员月薪和，以及所有雇员绩效和
    - SELECT SUM(salary), SUM(performance) FROM employee;
  - 查询所有雇员月薪+绩效和：
    - SELECT SUM(salary+IFNULL(performance,0)) FROM employee;
  - 统计所有员工平均工资：
    - SELECT AVG(salary) FROM employee;
- MAX和MIN
  - 查询最高工资和最低工资：
    - SELECT MAX(salary), MIN(salary) FROM employee;

分组查询

having作用和where一样，但having只能用于group by 分组后统计函数

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

SELECT department,SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;

select * from 表名 limit 0,3;

数据完整性

主键约束

每个表中要有一个主键
数据唯一，且不能为null

ALTER TABLE student ADD CONSTRAINT PRIMARY KEY (id);

CREATE TABLE 表名(字段名1 数据类型 primary key,字段2 数据类型);
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(要设置主键的字段));
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(主键1，主键2));

唯一约束

特点
- 指定列的数据不能重复
- 可以为空值
格式
- CREATE TABLE 表名(字段名1 数据类型字段2 数据类型 UNIQUE);

自动增长列

特点
- 指定列的数据自动增长
- 即使数据删除，还是从删除的序号继续往下
格式：
- CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE);

域完整性约束

数据类型
- 数值类型、日期类型、字符串类型
非空约束（not null）
- CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE not null);
默认值约束(default)
- CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE not null default ‘男’);
- 插入的时候，values当中的值直接给default

数据库的主键和外键类型一定要一致；

两个表必须得要是InnoDB类型

设置参照完整性后，外键当中的内值，必须得是主键当中的内容

一个表设置当中的字段设置为主键，设置主键的为主表

CREATE TABLE student(sid int PRIMARY key,name varchar(50) not null,sex varchar(10) default ‘男’);

创建表时，设置外键，设置外键的为子表

CREATE TABLE score(
sid INT,
score DOUBLE,
CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student(sid));

多表查询

合并结果集

什么是合并结果集
- 合并结果集就是把两个select语句的查询结果合并到一起
合并结果集的两种方式
- UNION
  - 合并时去除重复记录
- UNION ALL
  - 合并时不去除重复记录
格式：
- UNION
  - SELECT * FROM 表1 UNION SELECT * FROM 表2；
  - SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
示例
- 创建表
- UNION
- UNION ALL
注意事项
- 被合并的两个结果：列数、列类型必须相同。

连接查询

什么是连接查询
- 也可以叫跨表查询，需要关联多个表进行查询
什么是笛卡尔集
- 假设集合A={a,b}，集合B={0,1,2}，
- 则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
- 可以扩展到多个集合的情况
同时查询两个表，出现的就是笛卡尔集结果
查询时给表起别名
多表联查，如何保证数据正确
- 在查询时要把主键和外键保持一致
- 主表当中的数据参照子表当中的数据
- 原理
  - 逐行判断，相等的留下，不相等的全不要
根据连接方式分类
- 内连接
  - 等值连接
    - 两个表同时出现的id号（值）才显示
    - 与多表联查约束主外键是一样，只是写法改变了。
    - ON后面只写主外键
    - 如果还有条件直接在后面写where
    - 多表联查后面还有条件就直接写and
  - 多表连接
    - 建立学生，分数，科目表
    - 使用99连接法
    - 使用内联查询
  - 非等值连接
    - 示例表
    - 建表语句
      - CREATE TABLE emp (
        empno int(11) NOT NULL,
        ename varchar(255) DEFAULT NULL,
        job varchar(255) DEFAULT NULL,
        mgr varchar(255) DEFAULT NULL,
        hiredate date DEFAULT NULL,
        salary decimal(10,0) DEFAULT NULL,
        comm double DEFAULT NULL,
        deptno int(11) DEFAULT NULL,
        PRIMARY KEY (empno)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        
        INSERT INTO emp VALUES (7369, ‘孙悟空’, ‘职员’, ‘7902’, ‘2010-12-17’, 800, NULL, 20);
        INSERT INTO emp VALUES (7499, ‘孙尚香’, ‘销售人员’, ‘7698’, ‘2011-2-20’, 1600, 300, 30);
        INSERT INTO emp VALUES (7521, ‘李白’, ‘销售人员’, ‘7698’, ‘2011-2-22’, 1250, 500, 30);
        INSERT INTO emp VALUES (7566, ‘程咬金’, ‘经理’, ‘7839’, ‘2011-4-2’, 2975, NULL, 20);
        INSERT INTO emp VALUES (7654, ‘妲己’, ‘销售人员’, ‘7698’, ‘2011-9-28’, 1250, 1400, 30);
        INSERT INTO emp VALUES (7698, ‘兰陵王’, ‘经理’, ‘7839’, ‘2011-5-1’, 2854, NULL, 30);
        INSERT INTO emp VALUES (7782, ‘虞姬’, ‘经理’, ‘7839’, ‘2011-6-9’, 2450, NULL, 10);
        INSERT INTO emp VALUES (7788, ‘项羽’, ‘检查员’, ‘7566’, ‘2017-4-19’, 3000, NULL, 20);
        INSERT INTO emp VALUES (7839, ‘张飞’, ‘总裁’, NULL, ‘2010-6-12’, 5000, NULL, 10);
        INSERT INTO emp VALUES (7844, ‘蔡文姬’, ‘销售人员’, ‘7698’, ‘2011-9-8’, 1500, 0, 30);
        INSERT INTO emp VALUES (7876, ‘阿珂’, ‘职员’, ‘7788’, ‘2017-5-23’, 1100, NULL, 20);
        INSERT INTO emp VALUES (7900, ‘刘备’, ‘职员’, ‘7698’, ‘2011-12-3’, 950, NULL, 30);
        INSERT INTO emp VALUES (7902, ‘诸葛亮’, ‘检查员’, ‘7566’, ‘2011-12-3’, 3000, NULL, 20);
        INSERT INTO emp VALUES (7934, ‘鲁班’, ‘职员’, ‘7782’, ‘2012-1-23’, 1300, NULL, 10);
      - CREATE TABLE dept (
        deptno bigint(2) NOT NULL AUTO_INCREMENT COMMENT ‘表示部门编号，由两位数字所组成’,
        dname varchar(14) DEFAULT NULL COMMENT ‘部门名称，最多由14个字符所组成’,
        local varchar(13) DEFAULT NULL COMMENT ‘部门所在的位置’,
        PRIMARY KEY (deptno)
        ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;
        
        INSERT INTO dept VALUES (10, ‘财务部’, ‘北京’);
        INSERT INTO dept VALUES (20, ‘调研部’, ‘上海’);
        INSERT INTO dept VALUES (30, ‘销售部’, ‘王者峡谷’);
        INSERT INTO dept VALUES (40, ‘运营部’, ‘腾讯大楼’);
      - CREATE TABLE salgrade (
        grade bigint(11) NOT NULL AUTO_INCREMENT COMMENT ‘工资等级’,
        lowSalary int(11) DEFAULT NULL COMMENT ‘此等级的最低工资’,
        highSalary int(11) DEFAULT NULL COMMENT ‘此等级的最高工资’,
        PRIMARY KEY (grade)
        ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
        
        INSERT INTO salgrade VALUES (1, 700, 1200);
        INSERT INTO salgrade VALUES (2, 1201, 1400);
        INSERT INTO salgrade VALUES (3, 1401, 2000);
        INSERT INTO salgrade VALUES (4, 2001, 3000);
        INSERT INTO salgrade VALUES (5, 3001, 9999);
    - 查询所有员工的姓名，工资，所在部门的名称以及工资的等级
      - 1.查询所有员工的姓名，工资
      - 2.查询所有员工的姓名，工资和所有部门
      - 3.查询所有员工的姓名，工资和所在部门及工资等级
  - 自连接
- 外连接
  - 左外连接（左连接）
    - 两表满足条件相同的数据查出来，如果左边表当中有不相同的数据，也把左边表当中的数据查出来。
    - 左边表当中的数据全部查出，右边表当中，只查出满足条件的内容
    - 使用内连接时，周七不会查出来，没有成绩，缺考了。把所有考过试的学生分数查出来。
    - 使用左连接查询所有学生及学生的考试分数
      - 左连接会把左表当中的数据全部查出，右表当中只查出满足条件的数据
      - 可以省略outer不写
      - 查询时，两个表可以不需要建立主外键约束
  - 右外连接（右连接）
    - 右连接会把右当中的数据全部查出，左表当中只查出满足条件的数据
    - 右边表当中的所有数据全部查出，左边表只查出满足条件的记录
    - 站在表的角度去看，使用左连接就把左边表当中的内容全部查出，右边查出满足条件的。
    - 使用右连接，就把右边表当中的数据全部查出。左边查出满足条件的。
- 自然连接
  - 连接查询会产生无用笛卡尔集，我们通常使用主外键关系等式来去除它。
  - 而自然连接无需你去给出主外键等式，它会自动找到这一等式
  - 也就是说不用去写条件
  - 要求
    - 两张连接的表中列名称和类型完全一致的列作为条件
    - 会去除相同的列

子查询

什么是子查询
- 一个select语句中包含另一个完整的select语句。
- 或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置
- where后，把select查询出的结果当作另一个select的条件值
- from后，把查询出的结果当作一个新表；
示例表
使用
- 查询与项羽同一个部门人员工
  - 1.先查出项羽所在的部门编号
  - 2.再根据编号查同一部门的员工
  - 把第1条查出来的结果当第2天语句的条件
- 查询工资高于程咬金的员工
  - 1.查出程咬金的工资
  - 2.再去根据查出的结果查询出大于该值的记录员工名称
- 工资高于30号部门所有人的员工信息
  - 1.先查出30号部门工资最高的那个人
  - 2.再到整个表中查询大于30号部门工资最高的那个人
- 查询工作和工资与妲己完全相同的员工信息
  - 1.先查出妲已的工作和工资
  - 2.根据查询结果当作条件再去查询工作和工资相同的员工
  - 由于是两个条件，使用 IN进行判断
- 有2个以上直接下属的员工信息
  - 1.对所有的上级编号进行分组
  - 2.找出大于2个的，大于2个说明有两个下属
  - 3.把上条的结果当作员工编号时行查询
- 查询员工编号为7788的员工名称、员工工资、部门名称、部门地址