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 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957
| mysql相关知识整理
安装,以centos7 mysql5.6.40为例 rpm安装方式 访问http://luopengtest.gotoip2.com/rpm/MySQL-5.6.40-1.el7.x86_64.rpm 下载rpm包 使用rpm -ivh MySQL-5.6.40-1.el7.x86_64.rpm 安装
创建数据库 create database testdb1 charset utf8;
删除数据库 drop database testdb1;
使用,创建用户 create user 'testu1'@'192.168.8.8' identified by 'test123'; 创建一个testu1用户,密码设置test123,授权192.168.8.8 IP可链接
create user 'testu1'@'192.168.8.' identified by 'test123'; 创建一个testu1用户,密码设置test123,授权192.168.8. IP段可链接
create user 'testu1'@'%' identified by 'test123'; 创建一个testu1用户,密码设置test123,授权任意IP可链接
删除用户 drop user 'testu1'@'%';
修改用户 rename user 'testu1'@'192.168.8.8' to 'newtestu1'@'192.168.6.6';
修改密码 set password for 'testu1'@'192.168.8.8'=Password('newpassword');
查看权限 show grants for 'testu1'@'192.168.8.8';
授权用户testu1对数据库testdb1的表t1的查询权限 grant select on testdb1.t1 to 'testu1'@'192.168.8.8';
授权用户testu1对数据库testdb1的任意表的查询、插入、更新权限 grant select,insert,update on testdb1.* to 'testu1'@'192.168.8.8';
授权用户testu1对数据库testdb1的任意表的所有权限,除grant命令,此命令仅root才有 grant all privileges on testdb1.* to 'testu1'@'192.168.8.8';
授权用户testu1对所有数据库的任意表的所有权限,除grant命令,此命令仅root才有 grant all privileges on *.* to 'testu1'@'192.168.8.8';
取消用户testu1对数据库testdb1的任意表的所有权限 revoke all on testdb1.* from 'testu1'@'192.168.8.8';
取消用户testu1对所有数据库的任意表的所有权限 revoke all on *.* from 'testu1'@'192.168.8.8';
flush privileges; 刷新权限 show databases; 查看所有数据库 use testdb1; 使用testdb1; show tables; 查看当前数据库的所有表
创建表 create table t1(id int,name char(16)) default charset='utf-8'; 语法 create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名1 类型[(宽度) 约束条件], ... );
删除表 drop table t1;
插入数据 insert into t1(id,name) values(1,'ropon'),(2,'pengge'); insert into t1 values(3,'luopeng');
删除数据 delete from t1 where id=1;
修改数据 update testdb1.t1 set name='ropon1' where id =2;
查询所有字段(慎用) select * from t1;
查询具体字段 select id,name from t1;
加条件查询 select * from testdb1.t1 where id = 2;
查看表结构 desc t1;
查看表的详细结构 show create table t1\G;
查看数据库支持引擎 show engines\G;
查看正在使用引擎 show variables like 'storage_engine%';
指定存储引擎创建表 innodb,myisam,memory,blackhole create table t2(id int,name char(10))engine=memory;
memory 内存表,重启mysql服务或重启服务器,表内容将清空 blackhole 可往表中插入任意数据,相当于丢入黑洞,表内不做记录
复制表结构和数据 create table tt1 select * from testdb1.t1;
复制表结构不要内容 create table tt2 select * from testdb1.t1 where 1>2; 其中1>2 相当于false,所以查询不到内容,故仅复制表结构
create table tt3 like testdb1.t1;
数据类型 数字 整型 tinyint smallint mediumint int bigint tinyint[(m)] [unsigned] [zerofill] 小整型 unsigned 创建定义无符号 zerofill 插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0 zerofill默认为int(10) 当使用zerofill时,默认会自动加unsigned(无符号)属性 使用unsigned属性后,数值范围是原值的2倍 有符号 -128 ~ 127 无符号 0 ~ 255 注意 mysql中无布尔值,可以使用tinyint(1)构造
int 整型 有符号 -2147483648 ~ 2147483647 无符号 0 ~ 4294967295 bigint 大整型 有符号 -9223372036854775808 ~ 9223372036854775807 无符号 0 ~ 18446744073709551615
小数 float double
推荐用decimal 精准 内部使用字符串形式存储 decimal[(m[,d])] [unsigned] [zerofill] m 是整数部分总个数(-符号不计算) 最大值65 d 是小数点后个数 最大值30
字符串 char(10) 10个字符,浪费空间,比如ropon,会存成ropon00000,存取速度快 varchar 精准,节省空间,存取速度慢 优化建议 创建表定长先定义,变长后定义 大于255字符 存文件中 数据库存路径或URL地址 create table t8(x char(5),y varchar(4)); insert into t8 values('测试一','测试二'); length() 查看字节数 char_length() 查看字符数 select x,char_length(x),y,char_length(y) from t8; 在检索或者说查询时,查出的结果会自动删除尾部的空格,除非打开pad_char_to_full_length SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; select x,length(x),y,length(y) from t8; 一个中文字符3个字节
tinytext text mediumtext longtext
时间类型 year date time datetime timestamp create table t9(d date,t time,dt datetime); insert into t9 values(now(),now(),now());
datatime的日期范围1001-9999 timestamp的日期范围1970-2038 datetime存储时间与是时区无关 timestamp存储时间与时区有关,显示值也依赖时区 datetime使用8字节存储空间 timestamp使用4字节存储空间 相比timestamp空间利用率更高 datetime默认值为空 timestamp默认值不为空,默认为当前时间
插入时间需要以字符串形式,并且按照对应格式插入 插入年份时,尽量使用4位值 插入2位年份时,小于等于69,以20开头比如55 2055 大于等于70,以19开头比如79 1979
create table students( id int, name varchar(20), born_year year, born date, class_time time, reg_time datetime );
insert into students values (2,'ropon',"1999","1999-01-01","12:12:12","2018-01-01 12:02:02"), (3,'pengge',"1998","1998-02-02","13:13:13","2018-02-01 13:02:02");
枚举类型和集合类型 enum和set 应用场景 字段的值只能在给定范围中选择 比如单选框 enum 多选框 set create table user( id int, name varchar(50), sex enum('男','女','其它'), level enum('注册会员','铜牌会员','银牌会员','黄金会员'), fav set('玩游戏','听歌','看电影','逛街','吃') ); insert into user values (1,'Ropon','男','黄金会员','听歌'), (1,'鹏哥','男','铜牌会员','看电影');
约束 primary key (pk) 标识该字段为该表的主键 可做唯一标识记录 foreign key (fk) 标识该字段为该表的外键 not null 标识该字段不能为空 unique key (uk) 标识该字段的值是唯一的 auto_increment 标识该字段的值自动增长(整数类型 且为主键) default 为该字段设置默认值 unsigned 无符号 zerofill 使用0填充
sex enum('男','女') not null default '男' age int unsigned not null default 20
主键 primary key 外键 foreign key 索引 index,unique
create table t9(id int,nid int not null); insert into t9 values('',1);
create table students2( id int not null, name varchar(20) not null, age int(3) unsigned not null default 18, sex enum('男','女') default '男', fav set('玩游戏','听歌','看电影','逛街','吃') default '听歌' ); insert into students2(id,name) values(1,'Ropon');
unique 单列唯一 create table department( id int, name char(10) unique );
create table department( id int unique, name char(10) unique );
create table department( id int, name char(10), unique(name) );
create table services( id int, ip char(15), port int, unique(id), unique(ip,port) );
primary key 一个表中单列可作主键,多列也可以作主键 约束等价于not null nuique 字段唯一不为空 innodb存储引擎,一张表必须有一个主键
单列主键 create table user2( id int primary key, name char(20) not null );
复合(多列)主键 create table services2( ip char(15), port int, primary key(ip,port) );
auto_increment 约束的字段为自动增长 create table user3( id int primary key auto_increment, name char(20) not null );
清空表 truncate user3; 数据量大,删除效率高,有自增id删除后可从0开始 delete from user3; 有自增id删除后可从原来结束值开始
show variables like 'auto_inc%'; auto_increment_increment 步长默认为1 auto_increment_offset 起始偏移量默认为1
foreign key 外键 关联 约束fk_dep dep_id外键参考dep的id constraint fk_dep foreign key(dep_id) references dep(id) 同步删除 on delete cascad 同步更新 on update cascade
创建表时先创建被关联表,再创建关联表 create table dep( id int primary key, name varchar(20) not null, descripe varchar(30) not null );
create table emp( id int primary key, name varchar(20) not null, dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade );
insert into dep values (1,'技术部','技术部'), (2,'运维部','运维部'), (3,'销售部','销售部');
insert into emp values (1,'Ropon',1), (2,'Pengge',3), (3,'Luopeng',2), (4,'Liss',1);
加foreign key约束,2张表形成三种关系 多对一(一对多) 多对多 一对一
1.以emp表看 是否emp表的多条记录对应dep表的一条记录 如果是说明emp表的一个字段foreign key(外键)dep表的一个字段(通常是id)
2.以dep表看 是否dep表的多条记录对应dep表的一条记录 如果是说明dep表的一个字段foreign key(外键)emp表的一个字段(通常是id)
如果1成立 则emp表多对一dep表 如果2成立 则dep表多对一emp表
如果1和2同时成立 则emp表和dep表双向多对一,即多对多 且需要定义一个表存emp表和dep表的关系
如果1和2都不成立 而且emp表的一条记录唯一对应dep表的一条记录 反之亦然 通常是emp表一个字段foreign key(外键)emp表的一个字段且将emp表外键字段设置成unique
多对一(一对多) 例子:书和出版社 create table press( id int primary key auto_increment, name varchar(20) not null );
create table book( id int primary key auto_increment, name varchar(20) not null, press_id int not null, constraint fk_book_press foreign key(press_id) references press(id) on delete cascade on update cascade );
insert into press(name) values ('出版社1'),('出版社2');
insert into book(name,press_id) values ('书籍1',1),('书籍2',1),('书籍3',2);
多对多 例子:书和作者 create table author( id int primary key auto_increment, name varchar(20) not null );
create table author2book( id int not null unique auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, primary key(author_id,book_id) );
insert into author(name) values('ropon'),('pengge');
insert into author2book(author_id,book_id) values (1,1),(1,2),(1,3),(2,1),(2,2);
一对一 例子:用户和博客 create table user( id int primary key auto_increment, name varchar(20) );
create table blog( id int primary key auto_increment, url varchar(100) not null, user_id int unique, constraint fk_user foreign key(user_id) references user(id) on delete cascade on update cascade );
insert into user(name) values('ropon'),('pengge'),('luopeng');
insert into blog(url,user_id) values ('ropon.top/ropon',1), ('ropon.top/pengge',2), ('ropon.top/luopeng',3);
增删改查
增 插入完整数据(顺序插入) insert into 表名(字段1,字段2,字段3...) values(值1,值2,值3...); insert into 表名 values(值1,值2,值3...);
指定字段插入数据 insert into 表名(字段1,字段2) values(值1,值2);
插入多条数据 insert into 表名 values (值1,值2,值3...), (值1,值2,值3...), (值1,值2,值3...);
插入查询其他表的结果 insert into 表名(字段1,字段2,字段3...) select 字段1,字段2,字段3... from 表2 where ...;
删 删除表所有数据 delete from 表名;
删除指定记录 delete from 表名 where id=xx;
改 更新数据 update 表名 set 字段1=值1, 字段2=值2, 字段3=值3... where ...;
查 表单查询 select 字段1,字段2,字段3... from 表名 where 条件 group by filed having 筛选 order by field limit 限制条数
关键字优先级 from 从哪个表,找表 where 约束条件,取记录 group by 取出的记录进行分组 having 分组的结果进行过滤 select 执行select distinct 去重 order by 将结果按条件排序 limit 限制显示条数
create table employee( id int primary key auto_increment, name varchar(20) not null, sex enum('男','女') not null default '男', age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, depart_id int );
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('ropon','男','20','2017-01-03','it','1254025.25',11,1), ('ropon1','男','20','2017-02-03','it','254025.25',11,1), ('ropon2','男','21','2018-06-03','it','124025.24',11,2), ('ropon3','男','23','2018-02-03','it1','125025.23',11,3), ('ropon4','男','25','2018-01-03','it3','125425.22',11,3), ('ropon5','男','26','2018-03-03','it5','12525.21',11,3);
where 约束条件 比较运算符 > < >= <= <> != 值在a和b之间 between a and b 值是a或b或c in(a,b,c) 模糊匹配 like 'xxxx' _表示一个字符 比如like 'ropo_' ropon匹配成功 ropon1不匹配 %表示任意多字符 比如like 'ropo%' ropon匹配成功 ropon1也能匹配成功 逻辑运算符 and or not
group by 分组查询 分组是基于where之后得到的记录而进行的 分组是 将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组或者按照性别进行分组等 为何要分组 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数
'每'后面的字段,通常是分组的依据
可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
由于没有设置sql_mode为ONLY_FULL_GROUP_BY,默认都是组内的第一条记录,但其实这是没有意义的 如果想分组,则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY set global sql_mode='ONLY_FULL_GROUP_BY';
聚合函数 max() 求最大值 min() 求最小值 avg() 求平均值 sum() 求和 count() 求总个数
查看每个部门有多少员工 select post,count(id) from employee group by post;
查看每个部门的最高薪水 select post,max(salary) from employee group by post;
查看每个部门的最低薪水 select post,min(salary) from employee group by post;
查看每个部门的平均薪水 select post,avg(salary) from employee group by post;
查看每个部门的所有薪水 select post,sum(salary) from employee group by post;
having 过滤 优先级 where > group by > having where 发生在group by之前,因而where中可以有任意字段,但不能使用聚合函数 having 发生在group by之后,因而having中可以使用分组字段,无法直接取到其他字段,可以借助聚合函数
使用group by 要使用group_concat()函数,将所有的name值连接
查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 select post,group_concat(name),count(id) from employee group by post having count(id)<2;
查询各岗位平均薪资大于120000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary)>120000;
查询各岗位平均薪资大于120000且小于125000的岗位名、平均工资 select post,avg(salary) from employee group by post having avg(salary)>120000 and avg(salary)<125000;
order by 查询排序 默认升序 desc降序 按单列排序 select * from employee order by age; select * from employee order by age asc; select * from employee order by age desc;
按多列排序 先按年龄升序排序,如果年龄相同,再按id降序排序 select * from employee order by age asc,id desc;
limit 限制查询的记录数 select * from employee order by salary desc limit 3; 默认初始位置为0 select * from employee order by salary desc limit 0,5; 从第0条开始,即先查询出第一条,然后再往后查4条 1,2,3,4,5 select * from employee order by salary desc limit 5,5; 从第5条开始,即先查询出第6条,然后再往后查4条 6,7,8,9,10
多表查询
create table department2( id int not null, name varchar(20) not null );
create table employee2( id int primary key auto_increment, name varchar(20) not null, sex enum('男','女') not null default '男', age int not null, dep_id int not null );
insert into department2 values (800,'技术部'), (8001,'运维部'), (8002,'人力资源'), (8003,'销售部'), (8004,'业务部');
insert into employee2(name,sex,age,dep_id) values ('ropon','男',18,800), ('luo','男',20,8001), ('peng','女',19,8002), ('lisi','女',22,8003), ('zhangsan','女',22,8005);
多表连接查询 语法 select 表1.字段1,表.字段2,表2.字段1... from 表1 innerleftright join 表2 on 表1.字段=表2.字段;
交叉连接 不适用任何匹配条件 生成笛卡尔积 select * from employee2,department2;
内连接:只连接匹配的行 select employee2.id,employee2.name,employee2.age,department2.name from employee2 inner join department2 on employee2.dep_id=department2.id;
select employee2.id,employee2.name,employee2.age,department2.name from employee2,department where employee2.dep_id=department2.id;
以上2条sql含义相同
外链接之左连接:优先显示左表全部记录 select employee2.id,employee2.name,employee2.age,department2.name from employee2 left join department2 on employee2.dep_id=department2.id;
外链接之右连接:优先显示右表全部记录 select employee2.id,employee2.name,employee2.age,department2.name from employee2 right join department2 on employee2.dep_id=department2.id;
全外连接:显示左右两个表全部记录 select * from employee2 left join department2 on employee2.dep_id=department2.id union all select * from employee2 right join department2 on employee2.dep_id=department2.id;
select * from employee2 left join department2 on employee2.dep_id=department2.id union select * from employee2 right join department2 on employee2.dep_id=department2.id;
注意 union与union all的区别:union会去掉相同的纪录
符合条件连接查询 select employee2.id,employee2.name,employee2.age,department2.name from employee2 inner join department2 on employee2.dep_id=department2.id where age>20;
select employee2.id,employee2.name,employee2.age,department2.name from employee2 inner join department2 on employee2.dep_id=department2.id where age>18 order by age asc;
子查询 子查询指将一个查询语句嵌套在另一个查询语句中 内层查询语句结果,可以为外层查询语句提供查询条件 子查询语句中可以包含:in,not in,any,all,exists,not exists等关键字
in 查询平均年龄在20岁以上的部门名 select id,name from department2 where id in (select dep_id from employee2 group by dep_id having avg(age)>20);
查看技术部员工姓名 select name from employee2 where dep_id in (select id from department2 where name='技术部');
查看不足1人的部门名 select id,name from department2 where id not in (select dep_id from employee2 group by dep_id);
带比较运算符的子查询 比较运算符:=,!=,>,>=,<,<=,<>
查询大于 所有人平均年龄 的员工姓名与年龄 select name,age from employee2 where age > (select avg(age) from employee2);
查询大于 部门内平均年龄 的员工姓名与年龄
先查询每个部门平均年龄 select dep_id,avg(age) from employee2 group by dep_id;
得出临时表,表中dep_id和对应平均年龄,然后与员工表内链查询
select employee2.name,employee2.age from employee2 inner join (select dep_id,avg(age) as avg_age from employee2 group by dep_id) as t1 on employee2.dep_id=t1.dep_id and employee2.age>t1.avg_age;
带exists关键字的子查询
exists关键字表示存在,使用exists关键字时,内层查询语句不返回查询记录,而返回布尔值True或False 当返回True时,外层查询语句进行查询;当返回False时,外层查询语句不进行查询 SELECT * FROM employee2 WHERE EXISTS (SELECT id FROM department2 WHERE id=800);
SELECT * FROM employee2 WHERE EXISTS (SELECT id FROM department2 WHERE id=8005);
SELECT employee.`name`,employee.hire_date,employee.post FROM employee INNER JOIN (SELECT post,MAX(hire_date) AS maxdate FROM employee GROUP BY employee.post) AS t1 ON employee.hire_date = t1.maxdate;
mysql备份相关命令
导出数据库或数据库下某个表 语法:mysqldump -u用户名 -p密码 [--default-character-set=utf8] 数据库名 [表名] > 导出sql文件名 例子:mysqldump -uropon -p123456 ropon>~/ropon.sql [ d:\ropon.sql ]
仅导出数据库或数据库下某个表结构 语法:mysqldump -u用户名 -p密码 [--default-character-set=utf8] -d [--add-drop-table] 数据库名 [表名] > 导出sql文件名 例子:mysqldump -uropon -p123456 -d ropon >~/ropon.sql [ d:\ropon.sql ]
导入sql备份文件 语法:mysql -u用户名 -p密码 数据库名<~/ropon.sql [ d:\ropon.sql ] 例子:mysql -uropon -p123456 ropon<~/ropon.sql [ d:\ropon.sql ]
语法:mysqldump -u用户名 -p密码 数据库名<~/ropon.sql [ d:\ropon.sql ] 例子:mysqldump -uropon -p123456 ropon<~/ropon.sql [ d:\ropon.sql ]
语法:mysql -u用户名 -p密码 例子:mysql>source ~/ropon.sql [ d:\ropon.sql ];
索引 约束和加速查找
普通索引 唯一索引 主键索引 联合索引(多列) 联合主键索引 联合唯一索引 联合普通索引
无索引:从前往后一条一条查询 有索引:创建索引本质就是创建额外文件(某种格式存储,查询时候,先去额外文件找,定好位置 然后再去原始表中直接查询)
索引特点: 额外的文件保存特殊的数据结构 查询快,但增删改依然慢 创建索引之后,必须命中才有效
索引种类 hash类型的索引:查询单条快,查询范围慢 btree类型的索引:b+数,层数越多,数据量 成指数级 增长(innodb默认支持)
普通索引 仅有一个加速查找 创建表同时加普通索引 create table userinfo( id int primary key auto_increment, name varchar(20) not null, passwd varchar(30) not null, index ix_name(name) );
创建普通索引 create index 索引名字 on 表名(列名)
删除索引 drop index 索引名字 on 表名
查看索引 show index from 表名
唯一索引 唯一索引有两个功能:加速查找和唯一约束 允许为空null,但只有能一行 创建表同时加唯一索引 create table userinfo( id int primary key auto_increment, name varchar(20) not null, passwd varchar(30) not null, unique index ix_name(name) );
创建唯一索引 create unique index 索引名字 on 表名(列名)
删除索引 drop index 索引名字 on 表名
主键索引 主键索引有两个功能:加速查找和唯一约束 不能为空null 创建表同时加主键索引 create table userinfo3( id int primary key auto_increment, name varchar(20) not null, passwd varchar(30) not null, );
创建主键索引 alter table 表名 add primary key(列名)
删除主键索引 alter table 表名 drop primary key;
重新定义列类型并删除primay key属性 alter table 表名 modify 列名 int,drop primay key;
组合索引 组合索引是将n个列组合成一个索引 应用场景:频繁的同时使用n列来进行查询 create index 索引名 on 表名(列名1,列名2);
创建索引 命中索引 正确使用索引
create table userinfo4( id int not null, name varchar(20) not null, gender enum('男','女') default '男', email varchar(50) not null );
创建存储过程,批量插入数据 声明存储过程的结束符号$$ delimiter $$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into userinfo4 values(i,concat('ropon',i),'男',concat('pengge',i,'@ropon.top')); set i=i+1; end while; END$$ delimiter ;
查看存储过程 show create procedure auto_insert1\G;
调用存储过程 call auto_insert1();
删除存储过程 drop procedure auto_insert1;
生成大数据例子2 drop table if exists userinfotest; create table userinfotest( id int not null primary key auto_increment, name varchar(20) not null, age int not null, email varchar(50) not null );
创建存储过程,批量插入数据 例子2 delimiter $$ create procedure auto_insert3(in num int) BEGIN declare i int default 1; while(i<num)do insert into userinfotest(name,age,email) values(concat('ropon',i),rand()*50+18,concat('pengge',i,'@ropon.top')); set i=i+1; end while; END$$ delimiter ;
正确使用索引,以下例子没有正确使用 like '%pon66' 模糊匹配 select email from userinfo4 where name like '%pon66';
使用函数 reverse 反转 select * from userinfo4 where reverse(name) = '999nopor';
or 注意:当or条件中 有未建议索引的列 才失效 select * from userinfo4 where id = 8 or name='pengg1888'; select * from userinfo4 where id =8 or email = 'pengge168@ropon.top' and name = 'pengge168';
数据类型不一致 select * from userinfo4 where name = 43565;
!= 如果是主键,会走索引 select count(email) from userinfo4 where name != 'ropon'; select count(email) from userinfo4 where id != '28';
> 如果是主键或者索引是整数类型,会走索引 select * from userinfo4 where name > 'ropon'; select * from userinfo4 where id > 666;
order by 根据索引排序时,选择映射如果不是索引,则不走索引 但是如果对主键排序,则还是会走索引 select * from userinfo4 order by id desc limit 50;
组合索引最左前缀,查询时优先比配左边 如果组合索引为(name,email) name and email 走索引 select * from userinfo4 where name = 'ropon123' and email='pengge123@ropon.top'; name 走索引 select * from userinfo4 where name = 'ropon123'; email 不走索引 select * from userinfo4 where email='pengge123@ropon.top';
索引建议 避免使用select * 创建表时尽量使用char代替varchar 表的字段顺序固定长度优先 组合索引代替多个单列索引 尽量使用短索引 create index ix_title on article(title(10)); 使用连接join代替子查询 连接表时注意条件类型是否一致 散列(重复少)不适合创建索引,比如性别
分析优化 显示sql执行信息参数 explain sql语句
参数说明
select_type 查询类型 simple 简单查询 primary 最外层查询 subquery 映射为子查询 derived 子查询 union 联合 union result 使用联合的结果 table 正在访问的表名 type 查询时访问方式 all 全表扫描 对数据表从头到尾找一遍 select * from userinfo4; 不过如果有limit限制,找到之后就不再继续向下扫描 index 全索引扫描 对索引从头到尾找一遍 range 对索引列进行范围查找 index_merge 合并索引,使用多个单列索引搜索 ref 根据索引查找一个或多个值 eq_ref 连接时使用primary key 或unique类型 const 常量 system 系统
慢查询日志 show variables like 'slow_query%'; slow_query_log 慢查询状态 slow_query_log_file 慢查询日志的存放路径
开启慢查询日志 set global slow_query_log=1;
或者my.cnf [mysqld]后配置 slow_query_log =1
show variables like 'long%'; long_query_time 慢查询超时时间,默认10s
修改慢查询超时时间 比如修改为1s set global long_query_time=1
或者my.cnf [mysqld]后配置 long_query_time =1
修改慢查询日志存放路径 my.cnf [mysqld]后配置 slow_query_log_file ='xxxx'
|