MySQL基础知识

初识MySQL语句

#进入mysql客户端
$mysql
mysql> select user(); 		 # 查看当前用户
mysql> exit   			     # 也可以用\q quit退出

# 默认密码
grep 'temporary password' /var/log/mysqld.log

# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
$ mysql -uroot -p  			 # mysql5.6默认是没有密码的
#遇到password直接按回车键
mysql> set password = password('root');	 # 给当前数据库设置密码

# 创建账号
mysql> create user 'yang'@'192.168.10.%'   IDENTIFIED BY '123';# 指示网段
mysql> create user 'yang'@'192.168.10.5'  		 # 指示某机器可以连接
mysql> create user 'yang'@'%'                    # 指示所有机器都可以连接  
mysql> show grants for 'yang'@'192.168.10.5';	 # 查看某个用户的权限 
# 远程登陆
$ mysql -uroot -p123 -h 192.168.10.3

# 给账号授权
mysql> grant all on *.* to 'yang'@'%';
mysql> flush privileges;    # 刷新使授权立即生效

# 创建账号并授权
mysql> grant all on *.* to 'yang'@'%' identified by '123' 

# 取消账号权限
mysql> revoke all on 数据库名 from 'yang'@"%";  
mysql> revoke all on 数据库名.表名 from 'yang'@"%";  

创建数据库:

create database 数据库名 default charset utf8;  # 指定字符编码

创建数据表

create table t1(id int auto_increment,name char(10))engine=innodb default charset=utf8;

# char 定长 效率高  varchar 非定长 效率低
#　innodb 支持事务＼表锁＼行锁　　myisam 不支持事务,表锁
# auto_increment=1 表示：自增 从1开始
# primary key：  主键 约束(不能重复且不能为空) 加速查找    一个表只能有一个主键	主键可以由多列组成

# not null: 是否为空 

单表操作 - - 修改表结构

语法：
1. 修改表名
      ALTER TABLE 表名 RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
			ADD 字段名  数据类型 [完整性约束条件…],
 			ADD 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      ALTER TABLE 表名 
            DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
 			 MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
 			CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
 			CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    ALTER TABLE 表名
 			ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
 			ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    ALTER TABLE 表名
 			CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
 			MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
 			

单表操作 - - 查

单表查询的语法
SELECT 字段1,字段2... FROM 表名
      WHERE 条件
      GROUP BY field
      HAVING 筛选
      ORDER BY field
      LIMIT 限制条数

***** 重点中的重点：关键字的执行优先级 *****
	from  		找表
	where		条件
	group by    分组
	having		对分组进行条件筛选
	select		查的内容
	distinct    去重
	order by	排序(aesc升序/desc降序)
	limit		限制结果的显示条数

单表操作 - - 增

1. 插入完整数据（顺序插入）
    语法一：
    	INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    	INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

单表操作 - - 更新

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) where user=’root’ and host=’localhost’;

单表操作 - - 删除

语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user WHERE password=’’;

• 引擎

Innodb 
	- 支持事务
	- 支持表锁  行锁
	- 格式示例：
		- win终端：
			begin;
			select * from t1 for update; #表锁
			select * from t1 where id=2 for update; #行锁 锁住了id=2的行
			commit;
			
		- pymysql
			cursor.excute('select * from t1 for update')
			
		- django
			with trancation.automic():
			models.User.object.all().for_update()

Mysaim
	- 不支持事务
	- 支持表锁

• 数据库优化方案

1 避免使用 select *
2 固定长度往前放
3 避免使用 联表 
4 读写分离
5 分库		
6 分表
	- 水平分
	- 垂直分

• 索引的作用：

加速查找 + 约束
	- 主键索引：加速查找、不重复、非空
	- 唯一索引：加速查找、不重复			
	- 普通索引：加速查找
	- 联合索引：加速查找
	- 联合唯一索引（组合索引）：加速查找、不重复、
		ps:联合索引&联合唯一索引 遵循了最左前缀原则
	
	id     name    age      email
	
	select * from t1 where id=1;
	select * from t1 where id=1 and name = zs;

• 索引名词:

- 覆盖索引: 在索引数据结构中,能把数据直接查到,无需去物理表中查询
- 索引合并: 如果用多个单列索引查询就是索引合并

---

• 视图

- 创建视图

create [or replace] view 视图名[(column_list)] as 查询语句
# or replace：表示如果已经存在相同名字的视图，则替换原有视图
# column_list：指定哪些字段要出现在视图中
# 视图是一张虚拟表，所以在视图中的数据不会出现在硬盘上

- 查看视图

desc view_name;  # 查看视图的数据结构
show create view view_name [\G];  # 查看创建视图语句
select * from view_name;  # 查看视图内的数据，同查表数据一样

- 修改视图

alter view view_name as 查询语句;  

- 删除视图

drop view view_name;

示例:

# 数据准备
create database db02 charset utf8;
use db02
create table student(
  s_id int(3),
  name varchar(20),
  math float,
  chinese float 
);
insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);

create table stu_info(
  s_id int(3),
  class varchar(50),
  addr varchar(100)
);
insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');

# 创建视图包含编号、学生的姓名和班级
create view stu_v(编号,姓名,班级) as select student.s_id,name,class from student join stu_info on student.s_id = stu_info.s_id;

# 修改视图
alter view stu_v(编号,姓名) as select student.s_id,name from student join stu_info on student.s_id = stu_info.s_id;

