jck28 - 小柒 -SQL语法与数据库

一,基本介绍

1.1,简介

  • 数据库(DataBase) 就是一个以某种有组织的方式存储的数据集合
  • 是存储和管理数据的仓库
  • 其本质是一个文件系统
  • 数据库管理系统(DBMS)是一款管理软件

1.2,分类

  • 关系型数据库(RDB: Relationship DataBase)
  • 非关系型数据库(NoSQL)

1.3,常用数据库

  • 关系型数据库(RDBMS):MySQL、Oracle、Postgres、SQLite、SQLServer
  • NoSQL 数据库:MongoDB、Redis、HBase、Neo4j、NewSQL

1.4,使用场景

  • 关系型数据库
    • 需要做复杂处理数据
    • 数据量不是特别大的数据
    • 对安全性能要求高的数据
    • 数据格式单一的数据
  • 非关系型数据库(NoSQL)
    • 数据模型比较简单
    • 需要灵活性更强的 IT 系统
    • 对数据库性能要求较高
    • 不需要高度的数据一致性

二,SQL安装与分类

2.1 Mac系统安装MySQL

环境变量配置:

  • 进入 .bash_profile 文件
  • 添加内容:
    • export PATH=$PATH:/usr/local/mysql/bin
    • export PATH=$PATH:/usr/local/mysql/support-files

2.2 Windows系统安装MySQL

环境变量配置:

  • 新建系统变量 mysql,值为 mysql 安装路径
  • path 变量中添加 %mysql%\bin

2.3 数据库的命令行操作

  • 开启 mysql 服务:net start mysql
  • 登录:mysql -h主机IP -u用户名 -p密码
  • 修改密码:alter user 'root'@'localhost' identified by '密码';
  • 退出:exit
  • 关闭 mysql 服务:net stop mysql

2.4 sql介绍

目录结构:

数据库表:

  • 表:包含数据库中所有数据的数据库对象
    • 表名:每个表的唯一标识
    • 模式(schema):关于数据库和表的布局及特性的信息
    • 列:表中每列称为一个字段
    • 行:表中的一个记录

2.5 分类

  • 数据定义语言(DDL):用来定义数据库对象,比如数据库,表,列等
  • 数据操作语言(DML):用来对数据库中表的记录进行更新
  • 数据查询语言(DQL):用来查询数据库中表的记录
  • 数据控制语言(DCL):用来定义数据库的访问权限和安全级别及创建用户

三,数据库操作

3.1 DDL 数据库操作 - 增删改查

————————————————————————————————————————————

