MySQL数据库简单应用

MySQL数据库简单应用

1、数据库概述

1.1、什么是数据库

  • 提供存储数据的介质，能够持久化处理数据

1.2、什么是持久化数据

  • 将数据从内存存储到磁盘文件

1.3、数据库类型

  • 关系型数据库

  • 面向对象型数据库

  • nosql数据库

  • ......

2、关系型数据库

  • mysql（重点）

  • oracle（重点）

  • mssql

  • db2

  • 其他

2.1、什么是关系型数据库

数据库就是一个按照数据结构来组织，存储和管理数据的仓库

  • 关系型数据库的组成结构

    • 数据库

    • 数据表

3、MySQL数据库

3.1、概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

3.2、特点

  • mysql有开源版本和收费版本，你使用开源版本是不收费的

  • mysql支持大型数据库，可以处理上千万记录的大型数据库

  • Mysql使用标准的SQL数据库语言形式

  • Mysql在很多系统上面都支持

  • Mysql对PHP，PYTHON都有很好的支持当然其他的语言也支持比如JAVA,C

  • Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

4、MySQL的安装

  • 下载安装压缩包解压（解压目录不要带空格和中文）

  • 配置环境变量

    • 变量名：MYSQL_HOME

    • 变量值：解压的安装包路径（eg：C:mysqlmysql-8.0.15-winx64）

    • path里添加：%MYSQL_HOME%in（注意：没有分号）

  • 生成data文件

    • 以管理员身份运行cmd，进入C:mysqlmysql-8.0.15-winx64in 下执行命令：mysqld --initialize-insecure --user=mysql，在C:mysqlmysql-8.0.15-winx64目录下生成data目录

  • 启动服务

    • net start mysql

  • 以超级管理员身份登录

    • mysql -uroot -p

参考资料https://www.cnblogs.com/reyinever/p/8551977.html

  • MySQL如何设置密码

    • 用UPDATE直接编辑user表

      • 首先登录MySQL。

      • mysql> use mysql;

      • mysql> update user set password=password('123') where user='root' and host='localhost';

      • mysql> flush privileges;

参考资料https://www.cnblogs.com/mrhonest/p/10881646.html

5、MySQL解决中文乱码问题（5.0版本）

默认情况下，mysql不支持中文，需要将mysql的默认的编码集改为：utf-8

如何修改编码集

  • 直接将my.ini文件（文件已存网盘）复制到mysql的安装目录

  • 重启mysql服务

6、MySQL的卸载

  • 通过mysqld -remove卸载MySQL服务

7、sql语句基础

7.1、sql的分类

  • DML：数据操作语言，它是对表记录的操作(增、删、改)！)

  • DCL：数据控制语言（理解）

  • DDL：数据定义语言：主要由create、 alter、 drop和truncate四个关键字完成

  • DQL：数据查询语言

7.2、DQL语句

  • 查询所有列

 SELECT * FROM 表名;/*其中*表示查询所有列*/

  • 查询指定列

 SELECT 列1 [, 列2, ... 列N] FROM 表名;

  • 完全重复的记录只一次

    当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时很少会有这种情况，但只查询一列（或几列）时，这总可能就大了！

 SELECT DISTINCT * | 列1 [, 列2, ... 列N] FROM 表名;
/*查询员工表的工资，如果存在相同的工资只显示一次！*/
 SELECT DISTINCT sal FROM emp;

  • 列运算：数量类型的列可以做加、减、乘、除运算

 SELECT sal*1.5 FROM emp;
 SELECT sal+comm FROM emp;

  • 字符串类型可以做连续运算

 SELECT CONCAT('$', sal) FROM emp;

  • 转换NULL值

    有时需要把NULL转换成其它值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这时希望把NULL当前0来运算。

 SELECT IFNULL(comm, 0)+1000 FROM emp;
   --> IFNULL(comm, 0)：如果comm中存在NULL值，那么当成0来运算。  

  • 给列起别名

    你也许已经注意到了，当使用列运算后，查询出的结果集中的列名称很不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了

 SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;
   --> 其中AS可以省略

  • 条件控制（条件查询）

    与UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录

SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');

  • 模糊查询 当你想查询姓张，并且姓名一共两个字的员工时，这时就可以使用模糊查询

