MySQL基础总结


阅读数: 次   

MySQL

连接

默认：用户root

    show databases;
    
    use 数据库名称;
    
    show tables;
    
    select * from 表名;
    
    select name,age,id from 表名;
    
    mysql数据库user表
    use mysql;
    select user,host from user;

创建用户：

          create user 'username'@'192.168.1.1' identified by '123123';
          create user 'username'@'192.168.1.%' identified by '123123';
          create user 'username'@'%' identified by '123123';

授权

          grant select,insert,update  on db1.t1 to 'username'@'%';
          grant all privileges  on db1.t1 to 'username'@'%';
          
          revoke all privileges on db1.t1 from 'username'@'%';
          
    DBA: 用户名密码

创建表：

auto_increment是自增 

        show tables;  
        create tables t1(
        id  int not null auto_increment primary,
        name char(10),
        grade int
        )engine=innodb default charset=utf8;

数字类型

数据类型：

数字:

 tinyint
 int
 bigint
                
 FLOAT
 0.00000100000123000123001230123
 DOUBLE
 0.00000000000000000000100000123000123001230123
 0.00000100000123000000000000000
 decimal
 0.1

字符串:

 char(10)      速度快()
 root       
 varchar(10)   节省空间
 root
 PS: 创建数据表定长列往前放
时间类型:
 DATETIME
        
 enum
 set

清空表:

 delete from t1;
 truncate table t1;

删除表:

 drop table t1;

基本增删改查

插入数据

 insert into t1(id，name) values（xx，xx）
 case when ... then

删除

 delete from 表 where 约束条件

修改

添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; – 类型
alter table 表名 change 原列名 新列名 类型; – 列名，类型

添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;

添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称

修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
在修改表时添加唯一约束的语法格式为：
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

查看数据

 select * from 表

1.对自增的补充：

MySQL：自增步长

基于对话：对于设置的初始步长和初始值只能对一个对话起作用（一个mysql窗口）

基本操作

show session variables like ‘auto_inc%’; 查看全局变量
set session auto_increment_increment=2; 设置会话步长
set session auto_increment_offset=10; 下一个自增的数值

基于全局级别（不推荐使用）：

show global variables like ‘auto_inc%’; 查看全局变量
set global auto_increment_increment=2; 设置会话步长
set global auto_increment_offset=10;

唯一索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
    create table t1(  
                    id int ....,  
                    num int,  
                    xx int,  
                    unique 唯一索引名称 (列名,列名),  
                    constraint ....  
                )  
                唯一：
                        约束不能重复（可以为空）
                        PS: 主键不能重复（不能为空）
                    加速查找
##  外键的变种：
===> 一对一
			
				create table userinfo1(
					id int auto_increment primary key,
					name char(10),
					gender char(10),
					email varchar(64)
				)engine=innodb default charset=utf8;

				create table admin(
					id int not null auto_increment primary key,
					username varchar(64) not null,
					password VARCHAR(64) not null,
					user_id int not null,
					unique uq_u1 (user_id),
					CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES          
                    userinfo1(id)
				)engine=innodb default charset=utf8;
===》多对多
	
				create table userinfo2(
					id int auto_increment primary key,
					name char(10),
					gender char(10),
					email varchar(64)
				)engine=innodb default charset=utf8;

				create table host(
					id int auto_increment primary key,
					hostname char(64)
				)engine=innodb default charset=utf8;


				create table user2host(
					id int auto_increment primary key,
					userid int not null,
					hostid int not null,
					unique uq_user_host (userid,hostid),
					CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES 
                    userinfo2(id),
					CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES 
                    host(id)
				)engine=innodb default charset=utf8;
# 基本操作的补充  
### 增
			insert into tb11(name,age) values('alex',12);
			
			insert into tb11(name,age) values('alex',12),('root',18);
			
			insert into tb12(name,age) select name,age from tb11;
### 删
			delete from tb12;
			delete from tb12 where id !=2 
			delete from tb12 where id =2 
			delete from tb12 where id > 2 
			delete from tb12 where id >=2 
			delete from tb12 where id >=2 or name='alex'
		
