MySQL基本操作

MySQL基础知识

 使用方法:
 方式一: 通过图型界面工具,如 Navicat 等( 高级课使用 )
 方式二: 通过在命令行敲命令来操作 ( 基础阶段使用 )
 
 **SQL ( Structure query language ) 结构化查询语言**
 SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）
 1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
 2、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
 3、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE
 4、DQL语句 数据库查询语言： 查询数据SELECT
 https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/

 mysql数据库管理软件,记录事物一些数据特征:
 由库,表,记录组成.
 库相当于一个文件夹
 表相当于一个文件
 记录就是文件里面一条一条的内容
 表中的成员属性就是一个一个字段
 可以为每个项目建立一个数据库
 
 关系型数据库:表与表之间有联系
 比如:mysql,oracle,db2,sqlserver
 
 非关系型数据库: key-value 键值对形式 没有表的概念
 比如:redis,mongodb,memcache

 **SQL语句中的快捷键**
 G 格式化输出（文本式，竖立显示）
 s 查看服务器端信息
 c 结束命令输入操作
 q 退出当前sql命令行模式
 h 查看帮助
 
 #数据库语法特点
 SQL 语句可以换行, 要以分号结尾
 命令不区分大小写. 关键字和函数建议用大写
 如果提示符为 '> 那么需要输入一个'回车
 命令打错了换行后不能修改, 可以用 c 取消
 
 # ### mysql 卸载 
 # (1) windows 卸载
 关闭服务
 cmd : mysqld remove
 删除已经解压的文件夹
 重启电脑
 
 # (2) linux 卸载
 sudo apt-get autoremove --purge mysql-server-5.7 
 sudo apt-get remove mysql-common
 sudo rm -rf /etc/mysql/  /var/lib/mysql   
 #清理残留数据
 dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P  
 sudo apt autoremove
 sudo apt autoreclean

增删改查基本操作

 ctrl + l 清屏
 ctrl + c 终止
 G 垂直分布的方式进行查看
 s 查看服务器端信息
 
 #启动/停止服务器
 [linux]
 service mysql start   启动mysql
 service mysql stop    停止mysql
 service mysql restart 重启mysql
 [windows] 在管理员权限下运行
 net start mysql     启动mysql
 net stop  mysql     停止mysql
 
 (1)part1
 #连接到服务器,连接本地时可省略ip地址
 mysql -u用户名 -p密码 -h [ip地址 -P端口]
 #退出mysql
 exit 或 quit 或 q
 
 (2)part2
 #查询当前登陆用户
 select user()
 #设置密码
 set password = password('密码')
 #去除密码
 set password = password('')
 
 (3)part3
 #显示用户及权限设置
 select user,host from mysql.user #显示用户名及ip
 select * from mysql.user    #显示所有用户信息
 
 #创建用户
 create user '用户名'@'ip' identified by '密码' #给具体ip设置账户
 create user '用户名'@'191.168.23.%' idenfitied by '密码' #给某个网段设置账户
 create user '用户名'@'%' #所有ip都能利用该账户连接到数据库
 #删除账户
 drop user '用户名'@'ip'
 
 #用户权限
 #查询权限
 show grants for '用户名'@'ip';
 #grant 权限 on 数据库.表 to '用户名'%'ip' identified by '密码'
 #select/insert/update/delete 查询/插入/更新/删除数据库的权限
 #移除权限
 drop user '用户名'@'ip'
 #刷新权限
 flush privileges
 
 (4)part4
 1.操作[数据库] (文件夹)
 show databases;                         查看所有数据库
 show creat database 数据库名;            查看单个数据库
 desc 数据库名;                           查看单个数据库结构
 create databases 数据库名 charset 字符集; 创建数据库
 alter database 数据库名 charset 字符集;    更改数据库字符集
 drop database 数据库名;                  删除数据库
 
 2.操作[数据表] (文件夹)
 use 数据库名;                           先选择使用一个数据库进行创建表
 create table 表名(字段1 字符集, 字段2 字符集, ...);               创建表
 create table if not exists 表名(字段1 字符集, 字段2 字符集, ...); 如果不存在则创建表
 show tables;             查看所有表
 show create table 表名;   查看单个表
 desc 表名;                查看单个表的结构
 #改
 alter table 表名 modify 字段名 数据类型;         odify用于更改数据类型
 alter table 表名 rename 字段名;                rename用于更改表名
 alter table 表名 change 字段名 新字段名 数据类型; change可同时更改字段名和数据类型
 alter table 表名 add 字段名 数据类型;            add用于添加字段
 alter table 表名 drop cloumn 字段名;           drop cloumn用于删除字段,即删除列
 #删
 drop 表1[, 表2, ...]; #删除表
 
 3.操作记录 (文件内容)
 #NULL等价null select等价SELECT 系统不区分大小写 NULL等价None
 增:
 insert into 表(字段1,字段2) values(值1,值2);          插入一条数据,into可省略
 insert into 表(字段1,字段2) values(值1,值2),(值3,值4); 插入多条数据
 insert into 表 values(值1,值2);                      不指定具体字段,默认将所有字段都插一遍
 insert into 表(字段) values(值);                     可指定只插入某个字段
 查:
 select * from 表; 查询表中所有内容,*代表所有
 select 字段1,字段2 from 表; 查询表中某些字段
 改:
 update 表 set 字段=值; 更新某个字段的值
 update 表 set 字段1=值1 where 字段2=值2; 更新某个字段的值时加入约束
 删:
 delete from 表 where 字段=值; 删除某条数据
 delete form 表;              删除表中所有数据
 truncate 表;     刪除表中所有所有数据(ID记录也删除)
 #delete与truncate删除
 灵活性：delete根据条件删除数据，truncate只能删除所有数据
 效率：delete效率更低，一行一行删除，truncate重建表结构
 事务：delete是DML语句，可以rollback；truncate是DDL语句，需要drip权限，因此会隐式提交，不能rollback
 触发器：delete可以出发delete触发器，truncate不能出发任何delete触发器

