mysql数据库库表操作

于 2021-04-26 18:50:47 发布
阅读量391 收藏 4
点赞数
分类专栏： MySQL 文章标签： 数据库 python mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46049759/article/details/116167356
版权

文章目录

Mysql总结

索引需要完善

一、数据库操作

1.查看数据库
shou databases [like wild]

2.创建数据库
create database [if not exists] 数据库名称;
li: create database 数据库名称 [default charset=utf8mb4];
查看创建数据库：show create database <数据库名>;

3.删除
drop database [if exists] 数据库名;

4.切换
use 数据库名;

5.其他
查看当前连接的数据库: SELECT DATABASE();
查看数据库版本: SELECT VERSION();
查看当前用户: SELECT USER();
查看所有用户: SELECT User,Host,Password FROM mysql.user;

数据库对象命名规则

必须以字母开头
可包括数字和三个特殊字符（# _ $）
不要使用MySQL的保留字
同一Schema下的对象不能同名

二、表的基本操作

1.基本操作
=====1.创建表
 CREATE TABLE 表名
 (column datatype[DEFAULT expr] ,
 …
 ) ENGINE = 存储机制
 
简单语法：
CREATE TABLE 表名（
 列名 列类型，
 列名 列类型
 ）;
功能：在当前数据库中创建一张表

=====2.查看表
show tables [from 数据库名][like wild];

=====3.查看表中各列信息
{DESCRIBE|DESC} 表名[列名];
# or
show columns from 表名称; 

查看更全面的表定义信息
show create table 表名 \G;  # \G 是使显示结果旋转90°

=====4.删除表
drop table [if exists] 表名;

=====5.修改表的结构
修改列类型
alter table 表名 modify 列名 列类型;
增加列
alter table 表名 add 列名 列类型;
删除列
alter table 表名 drop 列名;
列改名
alter table 表名 change 旧列名 新列名 列类型;
更改表名
alter table 表名 rename 新表名;
rename table 表名 to 新表名;

=====6.复制表的结构
    复制一个表结构的实现方法有两种
    方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
    create table 新表名 like 源表;
    方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
    记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
    create table 新表名 select * from 源表;
    方法三：如果已经存在一张机构一致的表，复制数据
    insert into 表 select * from 原表;
2.表的约束
NOT NULL		非空
UNIQUE Key		唯一键
PRIMARY KEY		主键
FOREIGN KEY		外键
CHECK			检查
默认值约束

约束的方法

约束作为数据库对象，存放在系统表中，也有自己的名字

创建约束的时机

  1. 建表的同时创建
  2. 建表后创建（修改表

可定义列级或表级约束

有单列约束和多列约束

定义约束的语法

列级约束：在定义列的同时定义约束
语法：列定义 约束类型

表级约束：在定义了所有列之后定义的约束
语法：
列定义
[CONSTRAINT 约束名] 约束类型(列名)

约束名的取名规则
推荐采用：表名 *列名* 约束类型简介
约束可以在创建表时就定义，也可以在创建完后再添加
语法：
alter table 表名 add constraint 约束名 约束类型(要约束的列名)
3.约束示例
1.非空约束(not null)
列级约束，只能使用列级约束语法定义。
确保字段值不允许为空
只能在字段级定义

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,     
    NAME VARCHAR(18) NOT NULL
)
#  prinmary key 是主键约束
# auto_increment 是自动增长id
========================================
2.唯一约束(unique)
唯一性约束条件确保所在的字段或者字段组合不出现重复值
唯一性约束条件的字段允许出现多个NULL
同一张表内可建多个唯一约束
唯一约束可由多列组合而成
建唯一约束时MySQL会为之建立对应的索引。
如果不给唯一约束起名，该唯一约束默认与列名相同。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18) UNIQUE NOT NULL
)
========================================
3.主键约束(primary key)
主键从功能上看相当于非空且唯一
一个表中只允许一个主键
主键是表中唯一确定一行数据的字段
删除表的约束
自动增长和默认值
存储引擎
主键字段可以是单字段或者是多字段的组合
当建立主键约束时，MySQL为主键创建对应的索引
主键约束名总为PRIMARY。

CREATE TABLE tb_student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(18)
)
========================================
4.外键约束
外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。

格式  FOREIGN KEY(外键列名) REFERENCES 主表(参照列)

1.
CREATE TABLE tb_dept(
    dept_id INT PRIMARY KEY,
    NAME VARCHAR(18),
    description VARCHAR(255)
);

