Mariadb学习笔记

最新推荐文章于 2021-01-27 16:48:47 发布
最新推荐文章于 2021-01-27 16:48:47 发布
阅读量206 收藏
点赞数
分类专栏： Linux DataBase 文章标签： 学习笔记 mariadb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43520186/article/details/102706443
版权

数据库操作

数据库的创建

    MariaDB [(none)]> create database testdb;
    Query OK, 1 row affected (0.00 sec)

查看数据库列表

    MariaDB [(none)]> create database testdb;
    Query OK, 1 row affected (0.00 sec)

    MariaDB [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | hellodb            |
    | information_schema |
    | mysql              |
    | performance_schema |
    | testdb             |
    +--------------------+
    5 rows in set (0.00 sec)

进入testdb数据库

    MariaDB [(none)]> use testdb;
    Database changed
    MariaDB [testdb]>

数据库表操作

创建表

  • 方法一：常用
    MariaDB [testdb]> create table student (
        -> id int unsigned auto_increment primary key,
        -> name char(10) not null,
        -> age tinyint unsigned,
        -> gender enum('m','f') default 'm',
        -> mobile char(11) unique );
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [testdb]> show tables;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | student          |
    +------------------+
    1 row in set (0.00 sec)    

    #查看表结构

    MariaDB [testdb]> desc student;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    | name   | char(10)            | NO   |     | NULL    |                |
    | age    | tinyint(3) unsigned | YES  |     | NULL    |                |
    | gender | enum('m','f')       | YES  |     | m       |                |
    | mobile | char(11)            | YES  | UNI | NULL    |                |
    +--------+---------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
  • 方法二：根据已有表进行创建，里面有数据
    MariaDB [testdb]> create table user select user,host,password from mysql.user;
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    MariaDB [testdb]> show tables;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | student          |
    | user             |
    +------------------+
    2 rows in set (0.00 sec)

    MariaDB [testdb]> desc user;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | user     | char(80) | NO   |     |         |       |
    | host     | char(60) | NO   |     |         |       |
    | password | char(41) | NO   |     |         |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.01 sec)

    MariaDB [testdb]> select * from user;
    +------+-----------+-------------------------------------------+
    | user | host      | password                                  |
    +------+-----------+-------------------------------------------+
    | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    | root | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    +------+-----------+-------------------------------------------+
    3 rows in set (0.00 sec)
  • 方法三：根据已有表进行创建，里面无数据
    MariaDB [testdb]> create table newuser like user; 
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [testdb]> show tables;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | newuser          |
    | student          |
    | user             |
    +------------------+
    3 rows in set (0.00 sec)

    MariaDB [testdb]> desc newuser;
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
    +----------+----------+------+-----+---------+-------+
    | user     | char(80) | NO   |     |         |       |
    | host     | char(60) | NO   |     |         |       |
    | password | char(41) | NO   |     |         |       |
    +----------+----------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

    MariaDB [testdb]> select * from newuser;
    Empty set (0.00 sec)

修改表

表名修改

    MariaDB [test]> desc student;
    +--------+---------------------+------+-----+---------+----------------
    | Field  | Type                | Null | Key | Default | Extra          
    +--------+---------------------+------+-----+---------+----------------
    | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment 
    | name   | char(10)            | NO   |     | NULL    |                
    | age    | tinyint(3) unsigned | YES  |     | NULL    |                
    | gender | enum('m','f')       | YES  |     | m       |                
    +--------+---------------------+------+-----+---------+----------------
    4 rows in set (0.00 sec)

    MariaDB [test]> alter table student rename teacher;
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [test]> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | teacher        |
    +----------------+
    1 row in set (0.00 sec)

删除表

    MariaDB [test]> drop table teacher;
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [test]> show tables;
    Empty set (0.00 sec)

表字段操作

添加字段

    MariaDB [test]> alter table student add gender enum('m','f') default 'm'  after age;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    MariaDB [test]> desc student;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    | name   | varchar(20)         | NO   |     | NULL    |                |
    | age    | tinyint(3) unsigned | YES  |     | NULL    |                |
    | gender | enum('m','f')       | YES  |     | m       |                |
    +--------+---------------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)

修改字段

    MariaDB [test]> alter table student change name name char(10) not null;
    Query OK, 0 rows affected (0.02 sec)               
    Records: 0  Duplicates: 0  Warnings: 0

    MariaDB [test]> desc student;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    | name   | char(10)            | NO   |     | NULL    |                |
    | age    | tinyint(3) unsigned | YES  |     | NULL    |                |
    | gender | enum('m','f')       | YES  |     | m       |                |
    +--------+---------------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)

删除字段

    MariaDB [test]> alter table student drop gender;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    MariaDB [test]> desc student;
    +-------+---------------------+------+-----+---------+----------------+
    | Field | Type                | Null | Key | Default | Extra          |
    +-------+---------------------+------+-----+---------+----------------+
    | id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(20)         | NO   |     | NULL    |                |
    | age   | tinyint(3) unsigned | YES  |     | NULL    |                |
    +-------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)

表中数据操作

