mysql知识详解（上）——SQL语句基础

SQL语句基础

SQL全称：Structured Query Language ，结构化查询语言。

SQL语句分类

• 查询语句：select关键字
• DML（Data Manipulation Language,数据操作语言）：主要有insert，update，delete三个关键字
• DDL（Data Definition Language）：create，alter，drop，truncate四个关键字
• DCL（Data Control Language）： grant revoke两个关键字。
• 事务控制语句：commit，rollback，savepoint三个关键字完成。

sql关键字不区分大小写

标识符命名规则：

• 通常必须以字母开头
• 可以包括字母，数字，三个特殊字符（#_$）
• 不要使用当前数据库系统的关键字，保留字，通常由单词连缀而成，单词之间以_分开
• 同一个模式下的对象不应该同名，模式指的是外模式。

数据类型

• 整型
  • tinyint(1个字节)
  • smallint（2个字节）
  • int(4个字节)
  • bigint（8个字节）
• 无符号数字
  • tinyint
  • unsigned (0~255)
• 字符串
  • char(length): 定长，效率高
  • varchar(length):变长
• 浮点类型
  • double
  • float
• 定点小数
  • decimal(总位数, 小数位数)：例如：decimal(10, 2) 小数部分两位，整数部分最大8位
• 日期类型
  • datetime
  • timestamp

DML语句

• 1.创建数据库
  • create database 数据库名;
• 2.创建表
  • create table 表名(列定义);

create table test
(
    #整形常用int
    test_id int,
    #小数点
    test_price decimal,
    # 普通长度文本 default指定默认值
    test_name varchar(255) default 'xxx',
    # 大本文类型
    test_desc text,
    #图片
    test_img blob，
    test_date datetime
);

• 3.删除数据库
  • drop database 数据库名
• 4.删除表名
  • drop table 表名
• 5.修改表
  • alter table 表… (增删改)

添加列
语法：alter table 表名 add 列名 数据类型；

修改列
语法：alter table 表名 modfiy 列名 新类型 ；

删除列
语法：alter table 表名 drop 列名；

重命名列
语法：alter table 表名 rename column 旧列名 to 新列名;
语法：alter table 数据库名.表名 change 旧列名 新列名 类型;

重命名表
语法：alter table 表名 to 新表名；

数据库约束

• 数据分类：
  • not null： 非空约束，指定列不饿为空
  • unique：唯一约束 ，指定列里不能有重复
  • primary key ：主键，该列的值可以唯一标识该条记录
  • foreign key：外键 该行记录从属与主表的某条记录
  • check约束：检查约束，该列的值必须满足这个check表达式。mysql不支持check约束。
• 指定约束：
  • 在建表的时候指定约束
  • 表创建完成后，以修改表的方法增加约束。
• 约束分类
  • 单列约束
  • 多列约束

DML语句（基于表的语句）

• 功能：
  • 插入新数据
  • 修改已有数据
  • 删除不需要的数据
    -分类：
  • 1. insert into 语句(插入数据)
    • 格式：insert into 表名 (列名1，列名2…) values (值1，值2…)
    • 插入某一行数据
  • 2.update 语句(更新数据)
    • 格式： update 表名 set 列1 = 值2，…
    • 更新一行数据，也可以在后面加上where指定到具体更新哪一行。
  • 3.delete from 语句
    • 格式： delete from 表名 [where 限定]
    • 删除与where匹配的行数据。

select 查询

• 完整格式：select 列名… from 表 where 条件 group by 分组条件 having 分组筛选条件 order by 排序条件 limit;
• 意思为，查询在表的列，按照where条件过滤，再按照group by 分组 最后筛选，再显示排序【升序(asc)或者降序(desc)】。
• Sql语句支持简单的><=等等算术运算符，当然也有一些复杂的，比如：
  • expr1 between expr2 and expr3：要求exper1在exper2和exper3之间，包含双边。
  • exper1 in （exper1，exper2 ，exper3 ，exper4）：要求exper1包含于后面的集合。
  • like：字符串匹配，支持通配符。又称模糊查询。
  • is null 要求指定值为null。
  • not，and，or等等。
