mysql 备份 索引_MySQL语句、备份与索引

笔记

mac终端启动mysql

终端启动mysql

1、PATH="$PATH":/usr/local/mysql/bin

2、mysql -u root -p

3、输入密码:****

如果碰到密码不对什么的，重置密码

step 1: SET PASSWORD = PASSWORD(‘新密码’);

step 2: ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

step 3: flush privileges;

完成以上三步退出再登。

数据库相关知识点

数据库.png

多表查询

举例两张表,创建如下

student表

CREATE TABLE student (

sid int(11) NOT NULL PRIMARY KEY,

sname varchar(15) DEFAULT NULL,

gender varchar(2) DEFAULT NULL,

age int(11) DEFAULT NULL,

score int(11) NOT NULL,

FOREIGN KEY (gender) REFERENCES person(sex)

)

person表

CREATE TABLE person (

sex varchar(2) DEFAULT NULL PRIMARY KEY,

detail varchar(15) DEFAULT NULL

)

隐式内连接查询

select * from student as stu,person as p where stu.gender=p.sex;

意思:在查询的结果中再去过滤掉不符合条件的，效率低。

显示内连接查询

select * from student as stu inner join person as p on stu.gender=p.sex;

意思:带着条件去查询的所要的结果，效率高。

左外连接查询

select * from student stu left outer join person per on stu.gender=per.sex;

意思:将左表所有数据全部查出，同时右边符合条件的显示出来，如果右边中没有相应的数据，用null代替。

右外连接查询

select * from student stu right outer join person per on stu.gender=per.sex;

意思:将右表所有数据全部查出，同时左边符合条件的显示出来，如果左边中没有相应的数据，用null代替。

分页查询

select * from student limit startIndex,count; // 从startIndex=0开始，每页查询count个

第一个参数:起始位置，数据库是从0开始，计算:startIndex = (index当前页码-1)*count

第二个参数:每页查询个数

子查询查询语句中嵌套查询语句

select * from student where gender=(select sex from person where detail='男');// 子语句在where后面

select sname 姓名,(select detail 性别 from person p where stu.gender=p.sex) 性别 from student stu; // 用左右连接查询也可实现子语句在select后面

注意：上面的inner 与 outer关键字可以省略

inner join ...on = join ... on

left outer join ...on = left join ... on

right outer join ...on = right join ... on

备份与恢复

安装完mysql后，在mysql/bin目录下，会有一个mysqldump工具，使用它来进行数据库备份

备份db_goods数据库操作:

./mysqldump -u root -p db_goods >/usr/local/mysql/backup/db_goods.sql

数据库恢复，需要手动先创建库，然后再用恢复命令。

create database db_goods;

恢复命令:

mysql -u root -p 123456 db_goods

索引

索引分为单列索引与多列索引

创建索引时，需要确保该索引是应用在sql查询语句的条件中，即where后面。提高查询速度。

唯一索引，unique index ，列值必须唯一的。如果是多列唯一索引，可多列组合的值必须唯一。可以有null，且null可以有多个，不唯一。

注意：索引不宜创建过多，过多会降低更新表的效率，因为更新表时不仅要更新数据，同时还要更新索引文件

单列索引

方法一：

create index 索引名 on 表名(字段名)；

如: 为tb_goods表中的category_id添加索引

create index category_id on tb_goods('category_id');

唯一索引

create unique index 索引名 on 表名(字段名)；

方法二:

alter table 表名 add index 索引名(字段名)；

如: 为tb_goods表中的category_id添加索引

alter table tb_goods add index category_id('category_id');

唯一索引

alter table 表名 add unique 索引名(字段名)；

方法三:

创建表时直接添加

create table tb_goods(

sid int(11) NOT NULL PRIMARY KEY,

category_id varchar(15) DEFAULT NULL,

gender varchar(2) DEFAULT NULL,

age int(11) DEFAULT NULL,

score int(11) NOT NULL,

INDEX category_id(category_id)

);

唯一索引

上面index换成unique

删除索引：

drop index category_id('category_id');

查看索引

show index from 表名;

show index from `tb_category`;

多列索引

多列索引，相当于添加了多个组合索引，如(name,age,score)这个多列索引

相当于添加了(name)、(name,age)、(name,age,score)三个索引。

创建与删除方法与单索引大体一致，只是字段名是多个。如下示例：

create index 索引名 on 表名(字段名1，字段名2，...)；

如: 为tb_goods表中的category_id,brand_id添加组合索引

create index idx_category_brand on tb_goods(category_id,brand_id);

创建SQL函数示例

CREATE DEFINER=`root`@`%` FUNCTION `same_interest`(user_id BIGINT(32), user_id_two BIGINT(32)) RETURNS int(5)

READS SQL DATA

BEGIN

DECLARE return_value INT(5) DEFAULT 0;

SELECT COUNT(1) INTO return_value

FROM (SELECT til.label_id

FROM t_user_interest_label til

WHERE til.user_id = user_id

) aa

INNER JOIN (

SELECT til.label_id

FROM t_user_interest_label til

WHERE til.user_id = user_id_two

) bb

ON aa.label_id = bb.label_id;

RETURN return_value;

END;

外部调用示例： select same_interest(14, tu.id) as sameCount

from t_user tu