MySQL数据库笔记

-- 数据库
    存储数据，把数据持久化到硬盘

-- RDBMS
    关系型数据库管理系统
    关系模型，用二维表存储数据，每一行记录都拥有同样的字段
    多个表有关联的关系，一个表的某一列引用另一个表的一列数据
-- Mysql
    支持多系统
    免费，开源

-- 服务端命令
    -- 开启服务
    sudo service mysql start
    -- 停止服务
    sudo service mysql stop
    -- 重启服务
    sudo service mysql restart
    -- 查看服务进程
    ps ajx | grep mysql 

-- 客户端登录
    mysql -u root -pmysql

-- 数据完整性

    -- 数据类型
    对于字符串类型，长度有意义，字符的个数，一个中文、字母都是一个字符
    数值类型，由具体的类型决定范围，跟长度关系
    decimal(5,2)表示共存5位数，小数占2位，整数占3位

-- 命令行客户端
    mysql -u root -pmysql
    -- 查看所有数据库
    show databases;
    -- 查看当前使用的数据库
    select database();
    -- 使用某个数据库
    use python14;
    -- 创建数据库
    create database aa charset=utf8;
    -- 删除数据库
    drop database aa;

    -- 查看所有表
    show tables;
    -- 查看表结构
    desc hero2;
    -- 查看创建表的语句
    show create table hero2;

    -- 创建表
    create table students(    id int unsigned not null primary key auto_increment, name varchar(10));

    -- 添加字段
    alter table students add aeg int;
    -- 修改字段 同时修改名称和类型
    alter table students change aeg age int;
    -- 修改字段 只修改类型
    alter table students modify age varchar(5);
    -- 删除字段
    alter table students drop age;

    -- 表重命名
    alter table hero2 rename hero;

    -- 备份表
    create table hero2_back select * from hero2;

    -- 删除表
    drop table hero2;


    -- 简单查询
    select * from hero2;
    -- 添加数据 表后面不指定字段，需要添加的值与表的所有字段一一对应
    insert into hero2 values(0,'abc',1,1.1);
    insert into hero2(name,age) values('aaa',21);

    -- 修改数据 如果不写where，会把所有的数据的，age和height字段都改掉
    update hero2 set age=10,height=16 where id=10;

    -- 删除数据 如果不写where,会把所有数据都删除，表还存在
    delete from hero2 where id=10;

    **第二天**
- 


    -- 逻辑删除

    添加字段，表示某一天数据是否被删除，bit
    alter table students add isdelete bit default 0;
    删除数据时，更新isdelete字段
    update students set isdelete=1 where id=10;
    以后所有的查询中，必须见isdelete条件
    select * from students where isdelete=0;

-- 别名

    select name as 姓名,age as 年龄 from students;

-- 消除重复行  消除age和height同时重复的数据

    select distinct age,height from students;

-- 条件

    -- 比较运算符

    等于: =
    大于: >
    大于等于: >=
    小于: <
    小于等于: <=
    不等于: != 或 <>
    例1：查询编号大于3的学生
    select * from students where id>3;

    -- 逻辑运算符

    and
    or
    not
    例5：查询编号大于3的女同学
    select * from students where id>3 and gender='女';
    查询id大于3 或者 女同学
    select * from students where id>3 or gender=2;
    查询id不等于3
    select * from students where not id=3;

    -- 模糊查询

    like
    %表示任意多个任意字符
    _表示一个任意字符
    例7：查询姓周的学生
    select * from students where name like '周%';

    -- 范围查询

    in表示在一个非连续的范围内
    例10：查询编号是1或3或8的学生
    select * from students where id in(1,3,8);

    between ... and ...表示在一个连续的范围内
    select * from students where id between 3 and 8;

    -- 空判断

    注意：null与''是不同的
    判空is null
    例13：查询没有填写身高的学生
    select * from students where height is null;
    select * from students where height is  not null;

    -- 排序

    为了方便查看数据，可以对数据进行排序
    语法：
    select * from 表名
    order by 列1 asc|desc,列2 asc|desc,...

    select * from students order by age desc;

    -- 聚合函数

    为了快速得到统计数据，经常会用到如下5个聚合函数

    count 如果height中有null值，不会统计
    select count(height)  from students;
    select count(*)  from students;

    select max(age) as 最大年龄,min(age) as 最小年龄,avg(age) as 平均年龄,sum(age) as 年龄总和 from students;

    -- 分组

    select gender,count(*) from students group by gender;

    分组后过滤，只能用having
    select gender,count(*) from students group by gender having gender=2;

    -- 获取部分行

    当数据量过大时，在一页中查看数据是一件非常麻烦的事情
    语法
    select * from 表名
    limit start,count

    查询前3条数据
    select * from students limit 0,3;

    -- 连接

    内连接 按照两个表中的2列取交集

    select *  from students as s inner join pythons as p on s.cls_id=p.id; 

    左连接 查找所有的学生，都在哪个班级

    select *  from students as s left join pythons as p on s.cls_id=p.id;

    右连接 查找所有的班级，看都有哪些学生

    select *  from students as s right join pythons as p on s.cls_id=p.id;

    -- 自关联
    把一张表作为两个数据源
    create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
    );

    source areas.sql

    select * from areas as c inner join areas as p on c.pid=p.aid where p.atitle='河北省';


**第三天**

