Mysql(8.0)-基本操作(笔记)

目录

 字段属性

 数据行的管理

 新增数据

更新数据

查询数据

删除数据

数据查询语句

1、条件

通配符

模糊匹配

映射

想要获取的列

排序

分页查询

 多表查询

联合查询

连表查询

内连接

外连接

自连接

子查询

嵌套查询

 列子查询

 行子查询

 表子查询

 视图

 创建试图

删除试图 

分组

分组语法 

聚合函数

连接

什么是数据库

数据库管理系统DBMS

概念：用于存储和管理数据的仓库

作用：存储数据、数据管理

产品：

• mysql

• orcale

• sql server

• sqlite

• access

关系型：数据之间关联

非关系型：键值对存储关系，数据之间没有关联

区别

• 数据模型

• 数据一致性

• 可扩展性

• 灵活性

show databases;
create database mydata;
create database mydata default charset utf8mb4 collate utf8_0900_ai_ci;
# 指定字符集和校对规则
create database mydata default charset utf8 collate utf8_general_ci;
#使用数据库
use database;
# 查询当前数据库
select database();
#删除数据库
drop database mydata;
create database if not exists mydata;
drop database if exists mydata;

# 创建表
create table 表名（
    字段1 字段1类型[comment 自]
    
create table student(
    -> id int(10) not null,  -- 不允许为空
    -> name varchar(50) primary key,  -- 不允许为空，不重复
    -> email varchar(50),
    -> age int(10)) default 18  -- 默认值
    -> )
    
    # 查看表的结构
    desc student;
    # 建表语句
    show create table student;
    
    
    
    # 添加字段
    alter table 表名 add 字段名 类型(长度)  [comment 注释]；
    alter table student add password varchar(30);
    
    alter table 表名 add 字段名 类型 default 默认值;
    alter table 表名 add 字段名 类型 not null default 默认值;
    alter table 表名 add 字段名 类型 not null auto_increment;
    alter table 表名 add 字段名 类型 not null primary key auto_increment;
    
    # 修改字段
    alter table 表名 modify 字段名 新数据类型(长度)；
    alter table student modify password int;
    
    alter table 表名 change 旧字段名 新字段名 类型(长度);
    alter table student change name username varchar(30);
    
    # 修改列的默认值
    alter table 表名 modify 字段名 default 1000;  -- 修改某列的默认值
    alter table 表名 alter 字段名 set default 1000;
    
    # 删除列默认值
    alter table 表名 alter 字段名 drop default;
    
    # 删除字段
    alter table 表名 drop 字段名；
    
    # 修改表名
    alter table 表名 rename to 新表名；
    
    # 删除表
    drop table [if exists] 表名；
    
    truncate table 表名；  -- 删除表，重新再创建表  速度快，无法回滚撤销
    # 清空表
    delete from 表名 where 条件;
    
    # 添加主键
    alter table 表名 add 字段名 类型 primary key;
    alter table 表名 add primary key(字段名);
    
    # 删除主键
    alter table 表名 drop primary key;
    
    # 常见的字段类型
    create table 表(
        id int,
        name varchar(20)
    )detault chartset=utf8;
    
    varchar    
    char(n):固定长度的字符串    
    int    
    float    
    decima(p,s)：小数类型     p：总位数 s：小数位数  
    
    date:日期类型 不需要指定长度
    
    

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\MySQL\mysql-8.0.23-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\MySQL\mysql-8.0.23-winx64\Data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

int. 有符号整型  取值范围  -2^31 ~ 2^31-1

tinyint: 有符号整型： -128 ~ 127

            无符号整型： 0 ~ 255

bigint: 有符号整型：  -128 ~ 127

           无符号整型：  0 ~ 255

 字段属性

 UnSigned

无符号的

声明了该列不能为负数

zerofill

0填充的

不足位数的用0来填充，如int(3),5则为005

auto_increment

通常理解为自增，

 数据行的管理

 新增数据

# 指定字段
insert into 表名(列名1，2，3) values(值1，2，3);
# 全部字段
insert into 表名 values(值1，2，3);
# 批量的数据添加
insert into 表名(列名1，2，3) values(值1，2，3)，
            表名(列名1，2，3) values(值1，2，3)，
            表名(列名1，2，3) values(值1，2，3);


# 注：字符串和日期类型数据应该包含在引号内

# 插入数据

更新数据

