主要内容:
- 能够使用SQL语句操作数据库
- 能够使用SQL语句操作表结构
- 能够使用SQL语句进行数据的添加修改和删除的操作
- 能够使用SQL语句简单查询数据
- 能够使用SQL语句进行排序
- 能够使用聚合函数
- 能够使用SQL语句进行分组查询
- 能够使用DCL管理MySQL中的用户
1 SQL概念
1.1 什么是SQL?
SQL(Structured Query Language,结构化查询语言):其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。
1.2 SQL作用
- 是一种所有关系型数据库的查询规范,不同的数据库都支持。
- 通用的数据库操作语言,可以用在不同的数据库中。
- 不同的数据库SQL语句有一些区别。
1.3 SQL通用语法
- SQL 语句可以单行或多行书写,以分号结尾。(如果在SQLyog中不是必须加的)
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
- 三种注释:
- 单行注释:
-- 注释内容
或# 注释内容
(MySQL 特有); - 多行注释:
/* 注释 */
。
- 单行注释:
1.4 SQL分类
- DDL(Data Definition Language)数据定义语言:用来定义数据库对象:数据库,表,列等。关键字:
CREATE
,ALTER
,DROP
等; - DML(Data Manipulation Language)数据操作语言:用来对数据库中表的数据进行增删改。关键字:
INSERT
,UPDATE
,DELETE
等; - DQL(Data Query Language)数据查询语言:用来查询数据库中表的记录(数据)。关键字:
SELECT
,WHERE
,SHOW
等; - 【了解】DCL(Data Control Language)数据控制语言:用来定义数据库的访问权限和安全级别,及创建用户。关键字:
GRANT
,REVOKE
等。
2 DDL:操作数据库和表
2.1 操作数据库
2.1.1 C(Create):创建
- 创建数据库:
create database 数据库名称;
- 创建数据库,判断不存在,再创建:
create database if not exists 数据库名称;
- 创建数据库,并指定字符集:
create database 数据库名称 character set 字符集名;
-- 直接创建数据库db1 CREATE DATABASE db1; -- 判断是否存在,如果不存在则创建数据库db2 CREATE DATABASE IF NOT EXISTS db2; -- 创建数据库并指定字符集为gbk CREATE DATABASE db3 CHARACTER SET gbk; -- 创建db4数据库,判断是否存在,并制定字符集为gbk CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;
2.1.2 R(Retrieve):查询
- 查询所有数据库的名称:
show databases;
- 查询某个数据库的字符集、创建语句:
show create database 数据库名称;
2.1.3 U(Update):修改
- 修改数据库的字符集:
alter database 数据库名称 character set 字符集名称;
-- 将db3数据库的字符集改成utf8 ALTER DATABASE db3 CHARACTER SET utf8;
2.1.4 D(Delete):删除
- 删除数据库:
drop database 数据库名称;
- 判断数据库存在,存在再删除:
drop database if exists 数据库名称;
2.1.5 使用数据库
- 查询当前正在使用的数据库名称:
select database();
- 使用数据库:
use 数据库名称;
2.2 操作表
2.2.1 C(Create):创建
-
创建表:
CREATE TABLE 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n );
注意: 最后一列,不需要加逗号
,
。 -
数据类型:
- int: 整型,整数类型
age int,
- double: 浮点型,小数类型
score double(5,2)
- date: 日期类型,只包含年月日,yyyy-MM-dd
- datetime: 日期类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
- timestamp: 时间戳类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值 - varchar: 字符串型
name varchar(20)
:姓名最大20个字符注意: zhangsan(8个字符);张三(2个字符)
CREATE TABLE student ( id int, name varchar(32), age int, score double(4, 1), birthday date, insert_time timestamp );
- int: 整型,整数类型
-
详细的数据类型:
分类 类型名称 类型说明 整数 tinyInt 微整型:很小的整数(占8位二进制) smallint 小整型:小的整数(占16位二进制) mediumint 中整型:中等长度的整数(占24位二进制) int(integer) 整型:整数类型(占32位二进制) 小数 float 单精度浮点数,占4个字节 double 双精度浮点数,占8个字节 日期 time 表示时间类型 date 表示日期类型 datetime 同时可以表示日期和时间类型 字符串 char(m) 固定长度的字符串,无论使用几个字符都占满全部,M为0~255之间的整数 varchar(m) 可变长度的字符串,使用几个字符就占用几个,M为0~65535之间的整数 大二进制 tinyblob
(Big Large Object)允许长度0~255字节 blob 允许长度0~65535字节 mediumblob 允许长度0~167772150字节 longblob 允许长度0~4294967295字节 大文本 tinytext 允许长度0~255字节 text 允许长度0~65535字节 mediumtext 允许长度0~167772150字节 longtext 允许长度0~4294967295字节 -
复制表:
create table 表名 like 被复制的表名;
2.2.2 R(Retrieve):查询
- 查询某个数据库中所有的表名称:
show tables;
- 查询表结构:
desc 表名;
- 查看创建表的SQL语句:
show create table 表名;
2.2.3 U(Update):修改
- 修改表名:
alter table 表名 rename to 新的表名;
- 修改表的字符集:
alter table 表名 character set 字符集名称;
- 添加一列:
alter table 表名 add 列名 数据类型;
- 修改列名称、类型:
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;
- 删除列:
alter table 表名 drop 列名;
2.2.4 D(Delete):删除
- 直接删除表:
drop table 表名;
- 判断表是否存在,如果存在则删除表:
drop table if exists 表名 ;
2.3 客户端图形化工具:
- SQLyog: SQLyog是业界著名的Webyog公司出品的一款简洁高效、功能强大的图形化MySQL数据库管理工具。使用SQLyog可以快速直观地让您从世界的任何角落通过网络来维护远端的MySQL数据库。
- Navicat: Navicat Premium 是一套数据库开发工具,让你从单一应用程序中同时连接 MySQL、MariaDB、MongoDB、SQL Server、Oracle、PostgreSQL 和 SQLite 数据库。可以快速轻松地创建、管理和维护数据库。
3 DML:增删改表中数据
3.1 添加数据:Insert
- 给指定列(字段)设置值:
insert into 表名(列名1, 列名2, ..., 列名n) values(值1, 值2, ..., 值n);
注意: 列名和值要一一对应。
- 表名后不定义列名,则默认给所有列添加值:
insert into 表名 values(值1, 值2, ..., 值n);
注意: 除了数字类型,其他类型需要使用引号(单双都可以)引起来。
3.2 删除数据:Delete
- 带条件删除数据:
delete from 表名 [where 条件];
- 删除表中所有记录:
delete from 表名;
:不推荐使用。有多少条记录就会执行多少次删除操作;truncate table 表名;
:推荐使用。效率更高 先删除表,然后再创建一张一样的表。
-- 带条件删除数据,删除id为1的记录 DELETE FROM student WHERE id = 1; -- 不带条件删除数据,删除表中的所有数据 TRUNCATE TABLE student;
3.3 修改数据:Update
-
带条件修改数据:
update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件];
-
不加任何条件,则会将表中所有记录全部修改:
update 表名 set 列名1 = 值1, 列名2 = 值2, ...;
-- 不带条件修改数据,将所有的性别改成女 UPDATE student SET sex = '女'; -- 带条件修改数据,将id号为2的学生性别改成男 UPDATE student SET sex = '男' WHERE id = 2; -- 一次修改多个列,把id为3的学生,年龄改成26岁,address改成北京 UPDATE student SET age = 26, address = '北京' WHERE id = 3;
4 DQL:查询表中数据
4.1 语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组之后的条件
ORDER BY
排序
LIMIT
分页限定
4.2 基础查询
-
查询所有数据:
select * from 表名;
-
查询指定字段的数据:
select 字段名1, 字段名2, ... from 表名;
-
去除重复:
select distinct 字段名 from 表名;
-
起别名:
select 字段名1 as 别名1, 字段名2 as 别名2, ... from 表名 as 表别名;
注意:
as
也可以省略;- 起别名的好处: 显示的时候使用新的名字,并不修改表的结构;
- 表使用别名的原因: 用于多表查询操作。
-
查询结果参与计算:
- 使用四则运算计算一些列的值: 一般只会进行数值型的计算
- 某列数据和固定值运算:
select 列名1 + 固定值 from 表名;
- 某列数据和其他列数据参与运算:
select 列名1 + 列名2 from 表名;
- 某列数据和固定值运算:
IFNULL(表达式1, 表达式2)
: null参与的运算,计算结果都为null- 表达式1: 需要判断是否为null的字段;
- 表达式2: 如果该字段为null后的替换值。
-- 给所有的数学加5分 SELECT math + 5 FROM student; -- 查询math + english的和 SELECT *, math + english AS 总成绩 FROM student;
- 使用四则运算计算一些列的值: 一般只会进行数值型的计算
-
条件查询:
select 字段名1, 字段名2, ... from 表名 [where 条件];
比较运算符 说明 > 、< 、<= 、>= 、= 、<> <>在SQL中表示不等于,在mysql中也可以使用!=,没有== BETWEEN...AND 在一个范围之内,如:between 100 and 200
相当于条件在100到200之间,包头又包尾IN(集合) in里面的每个数据都会作为一次条件,只要满足条件的就会显示
集合表示多个值,使用逗号分隔LIKE 占位符
_:单个任意字符
%:多个任意字符模糊查询 IS NULL 查询某一列为NULL的值,不能写=NULL and 或 && 与,SQL中建议使用前者,后者并不通用 or 或 || 或 not 或 ! 非 -- 查询年龄大于20岁 SELECT * FROM student WHERE age > 20; -- 查询年龄小于等于20岁 SELECT * FROM student WHERE age <= 20; -- 查询年龄等于20岁 SELECT * FROM student WHERE age = 20; -- 查询年龄不等于20岁 SELECT * FROM student WHERE age != 20; SELECT * FROM student WHERE age <> 20; -- 查询年龄大于等于20 小于等于30 SELECT * FROM student WHERE age >= 20 && age <=30; SELECT * FROM student WHERE age >= 20 AND age <=30; SELECT * FROM student WHERE age BETWEEN 20 AND 30; -- 查询年龄22岁,18岁,25岁的信息 SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25 SELECT * FROM student WHERE age IN (22,18,25); -- 查询英语成绩为null -- SELECT * FROM student WHERE english = NULL; 不对。null值不能使用 =(!=)判断 SELECT * FROM student WHERE english IS NULL; -- 查询英语成绩不为null SELECT * FROM student WHERE english IS NOT NULL; -- 查询姓“马”的有哪些? SELECT * FROM student WHERE NAME LIKE '马%'; -- 查询姓名第二个字是“化”的人 SELECT * FROM student WHERE NAME LIKE "_化%"; -- 查询姓名是3个字的人 SELECT * FROM student WHERE NAME LIKE '___'; -- 查询姓名中包含“腾”的人 SELECT * FROM student WHERE NAME LIKE '%腾%';
4.3 排序查询
- 语法:
ORDER BY 排序字段1 排序方式1, 排序字段2 排序方式2, ...
- 排序方式:
-
单列排序: 只按某一个字段进行排序,单列排序。
ASC
:升序(默认值);DESC
:降序。
-- 查询所有数据,使用年龄降序排序 SELECT * FROM student ORDER BY age DESC;
-
组合排序: 同时对多个字段进行排序,如果第1个字段相等,则按第2个字段排序,依次类推。
ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序 SELECT * FROM student ORDER BY age DESC, math ASC;
-
4.4 聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值null。
聚合函数 | 作用 |
---|---|
count(列名) 一般选择非空的列,如主键 count(*):计算记录的行数 | 计算该列有多少条记录 |
max(列名) | 计算最大值 |
min(列名) | 计算最小值 |
sum(列名) | 计算和 |
avg(列名) | 计算平均值 |
-- 查询学生总数
SELECT COUNT(id) AS 总人数 FROM student;
SELECT COUNT(*) AS 总人数 FROM student;
-- 查询数学成绩总分
SELECT SUM(math) AS 总分 FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) AS 平均分 FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) AS 最高分 FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) AS 最低分 FROM student;
聚合函数对于null的记录不会统计,建议如果统计个数则不要使用有可能为null的列,但如果需要把null也统计进去呢?
IFNULL(列名, 默认值)
: 如果 列名
不为空,返回这列的值。如果为null,则返回 默认值
。
-- 查询id字段,如果为null,则使用0代替
SELECT IFNULL(id, 0) FROM student;
我们可以利用IFNULL()函数,如果记录为null,给个默认值,这样统计的数据就不会遗漏。
SELECT COUNT(IFNULL(id, 0)) FROM student;
4.5 分组查询
-
语法:
GROUP BY 分组字段 [HAVING 条件];
-
分组原理:
GROUP BY
将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。-
不使用聚合函数,按
性别
将学生分成2组:-- 按性别进行分组,求男生和女生数学的平均分 SELECT * FROM student GROUP BY sex;
-
使用聚合函数,按
性别
将学生分成2组:-- 按照性别分组。分别查询男、女同学的数学平均分 SELECT sex, AVG(math) FROM student GROUP BY sex;
注意: 当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的,如上例中的字段sex。
实际上是将每组的math求了平均,返回每组统计的结果:
-- 按照性别分组。分别查询大于25岁的男、女同学的人数 SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex;
-
-
HAVING 与 WHERE 的区别:
查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据,以下代码是错误的:SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex WHERE COUNT(*) > 2;
正确写法: 使用HAVING
-- 对分组查询的结果再进行过滤 SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(*) > 2;
子句 作用 where 子句 1. 对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤再分组。
2. where后面不可以使用聚合函数having子句 1. having子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。
2. having后面可以使用聚合函数-- 按照性别分组。分别查询男、女同学的平均分,人数。要求:分数低于70分的人,不参与分组 SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex; -- 按照性别分组。分别查询男、女同学的平均分,人数。要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人 SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; SELECT sex, AVG(math), COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
面试题: 有如下订单表orders表数据如下所示:
执行如下SQL语句,运行结果是?SELECT product, SUM(price) FROM orders GROUP BY product WHERE SUM(price) > 30;
答: 运行有误,group by后面不能出现where,使用having,查得总价大于30的各类产品。
4.6 分页查询
- 语法:
LIMIT offset, length;
是一个MySQL"方言"- offset: 起始行数,从0开始计数,如果省略,默认就是0;
- length: 每页查询的行数。
-- 查询学生表中数据,从第3条开始显示,显示6条。 SELECT * FROM student LIMIT 2,6;
- 公式:
开始的索引 = (当前的页码 - 1) * 每页显示的条数
-- 每页显示5条记录 SELECT * FROM student LIMIT 0,5; -- 第1页 SELECT * FROM student LIMIT 5,5; -- 第2页 SELECT * FROM student LIMIT 10,5; -- 第3页
注意:
- 如果第一个参数是0可以省略写:SELECT * FROM student LIMIT 5;
- 最后如果不够显示一页,有多少显示多少。
5 DCL:管理数据库用户及其权限
5.1 使用者
DBA(Database Administrator,数据库管理员):我们现在默认使用的都是root用户,超级管理员,拥有全部的权限。但是,一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以,我们应该可以根据不同的项目建立不同的用户,分配不同的权限来管理和维护数据库。
5.2 创建用户
-
语法:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
- ‘用户名’:将创建的用户名;
- ‘主机名’:指定该用户在哪个主机上可以登陆,如果是本地用户可用
localhost
,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
; - ‘密码’:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。
注:用户名和主机名要加单引号。
-
操作:
-
创建user1用户,只能在localhost这个服务器登录mysql服务器,密码为123:
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-
创建user2用户可以在任何电脑上登录mysql服务器,密码为123:
CREATE USER 'user2'@'%' IDENTIFIED BY '123';
注:创建的用户名都在mysql数据库中的user表中可以查看到,密码经过了加密。
-
5.3 删除用户
- 语法:
DROP USER '用户名'@'主机名';
- 操作:
DROP USER 'user2'@'%';
5.4 授权用户
- 语法:
GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
- GRANT…ON…TO:授权关键字;
- 权限:授予用户的权限,如CREATE、ALTER、SELECT、INSERT、UPDATE等。如果要授予所有的权限则使用ALL;
- 数据库名.表名:该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用
*
表示,如*.*
; - ‘用户名’@‘主机名’:给哪个用户授权(有2对单引号)。
- 操作:
-
给user1用户分配对test这个数据库操作的权限——创建表,修改表,插入记录,更新记录,查询:
GRANT CREATE, ALTER, INSERT, UPDATE, SELECT ON test.* TO 'user1'@'localhost';
-
给user2用户分配所有数据库的所有表的权限:
GRANT ALL ON *.* TO 'user2'@'%';
-
5.5 撤销授权
-
语法:
REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';
- REVOKE…ON…FROM:撤销授权的关键字;
- 权限:用户的权限,如CREATE、ALTER、SELECT、INSERT、UPDATE等,所有的权限则使用ALL;
- 数据库名.表名:对哪些数据库的哪些表,如果要取消该用户对所有数据库和表的操作权限则可用
*
表示,如*.*
; - ‘用户名’@‘主机名’:给哪个用户撤销。
-
操作:
撤销user1用户对test数据库所有表的操作的权限:REVOKE ALL ON test.* FROM 'user1'@'localhost';
5.6 查看权限
-
语法:
SHOW GRANTS FOR '用户名'@'主机名';
-
操作:
SHOW GRANTS FOR 'user1'@'localhost';
注:usage是指连接(登陆)权限,建立一个用户,就会自动授予其usage权限(默认授予)。
5.7 修改用户密码
-
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
-
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
5.8 修改管理员密码
- 知道旧密码:
mysqladmin -uroot -p password 新密码
- 忘记旧密码:
- 需要管理员运行该cmd,停止mysql服务:
net stop mysql
- 使用无验证方式启动mysql服务:
mysqld --skip-grant-tables
- 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功:
USE mysql; UPDATE USER SET PASSWORD = PASSWORD ('新密码') WHERE USER = 'root';
- 关闭两个窗口;
- 打开任务管理器,手动结束
mysqld.exe
的进程; - 启动mysql服务;
- 使用新密码登录。
- 需要管理员运行该cmd,停止mysql服务: