MySQL基础语法速查

2023-07-04 08:39:21   0  举报





AI智能生成

MySQL基础语法速查文件是一个包含MySQL数据库常用SQL语句和相关操作的参考手册。它可以帮助用户快速了解和掌握MySQL的基本语法，提高工作效率。该文件通常包括以下内容： 1、创建数据库、表和索引的语句：如CREATE DATABASE、CREATE TABLE、CREATE INDEX等。 2、数据查询语句：如SELECT、INSERT、UPDATE、DELETE等。 3、数据修改语句：如ALTER TABLE、DROP TABLE等。 4、数据管理语句：如GRANT、REVOKE等。 5、数据备份与恢复语句：如BACKUP、RESTORE等。 6、其他常用命令：如SHOW、EXPLAIN等。通过查阅MySQL基础语法速查文件，用户可以快速找到所需的SQL语句并进行相应的操作，从而更好地管理和维护自己的数据库。

数据库

作者其他创作

大纲/内容

1、数据库基本操作

数据库管理

1、查看已有库

show databases;

2、创建库

create database 库名 [character set utf8];

3、查看当前使用的库

select database();

4、切换库

use 库名;

5、删除库

drop database 库名;

数据表管理

基础数据类型

1、数字类型

2、字符串类型

1、创建表

create table 表名(字段名 数据类型 约束,字段名 数据类型 约束,...字段名 数据类型 约束);

2、查看数据表

show tables；

3、查看表结构

desc 表名;

4、查看数据表创建信息

show create table 表名；

5、删除表

drop table 表名;

表数据基本操作

1、插入

插入全部字段：insert into 表名 values (值1，值2...),(值1，值2...),...; 选择字段插入：insert into 表名 (字段1,...) values (值1，值2...),...;

2、查询

select * from 表名 [where 条件]; select 字段1,字段2 from 表名 [where 条件];

3、更新表记录

update 表名 set 字段1=值1,字段2=值2,... where 条件;

4、删除表记录

delete from 表名 where 条件;

5、运算符

算数运算符：+、-、*、/或DIV、%

比较运算符：=、!=、>、>=、<、<=、between、not between、in、not in、is null、is not null

逻辑运算符：not 、and、or

6、时间类型数据

时间类型：date、datetime、timestamp、time、year

日期时间函数：now()、date()、datediff(date1,date2)

2、高级查询语句

模糊查询

SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1

as 用法

select name as 姓名,score as 分数 from class;

排序

SELECT field1, field2,...fieldN from table_name1 where field1 ORDER BY field1 [ASC [DESC]]

分页/限制

SELECT column1, column2, columnN FROM table_name WHERE field LIMIT [num] [OFFSET num];

联合查询

SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

子查询

当一个select 查询语句使用（）括起来，放在其他sql语句中，作为其他sql语句的一部分 是，即是一个子查询语句。

3、聚合操作

聚合函数

聚合分组

group by

聚合筛选

having语句，对分组聚合后的结果进行进一步筛选

去重语句

distinct语句

窗口函数

select 窗口函数 OVER ([partition by 字段名 order by 字段名 ASC|DESC]) from [table];

4、索引操作

索引分类

普通(MUL)

唯一索引(UNI)

主键索引（PRI）

创建索引

1、创建表时直接创建索引： create table 表名( 字段名 数据类型， 字段名 数据类型， index 索引名(字段名)， unique 索引名(字段名)， primary key(字段名) );

2、在已有表中创建索引： create [unique] index 索引名 on 表名(字段名);

3、主键索引添加： alter table 表名 add primary key(id);

4、查看索引： （1）desc 表名;     --> KEY标志为：MUL 、UNI。 （2）show create table [tb];

5、删除索引： drop index 索引名 on 表名; alter table 表名 drop primary key;     # 删除主键

5、外键约束和表关联关系

外键约束

1、外键的定义语法： [constraint symbol] foreign key（外键字段）references tbl_name (主表主键) [on delete {RESTRICT | CASCADE | SET NULL}] [on update {RESTRICT | CASCADE | SET NULL}]

2、创建表时直接创建外键： CREATE TABLE person ( id int PRIMARY KEY AUTO_INCREMENT, name varchar(32) NOT NULL, age tinyint unsigned, salary decimal(10,2), dept_id int , constraint dept_fk foreign key(dept_id) references dept(id) );

