MySQL

一数据库简介

存储数据的仓库

本质上是一个文件系统以文件的方式存在服务器电脑上

所有关系型数据库都可以使用通用的SQL语句进行管理

常见的数据库软件有 MySQL Oracle PostgreSQL…

二 MySQL简介

开源免费的数据库小型数据库功能强大已经被Oracle收购 MySQL6.x版本也开始收费

1. MySQL安装

　　Linux 安装MySQL(使用Mac远程访问)

2. MySQL登录

　　mysql -u用户名 -p密码

　　mysql -hIP地址 -u用户名 -p密码

3. MySQL退出

　　exit

　　quit

4. MySQL常见数据类型

　　INT 整数类型

　　DOUBLE(X,Y) 小数类型最多有X位小数点后面保留Y位

　　DATE 日期只包含年月日 yyyy-MM-dd

　　DATETIME 日期包含年月日时分秒 yyyy-MM-dd HH:mm:ss

　　TIMESTAMP 时间戳默认使用当前的系统时间来自动赋值

　　VARCHAR(X) 字符串最多有X个字符

三 SQL简介

Structured Query Language 结构化查询语言

定义了操作所有关系型数据库的规则

通用的数据库操作语言可以用在不同的数据库中不同的数据库SQL语句有一些区别

四 SQL语句分类

1. DDL: Data Definition Language 数据定义语言比如建库建表

2. DML: Data Manipulation Language 数据操作语言比如对表数据的增删改

3. DQL: Data Query Language 数据查询语言比如对表数据的查询

4. DCL: Data Control Language 数据控制语言比如对用户权限的设置

五库操作

创建数据库

CREATE DATABASE 数据库名称;

创建数据库判断不存在再创建

CREATE DATABASE IF NOT EXISTS 数据库名称;

创建数据库并指定默认编码

CREATE DATABASE 数据库名称 CHARACTER SET utf8;

创建数据库判断不存在再创建并指定默认编码

CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET utf8;

删除数据库

DROP DATABASE 数据库名称;

删除数据库判断存在再删除

DROP DATABASE IF EXISTS 数据库名称;

修改当前使用的数据库

USE 数据库名称;

修改数据库的字符集

ALTER DATABASE 数据库名称 CHARACTER SET utf8;

查看当前使用的数据库

SELECT DATABASE();

查看所有数据库

SHOW DATABASES;

查看指定数据库的定义信息

SHOW CREATE DATABASE 数据库名称;

六单表操作

创建表

CREATE TABLE account (
    aid INT,
    aname VARCHAR(100),
    amoney DOUBLE
);

创建表指定主键主键特点是非空且唯一

CREATE TABLE account (
    aid INT PRIMARY KEY,
    aname VARCHAR(100),
    amoney DOUBLE
);

创建表指定主键且主键自动增长

CREATE TABLE account (
    aid INT PRIMARY KEY AUTO_INCREMENT,
    aname VARCHAR(100),
    amoney DOUBLE
);

创建表复制

CREATE TABLE account_new LIKE account;

删除表

DROP TABLE account;

删除表判断存在再删除

DROP TABLE IF EXISTS account;

修改表名

ALTER TABLE account RENAME TO account_new;

修改表的字符集

ALTER TABLE account CHARACTER SET utf8;

修改表添加列

ALTER TABLE account ADD asex INT;

修改表修改列的类型长度约束

ALTER TABLE account MODIFY asex VARCHAR(1) NOT NULL;

修改表修改列的名称类型长度约束

ALTER TABLE account CHANGE asex asex_new INT;

修改表删除列

ALTER TABLE account DROP asex_new;

查看数据库中的所有表

SHOW TABLES;

查看表结构

DESC account;

查看指定表的定义信息

SHOW CREATE TABLE account;

七单表数据操作

插入表纪录

