MySQL教程

目录

1.数据库和数据表的创建及删除

2.数据表的增删改查

3.MySQL之ORDER BY

4.MySQL之union

5.MySQL之group by

6.inner join(内连接)

7.MySQL常用引擎

8.事务

9.锁

10索引

注意：转载请注明出处https://blog.csdn.net/qq_37573924/article/details/83629640

---

 

---

1.数据库和数据表的创建及删除

数据库的创建：比如创建一个名叫human的数据库。

create database human;

数据表的创建：比如在human数据库中创建一个名叫people的数据表，属性有id（设置为主键，自动递增，int）、race（意为种族，varchar）、complexion（意为肤色，varchar）。

use human;

create table people(id int unsigned auto_increment,race varchar(20),complexion varchar(20),primary key(id));

unsigned为无符号数。

auto_incremeny定义列为自增的属性，一般用于主键，数值会自动加1。

primary key关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

数据表的删除：删除people数据表。

drop table people;

数据库的删除：删除human数据库。

drop database human;

当然可以使用Navicat for MySql工具对数据库和数据表进行创建及删除。

2.数据表的增删改查

插入数据：

insert into people(race,complexion) value("蒙古人种","黄皮肤");

删除数据：

删除表中的所有数据。

delete from people;

 

根据条件删除表中的数据。

delete from people where id=1;

更新数据：

update people set race="蒙古人种",complexion="黄皮肤" where id=1;

查询数据表：

查询people数据表的所有内容。

select * from people;

条件查询，查询id为4的race。

select race from people where id=4;

3.MySQL之ORDER BY

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

建立a表，如下：

按grade升序，命令如下：

select grade from a order by grade desc;

结果如下：

按grade降序，命令如下：

select grade from a order by grade asc;

结果如下：

4.MySQL之union

其作用是将俩张表符合条件的值合并在一起。

首先先建俩张学生成绩表，分别为a表和b表。

a表如下：

b表如下：

union：

select name from a union select name from b order by name;

结果如下：

大家请仔细看a表和b表，name列共有9个值，却只列出了5个值，因为a表name列和b表name列有相同值，而此条语句会删除重复的数据。那如何让他们全部显示出来呢，请继续往下看。

union all：

select name from a union all select name from b order by name;

结果如下：

由上可见，union只选取俩张表不同的值，而union all则会把满足条件的值都显示出来。

union all和where语句

select name from a where grade=92 union all select name from b where sno=2 order by name;

结果如下：

5.MySQL之group by

GROUP BY 语句根据一个或多个列对结果集进行分组。

首先建立一个a表，如下：

列出各学科的最高成绩，代码如下：

select name,course,max(grade) as maxgrade from a group by course;

结果如下：

列出每位同学的平均成绩，代码如下：

select name,course,avg(grade) as avegrade from a group by name;

结果如下：

6.inner join(内连接)

inner join:获取俩个表中字段匹配关系的记录。

首先建立俩张表，分别为table1和table2。

table1如下：

table2如下：

代码如下：

select a.id,a.founder,b.age,a.foundingtime,b.property from table1 a inner join table2 b on a.founder=b.founder;

结果如下：

查询结果集为table1和table2的相交的绿色部分。

left join:该语句会读取左边的数据表table1的所有选取的字符数据，即便在右侧表table2中没有对应的founder字段值。

代码如下：

select a.id,a.founder,b.age,a.foundingtime,b.property from table1 a left join table2 b on a.founder=b.founder;

结果如下：

RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

right join:该语句会读取右边的数据表table2的所有选取的字段数据，即便在左侧表table1中没有对应的founder字段。

代码如下：

select a.id,a.founder,b.age,a.foundingtime,b.property from table1 a right join table2 b on a.founder=b.founder;

结果如下：

7.MySQL常用引擎

mysql常用引擎有俩种，分别为MyISAM和InnoDB。

区别：

（1）MyISAM不支持事务，而InnoDB支持事务。（何为事务，请继续阅读：8.事务）

（2）MyISAM可被压缩，存储空间较小，而InnoDB则需要较多的内存和存储。

（3）MyISAM不支持外键，而InnoDB支持外键。

