Mysql实用知识点总结

最新推荐文章于 2019-06-05 14:03:57 发布
最新推荐文章于 2019-06-05 14:03:57 发布
阅读量77 收藏
点赞数
原文链接：http://www.cnblogs.com/ye-hcj/p/9530396.html
版权

本文介绍MYSQL相关知识，方便日常使用查阅

目录

准备
MYSQL常用命令
语言结构
sql语句
外键
自然语言全文搜索

  1. 准备

    你可以使用 Navicat Premium 12 或者 MySQL Workbench 8.0 CE开发MYSQL

在C:\mysql-8.0.12-winx64创建my.ini文件，文件编码ascll，输入如下内容
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:\mysql-8.0.12-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:\mysql-8.0.12-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

cmd进入mysql程序目录 cd C:\mysql-8.0.12-winx64\bin

执行 mysqld --initialize-insecure 初始化data文件夹

运行：
    前台进程
        mysqld 开启mysql服务
        mysqld --console 开启mysql服务，带错误提示
    后台服务
        mysqld --install 管理员
        net start mysql 开启服务
        mysqld --remove mysql 卸载服务
登录：
    不同的机器 mysql -h host -u user -p
    相同的机器 mysql -u root -p
退出：
    QUIT

  2. MYSQL常用命令

    重新定义定界符
    delimiter //
取消当前sql语句   
    \c 
显示所有数据库 
    SHOW DATABASES; 
显示所有表   
    SHOW TABLES;
开启用户访问权限    
    GRANT ALL ON yejiaweifirstdb.* TO 'root'@'localhost';
创建数据库       
    create database test
使用数据库   
    USE test
    mysql -u root -p test
创建表格        
    CREATE TABLE pets (
        宠物名称 VARCHAR(20), 
        主人 VARCHAR(20),
        种类 VARCHAR(20), 
        性别 CHAR(1), 
        出生日期 DATE, 
        死亡日期 DATE
    );
    create table shop (
        article int(4) unsigned zerofill default '0000' not null,
        dealer char(20) default '' not null,
        price double(16,2) default '0.00' not null,
        primary key (article, dealer)
    );
查看字符编码
    SELECT CHARSET('abc');
查看表格属性  
    DESCRIBE pets;
查看创建表格的语句   
    SHOW CREATE TABLE shirt\G   
查看当前是何数据库   
    SELECT DATABASE();
查看索引        
    show index from event;
查看访问文件权限    
    SHOW VARIABLES LIKE 'local_infile';
设置访问文件权限    
    SET GLOBAL local_infile = 1;
导入数据        
    LOAD DATA LOCAL INFILE 'C:/Users/26401/Desktop/pets.txt' INTO TABLE pets LINES TERMINATED BY '\r\n';
导入txt数据格式
    Fluffy  Harold  cat f   1993-02-04  \N
    Claws   Gwen    cat m   1994-03-17  \N
    Buffy   Harold  dog f   1989-05-13  \N
    Fang    Benny   dog m   1990-08-27  \N
    Bowser  Diane   dog m   1979-08-31  1995-07-29
    Chirpy  Gwen    bird    f   1998-09-11  \N
    Whistler    Gwen    bird    \N  1997-12-09  \N
    Slim    Benny   snake   m   1996-04-29  \N
count字句不允许去掉groupby的设置
    SET sql_mode = 'ONLY_FULL_GROUP_BY';    
    SET sql_mode = '';
批处理
    执行文件            
        mysql -u root -p < C:/Users/26401/Desktop/pets.txt 
    结果输出                
        mysql -u root -p < C:/Users/26401/Desktop/sql.txt >C:/Users/26401/Desktop/out.txt 
    结果格式输出      
        mysql -u root -p < C:/Users/26401/Desktop/sql.txt >C:/Users/26401/Desktop/out.txt -t 
    脚本和结果输出     
        mysql -u root -p < C:/Users/26401/Desktop/sql.txt >C:/Users/26401/Desktop/out.txt -v
    已处在mysql下执行文件   
        source C:/Users/26401/Desktop/sql.txt
        \. C:/Users/26401/Desktop/sql.txt
使用用户定义变量
    SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
    SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

运行localhost的数据库 
    mysql --user=root test
    mysql --host=localhost --user=root --password= test
    mysql --host=remote.example.com --port=13306
    mysql -u root -p --execute="use test;SELECT * FROM pets"; 执行sql并且退出

罗列mysql支持的所有参数 mysqladmin extended-status variables -u root -p

罗列帮助选项 mysqlshow --help

查看程序信息 mysqldump -u root personnel

可选的数字值 K, M, G, T, P, E 
    mysqladmin --count=1K --sleep=10 ping -u root -p 对server ping1024次，间隔10秒

查看是否使用了配置文件 mysqld --help

列出mysqld的所有参数 
    mysqld --verbose --help
    SHOW VARIABLES;
    SHOW STATUS;

垂直列出结果 select * from pets \G

mysql使用技巧
    SET sql_safe_updates=1,  # 不允许执行sql除非指定了where或者limit
    sql_select_limit=1000, # 选择记录上限为1000条除非指定了limit
    max_join_size=1000000; # 多表选择记录上限1000000条
    mysql --safe-updates --select_limit=500 --max_join_size=10000 --skip-reconnect

