MYSQL

最新推荐文章于 2024-03-27 18:10:09 发布

TheSadPoison

最新推荐文章于 2024-03-27 18:10:09 发布

阅读量110

点赞数

分类专栏： basa 文章标签： MYSQL

本文链接：https://blog.csdn.net/qq_42741973/article/details/83018928

版权

basa 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL

SQL: 结构化查询语言

 SQL的分类：
      DDL：数据定义语言 --> 主要操作库、表、列
      DML：数据操作语言 --> 操作（增，删，改）数据
      DQL：数据查询语言 --> 查询数据，不会改变数据内容
      DCL：数据控制语言 --> 数据控制语言，定义数据的访问权限和安全级别
      -- DDL 操作数据库 表 列
-- 1.创建数据库
-- 语法：create database 数据库名;
CREATE DATABASE whdb2;
-- 2.删除数据库
-- 语法：DROP DATABASE 数据库名；
DROP DATABASE whdb2;
-- 3.展示数据库
-- 语法 SHOW DATABASES;
show DATABASES;
-- 4.查看创建数据库
-- 语法：show CREATE DATABASE whdb2;
-- 5.修改编码格式
-- 语法：alter database whdb2 CHARACTER SET gbk;
alter database whdb CHARACTER SET utf8;
-- 6.创建数据表
CREATE TABLE 表名（
字段1 字段类型,
字段2 字段类型,
......
）;

DDL_查询

-- 基础查询
-- 查询所有列
SELECT * FROM stu;
-- 查询指定列
SELECT sid FROM stu;
-- 条件查询
-- 查询学生性别为女，并且年龄小于50的记录
SELECT * FROM stu WHERE gender='female' AND age<'50';
-- 查询学生学号为1001，或者姓名为liSi的记录
SELECT * FROM stu WHERE sid='1001' or sname='liSi';
-- 查询学号为1001，1002，1003的记录
SELECT * FROM stu WHERE sid='1001' OR sid='1002' OR sid='1003';
SELECT * FROM stu WHERE sid in(1001,1002,1003);
-- 查询学号不是1001,1002,1003的记录
SELECT * FROM stu WHERE sid NOT in(1001,1002,1003);
-- 查询学生年龄为null的记录
SELECT * FROM stu WHERE age is NULL;
-- 查询学生年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>20 AND age<40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

– 模糊查询

-- % 当前位置匹配所以字符；'张%' : 张三,张四,张五六…
SELECT * FROM stu WHERE sname LIKE 'Li%';
-- 查询姓名中以“i”结束的学生记录
SELECT * FROM stu WHERE sname LIKE '%i';
-- 查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';
-- 查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';
-- 查看emp表中工资，去除重复数据distinct
SELECT DISTINCT sal FROM emp;
-- 查看员工所属的部门类别
SELECT DISTINCT deptno FROM emp;
-- 查看员工所属的部门类别,并按编号降序
SELECT DISTINCT deptno FROM emp ORDER BY deptno DESC;
-- 查询所有学生年龄，按年龄升序排列
-- ORDER BY 字段 排序方式;
-- ORDER BY age DESC(降序)/ASC(升序);默认升序
SELECT * FROM stu ORDER BY age ASC;
-- 查询所有雇员，按月薪升序排序，如果月薪相同时，按编号降序排序
SELECT * FROM emp ORDER BY sal ASC,empno DESC;

– 聚合函数(针对一列数据做运算的) sum() avg() max() min() count() (字段)

