• 2024.8.7（SQL语句）

    一、回顾

    1、主服务器

    [root@slave-mysql ~]# yum -y install rsync
    [root@master-mysql ~]# yum -y install rsync

    [root@master-mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar 
    [root@master-mysql ~]# ls
    [root@master-mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
    [root@master-mysql ~]# ls
    [root@master-mysql ~]# vim mysql-8.0.33-linux-glibc2.12-x86_64/support-files/mysql.server 
    [root@master-mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
    [root@master-mysql ~]# cd /usr/local/mysql/
    [root@master-mysql mysql]# ls


    [root@master-mysql mysql]# mkdir mysql-files
    [root@master-mysql mysql]# ll

    [root@master-mysql mysql]# useradd -r -s /sbin/nologin mysql
    [root@master-mysql mysql]# id mysql
    uid=997(mysql) gid=995(mysql) 组=995(mysql)
    [root@master-mysql mysql]# chown mysql:mysql ./mysql-files/
    [root@master-mysql mysql]# ll

    [root@master-mysql mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql
    [root@master-mysql mysql]# ls

    [root@master-mysql mysql]# ./bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
    [root@master-mysql mysql]# cp support-files/mysql.server /etc/init.d/mysql8
    [root@master-mysql mysql]# vim my.cnf


    [root@master-mysql mysql]# service mysql8 start

    [root@master-mysql mysql]# ./bin/mysql -P 3306 -p

    [root@master-mysql mysql]# vim my.cnf

    [mysqld]
    basedir=/usr/local/mysql/
    datadir=/usr/local/mysql/data
    socket=/tmp/mysql.sock
    port=3306
    log-error=/usr/local/mysql/data/err.log
    log-bin=/usr/local/mysql/data/binlog
    charactor_set_server=utf8mb4

    server_id=10

    [root@master-mysql mysql]# service mysql8 restart
    [root@master-mysql mysql]# ls data

    2、从服务器（不用初始化）

    [root@slave-mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar 
    [root@slave-mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
    [root@slave-mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
    [root@slave-mysql ~]# useradd -r -s /sbin/nologin mysql
    [root@slave-mysql ~]# mkdir /usr/local/mysql/mysql-files
    [root@slave-mysql ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
    [root@slave-mysql ~]# ll /usr/local/mysql/


    [root@slave-mysql ~]# rm -rf /etc/my.cnf
    [root@slave-mysql ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

    3、主从同步

    [root@master-mysql mysql]# rm -rf /usr/local/mysql/data/auto.cnf

    [root@master-mysql mysql]# ls /usr/local/mysql/data/
    [root@master-mysql mysql]# rsync -av /usr/local/mysql/data root@192.168.8.128:/usr/local/mysql/
     

    [root@slave-mysql ~]# vim /usr/local/mysql/data/my.cnf


    [root@slave-mysql ~]# service mysql8 start

    [root@slave-mysql ~]# /usr/local/mysql/bin/mysql -pN6s9znjbL*/s

    4、主从实现
    1. 主服务器

    [root@master-mysql mysql]# vim /etc/profile


    [root@master-mysql mysql]# source /etc/profile

    [root@master-mysql mysql]# mysql -p

    1. mysql> create user 'slave'@'192.168.8.%' identified by 'slave_123'';
    3. Query OK, 0 rows affected (0.01 sec)
    5. mysql> grant replication slave on *.* to 'slave'@'192.168.8.%';
    6. Query OK, 0 rows affected (0.01 sec)
    8. mysql> flush privileges;
    9. Query OK, 0 rows affected (0.01 sec)
    11. mysql> flush tables with read lock;
    12. Query OK, 0 rows affected (0.00 sec)
    14. mysql> show master status;
    15. +---------------+----------+--------------+------------------+-------------------+
    16. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    17. +---------------+----------+--------------+------------------+-------------------+
    18. | binlog.000004 |     1198 |              |                  |                   |
    19. +---------------+----------+--------------+------------------+-------------------+
    20. 1 row in set (0.00 sec)
    2. 从服务器 

    [root@slave-mysql ~]# vim /etc/profile


    [root@slave-mysql ~]# source /etc/profile

    [root@slave-mysql ~]# mysql -h192.168.8.139 -uslave -pslave_123 --get-server-public-key

    [root@slave-mysql ~]# mysql -p123456 -P3306

    1. mysql> stop slave;
    2. Query OK, 0 rows affected, 2 warnings (0.00 sec)
    3.  
    4. mysql> reset slave;
    5. Query OK, 0 rows affected, 1 warning (0.00 sec)
    6.  
    7. mysql> change master to
    8.     -> master_host='192.168.8.139',
    9.     -> master_user='slave',
    10.     -> master_password='slave_123',
    11.     -> master_log_file='binlog.000004',
    12.     -> master_log_pos=3296;
    13. Query OK, 0 rows affected, 8 warnings (0.02 sec)
    14.  
    15. mysql> start slave;
    16. Query OK, 0 rows affected, 1 warning (0.01 sec)
    17.  
    18. mysql> show slave status\G;
    3. 主服务器创建表，添加数据
    1. mysql> unlock tables;
    2. Query OK, 0 rows affected (0.01 sec)
    3. mysql> create database if not exists test charset utf8mb4;
    4. Query OK, 1 row affected (0.01 sec)
    5.  
    6. mysql> use test;
    7. Database changed
    8. mysql> create table student(id int primary key,name varchar(45)not null,gender varchar(4) not null);
    9. Query OK, 0 rows affected (0.02 sec)
    10. mysql> insert into student values(1,'张三','男');
    11. Query OK, 1 row affected (0.02 sec)
    12.  
    13. mysql> insert into student values(2,'李四','男');
    14. Query OK, 1 row affected (0.00 sec)
    15.  
    16. mysql> insert into student values(3,'王五','男');
    17. Query OK, 1 row affected (0.00 sec)
    18.  
    19. mysql> select * from student;
    20. +----+--------+--------+
    21. | id | name   | gender |
    22. +----+--------+--------+
    23. |  1 | 张三   | 男     |
    24. |  2 | 李四   | 男     |
    25. |  3 | 王五   | 男     |
    26. +----+--------+--------+
    4. 从服务器查看同步并写入东西（从服务器不容许写入东西）
    1. mysql> show databases;
    2. +--------------------+
    3. | Database           |
    4. +--------------------+
    5. | information_schema |
    6. | mysql              |
    7. | performance_schema |
    8. | sys                |
    9. | test               |
    10. +--------------------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> use test;
    14. Reading table information for completion of table and column names
    15. You can turn off this feature to get a quicker startup with -A
    16.  
    17. Database changed
    18. mysql> select * from student;
    19. +----+--------+--------+
    20. | id | name   | gender |
    21. +----+--------+--------+
    22. |  1 | 张三   | 男     |
    23. |  2 | 李四   | 男     |
    24. |  3 | 王五   | 男     |
    25. +----+--------+--------+
    26. 3 rows in set (0.00 sec)
    27.  
    28. mysql> insert into student values(4,'李网','女');
    29. Query OK, 1 row affected (0.00 sec)
    30.  
    31. mysql> select * from student;
    32. +----+--------+--------+
    33. | id | name   | gender |
    34. +----+--------+--------+
    35. |  1 | 张三   | 男     |
    36. |  2 | 李四   | 男     |
    37. |  3 | 王五   | 男     |
    38. |  4 | 李网   | 女     |
    39. +----+--------+--------+
    40. 4 rows in set (0.00 sec)
    41.  
    42. mysql> insert into student values(6,'章节','男');
    43. Query OK, 1 row affected (0.01 sec)
    44.  
    45. mysql> select * from student;
    46. +----+--------+--------+
    47. | id | name   | gender |
    48. +----+--------+--------+
    49. |  1 | 张三   | 男     |
    50. |  2 | 李四   | 男    |
    51. |  3 | 王五  | 男     |
    52. |  6 | 章节   | 男     |
    53. +----+--------+--------+
    54. 4 rows in set (0.00 sec)
    5. 主服务器插入数据
    1. mysql> select * from student;
    2. +----+--------+--------+
    3. | id | name   | gender |
    4. +----+--------+--------+
    5. |  1 | 张三   | 男     |
    6. |  2 | 李四   | 男     |
    7. |  3 | 王五   | 男     |
    8. |  4 | 李网   | 女     |
    9. |  6 | 章节   | 男     |
    10. +----+--------+--------+
    11. 5 rows in set (0.00 sec)
    6. 主服务器查看
    1. mysql> select * from student;
    2. +----+--------+--------+
    3. | id | name   | gender |
    4. +----+--------+--------+
    5. |  1 | 张三   | 男     |
    6. |  2 | 李四   | 男     |
    7. |  3 | 王五   | 男     |
    8. |  4 | 李网   | 女     |
    9. |  6 | 章节   | 男     |
    10. +----+--------+--------+
    11. 5 rows in set (0.00 sec)

    二、SQL语句

    1、新增

            1. insert into库名称.表名

            （id，username，password）values（1,"abc","123"）

            2. insert into 表名称 values(1, "name","word")

            3. insert into 表名称 select* from 其他表

            4. insert into 表 values (),()

    2、删除

            1. delete from 表名

            2. deletefrom表名称 where id=3

            3. delete from 表名称 where age>8

            4. delete from 表 where name on （"a","b","c"）

    3、修改

            1. update mysql.user set host='%' where name='root'

            2. update user set password='abc' where username="zhangsan"

    4、查询
            1. 单表查询

                    1.1 select 字段名列表 from表名称，索引

            2. 多表查询  
    1. mysql> select count(*) from student;
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. |        3 |
    6. +----------+
    7. 1 row in set (0.05 sec)
    8.  
    9. mysql> select count(1) from student;
    10. +----------+
    11. | count(1) |
    12. +----------+
    13. |        3 |
    14. +----------+
    15. 1 row in set (0.06 sec)
    16.  
    17. mysql> select count(id) from student;
    18. +-----------+
    19. | count(id) |
    20. +-----------+
    21. |         3 |
    22. +-----------+
    23. 1 row in set (0.06 sec)
    24.  
    25. mysql> select a.*,b.* from student as a,student as b;
    26. +----+--------+--------+----+--------+--------+
    27. | id | name   | gender | id | name   | gender |
    28. +----+--------+--------+----+--------+--------+
    29. |  3 | 王五   | 男     |  1 | 张三   | 男     |
    30. |  2 | 李四   | 男     |  1 | 张三   | 男     |
    31. |  1 | 张三   | 男     |  1 | 张三   | 男     |
    32. |  3 | 王五   | 男     |  2 | 李四   | 男     |
    33. |  2 | 李四   | 男     |  2 | 李四   | 男     |
    34. |  1 | 张三   | 男     |  2 | 李四   | 男     |
    35. |  3 | 王五   | 男     |  3 | 王五   | 男     |
    36. |  2 | 李四   | 男     |  3 | 王五   | 男     |
    37. |  1 | 张三   | 男     |  3 | 王五   | 男     |
    38. +----+--------+--------+----+--------+--------+
    39. 9 rows in set (0.00 sec)
    5、远程连接数据库
            1. username
            2. password
            3. url   mysql IP地址 数据库名称 端口
    1. mysql> create user 'abc'@'%' identified by '123';
    2. Query OK, 0 rows affected (0.01 sec)
    3.  
    4. mysql> grant all on *.* to 'abc'@'%';
    5. Query OK, 0 rows affected (0.00 sec)
    6.  
    7. mysql> flush privileges;
    8. Query OK, 0 rows affected (0.00 sec)
    6、别名
    1. mysql> select id,name,gender from student;
    2. +----+--------+--------+
    3. | id | name   | gender |
    4. +----+--------+--------+
    5. |  1 | 张三   | 男     |
    6. |  2 | 李四   | 男     |
    7. |  3 | 王五   | 男     |
    8. +----+--------+--------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> select id as 编号,name,gender from student;
    12. +--------+--------+--------+
    13. | 编号   | name   | gender |
    14. +--------+--------+--------+
    15. |      1 | 张三   | 男     |
    16. |      2 | 李四   | 男     |
    17. |      3 | 王五   | 男     |
    18. +--------+--------+--------+
    19. 3 rows in set (0.00 sec)

    三、MySQL 函数

    数据分析的基础

    1、排序
            1. max
            2. min
    1. mysql> select max(price) from product group by name;
    2. +------------+
    3. | max(price) |
    4. +------------+
    5. |        8.5 |
    6. |       12.5 |
    7. |       12.4 |
    8. |       18.3 |
    9. +------------+
    10. 4 rows in set (0.00 sec)
    11.  
    12. mysql> select max(price) from product;
    13. +------------+
    14. | max(price) |
    15. +------------+
    16. |       18.3 |
    17. +------------+
    18. 1 row in set (0.00 sec)
    19.  
    20. mysql> select min(price) from product;
    21. +------------+
    22. | min(price) |
    23. +------------+
    24. |        8.5 |
    25. +------------+
    26. 1 row in set (0.00 sec)
    2、汇总
            1. count
            2. sum
            3. avg
    1. mysql> select sum(price) from product;
    2. +-------------------+
    3. | sum(price)        |
    4. +-------------------+
    5. | 51.69999885559082 |
    6. +-------------------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select avg(price) from product;
    10. +--------------------+
    11. | avg(price)         |
    12. +--------------------+
    13. | 12.924999713897705 |
    14. +--------------------+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql> select *,price*qty as tt from product;
    18. +----+-----------+-------+-----+-------------------+
    19. | id | name      | price | qty | tt                |
    20. +----+-----------+-------+-----+-------------------+
    21. |  1 | 香蕉      |   8.5 | 200 |              1700 |
    22. |  2 | 苹果      |  12.5 | 400 |              5000 |
    23. |  3 | 菠萝      |  12.4 |  70 | 867.9999732971191 |
    24. |  4 | 哈密瓜    |  18.3 | 400 | 7319.999694824219 |
    25. +----+-----------+-------+-----+-------------------+
    26. 4 rows in set (0.00 sec)
    27.  
    28. mysql> select sum(tt) from (select *,price*qty as tt from productt) as a;
    29. +--------------------+
    30. | sum(tt)            |
    31. +--------------------+
    32. | 14887.999668121338 |
    33. +--------------------+
    34. 1 row in set (0.00 sec)
    3、数制

            1. 二进制

            2. 八进制

            3. 十进制

            4. 十六进制

            5. AA 27

    1. mysql> select * from student order by gender desc;
    2. +----+--------+--------+
    3. | id | name   | gender |
    4. +----+--------+--------+
    5. |  1 | 张三   | 男     |
    6. |  2 | 李四   | 男     |
    7. |  3 | 王五   | 男     |
    8. |  4 | 花花   | 女     |
    9. |  5 | 花     | 女     |
    10. +----+--------+--------+
    11. 5 rows in set (0.00 sec)
    12.  
    13. mysql> select * from student order by gender asc;
    14. +----+--------+--------+
    15. | id | name   | gender |
    16. +----+--------+--------+
    17. |  4 | 花花   | 女     |
    18. |  5 | 花     | 女     |
    19. |  1 | 张三   | 男     |
    20. |  2 | 李四   | 男     |
    21. |  3 | 王五   | 男     |
    22. +----+--------+--------+
    23. 5 rows in set (0.00 sec)
    4、聚合函数

    只有select子句和having子句、order by子句中能使用聚合函数,where子句不能使用聚合函
    数。当使用聚合函数查询以后，不能使用where条件，如果要添加条件，

    1. mysql> select gender,count(gender) from student group by gender;
    2.  
    3. +--------+---------------+
    4. | gender | count(gender) |
    5. +--------+---------------+
    6. | 男     |             3 |
    7. | 女     |             2 |
    8. +--------+---------------+
    9. 2 rows in set (0.01 sec)
    10.  
    11. mysql> select gender as 性别,count(gender) as 人数 from studentgroup by gender;
    12. +--------+--------+
    13. | 性别   | 人数   |
    14. +--------+--------+
    15. | 男     |      3 |
    16. | 女     |      2 |
    17. +--------+--------+
    18. 2 rows in set (0.00 sec)
    1. mysql> create table product(
    2.     -> id int primary key auto_increment,
    3.     -> name varchar(45) not null,
    4.     -> price float not null,
    5.     -> qty int not null);
    6. Query OK, 0 rows affected (0.01 sec)
    7.  
    8. mysql> desc product;
    9. +-------+-------------+------+-----+---------+----------------+
    10. | Field | Type        | Null | Key | Default | Extra          |
    11. +-------+-------------+------+-----+---------+----------------+
    12. | id    | int         | NO   | PRI | NULL    | auto_increment |
    13. | name  | varchar(45) | NO   |     | NULL    |                |
    14. | price | float       | NO   |     | NULL    |                |
    15. | qty   | int         | NO   |     | NULL    |                |
    16. +-------+-------------+------+-----+---------+----------------+
    17. 4 rows in set (0.01 sec)
    18.  
    19. mysql> insert into product (name,price,qty) values("香蕉",8.5,200);
    20. Query OK, 1 row affected (0.01 sec)
    21.  
    22. mysql> insert into product (name,price,qty) values("苹果",12.5,4000);
    23. Query OK, 1 row affected (0.00 sec)
    24.  
    25. mysql> insert into product (name,price,qty) values("菠萝",12.4,700);
    26. Query OK, 1 row affected (0.00 sec)
    27.  
    28. mysql> insert into product (name,price,qty) values("哈密瓜",18.3,,400);
    29. Query OK, 1 row affected (0.00 sec)
    30.  
    31. mysql> select * from product;
    32. +----+-----------+-------+-----+
    33. | id | name      | price | qty |
    34. +----+-----------+-------+-----+
    35. |  1 | 香蕉      |   8.5 | 200 |
    36. |  2 | 苹果      |  12.5 | 400 |
    37. |  3 | 菠萝      |  12.4 |  70 |
    38. |  4 | 哈密瓜    |  18.3 | 400 |
    39. +----+-----------+-------+-----+
    40. 4 rows in set (0.00 sec)
    41.  
    42. mysql> select * from product order by qty;
    43. +----+-----------+-------+-----+
    44. | id | name      | price | qty |
    45. +----+-----------+-------+-----+
    46. |  3 | 菠萝      |  12.4 |  70 |
    47. |  1 | 香蕉      |   8.5 | 200 |
    48. |  2 | 苹果      |  12.5 | 400 |
    49. |  4 | 哈密瓜    |  18.3 | 400 |
    50. +----+-----------+-------+-----+
    51. 4 rows in set (0.00 sec)
    52.  
    53. mysql> select * from product order by price;
    54. +----+-----------+-------+-----+
    55. | id | name      | price | qty |
    56. +----+-----------+-------+-----+
    57. |  1 | 香蕉      |   8.5 | 200 |
    58. |  3 | 菠萝      |  12.4 |  70 |
    59. |  2 | 苹果      |  12.5 | 400 |
    60. |  4 | 哈密瓜    |  18.3 | 400 |
    61. +----+-----------+-------+-----+
    62. 4 rows in set (0.00 sec)
    63. mysql> select * from (select * from product order by qty) as a oerder by a.price;
    64. +----+-----------+-------+-----+
    65. | id | name      | price | qty |
    66. +----+-----------+-------+-----+
    67. |  1 | 香蕉      |   8.5 | 200 |
    68. |  3 | 菠萝      |  12.4 |  70 |
    69. |  2 | 苹果      |  12.5 | 400 |
    70. |  4 | 哈密瓜    |  18.3 | 400 |
    71. +----+-----------+-------+-----+
    72. 4 rows in set (0.00 sec)
    73.

     

    1. mysql> select now();
    2. +---------------------+
    3. | now()               |
    4. +---------------------+
    5. | 2024-08-07 15:30:50 |
    6. +---------------------+
    7. 1 row in set (0.00 sec)
    8.  
    9. mysql> select year(now());
    10. +-------------+
    11. | year(now()) |
    12. +-------------+
    13. |        2024 |
    14. +-------------+
    15. 1 row in set (0.00 sec)
    16.  
    17. mysql> select second(now());
    18. +---------------+
    19. | second(now()) |
    20. +---------------+
    21. |            13 |
    22. +---------------+
    23. 1 row in set (0.00 sec)
    24.  
    25. mysql> insert into product (name,price,qty) values (now(),7.8,90)
    26. );
    27. Query OK, 1 row affected (0.00 sec)
    28.  
    29. mysql> select * from product;
    30. +----+---------------------+-------+-----+
    31. | id | name                | price | qty |
    32. +----+---------------------+-------+-----+
    33. |  1 | 香蕉                |   8.5 | 200 |
    34. |  2 | 苹果                |  12.5 | 400 |
    35. |  3 | 菠萝                |  12.4 |  70 |
    36. |  4 | 哈密瓜              |  18.3 | 400 |
    37. |  5 | 2024-08-07 15:33:08 |   7.8 |  90 |
    38. +----+---------------------+-------+-----+
    39. 5 rows in set (0.00 sec)

  • 相关阅读:
    数字人民币如何将支付宝钱包余额转入到微信支付钱包余额？
    Git基础
    Docker技术概论（2）：Docker环境的搭建
    python函数运行加速
    硬件管理平台-硬件产品库-硬件项目
    openstack 环境配置及基础组件安装
    【Hadoop快速入门】Hdfs、MapReduce、Yarn
    受害者被锤 法官遭殃 背后的它公关赢了？
    深入理解Kafka分区副本机制
    【深度学习】04-01-自注意力机制（Self-attention）-李宏毅老师21&22深度学习课程笔记
  • 原文地址：https://blog.csdn.net/weixin_70751278/article/details/140973850