插入数据 insert

  • 方法一 常用
    MariaDB [testdb]> insert student (name,age,mobile,gender) values('zhao',30,10086,'m');
    Query OK, 1 row affected (0.00 sec)

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    +----+------+------+--------+--------+
    1 row in set (0.00 sec)
  • 方法二
    MariaDB [testdb]> insert student set name='qian',age=28,mobile='10010';
    Query OK, 1 row affected (0.00 sec)

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   28 | m      | 10010  |
    +----+------+------+--------+--------+
    2 rows in set (0.01 sec)

    MariaDB [testdb]> insert student set name='su',age=18,mobile='10000',gender='f';
    Query OK, 1 row affected (0.01 sec)

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   28 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.00 sec)
  • 方法三
    # 根据student表，生成一张teacher的表
    MariaDB [testdb]> create table teacher like student;
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [testdb]> select * from teacher;
    Empty set (0.00 sec)

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   28 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.00 sec)

    # 将查询到的数据添加到teacher表中
    MariaDB [testdb]> insert teacher select * from student;
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    MariaDB [testdb]> select * from teacher;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   28 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.01 sec)

更新数据 updata

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   28 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.00 sec)

    MariaDB [testdb]> update student set age=24 where id=2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   24 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.00 sec)

  • 注update使用时注意不要忘记加where条件，不加表示修改所有字段

  • 可以使用 -U选项，mysql -U -u root -p 'passwd'

  • 可以将mysql -U 添加为别名 mysql='mysql -U'

  • 或在配置文件的[mysql]中添加 safe-updates 项

删除数据

  • 只能删除表中数据，不能删除表

delete

  • 不加where，只删除表中数据，不删除表
    MariaDB [testdb]> select * from teacher;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   28 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.00 sec)

    MariaDB [testdb]> delete from teacher;
    Query OK, 3 rows affected (0.00 sec)

    MariaDB [testdb]> show tables;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | newuser          |
    | student          |
    | teacher          |
    | user             |
    +------------------+
    4 rows in set (0.00 sec)

    MariaDB [testdb]> select * from teacher;
    Empty set (0.00 sec)
  • 加where，只删除与where条件匹配的数据
    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   24 | m      | 10010  |
    |  3 | su   |   18 | f      | 10000  |
    +----+------+------+--------+--------+
    3 rows in set (0.00 sec)

    MariaDB [testdb]> delete from student where id=3;
    Query OK, 1 row affected (0.00 sec)

    MariaDB [testdb]> select * from student;
    +----+------+------+--------+--------+
    | id | name | age  | gender | mobile |
    +----+------+------+--------+--------+
    |  1 | zhao |   30 | m      | 10086  |
    |  2 | qian |   24 | m      | 10010  |
    +----+------+------+--------+--------+
    2 rows in set (0.00 sec)
  • 如果加入-U选项，则删除数据必须加where，否则报错

truncate 快速删表

truncate table teacher;

查询数据 select

  • 特殊字符

    as 对显示的字段起别名
    * 代表所有字段

单表查询

  • 查询学生表中的学生姓名，年龄，性别，班级id等数据，并且学生姓名显示为stu_name
    MariaDB [hellodb]> select Name as stu_name,age,Gender,ClassID from students;
    +---------------+-----+--------+---------+
    | stu_name      | age | Gender | ClassID |
    +---------------+-----+--------+---------+
    | Shi Zhongyu   |  22 | M      |       2 |
    | Shi Potian    |  22 | M      |       1 |
    | Xie Yanke     |  53 | M      |       2 |
    | Ding Dian     |  32 | M      |       4 |
    | Yu Yutong     |  26 | M      |       3 |
    | Shi Qing      |  46 | M      |       5 |
    | Xi Ren        |  19 | F      |       3 |
    | Lin Daiyu     |  17 | F      |       7 |
    | Ren Yingying  |  20 | F      |       6 |
    | Yue Lingshan  |  19 | F      |       3 |
    | Yuan Chengzhi |  23 | M      |       6 |
    | Wen Qingqing  |  19 | F      |       1 |
    | Tian Boguang  |  33 | M      |       2 |
    | Lu Wushuang   |  17 | F      |       3 |
    | Duan Yu       |  19 | M      |       4 |
    | Xu Zhu        |  21 | M      |       1 |
    | Lin Chong     |  25 | M      |       4 |
    | Hua Rong      |  23 | M      |       7 |
    | Xue Baochai   |  18 | F      |       6 |
    | Diao Chan     |  19 | F      |       7 |
    | Huang Yueying |  22 | F      |       6 |
    | Xiao Qiao     |  20 | F      |       1 |
    | Ma Chao       |  23 | M      |       4 |
    | Xu Xian       |  27 | M      |    NULL |
    | Sun Dasheng   | 100 | M      |    NULL |
    +---------------+-----+--------+---------+
    25 rows in set (0.00 sec)
  • 查询学生年龄小于等于20岁，或者年龄是27岁的所有记录
    MariaDB [hellodb]> select * from students where age <= 20 or age = 27 ;
    +-------+--------------+-----+--------+---------+-----------+
    | StuID | Name         | Age | Gender | ClassID | TeacherID |
    +-------+--------------+-----+--------+---------+-----------+
    |     7 | Xi Ren       |  19 | F      |       3 |      NULL |
    |     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
    |     9 | Ren Yingying |  20 | F      |       6 |      NULL |
    |    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
    |    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
    |    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
    |    15 | Duan Yu      |  19 | M      |       4 |      NULL |
    |    19 | Xue Baochai  |  18 | F      |       6 |      NULL |
    |    20 | Diao Chan    |  19 | F      |       7 |      NULL |
    |    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
    |    24 | Xu Xian      |  27 | M      |    NULL |      NULL |
    +-------+--------------+-----+--------+---------+-----------+
    11 rows in set (0.00 sec)
  • 查询学生年龄小于等于20岁，并且年龄不是19岁的所有记录
    MariaDB [hellodb]> select * from students where age <= 20 and age <> 19 ;
    +-------+--------------+-----+--------+---------+-----------+
    | StuID | Name         | Age | Gender | ClassID | TeacherID |
    +-------+--------------+-----+--------+---------+-----------+
    |     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
    |     9 | Ren Yingying |  20 | F      |       6 |      NULL |
    |    14 | Lu Wushuang  |  17 | F      |       3 |      NULL |
    |    19 | Xue Baochai  |  18 | F      |       6 |      NULL |
    |    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
    +-------+--------------+-----+--------+---------+-----------+
    5 rows in set (0.00 sec)
  • 查询没有教师的学生的所有数据
    MariaDB [hellodb]> select * from students where teacherid is null;
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
    |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
    |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
    |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
    |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
    |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
    |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
    |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    20 rows in set (0.00 sec)

    * 为空：is null  
    * 非空：is not null
  • 查询年龄在20到30之间(包含20 30)的学生的所有信息
    MariaDB [hellodb]> select * from students where age between 20 and 27;
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    12 rows in set (0.00 sec)
  • 查询年龄是18 20 22岁的学生的所有信息
    MariaDB [hellodb]> select * from students where age in (18,20,22);
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    6 rows in set (0.00 sec)
  • 查询学生的年龄，去除重复(distinct)
    MariaDB [hellodb]> select distinct age from students;
    +-----+
    | age |
    +-----+
    |  22 |
    |  53 |
    |  32 |
    |  26 |
    |  46 |
    |  19 |
    |  17 |
    |  20 |
    |  23 |
    |  33 |
    |  21 |
    |  25 |
    |  18 |
    |  27 |
    | 100 |
    +-----+
    15 rows in set (0.00 sec)
  • 查询所有姓shi的学生信息
    MariaDB [hellodb]> select * from students where name like 'shi%';
    +-------+-------------+-----+--------+---------+-----------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID |
    +-------+-------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
    |     2 | Shi Potian  |  22 | M      |       1 |         7 |
    |     6 | Shi Qing    |  46 | M      |       5 |      NULL |
    +-------+-------------+-----+--------+---------+-----------+
    3 rows in set (0.01 sec)
  • 聚合函数

