MySQL基础

1、初识MySql
2、MySql使用
3、MySql操作指令
存储引擎
用户管理
备份、还原

1、初识MySql

前端是用来页面展示的，就是用来进行人机交互的。可是前端没有凭空拥有数据，数据从哪里来呢，于是就想出了一个办法，把数据放到数据库里去，从数据库里拿取数据。

一个想法就诞生了【前端】 ------------获取数据-----------> 【数据库】

但就目前的情况来看，我们是通过下面这种方式来获取数据的

前端想要什么数据，如何获取数据，这称为了一个难题，所以很多事情就要由连接点来完成

【前端】 <-------------> 【连接点】 <-------------> 【数据库】

连接点（后台）
- 连接数据库，【JDBC、MyBatis】
- 连接前端（控制视图跳转，给前端传递数据）【Servlet，Spring，SpringMVC】

几种苦力工：

只会写代码，只会基本的数据库语句，只是基本的码农，基本混饭吃。
会MySql底层、操作系统，数据结构与算法。当一个不错的程序员。
离散数学，编译原理，实战经验。优秀的程序员。

数据库分类

关系型数据库：（SQL）

MySql、Oracle、Sql Server、DB2、SQLlite
通过表和表，行列之间的关系进行数据的存储。关系模型可以简单理解为二维表格模型。
Oracle、MySQL、DB2、Microsoft SQL Server、Microsoft Access等都是关系型数据库。

非关系型数据库：（NoSQL） Not Only SQL

Redis、MongDB
以对象存储，通过对象自身的属性来决定

DBMS、DB

DBMS（Database Management System）
- 数据库的管理软件，可以管理和操作数据。如：Oracle、MySql 、Redis 等。
DB
- 是一个数据集合，是在硬盘上的数据文件。
- 功能：存数据、管理数据

SQL与MySQL

SQL(structure query language)不是一种专利语言，而且存在一个标准委员会，他们试图定义可供所有DBMS使用的SQL语法，但事实上任意两个DBMS实现的SQL都不完全相同。

多条SQL语句必需要用分号隔开，MySQL像大多数DBMS一样，不需要分号（但是命令行里需要）。

SQL语句不区分大小写，但是习惯把关键字大写。

select检索排序时，a 和 A 相同。如果需要改变这种行为，需要管理员帮助设置。

MySql简介

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL 是最好的 RDBMS 应用软件之一。（Relational Database Management System，关系数据库管理系统）
体积小、速度快、总体拥有成本低，开放源码
中、小型网站或者有些大型网址都在使用，可以集群

MySql安装

选压缩包下载（其中MySql5.7和8.0是稳定版），下载后解压，我把它解压到了D:\Steep\mysql-5.7.19
path中加环境变量，我加D:\Steep\mysql-5.7.19\bin

加配置文件：根目录下新建my.ini，我的配置如下：

[mysqld]
# 设置时区
default-time_zone = '+8:00'
character-set-server=utf8
# 设置mysql的安装目录
basedir=D:\Steep\mysql-5.7.19\
# 设置mysql的数据存放目录
datadir=D:\Steep\mysql-5.7.19\data\
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
skip-grant-tables

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysql.server]
# 服务端使用的字符集默认为utf8
default-character-set=utf8
[mysql_safe]
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

安装服务：管理员身份运行cmd，切换到mysql的bin目录下输入mysqld -install，出现Service successfully installed.就行了
初始化数据，会在D:\Steep\mysql-5.7.19自动生成data目录
```
mysqld --initialize-insecure --user=mysql
```
命令net start mysql启动服务，再mysql -u root -p登录，密码为空。

设置密码为 123456，输入

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

设置密码为123456。刷新权限flush privileges;

删除或者注释掉my.ini的最后一句skip-grant-tables
重新关闭、启动mysql服务。最后用账号和密码登录mysql，

mysql -u root -p1234456

MySql卸载

如果安装的时候是压缩包安装的，卸载方法如下：

