MySQL学习资料

撰写时间： 2019-08-22 总共： 5274 字 转载请注明： BY-SA 4.0（除特别声明或转载文章外）

MySQL

数据库综述

  • 数据库（Database）以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。
  • 数据库管理系统（Database Management System）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
  • 数据库通常分为层次式数据库、网络式数据库和关系式数据库三种。不同的数据库是按不同的数据结构来联系和组织的。

常见的关系数据库有：MySQL、Access、SQL Server、Oracle。

MySQL综述

  • MySQL是开放源代码的数据库 任何人都可以修改MySQL数据库的源代码，并且任何人都能以任何目的来使用该数据库。
  • MySQL的跨平台性 Windows、Unix、Linux、MacOS等操作系统上运行。
  • 价格优势 免费，任何人都可以使用。
  • 功能强大且使用方便 MySQL是一个真正的多用户，多线程的sql数据库服务器。它是以客户机、服务器结构的实现，由一个服务器守护程序mysqld和很多不同的客户程序和库组成，它可以快速，有效，安全的处理大量的数据。MySQL主要目标是快速，健壮和易用。
  • 最初开发者为-瑞典的MySQL AB公司，08年被Sun公司收购，09年Sun被甲骨文收购

MySQL的安装

查看官网

选择软件安装，一路next也行吧

  1. 下载官网安装zip包
  2. 解压到某个位置
  3. 配置环境变量 将bin目录添加到系统path环境变量里
  4. 创建my.ini文件（bin同级目录下）
# my.ini文件
[mysqld]
   	port=3306
   	basedir=D:\\mysql-8.0.15-winx64 #（注意双斜杠）
   	max_connections=20
   	max_connect_errors=10
   	character-set-server=UTF8MB4
   	default-storage-engine=INNODB
   	default_authentication_plugin=mysql_native_password
[mysql]
   	default-character-set=UTF8MB4
[client]
   	port=3306
   	default-character-set=UTF8MB4
  1. 管理员权限打开cmd（Powershell）

#进入这个位置（D:\mysql-8.0.15-winx64）的bin目录

cd D:\mysql-8.0.15-winx64\bin

mysqld -install 
#（可以在mysqld -install + 自定义服务名(比如MySQL8)）提示成功

mysqld --initialize --console

# 生成data目录（在跟bin同级目录）（保存数据库文件的地方，你后面新建数据库也保存在这里）
#（打印出密码）
# data下包含一个.err后缀的文件，里面有root初始密码，在 localhost 后面#（复制密码）
  1. 查看是否安装成功
# 查看版本
mysql -V
  1. mysql服务的启动和停止
# 选择查看 计算机->管理->服务 
net start 服务名
# 如果在上面设置过服务名就是那个服务名 没有就是MySQL
net stop 服务名

MySQL的登录退出

# 1.mysql自带客户端（限于root用户）
# 2.windows 等 命令行

# 登录：
mysql -h 主机名 -P 端口号 -u 用户名 -p（密码）

# 输入初始密码 

# 本机直接 
mysql -u root -p 就ok
		
# 进入mysql，更改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

# 如果没有更改密码默认策略（在my.ini里已经修改）不能使用这个
SET PASSWORD = '*******(密码)';


# MySQL8.0后请使用alter修改用户密码，因为在MySQL8.0以后的加密方式为caching_sha2_password，
# 如果使用update修改密码会给user表中root用户的authentication_string字段下设置newpassowrd值，当再使用
alter user 'root'@'localhost' identified by 'newpassword' 
# 修改密码时会一直报错，必须清空后再修改，
# 因为authentication_string字段下只能是MySQL加密后的43位字符串密码，其他的会报格式错误，
# 所以在MySQL8.0以后能修改密码的方法只能是：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY  '密码';

# 退出：
exit
ctrl+c

MySQL常见命令

#【查看版本】
select version();
#【查看当前所有数据库】
show database;
#【使用某个库】
use 库名;
#【查看当前库的所有表】
show tables;
#【查看其他库的所有表】
show tables from 库名;
#【创建表】
	create tabale 表名(
		列名 列类型,		
		列名 列类型,
		...
	);
#【查看表结构】
desc 表名;
# 【数据库备份】
mysqldump

mysqldump -u 用户名 -p 密码 [-hlocalhost] 数据库名 > 输出的文件.sql

MySQL语法规范

  1. 不区分大小写，建议关键字大写，表名，列名小写
  2. 最好分号结尾ヾ(≧O≦)〃嗷~
  3. 每条命令根据需要，可以进行缩进换行等
  4. 注释：# 注释 或者 – 注释 或者 /* 注释 */

使用Navicat连接管理

略

SQL语言分类

SQL语言共分为四大类：

  • 数据查询语言DQL (Data Query Language)
  • 数据操纵语言DML (Data Manipulation Language)
  • 数据定义语言DDL (Data Definition Language)
  • 数据控制语言DCL (Data Control Language)

数据查询语言DQL

基本结构是由SELECT 子句FROM子句 WHERE子句组成的查询块等

SELECT 表字段名
FROM 表
WHERE 条件 -- 作用于表或视图的条件
GROUP BY 分组字段 -- 常和函数使用
HAVING 条件 -- 作用于分组记录的条件
ORDER BY 排序字段

执行顺序：from -> where -> group by -> having-> order by -> select