avg(), max(), min(), count(), sum()…

  • group by 与 条件语句的位置关系

    where在前，group by 在后
    或者 group by 在前 having 在后

  • 统计学生表中男女学生分别的平均年龄

    MariaDB [hellodb]> select gender,avg(age) from students group by gender;
    +--------+----------+
    | gender | avg(age) |
    +--------+----------+
    | F      |  19.0000 |
    | M      |  33.0000 |
    +--------+----------+
    2 rows in set (0.01 sec)
  • 统计每个班的男女生数
    MariaDB [hellodb]> select classid,gender,count(*) from students group by classid,gender;
    +---------+--------+----------+
    | classid | gender | count(*) |
    +---------+--------+----------+
    |    NULL | M      |        2 |
    |       1 | F      |        2 |
    |       1 | M      |        2 |
    |       2 | M      |        3 |
    |       3 | F      |        3 |
    |       3 | M      |        1 |
    |       4 | M      |        4 |
    |       5 | M      |        1 |
    |       6 | F      |        3 |
    |       6 | M      |        1 |
    |       7 | F      |        2 |
    |       7 | M      |        1 |
    +---------+--------+----------+
    12 rows in set (0.01 sec)
  • 统计每个班的男女生数,以班级id排序
    MariaDB [hellodb]> select classid,gender,count(*) from students group by classid,gender order by classid; 
    +---------+--------+----------+
    | classid | gender | count(*) |
    +---------+--------+----------+
    |    NULL | M      |        2 |
    |       1 | F      |        2 |
    |       1 | M      |        2 |
    |       2 | M      |        3 |
    |       3 | F      |        3 |
    |       3 | M      |        1 |
    |       4 | M      |        4 |
    |       5 | M      |        1 |
    |       6 | F      |        3 |
    |       6 | M      |        1 |
    |       7 | F      |        2 |
    |       7 | M      |        1 |
    +---------+--------+----------+
    12 rows in set (0.00 sec)
  • 统计每个班的男女生数,以班级id排序,并将NULL值放到最后
    MariaDB [hellodb]> select classid,gender,count(*) from students group by classid,gender order by -classid desc;
    +---------+--------+----------+
    | classid | gender | count(*) |
    +---------+--------+----------+
    |       1 | M      |        2 |
    |       1 | F      |        2 |
    |       2 | M      |        3 |
    |       3 | M      |        1 |
    |       3 | F      |        3 |
    |       4 | M      |        4 |
    |       5 | M      |        1 |
    |       6 | F      |        3 |
    |       6 | M      |        1 |
    |       7 | F      |        2 |
    |       7 | M      |        1 |
    |    NULL | M      |        2 |
    +---------+--------+----------+
    12 rows in set (0.00 sec)
  • 对学生年龄进行正序排序
    MariaDB [hellodb]> select * from students order by age;
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
    |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
    |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
    |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
    |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
    |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
    |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
    |     4 | Ding Dian     |  32 | M      |       4 |         4 |
    |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
    |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
    |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
    |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    25 rows in set (0.00 sec)
  • 对学生年龄进行倒序排序
    MariaDB [hellodb]> select * from students order by age desc;
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
    |     3 | Xie Yanke     |  53 | M      |       2 |        16 |
    |     6 | Shi Qing      |  46 | M      |       5 |      NULL |
    |    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
    |     4 | Ding Dian     |  32 | M      |       4 |         4 |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
    |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
    |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
    |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
    |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
    |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
    |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
    |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    25 rows in set (0.00 sec)
  • 挑出年龄最小的3个学生的信息
    MariaDB [hellodb]> select * from students order by age limit 3;
    +-------+-------------+-----+--------+---------+-----------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID |
    +-------+-------------+-----+--------+---------+-----------+
    |    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
    |     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
    |    19 | Xue Baochai |  18 | F      |       6 |      NULL |
    +-------+-------------+-----+--------+---------+-----------+
    3 rows in set (0.00 sec)
  • 跳过年龄最小的2个学生，往后取出3个学生的信息
    MariaDB [hellodb]> select * from students order by age limit 2,3;
    +-------+--------------+-----+--------+---------+-----------+
    | StuID | Name         | Age | Gender | ClassID | TeacherID |
    +-------+--------------+-----+--------+---------+-----------+
    |    19 | Xue Baochai  |  18 | F      |       6 |      NULL |
    |    12 | Wen Qingqing |  19 | F      |       1 |      NULL |
    |     7 | Xi Ren       |  19 | F      |       3 |      NULL |
    +-------+--------------+-----+--------+---------+-----------+
    3 rows in set (0.00 sec)

