MySQL数据库的基本使用

最新推荐文章于 2024-04-10 03:17:44 发布

一安逸一

最新推荐文章于 2024-04-10 03:17:44 发布

阅读量464

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_46112033/article/details/129503538

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL数据库的基本使用

开启mysql

mysql start;

关闭MySQL

mysql stop;

登陆mysql

语法:mysql -u 用户名 -p 密码(密码可以省略)

mysql -u root -p

一、数据库的创建、删除、修改、备份、查看

1.创建数据库

语法： CREATE DATABASE 数据库名称 CHARACTER SET 字符集 COLLATE 校验规则；

创建数据库和创建表时为了避免使用到关键字可以使用反引号 ` 规避

字符集和校验规则部分可以省略，使用默认字符集 "utf8",默认校验规则 "utf8_general_ci";

默认校验规则不区分大小写,使用 "utf8_bin" 区分大小写

CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin;

2.删除数据库

谨慎使用

语法: DROP DATABASE 数据库名称;

DROP DATABASE hsp_db02;

3.数据库的修改

4.数据库的备份

数据库备份需要在Dos下执行mysqldump指令(保存路径最好不要有中文)

语法: mysqldump -u 用户名-p -B 数据库名称(同时备份多个使用空格隔开) > 备份路径+文件名称.sql;

mysqldump -u root -p -B hsp_db01 hsp_db02 > d:\\2023\\DATABASE_LEARN\\hsp_mysql\\dump01.sql;

表的备份,(与数据库备份的区别:不带"-B")

语法: 语法: mysqldump -u 用户名-p 数据库名称表名(同时备份多个使用空格隔开) > 备份路径+文件名称.sql;

mysqldump -u root -p hsp_db01 t1 > D:\\2023\\DATABASE_LEARN\\hsp_mysql\\dump02.sql;

5.数据库的恢复

恢复数据库需要在mysql命令行执行命令

语法: source 备份路径+文件名称.sql;

source d:\\2023\\数据库学习\\hsp_mysql\\dump01.sql;

6.数据库的查看

#查看当前数据库服务中的的所有数据库
SHOW DATABASES; 
#查看创建数据库时的定义信息
SHOW CREATE DATABASE hsp_db01;
# 查看数据库中的所有表
SHOW TABLES;

二、表的创建、删除、备份、修改、查看、查询

1.表的创建

语法： CREATE TABLE 表名称（字段名数据类型（长度）,字段名数据类型（长度）,...）CHARACTER SET 字符集 COLLATE 校验规则 ENGINE 存储引擎类型；

创建数据库和创建表时为了避免使用到关键字可以使用反引号 ` 规避

没有指定长度,则为最大长度

字符集、校验规则和引擎部分可以省略，默认使用数据库的字符集和校验规则，默认引擎：INNODB

CREATE TABLE `user`(id INT,`name` VARCHAR(255),`psw` VARCHAR(32),`birthday` DATE)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

快速创建一个结构相同的表

语法: CREATE TABLE 要创建的表名 LIKE 作为模板的表;

CREATE TABLE hsp_db04 LIKE hsp_db03;

2.表的删除

语法: DROP TABLE 表名称;

 DROP TABLE hsp_db03;

3.表的备份

表的备份,(与数据库备份的区别:不带"-B")

语法: 语法: mysqldump -u 用户名-p 数据库名称表名(同时备份多个使用空格隔开) > 备份路径+文件名称.sql;

表的恢复方法和数据库一样

mysqldump -u root -p hsp_db01 t1 > D:\\2023\\DATABASE_LEARN\\hsp_mysql\\dump02.sql;

4.表的修改 (ALTER )

(1)添加字段 (ADD )

语法: ALTER TABLE 表名 ADD 字段名数据类型 AFTER 已经存在的字段名;

(2)修改字段 (MODIFY )

- 语法: ALTER TABLE 表名 MODIFY 字段名数据类型;

#### (3)删除字段 (DROP )

语法: ALTER TABLE 表名 DROP 字段名;

#### (4)修改表名 (RENAME )

语法: RENAME TABLE 要修改的表名 TO 修改后的表名;

(5)修改表的字符集 (CHARACTER SET)

语法: ALTER TABLE 表名 CHARACTER SET 字符集;

（6）修改字段名 (CHANGE )

语法：ALTER TABLE 表名 CHANGE 要修改的字段名修改后的字段名；

(7)修改存储引擎

语法:ALTER TABLE 表名 ENGINE = 存储引擎类型

ALTER TABLE t25 ENGINE = INNODB;

#在resume字段后天加imge字段
ALTER TABLE emp ADD imge VARCHAR(32) NOT NULL DEFAULT '' AFTER resume;
#修改job列
ALTER TABLE emp MODIFY job VARCHAR(69) NOT NULL DEFAULT '';
#删除sex列
ALTER TABLE emp DROP sex;
#修改表名
RENAME TABLE emp TO employee;
#修改表的字符集
ALTER TABLE employee CHARACTER SET utf8;
#修改字段名name -> user_name
ALTER TABLE employee CHANGE `name` `user_name` VARCHAR(60) NOT NULL DEFAULT '';

5.表的结构查看 (DESC )

语法: DESC 表名;

DESC hsp_db03;

6.表的查询 (SELECT )

