数据库查询知识

1、 导入hellodb.sql生成数据库

[[email protected] ~]# mysql -S /home/app/yanglie/3308/socket/mysql.sock < hellodb_innodb.sql

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| hellodb |

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

MariaDB [hellodb]> use hellodb;

MariaDB [hellodb]> select name,age,gender from students where age >25 and gender='M';

+--------------+-----+--------+

| name | age | gender |

+--------------+-----+--------+

| Xie Yanke | 53 | M |

| Ding Dian | 32 | M |

| Yu Yutong | 26 | M |

| Shi Qing | 46 | M |

| Tian Boguang | 33 | M |

| Xu Xian | 27 | M |

| Sun Dasheng | 100 | M |

+--------------+-----+--------+

7 rows in set (0.09 sec)

(2) 以ClassID为分组依据，显示每组的平均年龄

MariaDB [hellodb]> select ClassID,avg(age) from students group by ClassID;

+---------+----------+

| ClassID | avg(age) |

+---------+----------+

| NULL | 63.5000 |

| 1 | 20.5000 |

| 2 | 36.0000 |

| 3 | 20.2500 |

| 4 | 24.7500 |

| 5 | 46.0000 |

| 6 | 20.7500 |

| 7 | 19.6667 |

+---------+----------+

8 rows in set (0.00 sec)

(3) 显示第(2)题中平均年龄大于30的分组及平均年龄

MariaDB [hellodb]> select ClassID,avg(age) as pjnl from students group by ClassID having pjnl > '30';

+---------+---------+

| ClassID | pjnl |

+---------+---------+

| NULL | 63.5000 |

| 2 | 36.0000 |

| 5 | 46.0000 |

+---------+---------+

3 rows in set (0.09 sec)

(4) 显示以L开头的名字的同学的信息

MariaDB [hellodb]> select * from students where name like 'L%';

+-------+-------------+-----+--------+---------+-----------+

| StuID | Name | Age | Gender | ClassID | TeacherID |

+-------+-------------+-----+--------+---------+-----------+

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

+-------+-------------+-----+--------+---------+-----------+

3 rows in set (0.00 sec)

2、数据库授权tom用户，允许192.168.1.0/24网段可以连接mysql

MariaDB [mysql]> create user 'tom'@'192.168.1.0/24';

Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> select host,password,user from user;

+----------------+----------+-------------+

| Host | Password | User |

+----------------+----------+-------------+

| localhost | | mariadb.sys |

| localhost | invalid | root |

| localhost | invalid | mysql |

| localhost | | |

| mariadb-xin | | |

| 192.168.1.0/24 | | tom |

+----------------+----------+-------------+

6 rows in set (0.01 sec)

3、总结mysql常见的存储引擎以及特点。

#mysql中常见存储引擎有 MyISAM 和 Innodb 。

# MyISAM存储引擎适用场景

只读(或者写较少)、表较小(可以接受长时间进行修复操作)

MyISAM引擎特点

不支持事务

表级锁定

读写相互阻塞，写入不能读，读时不能写

只缓存索引

不支持外键约束

不支持聚簇索引

读取数据较快，占用资源较少

不支持MVCC(多版本并发控制机制)高并发

崩溃恢复性较差

MySQL5.5.5前默认的数据库引擎

#InnoDB弥补了myISAM的 缺点，但自身还有如下缺点

所有InnoDB表的数据和索引放置于同一个表空间中

表空间文件：datadir定义的目录下

数据文件：ibddata1, ibddata2, ...