【校招VIP】MySQL的概念&&数据库&&数据表的操作- 校招VIP

转载声明：文章来源https://blog.csdn.net/peng_19_20_qing/article/details/106944582

数据库 MySQL
为什么使用数据库

1	`如果通过io流操作文件，非常的不方便`

数据库的介绍

用于储存和管理数据的仓库，能够对数据进行统一的维护和管理
关系型数据库
以二维表格来组织数据结构，表与表之间可能还有一定的关联关系
非关系型数据库
不是以二维表格来组织数据

1 2	`使用一种统一的方式操作数据库----SQL` `MySQL是一种关联数据库`

SQL的分类

DDL 数据库定义语言
DML 数据库操作语言【重点】
DQL 数据库查询语言【重点】 
DCL 数据库控制语言

DDL 操作数据库和数据表
一.DDL操作数据库
DDL查询数据库

查询所有数据库
    SHOW DATABASES;
查询数据库的创建语句
    SHOW CREATE DATABASE 数据库名称;

DDL创建数据库

创建数据库
    CREATE DATABASE 数据库名称;
 创建数据库(判断，如果不存在则创建)
     CREATE DATABASE IF NOT EXISTS 数据库名称;
创建数据库(指定字符集)
    CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;

DDL修改删除使用数据库

修改数据库(修改字符集)
     ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
删除数据库
    DROP DATABASE 数据库名称;
 删除数据库(判断，如果存在则删除)
     DROP DATABASE IF EXISTS 数据库名称;
使用数据库
   USE 数据库名称;
查看当前使用的数据库
  SELECT DATABASE();

二.操作数据表
DDL查询数据表

查询当前正在使用的数据库中的所有表名
    show tables；
查询指定表的结构
    desc 表名
 查看指定数据库中指定表字符集   
   show table status from 数据库名 like ‘表名’
    
    例如
    查询库中所有的表
    SHOW TABLES;
查询user表结构
    DESC USER;
查看mysql数据库中user表字符集
 SHOW TABLE STATUS FROM mysql LIKE 'user';

DDL 创建数据表

CREATE TABLE 表名(
列名 数据类型 约束,
列名 数据类型 约束,
...
列名 数据类型 约束
);
数据类型
int：整数类型
double：小数类型
date：日期类型。包含年月日，格式 yyyy-MM-dd
datetime：日期类型。包含年月日时分秒，格式yyyy-MM-dd HH:mm:ss
timestamp：时间戳类型。包含年月日时分秒，格式 yyyy-MM-dd HH:mm:ss
* 如果不给该列赋值、或赋值为null，则默认使用当前系统时间自动赋值
 
varchar(长度)：可变长度字符串类型
char ：固定长度的字符串
     
    除了字符串必须指定长度，其他不管
    其余见资料MySQL数据类型

复制数据表
CREATE TABLE 表名 LIKE 被复制的表名;
DDL修改数据表

修改表名
     ALTER TABLE 表名 RENAME TO 新表名;
 修改表的字符集
  ALTER TABLE 表名 CHARACTER SET 字符集名称;   
 单独添加一列
     ALTER TABLE 表名 ADD 列名 数据类型;
 修改某列的数据类型
     ALTER TABLE 表名 MODIFY 列名 新数据类型;
 修改列名和数据类型
    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; //如果是varchar,要添加(长度)
 删除某一列
ALTER TABLE 表名 DROP 列名;

DDL删除数据表

删除数据表
    DROP TABLE 表名;
 删除数据表(判断，如果存在则删除)
    DROP TABLE IF EXISTS 表名;

三 .表数据的操作(增删改)
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,…),…;
注意:
列名和值的数量以及数据类型要对应，除了数字类型，其他数据类型的数据都需要加引号(单引双引都行，推荐单引)

DML修改表数据

修改表中的数据
     UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件];
注意:
    修改语句中必须加条件，如果不加条件，则会将所有数据都修改。

DML删除表中的数据

删除表中的数据
    DELETE FROM 表名 [WHERE 条件];
注意:
     删除语句中必须加条件，如果不加条件，则会将所有数据都删除

DQL 表数据查询【重点】
查询语法

单表查询完整语法
SELECT
    字段列表
FROM
    表名列表
WHERE
     条件列表
GROUP BY
     分组字段
HAVING
     分组后的过滤条件
ORDER BY
      排序