字段使用 " * " 代表所有字段

语法: SELECT 字段名(*) FROM 表名(多个使用 " , " 隔开) WHERE 过滤条件;

去重查询使用, "DISTINCT" 关键字

#去重查询,一条数据完全相同时去重
SELECT DISTINCT english FROM student;

使用别名使用 "AS" 关键字

SELECT `name` AS '名字',(chinese + english + math +10) AS total_score FROM student;

分组查询使用 "GROUP BY" ,过滤通常使用 "HAVING" 关键字

#分组(查询)统计
-- 显示每个部门的平均工资和最高工资
SELECT AVG(sal),MAX(saL),deptno FROM emp GROUP BY deptno; 
-- 显示每个部门每个岗位的平均工资和最低工资
SELECT AVG(sal),MIN(saL),deptno,job FROM emp GROUP BY deptno,job;
-- 显示平均工资低于2000的部门和它的平均工资
SELECT AVG(sal) AS 'avg',deptno FROM emp GROUP BY deptno HAVING avg < 2000;-- 使用having过滤
#增强group by 的使用演示
-- 显示每种岗位的雇员人数和平均工资
SELECT COUNT(*),job,FORMAT(AVG(sal),2) FROM emp GROUP BY job;
-- 显示雇员总数,以及获得补助的员工人数
SELECT COUNT(*),COUNT(comm) FROM emp;-- COUNT(列),不会统计为null的行
-- 统计没有获得补助的人数
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL)) FROM emp;
-- 显示管理者的人数
SELECT COUNT(DISTINCT mgr) FROM emp;-- 使用distinct去重,DISTINCT
-- 显示工资最大和最小的差值
SELECT MAX(sal) - MIN(sal) FROM emp;

模糊查询使用 "LIKE" 关键字

"%" (百分号)表示0到多个字符, "_"(下划线)表示单个任意支付

# "%" (百分号)表示0到多个字符, "_"(下划线)表示单个任意字符
-- 显示首字母为S的员工的工资
SELECT ename,sal FROM emp WHERE ename LIKE 'S%';
-- 显示名字第三个字母为O的员工
SELECT ename,sal FROM emp where ename LIKE '__O%';

查询后排序使用 "ORDER BY"关键字,默认降序排列(DESC),后接 "ASC" 为升序排列

-- 按照工资升序排列
SELECT * FROM emp ORDER BY sal ASC;
-- 按照部门号升序,然后按照工资降序
SELECT * FROM emp ORDER BY deptno ASC,sal DESC;-- 先第一个条件,加逗号再写第二个条件

分页查询使用 "LIMIT" 关键字

#LIMIT start,rows,表示从start + 1(start从0开始计算),开始显示,显示rows行,为一页,写在查询语句的最后
-- 按 雇员的ID升序排列,每一页显示三行
SELECT * FROM emp ORDER BY empno LIMIT 0,3;-- 第一页
SELECT * FROM emp ORDER BY empno LIMIT 3,3;-- 第二页
SELECT * FROM emp ORDER BY empno LIMIT 6,3;-- 第三页

过滤条件:逻辑与: "AND",逻辑或: "OR", 逻辑否: "NOT"

判断是否为空使用 "IS" 关键字,不能使用等号 "=" 或双等号 "=="

#select的使用
SELECT * FROM student;-- 查询表中的所有数据
#查询姓名的对应的英语成绩,限定查询
SELECT `name`,english FROM student;
#去重查询,一条数据完全相同时去重
SELECT DISTINCT english FROM student;
#在查询过程可以进行运算
-- 统计学生的总分
SELECT `name`,(chinese + english + math) FROM student;
#在总分的基础上加10分
SELECT `name`,(chinese + english + math +10) FROM student;
#使用别名显示统计的数据
SELECT `name` AS '名字',(chinese + english + math +10) AS total_score FROM student;

#使用where过滤的查询
-- 查询名字为赵云的数据
SELECT * FROM student WHERE `name`  = '赵云';
-- 查询英语成绩大于90的数据
SELECT * FROM student WHERE english > 90;-- 使用条件表达式
#查询总分大于200的数据
SELECT * FROM student WHERE (chinese + english + math) > 200;
ALTER TABLE student CHANGE `NAME` `name` VARCHAR(4);-- 修改字段名
#查询math大于60 并且（and）id大于4的数据
SELECT * FROM student WHERE math > 60 and id > 4;
#查询英语成绩大于语文成绩的数据
SELECT * FROM student WHERE english > chinese;
#查询总分大于200分,并且数学成绩下雨语文成绩并且姓'韩'的学生
SELECT * FROM student WHERE (chinese + english + math) > 200 and math > chinese and `name` LIKE '韩%';-- 模糊查询
#查询英语成绩在80 ~ 90之间的数据
SELECT * FROM student WHERE english >= 80 and english <= 90;
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
#查询数学成绩为89,90,91的数据
SELECT * FROM student WHERE math = 89 or math = 90 or math = 91;
SELECT * FROM student WHERE math IN (89,90,91);
#查询姓李的的数据
SELECT * FROM student WHERE `name` LIKE '李%';
#查询数学成绩大于80,语文成绩大于80的数据
SELECT * FROM student WHERE math > 80 and chinese > 80;
#演示order by的使用，默认是升序排列
#对数学成绩升序排列
SELECT * FROM student ORDER BY math;
#按总分降序排列
SELECT `name`,(chinese + english + math) AS total_socre FROM student ORDER BY total_socre DESC;
#对姓韩的成绩进行升序排列
SELECT `name`,(chinese + english + math) AS total_socre FROM student WHERE `name` LIKE '韩%' ORDER BY total_socre ASC;

