半小时掌握MySQL数据库语法

临风倜傥
1,292 阅读10分钟

MySql命令

登录

mysql -u root -p password

使用

USE 数据库名称;

查看

//查看所有数据库
SHOW DATABASES;
//查看数据库创建信息
SHOW CREATE DATABASE 数据库名称;
//查看当前所使用的的数据库
SELECT DATABASE();

创建

//创建数据库
CREATE DATABASE 数据库名称;
//创建数据库并设置编码；
CREATE DATEBASE 数据库名称1 CHARACTER SET gbk;
//输入数据库 数据库不存在则创建。
CREATE DATABASE IF NOT EXISTS 数据库名称2;

修改

ALTER DATABASE 数据库名称 CHARACTER SET utf8;

删除

DROP DATABASE 数据库名称;

数据库查询

基本查询语法

#基础语法
SELECT 列名 FROM 表名
#部分列
SELECT 列名1,列名2,列名3 FROM 表名
#所有列
SELECT * FROM 表名

列名操作

列的别名

SELECT 列 AS '列名' FROM 表名

查询结果去重

SELECT DISTINCT 列名 FROM 表名

操作表查询

排序查询

SELECT 列名 FROM 表名 ORDER BY 排序列[排序规则]
排序规则描述
ASC升序排序
DESC降序排序

条件查询

SELECT 列名 FROM 表名 WHERE 条件
条件描述
等值判断=
逻辑判断and、or、not
不等值判断>、>、>=、<=、!=、<>
区间判断列名 BETWEEN 值1 AND 值2
NULL值判断列名 IS NULL或者列名 IS NOT NULL
枚举查询IN(值1,值,值3)
模糊查询列名 LIKE '值1_' 或 列名 LIKE '唐%'

分支结构查询

CASE
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	WHEN 条件3 THEN 结果3
	ELSE 结果
END

分组查询

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列);

分组查询中，SELECT选择的列只能是分组依据，或者聚合函数列，不能出现其他列

分组过滤查询

SELECT 列名 FROM 表名 WHERE 条件 GROUNP BY 分组列 HAVING 过滤规则
关键字说明
HAVING过滤规则过滤规则定义对分组后的数据进行过滤

分页查询

SELECT 列名 FROM 表名 LIMIT 起始行,查询行数

子查询

条件判断

SELECT 列名 FROM 表名 WHERE 条件(子查询结果)
  • 注意：将子查询“一行一列”的结果作为外部查询的条件，做第二次查询。
  • 子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值判断条件

枚举查询条件

SELECT 列名 FROM 表名 WHERE 列名 IN (子查询结果);

将子查询“多行一列”的结果作为外部查询的枚举查询条件，做第二次查询

注意：当子查询结果集形式为多行单列时可以使用ANY或ALL关键字

一张表

SELECT 列名 FROM (子查询的结果集) WHERE 条件;
  • 将子查询“多行多列“的结果作为外部查询的一张表，做第二次查询。
  • 注意：子查询作为临时表，需要为其赋予一个临时表名

函数查询

时间查询

SELECT 时间函数([参数列表]) from 表名
时间函数描述
SYSDATE()当前系统时间（日、月、年、时、分、秒）
CURDATE()获取当前日期
CURTIME()获取当前时间
WEEK(DATE)获取指定日期为一年中的第几周
YEAR(DATE)获取指定时间的年份
HOUR(TIME)获取指定时间的小时值
MINUTE(TIME)获取指定时间的分钟值
DATEDIFF(DATE1,DATE2)获取DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N)计算DATE加锁N天后的日期

经验：执行时间函数查询，会自动生成一张虚表（一行一列）

字符串查询

SELECT 字符串函数([参数列表]) from 表名
字符串函数说明
CONCAT(str1,str2,…)将多个字符串连接
INSERT(str,post,len,newStr)将str中指定post位置开始len长度的内容替换为newStr
LOWER(str)将指定字符串转换为小写
UPPER(str)将指定字符串转换为大写
SUBSTRING(str,num.len)将str字符串指定num位置开始截取len个内容

聚合函数

SELECT 聚合函数(列名) FROM 表名;
聚合函数说明
SUM()求所有行中单列结果的总和
AVG()平均值
MAX()最大值
MIN()最小值
COUNT()求总行数

经验：对多条数据的单列进行统计，返回统计后的一行结果

多表查询

合并查询

SELECT * FROM 表名1 UNION SELECT * FROM 表名2
SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2
  • 注意：合并结果的两张表，列数必须相同，列的数据类型可以不同。

表连接查询

SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

多表连接查询

SELECT 列名 FROM 表1 
连接方式1 表2 ON 连接条件 
连接方式2 表3 ON 连接条件

内连接查询

SELECT 列名 FROM 表1 INNER JOIN 表2 ON 连接条件

左外连接

SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 连接条件
  • 注意：左外连接，是以左表作为主表，依次向右匹配，匹配到则返回结果；
  • 匹配不到则返回NULL值填充

右外连接

SELECT 列名 FROM 表1 RIGHT JOIN 表2 ON 连接条件
  • 注意：右外连接，是以右表为主表，依次向左匹配，匹配到返回结果。
  • 匹配不到，则返回NULL值填充

查询总结

SQL语句编写顺序

SELECT 列名 FROM 表名 
WHERE 条件 
GROUP BY 分组 HAVING 过滤条件 
ORDER BY 排序列(ASC|DESC) 
LIMIT 起始行,查询行数

SQL语句执行顺序