多表查询

纵向合并 union

  • union 如果查询中有重复记录则会去重显示

  • union all 不论是否有重复记录都显示，不去重

  • 合并时两个表中要合并显示的字段数必须一致

    MariaDB [hellodb]> select stuid as id,name,age,gender from students union select tid as id, name,age,gender from teachers;
    +----+---------------+-----+--------+
    | id | name          | age | gender |
    +----+---------------+-----+--------+
    |  1 | Shi Zhongyu   |  22 | M      |
    |  2 | Shi Potian    |  22 | M      |
    |  3 | Xie Yanke     |  53 | M      |
    |  4 | Ding Dian     |  32 | M      |
    |  5 | Yu Yutong     |  26 | M      |
    |  6 | Shi Qing      |  46 | M      |
    |  7 | Xi Ren        |  19 | F      |
    |  8 | Lin Daiyu     |  17 | F      |
    |  9 | Ren Yingying  |  20 | F      |
    | 10 | Yue Lingshan  |  19 | F      |
    | 11 | Yuan Chengzhi |  23 | M      |
    | 12 | Wen Qingqing  |  19 | F      |
    | 13 | Tian Boguang  |  33 | M      |
    | 14 | Lu Wushuang   |  17 | F      |
    | 15 | Duan Yu       |  19 | M      |
    | 16 | Xu Zhu        |  21 | M      |
    | 17 | Lin Chong     |  25 | M      |
    | 18 | Hua Rong      |  23 | M      |
    | 19 | Xue Baochai   |  18 | F      |
    | 20 | Diao Chan     |  19 | F      |
    | 21 | Huang Yueying |  22 | F      |
    | 22 | Xiao Qiao     |  20 | F      |
    | 23 | Ma Chao       |  23 | M      |
    | 24 | Xu Xian       |  27 | M      |
    | 25 | Sun Dasheng   | 100 | M      |
    |  1 | Song Jiang    |  45 | M      |
    |  2 | Zhang Sanfeng |  94 | M      |
    |  3 | Miejue Shitai |  77 | F      |
    |  4 | Lin Chaoying  |  93 | F      |
    +----+---------------+-----+--------+
    29 rows in set (0.00 sec)
横向合并 cross join
    MariaDB [hellodb]> select * from students cross join teachers limit 10;
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    | StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
    |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  93 | F      |
    |     2 | Shi Potian  |  22 | M      |       1 |         7 |   1 | Song Jiang    |  45 | M      |
    |     2 | Shi Potian  |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  94 | M      |
    |     2 | Shi Potian  |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  77 | F      |
    |     2 | Shi Potian  |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  93 | F      |
    |     3 | Xie Yanke   |  53 | M      |       2 |        16 |   1 | Song Jiang    |  45 | M      |
    |     3 | Xie Yanke   |  53 | M      |       2 |        16 |   2 | Zhang Sanfeng |  94 | M      |
    +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
    10 rows in set (0.00 sec)    

    MariaDB [hellodb]> select * from teachers cross join students limit 10;
    +-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
    | TID | Name          | Age | Gender | StuID | Name        | Age | Gender | ClassID | TeacherID |
    +-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
    |   1 | Song Jiang    |  45 | M      |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
    |   2 | Zhang Sanfeng |  94 | M      |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
    |   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
    |   4 | Lin Chaoying  |  93 | F      |     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
    |   1 | Song Jiang    |  45 | M      |     2 | Shi Potian  |  22 | M      |       1 |         7 |
    |   2 | Zhang Sanfeng |  94 | M      |     2 | Shi Potian  |  22 | M      |       1 |         7 |
    |   3 | Miejue Shitai |  77 | F      |     2 | Shi Potian  |  22 | M      |       1 |         7 |
    |   4 | Lin Chaoying  |  93 | F      |     2 | Shi Potian  |  22 | M      |       1 |         7 |
    |   1 | Song Jiang    |  45 | M      |     3 | Xie Yanke   |  53 | M      |       2 |        16 |
    |   2 | Zhang Sanfeng |  94 | M      |     3 | Xie Yanke   |  53 | M      |       2 |        16 |
    +-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+
    10 rows in set (0.00 sec)

SQL_JOIN