三、流程控制函数 (IF,IFNULL)

#流程控制函数
#IF(expr1,expr2,expr3),如果expr1为true,就返回expr2,否则返回expr3,类似Java的三元运算符
SELECT IF(TRUE,'杨逸','hsp') FROM DUAL;
#IFNULL(expr1,expr2),如果expr1为null,就返回expr2,否则返回expr1
SELECT IFNULL('hsp','杨逸') FROM DUAL;
-- 查询emp表,如果comm是null,则显示0.0
SELECT IFNULL(comm,0.0) FROM emp;
SELECT IF(comm IS	NULL,0.0,comm) FROM emp;-- 判断是否为空使用 "IS" 
#多分支流程
/*CASE case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;
case
*/
# 多分支使用 "WHEN" 和 "THEN" 关键字
SELECT ename,(SELECT CASE job
	WHEN 'CLERK' THEN '职员' 
		WHEN 'MANAGER'THEN '经理' 
		WHEN 'SALESMAN' THEN '销售人员'
	ELSE job
END) AS job FROM emp;

四、日期时间相关的函数

#日期相关的函数
-- 返回当前的日期,CURRENT_DATE
SELECT CURRENT_DATE() FROM DUAL;
-- 返回当前的时间,CURRENT_TIME
SELECT CURRENT_TIME() FROM DUAL;
-- 返回当前的时间戳,CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- 返回当前的时间, NOW()
SELECT NOW() FROM DUAL;
# 创建测试表
CREATE TABLE mes(id INT,content CHAR(16),date TIMESTAMP) CHARACTER SET utf8;
#添加测试数据
INSERT INTO mes(id,content,date) VALUE(1,'八甲新闻',CURRENT_TIMESTAMP),(2,'北京新闻',CURRENT_TIMESTAMP),(3,'阳春新闻',CURRENT_TIMESTAMP); 
SELECT * FROM mes;

#查询数据,只显示日期不显示时间,DATE(expr)
SELECT id,content,DATE(date) FROM mes;
#查询在十分钟内发布的新闻
#DATE_ADD(date,INTERVAL expr unit),返回date时间加上某一时间
#DATE_SUB(date,INTERVAL expr unit),返回date时间减去某一时间
SELECT * FROM mes WHERE DATE_ADD(date,INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes WHERE DATE_ADD(NOW(),INTERVAL 10 MINUTE) <= date;
#求日期差,DATEDIFF(expr1,expr2),返回的是天数
#求时间差,TIMEDIFF(expr1,expr2),返回的是时分秒
SELECT TIMEDIFF(NOW(),date) AS '时间差' FROM mes;
#求2011-11-11和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1') AS '日期差' FROM DUAL;
#求你自己活了多少天
SELECT DATEDIFF(NOW(),'2004-5-15') FROM DUAL;
#假设你能活到80岁,求你现在还能活多少天
SELECT DATEDIFF(DATE_ADD('2004-5-15',INTERVAL 80 YEAR),NOW()) AS '剩余天数' FROM DUAL;
#显示日期和时间的指定部分
SELECT TIMESTAMP(NOW()) FROM DUAL;-- 显示完整日期和时间
SELECT YEAR(NOW()) FROM DUAL;-- 只显示年
SELECT MONTH(NOW()) FROM DUAL;-- 只显示月
SELECT DAY(NOW()) FROM DUAL;-- 只显示日
SELECT HOUR(NOW()) FROM DUAL;-- 只显示小时
SELECT MINUTE(NOW()) FROM DUAL;-- 只显示分钟
SELECT SECOND(NOW()) FROM DUAL;-- 只显示秒
#返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
#将一个 unix_timestamp 秒数[时间戳],转换成指定的时间格式,FROM_UNIXTIME(unix_timestamp)
# '%Y-%m-%d %h:%i:%s' 是固定格式
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %h:%i:%s') FROM DUAL;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%h:%i:%s') FROM DUAL;
#日期的比较
-- 查询在1992-1-1后入职的员工
SELECT * FROM emp WHERE hiredate > '1992-1-1';
# 返回日期所在月的最后一天,last_day(日期)
SELECT LAST_DAY(NOW()) FROM DUAL;

五、统计函数和数学相关的函数

#统计查询
# 统计所有:count(*),结果为null会统计进去
# 统计部分count(字段...),结果为null的不统计
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student WHERE math > 90;
-- 统计总分大于250的人数
SELECT COUNT(*) FROM student WHERE (chinese + math + english) > 250;
DESC student;
INSERT INTO student (id,`name`) VALUES(7,'jack'),(8,NULL),(9,NULL);
SELECT * FROM student;
-- 统计所有字段
SELECT COUNT(*) FROM student;
-- 统计部分字段
SELECT COUNT(`name`) FROM student;-- 为null的字段没有被统计

