DDL
/*
DDL 对数据库和表进行 CRUD
C CREATE 创建
R RETRIEVE 查询
A ALTER 修改 ALTER
D DELETE 删除
增删改查
*/
-- 对数据库的操作SQL
----------------------------------------------------------------------------
-- 创建数据库
-- 语法格式 CREATE DATABASE 数据库名称 --默认编码格式为Latin1 编码
-- CREATE DATABASE 数据库名称 CHARACTER SET 编码格式 --指定字符集的方式创建数据库
CREATE DATABASE db1 ;
CREATE DATABASE db1 CHARACTER SET utf8;
CREATE DATABASE IF NOT EXISTS db1
----------------------------------------------------------------------------
-- 查询当前数据库的基本信息
-- 语法格式 SHOW CREATE DATABASE 数据库名称
SHOW CREATE DATABASE db1;
-- 切换数据库
-- 语法格式 USE 数据库名称
USE db1_1;
-- 查询当前正在使用的数据库
-- 语法格式 SELECT DATABASE()
SELECT DATABASE();
-- 查询当前MySQL中存在的所有数据库
-- 语法格式 SHOW DATABASES
SHOW DATABASES;
----------------------------------------------------------------------------
-- 修改数据库的字符集
-- 语法格式 alter database 数据库名 character set 编码格式
ALTER DATABASE db1 CHARACTER SET utf8;
----------------------------------------------------------------------------
-- 删除数据库
-- 语法格式 drop database 数据库名称 将数据库从Mysql中永久删除
DROP DATABASE db1; --慎用
----------------------------------------------------------------------------
-- 对表的操作SQL
/*
对表的 CRUD
Mysql中常见的数据类型
int 整型
double 浮点型
char 字符串型 固定长度
varchar 字符串型 可变长度，只使用字符所占的长度
date 日期类型 yyyy-MM-dd
datetime 日期类型 yyyy-MM-dd HH:mm:ss
*/
----------------------------------------------------------------------------
-- 创建表
-- 语法格式 CREATE TABLE 表名(字段名称1 字段类型，字段名称2 字段类型(长度))
CREATE TABLE category (cid int, cname,varchar(20));
-- 快速创建一个表结构相同的表（复制表结构）
-- 语法格式 CREATE TABLE 新表名称 like 旧表名称
CREATE TABLE db2 like db1;
----------------------------------------------------------------------------
-- 查看表结构
-- 语法格式 DESC 表名称
DESC db2;
-- 查看当前数据库中所有数据库表名
-- 语法格式 SHOW TABLES
SHOW TABLES;
-- 查看创建表的sql
-- 语法格式 SHOW CREATE TABLE 表名
show create table db1;
----------------------------------------------------------------------------
-- 修改表
--修改表名
-- 语法格式 RENAME TABLE 旧表名 TO 新表名
RENAME TABLE db1 TO db1_1;
-- 修改表的字符集(编码)
-- 语法格式 ALTER TABLE 表名 CHARACTER SET 字符集
ALTER TABLE db1 character set utf8;
-- 向表中添加一个字段 关键字: add
-- 语法格式 ALTER TABLE 表名 ADD 字段名称 字段类型(长度)
ALTER TABLE db1 ADD ccode int;
-- 修改表中的类型或者长度 关键字： modify
-- 语法格式 ALTER TABLE 表名 MODIFY 字段名称 字段类型
ALTER TABLE db1 modify ccode varchar(10);
-- 修改列的名称 关键字：change
-- 语法格式 ALTER TABLE 表名 change 旧列名 新列名 字段类型
ALTER TABLE db1 change ccode code int;
----------------------------------------------------------------------------
-- 删除表
-- 语法格式 DROP TABLE 表名 --从数据库中永久的删除一张表
-- DROP TABLE IF EXISTS 表名 --判断表是否存在，如果存在就删除，不存在就不执行删除
DROP TABLE db1;
DROP TABLE IF EXISTS db1;
-- 删除表中的字段(也就是删除表中的某一列) 关键字：drop
-- 语法格式 ALTER TABLE 表名 drop 列名
ALTER TABLE db1 DROP code;
----------------------------------------------------------------------------
DML
/*
DML 对表中的数据进行 增删改查 IDUS
I INSERT 创建
R RETRIEVE 查询
U UPDATE 修改
D DELETE 删除
*/
----------------------------------------------------------------------------
-- 新增数据
-- 语法格式：INSERT INTO 表名(字段名1,字段名2...) VALUES(字段值1,字段值2...)
-- INSERT INTO 表名 VALUES (字段值1,字段值2...) --适用插入全部字段
-- INSERT INTO 表名(字段名1,字段名3) VALUES(字段值1,字段值3)
INSERT INTO student (sid,sname,age,sex,address) VALUES(1,'孙悟空',18,'男','花果山')
/* 注意事项
1.值与字段必须对应 个数&数据类型&长度 都必须一致
2.在插入 varchar char date datetime 类型时，必须使用单引号（推荐使用）或双引号进行包裹
3.如果插入空值 可以忽略不写 NULL 或''
*/
----------------------------------------------------------------------------
-- 修改数据
-- 语法格式：UPDATE 表名 SET 列名1 = 值1 , 列名2 = 值2 --会将指定列的数据全部修改（慎用）
-- UPDATE 表名 SET 列名 = 值 [WHERE 条件表达式: 字段名 = 值]
UPDATE student SET sex='女';
UPDATE student SET sex='男' WHERE sid= 1;
----------------------------------------------------------------------------
-- 删除
-- 语法格式： DELETE FROM 表名 --删除表中的所有数据（慎用）
-- DELETE FROM 表名 WHERE 条件表达式
DELETE FROM student WHERE sex = '女';
DELETE FROM student ;
/*
注意事项
1.DELETE FROM 表名 不推荐，对表中的数据逐条删除，效率低 结合主键自增来说，新增数据后，将累加
2.TRUNCATE TABLE 表名 推荐，删除整张表后在创建一个表结构相同的新表 结合主键自增来说，新增数据后，从1开始
*/
-------------------------------------------------------------------------------------------------
DQL
/*
DQL 操作单表
[] 表明可选择
*/
-----------------------------------------------------------------------------------------------
-- 基本查询
-- student 表中的所有数据
-- 语法结构 SELECT * FROM 表名 -- *表示所有的列
-- SELECT 列名1, 列名2 FROM 表名
SELECT * FROM student;
SELECT cid,csex from student;-- 别名查询
-- 关键字 ： AS --AS可以省略
SELECT cid AS '编号', cname AS '姓名' , sex '性别' FROM student;
-- 去重操作
-- 关键字 distinct
-- 语法结构 SELECT DISTINCT 列名 FROM 表名
SELECT DISTINCT cname FROM student;
-- 查询字段进行数值操作
SELECT age+5 FROM student;
/* 注意 查询不会改变表中的数据 以上为简单查询 */
-----------------------------------------------------------------------------------------------
-- 条件查询
/* 条件查询 WHERE
逻辑运算 AND <==> && 和（并）
OR <==> || 或
NOT 取反
!= <==> <> 不等于
BETWEEN <==> >= 大于等于
AND <==> <= 小于等于
IN(参数) 匹配括号中的参数
% 通配符，表示匹配任意多个字符串，一般与LIKE搭配使用 ；LIKE '%孙%' --表示某列值中包含'孙'的内容
_ 通配符，表示匹配一个字符串
*/
-----------------------------------------------------------------------------------------------
-- 排序
-- 关键字 order by 字句 ASC 升序排序（默认） DESC 降序排序
-- 语法结构 SELECT 字段名 FROM 表名 [WHERE 字段名 = 值] ORDER BY 字段名称 [ASC/DESC]
SELECT * FROM student order by age; -- 单列排序 ,默认升序
SELECT * FROM student order by age desc,name desc; -- 组合排序 ,如果第一个字段值相同，就按照第二字段进行排序
-------------------------------------------------------------------------------------------------
-- 聚合函数
-- 作用：将一列数据作为一个整体，进行纵向的计算
/* 常用聚合函数
count(字段) 统计记录数 注意：在统计时候会忽略空值
sum(字段) 求和操作
max(字段) 求最大值
min(字段) 求最小值
avg(字段) 求平均值
语法结构 SELECT 聚合函数(字段名) FROM 表名 [WHERE 条件]
*/
-------------------------------------------------------------------------------------------------
-- 分组查询
-- 关键字 group by
-- 分组的目的就是为了做统计操作，一般分组会和聚合函数一起，除外也只能查询分组的字段
-- 语法结构 SELECT 分组字段/聚合函数 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 判断条件]
SELECT sex ,avg(age) from student group by sex;
Select cname ,avg(age) from student where cname is not null group by cname having avg(age)> 18;
/*
WHERE 和 HAVING 的区别
WHERE : 1. 在分组前进行过滤 2. where 后面不能跟聚合函数
HAVING：1. 在分组后进行过滤 2. having 后面可以写聚合函数
*/
-------------------------------------------------------------------------------------------------
-- limit
-- 通过limit 去指定要查询的数据的条数 行数
-- 语法结构 SELECT 字段 FROM 表名 LIMIT OFFSET, LENGTH;
-- 参数说明： OFFSET 起始行数 默认从 0 开始计数
-- LENGTH 返回的行数（要查询几条数据）
select * from student limit 3,3; --显示第二，每页显示三条
--分页公式 起始行数 = (当前页码 - 1) * 每页显示条数
-------------------------------------------------------------------------------------------------
索引
概念
通过对数据表中的字段创建索引 来提高查询速度
| 类型 | 特点 |
| 主键索引（Primary key） | 是一个唯一性的索引，每个表中只能有一个主键索引 |
| 唯一索引（unique） | 索引列的所有数据只能出现一次，必须是唯一 |
| 普通索引（index） | 最常见的索引，作用就是提高对数据的访问速度 |
注意：表对应的索引被保存在一个索引文件中，如果对数据进行增删改操作，就需要对索引进行操作
创建方式
主键索引（primary key）
- 创建表时，直接添加主键的方式添加主键索引
- 使用DDL在建表后，添加索引 -- ALTER TABL 表名 ADD PRIMARY KEY (列名)
唯一索引（unique）
- CREATE UNIQUE INDEX 索引名 ON 表名 (列名(长度))
普通索引（index）
- create index 索引名 on 表名 (列名[长度])；
- alter table 表名 add index 索引名(列名)；
删除普通索引的方式
- alter table 表名 drop index 索引名
-------------------------------------------------------------------------------------------------
总结
创建索引的原则：优先选择为 经常出现在查询条件或排序、分组后面的字段创建索引
优点：可以大大的提高查询速度；减少查询中分组和排序的时间；通过创建唯一索引保证数据的唯一性
缺点：创建和维护索引需要时间，数据量越大，时间越长；表中的数据进行增删改操作时，也需要进行维护，降低了维护的速度；索引文件需要占据磁盘空间
-------------------------------------------------------------------------------------------------
视图
概念
是由查询结果形成的一张虚拟的表（只读的表）;
作用
如果某个查询的结果出现的十分频繁，并且查询语法比较复杂，就可以根据这条查询语句构建一张视图，方便查询
语法结构
CREATE VIEW 视图名（字段列表） as select 查询语句；
视图与表的区别
- 视图是建立在表的基础之上
- 通过视图 不要进行增删改操作，视图主要就是用来简化查询
- 删除视图,表不受影响；删除表，视图就不在起作用
-------------------------------------------------------------------------------------------------
存储过程
概念
就是一堆SQL语句的合并，中间加入了一些逻辑控制
优点
- 调试完就可以稳定运行（在业务需求相对稳定情况）
- 可以减少业务系统与数据库的交互
缺点
- 互联网项目中，较少使用存储过程，因为业务需求变化太快
- 移植十分困难
创建方式
- delimiter $$ -- 声明语句的结束符号 自定义 ||
create procedure 存储过程名() -- 声明存储过程
begin -- 开始编写存储过程
-- 要执行的SQL语句, 分号结束SQL语句
end $$ -- 存储过程结束
- create procedure 存储过程名(IN 参数名 参数类型)
- 变量赋值 ，语法格式： set @变量名=值
out 输出参数， 语法格式： out 变量名 数据类型
调用
- call 存储过程名称()；
删除
- DROP PROCEDURE [ IF EXISTS ] <过程名>
-------------------------------------------------------------------------------------------------
触发器DCL（数据控制语言）
创建用户
语法结构 create user '用户名'@'主机名' identified by '密码'；
用户授权
语法结构 grant [SELECT，权限2 ....]/[ALL] on 数据库名.表 to '用户名'@'主机名' ；
收回权限
语法结构 Revoke 权限 on 权限作用列或表 from '用户名'@'主机名'
查看用户权限
语法结构 show grants for '用户名'@'主机名'；
删除用户
语法结构 drop user '用户名'@'主机名'；
-------------------------------------------------------------------------------------------------
数据库备份&还原
SQLyog方式--客户端工具导出
命令行方式
备份 mysqldump -u用户名 -p密码 数据库名 > 文件路径
还原 source sql 文件地址
/*
约束 是指对数据进行一定的限制，来保证数据的完整性 有效性 正确性
一般结合表创建时使用
*/
-------------------------------------------------------------------------------------------------
多表、键和数据库设计
/*
约束 是指对数据进行一定的限制，来保证数据的完整性 有效性 正确性
一般结合表创建时使用
*/
-------------------------------------------------------------------------------------------------
-- 主键约束
-- 创建主键
-- 关键字 primary key -- 特点 不可重复 唯一 非空 --作用 用来表示数据库中的每一条记录
-- 语法格式 字段名 字段类型 primary key -- primary key(字段名) --通过DDL语句 添加主键约束
CREATE TABLE db1( did int primary key , dname varchar(20), sex char(1));
CREATE TABLE db2( did int , dname varchar(20), sex char(1),primary key(did));
ALTER TABLE db3 add primary key(did);
-- 删除主键
-- 语法格式 ALTER TABLE 表名 DROP PRIMARY KEY
ALTER TABLE db1 DROP PRIMARY KEY;
-- 主键的自增
-- 关键字 auto_increment --字段类型必须为整数类型
-- 语法格式 字段名 字段类型 primary key auto_increment
CREATE TABLE db1( did int primary key auto_increment , dname varchar(20), sex char(1));
-- 自定义自增起始值
CREATE TABLE db1( did int primary key auto_increment , dname varchar(20), sex char(1))auto_increment=200;
-----------------------------------------------------------------------------------------------
-- 唯一约束
--关键字 unique -- 特点 表中的某一列不能够重复（对NULL值 不做唯一判断）
-- 语法格式 字段名 字段类型 unique
CREATE TABLE db1( did int, dname varchar(20) unique, sex char(1));
/*
主键约束和唯一约束的区别
主键约束 要求唯一切不能为空
唯一约束 唯一但是可为空
一个表中只能有一个主键约束，但是可以有多个唯一约束
*/
-------------------------------------------------------------------------------------------------
-- 非空约束
-- 关键字 not null -- 特点 某一列不允许为空
-- 语法格式 字段名 字段类型 not null
CREATE TABLE db1( did int, dname varchar(20), sex char(1) not null);
-------------------------------------------------------------------------------------------------
-- 外键约束
-- 关键字 foreign key -- 作用 可以让两张表之间产生一个对应的关系，从而保证了主从表引用的完整性
-- 语法格式 CREATE TABLE 表名 (字段名 ... [constraint] [外键约束名] foreign key(字段名) refernce 主表(主表字段))
-- 注意事项 从表的外键类型必须与主表的主键类型一致
-- 添加数据时，应该先添加主表的数据
-- 删除数据的时候，要先删除从表中的数据，进而删除主表的数据
-- 级联删除 （了解）
---------------------------------------------------------------------
-- 默认值 关键字 default -- 特点 用来指定 某一列的默认值
-- 语法格式 字段名 字段类型 default 默认值
CREATE TABLE db1( did int primary key , dname varchar(20), sex char(1) default '女');
--------------------------------------------------------------------- /*
事务
概念：是一个由一条或者多条SQL组成的一个整体，事务中的操作，要不全部成功，要不全部失败
-- 手动提交事务
1.开启事务 start transaction;或者begin;
2.提交事务 commit;
3.回滚事务 rollback；
-- 自动提交事务
1.MySql的提交方式默认为 自动提交事务
2.DML 操作
*/
-- 取消自动提交
SHOW VARIABLES LIKE 'autocommit'; -- 查看当前事务状态
SET @@AUTOCOMMIT =OFF; -- 取消自动提交
/*
事务的四大特性
-- 原子性 每个事务都是一个整体，不可再拆分，事务中的所有SQL要不全部成功，要不全部失败
-- 一致性 事务在执行之前 数据库的状态，与事务执行之后的状态要保持一致
-- 隔离性 事务与事务之间不应该相互影响，执行时要保证隔离状态 通过设置不同的隔离级别来解决并发访问的问题;并发访问存在的问题有 脏读 不可重复读 幻读
read uncommitted ：读未提交
read committed 读已提交 可防止脏读 （Oracle 默认级别）
repeatable read 可重复读 可防止脏读 不可重复读 （MySQL 默认级别）
serializable 串行化 可防止脏读 不可重复读 幻读
注意：隔离级别，从小到大，安全性是越来越高，但是效率是越来越低
-- 持久性 一旦事务执行成功，对数据的修改是持久的
*/
-- 查看隔离级别
-- 语法格式 select @@tx_isolation;
-- 设置隔离级别
-- 设置完需要重新连接数据库
-- 语法格式 set global transaction isolation level 隔离级别
---------------------------------------------------------------------
/*
表与表的三种关系
一对多关系（1: N）：在"多"的一方建立外键指向"一"的一方的主键
多对多关系（N: N）: 需要创建第三张表（中间表），中间表中至少要有两个字段（表1、表2 的主键字段），作为中间表的外键
一对一关系（1：1）：可以在任意一方建立外键指向另一方的主键
*/
-- 多表查询的分类
---------------------------------------------------------------------
-- 内连接查询
-- 特点 通过指定的条件去匹配两张表中的数据，匹配上就显示匹配不上就不显示；求两表交集
-- 隐式内连接 语法格式 SELECT 字段名 ... FROM 左表,右表 WHERE 连接条件
-- 显式内连接 语法格式 SELECT 字段名 ... FROM 左表 [inner] join 右表 on 连接条件
SELECT * FROM products,category WHERE category_id=cid; -- 隐式内连接
SELECT * FROM products inner join category on category_id=cid; --显示内连接
-------------------------------------------------------------------------------------------------
-- 外连接查询
-- 左外连接 关键字 left [outer] join 语法格式 SELECT 字段名 FROM 左表 left join 右表 on 连接条件
-- 特点 已左表为基准，匹配右表中的数据，如果匹配上就显示，如果匹配不上，左表数据正常显示，右表数据显示为null
-- 右外连接 关键字 right [outer] join 语法格式 select 字段名 from 左表 right join 右表 on 连接条件
-- 特点 已右表为基准，匹配左表中的数据，如果匹配上就显示，如果匹配不上，右表数据正常显示，左表数据显示为null
---------------------------------------------------------------------
-- 子查询 subQuery
/* 一条SELECT 语句的结果，作为另外一条select语句的一部分
特点： 必须放在小括号中，作为父查询的条件使用（更多时候）
分类
where型子查询：将子查询结果作为父查询的比较条件使用
from型子查询： 将子查询的查询结果作为一张表使用；需要起一个别名，否则无法使用表中的字段
exists型子查询： 查询结果是单列多行的情况，可以将子查询的结果作为夫查询的 in函数中的条件使用 ；语法格式： Select 字段名 from 表名 where 字段 in (子查询)；
*/
-- WHERE 型
SELECT p.pname,p.price FROM products p where p.category_id= (SELECT cid FROM category WHERE cname= '化妆品');
-- FROM 型
省略
-- EXISTS 型
SELECT * FROM category WHERE cid in (SELECT distinct category_id FROM products WHERE price<2000);
/*
总结
1. 子查询如果是一个字段（单列），那么就在where 后面作条件
2. 如果是多个字段（多列）就当作一张表使用(要起别名)
*/
-------------------------------------------------------------------------------------------------
-- 数据库设计三范式
/*
三范式指的就是数据库设计的一个规则
作用 就是为了创建 冗余较小 结构合理的数据库
范式 就是设计数据库的要求（规范）
第一范式（1NF） 具有原子性，设计列要做到列不可拆分
第二范式（2NF） 一张表只能描述一件事
第三范式（3NF） 消除传递依赖，表中的信息如果能够被推导出来，就不要设计一个字段单独来记录
*/
-------------------------------------------------------------------------------------------------
/*
反三范式指的是通过增加冗余或者重复数据来提高数据库的读性能；浪费存储空间 节省查询时间（以空间换取时间）
总结
1. 尽量根据三范式的规则去设计数据库
2. 可以合理的加冗余字段，减少join操作，让数据库执行的更快
*/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
