mysql学习笔记+MacOs

Mac安装

安装HomeBrew

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

HomeBrew安装MySql

#搜索版本
brew search mysql
#安装MySQL
brew install mysql@5.7
#设置开机启动
ln -sfv /usr/local/opt/mysql@5.7/*.plist ~/Library/LaunchAgents
#初始化操作，设置数据库密码，是否允许远程登录等等
mysql_secure_installation
#启动数据库
mysql.server start
#登录
mysql -uroot -p
#关闭数据库
mysql.server start
#可以查看提示来完成安装运行
brew info mysql@5.7

error

#添加环境变量
echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc
#可以重启一下数据库
ERROR 1045 (28000): Access denied for user 'root'@'localhost'

---

MySQL使用

创建数据库

create database niyadb;
show databases;
use niyadb;
drop database niyadb;

创建表

create table instructor;
show tables;
#查看表的结构
mysql> desc instructor;
+-----------+-----------+------+-----+---------+-------+
| Field     | Type      | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| ID        | char(5)   | NO   |     | NULL    |       |
| name      | char(254) | NO   |     | NULL    |       |
| dept_name | char(254) | NO   |     | NULL    |       |
| salary    | int(11)   | NO   |     | NULL    |       |
+-----------+-----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
#查看创建表的sql语句
show create table instructors;
#删除表
drop table instructor;
#新增一列
alter table instructor 
add column birth 
varchar(256) 
not null;
#修改列
alter table instructor
change column birth new_name 
varchar(256)
not null;
#删除列
alter table instructor
drop column birth;

插入行

insert into instructor values('10101','Srin','Comp','650');

查询

基本查询

#查询所有数据
mysql> select  * from instructor;
+-------+--------+-----------+--------+
| ID    | name   | dept_name | salary |
+-------+--------+-----------+--------+
| 10101 | Srin   | Comp      |    650 |
| 12121 | Wu     | Finance   |    900 |
| 15151 | Mozart | Music     |    400 |
+-------+--------+-----------+--------+
3 rows in set (0.00 sec)
#测试数据库连接
select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

按条件查询

mysql> select * from instructor where salary>700;
+-------+------+-----------+--------+
| ID    | name | dept_name | salary |
+-------+------+-----------+--------+
| 12121 | Wu   | Finance   |    900 |
+-------+------+-----------+--------+
1 row in set (0.00 sec)

常用的条件表达式

=, >, >=, <, <= ,<>(不相等)，LIKE(相似)

mysql> select * from instructor where name like '%i%';
+-------+------+-----------+--------+
| ID    | name | dept_name | salary |
+-------+------+-----------+--------+
| 10101 | Srin | Comp      |    650 |
+-------+------+-----------+--------+
1 row in set (0.00 sec)

投影查询

#可以指定输出的列，可以修改列的顺序
mysql> select name,id from instructor;
+--------+-------+
| name   | id    |
+--------+-------+
| Srin   | 10101 |
| Wu     | 12121 |
| Mozart | 15151 |
+--------+-------+
3 rows in set (0.00 sec)
#给name列起一个别名
mysql> select id,name new_name from instructor;
+-------+----------+
| id    | new_name |
+-------+----------+
| 10101 | Srin     |
| 12121 | Wu       |
| 15151 | Mozart   |
+-------+----------+
3 rows in set (0.00 sec)

排序

mysql> select * from instructor order by salary;
+-------+--------+-----------+--------+
| ID    | name   | dept_name | salary |
+-------+--------+-----------+--------+
| 15151 | Mozart | Music     |    400 |
| 10101 | Srin   | Comp      |    650 |
| 12121 | Wu     | Finance   |    900 |
+-------+--------+-----------+--------+
3 rows in set (0.00 sec)
#倒序输出
mysql> select * from instructor order by salary DESC;
+-------+--------+-----------+--------+
| ID    | name   | dept_name | salary |
+-------+--------+-----------+--------+
| 12121 | Wu     | Finance   |    900 |
| 10101 | Srin   | Comp      |    650 |
| 15151 | Mozart | Music     |    400 |
+-------+--------+-----------+--------+
3 rows in set (0.00 sec)
#按多列排序,先按salary倒序排序，相同的行按dept_name排序
mysql> select * from instructor order by salary DESC,dept_name;
#带where子句，order by要放在后面
mysql> select * from instructor where salary>600 order by salary DESC;
+-------+------+-----------+--------+
| ID    | name | dept_name | salary |
+-------+------+-----------+--------+
| 12121 | Wu   | Finance   |    900 |
| 10101 | Srin | Comp      |    650 |
| 17171 | Fang | History   |    650 |
+-------+------+-----------+--------+
3 rows in set (0.00 sec)

分页查询

#每页3条记录，从索引0开始取
mysql> select * from instructor
    -> order by salary
    -> limit 3 offset 0;
+-------+--------+-----------+--------+
| ID    | name   | dept_name | salary |
+-------+--------+-----------+--------+
| 15151 | Mozart | Music     |    400 |
| 10101 | Srin   | Comp      |    650 |
| 17171 | Fang   | History   |    650 |
+-------+--------+-----------+--------+
3 rows in set (0.00 sec)
#第2页，从索引3开始取
mysql> select * from instructor order by salary limit 3 offset 3;
+-------+------+-----------+--------+
| ID    | name | dept_name | salary |
+-------+------+-----------+--------+
| 12121 | Wu   | Finance   |    900 |
+-------+------+-----------+--------+
1 row in set (0.00 sec)
#上面的语句可以简写成
mysql> select * from instructor order by salary limit 3, 3;

聚合查询

#将查询结果展示的列名设置别名count
mysql> select count(*) count from instructor;
+-------+
| count |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)
#可以使用where条件统计

聚合函数

SUM(only 数值), AVG（only 数值, MAX, MIN

mysql> select avg(salary) avg from instructor;
+----------+
| avg      |
+----------+
| 650.0000 |
+----------+
1 row in set (0.00 sec)
#也可以用条件子句where

分组聚合

#按salary分组并计数
mysql> select salary, count(*) count from instructor group by salary;
+--------+-------+
| salary | count |
+--------+-------+
|    400 |     1 |
|    650 |     2 |
|    900 |     1 |
+--------+-------+
3 rows in set (0.00 sec)

多表查询

#给每个表设置一个简单的别名，行两两拼在一起，列数相乘
mysql> select s.id,s.name,c.dept_name from instructor s,course c;
+-------+--------+-----------+
| id    | name   | dept_name |
+-------+--------+-----------+
| 10101 | Srin   | Finance   |
| 12121 | Wu     | Finance   |
| 15151 | Mozart | Finance   |
| 17171 | Fang   | Finance   |
| 10101 | Srin   | Finance   |
| 12121 | Wu     | Finance   |
| 15151 | Mozart | Finance   |
| 17171 | Fang   | Finance   |
| 10101 | Srin   | Finance   |
| 12121 | Wu     | Finance   |
| 15151 | Mozart | Finance   |
| 17171 | Fang   | Finance   |
| 10101 | Srin   | Comp      |
| 12121 | Wu     | Comp      |
| 15151 | Mozart | Comp      |
| 17171 | Fang   | Comp      |
| 10101 | Srin   | Music     |
| 12121 | Wu     | Music     |
| 15151 | Mozart | Music     |
| 17171 | Fang   | Music     |
+-------+--------+-----------+
20 rows in set (0.00 sec)
#后面同样可以跟where子句

连接查询

mysql> select * from instructor s 
mysql> inner join course c 
mysql> on s.dept_name=c.dept_name;
+-------+--------+-----------+--------+-----------+------------------+-----------+---------+
| ID    | name   | dept_name | salary | course_id | title            | dept_name | credits |
+-------+--------+-----------+--------+-----------+------------------+-----------+---------+
| 12121 | Wu     | Finance   |    900 | BOI-101   | Intro to Finance | Finance   |       4 |
| 12121 | Wu     | Finance   |    900 | BOI-301   | Genetics         | Finance   |       4 |
| 12121 | Wu     | Finance   |    900 | BOI-399   | Computional      | Finance   |       3 |
| 10101 | Srin   | Comp      |    650 | CS-101    | Game Design      | Comp      |       3 |
| 15151 | Mozart | Music     |    400 | MU-199    | World Music      | Music     |       3 |
+-------+--------+-----------+--------+-----------+------------------+-----------+---------+

inner join	内连接，交集
left outer join	左连接，左表为基准，右表中不存在行填充NULL
right outer join	右连接，右表为基准，左表中不存在行填充NULL
full outer join	全连接，并集

写法：
1、确定主表from 表1；
2、需要连接的表 join 表2；
3、连接条件 on<条件……>；
4、可选添加子句。

修改数据

insert

#colume省略默认为全部字段
#可以一次插入多条值，中间以逗号分隔
insert into tablename (colume1,colume2) 
values(value1,value2);

update

update tablename 
set colume1=value1,colume2=value2 
where colume=value;

delete

#删除整行
delete from instructor;
#按条件删除
delete from instructor where id=1;

退出

exit;

实用sql语句

#插入或者替换：查询是否存在，存在-删除+插入，不存在-插入
#使用replace可以查询时删除，而不是先查询完再删除
relpace into tablename values();
#插入或更新
insert into tablename
values()
on duplicate key update column1=value1,column2=value2;
#插入或忽略
insert ignore into tablename (column1,...) values(value1,...);
#快照
#复制一份当前表的数据到一个新表,想当于旧表的查询结果保存到了一个新的表里
create table new_table select * from tablename where id=1;
#写入查询结果集
mysql> select * from new_instructor;
+-------+-----------+
| ID    | dept_name |
+-------+-----------+
| 10101 | Comp      |
| 12121 | Finance   |
| 17171 | History   |
+-------+-----------+
3 rows in set (0.00 sec)

mysql> insert into new_instructor (id,dept_name) select ID,dept_name from instructor;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from new_instructor;
+-------+-----------+
| ID    | dept_name |
+-------+-----------+
| 10101 | Comp      |
| 12121 | Finance   |
| 17171 | History   |
| 10101 | Comp      |
| 12121 | Finance   |
| 15151 | Music     |
| 17171 | History   |
+-------+-----------+
7 rows in set (0.00 sec)

事务 — 四种隔离级别

Read Uncommitted

问题：脏读
一个事务可以独当另一个事务更新但是未提交的数据，如果另一个事务回滚，那么当前事务读到的就是脏数据
读不阻塞读写，写阻塞写不阻塞读
行级锁

Read Committed

问题：不可重复读
一个事务多次读取同一数据，如果中间有另一个事务修改了数据，那两次读的数据可能不一致
读不阻塞读写，写阻塞读写
行级锁

Repeatable Read

问题：幻读
读记录，记录为空，更新记录时可以成功，再次读有结果
读阻塞写不阻塞读，写阻塞读写
行级锁

Serializable

最严格的的隔离级别
事务是串行执行（上面出现的三个问题都是因为并行执行产生的），但是性能会急剧下降
默认隔离级别，MySQL中使用InnoDB默认隔离级别为Repeatable Read
表级锁

相关锁

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

行级锁共享锁

读锁，只读不写，之上可加读锁

行级锁排它锁

写锁，可读可写，不可加读写锁

乐观锁之版本号与CAS算法

版本号机制

增加version字段，当前提交的version与数据库当前version进行比较，大于即可提交，否则被驳回

CAS算法

compare and swap，无锁算法，即不使用锁的情况下实现多线程之间的变量同步，也叫非阻塞同步
需要读写的内存值V与A进行比较，加自旋锁来更新V的值