net stop mysql停止服务
直接删除安装目录D:\Steep\mysql-5.7.19
需要手动删除服务sc delete 服务名，管理员运行命令窗口 sc delete MySql

SQLyog安装

下载，安装
破解，名称随意，证书8d8120df-a5c3-4989-8f47-5afc79c56e7c
连接，完成。

2、MySql使用

帮助

help

登录与退出

服务的启动与关闭（管理员身份运行）
```
net start mysql
net stop mysql
```

登录操作

C:\Users\17627>d:
D:\>cd D:\Portable\Mysql-5.6.24-winx64\bin
D:\Portable\Mysql-5.6.24-winx64\bin>mysql

C:\Users\17627>mysql -u root -p
Enter password:

退出
```
exit
quit
\q
```

MySql密码修改

一、用SET PASSWORD命令

首先登录MySQL。
格式：mysql> set password for 用户名@localhost = password('新密码');
例子：mysql> set password for root@localhost = password('123');

二、用mysqladmin

格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123

三、用UPDATE直接编辑user表

首先登录MySQL。
mysql> use mysql;
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;

四、修改密码

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';

五、忘记root密码的时候

以windows为例：

关闭正在运行的MySQL服务。
打开DOS窗口，转到mysql\bin目录: cd D:\Portable\mysql-5.6.24-winx64\bin
输入mysqld --skip-grant-tables 回车，就是启动MySQL服务的时候跳过权限表认证。
再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
输入mysql回车，如果成功，将出现MySQL提示符 >。
连接权限数据库： use mysql; 。
改密码：update user set password=password("root") where user="root";
刷新权限（必须步骤）：flush privileges;
退出 quit。
注销系统，再进入，使用用户名root和刚才设置的新密码登录。

错误及解决

一、--skip-grant-tables 的错误

错误原因：ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
flush privileges一下，再添加用户就可以了，

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO helei IDENTIFIED BY 'MANAGER' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

这个时候我们成功搞定了，再登录就可以了。

二、mysql Access denied for user root@localhost

在mysql的安装目录下编辑配置文件my.ini（D:\Portable\Mysql-5.6.24-winx64目录下）
D:\Portable\Mysql-5.6.24-winx64目录下没有my.ini。
把my-default.ini拷贝一份再重命名为my.ini
在[mysqld]这个条目下加入 skip-grant-tables 保存退出后重启mysql
以管理员的权限的命令窗口启动mysql后，出现服务名无效。（问题原因：mysql服务没有安装。）（在 mysql bin目录下以管理员的权限执行 mysqld -install命令）
以管理员的权限 net start mysql 开启Mysql服务。net stop mysql,关闭
这时候在cmd里面输入mysql -u root -p就成功了

继续按下面的流程走：

进入mysql数据库：
mysql> use mysql;
Database changed
给root用户设置新密码：

mysql> update user set password=password("新密码") where user="root";
Query OK, 1 rows affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
刷新数据库
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
退出mysql：
mysql> quit;
Bye
改好之后，再修改一下my.ini这个文件，把我们刚才加入的 "skip-grant-tables"这行删除，保存退出再重启mysql就可以了。

三、不用密码就可以登录

mysql -u root (先进入数据库)
USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';    (设置必须使用密码才能登录)
FLUSH PRIVILEGES;
exit;

3、MySql操作指令

扩展操作

在windows环境下cls清屏
只影响到客户端的编码方式，不影响到服务器端的编码方式。 set names gbk;

查看警告信息               mysql> show warnings;
查看当前用户和主机          mysql> select user();
查看时间                  mysql> select now();
查看数据库版本             mysql> select version();

MySql提示符

未登录客户端 mysql -uroot -proot --prompt 参数
已登录客户端（修改提示符） prompt 参数

参数的其中四种
- \D 完整的日期
- \d 当前数据库
- \h 服务器名称
- \u 当前用户

参数的使用示例

