mysql学习笔记1

最新推荐文章于 2024-05-19 03:18:57 发布
天是蓝的嘛
最新推荐文章于 2024-05-19 03:18:57 发布
阅读量40
点赞数
分类专栏： Java 文章标签： mysql 学习数据库
本文链接：https://blog.csdn.net/qq_52983712/article/details/129045383
版权
Java 专栏收录该内容
8 篇文章 0 订阅
订阅专栏
1、Sql:结构化查询语言
        程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，
        最终来完成数据库中数据的增删改查功能。
   三者之间的关系：
        DBMS-->执行-->SQL-->操作-->DB(存储数据的仓库)

2、SQL常用命令
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)

    mysql> CREATE DATABASE nanjing;
    Query OK, 1 row affected (0.00 sec)
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | nanjing            |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)

    mysql> use mysql;
    Database changed
    mysql> show tables;
    +---------------------------+
    | Tables_in_mysql           |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | engine_cost               |
    | event                     |
    | func                      |
    | general_log               |
    | gtid_executed             |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | innodb_index_stats        |
    | innodb_table_stats        |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | proxies_priv              |
    | server_cost               |
    | servers                   |
    | slave_master_info         |
    | slave_relay_log_info      |
    | slave_worker_info         |
    | slow_log                  |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    31 rows in set (0.00 sec)

    mysql> select *from db;
    +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    | Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
    +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    | localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
    | localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
    +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    2 rows in set (0.00 sec)

    mysql> desc db;
    +-----------------------+---------------+------+-----+---------+-------+
    | Field                 | Type          | Null | Key | Default | Extra |
    +-----------------------+---------------+------+-----+---------+-------+
    | Host                  | char(60)      | NO   | PRI |         |       |
    | Db                    | char(64)      | NO   | PRI |         |       |
    | User                  | char(32)      | NO   | PRI |         |       |
    | Select_priv           | enum('N','Y') | NO   |     | N       |       |
    | Insert_priv           | enum('N','Y') | NO   |     | N       |       |
    | Update_priv           | enum('N','Y') | NO   |     | N       |       |
    | Delete_priv           | enum('N','Y') | NO   |     | N       |       |
    | Create_priv           | enum('N','Y') | NO   |     | N       |       |
    | Drop_priv             | enum('N','Y') | NO   |     | N       |       |
    | Grant_priv            | enum('N','Y') | NO   |     | N       |       |
    | References_priv       | enum('N','Y') | NO   |     | N       |       |
    | Index_priv            | enum('N','Y') | NO   |     | N       |       |
    | Alter_priv            | enum('N','Y') | NO   |     | N       |       |
    | Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
    | Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
    | Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
    | Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
    | Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
    | Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
    | Execute_priv          | enum('N','Y') | NO   |     | N       |       |
    | Event_priv            | enum('N','Y') | NO   |     | N       |       |
    | Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
    +-----------------------+---------------+------+-----+---------+-------+
    22 rows in set (0.01 sec)

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.41    |
    +-----------+
    1 row in set (0.00 sec)

    mysql> select database();
    +------------+
    | database() |
    +------------+
    | mysql      |
    +------------+
    1 row in set (0.00 sec)


3、数据库中最基础的单元是表（类似Excel），为啥用表存储数据？
        直观
4、SQL语句的分类：
    DQL(Data Query Language)：数据查询语言（凡是带有select关键字的都是查询语句）
         select * from 表名；
    DML(Data Manipulation Language):数据操作语言（凡是对表中的数据进行增删改的都是DML）
        insert delete update
        insert 增
        delete 删
        update 改
        以上主要时操作表中的data
    DDL(Data Definition Language)):数据定义语言
        凡是带有create、drop、alter的都是DDL。
        DDL主要操作的是表的结构，非表的数据。
        create：新建，等同于增。
        drop：删除
        alter：修改
        以上是对表结构进行操作。
    TCL(Transaction Control Language)：事务控制语言
        commit：事务提交
        rollback：事务回滚
    DCL(Data Control Language):数据控制语言。
        例如：grant：授权
             revoke：撤销权限
5、DQL:
    mysql> desc db;
    mysql>  select Host from db; //一个字段
    +-----------+
    | Host      |
    +-----------+
    | localhost |
    | localhost |
    +-----------+
    2 rows in set (0.00 sec)

    mysql> select host,user from db;//多个字段，用","隔开。
    +-----------+---------------+
    | host      | user          |
    +-----------+---------------+
    | localhost | mysql.session |
    | localhost | mysql.sys     |
    +-----------+---------------+
    2 rows in set (0.00 sec)

    mysql>  select * from db;  //效率较低，可读性差。
    +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    | Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
    +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    | localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
    | localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
    +-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    2 rows in set (0.00 sec)

    mysql> select Host,Db as hb from db; //改名（若名字中有空格/中文，用单引号括起来）---只是修改的查询不改变原来的表名。
    +-----------+--------------------+
    | Host      | hb                 |
    +-----------+--------------------+
    | localhost | performance_schema |
    | localhost | sys                |
    +-----------+--------------------+
    2 rows in set (0.01 sec)

    字段可以使用字段表达式。