内连接(表交集) inner join
图1实现
  • 方法一
    MariaDB [hellodb]> select s.stuid as sid,s.name as s_name, t.tid,t.name as t_name from students as s inner join teachers as t on s.teacherid=t.tid;
    +-----+-------------+-----+---------------+
    | sid | s_name      | tid | t_name        |
    +-----+-------------+-----+---------------+
    |   5 | Yu Yutong   |   1 | Song Jiang    |
    |   1 | Shi Zhongyu |   3 | Miejue Shitai |
    |   4 | Ding Dian   |   4 | Lin Chaoying  |
    +-----+-------------+-----+---------------+
    3 rows in set (0.00 sec)
  • 方法二
    MariaDB [hellodb]> select s.stuid as sid,s.name as s_name, t.tid,t.name as t_name from students as s,teachers as t where s.teacherid=t.tid;
    +-----+-------------+-----+---------------+
    | sid | s_name      | tid | t_name        |
    +-----+-------------+-----+---------------+
    |   5 | Yu Yutong   |   1 | Song Jiang    |
    |   1 | Shi Zhongyu |   3 | Miejue Shitai |
    |   4 | Ding Dian   |   4 | Lin Chaoying  |
    +-----+-------------+-----+---------------+
    3 rows in set (0.00 sec)
外连接
左外连接 left outer join
  • 图2实现
    MariaDB [hellodb]> select tid,t.name as t_name, stuid, s.name as s_name  from teachers as t left outer join students as s on t.tid=s.teacherid;
    +-----+---------------+-------+-------------+
    | tid | t_name        | stuid | s_name      |
    +-----+---------------+-------+-------------+
    |   3 | Miejue Shitai |     1 | Shi Zhongyu |
    |   4 | Lin Chaoying  |     4 | Ding Dian   |
    |   1 | Song Jiang    |     5 | Yu Yutong   |
    |   2 | Zhang Sanfeng |  NULL | NULL        |
    +-----+---------------+-------+-------------+
    4 rows in set (0.00 sec)

    MariaDB [hellodb]> select s.stuid as sid,s.name as s_name, t.tid,t.name as t_name from students as s left outer join teachers as t on t.tid=s.teacherid;
    +-----+---------------+------+---------------+
    | sid | s_name        | tid  | t_name        |
    +-----+---------------+------+---------------+
    |   1 | Shi Zhongyu   |    3 | Miejue Shitai |
    |   2 | Shi Potian    | NULL | NULL          |
    |   3 | Xie Yanke     | NULL | NULL          |
    |   4 | Ding Dian     |    4 | Lin Chaoying  |
    |   5 | Yu Yutong     |    1 | Song Jiang    |
    |   6 | Shi Qing      | NULL | NULL          |
    |   7 | Xi Ren        | NULL | NULL          |
    |   8 | Lin Daiyu     | NULL | NULL          |
    |   9 | Ren Yingying  | NULL | NULL          |
    |  10 | Yue Lingshan  | NULL | NULL          |
    |  11 | Yuan Chengzhi | NULL | NULL          |
    |  12 | Wen Qingqing  | NULL | NULL          |
    |  13 | Tian Boguang  | NULL | NULL          |
    |  14 | Lu Wushuang   | NULL | NULL          |
    |  15 | Duan Yu       | NULL | NULL          |
    |  16 | Xu Zhu        | NULL | NULL          |
    |  17 | Lin Chong     | NULL | NULL          |
    |  18 | Hua Rong      | NULL | NULL          |
    |  19 | Xue Baochai   | NULL | NULL          |
    |  20 | Diao Chan     | NULL | NULL          |
    |  21 | Huang Yueying | NULL | NULL          |
    |  22 | Xiao Qiao     | NULL | NULL          |
    |  23 | Ma Chao       | NULL | NULL          |
    |  24 | Xu Xian       | NULL | NULL          |
    |  25 | Sun Dasheng   | NULL | NULL          |
    +-----+---------------+------+---------------+
    25 rows in set (0.00 sec)
  • 图4实现
    MariaDB [hellodb]> select tid,t.name as t_name, stuid, s.name as s_name  from teachers as t left outer join students as s on t.tid=s.teacherid where teacherid is null;
    +-----+---------------+-------+--------+
    | tid | t_name        | stuid | s_name |
    +-----+---------------+-------+--------+
    |   2 | Zhang Sanfeng |  NULL | NULL   |
    +-----+---------------+-------+--------+
    1 row in set (0.00 sec)
