MySQL入门基础语法【韩顺平老师MySQL课程的学习笔记】

MySQL入门基础语法

1. 数据库

1.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name
	[create_specification [,create_specification]... ]

create_specification:

[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name

• character set :指定数据库采用的字符集，如果不指定字符集，默认utf8；

• COLLATE : 指定数据库字符集的校对规则（常用的utf8_bin[区分大小写]、utf8_general_ci [ 不区分大小写 ] 默认是utf8_general_ci；

# 模板
DROP SCHEMA IF EXISTS table_name;
CREATE SCHEMA IF NOT EXISTS table_name default character set gbk;
USE  table_name ;

1.2 查看、删除数据库

显示数据库语句

SHOW DATABASES

显示数据库创建语句

SHOW CREATE DATABASE  db_name

数据库删除语句

DROP DATABASE [IF EXISTS] db_name

1.3 备份、恢复数据库

备份数据库（注意：在DOS执行）

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

恢复数据库（注意：进入SQLyog再进行）

Source 文件名.sql

备份数据库的表

mysqldump -u root -p 密码 数据库 表1 表2 表n > d:\\文件名.sql

2. 表

2.1 创建表

CREATE TABLE table_name
（			
	field1 datatype,			
    field2 datatype,
    field3 datatype
）character set 字符集 collate校对规则 enginey引擎

filed:指定列名 datatype 指定列类型（字段类型）

character set:如不指定则为所在数据库字符集

collate:如不指定则为所在数据库校对规则

2.2 删除表

DROP TABLE [表名]

2.3 修改表

使用 ALTER TABLE 语句追加，修改，或删除列的语法。

添加列

ALTER TABLE tablename
ADD {column datatype  [DEFAULT  expr]			[,column datatype ]...};

修改列

ALTER TABLE tablename
MODIFY {column datatype  [DEFAULT  expr]			[,column datatype ]...};

删除列

ALTER TABLE tablename
DROP  (column);
查看表的结构：desc 表名； --可以查看表所有的列

修改表名

Rename table 表名 to 新表名

修改表字符集

alter table 表名 character set 字符集;

3. Mysql常见数据类型

3.1 数值型（整数）的基本使用

说明，使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型。

3.2数值型（小数）的基本使用

• FLOAT/DOUBLE [UNSIGNED]

  FLOAT 单精度、DOUBLE双精度

• DECIMAL[M,D] [UNSIGNED]

  可以支持更加精确的小数位，M是小数位数，D是小数点（标度）后面的位数，

  如果D是0，则值没有小数点或分数部分。M最大65，D最大30。如果D被省略，默认是0.如果M被省略，默认是10。

3.3 字符串的基本使用

CHAR(size) 固定长度字符串 最大255字符

VARCHAR(size) 0~65535 可变长度字符串 最大65532字节 【utf8编码最大21844字符 1-3字节用于记录大小】

3.4 日期类型的基本使用

CREATE TABLE birthday6(
    t1 DATE,
    t2 DATETIME,
    t3 TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP );timestamp时间戳

mysql> INSERT INTO birthday(t1,t2) VALUES('2022-5-21','2022-5-21 10:10:10');

细节说明：TimeStamp在Insert和update时，自动更新。

4. CRUD

4.1 insert

使用 INSERT 语句向表中插入数据

INSERT INTO table_name [(column [,column...])]
VALUES   (value [,value...]);

4.2 Update

使用 Update 语句修改表中数据

UPDATE  tbl_name SET col_name1=expr1 [,col_name2=expr2...] [WHERE where_definition]

使用细节：

• UPDATE语句可以用新值更新原有表行中的各列。

• SET字句指示要修改哪些列和要给予哪些值。

• WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行（记录）。

• 如果要修改多个字段，可以通过 set 字段1=值1，字段2=值2…

4.3 Delete

使用delete语句删除表中的数据

delete from tbl_name [WHERE where_definition]

使用细节：

• 如果不使用where子句 ，将删除表中所有的数据。

• Delete语句不能删除某一列的值（可使用 update 设为 null 或者“）

• 使用delete语句仅删除记录，不删除表本身，如要删除表，使用drop table 语句。drop table 表名；

4.4 Select

4.4.1 单表查询

SELECT [DISTINCT] * | {column1,column2,column3...}
		FROM tablename;

注意事项：

• column指定列名。

• DISTINCT可选，指显示结果时，是否去掉重复数据。

使用表达式对查询的列进行运算

SELECT  * | {column1 | expression, column2 | expression...}
	FROM  tablename;

在select语句中可使用as语句

SELECT columnname as 别名 from 表名；

在where子句中经常使用的运算符

使用 order by 子句排序查询结果

SELECT column1,column2,column3...
 	FROM table;
	order by column asc |desc,...

• Order by 指定排序的列 ，排序的列既可以是表中的列名，也可以是select语句后指定的列名
• Asc 升序（默认）、Desc 降序
• Order by 子句应位于SELECT 语句的结尾

4.4.2 查询加强

• 分页查询

  基本语法：

  select ...limit start,rows
  

  表示从start+1行开始取，取出rows行，start从0开始计算

  公式：

  SELECT * FROM tablename 
  ORDER BY tablename				
  LIMIT 每页显示记录数 * （第几页-1），每页显示记录数
  

• 数据分组

  SELECT column1,column2,column3... FROM TABLE
  GROUP BY COLUMN
  HAVING CONDITION
  ORDER BY COLUMN
  LIMIT START,ROWS;
  

4.4.3 多表查询

• 自连接

  自连接是指在同一张表的连接查询（将同一张表看作两张表）。

4.4.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

• 单行子查询

  单行子查询是指只返回一行数据的子查询语句。

• 多行子查询

  多行子查询是指只返回多行数据的子查询 使用关键字 in

• 在多行子查询中使用any操作符

  如何显示工资比部门30的其中一个员工的工资高的员工姓名、工资和部门号。

  select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30)
  或
  select ename,sal,deptno from emp where sal>select min(sal) from emp where deptno=30)
  

