Mysql数据库

创表步骤：

1.CREATE DATABASE test CHARSET=utf8; #创建数据库并设置编码格式;
2. USE myyog； 使用库
3. 创建表

CREATE TABLE 数据库(
       姓名 VARCHAR(40),
       成绩  FLOAT
);

向表中插入数据：

方式一： insert into 表名（字段1，字段2…）values (值1，值2…)
INSERT INTO 数据库 (姓名,成绩) VALUES (‘李四’,80.5)

省略字段名关键字，键值用逗号表达式连接

INSERT INTO  数据库  VALUES ('李四',80.5),('戊戌',NULL);

方式二： insert into 表名 set 字段名1=值1，字段名2=值2；
INSERT INTO 数据库 SET 姓名='王五',成绩='100'

删除表中的数据

删除数据库、表都用drop关键字 如 ：DROP DATABASE myyog1 DROP TABLE student;
删除数据用 delete关键字 如： DELETE FROM 数据库 WHERE 姓名='李四'

格式：#delete from 表名 where 条件
 DELETE FROM 数据库 WHERE 姓名='李四'
 DELETE FROM 数据库 WHERE 成绩=80

删除全部数据

delete from student;
truncate student;
-- 使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1

ALTER：

对数据库和表的操作：

ALTER DATABASE mydb01 CHARACTER SET utf8
ALTER TABLE 数据库 CHANGE 姓名 姓名 VARCHAR(40) CHARACTER SET utf8;

#用alter 增/删表中的字段  格式：alter table 表名 add/drop 列名（类型大小）;

#重命名表的名字 :rename 旧表名to 新表名 
RENAME  TABLE bank TO orders  

#对表中字段进行重命名
ALTER TABLE orders CHANGE id 编号 VARCHAR(40) #重设字段的名称和类型
ALTER TABLE orders CHANGE 编号 id INT  #重设字段的名称
ALTER TABLE orders ADD add增加列 INT #add增加列
ALTER TABLE ORDERS DROP add增加列 #drop删除列

ALTER TABLE orders ADD 生日 DATE
ALTER TABLE orders CHANGE 生日 生日 DATETIME
#ALTER对表中字段进行重命名

**设置数据库的编码格式**
1.alter database 数据库名 character  set utf8  //将指定数据库的字符集改变utf-8 
2.修改表编码格式：alter table 表名 default character set utf8;
3.修改列的编码格式：alter table 表名 change 列名 列名 varchar(40) character set utf8;

Update：

对数据的修改操作：
#修改语法 update 表名set 字段名1=值1，字段名2=值2…where 条件

1.条件筛选更新部分数据：

UPDATE students SET id=1 WHERE 年龄=18 OR 年龄=20
UPDATE bank SET money=money+200 WHERE usename='马蓉';

2… UPDATE更新全部数据

示例：将所有记录的age设置为18 MySQL命令：
update student set age=18;

查询
条件查询 （筛选）: from 表名 where 条件1 (salary=1000 ) ；
逻辑判断：and ,or, not
!=等价于 <>

 where between 600 and 1000;   //between and关键字的范围必须是从小到大
 where salary >=600 and salary <=500 ;

给字段列取别名

SELECT 姓名 AS usename FROM 数据库 WHERE 成绩>=75 
也可以去掉as
 SELECT 姓名  usename FROM 数据库 WHERE 成绩>=75

给表取别名

SELECT id 编号,姓名  昵称,年龄 age FROM students 表 #给表取了别名
SELECT s.id,s.姓名 FROM students s  #给表students去别名为s，并且用b别名s来引用字段名id,姓名
SELECT s.id 编号,s.姓名 昵称 FROM students s 
#给表students去别名为s，并且用b别名s来引用字段名id,姓名，并给字段名也取了别名

SHOW DATABASES //显示数据库表
create database mydb2;  //创建数据库mydb2;
create database mydb2 if no exists mydb2;  //如果不存在指定数据库就创建
 create database mydb2 character set gbk; //创建数据库并指定字符集
 show create database mydb2;  //显示创建的指定数据库
SHOW CREATE TABLE 表     //显示创建的指定表
查看当前使用的数据库
select database();

select 列名 from 表名    #查询的列来自表
select * from 表名

//给查询到的列取别名
 Select 列名 as 别名；

//查询结果去重
distinct 列名
select distinct 列名   //去重

