数据库查询知识
1、 导入hellodb.sql生成数据库
[root@mariadb-xin ~]# 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, ...