一、SQL概述
什么是SQL?
SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统(RDBMS)。 SQL 的范围包括数据插入、查询、更新和删除,数据库模式创建和修改,以及数据访问控制。可以应用到所有关系型数据库中,但有些数据库都有一些属于自己的语句,其他数据库不支持。
语法要求
SQL语句在RDBMS当中是逐条进行的。
SQL语句可以单行或多行书写,以分号“;”结尾
SQL语句不区分关键字的大小写,不管是SELECT或者select都不会报错,表和列名也一样,但为了区分建议关键字使用大写。表名称在Linux系统上区分大小写
SQL语句中插入到表中的数据是区分大小写的,例如:computer、Computer或COMPUTER,三者是不一样的
SQL语句中含有字符串和日
期时,需要使用单引号(‘)将字符括起来,表示这是一个字符串,SQL语句中数字不需要任何符号标识,直接写就好。
SQL语句中单词之间需要用到半角空格或换行符来进行分隔,否则会报错
命名规则
在标准SQL中我们只能用半角英文、数字和下划线作为数据库、表和列的名称。
名称必大多数以英文半角字母开头
在同一个数据库中不那个有两个相同名称的表,在一个表中也不能创建两个名称相同的列。
数据类型
INT:整型,默认长度为11;
INT UNSIGNED:无符号整型。
FLOAT:单精度浮点型
DOUBLE:双精度浮点型,与c语言有点相似如:douoble(5,2)则表示最多5位,保留两位小数;
CHAR:固定长度字符串类型;例:char(5)就表示必须有长度为5位字符串,当字符串不够长度时会补足空格
VARCHAR:可变长度字符串类型;例:定义varchar(20),当输入数据不足定义长度时不会另行补足空格,输入多少就得到多少
TEXT:长文本数据
BLOB:二进制形式数据
DATA:日期值 YYYY-MM-DD
TIME:时间值或持续时间 HH:MM:SS
YEAR:年份值 YYYY
DATATIME:混合日期和时间值 YYYY-MM-DD HH:MM:SS
MySQL登录命令
常用登录命令:mysql -h localhost -P 3306 -u root -p password
解析
-h localhost
参数-h代表的是主机的地址(host),localhost表示此用户只能在本机访问,连接本地时可以省略,当远程连接用户时,主机地址不可省略
-P 3306
-P(注意大写)代表的是用户端口号,3306是默认端口,当使用默认端口时参数可以省略,当端口不是默认端口就不可省略
-u root
-u 指定的是登录的用户名,默认使用的用户名是root ,此参数一般不能省略
-ppassword
-p表示登录用户的密码,,-p与密码之间不能使用空格,-p后面可以不跟密码,回车之后会出现输入密码的提示,但密码不可见
各参数之间除了mysql要在最前面以外,其余顺序不固定
退出当前用户
exit和quit
如果需要退出当前用户但不退出MySQL环境在cmd命令行提示符中生效,我在mysql 5.7自带环境中会直接关闭整个程序
二、SQL语句分类
根据RDBMS赋予的指令种类不同 ,SQL语句可以分为以下三类:
DDL(数据定义语言)用来创建或者删除村塾数据用的数据库以及数据库中的表等对象。
CREATE:创建数据库和表等对象 ;
DROP: 删除数据库和表等对象 ;
ALTER: 修改数据库和表等对象的结构;
DML(数据操纵语言) 用来对表中的记录进行增删改。
INSERT:向表中插入新的数据;
UPDATE:更新表中数据;
DELETE:删除表中数据;
DQL(数据查询语言):用来查询表中记录
SELECT:查询表中的数据;
FROM :指定表名列表
WHERE:指定查询条件
GROUP BY:分组查询
HAVING:指定分组后条件
ORDER BY:排序查询
LIMIT:分页查询
DCL(数据控制语言)用来确定或者取消对数据库中的数据进行更改,除此之外,还可以用来对RDBMS的用户是否有权限操作数据库中的对象进行设定。
COMMIT:确认对数据库中的数据进行的变更;
POLLBACK:取消对数据库中的数据进行的变更;
GRANT:赋予用户操作权限;
REVOKE:取消用户操作权限;
三、基本操作
查看所有数据库:SHOW DATABASES;
查看MySQL命令行所在的数据库:SELECT DATABASE();
切换到指定数据库:USE 数据库名称;
查看当前数据库中的所有表:SHOW TABLES;
查看MySQL语句警示信息:SHOW WARNINGS;
四、数据库操作
数据库的创建
在创建表之前需要创建储存表的数据库,目前已知晓两种方法可以创建数据库:
运行CREATE DATABASE语句就可以在mysql上建立数据库了。
语法:(各单词之间用半角空格隔开)
CREATE DATABASE 数据库名称 ;
例:CREATE DATABASE chen;
在原先语法上添加 IF NOT EXISTS(如果不存在),在数据库创建时,如果已经存在数据库chen,继续使用CREATE DATABASE就会报错,而使用IF NOT EXISTS则不会报错;
语法:
CREATE DATABASE [ IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集名] [COLLATE 校验规则];
例:CREATE DATABASE IF NOT EXISTS chen;
意义:如果mysql不存在相关数据库,则执行语句创建数据库chen,如果mysql已经存在相关数据库,则忽略此语句。
查看数据库的建造信息
mysql中利用show还可以查看数据库的创造信息
语法:
SHOW CREATE DATABASE 数据库名;
或者(两者显示当时有所不同)
SHOW CREATE DATABASE 数据库名 \G(G要使用大写)
可以从建造信息看出建造数据库chen时所用的编码设置为默认的latn1模式
数据库的删除
语法格式:【】括号内表示可选
DROP DATABASE [IF EXISTS] 数据库名;
例:DROP DATABASE IF EXISTS chen;
一般加上IF EXISTS比较好,可以避免报错。
数据库编码
在MySQL中会为创建的每个数据库指定一个字符编码。如果在创建数据库时没有设定数据库编码,则会自动选择一个默认的字符编码,这个默认的字符编码可以在MySQL的配置文件my.ini中进行配置。在MySQL8.0中默认字符集修改为utf-8,本5.7版本默认编码则是latin1即ISO-8859-1
创建字符库时指定字符编码
语法格式:
CREATE DATABASE [IF NOT EXISTS] 数据库名称 DEFAULT CHARACTER(字符编码) SET 字符编码名称 COLLATE 校验规则名称 [DEFAULT ENRYPTION='N'](默认加密)
事例:
在MySQL命令行中创建名称为xiang_kun的数据库,指定数据库的默认字符编码为UTF-8,校验规则为utf-8_unicode_ci,不使用MySQL加密技术。
注意:在MySQL中不能utf-8只能输入成utf8
查看数据库xiang_kun的字符编码格式。
问题:添加上DEFAULE ENRYPTION='N'会报错,说是需要查看手册,获取使用次语句的方式,下来有待求证。
查看数据库默认字符编码的语句
语法格式:
SHOW VARIABLES(变量) LIKE '%CHARACTER_SET_DATABASE%';
修改数据库的字符编码
语法格式:
ALTER DATABASE 数据库名称CHARACTER SET 字符编码名称 COLLATE 校验方式名称
事例:将数据库chen的编码格式改为utf-8mb4,校验规则修改为utf8mb4_unicode_ci.
四、表的操作
表的创建
创建表时必须指定数据表的名称,首先需要使用“USE 数据库名称”来指定创建表的操作在那个数据库中进行,或者可以直接使用“数据库名.表名”的形式来创建数据表,
如:在数据库chen中创建数据表,利用CREATE TABLE语句。数据表的创建至少需要定义好一列的名称和类型来完成表的创建,不然会报错
语法:语句可以写多行最后以分号结束
CREATE TABLE [IF NOT EXISTS] 数据库名.表名
(列名1 数据类型 [该列所需约束],
列名2 数据类型 [该列所需约束],
列名3 数据类型 [该列所需约束],
……
[该表约束1],[ 该表约束2],……);
在创建数据表时需要指定数据表中每一列的名称和类型,列的名称和数据类型用()括起来,列名称和数据类型由空格分隔,多个列之间用“,”逗号分隔,
数据表主键的设立
主键(primary key)可以定义表中的一列或多列,一般称为单列主键和多列联合主键,能够唯一标识表中的一行记录,主键列数据必须唯一且不能为空
单列主键
单列主键值包含数据表中的一个字段,可以在定义字段的同时定义主键,也可以定义完所有列之后定义主键。
在定义列的同时定义主键
语法格式:
字段 数据类型 PRIMART KEY [默认值]
在创建数据表时,将id字段定义为主键,此时id字段列的数据唯一且不能为空,能唯一标识 111数据表中的一行记录。
定义完数据表中所有数据之后,定义主键
语法格式:
PRIMARY KEY(字段名)
查看表结构
DESC 表名;
Field:数据表中的每个字段
Type:数据表中字段的数据类型
Null:数据表中当前字段值是否可为空
Key:PRY表示当钱列是主键列
Default:表示当前是否有默认值,同时会显示当前列的,默认值为多少
Exter:与当前列相关的附件信息
删除表
DROP TABLE [IF EXISTS] 表名;
删除表时,表中数据全部都会被删除
修改表
添加列:
给cx表添加k列
ALTER TABLE 数据表名称 ADD(列名 数据类型,
列名 数据类型,列名 数据类型);
添加多列,用逗号隔开。
修改列的数据类型:
修改cx表的K类型为varchar(520)
ALTER TABLE 数据表名称 MODIFY 字段名称 新数据类型;
修改列名和字段类型:
修改cx表中的K列名为kun
ALTER TABLE 数据表名CHANGE 原列名 新列名 修改后列的数据类型;
注意:修改后的数据类型,不能与原数据发生冲突
删除列:
删除cx表中的cd列
ALTER TABLE 数据表名 DROP 列名;
修改表名称:
修改cx表名为cxk
ALTER TABLE 原表名RENAME TO 修改后的表名;
五、数据操作
插入数据
语法结构:(指定输入字段,只需要保证插入数据顺序与定义列顺序对应)
INSERT INTO 表名(列名1,列名2,……)
VALUES
(值1,值2,……值n),
(值1,值2,……值n),
……;
或(没有指定要插入的列,表示创建时按列的顺序插入值)
INSERT INTO 表名
VALUES
(值1,值2,……值n),
(值1,值2,……值n),
……;
插入多条数据,用逗号隔开,字符串和日期型数据应该包含在单引号中,插入数据大小应该在指定范围内
选择添加数据字段可以不按照顺序但输入的数据要和选择的字段名称对应
将查询结果放到另一个表中
INSERT 语句不仅支持向数据表中直接插入数据,还可以支持向数据表中,插入SELECT语句的查询结果。
语法格式:
INSERT INTO 需要插入数据的数据目标表名 (字段1,字段2,……,字段n)
SELECT
(数据来源表中字段1,字段2……,字段n)
FROM 数据来源表名
[WHERE 使用select语句查询的条件限制]
修改数据
UPDATE 表名 SET 列名1=值1,……列名n=值n [WHERE 条件]
当省略WHERE语句时,更新数据表中的全部数据。
删除数据
DELETE FROM 表名 [WHERE 条件];
省略WHERE语句时,删除指定表全部数据。
六、查询(DQL)
SELECT 字段列表
handler 一行一行查询
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
注意:上列所示也是DQL 语句的编写顺序
基本查询
查询多个字段
SELECT 字段1,字段2,…… FROM 表名;
或
SELECT *FROM 表名;
*通配符,表示查询返回所有字段。
设置别名(非必要)
SELECT 字段1 [AS]别名1,字段2[AS] 别名2…… FROM 表名 [AS] 别名;
别名只是一个为了增加程序可读性而产生的一个临时名称,也可以给表设置别名。
给表设置别名后,因为FROM 语句执行在SELECT语句之前,所以可以直接使用别名.字段名来编写
去除重复记录
SELECT DISTINCT 字段 FROM 表名
条件查询
基本语法:
SELECT 字段名 FROM表名WHERE 条件列表;
条件可以一个或者多个
条件符
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<>或!= | 不等于 |
BETWEEN…AND… | 在某个范围之内(含最小、最大值),相当于>= && <= |
IN(……) | in之后的列表中的值,能满足其一就可,条件之间用“,”隔开 |
LIKE 占位符 | 模糊匹配(”_“匹配单个字符,“%”匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能:连接两个条件 |
AND或&& | 并且(多个条件同时成立) |
OR或|| | 或者(多个条件任意一个成立) |
NOT或! | 非,不是 |
举例说明
查询stu表中姓名以张开头,年龄大于18,不是男性,或者编号是五位的所有记录
select * from stu where sname LIKE’张%’AND age >18 AND gender !='女';
'张%'也可以换成‘张_ _’,但是如果换了的话,就匹配不到张三这个记录了;
查询stu表中姓名以张开头,年龄大于18,不是男性,或者编号是五位的所有记录
select * from stu where sname LIKE '张%'AND age >18 AND gender !='女'OR sid LIKE '____' ;
在编译器中,模糊查询LIKE 后面的‘_’不能用空格隔开,不然会显示没有满足记录。
聚合函数
介绍
通常以列为一个整体,进行纵向计算,
常见的聚合函数
函数 | 功能 |
count | 统计数量(不为NULL的行数) |
max | 最大值(如果指定列为字符,按照字符顺序取最大值) |
min | 最小值(如果指定列为字符,按照字符顺序取最小值) |
avg | 平均值(如果指定列不是数值类型,结果为0) |
sum | 求和(如果指定列不是数值类型,结果为0) |
SELECT 聚合函数(字段列表) FROM 表名;
聚合函数大小写都可以,但和字段列表之间不能添加空格
NULL值不参与聚合函数运算
事例:求stu表中age列的数量
求stu表中男性的且大于18岁的数量
分组查询
语法结构:
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名称 [HAVING 分组后的过滤条件]
WHERE与HAVING 的区别
执行时机不同:WHERE是分组之前对分组字段进行过滤,不满WHERE条件,不参与分组;而HAVING是分组后对结果进行过滤。
判断条件不同:WHERE只是对字段进行判断,不能对聚合函数进行判断,而HAVING中可以使用聚合函数。
执行顺序:WHERE>聚合函数>HAVING
进行分组查询之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
人数和名字对不上,只会显示数据表中满足条件的第一行数据,所以说无任何意义。
事例:
将stu表中的男性和女性总人数分组查询出来
将基本查询语句写出来
SELECT * FROM stu GROUP BY gender;
因为要查询所有男女性的值,根本控制显示的是GROUP BY 后的需要分组字段的名称(好像只会显示分组字段的值,如果需要显示其他字段需要自己添加。)还不确定
尽量写成
SELECT gender FROM stu GROUP BY gender;
因为需要计数,所有在将聚合函数count(gender)加上去
SELECT gender,count(gender)FROM stu GROUP BY gender;
“gender,count(gender)”是查询字段
查询emp表中提成大于300的岗位,并根据岗位分组,获取岗位内员工大于1的部门;
排序查询
SELECT 字段列表 FROM 表名 [WHERE 条件]
ORDER BY
字段名1 排序方式1,字段名2 排序方式2,……,字段名n 排序方式n ;
注意:如果是多字段排序,只有当第一个字段有值相同时,才在此基础上根据第二个字段排序
排序字段也可以写成数字的形式,表示以表中某一列排序 例:order by 3,就是以表中第三列 进行排序,但是数字不能超过本表列数,否则会报错
排序方式
ASC:升序(默认值)
DESC:降序
事例
将stu表中的人员按照年龄进行升序排序,NULL会处于最高处。
根据年龄对stu表中的数据进行升序排序,年龄相同,再根据编号进行降序排序
番外:
今天遇到一个很奇怪的问题,在数据类型里定义的原本是varchar类型,当在我在Navicat 中利用图形化界面直接输入编号数据之后,发现编号顺序直接更改了,利用排序出来的结果也是错乱的,可能是因为原本的数值类型与输入的数据类型不相符,导致出现了错误,但我不知道错误的排序原理是什么
经过查询资料,我知道了解决方法,就是在排序查询后加入算数运算符和一个数字之后就可以按数值进行排序,但这只是解决方法,后续我还需要去了解其中原理
select * from order by sid +1 asc;
分页查询(LIMIT)
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
用来限定查询结果的起始行和总行数
注意
起始索引默认从0 开始,当使用时起始索引=(查询页码-1)*每页显示的记录数
分页查询是数据库的方言,在MySQL中是LIMIT。
如果查询的是第一页数据,起始索引可以省略,直接简写为LIMIT 查询记录数;
LIMIT还可以指定查询的起始行,假如,你要查询第五行到第十行的数据
SELECT * FROM stu LIMLT 4,6;
如果查询第二页,显示4条记录,起始索引就为5.
查询性别为男,且年龄在10到60岁(包含)以内的前五个员工信息,对查询结果按照年龄进行升序查询,年龄相同按编号进行降序查询。
注意:此语句是先进行的排序查询,后进行的分页查询,如果顺序不对会报错。
DQL执行顺序
DQL语句编写顺序:见查询开头
DEL语句执行顺序:
FROM 指定查询哪张表的数据
WHERE 指定查询的条件
GROUP BY 指定分组
HAVING 指定分组之后的条件
SELECT 指定查询要返回哪些字段
ORDER BY 指定排序
LIMIT 指定分页显示
七、数据控制(DCL)
查询用户
USE mysql;
SELECT * FROM user;
在mysql数据库中用户的信息,用户所具有的权限信息都存储在系统mysql数据库中的user表中
通过查询可以看到默认有三个用户,其中root用户是我们正在使用的,权限最大
第一字段的Host指定是,在mysql当中删除和创建用户,需要用户名的主机地址同时定位
localhost 表名当前数据库只能在本机访问,User是用户名,后面是用户所具有的权限。
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
'用户名'@'主机名' 两者与@之间不能添加空格不然会报错
如果要在任意设备都可以访问某个数据库,就需要将localhost改为LIKE函数也在用的通配符%
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
将用户root的密码123456改为1234
mysql_native_password是一种加密方式,可以通过mysql数据库user表中的plugin字段查询
删除用户
删除user表中,只能在本机访问的cxk用户
DROP USER '用户名'@‘主机名’;
八、权限控制
常用权限
权限 | 说明 |
ALL,ALL PRIVILEGES | 所有权限 |
CREATE | 查询数据 |
SELECT | 插入数据 |
INSERT | 修改数据 |
UPDATE | 删除数据 |
DELETE | 修改表 |
ALTER | 删除数据库/表/视图 |
DROP | 创建数据库/表 |
查询用户权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
如果要给所有的数据库或表赋予或撤销权限,可以使用通配符*进行替换
多个权限之间用逗号隔开