mysql> select concat('imooc','MySQL');
+-------------------------+
| concat('imooc','MySQL') |
+-------------------------+
| imoocMySQL |
+-------------------------+
1 row in set (0.00 sec)
mysql> select concat('imooc','-','MySQL');
+-----------------------------+
| concat('imooc','-','MySQL') |
+-----------------------------+
| imooc-MySQL |
+-----------------------------+
1 row in set (0.00 sec)
mysql> create table test(
-> first_name varchar(20),
-> last_name varchar(10)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test values('A','B'),
-> ('C','D'),
-> ('tom%',123),
-> (null,11);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A | B |
| C | D |
| tom% | 123 |
| NULL | 11 |
+------------+-----------+
4 rows in set (0.01 sec)
mysql> select concat(first_name,last_name) as full_name
-> from test;
+-----------+
| full_name |
+-----------+
| AB |
| CD |
| tom%123 |
| NULL |
+-----------+
4 rows in set (0.00 sec)
mysql> select concat_ws('|','A','B','C');
+----------------------------+
| concat_ws('|','A','B','C') |
+----------------------------+
| A|B|C |
+----------------------------+
1 row in set (0.00 sec)
mysql> select concat_ws('-','imooc','MySQL','Functions');
+--------------------------------------------+
| concat_ws('-','imooc','MySQL','Functions') |
+--------------------------------------------+
| imooc-MySQL-Functions |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select format(1256.74,2);
+-------------------+
| format(1256.74,2) |
+-------------------+
| 1,256.74 |
+-------------------+
1 row in set (0.00 sec)
mysql> select format(1256.74,1);
+-------------------+
| format(1256.74,1) |
+-------------------+
| 1,256.7 |
+-------------------+
1 row in set (0.00 sec)
mysql> select format(1256.74,0);
+-------------------+
| format(1256.74,0) |
+-------------------+
| 1,257 |
+-------------------+
1 row in set (0.00 sec)
mysql> select lower('MySQL');
+----------------+
| lower('MySQL') |
+----------------+
| mysql |
+----------------+
1 row in set (0.00 sec)
mysql> select upper('MySQL');
+----------------+
| upper('MySQL') |
+----------------+
| MYSQL |
+----------------+
1 row in set (0.00 sec)
mysql> select left('MySQL',2);
+-----------------+
| left('MySQL',2) |
+-----------------+
| My |
+-----------------+
1 row in set (0.00 sec)
mysql> select lower(left('MySQL',2));
+------------------------+
| lower(left('MySQL',2)) |
+------------------------+
| my |
+------------------------+
1 row in set (0.00 sec)
mysql> select right('MySQL',3);
+------------------+
| right('MySQL',3) |
+------------------+
| SQL |
+------------------+
1 row in set (0.00 sec)
mysql> select upper(right('MySQL',3));
+-------------------------+
| upper(right('MySQL',3)) |
+-------------------------+
| SQL |
+-------------------------+
1 row in set (0.00 sec)
mysql> select length(' MySQL ');
+-----------------------+
| length(' MySQL ') |
+-----------------------+
| 11 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select length(ltrim(' MySQL '));
+------------------------------+
| length(ltrim(' MySQL ')) |
+------------------------------+
| 9 |
+------------------------------+
1 row in set (0.00 sec)
mysql> select length(rtrim(' MySQL '));
+------------------------------+
| length(rtrim(' MySQL ')) |
+------------------------------+
| 7 |
+------------------------------+
1 row in set (0.00 sec)
mysql> select length(trim(' MySQL '));
+-----------------------------+
| length(trim(' MySQL ')) |
+-----------------------------+
| 5 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select trim(leading '?' from '??MySQL????');
+--------------------------------------+
| trim(leading '?' from '??MySQL????') |
+--------------------------------------+
| MySQL???? |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(trailing '?' from '??MySQL????');
+---------------------------------------+
| trim(trailing '?' from '??MySQL????') |
+---------------------------------------+
| ??MySQL |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(both '?' from '??MySQL????');
+-----------------------------------+
| trim(both '?' from '??MySQL????') |
+-----------------------------------+
| MySQL |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select trim(both '?' from '??My??SQL????');
+-------------------------------------+
| trim(both '?' from '??My??SQL????') |
+-------------------------------------+
| My??SQL |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select replace('??My??SQL????','?','');
+---------------------------------+
| replace('??My??SQL????','?','') |
+---------------------------------+
| MySQL |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select replace('??My??SQL????','?','!!');
+-----------------------------------+
| replace('??My??SQL????','?','!!') |
+-----------------------------------+
| !!!!My!!!!SQL!!!!!!!! |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select substring('MySQL',1,2);
+------------------------+
| substring('MySQL',1,2) |
+------------------------+
| My |
+------------------------+
1 row in set (0.00 sec)
mysql> select substring('MySQL',1,3);
+------------------------+
| substring('MySQL',1,3) |
+------------------------+
| MyS |
+------------------------+
1 row in set (0.00 sec)
--只给定位置,没有告诉取几个,默认到最后
mysql> select substring('MySQL',3);
+----------------------+
| substring('MySQL',3) |
+----------------------+
| SQL |
+----------------------+
1 row in set (0.00 sec)
--位置可以从负值取,但长度不能为负值
mysql> select substring('MySQL',-1);
+-----------------------+
| substring('MySQL',-1) |
+-----------------------+
| L |
+-----------------------+
1 row in set (0.00 sec)
--在MySQL中长度不能为负值
mysql> select substring('MySQL',-3,-1);
+--------------------------+
| substring('MySQL',-3,-1) |
+--------------------------+
| |
+--------------------------+
1 row in set (0.00 sec)
--like进行模式的匹配
mysql> select 'MySQL' like 'M%';
+-------------------+
| 'MySQL' like 'M%' |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A | B |
| C | D |
| tom% | 123 |
| NULL | 11 |
+------------+-----------+
4 rows in set (0.00 sec)
mysql> select * from test where
-> first_name like '%o%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom% | 123 |
+------------+-----------+
1 row in set (0.00 sec)
mysql> select * from test where first_name like '%%%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A | B |
| C | D |
| tom% | 123 |
+------------+-----------+
3 rows in set (0.00 sec)
--怎么告诉mysql中间的%不表示通配符了?
--只能随便给个值,告诉1后面的%不表示通配符了
mysql> select * from test where first_name like '%1%%' escape '1';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| tom% | 123 |
+------------+-----------+
1 row in set (0.00 sec)
--%(百分号):代表任意个字符
--_(下划线):代表任意一个字符
2数值运算符与函数
mysql> select ceil(3.01);
+------------+
| ceil(3.01) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(3.99);
+-------------+
| floor(3.99) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> select 3/4;
+--------+
| 3/4 |
+--------+
| 0.7500 |
+--------+
1 row in set (0.00 sec)
mysql> select 3 div 4;
+---------+
| 3 div 4 |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
mysql> select 5 % 3;
+-------+
| 5 % 3 |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
mysql> select 5 mod 3;
+---------+
| 5 mod 3 |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
mysql> select 5.3 mod 3;
+-----------+
| 5.3 mod 3 |
+-----------+
| 2.3 |
+-----------+
1 row in set (0.00 sec)
mysql> select power(3,3);
+------------+
| power(3,3) |
+------------+
| 27 |
+------------+
1 row in set (0.00 sec)
mysql> select round(3.652,2);
+----------------+
| round(3.652,2) |
+----------------+
| 3.65 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(3.652,1);
+----------------+
| round(3.652,1) |
+----------------+
| 3.7 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(3.652,0);
+----------------+
| round(3.652,0) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
--只作数字的截取,并没有四舍五入
mysql> select truncate(125.89,2);
+--------------------+
| truncate(125.89,2) |
+--------------------+
| 125.89 |
+--------------------+
1 row in set (0.00 sec)
mysql> select truncate(125.89,1);
+--------------------+
| truncate(125.89,1) |
+--------------------+
| 125.8 |
+--------------------+
1 row in set (0.00 sec)
mysql> select truncate(125.89,0);
+--------------------+
| truncate(125.89,0) |
+--------------------+
| 125 |
+--------------------+
1 row in set (0.00 sec)
mysql> select truncate(125.89,-1);
+---------------------+
| truncate(125.89,-1) |
+---------------------+
| 120 |
+---------------------+
1 row in set (0.00 sec)
3比较运算符与函数
mysql> select 15 between 1 and 22;
+---------------------+
| 15 between 1 and 22 |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
mysql> select 35 between 1 and 22;
+---------------------+
| 35 between 1 and 22 |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
mysql> select 35 not between 1 and 22;
+-------------------------+
| 35 not between 1 and 22 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select 10 in(5,15,20);
+-----------------+
| 10 in(5,15,20) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
mysql> select 13 in(5,15,20);
+-----------------+
| 13 in(5,15,20) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
mysql> select null is null;
+--------------+
| null is null |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select '' is null;
+------------+
| '' is null |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
mysql> select 0 is null;
+-----------+
| 0 is null |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from test where
-> first_name is null;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| NULL | 11 |
+------------+-----------+
1 row in set (0.00 sec)
mysql> select * from test where first_name is not null;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| A | B |
| C | D |
| tom% | 123 |
+------------+-----------+
3 rows in set (0.01 sec)
4日期时间函数
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-06-14 20:11:21 |
+---------------------+
1 row in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2018-06-14 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 20:11:54 |
+-----------+
1 row in set (0.00 sec)
mysql> select date_add('2014-3-12',interval 365 day);
+----------------------------------------+
| date_add('2014-3-12',interval 365 day) |
+----------------------------------------+
| 2015-03-12 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2014-3-12',interval -365 day);
+-----------------------------------------+
| date_add('2014-3-12',interval -365 day) |
+-----------------------------------------+
| 2013-03-12 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2014-3-12',interval 1 year);
+---------------------------------------+
| date_add('2014-3-12',interval 1 year) |
+---------------------------------------+
| 2015-03-12 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2014-3-12',interval 3 week);
+---------------------------------------+
| date_add('2014-3-12',interval 3 week) |
+---------------------------------------+
| 2014-04-02 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2013-3-12','2014-3-12');
+-----------------------------------+
| datediff('2013-3-12','2014-3-12') |
+-----------------------------------+
| -365 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date_format('2014-3-2','%m/%d/%Y');
+------------------------------------+
| date_format('2014-3-2','%m/%d/%Y') |
+------------------------------------+
| 03/02/2014 |
+------------------------------------+
1 row in set (0.00 sec)
5信息函数
--线程id
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 7 |
+-----------------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| my_django |
+------------+
1 row in set (0.00 sec)
mysql> desc test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table test add id smallint unsigned
-> key auto_increment first;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | A | B |
| 2 | C | D |
| 3 | tom% | 123 |
| 4 | NULL | 11 |
+----+------------+-----------+
4 rows in set (0.00 sec)
mysql> insert into test(first_name,last_name) values('aa','bb');
Query OK, 1 row affected (0.00 sec)
--只能查询auto_increment字段最后插入的记录id
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)
--插入多条记录,查出来的是最后一次插入的第一条记录id
mysql> insert into test(first_name,last_name) values('aa','cc'),('aa','dd');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
mysql> select user();
+------------------+
| user() |
+------------------+
| mhh123@localhost |
+------------------+
1 row in set (0.00 sec)
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.7.22-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.01 sec)
6聚合函数
--聚合函数只有一个返回值
mysql> select * from test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | A | B |
| 2 | C | D |
| 3 | tom% | 123 |
| 4 | NULL | 11 |
| 5 | aa | bb |
| 6 | aa | cc |
| 7 | aa | dd |
+----+------------+-----------+
7 rows in set (0.00 sec)
mysql> select avg(id) from test;
+---------+
| avg(id) |
+---------+
| 4.0000 |
+---------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------------+
| Tables_in_my_django |
+---------------------+
| test |
+---------------------+
1 row in set (0.00 sec)
mysql> select count(id) as counts from test;
+--------+
| counts |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)
mysql> select max(id) as big from test;
+------+
| big |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
mysql> select min(id) as small from test;
+-------+
| small |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> select sum(id) as total from test;
+-------+
| total |
+-------+
| 28 |
+-------+
1 row in set (0.00 sec)