SELECT * FROM emp WHERE ename LIKE '张_';
--> 模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。
--> 上面语句查询的是姓张，名字由两个字组成的员工。
SELECT * FROM emp WHERE ename LIKE '___'; //姓名由3个字组成的员工

  • 如果我们想查询姓张，名字几个字的员工时就要使用“%”了。

SELECT * FROM emp WHERE ename LIKE '张%';
--> 其中%匹配0~N个任意字符，所以上面语句查询的是姓张的所有员工
SELECT * FROM emp WHERE ename LIKE '%阿%';
--> 千万不要认为上面语句是在查询姓名中间带有阿字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到。
SELECT * FROM emp WHERE ename LIKE '%';
--> 这个条件等同与不存在，但如果姓名为NULL的查询不出来！

  • 排序

    • 升序

     SELECT * FROM emp ORDER BY sal ASC;
  --> 按sal排序，升序！
  --> 其中ASC是可以省略的

    • 降序

     SELECT * FROM emp ORDER BY comm DESC;
  --> 按comm排序，降序！
  --> 其中DESC不能省略

    • 使用多列作为排序条件

    SELECT * FROM emp ORDER BY sal ASC, comm DESC;
  --> 使用sal升序排，如果sal相同时，使用comm的降序排

  • 聚合函数：聚合函数用来做某列的纵向运算。

  • COUNT

SELECT COUNT(*) FROM emp;
--> 计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
--> 云计算emp表中comm列不为NULL的记录的行数

  • MAX

 SELECT MAX(sal) FROM emp;/*查询最高工资*/

  • MIN

SELECT MIN(sal) FROM emp;/*查询最低工资*/

  • SUM

 SELECT SUM(sal) FROM emp;/*查询工资合*/

  • AVG

SELECT AVG(sal) FROM emp;/*查询平均工资*/

  • 分组查询 分组查询是把记录使用某一列进行分组，然后查询组信息。

 //查看所有部门的记录数。
 SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
  --> 使用deptno分组，查询部门编号和每个部门的记录数
 SELECT job, MAX(SAL) FROM emp GROUP BY job;
 --> 使用job分组，查询每种工作的最高工资

  • 组条件

    组条件只能是分组字段或分组函数

 /*以部门分组，查询每组记录数。条件为记录数大于3*/
 SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

  • limit子句(方言)： LIMIT用来限定查询结果的起始行，以及总行数。

//查询起始行为第5行，一共查询3行记录，其中4表示从第5行开始，其中3表示一共查询3    行。即第5、6、7行记录。
SELECT * FROM emp LIMIT 4, 3;
// 一页的记录数：10行，查询第3页（公式：(当前页-1) * 每页记录数）
select * from emp limit 20, 10；

  • 补充

7.3、DML语句

  1. 插入数据

  • INTERT INTO 表名(列名1,列名2, ...) VALUES(列值1, 列值2, ...);

在表名后给出要插入的列名，其他没有指定的列等同与插入null值。所以插入记录总是插入一行，不可能是半行。 在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应

  • INTERT INTO 表名 VALUES(列值1, 列值2)

没有给出要插入的列，那么表示插入所有列。 值的个数必须是该表列的个数。 值的顺序，必须与表创建时给出的列的顺序相同。

  • 批量添加数据，条件：数据表结构要一致

    • insert into 表名 select * from 表名;

在数据库中所有的字符串类型，必须使用单引，不能使用双引！日期类型也要使用单引！

/*插入所有列*/
INSERT INTO stu(
  number, name, age, gender
)
VALUES(
  'ITCAST_0001', 'zhangSan', 28, 'male'
);

/* 插入部分列，没有指定的列默认为NULL值*/
INSERT INTO stu(
  number, name
) VAKLUES(
  'ITCAST_0002', 'liSi'
)

/*不给出插入列，那么默认为插入所有列！值的顺序要与创建表时列的顺序相同*/
INSERT INTO stu VALUES(
  'ITCAST_0003', 'wangWu', 82, 'female'
);

  1. 修改数据

  • UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ... [WHERE 条件]

  • 条件(条件可选的)：

条件必须是一个boolean类型的值或表达式：UPDATE t_person SET gender='男', age=age+1 WHERE sid='1'; 运算符：=、!=、<>、>、<、>=、<=、BETWEEN...AND、IN(...)、IS NULL、NOT、OR、AND

  1. 删除数据

  • DELETE FROM 表名 [WHERE 条件];

  • TRUNCATE TABLE 表名：TRUNCATE是DDL语句，它是先删除drop该表，再create该表。而且无法回滚！！！

