cc@cc-Inspiron-3542:~$ mysql -u root -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 这种bc |
| bc |
| class1804 |
| class18042 |
| class1804h |
| class1804n |
| class1804p |
| class222 |
| compamy |
| evenhom |
| gebilaowang |
| gradeinfo |
| mysql |
| performance_schema |
| sys |
| test |
| waijian |
+--------------------+
18 rows in set (0.22 sec)
mysql> use evenhom;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| customer |
| orde |
| orderdetail |
| product |
+-------------------+
4 rows in set (0.00 sec)
mysql> insert into orde values(
-> 1,20180911,1),(2,20180912,2),(3,20180914,3),(4,20180914),(5,20180918,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 4
mysql> insert into orde values(1,20180911,1),(2,20180912,2),(3,20180914,3),(4,20180914),(5,20180918,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 4
mysql> show select table orde;
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 'select table orde' at line 1
mysql> show create table orde;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orde | CREATE TABLE orde (
orderid int(11) NOT NULL AUTO_INCREMENT,
orderdate date DEFAULT NULL,
customerid int(11) DEFAULT NULL,
PRIMARY KEY (orderid),
KEY FK_ID (customerid),
CONSTRAINT FK_ID FOREIGN KEY (customerid) REFERENCES customer (customerid),
CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into orde(orderid,orderdate,customerid) values(1,20180911,1),(2,20180912,2),(3,20180914,3),(4,20180914),(5,20180918,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 4
mysql> insert into orde(orderid,orderdate,customerid) values(1,20180911,1),(2,20180912,2),(3,20180914,3),(4,20180914),(5,20180918,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 4
mysql> desc orde;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| orderid | int(11) | NO | PRI | NULL | auto_increment |
| orderdate | date | YES | | NULL | |
| customerid | int(11) | YES | MUL | NULL | |
+------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into orde(orderid,orderdate) values(1,20180911),(2,20180912),(3,20180914),(4,20180914),(5,20180918);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> insert into orde(orderid,orderdate) values(1,20180911),(2,20180912),(3,20180914),(4,20180914),(5,20180918);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> insert into orde(orderid,orderdate) values(1,20180911),(2,20180912),(3,20180914),(4,20180914),(5,20180918);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> insert into orde(orderid,orderdate) values(1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> insert into orde(orderid,orderdate) values(1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> select * from orde;
Empty set (0.00 sec)
mysql> insert into orde(orderid,orderdate) values (1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> insert into orde(orderid,orderdate) values (1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> alter table orde drop CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid);
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 'CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid)' at line 1
mysql> alter table orde drop foreign key FK_ID1;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table orde add constraint FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid)
-> ;
Query OK, 0 rows affected (1.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into orde(orderid,orderdate) values (1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> insert into orde(orderid,orderdate) values (1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> insert into orde(orderid,orderdate) values (1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.orde, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> alter table orde drop foreign key FK_ID1; Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into orde(orderid,orderdate) values (1,'20180911'),(2,'20180912'),(3,'20180914'),(4,'20180914'),(5,'20180918');
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> alter table orde add constraint FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid)
-> ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (evenhom.#sql-3c4_3, CONSTRAINT FK_ID1 FOREIGN KEY (orderid) REFERENCES orderdetail (orderid))
mysql> select * from orde; +---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | NULL |
| 2 | 2018-09-12 | NULL |
| 3 | 2018-09-14 | NULL |
| 4 | 2018-09-14 | NULL |
| 5 | 2018-09-18 | NULL |
+---------+------------+------------+
5 rows in set (0.00 sec)
mysql> show create table orde; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orde | CREATE TABLE orde (
orderid int(11) NOT NULL AUTO_INCREMENT,
orderdate date DEFAULT NULL,
customerid int(11) DEFAULT NULL,
PRIMARY KEY (orderid),
KEY FK_ID (customerid),
CONSTRAINT FK_ID FOREIGN KEY (customerid) REFERENCES customer (customerid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| customer |
| orde |
| orderdetail |
| product |
+-------------------+
4 rows in set (0.01 sec)
mysql> alter table orderdetail add constraint FK_ID2 FOREIGN KEY (productid) REFERENCES orderdetail (orderid,productid);
ERROR 1239 (42000): Incorrect foreign key definition for 'FK_ID2': Key reference and table reference don't match
mysql> show create table product;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| product | CREATE TABLE product (
productid int(11) NOT NULL AUTO_INCREMENT,
unitprice int(11) NOT NULL,
productname varchar(30) NOT NULL,
PRIMARY KEY (productid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table orderdetail;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orderdetail | CREATE TABLE orderdetail (
orderid int(11) NOT NULL,
productid int(11) NOT NULL,
discount int(11) DEFAULT NULL,
quantity int(11) DEFAULT NULL,
PRIMARY KEY (orderid,productid),
KEY FK_ID2 (productid),
CONSTRAINT FK_ID2 FOREIGN KEY (productid) REFERENCES product (productid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table orderdetail add constraint FK_ID2 FOREIGN KEY (productid) REFERENCES product(productid);
ERROR 1022 (23000): Can't write; duplicate key in table '#sql-3c4_3'
mysql> show create table orde;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orde | CREATE TABLE orde (
orderid int(11) NOT NULL AUTO_INCREMENT,
orderdate date DEFAULT NULL,
customerid int(11) DEFAULT NULL,
PRIMARY KEY (orderid),
KEY FK_ID (customerid),
CONSTRAINT FK_ID FOREIGN KEY (customerid) REFERENCES customer (customerid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from orde;
+---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | NULL |
| 2 | 2018-09-12 | NULL |
| 3 | 2018-09-14 | NULL |
| 4 | 2018-09-14 | NULL |
| 5 | 2018-09-18 | NULL |
+---------+------------+------------+
5 rows in set (0.00 sec)
mysql> shoe tables;
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 'shoe tables' at line 1
mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| customer |
| orde |
| orderdetail |
| product |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from orderdetail inner join product on orderdetail.product=product.productid;
ERROR 1054 (42S22): Unknown column 'orderdetail.product' in 'on clause'
mysql> select * from orderdetail inner join product on orderdetail.productid=product.productid;
Empty set (0.00 sec)
mysql> select * from orderdetail;
Empty set (0.00 sec)
mysql> insert into orderdetail(orderid,productid,discount,quesntuty) values (
-> 1,1,0.8,2),(2,3,0.9,3),(3,4,1.0,2),(4,5,0.5,3),(5,4,0.6,2);
ERROR 1054 (42S22): Unknown column 'quesntuty' in 'field list'
mysql> show create table orde; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orde | CREATE TABLE orde (
orderid int(11) NOT NULL AUTO_INCREMENT,
orderdate date DEFAULT NULL,
customerid int(11) DEFAULT NULL,
PRIMARY KEY (orderid),
KEY FK_ID (customerid),
CONSTRAINT FK_ID FOREIGN KEY (customerid) REFERENCES customer (customerid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table ordedetail;
ERROR 1146 (42S02): Table 'evenhom.ordedetail' doesn't exist
mysql> show create table orderdetail;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orderdetail | CREATE TABLE orderdetail (
orderid int(11) NOT NULL,
productid int(11) NOT NULL,
discount int(11) DEFAULT NULL,
quantity int(11) DEFAULT NULL,
PRIMARY KEY (orderid,productid),
KEY FK_ID2 (productid),
CONSTRAINT FK_ID2 FOREIGN KEY (productid) REFERENCES product (productid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into orderdetail(orderid,productid,discount,quantity) values ( 1,1,0.8,2),(2,3,0.9,3),(3,4,1.0,2),(4,5,0.5,3),(5,4,0.6,2);
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show create table orderdetail; +-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orderdetail | CREATE TABLE orderdetail (
orderid int(11) NOT NULL,
productid int(11) NOT NULL,
discount int(11) DEFAULT NULL,
quantity int(11) DEFAULT NULL,
PRIMARY KEY (orderid,productid),
KEY FK_ID2 (productid),
CONSTRAINT FK_ID2 FOREIGN KEY (productid) REFERENCES product (productid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from orderdetail;
+---------+-----------+----------+----------+
| orderid | productid | discount | quantity |
+---------+-----------+----------+----------+
| 1 | 1 | 1 | 2 |
| 2 | 3 | 1 | 3 |
| 3 | 4 | 1 | 2 |
| 4 | 5 | 1 | 3 |
| 5 | 4 | 1 | 2 |
+---------+-----------+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from orderdetail inner join product on orderdetail.productid=product.productid;
+---------+-----------+----------+----------+-----------+-----------+-------------+
| orderid | productid | discount | quantity | productid | unitprice | productname |
+---------+-----------+----------+----------+-----------+-----------+-------------+
| 1 | 1 | 1 | 2 | 1 | 18 | 洗发水 |
| 2 | 3 | 1 | 3 | 3 | 28 | 牛奶 |
| 3 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
| 4 | 5 | 1 | 3 | 5 | 78 | 牛肉 |
| 5 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
+---------+-----------+----------+----------+-----------+-----------+-------------+
5 rows in set (0.00 sec)
mysql> mysql> select * from orderjoin product on orderdetail.orderid=order.orderid;
ERROR 1054 (42S22): Unknown column 'order.orderid' in 'on clause'
mysql> select * from orde join product on orderdetail.orderid=orde.orderid;
ERROR 1054 (42S22): Unknown column 'orderdetail.orderid' in 'on clause'
mysql> select * from orde join product on orde.orderid=orderdetail.orderid;
ERROR 1054 (42S22): Unknown column 'orderdetail.orderid' in 'on clause'
mysql> select * from orde join product on orde.orderid=orderdetail.orderid;
ERROR 1054 (42S22): Unknown column 'orderdetail.orderid' in 'on clause'
mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| customer |
| orde |
| orderdetail |
| product |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from orde join orderdetail on orde.orderid=orderdetail.orderid;
+---------+------------+------------+---------+-----------+----------+----------+
| orderid | orderdate | customerid | orderid | productid | discount | quantity |
+---------+------------+------------+---------+-----------+----------+----------+
| 1 | 2018-09-11 | NULL | 1 | 1 | 1 | 2 |
| 2 | 2018-09-12 | NULL | 2 | 3 | 1 | 3 |
| 3 | 2018-09-14 | NULL | 3 | 4 | 1 | 2 |
| 4 | 2018-09-14 | NULL | 4 | 5 | 1 | 3 |
| 5 | 2018-09-18 | NULL | 5 | 4 | 1 | 2 |
+---------+------------+------------+---------+-----------+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from customer join order on orde.customerid=customer.customerid;
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 'order on orde.customerid=customer.customerid' at line 1
mysql> select * from customer join orde on orde.customerid=customer.customerid;
Empty set (0.00 sec)
mysql> select * from customer ;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
| 6 | 白义 | 北京市通州 | 北京 |
+------------+--------------+--------------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from customer join orde on orde.customerid=customer.customerid;
Empty set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| customer |
| orde |
| orderdetail |
| product |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from customer join orde on orde.customerid=customer.customerid;
Empty set (0.00 sec)
mysql> select * from customer;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
| 6 | 白义 | 北京市通州 | 北京 |
+------------+--------------+--------------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from orde;
+---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | NULL |
| 2 | 2018-09-12 | NULL |
| 3 | 2018-09-14 | NULL |
| 4 | 2018-09-14 | NULL |
| 5 | 2018-09-18 | NULL |
+---------+------------+------------+
5 rows in set (0.00 sec)
mysql> update orde set customerid customerid=1 where orderid=1;
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 'customerid=1 where orderid=1' at line 1
mysql> update orde set customerid=1 where orderid=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from orde;
+---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | 1 |
| 2 | 2018-09-12 | NULL |
| 3 | 2018-09-14 | NULL |
| 4 | 2018-09-14 | NULL |
| 5 | 2018-09-18 | NULL |
+---------+------------+------------+
5 rows in set (0.00 sec)
mysql> update orde set customerid customerid=2 where orderid=2;
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 'customerid=2 where orderid=2' at line 1
mysql> update orde set customerid customerid=2 where orderid=2;
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 'customerid=2 where orderid=2' at line 1
mysql> update orde set customerid=2 where orderid=2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update orde set customerid=3 where orderid=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update orde set customerid=4 where orderid=4;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update orde set customerid=5 where orderid=5;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from orde;
+---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | 1 |
| 2 | 2018-09-12 | 2 |
| 3 | 2018-09-14 | 3 |
| 4 | 2018-09-14 | 4 |
| 5 | 2018-09-18 | 5 |
+---------+------------+------------+
5 rows in set (0.00 sec)
mysql> select * from customer join orde on orde.customerid=customer.customerid;
+------------+--------------+--------------------+--------------+---------+------------+------------+
| customerid | customername | customeraddr | customercity | orderid | orderdate | customerid |
+------------+--------------+--------------------+--------------+---------+------------+------------+
| 1 | 李白 | 北京市通州 | 北京 | 1 | 2018-09-11 | 1 |
| 2 | 白聚义 | 北京市朝阳 | 北京 | 2 | 2018-09-12 | 2 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 | 3 | 2018-09-14 | 3 |
| 4 | 王伟 | 石家庄马家堡 | 河北 | 4 | 2018-09-14 | 4 |
| 5 | 吕布 | 保定绵阳镇 | 河北 | 5 | 2018-09-18 | 5 |
+------------+--------------+--------------------+--------------+---------+------------+------------+
5 rows in set (0.01 sec)
mysql> select (select unitprice from product ) from orderdetail where orderid=2 -> ;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select (select sun(unitprice) from product ) from orderdetail where orderid=2;
ERROR 1305 (42000): FUNCTION evenhom.sun does not exist
mysql> select (select sum(unitprice) from product ) from orderdetail where orderid=2;
+---------------------------------------+
| (select sum(unitprice) from product ) |
+---------------------------------------+
| 202 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select (select unitprice from product ) from orderdetail where orderid=2; ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from orderdetail;
+---------+-----------+----------+----------+
| orderid | productid | discount | quantity |
+---------+-----------+----------+----------+
| 1 | 1 | 1 | 2 |
| 2 | 3 | 1 | 3 |
| 3 | 4 | 1 | 2 |
| 4 | 5 | 1 | 3 |
| 5 | 4 | 1 | 2 |
+---------+-----------+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from order;
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 'order' at line 1
mysql> select * from orde;
+---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | 1 |
| 2 | 2018-09-12 | 2 |
| 3 | 2018-09-14 | 3 |
| 4 | 2018-09-14 | 4 |
| 5 | 2018-09-18 | 5 |
+---------+------------+------------+
5 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_evenhom |
+-------------------+
| customer |
| orde |
| orderdetail |
| product |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from customer;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
| 6 | 白义 | 北京市通州 | 北京 |
+------------+--------------+--------------------+--------------+
6 rows in set (0.00 sec)
mysql> select * from product;
+-----------+-----------+-------------+
| productid | unitprice | productname |
+-----------+-----------+-------------+
| 1 | 18 | 洗发水 |
| 2 | 20 | 沐浴露 |
| 3 | 28 | 牛奶 |
| 4 | 58 | 榴莲 |
| 5 | 78 | 牛肉 |
+-----------+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select * from orde;
+---------+------------+------------+
| orderid | orderdate | customerid |
+---------+------------+------------+
| 1 | 2018-09-11 | 1 |
| 2 | 2018-09-12 | 2 |
| 3 | 2018-09-14 | 3 |
| 4 | 2018-09-14 | 4 |
| 5 | 2018-09-18 | 5 |
+---------+------------+------------+
5 rows in set (0.01 sec)
mysql> select * from orderdetail;
+---------+-----------+----------+----------+
| orderid | productid | discount | quantity |
+---------+-----------+----------+----------+
| 1 | 1 | 1 | 2 |
| 2 | 3 | 1 | 3 |
| 3 | 4 | 1 | 2 |
| 4 | 5 | 1 | 3 |
| 5 | 4 | 1 | 2 |
+---------+-----------+----------+----------+
5 rows in set (0.00 sec)
mysql> select (select unitprice from product where productid=2) from orderdetail where orderid=2;
+---------------------------------------------------+
| (select unitprice from product where productid=2) |
+---------------------------------------------------+
| 20 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select (select unitprice from product where productid=2)quantity from orderdetail where orderid=2;
+------------------------------------------------------------+
| (select unitprice from product where productid=2)quantity |
+------------------------------------------------------------+
| 60 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select (select unitprice from product where productid=2)quantitydiscount from orderdetail where orderid=2;
+---------------------------------------------------------------------+
| (select unitprice from product where productid=2)quantitydiscount |
+---------------------------------------------------------------------+
| 60 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select (select unitprice from product where productid=2)quantitydiscount as zongjia from orderdetail where orderid=2;
+---------+
| zongjia |
+---------+
| 60 |
+---------+
1 row in set (0.00 sec)
mysql> select * from orderdetail inner join product on orderdetail.productid=product.unitprice;
Empty set (0.00 sec)
mysql> select * from orderdetail;
+---------+-----------+----------+----------+
| orderid | productid | discount | quantity |
+---------+-----------+----------+----------+
| 1 | 1 | 1 | 2 |
| 2 | 3 | 1 | 3 |
| 3 | 4 | 1 | 2 |
| 4 | 5 | 1 | 3 |
| 5 | 4 | 1 | 2 |
+---------+-----------+----------+----------+
5 rows in set (0.00 sec)
mysql> select * from orderdetail inner join product on orderdetail.productid=product.productid;
+---------+-----------+----------+----------+-----------+-----------+-------------+
| orderid | productid | discount | quantity | productid | unitprice | productname |
+---------+-----------+----------+----------+-----------+-----------+-------------+
| 1 | 1 | 1 | 2 | 1 | 18 | 洗发水 |
| 2 | 3 | 1 | 3 | 3 | 28 | 牛奶 |
| 3 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
| 4 | 5 | 1 | 3 | 5 | 78 | 牛肉 |
| 5 | 4 | 1 | 2 | 4 | 58 | 榴莲 |
+---------+-----------+----------+----------+-----------+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select productname,unitprice from orderdetail inner join product on orderdetail.productid=product.productid;
+-------------+-----------+
| productname | unitprice |
+-------------+-----------+
| 洗发水 | 18 |
| 牛奶 | 28 |
| 榴莲 | 58 |
| 榴莲 | 58 |
| 牛肉 | 78 |
+-------------+-----------+
5 rows in set (0.00 sec)
mysql> select productname,unitprice,discount from orderdetail inner join product on orderdetail.productid=product.productid;
+-------------+-----------+----------+
| productname | unitprice | discount |
+-------------+-----------+----------+
| 洗发水 | 18 | 1 |
| 牛奶 | 28 | 1 |
| 榴莲 | 58 | 1 |
| 牛肉 | 78 | 1 |
| 榴莲 | 58 | 1 |
+-------------+-----------+----------+
5 rows in set (0.00 sec)
mysql> select productname,discount.unitprice,discount.discount from orderdetail inne[1]+ 已停止 mysql -u root -pduct.productid;
cc@cc-Inspiron-3542:~$ mysql -u root -p;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.23-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select productname,unitprice,discount from orderdetail inner join product on orderdetail.productid=product.productid;
ERROR 1046 (3D000): No database selected
mysql> show datase;
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 'datase' at line 1
mysql> show database;
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 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 这种bc |
| bc |
| class1804 |
| class18042 |
| class1804h |
| class1804n |
| class1804p |
| class222 |
| compamy |
| evenhom |
| gebilaowang |
| gradeinfo |
| mysql |
| performance_schema |
| sys |
| test |
| waijian |
+--------------------+
18 rows in set (0.00 sec)
mysql> use evenhom;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select productname,unitprice,discount from orderdetail inner join product on orderdetail.productid=product.productid;
+-------------+-----------+----------+
| productname | unitprice | discount |
+-------------+-----------+----------+
| 洗发水 | 18 | 1 |
| 牛奶 | 28 | 1 |
| 榴莲 | 58 | 1 |
| 牛肉 | 78 | 1 |
| 榴莲 | 58 | 1 |
+-------------+-----------+----------+
5 rows in set (0.00 sec)
mysql> select orderdetail.productname,orderdetail.unitprice,orderdetail.discount from orderdetail inne
ERROR 1054 (42S22): Unknown column 'orderdetail.productname' in 'field list'
mysql> select product.productname,orderdetail.unitprice,orderdetail.discount from orderdetail inner join product on orderdetail.productid=product.productid;
ERROR 1054 (42S22): Unknown column 'orderdetail.unitprice' in 'field list'
mysql> select product.productname,product.unitprice,product.discount from orderdetail inne
ERROR 1054 (42S22): Unknown column 'product.discount' in 'field list'
mysql> select product.productname,product.unitprice,orderdetail.discount from orderdetail inne
+-------------+-----------+----------+
| productname | unitprice | discount |
+-------------+-----------+----------+
| 洗发水 | 18 | 1 |
| 牛奶 | 28 | 1 |
| 榴莲 | 58 | 1 |
| 牛肉 | 78 | 1 |
| 榴莲 | 58 | 1 |
+-------------+-----------+----------+
5 rows in set (0.00 sec)
mysql> select (select orderid from orderdetail) from orderdetail where quantity not is null
-> ;
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 'is null' at line 1
mysql> select (select orderid from orderdetail) from orderdetail where quantity not is null;
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 'is null' at line 1
mysql> select orderid from orderdetail where quantity not is null; 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 'is null' at line 1
mysql> select orderdetail.orderid from orderdetail where quantity not is null;
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 'is null' at line 1
mysql> select orderdetail.orderid from orderdetail where quantity!=0;
+---------+
| orderid |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---------+
5 rows in set (0.00 sec)
mysql> select orderdetail.orderid from orderdetail where quantity != 0;
+---------+
| orderid |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---------+
5 rows in set (0.00 sec)
mysql> select orderid from orderdetail where quantity != 0;
+---------+
| orderid |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---------+
5 rows in set (0.00 sec)
mysql> select orderid(select productname,unitprice,discount from orderdetail inner join product on orderdetail.productid=product.productid; ) from orderdetail where quantity != 0;
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 'select productname,unitprice,discount from orderdetail inner join product on ord' at line 1
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 ') from orderdetail where quantity != 0' at line 1
mysql> select orderid(select * from customer inner join orderdetail on orderdetail.productid=product.productid; ) from orderdetail where quantity != 0; 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 'select * from customer inner join orderdetail on orderdetail.productid=product.p' at line 1
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 ') from orderdetail where quantity != 0' at line 1
mysql> select (select customerid from order) from orderdetail where quantity != 0; 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 'order) from orderdetail where quantity != 0' at line 1
mysql> select (select sum(customerid )from order) from orderdetail where quantity != 0;
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 'order) from orderdetail where quantity != 0' at line 1
mysql> select * from orde where customerid>0 union select customername customeraddr from customer where customerid>0;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select customername customeraddr from orde where customerid>0 union select customername customeraddr
ERROR 1054 (42S22): Unknown column 'customername' in 'field list'
mysql> select * from orde where customerid>0 union select * from customer where customerid>0;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select * from customer where customerid=any(select customerid from order);
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 'order)' at line 1
mysql> select * from customer where customerid=any(select customerid from orde);
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
+------------+--------------+--------------------+--------------+
5 rows in set (0.00 sec)
mysql> select * from customer where customeraddr='北京市通州' union select from customer where customerid>3;
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 'from customer where customerid>3' at line 1
mysql> select * from customer where customeraddr='北京市通州' union select * from customer where customerid>3;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 6 | 白义 | 北京市通州 | 北京 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
+------------+--------------+--------------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from customer where customeraddr='北京市通州' union all select from customer where customerid>3;
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 'from customer where customerid>3' at line 1
mysql> select * from customer where customeraddr='北京市通州' union ALL select from customer where customerid>3;
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 'from customer where customerid>3' at line 1
mysql> select * from customer where customeraddr='北京市通州' union select * from customer where customerid>3;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 6 | 白义 | 北京市通州 | 北京 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
+------------+--------------+--------------------+--------------+
4 rows in set (0.00 sec)
mysql> select * from customer where customeraddr='北京市通州' union ALL select * from customer where customerid>3;
+------------+--------------+--------------------+--------------+
| customerid | customername | customeraddr | customercity |
+------------+--------------+--------------------+--------------+
| 1 | 李白 | 北京市通州 | 北京 |
| 6 | 白义 | 北京市通州 | 北京 |
| 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 吕布 | 保定绵阳镇 | 河北 |
| 6 | 白义 | 北京市通州 | 北京 |
+------------+--------------+--------------------+--------------+
5 rows in set (0.00 sec)
mysql> select * from orde inner join customer on orde.customerid=customer.customerid;
+---------+------------+------------+------------+--------------+--------------------+--------------+
| orderid | orderdate | customerid | customerid | customername | customeraddr | customercity |
+---------+------------+------------+------------+--------------+--------------------+--------------+
| 1 | 2018-09-11 | 1 | 1 | 李白 | 北京市通州 | 北京 |
| 2 | 2018-09-12 | 2 | 2 | 白聚义 | 北京市朝阳 | 北京 |
| 3 | 2018-09-14 | 3 | 3 | 赵子龙 | 襄阳市赵店 | 湖北 |
| 4 | 2018-09-14 | 4 | 4 | 王伟 | 石家庄马家堡 | 河北 |
| 5 | 2018-09-18 | 5 | 5 | 吕布 | 保定绵阳镇 | 河北 |
+---------+------------+------------+------------+--------------+--------------------+--------------+
5 rows in set (0.00 sec)
mysql>
网友评论