第一题
1.
CREATE DATABASE Market DEFAULT CHARSET=utf8;
2.
CREATE TABLE customers
(
c_num INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50) NOT NULL,
c_contact VARCHAR(50) NOT NULL,
c_city VARCHAR(50) NOT NULL,
c_birth DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
3.
ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;
4.
ALTER TABLE customers CHANGE c_name c_name VARCHAR(70);
5.
ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);
6.
ALTER TABLE customers ADD c_gender CHAR(1);
7.
ALTER TABLE customers RENAME customers_info;
8.
ALTER TABLE customers_info DROP c_city;
9.
ALTER TABLE customers_info ENGINE=MyISAM;
第二题
1.
CREATE TABLE orders
(
o_num INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
o_date DATE NULL,
c_id VARCHAR(50),
constraint c_id_customers FOREIGN KEY(c_id) REFERENCES customers_info(c_num)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
2.
ALTER TABLE orders DROP FOREIGN KEY c_id_customers;
DROP TABLE customers_info;
运算符练习
- 创建数据表tmpl5,其中包含 VARCHAR类型字段note和INT类型的字段price,使用运算符对表tmpl5中不同的字段进行运算。
mysql> create table tmpl5(note VARCHAR(100),price INT);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into tmpl5 values('thisgood',50);
Query OK, 1 row affected (0.01 sec)
- 判断price值是否落在30-80区间,返回70、30相比最大的值,判断price是否为In列表(10,20,50,35)中的某个值
mysql> select price between 30 and 80, greatest(price,70,30),price in (10,20,50,35) from tmpl5;
+-------------------------+-----------------------+------------------------+
| price between 30 and 80 | greatest(price,70,30) | price in (10,20,50,35) |
+-------------------------+-----------------------+------------------------+
| 1 | 70 | 1 |
+-------------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
- 对tmpl5中的字符串数值字段note进行比较运算、判断表tmpl5中note字段是否为空,使用like判断是否以字母't'开头;使用REGEXP判断是否以字母'y'结尾;判断是否包含字母'g'或'm'。
mysql> select note isnull,note like 't%',note regexp 'y$',note regexp '[gm]' from tmpl5;
+----------+----------------+------------------+--------------------+
| isnull | note like 't%' | note regexp 'y$' | note regexp '[gm]' |
+----------+----------------+------------------+--------------------+
| thisgood | 1 | 0 | 1 |
+----------+----------------+------------------+--------------------+
1 row in set (0.00 sec)
- 将price字段值与NULL、0进行逻辑运算
mysql> select price and null,price or null,price and 0,price or 0 from tmpl5;
+----------------+---------------+-------------+------------+
| price and null | price or null | price and 0 | price or 0 |
+----------------+---------------+-------------+------------+
| NULL | 1 | 0 | 1 |
+----------------+---------------+-------------+------------+
1 row in set (0.00 sec)
- 将price字段与2,4进行按位与、按位或操作、并对price进行按位操作
mysql> select price & 2,price | 4, ~price from tmpl5;
+-----------+-----------+----------------------+
| price & 2 | price | 4 | ~price |
+-----------+-----------+----------------------+
| 2 | 54 | 18446744073709551565 |
+-----------+-----------+----------------------+
1 row in set (0.00 sec)
-
mysql中的小数如何表示,不同表示方法之间有什么区别
float浮点型,含字节数为4,32位,只显示7个有效位,对最后一个数四舍五入。
double双精度实型,含字节数为8,64位,只显示15个有效位,对最后一位四舍五入。
decimal数字型,128位,不存在精度损失,常用语银行账目计算,可以支持28位float和double的操作,数字溢出不会报错,会有精度的损失。
当对decimal类型进行操作时,数值会因溢出而报错。
-
BLOB和TEXT分别适合于存储什么类型的数据?
BLOB适合存储音频、图片信息等。
TEXT只能存储纯文本文件。 -
说明ENUM和SET类型的区别以及在什么情况下适用?
ENUM只能取单值,最多有65535个选项
加了引号,SET可以取多值,最多有64个选项 -
在MySQL中执行如下算术运算:(9-7)*4,8+15/3,39%12
mysql> select (9-7)*4; +---------+ | (9-7)*4 | +---------+ | 8 | +---------+ 1 row in set (0.00 sec) mysql> select 8+15/3; +---------+ | 8+15/3 | +---------+ | 13.0000 | +---------+ 1 row in set (0.00 sec) mysql> select 39%12; +-------+ | 39%12 | +-------+ | 3 | +-------+ 1 row in set (0.00 sec)
-
在MySQL中执行如下比较运算:36>27,15>=8,40<50,15<=15,NULL<==>NULL
mysql> select 36>27,15>=8,40<50,15<=15,NULL<=>NULL; +-------+-------+-------+--------+-------------+ | 36>27 | 15>=8 | 40<50 | 15<=15 | NULL<=>NULL | +-------+-------+-------+--------+-------------+ | 1 | 1 | 1 | 1 | 1 | +-------+-------+-------+--------+-------------+ 1 row in set (0.00 sec)
-
在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; +------+----------+------------+---------+----+ | 4&&8 | -2||NULL | NULL xor 0 | 0 xor 1 | !2 | +------+----------+------------+---------+----+ | 1 | 1 | NULL | 1 | 0 | +------+----------+------------+---------+----+ 1 row in set (0.00 sec)
-
在MySQL中执行如下位运算:13&17,20|8,14^20,~16
mysql> select 13&17,20|8,14^20,~16; +-------+------+-------+----------------------+ | 13&17 | 20|8 | 14^20 | ~16 | +-------+------+-------+----------------------+ | 1 | 28 | 26 | 18446744073709551599 | +-------+------+-------+----------------------+ 1 row in set (0.00 sec)