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
/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
​
*/
​
案例1：查询价格在60到70之间的书籍信息
​
SELECT
*
FROM
tb_book
WHERE
price BETWEEN 60 AND 70;
​
​
3.in
/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③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');
​






/*
=或<>不能用于判断null值
is null或is not null 可以判断null值
​*/
​
案例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：排序查询
/**
语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;
​
​
特点：
1、asc代表的是升序，可以省略
desc代表的是降序
​
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
​
3、order by子句在查询语句的最后面，除了limit子句
​
**/
​
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;
​
```

分组查询

```
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
​
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
​
特点：
1、sum、avg一般用于处理数值型
  max、min、count可以处理任何类型
2、以上分组函数都忽略null值
​
3、可以和distinct搭配实现去重的运算
​
4、count函数的单独介绍
一般使用count(*)用作统计行数
​
5、和分组函数一同查询的字段要求是group by后的字段
​
*/
​
​
#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;

效果图参考