右外连接 right outer join
  • 图3实现
    MariaDB [hellodb]> select stuid,s.name as s_name, tid,t.name as t_name from teachers as t right outer join students as s on t.tid=s.teacherid;
    +-------+---------------+------+---------------+
    | stuid | s_name        | tid  | t_name        |
    +-------+---------------+------+---------------+
    |     1 | Shi Zhongyu   |    3 | Miejue Shitai |
    |     2 | Shi Potian    | NULL | NULL          |
    |     3 | Xie Yanke     | NULL | NULL          |
    |     4 | Ding Dian     |    4 | Lin Chaoying  |
    |     5 | Yu Yutong     |    1 | Song Jiang    |
    |     6 | Shi Qing      | NULL | NULL          |
    |     7 | Xi Ren        | NULL | NULL          |
    |     8 | Lin Daiyu     | NULL | NULL          |
    |     9 | Ren Yingying  | NULL | NULL          |
    |    10 | Yue Lingshan  | NULL | NULL          |
    |    11 | Yuan Chengzhi | NULL | NULL          |
    |    12 | Wen Qingqing  | NULL | NULL          |
    |    13 | Tian Boguang  | NULL | NULL          |
    |    14 | Lu Wushuang   | NULL | NULL          |
    |    15 | Duan Yu       | NULL | NULL          |
    |    16 | Xu Zhu        | NULL | NULL          |
    |    17 | Lin Chong     | NULL | NULL          |
    |    18 | Hua Rong      | NULL | NULL          |
    |    19 | Xue Baochai   | NULL | NULL          |
    |    20 | Diao Chan     | NULL | NULL          |
    |    21 | Huang Yueying | NULL | NULL          |
    |    22 | Xiao Qiao     | NULL | NULL          |
    |    23 | Ma Chao       | NULL | NULL          |
    |    24 | Xu Xian       | NULL | NULL          |
    |    25 | Sun Dasheng   | NULL | NULL          |
    +-------+---------------+------+---------------+
    25 rows in set (0.00 sec)

    MariaDB [hellodb]> select t.tid,t.name as t_name,s.stuid as sid,s.name as s_name from students as s right outer join teachers as t on t.tid=s.teacherid;
    +-----+---------------+------+-------------+
    | tid | t_name        | sid  | s_name      |
    +-----+---------------+------+-------------+
    |   3 | Miejue Shitai |    1 | Shi Zhongyu |
    |   4 | Lin Chaoying  |    4 | Ding Dian   |
    |   1 | Song Jiang    |    5 | Yu Yutong   |
    |   2 | Zhang Sanfeng | NULL | NULL        |
    +-----+---------------+------+-------------+
    4 rows in set (0.00 sec)
  • 图5 实现
    MariaDB [hellodb]> select t.tid,t.name as t_name,s.stuid as sid,s.name as s_name from students as s right outer join teachers as t on t.tid=s.teacherid where s.stuid is null;
    +-----+---------------+------+--------+
    | tid | t_name        | sid  | s_name |
    +-----+---------------+------+--------+
    |   2 | Zhang Sanfeng | NULL | NULL   |
    +-----+---------------+------+--------+
    1 row in set (0.00 sec)
完全外连接 full outer join

  • mysql 不支持完全外连接语法

  • 可以用左外连接 union 右外连接 实现相同用法

  • MariaDB中实现图6

MariaDB [hellodb]> select stuid as sid,s.name as s_name,tid,t.name as t_name from students as s left outer join teachers as t on s.teacherid=t.tid union select stuid as sid,s.name as s_name,tid,t.name as t_name from students as s right outer join teachers as t on s.teacherid=t.tid;
+------+---------------+------+---------------+
| sid  | s_name        | tid  | t_name        |
+------+---------------+------+---------------+
|    1 | Shi Zhongyu   |    3 | Miejue Shitai |
|    2 | Shi Potian    | NULL | NULL          |
|    3 | Xie Yanke     | NULL | NULL          |
|    4 | Ding Dian     |    4 | Lin Chaoying  |
|    5 | Yu Yutong     |    1 | Song Jiang    |
|    6 | Shi Qing      | NULL | NULL          |
|    7 | Xi Ren        | NULL | NULL          |
|    8 | Lin Daiyu     | NULL | NULL          |
|    9 | Ren Yingying  | NULL | NULL          |
|   10 | Yue Lingshan  | NULL | NULL          |
|   11 | Yuan Chengzhi | NULL | NULL          |
|   12 | Wen Qingqing  | NULL | NULL          |
|   13 | Tian Boguang  | NULL | NULL          |
|   14 | Lu Wushuang   | NULL | NULL          |
|   15 | Duan Yu       | NULL | NULL          |
|   16 | Xu Zhu        | NULL | NULL          |
|   17 | Lin Chong     | NULL | NULL          |
|   18 | Hua Rong      | NULL | NULL          |
|   19 | Xue Baochai   | NULL | NULL          |
|   20 | Diao Chan     | NULL | NULL          |
|   21 | Huang Yueying | NULL | NULL          |
|   22 | Xiao Qiao     | NULL | NULL          |
|   23 | Ma Chao       | NULL | NULL          |
|   24 | Xu Xian       | NULL | NULL          |
|   25 | Sun Dasheng   | NULL | NULL          |
| NULL | NULL          |    2 | Zhang Sanfeng |
+------+---------------+------+---------------+
26 rows in set (0.00 sec)
  • MariaDB中实现图7
    MariaDB [hellodb]> select * from (select stuid as sid,s.name as s_name,tid,t.name as t_name from students as s left outer join teachers as t on s.teacherid=t.tid union select stuid as sid,s.name as s_name,tid,t.name as t_name from students as s right outer join teachers as t on s.teacherid=t.tid) as s_t where s_t.sid is null or s_t.tid is null;
    +------+---------------+------+---------------+
    | sid  | s_name        | tid  | t_name        |
    +------+---------------+------+---------------+
    |    2 | Shi Potian    | NULL | NULL          |
    |    3 | Xie Yanke     | NULL | NULL          |
    |    6 | Shi Qing      | NULL | NULL          |
    |    7 | Xi Ren        | NULL | NULL          |
    |    8 | Lin Daiyu     | NULL | NULL          |
    |    9 | Ren Yingying  | NULL | NULL          |
    |   10 | Yue Lingshan  | NULL | NULL          |
    |   11 | Yuan Chengzhi | NULL | NULL          |
    |   12 | Wen Qingqing  | NULL | NULL          |
    |   13 | Tian Boguang  | NULL | NULL          |
    |   14 | Lu Wushuang   | NULL | NULL          |
    |   15 | Duan Yu       | NULL | NULL          |
    |   16 | Xu Zhu        | NULL | NULL          |
    |   17 | Lin Chong     | NULL | NULL          |
    |   18 | Hua Rong      | NULL | NULL          |
    |   19 | Xue Baochai   | NULL | NULL          |
    |   20 | Diao Chan     | NULL | NULL          |
    |   21 | Huang Yueying | NULL | NULL          |
    |   22 | Xiao Qiao     | NULL | NULL          |
    |   23 | Ma Chao       | NULL | NULL          |
    |   24 | Xu Xian       | NULL | NULL          |
    |   25 | Sun Dasheng   | NULL | NULL          |
    | NULL | NULL          |    2 | Zhang Sanfeng |
    +------+---------------+------+---------------+
    23 rows in set (0.00 sec)