mysql本地化
    SELECT @@lc_time_names; 查看当前本地语言
    set @@lc_time_names := 'zh_CN'; 设置本地语言

查看上一条sql语句的警告信息 
    SHOW WARNINGS;
  3. mysql语言结构
    • 字面量
    字符串 'a string' "another string"
Date  
    'YYYY-MM-DD' 或者 'YY-MM-DD' -> '2012-12-31', '2012/12/31', '2012^12^31', '2012@12@31' 任意分隔符
    'YYYYMMDD' 或者 'YYMMDD' -> '20150721', '150721'
    YYYYMMDD 或者 YYMMDD -> 20150721, 150721
DATETIME 和 TIMESTAMP
    'YYYY-MM-DD HH:MM:SS' 或者 'YY-MM-DD HH:MM:SS' -> '2012-12-31 11:30:45', '2012^12^31 11+30+45' '2012-12-31T11:30:45' 任意分隔符
    'YYYYMMDDHHMMSS' 或者 'YYMMDDHHMMSS' -> '20070523091528', '070523091528'
    YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS -> 19830905132800, 830905132800
TIME
    D HH:MM:SS.fraction', 'D HH:MM:SS', 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', 或者 'SS'
    'HHMMSS'
    HHMMSS 
Boolean
    TRUE, true, FALSE, false
    • 特殊的数据类型
    enum 
    create table myenum(test enum('a', 'b', 'c'));
    insert into myenum values('d');
    insert into myenum values(2);
set
    create table myset (test set('a', 'b', 'c'));
    insert into myset values('a');
    insert into myset values('a', 'b');
    select * from myset where find_in_set('a', test) = 1;
    select * from myset where test like '%a%';
json
    CREATE TABLE myjson (jdoc JSON);
    INSERT INTO myjson VALUES('{"key1": "value1", "key2": "value2"}');
    返回json数据类型
        SELECT JSON_TYPE('["a", "b", 1]');
        SELECT JSON_TYPE('"hello"');    
    返回json数据
        SELECT JSON_ARRAY('a', 1, NOW()); 返回json数组
        SELECT JSON_OBJECT('key1', 1, 'key2', 'abc'); 返回json对象
        SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}'); 合并
    赋值给自定义变量
        SET @j = JSON_OBJECT('key', 'value');
        SELECT @j;
    取键值
        SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
        SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');     # [1, 2, [3, 4, 5]]
        SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');      # [3, 4, 5]
        SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');         # [1, 2]
        SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');                    # [2, 3, 4]
        SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');          # [2, 3, 4]
    设置键值
        SELECT JSON_SET('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[0]', 'a');
        SELECT JSON_SET('[3, {"a": [5, 6], "b": 10}, [99, 100]]', '$[0]', 'a', '$[1]', 'b');
        SELECT JSON_REPLACE('[1, 2, 3, 4, 5]', '$[last]', 10);                  # [1, 2, 3, 4, 10]
        SELECT JSON_INSERT('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2); #  ["a", {"b": [true, false]}, [10, 20, 2]]
    移除键值
        SELECT JSON_REMOVE('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[1]', '$[1].b[1]'); # ["a", {"b": [true]}]
    • 类型转换
    隐式类型转换
    SELECT 1+'1'; -> 2
    select '11' = 11; -> 1
    SELECT CONCAT(2,' test'); -> '2 test'
显示类型转换
    SELECT CONVERT('abc' USING utf8);
    SELECT CONVERT('abc', CHAR CHARACTER SET utf8);
    SELECT CONCAT(38.8); -> '38.8'
    select cast('11' as unsigned) = 11; -> 1
    SELECT CAST(38.8 AS CHAR); -> '38.8'
    • 比较操作符
    SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;              -> 1, 1, 0
SELECT 1 = 1, NULL = NULL, 1 = NULL;                    -> 1, NULL, NULL
SELECT 'zapp' <> 'zappp';                               -> 1
SELECT 0.1 <= 2;                                        -> 1
SELECT 2 < 2;                                           -> 0
SELECT 2 >= 2;                                          -> 1
SELECT 2 > 2;                                           -> 0
SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;          -> 1, 1, 1
SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;     -> 1, 1, 0
SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;              -> 0, 0, 1
SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;      -> 1, 1, 0
SELECT 1 BETWEEN 1 AND 3, 3 BETWEEN 3 and 1;            -> 1, 0 (NOT BETWEEN min AND max)
SELECT COALESCE(NULL,1);                        -> 1 (返回第一个非NULL值)
SELECT GREATEST(100,20,30.1);                   -> 100.0 (返回最大值)
select least(1,0.1,2.2);                        -> 0.1 (返回最小值)
SELECT 'wefwf' IN ('wee','wefwf','weg');                -> 1 (NOT IN)
SELECT (3,4) IN ((1,2), (3,4));                     -> 1
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2); -> 是否有满足条件的
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2); -> 是否全部满足条件
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10); -> 同时比较多列
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2); -> 是否有结果
SELECT ISNULL(1+1);                             -> 0
SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);            -> 3 (查找23应该在顺序表中的索引)
    • 逻辑操作符
    SELECT NOT 10; -> 0 