C:\Users\Atlantis>mysql -uroot -proot --prompt \h          <--输入
localhost
localhostprompt mysql>          <--输入
PROMPT set to 'mysql>'
mysql>prompt \u@\h \d>          <--输入
PROMPT set to '\u@\h \d>'
root@localhost (none)>use test;          <--输入
Database changed
root@localhost test>
root@localhost test>prompt \D          <--输入
PROMPT set to '\D'
Tue Mar 24 08:29:04 2020

分隔符

-- 指定分隔符
mysql> DELIMITER //
mysql> select now();
    -> //                  
-- 只有加分隔符后才能算结束。
-- 删除分隔符，相当于把分隔符改为 ";"
mysql> DELIMITER ;

MySql注释

-- 单行注释

/*
是
多行注释
呀
*/

用网格方式显示数据

select * from table_name\G; G要大写 表格 grid

数据库操作

-- 查看当前服务器下的所有数据库
show {databases|schemas};
-- 创建数据库
create {database|schema} [if not exists] db_name
[default] character set [=] charset_name;
-- 使用指定的数据库
use db_name;
-- 查看数据库的字符编码
show create database db_name;
-- 修改数据库编码
alter {database|schema} [db_name]
[dafault] character set [=] charset_name;
alter database character set utf8;
-- 显示当前数据库所有表
show tables;
-- 查看当前使用的是哪个数据库
mysql> select database();
-- 删除数据库
drop {database|schema} [if exists] db_name;

-- 看你的mysql现在提供什么存储引擎:
mysql> show engines;
-- 看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';

表的操作

-- 查看当前数据库的所有数据表
show tables;
-- 查看某一张（比如：mysql表）表的数据表
show tables from mysql;

-- 查看数据表的创建代码,包括引擎和字符编码
show create table table_name;
-- 查看表结构
show columns from tb_name;     {desc|describe} tbl_name;

-- 修改表的编码方式
alter table tb_name character set {gbk|utf8};
-- 查看索引
show indexes from tb_name;

创建表

create table [if not exists] table_name(
	column_name data_type,
....
);

创建主键自增和外键级联删除的表

create table users1(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(10) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE
);

创建主键为自增的表
设置自增的字段必须设为主键（如：id自增）

mysql> create table tb3(
    -> id smallint unsigned auto_increment primary key,
    -> username varchar(30) not null
    -> );
-- 插入记录   
mysql> insert tb3(username) values('Tom');    
mysql> insert tb3(id,username) values(default,'Tom');
mysql> insert tb3(username) values('Joshua')，('kay');

创建有唯一约束的表

mysql> create table tb4(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED
    -> );
-- 不能插入相同的username,username字段有唯一约束        
mysql> INSERT tb4(username,age) VALUE('Tom',21);
mysql> INSERT tb4(username,age) VALUE('Tom',23);

创建有默认约束的表

-- 性别sex为默认约束，默认3表示隐藏。
mysql> create table tb5(
    -> username VARCHAR(20) NOT NULL,
    -> gender ENUM('1','2','3') DEFAULT '3'
    -> );

数据表的属性操作

-- ====================================   约束操作   ======================================
-- 添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
PRIMARY KEY [index_type] (index_col_name,...);
  ALTER TABLE users2 ADD PRIMARY KEY (id);
-- 添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
  ALTER TABLE users2 ADD unique (username);
-- 添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
references  reference_definition
  ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
-- 添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name
{SET DEFAULT literal | DROP DAFAULT}
-- 删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;
-- 删除唯一约束
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;
-- 删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

-- ===============================   列和表的属性操作   ==================================
-- 修改列属性
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];
-- 添加自增列
ALTER TABLE tbl_name modify id smallint unsigned not null auto_increment;
-- 添加单列
ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];
-- 添加多列
alter table tbl_name add (col_name1 varchar(30), col_name2 varchar(30),...);
-- 删除列
ALTER TABLE tb_name DROP col_name1 [,DROP col_name2] ......
-- 数据表更名
① ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
② RENAME TABLE tbl_name TO new_table_name
     [,tbl_name2 TO new_table_name].......

