数据库操作与SQL语句详解-优快云博客

本文详细介绍了数据库的基础概念，包括关系型与非关系型数据库的区别，以及MySQL的常见操作，如用户管理、数据库与数据表的创建、SQL语句的使用、数据迁移和SQL内置函数等，为数据库管理和数据操作提供了全面的指南。

上节回顾

什么是模版引擎？常用的模版引擎有什么？
flask的默认模版引擎是什么？
怎么指定静态文件的目录位置？
jinjia2模版引擎的基本语法？
jinjia2模版继承都需要注意什么？

数据库相关知识

什么是数据库？
- 数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。
- 数据库 是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。(摘自百度百科)
数据库的种类？
- 关系型数据库，代表人物 Oracle ，Mysql，SQLServer，PostgreSQL 等
- 非关系型数据库，代表人物 Redis，Mongodb，Memcached，Hbase 等
流行的数据库有哪些？
- 流行的关系型数据库 Oracle ，Mysql，SQLServer 等
- 流行的非关系型数据库 Redis，Mongodb，Hbase 等
Mysql 操作
- 用户创建与权限管理
  - 创建用户并设置密码
```
use mysql;
create user jack@localhost;
update user set password=password('123456') where user='jack'
```
```
create user ja@localhost identified by '123456';
```
  - 用户权限管理
```
- 授权
GRANT SELECT, INSERT, UPDATE on test.* to jack@localhost;

- 收回权限
REVOKE ALL PRIVILEGES, GRANT OPRTION FROM jack;
```
  - 忘记root密码
```
1. 停止mysql服务
2. 切入命令行模式，进入mysql的根目录启动  mysqld --skip-grant-tables;
3. 新建一个命令行模式，输入 mysql -u `用户名`
4. 进入之后，use mysql;
5. update user set password=password('123456') where user='用户名';
6. flush privileges;
```
- 建库建表
  - 创建数据库
```
create database 数据库名称;
```
  - 查询已创建的数据库
```
show databases;
```
  - 删除数据库
```
drop database 数据库名称;
```
  - 使用数据库
```
use  数据库名称;
```
  - 创建数据表
```
create table `user`(
	`uid` int(11) not null auto_increment,
	`username` varchar(32) CHARACTER SET utf8 NOT NULL,
	`create_time` datetime DEFAULT NULL,
	PRIMARY KEY(`uid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
```
  - 查询数据库中的所有表
```
show tables;
```
  - 查询数据表结构
```
desc 表名
```
  - 查询数据表建表语句
```
show create table 表名
```
  - 删除数据表
```
drop table 表名；
```
  - 更改数据表字段结构
```
# 更改属性
alter table `表名`  modify column `字段名` 属性(属性值);
# 一张表 两个字段 将前面的字段移到后面的字段
alter table `表名` modify column `前字段名` 属性（属性值） after `后字段名`;
# 修改字段名和属性以及属性值
alter table `表名` CHANGE `字段名1` `字段名2` 属性 属性值
```
- SQL 语句
  - insert 语句
```
insert into `表名` set `字段名`='值'
insert into `表名`(`字段1`,`字段2`) values('值1','值2')
```
  - select 语句
```
select `字段名` from `表名` where '条件'；
```
  - update 语句
```
update `表名` set `字段名`='值' where 条件；
```
  - delete 语句
```
delete from `表名` where 条件；
```
  - join 语句
```
select `表1.字段1` from `表1` left join `表2` on `表1`.字段名=`表2`.字段名 where 条件;
```
- 数据迁移
  - 导出
```
- 导出一个库中的所有数据
mysqldump -hlocalhost -uroot -p123456 test -d > test.data.sql;
- 导出一个库中某张表的所有数据
mysqldump -hlocalhost -uroot -p123456 test.demo -d > test.data.sql;
```
  - 导入
```
- mysql 导入
mysql -uroot -p123456 < test.sql
```
```
- source 导入
use database_demo;
source /ab/cd/ef.sql
```
```
- load data 导入
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
```
- SQL 内置函数
  - 字符串函数
    - concat(s1,s2,…Sn) :连接s1,s2..Sn为一个字符串
    - CONCAT_WS(sepa, s1, s2) : 用sepa 连接 s1 s2
    - insert(str,x,y,instr):将字符串str从第xx位置开始，y字符串的子字符串替换为字符串str
    - lower(str) : 将所有的字符串变为小写
    - upper(str)：将所有的字符串变为大写
    - left(str,x)：返回字符串中最左边的x个字符
    - rigth(str,y)：返回字符串中最右边的x个字符
    - lpad(str,n,pad)：用字符串pad对str最左边进行填充，直到长度为n个字符串长度
    - rpad(str,n,pad)：用字符串pad对str最右边进行填充，直到长度为n个字符串长度
    - trim(str)：去掉左右两边的空格
    - ltrim(str)：去掉字符串str左侧的空格
    - rtrim(str)：去掉字符串str右侧的空格
    - repeat(str,x)：返回字符串str重复x次
    - replace(str,a,b)：将字符串的的a替换成b
    - strcmp(s1,s2) ：比较字符串s1和s2
    - substring(s,x,y)：返回字符串指定的长度
    - length(str) ：返回值为字符串str 的长度
  - 日期函数
    - 获取日期格式的时间 NOW()、CURRENT_DATE()、CURRENT_TIME()、CURRENT_TIMESTAMP()、YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()
    - 日期时间格式和UNIX时间戳互换：FROM_UNIXTIME()、UNIX_TIMESTAMP()
    - 计算日期差值函数： TO_DAYS()、DATEDIFF()、SUBDATE()、ADDDATE()
  - 数值函数
    - abs(x) 返回x的绝对值
    - ceil(x) 返回大于x的最小整数值
    - floor(x) 返回小于x的最大整数值
    - mod(x,y) 返回x/y的取余结果
    - rand() 返回0~1之间的随机数
    - round(x,y)返回参数x的四舍五入的有y位小数的值
    - truncate(x,y) 返回x截断为y位小数的结果
  - 其他函数
    - database() :返回当前数据库名
    - version() :返回当前服务器版本
    - user() ：返回当前登陆用户名
    - inet_aton ：返回当前IP地址的数字表示 inet_aton("192.168.80.250");
    - inet_ntoa(num) :返回当前数字表示的inet_ntoa(3232256250);
    - password(str) : 返回当前str的加密版本
    - md5(str): 返回字符串str的md5值
- SQL 索引
  - 单列索引
    - 普通索引
      - mysql 基本的数据类型，几乎没有什么限制，只是在查询的时候速度要比没有索引的时候要快很多
    - 唯一索引
      
      索引中的值必须是唯一的，但是可以有空值
    - 主键索引
      
      主键索引是一种唯一索引，不允许有重复值
  - 组合索引
    - 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段左侧字段的时候，索引才会被使用。
    - 组合索引遵循最左前缀集合的原则
  - 全文索引
    - 全文索引，只能应用在 Myisam 存储引擎，而且只能在CHAR,VARCHAR,TEXT类型的字段上使用全文索引。
    - 全文索引，只是在通过一个关键字或者关键词来寻找该字段所属的记录行。
  - 空间索引
    - 空间索引是对空间数据类型的字段建立的索引，MySQL 空间的数据类型有四种，GEOMETRY , POINT , LINESTRING , POLYGON
    - 创建空间索引时使用 SPATIAL 关键字
    - 在 Myisam 存储引擎上创建空间索引。必须将其声明 NOT NULL。