（4）MyISAM保存有表的总行数，如果select count(*) from table1;会直接取出其总行数。而InnoDB没有保存表的总行数，它只能遍历，如果使用select count(*) from table1;就会遍历整个表，消耗很大，但如果加了where条件后，那么MyISAM和InnoDB处理的方式都一样，则遍历。也就是说没有where的count(*)使用MyISAM要比InnoDB快的多，因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。

（5）如果执行大量的select则应选择MyISAM。如果数据执行大量的update和insert，应使用InnoDB。delete从性能上InnoDB更优，但如果是delete from table时，InnoDB不会重新建立表，而是一行一行的删除。在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

（6）MyISAM只支持表级锁（想要了解锁，请继续阅读9.锁的重要性），用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。MyISAM锁的粒度是表级，而InnoDB支持行级锁定。简单来说就是, InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。

应用场景：

（1) MyISAM管理非事务表。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
（2) InnoDB用于事务处理应用程序。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

8.事务

事务的基本要素为原子性、一致性、隔离性、持久性，即为ACID。

原子性：众所周知，原子是物质构成的基本单位，也就是说事务是一个不可分割的主体。事务开始后所有操作要么全部做完，要么全部不做，是不能停留在中间环节的，如果事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。

一致性：先打个比方，如果李四用支付宝向张三转账，李四扣了钱张三也收到了，这叫一致性，如果李四扣了钱而张三没有收到就不叫一致性。一致性就是事务开始前和结束后其数据库的完整性约束没有被破坏。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

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

MySQL的事务处理在处理实际问题中有着广泛且重要的应用，最常见的应用如银行转账业务、电子商务支付业务等等。

如下是一个银行转账业务的事例：

