mysql-基础-SQL

SQL通用语法

单行注释：-- 或 #

多行注释：/* 注释内容 */

SQL

DDL 数据定义语言 定义数据库对象（数据库，表，字段）

DML 数据操作语言 对数据库表中的数据进行增删改

DQL 数据查询语言 用来查询数据库中表的记录

DCL 数据控制语言 用来创建数据库用户、控制数据库的访问权限

DDL-数据库操作

查询

• 查询所有数据库

  show databases;

• 查询当前数据库

  select database();

创建

• 创建数据库

  create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
  if not exists:如果不存在则创建，如果存在则不创建

删除

• 删除数据库

  drop database [if exists] 数据库名;

使用

• 使用数据库

  use 数据库名;

DDL-表操作

查询

• 查询当前数据库所有表

  show tables;

• 查询指定数据库所有表

  show table from 数据库名;

• 查询表结构

  desc 表名;

• 查询指定表的建表语句

  show create table 表名;

创建

• 创建数据表

  create table 表名 ( 字段1 字段1类型 [comment 字段1注释], 字段2 字段2类型 [comment 字段2注释], … 字段n 字段n类型 [comment 字段n注释] ) [comment 表注释];

数据类型

数值类型

类型	大小	有符号(signed)范围	无符号(unsigend)范围	描述
tinyint	1 byte	(-128,127)	(0,255)	小整数值
smallint	2 bytes	(-32768,32767)	(0,65535)	大整数值
mediumint	3 bytes	(-8388608,8388607)	(0,16777215)	大整数值
int或integer	4 bytes	(-2147483648,2147483647)	(0,4294967295)	大整数值
bigint	8 bytes	(-2^63,2^63-1)	(0,2^64-1)	极大整数值
float	4 bytes	(-3.4082823466 E+38,3.402823466 E+38)	0 和 (1.175494351 E-38,3.402823466 E+38)	单精度浮点数值
double	8 bytes	(-1.7976931348623157 E+308,1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308)	双精度浮点数值
decimal		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

字符串类型

类型	大小	描述
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
tinyblob	0-255 bytes	不超过255个字符的二进制数据
tinytext	0-255 bytes	短文本字符串
blob	0-65535 bytes	二进制形式的长文本数据
text	0-65535 bytes	长文本数据
mediumblob	0-16777215 bytes	二进制形式的中等长度文本数据
mediumtext	0-16777215 bytes	中等长度文本数据
longblob	0-4294967295 bytes	二进制形式的极大文本数据
longtext	0-4294967295 bytes	极大文本数据

日期时间类型

类型	大小	范围	格式	描述
date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
year	1	1901 至 2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

修改

• 添加字段

  alter table 表名 add 字段名 类型(长度) [约束] [comment 注释];

• 修改数据类型

  alter table 表名 modify 字段名 新字段类型 [约束] [after/first] [comment 注释];

• 修改字段名和字段类型

  alter table 表名 change 旧字段名 新字段名 类型(长度) [约束] [comment 注释];

• 删除字段

  alter table 表名 drop 字段名;

• 修改表名

  alter table 表名 rename to 新表名;

删除

• 删除表

  drop table [if exists] 表名;

• 删除指定表，并重新创建该表

  truncate table 表名;

DML

添加数据

• 给指定字段添加数据

  insert into 表名 (字段名1，字段名2,…) values (值1,值2,…);

• 给全部字段添加数据

  insert into 表名 values (值1,值2,…);

• 批量添加数据

  insert into 表名 (字段名1,字段名2,…) values (值1,值2,…),(值1,值2,…);

  insert into 表名 values (值1,值2,…),(值1,值2,…);

修改数据

• 修改数据

  update 表名 set 字段名1 = 值1,字段名2 = 值2,… [where 条件];

删除数据

• 删除数据

  delete from 表名 [where 条件];

DQL

语法

select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数

基本查询

• 查询多个字段

  select 字段1,字段2,字段3 … from 表名;

  select * from 表名;

• 设置别名

  select 字段1 [as 别名1],字段2 [as 别名2] … from 表名;

• 去除重复记录

  select distinct 字段列表 from 表名;

条件查询

• 语法

  select 字段列表 from 表名 where 条件列表;

• 条件

  比较运算符	功能
  >	大于
  >=	大于等于
  <	小于
  <=	小于等于
  =	等于
  <> 或 !=	不等于
  between ... and ...	在某个范围之内(含最小、最大值)
  in(...)	在in之后的列表中的值，多选一
  like 占位符	模糊匹配(_匹配单个字符，%匹配任意个字符)
  is null	是NULL

  ---

  逻辑运算符	功能
  and 或 &&	并且(多个条件同时成立)
  or 或 ||	或者(多个条件任意一个成立)
  not 或 !	非，不是

聚合查询

• 常见聚合函数

  函数	功能
  count	统计数量
  max	最大值
  min	最小值
  avg	平均值
  sum	求和

        select 聚合函数(字段列表) from 表名;

        注意： null值不参与所有聚合函数运算。

分组查询

• 分组查询

  select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

  区别:

  1.where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

  2.where不能对聚合函数进行判断，而having可以。

排序查询

• 排序查询

  select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

  排序方式:

  asc : 升序（默认值）

  desc : 降序

  注意： 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

• 分页查询

  select 字段列表 from 表名 limit 起始索引,查询记录数;

  注意：

  起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

  分页查询是数据库的方言，不同的数据库有不同的实现。

  如果查询的是第一页的数据，起始索引可以省略，直接简写为limit 10。

执行顺序

from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
select
字段列表
order by
排序字段列表
limit
分页参数

DCL

管理用户

• 查询用户

  select user,host from mysql.user;

• 创建用户

  create user '用户名'@'主机名' [identified by '密码'];

• 修改用户密码

  alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

• 删除用户

  drop user '用户名'@'主机名';

权限控制

• 常用权限

  权限	说明
  all,all privileges	所有权限
  select	查询数据
  insert	插入数据
  update	修改数据
  delete	删除数据
  alter	修改表
  drop	删除数据库/表/视图
  create	创建数据库/表

• 查询权限

  show grants for '用户名'@'主机名';

• 授予权限

  grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

• 撤销权限

  revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';