333333333333333333333333333

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 | |
| cid | tinyint(3) unsigned | NO | | NULL | |
| proid | tinyint(3) unsigned | NO | | NULL | |
| price | decimal(8,2) | NO | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> INSERT INTO user(username,pwd,sex,cid,proid,price)VALUES("QQ",md5(123),0,2,1,80);
Query OK, 1 row affected (0.37 sec)
mysql> INSERT INTO user(username,pwd,sex,cid,proid,price)VALUES("root",md5(123),0,2,1,80);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT id,username FROM user GROUP BY username HAVING count(username)>1;
+----+----------+
| id | username |
+----+----------+
| 8 | QQ |
| 7 | root |
+----+----------+
2 rows in set (0.00 sec)
mysql> mysql> DELETE t1 FROM user AS t1 LEFT JOIN (SELECT id,username FROM user GROUP BY username HAVI
Query OK, 2 rows affected (0.38 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 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 id,age FROM user;
+----+------+
| id | age |
+----+------+
| 1 | 25 |
| 2 | 35 |
| 3 | 25 |
| 4 | 83 |
| 6 | 116 |
| 7 | 25 |
| 8 | 66 |
+----+------+
7 rows in set (0.00 sec)

mysql> SELECT CONCAT(id,age) FROM user;
+----------------+
| CONCAT(id,age) |
+----------------+
| 125 |
| 235 |
| 325 |
| 483 |
| 6116 |
| 725 |
| 866 |
+----------------+
7 rows in set (0.10 sec)

mysql> SELECT CONCAT(id,"--",age) AS IDAGE FROM user;
+--------+
| IDAGE |
+--------+
| 1--25 |
| 2--35 |
| 3--25 |
| 4--83 |
| 6--116 |
| 7--25 |
| 8--66 |
+--------+
7 rows in set (0.00 sec)

mysql> SELECT CONCAT("id","/","name") ;
+-------------------------+
| CONCAT("id","/","name") |
+-------------------------+
| id/name |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT("/","id","quan","name") ;
+--------------------------------+
| CONCAT("/","id","quan","name") |
+--------------------------------+
| /idquanname |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS("/","id","quan","name") ;
+-----------------------------------+
| CONCAT_WS("/","id","quan","name") |
+-----------------------------------+
| id/quan/name |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 123213.456;
+------------+
| 123213.456 |
+------------+
| 123213.456 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT( 123213.456,2);
+-----------------------+
| FORMAT( 123213.456,2) |
+-----------------------+
| 123,213.46 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT LOWER("qunQUAN");
+------------------+
| LOWER("qunQUAN") |
+------------------+
| qunquan |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER("qunQUAN");
+------------------+
| UPPER("qunQUAN") |
+------------------+
| QUNQUAN |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT LEFT("qun/QUAN",2);
+--------------------+
| LEFT("qun/QUAN",2) |
+--------------------+
| qu |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LEFT("qun/QUAN",5);
+--------------------+
| LEFT("qun/QUAN",5) |
+--------------------+
| qun/Q |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER( LEFT("qun/QUAN",5));
+----------------------------+
| UPPER( LEFT("qun/QUAN",5)) |
+----------------------------+
| QUN/Q |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH("QUAN ZHI");
+--------------------+
| LENGTH("QUAN ZHI") |
+--------------------+
| 8 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(" QUAN ZHI");
+----------------------+
| LENGTH(" QUAN ZHI") |
+----------------------+
| 10 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(LTRIM(" QUAN ZHI"));
+-----------------------------+
| LENGTH(LTRIM(" QUAN ZHI")) |
+-----------------------------+
| 8 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH("???QUAN??");
+---------------------+
| LENGTH("???QUAN??") |
+---------------------+
| 9 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(TRIM("???QUAN??"));
+---------------------------+
| LENGTH(TRIM("???QUAN??")) |
+---------------------------+
| 9 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(TRIM(LEADING "?" FROM "???QUAN??"));
+--------------------------------------------+
| LENGTH(TRIM(LEADING "?" FROM "???QUAN??")) |
+--------------------------------------------+
| 6 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(TRIM(TRAILING "?" FROM "???QUAN??"));
+---------------------------------------------+
| LENGTH(TRIM(TRAILING "?" FROM "???QUAN??")) |
+---------------------------------------------+
| 7 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(TRIM(BOTH "?" FROM "???QUAN??"));
+-----------------------------------------+
| LENGTH(TRIM(BOTH "?" FROM "???QUAN??")) |
+-----------------------------------------+
| 4 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH "?" FROM "???QU??AN??");
+-----------------------------------+
| TRIM(BOTH "?" FROM "???QU??AN??") |
+-----------------------------------+
| QU??AN |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REPLACE(TRIM(BOTH "?" FROM "???QU??AN??"),"?"," ")
-> ;
+----------------------------------------------------+
| REPLACE(TRIM(BOTH "?" FROM "???QU??AN??"),"?"," ") |
+----------------------------------------------------+
| QU AN |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REPLACE(TRIM(BOTH "?" FROM "???QU??AN??"),"?","");
+---------------------------------------------------+
| REPLACE(TRIM(BOTH "?" FROM "???QU??AN??"),"?","") |
+---------------------------------------------------+
| QUAN |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING("QUANZHIQINAG",2);
+-----------------------------+
| SUBSTRING("QUANZHIQINAG",2) |
+-----------------------------+
| UANZHIQINAG |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING("QUANZHIQINAG",-1);
+------------------------------+
| SUBSTRING("QUANZHIQINAG",-1) |
+------------------------------+
| G |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING("QUANZHIQINAG",6);
+-----------------------------+
| SUBSTRING("QUANZHIQINAG",6) |
+-----------------------------+
| HIQINAG |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT "QUANZHI" LIKE "Q";
+--------------------+
| "QUANZHI" LIKE "Q" |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.09 sec)