数据库创建

  • 创建数据库语法:create {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名 CHARACTER SET [=] 字符集

    • create DATABASE student; (创建一个名为student的数据库)
    • create DATABASE person CHARACTER SET utf8; (创建一个名为person 的数据库,指定字符集为utf8)
    • create DATABASE IF NOT EXISTS son CHARACTER SET utf8; (如果数据库son不存在就创建名为son的数据库)
  • 创建数据库注意事项

    • 不能与其他数据库重名
    • 名称可以由任意字母、阿拉伯数字、下划线(_)和“$”组成,但不能使用单独的数字
    • 名称最长可为 64 个字符,别名最长为 256 个字符。
    • 不能使用 MySQL 关键字作为数据库名
    • 建议采用小写来定义数据库名

数据库查看

  • 查看数据库:SHOW DATABASES;
  • 选择数据库:USE 数据库名;
  • 查看数据库的定义信息:SHOW CREATE DATABASE 数据库名;

数据库修改

ALTER DATABASE 数据库名 CHARACTER SET utf8;

数据库删除

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名; //如果数据库存在,则删除这个数据库

————————————————————————————————————————————

数据表创建

  • 创建表语法:
create table 数据表名 (
    列名1 属性,
    列名2 属性…
   )


* 列属性:列名 数据类型 [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT]
[PRIMARY KEY ] [注释]
  *  NOT NULL | NULL:该列是否允许是空值,默认为NULL
  *  DEFAULT:表示默认值
  *  AUTO_INCREMENT:表示是否是自动编号
  *  PRIMARY KEY:表示是否为主键
  • 复制表语法: create table 数据表名 like 源数据表
    • LIKE 源数据表名:必选项,指定依照哪个数据表来创建新表

数据表查询

  • 查看表名语法:SHOW TABLES;
  • 查看表结构语法:
    • 查看表结构:DESCRIBE 数据表名; / DESCRIBE 数据表名 列名;
    • 查看表结构简写:DESC 数据表名; / DESC 数据表名 列名;

数据表修改

  • 添加新列:ALTER TABLE 表名 ADD 列名 列属性;
    • alter table student add email varchar(50) NOT NULL;
  • 修改列定义:ALTER TABLE 表名 MODIFY 列名 列属性;
    • alter table student modify score int;
  • 修改列名:ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
    • alter table student CHANGE COLUMN name stu_name varchar(30) default null
  • 删除列:ALTER TABLE 表名 DROP 列名;
  • 修改表名:ALTER TABLE 旧表名 RENAME AS 新表名; / RENAME TABLE 旧表名 To 新表名;

数据表删除

  • 删除表语法:DROP TABLE [IF EXISTS] 数据表名

————————————————————————————————————————————

3.2 DML 数据库表操作 - 增删改

插入数据

  • 表数据插入语法:INSERT INTO 数据表名 (列名1, 列名2…) VALUES(值1, 值2…);

  • 完整插入数据

    • INSERT INTO user (id,name,age,sex,address) VALUES(1,‘张三’,20,‘男’,‘北京’);
    • INSERT INTO user VALUES (2,‘李四’,22,‘女’,‘上海’);
  • 插入数据记录的一部分

    • INSERT INTO user (id,name,address) VALUES(3,‘王五’,‘深圳’);
  • 插入多条记录

    • INSERT INTO user (id,name,address) VALUES(4,‘赵六’,‘天津’),(5,‘小红’,‘成都’),(6,‘小白’,‘杭州’);
  • 注意事项

    1. 值与字段必须要对应,个数相同并且数据类型相同
    2. 值的数据大小,必须在字段指定的长度范围内
    3. VARCHAR ,CHAR, DATE 类型的值必须使用单引号包裹
    4. 如果要插入空值,可以忽略不写,或者插入 NULL
    5. 如果插入指定字段的值,必须要上写列名

修改数据

  • 表数据修改语法:UPDATE 数据表名 SET 列名1=值1 [, 列名2=值2…] [WHERE 条件表达式]

删除数据

  • 通过 DELETE 语句删除数据:DELETE FROM 数据表名 WHERE 条件表达式
  • 通过 TRUNCATE TABLE 语句删除表中所有数据:TRUNCATE TABLE 数据表名

—————————————————————————————————————————————

3.3 DQL 数据库表操作 - 查询

条件查询

  • 单表查询:select* from表名;

  • 字段查询:select 列名 from 表名;

  • 起别名:

    • 为表起别名: select 列名 from 表名 表别名;
    • 为字段起别名:select 列名 AS 别名 FROM 表名;
  • 去重:select distinct 列名 from 表名;

  • 运算查询: select (列名 运算表达式) from 表名;

    • select emp_no , salary + 1000 from salaries;
  • 条件查询:select 列名 from 表名 where 条件表达式

  • 比较运算符:

    • 符号 > , <, <=, >=, =, <>, != : 大于、小于、小于等于、大于等于、等于、不等于
    • BETWEEN…AND… : 范围限定(模糊查询)
    • IN :子集限定(模糊查询)
    • LIKE ‘%or%’ :模糊查询
    • IS NULL :
  • 逻辑运算符:

    • AND 、 && :多个条件同时成立
    • OR 、| :多个条件任一成立
    • NOT:不成立
  • 通配符:

    • % : 匹配任意多个字符
    • ‘- ’ :匹配1个字符 :SELECT FROM employees WHERE first_name LIKE ‘fa_’;

排序

  • 语法:SELECT 列名 FROM 表名 [WHERE 条件表达式] ORDER BY 列名1 [ASC / DESC], 列名2 [ASC / DESC]

  • ASC - 升序排序(默认) DESC - 降序排序

聚合函数

  • COUNT():统计指定列不为 NULL 的记录行数
  • MAX():计算指定列的最大值
  • MIN():计算指定列的最小值
  • SUM():计算指定列的数值和
  • AVG():计算指定列的平均值

分组

  • SELECT 分组列/聚合函数 FROM 表名 GROUP BY 分组列 [HAVING 条件];
    • GROUP BY 分组列:按哪些列进行分组,搜集数据行到各个组中,统计函数为各个组计算统计值
    • HAVING:对分组结果再次过滤(where是在结果之前做过滤)

限制 - limit关键字

  • SELECT * FROM 表名 LIMIT [开始的行数], <查询记录的条数>

– 使用 OFFSET 关键字指定开始的行数

  • SELECT * FROM 表名 LIMIT <查询记录的条数> OFFSET <开始的行数>

主键约束

  • 对表中的数据进行进一步的限制
  • 保证数据的正确性、有效性、完整性
  • 违反约束的不正确数据无法插入到表中
  • 常见的约束
    • 主键:PRIMARY KEY
    • 非空:NOT NULL
    • 唯一:UNIQUE
    • 默认:DEFAULT
    • 外键:FOREIGN KEY
PRIMARY KEY
  • 主键:一列(或一组列),其值能够唯一标识表中每一行
  • 特点:不可重复,唯一,非空
  • 语法:列名 字段类型 PRIMARY KEY
  • 举例:
    • 创建一个带主键的表

      • CREATE TABLE emp1( eid INT PRIMARY KEY, ename VARCHAR(20), sex CHAR(1) )
    • 给存在的表添加主键,通过 DDL 语句进行设置

      • ALTER TABLE 表名 ADD PRIMARY KEY(eid);
    • 创建主键自增的表 (AUTO_INCREMENT表示自动增长,数据类型必须是整数类型)

      • CREATE TABLE emp3( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) );
    • 创建主键自增的表,自定义自增起始值

      • CREATE TABLE emp4( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) )AUTO_INCREMENT=100;
    • 删除表中的主键

      • ALTER TABLE 表名 DROP PRIMARY KEY;

