mysql基础

---

sql: 结构化查询语言(Structured Query Language)简称SQL，是一种特殊
目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以
及查询、更新和管理关系数据库系统。

注释：

#单行注释
– 单行注释
/*
多行注释
多行注释
*/

注意： mysql关键字不区分大小写

DDL：数据（结构）定义语言

用于创建和修改数据库表结构的语言（create alter drop rename）

创建数据库并设置编码格式：

CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8];

删除数据库：

DROP DATABASE 数据库名 / [IF EXISTS数据库名];

修改字符集：

ALTER DATABASE 数据库名 CHARSET gbk;

（数据库一旦创建库名不能被修改）

数据库储存数据的特点：

1 将数据放到表中，表再放在库中

2 一个数据库可以有多个表，每个表都有唯一的名字，不能重名。

3 表具有的特性，定义了数据该怎么储存，类似java中的类

4 表中的列类似于java中的属性

5 表中的数据按行储存，每行类似于java中的对象

基本概念：

1 数据表

每个表都由表头和表体组成，表头定义表名和列名，表中的行就是记录，列被看做记录的字段

2 记录 记录是表中的一行数据。

3 字段 表里的一列用来保存每条记录里的特定信息，包含了特定字段的全部信息。

2 设计表：

设计表之前应明确：表名（表信息） 字段 字段的数据类型和长度 约束

char(n)长度为n的定长字符串

varchar(n) 最大长度为n的可变长度字符串

date 日期，年月日

datetime 年月日时分秒

整数类型：

浮点类型：

数据类型（M,D)M表示精度数据的总长度，D表示标度，小数点后的长度 eg：Float(6,2)

主键：唯一的记录，不能重复，不能为空

约束：

PRIMARY KEY 主键约束

NOT NULL 不为空

UNIQUE 唯一性约束

CHECK 检查约束

DEFAULT 设置默认值

eg:

gender CHAR(1) DEFAULT '男' CHECK(user_gender IN('男','女'));

age INT(3) CHECK(age>18);

AUTO_INCREMENT 主键自增长

comment ‘注释’ 字段注释

外键约束

删除表：drop table [if exists]表名

修改表名：rename table 旧表名 to 新表名

复制表结构 create table 新表名 like 被复制表名

DML：数据库操纵语言

(DataManipulation Language)

常用语句（insert delete update）

插入数据：

INSERT INTO 表名（列1，……，列n）VALUES（值1，……值n），（值，……）；

INSERT INTO 表名 set 列名1=值1，……，列名n=值n；

INSERT INTO 表名（列1，……，列n）查询语句（查询的列数与插入列数匹配）

修改数据：

UPDATE 表名 SET 列名=‘新值’ where 条件

删除数据：

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名； 清空整张表（自增索引也会清空，从头开始）

DQL：数据查询语言

（Data Query Language）

语法 select 列名 from 表名；

特点：查询列可以是：表中字段、常量、表达式、函数；查询结果是一个虚拟表格

查询常量值 SELECT 100;

查询表达式：select 100*98;

查询函数：select 函数; eg：select version()

特定列查询:select column1,column2 from table

全部列查询: select * from table（在需要查询所有列时使用，一般不建议）

排除重复行: select distinct column1,column2 from table（指的是所有列的的值都相同，实际多用于关联条件导致一些重复数据 ）

算数运算符:+ - * （只能用作算术运算，eg：+不能用作字符串拼接 字符串连接函数为CONCAT(列名，‘：’列名) 定义别名用as as还可省略）

eg：

SELECT CONCAT(sname,':',gender)AS sname FROM student;

单行函数

（注意这些函数都是用在select后面）

单行函数：此函数会对查询结果的每行进行处理

length(列名); 以字节为单位

char_length(列名) 以字符为单位

concat(列名，列名) 拼接字符串

upper(列名)转大写

lower(列名)转小写

字符串函数

substring(列名，开始位置（从1开始），截取的长度)

instr(列名，查找的字串) 类似于java中的indexof();返回字串第一次出现的索引，如果找不到返回0（从1开始）

