本文共 12306 字，大约阅读时间需要 41 分钟。

一、SQL常用操作小技巧

–1、Tab键上的~ 即 ` 键 像python中的三引号一样里面的内容表示一个整体 ，有时候由于单词之前的符号会造成识别命令异常 ，加上就好了

–2、数据库的命令要以分号结束然后回车执行；

1、库级操作

- 查看当前已有数据库 记得加sshow databases;- 创建数据库create database  数据库名 ;create database  数据库名 charset=utf8;- 查看创建时命令show create database 数据库名;- 删除数据库drop database 数据库名;- 切换数据库   可以直接从一个库跳到另一个库use 数据库名;- 查看当前选择的数据库 这里没有s select database();- 删除数据库drop database 数据库名;

2、表级操作

代码顺序为增删改查

- 查看当前数据库中的表show tables;- 表内的约束内容- auto_increment表示自动增长- 创建命令create table 表名(列名及类型还有约束，可以无序，期间内容用空格分隔。不同列用逗号分隔)- 举例  enum枚举中的数据下标（索引）从1开始create table students(	id int auto_increment primary key,	sname varchar(10) not null default '保密',	gender enum('男','女','保密') default '未知',	high decimal(5,2));- 删除表内字段alter table 表名 drop 列名;alter table students drop sname;- 删除整个表 这里故意没加分号drop table 表名- 修改表结构alter table 表名 add|modify|change|drop 列名 类型;- 增加表内字段：alter table students add birthday datetime;- 修改表内某字段的约束 不用重新命名字段alter table students modify birthday date;- 修改表内某字段的约束 并重新命名字段alter table students change birthday date default "2020-09-15";- 查看创建命令- show create table 表名;show create table students;- 查看表结构desc 表名;- 重命名整体的表名rename table 原表名 to 新表名;

3、行级数据操作

*代码顺序为增删改查*- 增加- 1、全列插入：insert into 表名 values(所有的列的值 都要对应写上并用逗号分隔)insert into students values(1,'小梦','男',183.33);insert into students values(null,'小梦','男',183.33);insert into students values(default,'小梦','男',183.33);- 枚举中的数据可以用下标表示insert into students values(null,'小梦','1',183.33);- 可以同时整体插入多个 用逗号隔开就好insert into students values (1,'小梦','男',183.33),(9,'小明','男',168.77);- 2、部分插入：insert into 表名(列名1,列名x,...) values(值1,值x...) 其余部分会按约束规则补齐 default 或者 null- 可以同时插入多个 用逗号隔开就好insert into students (sname,gender) values('小梦','男');insert into students (sname,gender) values('小梦','男'),('小明','男');- 删除- 条件删除delete from 表名 where 条件- 无差别全删 挡我者si- delete from 表名- 逻辑删除，本质就是修改操作updatealter table students add isdelete bit default 0;- 如果需要删除则update students isdelete=1 where ...;- 修改- 全列值修改 （慎用）update 表名 set 列1=值1,...- 列中指定某些满足条件的值修改update 表名 set 列1=值1,... where 条件

查询语句较多 单独写出来

3.1 常规查询

- 全部查询 (数据多的时候不要这样 会很卡 ，特别多会内存耗尽)select * from 表名;- 条件查询select * from 表名 where name = "小名";- 指定字段查询select sname,gender from students where name ="小明";- 指定字段查询 并对输出结果的列名重命名 这个别名出现在结果集中 输出顺序和查询顺序一样select haha.sname as "姓名",haha.gender as "性别" from students as haha ；select sname as "姓名",gender as "性别" from students where id < 9;- 在select后面的列名前使用distinct可以消除重复的行 显示结果会去重- 去重查询select distinct gender from students;- 分页查询select * from 表名 limit start,count

3.2 条件查询

1	2	3	4	5
比较运算符	逻辑运算符	模糊匹配查询	范围匹配查询	null空值查询
> / < / = / >= / <= / =	and /or /not	like / % / _ /rlike(接正则)	in / not in / (not)between…and	is null / is not null

--优先顺序：（ ）>>> not >>> 比较运算符 >>> 逻辑运算符(and > or)select * from 表名 where 条件;- 1、比较运算符 和python基本一致--比较运算符 等于 =  大于 >  大于等于 >=  小于 <  小于等于 <=  不等于!=或 <>--举例：select * from students where id>3;-2、逻辑运算符  and or not--举例：select * from students where id>3 and gender=0;-3、模糊匹配查询-- like rlike (后面写正则表达式)-- %表示任意多个任意字符-- _表示一个任意字符--举例：-- 查询姓杨的学生select * from students where sname like '杨%';-- 查询姓杨或叫 过 的学生select * from students where sname like '杨%' or sname like '%过%';--查询姓杨并且名字是一个字的学生select * from students where sname like '杨_';--查询名字至少两个字的同学select * from students where sname like '__%';--查询名字以杨开头以过结尾的同学select * from students where sname rlike "^杨.*过$";-4、范围匹配查询-- in表示在一个非连续的范围内 -- between ... and ...表示在一个连续的范围内-- 举例：-- 查询编号是1或3或8的学生select * from students where id in(1,3,8);-- 查询编号是3-8的学生select * from students where id between 3 and 8;-- 查询学生是3至8的男生select * from students where id between 3 and 8 and gender=1;-- 查询学生不是3至8的男生select * from students where id not between 3 and 8 and gender=1;--(下面这个不常用)select * from students where not id between 3 and 8 and gender=1;-5、空值查询 （null与' '是不同的）-- 语句有 is null 和 is not null-- 举例：-- 查询没有填写地址的学生select * from students where hometown is null;-- 查询填写了地址的学生select * from students where hometown is not null and gender=0;

3.3 排序查询

--排序查询 参考语句：asc升序 desc 降序select * from 表名 order by 列1 asc|desc,列2 asc|desc,...-- 举例：select * from students where gender=1  order by id desc;-- 在排序条件结果相同的排序条件下，可以再增加新的排序规则select * from students where gender=1  order by id desc,age asc;

3.4 聚合查询

- 为了快速得到统计数据，提供了5个聚合函数 count() max() min() sum() avg()-- 举例：select count(*) from students;select count(*) as 数量 from students;-- max(列)表示求此列的最大值select max(age) from students where gender=0;-- min(列)表示求此列的最小值select min(id) from students where isdelete=0;-- sum(列)表示求此列的和select sum(age) from students where gender=1;-- avg(列)表示求此列的平均值select avg(age) from students;select sum(age)/count(*) from students;--使用函数限制小数位 并四舍五入select round(sum(age)/count(*),2) from students;

3.5 分组查询

-- 分组后的结果和去重差不多，但是能进行进一步的组内操作select 列1,列2,聚合... from 表名 group by 列1,列2,列3...-- 查询男女生总数select gender as 性别,count(*) from students group by gender;-- 使用having对分组后的数据进行进一步筛选  语法类似与where 但where是对原数据表进行的select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,...聚合...-- 查询男生总人数select gender as 性别,count(*) from students group by gender having gender=1;-- 查询同种性别的总人数 和其中所有人姓名select gender as 性别,count(*) as 总人数 group_concat(name) from students group by gender;-- 查询同种性别里年龄平均超过30岁的总人数 和其中所有人姓名select gender as 性别,count(*)  group_concat(name) from students group by gender having avg(age)>20;-- 查询同种性别里年龄年龄大于20岁，且平均分数超过90分的总人数 和其中所有人姓名select gender as 性别,count(*)  group_concat(name) from students where age>20 group by gender having avg(scor)>90;

3.6 连接查询

--连接查询--将多个由外键关联的表合按照规则并成一个大的集合 并从中提取数据-- 左连接和右连接其实用同一种语法也可 关键是谁先谁后-'内连接 inner join'（结果取交集） on后面跟连接后筛选的条件 返回的结果是一个新的表 后面可以继续接其他匹配语句select students.sname,subjects.stitle,scores.score from scores inner join students on scores.stuid=students.id;-支持as起别名简写select stu.sname,sub.stitle,sco.score from scores as sco inner join students as stu on sco.stuid=stu.id;-'左连接 left join'（结果不仅取交集，还会把左边 即 先写的那个表中数据都加上）select stu.sname,sub.stitle,sco.score from scores as sco left join students as stu on sco.stuid=stu.id;-'右连接 right join' （同左连接 ，只是数据取交集后还会把右边表的都再加上）select stu.sname,sub.stitle,sco.score from scores as sco right join students as stu on sco.stuid=stu.id;-- 举例：-- 查询科目的名称、平均分select subjects.stitle,avg(scores.score) from scores inner join subjects on scores.subid=subjects.id group by subjects.stitle;-- 查询未删除科目的名称、最高分、平均分select subjects.stitle,avg(scores.score),max(scores.score) from scores inner join subjects on scores.subid=subjects.id where subjects.isdelete=0 group by subjects.stitle;

3.7 自关联

--自关联-- 表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的-- 查询省的名称为“河南省”的所有城市select city.* from areas as city inner join areas as province on city.pid=province.id where province.atitle='河南省';-- 查询省的名称为“安阳市”的所有县或者区select city.* from areas as city inner join areas as province on city.pid=province.id where province.atitle='安阳市';-- 查询市的名称为“广州市”的所有区县select dis.*,dis2.* from areas as dis inner join areas as city on city.id=dis.pid left join areas as dis2 on dis.id=dis2.pid where city.atitle='广州市';

3.8 子查询

--子查询 查询语句里可以嵌套别的查询结果 （耗时比较大一些）select sname from students where score = (select max(*.语文) from score) ;

4、内置函数

4.1 字符串函数

函数功能	方法	举例
大小写转换	lower(str) 、upper(str)	select lower(‘aBcD’);
替换字符串	replace(str,from_str,to_str)	select replace(‘abc123’,‘123’,‘def’);
去除字符	trim(str) 默认去除空格	select trim(’ bar ');
	去除左侧 ltrim(str)	select ltrim(’ bar ');
	去除右侧 trim(str)	select rtrim(’ bar ');
	trim(方向 remstr from str)	方向：both、leading、trailing，表示两侧、左、右
		例如：select trim(both ‘x’ FROM ‘xxxbarxxx’);
截取字符串	left(str,len)返回str的左端len个字符	select substring(‘abc123’,2,3);
	right(str,len)	返回str的右端len个字符
	substring(str,pos,len)	返回str的位置pos起len个字符
包含字符个数	length(str)	select length(‘abc’);
拼接字符串	concat(str1,str2…)	select concat(12,34,‘ab’);
查看ascii码值对应的字符	char(数字)	select char(97);
查看字符的ascii码值	ascii(str)str是空串时返回0	select ascii(‘a’);

4.2 数学函数

函数功能	方法	举例
求绝对值	abs(n)	select abs(-32);
求m除以n的余数	mod(m,n)，同运算符%	select mod(10,3); select 10%3;
不小于n的最小整数	ceiling(n)	select ceiling(5.3);
不大于n的最大整数	floor(n)	select floor(2.3);
求四舍五入值	round(n,d)n表示原数，d表示小数位置，默认为0	select round(1.6);
求x的y次幂	pow(x,y)	select pow(2,3);
获取圆周率	PI()	select PI();
随机数	rand()值为0-1.0的浮点数	select rand();

4.3 日期函数

时间连接符号不必都是横线或者斜线，统一即可，注意格式化时的引号拼写

函数功能	方法	举例
获取当前时间	now()	select now();
当前日期	current_date()不含时分秒	select current_date();
获取字符中的	年	select year(‘2020-12-21’);
	月、日、时、分、秒	month()、day()、hour()、minute()、second()
日期计算	使用±运算符 跟 单位	select ‘2020-12-21’+interval 1 day;
日期格式化	date_format(date,format)	select date_format(‘2020-12-21’,’%Y %m %d’);
	年%Y	返回4位的整数
	年%y	返回2位的整数
	月%m	返回值为1-12的整数
	日%d	返回整数
	时%H	值为0-23的整数
	时%h	值为1-12的整数
	分%i	值为0-59的整数
	秒%s	值为0-59的整数

5、视图封装

视图本质就是对查询的一个封装，对于复杂的查询，在多次使用时，维护是一件非常麻烦的事情，可封装后使用

create view stuscore as

select students.*,scores.score from scores inner join students on scores.stuid=students.id;

- 封装create view stuscore as select students.*,scores.score from scores inner join students on scores.stuid=students.id;- 使用- select * from stuscore;

6、事务

当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回-

使用事务可以完成退回的功能，保证业务逻辑的正确性

事务四大特性(简称ACID)原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障要求：表的类型必须是innodb或bdb类型，才可以对此表使用事务

事务语句 | 开启begin; | 提交commit; | 回滚rollback;

二、备份与恢复

-数据备份--进入超级管理员sudo -s--进入mysql库目录cd /var/lib/mysql--运行mysqldump命令mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;--按提示输入mysql的密码-数据恢复--连接mysqk，创建数据库--退出连接，执行如下命令mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql--根据提示输入mysql密码

三、小技巧

3.1从文件导入sql命令

3.2 表中外键的修改

四、Python 和 mysql 交互操作

4.1 查询

4.11查询一行数据 fetchone() 返回单个元组

4.12查询多行数据 fetchmany(3) fetchall() 返回一个嵌套元组

4.2 增删改数据 commit() 执行 rollback() 撤回

4.3 执行用户输入的操作时要防止sql注入

4.4 封装增删改查语句

4.4.1、封装

#encoding=utf8import MySQLdbclass MysqlHelper():    def __init__(self,host,port,db,user,passwd,charset='utf8'):        self.host=host        self.port=port        self.db=db        self.user=user        self.passwd=passwd        self.charset=charset    def connect(self):        self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)        self.cursor=self.conn.cursor()    def close(self):        self.cursor.close()        self.conn.close()    def get_one(self,sql,params=()):        result=None        try:            self.connect()            self.cursor.execute(sql, params)            result = self.cursor.fetchone()            self.close()        except Exception, e:            print e.message        return result    def get_all(self,sql,params=()):        list=()        try:            self.connect()            self.cursor.execute(sql,params)            list=self.cursor.fetchall()            self.close()        except Exception,e:            print e.message        return list    def insert(self,sql,params=()):        return self.__edit(sql,params)    def update(self, sql, params=()):        return self.__edit(sql, params)    def delete(self, sql, params=()):        return self.__edit(sql, params)    def __edit(self,sql,params):        count=0        try:            self.connect()            count=self.cursor.execute(sql,params)            self.conn.commit()            self.close()        except Exception,e:            print e.message        return count

4.4.2、添加

#encoding=utf8from MysqlHelper import *sql='insert into students(sname,gender) values(%s,%s)'sname=raw_input("请输入用户名：")gender=raw_input("请输入性别，1为男，0为女")params=[sname,bool(gender)]mysqlHelper=MysqlHelper('localhost',3306,'test1','root','mysql')count=mysqlHelper.insert(sql,params)if count==1:    print 'ok'else:    print 'error'

4.4.3、查询

#encoding=utf8from MysqlHelper import *sql='select sname,gender from students order by id desc'helper=MysqlHelper('localhost',3306,'test1','root','mysql')one=helper.get_one(sql)print one

五、mysql安装图解8.0.22

5.1 Ubuntu 安装方式 ：

5.2 Windows 安装方式：

官网下载压缩包

下载完以后 找到下载位置 然后解压

配置步骤图解

1.添加环境变量

2.终端初始化

3.进入之前解压后文件夹的bin目录执行以下命令

先把可能报错的图和解决方式放这里了 有的电脑不会报错

功能	命令代码
安装mysql	mysqld --install
初始化	mysqld --initialize --console
启动	net start mysql
登录	mysql -u root -p
更改密码 这行可能要手打	alter user ‘root’@‘localhost’ identified by ‘password’;
退出	exit;
注意	(上面这两句记得在末尾加分号)

5.3 Navicat 可视化数据库管理工具的安装

① 安装和破解过程（破解软件请自己搜索）

1、访问官网下载premium版本

https://www.navicat.com.cn/download/navicat-premium 2、选择适合自己的版本下载 速度还是很快的 3、默认安装 记得同意许可 4、破解 去脚本之家或者哪里下载一个破解器 5、破解后 启动程序 点击注册 此时已经不联网了 由破解程序去继续填充 6、激活 7、激活成功

②Ubuntu下曾经遇到的乱码框框问题解决方式

转载地址：http://bjjzk.baihongyu.com/