非空约束

  • 非空约束特点: 某一列不予许为空
  • 语法:列名 字段类型 NOT NULL
  • 举例:
    – 添加非空约束
    • CREATE TABLE emp5( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20) NOT NULL, sex CHAR(1) );

唯一约束

  • 唯一约束: 表中的某一列的值不能重复

  • 对 NULL 不做唯一的判断

  • 语法:列名 字段类型 UNIQUE

  • 举例:

    • 创建带有唯一约束的表
      • CREATE TABLE emp6( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20) UNIQUE, sex CHAR(1) );
  • 主键约束与唯一约束的区别

      1. 主键约束,唯一且不能够为空
      1. 唯一约束,唯一但是可以为空
      1. 一个表中只能有一个主键,但是可以有多个唯一约束

默认值

  • 默认值约束:用来指定某列的默认值
  • 语法:列名 字段类型 DEFAULT 默认值
  • 举例:
    • 创建带有默认值的表
      • CREATE TABLE emp7( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20),
        sex CHAR(1) DEFAULT ‘女’);

—————————————————————————————————————————————

3.2 多表查询

外键约束

  • 主键:可以唯一标识一条记录的列
  • 外键:从表中与主表的主键对应的字段
  • 主表:外键所指向的表,约束其他表的表
  • 从表:外键所在的表,被约束的表
  • 价值:建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性

建立外键约束:

  • 创建表时添加外键约束:CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]
  • 添加外键约束:ALTER TABLE [表名] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]
# 创建一个关联到主表的从表
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)

删除外键约束

  • 语法:
    • ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]
  • 注意事项
    • 从表外键数据类型必须与主表的主键一致
    • 删除数据时,需先删除从表数据再删除主表的数据
    • 添加数据时先添加主表数据,再添加从表数据
# 删除外键约束 
ALTER TABLE emp_part DROP FOREIGN KEY emp_dept 

# 删除主表中的数据 
DELETE FROM dept WHERE id=2