LIMIT
      分页限定

查询全部

查询全部的表数据
     SELECT * FROM 表名;
 查询指定字段的表数据
     SELECT 列名1,列名2,… FROM 表名;
 去除重复查询
     SELECT DISTINCT 列名1,列名2,… FROM 表名;
注意:去除重复,必须是查询的所有列都重复
  
    计算列的值(四则运算)
    SELECT 列名1 运算符(+ - * /) 列名2 FROM 表名; 
 起别名查询
   SELECT 列名 AS 别名 FROM 表名;

条件查询
条件查询分类

条件查询语法

1	`SELECT 列名列表 FROM 表名 WHERE 条件;`

案列

/*
    条件查询
    标准语法：
        SELECT 列名列表 FROM 表名 WHERE 条件;
*/
-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;
 
 
-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand='华为';
 
-- 查询金额在4000 ~ 6000之间的商品信息
SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
 
 
-- 查询库存为14、30、23的商品信息
SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
SELECT * FROM product WHERE stock IN(14,30,23);
 
-- 查询库存为null的商品信息
SELECT * FROM product WHERE stock IS NULL;
 
-- 查询库存不为null的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;
 
 
-- 查询名称以小米为开头的商品信息
SELECT * FROM product WHERE NAME LIKE '小米%';
 
-- 查询名称第二个字是为的商品信息
SELECT * FROM product WHERE NAME LIKE '_为%';
 
-- 查询名称为四个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';
 
-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE NAME LIKE '%电脑%';

聚合函数查询
聚合函数的分类

聚合函数的查询语法

1	`SELECT 函数名(列名) FROM 表名 [WHERE 条件];`

例如

/*
    聚合函数
    标准语法：
        SELECT 函数名(列名) FROM 表名 [WHERE 条件];
*/
-- 计算product表中总记录条数
SELECT COUNT(*) FROM product;
-- 查看所有数据 
    select * from product;
-- 获取最高价格
SELECT MAX(price) FROM product;
 
-- 获取最低库存
SELECT MIN(stock) FROM product;
 
-- 获取总库存数量
SELECT SUM(stock) FROM product;
 
-- 获取品牌为苹果的总库存数量
SELECT SUM(stock) FROM product WHERE brand='苹果';
 
-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM product WHERE brand='小米';

排序

select from where...order by 列名1 排序方式1,列名2 排序方式2
    语法
    SELECT 列名列表 FROM 表名 [WHERE 条件] ORDER BY 列名 排序方式,列名 排序方式,…;
注意:
排序方式：ASC-升序，DESC-降序
如果有多个排序条件，只有当前边的条件值一样时，才会判断第二条件。

案列

/*
    排序查询
    标准语法：
        SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
*/
-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;
-- 默认升序排序,可以省略 ASC
SELECT * FROM product ORDER BY stock;
 
-- 查询名称中包含手机的商品信息。按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
 
 
-- 按照金额升序排序，如果金额相同，按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;

分组查询
分组查询语法

SELECT 列名列表 FROM 表名 
   [WHERE 条件] 
   GROUP BY 分组列名
   [HAVING 分组后的条件过滤]
   [ORDER BY 排序列名 排序方式];
 
注意事项:
where 后面不能跟聚合函数条件,聚合函数条件只能跟在having后面

分页查询

分页查询
    标准语法：
        SELECT 列名 FROM 表名 
        [WHERE 条件] 
        [GROUP BY 分组列名]
        [HAVING 分组后条件过滤] 
        [ORDER BY 排序列名 排序方式] 
        LIMIT 当前页数,每页显示的条数;
     
    LIMIT 当前页数,每页显示的条数;
    公式：当前页数 = (第几页-1) * 每页显示的条数
         令每页显示的条数为x
    即  第1页, 当前页数=(1-1)*x  0
        第2页,当前页数 = (2-1)*x   x
        第3页,当前页数 = (3-1)*x   2x
        ...
        第n页 当前页数 = (n-1)*x

约束
约束的介绍

1 2	`什么是约束` `对表中的数据进行限定，保证数据的正确性、有效性、完整性！`

约束的分类

单表约束
主键约束
非空约束
唯一约束
     
多表约束
   外键约束
    foreign key

主键约束 PRIMARY KEY