7.4、DDL语句

1、数据库

  • 查看所有数据库：SHOW DATABASES

  • 切换（选择要操作的）数据库：USE 数据库名

  • 创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名[CHARSET=utf8]

  • 删除数据库：DROP DATABASE [IF EXISTS] 数据库名

  • 修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

2、表

  • 创建表： CREATE TABLE [IF NOT EXISTS] 表名( 字段名 数据类型 约束条件， 字段名 数据类型 约束条件， ... 字段名 数据类型 约束条件， );

  • 查看当前数据库中所有表名称：SHOW TABLES;

  • 查看指定表的创建语句：SHOW CREATE TABLE 表名(了解);

  • 查看表结构：DESC 表名;

  • 删除表：DROP TABLE 表名;

  • 修改表：前缀：ALTER TABLE 表名

    • 修改之添加列： ALTER TABLE 表名 ADD ( 列名 列类型, 列名 列类型, ... );

    • 修改之修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据)：ALTER TABLE 表名 MODIFY 列名 列类型;

    • 修改之修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

    • 修改之删除列：ALTER TABLE 表名 DROP 列名;

    • 修改表名称：ALTER TABLE 原表名 RENAME TO 新表名;

7.5、数据库约束

  • 作用：保证数据的完整性和合理性

  • MySQL支持四种约束

    • NOTNULL：非空约束，指定某列不能为空

    • UNIQUE：唯一 约束，指定某列或者几列组合不能为空

    • PRIMARY KEY：主键约束，指定该列的值唯一地标识该条记录

      • 主键必须包含唯一的值

      • 主键列不能包含NULL值

      • 每个表应该都一个主键，并且每个表只能有一个主键

      • 主键约束既可以使用列级约束，也可以使用表级约束

    • FOREIGN KEY：外键约束，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性，语法：CONSTRAINT 外键名 FOREIGN KEY(外键列名) REFERENCES 关联表名(关联表的主键) ，eg：

      /*
*教师表
*/
CREATE TABLE Teacher(
Tid VARCHAR(10) PRIMARY KEY,
Tname VARCHAR(10));*

/*
*学生表
*/
CREATE TABLE Student(
Sid VARCHAR(6) PRIMARY KEY,
Sname VARCHAR(10),
Sage DATETIME,
Ssex VARCHAR(10),
STid VARCHAR(10),
CONSTRAINT fk_Student_Teacher FOREIGN KEY(STid) REFERENCES Teacher(Tid) 
);

      • 外键必须是另一表的主键的值(外键要引用主键！)

      • 外键可以重复

      • 外键可以为空

      • 一张表中可以有多个外键

  • 约束写法

    • 列级约束

    • 表级约束

7.6视图用法

概述

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。

创建视图

create view 视图名 as select 字段名 from 表名;

参考资料https://www.cnblogs.com/zzwlovegfj/archive/2012/06/23/2559596.html

7.7、索引用法

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

创建索引

CREATE INDEX 索引名 ON 表名 (column_name);

8、多表查询

8.1、多表查询分类

  • 合并结果集(了解)

  • 连接查询

  • 子查询

合并结果集

  • 要求被合并的表中，列的类型和列数相同

  • UNION，去除重复行

  • UNION ALL，不去除重复行

连接查询分类

8.2、内连接

* 方言：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
* 标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
* 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
* 内连接查询出的所有记录都满足条件。

8.3、外连接

  • 左外

SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL

  • 左外自然

SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

  • 右外

SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为NULL

  • 右外自然

SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

  • 全链接：可以使用UNION来完成全链接

8.4、子查询

查询中有查询（查看select关键字的个数！）

1、子查询出现的位置：

* where后作为条件存在
* from后作为表存在(多行多列)

2、子查询条件

  • 单行单列

SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)

  • 多行单列

SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)

  • 单行多列

SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)

  • 多行多列

SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

  • 多表查询时的条件顺序

    • where

    • group by

    • having

    • order by

    • limit

9、事务管理

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

9.1、事务的四大特征（ACID）

  • 原子性(atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  • 一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。

  • 隔离性(isolcation)： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

  • 持续性(durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

9.2、MYSQL 事务处理的两种方法

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务

  • ROLLBACK 事务回滚

  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交

  • SET AUTOCOMMIT=1 开启自动提交

记得快乐
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Y-wee/p/13507531.html