MYSQL5.8----M2
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)
