Mysql命令总结

day7
数据库(mysql：关系型数据库管理系统)

(总结：https://blog.csdn.net/picway/article/details/53646994
练习：https://www.cnblogs.com/aqxss/p/6563625.html
）

1.数据库语言（5种）

1.数据定义语言DDL（Data Definition Language）	:create、drop、truncate、alter、show语句
2.数据操作语言DML（Date Manipulation Language）	:insert、update、delete语句
3.数据查询语言DQL（Data Query Language）	:select语句
4.数据控制语言DCL（Data Control Language）	:grant、revoke语句
5.事务控制语言DTL（Data Transaction Language）	:commit,rollback等语句


 1：数据定义语言（DDL）
	 用于创建、修改、和删除数据库内的数据结构，如：1：创建和删除数据库(CREATE DATABASE || DROP  DATABASE)；2：		创建、修改、重命名、删除表(CREATE  TABLE || ALTER TABLE|| RENAME TABLE||DROP  TABLE)；3：创建和删除索引				(CREATEINDEX  || DROP INDEX)

2：数据查询语言（DQL）
    	从数据库中的一个或多个表中查询数据(SELECT)

3：数据操作语言（DML）
	修改数据库中的数据，包括插入(INSERT)、更新(UPDATE)和删除(DELETE)

4：数据控制语言（DCL）
      	用于对数据库的访问，如：1：给用户授予访问权限（GRANT）;2：取消用户访问权限（REMOKE）
5.事务控制语言DTL（Data Transaction Language）	:commit,rollback等语句

2.打开doc命令窗口：win+r

3.cmd登录mysql：mysql -u root -p
root

4.数据库mysql:(别删库！)
数据库中:数据集是utf8，没有-
数据库启动、关闭：计算机管理–服务和应用程序–服务（修改数据库配置内容时，有时候需要在里面停止再启动或重启）
数据库结束语： 分号“；”，windows中mysql命令不区分大小写，linux区分（所以尽量区分养成习惯）

5.数据类型：4种

1、数值型：整型，小数型(double,float)

	1.整数型：
		tinyint(1字节)  :-128---127
  			smallint(2字节) :-32768---32767
  			mediumint(3字节):-8388608-8388607
  			int(4字节)      :-2147483648-2147483647
  			bigint(8字节)   :太大

	2.小数型：
		float（浮点型）
		double（双精度浮点型）
		
		decimal(定点型)

3.字符串:
	enum(限定字符串)：
	char(<=225b)	:每次存数据，余出来的内存会用空格填补，反应时间短（适用于定长字符串）
	varchar(<=225b):优化，每次存数据，多出来的内存会释放，反应时间较长
	text(文本)


3、时间
	1.year（年份）	:	yyy
	2.date（年月日）:	yy-mm-dd
	3.time(时分秒)	:	hh-ii-ss
	4.datetime	:	yy-mm-dd hh-ii-ss


4、空
	null?



5.类型附加功能
	unsigned		：不分正负，	如tinyint unsigned	   （tinyint是-128---127,加unsigned后范围在0---255中）

	zerofill		: 最少位数，	如int(6) zerofill	   （最少显示六位，多写多显示）

6.null 和 default
not null和default : 非空和默认值，如int(6) not null default 0 (非空，默认值为0)

7.字符集（编码作用）
字符集与校对规则：https://www.cnblogs.com/geaozhang/p/6724393.html?utm_source=itdadao&utm_medium=referral

查看mysql支持哪些字符集		：show character set；
查看当前服务器使用的字符集？	：show variables like 'character_set_server';
查看当前服务器字符集的校对规则	：show variables like 'collation_server';
查看当前Mysql数据库的字符集	：show varialbes like 'character_set_database';
数据库字符集的校对规则		：show variables like 'collation_database';
设置字符集			：set names 'utf8'
查看某个校对规则对应的字符集	：show collation like '校对规则(gbk_chinese_ci)'
查看所有校队规则对应的字符集	：show collation like '%'；（%表查全部）
查看字符串长度			：select length('张三');
数据库创建字符集		：create database 库名 default character set 字符集 collate 校对规则；
数据表创建字符集		：create table 表名(...) default character set 字符集 collate 校对规则；

一、事务
定义：
Transaction
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就 是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
事务只和DML语句有 关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

事务四大特性：
		原子性(A)：事务是最小单位，不可再分
		一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
		隔离性(I)：事务A和事务B之间具有隔离性
		持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

关于事务的一些术语
	开启事务：Start Transaction
	事务结束：End Transaction
	提交事务：Commit Transaction
	回滚事务：Rollback Transaction

和事务相关的两条重要的SQL语句(TCL)
	commit:提交
	rollback：回滚

六、事务开启的标志、事务结束的标志
	开启标志：

-任何一条DML语句(insert、update、delete)执行，标志事务的开启

	结束标志(提交或者回滚)：

提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步

	回滚：失败的结束，将所有的DML语句操作历史记录全部清空

8.表（引擎）：背

1.mysiam
	1.成熟、稳定、易于管理
	2.支持表级锁，优化高并发
	3.optimize table:处理碎片，该属性可以优化空间
	4.缺点：不支持事务处理，行级锁
	5.空间占有量相对小
	6.支持全文索引
	7.不支持外键约束


2.innodb(创建表默认类型)
	1.myisam的更新产品
	2.支持事务，回滚等功能
	3.与myisam不同的是，他属于行级锁
	4.空间占用量太大(大约是myisam两倍)，操作慢，不支持索引
	5.不支持全文索引


3.设定表类型,字符集
	create table 表（字段 类型 是非空 自增长 primary key,字段1，类型1 是非空 默认值，..)engine=myisam default character set gbk collate gbk_Chinese_ci;

	create table A(zd1 int auto_increment primary key,zd2 varchar(50) not null defalut 0) default character set gbk collate gbk_chinese_ci;	#自增长主键必须是int类型