常用数据类型

 tinyint(m)  1个字节  范围(-128~127)
 int(m)      4个字节  范围(-2147483648~2147483647)
 float(m,d)  单精度浮点型    8位精度(4字节)  m总个数，d小数位,默认保留5位小数
 double(m,d) 双精度浮点型    16位精度(8字节) m总个数，d小数位,,默认保留16位小数
 decimal(m,d)定点数   参数m<65 是总个数，d<30且 d<m 是小数位。
 char(n)     固定长度，最多255个字符
 varchar(n)  可变长度，最多65535个字符
 text        可变长度，最多65535个字符
 
 enum() 枚举:从一组数据中选一个(如性别)
 set()  集合:从一组数据中选多个(自动去重)
 
 #取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
 #int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。
 
 char和varchar：
 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。 
 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。 
 MySQL数据类型 https://www.cnblogs.com/-xlp/p/8617760.html

时间类型

 year        YYYY 年份 (酒的年份)
 date        YYYY-MM-DD 年月日 (生日)
 time        HH:MM:SS (计时)
 datetime    YYYY-MM-DD HH:MM:SS (登陆时间,下单时间)
 timestamp   YYYYMMDDHHMMSS 时间戳自动更新时间 (不需要手动写入,修改数据的时候,自动更新,可以看到最后一次修改时间)
 now()       内置函数,获取当前函数
 #2038年问题:到2038年1月19日,在大部分的32位操作系统上，此“time_t”数据模式使用一个有正负号的32位整数（signed int32）存储计算的秒数