• 在多行子查询中使用all操作符

  显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

  select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30)
  

4.4.5合并查询

• 介绍

  有时在实际应用中，为了合并多个select语句的结果，可以结合集合操作符号union，union all

  1）union all

  该操作符用于取得两个结果集的并集，当使用该操作符时，不会取消重复行。

  select ename,sal,job from emp where sal>2500	--3条结果
  select ename,sal,job from emp where job='manager'	--5条结果
  
  select ename,sal,job from emp where sal>2500
  union all
  select ename,sal,job from emp where job='manager'	--8条结果,不会取消重复行
  

  2） union

  该操作符与union all 相似，但是会自动去掉结果集中重复行

  select ename,sal,job from emp where sal>2500 
  union
  select ename,sal,job from emp where job='manager'   --5条结果，取消重复行
  

5. 函数

5.1 统计函数

合计/统计函数-count

Count 返回行的总数

Select  count(*)  | count (列名)  from tablename
	   (WHERE where_definition)

Sum 函数返回满足where条件的行的和 - 一般使用在数值列

Select sum(列名) {， sum(列名)...} from tablename
WHERE where_definnition]

Max/min函数返回满足where条件的一列的最大/最小值

Select max(列名)  from  tablename
	[WHERE  where_definnition]

合计函数 - avg

AVG函数返回满足where条件的一列的平均值

Select avg(列名) {， avg(列名)...}from tablename
	[WHERE where_definnition]

使用group by 子句对列进行分组

SELECT column1,column2,column3..FROM table
	group by column

使用having子句对分组后的结果进行过滤

SELECT column1，column2，column3..
	FROM table
	group by column having...

group by 用于对查询的结果分组统计。

having子句用于限制分组的显示结果。

5.2 时间日期

5.3 字符串函数

5.4 数学函数

5.5 加密函数

SELECT USER() FROM DUAL;  --用户@IP地址
SELECT DATABASE();  --数据库名称
SELECT MD5('123456') FROM DUAL;  --root密码是123456->加密md5->在数据库存放的是加密后的密码
SELECT PASSWORD('123456') FROM DUAL;

5.6 流程控制函数

6. 内连接

7. 外连接

• 左外连接 （如果 左侧的表完全显示 我们就说是左外连接）。

  select .. from 表1 left join 表2 on 条件
  

• 右外连接 （如果 右侧的表完全显示 我们就说是左外连接）。

  select .. from 表1 right join 表2 on 条件
  

8. 约束

• 基本介绍

  约束用于确保数据库的数据满足特定的商业规则。

  在mysql中，约束包括：not null、unique、primary key、foreign key和check 五种。

8.1 not null

如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

字段名 字段类型 not null

8.2 primary key

• 基本使用

  字段名 字段类型 primary key
  

  用于唯一的标示表行的数据，当定义主键约束后，该列不能重复。

