Mysql

一、用户相关

1.创建用户

1. 创建用户
create user  'lqw'@'ip地址'  identified by '密码'
ex:
     create user 'lqw'@'%'  identified by '123456'
     create user 'lqw'@'127.0.%'  identified by '123456'
2. 删除用户
drop  user  'lqw'@'ip地址'；
3.修改用户
rename user 'lqw'@'ip地址' to 'lqw1'@'ip地址'；
4. 修改密码
set password 'lqw'@'ip地址' = Password('new_password')

2.权限

1. 赋予权限
权限  人
grant select，insert，update on table.* to 'lqw'@'*';
grant all privileges on table.* to 'lqw'@'*';
2. 取消权限
revoke 权限  on table.*  from 'lqw'@'*';
ex:
    revoke all privileges on table.* from  from 'lqw'@'*';

二、数据库的操作

1. 库的操作

1. 创建
create database test;
create database db default charset utf8; 编码默认为utf8
2. 查看数据库
show databases;
3. 删除
drop database ‘name’;
4. 使用
use database 
5. 查看当前所在表
select database();


6.数据转存 mysqldump -u 密码 db1 > test.sql -p; # 将db1 的数据保存至test.sql，包含数据和表结构，加 -d 参数，只包含表结构。

数据导入 mysqldump -u 账号 db < db1.sql -p;

2.表的操作

1. 查看表
show tables;
desc table; # 查看表结构
2.创建表
create table t1(id int not null auto_increment primary key,name char(64) default '111') engine = innodb default charset=utf8; id 不能为空，自增，主键。name默认'111',为默认utf8,引擎为innodb;

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

3. 插入表
insert into t1(id,name) values(1,'123')
4. 查看数据
select * from table;

show create table class \G # 查看表结构。
5. 清空表
delete from table where id > 5; # 把内容删掉，自增会在原来的基础上
truncate table t1; # 也会把表清空，自增在

6.删除表
drop table t1;

7 修改：
update 表明 set name = 10 where id > 10;

alter table t1 auto_increment = 100 # 起始自增点。
alter table 表名 add primary key (id); # 添加主键
alter table 表名 change id id int not null auto_increment;# 设置正自增。
tips:
auto_increment 自增，一个表里面只能有一个自增，必须为key
primary key   不能重复，不能为空，加速查找。只能有一个主键。

3.字段类型

1.数字类型：表示的范围  是否有符号 unsigned
tinyint   
int
bigint
小数
double
float # float 和double 表示不精准；float短的时候就不精准。double 长的时候不精准
decimal  #精准，底层是字符串存的, num decimal(总位数,小数点后最多有多少)

2.字符串
 char(10)   #会帮你填写空的，速度快，
 varchar(10) # 最多帮占4个,节省空间，速度比较慢
 优化点：定长的往前放，最高255个字符
 text 65535
****** tips :图片、文件、视频,存放文件所在的路径。
3. 时间
datetime   yyyy-mm-dd HH:MM:SS

4. 二进制
TinyBlob
Blob
MediumBlob
LongBlob 

5. 枚举类型
size ENMU('男','女','不详')

6.集合
craete table t(hobby SET('a','b'))
insert into t(hobby) values(('a,b'))

4.外键

1. 节省空间
2. 和其他表有关联

**要想外键，数据库引擎必须为innodb,外键字段必须唯一索引。可以是多个**

craete table sex(
id int auto_increment primary key,
type char(64),
) engine = innodb default charset=utf8;

第一种：
create table user_info(id 
id int auto_increment primary key,                 name char(64),
sex_id int,
foreign (sex_id) references sex(id),              
) engine  = innodb default charset=utf8;

第二种：
create table user_info(id 
id int auto_increment,                 name char(64),
sex_id int,
primary key(id)  # 支持多列主键。
foreign (sex_id) references sex(id),              
) engine  = innodb default charset=utf8;

5. 自增步长

1. 基于会话的自增，
show session variables like 'auto_increment';
set session auto_increment_increment = 5 # 在会话中可以

2. 基于全局级别的
每次打开会话，都是全局步长的
set global session auto_increment_increment = 5

6.索引相关

0. 唯一索引
create table t1(
	id int,
    iden unique,
    unique (ip,port)。
) # 唯一 加速查找，可以为空，主键不能重复，不能为空。

三、表库操作

增:
insert into 表名(字段) values ('xxx'),('yyyy');

insert into 表名() select name,age from 表名;

删:
delete from 表名 where id ！= 5 or id = 5;

改:
update 表名 set name='xxx' where id is not null;

查:
select id as 别名,name from 表名 where id not between 1 and 2;
select id,name,11 from 表名;
select * from table where id <> 1;

四、查询

1.范围

select * from table where id not in (1，2，3);
select * from table where id not between 1 and 6;
select * from table where id  not null ;
select * from table where id in (select id from 表;)

tips:
    三元表达式
    case when 条件 then a else b 满组条件 就是 A  否则 就是 b
    if(exp1,exp2,exp3); 如果exp1 为Ture,则为 exp2 否则为exp3;

2.通配符