CREATE TABLE tb_employee(
    employee_id INT PRIMARY KEY,
    NAME VARCHAR(18),
    gender VARCHAR(10),
    dept_id INT REFERENCES tb_dept(dept_id),
    address VARCHAR(255)
);

2.
create table SC(
sno int(10),
cno int(10), 
score int(10),
primary key (sno,cno),
foreign key(sno) references Student(sno),  # 外键约束
foreign key(cno) references Course(cno));
========================================
5.检查约束
# 注意检查约束在8.0之前，MySQL默认但不会强制的遵循check约束（写不报错，但是不生效，需要通触发器完成）
# 8之后就开始正式支持这个约束了

create table t3(
    id int,
    age int check(age > 18),
    gender char(1) check(gender in ('M','F'))
);
========================================
6.默认值约束
可以使用default关键字设置每一个字段的默认值。
-- 创建一张user表
CREATE TABLE test.user(  			#test.user 是在test数据库中创建user表
    id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    name VARCHAR(225) COMMENT '姓名',
    sex TINYINT(1) DEFAULT 1 COMMENT '性别 1男 0女',
    PRIMARY KEY (id)
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
4.删除约束
删除NOT NULL约束
alter table 表名 modify 列名 类型;
删除UNIQUE约束
alter table 表名 drop index 惟一约束名;
删除PRIMARY KEY约束
alter table 表名 drop primary key;
删除FOREIGN KEY约束 
alter table 表名 drop foreign key 外键名;

三、数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型

text类型

img

number类型

date类型

img

常用数据类型

img

四、用户授权

MySQL中授权(grant)和撤销授权(revoke）。

方法1：create和grant结合
    help CREATE USER;
    命令：CREATE USER <'用户名'@'地址'> IDENTIFIED BY ‘密码’;
    查看用户权限： help SHOW GRANTS;
    命令：show grants for '用户名'@'地址'；
    授权：help GRANT;
方法2：直接grant
    收回权限：REVOKE
    删除用户：DROP USER username
    
==========
生产环境授权用户建议：
    1、博客，CMS等产品的数据库授权
        select,insert,update,delete,create
        库生成后收回create权限
    2、生产环境主库用户授权
        select,insert,update,delete
    3、生产环境从库授权
        select
==========        
创建用户方法（推荐使用方法三）:
方法一：CREATE USER语句创建
    CREATE USER user1@’localhost’ IDENTIFIED BY ‘123456’;
	
方法二： INSERT语句创建
    INSERT INTO mysql.user(user,host, authentication_string,ssl_cipher,
                                x509_issuer,x509_subject)
    VALUES('user2','localhost',password('ABCabc123!'),'','','');
    FLUSH PRIVILEGES;
    
方法三： GRANT语句创建
    GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘123456’;
    FLUSH PRIVILEGES;
    
语法格式：
    grant 权限列表 on 库名.表名 to 用户名@'客户端主机'
    [identified by '密码' with option参数];
如：
    grant select on testdb.* to common_user@'%'
    grant insert on testdb.* to common_user@'%'
    grant update on testdb.* to common_user@'%'
    grant delete on testdb.* to common_user@'%'
    grant select, insert, update, delete on testdb.* to common_user@'%'
    grant create on testdb.* to developer@'192.168.0.%';
    grant alter on testdb.* to developer@'192.168.0.%';
    grant drop on testdb.* to developer@'192.168.0.%';
    grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
    show grants; -- 查看当前用户（自己）权限
    show grants for dba@localhost;
    grant all on *.* to dba@localhost;
    # 移除权限
    # revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可
    revoke all on *.* from dba@localhost;

with_option参数
    GRANT OPTION： 授权选项
    MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数
    MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数
    MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数
    MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数

五、有关数据表的DML操作

DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作。INSERT、DELETE、UPDATE、SELECT等

1.insert
insert into 表名[(列名,…)] select 语句——可以非常复杂。
2.replace
语法格式1：
replace into 表名 [（字段列表）] values （值列表）
语法格式2：
replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 where 条件表达式
语法格式3：
replace [into] 表名 set 字段1=值1, 字段2=值2

REPLACE与INSERT语句区别

​ replace语句的功能与insert语句的功能基本相同，不同之处在于：使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录。

​ 使用replace的最大好处就是可以将delete和insert合二为一，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了

3.update
UPDATE 表名
SET 列名 = value [, column = value]
[WHERE 限制条件];

li:
UPDATE SC SET score=90
where sid = "07"
and cid ="02";

修改可以一次修改多行数据，修改的数据可用where子句限定，where子句里是一个条件表达式，只有符合该条件的行才会被修改。

没有where子句意味着where字句的表达式值为true。也可以同时修改多列，多列的修改中间采用逗号(,)隔开

4.truncate
TRUNCATE TABLE 表名

完全清空一个表。 DDL语句。
DROP、TRUNCATE、DELETE的区别：
delete：删除数据，保留表结构，可以回滚，如果数据量大，很慢
truncate： 删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快
drop： 删除数据和表结构,删除速度最快

5.select

开发环境中不能用 *

简单的SELECT语句:

SELECT {*, column [alias],...}
FROM table;

说明：
–SELECT列名列表。*表示所有列。
–FROM 提供数据源(表名/视图名)
–默认选择所有行

SELECT语句中的算术表达式：

对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。
SELECT last_name, salary, salary*12 FROM employees;

补充：+说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL

运算符的优先级：

乘法和除法的优先级高于加法和减法
同级运算的顺序是从左到右
表达式中使用括号可强行改变优先级的运算顺序
  SELECT last_name, salary, salary*12+100
  FROM employees;
 
  SELECT last_name, salary, salary*(12+100)
  FROM employees;

NULL值的使用：

空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
字符串和null进行连接运算，得到也是null.

补充说明:

安全等于<=>
1.可作为普通运算符的=
2.也可以用于判断是否是NULL 如：where salary is NULL/(is not NULL) ->where salary<=>NULL

示例1：查询emp表奖金为空的员工信息。
select * from emp where comm <=> NULL;

示例2：查询emp表奖金为50000的员工信息
select * from emp where comm <=> 50000;

定义字段的别名：

改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。
  SELECT last_name as “姓名”, salary “薪水”
  FROM employees;
 
  SELECT last_name, salary*12 “年薪”
  FROM employees;

重复记录（distinct）

缺省情况下查询显示所有行,包括重复行
  SELECT department_id
  FROM employees;
 
使用DISTINCT关键字可从查询结果中清除重复行
  SELECT DISTINCT department_id
  FROM employees;
 
DISTINCT的作用范围是后面所有字段的组合
  SELECT DISTINCT department_id , job_id
  FROM employees;

限制所选择的记录：

1.使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
SELECT[DISTINCT] {*, column [alias], ...}
FROM table–[WHEREcondition(s)];
WHERE中的字符串和日期值
	字符串和日期要用单引号扩起来
	字符串是大小写敏感的,日期值是格式敏感的
  SELECT last_name, job_id, department_id
  FROM employees
  WHERE last_name = "king";
 
 
WHERE中比较运算符:
  SELECT last_name, salary, commission_pct
  FROM employees
  WHERE salary<=1500;  
 
2.其他比较运算符
使用BETWEEN运算符显示某一值域范围的记录
 SELECTlast_name, salary
  FROM employees
  WHERE salary BETWEEN 1000 AND 1500;
 
(1)使用IN运算符
使用IN运算符获得匹配列表值的记录
 SELECTemployee_id, last_name, salary, manager_id
  FROM employees
  WHERE manager_id IN (7902, 7566, 7788);
 
(2)使用LIKE运算符
  使用LIKE运算符执行模糊查询
  查询条件可包含文字字符或数字
  (%) 可表示零或多个字符
  ( _ ) 可表示一个字符
    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_A%';
   
(3)使用IS NULL运算符
  查询包含空值的记录
   SELECT last_name, manager_id
   FROM employees
   WHERE manager_id IS NULL;
   逻辑运算符
   
(4)使用AND运算符
    AND需要所有条件都是满足T.
   
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100–4 AND job_id='CLERK';
(5)使用OR运算符
    OR只要两个条件满足一个就可以
 
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100 OR job_id='CLERK';
(6)使用NOT运算符
    NOT是取反的意思
    SELECT last_name, job_id
    FROM employees
    WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');
   
   
3.使用正则表达式:REGEXP
<列名> regexp '正则表达式'
select * from product where product_name regexp '^2018';
 
4.数据分组--GROUP BY
(1)GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
 分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。
 分组函数忽略空值, 结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。
 
 SELECT column, group_function
 FROM table
 [WHERE condition]
 [GROUP BY group_by_expression]
 [ORDER BY column];
 
#每个部门的平均工资
SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno
#查每个部门的整体工资情况
#如果select语句中的列未使用组函数，那么它必须出现在GROUP BY子句中
#而出现在GROUP BY子句中的列，不一定要出现在select语句中
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno #根据部门编号分组
#每个部门每个职位的平均工资
SELECT deptno,job,AVG(sal) FROM TB_EMP GROUP BY deptno,job
 
分组函数重要规则
如果使用了分组函数，或者使用GROUP BY 的查询：出现在SELECT列表中的字段，要么出现在组合函数里，
要么出现在GROUP BY 子句中。
GROUP BY 子句的字段可以不出现在SELECT列表当中。
使用集合函数可以不使用GROUP BY子句，此时所有的查询结果作为一组。
 
(2)数据分组-限定组的结果:HAVING子句
HAVING子句用来对分组后的结果再进行条件过滤。

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BYcolumn];

HAVING子句用来对分组后的结果再进行条件过滤。
#查询部门平均工资大于2000的
#分组后加条件 使用having
#where和having都是用来做条件限定的,但是having只能用在group by之后

SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno
HAVING AVG(sal) > 2000

HAVING与WHERE的区别
WHERE是在分组前进行条件过滤， HAVING子句是在分组后进行条件过滤，WHERE子句中不能使用聚合函数，HAVING子句可以使用聚合函数。

组函数的错误用法
	不能在WHERE 子句中限制组.
	限制组必须使用HAVING 子句.
	不能在WHERE 子句中使用组函数

(4)补充：MySQL 多行数据合并 GROUP_CONCAT
Syntax: GROUP_CONCAT(expr)

示例:fruits表按s_id,将供应水果名称合并为一行数据
 mysql> select s_id,group_concat(f_name)
  -> from fruits
  -> group by s_id;
 +------+-------------------------+
 | s_id | group_concat(f_name)  |
 +------+-------------------------+
 |  101 | apple,blackberry,cherry |
 |  102 | orange,banana,grape   |
 |  103 | apricot,coconut     |
 |  104 | berry,lemon       |
 |  105 | melon,xbabay,xxtt    |
 |  106 | mango          |
 |  107 | xxxx,xbababa      |
 +------+-------------------------+
  7 rows in set (0.00 sec)
 注意：使用 GROUP_CONCAT（）函数必须对源数据进行分组，否则所有数据会被合并成一行
  
  
对结果集排序
  查询语句执行的查询结果，数据是按插入顺序排列
  实际上需要按某列的值大小排序排列
  按某列排序采用order by 列名[desc],列名…
  设定排序列的时候可采用列名、列序号和列别名
  如果按多列排序，每列的asc，desc必须单独设定  
 
 
(5)联合查询
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION   去重复
UNION ALL 不去重复

(6)查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
例子：
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/

MySQL中的通配符：
MySQL中的常用统配符有三个：
   %：用来表示任意多个字符，包含0个字符
   _ : 用来表示任意单个字符
   escape：用来转义特定字符
6.多表关联查询
1. inner join：代表选择的是两个表的交差部分。
  内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。语法如下：
  SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
  
  
2. left join：代表选择的是前面一个表的全部。
  左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。语法如下：
  SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
  
  
3. right join：代表选择的是后面一个表的全部
同理，右连接将会以右边作为基准，进行检索。语法如下：
  SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;


4.自连接
  自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)