排序
order by 列名 ASC /DESC    #升序和降序

条件查询 （筛选）:  from 表名 where 条件1 (salary=1000 ) ；
 逻辑判断：and ,or, not
  !=等价于 <>
 where between 600 and 1000;   //between and关键字的范围必须是从小到大
 where salary >=600 and salary <=500 ;

NULL 拿不到
is NULL;表示是空值
is not NULL； 表示不是空值
where salary =is NULL; 

case   and 生成一个独立的列  from表
select 列a  列b
case 
    when salary>=1000 then 'A'
    when salary>=800 and salary<1000 then 'B'
else   'C'
  end as'列名'     //将生成的新列取名
from 表名


/********分组查询*********/ 
 分组查询中国，select显示的只能是分组依据或者聚合函数
select 列名 from 表 where  group by 条件列名；
SELECT  列名，COUNT(列名) from 表名 GROUP BY 条件列
SELECT 部门列  AVG(薪水列) from表  group by部门列    // 统计部门的平均工资 

 
 ###################建立用户表及其格式设置
 
CREATE TABLE users(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
  usename VARCHAR(40) NOT NULL COMMENT '用户名',
  gender VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  idcard  VARCHAR(20) NOT NULL UNIQUE  COMMENT '身份证',
 javascore FLOAT DEFAULT '0' COMMENT 'java成绩'
);
INSERT INTO users SET usename='蔡徐坤',gender='男',idcard='111',javascore=18;
####DESC降序排序
SELECT * FROM users ORDER BY id DESC
####ESC 升序排序 
SELECT *FROM users ORDER BY id ASC
SELECT LOWER ('mysql');

###########同时查询多条记录####
SELECT * FROM users WHERE id=1 OR id=2 OR id=3
##等价于
SELECT * FROM users WHERE id IN(1,2,3)
###等价于
SELECT * FROM users WHERE id NOT IN(4,5) ORDER BY javascore ASC  ##不在in的范围内并根据javascore的升序

SELECT  * FROM users GROUP BY gender   #即为以gender作为关键字来分类
###GROUP BY 后面的筛选条件用having
SELECT  gender FROM users GROUP BY gender HAVING gender='男'  ##查询是否有gender为男的数据

########分页#######(limit 起始下标，页数)
SELECT * FROM users LIMIT 0,3;

###将一张表赋值到新建表中#####
CREATE TABLE  新表(
  SELECT * FROM users
)
INSERT INTO 新表 SELECT  * FROM users WHERE usename='蔡徐坤' #将蔡徐坤插入到新表


##########时间函数 datetime(date,formate)
ALTER TABLE 新表 ADD 日期 DATETIME
SELECT usename, DATE_FORMAT(日期,'%Y/%m') FROM 新表

多表查询

在数据库中新建两张表：

#部门表
CREATE TABLE depart(
 id INT NOT NULL  AUTO_INCREMENT PRIMARY KEY COMMENT '部门编号',
 deptname VARCHAR (40) COMMENT '部门名称'
)
#员工表
CREATE TABLE  emp(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号',
   ename VARCHAR(40) COMMENT '员工名称',
   salary FLOAT COMMENT '员工薪水',
   deid INT COMMENT '部门编号'
)

#查找开发部员工的部门id、部门名称、员工姓名、员工薪水

 SELECT d.id,d.`deptname`,e.`ename`,e.`salary`  #所要查询的字段
 FROM  depart AS d ,emp AS e    #给两个表定义别名
 WHERE d.id=e.deid AND d.`deptname`="开发部"  #筛选条件 查询所有部门的并且部门要是开发部

查询结果

查询开发部和测试部的员工所有信息以及所属部门：

 SELECT  e.* ,d.`deptname`
 FROM `depart` AS d, `emp` AS e
 WHERE d.id=e.deid AND (d.`deptname`="测试部" OR d.`deptname`="开发部")

Union合并

union：必须要查询的表结构一致才能进行合并，union会自动去重，而union all不会去重。

 ###union(合并自动去重)查询的表结构必须一致才能合并
 ###union all (合并但不去重) 结构也要一样

 SELECT d.`deptname`,e.`deid`,e.ename,e.`salary`
 FROM `depart` AS d,`emp`AS e
 WHERE d.id=e.`deid` AND d.`deptname`="测试部"
 UNION 
  SELECT d.`deptname`,e.`deid`,e.ename,e.`salary`
 FROM `depart` AS d,`emp`AS e
 WHERE d.id=e.`deid` AND d.`deptname`="开发部"