### 改
			update tb12 set name='alex' where id>12 and name='xx'
			update tb12 set name='alex',age=19 where id>12 and name='xx'
### 查
			
			select * from tb12;
			
			select id,name from tb12;
			
			select id,name from tb12 where id > 10 or name ='xxx';
			
			select id,name as cname from tb12 where id > 10 or name ='xxx';
			
			select name,age,11 from tb12;
			
### 其他：
				select * from tb12 where id != 1
				select * from tb12 where id in (1,5,12);
				select * from tb12 where id not in (1,5,12);
				select * from tb12 where id in (select id from tb11)
				select * from tb12 where id between 5 and 12;
	
			
				通配符（搜索时用，写正则~）：
				
				select * from tb12 where name like "a%"
				select * from tb12 where name like "a_"
	
			
				分页：
				
					select * from tb12 limit 10;  # 查看前十条
					
					select * from tb12 limit 0,10;          # 数据的起始位置从0开始
					select * from tb12 limit 10,10;         # 取10后面的10条
					select * from tb12 limit 20,10;         # 取20后面的10条
					# 另一种写法
					select * from tb12 limit 10 offset 20;  # 从20开始取10条

		
					# page = input('请输入要查看的页码')
					# page = int(page)
					# (page-1) * 10
					# select * from tb12 limit 0,10; 1 
					# select * from tb12 limit 10,10;2
				
				
				排序：
					select * from tb12 order by id desc; 大到小
					select * from tb12 order by id asc;  小到大
					 select * from tb12 order by age desc,id desc;
					 
					取后10条数据
					select * from tb12 order by id desc limit 10;
                分组：
                    select 筛选条件 from 表名 group by ***                    
                    函数 max min sum avg ...
                    如果对于聚合函数进行二次筛选时（在筛选结果的基础上在进行筛选），必须使用having
                    eg：select **，** from tablle_name group by ** having **>1;
                    select **，** from tablle_name where **>* group by ** having **>1;
                连表操作：
                     select * from tb1,tb2 where tb1.xx = tb2.xx;
                     select * from tb1 left join tb2 on tb1.xx = tb2.xx;
                     # tb1左边全部显示
                     select * from tb1 right join tb2 on tb1.xx = tb2.xx;
                     # tb2右边全部显示

## 备份： 数据表结构 + 数据
        mysqldump -u root db1 > db1.sql -p
## 备份： 数据表结构
        mysqldump -u root -d db1 > db1.sql -p
## 导入文件 
        create datebase db2;
        mysqldump -u root -d db2 < db1.sql -p

## 自定义函数
	delimiter //
		create function f1（   
			i1 int,  
			i2 int)  
		returns int
		BEGIN  
			declare num int;
			set num = i1 + i2;
			return(num);
		END		
	//delimiter;
	调用： select f1(1,100);

MySQL索引

1. 索引

索引是表的目录，保存在额外的空间中，加速查找。

1
create index xxx(索引名) on 表名（列名）

2.索引的种类

  • 普通索引：仅加速查询
  • 唯一索引：加速查询 + 列名唯一（可以为空）
  • 主键索引：加速查找 + 列名唯一 + 表中只有一个（不能为空）
  • 组合索引：多列值组成一个索引，专门用于组合搜索，骑效率大于索引合并
  • 全文索引：对文本的内容进行分词，进行搜索
    索引合并，使用多个单列索引组合搜索
    覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

3. 相关命令

1
2
3
4
5
6
7
8
9
10
11
12
13
- 查看表结构
    desc 表名

- 查看生成表的SQL
    show create table 表名

- 查看索引
    show index from  表名

- 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

4.正确使用索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'xxx';
- 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                -- 不使用索引

5.注意事项

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

6.limit分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
每页显示10条：
当前 118 120， 125

倒序：
            大      小
            980    970  7 6  6 5  54  43  32

21 19 98     
下一页：

    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;



    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;


上一页：

    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)  
    order by 
        nid desc 
    limit 10;


    select 
        * 
    from 
        tb1 
    where 
        nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)  
    order by 
        nid desc 
    limit 10;

7.慢日志查询

a、配置MySQL自动记录慢日志

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

