MySQL习题

第三章习题

(1)查看当前系统中的数据库。

mysql> SHOW DATABASES;

(2)创建数据库Book，使用SHOW CREATE DATABASE 语句查看数据库定义信息。

mysql> CREATE DATABASE Book;

mysql> SHOW CREATE DATABASE Book;

(3)删除数据库Book.

DROP DATABASE Book;

 

第四章习题

1.创建数据库Market，在Market中创建表customers，customers表结构如表4.5所示，按要求进行操作。

表4.5 customers表结构字段名	数据类型	主键	外键	非空	唯一	自增
c_num	INT(11)	是	否	是	是	是
c_name	VARCHAR(50)	否	否	否	否	否
c_contact	VARCHAR(50)	否	否	否	否	否
c_city	VARCHAR(50)	否	否	否	否	否
c_birth	DATETIME	否	否	是	否	否

 

（1） 创建数据库Market。

mysql> CREATE DATABASE Market;

（2） 创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段添加非空约束。

mysql> USE Market;

mysql> CREATE TABLE customers(

    -> c_num INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,

    -> c_name VARCHAR(50),

    -> c_contact VARCHAR(50),

    -> c_city VARCHAR(50),

    -> c_birth DATETIME NOT NULL

    -> );

（3） 将c_cantact字段插入到c_birth字段后面。

mysql> ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;

（4） 将c_name字段数据类型改为VARCHAR(70)。

mysql> ALTER TABLE customers MODIFY c_name VARCHAR(70);

（5） 将c_cantact字段改名为c_phone.

mysql> ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(70)

（6） 增加c_gender字段，数据类型为CHAR(1)。

mysql> ALTER TABLE customers ADD c_gender CHAR(1)

（7） 将表名修改为customers_info。

mysql> ALTER TABLE customers RENAME customers_info;

（8） 删除字段c_city。

mysql> ALTER TABLE customers_info DROP c_city;

（9） 修改数据表的存储引擎为MyISAM。

mysql> ALTER TABLE customers_info ENGINE=MyISAM;

2.在Market表中创建数据表orders，orders表结构如表4.6所示，按要求进行操作。

表4.6 orders表结构字段名	数据类型	主键	外键	非空	唯一	自增
o_num	INT(11)	是	否	是	是	是
o_date	DATE	否	否	否	否	否
c_id	VARCHAR(50)	否	是	否	否	否

 

（1） 创建数据表orders，在o_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联customs表中的主键c_num。

mysql> ALTER TABLE customers_info ENGINE=InnoDB;

mysql> CREATE TABLE orders(

    -> o_num INT(11) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,

    -> o_date DATE,

    -> c_id INT(11) ,

    -> FOREIGN KEY(c_id) REFERENCES customers_info(c_num)

    -> );

（2） 删除orders表的外键约束，然后删除表customers。

mysql> ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

mysql> DROP TABLE customers_info;

 

 

 第五章习题

(1)MySQL中的小数如何表示，不同表示方法之间有什么区别？ 

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型； 

MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。 

FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。 

(2)BLOB和TEXT分别适合于存储什么类型的数据？ 

BLOB，二进制大对象（字节流）。可以用来存储图片，声音和视频等二进制文件。没有字符集的说法。 

TEXT，文本大对象（字符流）。可以用来存储大量的字符串，可以理解为超大的char或者varchar类型。由于是存储字符，所以有字符集的说法。 

(3)说明ENUM和SET类型的区别以及在什么情况下适用？

mysql中的enum和set其实都是string类型的而且只能在指定的集合里取值, 

不同的是set可以取多个值,enum只能取一个值。 

ENUM 是一个字符串对象，其值通常选自一个允许值列表中，该列表在表创建时的列规格说明中被明确地列举。 

在下列某些情况下，值也可以是空串("") 或NULL： 

如果将一个无效值插入一个 ENUM (即，一个不在允许值列表中的字符串)，空字符串将作为一个特殊的错误值被插入。事实上，这个字符串有别于一个"普通的"空字符串，因为这个字符串有个数字索引值为 0。稍后有更详细描述。 

如果一个 ENUM 被声明为NULL，NULL 也是该列的一个合法值，并且该列的缺省值也将为NULL 。如果一个ENUM 被声明为NOT NULL，该列的缺省值将是该列表所允许值的第一个成员。 

每个枚举值均有一个索引值： 

在列说明中列表值所允许的成员值被从 1 开始编号。 

