MySQL的使用

目录

一，用户的管理

二，建库

三，建表

四，四大引擎的作用及区别

（1）.InnoDB存储引擎（支持事务）

（2）MyISAM存储引擎（不支持事务，效率会更快）

（3）MEMORY存储引擎

（4）Archive存储引擎

五，案例演示

前言：

1.对于系统开发来讲，有三个环境

（1）线网，生产环境：系统开发完毕，客户使用的环境

（2）测试环境：公式内部模拟客户现场，搭建的环境

（3）开发环境：本地电脑地址

2.权限的划分一般针对的是生产环境

一，用户的管理

我们需要来看一下mysql默认数据库里面的四张表（user，db，tables_priv，columns_priv）

（1）user表（用户层权限）

注意事项：登陆的时候验证Host,User,Password(authentication_string)也就是ip,用户名，密码是否匹配，匹配登陆成功将会为登陆者分配权限。

（2）db表（数据库层权限）

注意事项：db表之后会匹配Host,User然后会根据Db字段对应的表进行权限分配（运用语句：Select，Insert，Update，Delete，Create，Drop---------分别代表着查询，增加，更新，删除，创建，销毁）

（3）tables_priv表（表层权限）

注意事项：同上分配权限，但是它只有Table_priv和Column_priv两个字段来记录权限。

（4）columns_priv表（字段层权限）

注意事项：通过Host，Db，User，Table，Column来进行定位到字段层的一个权限分配

《演示图》

提示：在MySQL里面的表很多，切记不能删掉，否则MySQL数据库用不成

《代码演示》

SELECT * FROM `user`


创建用户
create user dxy;

设置密码
ALTER USER 'dxy'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
​

设置客户的权限
#语法：grant privileges on databasename.tablename to username@'host';

赋有所有权限
grant all on db_dengxiyan.* to dxy@'%';

只有查询权限
grant SELECt,DELETE db_xe.t_p1_user to xionger@'%';


查看所有权限
show grants for 'dxy'@'%';

以下权限可以被收回的
REVOKE DELETE on db_dengxiyan.* FROM dxy@'%';
grant all on db_dengxiyan.* to dxy@'%';

当某个库里的某一张表里的某一权限不能收回，
REVOKE UPDATe on db_dengxiyan.tb_book FROM dxy@'%';

 《效果图》

当没有给权限时会默认与一个权限，当给用户添加了权限时，所有权限将一并查出 

二，建库

方式一：

直接右键，新建数据库

注：默认的数据库编码集：utf8（即UTF-8），collate表示排序规则为utf8_general_ci

方式二：

代码创建 

建库
create database tb_dxy_0629;

查询版本（当版本的不同可能会产生乱码的现象）
SELECt version();

查询数据库
show databases;

三，建表

1.数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
1）字符类型：char varchar text blob
2）数值类型：int bigint float decimal
int -> int
bigint -> long
float -> 成绩
decimal -> 货币类型(精度,小数)
​
3）日期类型：date time datetime timestamp
​
date -> yyyy:MM:dd HH:mm:ss
time -> HH:mm:ss
datetime -> yyyy:MM:dd
timestamp（时间戳） -> 长整数

《代码演示》

CREATE TABLE user2(
#主键
id int PRIMARY key,
#不为空
name VARCHAr(15) not null,
#唯一约束
sex char DEFAULT '男',
birthday datetime,
code VARCHAr(18) UNIQUE
)
desc user2;


修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
​
修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

表的删除
DROP TABLE IF EXISTS book_author;

添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
​
删除列
ALTER TABLE book_author DROP COLUMN annual;
​
修改表名
ALTER TABLE author RENAME TO book_author;
​

四，四大引擎的作用及区别

（1）.InnoDB存储引擎（支持事务）

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。

（2）MyISAM存储引擎（不支持事务，效率会更快）

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

（3）MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

（4）Archive存储引擎

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

| **功 能**    | **MYISAM** | **Memory** | **InnoDB** | **Archive** |
| 存储限制   | 256TB        | RAM            | 64TB          | None        |
| 支持事物   | No              | No               | Yes             | No             |
| 支持全文索引 | Yes        | No               | No              | No             |
| 支持数索引   | Yes          | Yes             | Yes              | No            |
| 支持哈希索引| No           | Yes             | No               | No            |
| 支持数据缓存 | No          | N/A             | Yes              | No            |
| 支持外键     | No             | No              | Yes               | No            |

五，案例演示

案例演示

3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;
​
通用的写法：
DROP DAT
ABASE IF EXISTS 
CREATE DATABASE 新库名;
​
DROP TABLE IF EXISTS user2;
CREATE TABLE 表名();
​
#4.表的复制
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
​
SELECT * FROM Author;
SELECt * FROM copy2;
​
1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
​
2.复制表的结构+数据
CREATE TABLE copy2
SELECt * FROM author;
​
只复制部分数据
CREATE TABLE copy3
SELECt id,au_name
FROM author
WHERe nation='中国';
​
​
仅仅复制某些字段
CREATE TABLE copy4
SELECt id,au_name
FROM author
WHERe 0;





过滤查询案例

select
bname
from
tb_book
where bname='朝花夕拾-01';

​
模糊查询
​
查询工资>12000的员工信息
​
SELECt
*
FROM
tb_book
WHERe
price>70;