select * from table where name like '%a%' ;# 包含a的

select * from table where name like '____'; # 四个字符的

3.分组

select dep_id,count(age) from table where id <=5 group by dep_id having max(age)>18;
常见聚合函数：
max，min,count,sum,avg;
分组 可以取得值，分组依据，聚合函数。
having 要用在group之后。

默认分为1组
select count(id) from 表名。

4.连表

1. 笛卡尔积
select * from table，table2;
2.left join
左边都显示
3. right join 
右边都显示
4. inner join
存在的都显示 

5. 上下连表 union，将上下两表连接在一起
select name from table1 
union
select age from table2;  # 列数要相同。会去重

select name from table1
union all 
select name from table1;#拼在一起不会去重。

5.去重

select distinct name from table   # 
# 注意点：
id 不能添加进入。
# 也可以通过group by 来去重

6.循环查询

# 循环查询

SELECT DISTINCT out_table.student_id,
(SELECT num from score as inner_table WHERE inner_table.student_id = out_table.student_id AND inner_table.course_id = 1) as 语文,
(SELECT num from score as inner_table WHERE inner_table.student_id = out_table.student_id AND inner_table.course_id = 2) as
英语,
(SELECT num from score as inner_table WHERE inner_table.student_id = out_table.student_id AND inner_table.course_id = 3) as
数学,
(SELECT count(1) from score as inner_table WHERE inner_table.student_id = out_table.student_id and inner_table.course_id in (1,2,3) ),
(SELECT avg(num) from score as inner_table WHERE inner_table.student_id = out_table.student_id and inner_table.course_id in (1,2,3))
 from score as out_table;
 
 
 select id from table; #本质就是循环,id 就是table.id
sqlalchemy:
res = session.query(UserTypes,session.query(Users.id).filter(Users.id ==UserTypes.id).as_scalar()).all()

7.三元表达式

case when 1>2 then 2 else 1 end;
if(1 >2,2,1)

五 sql其他相关

1.视图

假设100 sql,都需要用到一个复查的表,可以用视图;
创建视图
create view test_view as sql语句;
删除视图
drop view 视图名称
修改视图
alter view 名字 as  set  sql语句
tips:
视图是虚拟表，不要对其增删改查，否则会有问题;

2.触发器

在对表操作时候增删改查,可以关联自定义行为；
delimiter //
create trigger t1 before insert on 表名 for each row
begin
insert into 表名  New（表示新增的数据）,old表示旧的数据;
end //
delimiter ;

3.函数

时间函数,将时间转成年月。去官网看
SELECT DATE_FORMAT(date,'%y年%m月'),count(1),group_concat(date_test.id) as 文章  FROM date_test GROUP BY DATE_FORMAT(date,'%y年%m月');  时间为'2021-01-04 15:15:15'
按照年月分组。

delimiter //
create function f1(
i1 int,
i2 int)
returns int
Begin
declare num int default 0;
set num = i1+i2;
return(num);
end //
delimiter ;

4.存储过程

4.1 一般版本

保存在mysql的一个别名，一坨代码，想干嘛就干嘛。
别名()，用于替代程序员写sql语句。直接调用就行了。
delimiter //
create procedure p1()
Begin
select * from student;
END //
delimiter ; 

pymsql中调用存储过程  res = cursor.callproc('p1')

4.2参数版本

# 传参数  in out  inout 三种。
delimiter //
create procedure p2(
in n1 int,
out n2 int
)
Begin
select * from test where id > n1;
set n2 = 999
END //
delimiter ; 
'''
pymysql 获取存储过程结果
res = cursor.callproc('p2',args=(2,45))
print(cursor.fetchall())   #先获取结果
cursor.execute('select @_p2_0,@_p2_1')
print(cursor.fetchall()) 在获取 out
'''
状态表示可以存储过程执行的状态。

4.3 事务版本

delimiter //
create procedure p7(
out status int
)
BEGIN
    DECLARE EXIT HANDLER for SQLEXCEPTION  --有问题执行这个 
    BEGIN
        set status = 1;
        ROLLBACK;
    END;
    
    START TRANSACTION;  --开始事务
     		SELECT * FROM test1;
    COMMIT;
    set status = 2;
END //
delimiter ;

4.4 循环

//创建存储过程前先检查是否存在，存在就删除，这个可以忽略
DROP PROCEDURE IF EXISTS staff_zt_test;
//存储过程
CREATE PROCEDURE staff_zt_test()
BEGIN
	//该变量用于标识是否还有数据需遍历
	DECLARE flag INT DEFAULT 0;
	//创建一个变量用来存储遍历过程中的值
	DECLARE id BIGINT(40);
	//查询出需要遍历的数据集合
	DECLARE idList CURSOR FOR (SELECT id FROM table WHERE a = 1);
	//查询是否有下一个数据，没有将标识设为1，相当于hasNext
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
	//打开游标
	OPEN idList;
		//取值设置到临时变量中
		FETCH idList INTO id;
		//遍历未结束就一直执行
		WHILE flag != 1 DO
				-- targetSQL //你想要执行的目标功能，这里可以写多个SQL
				
				//  注意
				//这里有一个坑，目标语句引用临时变量，实测发现不需要加@符号，但是搜索到的结果都是例如：@id ，这样来使用，实测发现无法取到数据
				//  注意

				update XXX from table2 where key_id = id;
				//一定要记得把游标向后移一位，这个坑我替各位踩过了，不需要再踩了
				FETCH idList INTO id;
		END WHILE;
	CLOSE idList;
