掌握以下函数的用法,并在命令行进行实践操作:concat()、group_concat()、concat_ws()、load_file()、hex()、char()、count()、substring()、into+outfile、LOAD DATA INFIL、limit。
concat(str1,str2…)
- 将多个字符串连接起来,中间并无分隔符
such:
mysql> select concat('hello','jerrybird'); +-----------------------------+ | concat('hello','jerrybird') | +-----------------------------+ | hellojerrybird | +-----------------------------+ 1 row in set (0.00 sec)
group_concat()
- 将按组分开的数据,以组的方式显示出来,以逗号分隔
- such:
mysql> select *from teacher; +----+------+--------+----------+ | id | name | sex | addr | +----+------+--------+----------+ | 1 | Leo | Male | hangzhou | | 2 | Able | Male | hangzhou | | 3 | John | Male | beijing | | 4 | Mary | Female | hangzhou | | 5 | Bird | Male | huainan | +----+------+--------+----------+ 5 rows in set (0.00 sec) mysql> select sex,group_concat(name) from teacher group by sex; +--------+--------------------+ | sex | group_concat(name) | +--------+--------------------+ | Female | Mary | | Male | Leo,Able,John,Bird | +--------+--------------------+ 2 rows in set (0.00 sec)
concat_ws(separator,str1,str2…)
- 将多个字符串以指定的分隔符连接起来
- such:
mysql> select concat_ws('_','author','jerrybird','age','20'); +------------------------------------------------+ | concat_ws('_','author','jerrybird','age','20') | +------------------------------------------------+ | author_jerrybird_age_20 | +------------------------------------------------+ 1 row in set (0.00 sec)
load_file(path)
- 将某个路径下的文件读取并显示出来
- 若文件不存在会显示 null
- 若使用phpstudy 5.3…而未进行设置则会提示错误。因而需将mysql的配置文件修改下.打开my.ini 添加如下一行:secure_file_priv=”e:/code/MySQL/”
- 之后load data infile or into outfile 等只能对此文件夹下的文件有权限操作
mysql> select load_file('e:/code/MySQL/test.txt'); +-------------------------------------+ | load_file('e:/code/MySQL/test.txt') | +-------------------------------------+ | hello jerrybird,how are you? | +-------------------------------------+ 1 row in set (0.00 sec)
hex(number)
-将number 转换为16进制整数mysql> select hex(100); +----------+ | hex(100) | +----------+ | 64 | +----------+ 1 row in set (0.00 sec)
char(number)
- 将number根据ascii码转为相应的字符
- 0<number<255
mysql> select char(65); +----------+ | char(65) | +----------+ | A | +----------+ 1 row in set (0.00 sec)
count(column1…)
- 这是一个聚合函数,用来计算符合指定条件的记录的行数
- such:
mysql> select count(*) from teacher; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from teacher where sex='male'; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
- substring(str,pos[,len]) or substring(str from pos [for len])
- len 为可选的参数
- 从str中选取 从pos位置开始长度为len的字串
- pos可以为负数,代表从右往左第几位
- str的位置从1开始
mysql> SELECT SUBSTRING('Quadratically',5)
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4)
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6)
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3)
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3)
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2)
-> 'ki'
- select colomn into outfile ‘path/file’ from table-name where (…);
- 将数据表中符合条件的数据备份到path路径下的file中
- file为新建的
- path为之前设置的secure_file_priv
mysql> select * into outfile 'e:/code/MySQL/back_up.sql' from teacher where sex=
'male';
Query OK, 4 rows affected (0.00 sec)
- load data infile
- load data infile ‘path/file’ into table tb_name;
- 将file中的数据导入到指定的table中
- path为secure_file_priv
-
mysql> load data infile 'e:/code/MySQL/back_up.sql' into table teacher;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
limit
- select (colomn…) from table table_name limit number;
- 从指定table中将前number个记录的colomn…显示出来
mysql> select *from teacher limit 3; +----+------+------+----------+ | id | name | sex | addr | +----+------+------+----------+ | 1 | Leo | Male | hangzhou | | 2 | Able | Male | hangzhou | | 3 | John | Male | beijing | +----+------+------+----------+ 3 rows in set (0.00 sec)
清空表中的记录
delete from table_name;
mysql> delete from teacher; Query OK, 4 rows affected (0.00 sec)
truncate table table_name;
mysql> truncate table teacher; Query OK, 0 rows affected (0.00 sec)