SELECT !10; -> 0
SELECT NOT NULL; -> NULL

SELECT 1 AND 0; -> 0
SELECT 1 && 0; -> 0
SELECT 1 AND NULL; -> NULL

SELECT 1 OR 0; -> 1
SELECT 1 || 0; -> 1
SELECT 0 OR NULL; -> NULL

SELECT 1 XOR 1 XOR 1; -> 1 (两个操作数当且仅当有一个是true返回true)
SELECT 1 XOR NULL; -> NULL
    • 赋值运算符
    SELECT @var1 := 1; SELECT @var1; -> 1
    • 流程控制函数
    CASE
    SELECT CASE 3
    WHEN 1 THEN 'one'
    WHEN 2 THEN 'two'
    ELSE 'more' 
    END;

    SELECT CASE WHEN 
    1>0 THEN 'true' 
    ELSE 'false' 
    END;
IF  
    SELECT IF(1>2,2,3); -> 3
    SELECT IF(1<2,'yes','no'); -> 'yes'
IFNULL
    SELECT IFNULL(1,0); -> 1
    SELECT IFNULL(NULL,10); -> 10
NULLIF
    SELECT NULLIF(1,1); -> NULL (如果expr1=expr2，返回NULL，否则返回expr1)
    SELECT NULLIF(1,2); -> 1
LOOP
    CREATE PROCEDURE doiterate(p1 INT)
    BEGIN
        label1: LOOP
            SET p1 = p1 + 1;
            IF p1 < 10 THEN
            ITERATE label1;
            END IF;
            LEAVE label1;
        END LOOP label1;
        SET @x = p1;
    END;
REPEAT
    CREATE PROCEDURE dorepeat(p1 INT)
    BEGIN
        SET @x = 0;
        REPEAT
            SET @x = @x + 1;
        UNTIL @x > p1 END REPEAT;
    END
WHILE
    CREATE PROCEDURE dowhile()
    BEGIN
        DECLARE v1 INT DEFAULT 5;
        WHILE v1 > 0 DO
            ...
            SET v1 = v1 - 1;
        END WHILE;
    END;
游标
    CREATE PROCEDURE curdemo()
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE a CHAR(16);
        DECLARE b, c INT;
        DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
        DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN cur1;
        OPEN cur2;

        read_loop: LOOP
            FETCH cur1 INTO a, b;
            FETCH cur2 INTO c;
            IF done THEN
                LEAVE read_loop;
            END IF;
            IF b < c THEN
                INSERT INTO test.t3 VALUES (a,b);
            ELSE
                INSERT INTO test.t3 VALUES (a,c);
            END IF;
        END LOOP;

        CLOSE cur1;
        CLOSE cur2;
    END;
    • 字符串常用函数
    SELECT CONCAT('My', 'S', 'QL');                                     -> 'MySQL'
SELECT CONCAT_WS('|','First name','Second name','Last Name');       -> 'First name|Second name|Last Name'
SELECT FORMAT(11.11111111, 1);                                      -> '11.1' (保留几位小数)
SELECT INSERT('Quadratic', 1, 3, 'What');                           -> 'Whatdratic' (从原字符串的第1个字母开始，删掉连续的3个，替换成'what')
SELECT INSTR('foobarbar', 'f');                                     -> 1 (返回右字符串在左字符串中第一个匹配的索引，从1开始)
SELECT LOCATE('f','foobarbar');                                     -> 1 (返回左字符串在右字符串中第一个匹配的索引，从1开始 SELECT LOCATE('f','foobarbar', 1); )
SELECT LEFT('foobarbar', 5);                                        -> 'fooba' (取左边五个字符)
SELECT RIGHT('foobarbar', 5);                                       -> 'arbar'
SELECT LENGTH('abc');                                               -> 3 (返回字节数 SELECT LENGTH('叶家伟'); -> 6)
SELECT LOWER('YEJIAWEI');                                           -> 'yejiawei' (大写转小写)
SELECT LPAD('||',4,'??');                                           -> '??||' (左填充，一共4个字符)
SELECT RPAD('||',4,'??');                                           -> ||??
SELECT LTRIM('  ||');                                               -> '||' (去掉左空格)
SELECT RTRIM('||  ');                                               -> '||'
SELECT TRIM('  ||   ');                                             -> '||'
SELECT TRIM(LEADING 'x' FROM 'xxx||xxx');                           -> '||xxx'
SELECT TRIM(BOTH 'x' FROM 'xxx||xxx');                              -> '||'
SELECT TRIM(TRAILING 'x' FROM 'xxx||xxx');                          -> 'xxx||'
SELECT REPEAT('MySQL', 3);                                          -> 'MySQLMySQLMySQL'
SELECT REPLACE('abcd', 'b', 'fg');                                  -> 'afgcd'
SELECT REVERSE('abc');                                              -> 'cba'
SELECT SPACE(6);                                                    -> '      '
SELECT SUBSTRING('abcdefg', 2);                                     -> 'bcdefg' 等价于 SELECT SUBSTRING('abcdefg' from 2);
SELECT SUBSTRING('abcdefg',2,2);                                    -> 'bc'
SELECT SUBSTRING('abcdefg',-1);                                     -> 'g'
SELECT SUBSTRING('abcdefg' FROM -2 FOR 2);                          -> 'fg'
SELECT SUBSTRING_INDEX('a.a.a.b.b', '.', 2);                        -> 'a.a' 按分隔符截取
SELECT SUBSTRING_INDEX('a.a.a.b.b', '.', -2);                       -> 'b.b'
SELECT UPPER('aaa');                                                -> 'AAA'
LIKE(同理有NOT LIKE)
    % 代表任意数量的字符
    _ 代表任意一个字符
    SELECT 'a' LIKE 'a ', 'a' = 'a ';                               -> 0 1
    SELECT 'abc' LIKE 'ab_';                                        -> 1
    SELECT 'abcd' LIKE '%b%';                                       -> 1
    SELECT 'abc' LIKE 'abdc' ESCAPE 'd';                            -> 1
    SELECT 'abc' LIKE 'ABC';                                        -> 1
    SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;    -> 1
    SELECT 100000 LIKE '1%';                                        -> 1
