SQL
1) 什么是SQL ?
结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库
查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。
2) SQL 的作用
是所有关系型数据库的统一查询规范,不同的关系型数据库都支持SQL
所有的关系型数据库都可以使用SQL
不同数据库之间的SQL 有一些区别 方言
SQL通用语法
1) SQL语句可以单行 或者 多行书写,以分号 结尾 ; (Sqlyog中可以不用写分号)
2) 可以使用空格和缩进来增加语句的可读性。
3) MySql中使用SQL不区分大小写,一般关键字大写,数据库名 表名列名 小写。
4) 注释方式
注释语法 | 说明 |
---|---|
– 空格 | 单行注释 |
/* */ | 多行注释 |
# | MySql特有的单行注释 |
# show databases; 单行注释
-- show databases; 单行注释
/* 多行注释 show databases; */
SQL的分类
分类 | 说明 |
---|---|
数据定义语言 | 简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。 |
数据操作语言 | 简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。 |
数据查询语言 | 简称DQL(Data Query Language),用来查询数据库中表的记录。 |
数据控制语言 | 简称DCL(Date Control Language),用来定义数据库的访问权限和安全级别,及创建用户。(了解) |
注: 重点学习 DML 与 DQL!
DDL操作 数据库
创建数据库
命令 | 说明 |
---|---|
create database 数据库名; | 创建指定名称的数据库。 |
create database 数据库名 character set字符集; | 创建指定名称的数据库,并且指定字符集(一般都指定utf-8) |
查看/选择数据库
命令 | 说明 |
---|---|
use 数据库名 | 切换数据库 |
select database(); | 查看当前正在使用的数据库 |
show databases; | 查看Mysql中 都有哪些数据库 |
show create database 数据库名; | 查看一个数据库的定义信息 |
-- 切换数据库
USE db1;
-- 查看当前正在使用的数据库
SELECT DATABASE();
-- 查看mysql中有哪些数据库
SHOW DATABASES;
-- 查看一个数据库的定义信息
SHOW CREATE DATABASE db1;
修改数据库
命令 | 说明 |
---|---|
alter database 数据库名 character set 字符集; | 数据库的字符集修改操作 |
-- 将数据库db1 的字符集 修改为 utf8
ALTER DATABASE db1 CHARACTER SET utf8;
删除数据库
命令 | 说明 |
---|---|
drop database 数据库名 | 从MySql中永久的删除某个数据库 |
-- 删除某个数据库
DROP DATABASE db1_1;
DDL 操作 数据表
MySQL常见的数据类型
类型 | 描述 |
---|---|
int | 整型 |
double | 浮点型 |
varchar | 字符串型 |
date | 日期类型,给是为 yyyy-MM-dd ,只有年月日,没有时分秒 |
注意:MySQL中的 char类型与 varchar类型,都对应了 Java中的字符串类型,区别在于:
- char类型是固定长度的: 根据定义的字符串长度分配足够的空间。
- varchar类型是可变长度的: 只使用字符串长度所需的空间
- char类型适合存储 固定长度的字符串,比如 密码 ,性别一类
- varchar类型适合存储 在一定范围内,有长度变化的字符串
创建表
创建表语法格式:
CREATE TABLE 表名(
字段名称1 字段类型(长度),
字段名称2 字段类型 注意 最后一列不要加逗号
);
复制表结构语法格式:
create table 新表明 like 旧表名
/*
表名:category
表中字段:
分类ID :cid ,为整型
分类名称:cname,为字符串类型,指定长度20
*/
CREATE TABLE category (
cid INT,
cname VARCHAR(20)
);
/*
表名: test1
表中字段:
测试ID : tid ,为整型
测试时间: tdate , 为年月日的日期类型
*/
CREATE TABLE test1 (
tid INT,
tdate DATE
);
-- 创建一个表结构与 test1 相同的 test2表
CREATE TABLE test2 LIKE test1;
-- 查看表结构
DESC test2;
查看表
命令 | 说明 |
---|---|
show tables; | 查看当前数据库中的所有表名 |
desc 表名; | 查看数据表的结构 |
-- 查看当前数据库中的所有表名
SHOW TABLES;
-- 显示当前数据表的结构
DESC category;
-- 查看创建表的SQL语句
SHOW CREATE TABLE category;
删除表
命令 | 说明 |
---|---|
drop table 表名; | 删除表(从数据库中永久删除某一张表) |
drop table if exists 表名; | 判断表是否存在, 存在的话就删除,不存在就不执行删除 |
-- 直接删除 test1 表
DROP TABLE test1;
-- 先判断 再删除test2表
DROP TABLE IF EXISTS test2;
修改表
- 修改表名语法格式:
rename table 旧表名 to 新表名
-- 将category表 改为 category1
RENAME TABLE category TO category1;
- 修改表的字符集语法格式:
alter table 表名 character set 字符集
-- 将category表的字符集 修改为gbk
ALTER TABLE category CHARACTER SET gbk;
- 向表中添加列, 关键字 ADD;语法格式:
alert table 表名 add 字段名称 字段类型
-- 为分类表添加一个新的字段为 分类描述 cdesc varchar(20)
ALTER TABLE category ADD cdesc VARCHAR(20);
- 修改表中列的 数据类型或长度 , 关键字 MODIFY
alter table 表名 modify 字段名称 字段类型
-- 对分类表的描述字段进行修改,类型varchar(50)
ALTER TABLE category MODIFY cdesc VARCHAR(50);
- 修改列名称 , 关键字 CHANGE
alter table 表名 change 旧列名 新列名 类型(长度);
-- 对分类表中的 desc字段进行更换, 更换为 description varchar(30)
ALTER TABLE category CHANGE cdesc description VARCHAR(30);
- 删除列 ,关键字 DROP
alter table 表名 drop 列名;
-- 删除分类表中description这列
ALTER TABLE category DROP description;
DML 操作表中数据
插入数据
insert into 表名 (字段名1,字段名2…) values(字段值1,字段值2…);
方式1: 插入全部字段, 将所有字段名都写出来
/*
表名:student
表中字段:
学员ID, sid int
姓名, sname varchar(20)
年龄, age int
性别, sex char(1)
地址, address varchar(40)
*/
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
age INT,
sex CHAR(1),
address VARCHAR(40)
);
/*
向 学生表中添加数据,3种方式
方式1: 插入全部字段, 将所有字段名都写出来
*/
INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟空',20,'男','花果山');
方式2: 插入全部字段,不写字段名
INSERT INTO student VALUES (2,'孙悟饭',10,'男','地球');
方式3:插入指定字段的值
INSERT INTO category (cname) VALUES ('白骨精');
注意:
1)值与字段必须要对应,个数相同&数据类型相同
2)值的数据大小,必须在字段指定的长度范围内
3)varchar char date类型的值必须使用单引号,或者双引号 包裹。
4)如果要插入空值,可以忽略不写,或者插入null
5) 如果插入指定字段的值,必须要上写列名
修改数据
- 语法格式1:不带条件的修改
update 表名 set 列名 = 值 - 语法格式2:带条件的修改
update 表名 set 列名 = 值 where 条件表达式:字段名 = 值
-- 不带条件修改,将所有的性别改为女(慎用!!)
UPDATE student SET sex = '女';
-- 带条件的修改,将sid 为1的学生,性别改为男
UPDATE student SET sex = '男' WHERE sid = 1;
-- 一次修改多个列, 将sid为 2 的学员,年龄改为 20,地址改为 北京
UPDATE student SET age = 20, address = '北京' WHERE sid = 2;
删除数据
- 语法格式1:删除所有数据
delete from 表名 - 语法格式2: 指定条件 删除数据
delete from 表名 where 字段名 = 值
-- 删除 sid 为 1 的数据
DELETE FROM student WHERE sid = 1;
-- 删除所有数据
DELETE FROM student;
- 如果要删除表中的所有数据,有两种做法
delete from 表名; 不推荐. 有多少条记录 就执行多少次删除操作. 效率低
truncate table 表名: 推荐. 先删除整张表, 然后再重新创建一张一模一样的表. 效率高
truncate table student;
DQL 查询表中数据
CREATE TABLE emp(
eid INT,
ename VARCHAR(20),
sex CHAR(1),
salary DOUBLE,
hire_date DATE,
dept_name VARCHAR(20)
);
INSERT INTO emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部');
INSERT INTO emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
INSERT INTO emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部');
INSERT INTO emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部');
INSERT INTO emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部');
INSERT INTO emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部');
INSERT INTO emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
INSERT INTO emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部');
INSERT INTO emp VALUES(9,'吴承恩','男',20000,'2000-03-14',NULL);
INSERT INTO emp VALUES(10,'孙悟饭','男', 10,'2020-03-14',财务部);
INSERT INTO emp VALUES(11,'兔八哥','女', 300,'2010-03-14',财务部);
简单查询
查询emp中的 所有数据
-- 查询emp中的 所有数据
SELECT * FROM emp;
查询emp表中的所有记录,仅显示id和name字段
-- 查询emp表中的所有记录,仅显示id和name字段
SELECT eid, ename FROM emp;
将所有的员工信息查询出来,并将列名改为中文
别名查询,使用关键字 as
-- 将所有的员工信息查询出来,并将列名改为中文
SELECT
eid AS '编号',
ename AS '姓名',
sex AS'性别',
salary AS '薪资',
hire_date '入职时间', -- as可以省略
dept_name '部门名称'
FROM emp;
查询一共有几个部门
使用去重关键字 distinct
-- 查询一共有几个部门
SELECT DISTINCT dept_name FROM emp;
将所有员工的工资 +1000 元进行显示
-- 将所有员工的工资 +1000 元进行显示
SELECT ename, salary + 1000 FROM emp;
条件查询
语法格式:
select 列名 from 表名 where 条件表达式
运算符
1) 比较运算符
运算符 | 说明 |
---|---|
> < <= >= = <> != | 大于、小于、大于(小于)等于、不等于 |
BETWEEN …AND… | 显示在某一区间的值;例如: 2000-10000之间: Between 2000 and 10000 |
IN(集合) | 集合表示多个值,使用逗号分隔,例如: name in (悟空,八戒) in中的每个数据都会作为一次条件,只要满足条件就会显示 |
LIKE ‘%张%’ | 模糊查询 |
IS NULL | 查询某一列为NULL的值, 注: 不能写 = NULL |
2) 逻辑运算符
运算符 | 说明 |
---|---|
And && | 多个条件同时成立 |
Or | 多个条件任一成立 |
Not | 不成立,取反。 |
# 查询员工姓名为黄蓉的员工信息
SELECT * FROM emp WHERE ename = '黄蓉';
# 查询薪水价格为5000的员工信息
SELECT * FROM emp WHERE salary = 5000;
# 查询薪水价格不是5000的所有员工信息
SELECT * FROM emp WHERE salary != 5000;
SELECT * FROM emp WHERE salary <> 5000;
# 查询薪水价格大于6000元的所有员工信息
SELECT * FROM emp WHERE salary > 6000;
# 查询薪水价格在5000到10000之间所有员工信息
SELECT * FROM emp WHERE salary >= 5000 && salary <= 10000;
SELECT * FROM emp WHERE salary >= 5000 AND salary <= 10000;
SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;
# 查询薪水价格是3600或7200或者20000的所有员工信息
SELECT * FROM emp WHERE salary = 3600 OR salary = 7200 OR salary = 20000;
SELECT * FROM emp WHERE salary = 3600 || salary = 7200 || salary = 20000;
SELECT * FROM emp WHERE salary IN(3600,7200,20000);
模糊查询 通配符
通配符 | 说明 |
---|---|
% | 表示匹配任意多个字符串 |
_ | 表示匹配 一个字符 |
# 查询含有'精'字的所有员工信息
SELECT * FROM emp WHERE ename LIKE '%精%';
# 查询以'孙'开头的所有员工信息
SELECT * FROM emp WHERE ename LIKE '孙%';
# 查询第二个字为'兔'的所有员工信息
SELECT * FROM emp WHERE ename LIKE '_兔%';
# 查询没有部门的员工信息
SELECT * FROM emp WHERE dept_name IS NULL;
# select * from emp where dept_name = null; 不能这样写
# 查询有部门的员工信息
SELECT * FROM emp WHERE dept_name IS NOT NULL;
排序
通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实数据)
格式:
SELECT 字段名 FROM 表名 WHERE 字段 = 值]ORDER BY 字段名 ASC / DESC
ASC 表示升序排序(默认)
DESC 表示降序排序
排序方式
1) 单列排序
- 只按照某一个字段进行排序, 就是单列排序
使用 salary 字段,对emp 表数据进行排序 (升序/降序)
# 使用 salary 字段,对emp 表数据进行排序 (升序/降序)
-- 默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;
-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;
2) 多列排序
- 同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推
在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;
聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对某一列的值进行计算,然后返回一个单一的值(另外聚合函数会忽略null空值。);
语法结构:
SELECT 聚合函数(字段名) FROM 表名;
聚合函数 | 作用 |
---|---|
count(字段) | 统计指定列不为NULL的记录行数 |
sum(字段) | 计算指定列的数值和 |
max(字段) | 计算指定列的最大值 |
min(字段) | 计算指定列的最小值 |
avg(字段) | 计算指定列的平均值 |
#1 查询员工的总数
SELECT COUNT(eid) FROM emp; -- 使用某一个字段
SELECT COUNT(*) FROM emp; -- 使用 *
SELECT COUNT(1) FROM emp; -- 使用 1,与 * 效果一样
-- 下面这条SQL 得到的总条数不准确,因为count函数忽略了空值
-- 所以使用时注意不要使用带有null的列进行统计
SELECT COUNT(dept_name) FROM emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
SELECT
SUM(salary) AS '总薪水',
MAX(salary) AS '最高薪水',
MIN(salary) AS '最低薪水',
AVG(salary) AS '平均薪水'
FROM emp;
#3 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary > 4000;
#4 查询部门为'教学部'的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';
#5 查询部门为'市场部'所有员工的平均薪水
SELECT AVG(salary) FROM emp WHERE dept_name = '市场部';
分组
- 分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组
- 语法格式:SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 HAVING 条件;
- 分组过程
# 通过性别字段 进行分组,求各组的平均薪资
SELECT sex, AVG(salary) AS '平均薪资' FROM emp GROUP BY sex;
#1.查询所有部门信息
SELECT dept_name FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资
SELECT dept_name, AVG(salary) FROM emp GROUP BY dept_name;
#3.查询每个部门的平均薪资, 部门名称不能为null
-- 方式一:
SELECT
dept_name,
AVG(salary)
FROM emp
GROUP BY dept_name HAVING dept_name IS NOT NULL;
-- 方式二:
SELECT
dept_name,
AVG(salary)
FROM emp
WHERE dept_name IS NOT NULL
GROUP BY dept_name;
#查询平均薪资大于6000的部门.
-- 需要在分组后再次进行过滤,使用 having
SELECT
dept_name
FROM emp GROUP BY dept_name HAVING AVG(salary) > 6000 AND dept_name IS NOT NULL;
SELECT
dept_name,
AVG(salary)
FROM emp
WHERE dept_name IS NOT NULL
GROUP BY dept_name HAVING AVG(salary) > 6000;
- where 与 having的区别
过滤方式 | 特点 |
---|---|
where | where 进行分组前的过滤;where 后面不能写 聚合函数 |
having | having 是分组后的过滤;having 后面可以写 聚合函数 |
limit关键字
- limit是限制的意思,用于 限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
- limit 语法是 MySql的方言,用来完成分页
- 语法结构:
SELECT 字段1,字段2… FROM 表名 LIMIT offset , length;
limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数
offset 起始行数, 从0开始记数, 如果省略 则默认为 0.(从几行开始查,就用几行减一)
length 返回的行数
# 查询emp表中的前 5条数据
-- 起始值默认是从0开始的.
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0, 5;
# 查询emp表中 从第4条开始,查询6条
SELECT * FROM emp LIMIT 3, 6;
# 分页操作 每页显示3条数据
-- 分页公式 起始索引 = (当前页 - 1) * 每页条数
SELECT * FROM emp LIMIT 0, 3;
SELECT * FROM emp LIMIT 3, 3;
SELECT * FROM emp LIMIT 6, 3;