END;

4.5 动态执行sql 防止sql注入

delimiter //
CREATE PROCEDURE test_sql(
in id int
)
Begin 
	set @nid = nid；
	Prepare prod from 'sql语句’;
	Execute prod using @nid;
	Deallocate prepare prod;
End //
delimiter ;

六、索引

1.索引的意义

1.对列进行约束
2.对列进行创建‘字典’，加速查找
如 unique 对字段约束成唯一，primiary key 非空且唯一

2.mysql索引的类别

1. hash索引,类似于字典，会维护一个hash表，根据hash值去查找，单行查找很快，范围查找就很慢;
2. btree索引，维护成一个树，对于范围查找比较快;

聚簇索引:聚簇索引只可能是主键，或者所有组成唯一键的所有列都为NOT NULL的第一个唯一索引，或者隐式创建的聚簇索引这三种情况;
非聚簇索引:又叫辅助索引,辅助索引,每个辅助索引的每条记录都包含主键，也包含非聚簇索引指定的列,辅助索引应该短一点，在查询普通索引时，会有回表这个操作，如下图

3.索引的创建

create index '索引名' on 表(字段)
drop index '索引名' on 表

4.索引的名词

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'dada';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

七、额外

1.注意事项

避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

2.分页

select * from  tb1  where  nid < 1000 order by nid desc limit 10;
select * from  tb1  where  nid > 1000 limit 10;

3.执行计划explain

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
            |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |

select_type
        查询类型
            SIMPLE          简单查询
            PRIMARY         最外层查询
            SUBQUERY        映射为子查询
            DERIVED         子查询
            UNION           联合
            UNION RESULT    使用联合的结果
            ...
    table
        正在访问的表名


    type
        查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
            ALL             全表扫描，对于数据表从头到尾找一遍
                            select * from tb1;
                            特别的：如果有limit限制，则找到之后就不在继续向下扫描
                                   select * from tb1 where email = 'seven@live.com'
                                   select * from tb1 where email = 'seven@live.com' limit 1;
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

            INDEX           全索引扫描，对索引从头到尾找一遍
                            select nid from tb1;

            RANGE          对索引列进行范围查找
                            select *  from tb1 where name < 'alex';
                            PS:
                                between and
                                in
                                >   >=  <   <=  操作
                                注意：!= 和 > 符号


            INDEX_MERGE     合并索引，使用多个单列索引搜索
                            select *  from tb1 where name = 'alex' or nid in (11,22,33);

            REF             根据索引查找一个或多个值
                            select *  from tb1 where name = 'seven';

            EQ_REF          连接时使用primary key 或 unique类型
                            select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;


            CONST           常量
                            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                            select nid from tb1 where nid = 2 ;

            SYSTEM          系统
                            表仅有一行(=系统表)。这是const联接类型的一个特例。
                            select * from (select nid from tb1 where nid = 1) as A;
    possible_keys
        可能使用的索引

    key
        真实使用的

    key_len
        MySQL中使用索引字节长度

    rows
        mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

    extra
        该列包含MySQL解决查询的详细信息
        “Using index”
            此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
        “Using where”
            这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
        “Using temporary”
            这意味着mysql在对查询结果排序时会使用一个临时表。
        “Using filesort”
            这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
        “Range checked for each record(index map: N)”
            这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

4.慢日志查询

slow_query_log = OFF                       是否开启慢日志记录
long_query_time = 2                        时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log        日志文件
log_queries_not_using_indexes = OFF        未使用索引的搜索是否记录

注：查看当前配置信息：
　　     show variables like '%query%'
     修改当前配置：
　　　　set global 变量名 = 值；

5.ondelete

set null 置为空，要可以为空
cascade  删除
no action restrict  有的话不允许删除

posted @ 2022-02-09 19:19 我也想18岁阅读(59) 评论(0) 编辑收藏举报

刷新页面返回顶部

Mysql

Mysql

一 、用户相关

1.创建用户

2.权限

二 、 数据库的操作

1. 库的操作

2.表的操作

3.字段类型

4.外键

5. 自增步长

6.索引相关

三 、表库操作

四、查询

1.范围

2.通配符

3.分组

4.连表

5.去重

6.循环查询

7.三元表达式

五 sql其他相关

1.视图

2.触发器

3.函数

4.存储过程

4.1 一般版本

4.2参数版本

4.3 事务版本

4.5 动态执行sql 防止sql注入

六 、索引

1.索引的意义

2.mysql索引的类别

3.索引的创建

4.索引的名词

七 、额外

1.注意事项

2.分页

3.执行计划explain

4.慢日志查询

5.ondelete

一、用户相关

二、数据库的操作

三、表库操作

六、索引

七、额外