SQL语言超详细笔记——MySQL数据库、DDL、DML、DQL、多表设计与查询

数据库概述

1、我们为什么要学习数据库？

①、实现数据持久化到本地。

②、使用完整的管理系统统一管理，可以实现结构化查询，方便管理。

2、数据库全称是数据库管理系统

数据库是为了方便数据的存储和管理，将数据存储在磁盘上的仓库，使用sql语言对数据进行管理(创建表结构，增删改查)。

3、数据库分类：

关系型数据库（RDBMS）：表与表之间的关联关系。

mysql sqlserver orace db2

非关系型数据库：

redis(专业做缓存用)

MySQL数据库

1、由瑞典MySQL AB 公司开发，目前属于Oracle旗下产品。

MySql软件是一种开放源码软件,可以修改源码来开发自己的 Mysql 系统。

MySql数据库服务器具有快速、可靠和易于使用的特点。

MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言。

2、【Mysql的安装】:百度。
【cmd命令行方式连接mysql】 :

登录：mysql [-hlocalhost -P3306]（本机可省略） -uroot -p（可以直接写密码，不能有空格）

-h：主机名

-P：端口号

-u：用户名

-p：密码

退出：exit

【查看mysql数据库的版本】

select version();

【MySQL的常用命令】

查看当前所有的数据库：show databases;

选择指定的库：use 库名

查看当前的所有表：show tables;

查看其他库的所有表：show tables from 库名;

【可视化客户端工具】

SQLyog / Navicat

3、MySQL语法规范

● 不区分大小写，建议关键字大写，表名、列名小写

● 每句话用;

● 注释

● 单行注释：#注释文字

● 单行注释：

– 注释文字（要有空格）

● 多行注释：

/* 注释文字 */

4、sql语言：

全称：结构化查询语言，一种特殊目的的编程语言。

优点：简单易学，几乎所有DBMS都支持SQL ，灵活使用可以进行非常复杂和高级的数据库操作。

DDL数据定义语言

1、【创建,删除数据库表结构 】

常用的语句：create ,alter,drop,rename

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

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

删除数据库

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

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

3、数据库表的基本概念：

数据表：表由行和列组成，分别都包含着数据。

记录：记录是一行数据。

字段：字段是表里的一列，用于保存每条记录的特定信息。

4、设计表：

● 表名(表信息)

● 表中的字段

● 字段的数据类型和长度

● 哪些约束

列的数据类型：

5、约束：

①.主键约束 PRIMARY KEY ：

②.在一张表中代表唯一的一条记录,不能为空,不能重复

③.主键列在一张表中只能有一个,主键列不能为空,不能重复

④.mysql还支持主键列自动增长(必须为整数类型)：AUTO_INCREMENT

⑥.不能为空约束：NOT NULL

⑦.唯一约束：UNIQUE

⑧.检查约束：设置条件

⑨.外键约束(多表)

6、创建表语法:

CREATE TABLE 表名(列名 数据类型 [约束] [默认值] [ 注释],…)

CREATE TABLE t_user(
id INT [PRIMARY KEY NOT NULL AUTO_INCREMENT],
number INT(5) NOT NULL,
stuname VARCHAR(10) NOT NULL,
age INT(3) CHECK(age>18),
birthday DATE,
weight DOUBLE, 
opertime datetime,
[CONSTRAINT 约束名 约束规则]
)

7、删除表，修改表名：

删除表
DROP TABLE 表名
修改表名
RENAME TABLE 旧表名 TO 新表名
复制表结构，没有数据
CREATE TABLE 新表名 LIKE 被复制表名;

DML数据操纵语言

数据操纵语言DML常用语句：insert,delete,update

删除数据：

DELETE FROM 表名 WHERE 条件 – 逐行删除数据
DROP TABLE 表名 – 删除表结构
TRUNCATE TABLE 表名 – 清空整张表数据 是一个ddl级别的操作