自连接
    MariaDB [hellodb]> create table emp(id int, name char(20), leader int);
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [hellodb]> insert emp values(1,'张三',null),(2,'李四',1),(3,'王二',2),(4,'麻子',3); 
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0

    MariaDB [hellodb]> select * from emp;
    +------+--------+--------+
    | id   | name   | leader |
    +------+--------+--------+
    |    1 | 张三   |   NULL |
    |    2 | 李四   |      1 |
    |    3 | 王二   |      2 |
    |    4 | 麻子   |      3 |
    +------+--------+--------+
    4 rows in set (0.00 sec)

    MariaDB [hellodb]> select e.name as 员工姓名,l.name as 领导姓名 from emp as e left join emp as l on e.leader=l.id;
    +--------------+--------------+
    | 员工姓名     | 领导姓名      |
    +--------------+--------------+
    | 李四         | 张三         |
    | 王二         | 李四         |
    | 麻子         | 王二         |
    | 张三         | NULL         |
    +--------------+--------------+

    4 rows in set (0.00 sec)

子查询

  • 查找学生年龄小于平均年龄的学生信息
    MariaDB [hellodb]> select * from students where age < (select avg(age) from students);
    +-------+---------------+-----+--------+---------+-----------+
    | StuID | Name          | Age | Gender | ClassID | TeacherID |
    +-------+---------------+-----+--------+---------+-----------+
    |     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
    |     2 | Shi Potian    |  22 | M      |       1 |         7 |
    |     5 | Yu Yutong     |  26 | M      |       3 |         1 |
    |     7 | Xi Ren        |  19 | F      |       3 |      NULL |
    |     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
    |     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
    |    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
    |    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
    |    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
    |    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
    |    15 | Duan Yu       |  19 | M      |       4 |      NULL |
    |    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
    |    17 | Lin Chong     |  25 | M      |       4 |      NULL |
    |    18 | Hua Rong      |  23 | M      |       7 |      NULL |
    |    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
    |    20 | Diao Chan     |  19 | F      |       7 |      NULL |
    |    21 | Huang Yueying |  22 | F      |       6 |      NULL |
    |    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
    |    23 | Ma Chao       |  23 | M      |       4 |      NULL |
    |    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
    +-------+---------------+-----+--------+---------+-----------+
    20 rows in set (0.00 sec)

数据库其他操作

  • show character set; 查看所有字符集

  • show variables like ‘%character%’; 查看当前数据库使用的字符集

  • show collation; 查看所有排序规则

  • create database [if not exists] ‘db_name’; 创建数据库

  • show create database db_name; 查看数据库创建过程

  • show table status like ‘student’\G; 查看表的状态，\G 表示结果竖着显示

  • show engines; 查看数据库支持的引擎

  • 数据库中表名大小写敏感，表中数据大小写不敏感

  • 导入sql文件

    $mysql -u root -p  < hellodb_innodb.sql 
    Enter password:
东烨
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mariadb学习笔记
05-12
Mariadb学习笔记，MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB...
MariaDB基本命令
qq_40343772的博客
06-11 1132
数据库表单数据类型及修饰符 整型： INT：基本整型数据，4字节长度（包含正负数，以下均如此） TINYINT：范围更小的整形数据，1字节 BIGINT：8字节长度整形数据 浮点型： FLOAT：单精度浮点型，4字节 DOUBLE：双精度浮点型，8字节 字符型： CHAR(#)：长度为#的字符串，不区分大小写 BINARY(#)：长度为#的字符产。区分大小写 VARCHAR(#)：最大长度为#的变...
MariaDB基础知识和基础命令的使用
潇洒哥的运维之道
12-17 2914
一、目标 MariaDB最基本的一些知识学习和基础命令的使用 二、平台 centos7.6, 5.5.60-MariaDB MariaDB Server 三、解析 1.*** 四、MariaDB基本用法 1.登录数据库 ：[root@xserver ~]# mysql -u root -p 2.查看所有数据库：MariaDB [(none)]> show databases; ...
MariaDB命令详解
weixin_34189116的博客
01-18 758
MariaDB命令详解mysql客户端程序：命令行交互式客户端程序：mysqlmysqlmysql[OPTIONS][database]#mysql--help配置文件的读取次序：/etc/mysql/my.cnf/etc/my.cnf~/.my.cnfrpm安装的读取方法如果有相同的内容最后读取的会覆盖前面读取的，不同版本的顺序可能不同，用mysql...
MariaDB(MySQL)创建、删除、选择及数据类型使用详解
国海的博客
12-10 1万+
一、MariaDB简介（MySQL简介略过）         MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Wi
linux -- 学习笔记MariaDB
lljxk2008的博客
10-24 276
MariaDB是Mysql的分支产品，是一款全新的数据库管理系统 但是几乎完全兼容Mysql, 可以认为MariaDB与Mysql， 在性能是基本保持相同 安装MariaDB： # yum -y install mariadb mariadb-server # systemctl enable mariadb -- 开机启动 初始化MariaDB： 在使用Maria...
【Todd学习笔记】Mysql/MariaDB基本语法学习笔记（3分钟快速入门sql基本语句）
04-29
Todd总结的Mysql/MariaDB 基本语句，语法，让你三分钟快速入门sql语法，学习mysql的可以在安装完按照文档中的命令练习，很快就能掌握基本的mysql语句。
TiDB工具——学习笔记
02-26
【TiDB工具学习笔记】 TiDB 是一个分布式数据库系统，为了解决大数据处理和高并发场景下的挑战，它提供了一系列的工具来辅助管理和维护数据。这些工具包括 Dumpling、Lightning、Data Migration (DM)、Backup & ...
cent7yum安装mariadb(有外网)-详细笔记文档总结
07-07
CentOS 7 使用 Yum 安装 MariaDB 详细笔记文档总结 在本文中，我们将详细介绍如何使用 Yum 在 CentOS 7 中安装 MariaDB 数据库，并进行简单的安全配置。 首先，我们需要使用 Yum 安装 MariaDB 数据库。输入以下...
Mysql学习笔记.pdf
02-13
本学习笔记将围绕MySQL的基础知识、体系架构、性能优化等方面进行深入探讨。 首先，MySQL的学习需要掌握基本的SQL语法，如数据查询、插入、更新和删除操作。推荐的书籍包括《MySQL必知必会》和《MySQL深入浅出》，...
MariaDB [(none)]> drop user cr; ERROR 1396 (HY000): Operation DROP USER failed for 'cr'@'%'
weixin_30606669的博客
07-04 432
首先查看mysql.user表中是否存在该用户如果存在，更新权限MariaDB [(none)]> flush privileges;重新执行删除操作（注意删除的时候要写全称）MariaDB [(none)]> drop user 'cr'@'localhost';查看用户表MariaDB [(none)]> SELECT DISTINCT CO...
Mysql mariadb none_MariaDB/MySQL安全配置以及账户管理
weixin_39728213的博客
01-27 495
原标题：MariaDB/MySQL安全配置以及账户管理MySQL安装好后，我们要对MySQL做一些初步的安全设置，使得其适用于生产环境，此外对MySQL账户的管理设置也是有讲究的，本文从安全的角度给大家介绍MySQL/MariaDB的相关设置。我们知道，初次安装好MySQL后，MySQL会默认自带一些用户和数据库，主要用于测试。而实际生产环境中不需要这些，为了避免带来安全隐患，我们应当移出这些测试...
mysql 切换日志_转：MySQL之日志管理
weixin_42463997的博客
01-19 413
转自：http://www.178linux.com/100936本节索引一、事务日志二、错误日志三、通用日志四、慢查询日志五、二进制日志六、性能分析工具profileMySQL数据库中支持多种日志类型，通过分析日志，我们可以优化数据库性能，排除故障，甚至能够还原数据，本节内容将带你了解MySQL数据库中的日志管理一、事务日志作用：用来记录数据库更新情况的文件，它可以记录针对数据库的任何操作，并将...
MariaDB 表的基本操作(3)
weixin_30364147的博客
12-24 336
MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可MariaDB的目的是完全兼容MySQL,包括API和命令行,MySQL由于现在闭源了,而能轻松成为MySQL的代替品.在存储引擎方面,使用XtraDB来代替MySQL的InnoDB,MariaDB由MySQL的创始人Michael Widenius主导开发,他早前曾以10亿美元的价格,将自己创建的公司My...
Mysql mariadb none_【MySQL】Mariadb字符集
weixin_30708203的博客
01-27 280
Mariadb字符集如果不设置字符集，可以查看mariadb的字符集的默认设置是latin1。如下命令,查看Mariadb的默认配置:[root@oradb ~]# /usr/local/mysql/bin/mysqld --verbose --help |grep character-set-server2019-09-23 16:10:05 0 [Note] Plugin 'FEEDBACK'...
Mysql mariadb none_忘记mysql或mariadb数据库的密码之解决方案
weixin_36084130的博客
01-27 949
一、实验环境CentOS Linux release 7.5.1804 (Core)mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1二、实施方案第一步：添加--skip-grant-tables在/etc/my.cnf.d/server.cnf中[mysqld]下面添加选项[mysqld]sk...
MariaDB设置初始化密码及修改密码
热门推荐
zwj52pk的博客
11-28 2万+
一、初始化设置密码 /etc/init.d/mysql stop cd /usr/local/mysql mysqld_safe –user=mysql –skip-grant-tables –skip-networking & mysql -u root mysql mysql > UPPATE mysql.user SET password=PASSWORD(‘newpass
mariadb无密码登录解决方式
一叶孤舟YI的博客
04-18 1万+
正确的操作打开方式:1、进到数据库进行操作[root@localhost etc]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7Server version: 5.5.41-MariaDB MariaDB ServerCopyright (c) 2000...
启动 mariadb
最新发布
03-24
MariaDB是一个开源的关系型数据库管理系统，它是MySQL的一个分支。启动MariaDB可以按照以下步骤进行： 1. 安装MariaDB：首先需要在你的计算机上安装MariaDB。你可以从MariaDB官方网站下载适合你操作系统的安装包，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值