外键

create table user(
    id            int unsigned auto_increment primary key comment '用户id',
    username      varchar(50) not null comment '用户名',
);

create table the_property(
    userId     int unsigned comment '对应用户id',
    name       varchar(20),
    constraint fk_user_property foreign key (userId) references user(id)
);
-- 数据类型要一样 int unsigned
-- fk_user_property 外键名
-- (userId) 字段名
-- user(id) 主表，列名

数据类型

【整型】TINYINT:1字节，SAMLLINT:2，MEDIUMINT:3，INT:4，BIGINT:8
【浮点型】 FLOAT[(M,D)]、DOUBLE[(M,D)]：M是数字总位数，D是小数点后面的位数

记录增、删、改

增加记录

INSERT users VALUES('Tom', '123'), ('Rose', '234');
insert users(name, pwd) values('Tom', '123');

INSERT users SET username='Tom', password='123';
INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...;

更新

UPDATE users SET age = age - 1, sex = 0 WHERE id % 2 = 0;

删除

DELETE FROM tbl_name [WHERE where_condition];
TRUNCATE TABLE `table_name`           -- 使自增归零
DELETE FROM `table_name`              -- 不会影响自增

操作符

distinct

select distinct id from users; # 选出所有不同的id
select distinct id, name from users; # distinct应用于所有的行(id和name)，除非给定的id和name都相同，否则所有的行都会被检索出来。
# select id, distinct name from users;   distinct只能放在最前面

limit

select * from users limit 5;  # 检索出[0,4]行（一共5行）
select * from users limit 5, 3;  # 检索出[5,7]行（一共3行）
select * from users limit 3 offset 1;  # 检索出[1,3]行（一共3行）

order

SELECT * FROM users ORDER BY age, id DESC; # 在age升序的基础上，id降序

group by

select gender, count(*) from users group by gender;  # 按性别(gender)分组，检索出数量
# 如果有null的话，null也算一个分组。
gender     count(*)
 男           4
 女           5

select gender, age from users group by gender, age;  # 按性别和年龄分组，性别和年龄一样的是一组

having

# where用来过滤行，而having过滤分组。
select gender from users group by gender having count(*) >= 5;  # 如果有4个男的，5个女的，结果就是女

order by

select gender from users group by gender having count(*) >= 2 order by count(*);
# 按升序排序，4个男的，5个女的
gender
  男
  女

查找格式

SELECT select_exp [,select_expr ...]
[
    FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | position} [ASC | DESC], ... ]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ... ]
    [LIMIT {[offset,] row_count | row_count OFFSET offset }]
]

其它

!=   <>    # 不等于的两种表示
BETWEEN 5 AND 10  # 包括5和10
IN (100, 103) # 100 和 103
100 OR 103  # IN 一般比 OR 要快

count(column)  # 查询这列的数量，不包括 null
count(*)    # 查询记录的条数
count(1)  和  count(*) # 其实这两个都一样

表的连接查询

table1
id      name
1        'a'
1        'b'
2        'c'
table2
id      age
1        4
1        5
2        6

# 笛卡尔乘积
select * from table1, table2; # 检索出的行数是table1和table2的乘积
# 结果有 9 条记录
table1.id    name    table2.id    age
... 9 条记录

natural join

对相同的列进行连接，并且使用笛卡尔乘积

select * from table1 natural join table2;
# 结果有 5 条记录(2*2 + 1*1 + 0*0)
id    name    age
1      'a'     4
1      'b'     5
1      'a'     4
1      'b'     5
2      'c'     6

inner join

select vend_name, prod_name, prod_price
from vendors inner join products on vendors.id = products.id;
# 和下面的where查询是一样的
select vend_name, prod_name, prod_price 
from vendors, products where vendors.id = products.id;

outer join

分为left out join和right outer join

表A记录如下：
aID　　　　　aNum
1　　　　　a20050111
2　　　　　a20050112
5　　　　　a20050115