约束

 # 约束constraint: 对插入的数据进行限制,不满足条件的报错
 unsigned       无符号
 not null       不为空
 default        设置默认值
 unique         唯一约束,数据唯一不重复
 primary key    主键,唯一不为空的值,用来表达数据的唯一性
 auto_increment 自增加一 [一般针对主键 或者 unique 进行设置]
 zerofill       零填充 , int(10) , 位数不够填充0
 foreign key    外键,把多张表通过一个字段联合在一起
 
 #联合约束
 # 1.联合唯一约束 : 
 (1)联合唯一主键 字段1 not null,字段2 not null,unique(字段1,字段2 , ....)
 (2)联合唯一索引 字段1,字段2, unique(字段1,字段2) MUL 就是一个普通的索引(单纯为了加快查询速度)
 
 (3)如果两个类型同时存在,默认吧primary key 设置成PRI 主键
 # ip 和 port 是联合在一起作为一个主键 , id 是单个字段的主键, 针对于单个字段只能有一个主键
 #(了解) unique(ip,port) primary key(ip,port) 用法一样,区别在于不能继续添加主键
 (4)联级删除 联级更新 (谨慎操作)
 on delete cascade 联级删除
 on update cascade 联级更新

存储引擎场景

 InnoDB
 用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
 
 MyISAM
 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
 
 Memory
 将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
 
 黑洞服务器:从主服务器读取relaylog,创建新Binarylog日志,负责发送给从数据库,而本身不需要任何数据
 
 # 存储引擎:
 # 概念理解:
 表级锁(table-level locking): 如果有一个人在修改这张表,那么就上锁,其他人无法操作,速度比较慢,不能并发
 行级锁(row-level locking): 有一个人修改这个表中的一条记录,当前这个记录上锁,其他数据可以正常修改,允许更大的并发,和更快的速度
 事务处理 : 如果执行sql语句,全部成功之后,然后提交,如果有一条失败,那么回滚,恢复成原来的数据;
     begin    开启事务  
     commit   提交数据 
     rollback 回滚数据
 
 # 存储引擎常用种类:
 MyISAM  : 5.6版本之前,默认的存储引擎 ,支持表级锁
 InnoDB  : 5.6版本之后,支持行级锁,外键,能抗住更大的并发,支持事务
 MEMORY  : 把数据存储在内存当中,也可以叫做缓存.
 BLACKHOLE: 黑洞,用来同步数据,场景在服务器集群中使用,比如:主从数据库
 #查看引擎 show engines
 #查看当前的默认存储引擎 show variables like "default_storage_engine"
 #更改表的存储引擎 
 方法1:alter table t1 engine = innodb;
 方法2:
 #my.ini文件
 [mysqld]
 default_storage_engine=INNODB
 
 我以为我对Mysql索引很了解，直到我遇到了阿里的面试官 - 为何不是梦的文章 - 知乎
 https://zhuanlan.zhihu.com/p/73204847

条件查询

1.单表查询

#sql查询语句完整语法(约束条件):
select ... from .... where ... group by ... having ... order by ... limit 
一.where 条件的使用
"""功能:对表中的数据进行过滤筛选"""
    1.判断的符号:
    = > < >= <= != <> 不等于
    2.拼接条件的关键字
    and not or
    3.查询区间范围值
    between 小值 and 大值 [小值,大值] 查询两者之间的范围
    4.查询某个具体的范围值
    in(值1,值2,值3) 在括号这个范围中查询
    5.模糊查询 like '%' 通配符
        like "%a" 匹配以a结尾的任意长度的字符串
        like "a%" 匹配以a开头的任意长度的字符串
        like "%a%"匹配含有a字母的任意长度的字符串
        like "_a" 一共长度2个,以a结尾,前面这个字符无所谓
        like "a__"一共长度3个,以a开头,后面必须是2个字符,是什么字符无所谓
    6.关键字 is null (判断某个字段是不是null,不能用等号,只能用is)
    7.concat联结 sql内置函数 concat(参数1,参数2,参数3,...)
    8.在sql中也可以使用四则运算
		
二.group by 分组,分类;
    #group by 分组分类功能,by后面的字段,一般是select后面要搜索的字段值(by谁搜谁)
    1.group_concat按照分组形式进行拼接字段
    2.聚合函数
        一般来说,分组+聚合函数 配合使用
        count() 统计记录总数,相当于行数,count(*)统计表中所有
        	select count(*) from employee
        avg()	统计平均值
        sum()	统计总和
        max()   统计最大值
        min()	统计最小值