将查询的开发部和测试部的员工部门、编号、员工名称、薪水进行了合并

内连接（inner join on）关联两张表

将两张表进行关联：
格式: 表1 inner join 表2 on 条件

#########内连接  表1 inner join  表2   on 条件   ####根据on后面的条件将表1和表2进行关联

SELECT d.`deptname`,e.`deid`,e.`ename`,e.`salary`  #查询表
FROM depart AS d INNER JOIN emp AS e   #通过关键字Inner join将d表和e表进行关联
ON d.id=e.`deid`     ##根据这个条件关联
 
 #####找出开发部的员工的"部门"、名称、薪水
SELECT d.`deptname`,e.`ename`,e.`salary`
FROM depart AS d INNER JOIN emp AS e
ON d.`deptname`="开发部"

查询结果

找出开发部的员工的"部门"、名称、薪水

  #####找出开发部的员工的"部门"、名称、薪水
SELECT d.`deptname`,e.`ename`,e.`salary`
FROM depart AS d INNER JOIN emp AS e
ON d.`id`=e.`deid`AND d.`deptname`="开发部"

查询结果

分组查询

查询的字段 后面跟上多行查询函数就表示是以这个字段来分组的，如下例就是查询deid的薪水平均值

两种实现方式可以看出，where和having都是过滤作用,having是在分组后过滤，where是在分组前过滤

1.根据deid按薪资的平均值分组 ORDER BY 根据deid降序排序
SELECT  deid ,AVG(salary) FROM emp GROUP BY deid ORDER BY deid DESC
SELECT  id, AVG(bprice) FROM book GROUP BY id

2.查询编号不为1的部门的平均薪水 并以部门来分组
SELECT  deid ,AVG(salary) FROM emp GROUP BY deid HAVING deid!=1  
SELECT deid ,AVG(salary) FROM emp  WHERE deid!=1 GROUP BY deid

单表查询

#列出工资最小值小于8000的人
SELECT salary ,MIN(salary) 工资最小值 
FROM emp 
GROUP BY salary HAVING 工资最小值<8000

-- 列出平均工资大于5000的人和它部门编号
SELECT  ename,AVG(salary),deid   -- 查询的列以及多列函数，列就是分组名
FROM emp                         -- 来自哪个表
GROUP BY ename,deid              -- 分组名
HAVING AVG(salary)>5000          -- 约束条件

– 统计人数大于1的部门编号以及平均工资

SELECT  deid, COUNT(ename)AS 人数 ,AVG(salary)
FROM emp
GROUP BY deid 
HAVING 人数>1

– 统计各部门的最高工资，排除最高工资小于8000的部门编号

SELECT deid, MAX(salary) 最高薪水
FROM emp
GROUP BY deid 
HAVING 最高薪水 >8000

– 多表查询 inner join on 显示所有员工匹配的信息

SELECT *
FROM emp e JOIN depart d    -- 将两张表合并
ON e.id=d.id        -- 条件，当两张表id相等，

– 外连接------
– 左外： 左边表的信息，即使不匹配也可以展示出来
– 右外： 右边表的信息，即使不匹配也可以展示出来

左外查询，左边的表的数据全部展示

SELECT *
FROM emp e
LEFT OUTER JOIN depart d      -- 左外查询，左边的表的数据全部展示
ON e.id=d.id

右外查询，右边表的数据全部展示出来
ON e.id=d.id

SELECT *
FROM emp e
RIGHT OUTER JOIN depart d   -- 右外查询，右边表的数据全部展示出来
ON e.id=d.id

– 用union并集来弥补左外和右外的缺陷，实现mysql不支持全外连接的功能
– union会去重

 SELECT *
 FROM emp e
 LEFT OUTER JOIN depart d
 ON e.id=d.id
 UNION
SELECT *
FROM emp e
RIGHT OUTER JOIN depart d  
ON e.id=d.id

– union all 不去重，直接合并

 SELECT *
 FROM emp e
 LEFT OUTER JOIN depart d
 ON e.id=d.id
 UNION ALL
SELECT *
FROM emp e
RIGHT OUTER JOIN depart d  
ON e.id=d.id