表B记录如下:
bID　　　　　bName
1　　　　　2006032401
2　　　　　2006032402
8　　　　　2006032408

左外连接

select * from A left join B on A.aID = B.bID;
结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
5　　　　　a20050115　　　　NULL　　　　　NULL

右外连接

select * from A right join B on A.aID = B.bID
结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
NULL　　　　　NULL　　　　　8　　　　　2006032408

full join

全连接full join，语法为full join ... on ...，全连接的查询结果是左外连接和右外连接查询结果的并集。

select * from dept full join employee on dept.deptid = employee.deptid;
select * from dept full join employee on 1 = 1;

union

union中的每个查询的必须包含相同的列、表达式或聚焦函数。或者列之间可以隐式转换。

select age name from employee where age >= 59
union            # 会自动删除重复的行
select age name from employee where age < 33;
# 可以用where语句写
select age name from employee where age >= 59 and age < 33;

# 如果不想删除重复的行要使用 union all

# 排序：如果想要排序，要把order by写在最后，是对所有的查询结果进行排序。不允许一部分结果使用一种排序，另一部分结果使用另一种排序。

函数

md5函数

MD5('123456')

test> select * from name;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A          | B         |  
| tom%       | cat       |
| NULL       | 1234      |
+------------+-----------+

通配符

-- 结果为 1 代表存在，0 代表不存在
select 'joshua' like 'jo%';  # 结果是 1
-- 把 a 后面的 % 转义不做通配符
test> select * from name where first_name like '%a%%' escape 'a';
+-------------+------------+
| first_name  |  last_name |
+-------------+------------+
| tom%        |   cat      |
+-------------+------------+
%(百分号)代表任意个字符
_(下划线)代表任意一个字符

# 第三个字符是_的员工
select * from employees where name like '__#_%' escape '#';  
# 意思就是将#后面的_转义，不让它做通配符使用

test> select concat('蒋','潇','伟');                     蒋潇伟
test> select concat_ws('|','蒋','潇','伟');              蒋|潇|伟
test> select format(3.1415926,3);                       3.142
test> select lower('MySql');                            mysql
test> select left('MySql',2);                           My
test> select length('my name');                         7
-- trim 的用法
test> select trim(leading '?' from '??MySql???');       MySql???
test> select trim(trailing '?' from '??MySql???');      ??MySql
test> select trim(both '?' from '??My?Sql???');         My?Sql
-- 字符串的替换
test> select replace('??My??Sql???','?','');            MySql
-- 字符串的截取
test> select substring('mysql',1,2);                    my

-- 日期相关，MySQL首选的格式 yyyy-mm-dd
mysql> select date_add(now(),interval 39 day);
mysql> select date_add('2019/10/17',interval -39 day);
mysql> select datediff('2019-10-17','2019-12-31');
mysql> select date_format('2019-12-9','%y/%M/%D');  (y,m,d大小写无所谓);

自定义函数

删除自定义函数

mysql> drop function 函数名;       -- 格式
mysql> drop function thisTime;    -- 例子

创建自定义函数

1、自定义时间

-- 创建自定义函数
mysql> create function thisTime()
        -> returns varchar(50)                                     -- 表示要返回的类型
        -> return date_format(now(),'%Y年%m月%d日 %H秒:%i分:%s秒');  -- 表示要返回的内容
-- 使用自定义函数
mysql> select thisTime();

2、自定义除法

mysql> create function divide(num1 smallint,num2 smallint)
        -> returns float(3,2)
        -> return num1/num2;
mysql> select divide(8,5);

3、创建具有复合函数结构体的聚合函数