-- SUM(该段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
-- 查询所有雇员月薪和：
SELECT SUM(sal) FROM emp;
-- 查询所有雇员奖金和
SELECT SUM(comm) FROM emp;
-- 查询所有雇员月薪和，以及所有雇员佣金和：
-- AS 为列取别名，as可以省略
SELECT SUM(sal) AS salSum,SUM(comm) AS commSum FROM emp;
SELECT SUM(sal) salSum,SUM(comm) commSum FROM emp;
-- 查询所有雇员月薪+奖金和：
SELECT SUM(sal)+SUM(comm) TOTAL FROM emp;
-- MAX()和MIN() 求该列最大值 MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；MIN()：
-- 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
-- 查询最高工资和最低工资：
SELECT MAX(sal) FROM emp;
SELECT MIN(sal) FROM emp;
-- 求员工姓名的字典顺序的最大值
SELECT MAX(ename) FROM emp;
-- AVG 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
-- 统计所有员工平均工资：
SELECT AVG(sal) FROM emp;
SELECT AVG(comm) FROM emp;

– 分组查询

-- 当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。
-- 查询每个部门的部门编号和每个部门的工资和：
SELECT deptno,SUM(sal) salSum FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门的人数：
SELECT deptno,COUNT(deptno) FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(deptno) TOTAL FROM emp WHERE sal > 1500 GROUP BY deptno;

– HAVING

-- 查询工资总和大于9000的部门编号以及工资和：
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
-- 查询每个员工工资大于1500的部门的工资总和大于6000的部门编号以及工资和：
SELECT deptno,SUM(sal) FROM emp WHERE sal>1500 GROUP BY deptno HAVING SUM(sal) > 6000;
-- 查询工资总和大于9000的部门编号以及工资和并根据工资总和降序
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000 ORDER BY SUM(sal) DESC;


-- 书写顺序 SELECT * FROM 表 WHERE 条件 GROUP BY 字段 HAVING 条件 ORDER BY 字段 LIMIT
-- 执行顺序 SELECT >FROM >WHERE >GROUP BY >HAVING >SELECT >ORDER BY >LIMIT

– LIMIT

 limit用来限定查询结果的起始行，以及总行数 LIMIT 0,3(从第1条开始展示3条)
-- 展示3条员工信息，从第1条开始
SELECT * FROM emp LIMIT 0,3;
-- 展示3条员工信息，从第4条开始
SELECT * FROM emp LIMIT 3,3;
-- int pageno=1;当前页
-- int pagesize=3;每页展示多少数据
-- SELECT * FROM emp LIMIT (pageno-1)*pagesize,pagesize;

-- 查询5行记录，起始行从0开始
SELECT * FROM emp LIMIT 0,5;
-- 查询10行记录，起始行从3开始
SELECT * FROM emp LIMIT 2,10;

/*
mysql的数据引擎默认是：innodb
myasim
*/
-- DESC 描述数据表字段信息
-- show 展示创建数据库/表的具体执行命令

-- DML 增(INSERT) 删(DROP) 改(UPDATE)
DROP TABLE t1;
DROP TABLE `user`;
-- 创建员工表
CREATE TABLE emp(
id INT,
name VARCHAR(20),
sex VARCHAR(10),
birthday DATE,
salary DOUBLE(7,2)
);

DML

-- 插入/增加 数据
-- INSERT INTO 表名（字段1,字段2,······,字段n）values(列值1,列值2,······,列值n);
INSERT INTO emp (id,name,sex,birthday,salary)VALUES(1,'张三','男','2018-8-2',99999.01);
INSERT INTO emp (id,name,birthday,salary,sex)VALUES(2,'王艳','2018-8-2',10000.01,'女');
INSERT INTO emp (id,name,birthday,salary,sex)VALUES(3,'王言','2018-8-2',10400.01,'女');
SELECT * FROM emp;
-- 删除
-- DELETE FROM 表名;
DELETE FROM emp; -- 删除表内所有信息
-- DELETE FROM 表名 WHERE 字段1=列值1;
DELETE FROM emp WHERE id=1;
-- 更新
-- UPDATE 表名 SET 字段1=列值1,字段2=列值2 where 字段名=列值;
UPDATE emp SET sex='女',birthday='2018-08-03',salary=4567.89 WHERE id=2;
INSERT INTO emp(id,sex)VALUES(5,'女');
UPDATE emp SET name='叶子',birthday='2010-01-01',salary=99999.99 WHERE id=5;
-- 如果插入的值顺序和个数完全一致，表名后的列值可以不写，直接写values
INSERT INTO emp VALUES(7,'王妍','女','2018-8-2',1400.01);

数据完整性_实体完整性

-- --------1) 主键(primary key)约束,数据唯一,且不能为null-----------
-- 索引,主键一般都是索引
-- 创建表时添加主键
CREATE TABLE user(
    id int PRIMARY KEY auto_increment COMMENT '添加主键约束,自增',
    name VARCHAR(20) COMMENT '用户姓名'
);
/*
主键自增,不指定键,默认从1开始自增,
如果指定键,且该键与原有键不重复也可以.
即使数据删除,主键记录不会删除,接着以前的id往后自增
*/
INSERT INTO user (name) VALUE('张三');
INSERT INTO user (name) VALUE('李四');
INSERT INTO user (name) VALUE('王五');

INSERT INTO user (id,name) VALUE(5,'赵六');

DELETE FROM user WHERE id=5;

INSERT INTO user (name) VALUE('赵六');

-- 创建表时添加主键方式2
CREATE TABLE user(
    id int ,
    name VARCHAR(20) COMMENT '用户姓名',
    accountId int,
    PRIMARY KEY(id,accountId)    -- 联合主键
);

-- 用修改语句添加主键
ALTER TABLE user add PRIMARY key(id);

-- ------------------------数据唯一约束---------------------
-- 创建表时限制字段数据唯一:unique
CREATE TABLE user(
    id int ,
    name VARCHAR(20) UNIQUE COMMENT '用户姓名'
);
INSERT INTO user VALUES(1,'张三');
INSERT INTO user VALUES(2,'张三'); -- 报错! name字段数据重复
INSERT INTO user VALUES(2,'李四');

-- --------------------自增列----------
-- Incorrect table definition;
-- there can be only one auto column and it must be defined as a key
-- 自增列需要声明为主键列,才能使用自增.
CREATE TABLE user(
    uid int auto_increment, -- 报错! 不是主键
    name VARCHAR(20) UNIQUE COMMENT '用户姓名'
);

– 引用完整性

