Mysql数据的插入表的复制函数的运用多表查询
- 技术交流
- 2024-09-25 19:26:01
1.从另一个数据库中插入语句
创建一个users表从index_db数据库中的users表
mysql>createtableuserslikeindex_db.users;
从数据库index_db中users表所有的数据，复制到users表中
mysql>insertintousersselect*fromindex_db.users;
2.查询前三行数据
mysql>select*fromuserslimit3;
3.查询第二行之后，2条数据；
mysql>select*fromuserslimit2,2;
4.查看分组
mysql>selectaddress,count(address)fromusersgroupbyaddress;
5.统计中使用别名
mysql>selectaddress,count(address)ascfromusersgroupbyaddress;
6.分组条件
mysql>selectaddress,count(address)ascfromusersgroupbyaddresshavingc>=2;
7.address升序排列
mysql>selectaddress,count(address)ascfromusersgroupbyaddressorderbycasc;
8.address降序排列
mysql>selectaddress,count(address)ascfromusersgroupbyaddressorderbycdesc;
9.更新一条语句的多个字段
mysql>updateuserssetuname='linux',address='zhumadian'whereid=2;
mysql数据记录查询
10.去重distinct
mysql>selectdistinct(address)fromusers;
11.加减乘除运算
mysql>select7+8fromdual;
mysql>select9-8fromdual;
mysql>select8*7fromdual;
mysql>select8/4fromdual;
取模
mysql>select8%7fromdual;
mysql>select8mod7fromdual;
12.字符串的连接
mysql>selectconcat("hello","world")fromdual;
+-------------------------+
|concat("hello","world")|
+-------------------------+
|helloworld|
+-------------------------+
1rowinset(0.06sec)
mysql>selectconcat(uname,concat("idis",id),concat(",addressis",address))fromusers;
+--------------------------------------------------------------------+
|concat(uname,concat("idis",id),concat(",addressis",address))|
+--------------------------------------------------------------------+
|jinhanidis1,addressisbeijing|
|jinhanidis1,addressisshanghai|
|linuxidis2,addressiszhumadian|
|tom4idis4,addressisbeijing|
|tom4idis4,addressisbeijing|
+--------------------------------------------------------------------+
13.isnull
mysql>select*fromuserswhereaddressisnull;
+------+------------+---------+
|id|uname|address|
+------+------------+---------+
|3|lvguanghui|NULL|
14.in
mysql>select*fromuserswhereidin(1);
+------+--------+----------+
|id|uname|address|
+------+--------+----------+
|1|jinhan|beijing|
|1|jinhan|shanghai|
+------+--------+----------+
15.like模糊查询
匹配以j开头的uname
mysql>select*fromuserswhereunamelike"j%";
匹配以n结尾的uname
mysql>select*fromuserswhereunamelike"%n";
16.匹配单个%
mysql>select*fromuserswhereunamelike"\%";
匹配多个%
mysql>select*fromuserswhereaddresslike"\%\%";
17.统计函数与分组数据记录查询
统计count
mysql>selectcount(*)fromusers;
平均值avg
mysql>selectavg(age)fromusers;
最大值max
mysql>selectmax(age)fromusers;
最小值min
mysql>selectmin(age)fromusers;
分组groupby
mysql>selectage,count(*)fromusersgroupbyage;
功能分组查询
mysql>selectuname,group_concat(distinct(age)),count(*)fromusersgroupbyuname;
分组过滤条件having
mysql>selectuname,ageasa,count(*)ascfromusersgroupbyunamehavinga>49;
多表查询和子查询
18.等值查询
mysql>selects.stu_id,s.stu_name,sc.stu_car_timefromstus,stu_cardscwheres.stu_id=sc.stu_card_idorderbystu_idasc;
+--------+----------+--------------+
|stu_id|stu_name|stu_car_time|
+--------+----------+--------------+
|1|tom|2015-11-20|
|2|link|2015-11-23|
|3|lony|2015-11-22|
|4|sony|2015-11-21|
+--------+----------+--------------+
19.合并查询，相同则不显示
mysql>select*fromstuunionselect*fromstu2;
20.子查询，返回单行单列
mysql>select*fromstuwherestu_age>(selectstu_agefromstuwherestu_name="tom");
21.子查询，返回多行多列
mysql>select*fromstuwhere(stu_age,stu_name)=(selectstu_age,stu_namefromstuwherestu_name="tom");
22.in的使用
mysql>select*fromstuwherestu_agein(20);
23.any的使用
mysql>select*fromstuwherestu_age>any(selectstu_agefromstuwherestu_name='link');
24.all的使用
mysql>select*fromstuwherestu_age>all(selectstu_agefromstuwherestu_name='link');
25.exists的使用
mysql>select*fromstuswhereexists(selectstu_agefromstuwhere30=s.stu_age);
mysql常用函数
26.concat和concat_ws
mysql>selectconcat("hello","mysql");
+-------------------------+
|concat("hello","mysql")|
+-------------------------+
|hellomysql|
+-------------------------+
1rowinset(0.09sec)
mysql>selectconcat_ws(":","2016","11","12");
+---------------------------------+
|concat_ws(":","2016","11","12")|
+---------------------------------+
|2016:11:12|
+---------------------------------+
1rowinset(0.00sec)
27.字符串比较函数strcmp();比较字母是以ascill码的先后顺序；前者大于后者返回1，反之返回-1，两者相等则返回0；
mysql>selectstrcmp("a","b");
+-----------------+
|strcmp("a","b")|
+-----------------+
|-1|
+-----------------+
1rowinset(0.06sec)
mysql>selectstrcmp("b","a");
+-----------------+
|strcmp("b","a")|
+-----------------+
|1|
+-----------------+
1rowinset(0.00sec)
mysql>selectstrcmp("a","a");
+-----------------+
|strcmp("a","a")|
+-----------------+
|0|
+-----------------+
1rowinset(0.00sec)
28.统计字符length和char_length
mysql>selectchar_length("aaa");
+--------------------+
|char_length("aaa")|
+--------------------+
|3|
+--------------------+
1rowinset(0.00sec)
mysql>selectlength(111);
+-------------+
|length(111)|
+-------------+
|3|
+-------------+
1rowinset(0.00sec)
29.字符串大小写转换
大写
mysql>selectupper("aa");
+-------------+
|upper("aa")|
+-------------+
|AA|
+-------------+
1rowinset(0.00sec)
mysql>selectucase("aa");
+-------------+
|ucase("aa")|
+-------------+
|AA|
+-------------+
1rowinset(0.00sec)
小写
mysql>selectlower("DD");
+-------------+
|lower("DD")|
+-------------+
|dd|
+-------------+
1rowinset(0.00sec)
mysql>selectlcase("DD");
+-------------+
|lcase("DD")|
+-------------+
|dd|
+-------------+
1rowinset(0.00sec)
30.使用字符串函数，查找字符串；
mysql>selectfind_in_set("a","a,b,c,d");
+----------------------------+
|find_in_set("a","a,b,c,d")|
+----------------------------+
|1|
+----------------------------+
1rowinset(0.00sec)
mysql>selectfield("a","b","c","d","a");
+----------------------------+
|field("a","b","c","d","a")|
+----------------------------+
|4|
+----------------------------+
1rowinset(0.00sec)
查看位置
mysql>selectlocate("a","abc");
+-------------------+
|locate("a","abc")|
+-------------------+
|1|
+-------------------+
1rowinset(0.00sec)
查看位置
mysql>selectposition("a"in"abc");
+----------------------+
|position("a"in"abc")|
+----------------------+
|1|
+----------------------+
1rowinset(0.00sec)
匹配首个字母是否存在
mysql>selectinstr("a","a");
+----------------+
|instr("a","a")|
+----------------+
|1|
+----------------+
1rowinset(0.00sec)
mysql>selectinstr("a","d");
+----------------+
|instr("a","d")|
+----------------+
|0|
+----------------+
1rowinset(0.00sec)
mysql>selectinstr("a","da");
+-----------------+
|instr("a","da")|
+-----------------+
|0|
+-----------------+
1rowinset(0.00sec)
查询位置为1的字符
mysql>selectelt(1,"a","b");
+----------------+
|elt(1,"a","b")|
+----------------+
|a|
+----------------+
1rowinset(0.00sec)
31.字符串的截取
左边截取
mysql>selectleft("abc",1);
+---------------+
|left("abc",1)|
+---------------+
|a|
+---------------+
1rowinset(0.00sec)
右边截取
mysql>selectright("abc",1);;
+----------------+
|right("abc",1)|
+----------------+
|c|
+----------------+
1rowinset(0.00sec)
Substring的截取
mysql>selectsubstring("helloworld",1,5);
+-----------------------------+
|substring("helloworld",1,5)|
+-----------------------------+
|hello|
+-----------------------------+
Mid的截取
mysql>selectmid("helloworld",1,5);
+-----------------------+
|mid("helloworld",1,5)|
+-----------------------+
|hello|
+-----------------------+
32.去除空格
左边空格ltrim
mysql>selectltrim("abc");
+---------------+
|ltrim("abc")|
+---------------+
|abc|
+---------------+
右边空格rtrim
mysql>selectrtrim("abc");;
+---------------+
|rtrim("abc")|
+---------------+
|abc|
+---------------+
去除字符前面空格trim
mysql>selecttrim("abc");
+---------------+
|trim("abc")|
+---------------+
|abc|
+---------------+
1rowinset(0.00sec)
33.字符串的替换
Replace的使用
把字符串a替换为1
mysql>selectreplace("abc","a","1");
+------------------------+
|replace("abc","a","1")|
+------------------------+
|1bc|
+------------------------+
1rowinset(0.00sec)
34.获取随机函数
Rand的使用
默认的rand()为[0,1);可以使用rand()*n对随机数的获得
mysql>selectrand();
+-------------------+
|rand()|
+-------------------+
|0.928362891011278|
+-------------------+
1rowinset(0.00sec)
mysql>selectrand()*3;
+--------------------+
|rand()*3|
+--------------------+
|2.7664726923838936|
+--------------------+
1rowinset(0.00sec)
35．取整数
向上取整
mysql>selectceil(2.1);
+-----------+
|ceil(2.1)|
+-----------+
|3|
+-----------+
向下取整
mysql>selectfloor(3.2);
+------------+
|floor(3.2)|
+------------+
|3|
+------------+
36.保留小数点位数
右边保留
mysql>selecttruncate(2222.44,2);
+---------------------+
|truncate(2222.44,2)|
+---------------------+
|2222.44|
+---------------------+
1rowinset(0.00sec)
左边保留
mysql>selecttruncate(11.245,0);
+--------------------+
|truncate(11.245,0)|
+--------------------+
|11|
+--------------------+
1rowinset(0.00sec)
mysql>selecttruncate(11.245,-1);
+---------------------+
|truncate(11.245,-1)|
+---------------------+
|10|
+---------------------+
1rowinset(0.00sec)
mysql>selecttruncate(11.245,-2);
+---------------------+
|truncate(11.245,-2)|
+---------------------+
|0|
+---------------------+
1rowinset(0.00sec)
37.四舍五入
右边四舍五入
mysql>selectround(11.15,1);
+----------------+
|round(11.15,1)|
+----------------+
|11.2|
+----------------+
1rowinset(0.00sec)
左边的四舍五入
mysql>selectround(1567.22,-1);
+-------------------+
|round(1567.22,-1)|
+-------------------+
|1570|
+-------------------+
1rowinset(0.00sec)
38.时间函数
获取系统当前时间
mysql>selectnow();
+---------------------+
|now()|
+---------------------+
|2016-11-1210:56:18|
+---------------------+
1rowinset(0.07sec)
mysql>selectsysdate();
+---------------------+
|sysdate()|
+---------------------+
|2016-11-1211:00:33|
+---------------------+
1rowinset(0.02sec)
获取系统当前日期
mysql>selectcurdate();
+------------+
|curdate()|
+------------+
|2016-11-12|
+------------+
1rowinset(0.00sec)
获取系统秒数并转换为当前系统日期
mysql>selectunix_timestamp(now()),now();
+-----------------------+---------------------+
|unix_timestamp(now())|now()|
+-----------------------+---------------------+
|1478919542|2016-11-1210:59:02|
+-----------------------+---------------------+
获取时间分值函数
年
mysql>selectyear(now());
+-------------+
|year(now())|
+-------------+
|2016|
+-------------+
1rowinset(0.00sec)
月
mysql>selectmonth(now());
+--------------+
|month(now())|
+--------------+
|11|
+--------------+
1rowinset(0.00sec)
日
mysql>selectdayofmonth(now());
+-------------------+
|dayofmonth(now())|
+-------------------+
|12|
+-------------------+
1rowinset(0.06sec)
时
mysql>selecthour(now());
+-------------+
|hour(now())|
+-------------+
|11|
+-------------+
1rowinset(0.00sec)
分
mysql>selectminute(now());
+---------------+
|minute(now())|
+---------------+
|5|
+---------------+
1rowinset(0.00sec)
秒
mysql>selectsecond(now());
+---------------+
|second(now())|
+---------------+
|47|
+---------------+
相隔天数
表示从1970的相隔天数
mysql>selectto_days("2016-11-12");
+-----------------------+
|to_days("2016-11-12")|
+-----------------------+
|736645|
+-----------------------+
1rowinset(0.00sec)
一段时间后日期的函数
mysql>selectfrom_days(to_days("2016-10-3"));
+---------------------------------+
|from_days(to_days("2016-10-3"))|
+---------------------------------+
|2016-10-03|
+---------------------------------+
1rowinset(0.00sec)
两个日期自己的相隔天数
mysql>selectdatediff("2016-11-12","2016-11-05");
+-------------------------------------+
|datediff("2016-11-12","2016-11-05")|
+-------------------------------------+
|7|
+-------------------------------------+
1rowinset(0.00sec)
39.使用系统函数
获取当前系统版本号
mysql>selectversion();
+-----------+
|version()|
+-----------+
|5.5.15|
+-----------+
获取使用当前所用的数据库
mysql>selectdatabase();
+------------+
|database()|
+------------+
|mydb|
+------------+
1rowinset(0.00sec)
获取当前用户
mysql>selectuser();
+--------+
|user()|
+--------+
|ODBC@|
+--------+
1rowinset(0.00sec)
40.获取自动增长的主键的id
查找最后以增加的id
mysql>selectlast_insert_id()fromtable_autolimit1;
-----------------------------------
Mysql数据的插入，表的复制，函数的运用，多表查询
Mysql数据的插入表的复制函数的运用多表查询由讯客互联技术交流栏目发布，感谢您对讯客互联的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人网站或者朋友圈，但转载请说明文章出处“Mysql数据的插入表的复制函数的运用多表查询”