# 删除视图
drop view stu_v;

*********注意：对视图数据的insert update delete会同步到原表中，但由于视图可能是部分字段，很多时候会失败*********

• 触发器

• 作用:有点像python的装饰器,在执行代码前做一些别的操作

- 创建触发器

create trigger triggerName  
after/before insert/update/delete on 表名  
for each row   #这句话在mysql是固定的  
begin  
    sql语句;  
end;  

****************** insert *********************
# 在学生表中插入学生时,会自动在老师表中插入相应的数据
insert into tb (....)

-- delimiter //
-- create trigger t1 BEFORE INSERT on student for EACH ROW
-- BEGIN
-- 	INSERT into teacher(tname) values(NEW.sname);
-- END //
-- delimiter ;
-- 

-- insert into student(gender,class_id,sname) values('女',1,'CR2'),('女',1,'CR');

• 函数

- 创建函数

delimiter \\  # 定义结束符
	create function f1(
		i1 int,  # 要指明数据类型
		i2 int)
	returns int
	BEGIN
		declare num int default 0; #声明 num 的默认值为 0
		set num = i1 + i2;
		return(num);
	END \\
delimiter ;

- 调用函数
SELECT f1(1,100);

• 存储过程

-存储过程本身并没有返回值 ,通过out参数伪造返回值

- 简单存储过程 不带参数

# 创建
delimiter //
create procedure p1() # 固定格式 p2 为 过程名
BEGIN	
	select * from student where sid > n1;
END //
delimiter ;

call p1()  # mysql 调用
cursor.callproc('p1')  # pymysql 调用

- 带参数 ( in ) - - 存储过程

delimiter //
create procedure p2(
	in n1 int, # in 参数 指定类型     (  分为三类  in   out   inout  )
	in n2 int
)
BEGIN	
	select * from student where sid > n1;
END //
delimiter ;

call p2(12,2)  # mysql 调用
cursor.callproc('p2',(12,2))   # pymysql 调用

- 带参数 ( out ) - - 存储过程
 
delimiter //
create procedure p3(
	in n1 int,
	inout n2 int
)
BEGIN
	set n2 = 123123;  # 在内部进行对 n2 进行赋值
	select * from student where sid > n1;
END //
delimiter ;

set @v1 = 10; 	 #  固定格式  声明 @v1 = 10
call p2(12,@v1)  # 调用 p2
select @v1; 	 # 获取 v1 的返回值  为 123123　

set @_p3_0 = 12  # 声明变量
set @_p3_1 = 5
call p3(@_p3_0,@_p3_1)  # 调用
select @_p3_0,@_p3_1    # 获取 传入的两个值的结果

#　pymysql 调用
cursor.callproc('p3',(12,5))  #　位置参数 (12, 5)
r1 = cursor.fetchall() 
print(r1)

cursor.execute('select @_p3_0,@_p3_1')  # 固定格式 @_过程名_位置参数
r2 = cursor.fetchall()
print(r2)

- 存储过程 - - 事务

******************** 伪代码 *******************			
delimiter //
create procedure p4(
	out status int
)
BEGIN
	1. 声明如果出现异常则执行{
		set status = 1;
		rollback;
	}
	   
	开始事务
		-- A户减去100
		-- B 账户加90
		-- C 账户加10
		commit;
	结束
	
	set status = 2;
	
	
END //
delimiter ;

============= 代码 ==================

delimiter \\
create PROCEDURE p5(
	OUT p_return_code int
)
BEGIN 
  DECLARE exit handler for sqlexception  #  声明
  BEGIN 
	-- ERROR 
	set p_return_code = 1; 
	rollback; 			 # 回滚
  END; 
 
  START TRANSACTION; 
	DELETE from tb1;
	insert into tb2(name)values('CR');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 2; 
 
  END\\
delimiter ;

- 存储过程 - - 游标

delimiter //
create procedure p6()
begin 
	declare row_id int;	 # 自定义变量1  
	declare row_num int; # 自定义变量2 
	declare done INT DEFAULT FALSE;   #  自定义变量 done 为 False 
	declare temp int;  	 # 自定义变量temp 
	
	declare my_cursor CURSOR FOR select id,num from A;  
	declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	
	open my_cursor;
		xxoo: LOOP   	#　xxoo 自定义
			fetch my_cursor into row_id,row_num;
			if done then  
				leave xxoo;  #　如果　done 为Flase 退出
			END IF;
			set temp = row_id + row_num;
			insert into B(number) values(temp);
		end loop xxoo;
	close my_cursor;
	
	
end  //
delimter ;

call p6()

存储过程 ( 防SQL注入 ) - - 动态执行SQL

******************** 伪代码 *******************
delimiter //
create procedure p7(
	in tpl varchar(255),
	in arg int
)
begin 
	1. 预检测某个东西 SQL语句合法性
	2. SQL =格式化 tpl + arg 
	3. 执行SQL语句
	
	set @xo = arg;
	PREPARE xxx FROM 'select * from student where sid > ?';
	EXECUTE xxx USING @xo;
	DEALLOCATE prepare prod; 
end  //
delimter ;



call p7("select * from tb where id > ?",9)

============= 代码 ==================

delimiter \\
CREATE PROCEDURE p8 (
	in nid int
)
BEGIN
	set @nid = nid;
	PREPARE prod FROM 'select * from student where sid > ?';
	EXECUTE prod USING @nid;
	DEALLOCATE prepare prod; 
END\\
delimiter ;