mysql> SELECT "QUANZHI" LIKE "Q%";
+---------------------+
| "QUANZHI" LIKE "Q%" |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT "QUANZHI" LIKE "z%";
+---------------------+
| "QUANZHI" LIKE "z%" |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT "QUANZHI" LIKE "Z%";
+---------------------+
| "QUANZHI" LIKE "Z%" |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT "QUANZHI" LIKE "%Z%";
+----------------------+
| "QUANZHI" LIKE "%Z%" |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT "QUANZHI" LIKE "_U%";
+----------------------+
| "QUANZHI" LIKE "_U%" |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM user WHERE username LIKE "%小%";
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| 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 |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
5 rows in set (0.00 sec)

mysql> SELECT CEIL(3.001);
+-------------+
| CEIL(3.001) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT CEIL(3.00);
+------------+
| CEIL(3.00) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT 1 + 2
-> ;
+-------+
| 1 + 2 |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT 10 DIV 3;
+----------+
| 10 DIV 3 |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(3.001);
+--------------+
| FLOOR(3.001) |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT 10 MOD 3;
+----------+
| 10 MOD 3 |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT FOWER(2**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 '*2)' at line 1
mysql> SELECT POWER(2**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 '*2)' at line 1
mysql> SELECT POWER(2,2);
+------------+
| POWER(2,2) |
+------------+
| 4 |
+------------+
1 row in set (0.15 sec)

mysql> SELECT ROUND(2.25);
+-------------+
| ROUND(2.25) |
+-------------+
| 2 |
+-------------+
1 row in set (0.35 sec)

mysql> SELECT ROUND(2.55);
+-------------+
| ROUND(2.55) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(2.55,1);
+---------------+
| ROUND(2.55,1) |
+---------------+
| 2.6 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(1.121445,2)
-> ;
+----------------------+
| TRUNCATE(1.121445,2) |
+----------------------+
| 1.12 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(1.121445,5);
+----------------------+
| TRUNCATE(1.121445,5) |
+----------------------+
| 1.12144 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(1.121445,-5);
+-----------------------+
| TRUNCATE(1.121445,-5) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(1.121445,-1);
+-----------------------+
| TRUNCATE(1.121445,-1) |
+-----------------------+
| 0 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(2221.121445,-1);
+--------------------------+
| TRUNCATE(2221.121445,-1) |
+--------------------------+
| 2220 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT 123 BETWEEN 111 AND 133;
+-------------------------+
| 123 BETWEEN 111 AND 133 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT 111 BETWEEN 111 AND 133;
+-------------------------+
| 111 BETWEEN 111 AND 133 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT 133 BETWEEN 111 AND 133;
+-------------------------+
| 133 BETWEEN 111 AND 133 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT 134 BETWEEN 111 AND 133;
+-------------------------+
| 134 BETWEEN 111 AND 133 |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 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 user WHERE price BETWEEN 200 AND 400;
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| id | username | pwd | sex | age | edu | cid | proid | price |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| 4 | 小米 | 5eac43aceba42c8757b54003a58277b5 | 0 | 83 | 3 | 4 | 5 | 249.00 |
| 6 | 小xiao | 09a6f4ead95fb05ee29ab9e7d1219e33 | 0 | 116 | 3 | 4 | 4 | 348.00 |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM user WHERE price NOT BETWEEN 200 AND 400;
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| 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 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | 1 | 2 | 75.00 |
| 8 | QQ | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 66 | 3 | 7 | 4 | 888.99 |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
5 rows in set (0.00 sec)

mysql> SELECT 1 IN 1,23;
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 '1,23' at line 1
mysql> SELECT 1 IN(1,23);
+------------+
| 1 IN(1,23) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT 2 IN(1,23);
+------------+
| 2 IN(1,23) |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM user WHERE id IN(1,3,5);
+----+----------+----------------------------------+------+------+-----+-----+-------+-------+
| id | username | pwd | sex | age | edu | cid | proid | price |
+----+----------+----------------------------------+------+------+-----+-----+-------+-------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | 2 | 1 | 75.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | 3 | 3 | 75.00 |
+----+----------+----------------------------------+------+------+-----+-----+-------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM user WHERE id IN(1,3,7);
+----+----------+----------------------------------+------+------+-----+-----+-------+-------+
| id | username | pwd | sex | age | edu | cid | proid | price |
+----+----------+----------------------------------+------+------+-----+-----+-------+-------+
| 1 | 小强 | 202cb962ac59075b964b07152d234b70 | 0 | 25 | 2 | 2 | 1 | 75.00 |
| 3 | 小jj | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 25 | 2 | 3 | 3 | 75.00 |
| 7 | root | f356355c1634839cf42769e7f30905a3 | 0 | 25 | 2 | 1 | 2 | 75.00 |
+----+----------+----------------------------------+------+------+-----+-----+-------+-------+
3 rows in set (0.00 sec)

mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT "NULL" IS NULL;
+----------------+
| "NULL" IS NULL |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD("2019-03-03", INTERVAL 365 DAY);
+------------------------------------------+
| DATE_ADD("2019-03-03", INTERVAL 365 DAY) |
+------------------------------------------+
| 2020-03-02 |
+------------------------------------------+
1 row in set (0.09 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 365 DAY);
+-----------------------------------+
| DATE_ADD(NOW(), INTERVAL 365 DAY) |
+-----------------------------------+
| 2021-03-31 13:53:23 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL -365 DAY);
+------------------------------------+
| DATE_ADD(NOW(), INTERVAL -365 DAY) |
+------------------------------------+
| 2019-04-01 13:54:18 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
+----------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 YEAR) |
+----------------------------------+
| 2021-03-31 13:54:51 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(), INTERVAL 2 YEAR);
+----------------------------------+
| DATE_ADD(NOW(), INTERVAL 2 YEAR) |
+----------------------------------+
| 2022-03-31 13:54:55 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF("2019-09-09","2020-09-09");
+-------------------------------------+
| DATEDIFF("2019-09-09","2020-09-09") |
+-------------------------------------+
| -366 |
+-------------------------------------+

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2020-03-31 13:49:28 |
+---------------------+
1 row in set (0.09 sec)