STRCMP(比较字符串大小)
    SELECT STRCMP('ab', 'ab');                                      -> 0 (相等)
    SELECT STRCMP('ab', 'ac');                                      -> -1 (前者比后者小)
    SELECT STRCMP('ac', 'ab');                                      -> 1 (后者比前者小)
正则表达式
    .   表示任意单个字符
    +   表示至少一个字符
    [abc] [a-z] [0-9]   多个选一个
    *   表示任意个之前的字符 [0-9]*任意个数字  .*任意个任意值
    ^   表示开头
    $   表示结尾
    ?   0个或者1个字符
    |   或者
    {2,3}   2个到3个字符
    SELECT 'aaaaa' REGEXP 'a*';                                     -> 1
    SELECT REGEXP_INSTR('abcdefg', 'bc');                           -> 2
    SELECT REGEXP_INSTR('abcdefg', 'b.*');                          -> 2
    SELECT REGEXP_INSTR('abcdefg', 'bc', 3);                        -> 0
    SELECT REGEXP_LIKE('abc', 'ABC', 'c');                          -> 0 (区分大小写)
    SELECT REGEXP_LIKE('abc', 'ABC');                               -> 1
    SELECT REGEXP_REPLACE('abc', 'b.*', '|');                       -> 'a|'
    SELECT REGEXP_REPLACE('abcbcd', 'b.*', '|', 4, 1);              -> 'abc|' (从第4个位置开始，替换1个满足匹配的子字符串)
    SELECT REGEXP_SUBSTR('123, 456', '[1-9]+');                     -> '123'
    • 数字函数
    SELECT ABS(-1);                                                     -> 1
SELECT CEILING(1.23);                                               -> 2
SELECT FLOOR(1.23);                                                 -> 1
SELECT MOD(3, 2);                                                   -> 1
SELECT POW(2,2);                                                    -> 4 (次方)
SELECT SQRT(4);                                                     -> 2 (开方)
SELECT RAND();                                                      -> 返回0到1之间的随机数
SELECT RAND(3);                                                     -> 指定相同的seed，随机数不变
SELECT ROUND(1.298);                                                -> 1 (四舍五入)
SELECT ROUND(1.298, 2);                                             -> 1.30 (指定小数位数)
SELECT TRUNCATE(1.223,1);                                           -> 1.2 (截取小数位数)
    • 日期和时间函数
    SELECT DATE_ADD('2000-01-01', INTERVAL 1 MONTH);                    -> 2000-02-01
SELECT ADDDATE('2000-01-01', INTERVAL 1 MONTH);                     -> 2000-02-01
SELECT ADDDATE('2000-01-01', -1);                                   -> 1999-12-31

SELECT DATE_SUB('2000-01-01', INTERVAL 1 SECOND);                   -> 1999-12-31 23:59:59
SELECT SUBDATE('2008-01-02 12:00:00', 31);                          -> 2007-12-02 12:00:00

SELECT DATE('2000-01-01 00:00:00');                                 -> 2000-01-01 提取日期部分
SELECT DATEDIFF('2000-01-01 00:00:00', '2000-02-01 00:00:00');      -> -31

SELECT CURDATE();                                                   -> 2000-01-01 获取当前日期
SELECT CURDATE() + 0;                                               -> 20000101

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y\%m\%d');              -> 2009\10\04
SELECT DATE_FORMAT('2000-01-01',GET_FORMAT(DATETIME,'ISO'));        -> 2000-01-01 00:00:00 格式化
SELECT DAY('2000-01-10');                                           -> 10

SELECT NOW();                                                       -> 2000-01-02 00:00:00获取当前日期和时间
SELECT NOW() + 0;                                                   -> 20000102000000


SELECT TIME('2003-12-31 01:02:03');                                 -> 01:02:03 提取时间部分
SELECT TIMEDIFF('2000:01:01 00:00:00','2000:01:01 00:00:00.000001');-> 46:58:57.999999

SELECT CURTIME();                                                   -> 00:00:00
SELECT CURTIME() + 0;                                               -> 000000