#合计(求和)函数, SUN()
-- 统计一个班的数学总成绩
SELECT SUM(math) FROM student;
-- 统计各科的总成绩
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
-- 统计一个所有成绩的总和
SELECT SUM(chinese + math + english) FROM student;
-- 统计语文的平均分
SELECT SUM(chinese)/COUNT(*) FROM student;

#求平均的函数,AVG()
-- 求数学平均分
SELECT FORMAT(AVG(math),2) FROM student;
-- 求班级平均分
SELECT FORMAT(AVG(chinese + math + english),2) FROM student;

#求最大值,MAX()
#求最小值,MIN()
-- 求班级最高分和最低分
SELECT MAX(chinese + math + english) AS'MAX',MIN(chinese + math + english) AS 'MIN'FROM student;
-- 求数学最高分和最低分
SELECT MAX(math) AS'MAX',MIN(math) AS 'MIN'FROM student;
#演示数学相关的函数
-- 绝对值,ABS(X)
SELECT ABS(-10) FROM DUAL;
-- 十进制转二进制,BIN(N)
SELECT BIN(8) FROM DUAL;
-- 向上取整, CEILING(X)
-- 向下取整, FLOOR(X)
SELECT CEILING(9.99) FROM DUAL;
SELECT FLOOR(9.99) FROM DUAL;
-- 进制转换, CONV(N,from_base,to_base),将from_base的N,转换成to_base进制
SELECT CONV(8,10,2) FROM DUAL;
-- 控制保留小数的位数,FORMAT(X,D),保留D位小数,四色五入保留
SELECT FORMAT(12.3456,2) FROM DUAL;
-- 转成16进制,HEX(N_or_S)
SELECT HEX(16) FROM DUAL;
-- 求最小值,LEAST(value1,value2,...)
SELECT LEAST(2,2,3,1) AS 'MIN' FROM DUAL;
-- 求余,MOD(N,M),N模M
SELECT MOD(10,3) FROM DUAL;
-- 返回一个随机数,范围在[0,1],RAND()
SELECT RAND() FROM DUAL;
SELECT RAND(1) FROM DUAL; -- 如果加入参数,则返回一个固定的随机数

六、系统函数和加密函数

#演示加密函数和系统函数
#查询用户,USER()
#查询登陆到MySQL的用户的IP地址
SELECT USER() FROM DUAL; -- 用户名@IP地址
#查询当前使用的数据库的名称,DATABASE()
SELECT DATABASE() FROM DUAL;
# MD5(str),加密一个字符串得到一个32位的字符串
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;

#PASSWORD(str),加密函数,数据库用户的密码就是使用PASSWOED(str)进行加密
SELECT LENGTH(PASSWORD('h12sp')) FROM DUAL;-- 加密后的长度是41

七、字符串常用函数

#字符串常用函数的演示
-- CHARSET(str),返回字符串的字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT(str1,str2,...),拼接字符串
SELECT CONCAT(ename,' 的工作是 ',job) FROM emp;
-- INSTR(str,substr), 返回substr在str中出现的位置,没有就返回0,MySQL字符串的开始位置为1
SELECT INSTR('杨逸好帅','好') FROM DUAL;-- DUAL 是亚元表,系统自带的表,用于可以测试
-- UCASE(str),将字符串转换成大写
-- LCASE(str),将字符串转换成小写
SELECT UCASE(ename) FROM emp;
SELECT LCASE(ename) FROM emp; 
-- LEFT(str,len),从str的左边起取len个字符
-- RIGHT(str,len),从str的右边取len个字符
SELECT LEFT(ename,2) FROM emp;
SELECT RIGHT(ename,2) FROM emp;
-- LENGTH(str),返回字符串的长度(单位:字节),按字节的方式
SELECT LENGTH(ename) FROM emp;
SELECT LENGTH('   hsp  ') FROM emp;-- 空格占一个字节
-- 字符串的替换,REPLACE(str,from_str,to_str),将字符串str中的from_str替换成to_str
SELECT REPLACE(job,'MANAGER','经理') FROM emp;
-- 字符串比较大小,STRCMP(expr1,expr2),逐字比较
-- 返回0则相等,-1则前者小于后者,1则前者大于后者
SELECT STRCMP('zsp','yangyi') FROM emp;
-- 截取字符串, SUBSTRING(str,pos,len),从pos开始截取,截取len个支符
SELECT SUBSTR(ename,1,1) FROM emp;-- MySQL的字符串从1开始
-- 去除左右两端的空格, TRIM(str)
-- 去除左边的空格,LTRIM(str)
-- 去除右边的空格,RTRIM(str)
SELECT TRIM('  杨逸是软件工程师    ') FROM emp;
SELECT LTRIM('  杨逸是软件工程师    ') FROM emp;
SELECT RTRIM('  杨逸是软件工程师    ') FROM emp;

-- 作业:以首字母小的形式显示emp表的姓名
#	1.截取第一个字符和剩余的字符
# 2.将第一个字符转成小写,其他的字符不变
# 3.将字符进行拼接
SELECT CONCAT(LCASE(SUBSTR(ename,1,1)),SUBSTR(ename,2)) AS 'name' FROM emp;

八、数据库的增删改查（CRUD）

1.添加数据（INSERT）

语法: INSERT INTO 表名(字段名1，字段名2，... ) VALUES (数据1,数据2, ... );

2.更新数据 (UPDATA)