mysql> SELECT CURDASTE();
ERROR 1305 (42000): FUNCTION mon.CURDASTE does not exist
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2020-03-31 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 13:49:55 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2020-03-31 13:49:28 |
+---------------------+
1 row in set (0.09 sec)

mysql> SELECT CURDASTE();
ERROR 1305 (42000): FUNCTION mon.CURDASTE does not exist
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2020-03-31 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 13:49:55 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mon |
+------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)

mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 10 |
+------------------+
1 row in set (0.00 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 AVG(price) FROM user;
+------------+
| AVG(price) |
+------------+
| 254.427143 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(id) FROM user;
+-----------+
| COUNT(id) |
+-----------+
| 7 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT username ,MAX(price) FROM user;
+----------+------------+
| username | MAX(price) |
+----------+------------+
| 小强 | 888.99 |
+----------+------------+
1 row in set (0.00 sec)
#上面是错误的数据：
mysql> SELECT * FROM user WHERE price = (SELECT MAX(price) FROM user);
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| id | username | pwd | sex | age | edu | cid | proid | price |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
| 8 | QQ | 81dc9bdb52d04dc20036dbd8313ed055 | 0 | 66 | 3 | 7 | 4 | 888.99 |
+----+----------+----------------------------------+------+------+-----+-----+-------+--------+
1 row in set (0.00 sec)

mysql> SELECT MD5(123456);
+----------------------------------+
| MD5(123456) |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%Y年-%m月-%d日=%H%M%s');
+-----------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年-%m月-%d日=%H%M%s') |
+-----------------------------------------------+
| 2020年-03月-31日=14March32 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%Y年-%m月-%d日=%H:%I:%s');
+-------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年-%m月-%d日=%H:%I:%s') |
+-------------------------------------------------+
| 2020年-03月-31日=14:02:04 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION mydate() RETURNS VARCHAR(30)
-> RETURN DATE_FORMAT(NOW(),'%Y年-%m月-%d日=%H:%i:%s');
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE FUNCTION mydate() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年-%m月-%d日=%H:%i:%s');
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT mydate();
+------------------------------+
| mydate() |
+------------------------------+
| 2020年-03月-31日=14:34:24 |
+------------------------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION mydate2(num1 INT,num2 INT)
-> RETURNS FLOAT(10.2)
-> RETURN (num1/num2)
-> ;
Query OK, 0 rows affected (0.37 sec)

mysql> SELECT mydata2(10,5);
ERROR 1305 (42000): FUNCTION mon.mydata2 does not exist
mysql> SELECT mydate2(10,5);
+---------------+
| mydate2(10,5) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT mydate2(10,3);
+--------------------+
| mydate2(10,3) |
+--------------------+
| 3.3333332538604736 |
+--------------------+
1 row 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> DELIMITER //
mysql> CREATE FUNCTION add_class(class VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT class(classname) VALUES(class);
-> RETURN LAST_INSERT_ID();
-> END
-> //
Query OK, 0 rows affected (0.37 sec)

mysql> DELIMITER ;
mysql> SELECT add_class("jaja");
+-------------------+
| add_class("jaja") |
+-------------------+
| 8 |
+-------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM class;
+----+-----------+
| id | classname |
+----+-----------+
| 1 | dada |
| 2 | jave |
| 3 | javed |
| 4 | py |
| 8 | jaja |
+----+-----------+
5 rows in set (0.00 sec)

mysql>

mysql> CREATE PROCEDURE spi() SELECT VERSION();
Query OK, 0 rows affected (0.01 sec)

mysql> CALL spi();
+------------+
| VERSION() |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL spi;
+------------+
| VERSION() |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+---------------+
| Tables_in_mon |
+---------------+
| ALT |
| class |
| class_type |
| fa |
| father |
| nu |
| numm |
| nummm |
| provices |
| qqq |
| son |
| ss |
| tp3 |
| tp4 |
| tp5 |
| tp6 |
| user |
+---------------+
17 rows in set (0.00 sec)

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 |
+----+-------------+-----+
12 rows in set (0.00 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE declass(IN iid INT UNSIGNED) BEGIN DELETE FROM class_type WHERE id =iid ;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL declass(12);
-> //
Query OK, 1 row affected (0.09 sec)

mysql> DELIMITER ;
mysql> CALL declass(11);
Query OK, 1 row affected (0.10 sec)

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 |
+----+-------------+-----+
10 rows in set (0.00 sec)

带有IN OUT 参数的存储过程创建:

mysql> DELIMITER //
mysql> CREATE PROCEDURE removepid(IN p_id INT UNSIGNED,OUT p_num INT UNSIGNED)
-> BEGIN
-> DELETE FROM class WHERE id = p_id;
-> SELECT COUNT(id) FROM class INTO p_num;
-> END
-> //
Query OK, 0 rows affected (0.03 sec)

mysql> CALL removepidd(8,@numb)
-> //
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @numb;
-> //
+-------+
| @numb |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
-> //
+----+-----------+
| id | classname |
+----+-----------+
| 1 | dada |
| 2 | jave |
| 4 | py |
+----+-----------+
3 rows in set (0.00 sec)

mysql>

这里的@numb是用来接受存储过程返回的值得，存储过程里面得值只能再存储过程里面进行使用

多个参数的建立

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> CREATE PROCEDURE rrrr(IN p_age TINYINT UNSIGNED,OUT duer TINYINT UNSIGNED,OUT euer TINYINT UNSIG
-> BEGIN
-> DELETE FROM user WHERE age = p_age;
-> SELECT ROW_COUNT() INTO duer;
-> SELECT COUNT(id) FROM user INTO euer;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL rrrr(25,@dddd,@eeee);
-> //
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @dddd//
+-------+
| @dddd |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
mysql> SELECT @eeee//
+-------+
| @eeee |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)