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</usr/local/mysql/backup/db_goods.sql

索引

  • 索引分为单列索引与多列索引
  • 创建索引时,需要确保该索引是应用在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

推荐阅读更多精彩内容