MySQL
MySQL简介
MySQL常用命令
启动和关闭数据库
-- 启动数据库
net start mysql
-- 关闭数据库
net stop mysql
连接数据库
-- 连接数据库格式:mysql-h服务器主机地址 -u用户名 -p密码
-- 连接本机
mysql -uroot -proot
查看系统的版本
SELECT VERSION();
查询自增步长
SELECT @@auto_increment_increment;
MySQL数据类型
数值类型
数据类型 | 简介 | 字节数 |
---|---|---|
tinyint | 十分小的数据 | 1个字节 |
smallint | 较小的数据 | 2个字节 |
mediumint | 中等大小的数据 | 3个字节 |
int | 标准的整数(常用) | 4个字节 |
bigint | 较大的数据 | 8个字节 |
float | 浮点数 | 4个字节 |
double | 浮点数 | 8个字节 |
decimal | 字符串形式的浮点数(金融计算常用) |
字符类型
数据类型 | 简介 | 范围 |
---|---|---|
char | 字符串固定大小 | 0~255 |
varchar | 可变字符串 | 0~65535 |
tinytext | 微型文本 | 2^8-1 |
text | 文本串(保存大文本) | 2^16-1 |
日期类型
- date YYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 , 1970.1.1到现在的毫秒数
- year 年份表示
运算符
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b;a&&b | 逻辑与,两个为真,结果为真 |
or || | a or b;a||b | 逻辑或,其中一个为真,结果为真 |
Not ! | not a;! a | 逻辑非,真为假,假为真 |
比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为NULL,结果为真 |
BETWEEN | a between b and c | 若a与b和c之间,则结果为真 |
LIKE | a like b | SQL匹配,如果a匹配b,则结果为真 |
IN | a in (a1,a2,a3…) | 假设a在a1,或者a2…其中的某一个值中,结果为真 |
MySQL字段约束
整数(Unsigned)
- 无符号的整数
- 声明了该列不能为负数
填充(ZeroFill)
- 0填充
- 不足的位数,使用0来填充(例如:int(3) 5—>005)
自增(Auto increment)
- 通常理解为自增,自动在上一条记录的基础上+1
- 通常用来设计唯一主键,必须是整数类型
- 可以自定义设置主键自增的初始值和步长
非空(Not null)
- 假设设置为非空,如果不赋值,就会报错
- Null,如果不填写,默认是Null
默认(Default)
- 设置默认值
- sex,默认值为男
SQL(Structed Query Language):结构化查询语言
DDL
DDL(Data Definition Language)数据定义语言
用来定义数据库对象:数据库,表,列等。关键字:create, drop,alter 等
数据库操作
创建一个数据库
CREATE DATABASE school;
创建一个数据库(判断是否存在)
CREATE DATABASE IF NOT EXISTS school;
查看数据库列表
SHOW DATABASES;
选择数据库
USE school;
删除数据库
drop database school;
查询数据库创建语句
SHOW CREATE DATABASE school;
查询该数据库中的表
SHOW TABLES;
表操作
创建表
-- 注意点
-- 表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT自增
-- 字符串使用''括起来
-- PRIMARY KEY 主键,一般一个表只有唯一的主键,尽量独立出来不要写在语句中
-- ENGINE 搜索引擎
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET =utf8;
查询表
SELECT * FROM student;
查看数据库表结构
DESCRIBE student;
DESC student;
查看数据库表创建语句
SHOW CREATE TABLE student;
修改表
-- 修改表名
ALTER TABLE student RENAME AS student1;
-- 增加表字段
ALTER TABLE student1 ADD age INT(11) COMMENT '年龄';
-- 修改表字段(重命名,约束)
ALTER TABLE student1 MODIFY age VARCHAR(11); --修改约束
ALTER TABLE student1 CHANGE age age1 INT(1); --字段重命名
-- 删除表字段
ALTER TABLE student1 DROP age1;
删除表
-- 删除表
DROP TABLE IF EXISTS student1;
主外键操作
添加主键
ALTER TABLE `student` ADD CONSTRAINT `id` PRIMARY KEY`student`(id);
添加外键
创建方式一:在创建表的时候添加约束
-- 创建班级表
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给外键添加约束 执行reference引用
CREATE TABLE IF NOT EXISTS `student` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
方式二:创建表成功后添加外键约束
-- 创建班级表
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建学生表
CREATE TABLE IF NOT EXISTS `student` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
PRIMARY KEY (`id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(那个字段);
ALTER TABLE `student` ADD CONSTRAINT `gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上的操作都是物理外键,数据库级别的外键,我们一般不使用(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 想用多张表的数据,或想使用外键,使用程序去实现
DML
DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字:insert, delete, update 等
插入
-- insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
-- 列名和值要一一对应。
INSERT INTO `grade`(`gradename`) VALUES ('大四');
更新
-- update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
-- 如果不加where条件,则会将表中所有记录全部修改。
UPDATE grade SET `gradename`= '大一' WHERE gradeid =1;
删除
delete
-- 删除所有数据
DELETE FROM `student`;
-- 删除表中某一行
DELETE FROM `student` WHERE id = 1;
truncate
-- 清空student表
-- TRUNCATE完全清空一个数据库表,表的结构和索引约束不会变
TRUNCATE `student`;
delect 和 truncate的区别
- 相同点:都能删除数据,都不会删除表结构
- 不同点:
- truncate重新设置自增列 计数器会归零
- truncate 不会影响事务
注意:delect删除问题,重启数据库后出现现象
INNODB 自增列会从1开始(原因:数据存在内存中,断电即失)
MYISAM 继续从上一个自增量开始(原因:数据存储在文件中,不会丢失)
DQL
DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字:select, where 等
基本查询语法
-- 查询所有学生
SELECT * FROM student;
-- 查询指定字段
select `name`,`pwd` from student;
别名
-- 给结果起一个名字
-- 可以给字段起别名,也可以给表起别名
select `name` as '名字',`pwd` as '密码' from student as s;
去重
-- DISTINCT去除SELECT查询出来的结果中重复的数据,重复数据只显示一条
SELECT DISTINCT gradeid FROM student;
where条件查询
关键字:like,between,in,or,is null,is not null
-- 查询姓名中包含峰的人
SELECT * FROM student WHERE `name` LIKE '%峰%';
-- 查询姓名是3个字的人
SELECT * FROM student WHERE `name` LIKE '___';
-- 查询姓名第二个字是海的人
SELECT * FROM student WHERE NAME LIKE "_海%";
-- 查询姓李的人
SELECT * FROM student WHERE NAME LIKE '李%';
练习:
-- 查询年龄大于20岁
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;
-- 查询年龄等于20岁
SELECT * FROM student WHERE age = 20;
-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
-- 查询年龄大于等于20 小于等于30
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
-- 查询年龄22岁,18岁,25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);
-- 查询英语成绩为null
SELECT * FROM student WHERE english IS NULL;
-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
聚合函数
AVG():计算平均值
COUNT():计算个数
MAX():计算最大值
MIN():计算最小值
SUM():计算和
字符串函数
CONCAT()
-- 拼接字符串
SELECT CONCAT('姓名:',`name`)AS 拼接名字 FROM student;
INSERT()
-- 字符串替换
-- 格式SELECT INSERT(原字符串,开始位置,替换长度,需替换的字符串);
SELECT INSERT('这是Oracle数据库',3,6,'MYSQL');
LOWER()
-- 字符串转小写
SELECT LOWER('MYSQL');
UPPER()
-- 字符串转大写
SELECT UPPER('mysql');
SUBSTRING()
-- 字符串截取
-- 格式SELECT SUBSTRING(字符串,开始位置,截取长度);
SELECT SUBSTRING('javamysqloracle',5,5);
时间日期函数
CURDATE()
-- 获取当前日期
SELECT CURDATE();
CURTIME()
-- 获取当前时间
SELECT CURTIME();
NOW()
-- 获取当前日期和时间
SELECT NOW();
WEEK(date)
-- 返回日期date为一年中的第几周
SELECT WEEK(NOW());
YEAR(date)
-- 返回日期date的年份
SELECT YEAR(NOW());
HOUR(time)
-- 返回时间time的小时值
SELECT HOUR(NOW());
MINUTE(time)
-- 返回时间time的分钟值
SELECT MINUTE(NOW());
DATEDIFF(date1,date2)
-- 返回日期参数date1和date2之间相隔的天数
SELECT DATEDIFF(NOW(),'2020-01-01');
ADDDATE(date,n)
-- 计算日期参数date加上n天后的日期
SELECT ADDDATE(NOW(),5);
数学函数
CEIL(x)
-- 返回大于或等于数值的最小整数
SELECT CEIL(2.3);
FLOOR(x)
-- 返回小于或等于数值的最大整数
SELECT FLOOR(2.3);
RAND()
-- 返回0到1之间的随机数
SELECT RAND();
排序
降序:desc
升序:asc
分页LIMIT
子查询
分组查询
GROUD BY
HAVING
where 和 having 的区别:
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
多表连接查询
笛卡尔积:
- 有两个集合A,B .取这两个集合的所有组成情况。
- 要完成多表查询,需要消除无用的数据
准备
-- 创名为公司的数据库
CREATE DATABASE company;
-- 使用数据库
USE company;
-- 创建部门表
CREATE TABLE IF NOT EXISTS `dept`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` VARCHAR(20) NOT NULL COMMENT '名字',
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET =utf8;
-- 添加数据
INSERT INTO dept (`name`) VALUES ('开发部'),('市场部'),('财务部');
-- 查询数据
SELECT * FROM dept;
-- 创建员工表
CREATE TABLE IF NOT EXISTS `emp` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` VARCHAR(10) NOT NULL COMMENT '名字',
`sex` CHAR(1) COMMENT '性别',
`salary` DOUBLE COMMENT '工资',
`join_date` DATE COMMENT '入职日期',
`dept_id` INT(4),
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET =utf8;
-- 添加外键
ALTER TABLE `emp` ADD CONSTRAINT `dept_id` FOREIGN KEY(`dept_id`) REFERENCES `dept`(`id`);
-- 添加数据
INSERT INTO emp(`name`,`sex`,`salary`,`join_date`,`dept_id`) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(`name`,`sex`,`salary`,`join_date`,`dept_id`) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(`name`,`sex`,`salary`,`join_date`,`dept_id`) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(`name`,`sex`,`salary`,`join_date`,`dept_id`) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(`name`,`sex`,`salary`,`join_date`,`dept_id`) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
-- 查询数据
SELECT * FROM emp;
-
内连接查询:
-
隐式内连接:使用where条件消除无用数据
-- 查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; -- 查询员工表的名称,性别。部门表的名称 SELECT emp.`name`,emp.`sex`,dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
-
显式内连接:select 字段列表 from 表名1 [inner] join 表名2 on 条件
-- 查询所有员工信息和部门信息 SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
-
查询要领:
- 从哪些表中查询数据
- 条件是什么
- 查询哪些字段
-
-
外链接查询:
-
左外连接:查询的是左表所有数据以及其交集部分。
-- 语法:select 字段列表 from 表1 left [outer] join 表2 on 条件; -- 查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称 SELECT emp.*, dept.`name` FROM emp LEFT JOIN dept ON emp.`dept_id` = dept.`id`;
-
右外连接:查询的是右表所有数据以及其交集部分。
-- 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件; SELECT * FROM dept RIGHT JOIN emp ON emp.`dept_id` = dept.`id`;
-
子查询:查询中嵌套查询,称嵌套查询为子查询
-- 查询工资最高的员工信息 SELECT * FROM emp WHERE `salary`=(SELECT MAX(`salary`) FROM emp);
子查询的结果是单行单列的:
-- 子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= = -- 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.`salary`<(SELECT AVG(`salary`) FROM emp);
子查询的结果是多行单列的:
-- 子查询可以作为条件,使用运算符in来判断 -- 查询'财务部'和'市场部'所有的员工信息 SELECT * FROM emp WHERE `dept_id` IN (SELECT id FROM dept WHERE `name`='财务部' OR `name`='市场部');
子查询的结果是多行多列的:
-- 子查询可以作为一张虚拟表参与查询 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 SELECT * FROM dept,(SELECT * FROM emp WHERE `join_date`> '2011-11-11') t1 WHERE dept.id = t1.dept_id; -- 普通内连接 SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11';
-
DCL
DCL(Data Control Language)数据控制语言
用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等
用户管理
使用mysql数据库
USE mysql;
查询登录ip,用户,密码
-- HOST: 允许用户登录的ip‘位置'%表示可以远程;
-- USER:当前数据库的用户名;
-- authentication_string: 用户密码;
SELECT HOST, USER, authentication_string FROM USER;
添加用户:
-- CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'fwh'@'localhost' IDENTIFIED BY 'fwh';
删除用户:
-- DROP USER '用户名'@'主机名';
DROP USER 'fwh'@'localhost';
修改用户密码:
踩坑点:在mysql 5.7.9以后废弃了password字段和password()函数
-- 清空密码 UPDATE USER SET authentication_string='' WHERE USER='用户名';
UPDATE USER SET authentication_string='' WHERE USER='fwh';
-- 添加密码 ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';
ALTER USER 'fwh'@'localhost' IDENTIFIED BY 'qwe';
-- 刷新权限
FLUSH PRIVILEGES;
通配符: % 表示可以在任意主机使用用户登录数据库
权限管理
查询权限
-- SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'fwh'@'localhost';
授予权限
-- grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
GRANT ALL ON *.* TO 'fwh'@'localhost';
撤销权限
-- revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE ALL PRIVILEGES ON *.* FROM 'fwh'@'localhost';
事务
概念
如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
mysql中只有使用了INNODB数据库引擎的数据库或表才支持事务
事务的四大特征(ACID)
原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
持久性:当事务提交或回滚后,数据库会持久化的保存数据。
隔离性:多个事务之间。相互独立。
一致性:事务操作前后,数据总量不变
事务控制语句
开启事务
START TRANSACTION;
事务回滚
ROLLBACK;
事务提交
COMMIT;
查看事务的默认提交方式
-- 1 代表自动提交 0 代表手动提交
SELECT @@autocommit;
修改默认提交方式
SET @@autocommit = 0;
事务的隔离级别
概念:
多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
存在问题:
脏读:一个事务,读取到另一个事务中没有提交的数据
不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
隔离级别:
read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
read committed:读已提交 (Oracle)
产生的问题:不可重复读、幻读
repeatable read:可重复读 (MySQL默认)
产生的问题:幻读
serializable:串行化
可以解决所有的问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
数据库查询隔离级别:
-- mysql8.0以上
SELECT @@transaction_isolation;
-- mysql8.0以下
SELECT @@tx_isolation;
数据库设置隔离级别:
set global transaction isolation level 级别字符串;