-- 学生表
CREATE TABLE student(
stuid int PRIMARY KEY auto_increment,
stuname VARCHAR(20)
);
-- 成绩表
CREATE TABLE scroe(
id int,
score int,
-- 添加外键约束
constraint fk_id_sid FOREIGN KEY(id) REFERENCES student(stuid)
);
/*
RESTRICT:当子表已经引用了该数据,主表删除时,子表不允许删除
NO ACTION:类似RESTRICT
CASCADE:子表跟主表操作一样,主表删子表也删
SET NULL:当主表删除是,子表该行数据置为null
mysql默认是RESTRICT类型的

– 域完整性

-- 单元格的非空约束: NOT NULL
CREATE TABLE user(
id int NOT NULL,
name VARCHAR(20) NOT NULL,
sex  VARCHAR(10)
);
-- 默认值约束:DEFAULT
CREATE TABLE user(
id int,
name VARCHAR(20) NOT NULL,
sex  VARCHAR(10) DEFAULT '男'
);
INSERT INTO user(id,name) values(1,'李四');

多表联查

-- 内连接  
-- 表1 [INNER] JOIN  表2  on 表12之间的关系...
-- 查询全部员工的信息以及所属部门的信息
SELECT * FROM emp as e,dept d WHERE e.deptno=d.deptno;
-- SELECT * FROM dept;
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
SELECT * FROM emp e  JOIN dept d ON e.deptno=d.deptno;
-- 查询全部员工的信息,如果有部门,就把部门信息全部展示
-- 外连接
-- 左外连接 左表 LEFT [OUTER] JOIN 右表
-- 以左表为主:如果没有与右边匹配的,右表数据展示为null,保留左表全部数据
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
-- 右外连接 左表 RIGHT [OUTER] JOIN 右表
-- 以右表为主:如果没有与左边匹配的,左表数据展示为null,保留右表全部数据
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
--  子查询(嵌套查询)-------------------------------------
-- 查询与SCOTT同一个部门的全部员工
-- 第一步:查出SCOTT的部门编号
SELECT deptno from emp WHERE ename = 'SCOTT'; -- 20
-- 第二步:根据部门编号20查出所有该部门的员工
SELECT * FROM emp WHERE deptno=20;
-- 改进
SELECT * FROM emp WHERE deptno=(
SELECT deptno from emp WHERE ename = 'SCOTT');
-- 工资高于30号部门中工资最多那个人的所有人信息
SELECT * FROM emp WHERE sal >(
SELECT max(sal) FROM emp WHERE deptno = 30);
-- 查询员工编号7788的员工名称,工资 ,部门名称,部门地址
SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,
((SELECT dname,loc,deptno FROM dept WHERE deptno=
  (SELECT deptno FROM emp WHERE empno=7788))
) d WHERE e.empno=7788;
SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,
(SELECT deptno,dname,loc FROM dept) d
WHERE e.empno=7788 AND e.deptno=d.deptno

事务

DROP TABLE account;
CREATE table account(
id int PRIMARY KEY,
name VARCHAR(20),
balence int
);
INSERT INTO account VALUES(1,'张三',1000);
INSERT INTO account VALUES(2,'李四',1000);
-- 转账:张三给李四转100;
UPDATE account SET balence = balence-100 WHERE id=1;
-- 意外
UPDATE account SET balence = balence+100 WHERE id=2;
-- 事务:就是保证数据库操作要么同时成功,要么同时失败
-- INNODB 支持事务,
-- mysql默认是开启事务,且自动提交
-- 手动开始事务:
-- 开启事务:START TRANSACTION;
-- 开启事务:BEGIN;
BEGIN;
-- 转账:张三给李四转100;
UPDATE account SET balence = balence-100 WHERE id=1;
-- 意外
UPDATE account SET balence = balence+100 WHERE id=2;
-- 提交数据
COMMIT;
-- 数据回滚
ROLLBACK;
![在这里插入图片描述](https://img-blog.csdn.net/20181011212650869?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzQxOTcz/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)

– 事务特点:ACID

原子性(Atomicity):事务是个整体,要么同时失败,要么同时成功,不可分割.
一致性(CONSISTENCY):从一个一致状态到另一个一致状态.
隔离级别(ISOLATION):两个事务之间有隔离性,不相互影响.
持久性(Durability):事务一旦提交,不可改变.
因为隔离级别不同产生的问题:
1) 脏读:指一个事务读取了另外一个事务未提交的数据
2) 不可重复读:当一个事务开启,第一次读该条数据,和第二次读结果不一致,特指updata
3) 幻读(虚读):当一个事务开启,第一次读整个表数据,和第二次读数据条数不一致,特指insert
隔离级别:
读未提交:READ UNCOMMITTED:以上三种情况都有可能,
读已提交:READ COMMITTED:避免脏读,不能避免不可重复读和幻读(虚读)
可重复读:REPEATABLE READ:避免脏读,不可重复读,不能避免幻读(虚读)
串行化:SERIALIZABLE:全部可以避免
mysql的默认隔离级别:REPEATABLE-READ
-- 数据库锁:表锁
-- INNODB默认是行锁,如果更新/删除操作的数据使用的条件没有用到索引,
-- 自动提升为表锁.
-- 查看mysql的隔离级别
SELECT @@TX_isolation;
-- 修改当前数据库隔离级别:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;