数据库是是按照数据结构来组织、存储和管理数据的仓库
最早的数据库:
通过大量的分类、比较和表格绘制的机器运行数百万穿孔卡片来进行数据的处理,其运行结果在纸上打印出来或者制成新的穿孔卡片。 而数据管理就是对所有这些穿孔卡片进行物理的储存和处理。
现在的数据库
当时计算机开始广泛地应用于数据管理,对数据的共享提出了越来越高的要求。传统的文件系统已经不能满足人们的需要。能够统一管理和共享数据的数据库管理系统(DBMS)应运而生。
SQL语言的功能包括查询、操纵、定义和控制,是一个综合的、通用的关系数据库语言,同时又是一种高度非过程化的语言,只要求用户指出做什么而不需要指出怎么做。
主要包含:MongoDB,Redis,HBase…
下载地址:https://dev.mysql.com/downloads/mysql/
下载完成后打开
点击确定,保存 打开cmd 输入 mysql -uroot -p密码
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。 不同的存储引擎性能是不一样的
是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。
MYISAM:
INNODB:
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
MEMORY:
注意写的顺序,先使用数据库,再去查有多少张表
举例: 查看数据库
使用数据库:
官网地址:http://www.navicat.com.cn/download/direct-download?product=navicat_mysql_cs_x64.exe&location=1&support=Y 网盘地址: https://pan.baidu.com/s/1_z9TbieMX9Kemnyj5WWrBQ 提取码:4dgw
一套文字符号及其编码,比较规则 的集合。 20世纪60年代初。美国标准化组织ANSI发布了第一个字符集。ASCII 后来又进一步变成了国际标准ISO-646
用来定义数据库对象:创建库,表,列等。
用来操作数据库表中的记录
用来查询数据
用来定义访问权限和安全级别
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。 MySQL支持所有标准SQL数值数据类型。
在mysql中,字符串类型和日期类型都要用单引号括起来。‘Joker’ ‘2020-01-01’
使用Navicate图形化界面工具 连接数据库:
create database 数据库名 character set utf8;
use joker;
create table student( -- 创建数据表
Id bigint,
name varchar(20),
age int
);
ALTER TABLE 表名 ADD 列名 数据类型;
DESC 表名;
ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE 表名 DROP 字段名;
RENAME TABLE 原始表名 TO 要修改的表名;
SHOW CREATE TABLE 表名;
ALTER TABLE 表名 CHANGE 原始列名 新列名 数据类型;
DROP TABLE 表名;
ALTER TABLE 表名 CHARACTER SET 字符集名称;
SELECT * FROM 表名;
INSERT INTO 表名(列名1,列名2 ...)VALUE (列值1,列值2...);
注意事项:
批量插入
INSERT INTO 表名(列名1,列名2 ...)VALUES (列值1,列值2...),(列值1,列值2...);
UPDATE 表名 SET 列名1=列值1,列名2=列值2 ... WHERE 列名=值
update students set age=20
update students set age=18 where name='小键';
update students set age=age+1 where name='小吴';
use mysql;
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
flush privileges;刷新MySQL的系统权限相关表
mysql8:
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
使用mysqladmin修改密码:
mysqladmin -u root -p password 123456
DELETE FROM 表名 【WHERE 列名=值】
TRUNCATE TABLE 表名;
DELETED 与TRUNCATE的区别
SELECT * FROM 表名;
数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。 结果集:
SELECT 列名1,列表2... FROM 表名;
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用一些运算符及关键字:
SELECT * FROM students WHERE gender='男' AND age=20;
SELECT * FROM students WHERE id ='1001' OR name='zs';
SELECT * FROM students WHERE id='1001' OR id='1002' OR 1001='1003';
SELECT * FROM students WHERE id IN('1001','1002','1003');
SELECT * FROM students WHERE age IS NULL;
SELECT * FROM students WHERE age>=18 AND age<=20;
SELECT * FROM students WHERE age BETWEEN 18 AND 20;
SELECT * FROM students WHERE gender !='男';
SELECT * FROM students WHERE name IS NOT NULL;
根据指定的关键进行查询, 使用LIKE关键字后跟通配符 通配符: _ :任意一个字符 %:任意0~n个字符
SELECT * FROM students WHERE name LIKE '_____';
-- 模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母,5个“_”表示5个任意字母。
SELECT * FROM students WHERE name LIKE '____s';
SELECT * FROM students WHERE name LIKE 'm%';
-- 其中“%”匹配0~n个任何字母。
SELECT * FROM students WHERE name LIKE '_u%';
SELECT * FROM stu WHERE name LIKE '%s%';
SELECT DISTINCT name FROM students;
SELECT *,字段1+字段2 FROM 表名;
列有很多记录的值为NULL, 因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。 下面使用了把NULL转换成数值0的函数IFNULL:
SELECT *,age+IFNULL(score,0) FROM students;
在上面查询中出现列名为sx+IFNULL(yw,0),这很不美观,现在我们给这一列给出一个别名,为total:
SELECT *, yw+IFNULL(sx,0) AS total FROM score;
SELECT *, yw+IFNULL(sx,0) total FROM score; -- 省略 AS
创建表:
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`gender` varchar(1) DEFAULT NULL,
`hire_date` date DEFAULT NULL,
`salary` decimal(10,0) DEFAULT NULL,
`performance` double(255,0) DEFAULT NULL,
`manage` double(255,0) DEFAULT NULL,
`department` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee` VALUES (1001, '张三', '男', '1991-7-25', 2000, 200, 500, '营销部');
INSERT INTO `employee` VALUES (1002, '李四', '男', '2017-7-5', 4000, 500, NULL, '营销部');
INSERT INTO `employee` VALUES (1003, '王五', '女', '2018-5-1', 6000, 100, 5000, '研发部');
INSERT INTO `employee` VALUES (1004, '赵六', '男', '1991-6-1', 1000, 3000, 4000, '财务部');
INSERT INTO `employee` VALUES (1005, '孙七', '女', '2018-3-23', 8000, 1000, NULL, '研发部');
INSERT INTO `employee` VALUES (1006, '周八', '男', '2010-9-8', 5000, 500, 1000, '人事部');
INSERT INTO `employee` VALUES (1007, '吴九', '女', '2017-7-5', 8000, 601, NULL, '研发部');
INSERT INTO `employee` VALUES (1008, '郑十', '女', '2014-4-6', 4000, 1801, NULL, '人事部');
对查询的结果进行排序 使用关键字ORDER BY 排序类型
SELECT *FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC, id ASC;
对查询的结果进行统计计算 常用聚合函数:
SELECT COUNT(*) AS total_record FROM employee;
SELECT COUNT(performance) FROM employee;
SELECT COUNT(*) FROM employee WHERE salary > 2500;
SELECT COUNT(*) FROM employee WHERE salary+IFNULL(performance,0) > 5000;
SELECT COUNT(performance), COUNT(manage) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary), SUM(performance) FROM employee;
SELECT SUM(salary+IFNULL(performance,0)) FROM employee;
SELECT AVG(salary) FROM employee;
查询最高工资和最低工资:
SELECT MAX(salary), MIN(salary) FROM employee;
什么是分组查询
将查询结果按照1个或多个字段进行分组,字段值相同的为一组
分组使用
SELECT gender from employee GROUP BY gender;
根据gender字段来分组,gender字段的全部值只有两个(‘男’和’女’),所以分为了两组 当group by单独使用时,只显示出每组的第一条记录 所以group by单独使用时的实际意义不大
group_concat(字段名)可以作为一个输出字段来使用 表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合
SELECT gender,GROUP_CONCAT(name) from employee GROUP BY gender;
通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个"值的集合"做一些操作
查询每个部门的部门名称和每个部门的工资和
SELECT department,SUM(salary) FROM employee GROUP BY department;
查询每个部门的部门名称以及每个部门的人数
SELECT department,COUNT(*) FROM employee GROUP BY department;
查询每个部门的部门名称以及每个部门工资大于1500的人数
SELECT department,COUNT(salary) FROM employee WHERE salary > 1500 GROUP BY department;
用来分组查询后指定一些条件来输出查询结果 having作用和where一样,但having只能用于group by
查询工资总和大于9000的部门名称以及工资和
SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
SELECT department,SUM(salary) FROM employee GROUP BY department;
总和大于9000
SELECT department,SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;
having是在分组后对数据进行过滤. where是在分组前对数据进行过滤 having后面可以使用分组函数(统计函数) where后面不可以使用分组函数 WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
查询工资大于2000的,工资总和大于6000的部门名称以及工资和
SELECT * FROM employee WHERE salary >2000;
SELECT department, GROUP_CONCAT(salary) FROM employee WHERE salary >2000 GROUP BY department;
SELECT department, SUM(salary) FROM employee WHERE salary >2000 GROUP BY department;
SELECT department, SUM(salary) FROM employee WHERE salary >2000 GROUP BY department HAVING SUM(salary)>6000;
SELECT department, SUM(salary) FROM employee
WHERE salary >2000
GROUP BY department
HAVING SUM(salary)>6000
ORDER BY SUM(salary) DESC;
从哪一行开始查,总共要查几行 Limit 参数1,参数2
角标是从0开始
格式:
select * from 表名 limit 0,3;
保证用户输入的数据保存到数据库中是正确的。
在创建表时给表中添加约束
表中的一行(一条记录)代表一个实体(entity)
标识每一行数据不重复。行级约束
特点:
添加主键约束的方式
CREATE TABLE 表名(字段名1 数据类型 primary key,字段2 数据类型);
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(要设置主键的字段));
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(主键1,主键2));
联合主键: 两个字段数据同时相同时,才违反联合主键约束。
1.先创建表 2.再去修改表,添加主键
ALTER TABLE student ADD CONSTRAINT PRIMARY KEY (id);
特点:
CREATE TABLE 表名(字段名1 数据类型 字段2 数据类型 UNIQUE);
特点:
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ,字段2 数据类型 UNIQUE);
限制此单元格的数据正确,不对照此列的其它单元格比较 域代表当前单元格
域完整性约束:
数据类型 : 数值类型、日期类型、字符串类型
非空约束(not null)
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ,字段2 数据类型 UNIQUE not null);
默认值约束(default)
CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ,字段2 数据类型 UNIQUE not null default '男');
插入的时候,values当中的值直接给default
什么是参照完整性
是指表与表之间的一种对应关系 通常情况下可以通过设置两表之间的主键、外键关系,或者编写两表的触发器来实现。 有对应参照完整性的两张表格,在对他们进行数据插入、更新、删除的过程中,系统都会将被修改表格与另一张对应表格进行对照,从而阻止一些不正确的数据的操作。 数据库的主键和外键类型一定要一致; 两个表必须得要是InnoDB类型 设置参照完整性后 ,外键当中的内值,必须得是主键当中的内容
一个表设置当中的字段设置为主键,设置主键的为主表
CREATE TABLE student(sid int PRIMARY key,name varchar(50) not null,sex varchar(10) default '男');
创建表时,设置外键,设置外键的为子表
CREATE TABLE score(
sid INT,
score DOUBLE,
CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student(id));
创建Car表
创建老师表
创建学生表
创建学生与老师关系表
关系图
添加外键
合并结果集就是把两个select语句的查询结果合并到一起
格式: UNION:
SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
创建表:
UNION:
UNION ALL:
注意事项:被合并的两个结果:列数、列类型必须相同。
也可以叫跨表查询,需要关联多个表进行查询
假设集合A={a,b},集合B={0,1,2}, 则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。 可以扩展到多个集合的情况 同时查询两个表,出现的就是笛卡尔集结果
内连接 图示:
作用:查询两张表的共有部分 语句:
Select <select_list> from tableA A Inner join tableB B on A.Key = B.Key
示例:
SELECT * from employee e INNER JOIN department d on e.depart_id = d.id;
多表连接: 建表:
使用99连接法:
使用内联查询
图示
作用: 把左边表的内容全部查出,右边表只查出满足条件的记录 语句:
Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key
示例
SELECT * from employee e LEFT JOIN department d on e.depart_id = d.id;
图示:
作用
把右边表的内容全部查出,左边表只查出满足条件的记录
语句
Select <select_list> from tableA A Right Join tableB B on A.Key = B.Key
示例
SELECT * from employee e RIGHT JOIN department d on e.depart_id = d.id;
什么是子查询
一个select语句中包含另一个完整的select语句。 或两个以上SELECT,那么就是子查询语句了。
子查询出现的位置
示例表
使用
把第1条查出来的结果当第2天语句的条件
将传入的字符连接成一个字符串 任何字符串与null进行连接结果都是null
将字符串str从x位置开始,y个字符长的子串替换为指定的字符
将字符串转成小写或大写
分别返回字符串最左边的x个字符和最右边的x个字符
如果第二个参数为null,那么不返回任何字符
用字符串pad对str最左边或最右边进行填充,直接到长度为n个字符长度
去掉字符串当中最左侧和最右侧的空格
去掉字符串左右的空格
返回str重复x次的结果
用字符串b替换字符串str中所有出现的字符串a.
返回字符串str中第x位置起y个字符长度的字符
返回X的绝对值
小数不为零部分上取整,即向上取最近的整数
小数部分下取整,即向下取最近的整数
返回X/Y的模
返回0-1内容的随机值
返回当前日期,只包含年月日
返回当前时间,只包含时分秒
返回当前日期和时间,年月日时分秒都包含
返回当前日期的时间戳
将一个时间戳转换成日期
返回当前是一年中的第几周
返回所给日期是那一年
返回当前时间的小时
返回当前时间的分钟
按字符串格式化日期date值
计算日期间隔
计算两个日期相差的天数
如果value是真,返回t,否则返回f
如果value1不为空,返回value1否者返回value2
返回当前数据库名
返回当前数据库版本
返回当前登陆用户名
对str进行加密
返回str的MD5值
原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚
隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
持久性是指一个事务一旦被提交了,就不能再回滚了,已经把数据保存到数据库当中了。
举例:
举例:
举例:
select @@global.tx_isolation,@@tx_isolation;
set global transaction isolation level read committed;
set session transaction isolation level read committed;
限制一个用户能够做什么事情,在MySQL中,可以设置全局权限,指定数据库权限,指定表权限,指定字段权限
创建用户:
create user '用户名'@'localhost' identified by '密码';
删除用户:
DROP USER 用户名称
分配权限:
GRANT 权限 (columns) ON 数据库对象 TO 用户 IDENTIFIED BY "密码" WITH GRANT OPTION
MySQL8新增了角色(role)的概念,使账号权限的管理,更加灵活方便。 角色,就是一些权限的集合。然后再把角色授权给某个账户
create role 'app_dev','app_read','app_write';
查询授权情况
show grants for 'app_dev';
USAGE “无权限” 就是让你这个用户可以像个用户似的登录,但是除了能看到有哪些数据库外,什么权限也没有
创建的角色也和账号一样保存在mysql.user表中
select * from mysql.user;
给指定角色权限
grant select,insert,update,delete on *.* to app_dev;
grant select on mydb.* to app_read;
grant insert,update,delete on mydb.* to app_write;
create user myuser2 identified with mysql_native_password by 'myuser2';
grant app_write to myuser2;
show grants for myuser1;
看到的是角色,并不是具体的权限 如果要查看具体的权限则需要这样执行show grants
show grants for myuser2 using app_dev
通过使用using app_dev,会将账号和角色的权限一并显示 4. 可以继续给角色添加权限
grant create on mydb.* to app_dev;
查看当前角色
select current_role()
激活指定用户授权的所有角色
set default role all to myuser1
revoke create on sys.* from '角色名称'@'127.0.0.1';
revoke all on sys.* from 'app_dev'@'127.0.0.1';
drop user '用户名'@'127.0.0.1';
create [UNIQUE] index 索引名称 ON 表名(字段(长度))
alter 表名 add [unque] index[索引名称] on(字段(长度))
show index from 表名
drop index[索引名称] on 表名
添加一个主键,索引必须是唯一索引,不能为NULL
alter table tab_name add primary key(column_list)
创建的索引是唯一索引,可以为NULL
alter table tab_name add unque index_name(column_list)
普通索引,索引值可出现多次
alter table tab_name add index index_name(column_list)
全文索引
alter table tab_name add fulltext index_name(column_list)
适合建立索引
CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名 [(属性清单)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];
ALGORITHM参数:
WITH CHECK OPTION:更新数据时不能插入或更新不符合视图限制条件的记录。 LOCAL和CASCADED:为可选参数,决定了检查测试的范围,默认值为CASCADED。
CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
drop view 视图名称;
创建存储过程
CREATE PROCEDURE 名称()
BEGIN
语句
END $$
调用存储过程
call 名称();
删除存储过程
drop procedure 名称
存储过程变量
DECLARE total_sale INT DEFAULT 0;
SET total_count = 10;
使用SELECT INTO语句将查询的结果分配给一个变量
SELECT COUNT(*) INTO total_products FROM products
三种类型
定义参数:create produce name(模式,参数名称 数据类型(大小))
IF expression THEN
statements;
END IF;
##################
IF expression THEN
statements;
ELSE
else-statements;
END IF;
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
END CASE;
WHILE expression DO
statements
END WHILE
#############
REPEAT
statements;
UNTIL expression
END REPEAT
查看所有存储过程:
SHOW PROCEDURE STATUS;
查看指定数据库中的存储过程
SHOW PROCEDURE STATUS WHERE db = 'My_test4';
查看指定存储过程源代码
SHOW CREATE PROCEDURE 存储过程名