3、建表后增加外键： alter table person add constraint dept_fk foreign key(dept_id) references dept(id);

4、通过外键名称解除外键约束： alter table person drop foreign key dept_fk;

表关联关系和E-R模型图

1、表关系

一对多

多对多

2、实体、属性、关系

矩形框代表实体,菱形框代表关系,椭圆形代表属性

表关联查询

1、简单多表查询

select 字段1,字段2... from 表1,表2... [where 条件]

2、内连接

SELECT 字段列表 FROM 表1  inner join 表2 on 表1.字段 = 表2.字段;

3、左连接

SELECT 字段列表 FROM 表1  left join 表2 on 表1.字段 = 表2.字段;

4、右连接

SELECT 字段列表 FROM 表1  right join 表2 on 表1.字段 = 表2.字段;

6、视图

创建视图

create [OR REPLACE] view [view_name] as [SELECT_STATEMENT];

视图表的增删改查操作

视图的增删改查操作与一般表的操作相同

查看现有视图

show full tables in stu;

修改视图

参考创建视图，将create关键字改为alter

删除视图

drop view [视图名];

7、函数和存储过程

函数创建

1、创建函数前，有些数据库系统没有开启创建函数必须的日志管理权限。需要这时需要设置一下： set global log_bin_trust_function_creators=1;

2、 delimiter 自定义符号 create function 函数名(形参列表) returns 返回类型      -- 注意是retruns begin           函数体      -- 若干sql语句，但是不要直接写查询           return val; end  自定义符号 delimiter ;

3、设置变量

定义用户变量

set @[变量名] = 值；使用时用@[变量名]

定义局部变量

在函数内部设置 declare [变量名] [变量类型];  局部变量可以使用set 赋值或者使用into关键字

存储过程创建

1、 delimiter 自定义符号 create procedure 存储过程名(形参列表) begin           存储过程   -- sql语句构成存储过程语句集 end 自定义符号 delimiter ;

2、存储过程三个参数的区别

in类型

out类型

inout类型

存储函数和存储过程的操作

1、调用存储函数

select 存储函数名字（[函数的参数[,……]])

2、调用存储过程

call 存储过程名字（[存储过程的参数[,……]])

3、使用show create语句查看存储过程和函数的定义

show create {procedure|function} 存储过程或存储函数的名称

4、查看所有函数或者存储过程

show procedure|function status where db="stu";

5、删除存储过程或存储函数

drop {PROCEDURE | FUNCTION} 存储过程或存储函数的名称

函数和存储过程区别

1. 函数有且只有一个返回值，而存储过程不能有返回值。 2. 函数只能有普通参数，而存储过程可以有in,out,inout多个类型参数。 3. 存储过程中的语句功能更丰富，实现更复杂的业务逻辑，可以理解为一个按照预定步骤调用的执行过程，而函数中不能展示查询结果集语句，只是完成查询的工作后返回一个结果，功能针对性比较强。 4. 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

8、事务控制

事务操作

1、开启事务

mysql>begin;

2、终止事务

mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束! mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

事务四大特性

1. 原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作, 整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

2. 一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

3. 隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止 多个事务并发执行时由于交叉执行而导致数据的不一致。

4. 持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢 失。

事务隔离级别

1、读未提交：read uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到 这里读取到的数据叫做“脏数据” 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

2、读已提交：read committed

事物A和事物B，事物A提交的数据，事物B才能读取到 这种隔离级别高于读未提交 换句话说，对方事物提交之后的数据，我当前事物才能读取到 这种级别可以避免“脏数据” 这种隔离级别会导致“不可重复读取”

3、可重复读：repeatable read

事务A和事务B，事务A提交之后的数据，事务B读取不到 事务B是可重复读取数据 这种隔离级别高于读已提交 MySQL默认级别 虽然可以达到可重复读取，但是会导致“幻像读”

3、串行化：serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待 这种隔离级别很少使用，吞吐量太低，用户体验差 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串 行，而不并发

9、数据库优化

数据库设计范式

1、第一范式

合理的设计字段 做到不可在分割

2、第二范式

合理的设计主键

3、第三范式

