Mysql数据库基础——进阶

Mysql数据库的使用和sql语句——进阶

前言：已经用过一段时间的mysql，想对mysql的使用进行一个总结。如果有错误的话欢迎下方留言，谢谢！

连接数据库

1. 软件连接：Navicat
   
2. windows终端连接
   
3. python连接

import pymysql

if __name__=='__main__':
	conn = pymysql.connect(host='127.0.0.1', 
						user='root',
						password='root', 
						database='mydatabse', 
						port=3306, 
						charset='utf8')
	cur = conn.cursor()
	ret = cur.execute('select * from emp;')	# 查看表信息
	lines = fetchall()
	print('emp表的内容：', lines)

4. 远程连接数据库常见错误及解决办法：
   （1）连接数据库报错：ERROR 1045 <280000>: Access denied for user ‘root’@‘localhost’
   解决方法：修改mysql配置文件跳过用户验证
   示例路径：D:\MySQL5.7.26\my.ini
   方法：在 [mysqld] 节下面第一行添加“skip-grant-tables”字段，如：

[mysqld]
skip-grant-tables

重启 mysql 数据库服务，之后输入 mysql 不需要账号、密码就可以连上了

（2）连接数据库报错：pymysql.connect错误解决 (1130, “Host ‘X.X.X.X’ is not allowed to connect to this MySQL server”)

本地直接登录mysql
msql -u root -p
-->输入password
切换到mysql数据库：use mysql;
设置root客户端用户可以通过任意ip连接数据库：
update user set host='%' where user='root';	
flush privileges;	

基础篇

1. MySQL的创始人 Monty
2. mysql 查询引擎
   ENGINE=MyISAM
   ENGINE=InnoDB
3. 数据库工程师 DBA
4. 关系型数据库 Oracle和Mysql
5. 非关系型数据库 Redis和MongoDB

SQL 语言分类
	1.DDL  数据定义语言   关键词
		创建：create
		删除表：drop
		清空表数据，表存在：truncate
		DDL：data definition language
	2.DML  数据操纵语言   关键词
		增加：insert
		删除：delete  
		修改：update
		DML：data manipulation language
	3.DQL  数据查询语言   关键词
		select  <字段名>  from<表或视图名>
		where<查询条件>
		DQL：Data Query Language
	4.DCL  数据控制语言   关键词
		授予：grant  
		回收：revoke  
		提交：commit
		回滚：rollback
		DCL：Data Control Language

 常见的数据类型
	1.1字符类型：
		定长字符串（固定）：char    
		变长字符串（可变）：varchar  
		长文本数据：text
	1.2数值类型：
		整数：int   
		极大整数：bigint    
		浮点数：double （M,D）M位整数，D位小数
	1.3日期时间类型
		年、月、日：data
		时、分、秒：time
		混合日期和时间（YYYY-MM--DDHH:MM:SS）:datetime
		混合日期和时间值：timestamp
		（datetime的 时间范围是1000-9999年）
		（timestamp的范围是1970-2038年）（时间戳自动获取）
	1.4约束类型
		主键：primary key    
		外键：foreign key    
		唯一：unique    
		非空：not null
		自增：auto_increment
		默认值：default
		外键约束：foreign key

9. 启动数据库：Mysql -h 127.0.0.1 -u root -p 3306 -p
10. 创建数据库 create database +数据库名;
11. 查看数据库 show DATABASES;
12. 查看指定数据库中有哪些表——有三步：
    1、show DATABASES;
    2、use +数据库名;
    3、SHOW TABLES;
13. 删除数据库 drop database +数据库名；
14. 备份数据库：使用工具直接导出
15. 恢复数据库：使用工具导入

MySQL操作数据，数据的增删查改

表

创建表存在时的处理：
drop table if exists stu;		// 如果表存在就删除
CREATE TABLE stu if not EXISTS stu( . . . . . . );		// 如果表不存在就创建，如果存在就跳过

创建表