空字符串错误值的索引值为 0。 

(4)在MySQL中执行去下算术运算:(9-7)*4，8+15/3，17 DIV 2，39%12。 

mysql> SELECT (9-7) * 4, 8 + 15 / 3, 17 DIV 2, 39 % 12;

(5)在MySQL中执行如下比较运算:36>27，15>=8，40<50，15<=15，NULL<=>NULL，NULL<=>1，5<=>5。 

mysql> SELECT 36 > 27, 15 >= 8, 40 < 50, 15<=15, NULL<=>NULL, NULL<=>1, 5<=>5;

(6)在MySQL中执行如下逻辑运算:4&&8，-2||NULL，NULL XOR 0，0 XOR 1，!2。 

mysql> SELECT 4 && 8, -2 || NULL, NULL XOR 0, 0 XOR 1, !2;

(7)在MySQL中执行如下位运算:13&17，20|8，14^20，~16。 

mysql> SELECT 13 & 7, 20 | 8, 14 ^ 20, ~16;

第六章习题

1.使用数学函数进行如下运算: 

(1)计算18除以5的商和余数。 

mysql> SELECT FLOOR(18 / 5), MOD(18, 5);

(2)将弧度值PI()/4转换为角度值。 

mysql> SELECT DEGREES(PI()/4);

(3)计算9的4次方值。 

mysql> SELECT POW(9, 4);

(4)保留浮点值3.14159小数点后面的两位 

mysql> SELECT FORMAT(3.14159, 2);

 

2.使用字符串函数进行如下运算: 

(1)分别计算字符串"Hello World!"和"University"的长度。 

mysql> SELECT CHAR_LENGTH('Hello World!'), CHAR_LENGTH('University');

(2)从字符串"Nice to meet you!"中获取子字符串"meet"。 