SELECT ADDTIME('2000-01-01 00:00:00.000000', '1 1:1:1.000001');     -> 2000-01-02 01:01:01.000001
SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');      -> '2007-12-30 22:58:58.999997'

SELECT TIMESTAMP('2003-12-31 12:00:00','2:00:00');                  -> 2003-12-31 14:00:00
SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');                         -> 2003-01-02 00:01:00
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');              -> 3
SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');                  -> 100 100 04 04 4
SELECT TIME_TO_SEC('22:23:00');                                     -> 80580


SELECT PERIOD_ADD(200801,2);                                        -> 200803
SELECT PERIOD_DIFF(200802,200703);                                  -> 11


SELECT MONTHNAME('2008-02-03');                                     -> February 获取月份名称
SELECT DAYNAME('2000-01-01');                                       -> Saturday
SELECT DAYOFWEEK('2000-01-01');                                     -> 7 同上
SELECT DAYOFMONTH('2000-01-01');                                    -> 1
SELECT DAYOFYEAR('2000-10-01');                                     -> 275 返回一年中的第几天
SELECT HOUR('2000-01-01 10:11:12');                                 -> 10
SELECT MINUTE('2008-02-03 10:05:03');                               -> 5
SELECT MICROSECOND('12:00:00.123456');                              -> 123456 获取微秒
SELECT QUARTER('2008-04-01');                                       -> 2 获取季度
SELECT SECOND('10:05:03');                                          -> 3 获取秒
SELECT WEEK('2008-02-20');                                          -> 7
SELECT YEAR('2000-01-01');                                          -> 2000


SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');              -> 200907 截取
SELECT LAST_DAY('2000-01-01');                                      -> 2000-01-31 当月最后一天的日期

SELECT MAKEDATE(2000,200);                                          -> 2000-07-18 获取当年第200天的日期
SELECT MAKETIME(12,15,30);                                          -> 12:15:30

SELECT SEC_TO_TIME(2378);                                           -> 00:39:38 将秒转化成time
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');                         -> 2013-05-01 转化成日期

SELECT NOW(), SLEEP(2), NOW();                                      -> 2000-01-01 00:00:00 |        0 | 2000-01-01 00:00:00
SELECT SYSDATE(), SLEEP(2), SYSDATE();                              -> 2000-01-01 00:00:00 |        0 | 2000-01-01 00:00:02
    • 聚合函数
    select avg(num) from mynumber group by num;                         -> 取平均值
select count(num) from mynumber group by num;                       -> 计数
select count(distinct num) from mynumber group by num;
select group_concat(num) from mynumber1 group by id;                -> 1,2 将分组的列值拼接成一个字符串
select group_concat(
    distinct num order by num desc separator '|'
) from mynumber group by id;                                        -> 2|1  对分组的记录去重，排序，设置分隔符
select json_arrayagg(num) from mynumber1 group by id;               -> [1, 2] 将结果聚合成json数组
select json_objectagg(id,num) from mynumber1 group by id;           -> {"1": 2} 将结果聚合成json对象
select min(num), max(num) from mynumber1 group by id;               -> 取最值
select sum(num) from mynumber1 group by id with rollup;             -> 计算分组的和，并且通过with rollup得到总的值

select 
    id, num,sum(num), GROUPING(id),GROUPING(num) 
from 
    mynumber1 
group by 
    id, num 
with rollup;                                                        -> with rollup 用在多个聚合条件的情况下，会按照每一个条件的分组做汇总
    结果如下：
    +------+------+----------+--------------+---------------+
    | id   | num  | sum(num) | GROUPING(id) | GROUPING(num) |
    +------+------+----------+--------------+---------------+
    |    1 |    1 |        1 |            0 |             0 |
    |    1 |    2 |        2 |            0 |             0 |
    |    1 | NULL |        3 |            0 |             1 |
    |    2 |    3 |        3 |            0 |             0 |
    |    2 |    4 |        4 |            0 |             0 |
    |    2 | NULL |        7 |            0 |             1 |
    |    3 |    5 |        5 |            0 |             0 |
    |    3 | NULL |        5 |            0 |             1 |
    | NULL | NULL |       15 |            1 |             1 |
    +------+------+----------+--------------+---------------+
    GROUPING 可以用来指示哪一个聚合条件的数据参与汇总了，如上表中的NULL，GROUPING就会返回1，反之返回0
    如果你不想要NULL，可以自定义显示内容，sql如下
select 
    if(grouping(id), '所有的id', id), 
    if(grouping(num), '所有的num', num), 
    sum(num) 
from 
    mynumber1 
group by 
    id, num 
with rollup;
    结果如下
    +----------------------------------+-------------------------------------+----------+
    | if(grouping(id), '所有的id', id) | if(grouping(num), '所有的num', num) | sum(num) |
    +----------------------------------+-------------------------------------+----------+
    | 1                                | 1                                   |        1 |
    | 1                                | 2                                   |        2 |
    | 1                                | 所有的num                           |        3 |
    | 2                                | 3                                   |        3 |
    | 2                                | 4                                   |        4 |
    | 2                                | 所有的num                           |        7 |
    | 3                                | 5                                   |        5 |
    | 3                                | 所有的num                           |        5 |
    | 所有的id                         | 所有的num                           |       15 |
    +----------------------------------+-------------------------------------+----------+