修改数据：

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

DQL数据查询语言

SQL是使用频率最高的一个操作，可以从一个表或多个表中查询数据。

select 结果列 from 表名 where 条件 order by 排序 group by 分组 limit0,2

1、查询结果：

– 特定列查询：

select column1 column2... from 表名

SELECT num,sname,gender FROM student

– 查询所有的列

SELECT * FROM student

– 查询结果使用算数运算符±*/ + 只能作为算数运算

  SELECT num,sname,height+1 FROM student
  SELECT num,sname,height+num FROM student

– DISTINCT 去除重复数据,重复数据指的是每列的值都是相同的.

 SELECT DISTINCT sname,gender FROM student

select 语句后使用函数：

单行函数: 会对每行数据进行处理（ 字符串处理、逻辑处理、日期、算数）

分组函数/聚合函数/统计函数: 将多条结果转为一条 sum max count

– length(列名) 返回列的长度 以字节为单位 中文3个字节

  SELECT num,LENGTH(sname) FROM student

– char_length(列名) 以字符为单位的长度

   SELECT num,CHAR_LENGTH(sname)sname FROM student

– concat(“”,“”,“”…) as 别名

   SELECT CONCAT(num,":",sname,":",gender)AS studentInfo FROM student

   SELECT UPPER(sname)FROM student
   SELECT LOWER(sname)FROM student

– SUBSTRING(列,开始位置,截取长度)

 SELECT num,SUBSTRING(sname,2,2) FROM student

– instr(列名,查找的字符) 返回指定字符在字符串中首次出现的位置

   SELECT num,INSTR(sname,"i") FROM student

– trim(列名) 去掉前后的空格

   SELECT CHAR_LENGTH(TRIM(sname)) FROM student 

– trim(子串 from 列名) 去掉列名前后的指定子串

   SELECT TRIM("a" FROM sname) FROM student 

– 左填充指定内容到指定长度

   SELECT LPAD(sname,5,"a"),RPAD(sname,5,"a") FROM student 

– 替换

   SELECT REPLACE(sname,"i","I") FROM student

– 对查询结果进行逻辑处理

SELECT num,sname,
       (CASE WHEN height>=1.80 THEN '大高个' ELSE '不是大高个' END)height,
       (CASE 
            WHEN height>=1.80 THEN '大高个'
            WHEN height<1.80 AND height>1.60 THEN '中等'
            ELSE '低个子' END)height                                 
      FROM student

– ifnull(列,默认值)

 SELECT num,sname,IFNULL(address,'暂未录入')  FROM student 

– if(条件,条件成立返回,不成立返回)

 SELECT num,sname,IF(height>1.80,'大高个子',"不是大高个")height    FROM student

SELECT num,sname,ROUND(height) FROM student 
  SELECT num,sname,FLOOR(height) FROM student 

– 截断 不会进位 RAND() 随机生成 0-1之间的随机数

 SELECT num,sname,TRUNCATE(height,1),RAND() FROM student 

 SELECT num,sname,NOW(),CURDATE(),CURTIME() FROM student

 SELECT num,sname,YEAR(birthday),MONTH(birthday) FROM student

– 字符串转 日期类型

 SELECT STR_TO_DATE('2002-1-1',"%Y-%m-%d") FROM student

– 日期转字符串类型

 SELECT DATE_FORMAT(birthday,"%Y-%m")birthday FROM student  

 SELECT DATE_FORMAT(birthday,"%Y")birthday FROM student 

– 计算两个日期之间的天数

 SELECT DATEDIFF(CURDATE(),birthday) FROM student

– 分组函数/聚合函数/统计函数 多变一
– sum() avg()只能处理数值类型的列
– max() min() count() 可以处理任何数据类型

 SELECT SUM(height),AVG(height),MAX(height),MIN(height) FROM student

 SELECT COUNT(*) FROM student
 SELECT COUNT(num) FROM student
 SELECT MAX(sname),MIN(sname) FROM student