b、查看MySQL慢日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
"""
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time
"""

MySQL的存储过程

s

存储过程

    保存在MySQL上的一个别名 => 一串MySQL语句
用于代替程序员写MySQL语句 

1
2
3
4
5
6
7
8
-- 创建存储过程
delimiter //
create PROCEDURE P1()
BEGIN
  select * from tb;
  insert into teacher(tname) values("XXX");
END //
delimiter ;

相当于将SQL语句封装起了个别名，这样以后用其他语言操作数据库的时候就不用写SQL语句了，将程序和SQL语句拆开了，存储过程保存在数据库中，以后如果想要修改直接在数据库中找到存储过程改一下即可，可以解放程序员

1
2
3
4
5
6
7
8
9
10
11
12
13
import pymysql
'''
python中对p1的调用
'''
conn = pymysql.connect(host='localhost',user='root',password='',database='db1',charset='utf8')
cursor = conn.cursor()
cursor.callpro('p1')
conn.commit()
res = cursor.fetchall()
print(res)
cursor.close()
conn.close()

传参数（in,out,inout）

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建存储过程
delimiter //
create PROCEDURE P2(
    in n1 int,
    in n2 int
)
BEGIN
  select * from student where sid > n1;
  insert into teacher(tname) values("XXX");
END //
delimiter
-- 在MySQL中用call p2(nuum1,num2)调用  
1
2
3
4
5
6
7
8
9
10
11
12
import pymysql
'''
python中对p1的调用
'''
conn = pymysql.connect(host='localhost',user='root',password='',database='db1',charset='utf8')
cursor = conn.cursor()
cursor.callpro('p2',(num1,num2))
conn.commit()
res = cursor.fetchall()
print(res)
cursor.close()
conn.close()

参数out

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建存储过程
delimiter //
create PROCEDURE P3(
    in n1 int,
    out n2 int   -- 如果将n2传入MySQL在MySQL中n2的值为空
)
BEGIN
  set n2 = 123131;     -- 可以在外部获取到改变后的n2值
  select * from student where sid > n1;
  insert into teacher(tname) values("XXX");
END //
delimiter
-- 在MySQL中调用
set @v1 = 0;
call p3(num,@v1)
select @v1 --v1变为n2的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import pymysql
'''
python中对p1的调用
'''
conn = pymysql.connect(host='localhost',user='root',password='',database='db1',charset='utf8')
cursor = conn.cursor()
cursor.callpro('p3',(num1,num2))
res1 = cursor.fetchall()
print(res1)
conn.commit()
cursor.execute('select @p3_0,@p3_1') # p3_0的值为num1，p3_1的值为改变后num2的值
res2 = cursor.fetchall()
print(res2)
cursor.close()
conn.close()

事务 

1
2
3
4
5
6
7
8
9
10
delimiter //
 create PROCEDURE P3(
     in n1 int,
     out n2 int   -- 如果将n2传入MySQL在MySQL中n2的值为空
 )
 BEGIN
   -- 声明如果出现异常则执行{
       set status = 1
   }
   -- 开始事务

游标 

1. 声明游标
2. 获取数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
  delimiter //
  create procedure p6()
  begin
    declare row_id int, 
    declare row_num int,
    declare done INT DEAFULT FALSE;
    decalre temp int;
    declare my_cursor CURSOR FOR select sid,sname from student;
    declare  CONTINUE HANDLE FOR NOT FOUND SET done = True; -- 声明终止条件

    open my_cursor;
        aaaa： LOOP -- 循环
            fetch my_cursor into row_id,row_num;
            if done then
                leave aaaa;
            END IF;
            set = temp = row_num + row_id;
            insert into B(num) values (temp);
        end loop aaaa;
    close my_cursor;
end   //
delimter ;

动态执行SQL（防SQL注入）

1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create procedure p7(
    in tpl varchar(255),
    in arg int
)
begin
    1. 预检测某个东西 SQL语句的合法性
    2. SQL = 格式化 tpl + arg
    3. 执行SQL语句
end  //
delimiter;

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

老板大气

支付宝
微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

很惭愧<br><br>只做了一点微小的工作<br>谢谢大家