5.交叉连接：不适用任何匹配条件。生成笛卡尔积
  联合查询
  说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
  UNION 去重复
  UNION ALL 不去重复
  -- 中国或美国城市信息
  SELECT * FROM city
  WHERE countrycode IN ('CHN' ,'USA');
  SELECT * FROM city WHERE countrycode='CHN'
  UNION ALL
  SELECT * FROM city WHERE countrycode='USA'

六、SQL函数

1.聚合函数
聚合函数对一组值进行运算，并返回单个值。也叫组合函数。
COUNT(*|列名) 		统计行数
AVG(数值类型列名) 	 平均值
SUM (数值类型列名) 	 求和
MAX(列名)	 			最大值
MIN(列名) 			最小值

除了COUNT()以外，聚合函数都会忽略NULL值。
2.数值型函数
ABS 	求绝对值
SQRT 	求平方根
POW 和 POWER 两个函数的功能相同，返回参数的幂次方
MOD 	求余数
CEIL 和 CEILING 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR 	向下取整，返回值转化为一个BIGINT
RAND 	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND 	对所传参数进行四舍五入
SIGN 	返回参数的符号
3.字符串函数
LENGTH 			计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH 	计算字符串长度函数，返回字符串的字节长度，注意两者的区别
CONCAT			合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT(str,pos,len,newstr) 替换字符串函数
LOWER 			将字符串中的字母转换为小写
UPPER 			将字符串中的字母转换为大写
LEFT(str,len) 	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT 			从右侧字截取符串，返回字符串右边的若干个字符
TRIM 			删除字符串左右两侧的空格
REPLACE(s，s1，s2) 	字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len) 	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE 		字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)	  比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
LOCATE(substr,str [,pos]) 返回第一次出现子串的位置
INSTR(str,substr) 	返回第一次出现子串的位置
4.日期和时间函数
SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');
5.流程控制函数
IF(expr,v1,v2) 	判断，流程控制，当expr = true时返回 v1，当expr = false、null 时返回v2
IFNULL(v1,v2) 	判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE 			搜索语句

