mysql> select * from book;
+----------------------+-------+---------------------+-------+------------------
---+----------+
| sname | price | upload_time | state | borrow_time
| borrower |
+----------------------+-------+---------------------+-------+------------------
---+----------+
| JAVA基础入门 | 35.4 | 2024-04-11 09:11:16 | 0 | NULL
| NULL |
| 三国演义 | 41.4 | 2024-04-11 09:23:11 | 2 | NULL
| NULL |
| MYSQL数据库入门 | 24 | 2024-04-11 09:23:11 | 1 | 2024-04-11 09:35:
09 | 1 |
| 西游记 | 35.4 | 2024-04-11 09:23:11 | 0 | NULL
| NULL |
| 水浒传 | 39.6 | 2024-04-11 09:23:11 | 2 | NULL
| NULL |
| JAVA开阀入门 | 29.4 | 2024-04-11 09:24:38 | 0 | NULL
| NULL |
+----------------------+-------+---------------------+-------+------------------
---+----------+
6 rows in set (0.02 sec)
mysql> select * from book where state=0 ;
+------------------+-------+---------------------+-------+-------------+--------
--+
| sname | price | upload_time | state | borrow_time | borrowe
r |
+------------------+-------+---------------------+-------+-------------+--------
--+
| JAVA基础入门 | 35.4 | 2024-04-11 09:11:16 | 0 | NULL | NUL
L |
| 西游记 | 35.4 | 2024-04-11 09:23:11 | 0 | NULL | NUL
L |
| JAVA开阀入门 | 29.4 | 2024-04-11 09:24:38 | 0 | NULL | NUL
L |
+------------------+-------+---------------------+-------+-------------+--------
--+
3 rows in set (0.00 sec)
mysql> select sname,upload_time from book where state=0 ;
+------------------+---------------------+
| sname | upload_time |
+------------------+---------------------+
| JAVA基础入门 | 2024-04-11 09:11:16 |
| 西游记 | 2024-04-11 09:23:11 |
| JAVA开阀入门 | 2024-04-11 09:24:38 |
+------------------+---------------------+
3 rows in set (0.00 sec)
mysql> select sname,price,state from book order by sname asc limit 5 ;
+----------------------+-------+-------+
| sname | price | state |
+----------------------+-------+-------+
| JAVA基础入门 | 35.4 | 0 |
| JAVA开阀入门 | 29.4 | 0 |
| MYSQL数据库入门 | 24 | 1 |
| 三国演义 | 41.4 | 2 |
| 水浒传 | 39.6 | 2 |
+----------------------+-------+-------+
5 rows in set (0.02 sec)
mysql> select sname,price from book where price>50 ;
Empty set (0.02 sec)
mysql> update book set price=60 where state=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select sname,price from book where price>50 ;
+----------------------+-------+
| sname | price |
+----------------------+-------+
| MYSQL数据库入门 | 60 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> select sname,price from book where price>=30 && price<=50 ;
+------------------+-------+
| sname | price |
+------------------+-------+
| JAVA基础入门 | 35.4 |
| 三国演义 | 41.4 |
| 西游记 | 35.4 |
| 水浒传 | 39.6 |
+------------------+-------+
4 rows in set (0.00 sec)
mysql> select sname,price from book where price between 30 and 50 ;
+------------------+-------+
| sname | price |
+------------------+-------+
| JAVA基础入门 | 35.4 |
| 三国演义 | 41.4 |
| 西游记 | 35.4 |
| 水浒传 | 39.6 |
+------------------+-------+
4 rows in set (0.01 sec)
mysql> show full columns from book\G;
*************************** 1. row ***************************
Field: sname
Type: varchar(60)
Collation: utf8mb4_general_ci
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment: 123
*************************** 2. row ***************************
Field: price
Type: float
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 3. row ***************************
Field: upload_time
Type: datetime
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 4. row ***************************
Field: state
Type: int(11)
Collation: NULL
Null: NO
Key:
Default: 0
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 5. row ***************************
Field: borrow_time
Type: datetime
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 6. row ***************************
Field: borrower
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
6 rows in set (0.00 sec)
ERROR:
No query specified
mysql> select sname,borrow_time,borrower from book where state=1;
+----------------------+---------------------+----------+
| sname | borrow_time | borrower |
+----------------------+---------------------+----------+
| MYSQL数据库入门 | 2024-04-11 09:35:09 | 1 |
+----------------------+---------------------+----------+
1 row in set (0.00 sec)
mysql> select sname from book where sname like '%java%' ;
+------------------+
| sname |
+------------------+
| JAVA基础入门 |
| JAVA开阀入门 |
+------------------+
2 rows in set (0.02 sec)
mysql> select sname from book where sname like '%入门' ;
+----------------------+
| sname |
+----------------------+
| JAVA基础入门 |
| MYSQL数据库入门 |
| JAVA开阀入门 |
+----------------------+
3 rows in set (0.00 sec)
mysql> select * from book where name='西游记' or name='红楼梦' ;
ERROR 1054 (42S22): Unknown column 'name' in 'where clause'
mysql> select * from book where sname='西游记' or sname='红楼梦' ;
+-----------+-------+---------------------+-------+-------------+----------+
| sname | price | upload_time | state | borrow_time | borrower |
+-----------+-------+---------------------+-------+-------------+----------+
| 西游记 | 35.4 | 2024-04-11 09:23:11 | 0 | NULL | NULL |
+-----------+-------+---------------------+-------+-------------+----------+
1 row in set (0.00 sec)
mysql> insert into book values ('红楼梦',60,2024/6/9,2,null,null);
ERROR 1292 (22007): Incorrect datetime value: '37.481481481444444444' for column
'upload_time' at row 1
mysql> insert into book values ('红楼梦',60,2024/06/09,2,null,null);
ERROR 1292 (22007): Incorrect datetime value: '37.481481481444444444' for column
'upload_time' at row 1
mysql> insert into book values ('红楼梦',60,2024-06-09,2,null,null);
ERROR 1292 (22007): Incorrect datetime value: '2009' for column 'upload_time' at
row 1
mysql> insert into book values ('红楼梦',60,2024-04-11 09:23:11,2,null,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 '09:23
:11,2,null,null)' at line 1
mysql> insert into book values ('红楼梦',60,'2024-04-11 09:23:11',2,null,null);
Query OK, 1 row affected (0.02 sec)
mysql> select * from book where sname='西游记' or sname='红楼梦' ;
+-----------+-------+---------------------+-------+-------------+----------+
| sname | price | upload_time | state | borrow_time | borrower |
+-----------+-------+---------------------+-------+-------------+----------+
| 西游记 | 35.4 | 2024-04-11 09:23:11 | 0 | NULL | NULL |
| 红楼梦 | 60 | 2024-04-11 09:23:11 | 2 | NULL | NULL |
+-----------+-------+---------------------+-------+-------------+----------+
2 rows in set (0.00 sec)
mysql> select * from book where sname in ('红楼梦','西游记') ;
+-----------+-------+---------------------+-------+-------------+----------+
| sname | price | upload_time | state | borrow_time | borrower |
+-----------+-------+---------------------+-------+-------------+----------+
| 西游记 | 35.4 | 2024-04-11 09:23:11 | 0 | NULL | NULL |
| 红楼梦 | 60 | 2024-04-11 09:23:11 | 2 | NULL | NULL |
+-----------+-------+---------------------+-------+-------------+----------+
2 rows in set (0.02 sec)
mysql> select * from book group by state;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and c
ontains nonaggregated column 'lb_db.book.sname' which is not functionally depend
ent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_
group_by
mysql> select * from book where group by state;
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 'group
by state' at line 1
mysql> select state from book where group by state;
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 'group
by state' at line 1
mysql> select state from book group by state;
+-------+
| state |
+-------+
| 0 |
| 1 |
| 2 |
+-------+
3 rows in set (0.02 sec)
mysql> select * from book group by state;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and c
ontains nonaggregated column 'lb_db.book.sname' which is not functionally depend
ent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_
group_by
mysql> select state,count(*) from book group by state;
+-------+----------+
| state | count(*) |
+-------+----------+
| 0 | 3 |
| 1 | 1 |
| 2 | 3 |
+-------+----------+
3 rows in set (0.02 sec)
mysql> select state,count(*) total from book group by state;
+-------+-------+
| state | total |
+-------+-------+
| 0 | 3 |
| 1 | 1 |
| 2 | 3 |
+-------+-------+
3 rows in set (0.01 sec)
mysql> select state,count(*) total from book group by state having state>0;
+-------+-------+
| state | total |
+-------+-------+
| 1 | 1 |
| 2 | 3 |
+-------+-------+
2 rows in set (0.02 sec)
mysql>