INSERT INTO account VALUES (1, '吃饭支出', 247);
INSERT INTO account (aid, aname, amoney) VALUES (2, '工资收入', 12345);
INSERT INTO account (aid, aname, amoney) VALUES (3, '服装支出', 1000);
INSERT INTO account (aid, aname, amoney) VALUES (4, '吃饭支出', 325);
INSERT INTO account (aid, aname, amoney) VALUES (5, '股票收入', 8000);
INSERT INTO account (aid, aname, amoney) VALUES (6, '打麻将支出', 8000);
INSERT INTO account (aid, aname, amoney) VALUES (7, NULL, 5000);
INSERT INTO account (aid, aname, amoney) VALUES (8, '打麻将支出', 2000);

删除所有记录一条一条删除不清空AUTO_INCREMENT记录数

DELETE FROM account;

删除所有记录直接将表删除重新建表 AUTO_INCREMENT归零效率更高

TRUNCATE TABLE account;

删除指定记录一条一条删除不清空AUTO_INCREMENT记录数

DELETE FROM account WHERE aid = 1;

修改所有记录

UPDATE account SET aname = '钱都用光了';

修改指定记录

UPDATE account SET aname = '捡到一袋子钱', amoney = 88888 WHERE aid = 1;

查询语法

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组之后的条件 ORDER BY 排序 LIMIT 分页

查询所有字段

SELECT * FROM account;

查询指定字段

SELECT aname, amoney FROM account;

查询去除重复记录

SELECT DISTINCT amoney FROM account;

查询别名

SELECT * FROM account AS a;
SELECT * FROM account a;
SELECT aname, amoney AS money FROM account;
SELECT aname, amoney money FROM account;

查询运算

SELECT aname, amoney + 100 FROM account;

查询不等于

SELECT * FROM account WHERE aname <> '吃饭支出';

查询大于

SELECT * FROM account WHERE amoney > 1000;

查询之间

SELECT * FROM account WHERE amoney >= 2000 AND amoney <= 5000;
SELECT * FROM account WHERE amoney BETWEEN 2000 AND 5000;

查询或

SELECT * FROM account WHERE amoney = 1000 OR amoney = 3500 OR amoney = 5000;
SELECT * FROM account WHERE amoney IN (1000, 3500, 5000);

查询包含

SELECT * FROM account WHERE aname LIKE '%支出%';

查询字符长度匹配

SELECT * FROM account WHERE aname LIKE '_____';

查询不为NULL

SELECT * FROM account WHERE aname IS NOT NULL;
SELECT * FROM account WHERE NOT (aname IS NULL);

查询升序+降序

SELECT * FROM account ORDER BY amoney ASC, aid DESC;

查询条件+升序排序必须放在最后

SELECT * FROM account WHERE aname LIKE '%支出%' ORDER BY amoney ASC;

查询求和对表中数据条数求和

SELECT COUNT(aid) FROM account;
SELECT COUNT(IFNULL(aname, 0)) FROM account;

查询求和对表中数据内容求和一般是数值型

SELECT SUM(amoney) FROM account WHERE aname LIKE '%收入%';

查询最大值一般是数值型

SELECT MAX(amoney) FROM account;

查询最小值一般是数值型

SELECT MIN(amoney) FROM account;

查询平均值一般是数值型

SELECT AVG(amoney) FROM account;

查询分组必须跟随聚合函数

SELECT aname, SUM(amoney) FROM account WHERE aname LIKE '%支出%' GROUP BY aname HAVING SUM(amoney) > 888 ORDER BY SUM(amoney) ASC;

查询分页参数一=开始索引参数二=每页显示个数 pageSize

SELECT * FROM account LIMIT (N - 1) * pageSize, pageSize;
SELECT * FROM account LIMIT 0, 3;
SELECT * FROM account WHERE aname LIKE '%支出%' ORDER BY amoney ASC LIMIT 2, 2;

八约束

对表中的数据进行限定保证数据的正确性有效性和完整性

1. 主键约束 primary key

非空且唯一一张表只能有一个字段为主键主键就是表中记录的唯一标识

a. 在创建表时添加主键约束

