MYSQL5.8----M2
2024-09-03 16:08:43
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| m1 |
| mon |
| mysql |
| performance_schema |
| quan； |
| quanbbs |
| quantest |
| quantesttwo |
| sys |
+--------------------+
10 rows in set (0.02 sec) mysql> use mon
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_mon |
+---------------+
| ALT |
| fa |
| father |
| nu |
| numm |
| nummm |
| qqq |
| son |
| ss |
| tp3 |
| tp4 |
| tp5 |
| tp6 |
| user |
+---------------+
14 rows in set (0.00 sec) mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
mysql> SELECT username,id,sex FROM user;
+----------+----+------+
| username | id | sex |
+----------+----+------+
| 小强 | 1 | 0 |
| 小xx | 2 | 0 |
| 小jj | 3 | 0 |
| 小米 | 4 | 0 |
| 小xiao | 6 | 0 |
| root | 7 | 0 |
+----------+----+------+
6 rows in set (0.00 sec)
mysql> SELECT user.id,user.username as un FROM user;
+----+---------+
| id | un |
+----+---------+
| 1 | 小强 |
| 2 | 小xx |
| 3 | 小jj |
| 4 | 小米 |
| 6 | 小xiao |
| 7 | root |
+----+---------+
6 rows in set (0.00 sec)
mysql> mysql> SELECT u.id,u.username AS un FROM user AS u;
+----+---------+
| id | un |
+----+---------+
| 1 | 小强 |
| 2 | 小xx |
| 3 | 小jj |
| 4 | 小米 |
| 6 | 小xiao |
| 7 | root |
+----+---------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM user WHERE sex = 0;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec) mysql> SELECT * FROM user WHERE age = 25;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+-----------
| id | username | pwd | sex | age | edu | classname | province
+----+----------+----------------------------------+------+------+-----+-----------+-----------
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津
+----+----------+----------------------------------+------+------+-----+-----------+-----------
6 rows in set (0.00 sec) mysql> SELECT age FROM user GROUP BY age;
+------+
| age |
+------+
| 25 |
| 35 |
| 83 |
| 116 |
+------+
4 rows in set (0.00 sec)
mysql> mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec) mysql> SELECT age FROM user GROUP BY age;
+------+
| age |
+------+
| 25 |
| 35 |
| 83 |
| 116 |
+------+
4 rows in set (0.00 sec) mysql> SELECT age FROM user GROUP BY age HAVING count(id) > 2;
+------+
| age |
+------+
| 25 |
+------+
1 row in set (0.13 sec)
mysql> SELECT * FROM user HAVING id % 2 = 0 ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
3 rows in set (0.00 sec) mysql> SELECT * FROM user WHERE id % 2 = 0 ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec) mysql> SELECT * FROM user ORDER BY id DESC;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec) mysql> SELECT * FROM user ORDER BY age ASC;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM user ORDER BY age ASC,id DESC;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM user ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
6 rows in set (0.00 sec) mysql> SELECT * FROM user LIMIT 2 ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+
2 rows in set (0.00 sec) mysql> SELECT * FROM user LIMIT 3,4 ;
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
3 rows in set (0.00 sec) mysql> SELECT * FROM user LIMIT 3,3 ;
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
3 rows in set (0.00 sec) mysql> SELECT * FROM user LIMIT 3,2 ;
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
2 rows in set (0.00 sec) mysql> SELECT * FROM user LIMIT 2,2 ;
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
2 rows in set (0.00 sec) mysql> SELECT * FROM user ORDER BY age DESC LIMIT 2 ;
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| id | username | pwd | sex | age | edu | classname | province |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 |
+----+----------+----------------------------------+------+------+-----+-----------+----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM user ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+-------+
| id | username | pwd | sex | age | edu | classname | province | price |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+-------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | 0.00 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 | 0.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | 0.00 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 | 0.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 | 0.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | 0.00 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+-------+
6 rows in set (0.00 sec) mysql> UPDATE user SET price = age*edu WHERE id % 2 =0;
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM user ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| id | username | pwd | sex | age | edu | classname | province | price |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | 0.00 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 | 70.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | 0.00 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 | 249.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 | 348.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | 0.00 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
6 rows in set (0.00 sec) mysql> UPDATE user SET price = age*edu*1.5 WHERE id % 2 =1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM user ;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| id | username | pwd | sex | age | edu | classname | province | price |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | 75.00 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 | 70.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | 75.00 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 | 249.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 | 348.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | 75.00 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
6 rows in set (0.00 sec) mysql> SELECT AVG(price) FROM user;
+------------+
| AVG(price) |
+------------+
| 148.666667 |
+------------+
1 row in set (0.36 sec) mysql> SELECT ROUND(AVG(price)) FROM user;
+-------------------+
| ROUND(AVG(price)) |
+-------------------+
| 149 |
+-------------------+
1 row in set (0.01 sec) mysql> SELECT ROUND(AVG(price),2) FROM user;
+---------------------+
| ROUND(AVG(price),2) |
+---------------------+
| 148.67 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT id,username,price FROM user WHERE price > 148.67;
+----+----------+--------+
| id | username | price |
+----+----------+--------+
| 4 | 小米 | 249.00 |
| 6 | 小xiao | 348.00 |
+----+----------+--------+
2 rows in set (0.09 sec) mysql> SELECT id,username,price FROM user WHERE price < 148.67;
+----+----------+-------+
| id | username | price |
+----+----------+-------+
| 1 | 小强 | 75.00 |
| 2 | 小xx | 70.00 |
| 3 | 小jj | 75.00 |
| 7 | root | 75.00 |
+----+----------+-------+
4 rows in set (0.00 sec) mysql> SELECT id,username,price FROM user WHERE price >(SELECT ROUND(AVG(price),2) FROM user);
+----+----------+--------+
| id | username | price |
+----+----------+--------+
| 4 | 小米 | 249.00 |
| 6 | 小xiao | 348.00 |
+----+----------+--------+
2 romysql> SELECT price FROM user WHERE classname = 'jave';
+-------+
| price |
+-------+
| 75.00 |
| 70.00 |
+-------+
2 rows in set (0.00 sec)
ws in set (0.00 sec)
mysql> SELECT username,price FROM user WHERE classname = 'jave';
+----------+-------+
| username | price |
+----------+-------+
| 小强 | 75.00 |
| 小xx | 70.00 |
+----------+-------+
2 rows in set (0.00 sec)
mysql> SELECT username,price FROM user WHERE price > ANY (SELECT price FROM user WHERE classname = 'jave');
+----------+--------+
| username | price |
+----------+--------+
| 小强 | 75.00 |
| 小jj | 75.00 |
| 小米 | 249.00 |
| 小xiao | 348.00 |
| root | 75.00 |
+----------+--------+
5 rows in set (0.09 sec) mysql> SELECT username,classname,price FROM user WHERE price > ANY (SELECT price FROM user WHERE classname = 'jave');
+----------+-----------+--------+
| username | classname | price |
+----------+-----------+--------+
| 小强 | jave | 75.00 |
| 小jj | javed | 75.00 |
| 小米 | py | 249.00 |
| 小xiao | py | 348.00 |
| root | dada | 75.00 |
+----------+-----------+--------+
5 rows in set (0.00 sec) mysql> SELECT username,classname,price FROM user WHERE price >= ANY (SELECT price FROM user WHERE classname = 'jave');
+----------+-----------+--------+
| username | classname | price |
+----------+-----------+--------+
| 小强 | jave | 75.00 |
| 小xx | jave | 70.00 |
| 小jj | javed | 75.00 |
| 小米 | py | 249.00 |
| 小xiao | py | 348.00 |
| root | dada | 75.00 |
+----------+-----------+--------+
6 rows in set (0.00 sec)
mysql> CREATE TABLE provices(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (0.14 sec)
mysql> SELECT * FROM provices;
Empty set (0.00 sec) mysql> SELECT province FROM user;
+-----------+
| province |
+-----------+
| 内蒙古 |
| 内蒙古 |
| 湛江 |
| 背景 |
| 福门 |
| 天津 |
+-----------+
6 rows in set (0.00 sec) mysql> SELECT province FROM user GROUP BY province;
+-----------+
| province |
+-----------+
| 内蒙古 |
| 天津 |
| 湛江 |
| 福门 |
| 背景 |
+-----------+
5 rows in set (0.00 sec)
mysql> INSERT INTO provices(name) SELECT province FROM user GROUP BY province;
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM provices;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 内蒙古 |
| 2 | 天津 |
| 3 | 湛江 |
| 4 | 福门 |
| 5 | 背景 |
+----+-----------+
5 rows in set (0.00 sec) mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+----
| id | username | pwd | sex | age | edu | classname | pro
+----+----------+----------------------------------+------+------+-----+-----------+----
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津
+----+----------+----------------------------------+------+------+-----+-----------+----
6 rows in set (0.00 sec)
mysql> UPDATE user INNER JOIN provices ON user.province = provices.name SET user.provinc
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 0 mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+----
| id | username | pwd | sex | age | edu | classname | pro
+----+----------+----------------------------------+------+------+-----+-----------+----
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 1
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 1
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 3
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 5
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 4
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 2
+----+----------+----------------------------------+------+------+-----+-----------+----
6 rows in set (0.00 sec)
mysql> CREATE TABLE class(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> classname VARCHAR(20) NOT NULL)
-> SELECT classname FROM user GROUP BY classname;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM class
-> ;
+----+-----------+
| id | classname |
+----+-----------+
| 1 | dada |
| 2 | jave |
| 3 | javed |
| 4 | py |
+----+-----------+
4 rows in set (0.00 sec)
mysql> UPDATE user INNER JOIN class ON user.classname = class.classname SET user.classna
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0 mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----------+----
| id | username | pwd | sex | age | edu | classname | pro
+----+----------+----------------------------------+------+------+-----+-----------+----
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | 2 | 1
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | 2 | 1
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | 3 | 3
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | 4 | 5
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | 4 | 4
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | 1 | 2
+----+----------+----------------------------------+------+------+-----+-----------+----
6 rows in set (0.00 sec)
mysql> DESC user;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| pwd | char(32) | NO | | NULL | |
| sex | tinyint(4) | YES | | 0 | |
| age | tinyint(4) | YES | | 18 | |
| edu | tinyint(4) | NO | | 2 | |
| classname | varchar(10) | NO | | NULL | |
| province | varchar(10) | NO | | NULL | |
| price | decimal(8,2) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
9 rows in set (0.15 sec)
mysql> ALTER TABLE user CHANGE classname cid TINYINT UNSIGNED NOT NULL, CHANGE province
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----+-------+--
| id | username | pwd | sex | age | edu | cid | proid | p
+----+----------+----------------------------------+------+------+-----+-----+-------+--
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | 2 | 1 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | 2 | 1 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | 3 | 3 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | 4 | 5 | 2
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | 4 | 4 | 3
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | 1 | 2 |
+----+----------+----------------------------------+------+------+-----+-----+-------+--
6 rows in set (0.00 sec)
mysql> SELECT user.id,user.username,class.classname AS cname FROM user INNER JOIN class ON user.cid = class.id;
+----+----------+-------+
| id | username | cname |
+----+----------+-------+
| 1 | 小强 | jave |
| 2 | 小xx | jave |
| 3 | 小jj | javed |
| 4 | 小米 | py |
| 6 | 小xiao | py |
| 7 | root | dada |
+----+----------+-------+
6 rows in set (0.03 sec) mysql> SELECT u.id,u.username,c.classname FROM user AS u LEFT JOIN class AS c ON u.cid = c.id;^C
mysql> INSERT INTO user (username,pwd,age,edu,cid,proid,price) VALUES('QQ',md5(1234),66,3,7,4,888.99);
Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM user;
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| id | username | pwd | sex | age | edu | cid | proid | price |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | 2 | 1 | 75.00 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | 2 | 1 | 70.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | 3 | 3 | 75.00 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | 4 | 5 | 249.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | 4 | 4 | 348.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | 1 | 2 | 75.00 |
| 8 | QQ | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 66 | 3 | 7 | 4 | 888.99 |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
7 rows in set (0.00 sec) mysql> SELECT * FROM class;
+----+-----------+
| id | classname |
+----+-----------+
| 1 | dada |
| 2 | jave |
| 3 | javed |
| 4 | py |
+----+-----------+
4 rows in set (0.00 sec) mysql> SELECT u.id,u.username,c.classname FROM user AS u LEFT JOIN class AS c ON u.cid = c.id;
+----+----------+-----------+
| id | username | classname |
+----+----------+-----------+
| 7 | root | dada |
| 1 | 小强 | jave |
| 2 | 小xx | jave |
| 3 | 小jj | javed |
| 4 | 小米 | py |
| 6 | 小xiao | py |
| 8 | QQ | NULL |
+----+----------+-----------+
7 rows in set (0.00 sec) mysql> SELECT u.id,u.username,c.classname FROM user AS u RIGHT JOIN class AS c ON u.cid = c.id;
+------+----------+-----------+
| id | username | classname |
+------+----------+-----------+
| 1 | 小强 | jave |
| 2 | 小xx | jave |
| 3 | 小jj | javed |
| 4 | 小米 | py |
| 6 | 小xiao | py |
| 7 | root | dada |
+------+----------+-----------+
6 rows in set (0.00 sec)
多表连接：
mysql> SELECT u.id,u.username,u.pwd,u.sex,u.age,u.edu,c.classname,p.name,u.price FROM user AS u INNER JOIN class AS c ON c.id =u.cid INNER JOIN provices AS p ON p.id = u.proid;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| id | username | pwd | sex | age | edu | classname | name | price |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | 75.00 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 | 70.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | 75.00 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 | 249.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 | 348.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | 75.00 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
6 rows in set (0.00 sec) mysql> SELECT u.id,u.username,u.pwd,u.sex,u.age,u.edu,c.classname,p.name,u.price FROM user AS u ,class AS c,provices AS p WHERE u.cid = c.id AND u.proid = p.id;
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| id | username | pwd | sex | age | edu | classname | name | price |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | jave | 内蒙古 | 75.00 |
| 2 | 小xx | 202cb962ac59075b964b07152d234b70 | 0 | 35 | 2 | jave | 内蒙古 | 70.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | javed | 湛江 | 75.00 |
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | py | 背景 | 249.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | py | 福门 | 348.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | dada | 天津 | 75.00 |
+----+----------+----------------------------------+------+------+-----+-----------+-----------+--------+
6 rows in set (0.00 sec)
子表：
mysql> CREATE TABLE class_type(
-> id INT AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> pid INT UNSIGNED NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.49 sec)
mysql> DESC class_type;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| pid | int(10) unsigned | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> INSERT INTO class_type values(null,"java开发",2);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO class_type values(null,"h5开发",2);
Query OK, 1 row affected (0.36 sec) mysql> INSERT INTO class_type values(null,"linux运维",2);
Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM class_type;
+----+-------------+-----+
| id | name | pid |
+----+-------------+-----+
| 1 | java开发 | 2 |
| 2 | h5开发 | 2 |
| 3 | linux运维 | 2 |
+----+-------------+-----+
3 rows in set (0.00 sec) mysql> INSERT INTO class_type VALUES(null,"JAVASE",1),(null,"thinkphp",2),(
-> "laravel",2),
-> (null,"oststorp",2),
-> (null,"myslq",4),
-> (null,"redis",4),
-> (null,"HTML",3),
-> (null,"css",3),
-> (null,"javascript",3);
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM class_type;
+----+-------------+-----+
| id | name | pid |
+----+-------------+-----+
| 1 | java开发 | 2 |
| 2 | h5开发 | 2 |
| 3 | linux运维 | 2 |
| 4 | JAVASE | 1 |
| 5 | thinkphp | 2 |
| 6 | laravel | 2 |
| 7 | oststorp | 2 |
| 8 | myslq | 4 |
| 9 | redis | 4 |
| 10 | HTML | 3 |
| 11 | css | 3 |
| 12 | javascript | 3 |
+----+-------------+-----+
12 rows in set (0.00 sec)
mysql> update class_type set pid = 0 WHERE id = 1 OR id = 2 OR id = 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM class_type;
+----+-------------+-----+
| id | name | pid |
+----+-------------+-----+
| 1 | java开发 | 0 |
| 2 | h5开发 | 0 |
| 3 | linux运维 | 0 |
| 4 | JAVASE | 1 |
| 5 | thinkphp | 2 |
| 6 | laravel | 2 |
| 7 | oststorp | 2 |
| 8 | myslq | 4 |
| 9 | redis | 4 |
| 10 | HTML | 3 |
| 11 | css | 3 |
| 12 | javascript | 3 |
+----+-------------+-----+
mysql> SELECT s.id,s.name,p.name FROM class_type AS s LEFT JOIN class_type AS p ON s.id = p.id;
+----+-------------+-------------+
| id | name | name |
+----+-------------+-------------+
| 1 | java开发 | java开发 |
| 2 | h5开发 | h5开发 |
| 3 | linux运维 | linux运维 |
| 4 | JAVASE | JAVASE |
| 5 | thinkphp | thinkphp |
| 6 | laravel | laravel |
| 7 | oststorp | oststorp |
| 8 | myslq | myslq |
| 9 | redis | redis |
| 10 | HTML | HTML |
| 11 | css | css |
| 12 | javascript | javascript |
+----+-------------+-------------+
12 rows in set (0.00 sec)
mysql> SELECT p.id,p.name,s.name FROM class_type AS p LEFT JOIN class_type AS s ON p.id = s.pid;
+----+-------------+------------+
| id | name | name |
+----+-------------+------------+
| 1 | java开发 | JAVASE |
| 2 | h5开发 | thinkphp |
| 2 | h5开发 | laravel |
| 2 | h5开发 | oststorp |
| 4 | JAVASE | myslq |
| 4 | JAVASE | redis |
| 3 | linux运维 | HTML |
| 3 | linux运维 | css |
| 3 | linux运维 | javascript |
| 5 | thinkphp | NULL |
| 6 | laravel | NULL |
| 7 | oststorp | NULL |
| 8 | myslq | NULL |
| 9 | redis | NULL |
| 10 | HTML | NULL |
| 11 | css | NULL |
| 12 | javascript | NULL |
+----+-------------+------------+
17 rows in set (0.00 sec)
最新文章
- .NET 4.6.2正式发布带来众多特性
- WinForm程序执行JS代码的多种方法以及使用WebBrowser与JS交互
- java 类加载顺序
- BZOJ_3196_二逼平衡树_(树套树,线段树+Treap)
- Cmake的install与file命令的区别
- 《think in python》学习-6
- 用cocos2d-x 3.2 实现的FlappyBird
- C语言高效位操作
- MongoDB备份恢复与导出导入
- java虚拟机和java内存区域概述
- tp5时间戳转日期的方法
- c语言求最大公约数
- 快速掌握Nginx(一) —— 安装Nginx和简单配置虚拟主机
- 出现Failed to get convolution algorithm的解决方法
- 问题1：Oracle数据库监听启动失败（重启监听，提示The listener supports no services）
- [转]Examining Open vSwitch Traffic Patterns
- python全栈开发day58-mysql存储过程，权限，索引，慢日志，执行计划，分页优化处理
- listagg乱码问题
- 升级 php composer 版本
- java利用poi包 为excel生成超链接