select 
    *, ROW_NUMBER() over w AS 'row_number', 
from 
    mynumber1                                                       
window w as (order by id);                                          -> mysql8的新特性window函数   
上面讲过的聚合函数都是对最后的结果做一个汇总操作，而window能够对每一条记录做操作
select 
    id, num , 
    sum(num) over(),                                                -> 不带参数的over就是整个记录
    sum(num) over(partition by id)                                  -> 通过partition可以指定分组依据
    ROW_NUMBER() OVER(PARTITION BY id) 
from 
    mynumber1 order by id, num; 
    结果如下
    +------+------+-----------------+--------------------------------+------------------------------------+
    | id   | num  | sum(num) over() | sum(num) over(partition by id) | ROW_NUMBER() OVER(PARTITION BY id) |
    +------+------+-----------------+--------------------------------+------------------------------------+
    |    1 |    1 |              15 |                              3 |                                  1 |
    |    1 |    2 |              15 |                              3 |                                  2 |
    |    2 |    3 |              15 |                              7 |                                  1 |
    |    2 |    4 |              15 |                              7 |                                  2 |
    |    3 |    5 |              15 |                              5 |                                  1 |
    +------+------+-----------------+--------------------------------+------------------------------------+

  4. sql语句（不区分大小写）

    • select语句
        SELECT VERSION(), USER(), CURRENT_DATE; 
    SELECT SIN(PI()/4), (4+1)*5;
    where字句的使用
        select * from pets where 主人='Gwen'
        select * from pets where 出生日期 >= '1998-09-11';
        select * from pets where 种类='dog' and 性别 = 'f';
        select * from pets where 种类='dog' or 种类 = 'snake';
        select * from pets where (种类 = 'cat' and 性别 = 'm') or (种类 = 'dog' and 性别 = 'f');
    指定列
        select 宠物名称, 出生日期 from pets;
        select 宠物名称, 种类, 出生日期 from pets where 种类='dog' or 种类 = 'cat';
    去重
        select distinct 主人 from pets;
    排序不区分大小写 
        select 宠物名称, 出生日期 from pets order by 出生日期;
    排序区分大小写 
        select * from pets order by binary 宠物名称;
    降序
        select * from pets order by 出生日期 desc;
    多列排序
        select 宠物名称, 种类, 出生日期 from pets order by 种类, 出生日期 desc;
    时间间隔
        select 宠物名称, 出生日期, curdate(), timestampdiff(year, 出生日期, curdate()) as age from pets;
        select 宠物名称, 出生日期, 死亡日期, timestampdiff(year, 出生日期, 死亡日期) as age from pets where 死亡日期 is not null order by age;
        select 宠物名称, 出生日期,year(出生日期), month(出生日期),dayofmonth(出生日期) from pets;
        月份加一月 
            select 宠物名称, 出生日期 from pets where month(出生日期) = month(date_add(curdate(), interval 1 month));
            select 宠物名称, 出生日期 from pets where month(出生日期) = mod(month(curdate()), 12) + 1;
    空值
        select 1 is null, 1 is not null;
    标准模式匹配
        _   代表任意单个字符
        %   代表任意多个字符
        select * from pets where 宠物名称 like 'b%';
        select * from pets where 宠物名称 like '_____';
    扩展模式匹配
        .       表示任意单个字符
        [abc] [a-z] [0-9]   多个选一个
        *       表示任意个之前的字符 [0-9]*任意个数字      .*任意个任意值
        ^       表示开头
        $       表示结尾
        select * from pets where regexp_like(宠物名称, '^b');
        select * from pets where regexp_like(宠物名称, '^.{5}$');
        大小写敏感
            select * from pets where regexp_like(宠物名称, binary '^B');    
            select * from pets where regexp_like(宠物名称,'^B','c');
    获取个数
        select count(*) from pets;
        select 主人, count(*) from pets group by 主人;
        select 种类, 性别, count(*) from pets group by 种类, 性别;
    inner join的使用
        select pets.宠物名称, timestampdiff(year, 出生日期, eventdate) as age, remark 
        from pets inner join event on 
        pets.宠物名称 = event.petname 
        where event.eventtype = 'litter';

        select p1.宠物名称, p1.性别, p2.宠物名称, p2.性别, p1.种类 
        from pets as p1 inner join pets as p2 on 
        p1.种类 = p2.种类 and p1.性别 = 'f' and p2.性别 = 'm';
    获取最大值
        select Max(article) as article from shop;
        select * from shop where price = (select Max(price) from shop);
        select article, max(price) as price from shop group by article;

        可以使用如下语句代替

        SELECT s1.article, s1.dealer, s1.price
        FROM shop s1
        LEFT JOIN shop s2 ON s1.price < s2.price
        WHERE s2.article IS NULL;

        select * from shop order by price desc limit 1;
    • insert语句
    INSERT INTO pets VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
    • delete语句
    清空表中数据  DELETE FROM pets; <=> TRUNCATE TABLE pets;
    • update语句
    update pets set 出生日期='1989-08-31' where 宠物名称='Bowser';
    • union 语句
    select * from shirt where id = '2'