案例2：查询部门编号不等于90号的员工名和部门编号
SELECt
bname,
bid
FROM
tb_book
WHERe
bid<>12;
​
​
二、按逻辑表达式筛选
​
案例1：查询编号在1到10之间的书名、价格
SELECt
bname,
price
FROM
tb_book
WHERe
bid>=1 AND bid<=10;


案例2：查询部门编不是在1到10之间，或者价格高于80的书籍信息
SELECt
*
FROM
tb_book
WHERe
NOT(bid>=1 AND bid<=10) OR price>90;




三、模糊查询

案例1：查询书籍名中包含字符花的书籍信息
​
select
*
from
tb_book
where
bname like '%花%';


案例2：查询书籍名称中第二个字符为哈，第四个字符为的书籍名称和价格
select
bname,
price
FROM
tb_book
WHERe
bname LIKE '_哈_拾%';
​
​
​
案例3：查询书籍名中第二个字符为-的员工名
​
SELECt
bname
FROM
tb_book
WHERe
bname LIKE '_$花%' ESCAPE '$';





2.between and

​
案例1：查询价格在60到70之间的书籍信息
​
SELECt
*
FROM
tb_book
WHERe
price BETWEEN 60 AND 70;
​
​
3.in


案例：查询书籍的编号是2,或3，或10中的一个书籍名和书籍编号
select * from tb_book

方式一
SELECt
bname,
bid
FROM
tb_book
WHERe
bid = '2' OR bid = '3' OR bid ='10'; 
​
​方式二
SELECt
bname,
bid
FROM
tb_book
WHERe
bid IN( '2' ,'3','10');
​







​
案例1：查询没有价格的书籍名称和编号
SELECt
bname,
bid
FROM
tb_book
WHERe
price IS NULL;
​


例1：查询有价格的书籍名和书籍编号
SELECt
bname,
bid
FROM
tb_book
WHERe
price IS NOT NULL;
​


#安全等于 <=>
案例1：查询没有价格的书籍名称和编号
SELECt
bname,
price
FROM
tb_book
WHERe
price <=>NULL;


案例2：查询价格为66.99的员工信息
SELECt
bname,
bid
FROM
tb_book
WHERe
price <=> 66.99;


​
#is null pk <=>
​
IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=>   :既可以判断NULL值，又可以判断普通的数值，可读性较低
```

```
进阶3：排序查询

​
1、按单个字段排序（降序）
SELECt * FROM tb_book ORDER BY bid DESC;
​
#2、添加筛选条件再排序
​
案例：查询书籍编号>=10的书籍信息，并按书籍价格降序
​
SELECt *
FROM tb_book
WHERe bid>=10
ORDER BY price DESC;
​

#4、按别名排序
案例：查询员工信息 按年薪升序
​
SELECt *,salary*12*(1+IFNULL(commission_pct,0)) price
FROM tb_book
ORDER BY 年薪 ASC;
​
#5、按函数排序
案例：查询员工名，并且按名字的长度降序
​
SELECt LENGTH(last_name),last_name
FROM t_mysql_employees
ORDER BY LENGTH(last_name) DESC;
​
#6、按多个字段排序
​
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECt *
FROM t_mysql_employees
ORDER BY salary DESC,employee_id ASC;
​


排序练习

#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
​
SELECt last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM t_mysql_employees
ORDER BY 年薪 DESC,last_name ASC;
​
​
#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECt last_name,salary
FROM t_mysql_employees
​
WHERe salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
​
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
​
SELECt *,LENGTH(email)
FROM t_mysql_employees
WHERe email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
​
```

分组查询

```

​
​
#1、简单 的使用
SELECt SUM(salary) FROM t_mysql_employees;
SELECt AVG(salary) FROM t_mysql_employees;
SELECt MIN(salary) FROM t_mysql_employees;
SELECt MAX(salary) FROM t_mysql_employees;
SELECt COUNT(salary) FROM t_mysql_employees;
​
​
SELECt SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;
​
SELECt SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;
​
#2、参数支持哪些类型
​
SELECt SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;
SELECt SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;
​
SELECt MAX(last_name),MIN(last_name) FROM t_mysql_employees;
​
SELECt MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;
​
SELECt COUNT(commission_pct) FROM t_mysql_employees;
SELECt COUNT(last_name) FROM t_mysql_employees;
​
#3、是否忽略null
​
SELECt SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM t_mysql_employees;
​
SELECt MAX(commission_pct) ,MIN(commission_pct) FROM t_mysql_employees;
​
SELECt COUNT(commission_pct) FROM t_mysql_employees;
SELECt commission_pct FROM t_mysql_employees;
​
​
#4、和distinct搭配
​
SELECt SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;
​
SELECt COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;
​
​
​
#5、count函数的详细介绍
​
SELECt COUNT(salary) FROM t_mysql_employees;
​
SELECt COUNT(*) FROM t_mysql_employees;
​
SELECt COUNT(1) FROM t_mysql_employees;
​
效率：
MYISAM存储引擎下 ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
​
​
#6、和分组函数一同查询的字段有限制,employee_id是最小的那个
​
SELECt AVG(salary),employee_id FROM t_mysql_employees;

效果图参考