数据库进阶操作

数据库相关操作

查看所有数据库：show databases

使用数据库：user 数据库名

查看当前使用的数据库：select database()

创建数据库：create database 数据库名 charset=utf8

删除数据库：drop database 数据库名

数据表相关操作

查看当前数据库中所有表：show tables

查看表结构：desc 表名

查看表的创建语句：show create table 表名

数据库备份

以管理员身份进入cmd，运行mysqldump命令

cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin

mysqldump –uroot –p 数据库名 > ceshi.sql

# 按提示输入mysql的密码

数据库恢复

先创建新的数据库

mysql -uroot –p 新数据库名 < ceshi.sql

# 按提示输入mysql的密码

mysql内置函数

字符串相关

• concat(str1,str2…):字符串关联、连接
• length(str):字符串长度

截取字符串

• left(str,len)返回字符串str的左端len个字符
• right(str,len)返回字符串str的右端len个字符
• substring(str,pos,len)返回字符串str的位置pos起len个字符

去掉空格

• ltrim(str)返回删除了左空格的字符串str
• rtrim(str)返回删除了右空格的字符串str
• trim

大小写转换

• lower(str)
• upper(str)

数字相关

• round(n,d)：四舍五入值，n表示原数，d表示小数位置，默认为0
• 求x的y次幂pow(x,y)
• 获取圆周率PI()
• rand()：随机数，值为0-1.0的浮点数，用在随机排序，抽取随机记录

日期相关

• current_date()：返回当前年月日

• current_time()：返回当前时分秒

• now()：返回当前年月日和时分秒

• date_format(date,format)：日期格式化

  如：select date_format('2016-12-21','%Y %m %d');

  参数format可选值：

  %Y 获取年，返回完整年份
  %y 获取年，返回简写年份
  %m 获取月，返回月份
  %d 获取日，返回天值
  %H 获取时，返回24进制的小时数
  %h 获取时，返回12进制的小时数
  %i 获取分，返回分钟数
  %s 获取秒，返回秒数
  

存储过程

也翻译为存储程序，是一条或者多条SQL语句的集合

存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.

存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql,
不需要重复编译

格式

-- 存储过程
delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;


-- 调用
call 存储过程(参数列表);

示例：
要求：创建查询过程，查询学生信息

-- step1：设置分割符
delimiter //
-- step2：创建存储过程
create procedure proc_stu()
begin
select * from students;
end
//
-- step3：还原分割符
delimiter ;

-- 调用存储过程proc_stu
call proc_stu();

视图

**本质是对查询的封装。**适用于查询复杂的情况，将视图当作一张表，只读，不能删除和修改记录，只能查看。

定义视图，建议以v_开头：create view 视图名称 as select语句;

索引

数据量比较大时可加索引

• 删除索引：drop index 索引名称 on 表名

• 查看索引：show index from 表名

• 创建索引

  • 建表时创建：create table create_index(...）

  • 对于已经存在的表，添加索引:create index 索引名称 on 表名(字段名称(长度))
    如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致

    create index age_index on create_index(age);
    create index name_index on create_index(name(10));
    

事务

广泛应用于订单系统、银行等场景

对表的修改操作，多条数据操作时适用，一致性，例如转账，要么成功，要么失败，一旦开启事务，最好尽快，事务周期越短越好

开启事务

• begin

结束事务

• rollback

  • 回滚事务，放弃缓存中的变更数据

• commit

  • 提交，将缓存中的数据变更维护到物理表中

示例：实现效果，需要再两个命令行窗口，使用同一个数据库，操作同一张表

• rollback

命令行 1 ：select * from students;
命令行2 ：
begin;
insert into students(studentNo,name) values ('013','我是新来的');//添加数据
命令行2 ：select * from students;//查询数据显示有新增的数据
命令行 1 ：select * from students;//查询数据显示有新增的数据
命令行2：rollback;
命令行 1 ：select * from students;//查询数据中没有新增的数据

• commit

    命令行 2 ：
    begin;
    insert into students(studentNo,name) values ('013','我是新来的');//添加数据
    命令行 2 ：select * from students;//查询数据显示有新增的数据
    命令行 1 ：select * from students;//查询数据显示有新增的数据
    命令行 2 ：commit;
    命令行 1 ：select * from students;//查询数据中有新增的数据
  

命令行

启动数据库

打开cmd程序，进入到mysql安装目录的bin目录下

-- 1、进入mysql的bin目录
cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
-- 2、连接mysql
mysql -uroot -p

外键foreign key

设置外键约束

• 1、建表时设置
  表1：id int unsigned primary key auto_increment,
  表2：foreign key(自己的字段) references 主表(主表字段)

• 建表后设置

  alter table 从表名 add foreign key (从表字段) references 主表名(主表字段);
  alter table stu add foreign key (class_id) references class(id);
  

删除

-- 先获取外键约束名称
show create table stu;
-- 获取名称之后就可以根据名称来删除外键约束
alter table 表名 drop foreign key 外键名称;
alter table stu drop foreign key stu_ibfk_1;

修改密码

使用root登录，修改mysql数据库的user表

1、使用password()函数进行密码加密

2、注意修改完成后需要刷新权限

use mysql;
update user set password=password('新密码') where user='用户名';

update user set password=password('123') where user='root';

-- 刷新权限：flush privileges;

忘记 root 账户密码怎么办

1、配置mysql登录时不需要密码，修改配置文件

​ Centos中：配置文件位置为/data/server/mysql/my.cnf

​ Windows中：配置文件位置为C:\Program Files (x86)\MySQL\MySQL Server 5.1\my.ini修改，找到mysqld，在它的下一行，添加skip-grant-tables

[mysqld]
skip-grant-tables

2、重启mysql，免密码登录，修改mysql数据库的user表

use mysql;
update user set password=password('新密码') where user='用户名';

update user set password=password('123') where user='root';
-- 刷新权限：flush privileges;

3、还原配置文件，把刚才添加的skip-grant-tables删除，重启