1.FROM:指定数据来源表
2.WHERE:对查询数据第一次过滤
3.GROUP BY:分组
4.HAVING:对分组后的数据第二次过滤
5.SELECT:查询各字段的值
6.ORDER BY:排序
7.LIMIT:限定查询结果

DML操作

新增

INSERT INTO 表名(列1,列2,列3......) VALUES(值1,值2,值3......);

注意：表名后的列名要和VALUE里的值一一对应（个数、顺序、类型）

修改

UPDATE 表名 SET 列1 = 值1,列2 = 值2,列3 = 值3 WHERE 条件;
  • 注意：SET后多个列名=值，绝大多数情况下都要加WHERE条件指定修改，否则为整表更新。

删除

DELETE FROM 表名 WHERE 条件;
  • 注意：删除时，如果不加条件，则默认删除整张表。

清空整表数据

TRUNCATE TABLE 表名;
  • 注意：与DELETE不加WHERE删除整表数据不同，TRUNCATE是把原表销毁，再按原表的格式创建一张新表。

数据表操作

数据类型

数值类型

类型大小范围（有符号）范围（无符号）用途
INT4字节(-2147483648,2147483647)(0,4294967295)大整数值
DOUBLE8字节(-1.797E+308,-2.22E-308)0,(2.22E-308,1.797E+308)双精度浮点数值
DOUBLE(M,D)8字节，M表示长度，D表示小数位数同上，受M和D的约束；如DOUBLE(5,2) -999.99~999.99同上，受M和D的约束双精度浮点数值
DECIMAL(M,D)DECIMAL(M,D)依赖于M和D的值，M最大值为65依赖于M和D的值，M最大值为65小数值

日期类型

类型大小范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DETETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳

字符串类型

类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
BLOB（binary large object）0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据

数据表创建

CREATE TABLE 表名(
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    ......
    列名 数据类型 [约束]#最后一行的末尾不加逗号
)[CHARSET=UTF8]#可根据需要指定表的字符编码集

数据表的修改

ALTER TABLE 表名 操作;

添加列

ALTER TABLE 表名 ADD 列名 列的选项参数;

修改列

ALTER TABLE 表名 MODIFY 列名 列的选项参数;
  • 注意：修改表中的某列时，也要写全列的名字，数据类型，约束。

删除列

ALTER TABLE 表名 DROP 列名;

注意：删除列时，每次只能删一列

修改列名

ALTER TABLE 表名 CHANGE 列名 新列名 新列的选项参数;
  • 注意：修改列名时，在给定列新名称时，要指定列的类型和约束。

修改表名

ALTER TABLE 表名 RENAME 新表名;

数据表的删除

DROP TABLE 表名

约束

实体完整性约束

主键约束

标识表中的一行数据，此列的值不可重复，且不能为NULL

PRIMARY KEY

唯一约束

标识表中的一行数据，不可重复，可以为NULL

UNIQUE

自动增长列

主键数值列添加自动增长。从1开始，每次加1

AUTO_INCREAMENT

域完整性约束

非空约束

NOT NULL

默认值约束

DEFAULT

引用完整性约束

CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)
  • FOREIGN KEY引用外部表某个列的值，新增数据时，约束此列的值必须是引用表中存在的值
  • 当两张表存在引用关系时**，要执行删除操作，一定要先删除从表（引用表），再删除主表（**被引用表）

事务

#开启事务
START TRANSACTION;
#localhost连接，提交事务
COMMIT;
#localhost连接，事务回滚
ROLLBACK;

事务的特性（ACID）

  • Atomicity（原子性）

    表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。

  • Consistency（一致性）

    表示一个事务内有一个操作失败时，所有更改过的数据都必须回滚到修改前状态。

  • Isolation（隔离性）

    事务查看数据库操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事物修改它之后的状态，事务不会查看中间状态的数据。

  • Durability（持久性）

    持久性事务完成之后，它对与系统的影响是永久性的。

权限管理

创建用户

CREATE USER 用户名 IDENTIFIED BY 密码

授权

GRANT ALL ON 数据库.表 TO 用户名;

撤销权限

REVOKE ALL ON 数据库.表名 FROM 用户名

注意：撤销权限后，账户需要重新连接客户端才会生效

删除用户

DROP USER 用户名

视图

视图的创建

CREATE VIEW 视图名 AS 查询数据源表结构语句;

视图的修改

方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句
方式二：ALTER VIEW 视图名 AS 查询语句

视图的删除

DROP VIEW 视图名
  • 注意：删除视图不会影响原表。

视图注意

  • 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
  • 如果视图包含以下结构中的一种，则视图不可更新：
    • 聚合函数的结果
    • DISTINCT去重后的结果
    • GROUP BY分组后的结果
    • HAVING筛选过滤后的结果
    • UNION、UNION ALL联合后的结果

触发器

  • 监视地点(table表)
  • 监视事件(insert | update | delete)
  • 触发时间(before | after)
  • 触发事件(insert | update | delete)

创建触发器

# 创建触发器
CREATE TRIGGER 触发器名
  BEFORE 或 AFTER # 触发时间
  INSERT 或UPDATE 或 DELETE # 监视事件
  ON 表名 # 监视地点
  FOR EACH ROW #在mysql中必须写，行级触发器，在oracle可以不写，表示语句级触发器
  BEGIN # 开始触发
    sql语句1 sql语句2 ......
  END # 结束触发

查看触发器

SHOW TRIGGERS;

删除触发器

DROP TRIGGER 触发器名;

参考

【千峰】一天学会 MySQL 数据库

【黑马】MySQL学习笔记

avatar
文章
阅读
粉丝