三.having 查询数据之后再进行过滤 , 一般配合group by 使用, 主要用在分组之后再过滤
	相当于where,不同的having是用于分组后面的条件约束
 	select post,avg(salary) from employee group by post having avg(salary) > 10000
 	
四.order by 按照什么字段进行排序
 	默认按照asc 升序排序,可以按照desc降序排序
 	select emp_name,age,post from employee order by age      (默认升序)
	select emp_name,age,post from employee order by age desc (降序)
	
五.limit 限制查询的条数 (做数据分页)
	limit m,n	从m+1开始查询,查询n条;m可省略,则默认从第一条开始查询n条
 	select * from employee limit 0,5 #从第一条开始查询5条
 	select * from employee group by id desc limit 1 #配合分组查询最后一条
 	
六.可以使用正则表达式查询数据(了解,不好用,效率不高)
	select * from employee where  emp_name regexp "程.*?金";

	part2:多表查询

2.多表查询

一.内连接:inner join,内联查询,两表或多表满足条件的所有数据查询出来(表和表之间都用共同的部分查询出来)
	select 字段 from 表1 inner join 表2 on 条件
	select 字段 from 表1 inner join 表2 on 条件 inner join 表2 on 条件 ...
	基本语法:
	select * from employee inner join department on demploy.dep_id = deparment.id 
	select * from employee as e inner join department as d on e.dep_id = d.id #用as起别名,可省略as
	#使用普通where默认使用的就是内联查询
	select * from employee as e, department as where e.dep_id = d.id

二.外连接
	左连接(left join):以左边为主,右边为辅,完整查询左表数据,右表没有的数据补NULL
	select 字段 from 表1 left join 表2 on 条件"""
	select * from employee left join department on employee.dep_id = department.id;	
	右连接(right join):以右边为主,左边为辅,完整查询右表数据,左表没有的数据补NULL
	select 字段 from 表1 right join 表2 on 条件"""
	select * from employee right join department on employee.dep_id = department.id;
	全连接(union)
	select * from employee left join department on employee.dep_id = department.id	
	union
	select * from employee right join department on employee.dep_id = department.id

3.子查询

	#where查询
	select d.id, d.name from employee e, department d where e.dep_id = d.id group by d.id, d.name having avg(e.age) > 25
	#inner join查询
	select d.id, d.name from employee e inner join deparment d on e.dep_id = d.id group by d.id, d.name having avg(e.age) > 25
	#子查询
	select id, name where id in (select dep_id from employee group by dep_id having avg(age) > 25)
子查询: 嵌套查询
1.子查询是查询的语句当中又嵌套了另外一个sql语句,用括号()包起来,表达一个整体
2.一般应用在 from 或者 where 字句的后面,子查询这个整体可以表达一张表,可以表达一个条件.
3.速度从块到慢  单表查询速度最快 > 联表查询 > 子查询

SQL注入攻击(python示例）

import pymysql
user = input("user>>:").strip()
pwd = input("password>>:").strip()
#攻击代码示例: ' or True -- k
conn = pymysql.connect(host="127.0.0.1",user="root",password="root",database="db1")
cursor = conn.cursor()
sql = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
res = cursor.execute(sql)
print(res)
if res:
	print("登陆成功")
else:
	print("登陆失败")
cursor.close()
conn.close()

python操作数据库

import pymysql
conn = pymysql.connect(host='localhost',user='root',password='root',database='db1')
cursor = conn.cursor(cursor = pymsql.cursors.DictCursor)

#sql0 = 'begin' #事务处理使用begin开头,commit提交
sql = 'insert into employee(id, name) values(%s, %s)'
#sql1 = 'commit'

#cursor.execute(sql0)
cursor.execute(sql,(11,'a'))
#cursor.execute(sql1)

cursor.close()
conn.close()