-- 子查询

    在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

    标量子查询: 子查询返回的结果是一个数据(一行一列)

        查询班级学生平均年龄
        查询大于平均年龄的学生

        select avg(age) from students;
        select * from students where age>26.7857;

        select * from students where age>(select avg(age) from students);


    列子查询: 返回的结果是一列(一列多行)

        查询还有学生在班的所有班级名字
        找出学生表中所有的班级 id
        找出班级表中对应的名字
        select cls_id from students;
        select * from pythons where id in (select cls_id from students);


    行子查询: 返回的结果是一行(一行多列)

        需求: 查找班级年龄最大,身高最高的学生
        行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素

        select * from students where age=max(age) and height=max(height);

        select * from students where (age,height)=(select max(age),max(height) from students) 

    表级子查询: 返回的结果是多行多列

        select * from (select ...)

        查询学生与班级对应的信息

        select * from (select s.name as sname,p.name as pname from students s inner join pythons p on s.cls_id=p.id) as a

    子查询中 in some any all

    in =some =any

    select * from pythons where id >all(select cls_id from students where cls_id between 2 and 4);

    -- 函数

        随机取一条记录
        select * from stus order by rand() limit 1;

    -- 根据性别显示帅哥或美女

select 
case gender
when 1 then concat(left(name,1),'帅哥')
when 2 then concat(left(name,1),'美女')
else concat(left(name,1),'**')
end as a,
name,gender from students;

a = 3 if b==2 else 1;


    -- 查询演练

    create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
    );

    求所有电脑产品的平均价格,并且保留两位小数

    select round(avg(price),2) from goods;

    查询所有价格大于平均价格的商品，并且按价格降序排序

    select * from goods where price>(select round(avg(price),2) from goods) order by price desc;

    查询类型为'超级本'的商品价格

    select price from goods where cate='超级本';

    查询价格大于或等于"超级本"价格的商品，并且按价格降序排列

    select * from goods where price >=any(select price from goods where cate='超级本') order by price desc;

    -- 数据分表

    创建类型表

create table goods_cates(
id int unsigned not null primary key auto_increment,
name varchar(10)
);

    查询商品中的所有类型
    select distinct cate from goods;

    把从商品表中查询出来的类型插入到类型表中
    insert into goods_cates(name) select distinct cate from goods;

    连接商品表和类型表，获取类型对应的类型id
    select * from goods g inner join goods_cates c on g.cate=c.name;

    更新商品表中的类型

    update goods g inner join goods_cates c on g.cate=c.name set g.cate=c.id;

    修改商品表的cate字段，改为cate_id,int

    alter table goods change cate cate_id int;

    查询商品的类型数据

    select g.name,c.name from goods g inner join goods_cates c on g.cate_id=c.id;

    创建品牌表，且把品牌数据添加到表中

    create table aa select * from students;

create table goods_brands(
id int unsigned not null primary key auto_increment,
name varchar(10)
)
select distinct brand_name as name from goods;

    更新商品表中的品牌

    update goods g inner join goods_brands b on g.brand_name=b.name set g.brand_name=b.id;

    修改商品表中的bran_name,改为brand_id,int

    alter table goods change brand_name brand_id int;

    查询商品的品牌数据
    select * from goods g inner join goods_brands b on g.brand_id=b.id;

    同时查询商品的类型和品牌
    select g.name,b.name,c.name from goods g inner join goods_brands b on g.brand_id=b.id
    inner join goods_cates c on g.cate_id=c.id;

    insert into goods_cates(cate_name) values ('路由器'),('交换机'),('网卡');


    select g.name,b.name,c.name from goods g left join goods_brands b on g.brand_id=b.id
    left join goods_cates c on g.cate_id=c.id;

    select g.name,b.name,c.name from goods g right join goods_cates c on g.cate_id=c.id
    right join goods_brands b on g.brand_id=b.id;

    -- 外键，约束商品表类型和品牌的取值范围

    给商品类型添加外键约束 , 
    注意 goods表中cate_id字段的值需要符合约束
    注意 cate_id 必须 id 类型一致
    alter table goods add foreign key (cate_id) references goods_cates(id);

    给商品品牌添加外键约束

    alter table goods add foreign key (brand_id) references goods_brands(id);
    

    -- 取消外键

    查询外键的名称
    show create table goods;

    alter table goods drop foreign key goods_ibfk_1;

    -- 创建外键时，mysql字段在这一列创建索引

    alter table goods drop index brand_id;


    -- 创建账户并授予权限

        需要使用实例级账户登录后操作，以root为例
        常用权限主要包括：create、alter、drop、insert、update、delete、select
        如果分配所有权限，可以使用all privileges
        创建账户并授权

        语法如下：
        grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';


        grant all privileges on python14.* to 'py'@'%' identified by '123';

    -- 回收权限

        需要使用实例级账户登录后操作，以root为例
        如果不希望某用户拥有此权限，可以将此权限从用户上撤销
        语法如下：
        revoke 权限列表 on 数据库名.* from  '用户名'@'主机';

        revoke insert on python14.* from 'py'@'%';

        flush privileges;

    -- 修改密码

        语法1：不需登录
        mysqladmin -u py -p password '新密码'

        mysqladmin -u py -p password '123456';回车数据旧密码

        语法2：dba使用root账户修改密码
        use mysql;
        update user set authentication_string=password('123') where user='py';
        flush privileges;

    -- 删除用户

        语法1：使用root登录
        drop user '用户名'@'主机';
        例：
        drop user 'py'@'%';
        语法2：使用root登录，删除mysql数据库的user表中数据
        delete from user where user='用户名';
        例：
        delete from user where user='py';

        -- 操作结束之后需要刷新权限
        flush privileges

    -- root 密码忘记

    修改文件/etc/mysql/mysql.conf.d/mysql.cnf
    [mysqld]
     skip-grant-tables

     重启

     直接输入 mysql 登录

     update user set authentication_string=password('123') where user='root';

     删除 skip-grant-tables