级联删除

  • 删除主表数据的同时,也删除掉从表中相关的数据
    • ON DELETE CASCADE ```sql
CREATE TABLE emp_part( 
emp_id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
gender VARCHAR(10), dept_id INT,
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id) 
– 设置允许级联删除 ON DELETE CASCADE ; 

# 向员工信息表中添加一条数据
 INSERT INTO emp_part VALUES(1,‘cindy’,20,‘female’,‘2’) 
#删除主表中部门id=2的部门 
DELETE FROM dept WHERE id=2
 # 查看从表中的数据是否同时被删除 
SELECT * FROM emp_part 

—————————————————————————————————————————————

多表查询 - 内连接查询

  • 内连接(INNER JOIN):使用比较运算符进行表间某(些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行,组合成新的记录。匹配上显示,匹配不上不显示。

  • 按语法结构分为: 隐式内连接和显式内连接

  • 隐式内连接

    • 在笛卡尔积的的基础上,使用where条件过滤无用的数据,这种连接方式是隐式内连接.
    • 语法:select [字段名称] from 表1,表2 where [条件]
    • 例1: SELECT emp_id,ename,dept_location FROM emp_part,dept WHERE dept_id=id and dept_name=“运营部”;
  • 显式内连接

    • 显式内连接: 使用 select [字段名称] from [表1]inner join [表2] on [条件] 这种方式
    • 例2: SELECT emp_id,dept_location,ename FROM emp_part INNER JOIN dept ON dept_id=id and dept_name=“运营部”;

—————————————————————————————————————————————

多表查询 - 外连接查询

  • 外连接查询:查询多个表中相关联的行,有时候需要包含没有关联的行中数据,即返回查询结果集合中不仅包含符合连接条件的行,还包括左表(左连接)、右表(右连接)中的所有数据行。

  • 根据语法结构分为左连接和右连接

  • 左连接

    • 左连接:以左表为基准匹配右表的数据,右表中没有的项,显示为空
    • 语法:SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]
    • 例子:SELECT emp_id,ename,gender,dept_name,dept_location FROM dept LEFT JOIN emp_part ON dept.id=emp_part.dept_id;
  • 右连接

    • 右连接:以右表为基准匹配左表的数据,左表中没有的项,显示为空
    • 语法:SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]
    • 例子:SELECT emp_id,ename,gender,dept_name,dept_location FROM dept RIGHT JOIN emp_part ON dept.id=emp_part.dept_id;

多表查询 - 子查询

  • 定义:子查询指一个查询语句嵌套在另一个查询语句内部,在SELECT子句中先计算子查询,子查询的结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。 这个特性从MySQL 4.1开始引入。

  • 子查询作为过滤条件时需要用() 包裹

  • 常见子查询分类

    • From型子查询:将子查询的结果作为父查询的表来使用

      • select dept_name,count(emp_id) from
        (select dept_name, emp_id,ename,gender from dept inner join emp_part where id=dept_id and gender=‘男’)b group by dept_name;
    • in/not in 型子查询:子查询的结果是单列多行,作为where的过滤条件

      • SELECT * FROM emp_part WHERE dept_id IN (SELECT id FROM dept WHERE dept_location = ‘北京’)
    • where型子查询:查询结果作为过滤条件出现在比较运算符的一端

      • SELECT emp_id,ename,salary FROM emp_part WHERE salary > (SELECT AVG(salary) FROM emp_part);
  • with as

    • 如果一整句查询语句中,某个子查询的结果会被多个父查询引用,通常建议将共用的子查询用简写表示出来
    • 语法: with [表名] as (select…)
# 查询出部门平均薪资大于公司平均薪资的部门名称,部门主管,所在地及部门平均薪资

# 不使用 with ...as
select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_id 
and avg_salary > (select avg(avg_salary) from 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);

# 使用 with ...as
with dept_avg as
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)select
dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg 
on id=dept_id 
and avg_salary > (select avg(avg_salary) from dept_avg);

知识扩展-视图

  • 定义:视图是一种虚拟的表,它并不会在你的存储空间复制一份数据,而是对原有数据的一种引用。可以将视图理解为一种存储起来的sql语句
  • 视图可以简化多表查询
  • 视图也可以用于控制用户权限
  • 使用关键词view来创建视图
  • 语法:CREATE VIEW [视图名称] AS SELECT……
    • WITH temp_dept AS (SELECT order_list.dept_id,city, manager,SUM( volume ) total_volume FROM order_list INNER JOIN department ON order_list.dept_id = department.dept_id GROUP BY order_list.dept_id ORDER BY SUM( volume ) DESC ) ;
      SELECT * FROM temp_dept WHERE total_volume >(SELECT AVG( total_volume ) FROM temp_dept);
1 Like