数据库

create database mysql_2019;  创建数据库

show databases  展示数据库

use mysql_2019;  连接指定数据库

drop database mysql_2019;  删除指定数据库

status; 查看数据库信息

------------------------------------------
创建表
create table t_user1(    
ID int,
name varchar(5),
Age int
);

desc t_user1;  查看表结构

alter table t_user1 rename t_user; 修改表名

show tables; 查看所有表

---------------------------------------------
修改表结构
drop table t_user; 删除指定表

alter table t_user add Kg int;   表中添加字段

alter table t_user change name/*原来名字*/ useName/*新的名字*/ varchar(5);  表中修改字段
alter table t_user modify name varchar(3); 修改字段类型

alter table t_user drop Kg;    表中删除指定字段;

------------------------------------------------------
数据操作
insert into t_user values(1,"张三",12);默认往表中添加全部数据
insert into t_user (ID,userName)values(1,"张三");往表中添加指定数据;

select * from t_user;   查看数据

update t_user set name="李四",age=15 where ID=1;  修改数据

delete from t_user where ID=1;  删除指定数据

delete from t_user ; 清空表中数据

-------------------------------------------------------

ID INT PRIMARY KEY AUTO_INCREMENT,   主键约束 PK + 主键自增  

Name VARCHAR(5) NOT NULL; 非空约束;此字段内容不能为空 NK

email VARCHAR(50) UNIQUE;  唯一约束;  UK

CREATE TABLE t_order(
ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(10) NOT NULL,
User_ID INT NOT NULL,
CONSTRAINT user_id_fk/*外键名称*/ FOREIGN KEY (User_ID) REFERENCES t_user1(ID) 外键约束
);

-------------------------------------------------------------
mysqldump -u root -p mysql_2019>D:\shujuk\mys1q.sql  导出数据库

mysqldump -u root -p mysql_2019 t_user1>D:\shujuk\mys1q.sql  导出数据库中指定表

 source D:\shujuk\mys1q.sql  进入数据库导入表

---

---

员工表 表名 EMP

部门表 表明DEP

限定查询

select * from EMP;   查看EMP表中所有数据

select Name , JOB from EMP  查看指定字段数据(投影)

SELECT e.ID ,e.`Name` FROM emp e;   别名

SELECT 
e.ID AS '员工编号' ,
e.`Name` AS '员工名字' 
FROM emp e;  别名

SELECT DISTINCT e.JOB emp e; 去除重复内容

SELECT CONCAT('员工编号: ',e.ID ,'员工名字', e.`Name`)AS ' 员工信息: ' FROM emp e;  多列合并成一列展示
员工编号: ** 员工信息: ***

SELECT * FROM emp e WHERE e.ID=2;   查询编号为2的员工信息

SELECT * FROM emp e WHERE e.SAL>2000;  查询工资大于2000的员工

SELECT * FROM emp e WHERE e.SAL>=1800 AND e.SAL <=2300;  工资>=1800 <=2300的员工

SELECT * FROM emp e WHERE e.depp='文员部' OR e.depp ='技术部'; 查询两个部门的员工
SELECT * FROM emp e WHERE e.depp IN('文员部','技术部');    查询属于两个部门的员工
SELECT * FROM emp e WHERE e.depp NOT IN('文员部','技术部');  不属于两个部门的员工

SELECT * FROM emp e WHERE e.bonus IS NULL;  查询没有奖金的员工
SELECT * FROM emp e WHERE e.bonus IS NOT NULL;

SELECT * FROM emp e WHERE e.Name LIKE '李%';  模糊查询,查询姓李的员工
SELECT * FROM emp e WHERE e.Name LIKE '%四%';  查询名字有四的员工
SELECT * FROM emp e WHERE e.Name NOT LIKE '%四%';  名字没有四的员工

SELECT e.* FROM emp e  ORDER BY e.SAL ASC;  查询奖金从低到高 asc .. desc从高到低

多表查询

SELECT * FROM emp,dep; 产生笛卡尔积(没有加关联字段)
SELECT * FROM emp e,dep d WHERE e.depp=d.`name`;  消除笛卡尔积(一个员工对应一个部门)
SELECT e.ID,e.`Name` ,e.depp,d.id FROM emp e,dep d WHERE e.depp=d.`name`; 员工表的部门对应的部门编号

SELECT e.ID,e.`Name`,e.MGR,m.`Name` FROM emp e, emp m WHERE e.mgr=m.ID;  自连接  查询员工的MGR编号对应的姓名
SELECT e.ID,e.`Name`,e.MGR,m.`Name` FROM emp e LEFT JOIN emp m ON (e.MGR=m.ID); 左连接 
以左表为主,左表的数据全部显示,右表数据匹配到就显示.没匹配到就null;

