数据库练习（二）

1、单表查询

库表student.report,有3个字段， 姓名、 学科、 成绩， 记录如下， 根据要求完成SQL语句

Name	Subject	Result
李白	Math	95
杜甫	English	83
李商隐	Math	79
白居易	Math	98
李清照	English	85
王维	Math	74

 

1）查询姓李的同学的个数

方法1：使用like模糊查询

SELECT COUNT(1) FROM student.report WHERE Name LIKE '李%';

方法2：使用正则

SELECT COUNT(1) FROM student.report WHERE Name REGEXP '^李';

2）查询表中数学成绩大于80的前2名同学的名字， 并按分数从大到小的顺序排列

select Name,Result from student.report where Subject='math' and Result>'80' order by Result  desc limit 0,2;

2、多表查询

假设一个出版社的数据库中有Books和Authors两个表，分别存储书籍信息和作者信息。两个表的结构如下：

创建表的SQL：

CREATE TABLE `Books` (

`ID_` int(11) DEFAULT NULL,

`Author` int(11) DEFAULT NULL,

`Title` varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Authors` (

`ID_` int(11) DEFAULT NULL,

`Name_` varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

为表中插入数据

insert into Books values(1001,1,'鸟哥的私房菜基础篇'),(1002,1,'鸟哥的私房菜服务器篇'),(1003,2,'精通MySQL'),(1004,3,'循序渐进Linux'),(1005,3,'高性能Linux服务器构建实战');

insert into Authors values(1,'鸟哥'),(2,'小辉'),(3,'高俊峰');

1) 请写一句SQL，列出所有作者姓名和其所写书籍名称。

select a.Name_,b.Title from Authors a, Books b where a.ID_ = b.Author;  //这里给两张表写了一个别名代替

2) 请写一句SQL，列出写过两本以上（包括两本）书籍的作者姓名。

select a.Name_ from ( select Author from Books group by Author having count(*) >=2) b,Authors a where a.ID_ = b.Author;

或者

select Name_ from Authors where ID_ in (select Author from Books group by Author having count(1)>=2);

3、用户授权

1.MySQL如何对用户smart授权访问，密码为123456。

GRANT ALL ON *.* TO smart@'%' IDENTIFIED BY '123456';   //授权所有的数据库，所有表的权限给smart用户，允许以任何方式登录，密码为123456.

2.授权用户tom可以在网络中的192.168.4.254主机登录，仅对对userdb库下的user表有查看记录、更新name字段的权限 ， 登录密码userweb888。

GRANT SELECT,UPDATE(name) ON userdb.user TO tom@'192.168.4.254' IDENTIFIED BY 'userweb888';

4、备份恢复

1.现在有一个MySQL数据库，库名test，要求使用mysqldump对数据库进行备份。

mysqldump -uroot -p -B test > /test.sql        //这样指定的路径就会多一个备份文件

mysql -uroot -p test < /test.sql               //对test数据库进行还原
或者进入数据库中
MariaDB [(none)]> source /test.sql;            //使用source命令进行还原