update 表名 set 字段名1=值1，
                字段名2=值2；

where 条件

查询数据

select * from 表名;

select 列1，2，3 from 表名;

select 列1，2，3 as 别名1，2，3 from 表名;

select * from 表名 where 条件;

删除数据

delete from 表名;

delete from 表名 where 条件;

数据查询语句

 select

        字段列表

from

       表名

where

        条件列表

group by

        分组字段列表

1、条件

select * from 表名 where 条件;

示例：

select * from info where id=5;

select * from info where age>30;

select * from info where age between 18 and 30;

select * from info where name='mark' or age<40;

select * from info where (name='mark' or email='mark@qq.com') and age<40;

# 4或者6,这是一个容器，没有5
select * from info where id in (4,6);

select * from info where id in (select id from depart);

# 后面为空，前面就不查了
select * from depart where exists (select id from depart where id=5);


# 等效
select * from (select * from info where id > 1) as t where age>10;
select * from info where id>1 and age>10;

通配符

模糊匹配

select * from 表名 where 字段名 like "%关键字%"

# 以r开头
select * from info where name like "r%";


# name里面含有k或者t
select * from info where name rlike "k|t|.";


select * from info where name like "m/_a";  -- /_表示不作为



注意：数据量少搜索

映射

想要获取的列

select id,name from info; 

select id,name as nm,123 from info; 

select id,name,666 as num from info;


select
    -> id,
    -> name,
    -> (select title from depart where depart.id=info.depart_id) as depart_name
    -> from info;
+----+--------+-------------+
| id | name   | depart_name |
+----+--------+-------------+
|  1 | 付斌斌 | 开发        |
|  2 | 王强   | 开发        |
|  3 | richa  | 运营        |
|  4 | mark   | 开发        |
|  5 | ray    | 销售        |
|  6 | tina   | 开发        |
|  7 | 智哥   | 运营        |
+----+--------+-------------+
7 rows in set (0.00 sec)



mysql> select
    -> id,
    -> name,
    -> (select title from depart where depart.id=info.depart_id) as depart_name,
    -> (select title from depart where depart.id=info.id) as x
    -> from info;
+----+--------+-------------+------+
| id | name   | depart_name | x    |
+----+--------+-------------+------+
|  1 | 付斌斌 | 开发        | 开发 |
|  2 | 王强   | 开发        | 运营 |
|  3 | richa  | 运营        | 销售 |
|  4 | mark   | 开发        | NULL |
|  5 | ray    | 销售        | NULL |
|  6 | tina   | 开发        | NULL |
|  7 | 智哥   | 运营        | NULL |
+----+--------+-------------+------+
7 rows in set (0.00 sec)


mysql> select
    -> id,
    -> name,
    -> case depart_id when 1 then '第一部门' end v1
    -> from info;
+----+--------+----------+
| id | name   | v1       |
+----+--------+----------+
|  1 | 付斌斌 | 第一部门 |
|  2 | 王强   | 第一部门 |
|  3 | richa  | NULL     |
|  4 | mark   | 第一部门 |
|  5 | ray    | NULL     |
|  6 | tina   | 第一部门 |
|  7 | 智哥   | NULL     |
+----+--------+----------+
7 rows in set (0.00 sec)


mysql> select
    -> id,
    -> name,
    -> case depart_id when 1 then '第一部门' else '其他部门' end as v2
    -> from info;
+----+--------+----------+
| id | name   | v2       |
+----+--------+----------+
|  1 | 付斌斌 | 第一部门 |
|  2 | 王强   | 第一部门 |
|  3 | richa  | 其他部门 |
|  4 | mark   | 第一部门 |
|  5 | ray    | 其他部门 |
|  6 | tina   | 第一部门 |
|  7 | 智哥   | 其他部门 |
+----+--------+----------+
7 rows in set (0.00 sec)



mysql> select
    -> id,
    -> name,
    -> case depart_id when 1 then '1'when 2 then '2' when 3 then '3' end as v2
    -> from info;
+----+--------+------+
| id | name   | v2   |
+----+--------+------+
|  1 | 付斌斌 | 1    |
|  2 | 王强   | 1    |
|  3 | richa  | 2    |
|  4 | mark   | 1    |
|  5 | ray    | 3    |
|  6 | tina   | 1    |
|  7 | 智哥   | 2    |
+----+--------+------+
7 rows in set (0.00 sec)