语法: UPDATE 表名 SET 要修改的字段 = 值 WHERE 过滤条件;

不带过滤条件会将整个字段都更新

3.删除数据 (DELETE)

语法: DELETE FROM 表名WHERE 过滤条件;

不带过滤条件会将整个字段都删除

#insert的使用,添加数据
CREATE TABLE goods (id INT,`name` VARCHAR(10),price DOUBLE);
-- 添加数据
INSERT INTO goods(id,`name`,price)VALUES(1,'手机',3999);
INSERT INTO goods(id,`name`,price)VALUES(2,'电脑',9999);

#update的使用,更新数据,可以同时更新多个字段,用逗号隔开
#where是条件筛选,可以带多个字段条件
-- 不带where慎用
UPDATE goods SET price = 6666,`id` = 00;-- 不带where，将所有的price都改成6666
UPDATE goods SET price = 66686 WHERE `name` = '电脑';-- 将指定的列的price修改
#在原有价格的基础上添加1000
UPDATE goods SET price = price + 1000 WHERE `name`  = '手机';

#delete删除数据
-- 不带where会删除表中的所有数据
DELETE FROM goods;
DELETE FROM goods WHERE `name` = '手机';-- 删除name为"手机的整条数据"

九、多表查询

1.多表查询

多表查询的过滤条件不能少于，表的个数 - 1，否则会出现"笛卡尔集"的现象

-- 默认情况直接查询两张表,结果是:从第一张表取出一行和第二张表的每一行进行拼接
SELECT * FROM emp,dept;
-- 多表查询,需要正确处理过滤条件
SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 显示特定的字段
SELECT ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno = dept.deptno;-- 当两张表存在相同的字段是,要指定查询的是哪一张表的
-- 显示部门号为10的部门名和员工名和工资
SELECT ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno = dept.deptno AND emp.deptno = 10;

### 2.自连接 --同时查询同一张表

#两个字段都在同一个表,但没有直接指向, #员工的上级通过一个员工编号进行指向,一个编号就代表一个员工 #查询同一张表需要指定表的别名,指定表的别名不需要使用"AS", -- 显示员工和他的上级 SELECT worker.ename AS '员工',boss.ename AS '上级' FROM emp worker,emp boss WHERE worker.mgr = boss.empno;

十、子查询

查询中包含查询的查询语句

返回的数据可以当表使用,也到字段使用

当表使用时,放在表的位置,"FROM"后面

当字段使用时,放在过滤条件的位置,"WHERE"后面

1.单行子查询

子查询只返回一行的查询语句

-- 显示与SMITH同一个部门的员工
# 先查询到SMITH的部门号,再使用部门号作为条件进行查询
# 子语句需要括号
# 单行子查询,子语句只返回一条数据的查询
SELECT deptno FROM emp WHERE ename = 'SMITH';
SELECT ename FROM emp WHERE (SELECT deptno FROM emp WHERE ename = 'SMITH');

2.多行子查询

子查询只多行的查询语句

过滤条件配合 "IN" 关键字使用

-- 查询和部门10的工作相同的员工的名字、岗位、工资、部门号，但不包含部门10
-- 先查询到部门10有那些岗位,注意去重
# MySQL中不等于可以使用 "!="或者 "<>"
SELECT DISTINCT job FROM emp WHERE deptno = 10;
SELECT ename,job,sal,deptno FROM emp WHERE job IN(SELECT DISTINCT job FROM emp WHERE deptno = 10) AND deptno <> 10;

3. 多列子查询

返回多列数据

-- 查询与SMITH的部门和岗位完全相同的所有员工,不包含SMITH本人
-- 先得到SIMTH的部门和岗位,再查询表进行比较
SELECT deptno,job FROM emp WHERE ename = 'SMITH';
-- 方式一
SELECT * FROM emp WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename = 'SMITH') AND ename != 'SMITH';
-- 方式二
SELECT ename,emp.deptno,emp.job FROM emp,(SELECT deptno,job FROM emp WHERE ename = 'SMITH') temp WHERE ename != 'SMITH' AND emp.job = temp.job AND emp.deptno = temp.deptno;

4.子查询当作临时表使用

-- 查询ecshop中各个类别中价格最高的商品
-- 先得到,各个类别中价格最高的商品
SELECT cat_id,MAX(shop_price) FROM ecs_goods GROUP BY cat_id;
-- 再将得到的结果当作为临时表使用,在真正的表中对比得到结果
-- 为了发表处理,给临时表取个别名
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price FROM (SELECT cat_id,MAX(shop_price) AS max_price FROM ecs_goods GROUP BY cat_id) temp,ecs_goods WHERE temp.cat_id = ecs_goods.cat_id AND temp.max_price = ecs_goods.shop_price;

十一、 ALL 、 ANY、IN 、ENUM（枚举）关键字的使用

"ALL" 和 "ANY" 一般用在比较大小的语句中

"IN" 一般用在比较相等的语句中,表示存在

1.ALL

-- 查询比部门30所有员工工资高的员工的姓名、工资、部门
# ALL 代表所有的
SELECT ename,sal,deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);
SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

2.ANY

-- 查询比部门30其中一个员工工资高的员工的姓名、工资、部门
# ANY 代表其中一个
SELECT ename,sal,deptno FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);
SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MIN(sal)FROM emp WHERE deptno = 30);