• 限制返回结果个数
  • limit m; // 最多返回m个结果
  • limit n,m; // 最多返回m个结果，n代表起始下标，下标从0开始

DCL语句

• grant 授权
• revoke 回收权限

创建用户
sql
create user 用户名 identified by '密码';

授权语法：
sql
grant 权限 to 用户名;

例如：把查询test库中所有表的查询权限授权给user1
grant select on test.* to user1;

回收权限
sql
revoke 权限 from 用户名;

例如：回收之前分配的权限:
revoke select on test.* from user1;

数据库函数（常用于select语句之后或者where子句之中）

• 函数被分为多行函数和单行函数，单行函数对每行输入的值单独计算，每行得到一个结果返回给用户，多行函数对多行输入值整体计算，最后只得到一个结果。

• 语法：函数名（参数1，参数2，…）

• 单行函数分类：

  • 日期时间函数
  • 数值函数
  • 字符函数
  • 转换函数
  • 其他函数：再分类
    • 位函数
    • 流程控制函数
    • 加密解密函数
    • 信息函数

• 数学函数

函数名	作用
ABS(x)	返回x的绝对值
BIN(x)	返回x的二进制（OCT-八进制，HEX-十六进制）
CEILING(x)	返回大于x的最小整数值
EXP(x)	返回值e（自然对数的底）的x次方
FLOOR(x)	返回小于x的最大整数值
GREATEST(x1…,xn)	返回集合中最大的值
LEAST(x1…,xn)	返回集合中最小的值
LN(x)	返回x的自然对数
LOG(x,y)	返回x的以y为底的对数
MOD(x,y)	返回x的以y为底的对数
RAND()	随机数生成器生成一个指定的值
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
SIGN(x)	返回代表数字x的符号的值
SQRT(x)	返回一个数的平方根
TRUNCATE(x,y)	返回数字x截短为y位小数的结果

2. 聚合函数(常用于GROUP BY从句的SELECT查询中)

函数名	作用
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果

3. 字符串函数

函数名	作用
ASCII(char)	返回字符的ASCII码值
BIT_LENGTH(str)	返回字符串的比特长度
CONCAT(s1,s2…,sn)	将s1,s2…,sn连接成字符串
CONCAT_WS(sep,s1,s2…,sn)	将s1,s2…,sn连接成字符串，并用sep字符间隔
LCASE(str)/LOWER(str)	返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)	返回字符串str中最左边的x个字符
LTRIM(str)	从字符串str中切掉开头的空格
POSITION(substr,str)	substr在字符串str中第一次出现的位置
QUOTE(str)	用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)	返回字符串str重复x次的结果
REVERSE(str)	返回颠倒字符串str的结果
RIGHT(str,x)	返回字符串str中最右边的x个字符
RTRIM(str)	返回字符串str尾部的空格
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去除字符串首部和尾部的所有空格
UCASE(str)/ UPPER(str)	去除字符串首部和尾部的所有空格

4. 日期和时间函数

函数名	作用
CURDATE()/CURRENT_DATE()	返回当前的日期
CURTIME()/CURRENT_TIME()	返回当前的时间
DATE_FORMAT(date,fmt)	依照指定的fmt格式格式化日期date值
DAYOFWEEK(date)	返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)	返回date是一个月的第几天(1~31)
DAYOFYEAR(date)	返回date是一年的第几天(1~366)
FROM_UNIXTIME(ts,fmt)	根据指定的fmt格式，格式化UNIX时间戳ts
UNIX_TIMESTAMP(date)	把字符串日期转成时间戳
NOW()	返回当前的日期和时间
WEEK(date)	返回日期date为一年中第几周(0~53)
YEAR(date)	返回日期date的年份(1000~9999)

5. 系统信息函数

函数名	作用
DATABASE()	返回当前数据库名
BENCHMARK(count,expr)	返回当前的时间
CONNECTION_ID()	返回当前客户的连接ID
FOUND_ROWS()	返回最后一个SELECT查询进行检索的总行数
USER()/SYSTEM_USER()	返回当前登陆用户名
VERSION()	返回MySQL服务器的版本

常用的数据库函数就是这些。

基础知识就整理到这里，在mysql知识详解（下）里整理连接查询和子查询。