创建表：案例：
CREATE TABLE stu (
	sid int(20) NOT null auto_increment,
	sname varchar(30) NOT null,
	sex char(3) DEFAULT '男',
	age varchar(2) DEFAULT 18,
	ajointime date DEFAULT '2008-01-01',
	primary key(sid)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看全部表 SHOW TABLES;
查看表结构：desc +表名;
修改表名：alter table +旧表名+rename+新表名;
查询表：select * from stu;
增加字段：alter table stu add(beizhu varchar(200) not null );
删除字段：alter table stu drop column beizhu;

插入数据

insert into stu values (1,'小红','女',18,'2019-08-22',100);
insert into stu (sname,sex,age,ajointime,t_id) values ('小红','女',18,'2019-08-22',100);

根据条件查询数据

select * from stu where sname='伦';
select * from stu where age=18;
select * from stu where age=18 and sex='男';
select * from stu where age=18 or sname='小红';

删除数据

删除表：drop
	drop table stu;
删除数据：delete
	delete from stu where age=30; 
	delete from stu where age=22 or name='李白';
清空数据：truncate（将表中的数据全部删除，不支持带where 条件删除；删除内容，不删除表结构）：
truncate table +表名;

修改表数据

语法：update 表名 set 列名1=值，列名2=值 （set：设置值）（修改表的所有记录不建议使用）

update stu set age=88, ajointime='2018-05-25';将stu表中的年龄和入学时间修改为：88岁，2018-05-25
update stu set sex='女';将stu表中的性别，全部修改为女
根据条件修改：
update stu set sname='大大' where sname='小红';
update stu set sex='女',sname='孙悟空' where sex='男' and sname='猴子';
update stu set sex='女' where sname='张三';

MySQL查询语句+运算符+函数

基础查询
字段名------要查询的列名称：SELECT
查询指定列：SELECT sid, sname, age FROM stu;
表名------要查询的表名称：FROM
字段条件------接查询的值的条件：WHERE
字段分组------对结果分组：GROUP BY
字段分组的条件------分组后的行条件：HAVING
排序字段------对结果排序 ：ORDER BY

select Market,count(Market) from Product GROUP BY Market ORDER BY Market DESC;	// 查询Product表中Market相同值的数量，并对Market进行排序

条件查询+运算符

算数运算符 ： 加（+）、减（-）、乘（*）、除（/）（%）

select * from stu where age<40;

比较运算符： 等于（=）、不等于（!=）、小于（<）、大于（>）、小于等于（<=）、大于等于（>=）
多个值：in

select * from stu where sid not in(1,2,3);
select * from stu where sid in(1,2,3);

为空：is null

select * from stu where beizhu is null;

不为空：is not null

select * from stu where beizhu is not null;

在/不在…之间（相当于大于小于）：between、not between

逻辑运算符： 和（and/&）、或（or）、非（not）

 select * from stuwhere sex='女 ' and age<50;
 SELECT * from stuwhere sid=1 or sname='小白';
 SELECT * from stu where sid in(1,2,3,4);
 select * from stu where sid not in (1,2,3);
 SELECT * FROM stu WHERE age IS NULL; 
 SELECT * FROM stu WHERE age>=20 AND age<=40;
 SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

连接运算符：Concat

模糊查询：like

通配符：%

select * from EMP where ename like '%A%';
select * from EMP where ename like 'A%';
select * from EMP where ename like '%A';

通配符：_

Select * from emp where job like '_____';     （查询职称为 5 位）

% 与 _ 的区别：%表示任意多个字符，_ 表示任意一个字符

字段控制查询：DISTINCT( 不同的）

sal去重查询：

select distinct sal from emp;

查看员工的月薪与奖金之和

Select sal+comm from emp;

查询 emp 表，薪资 函数 IFNULL （如果奖金为 NULL 的时候，转为0）如果不为 NULL 不做转换

 select sal+ ifnull (comm,0) from emp;
 select ifnull(sal,0)+ ifnull (comm,0) from emp;

将 sal +comm 字段取别名为total

Select sal+ifnull(comm,0) as total from emp;

连接：concat （内容 1, 内容 2）

select concat (ename,sal) from emp;

排序：order by

升序（值从小到大排列）：asc
降序（值从大到小排列）：desc
select * from emp order by sal desc;
当sal排序的值相同时，这两个值按mgr排序
select * from emp order by sal desc,mgr asc;

分组查询GROUP BY和having子句

查询emp表各个job的人数

select job,count(*) from emp group by job;

查询 员工表职位以及职位的人数，并且人数 2 的职位

 select job,count(*) from emp group by job having count(*) > 2;

分页，翻页：limit

第5行开始，查看3条数据

select * from stu limit 4,3;

多表连查
常用：内连接，左连接

内连接（常用）：inner join 例如：select * from emp e inner join dept d on e.detno=d.deptno;
外连接：full join 或 full outer join
左连接（常用）：left join 例如：select * from emp e left join dept d on e.detno=d.deptno;
右连接：right join 例如：select * from emp e right join dept d on e.detno=d.deptno;
自然连接：natural join 例如：select * from emp natural join dept;

子查询（嵌套查询）（where，from）

select ename from emp where age>(select age from emp where ename='allen');

聚合函数

总记录数：COUNT()

select count(sal) as total from emp where sal>2500;

最大值：MAX()

select max(sal) from emp;

最小值：MIN()

select min(sal) from emp;

数值和：SUM()

select sum(sal),sum(comm) from emp;

平均值：AVG()

select avg(sal) from emp;

内置函数

字符串函数

拼接字符串函数：concat

select concat('hello','word') from emp;
select concat(ename,sal) from emp;

计算字符个数：length

select length('abc') from emp;
select length(ename) from emp;

截取字符串：substr

select substr('hellowordhhh',6,2)from emp;   截取第6位后面2位的字符串
select substr(ename,1,2)from emp;   截取员工的名字

小——大转换：upper

select upper('aaa') from dual;
select upper(ename) from emp;

大——小转换：lower

select lower('AAA') from dual;

数字函数

四舍五入：round

select round(188.5890) from emp;

日期函数

当前日期：current_date()

select current_date() as newdate;

当前时间：current_time()

select current_time() as newdate;

当前日期和时间： now()

select now() as newdate;

其他函数

将空值转换为 0 的函数：ifnull

select sal+ifnull(comm,0) from emp;        （数值为空时加ifnull）

进阶篇

存储过程：procedure

存储过程的创建和调用

drop procedure if exists testa; --如果存储过程存在就删除
create procedure testa()   --创建存储过程
	begin --开始
		--声明变量（不是必要的）
		select * from emp;  --写sql语句
	end; --结束
call testa();		--调用存储过程

变量的声明和使用

变量声明的语法：宣布声明 ：declare 变量名 类型（长度）
变量的使用：

drop procedure if exists testb;
create procedure testb()
	begin
		declare t_name varchar(20);  --声明变量
		set t_name="喜洋洋";  --变量赋值
		select t_name;  --查询t_name的值
	end;
call testb();  --调用存储过程

流程语句的使用

if 分支语句：

		--if分支语句
		if （条件） then
		pl/sql 或 sql 语句
		[elseif (条件) then ]
		...可以有多个 elseif
		[else]
		end if；
		--使用 IF…ELSE 

drop procedure if exists teatc;
create procedure testc()
	BEGIN
		declare t2 int(10);
		set t2=9000;
		if t2>=8000 then
				select '大哥大';
			elseif t2>=4000 then
				select '小哥哥';
			else
				select '萌新';
		end if;
	end;
call testc;

loop 循环语句：

	--Loop 循环中，我们需要自定义循环结束的条件，语法如下： 
	loopName:loop
	if 条件 THEN -- 满足条件时离开循环
	leave loopName; --结束循环
	end if；
	end loop;
	--例如：-输出 10 遍 helloWorld 

DROP PROCEDURE IF EXISTS test_e;
create procedure test_e()
	begin
	declare i int default 0;
		loopName : loop --开始循环，取名为 looopname
		select 'helloWorld'; -- 查询 helloworld
		set i = i+1; -- i 变量每次循环加 1
			if i > 10 then -- 当 i 等于 11 时
			leave loopName; -- 跳出循环
			end if;
		end loop; -- 结束循环
	end;
call test_e();
--备注：循环需要找条件退出，不能一直循环，否则会死循环。

while 循环语句
for 循环语句

测试应用-批量插入数据：

--批量插入数据 100 条
drop rocedure if exists teaq;
create procedure testq()
begin
	declare a int default 0;
	loopname:loop  --插入数据
	insert into stu (sname,sex,age,ajointime,t_id,beizhu,loc) VALUES ('支付宝','女',18,'2019-08-08',1,'她是一个明星','北京');
	set a=a+1;
	if a>100 then
		leave loopname;
	end if;
	end loop;
end;  --调用存储过程
call testq();

select * from stu;  --查询stu查看数据是否成功

游标的用法：

DROP PROCEDURE IF EXISTS testProce ;
CREATE PROCEDURE testProce ()
BEGIN
-- 定义变量
DECLARE tname VARCHAR (10);
-- 创建游标，并存储数据
-- 游标结束的标志
DECLARE done,
total INT DEFAULT 0;
DECLARE cur_test CURSOR FOR SELECT t_name AS tname FROM test_a;
-- 打开游标
OPEN cur_test;
-- 初始化 变量
SET total = 0;
-- 执行循环
posLoop :
LOOP
-- 取游标中的值
FETCH cur_test INTO tname;
-- 执行更新操作
SELECT tname;
IF done = 1 THEN LEAVE posLoop;
END IF;
-- 累计
SET total = total + 1;
END LOOP posLoop;
-- 释放游标
CLOSE cur_test;
-- 调用存储过程
call testProce();

	在工作中游标的真实工作应用场景——迁移数据
	例如：
	B库想拿到A库table1表中sname和table2表中的

➢ 声明一个游标：
语法：declare +游标名 +cursor for +查询语句;

DECLARE cur_test cursor for select ename from emp;

➢ 打开定义的游标：OPEN cur_test;
➢ 获得下一行数据：
语法：fetch +游标名 +into +变量名;

FETCH cur_test INTO tname;

➢ 释放游标: CLOSE 游标名称;

游标是系统为用户开设的一个数据缓冲区，存放 sql 语句执行结果，每个游标区都有一个名字，用户可以通过游标逐一获取记录。

视图

创建视图（视图语法）

语法：CREATE VIEW 视图名称 AS sql

– CREATE 后面加 OR REPLACE 代表如果本视图存在则覆盖

– sql 必须是查询类 sql sql 内容可以是查询视图 也就是说 mysql 允许视图嵌套

调用视图：

SELECT * FROM view_name_1;

删除视图：DROP VIEW 视图名;

事务

引发事务的操作：mysql 默认不支持事务,原因是 autocommit=1 默认自动提交,在InnoDB 引擎下可通过修改autocommit=0 关闭自动提交功能后实现事务

开启、提交事务的方法

1. BEGIN 或 START TRANSACTION 开启一个事务；
2. COMMIT 提交事务。把事务中对数据库的修改改进进行永久保存
3. ROLLBACK 回滚事务，即取消对数据库所做的任何修改。

语法：

begin; # 开始事务
---SQL 语句
rollback; # 回滚

索引

创建索引

普通索引：

alter table +表名+ add index +索引名(字段名);

唯一索引：

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

删除索引：

DROP INDEX+索引名+ ON +表名;  

主/外键

主键：primary key

创建主键：

alter table +表名+add + primary key(id);

删除主键：

alter table +表名 + drop primary key;

外键：foreign key

ALTER TABLE +表名+ ADD CONSTRAINT +索引名+FOREIGN KEY(列名) REFERENCES +主表名+ (列名);

	外键约束使用最多的两种情况：
	1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；
	2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

前一种情况，在外键定义中，我们使用：
ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用
ON UPDATE CASCADE ON DELETE CASCADE。
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>)
ON UPDATE CASCADE ON DELETE RESTRICT ;

删除外键：

ALTER TABLE +表名+ DROP FOREIGN KEY +外键约束名;

数据库优化：

1.创建视图、索引这些就是在对查询数据进行优化
2.表的设计和分区也可以进行优化。