3.IN

-- 查询与部门30其中一个员工工资相同的员工的姓名、工资、部门
# IN 代表在其中,一般用在相等对比语句中
SELECT ename,sal,deptno FROM emp WHERE sal = IN(SELECT sal FROM emp WHERE deptno = 30);

4.ENUM(枚举)

CREATE TABLE hsp_db (id INT,`name` VARCHAR,sex ENUM('男','女'));

十二、表的自我复制和去重

1.表的自我复制(蠕虫复制)

查询一个表,将查询到的数据,插入到本表

INSERT INTO my_tab01 (id,`name`,job,denptno,sal) (SELECT id,`name`,job,denptno,sal FROM my_tab01);

一般用创建海量数据表,

2.表的去重

思路:

先创建一个结构相同的临时表 my_tab

把要去重的表 my_tab02 通过distinct 关键字处理后,插入到临时的表 my_tab

清除 my_tab02 的记录

将临时表 my_tab 插入到 my_tab02

最后删除临时表 my_tab

# 先创建一个结构相同的临时表 my_tab
CREATE TABLE my_tab LIKE my_tab02;
# 把要去重的表 my_tab02 通过distinct 关键字处理后,插入到临时的表 my_tab
INSERT INTO my_tab (id,`name`,job,denptno,sal) (SELECT DISTINCT id,`name`,job,denptno,sal FROM my_tab02);
# 清除 my_tab02 的记录
DELETE FROM my_tab02;
# 将 临时表 my_tab 插入到 my_tab02
INSERT INTO my_tab02 (id,`name`,job,denptno,sal) (SELECT id,`name`,job,denptno,sal FROM my_tab);
# 最后删除 临时表 my_tab
DROP TABLE my_tab;

十三、合并查询

1. 不去重合并 (UNION ALL)

# "UNION ALL" 合并所有行,不会去掉重复行
SELECT ename,job,sal FROM emp WHERE sal > 2500 UNION ALL SELECT ename,job,sal FROM emp WHERE job = 'MANAGER';

2.去重合并 (UNION)

# "UNION" 合并会去掉重复行
SELECT ename,job,sal FROM emp WHERE sal > 2500 UNION SELECT ename,job,sal FROM emp WHERE job = 'MANAGER';

十四、外连接

多数情况使用的是前面学内连接,少数情况属于外连接

1.左外连接

左外连接,左表所有行都显示,右表没有匹配的显示为null

语法: SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 判断条件

# 左外连接,左表所有行都显示,右表没有匹配的显示为null
# 语法:SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 判断条件
-- 显示id,姓名,成绩
SELECT `name`,stu.id,sorce FROM stu LEFT JOIN grder ON stu.id = grder.id;

2.右连接

右外连接,右表所有行都显示,左表没有匹配的显示为null

语法: SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 判断条件

# 右外连接,右表所有行都显示,左表没有匹配的显示为null
# 语法:SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 判断条件
-- 显示id,姓名,成绩
SELECT `name`,stu.id,sorce FROM stu RIGHT JOIN grder ON stu.id = grder.id;

十五、MySQL数据约束

五种约束:not null,unique,primary key,foreign key,check

1.主键(primary key)

语法: 字段名字段类型 primary key,也可以在表定义最后使用: primary key (字段1,字段2,...)

使用主键约束的字段不能重复,也不能为null

一张表最多只能有一个主键,但可以是复合主键(比如: id + name)

CREATE TABLE t17 (id INT PRIMARY KEY,`name` VARCHAR(32),email VARCHAR(32));-- id 使用主键约束
CREATE TABLE t18 (id INT,`name` VARCHAR(32),email VARCHAR(32),PRIMARY KEY (id,`name`));-- id + name 使用复合主键约束

2.非空约束(not null)

# 非空约束,not null,被约束的字段不能为null
CREATE TABLE t19 (id INT NOT NULL DEFAULT '');

3.唯一约束(unique)

使用后不能出现重复,如果没有指定非空,则可以出现多个null

一张表可以有多个unique

CREATE TABLE t20 (id INT UNIQUE NOT NULL);

4.外键约束(foreign key)

语法: foreign key (从表字段名) references 主表名(主键字段名或者 unique字段名)

要带括号,不然会报错

用于定义主表的从表之间的关系,外键约束要定义在从表上,

主表必须具有主键约束或unique约束,外键数据在主表上存在或者为null

形成外键关系的主表的数据不能随意删除,需要从表没有字段指向时才能删除

主表的类型要求是innodb,这样的表才支持外键

一张表可以存在多个外键

CREATE TABLE class (id INT PRIMARY KEY,`name` VARCHAR(32),address VARCHAR(32));-- 主表
CREATE TABLE stu (id INT,`name` VARCHAR(32),class_id INT,FOREIGN KEY (class_id) REFERENCES class(id));-- 从表

5.条件约束 (check)

语法: CHECK (约束条件);

加在需要约束的字段后

mysql5.7目前还不支持check,只做语法的校验,不会真的生效

oracle,sql server,这两个数据库是会生效的

CREATE TABLE t23(id INT,`name` VARCHAR(32),sex VARCHAR(6) CHECK (sex IN('man','woman')),sal INT CHECK (sal > 1000 AND sal < 3000) );-- 性别限制为男女,工资限制为1000~3000