mysql> select * from users1;
+----+----------+
| id | username |
+----+----------+
|  4 | Joshua   |
|  5 | Mira     |
+----+----------+
mysql> desc users1;
+----------+------------------------+------+-----+---------+----------------+
| Field    | Type                   | Null | Key | Default | Extra          |
+----------+------------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned   | NO   | PRI |  NULL   | auto_increment |
| username | varchar(10)            | NO   |     |  NULL   |                |
+----------+------------------------+------+-----+---------+----------------+
mysql> create function adduser(name varchar(20))
    -> returns int unsigned
    -> begin
    -> insert users1(username) values(name);
    -> return last_insert_id();
    -> end
    -> //
-- ============================== 使用添加用户的函数 ===================== 

mysql> select adduser('Kay');
+----------------+
| adduser('Kay') |
+----------------+
|      6         |
+----------------+

mysql> select * from users1;
+----+----------+
| id | username |
+----+----------+
|  4 | Joshua   |
|  5 | Mira     |
|  6 | Kay      |
+----+----------+

存储过程与自定义函数的区别

存储过程	函数
实现的功能要复杂一些	针对性更强
可以返回多个值	只能有一个返回值
一般独立地来执行	可以作为其它SQL语句地组成部分来出现

存储过程

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

1、选择版本

mysql> create procedure v() select version();
mysql> call v();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+

2、通过id移除用户

test> CREATE PROCEDURE removeUserById(IN del_id INT)
    -> BEGIN
    -> DELETE FROM name WHERE id = del_id;
    -> END
test> CALL removeUserById(2);

3、添加和获取用户编号

test> DELIMITER //
test> CREATE PROCEDURE addAndGetUsersNum(IN one VARCHAR(10),IN two VARCHAR(10),OUT num TINYINT)
    -> BEGIN
    -> INSERT name(first_name,last_name) VALUES(one,two);
    -> SELECT COUNT(id) FROM name INTO num;
    -> END
    -> //
test> SELECT addAndGetUsersNum('GOOD','JOB',@num);

4、删除用户并获取信息

test> CREATE PROCEDURE del_userAndGetInfo(IN firstName VARCHAR(10),
    -> OUT deletedNums TINYINT,OUT leftUsers TINYINT)
    -> BEGIN
    -> DELETE FROM name WHERE first_name = firstName;
    -> SELECT ROW_COUNT() INTO deletedNums;
    -> SELECT COUNT(id) from name INTO leftUsers;
    -> END
test> CALL del_userAndGetInfo('Jiang',@del,@left);

全文本搜索

# 添加FULLTEXT索引
alter table users add fulltext (name); # 为name列添加

# 查询有joshua匹配的
select name from users where MATCH(name) Against('joshua');
# 使用查询扩展
select name from users where MATCH(name) Against('joshua' with query expansion);

布尔文本搜索

即使没有使用fulltext索引也可使用布尔文本搜索，但这样的速度会更慢。

布尔文本搜索可以使用下面的这些操作符：

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
>	包含，并且增加等级值
<	包含，并且减小等级值
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语（与单个词的列表不一样，它匹配整个短语，以便包含或排除这个短语）

# 匹配包含abc和def的行
select col from table1 where match(col) against('+abc +def' in boolean mode);

# 匹配包含abc或def的行
select col from table1 where match(col) against('abc def' in boolean mode);

# 匹配包含abc def这个词的行，而不是分开abc和def
select col from table1 where match(col) against('"abc def"' in boolean mode);

# 匹配abc和def，但是增加abc的等级（重要性），减少def的等级（重要性）
select col from table1 where match(col) against('>abc <def' in boolean mode);

# 匹配abc和def，并且降低def的等级
select col from table1 where match(col) against('+abc +(<def)' in boolean mode);

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

# 创建一个视图
CREATE VIEW vendorlocations AS
SELECT Concat (Rtrim(vend_name), '(', Rtrim(vend_country), ')')
AS vend_title
FROM vendors;
# 现在如果经常要使用这个格式的结果，就不需要每次都写了
# 下面执行查询操作
select * from vendorlocations;

# 查看创建的视图
SHOW CREATE VIEW viewname;

# 删除视图
DROP VIEW viewname;