mysql> select
    -> id,
    -> name,
    -> case depart_id when 1 then '1'when 2 then '2' when 3 then '3' end as v2
    -> ,
    -> case when age<18 then '少年' else '成年' end v3
    -> from info;
+----+--------+------+------+
| id | name   | v2   | v3   |
+----+--------+------+------+
|  1 | 付斌斌 | 1    | 成年 |
|  2 | 王强   | 1    | 成年 |
|  3 | richa  | 2    | 成年 |
|  4 | mark   | 1    | 成年 |
|  5 | ray    | 3    | 成年 |
|  6 | tina   | 1    | 成年 |
|  7 | 智哥   | 2    | 成年 |
+----+--------+------+------+
7 rows in set (0.00 sec)

排序

select * from info order by age desc;  --倒序
select * from info order by age asc;  --正序



# 优先按照age从小到大，如果age相同  按照id从大到小

mysql> select * from info order by age asc,id desc;
+----+--------+------------------+------+-----------+
| id | name   | email            | age  | depart_id |
+----+--------+------------------+------+-----------+
|  6 | tina   | tina@qq.com      |   18 |         1 |
|  3 | richa  | richa@qq.com     |   18 |         2 |
|  1 | 付斌斌 | fubinbin@qq.com  |   19 |         1 |
|  5 | ray    | ray@qq.com       |   20 |         3 |
|  7 | 智哥   | dancer@qq.com    |   38 |         2 |
|  4 | mark   | mark@qq.com      |   40 |         1 |
|  2 | 王强   | wangqiang@qq.com |   49 |         1 |
+----+--------+------------------+------+-----------+
7 rows in set (0.00 sec)



-- id大于5，然后按照age从小到大

mysql> select * from info where id >5 order by age asc;
+----+------+---------------+------+-----------+
| id | name | email         | age  | depart_id |
+----+------+---------------+------+-----------+
|  6 | tina | tina@qq.com   |   18 |         1 |
|  7 | 智哥 | dancer@qq.com |   38 |         2 |
+----+------+---------------+------+-----------+
2 rows in set (0.00 sec)

分页查询

select * from info limit 5;  -- 前五条数据

select * from info order by age asc limit 5;  --先排序 在获取前5条数据

select * from info limit 5 offset 3;  -- 从位置3开始，往后获取5条数据

 多表查询

多表关系：

1. 一对多（多对一）
2. 一对一
3. 多对多

外键

保证数据的完整性和一致性

• 创建表增加外键约束