十六、自增长(auto_increment)

语法: 字段名整型 auto_increment;

一般自增长配合主键使用,也可以配合unique使用

当添加数据时,自增长的字段为null或者没有写时,启动自增长

默认从1开始增长

当指定数字时,以指定的数字为准,下个以最大的数字开始增长

修改自增长开始值为100,alter table t23 auto_increment = 100

修改自增长开始值语法: alter table 表名 auto_increment = 开始值

# 自增长
# 语法: 字段名 整型 auto_increment;
# 一般自增长配合主键使用,也可以配合unique使用
# 当添加数据时,自增长的字段为null或者没有写时,启动自增长
# 默认从1开始增长
# 当指定数字时,以指定的数字为准,下个以最大的数字开始增长
# 修改自增长开始值为100,alter	table t23 auto_increment = 100
# 修改自增长开始值语法: alter	table 表名 auto_increment = 开始值
CREATE TABLE t23(id INT PRIMARY KEY AUTO_INCREMENT,
								email VARCHAR(32),
								`name` VARCHAR(32));-- 创建id自增长的表
# 添加数据的方式
INSERT INTO t23 (id,`name`,email) VALUES(NULL,'jack','jack.com');-- 自增长的字段填null
INSERT INTO t23 (`name`,email) VALUES('jack','jack.com');-- 省略自增长的字段
INSERT INTO t23 (id,`name`,email) VALUES(5,'jack','jack.com');-- 指定数字

十七、MySQL索引

索引不提高占用内存,但提高占用磁盘空间

删除索引语法: drop 索引名称 on 表名

1.索引类型

主键索引(primary key),主键同时也是索引

唯一索引(unique),

普通索引(index)

全文索引(fulltext)[适用于MyISAM数据引擎]

3.创建索引

创建索引语法一: create 索引类型创建索引的名称 on 表名 (要创建索引的字段名)

创建索引语法二: alter table 表名 add 索引类型创建索引的名称 (要创建索引的字段名)

# 创建索引语法一: create 索引类型 创建索引的名称 on 表名 (要创建索引的字段名)
# 创建索引语法二: alter table 表名 add 索引类型 创建索引的名称 (要创建索引的字段名)
CREATE TABLE t25(id INT,`name` VARCHAR(32));
# 创建索引
CREATE UNIQUE INDEX id_index ON t25 (id);-- 添加唯一索引
CREATE INDEX id_index ON t25 (id);-- 添加普通索引
ALTER TABLE t25 ADD INDEX id_index (id); -- 添加普通索引

4.删除索引

# 删除索引
# 语法: drop 索引名称 on 表名
DROP id_index ON t25;
-- 删除主键索引
ALTER TABLE t25 DROP PRIMARY KEY;

5.修改索引

修改思路:先删除,再创建一个新的索引

6.查看索引的四种方式

#查看索引的四种方式
SHOW INDEX FROM t25;
SHOW INDEXES FROM t25;
SHOW KEYS FROM t25;
DESC t25;

7.创建索引的一般规则

频繁查询的字段应该创建索引

唯一性太差的字段不适合创建索引(例如性别)

更新频繁的字段不适合创建索引

不会作为查询条件的字段不应该创建索引

十八、MySQL的事务机制

MySQL的事务机制需要innodb类型的存储引擎,其他的存储引擎不支持事务机制

事务用于保证数据的一致性,由一组dml语句组成,该组语句要么全部成功,要么全部失败

dml语句就是,insert、updata、delete操作

当执行事务操作时，mysql会在表上加锁

1.事务的基本操作

开始事务语法： start transaction，set auto_commit=off

-- 开始事务
START TRANSACTION;
SET AUTOCOMMIT=off;

保存点语法： savepoint 保存的点名

-- ,添加数据,并设置保存点a
INSERT INTO t27(id,`name`) VALUES(100,'jk');
SAVEPOINT a;
-- 设置保存点b
INSERT INTO t27 VALUES(300,'kl');
SAVEPOINT b;

会退事务语法：rollback to 要回退到的点名；rollback(不带"to"表示回退全部事务,回到事务开始的状态)

-- 回退到b点
ROLLBACK b;
-- 回退到a点,可以直接回退到a点,但就不能再回退到b点了
ROLLBACK a;
-- 回退到事务开始状态
ROLLBACK;

提交事务

-- 提交事务
COMMIT;
#不开启事务,dml语句默认自动提交,就不能回退

2.事务隔离级别

当多个连接使用事务操作数据库时,数据库系统负责隔离操作,保证数据的正确性

脏读:当一个事务读取到另一个事务未提交的修改时,就说明产生了脏读

不可重复读:同一个查询在同一事务多次进行,由于其他提交事务做的修改或删除,每次返回不同的结果集,此时就发生了不可重复读

幻读:同一个查询在同一事务多次进行,由于其他提交事务做的插入,每次返回不同的结果集,此时就发生了幻读

1.四种隔离级别

读未提交(Read uncommitted)

读已提交(Read committed)

可重复读(Repeatable read)

可串型化(Serializable)

# 查看当前MySQL的隔离级别
SELECT @@tx_isolation;
# 查看系统的隔离级别
SELECT @@global.tx_isolation;
# 设置隔离级别
# 语法:SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别的类型;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 设置系统隔离级别
# 语法:SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别的类型;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