# 更新视图
# 可以先使用 DROP 再使用 CREATE，或者直接使用下面这种
CREATE OR REPLACE VIEW

更新视图数据

视图的更新包括update delete insert。

视图的更新有很多限制，不过这没有关系，我们主要用视图来检索数据的。

事务

ROLLBACK

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
# 使用 ROLLBACK 回退到 START TRANSACTION 时的状态

COMMIT

START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
# 如果两条 DELETE 语句中出现了错误，则 COMMIT 不会更改事务内的操作。

SAVEPOINT

# 设置保留点
# 这样是为了支持回退部分事务处理，保留点越多越灵活。
SAVEPOINT delete1;
ROLLBACK TO delete1;

默认提交行为

# 更改默认的提交行为
# 默认的MySQL行为是自动提交所有更改。任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所作的更改立即生效。
SET autocommit = 0; # 设置为 0 (假) 表示不自动提交，即使使用 COMMIT 也不会自动提交。
# 直到 autocommit 设置为真才提交。

存储引擎

定义

MySQL中的数据用各种不同的技术存储在文件中。
这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

支持的存储引擎

MyISAM：早些年使用 InnoDB：默认使用 Memory CSV Archive

存储引擎对比

特点	MyISAM	InnoDB
事务支持	——	支持
事务安全	——	表锁
支持外键	——	支持
颗粒锁	支持	行锁
全文索引	支持	MySql5.7之后支持
表空间大小	较小	较大，约两倍

MyISAM：节约空间，速度快
InnoDB：安全性高，多表多用户操作

修改存储引擎

通过MySql配置文件实现
- default-storage-engine = InnoDB
通过创建数据表命令实现
- CREATE TABLE table_name(
  
  ........
  
  )ENGINE = InnoDB;
通过修改数据表命令实现
- ALTER TABLE table_name ENGINE [=] InnoDB;

用户管理

1、SQL命令操作

用户表：mysql.user ，就是对这张表进行增删改查。

-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码';

--删除用户
DROP USER 用户名
DELETE FROM `mysql`.`user` WHERE `Host` = '主机号' AND `User` = '用户名'; 

-- 修改当前用户的密码
SET PASSWORD = PASSWORD('密码');

-- 修改指定用户的密码
SET PASSWORD FOR 用户名 = PASSWORD('密码');

-- 重命名用户名
RENAME USER 当前的用户名 TO 新的用户名;

-- 用户授权所有权限，除了Grant权限
GRANT ALL PRIVILEGES ON *.* TO 用户名

-- 查询权限
SHOW GRANTS FOR 用户名
SHOW GRANTS FOR root@localhost

-- 撤销权限 哪些权限(*.* 代表所有权限) ,在那个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM 用户名;

2、SQLyog的可视化管理

备份、还原

拷贝物理磁盘：

直接拷贝物理磁盘文件（位置：MySql安装目录下的data文件中）

SQLyog：

用SQLyog导出，右键导出。SQLyog还原：直接鼠标拖入

使用命令行导出： mysqldump 命令

# 导出某数据库的某张表
mysqldump  -h本机   -u用户名    -p密码   数据库   表名   >导出的位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# 导出某数据库的多张表
mysqldump  -h本机     -u用户名  -p密码   数据库   表名1  表名2  >导出的位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student score >D:/b.sql

# 导出某数据库
mysqldump    -h本机    -u用户名 -p密码  数据库  >导出的位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql
-- 注：localhost代表本机，也可以用远端IP

# 导出所有数据
mysqldump -uroot -p123456 --all-databases > d:\mysql.sql

命令行导入：source命令

# 登录的情况下（建议登录后进行导入，防止不登陆情况下覆盖了原有的东西）
# linux下路径分隔符是"/"，windows下是"\"和"/"都行
source d:/a.sql

#不登录的情况
mysql -uroot -p123456 库名< d:/a.sql

posted on 2021-05-30 21:18 Jumpig 阅读(119) 评论(0) 收藏举报

刷新页面返回顶部

导航