MySQL 考试练习题
1、用户表account
1、用户表account（用户编号userid，用户名fullname，密码passward，性别sex，住址adderss，邮箱email，电话phone）
-
account(userid char(6),fullname char(4),passward char(7),sex char(2),adderess char(6),email char(20),phone char(12)) -
account(userid,fullname,passward,sex,adderess,email,phone)
('u0001','刘晓和','123456','男','广东深圳市','liuxh@163.com','13512345678'),
('u0002','张嘉庆','123456','男','广东深圳市','zhangjq@163.com','13512345679'),
('u0003','罗红红','123456','女','广东深圳市','luohh@163.com','13512345689'),
('u0004','李昊华','123456','女','广东深圳市','lihh@163.com','13812345679'),
('u0005','王天赐','123456','男','广东中山市','wangtc@163.com','13802345679'),
('u0006','吴美霞','123456','女','广东珠海市','wumx@163.com','13512345879');
代码过程
#显示所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| club |
| exam |
| mysql |
| performance_schema |
| qq |
| sys |
+--------------------+
7 rows in set (0.02 sec)
#创建exam数据库
mysql> create database exam;
Query OK, 1 row affected (0.01 sec)
#定义字符类型为utf8
mysql> alter database exam charset utf8;
Query OK, 1 row affected (0.00 sec)
#进入exam数据库
mysql> use exam;
Database changed
#创建数据库的字段
mysql> create table account(userid char(6),fullname char(4),passward char(7),sex char(2),adderess char(6),email char(20),phone char(12));
Query OK, 0 rows affected (0.03 sec)
#进入数据库exam
mysql> use exam;
Database changed
#选择account数据表
mysql> select * from account;
Empty set (0.00 sec)
#插入新的内容
mysql> insert into account(userid,fullname,passward,sex,adderess,email,phone) values('u0001','刘晓和','123456','男','广 东深圳市','liuxh@163.com','13512345678');
Query OK, 1 row affected (0.01 sec)
#批量插入新的内容
mysql> insert into account(userid,fullname,passward,sex,adderess,email,phone) values
-> ('u0002','张嘉庆','123456','男','广东深圳市','zhangjq@163.com','13512345679'),
-> ('u0003','罗红红','123456','女','广东深圳市','luohh@163.com','13512345689'),
-> ('u0004','李昊华','123456','女','广东深圳市','lihh@163.com','13812345679'),
-> ('u0005','王天赐','123456','男','广东中山市','wangtc@163.com','13802345679'),
-> ('u0006','吴美霞','123456','女','广东珠海市','wumx@163.com','13512345879');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
#显示所有内容
mysql> select * from account;
+--------+-----------+----------+------+-----------------+-----------------+-------------+
| userid | fullname | passward | sex | adderess | email | phone |
+--------+-----------+----------+------+-----------------+-----------------+-------------+
| u0001 | 刘晓和 | 123456 | 男 | 广东深圳市 | liuxh@163.com | 13512345678 |
| u0002 | 张嘉庆 | 123456 | 男 | 广东深圳市 | zhangjq@163.com | 13512345679 |
| u0003 | 罗红红 | 123456 | 女 | 广东深圳市 | luohh@163.com | 13512345689 |
| u0004 | 李昊华 | 123456 | 女 | 广东深圳市 | lihh@163.com | 13812345679 |
| u0005 | 王天赐 | 123456 | 男 | 广东中山市 | wangtc@163.com | 13802345679 |
| u0006 | 吴美霞 | 123456 | 女 | 广东珠海市 | wumx@163.com | 13512345879 |
+--------+-----------+----------+------+-----------------+-----------------+-------------+
6 rows in set (0.00 sec)
2、商品表product
-
（商品编号productid，商品分类号catid，商品名name，商品介绍descn，市场价格listprice，成本价格unitcost，数量qty） -
(productid,catid,name,descn,listprice,unitcost,qty) -
product(productid char(15),catid char(3),name char(10),descn char(15),listprice float(7,2),unitcost float(7,2),qty float(7,2))
('av-cb-01','05','亚马逊鹦鹉','75岁以上高龄的好伙伴',50.00,60.00,100),
('av-sb-02','05','燕雀','非常好的减压宠物',45.00,50.00,98),
('fi-fw-01','01','锦鲤','来自日本的淡水鱼',45.50,45.50,300),
('fi-fw-02','01','金鱼','来自中国的淡水鱼',6.80,6.80,100),
('fi-sw-01','01','天使鱼','来自澳大利亚的海水鱼',10.00,10.00,100),
('fi-sw-02','01','虎鲨','来自澳大利亚的海水鱼',18.50,20.00,200),
('fl-dlh-02','04','波斯猫','友好的家钜猫',1000.00,1200.00,15),
('fl-dsh-01','04','马恩岛猫','灭鼠能手',80.00,100.00,40),
('k9-bd-01','02','牛头犬','来自英格兰的友好的狗',1350.00,1500.00,5),
('k9-cw-01','02','吉娃娃犬','很好的陪伴狗',180.00,200.00,120),
('k9-dl-01','02','斑点狗','来自消防队的大狗',3000.00,3000.00,1),
('k9-po-02','02','狮子犬','来自法国可爱的狗',300.00,300.00,200),
('rp-li-02','03','鼠蹊','友好的绿色朋友',60.00,78.00,40),
('rp-sn-01','03','响尾蛇','兼当看门狗',200.00,240.00,10);
代码过程
mysql> create table product(productid char(15),catid char(3),name char(10),descn char(15),listprice float(7,2),unitcost float(7,2),qty float(7,2))
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from product;
Empty set (0.00 sec)
mysql> insert into product(productid,catid,name,descn,listprice,unitcost,qty) values
-> ('av-cb-01','05','亚马逊鹦鹉','75岁以上高龄的好伙伴',50.00,60.00,100),
-> ('av-sb-02','05','燕雀','非常好的减压宠物',45.00,50.00,98),
-> ('fi-fw-01','01','锦鲤','来自日本的淡水鱼',45.50,45.50,300),
-> ('fi-fw-02','01','金鱼','来自中国的淡水鱼',6.80,6.80,100),
-> ('fi-sw-01','01','天使鱼','来自澳大利亚的海水鱼',10.00,10.00,100),
-> ('fi-sw-02','01','虎鲨','来自澳大利亚的海水鱼',18.50,20.00,200),
-> ('fl-dlh-02','04','波斯猫','友好的家钜猫',1000.00,1200.00,15),
-> ('fl-dsh-01','04','马恩岛猫','灭鼠能手',80.00,100.00,40),
-> ('k9-bd-01','02','牛头犬','来自英格兰的友好的狗',1350.00,1500.00,5),
-> ('k9-cw-01','02','吉娃娃犬','很好的陪伴狗',180.00,200.00,120),
-> ('k9-dl-01','02','斑点狗','来自消防队的大狗',3000.00,3000.00,1),
-> ('k9-po-02','02','狮子犬','来自法国可爱的狗',300.00,300.00,200),
-> ('rp-li-02','03','鼠蹊','友好的绿色朋友',60.00,78.00,40),
-> ('rp-sn-01','03','响尾蛇','兼当看门狗',200.00,240.00,10);
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * fromm product;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fromm product' at line 1
mysql> select * from product;
+-----------+-------+-----------------+--------------------------------+-----------+----------+--------+
| productid | catid | name | descn | listprice | unitcost | qty |
+-----------+-------+-----------------+--------------------------------+-----------+----------+--------+
| av-cb-01 | 05 | 亚马逊鹦鹉 | 75岁以上高龄的好伙伴 | 50.00 | 60.00 | 100.00 |
| av-sb-02 | 05 | 燕雀 | 非常好的减压宠物 | 45.00 | 50.00 | 98.00 |
| fi-fw-01 | 01 | 锦鲤 | 来自日本的淡水鱼 | 45.50 | 45.50 | 300.00 |
| fi-fw-02 | 01 | 金鱼 | 来自中国的淡水鱼 | 6.80 | 6.80 | 100.00 |
| fi-sw-01 | 01 | 天使鱼 | 来自澳大利亚的海水鱼 | 10.00 | 10.00 | 100.00 |
| fi-sw-02 | 01 | 虎鲨 | 来自澳大利亚的海水鱼 | 18.50 | 20.00 | 200.00 |
| fl-dlh-02 | 04 | 波斯猫 | 友好的家钜猫 | 1000.00 | 1200.00 | 15.00 |
| fl-dsh-01 | 04 | 马恩岛猫 | 灭鼠能手 | 80.00 | 100.00 | 40.00 |
| k9-bd-01 | 02 | 牛头犬 | 来自英格兰的友好的狗 | 1350.00 | 1500.00 | 5.00 |
| k9-cw-01 | 02 | 吉娃娃犬 | 很好的陪伴狗 | 180.00 | 200.00 | 120.00 |
| k9-dl-01 | 02 | 斑点狗 | 来自消防队的大狗 | 3000.00 | 3000.00 | 1.00 |
| k9-po-02 | 02 | 狮子犬 | 来自法国可爱的狗 | 300.00 | 300.00 | 200.00 |
| rp-li-02 | 03 | 鼠蹊 | 友好的绿色朋友 | 60.00 | 78.00 | 40.00 |
| rp-sn-01 | 03 | 响尾蛇 | 兼当看门狗 | 200.00 | 240.00 | 10.00 |
+-----------+-------+-----------------+--------------------------------+-----------+----------+--------+
14 rows in set (0.00 sec)
3、购物车表shopcat
-
（购物车编号shopcatid，用户编号userid，商品编号productid，数量quantity，单价unitprice，总价totalprice，订单日期orderdate，订单状态status） -
(shopcatid,userid,productid,quantity,unitprice,totalprice,orderdate,status) -
(shopcatid int,userid char(6),productid char(9),quantity int,unitprice float(6,2),totalprice int,orderdate char(11),status int)
(20130411,'u0001', 'fi-sw-01',10,18.50,0,'2013-04-11 15:07:34', 0),
(20130412,'u0002', 'k9-bd-01',2,120.00,0,'2013-04-09 15:08:11', 0),
(20130413,'u0003', 'k9-dl-01',2,130.00,0,'2013-04-15 15:09:34', 0),
(20130414,'u0003', 'rp-sn-01',3,125.00,0,'2013-04-15 15:09:30', 1),
(20130415,'u0004', 'av-sb-02',2,50.00,0,'2013-04-02 15:10:34', 0);
代码过程
mysql> create table shopcat;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table shopcat(shopcatid int,userid char(6),productid char(9),quantity int,unitprice float(6,2),totalprice int,orderdate char(11),status int);
Query OK, 0 rows affected (0.02 sec)
mysql> select * from shopcat;
Empty set (0.00 sec)
mysql> insert into shopcat(shopcatid,userid,productid,quantity,unitprice,totalprice,orderdate,status) values
-> (20130411,'u0001', 'fi-sw-01',10,18.50,0,'2013-04-11 15:07:34', 0),
-> (20130412,'u0002', 'k9-bd-01',2,120.00,0,'2013-04-09 15:08:11', 0),
-> (20130413,'u0003', 'k9-dl-01',2,130.00,0,'2013-04-15 15:09:34', 0),
-> (20130414,'u0003', 'rp-sn-01',3,125.00,0,'2013-04-15 15:09:30', 1),
-> (20130415,'u0004', 'av-sb-02',2,50.00,0,'2013-04-02 15:10:34', 0);
ERROR 1406 (22001): Data too long for column 'orderdate' at row 1
mysql> create table shopcat(shopcatid int,userid char(6),productid char(9),quantity int,unitprice float(6,2),totalprice int,orderdate char(25),status int);
ERROR 1050 (42S01): Table 'shopcat' already exists
# 修改字段长度命令
mysql> alter table shopcat modify column orderdate varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into shopcat(shopcatid,userid,productid,quantity,unitprice,totalprice,orderdate,status) values
-> (20130411,'u0001', 'fi-sw-01',10,18.50,0,'2013-04-11 15:07:34', 0),
-> (20130412,'u0002', 'k9-bd-01',2,120.00,0,'2013-04-09 15:08:11', 0),
-> (20130413,'u0003', 'k9-dl-01',2,130.00,0,'2013-04-15 15:09:34', 0),
-> (20130414,'u0003', 'rp-sn-01',3,125.00,0,'2013-04-15 15:09:30', 1),
-> (20130415,'u0004', 'av-sb-02',2,50.00,0,'2013-04-02 15:10:34', 0);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from shopcat;
+-----------+--------+-----------+----------+-----------+------------+---------------------+--------+
| shopcatid | userid | productid | quantity | unitprice | totalprice | orderdate | status |
+-----------+--------+-----------+----------+-----------+------------+---------------------+--------+
| 20130411 | u0001 | fi-sw-01 | 10 | 18.50 | 0 | 2013-04-11 15:07:34 | 0 |
| 20130412 | u0002 | k9-bd-01 | 2 | 120.00 | 0 | 2013-04-09 15:08:11 | 0 |
| 20130413 | u0003 | k9-dl-01 | 2 | 130.00 | 0 | 2013-04-15 15:09:34 | 0 |
| 20130414 | u0003 | rp-sn-01 | 3 | 125.00 | 0 | 2013-04-15 15:09:30 | 1 |
| 20130415 | u0004 | av-sb-02 | 2 | 50.00 | 0 | 2013-04-02 15:10:34 | 0 |
+-----------+--------+-----------+----------+-----------+------------+---------------------+--------+
5 rows in set (0.00 sec)
代码说明：修改字段长度命令
- 语法
alter table 表名 modify column 列名 类型(要修改的长度); - 修改mrflysand表中id的char值为10个字长
alter table mrflysand modify column id varchar(10);