七、视图

1.视图定义
  • 视图通过以定制的方式显示来自一个或多个表的数据
  • 视图是一种数据库对象，用户可以像查询普通表一样查询视图
  • 视图内其实没有存储任何数据，它只是对表的一个查询
  • 视图的定义保存在数据字典内，创建视图所基于对表称为“基表”
2.视图的好处

作用：

  • 控制安全
  • 保存查询数据

优点：

  • 提供了灵活一致级别安全性。

  • 隐藏了数据的复杂性

  • 简化了用户的SQL指令

  • 通过重命名列，从另一个角度提供数据

3.视图的基本操作

使用规则

  1. 视图必须有唯一命名
  2. 在mysql中视图的数量没有限制
  3. 创建视图必须从管理员那里获得必要的权限
  4. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
  5. 在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY
  6. 视图不能索引，也不能关联触发器或默认值
  7. 视图可以和表同时使
=====1.创建视图=====
create view v_emp_dept as
select emp.*, dept.name deptName, dept.intro deptIntro
from emp,
     dept
where emp.dept_id = dept.id;

=====2.修改视图=====
可以直接使用 create or replace view 进行修改视图
create or replace view v_emp_dept as
select emp.*, dept.name deptNameGai
from emp,
     dept
where emp.dept_id = dept.id;