6、条件查询（where）
    mysql> select host,DB from db where DB='sys';
    +-----------+-----+
    | host      | DB  |
    +-----------+-----+
    | localhost | sys |
    +-----------+-----+
    1 row in set (0.00 sec)

    mysql> select host,DB from db where DB!='sys';
    +-----------+--------------------+
    | host      | DB                 |
    +-----------+--------------------+
    | localhost | performance_schema |
    +-----------+--------------------+
    1 row in set (0.00 sec)
    若为null的话，使用 is null。
    并列条件用or。
    and和or同时出现，and优先级高。（相同字段用or,不同字段用and）
    in:对象（not）in(条件一，条件二，...)    相当于多个or
模糊查询：like。
    %：任意多个字符
    _: 任意一个字符
   对象 like '_a%',找出第二个字母以a开头的。
        若找出其中有下划线的，采用'%\_%'。
7、排序
    mysql> select user,table_scans from x$user_summary order by user desc;
    +------------+-------------+
    | user       | table_scans |
    +------------+-------------+
    | root       |          11 |
    | background |           0 |
    +------------+-------------+
    2 rows in set (0.01 sec)

    mysql> select user,table_scans from x$user_summary order by user;
    +------------+-------------+
    | user       | table_scans |
    +------------+-------------+
    | background |           0 |
    | root       |          12 |
    +------------+-------------+
    2 rows in set (0.01 sec)

    mysql> select user,table_scans from x$user_summary order by table_scans;
    +------------+-------------+
    | user       | table_scans |
    +------------+-------------+
    | background |           0 |
    | root       |          13 |
    +------------+-------------+
    2 rows in set (0.01 sec)
8、数据处理函数
    单行处理函数（一对一处理）和多行处理函数（多对一处理）
    lower 小写}        +（对象）
    upper 大写}
    substr（对象，起始位置（1默认开始），截取长度），可用于模糊查询
    concat函数   字符串拼接   concat(对象一，对象二)
    length 长度
    trim   去空格
   select后面可以跟某个表的字段名（可以等同看作变量名），也可以跟字面值/字面量(同样可看作为数据)
   eg. select round(1234.567,0) as num from emp;     保留整数
    rand()生成随机数
    ifnull ,数据库中只要有NULL参与的运算结果就为null。为了避免，使用ifnull，方法为ifnull(数据，被当作那个值)。


    case...when...then...when...then...else...end
  注释(if(           )      elseif(  )    else()    )

9、多行处理函数（也叫分组函数）
    count
    sum
    avg
    min
    max
    使用之前必须分组，不分组默认为一组数据。同时，分组函数自动忽略null。
    所有的分组函数都可以组合起来用：select sum(sal),min(sal),max(sal),count(*) from emp;
10、分组查询
    select job,sum(sal) from emp group by job;
    在一条select语句中，如果存在group by的话，Select后面只能跟参加分组的字段，分组函数。其他一定不能跟。
11、大总结
     select
     。。。
     from
     。。。
     where
     。。。
     group by
     ...
     having
     ...
     order by
     ...
     执行顺序：1、from 2、where 3、group by 4、having 5、select 6、order by
     eg.
    select
    job，avg(sal) as avgsal
    from
    emp
    where
    job <> 'MANAGEAR'
    group by
    job
    order by
    avgsal desc;
12、distinct只能出现所有字段的最前方，表示去除所有字段（并的关系）的重复。
    count（distinct job）,并列
13、连接查询
        从多张表中联合查询数据
    分类：内连接：等值连接
                非等值连接
                自连接
         外连接：左外连接（左连接）
                右外连接（右连接）
         全连接(不讲)
14、避免笛卡尔积现象要加条件
        select e.name,d.dname from emp e,dept.d where e.deptno = d.deptno;
15、内连接之等值连接
    SQL99
    select
        e.name,d.dname
    from
        emp e
    (inner) join
        dept d
    on
        e.deptno=d.deptno
    where ....
16、内连接之非等值连接
    on ... between ... and ...
17、内连接之自连接

18、外连接
一张表是主表
    select
        e.name,d.dname
    from
        emp e
    left (outer) join
        dept d
    on
        e.deptno=d.deptno
19.子查询
where子查询
    select
    ename,sal
    from
    emp
    where
    sal>(select min(sal) from emp);
from子查询
    可以将子查询的查询结果当成一张临时表
     select
     t.*,s.grade
     from
     (select job,avg(sal) as avgval from emp group by job) t
     join
     salgrade s
     on
     t.avgval between s.losal and s.hisal;
select子查询
    了解
20.Union用法
    select ename，job from emp where job='MANAGER'
    union
    select ename，job from emp where job='MANAGER';
    优点：表连接次数减少，时间复杂度提升

21、limit（*****）
    作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
    类似百度网页：一页显示10条记录。
    提高用户体验
    完整用法：limit startIndex，length（缺省默认从零开始）
    select ename，sal from emp order by sal desc limit 0,5;//显示前5条记录
    ***优先级：limit在order by后面执行
 分页：limit (pageno-1)*pagesize,pagesize