SELECT  e.ID, e.`Name`,d.id,d.`name` FROM emp e RIGHT JOIN dep d ON (d.`name`=e.depp); 所以部门下员工编号 姓名 部门 部门编号
右连接 ,右表的数据全部显示,左表数据匹配到就显示.没匹配到就null;

SELECT * FROM dep
UNION              两个查询结果合并,重复内容不显示, UNION ALL 重复内容显示
SELECT * FROM dep;

---

---

常用函数

表名:string

SELECT UPPER(s.`Name`),LOWER(s.gg) FROM string s; 查询字符串转成大写  小写

SELECT LENGTH(s.`Name`) , s.`Name` FROM string s; 字符串长度

SELECT SUBSTR(s.`Name`,2,2),s.`Name` FROM string s;  截取字符串

SELECT REPLACE(s.`Name`,'x','cao'),s.`Name` FROM string s;  字符串替换

SELECT  TRIM(s.`Name`), s.`Name` FROM string s; 去掉左右空格

SELECT INSTR(s.`Name`,"a") FROM string s;  查找某个字符的位置

SELECT ROUND(s.Age,2), s.Age FROM string s;  对小数四舍五入
SELECT MOD(s.Age,3) FROM string s;  取模

SELECT NOW() FROM string s;  获取当前时间

SELECT ADDDATE(s.DATA,5) , s.DATA FROM string s;  日期函数,指定日期加上指定天数,得出最新日期
SELECT LAST_DAY(s.DATA) FROM string s;      求出指定日期的最后一天

---

IFNULL / CASE

SELECT IFNULL(e.MGR,'这是空值') FROM emp e;  如果值为空,则使用默认值

SELECT CASE 
	WHEN sex=1 THEN
		'男'
	ELSE
		'女'
END FROM string s;

---

统计函数

SELECT COUNT(1) FROM emp;  查看记录数

SELECT SUM(SAL) + SUM(bonus) FROM emp;  求出工资总和

SELECT MAX(e.SAL), MIN(e.SAL),ROUND(AVG(e.SAL),1) FROM emp e;  求出最高,最低,平均工资

分组统计查询

SELECT
	IFNULL(e.depp,'空值') AS '部门',
	COUNT( ID ) AS '人数'
FROM
	emp e 
GROUP BY
	e.depp;   根据部门分组,然后查询每个部门的人数
------------------------
SELECT
	d.`name` AS '部门',
	d.id AS '部门编号',
	COUNT(e.ID) AS '人数',
	AVG(e.SAL) AS '平均工资'
FROM
	emp e
	RIGHT JOIN dep d ON ( e.depp = d.`name` ) 
GROUP BY
	e.depp;   查询部门编号 名称 部门人数 平均工资  (多表连接)
-----------------------------
SELECT e.depp , AVG(SAL) FROM emp e GROUP BY e.depp HAVING AVG(SAL)>1800;
根据部门分组,然后查询平均工资大于1800  HAVING 一般与 GROUP BY搭配

SELECT
	e.depp,
	d.`name`,
	COUNT(e.ID) AS sum
FROM
	emp e
	LEFT JOIN dep d ON ( e.depp = d.`name` ) 
GROUP BY
	e.depp HAVING sum>=2;     查询部门编号,名称,部门大于=2个人

子查询

SELECT e.SAL FROM emp e WHERE e.ID=2 ;
SELECT * FROM emp e WHERE e.SAL< (SELECT e.SAL FROM emp e WHERE e.ID=2) ;
找出比ID2 工资少的员工

SELECT e.JOB,e.MGR FROM emp e WHERE e.ID=2 ;
SELECT * FROM emp e WHERE (e.JOB,e.MGR)=(SELECT e.JOB,e.MGR FROM emp e WHERE e.ID=2 );
查询与 ID2 工作 领导 相同的员工

SELECT e.JOB,e.MGR FROM emp e WHERE e.ID IN(2,5) ;
SELECT * FROM emp e WHERE (e.JOB,e.MGR) IN(SELECT e.JOB,e.MGR FROM emp e WHERE e.ID IN(2,5));
查询与 ID:2 ID:5 工作 领导 相同的员工

SELECT d.`name` FROM dep d WHERE d.id=105;
SELECT * FROM emp e WHERE e.depp=(SELECT d.`name` FROM dep d WHERE d.id=105);
查询部门编号为105 的部门下员工

分页