• 细节说明

  1） primary key不能重复而且不能为null

  2）一张表最多只能有一个主键，但可以是复合主键。

  create table tablename(
  	id int ,
      name varchar(32),
      email varchar(32),
      primary key (id,name));--复合主键
  

  3）主键的指定方式有两种：

  直接在字段名后指定：字段名 primary key

  在表定义最后写 primary key(列名)；

  4）使用 desc 表名，可以看到primary key的情况。

8.3 unique

当定义了唯一约束后，该列值是不能重复的。

字段名 字段类型 unique

细节：

1） 如果没有指定 not null，则unique字段可以有多个null。

如果一个列（字段），是unique not null 使用效果类似 primary key

2）一张表可以有多个unique字段。

8.4 foreign key

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null（学生/班级）

FOREIGN KEY(本表字段名) REFEREENCES
主表名（主键名或是为unique字段名）

细节说明：

1）外键指向的表的字段，要求是primary key 或者是 unique。

2）表的类型是innodb，这样的表才支持外键。

3）外键字段的类型要和主键字段的类型一致（长度可以不同）。

4）外键字段的值，必须为主键字段中出现过，或者为null [ 前提是外键字段允许为null ]。

5）一旦建立外键的关系，数据不能随意删除了。

8.5 check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求列值在1000~2000之间， 如果不在1000~2000之间就会提示出错。

基本语法：

列名  类型 check (check条件)

8.6 自增长

字段名 整形 primary key auto-increment

添加自增长的字段方式

insert into xxx(字段1,字段2...) values(null,'值'...);
insert into xxx(字段2...)values('值1','值2');
insert into xxx values(null,'值1',...);

自增长使用细节

1） 一般来说自增长是和primary key 配合使用的

id int primary key auto_increment

2）自增长也可以单独使用【但是需要配合一个unique】

3）自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用）

4）自增长默认从1开始，你也可以通过如下命令修改alter

table 表名 auto_increment = 新的开始值;

5）如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据。

9. 索引

• 索引的类型

1） 主键索引 ，主键自动的为主索引（类型Primary）

create table t1(
	id int primary key,-- 主键，同时也是索引，称为主键索引
    name varchar(32));

2） 唯一索引（UNIQUE）

create table t2(
	id int unique,-- id是唯一的，同时也是索引，称为unique索引
    name varchar(32));

3） 普通索引（INDEX）

4） 全文索引(FULLTEXT)【适用于MyISAM】

一般开发中，不使用MySQL自带的全文索引，而是使用：全文搜索Solr 和 ElasticSearch (ES)

• 索引使用：

1）添加索引

create [UNIQUE] index index_name on tbl_name (col_name [(length)] [ASC | DESC],...);
alter table table_name ADD INDEX [index_name] (index_col_name,...)

-- 添加唯一索引
create unique index id_index on t25 (id);
-- 添加普通索引
create index id_index on t25 (id);
-- 添加普通索引方式2
alter table t25 ADD INDEX id_index (id);
-- 如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引

2）添加主键（索引）

ALTER TABLE 表名 ADD PRIMARY KEY（列名,...);

3）删除索引

DROP INDEX index_name ON tbl_name;
alter table table_name drop index index_name;

4）删除主键索引

alter table t_b drop primary key;

5）查询索引（三种方式）

show index(es) from table_name;
show keys from table_name;
desc table_Name;

10. 事务

10.1 事务管理

• 什么是事务？

事务用来保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用来保证数据的一致性。

• 事务和锁

当执行事务操作时（dml语句），MySQL会在表上加锁，防止其他用户改表的数据。

mysql数据库控制台事务的几个重要操作：

start transaction -- 开始一个事务
savepoint 保存点名 -- 设置保存点
rollback to 保存点名 -- 回退事务
rollback -- 回退全部事务
commit -- 提交事务，所有的操作生效，不能回退

• 事务细节讨论

  1）如果不开始事务，默认情况下，dml操作是自动提交的们不能回滚。

  2）如果开始一个事务，你没有创建一个保存点，你可以执行rollback，默认就是回退到你事务开始的状态。

  3）你也可以在这个事务中（还没有开始提交时），创建多个保存点。比如：savepoint aaa；执行dml，savepoint bbb;

  4）你可以在事务没有提交前，选择回退到哪个保存点。

  5）mysql的是事务机制需要innodb的存储引擎还可以使用，myisam不好使。

  6）开始一个事务 start transaction ,set autocommit=off;

