1.查看Mysql 内置函数
1 # 查看Mysql 内置函数 2 mysql> help contents; 3 You asked for help about help category: "Contents" 4 For more information, type 'help <item>', where <item> is one of the following 5 categories: 6 Account Management 7 Administration 8 Compound Statements 9 Data Definition 10 Data Manipulation 11 Data Types 12 Functions 13 Functions and Modifiers for Use with GROUP BY 14 Geographic Features 15 Help Metadata 16 Language Structure 17 Plugins 18 Procedures 19 Storage Engines 20 Table Maintenance 21 Transactions 22 User-Defined Functions 23 Utility 24 25 # mysql> help Functions; 26 You asked for help about help category: "Functions" 27 For more information, type 'help <item>', where <item> is one of the following 28 categories: 29 Bit Functions 30 Comparison operators 31 Control flow functions 32 Date and Time Functions 33 Encryption Functions 34 Information Functions 35 Logical operators 36 Miscellaneous Functions 37 Numeric Functions 38 String Functions 39 40 # mysql> help String Functions 查看字符功能函数 41 You asked for help about help category: "String Functions" 42 For more information, type 'help <item>', where <item> is one of the following 43 topics: 44 ASCII 45 BIN 46 BINARY OPERATOR 47 BIT_LENGTH 48 CAST 49 CHAR FUNCTION 50 CHARACTER_LENGTH 51 CHAR_LENGTH 52 CONCAT #****** 53 CONCAT_WS 54 CONVERT 55 ELT 56 EXPORT_SET 57 EXTRACTVALUE 58 FIELD 59 FIND_IN_SET 60 FORMAT 61 FROM_BASE64 62 HEX 63 INSERT FUNCTION 64 INSTR 65 LCASE 66 LEFT 67 LENGTH 68 LIKE 69 LOAD_FILE 70 LOCATE 71 LOWER 72 LPAD 73 LTRIM 74 MAKE_SET 75 MATCH AGAINST 76 MID 77 NOT LIKE 78 NOT REGEXP 79 OCT 80 OCTET_LENGTH 81 ORD 82 POSITION 83 QUOTE 84 REGEXP 85 REPEAT FUNCTION 86 REPLACE FUNCTION 87 REVERSE 88 RIGHT 89 RPAD 90 RTRIM 91 SOUNDEX 92 SOUNDS LIKE 93 SPACE 94 STRCMP 95 SUBSTR 96 SUBSTRING 97 SUBSTRING_INDEX 98 TO_BASE64 99 TRIM 100 UCASE 101 UNHEX 102 UPDATEXML 103 UPPER 104 WEIGHT_STRING
2.
#1.select 配合内置函数使用 #查看当前用户 mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) #查看当前mysql 版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.13 | +-----------+ 1 row in set (0.00 sec) #查看当前时间 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-11-02 20:10:55 | +---------------------+ 1 row in set (0.00 sec) #查看当前在哪个数据库 mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) #拼接命令 concat("") mysql> select concat("hello world!"); +------------------------+ | concat("hello world!") | +------------------------+ | hello world! | +------------------------+ 1 row in set (0.01 sec) mysql> select user,host from mysql.user; #拼接格式为:mysql.sys@localhost +-----------+-----------+ | user | host | +-----------+-----------+ | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 2 rows in set (0.00 sec) mysql> select concat(user,"@",host) from mysql.user; #拼接最终结果 +-----------------------+ | concat(user,"@",host) | +-----------------------+ | mysql.sys@localhost | | root@localhost | +-----------------------+ 2 rows in set (0.00 sec) #2.计算 mysql> select 10 * 100; +----------+ | 10 * 100 | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) mysql> select 100 / 10; +----------+ | 100 / 10 | +----------+ | 10.0000 | +----------+ 1 row in set (0.00 sec) mysql> select 100 - 50; +----------+ | 100 - 50 | +----------+ | 50 | +----------+ 1 row in set (0.00 sec) mysql> select 100 + 30; +----------+ | 100 + 30 | +----------+ | 130 | +----------+ 1 row in set (0.00 sec) mysql> select 100 % 10; +----------+ | 100 % 10 | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) #3.查询数据库参数 #查看全部参数: 504个 | transaction_prealloc_size | 4096 | transaction_write_set_extraction | OFF | tx_isolation | REPEATABLE-READ | tx_read_only | OFF | unique_checks | ON | updatable_views_with_limit | YES | version | 5.7.13 | version_comment | Source distribution | version_compile_machine | x86_64 | version_compile_os | Linux | wait_timeout | 28800 | warning_coun | 0 504 rows in set (0.00 sec) #版本不一样,总的参数数量也不一样 #查看只记住参数部分时命令 用Like mysql> show variables like "%trx%"; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_api_trx_level | 0 | | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 2 rows in set (0.00 sec) mysql> select @@datadir; #查看数据目录 +-------------------+ | @@datadir +-------------------+ | /home/mysql/data/ | +-------------------+ 1 row in set (0.00 sec) mysql> select @@socket; #查看socket +-----------------+ | @@socket | +-----------------+ | /tmp/mysql.sock | +-----------------+ 1 row in set (0.00 sec) mysql> select @@port; #查看port +--------+ | @@port | +--------+ | 3306 | +--------+ 1 row in set (0.00 sec) mysql> select @@innodb_flush_log_at_trx_commit; +----------------------------------+ | @@innodb_flush_log_at_trx_commit | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec)
3.
#1.单表 默认执行顺序(严格按照执行顺序) select * 1. from 表1,表2,l... 2. where 过滤条件1 过滤条件2 ... 3. group by 条件列1 条件列2 4. select_list 5. having 过滤条件1 过滤条件2 ... 6. order by 条件列1 条件列2 7. limit 限制条件 先导入数据world.sql [root@shell ~21:01:45]# mysql -uroot -p123456 <world.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql> show databases; #查看所有库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 5 rows in set (0.00 sec) mysql> use world; #进入world库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; #查看world库中所有表 +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec) mysql> desc city; #查看city 表结构 +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> show create table city; #查看city 建表语句 | Table | Create Table CREATE TABLE `city` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 | #1. select 配合from 子句使用 语法:select 列 from 表; 类似 于cat /etc/passwd select * from 表 ; ---查询表中所有数据 select * from world.city; select Id,name,countycode,district,population from world.city; ---查询部分列值 类似于awk 取列 select name,population from world.city; #2. select+from +where 其中where类似于grep 过滤 #a.where 配合比较符来使用,例如: > = < >= <= != 查询city表中,所有中国城市信息 mysql> select * from world.city where countrycode='CHN'; 查询city表中,人口数小于1000的城市 mysql> select * from world.city where population <1000; #b.where 配合like 进行模糊查询 适合于字符串类型,不能like数字等 查询city表中,国家代号为CH开头的城市信息 select * from world.city where countrycode like 'CH%'; #注意:like语句在使用时,切记不可出现前面带%的模糊查询,因为不走索引 #c.where 配合逻辑链接符 AND OR XOR(非) 查询中国人口数小于500万的城市信息 select * from world.city where countrycode='CHN' AND population <5000000; 查询中国或者美国的城市信息 select * from world.city where countrycode='CHN' OR countrycode='USA'; #3.select +from +where +group by #分组后一定要显示该列, select CountryCode, count(id/name)from world.city group by CountryCode; #a.gtoup by 配合聚合函数 max() #求最大值 min() #求最小值 avg() #求平均值 count() #统计个数 sum() #求和 group_concat() #列转行 说明:碰到group by 必然会有聚合函数 统计每个国家的总的人口数 select CountryCode, count(id/name)from world.city group by CountryCode; 统计中国,每个省的总人口数 select District,sum(population) from city where CountryCode='CHN' group by District; 统计中国,每个省总人口,城市个数,城市名列表 select District,sum(population),count(id) from city where CountryCode='CHN' group by District; #4.select +from +where +group by+having #having 属于后过滤,以group by 为中间,where 为前过滤. having 作用:与where 子句类型,having 属于后过滤 场景:需要在group by +聚合函数后,再做过滤时使用 统计中国,每个省的总人口数并只显示总人口数大于500万 select District,sum(population) from city where CountryCode='CHN' group by District having sum(population) >5000000; #5.select +from +where +group by+having +order by order by 作用: 排序作用 统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以小到大进行排序 select District,sum(population) from city where CountryCode='CHN' group by District having sum(population) >5000000 order by sum(population); 统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以大到小进行排序 select District,sum(population) from city where CountryCode='CHN' group by District having sum(population) >5000000 order by sum(population) desc; #6.select +from +where +group by+having +order by+limit limit作用:分页显示结果集 统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以大到小进行排序,只显示前5名 select District,sum(population) from city where CountryCode='CHN' group by District having sum(population) >5000000 order by sum(population) desc limit 5; 统计中国,每个省的总人口数,只显示总人口数大于500万并以总人口数以大到小进行排序,只显示6-10名 select District,sum(population) from city where CountryCode='CHN' group by District having sum(population) >5000000 order by sum(population) desc limit 5,5; #意思为跳过5(左边开始第2个5为跳),再显示5,及为6-10名 select District,sum(population) from city where CountryCode='CHN' group by District having sum(population) >5000000 order by sum(population) desc limit 5 offset 5; #意思为跳过5,再显示5,及为6-10名 显示3-5行 limit 2,3 或 limit 3 offset 2 #7.select 的别名 #a.列别名(在select 后面就是列),或者不接AS 直接别名也行. 作用: 1.做为好看 2.group by having order by 子句调用,where 子句不行(因为执行顺序原因) select District AS '城市名称', sum(population) AS '总人口数' from city where CountryCode='CHN' group by District having sum(population) >5000000 order by sum(population) desc limit 5 offset 5; #别名子名调用 例子: select District AS '城市名称', sum(population) AS '总人口数' from city where CountryCode='CHN' group by 城市名称 having 总人口数 >5000000 order by 总人口数 desc limit 5 offset 5; #b.表别名 作用:全局调用定义表别名 select a.sno AS '学号',a.sname AS '姓名',group_concat(c.cname) AS '课程名称' from student AS a join sc AS b on a.sno=b.sno join course AS c where a.sname='zhang3' group by a.sno,a.sname;