SELECT * FROM emp ORDER BY SAL DESC LIMIT 3;  工资从高到低,查询前三, limit 默认从0,0位第一行

视图

CREATE VIEW sss AS SELECT * FROM emp e WHERE e.depp IN('文员部','技术部');

事务的概念:事务是指逻辑上的一组操作,这组操作要么同时完成要么同时不完成.

• 事务的管理:默认情况下,数据库会自动管理事务,管理的方式是一条语句就独占一个事务.
  如果需要自己控制事务也可以通过如下命令开启/提交/回滚事务
  start transaction;
  commit;
  rollback;

• JDBC中管理事务:
  conn.setAutoCommit(false);
  conn.commit();
  conn.rollback();
  SavePoint sp = conn.setSavePoint();
  conn.rollback(sp);

事务的四大特性:一个事务具有的最基本的特性,一个设计良好的数据库可以帮我们保证事务具有这四大特性(ACID)

• 原子性:原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性:如果事务执行之前数据库是一个完整性的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整性状态.
  数据库的完整性状态:当一个数据库中的所有的数据都符合数据库中所定义的所有的约束,此时可以称数据库是一个完整性状态.
• 隔离性:事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
• 持久性:持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

---

1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。

2、不可重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。

3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。
隔离性:
将数据库设计成单线程的数据库,可以防止所有的线程安全问题,自然就保证了隔离性.但是如果数据库设计成这样,那么效率就会极其低下.

数据库中的锁机制:
共享锁:在非Serializable隔离级别做查询不加任何锁,而在Serializable隔离级别下做的查询加共享锁,
共享锁的特点:共享锁和共享锁可以共存,但是共享锁和排他锁不能共存
排他锁:在所有隔离级别下进行增删改的操作都会加排他锁,
排他锁的特点:和任意其他锁都不能共存

如果是两个线程并发修改,一定会互相捣乱,这时必须利用锁机制防止多个线程的并发修改
如果两个线程并发查询,没有线程安全问题

如果两个线程一个修改,一个查询…

• 四大隔离级别:
  Read uncommitted – 不防止任何隔离性问题,具有脏读/不可重复度/虚读(幻读)问题
  Read committed – 可以防止脏读问题,但是不能防止不可重复度/虚读(幻读)问题
  Repeatable read – 可以防止脏读/不可重复读问题,但是不能防止虚读(幻读)问题
  Serializable – 数据库被设计为单线程数据库,可以防止上述所有问题

从安全性上考虑: Serializable>Repeatable read>read committed>read uncommitted
从效率上考虑: read uncommitted>read committed>Repeatable read>Serializable

真正使用数据的时候,根据自己使用数据库的需求,综合分析对安全性和对效率的要求,选择一个隔离级别使数据库运行在这个隔离级别上.
mysql 默认下就是Repeatable read隔离级别
oracle 默认下就是read committed个隔离级别

Serializable :
在Serializable 隔离级别下, 如果开启了两个事务,A 事务查询了 ,B事务也进行查询 , AB事务在该 数据库 相当于持有了共享锁
然后 A事务进行修改操作, 需要将A事务中共享锁转换为 排它锁,但是B事务也有共享锁,所以需要等B事务结束才可以转换, 如果此时B事务进行修改操作,B则需要转换成排它锁,此时则会发生错误,B会强行结束,A继续修改操作,数据库自动执行

Repeatable read 级别下; 当A事务查询后 ,B事务进行修改后 ,A事务还是可以查询(数据跟第一次查询的一样), 如果A事务要修改,则需要等B事务回滚后或者提交后才能进行(为了保持数据最新); (在非Serializable隔离级别做查询不加任何锁)

Serializable 级别下 ; 当A查询后 ,B事务进行修改后 , A事务就不能进行查询了,需要等B事务回滚后或者提交后才能进行;

所以 这就是共享锁:在非Serializable隔离级别做查询不加任何锁,而在Serializable隔离级别下做的查询加共享锁,
共享锁的特点:共享锁和共享锁可以共存,但是共享锁和排他锁不能共存
排他锁:在所有隔离级别下进行增删改的操作都会加排他锁,
排他锁的特点:和任意其他锁都不能共存

---

更新丢失问题:
两个线程基于同一个查询结果进行修改,后修改的人会将先修改人的修改覆盖掉.

悲观锁:悲观锁悲观的认为每一次操作都会造成更新丢失问题,在每次查询时就加上排他锁

乐观锁:乐观锁会乐观的认为每次查询都不会造成更新丢失.利用一个版本字段进行控制

查询非常多,修改非常少,使用乐观锁
修改非常多,查询非常少,使用悲观锁