10.2 rollback

在介绍回退事务前，先介绍一下保存点。保存点是事务中的点，用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

10.3 commit

使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务之后，其他会话将可以查看事务变化后的新数据。

10.4 隔离级别

• 事务隔离级别的介绍

  1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作员，以保证各个连接在获取数据时的准确性。

  2. 如果不考虑隔离性，可能会引发如下问题：

  1）脏读

  2） 不可重复读

  3）幻读

• 查看事务隔离级别

  脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读。

  不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。

  幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

​ 说明：V可能出现 ，x不会出现

​

1. 查看当前会话隔离级别

   select @@tx_isolation;	-- mysql5.7版本
   

2. 查看系统当前隔离级别

   select @@global.tx_isolation;
   

3. 设置当前会话隔离级别

   set session transaction isolation lever repeatable read; -- 设置为可重复读
   

4. 设置系统当前隔离级别

   set global transaction isolation lever repeatable read; -- 设置为可重复读
   

5. mysql默认的事务隔离级别是 repeatable read,一般情况下，没有特殊要求，没有必要修改。

10.5 ACID

• 原子性（Atomicity)

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

• 一致性（Consistency)

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

• 隔离性（Isolation)

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

• 持久性 (Durability)

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

11. 视图

• 视图的基本使用

  1.create view 视图名 as select语句
  2.alter view 视图名 as select语句
  3.SHOW CREATE VIEW 视图名
  4.drop view 视图名1，视图名2
  

• 视图最佳实践

  1. 安全。一些数据表有着重要的信息，有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
  2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN），这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用（JOIN）查询数据。
  3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃，然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

12. MySQL管理

12.1 Mysql用户管理

• Mysql用户

  mysql中的用户，都存储在系统数据库mysql中user表中

  

  其中user表的重要字段说明：

  1. host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定IP地址，比如192.168.1.100

  2. user：用户名；

  3. authentication_string：密码，是通过mysql的password()函数加密之后的密码。

• 创建用户

  create user '用户名'@'允许登录位置' identified by '密码'
  

  说明：创建用户，同时指定密码

• 删除用户

  drop user '用户名'@'允许登录位置';
  

12.2 Mysql权限管理

• 给用户授权

  基本语法：

  grant 权限列表 on 库.对象名 to '用户名' @ '登录位置' [identified by '密码']
  

  说明：

  1. 权限列表，多个权限用逗号分开

  grant select on .....
  grant select ,delete,create on ....
  grant all [privileges] on .... //表示赋予该用户在该对象上的所有权限
  

  2. 特别说明

  *.*：代表本系统中的所有数据库的所有对象（表，视图，存储过程）
  库.*：表示某个数据库中的所有数据对象（表，视图，存储过程）
  

  3. identified by 可以省略，也可以写出。

  （1）如果用户存在，就是修改该用户的密码。

  （2）如果该用户不存在，就是创建该用户。

• 回收用户授权

  基本语法：

  revoke 权限列表 on 库.对象名 from '用户名"@"登录位置';
  

• 权限生效指令

  如果权限没有生效，可以执行下面命令。

  基本语法：

  FLUSH PRIVILEGES;
  

  细节说明：

  1. 在创建用户的时候，如果不指定Host，则为%，%表示所有的IP都有连接权限

   create user xxx;
  

  2. 你也可以这样指定

  create user 'xxx'@'192.168.1.%' 表示xxx用户在192.168.1.* 的IP可以登录MySQL
  

  3. 在删除用户的时候，如果host不是%，需要明确指定 ’用户‘@ ’host值‘。

13 mysql表类型和存储引擎

• 基本介绍

  1. MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MylSAM、innoDB、Memory等。
  2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
  3. 这六种又分为两类，一类是“事务安全型”（transaction-safe)，比如：InnoDB;其余都属于第二类，称为“非事务安全型”（non-transaction-safe)[mysiam 和 memory]。

• 主要的存储引擎/表类型特点

  

• 细节说明

  这里重点介绍三种：MyISAM、InnoDB、MEMORY

  1. MyISAM不支持事务，也不支持外键，但其访问速度快，对事物完整性没有要求。
  2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
  3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。

• 如何选择表的存储引擎

  1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快。

  2. 如果需要支持事务，选择InnoDB。

  3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态()。）

• 修改存储引擎

  ALTER TABLE '表名' ENGINE = 存储引擎;