退出数据库:exit
增加用户:grant select on db.* to admin@localhost identified by ‘123’;

7.命令（结束加分号）：

1.对数据库：
	1.启动数据库：

	2.关闭数据库：

	3.返回  ：\c	
	
	4.登录数据库	：mysql -h localhost -u root -p 或 mysql -h localhost -u root -proot

   		5.退出数据库	：exit  quit  Ctrl+c(偶尔不好使)

	6.查看当前数据库服务中有哪些库	：show databases;	(databases:数据库，复数)

	7.创建库	：create database 库名

	8.选择库（进入库）	：use 库名

	9.删除库	：drop database 库名


	
2.对表：
	1.查看当前库下所有表	：show tables;

	2.创建表	：create table 表（字段 类型 是非空 自增长 primary key,字段1...，..)engine=表类型（myisam,innodb） default character set 字符集(gbk) collate （校对规则)gbk_Chinese_ci;
			create table 库名.表名（字段 数据类型【长度】 字段属性，字段 数据类型【长度】 字段属性，...,primary key(主键字段))
			如:not null auto_increment primary key,字段名1 数据类型，字段2 数据类型）；
			（auto_increment自增长只能在int中用）

	3.查看表信息	：select * from 表名；

	4.查看表结构	：desc 表名   （default:默认值）

	5.查看表的创建（详细）信息	：show create table 表名;

	6.修改表名	：alter table 旧表名 rename 新表名;

	7.删除表		：drop table 表名