使用alter view 语句修改 v_emp_dept 视图. 为每个列指定列名.
alter view v_emp_dept (a,b,c,d)
as select emp.*, dept.name deptName
from emp,
     dept
where emp.dept_id = dept.id;

=====3.删除视图=====
drop view 视图名

八、索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。（注意：一般数据库默认都会为主键生成索引）

1.什么是索引

模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

索引的原理

就是把无序的数据变成有序的查询

  1. 把创建的索引的列的内容进行排序
  2. 对排序结果生成倒排表
  3. 在倒排表内容上拼上数据地址链
  4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

2.索引优缺点

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组
和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了

3.索引分类

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快

MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等

4.索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的
索引

  1. 选择惟一性索引
  2. 为经常需要排序、分组和联合操作的字段建立索引
  3. 为常作为查询条件的字段建立索引
  4. 限制索引的数目
  5. 尽量使用数据量少的索引
  6. 尽量使用前缀来索引
  7. 删除不再使用或者很少使用的索引

5.创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种方式，这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句 来创建索引

创建表时创建索引

创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下：
CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);

1 普通索引
# 直接创建索引
CREATE INDEX index_name ON table(column(length))

# 创建表的时候同时创建索引
Create table index1(
    Id int,
    Name varchar(20),
    Sex boolean,
    index(id),
);

# 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

# 查询索引
Show create table index1 \G

# 查询某张表中索引情况
show index from table_name;
# 使用计划查询SQL使用索引情况
Explain select * from index1 where id=1 \G
# 删除索引
DROP INDEX index_name ON table

# 2 创建唯一性索引 ，当然也有多种创建方式
Create table index2(
    Id int unique,
    Name varchar(20),
    Unique index index2_id(id asc)
);

# 3 创建全文索引（FULLTEXT）
# MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；
# 他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，
# 或是随后使用ALTER TABLE 或CREATE INDEX被添加。
# 对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，
# 然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。
# 不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
只能创建在char,varchar或text类型的字段上。
create table index3(
    Id int,
    Info varchar(20),
    Fulltext index index3_info(info)
);
explain select * from table where id=1;
EXPLAIN分析结果的含义:
table：这是表的名字。
type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能
从差到好）
possible_keys：可能可以利用的索引的名字
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