union
select * from shirt where id = '3'
    • event
    创建一个event
    create event 
        myevent 
    on schedule
        every 1 second 
    comment 
        'A sample comment.' 
    do 
        insert into test.mynumber values(100);
修改event 
    alter event 
        myevent 
    on schedule 
        every 1 second starts current_timestamp + interval 1 minute 
    do 
        update test.mynumber set num = SECOND(current_timestamp);
禁用event
    ALTER EVENT myevent DISABLE; 
修改event名称
    ALTER EVENT myevent RENAME TO mynewevent;
移动event
    ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
    • alter表格
    删除列 alter table mynumber1 drop column id;
重置自增起始值 alter table mynumber1 AUTO_INCREMENT = 10;
修改表编码
    show create table mynumber1;
    ALTER TABLE mynumber1 CHARACTER SET = utf8;
    • 存储过程
    创建存储过程
    CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
    BEGIN
        # Set value of OUT parameter
        SELECT VERSION() INTO ver_param;
        # Increment value of INOUT parameter
        SET incr_param = incr_param + 1;
    END;
    INOUT的变量需要初始化
    OUT的变量不需要初始化

调用存储过程
    SET @increment = 10;
    CALL p(@version, @increment); 
    SELECT @version, @increment;

    SET @increment = 10;
    PREPARE s FROM 'CALL p(?, ?)';
    EXECUTE s USING @version, @increment;
    SELECT @version, @increment;
    • with语句
    合并集合
WITH cte (col1, col2) AS
(
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
)
SELECT col1, col2 FROM cte;
    • 事务
    start transaction
select @A:=sum(num) from mynumber;
insert into mynumber values(@A);
commit;
    • 触发器
    创建触发器
    CREATE TRIGGER 
        triggername 
    BEFORE                                                                  # AFTER
        INSERT                                                              # DELETE and UPDATE
    ON 
        tablename
    FOR EACH ROW SET @sum = @sum + NEW.tablecolumn;

如果触发器同名，可以指定先后顺序
    FOR EACH ROW PRECEDES
    FOR EACH ROW FOLLOWS 

使用触发器
    SET @sum = 0;
    INSERT INTO ...
    SELECT @sum;

删除触发器
    DROP TRIGGER triggername;
    • 视图
    创建视图
    CREATE TABLE t (qty INT, price INT);
    INSERT INTO t VALUES(3, 50), (5, 60);
    CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
    SELECT * FROM v;

  5. 外键（只是一个memo）

    外键不会创建index，只是一个标记而已
CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

# ALTER TABLE person AUTO_INCREMENT = 100;

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

  6. 全文搜索

    CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
)

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

自然语言全文搜索

    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

    其中MATCH的参数和建表时的FULLTEXT参数一致，全文搜索起到一个快速检索和相关性排序的功能

    SELECT COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count FROM articles;

    上面这条语句，避免了相关性排序的操作
Boolean 全文搜索

    SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); 查找记录中包含MySQL但是不包含YourSQL

转载于:https://www.cnblogs.com/ye-hcj/p/9530396.html