主键约束的特点
   主键约束默认包含非空和唯一两个功能。
    一张表只能有一个主键。
    主键一般用于表中数据的唯一标识。 
     
     建表时添加主键约束
    CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY,
    ...
    列名 数据类型 约束
    );
   
     删除主键约束
  ALTER TABLE 表名 DROP PRIMARY KEY;
 
 建表后单独添加主键约束
  ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

主键自增

建表时添加主键自增约束
    CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    ...
    列名 数据类型 约束
    );
 
 删除主键自增约束
     ALTER TABLE 表名 MODIFY 列名 数据类型;
 
建表后单独添加主键自增约束
    ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
 
    MySQL 中的自增约束，必须配合键的约束一起使用！

唯一约束

建表时添加唯一约束
     CREATE TABLE 表名(
    列名 数据类型 UNIQUE,
    ...
    列名 数据类型 约束
    );
 删除唯一约束
     ALTER TABLE 表名 DROP INDEX 列名;
 
建表后单独添加唯一约束
     ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

非空约束

建表时添加非空约束
    CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
    ...
    列名 数据类型 约束
    );
删除非空约束
    ALTER TABLE 表名 MODIFY 列名 数据类型;
 建表后单独添加非空约束
     ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

约束练习

-- 创建表(编号、姓名、密码,手机号码)  编号设为主键自增，姓名设为非空，手机号设为唯一
CREATE TABLE acount(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(30) NOT NULL,
    PASSWORD VARCHAR(30),
    phonenumber INT(11) UNIQUE
);
-- 查询表的详细信息
   DESC acount;
-- 1-1 删除id的主键约束
    注意:要删除id的主键约束,需要先删除自动增长,再删除主动约束
        -- 删除自动增长
        alter table acount modify id int;
       -- 删除id的主键约束
        alter table acount drop primary key;   
 
-- 1-2 添加id的主键约束 
    alter table acount modify id int primary key
    //添加主键自增可选可不选
    alter table acount modify id int auto_increment;
 
-- 2-1 删除username非空约束
    alter table acount modify username varchar(30);
-- 2-2 添加username非空约束
    alter table acount modify username varchar(30) not null;
 
-- 3-1 删除 phonenumber 唯一约束
    alter table acount drop index phonenumber;
-- 3-2 添加 phonenumber 唯一约束
    alter table acount modify phonenumber int(11) unique;
 
总结:
  只有删除主键约束和唯一约束时 使用关键字 drop

综合练习

// 现已有一个表格 表明emp 列名如下
/*
    empno:员工编号
    ename：员工姓名
    job：员工职位
    mar：领导编号
    hiredate：入职日期
    sal：工资
    comm：奖金
    deptno：部门编号
*/
// 需求
-- 1 查询出部门编号为30的所有员工
    select * from emp where deptno=30;
 
-- 2 查询所有销售员的姓名、编号和部门编号
    select ename, empno,deptno from emp where job='销售员';
 
-- 3 查询奖金高于工资的员工
    select * from emp where comm > sal;
 
-- 4 查询奖金高于工资60%的员工
    select * from emp where comm > sal*0.6;
 
-- 5 查询10号部门中的所有经理，和20号部门中所有销售员的详细资料
 -- 注 and 优先级 高于 or
    select * from emp where deptno=10 and job='经理' or deptno=20 and job='销售员';
 
-- 6 查询10号部门中的所有经理，和20号部门中所有销售员，还有即不是经理又不是销售员但其工资大或等于         20000的所有员工详细资料
    select * from emp where deptno=10 and job='经理' or deptno=10 and job='销售员' or sal >= 20000 and job not in('经理','销售员');
 
 -- 7 查询无奖金或奖金低于1000的员工
     select * from emp where comm < 1000 or comm is null;
 
-- 8 查询名字由三个字组成的员工
     select * from emp where ename like '___';
  
 -- 9 查询2001年入职的员工
   select * from emp where hiredate like '2001-%';
  
 -- 10 查询所有员工信息按照工资降序排序，如果工资相同则按入职日期升序排序
   select * from emp order by sal desc,hiredate asc;
  
 -- 11 按照部门统计每个部门的人数
 select deptno, count(*) from emp group by deptno;
  
 -- 12 按照部门统计平均工资，降序显示，工资超过50000的不参与统计，只显示前两名。
  SELECT deptno, AVG(sal) AS AVG FROM emp WHERE sal <50000 GROUP BY deptno ORDER BY AVG DESC LIMIT 0,2;