package edu.hebea;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Account {
	public static Connection MyUtil() {
		String DBDriver = "com.mysql.cj.jdbc.Driver";//注意JDBC driver 由“com.mysql.jdbc.Driver”改为“com.mysql.cj.jdbc.Driver”
		String DBUrl = "jdbc:mysql://localhost:3306/bank?serverTimezone=GMT%2B8";// （GMT%2B8代表东八区）不加的话会报错
		String name = "root";
		String pass = "1234";
		Connection con = null;
		try {
			Class.forName(DBDriver);
			con = DriverManager.getConnection(DBUrl, name, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	public static boolean account(int fromId, int toId, double amount) {
		Connection con = MyUtil();
		String sql1 = "update account set account = account - ? where id = ?";
		String sql2 = "update account set account = account + ? where id = ?";
		String sql3 = "select account  from account where id = ?";
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs = null;
		try {
			// 开启事务true：sql命令的提交（commit）由驱动程序负责
			// false：sql命令的提交由应用程序负责，程序必须调用commit或者rollback方法
			// 当n个sql 视为一个整体（原子性），要么都执行，要么一个不执行时候
			// 通过 setAutoCommit 进行控制。
			// setAutoCommit(false);
			con.setAutoCommit(false);
			// 转账时涉及的两个账户以及各账户的金额变动
			ps1 = con.prepareStatement(sql1);
			ps1.setDouble(1, amount);
			ps1.setInt(2, fromId);
			ps1.executeUpdate();

			ps2 = con.prepareStatement(sql2);
			ps2.setDouble(1, amount);
			ps2.setInt(2, toId);
			ps2.executeUpdate();

			// 检查转出方账户的余额是否足够支持此次转账金额；如果余额不足，则抛出“余额不足”异常，并回滚
			ps3 = con.prepareStatement(sql3);
			ps3.setInt(1, fromId);
			rs = ps3.executeQuery();
			Double balance = 0.0;
			if (rs.next()) {
				balance = rs.getDouble("account");
			}
			if (balance < 0) {
				throw new Exception("账户余额不足");
			}
			con.commit();// 提交事务
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			con.rollback();// 回滚事务
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps1.close();
				ps2.close();
				ps3.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	public static void main(String[] args) {
		account(2, 1, 3000.48);
	}

}

package edu.hebea;

public class Entity {
	private int id;
	private String user;
	private String account;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUser() {
		return user;
	}
	public void setUser(String user) {
		this.user = user;
	}
	public String getAccount() {
		return account;
	}
	public void setAccount(String account) {
		this.account = account;
	}
	

}

在maven项目中的pom.xml复制以下代码

                <dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.11</version>
		</dependency>

如果是Java项目的话则引入需要mysql的jar包。

当俩个账户发生转账关系时，首先改变双方账户的余额状态，随后验证转出方账户是否有足够的余额来支撑此次的转账操作，若余额不足，则抛出账号余额不足的异常。

用户李四给用户张三转账5000元主要步骤可以概括为以下几步：

A：检测李四账户余额 > 5000元

B：李四账户余额减去 5000元

C：张三账户余额增加 5000元

这个小程序充分的体现了mysql的事务。

原子性：事务里面的操作（A、B、C），要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。

一致性：一个事务的执行（A或B或C）不应该破坏数据库的完整性约束。如果上述例子中B操作执行后系统崩溃，保证李四和张三的金钱总计是不会变的。

隔离性：事务之间（A、B、C）相互独立，互不干挠。

持久性：事务提交（A、B、C）之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

9.锁

锁的重要性：数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

表级锁定：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。MyISAM使用表级锁定。

行级锁定：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。俩个事务不能锁同一个索引。使用行级锁定的主要是InnoDB存储引擎

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性：对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放为止。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

行级锁定：其不是MySQL自己实现的锁定方式，而是由其他存储引擎InnoDB等自己所实现的。在这里简单介绍以下InnoDB的实现机制。
InnoDB的行级锁定同样分为两种类型，共享锁和排他锁（分别类似于MyISAM的读锁和写锁），而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁、排他锁、意向共享锁、意向排他锁。共享锁与共享锁、意向共享锁兼容，与其他共享锁冲突。排他锁与一切锁冲突。意向共享锁与共享锁、意向共享锁、意向排他锁兼容，与排他锁冲突。意向排他锁与意向共享锁、意向排他锁兼容，与其他锁冲突。如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于update、delete和insert语句，InnoDB会自动给涉及数据集加排他锁；对于普通select语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。                                                   

 共享锁：

select * from tablename where id=1 lock in share mode;

 排他锁：

select * from tablename where id=1 for update;

死锁：其产生的根本原因是两个以上的进程都要求对方释放资源，以至于进程都一直等待。在代码上是因为两个或者以上的事务都要求另一个释放资源。死锁产生的四个必要条件：互斥条件、环路条件、请求保持、不可剥夺，缺一不可，相对应的只要破坏其中一种条件死锁就不会产生。

MyISAM是没有死锁问题的，因为他会一次性获得所有的锁。InnoDB发生死锁后一般能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

在应用中，可通过以下俩种方式来避免死锁：

(1) 如果不同的程序会并发的存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

(2) 在程序以批量方式处理数据时，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大的降低出现死锁的可能 。

10索引

索引的重要性：索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

mysql索引：单列索引(主键索引,唯索引,普通索引)和组合索引。

单列索引：一个索引只包含一个列,一个表可以有多个单列索引。

组合索引：一个组合索引包含两个或两个以上的列。

利用Navicat for MySQL创建索引

首先右击表，点击设计表，点击索引。便可创建索引。

 

MySQL几种常用的索引类型：

Normal：表示普通索引

UNIQUE：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

FULLTEXT表示全文搜索的索引。目前只有 char、varchar，text列上可以创建全文索引。它的出现是为了解决where　name　like＂%word%"这类针对文本的模糊查询效率较低的问题。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。目前只有MyISAM引擎支持。

MySQL几种常见的索引方法：

HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位。但是，这种高效是有条件的，即只在“=”、“in”和“<=>”（<=>是安全等于的意思。是比较运算符，当比较的值含有null值的时候，来返回一个布尔值。比如:select 1<=>null -- 结果为0，select null<=>null -- 结果为1）条件下高效，对于范围查询、排序及组合索引仍然效率不高。

BTREE：索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。由于Btree索引对结构的利用率很高，定位高效。当1千万条数据时，Btree索引也是三层结构。定位记录仍只需要三次I/O，100条数据和1千万条数据的定位，在btree索引中的花销是一样的。

注意：转载请注明出处https://blog.csdn.net/qq_37573924/article/details/83629640