还可以在配置文件设置系统默认隔离级别

3.事务的特性

原子性:事务是一个不可分割的工作单位,事务中的操作要么全部发生,要么全都不发生

一致性:事务必须使数据库从一个一致性转换的另一个一致性

隔离型:多个用户并发访问数据库时,数据库为每一个用户开启事务,不能被其他用户的操作数据干扰,多个并发事务之间要相互隔离

持久性:事务一旦提交,对数据库的影响是永久性的

十九、MySQL的表类型和存在引擎(Storage Engines)

MySQL的表类型由存储引擎决定，MySQL常用三种：INNODB、MYISAM、Memory

MySQL支持六种类型:CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、INNODB

事务安全型:INNODB

非事务安全型：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM

#查看所有的存储引擎
SHOW ENGINES;
# INNODB 存储引擎
-- 1.支持事务2.支持外键3.支持行级锁
# MyISAM 存储引擎
-- 1.添加数独快2.不支持事务和外键3.支持表级锁
# MEMORY 存储引擎
-- 重启MySQL时数据会丢失
-- 1.数据存储在内存中2.执行速度快(没有IO读写)3.默认支持哈希索引

# 修改存储引擎
# 语法:ALTER TABLE 表名 ENGINE = 存储引擎类型;
ALTER TABLE t25 ENGINE = INNODB;

二十、MySQL视图

通过视图查询数据可以隐藏隐私数据,只允许查询指定的字段

视图是根据基表(可以有多个基表)创建的

视图是虚拟的表,只是一个结构,没有数据,只映射基表的数据

通过视图可以修改基表的数据

基表的改变也会影响到视图的数据

# 创建视图
# 语法: CREATE VIEW 视图名称 AS SELECT语句(相当于子查询)
CREATE VIEW my_view AS SELECT id,`name` FROM emp;
# 修改视图
# 语法: ALTER VIEW 视图的名称 AS SELECT语句(相当于子查询);
ALTER VIEW my_view AS SELECT `name`,sal FROM emp;
# 显示创建视图的信息
# 语法:SHOW CREATE VIEW 视图名称;
SHOW CREATE VIEW my_view;
# 删除视图
# 语法:DROP VIEW 视图名称;
DROP VIEW my_view;
# 查看视图结构
# 语法:DESC 视图名称
DESC my_view;

二十一、MySQL用户管理

host:允许登陆的"位置",localhost表示只允许本地登陆,也可以指定IP地址和IP网段

user:用户名

autherntication_string:密码,通过PASSWORD加密后的密码

查看系统的所有用户

SELECT * FROM mysql.user;

1.创建用户

语法:CREATE USER '用户名'@'可以登陆的地址' IDENTIFIED BY '设置的密码';

创建用户时,没有指定host,则为"%","%" 表示该用户在所有IP都可以登陆,表示创建了一个'xxx'@'%'用户

也可以指定IP段,'xxx'@'192.168.1.%',表示可以在"192.168.1.*"网段登陆

CREATE USER 'Jack';-- 不带密码,默认没有密码
CREATE USER 'yangyi'@'localhost' IDENTIFIED BY '123';

2.修改密码

# 语法:SET PASSWORD = PASSWORD('修改后的密码')
SET PASSWORD = PASSWORD('123456')
# 修改其他用户的密码
# 语法:SET PASSWORD FOR '用户名'@'可以登陆的地址' = PASSWORD('修改后的密码');
SET PASSWORD FOR 'yangyi'@'lacalhost' = PASSWORD('123456');

3.授予权限

语法:语法:GRANT 权限列表 ON 库.对象名 TO '用户名'@'登陆位置' IDENTIFIED BY '密码'

"IDENTIFIED BY" 可以省略,如果没有省略,当用户存在时修改它的密码同时授予权限,不存在时创建该用户同时授予权限

# 语法:GRANT 权限列表 ON 库.对象名 TO '用户名'@'登陆位置' IDENTIFIED BY '密码'
# "*.*" 代表本系统中的所有数据库的所有对象
# "库.*" 代表某个数据库的所有对象
# "IDENTIFIED BY" 可以省略,如果没有省略,当用户存在时修改它的密码同时授予权限,不存在时创建该用户同时授予权限
GRANT ALL ON hsp_db02 TO 'yangyi'@'localhoat' IDENTIFIED BY '123456';

4.撤销权限

语法:REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登陆地址';

# 撤销授权
# 语法:REVOKE 权限列表 ON 库.对象名 FROM '用户名'@'登陆地址';
REVOKE ALL ON hsp_db02 FROM 'yangyi'@'localhost';

# 权限生效指令
# 如果修改权限没有生效时使用
FLUSH PRIVILEGES;

5.删除用户

语法:语法:DROP USER '用户名'@'可以登陆的地址';

# 删除用户
# 语法:DROP USER '用户名'@'可以登陆的地址';
# 删除用户时,如果host不是"%",则需要指明host
DROP USER 'jack';
DROP USER 'yangyi'@'localhost';

一安逸一

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL数据库的基本使用

sql语句的基本语法,包括创建数据库,创建表,表字段的修改,数据库的增删改查,用户名管理,常函数的基本使用
复制链接

扫一扫

专栏目录