trim(指定字串 from 字符串)去掉前后指定的字符串，默认去掉空格

lpad(列名，长度，字符串)左填充字符串

rpad(列名，长度，字符串)右填充字符串

replace(str ,old,new)替换，替换所有的字串

（注意下面这些函数都是用在select后面）

逻辑

1 case when ……then……else……end；可以有多个when

eg：case when height>1.70 then “高个子”else“非高个子”end

2 ifnull(列名，字符串)

eg：ifnull(height,‘身高信息未录入’)

3 if(条件，结果1，结果2) 条件成立输出结果1，否则输出结果二

数学

round(数值)：四舍五入

ceil(数值)：向上取整，返回>=该参数的最小整数

floor(数值)：向下取整，返回<=该参数的最大整数

truncate(数值,保留小数的位数)：截断，小数点后截断到几位，无进位

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

rand()：获取随机数，返回0-1之间的小数

日期

now()：返回当前系统日期+时间

curdate()：返回当前系统日期，不包含时间

curtime()：返回当前时间，不包含日期

YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期)
SECOND(日期) 获取指定部分的年、月、日、时、分、秒

str_to_date：将日期格式的字符转换成指定格式的日期

date_format：将日期转换成字符串

eg：date_format(birthday,‘%Y-%m’)

datediff(big,small)：返回两个日期相差的天数

分组函数

功能：用于统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）

1 sum avg 一般用于处理数值型，max min count可以处理任何类型

eg：sum(数值/字符串) 字符使用字符编码比较大小

2 忽略null值，不统计，count(*)建议使用星号

3 和分组函数一同查询的字段要求是group by 后的字段

条件查询

where 过滤不满足条件的行，语法：select 结果 from 表名 where 条件

比较 ：=, != 或<>, >, <, >=, <= where 后面的=表示的是是否相等，不是赋值，select 后面的等号是赋值

逻辑运算：and 与、 or 或 、 not非

not 一般不单独使用

eg：

select * from student where num not in（101,102,103）;
select * from student where phone is not null;

列名 between……and…… 在两者之间包含边界

like 模糊匹配：eg：name like ‘张%’ %匹配任意多个字符、 _匹配一个字符

排序&限制数量

order by 排序列 ASC/DESE 升序/降序 默认升序（从小到大）

limit 索引，查询数量 ：索引从0开始

group by 列名：以那一列为条件分组eg：select count(*),gender from student group by gender

分组查询

select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]

eg：

select count(*)c,gender from student where id>=1 group by gender having c>2;

union 将两条查询结果合并为一个结果 去除两条中重复的数据（每列数据都是相同的）

union all 将两条查询结果合并为一个结果，不会去除两条查询中的重复数据

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询

按功能、结果集的行列数不同：标量子查询（一行一列）、列子查询（一列多行）、行子查询（一行多列）、表子查询（多行多列）

子查询主要使用在查询语句中，一般牵扯到两张表以上的操作，都需要通过表名调用列，可以为表名定义别名

select 语句只支持标量子查询（一行一列）

from 后面只支持表子查询（多行多列，类似于表）

where或having 后面支持标量子查询、列子查询、行子查询

多表设计

一个系统有很多信息，一张表储存一类信息，数据冗余需要拆分，不可能也不建议将许多信息放在一张表中

多张表之间是有关系的，遵循数据库设计范式（规则）

第一范式：确保每列保持原子性（不可再分割）（eg：联系方式可以拆分为：qq 电话 地址等不满足要求）

第二范式：要有主键，其他字段依赖于主键

第三范式：消除传递依赖、一张表储存一类信息 每一类信息依赖于自己的主键

表与表之间的关联关系：

1 弱关联关系：表与表之间并没有强制的约束关系，只是逻辑关系

2 强关联关系：表与表之间有外键关系（外键约束）

eg：

alter table student add column majorid int ;
alter table student add constraint fk_student_majorid foreign key(majorid) references major(id)

笛卡尔乘积现象 将两张表中的所有数据全部关联起来 可以添加条件来避免