MySQL
文章目录
前言
提示:这里可以添加本文要记录的大概内容:
例如:随着人工智能的不断发展,机器学习这门技术也越来越重要,很多人都开启了学习机器学习,本文就介绍了机器学习的基础内容。
提示:以下是本篇文章正文内容,下面案例可供参考
一、MySQL语句
1. DDL操作数据库和数据表
1.1创建库
– 直接创建数据库
CREATE DATABASE 库名;
– 判断是否存在并创建数据库
CREATE DATABASE IF NOT EXISTS 库名字;
– 创建数据库db3并指定字符集为gbk
CREATE DATABASE db3 CHARACTER SET gbk;
– 查看所有的数据库
SHOW DATABASES;
– 查看某个数据库的定义信息
– 将db3数据库的字符集改成utf8
ALTER DATABASE db3 CHARACTER SET utf8;
– 删除db2数据库
DROP DATABASE db2;
DROP DATABASE IF EXISTS db2;
– 查看正在使用的数据库
SELECT DATABASE();
1.2创建表
创建数据表
CREATE TABLE 表名 (
列名 数据类型,
列名 数据类型,
…
列名 数据类型 );
常用数据类型
int:整数类型
double:小数类型
varchar(长度):字符串类型
date 表示日期类型 yyyy-MM-dd
查询所有的数据表
SHOW TABLES;
查询表结构
DESC 表名;
查看创建表的SQL语句
SHOW CREATE TABLE 表名;
快速创建一个表结构相同的表(复制表)
CREATE TABLE 表名 LIKE 其他表;
删除数据表
DROP TABLE 表名;
删除数据表(判断,如果存在则删除)
DROP TABLE IF EXISTS 表名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
修改表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称;
单独添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
修改某列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
删除某一列
ALTER TABLE 表名 DROP 列名;
2.DQL表数据的查询
2.1新增数据表
给指定列添加数据
INSERT INTO 表名(列名1, 列名2, …) VALUES (值1, 值2, …);
示例:INSERT INTO goods (NAME, price, sales_volume, produced_date) VALUES
(‘华为P40’, 5999, 1000, ‘2020-08-20’);
给全部列添加数据
INSERT INTO 表名 VALUES (值1, 值2, …);
批量添加数据(一次加入多条数据)
INSERT INTO 表名 VALUES (值1, 值2, …), (值1, 值2, …), (值1, 值2, …);
插入数据注意事项 列名和值的数量要对应。 值的类型列的类型要对应。 除了数值类型,其他数据类型的数据都需要加引号(单引双引都行,推荐单引)
2.2DML修改和删除表数据
修改表中的数据
UPDATE 表名 SET 列名1=值1, 列名2=值2, … [WHERE 条件];
修改语句中必须加条件,如果不加条件,则会将所有数据都修改。
删除表中的数据
DELETE FROM 表名 [WHERE 条件];
删除语句中必须加条件,如果不加条件,则会将所有数据都删除。
3.DQL表数据的查询
3.1DQL表数据查询-无条件查询
查询指定字段的数据
SELECT 字段名1, 字段名2, 字段名3 FROM 表名;
查询所有字段的数据
SELECT * FROM 表名;
去除重复查询
SELECT DISTINCT 列名1 FROM 表名;
计算列的值(四则运算)
SELECT 列名1 (+ - * /) 列名2 FROM 表名;
起别名查询
SELECT 列名1 AS 别名1, 列名2 AS 别名2 FROM 表名;
3.2DQL表数据查询-条件查询
条件查询语法
SELECT 列名列表 FROM 表名 WHERE 条件;
(一般的逻辑与运算省略)
取值范围
判断是否为空值
模糊查询语法
SELECT * FROM 表名 WHERE 字段名 LIKE ‘通配符字符串’;
MySQL通配符有两个:
%: 表示任意多个字符
_: 表示一个字符
3.3DQL表数据查询-排序查询
排序查询语法
SELECT 列名列表 FROM 表名 [WHERE 条件] ORDER BY 列名 排序方式;
排序方式:
ASC: 升序
DESC: 降序如果有多个排序条件,只有当前边的条件值一样时,才会判断第二条件。
4.扩展:蠕虫复制 (将一张表中的数据复制到另一张表中)
创建一张表结构相同的表将goods表中的数据复制到goods6表中
CREATE TABLE
goods6
goods6 LIKE goods;
INSERT INTO goods6 SELECT *FROM goods;
5.MySQL复杂查询
5.1 聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。
五个聚合函数
COUNT: 统计指定列记录数
average
函数名 | 意义 |
---|---|
COUNT() | SUM |
SUM() | 计算指定列的数值和 |
MAX() | 计算指定列的最大值 |
MIN() | 计算指定列的最小值 |
AVG() | 计算指定列的平均值 |
使用方法:
SELECT 字段名 FROM 表名;
SELECT 聚合函数(字段名) FROM 表名;
记录为NULL的不统计。 如果不是数值类型,那么计算结果为0。
5.2 分组
分组语法
SELECT * FROM 表名 GROUP BY 字段名;
使用分组时使用聚合函数,记得连分组一起查询
having与where的区别
having是在分组后对数据进行过滤 where是在分组前对数据进行过滤 having后面可以使用聚合函数
where后面不可以使用聚合函数
5.3 LIMIT语句
当我们浏览一些购物网站,比如京东,淘宝,返回的商品信息可能有几万甚至上百万条,服务器一次性把这么多的数据返回给浏览器合适么?
LIMIT语句作用:
LIMIT是限制的意思,LIMIT的作用就是获取部分查询的数据。
LIMIT语句格式
SELECT * FROM 表名 LIMIT offset, length;
offset是指偏移量,可以认为是跳过的记录数量,不写则默认为0。
length是指需要显示的总记录数
6.DCL 数据控制语言
用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等
6.1 创建用户
我们现在默认使用的都是root用户,超级管理员,拥有全部的权限。但是,一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以,我们应该可以根据不同的项目建立不同的用户,分配不同的权限来管理和维护数据库。
创建用户语句
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
关键字说明
1.用户名:将创建的用户名
2.主机名:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
3.密码:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器
6.2 用户授权
用户创建之后,基本没什么权限!需要给用户授权
授权格式
GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
关键字说明
1.GRANT 授权关键字
2.授予用户的权限,如SELECT,INSERT,UPDATE等。如果要授予所的权限则使用ALL
3.数据库名.表名:该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
4.'用户名'@'主机名'
: 给哪个用户授权
6.3 查看和撤销授权
撤销授权语法
REVOKE 权限1, 权限2... ON 数据库名.表名 FROM '用户名'@'主机名';
查看权限
SHOW GRANTS FOR '用户名'@'主机名';
6.4 删除用户和修改管理员密码
删除用户语法
DROP USER '用户名'@'主机名';
修改管理员密码:不需要登录MySQL
在DOS命令行: mysqladmin -uroot -p老密码 password 新密码
7. 数据库备份与还原(重点)
在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。
数据库备份和还原有两种方式
命令
图形化界面
命令备份与还原
备份格式 注意:这个操作不用登录
在DOS命令行:mysqldump -u用户名 -p密码 数据库 > 文件的路径
还原格式 注意:还原的时候需要先登录MySQL,并选中对应的数据库
SOURCE 文件的路径
图形化界面与还原
备份格式
还原格式
8.约束
什么是约束?
对表中的数据进行限定,保证数据的正确性、有效性、完整性!
约束通常是在创建表结构的时候创建
约束 | 说明 |
---|---|
PRIMARY KEY | 主键约束 |
UNIQUE | 唯一约束 |
NOT NULL | 非空约束 |
DEFAULT | 默认值约束 |
FOREIGN KEY | 外键约束 |
8.1 主键约束
主键的作用
用来区分表中的数据
主键的特点 主键必须是唯一不重复的值 主键不能包含NULL值
建表时创建主键
CREATE TABLE 表名 (
字段名 字段类型 PRIMARY KEY,
字段名 字段类型
);
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
AUTO_INCREMENT的开始值是1,如果希望修改起始值,请使用下列SQL语法
ALTER TABLE 表名 AUTO_INCREMENT = 10000;
8.2 唯一约束
唯一约束的作用
让字段的值唯一,不能重复。
CREATE TABLE 表名 (
字段名 字段类型 UNIQUE,
字段名 字段类型
);
8.3 默认值约束
默认值约束的作用
如果这个字段不设置值,就使用默认值。
CREATE TABLE 表名 (
字段名 字段类型 DEFAULT 值,
字段名 字段类型
);
8.4 外键约束
什么是外键
一张表中的某个字段引用其他表的主键,这个字段称为外键 主表: 主键所在的表,约束别人的表,将数据给别人用 副表/从表:
外键所在的表,被约束的表,使用别人的数据
新建表时添加外键
CREATE TABLE 表名 (
字段名 字段类型,
字段名 字段类型,
-- 添加外键约束
CONSTRAINT 外键约束名 FOREIGN KEY(外键字段名) REFERENCES 主表(主键字段名)
);
关键字解释:
CONSTRAINT: 表示约束外键约束名: 给外键约束取个名字,将来通过约束名可以删除这个约束 FOREIGN KEY(外键字段名):
指定某个字段作为外键 REFERENCES 主表(主键字段名) : 引用主表的主键的值
删除外键约束(一般不会使用)
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
8.5 外键的联级
有了外键约束后不能直接修改和删除主键数据
什么是级联操作
在修改和删除主表的主键时,同时更新或删除从表的外键值,称为级联操作
ON UPDATE CASCADE:级联更新
主表主键修改后,从表的数据也跟着修改
ON DELETE CASCADE:级联删除
主表主键删除后,从表数据也跟着删除
创建表添加外键时添加联级
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
CONSTRAINT ky_dep_id FOREIGN KEY(dep_id) REFERENCES department(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
9. MySQL多表查询
多表查询的分类
9.1 内连接查询
什么是内连接
多表查询时获取符合条件的数据
内连接效果
隐式内连接
看不到
JOIN
关键字,条件使用WHERE
指定
SELECT * FROM 表1, 表2 WHERE 条件;
显式内连接
使用INNER JOIN … ON语句, 可以省略INNER
SELECT * FROM 表1 INNER JOIN 表2 ON 表连接条件 WHERE 查询条件;
9.2 外连接查询
左外连接
使用LEFT OUTER JOIN … ON,OUTER可以省略
效果图
左外连接可以理解为:将满足要求的数据显示, 左表不满足要求的数据也显示。
右外连接
使用RIGHT OUTER JOIN … ON,OUTER可以省略
效果与左连接相反
9.3 子查询
什么是子查询
一个查询语句的结果作为另一个查询语句的一部分
SELECT 查询字段 FROM 表 WHERE 条件;
SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);
子查询需要放在()中 先执行子查询,将子查询的结果作为父查询的一部分。
子查询结果是单列,在WHERE后面作为条件
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
子查询结果是多行单列,结果集类似于一个数组,在WHERE后面作为条件,父查询使用IN运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
子查询结果是多列,在FROM后面作为虚拟表
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
10. 索引
什么是索引
是帮助数据库快速查询数据的技术。索引类似新华字典的索引目录,可以通过索引目录快速查到你想要的字快速查找数据。
10.1 创建索引
CREATE INDEX 索引名 ON 表名(字段名);
10.2 查看所有索引
SHOW INDEX FROM 表名;
10.3 删除索引
DROP INDEX 索引名 ON 表名;
10.4 复合索引(了解)
CREATE INDEX 索引名 ON 表名(字段名1, 字段名2);