CREATE TABLE demo_primary_key (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

b. 删除主键约束删除不了自动增长的主键

ALTER TABLE demo_primary_key DROP PRIMARY KEY;

c. 创建表后添加主键约束

ALTER TABLE demo_primary_key MODIFY id INT PRIMARY KEY;

2. 非空约束 not null

值不能为null

a. 在创建表时添加非空约束

CREATE TABLE demo_not_null (
    id INT,
    name VARCHAR(20) NOT NULL
);

b. 删除非空约束

ALTER TABLE demo_not_null MODIFY name VARCHAR(20);

c. 创建表后添加非空约束

ALTER TABLE demo_not_null MODIFY name VARCHAR(20) NOT NULL;

3. 唯一约束 unique

值不能重复

a. 在创建表时添加唯一约束

CREATE TABLE demo_unique (
    id INT,
    name VARCHAR(20) UNIQUE
);

b. 删除唯一约束

ALTER TABLE demo_unique DROP INDEX name;

c. 创建表后添加唯一约束

ALTER TABLE demo_unique MODIFY name VARCHAR(20) UNIQUE;

4. 外键约束 foreign key

让表于表产生关系从而保证数据的正确性

a. 在创建表时添加外键约束

CREATE TABLE 表名 (
    ...
    外键列
    CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表字段名称)
);

b. 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

c. 创建表后添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表字段名称);

九多表操作(一对多)

1. 表操作

创建表一

CREATE TABLE category (
    cid VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100)
);

创建表多

CREATE TABLE product (
    pid VARCHAR(32) PRIMARY KEY,
    pname VARCHAR(100),
    pprice DOUBLE,
    pcid VARCHAR(32)
);

添加外键约束

ALTER TABLE product ADD CONSTRAINT product_fk FOREIGN KEY (pcid) REFERENCES category (cid);

删除外键约束

ALTER TABLE product DROP FOREIGN KEY product_fk;

2. 数据操作

插入表纪录

INSERT INTO category VALUES ('c001', '家电');
INSERT INTO category VALUES ('c002', '服饰');
INSERT INTO category VALUES ('c003', '化妆品');
INSERT INTO product VALUES ('p001', '联想', 5000, 'c001');
INSERT INTO product VALUES ('p002', '海尔', 3000, 'c001');
INSERT INTO product VALUES ('p003', '雷神', 5000, 'c001');
INSERT INTO product VALUES ('p004', 'JACK JONES', 800, 'c002');
INSERT INTO product VALUES ('p005', '真维斯', 200, 'c002');
INSERT INTO product VALUES ('p006', '花花公子', 440, 'c002');
INSERT INTO product VALUES ('p007', '劲霸', 2000, 'c002');
INSERT INTO product VALUES ('p008', '香奈儿', 800, 'c003');
INSERT INTO product VALUES ('p009', '相宜本草', 200, 'c003');

查询隐式内连接两表交集

SELECT * FROM category, product WHERE cid = pcid;

查询显示内连接两表交集

SELECT * FROM category INNER JOIN product ON cid = pcid;

查询左外连接左表全部和两表交集

SELECT * FROM category LEFT JOIN product ON cid = pcid;

查询右外连接右表全部和两表交集

SELECT * FROM category RIGHT JOIN product ON cid = pcid;

查询子查询一条SELECT语句结果作为另一条SELECT语句一部分

SELECT * FROM product WHERE pcid = (SELECT cid FROM category WHERE cname = '化妆品');

十多表操作(多对多)

创建表多

CREATE TABLE goods (
    gid VARCHAR(32) PRIMARY KEY,
    gname VARCHAR(100),
    gprice DOUBLE
);

创建表多

CREATE TABLE orders (
    oid VARCHAR(32) PRIMARY KEY,
    ototal_price DOUBLE
);

创建表中间表

CREATE TABLE item (
    gid VARCHAR(32),
    oid VARCHAR(32)
);

添加外键约束

ALTER TABLE item ADD CONSTRAINT item_goods_fk FOREIGN KEY (gid) REFERENCES goods (gid);
ALTER TABLE item ADD CONSTRAINT item_orders_fk FOREIGN KEY (oid) REFERENCES orders (oid);

