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