2、条件查询

语法：select 结果 from 表名 where条件

条件 比较 =, != 或<>, >, <, >=, <=

逻辑 and or not

– is null 查询值为空的

SELECT * FROM student WHERE  address IS NULL
SELECT * FROM student WHERE  address IS NOT NULL

– like %关键字% % 匹配任意个字符 _ 匹配一个字符

SELECT * FROM student WHERE sname LIKE "张_"
SELECT * FROM student WHERE sname LIKE "%三%"

– union 合并多个查询语句结果 可以去除多条结果中重复数据 多条查询结果列相同

SELECT num FROM student WHERE gender = "男"
 UNION 
SELECT num FROM student 

– union all 将多条查询合并 不会去除重复数据

SELECT * FROM student WHERE gender = "男"
 UNION  ALL
SELECT * FROM student 

3、排序查询

– 默认按主键从小到大排序

SELECT * FROM student 

– order by 列 asc(升序) desc(降序)

SELECT * FROM student  ORDER BY reg_time ASC
SELECT * FROM student  ORDER BY reg_time DESC
SELECT * FROM student  ORDER BY height ASC
SELECT * FROM student  WHERE gender = "男" ORDER BY height ASC

SELECT * FROM student   ORDER BY height ASC, reg_time DESC

– 分页显示数据 假设每页显示4条
– limit 开始的位置(从0开始),每次查询的数量

SELECT * FROM student WHERE num>0 ORDER BY num ASC  LIMIT 0,4
SELECT * FROM student WHERE num>0 ORDER BY num ASC  LIMIT 4,4

4、分组查询 group by 分组条件 把分组条件相同的数据划分到同一个组中处理

SELECT gender,COUNT(*),MAX(height),AVG(height) FROM student GROUP BY gender

– 子查询

SELECT * FROM(SELECT gender,COUNT(*)c FROM student   GROUP BY gender)AS t WHERE t.c>3

– where 是对分组前(表中的原始数据)筛选, HAVING对已经分组后的数据再进行筛选

SELECT gender,COUNT(*)c FROM student WHERE num>0   GROUP BY gender HAVING c>3

多表设计_关联查询

1、数据库设计范式

第一范式：要求列保持原子性（不可拆分、例如电话号码）。

第二范式：有逐渐，其他字段依赖主键。

第三范式：消除冗余，一表存一类信息。

2、外键：引用另外一个数据表的某条记录。

外键列类型与主键列类型一致
数据表之间的关联/引用是靠主键和外键建立起来的。

3、

·当主表中没有对应的记录时，不能将记录添加到从表
·不能更改主表中的值而导致从表中的记录孤立
·从表存在与主表对应的记录，不能从主表中删除该行
·删除主表前，先删从表

4、关联查询

①.内连接(inner join)：

把满足了条件的两张表中的交集数据查询出来

(1).内关联(等值连接) 把两张表中满足条件的数据查询出来

SELECT * FROM 表1 INNER JOIN 表2 ON 表1.column1 = 表2.column2

(2).非等值连接，用于区分范围、积分等级

eg: SELECT * FROM students INNER JOIN height-level h ON s.height BETWEEN h.level-lower AND h.leve-upper

(3).自连接，例如中国行政单位表，收货地址：省市县区

eg:SELECT * FROM area WHERE pif=…

SELECT * FROM area al INNER JOIN area a2 ON a1.pid=a2.pid

​ INNER JOIN area a3 ON a3.pid=a2.pid

​ WHERE a1.id=6100101

②.外连接：

(1).左外连接(left join)：

select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2

(2).右外连接(right join)：
select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2

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

子查询出现在：
select后面：支持标量子查询（一行一列）
from后面：支持表子查询（多行多列）
where后面：支持标量子查询（一行一列）和列子查询（一列多行）