删除外键约束

ALTER TABLE item DROP FOREIGN KEY item_goods_fk;
ALTER TABLE item DROP FOREIGN KEY item_orders_fk;

十一数据库设计范式

设计数据库时需要遵循的一些规范要遵循后边的范式要求必须先遵循前边的所有范式要求

1. 第一范式(1NF) 原子性

每一列都是不可分割的原子数据项

表中的每一列不可再拆分

2. 第二范式(2NF) 不产生局部依赖

在1NF的基础上非码属性必须完全依赖于码

表中的每一列都完全依赖于主键

3. 第三范式(3NF) 不产生传递依赖

在2NF的基础上任何非码属性不依赖于其它非码属性

表中的每一列都直接依赖于主键而不是通过其它列来间接依赖于主键数据不能存在传递关系

十二数据库命名规范

1. 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’_’组成命名简洁明确多个单词用下划线’_’分隔

2. 全部小写命名禁止出现大写

3. 禁止单独使用数据库关键字比如name time password等等

4. 表名称不应该取得太长(一般不超过三个英文单词)

5. 用单数形式表示名称比如使用user而不是users

6. 表名业务表前缀business 系统表前缀system

7. 如果该表主键需要作为别的多个表的外键需要有前缀区分并且命名一致比如产品类型表business_product_type 表的主键会被产品表business_product和价格表business_price等多张表设为外键那么business_product_type表的主键不能是id 应该是product_type_id 产品表business_product和价格表business_price的外键命名也应该是product_type_id

十三事务

1. 简介

如果一个包含多个步骤的业务操作被事务管理那么这些操作要么同时成功要么同时失败

2. 自动提交事务

MySQL默认每一条DML(增删改)语句都是一个单独的事务每条语句都会自动开启一个事务语句执行完毕自动提交事务 MySQL默认开启自动提交事务

3. 手动提交事务

a. 执行成功的情况

　　开启事务 -> 执行多条SQL语句 -> 提交事务

　　START TRANSACTION; -> SQL… -> COMMIT;

b. 执行失败的情况

　　开启事务 -> 执行多条SQL语句 -> 回滚事务

　　START TRANSACTION; -> SQL… -> ROLLBACK;

4. 修改事务提交方式

查看事务提交方式

　　SELECT @@autocommit; //0=手动提交 1=自动提交

修改事务提交方式

　　set @@autocommit = 0; //0=手动提交 1=自动提交

5. 事务的四大特征

原子性: 是不可分割的最小操作单位要么同时成功要么同时失败

持久性: 当事务提交或回滚后数据库会持久化的保存数据

隔离性: 多个事务之间相互独立

一致性: 事务操作前后数据总量不变

6. 事务的隔离级别

多个事务之间保持隔离互不影响如果多个事务操作同一批数据则会引发一些问题设置不同的隔离级别就可以解决这些问题

a. 存在问题

1> 脏读: 一个事务读取到另一个事务中没有提交的数据

2> 不可重复读(虚读): 在一个事务中两次读取到的数据内容不一样这是update时引发的问题

3> 幻读: 在一个事务中两次读取到的数据数量不一样这是insert或delete时引发的问题

b. 隔离级别

隔离级别越高性能越差安全性越高

1> read uncommitted 读未提交

　　产生的问题: 脏读不可重复读幻读

2> read committed 读已提交 Oracle默认

　　产生的问题: 不可重复读幻读

3> repeatable read 可重复读 MySQL默认

　　产生的问题: 幻读

4> serializable 串行化

　　可以解决所有的问题

c. 查看数据库隔离级别

查看数据库隔离级别

　　select @@tx_isolation;

修改数据库隔离级别重新连接才会生效

　　set global transaction isolation level 隔离级别字符串;

本文链接：https://www.cnblogs.com/huangyi-427/p/13345598.html

MySQL

MySQL的更多相关文章

随机推荐

热门专题

目录导航