– 自连接，将自己表的某个关键字与自己表的另外一个关键字关联

 -- 例如：查找每个员工的人民和上级，而上级又的上级又是员工
SELECT *
FROM emp e1 JOIN emp e2
ON e1.id=e1.deid

– 单行子查询

– 单行子查询–
– 相当于嵌套查询，先执行括号里面的查询，并且单行子查询的结果只返回一行

查询工资大于平均工资员工的名字和薪水

SELECT ename,salary    -- 查询的字段是名字、薪水
FROM emp               -- 查询的表
WHERE salary >(SELECT AVG(salary)FROM emp) 子查询是查询平均工资，先执行子查询

查询结果如下：只有王总一个人的工资大于平均薪水

– 查询和张主管同一部门且工资比他低的员工的名字和工资

SELECT ename,salary  #展示名称和薪水关键字
FROM emp
WHERE depname=(SELECT depname FROM emp WHERE ename="张主管")#查询和张主管同一个部门
AND salary <(SELECT salary FROM emp WHERE ename="张主管" )#查询薪水比张主管低

查询结果如下：可以看到与张主管在同一个部门中的只有张三李四工资比王总工资低

多行子查询

引入：：多行子查询的结果是返回多行

下面要求查询工资小于编号为1的信息

SELECT *
FROM emp
WHERE salary <(SELECT salary FROM emp WHERE deid=1) #这个是返回多行

可以看出报错提示子查询返回的多余一行，所以这是个多行子查询，我们需要用一个ANY表示只要小于任意一个就能筛选出来，所以多行子查询的正确使用应该加一个关键字诸如：ANY,ALL,用IN也可以，例如：

-- 1.下面要求查询工资小于编号为1的信息
SELECT *
FROM emp
WHERE salary <ANY(SELECT salary FROM emp WHERE deid=1)#用ANY来表示多行的任何一个

-- 2.#查询部门编号是2并且部门名称和部门编号是1的部门名称相等的信息
SELECT * FROM emp WHERE deid=2
SELECT depname FROM emp WHERE deid=1
转化为下面的多行子查询
SELECT  * FROM emp
WHERE deid=2 AND
depname =ANY(SELECT  depname WHERE deid=1)
#可以看出子查询是查询编号为1的部门名称，只要与任意一个相等都能被筛选出来，所有用any
 
-- 3.查询工资比研发部的人工资都高的信息
SELECT * 
FROM emp
WHERE salary>ALL(SELECT salary FROM emp WHERE depname="研发部")
#可以看出子查询的是研发部的工资，只要比他们所有人都高就可以，所以用all

相关子查询

相关子查询就是在不相关子查询的基础上给外查询的表取一个别名，以便在内查询中能够使用别名来使用表的字段名的每个记录。

事务：

默认一个DML是一个事务

#开启事务，下面运行的所有操作都是在一个事务中
START TRANSACTION
UPDATE   account  SET salary=salary+200 WHERE id=1
UPDATE  account SET salary=salary-200 WHERE id=2

#手动回滚，将所有执行的操作全部取消

ROLLBACK; 

-- 手动提交
COMMIT

-- 在回滚和提交之前，所有操作的数据都是缓存中的数据，而不是数据库的数据
-- 回滚就相当于将数据库的数据重新拿出来
-- COMMIT就是将缓存的数据保存到数据库

并发引起的三个问题：
1.脏读：当事务A在访问这个数据，事务B也访问了这个数据并进行了修改，而这种修改还没有提交到数据库，接着事务A又访问了这个数据，访问的结果是这个数据是缓存里的150不是数据库里的100，但事务B会回滚，那么事务A访问到的150就形成了脏读。

2.不可重复读：当事务A的两次查询中，事务B在这两次查询中也读取了数据并做了修改，接着又提交到了数据库，导致事务A的前两次查询与第三次查询的结果不一致。

3.幻读：当事务A读取多行数据时，在事务A还没有完全读完数据，事务B插入了事务A读取范围内的数据，导致事务A多读取了一些本不该存在的数据。

在MYSQL数据库中查看当前事务的隔离级别

SELECT @@transaction_isolation;

LIKE模糊查询

– 模糊查询格式： like '%字符%'
将模糊查询带的字符放入两个%%中间

SELECT *
FROM emp
WHERE depname LIKE '%研发%'