mysql> SELECT SUBSTRING(‘Nice to meet you!',9,4); 

(3)重复输出3次字符串"Cheer!"。 

mysql> SELECT REPEAT(‘Cheer!',3); 

(4)将字符串"viidoo"逆序输出。 

mysql> SELECT REVERSE(‘viidoo'); 

(5)4个字符串"MySQL"，"not"，"is"，"great"，按顺序排列，从1，3和4位置处的字符串组成新的字符串。 

mysql> SELECT MAKE_SET(1|3|4,'MySQL','not','is','great');

3.使用日期和时间函数进行如下运算: 

(1)计算当前日期是一年的第几周。 

mysql> SELECT WEEK(CURDATE()); 

(2)计算当前日期是一周中的第几个工作日。 

mysql> SELECT DAYOFWEEK(CURDATE()) - 1;

(3)计算"1992-02-14"与当前日期之间相差的年份。 

mysql> SELECT YEAR(CURDATE()) - YEAR('1992-02-14');

(4)按"97 Oct 4th Saturday"格式输出当前日期。 

mysql> SELECT DATE_FORMAT(now(), '%y %b %D %W ');

(5)从当前日期时间值中获取时间值，并将其转换为秒值。 

mysql> SELECT TIME_TO_SEC(NOW());

4.使用MySQL函数进行如下运算: 

(1)使用show processlist语句查看当前连接状态。 

(2)使用加密函数encode对字符串"mysql"加密，并使用decode函数解密。 

mysql> SELECT DECODE(ENCODE('mysql','test'),'test');

(3)将十进制的值100转换为十六进制值。 

mysql> SELECT CONV(100, 10, 16);

(4)格式化数值5.1584，四舍五入保留到小数点后面第3位。 

mysql> SELECT FORMAT(5.1584, 3);

(5)将字符串"new string"的字符集改为gb2312。 

mysql> SELECT CONVERT('new string' USING gb2312);

第七章习题

mysql> CREATE TABLE dept

    -> (

    -> d_no         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

    -> d_name       VARCHAR(50),

    -> d_location  VARCHAR(100)

    -> );

mysql> CREATE TABLE employee

    -> (

    -> e_no        INT NOT NULL PRIMARY KEY,

    -> e_name      VARCHAR(100) NOT NULL,

    -> e_gender    CHAR(2) NOT NULL,

    -> dept_no    INT NOT NULL,

    -> e_job       VARCHAR(100) NOT NULL,

    -> e_salary   SMALLINT NOT NULL,

    -> hireDate   DATE,

    -> CONSTRAINT dno_fk FOREIGN KEY(dept_no)

    -> REFERENCES dept(d_no)

    -> );

mysql> INSERT INTO dept 

    -> VALUES (10, 'ACCOUNTING', 'ShangHai'),

    -> (20, 'RESEARCH ', 'BeiJing '),

    -> (30, 'SALES ', 'ShenZhen '),

    -> (40, 'OPERATIONS ', 'FuJian ');

mysql> INSERT INTO employee 

    -> VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),

    -> (1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),

    -> (1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),

    -> (1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),

    -> (1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'), 

    -> (1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),

    -> (1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),

    -> (1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),

    -> (1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),

    -> (1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),

    -> (1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),

    -> (1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

(1) 计算所有女员工（“F”）的年限

mysql> SELECT YEAR(NOW()) - YEAR(hireDate) FROM employee WHERE e_gender='F';

(2) 使用LIMIT 查询从第3 条记录开始到第6 条记录

mysql> SELECT * FROM employee limit 2,4;

(3) 查询销售人员（SALESMAN）的最低工资

SELECT MIN(e_salary) FROM employee WHERE e_job='SALESMAN';

(4) 查询名字以字母N 或S 结尾的记录

mysql> SELECT * FROM employee WHERE e_name REGEXP '[NS]$';

(5) 查询在BeiJing 工作的员工的姓名和职务

法一：

mysql> SELECT e_name,e_job FROM employee WHERE dept_no=(SELECT d_no FROM dept WHERE d_location='BeiJing');

法二：

mysql> SELECT e_name,e_job FROM employee,dept WHERE d_location='BeiJing' AND employee.dept_no=dept.d_no; 

法三：

mysql> SELECT e_name,e_job FROM employee JOIN dept ON employee.dept_no=dept.d_no WHERE dept.d_location='BeiJing'; 

(6) 使用左链接方式查询employee 和dept 表

mysql> SELECT * FROM employee LEFT OUTER JOIN dept ON employee.dept_no=dept.d_no;

(7) 查询所有2001-2005 年入职的员工的信息，查询部门编号为20 和30 的员工信息并使用UNION 合并两个查询结果

mysql> SELECT * FROM employee WHERE YEAR(hireDate) BETWEEN 2001 AND 2015 UNION SELECT * FROM employee WHERE dept_no IN (20, 30);

(8) 使用LIKE 查询员工姓名中包含字母a 的记录

mysql> SELECT * FROM employee WHERE e_name LIKE '%a%';

(9) 使用REGEXP 查询员工姓名中包含T、C 或者M3 个字母中任意1 个的记录

mysql> SELECT * FROM employee WHERE e_name REGEXP '[TCM]'; 

第八章习题

（1）创建数据表pet，插入数据

mysql> CREATE TABLE pet(

    -> name VARCHAR(20) NOT NULL,

    -> owner VARCHAR(20),

    -> species VARCHAR(20) NOT NULL,

    -> sex CHAR(1) NOT NULL,

    -> birth YEAR NOT NULL,

    -> death YEAR

    -> );

mysql> INSERT INTO pet VALUES('Fluffy', 'Harold', 'cat', 'f', 2003, 2010), 

    -> ('Claws', 'Gwen', 'cat','m', 2004, NULL),

    -> ('Buffy', NULL, 'dog', 'f', 2009, NULL),

    -> ('Fang', 'Benny', 'dog', 'm', 2000, NULL),

    -> ('Bowser', 'Diane', 'dog', 'm', 2003, 2009),

    -> ('Chirpy', NULL, 'bird', 'f', 2008, NULL);

（2）使用UPDATE 将Fang 的主人改为Kevin

mysql> UPDATE pet SET owner='Kevin' WHERE name='Fang';

（3）将没有主人的宠物的owner 字段值改为Duck

mysql> UPDATE pet SET owner='Duck' WHERE owner IS NULL;

（4）删除已经死亡的宠物记录

mysql> DELETE FROM pet WHERE death is NOT NULL;

（5）删除所有表中的记录

mysql> DELETE FROM pet;

第九章习题

在index_test 数据库中创建数据表writers.

（1）存储引擎为MyISAM，创建表的同时在w_id 字段上添加唯一名称为UniqIdx 的唯一索引

mysql> CREATE DATABASE index_test;

mysql> USE index_test;

mysql> CREATE TABLE writers(

    -> w_id SMALLINT(11) PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,

    -> w_name VARCHAR(255) NOT NULL,

    -> w_age CHAR(2) NOT NULL,

    -> w_address VARCHAR(255),

    -> w_note VARCHAR(255),

    -> UNIQUE INDEX Uniqidx(w_id)

    -> );

mysql> ALTER TABLE writers ENGINE=MyISAM;

（2）使用ALTER TABLE 语句在w_name 字段上建立名称为nameIdx 的普通索引

mysql> ALTER TABLE writers ADD INDEX nameIdx(w_name(255));

（3）使用CREATE INDEX 语句在w_address 和w_age 字段上建立名称为MultiIdx 的组合索引

mysql> CREATE INDEX MutiIdx ON writers(w_address,w_age);

（4）使用CREATE INDEX 语句在w_note 字段上建立名称为FTIdx 的全文索引

mysql> CREATE FULLTEXT INDEX FTIdx ON writers(w_note);

（5）删除名称为FTIdx 的全文索引

mysql> DROP INDEX FTIdx ON writers;

第十章习题

CREATE TABLE sch(id INT, name VARCHAR(50),class VARCHAR(50));
INSERT INTO sch VALUE(1,’xiaoming’,’1班’), (1,’xiaojun’,’2班’);

（1）写一个Hello World 的存储过程和函数

mysql> DELIMITER //

mysql> CREATE PROCEDURE hello_world()

    -> BEGIN

    -> END

    -> //

mysql> CREATE FUNCTION hello_world() 

    -> RETURNS DATETIME

    -> RETURN (SELECT CURDATE())

    -> //

mysql> DELIMITER ;

（2）写一个完整的包括参数、变量、变量赋值、条件判断、UPDATE 语句、SELECT 返回语句结果集的存储过程

DELIMITER //
CREATE FUNCTION count_sch()
RETURNS INT
RETURN (SELECT COUNT(*) FROM sch);
//

DELIMITER //
CREATE PROCEDURE add_id(out count INT)
BEGIN
DECLARE itmp INT;
DECLARE cur_id CURSOR FOR SELECT id FROM sch;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
SELECT count_sch() INTO count;
SET @sum=0;
OPEN cur_id;
REPEAT
FETCH cur_id INTO itmp;
IF itmp<10
THEN SET  @sum=  @sum+itmp;
END IF;
UNTIL 0 END REPEAT;
CLOSE cur_id;
END //

（3）创建一个执行动态SQL 的存储过程

mysql> CREATE PROCEDURE add_stu(IN name VARCHAR(50), IN class VARCHAR(50), out count INT)

    -> BEGIN

    -> INSERT INTO sch VALUES(name, class);

    -> SELECT count_sch() INTO count;

    -> END

    -> //

（4）创建实现功能相同的存储函数，比较他们之间的不同点在什么地方？

 这题瞎写写

第十一章习题

（1）在一个表上创建视图

mysql> CREATE OR REPLACE VIEW employee_job(e_name,e_job) AS SELECT e_name,e_job FROM employee;

mysql> SELECT * FROM employee_job;

 

（2）在多个表上创建视图

mysql> CREATE VIEW employee_info AS SELECT * FROM employee LEFT OUTER JOIN dept ON employee.dept_no=dept.d_no;

（3）更改视图

mysql> ALTER VIEW employee_info AS SELECT * FROM employee LEFT OUTER JOIN dept ON employee.dept_no=dept.d_no ORDER BY employee.e_salary;

（4）查看视图详细信息

SHOW TABLE STATUS LIKE 'employee_info';

（5）更新视图内容

mysql> UPDATE employee_job SET e_job = 'SALESMAN' WHERE e_name='SMITH';

（6）理解视图和基本表之间的关系.

用户操作的权限视图是一个或多个基本表导出的表，与基本表相比，视图是一个虚表。数据库中只存放视图的定义，但并不存放其中的数据，这些数据仍在原来的基本表中。

第十二章习题

步骤1：创建persons表

CREATE TABLE persons (name VARCHAR(40), num int);

步骤2：创建一个销售额表sales

CREATE TABLE sales (name VARCHAR(40), sum int);

（1）创建INSERT 事件的触发器

mysql> DELIMITER //

mysql> CREATE TRIGGER num_sum AFTER INSERT ON persons

    -> FOR EACH ROW

    -> BEGIN

    -> IF (SELECT COUNT(*) FROM sales WHERE name=NEW.name) > 0 THEN

    -> UPDATE sales SET sum = sum + NEW.num WHERE sales.name=NEW.name; 

    -> ELSE

    -> INSERT INTO sales VALUES (NEW.name,NEW.num);

    -> END IF;

    -> END

    -> //

mysql> DELIMITER ;

（2）创建UPDATE 事件的触发器

mysql> DELIMITER //

mysql> CREATE TRIGGER update_sum AFTER UPDATE ON persons FOR EACH ROW

mysql> BEGIN

mysql> UPDATE sales SET sum = sum-OLD.num+NEW.num WHERE sales.name=NEW.name;

mysql> END//

mysql> DELIMITER ;

（3）创建DELECT 事件的触发器

mysql> DELIMITER //

mysql> CREATE TRIGGER update_sum_after_delete_person AFTER DELETE ON persons FOR EACH ROW

    -> BEGIN

    -> UPDATE sales SET sum = sum-OLD.num WHERE sales.name=OLD.name;

    -> END

    -> //

mysql> DELIMITER ;

 

（4）查看触发器

测试上述触发器

mysql> INSERT INTO persons VALUES('zhangSan', 100);

mysql> SELECT * FROM sales;

mysql> UPDATE persons SET num=50 WHERE name='zhangSan';

mysql> INSERT INTO persons VALUES('zhangSan', 125);

mysql> SELECT * FROM sales;

mysql> DELETE FROM persons WHERE num=50;

mysql> SHOW TRIGGERS; 

（5）删除触发器

mysql> DROP TRIGGER update_sum_after_delete_person;

mysql> DELETE FROM persons;

mysql> SELECT * FROM sales;

第十三章经典习题

1.创建数据库Team，定义数据表player 

（1）创建一个新账户account1，该用户通过本地主机连接数据库，密码为pldpwd1.授权该用户对Team 中player 表的SELECT 和INSERT 权限，并且授权该用户对player 表的info 字段的UPDATE 权限

mysql> GRANT SELECT,INSERT,UPDATE(info) 

    -> ON Team.player

    -> TO 'account1'@localhost IDENTIFIED BY 'pldpwd1';

（2）更改account1 的密码为newpsw2 

mysql> SET PASSWORD FOR 'account1'@localhost = PASSWORD('newpsw2');

（3）使用PLUSH PRIVILEGES 重新加载权限表

PLUSH PRIVILEGES

（4）查看授权给account1 用户的权限

mysql> SHOW GRANTS FOR 'account1'@localhost;

（5）收回account1 的权限

mysql> REVOKE ALL PRIVILEGES,GRANT OPTION  FROM 'account1'@localhost;

（6）删除account1 的用户信息

mysql> DROP USER 'account1'@localhost;

第十四章经典习题

创建测试数据：

mysql> CREATE DATABASE test;

mysql> use test;

mysql> CREATE TABLE suppliers(

    -> id INT(11) PRIMARY KEY UNIQUE AUTO_INCREMENT NOT NULL,

    -> name VARCHAR(50) NOT NULL

    -> );

mysql> CREATE TABLE fruits(

    -> id INT(11) PRIMARY KEY UNIQUE AUTO_INCREMENT NOT NULL,

    -> supplier_id INT(11) NOT NULL,

    -> name VARCHAR(50) NOT NULL,

    -> FOREIGN KEY(supplier_id) REFERENCES suppliers(id)

    -> );

mysql> INSERT INTO suppliers(name) VALUES('jingDong'), ('taoBao'),('amazon');

mysql> INSERT INTO fruits(name, supplier_id) VALUES ('apple', 1), ('apple', 2), ('orange', 2);

（1）同时备份test 数据库中的fruits 和suppliers 表，然后删除两个表中的内容并还原利用mysqldump 备份

localhost:~ xushaojun$ mysqldump -u root -p -h localhost  --databases test > test.sql;

mysql> USE test;

mysql> DROP TABLE fruits,suppliers;

localhost:~ xushaojun$ mysql -u root -p test < test.sql

（2）将test 数据库中不同的数据表导出到xml 文件或者html 文件，并查看文件内容

localhost:~ xushaojun$ mysql -h localhost -u root  -p --xml --execute="SELECT * FROM fruits;" test > fruits.xml

localhost:~ xushaojun$ mysql -h localhost -u root  -p --html --execute="SELECT * FROM suppliers;" test > suppliers.html

（3）使用mysql 命令导出fruits 表中记录，并将查询结果以垂直方式显示写入文件

localhost:~ xushaojun$ mysql -h localhost -u root -p --execute="SELECT * FROM fruits" test > fruits.txt