知识的搬运工,仅供参考😄
SQL(Structure Query Language)
SQL: (结构化查询语言)
SQL通用语法
- SQL语句可以单行或者多行书写,以分号结尾。
- 可以使用空格和缩进来增强语句可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 3种注释方法
①单行注释:(-- 注释内容)(#注释内容)
②多行注释:(/* 注释内容 */)
show databases; -- 这是一段注释(--之后需要有一个空格)
show databases; #这也是一段注释
SQL分类
- DDL(Data Definition Language) 数据定义语言
用于定义数据库对象:数据库,表,列等。关键字:create,drop,alter 等 - DML(Data Manipulation Language) 数据库操作语言
用来对数据库中表的数据进行增删改。关键字:insert,delete,update 等 - DQL(Data Query Language) 数据查询语言
用来查询数据库中表的记录(数据)。关键字:select,where 等 - DCL(Data Control Language) 数据控制语言
用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等
DDL:操作数据库、表
-
操作数据库(操作语法):CRUD(五种)
C:Create 创建
*创建db数据库
*create database db;
*创建db数据库,判断是否存在,存在再创建
*create database if not exits db;
*创建db数据库,判断是否存在,并制定字符集为gbk
*create database if not exits db character set gbk;
R:Retrieve 查询
*查询所有数据库名称:
*show databases;
*查询某个数据库字符集:查询某个数据库的创建语句
*show create databases 数据库名称;
U:Update 修改
*修改数据库的字符集
*alter database 数据库名称 character set 字符集名称;
D:Delete 删除
*删除数据库
*drop database 数据库名称;
*判断数据库是否存在再删除
*drop database if exists 数据库名称;
使用数据库
*查询当前正在使用的数据库
*select database();
*使用数据库
*use 数据库名称; -
操作数据库(操作语法):CRUD(五种)
C:Create 创建
①语法:
creat table 表名(
列名1 数据类型1,
列名2 数据类型2,
…
列名n 数据类型n
);/* 最后一列最后不要“,” */
②复制表:
create table 表名 like 被复制的表名;
③ 数据库类型:
R:Retrieve 查询
*查询某个数据库中所有表的名称:
*show tables;
*查询表结构
*desc 表名称;
U:Update 修改
① 修改表名;
*alter table 表名 rename to 新表名;
②修改表的字符集;
*alter table 表名 character set 字符集名称;
③添加一列
*alter table 表名 add 列名 数据类型;
④修改列的名称或类型
*alter table 表名 change 列名 新列名 新数据类型;// 修改列名以及类型
*alter table 表名 modify 列名 新数据类型;//只修改类型
D:Delete 删除
⑤删除列
*drop table 表名;
*drop table if exists 表名
DML:增删改表中的数据
1.添加数据:
*语法:
*insert into 表名(列名1,列名2,...,列名n) values (值1,值2,...,值n);
*注意:
1.列名要和值一一对应。
2.如果表名后,不定义列名,则默认给所有列添加值。
*insert into 表名 values (值1,值2,...,值n);
3.除了数字类型,其他的类型需要引号(单引号双引号都可以)
2.删除数据
*语法:
*delete from 表名 {where 条件};//条件语句时不需要{}
*注意:
1.如果不加条件,则删除表中所有的记录。
2.如果要删除所有的记录:
1.delete from 表名; // 不推荐使用,表中有多少条记录就会执行多少次删除操作,逐条删除,效率低下。
2.TRUNCATE TABLE 表名;//先删除表,然后在创建一张一毛一样的表,效率高。
3.修改数据
*语法:
*update 表名 set 列名1 = 值1, 列名n = 值n where 条件;
*注意:
1,如果不加任何条件,则会将表中的所有记录修改。
DQL:查询表中的记录
1.语法:
select 字段 from
表名
where 条件列表
group by 分组字段
having 分组之后的条件限定
order by 排序
limit 分页限定;
2.基础查询:
1.查询多个字段:
select 字段1,字段2,...字段n from 表名;
注意:如果查询所有的字段,字段出可以用*代替字段列表(规范做法最好别用)
2. 去除重复:distinct
3.计算列
*一般可以使用四则运算的计算的一些列的值(一般只会进行数值型的计算)
*ifnull(表达式1(需要判断是否有null的字段),表达式2(如果为null将null替换为表达式2的值)):Null参与的运算,计算结果都为null。
4.起别名:在字段后面加个空格就可以为字段起新的名字(或者使用AS关键字 一般省略)
3.条件查询
1.where子句后面跟 字段 以及 条件判断
2.运算符。
*>,<,>=,<=,=,<>(不等于)
*BETWEEN low AND high //(包含low 和 high [low,high]闭区间)
*LIKE //模糊查询
*占位符:
*_:单个任意字符
*%:多个任意字符
例如:查找名字姓马的人:
SELECT * FROM 表名 WHERE name LIKE "马%"
*IS NULL ,IS NOT NULL //查询为空的记录,查询非空的记录
*AND 或 &&
*OR 或 || 或者 IN表达式//(a or b or c 相当于 in(a,b,c))
*NOT 或 !
查询
1.排序查询
*语法:
*order by 排序字段1 排序方式1,排序字段2 排序方式2 ...;
*排序:
*ASC:升序,默认的。
*DESC:降序。
*注意:
*如果有多个排序条件,则当前面的条件值一致时,才会判断第二条件,以此类推。
2.聚合函数:将一列数据作为一个整体,尽心纵向的计算
1.count:计算个数
2.max:计算最大值
3.min:计算最小值
4.sum:计算和
5.avg:计算平均值
*注意:聚合函数计算时,会派出NULL的值。
*解决方案:
1.选择不包含空的列进行计算。
2.IFNULL函数
3.分组查询
1.group by 需要分组的字段
SELECT 分组字段,聚合函数 FROM 表名 GROUP BY 分组字段;
2.注意:
1.分组之后查询的字段:分组字段,聚合函数。
2.where 和 having 的区别。
1.where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来。
2.where后不可以跟聚合函数,having可以进行聚合函数的判断。
例如: SELECT sex, AVG(math),COUNT(id) 自定义名 FROM student WHERE math>70 GROUP BY sex HAVING 自定义名 >2;
或者: SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT >2;
上述语句执行的功能为:将表中的数学分数大于70分的人选出,按照性别分组,分组后显示分组后的数学平均分,以及各个组的人数。将人数大于2人的组显示出来。
4.分页查询
1.语法:limit 开始的索引,每页查询的条数;
2.公式:SELECT * FROM 表名 LIMIT (当前页码-1)*每页条数 ,每页条数;
3.分页操作是一个“方言”。(limit是MySQL的方言)
2.约束
概念:对表中的数据进行限定,保证数据的正确性、有效性、完整性。
分类:
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
1.主键约束:primary key
1.注意:
1.含义:非空且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识
2.在创建表时,添加主键约束:
CREATE TABLE stu(
id INT PRIMARY KEY, --给id添加主键约束
phone_number VARCHAR(20)
);
3.删除主键约束:
ALTER TABLE stu DROP PRIMARY KEY;
4.创建表以后添加主键约束:
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
5.自动增长
1.概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值的自动增长
2.在创建表的时候来添加主键约束,并且完成主键的自动增长
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, --给id添加主键约束,并完成自增长
phone_number VARCHAR(20)
);
添加一条记录:
INSERT INTO stu VALUES(NULL,phone_number1);//主键id会从1开始编号
也可以手动输入id
INSERT INTO stu VALUES(4,phone_number2);
下一条自动增长的记录之和上一条记录的主键有关系
INSERT INTO stu VALUES(NULL,phone_number3);//主键id会继上一条记录往下编 id=5;
3.删除自动增长:
ALTER TABLE stu MODIFY id INT;
4.创建表之后添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
2.非空约束:not null(添加新记录时该字段不能为空)
1.创建表时添加约束:
CREATE TABLE stu(
id INT,
name VARCHAR(20) NOT NULL --name 字段不能为空
);
2.删除字段的非空约束:ALTER TABLE stu MODIFY name VARCHAR(20);
3.创建完表后添加非空约束:ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
3.唯一约束:unique (值不能重复)
1.创建表时添加唯一约束:
CREATE TABLE 表名(
id INT,
phone_number VARCHAR(20) UNIQUE --添加了唯一约束
);
2.删除唯一约束:
ALTER TABLE 表名 DROP INDEX phone_number;
3.创建表之后,添加唯一约束:
ALTER TABLE 表名 MODIFY phone_number VARCHAR(20) UNIQUE;
4.外键约束:foreign key,让表与表之间产生关系,从而保证数据的正确性
1.在创建表时,可以添加外键
*语法:
1.创建表的时候添加外键:
//创建部门表与员工表,使员工表中的部门字段关联部门表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age INT,
dep_id INT,-- 需要添加外键的字段
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCE department(id)
//CONSTRAINT 自己取一个外键名称 FOREIGN KEY (需要添加外键的字段) REFERENCE 需要添加引用的表名(字段名)
);
2.删除外键:
ALTER TABLE employee DROP FOREIGN KET emp_dept_fk;
3. 创建了表之后,添加外键:
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCE department(id);
//注意,在添加外键的时候应该保证employee表中的关联字段是包含在department表的关联字段里的。
4.级联操作
1.添加级联操作:
语法:
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCE department(id) ON UPDATE CASCADE; -- 级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCE department(id) ON DELETE CASCADE; -- 级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCE department(id) ON UPDATE CASCADE ON DELETE CASCADE; -- 级联更新和删除
2.分类:
1.级联更新:ON UPDATE CASCADE:
在更新被关联主键的值时(department.dep_id),employee表中的dep_id也会随之改变
2.级联删除:ON DELETE CASCADE
在删除被关联主键的值时(department.dep_id),employee表中的与删除值有关的dep_id记录也会被随之删除
*级联更新与级联删除可以同时使用,但是一定要谨慎使用一面级联删除太多相关记录。
数据库的设计
1.多表之间的关系
1.分类:
1.一对一(了解):
*如:人和身份证
*分析:一个人只有一个身份证,一个身份证只能对应一个人
2.一对多(多对一):
*如:部门和员工
*分析:一个部门有多个员工,一个员工只能对应一个部门
3.多对多:
*如:学生和课程
*分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
2.:实现方式:
1.一对一(了解):
*如:人和身份证
*实现方式:一对一关系实现,可以在任意一方添加唯一的外键指向另一方的主键
2.一对多(多对一):
*如:部门和员工
*实现方式:在多的一方建立外键,指向一的一方的主键。
3.多对多:
*如:学生和课程
*实现方式:多对多关系实现需要借助第三张中间表。
中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。
2.范式(百度)
3.数据库的备份与还原
======================================================================
1.多表查询
======================================================================
事务
1.事务的基本介绍:
1.概念:
*如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
2.操作:
1.开启事务:start transaction;
2.回滚:rollback;
3.提交:commit;
3.MySQL执行单条语句时默认自动提交,除非手动开启事务START TRANSACTION后手动提交COMMIT或者回滚ROLLBACK
*查看事务的默认提交方式:SELECT @@autocommit;-- 1→自动提交,0→手动提交
*修改默认提交方式:set @@autocommit = 1或者0;
剧透:oracle数据库默认是手动提交事务的
2.事务的四大特性:
1.原子性:是不可分割的最小单位,要么同时成功,要么同时失败。
2.持久性:当事务提交或者回滚后,数据库会持久化的保存数据。
3.隔离性:多个事务之间,相互独立。
4.一致性:事务操作前后,数据的总量不会改变。
3.事务的隔离级别
*概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题
*存在的问题:
1.脏读:一个事务,读取到另一个事务没有提交的数据。
2.不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
*隔离级别:(数据库的属性)
1.READ UNCOMMITTED:读未提交
存在问题:脏读,虚读,幻读。
2.READ COMMITTED:读已提交
存在问题:虚读,幻读
3.REPEATABLE READ:可重复读
存在问题:幻读
4.SERIALIZABLE:串行化(序列化)
没得问题
*注意:隔离级别从小到大安全性越来越高,但是效率越来越低
*查询数据库的隔离级别:
SELECT @@tx_isolation;
*设置数据库的隔离级别:
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
DCL
1.管理用户
1.添加用户:
*语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
2.删除用户:
*语法:DROP USER '用户名'@'主机名';(主机名写%代表可以登录任何的主机)
3.修改用户密码:
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
*要是忘记了mysql的root用户密码怎么办?
1.管理员权限运行cmd -->net stop mysql (停止mysql服务)
2.使用无验证方式启动mysql服务: mysql --skip-grant-tables
3.打开新的cmd窗口,直接输入mysql命令,回车。即可以成果登录。
4.use mysql;
5.UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = 'root';
6.关闭两个窗口。
7.打开任务管理器,手动结束mysqld.exe进程
8.启动mysql -->net start mysql;
9.使用新密码登录
4.查询用户:
-- 1.切换到mysql数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;
2. 权限管理:
1. 查询权限:
SHOW GRANTS FOR '用户名'@'主机名';
2. 授予权限:
语法:GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
举例:GRANT SELECT,UPDATE,...,DELETE ON 数据库名.表名 TO '用户名'@'主机名';
授予全部权限:GRANT ALL ON *.* TO '用户名'@'主机名';
3.撤销授权:
语法:REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
总结总结
1.DDL:操作数据库和表
2.DML:增删改表中的数据
3.DQL : 查询表中的数据
4. DCL:管理用户,授权
操作字段
删除某字段:
ALTER TABLE 表名 DROP 字段名;
添加字段:
ALTER TABLE 表名 ADD 字段名 字段类型(字段长度) DEFAULT 默认值 COMMENT '注释'
修改字段:
a、修改字段名:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名
b、修改字段类型
ALTER TABLE 表名 MODIFY 字段名 字段类型(字段长度)
更改字段顺序:
1.仅仅是调整字段的顺序:modify
ALTER TABLE 表名 MODIFY 需要调整的字段名 数据类型 修饰 AFTER 字段名;
ALTER TABLE 表名 MODIFY 需要调整的字段名 数据类型 修饰 FIRST;
2.调整字段的顺序,还要更改字段名
ALTER TABLE 表名 CHANGE 老字段名 新字段名 数据类型 AFTER 字段名;
ALTER TABLE 表名 CHANGE 老字段名 新字段名 数据类型 FIRST;
MYSQL
mysql索引
定义: 索引(Index)是帮助MySQL高效获取数据的数据结构。(总结:索引是一种数据结构)
什么情况下需要建立索引?
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- WHERE条件中很少使用的字段就不用去建立索引了
- 单键索引还是组合索引?(高并发环境下建议组合索引)
- 查询中排序的字段,排序字段若通过索引去访问,将大大提高排序的速度
- 查询中统计或者分组的字段
什么情况下不要建立索引?
- 频繁更新的字段不适合创建索引。(因为每次更新不仅仅是更新表中的记录,还会更新索引文件,比较耗时)
- 表的记录太少,不必建立索引(一般来说300万条数据以上开始优化,建立索引)
- 数据重复且分布平均的表字段不需要建立索引,例如对性别字段建立索引就没有太大的实际效果。因此应该只为最经常查询和最经常排序的数据列建立索引。