合理的设计外键

.....

MySQL存储引擎

1、基本操作

查看所有存储引擎

mysql> show engines;

查看已有表的存储引擎

mysql> show create table 表名;

创建表指定

create table 表名(...)engine=MyISAM;

已有表指定

alter table 表名 engine=InnoDB;

2、常用存储引擎特点

InnoDB

1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。 2. 支持外键、事务、事务回滚 3. 表字段和索引同存储在一个文件中             1. 表名.frm ：表结构             2. 表名.ibd : 表记录及索引文件

子主题

MyISAM

1. 支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许 2. 表字段和索引分开存储            1. 表名.frm ：表结构            2. 表名.MYI : 索引文件(my index)            3. 表名.MYD : 表记录(my data)

3、如何选择存储引擎

1. 执行查操作多的表用 MyISAM(使用InnoDB加过多的锁浪费资源) 2. 执行写操作多的表用 InnoDB，默认是InnoDB

SQL优化

explain工具

type中包含的值： - system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键. - eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键) - ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 - range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时 发生的情况 - index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 - ALL： 全表扫描，应该尽量避免

SQL语句优化方法

尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引 尽量避免使用 select * ...;用具体字段代替 * ,不要返回用不到的任何字段 尽量控制使用自定义函数 查询最后添加 LIMIT 会停止全表扫描 尽量避免 NULL 值判断,否则会进行全表扫描，默认值为空时可以用默认0代替 优化前：select number from t1 where number is null; 优化后：select number from t1 where number=0; 尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替 优化前：select id from t1 where id=10 or id=20; 优化后： select id from t1 where id=10 union all select id from t1 where id=20; 尽量避免使用 in 和 not in,否则会全表扫描 优化前：select id from t1 where id in (1,2,3,4); 优化后：select id from t1 where id between 1 and 4;

10、数据备份和导入

表内容复制

create table 表名 select 查询命令;

数据库备份

1、备份命令格式

mysqldump  -u   用户名  -p   源库名  > stu.sql

2、恢复命令格式

mysql   -u  root  -p  目标库名 < stu.sql

11、用户与权限

添加用户权限

1、用root用户登录mysql

mysql -u root -p 密码

2、添加用户 % 表示自动选择可用IP

create user 'username'@'host' identified by  'password';

3、权限管理

增加权限

grant 权限列表 on 库.表 to "用户名"@"%" with grant option;

删除权限 注意该操作指定的库.表必须与grant时写法一致

revoke insert,update,select on 库.表 from 'user'@'%';

4、刷新权限

flush privileges;

5、删除用户

drop user "用户名"@"%"

权限列表

all privileges ，select ，insert ，update，delete，alter，create，drop等。 库.表 ： *.* 代表所有库的所有表

12、pymysql模块

第三方库pymysql安装

sudo pip3 install pymysql

pymysql使用流程

1. 建立数据库连接：db = pymysql.connect(...) 2. 创建游标对象：cur = db.cursor() 3. 游标方法: cur.execute("insert ....") 4. 提交到数据库或者获取数据 : db.commit() / cur.fetchall() 5. 关闭游标对象 ：cur.close() 6. 断开数据库连接 ：db.close()

常用函数

db = pymysql.connect(参数列表) 功能: 链接数据库           host ：主机地址,本地 localhost           port ：端口号,默认3306           user ：用户名           password ：密码           database ：库           charset ：编码方式,推荐使用 utf8 cur = db.cursor() 功能： 创建游标 返回值：返回游标对象,用于执行具体SQL命令 cur.execute(sql,args_list) 功能： 执行SQL命令 参数： sql sql语句 args_list 列表，用于给sql语句传递参量 cur.executemany(sql命令,args_list) 功能： 多次执行SQL命令，执行次数由列表中元组数量决定 参数： sql sql语句 args_list 列表中包含元组 每个元组用于给sql语句传递参量，一般用于写操作。 db.commit() 提交到数据库执行，必须支持事务操作才有效 db.rollback() 回到原来的数据形态，必须支持事务操作才有效 cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))，查询不 到内容返回空元组。 cur.fetchall() 获取所有查找到的记录，返回结果形式同上。 cur.close() 关闭游标对象 db.close() 关闭数据库连接