create table 表(
    字段名 字段类型,
    ...    ...
    primary key ('id'),
    constraint fk_info1_depart1 foregin key (depart_id) references depart1(id)
）;

•  已创建表增加外键约束

alter table 表名 add constartint 外键名称 foreign key (外键字段名) references 主表(主表名称);

• 删除外键

alter table 表名 drop  foreign key (外键名称);

 

联合查询

把多次的查询结果进行合并（上下），形成一个新的结果

语法：

select 字段 from 表1 
union (all)
select 字段 from 表2;

• 列数相同
• union 自动去重  union all :不去重

连表查询

合并查询（笛卡尔积，显示所有的查询结果）

select * from info,depart;

消除笛卡尔积

delect * from info,depart where info.depart_id = depart.id;

内连接

-- 隐式内连接
select * from info,depart where info.depart_id = depart.id;

-- 显示内连接  性能更好
select * from info (inner) join depart on info.depart_id = depart.id;

select info.id,name,title from info (inner) join depart on info.depart_id = depart.id;

外连接

• 左外连接

select * from info left (outer) join depart on info.depart_id = depart.id;

• 右外连接

select * from info right join depart on info.depart_id = depart.id;

自连接

• 子连接必须使用别名

-- 查询每个员工以及对应领导名字
select a.name,b.name from info a,info b where a.leader = b.id;

select a.name,b.name from info a left join info b on a.leader = b.id;

子查询

嵌套查询

标量子查询

子查询结果为单个值

• 查询开发部多有员工

-- 
select* from info where depart_id=1;

select* from depart where title='开发';
-- 子查询
select* from info where depart_id=(select id from depart where title='开发');

 列子查询

子查询结果为一列

操作符: 

in 

not in

any

all

some

•  查询开发部和销售部所有员工

select* from info where depart_id in (select id from depart where title='开发' or title='销售');

• 查询比运营部门里所有人年龄都大的员工信息

select * from info where age > all(select age from info where depart_id=(select id from depart where title='运营'));

•  查询比运营部门里任意一个人年龄都大的员工信息

select * from info where age > any(select age from info where depart_id=(select id from depart where title='运营'));

 行子查询

子查询结果为一行

•  查询与mark的年龄以及领导相同的员工信息

select * from info where (age,leader) = (40,1);


​
select * from info where (age,leader) = (select age,leader from info where name='mark');

​

 表子查询

子查询结果为多行多列

•  查询与mark和王强的部门和年龄都相同的员工

select * from info where (depart_id,age) in (select depart_id,age from info where name='mark' or name='王强');

•  查询年龄为30以上的员工，以及部门信息

select info.age,info.name,depart.title
from info
join depart on info.depart_id = depart.id
where age > 30;




select * from (select * from info where age > 30) as a left join depart as d on a.depart_id = d.id;

 视图

虚拟表

 创建试图

create view xxx as select * from 表;

create or replace view xxx as select * from 表;

删除试图 

drop view a;

drop view if exists a;




with ...as....

with a as (select * from info where age > 30)
select id,name from a;

create view和with as区别：

• create view用于创建一个命名的视图可以在查询中多次使用，视图时持久化的，可以存储在数据库里面。
• with as用于创建一个临时命名的子查询对象，也可以在查询中多次使用，with as 查询是临时的，只在当前查询中有效

分组

select max(age) from info;  -- 年龄的最大值

select count(id) from info;  -- 有多少数据


select distinct 字段 from 表名;   -- 去重数据



select depart_id,avg(age) from info group by depart_id;

mysql> select depart_id,avg(age) from info group by depart_id;
+-----------+----------+
| depart_id | avg(age) |
+-----------+----------+
|         1 |  31.5000 |
|         2 |  28.0000 |
|         3 |  20.0000 |
+-----------+----------+
3 rows in set (0.00 sec)

分组语法 

 select 字段 from 表名 [where条件] group by 分组字段名 [having 分组后的过滤条件] 

聚合函数

count
max
min
avg:平均
sum

连接

mysql> select * from info left join depart on info.depart_id = depart.id;
+----+--------+------------------+------+-----------+------+-------+
| id | name   | email            | age  | depart_id | id   | title |
+----+--------+------------------+------+-----------+------+-------+
|  1 | 付斌斌 | fubinbin@qq.com  |   19 |         1 |    1 | 开发  |
|  2 | 王强   | wangqiang@qq.com |   49 |         1 |    1 | 开发  |
|  3 | richa  | richa@qq.com     |   18 |         2 |    2 | 运营  |
|  4 | mark   | mark@qq.com      |   40 |         1 |    1 | 开发  |
|  5 | ray    | ray@qq.com       |   20 |         3 |    3 | 销售  |
|  6 | tina   | tina@qq.com      |   18 |         1 |    1 | 开发  |
|  7 | 智哥   | dancer@qq.com    |   38 |         2 |    2 | 运营  |
+----+--------+------------------+------+-----------+------+-------+
7 rows in set (0.00 sec)

窗口函数(OLAP函数)

窗口函数 over (partition by 分组字段 ... order by 排序字段 )

mysql8.0新特性 

• 排名

1. rank() :排名次 并列的结果序号是可以重复的，序号不连续
2. dense_rank()：排名次 并列的结果序号是可以重复的，序号连续
3. row_number()：排名次 排序相同也不会重复，按照行数排序

• 聚合

1. sum()
2. count()
3. max()
4. min()
5. avg()

-- 求不同种类的平均收入，按照种类来分组
mysql> 
    -> 
    -> 
select * from sales t1
left join (
select category,avg(revenue)as avg_revem from sales group by category
) as t2
on t1.category = t2.category
order by t1.category;



select
*,
avg(revenue) over (partition by category) as avg_revenue
from sales;



mysql> rank() over (partition by category order by quantity desc) as q1,
    -> dense_rank() over (partition by category order by quantity desc) as q2,
    -> row_number() over (partition by category order by quantity desc) as q3
    -> from sales;

SELECT a.*
FROM xuexiao AS a
JOIN (
    SELECT course, MAX(score) AS max_score
    FROM student_table
    GROUP BY course
) AS b ON a.course = b.course AND a.score = b.max_score;





SELECT name, COUNT(*) AS pass_count
FROM xuexiao
WHERE score >= 60
GROUP BY name;