9.对数据(insert into..., update..set..，delete..from...)：
	1.(增)插入字段数据
		insert 表（字段名） values(内容)

		insert into 表(字段1，字段2，...) values('..',)

		insert into 表 values(字段1的值，......)  #这种插入方法	必须将表中所有字段全部插入	按照从左到右依次填写	尽量不要用强制转换机制

		insert into 表 values(null,....)	  #主键若为自增长 主键值写null,null为默认值，虽not null，但自增长优先级高，主键值会自动插入

		insert into 表 set 字段1 = '值1',字段2 = ‘值2’，...;（相对不好用）


	2.(改)修改表数据
		update 表 set 字段 = 值，字段 = 值 where 主键 = 值；   （where后跟的是：条件，不写where整个列都修改）


	3.(删)删除表数据
		truncate (table) tb#删除表中所有数据，不能与where一起使用，truncate为DDL语句,隐式提交,不可rollback
		delete from db where 主键='zhi'		#删一个,delete可以rollback
		delete from db where 主键 in('zhi1','zhi2')	#多个
	区别：truncate、delete:
		1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
          			原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
     		2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引    
     		3、 truncate 不能触发任何Delete触发器。
     		4、delete 删除可以返回行数





10.对字段(alter...add/change/drop)：
	1.(增)添加字段,设置字段位置(add):
		alter table 表 add 字段 类型

	  	alter table 表 add 添加字段 类型 是否空 默认 after/first 已有字段



	2.(改)修改字段(change)：
		alter table 表 change 旧字段名 新字段名 新数据类型 ... (如：alter table student change weight weight int not null;)



	3.(删)删除字段(drop)：
		alter table 表 drop 字段

查询语句（所有查询语句不会造成影响行数）

简单查询：（select ... from ...）

	select 字段1,字段2.../(*) from 表  （速度比*快，是一种优化）
	select 字段1,字段2.../(*) from 表 where id =/in()




逻辑查询：（select ... from ... where ...)

	select 字段1，字段2.../(*) from 表 where id 【=/>/</!=】 限制条件;






运算查询:(select ... from ...)加减乘除，取模（取余）

	select 字段1,字段2.../(*) from 表 where id = 1+1;






模糊查询(关键字,_,%)：(select ... from ... where ... like ...)

	select * from 表 where 字段 like 值
	select * from 表 where 字段 like 'Mar_';   (匹配Mary,一个_只能匹配一个任意字符)
	select * from 表 where 字段 like ‘%a%"； （匹配Mary,一个%匹配若干个任意字符）
	show character set "%'	：查看校对规则




排序查询order by：(不能使用where，也不能接条件)

	？？？？select ... from ... where ... order by 字段
	select * from 表 order by 字段 asc(顺)/desc(逆);	 排序语句不能使用where



受限查询：limit 0,3（分页）

	select * from 表 limit 5,3   (从0开始数，第5条开始取3个)
	select * from 表 limit 0,2



隐藏数据：

	alter table 表 add `delete` enum('1','0') not null default '1';
	update 表 set delete='0' where 主键 in (1,3,5,7,9);
	select * from 表 where `delete` = '1';	



