mysql>showdatabases;+--------------------+|Database|+--------------------+| information_schema || mysql || performance_schema || sys || z1 |+--------------------+5rowsinset(0.00 sec)
mysql>use z1
Database changed
mysql>showtables;+--------------+| Tables_in_z1 |+--------------+| customer || exam || goods || purchase |+--------------+4rowsinset(0.00 sec)
mysql>desc exam
->;+---------+--------------+------+-----+---------+-------+| Field |Type|Null|Key|Default| Extra |+---------+--------------+------+-----+---------+-------+| id |int| YES ||NULL||| name |varchar(20)| YES ||NULL||| chinese |decimal(4,1)| YES ||NULL||| math |decimal(4,1)| YES ||NULL||| english |decimal(4,1)| YES ||NULL||+---------+--------------+------+-----+---------+-------+5rowsinset(0.00 sec)
mysql>select*from exam limit5;+------+------+---------+------+---------+| id | name | chinese | math | english |+------+------+---------+------+---------+|1| z1 |23.0|23.0|23.0||2| z2 |49.0|54.0|65.0||3| z3 |87.0|54.0|34.0||4| z4 |58.0|80.0|90.0||5| z5 |78.0|88.0|98.0|+------+------+---------+------+---------+5rowsinset(0.00 sec)
mysql>select*from exam limit5offset5;+------+------+---------+-------+---------+| id | name | chinese | math | english |+------+------+---------+-------+---------+|6| z6 |99.0|100.0|100.0||7| z7 |13.0|46.0|70.0||8| z8 |87.0|67.0|90.0||9| z9 |45.0|23.0|54.0||10| q1 |45.0|76.0|54.0|+------+------+---------+-------+---------+5rowsinset(0.00 sec)
mysql>select*from exam where name like'g%';+------+-------+---------+------+---------+| id | name | chinese | math | english |+------+-------+---------+------+---------+|17| g1 |32.0|45.0|76.0||18| g2434 |21.0|24.0|45.0||19| g3 |34.0|32.0|99.0|+------+-------+---------+------+---------+3rowsinset(0.00 sec)
mysql>select*from exam where name like'g_';+------+------+---------+------+---------+| id | name | chinese | math | english |+------+------+---------+------+---------+|17| g1 |32.0|45.0|76.0||19| g3 |34.0|32.0|99.0|+------+------+---------+------+---------+2rowsinset(0.00 sec)
mysql>select*from exam where math between40and70;+------+------+---------+------+---------+| id | name | chinese | math | english |+------+------+---------+------+---------+|2| z2 |49.0|54.0|65.0||3| z3 |87.0|54.0|34.0||7| z7 |13.0|46.0|70.0||8| z8 |87.0|67.0|90.0||14| r2 |43.0|65.0|75.0||16| v2 |76.0|45.0|87.0||17| g1 |32.0|45.0|76.0|+------+------+---------+------+---------+7rowsinset(0.00 sec)
mysql>select name,id from exam where math between40and70;+------+------+| name | id |+------+------+| z2 |2|| z3 |3|| z7 |7|| z8 |8|| r2 |14|| v2 |16|| g1 |17|+------+------+7rowsinset(0.00 sec)
mysql>select name,chinese+english+math as total from exam where math between40and70;+------+-------+| name | total |+------+-------+| z2 |168.0|| z3 |175.0|| z7 |129.0|| z8 |244.0|| r2 |183.0|| v2 |208.0|| g1 |153.0|+------+-------+7rowsinset(0.00 sec)
mysql>select name,chinese+english+math as total from exam where math isnull;+------+-------+| name | total |+------+-------+| t54 |NULL|| gdwe |NULL|+------+-------+2rowsinset(0.00 sec)
mysql>select*from exam where math isnull;+------+------+---------+------+---------+| id | name | chinese | math | english |+------+------+---------+------+---------+|21| t54 |NULL|NULL|NULL||22| gdwe |32.0|NULL|NULL|+------+------+---------+------+---------+2rowsinset(0.00 sec)
mysql>select*from exam where math isnotnull;+------+-------+---------+-------+---------+| id | name | chinese | math | english |+------+-------+---------+-------+---------+|1| z1 |23.0|23.0|23.0||2| z2 |49.0|54.0|65.0||3| z3 |87.0|54.0|34.0||4| z4 |58.0|80.0|90.0||5| z5 |78.0|88.0|98.0||6| z6 |99.0|100.0|100.0||7| z7 |13.0|46.0|70.0||8| z8 |87.0|67.0|90.0||9| z9 |45.0|23.0|54.0||10| q1 |45.0|76.0|54.0||11| q2 |78.0|87.0|34.0||12| q3 |99.0|88.0|77.0||13| r1 |76.0|32.0|54.0||14| r2 |43.0|65.0|75.0||15| v1 |34.0|34.0|43.0||16| v2 |76.0|45.0|87.0||17| g1 |32.0|45.0|76.0||18| g2434 |21.0|24.0|45.0||19| g3 |34.0|32.0|99.0||20| y1 |56.0|76.0|76.0|+------+-------+---------+-------+---------+20rowsinset(0.00 sec)
mysql>select*from exam where math isnotnulland math <=50;+------+-------+---------+------+---------+| id | name | chinese | math | english |+------+-------+---------+------+---------+|1| z1 |23.0|23.0|23.0||7| z7 |13.0|46.0|70.0||9| z9 |45.0|23.0|54.0||13| r1 |76.0|32.0|54.0||15| v1 |34.0|34.0|43.0||16| v2 |76.0|45.0|87.0||17| g1 |32.0|45.0|76.0||18| g2434 |21.0|24.0|45.0||19| g3 |34.0|32.0|99.0|+------+-------+---------+------+---------+9rowsinset(0.00 sec)
mysql>select*from exam where math <=>null;+------+------+---------+------+---------+| id | name | chinese | math | english |+------+------+---------+------+---------+|21| t54 |NULL|NULL|NULL||22| gdwe |32.0|NULL|NULL|+------+------+---------+------+---------+2rowsinset(0.00 sec)
mysql>select*from exam where math =null;
Empty set(0.00 sec)
mysql>select*from exam where math<=50and math not45;
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 touse near '45' at line 1
mysql>select*from exam where math<=50and math isnot45;
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 touse near '45' at line 1
mysql>select*from exam where math<=50and math !=45;+------+-------+---------+------+---------+| id | name | chinese | math | english |+------+-------+---------+------+---------+|1| z1 |23.0|23.0|23.0||7| z7 |13.0|46.0|70.0||9| z9 |45.0|23.0|54.0||13| r1 |76.0|32.0|54.0||15| v1 |34.0|34.0|43.0||18| g2434 |21.0|24.0|45.0||19| g3 |34.0|32.0|99.0|+------+-------+---------+------+---------+7rowsinset(0.00 sec)
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || z1 |+--------------------+5 rows in set (0.00 sec
1023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