注意：

  • group by 通常和聚合函数(avg(),count()…)一起使用 ,经常先使用group by关键字进行分组，然后再进行集合运算。
  • group by与having 一起使用，可以限制输出的结果，只有满足条件表达式的结果才会显示。
  • having和where的区别：两者起作用的对象不一样，where作用于表或视图，是表和视图的查询条件。having作用于分组后的记录，用于选择满足条件的组。

数据操纵语言DML

主要是 INSERT UPDATE DELETE 对数据进行操纵

用于操作数据库对象中包含的数据，操作的单位是记录。

-- 插入
-- 插入所有字段
INSERT INTO tbl_name 
VALUES(expr, expr)
-- 插入指定字段
INSERT INTO tbl_name (id,name...)
VALUES(expr,expr)

-- 更新
UPDATE table_reference 
SET col_name1 = expr1,... 
WHERE where_condition

-- 删除
-- 删除符合条件的记录
DELETE FROM tbl_name WHERE 条件
-- 删除所有记录 
DELETE * FROM tbl_name

数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象:

表、视图、索引、同义词、聚簇等等。

DDL对这些对象和属性的管理和定义具体表现在

CREATE、DROP 和ALTER,TRUNCATE上，不会对具体的数据进行操作。

DDL操作是隐性提交的！不能rollback

-- 创建
-- 创建数据库
CREATE DATABASE database_name
-- 使用数据库
use database_name;
-- 创建表
CREATE TABLE tbl_name(
	col_name1 数据类型,
    col_name2 varchar(255)等等,
    ....
);
-- 创建索引
CREATE INDEX index_name
ON tbl_name (col_name)

-- 查看表
show tables;

-- 删除
-- 删除数据库
DROP DATABASE database_name
-- 删除表
DROP TABLE tbl_name 
-- 删除索引 （各个数据库不大一样）
-- mysql
ALTER TABLE tbl_name DROP INDEX index_name

-- 修改数据表定义及属性
-- 添加列
ALTER TABLE table_name
ADD column_name datatype
-- 删除列
ALTER TABLE table_name 
DROP COLUMN column_name
-- 改变列的数据类型
ALTER TABLE table_name
ALTER COLUMN column_name datatype

-- 删除表中所有数据,并重置自增属性等 区别于DELETE
TRUNCATE TABLE tbl_name

注意：

区别于DELETE：

  • delete可以有条件删除，用法：delete from 表名 where 条件
  • truncate是删除表中所有数据（还会重置自增长字段）
  • delete可以rollback，truncate不能
  • truncate删除速度比delete快

数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制 数据库操纵事务发生的时间及效果，对数据库实行监视等。

DCL的操作对象(用户),此时的用户指的是数据库用户

基本包括，GRANT,REVOKE,ROLLBACK,COMMIT

  • GRANT：授权,允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限

  • REVOKE ：回收权限，可以废除某用户或某组或所有用户访问权限

  • ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点,回滚命令使数据库状态回到上次最后提交的状态

  • COMMIT [WORK]：提交,在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看 到所做的事情，别人只有在最后提交完成后才可以看到。 提交数据有三种类型：显式提交、隐式提交及自动提交。下面分 别说明这三种类型。

  • (1) 显式提交 用COMMIT命令直接完成的提交为显式提交。

  • (2) 隐式提交 用SQL命令间接完成的提交为隐式提交。

    这些命令是： ALTER,AUDIT,COMMENT,CONNECT,

    CREATE,DISCONNECT,DROP,EXIT,

    GRANT,NOAUDIT,QUIT,REVOKE,RENAME

  • (3) 自动提交 若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后， 系统将自动进行提交，这就是自动提交。

-- 创建用户
# 用户只能在指定的IP地址上登录:
create user user_name@IP_addr identified by 'password';

# 用户可以在任意IP地址上登录:
create user user_name@'%' identified by 'password';

# 查看指定用户的权限:
show grants for user_name@IP_addr

-- 删除用户
drop user user_name@IP_addr

-- 授权
GRANT   权限1,权限2...
ON    对象类型   对象名
TO     用户1,用户2...
(WITH GRANT OPTION) ;

-- 授权所有权限
GRANT ALL ON database.* TO User_name

-- 例如
-- 把查询表的权限授给用户User1
GRANT SELECT
ON TABLE tbl_name
TO User1;
 
-- 把查询表和修改id的权限授给用户User2
GRANT UPDATE(id),SELECT
ON TABLE tbl_name
TO User2;
 
-- 把表的INSERT权限授予User3用户，并允许将此权限再授予其他用户
GRANT INSERT 
ON TABLE tbl_name 
TO User3
WITH GRANT OPTION;

-- 回收权限
-- 采用级联收回的策略,在收回User1权限的同时也收回了User1授予User2的权限。
REVOKE 权限1,权限2...
ON   对象类型  对象名
FROM 用户1,用户2...;

-- 例如
-- 回收上面User1 的查询权限回收
REVOKE SELECT
ON TABLE tbl_name
FROM User1;

MySQL结语

注意查询语句的使用，where子句的实现以及一些聚合函数的使用

LIKE 通配符等等

在windows上的mysql安装仅为学习，实际应多使用Linux下的MySQL，注意CentOS7 后期的默认数据库更改，

不再为MySQL。

关于在CentOS 7.6 下我安装的过程

奖励一下