weixin_30955341
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
自学MySql内置函数知识点总结
12-14
以下是对标题和描述中涉及的一些关键知识点的详细说明： 1. **字符串函数**： - `ASCII(str)`：返回字符串`str`的第一个字符的ASCII码值，空串返回0。 - `CHAR(num)`：将ASCII码值`num`转换为对应的字符。 - `...
SQL Server 限制IP登陆（登陆触发器运用）
weixin_33743703的博客
05-23 302
一.本文所涉及的内容（Contents） 本文所涉及的内容（Contents） 背景（Contexts） 实现代码（SQL Codes） 补充说明（Addon） 疑问（Questions） 参考文献（References） 二.背景（Contexts） 　　在MySQL的mysql.User表保存了登陆用户的权限信息，Host和User字段则是关于登陆IP的限制。但是在SQ...
linux5.6以下版本的不兼容问题
weixin_33912246的博客
06-22 142
之前一直用的都是mysql5.6版本，最近突然使用到了mysql5.1版本，于是在导入数据的时候便出现了很多由于版本不兼容的问题。   1.mysql5.1没有datetime类型，所以对于时间类型，只能使用timestamp 例： 1 `FRecordTime` datetime DEFAULT CURRENT_TIMESTAMP 需要改为 1 `FRecordTime` timestam...
java jdbc 报错SET OPTION SQL_SELECT_LIMIT=10;
weixin_33804582的博客
07-13 316
2019独角兽企业重金招聘Python工程师标准>>> ...
Mysql实用知识库分享
11-10
MySQL实用知识库分享 MySQL是一种关系型数据库管理系统，本知识库分享涵盖了MySQL的多个方面，包括查询数据、约束、数据排序与分组、外键、索引、子查询、多表查询、常用函数、数据库设计规范、排名实现等。 一、 ...
MySQL实用教程课后答案
12-11
### MySQL实用教程知识点总结 #### 第1章 数据库的基本概念 - **数据库**: 是长期存储在计算机内、有组织的、可共享的数据集合。 - **数据模型**: 包括数据结构、数据操作和数据完整性约束三部分内容。 - **关系...
MYSQL实用,常用命令总结
06-21
根据给定的文件信息，以下是对“MYSQL实用,常用命令总结”的详细知识点解析： ### MySQL 基础操作 #### 数据插入（Insert） 在MySQL中，`INSERT`语句用于向表中添加新记录。例如，若想在不指定列的情况下插入数据...
mysql数据库实用教程课后习题及章总结
06-04
MySQL数据库是一种广泛使用的开源关系型数据库管理系统，它具有丰富的功能和...以上是MySQL数据库实用教程中涉及的主要知识点，这些内容对于学习和使用MySQL数据库进行数据管理至关重要，同时也适用于考试复习和预习。
OPTION 子句 (Transact-SQL)
我想我是海 冬天的大海 心情随风轻摆
10-28 3093
OPTION 子句 (Transact-SQL) 2017/03/16 作者  本主题适用于：SQL Server（从 2008 开始）Azure SQL 数据库Azure SQL 数据仓库并行数据仓库 指定应在整个查询中使用所指定的查询提示。 每个查询提示只能指定一次，但允许指定多个查询提示。 使用该语句只能指定一个 OPTION 子句。 可以
SQL SERVER 函数
weixin_30825581的博客
12-17 1427
决定还是先帖简单的，时间日期函数 1.DATEADD在向指定日期加上一段时间的基础上，返回新的 datetime 值。 语法DATEADD ( datepart , number, date ) 参数datepart 是规定应向日期的哪一部分返回新值的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。 日期部分 缩写 Year...
quartz报错：OPTION SQL_SELECT_LIMIT=1
h691738337的博客
06-05 1118
[2017-11-30 09:13:49.366][ERROR][ErrorLogger.schedulerError] An error occurred while scanning for the next triggers to fire. org.quartz.JobPersistenceException: Couldn't acquire next trigger: You hav...
ASP基于BS结构的学生在线选课系统的实现(源代码+论文).rar
08-10
资源包主要包含以下内容： ASP项目源码：每个资源包中都包含完整的ASP项目源码，这些源码采用了经典的ASP技术开发，结构清晰、注释详细，帮助用户轻松理解整个项目的逻辑和实现方式。通过这些源码，用户可以学习到ASP的基本语法、服务器端脚本编写方法、数据库操作、用户权限管理等关键技术。 数据库设计文件：为了方便用户更好地理解系统的后台逻辑，每个项目中都附带了完整的数据库设计文件。这些文件通常包括数据库结构图、数据表设计文档，以及示例数据SQL脚本。用户可以通过这些文件快速搭建项目所需的数据库环境，并了解各个数据表之间的关系和作用。 详细的开发文档：每个资源包都附有详细的开发文档，文档内容包括项目背景介绍、功能模块说明、系统流程图、用户界面设计以及关键代码解析等。这些文档为用户提供了深入的学习材料，使得即便是从零开始的开发者也能逐步掌握项目开发的全过程。 项目演示与使用指南：为帮助用户更好地理解和使用这些ASP项目，每个资源包中都包含项目的演示文件和使用指南。演示文件通常以视频或图文形式展示项目的主要功能和操作流程，使用指南则详细说明了如何配置开发环境、部署项目以及常见问题的解决方法。 毕业设计参考：对于正在准备毕业设计的学生来说，这些资源包是绝佳的参考材料。每个项目不仅功能完善、结构清晰，还符合常见的毕业设计要求和标准。通过这些项目，学生可以学习到如何从零开始构建一个完整的Web系统，并积累丰富的项目经验。
基于SSM的高校实验室预约管理系统的设计与实现源码
08-10
本系统结合当前实验室预约、使用和管理中存在的问题和现状进行分析，设计一套可以解决实验室使用冲突、预约流程简单的实验室预约管理系统，满足实验室的在线预约申请与审核的基本需求，可以方便对实验室和实验室设备进行信息化管理。本系统中的教师可以查看当前实验室以及设备的基本信息，在线进行实验室的预约。管理员可以对实验室和实验室的设备进行管理，以及实验室的预约申请进行审核。
毕业设计javajsp超市会员管理系统ssm源码含文档工具包
08-10
毕业设计javajsp超市会员管理系统ssm源码含文档工具包 后台是ssm框架，页面是jsp，数据库mysql，jdk1.8，开发工具用ecplise、myecplise、sts、idea都可以 会员管理 公告育理 评论管理 前台 邮箱管理 积分管理 帮动 修改密码 退出系统 会员管理 公告管理 评论管理 工具管理 包含：源码、数据库脚本、论文、答辩ppt、开题报告、环境工具包、相同框架项目的安装教程（在说明文档中）
STM32F103C8T6 CAN通讯HAL库代码，采用中断接收，含Stm32CubeMX工程
最新发布
08-10
STM32F103C8T6 CAN通讯HAL库例程代码，采用中断接收，含Stm32CubeMX工程
MySQL精华总结：从入门到精通必备知识点与实战面试技巧
本文档深入介绍了MySQL的相关知识点，包括SQL的概述、优点和分类，以及在数据库设计和管理中的关键概念。首先，SQL（Structured Query Language）作为关系数据库的标准语言，用于创建、操作和管理数据，其优点在于...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值