# 4 创建单列索引
Create table index4(
    Id int,
    Subject varchar(30),
    Index index4_st(subject(10))
);

# 5 创建多列索引
使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。
也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，
MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
Create table index5(
    Id int,
    Name varchar(20),
    Sex char(4),
    Index index5_ns(name,sex)
);

# 6 创建空间索引
Create table index6(
    Id int,
    Space geometry not null,
    Spatial index index6_sp(space)
)engine=myisam;
建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括
geometry,point,linestring和polygon类型等。平时很少用到。

create index

首先保证已经存在表，才能使用这个命令创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：help create index
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );

1．创建普通索引
CREATE INDEX index_name ON table(column(length))

2．创建惟一性索引
CREATE UNIQUE INDEX indexName ON table(column(length))

3．创建全文索引
CREATE FULLTEXT INDEX index_content ON article(content)

4．创建单列索引
CREATE INDEX index3_name on index3 (name(10));

5．创建多列索引
6．创建空间索引

ALTER TABLE
用ALTER TABLE语句来创建索引，也是存在表的情况下

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）;

1．创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column(length))

2．创建惟一性索引
ALTER TABLE table_name ADD UNIQUE indexName (column(length))

3．创建全文索引
ALTER TABLE index3 add fulltext index index3_name(name);

4．创建单列索引
ALTER TABLE index3 add index index3_name(name(10));

5．创建多列索引
6．创建空间索引

6.删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。
对于这样的索引，应该将其删除。本节将详细讲解删除索引的方法。

对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下：

DROP INDEX 索引名 ON 表名 ;

九、事务

1.什么是事务

数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部失败

举个例子

比如A用户给B用户转账100操作，过程如下：

1.从A账户扣100
2.给B账户加100

如果在事务的支持下，上面最终只有2种结果：

操作成功：A账户减少100；B账户增加100

操作失败：A、B两个账户都没有发生变化

如果没有事务的支持，可能出现错：A账户减少了100，此时系统挂了，导致B账户没有加上100，而A账户凭空少了100。

2.事务的特性(ACID)

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要 么都不发生。

一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

隔离性Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事 务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性 的，接下来即使数据库发生故障也不应该对其有任何影响。

3.使用事务

MySQL开启事务、回滚事务、提交事务命令

begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令

commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了

rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

自动提交策略：

MySQL默认已经开启自动提交，我们可以通过对应的设置来开启或者关闭自动提交

show variables like 'autocommit'; /*ON为开启立即提交*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

select @@autocommit; /* 1为开启立即提交*/
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

=====设置关闭=====
set autocommit=0;
set global autocommit=0;

注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

隐式提交:

=====用于隐式提交的 SQL 语句
begin
a
b
begin
SET AUTOCOMMIT = 1

导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

开始事务流程:

=====1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';

=====2、开启事务,并结束事务
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;

4.事务的隔离级别

1.脏读

指一个事务读取了另外一个事务未提交的数据。

这是非常危险的，假设Ａ向Ｂ转帐100元，对应sql语句如下所示
1. update account set money=money+100 where name=‘b’;
2. update account set money=money-100 where name=‘a’;
2.不可重复读

不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修 改了该数据，T1为了

对读取值进行检验而再次读取该数据，便得到了不同的结果。

一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事 务也访问该同一数据修

改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那 么第一个事务两次读到的数据可能不一样，

这样就发生了在一个事务内两次读到的数据是不一样的，因 此称为不可重复读，即原始读取不可重复

3.虚读/幻读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

如：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按

照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读。

4.事务隔离级别

数据库共定义了四种隔离级别：

  • Serializable：可避免脏读、不可重复读、虚读情况的发生（串行化）

  • Repeatable read：可避免脏读、不可重复读情况的发（可重复读）

  • Read committed：可避免脏读情况发生（读已提交）

  • Read uncommitted：最低级别，以上情况均无法保证（读未提交）

可以通过命令 set transaction 命令设置事务隔离级别：

set transaction isolation level 设置事务隔离级别

select @@tx_isolation 查询当前事务隔离级别
=====MySql8=====
select @@transaction_isolation 查询当前事务隔离级别
嘻嘻你好呀
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值