聚合查询：

	select count(id/*) from biao;   (计算有几个id)
	select sum/max/min/avg(age) from 表   (运算)



区间查询：betwwen

	select * from 表 where id between 2 and 5；   (2到5之间，包括2,5)





分组查询：group by（不能使用where）  having+条件（只能和group by 连用）

	create table ks(id int not null auto_increment primary key,username varchar(66) not null default '',content text not null)	创建

	insert into ks values(null,'Eric','asdfgh');  #插入
	
	select username,count(username) from ks group by username;     #group by用username分组 




嵌套查询：（分组，排序，查询）

	select username,count(username) from ks group by username order by count(username) desc limit 0,3;	#先分组，再排序，最后受限
	select username,count(*) from ks group by username having count(*) >=5;
	select username,count(*) from ks group by username having count(*) >=5 order by count(*) desc;



多表查询：

	select students.name,scores.* from students,scores where students.id=scores.pid;




联合查询（比多表快7倍）

	select students.name,scores.* from students join scores on students.id = scores.pid && students.name='zhanglin';




左右表查询：

	左键查询：select students.name,scores.* from students left join scores on students.id=scores.pid;  
			(一切以左表students为主：左表students对应的所有行都会显示，如scores无对应行也会用空补齐，scores若有多余行则不会显示)

	右键查询：select students.name,scores.* from students right join scores on students.id=scores.pid;
			(一切以右表scores为主：右表scores对应的所有行都会显示，如students无对应行也会用空补齐，students若有多余行则不会显示)

外键（innodb支持外键）
主表（innodb） 从表(innodb)（先填主表，再填从表）

create table score(....,foreign key(pid) references students(id))   主表主键

sql注入：条件等于真,会把所有信息拿出来

笛卡尔乘积：

索引（前三要记）：提高检索速度

1.主键索引：

2.外键索引：foreign key 外键 references 主表（主键）

3.唯一索引:只有确认某一列不能出现重复的值是才能使用
	设定唯一索引：usename varchar(66) not null default '' unique

4.普通索引：只是提高查询速度
	创建索引：create index 索引名 on 表(字段);
	查看索引：show index from biao
	删除索引：drop index 索引名 on biao

数据库事务（回滚只适用innodb类型）：

概念：将多个sql语句要完成的任务看成是一个任务

提交：
	开启手动提交，关闭自动提交（每次登陆都要重新设置）
		set autocommit=0;

	提交
		commit；

	回滚（只对innodb类型表的数据有效，对结构（库，表等）无效）
		rollback（回滚到上一次提交的时间）；

用户管理：

root:	库user，库mysql是核心库，千万不要动

查询用户信息：
	use mysql
	select user,host,password from user;

mysql加密PASSWORD(64位)：select PASSWORD('123');

创建用户：create user "用户名'@'localhost' identified by '密码'

登陆用户：mysql -h localhost -u admin -p

赋予添加，修改，删除，选择权限:
	1.*.* 	
	2.库.*  
	3.库.表
				1.use mysql
				2.grant insert,update,delete,select on python0806.* to 'admin'@'lacalhost';
收回权限：
	revoke delete,insert,update on python0806.* from 'admin'@'localhost';

修改密码
	查看用户信息：select user,host,password from user;
	update user set password=PASSWORD('新密码') where user='admin' && host='localhost';
	重启配置（电脑-计算机管理-服务-mysql-重启动）
删除用户：
	use mysql
	show tables;

	drop user 'admin'@'localhost';(推荐用)
	重启

	delete from user where user='admin' && host='localhost';(不建议使用delete，删除不干净，删除后创建同名用户时会报错，用户已存在)
	重启mysql

视图（虚拟的表：不占用物理空间）：可插入数据，基表和视图的数据互相影响,联合视图改不了视图
创建视图：create…as…

	create view 视图名 as select * from ks;
	create view muth_view as select student.name,scores.linux,scores.mysql from students join scores on students.id=scores.pid;



查看视图创建信息：

	show create view ks_view;


查看视图：

	select * from ks_view；


查看创建视图信息：

	show create view muth_view;


删除视图：

	drop view muth_view

备份，恢复
使用bin的mysqldump:

备份：
	mysqldump -u root -p practice >D:/practice.sql    (备份数据库practice)

恢复：  
	create table practice;		(先创建一个空库)
	mysql -u root -p practice <D:/practice.sql	(恢复备份的数据库practice)

连接数据库
import pymysql
database = pymysql.connect(‘localhost’,‘root’,‘root’,‘practice’,3309) #链接数据库：主机 用户 用户密码 库名 端口号（默认端口号是3306.可在初始化文件查看）
cur = database.cursor() #创建游标对象
sql = 'select * from students’ #编辑sql语句，表名，字段名都用反引，值用引号
num = cur.execute(sql) 执行
print(cur.fetchall()) 显示结果
database.commit() 提交数据库

小注释：
(关于输错命令要取消返回的解释：http://www.anbob.com/archives/579.html）
(翻译：2 rows affected:影响行数为2（表示执行了两行）)
(注意：数据库中库，表，数据，万不得已不要删(drop delete updatas慎用(用的话一定加上where语句，批量修改也不能恢复))