mysql常用 总结(全)

-- ====================================
-- 以下命令需要在装有mysql服务的Linux上面运行：

-- 将数据库m中的所有数据导出为m.sql文件
-- mysqldump --opt m > m.sql
-- mysql < m.sql 导入数据

-- 查看mysql的版本
-- SELECT VERSION();

-- 查看当前时间
-- SELECT `CURRENT_DATE`();

-- 显示当前选择的数据库
-- SHOW `DATABASE`();

-- 查看当前数据库的所有表
-- SHOW TABLES;

-- 显示标的结构
-- DESCRIBE mytable;

-- ====================================

-- 显示标的结构
DESCRIBE `user`;

-- 查看mysql的版本
SELECT VERSION();

SELECT * FROM `user`;
-- 插入值，一次性可以插入多条
INSERT INTO `user` (age, name, address, country, number) 
VALUES
(67, 'n', '河北', '中国', 133), 
(55, 'k', 'cs', '美国', 133),
(55, 'r', '河北', '英国', 133);
-- 插入时间
INSERT INTO `user` (time) VALUES (NOW());
-- 将时间为空的列更新为最新时间
UPDATE `user` time=NOW() WHERE time=NULL;

-- 设置时间为特定时间， 用date_sub 和 date_add来将时间向前向后推移 
-- 从当前时间开始计算，并修改
UPDATE `user` c SET  c.time = DATE_SUB(NOW(),INTERVAL 60 SECOND) WHERE id=30;
-- 从原有时间开始计算，并修改
UPDATE `user` c SET  c.time = DATE_SUB(c.time,INTERVAL 10 DAY) WHERE id=30;
-- 只是算结果，不修改原来的时间
SELECT u.time, DATE_ADD(u.time,INTERVAL 10 DAY) AS new_time FROM `user` AS u WHERE u.id = 30;

-- NULL的重置与查询 
-- null 和 '' 的不同，back键='' ， delete键=null
UPDATE `user` SET address='' WHERE id=38;
UPDATE `user` SET address=NULL WHERE id=38;
SELECT * FROM `user`  AS u WHERE u.address IS NULL;
-- SELECT * FROM `user`  AS u WHERE u.address IS NOT NULL; -- 可以排除null
-- SELECT * FROM `user` AS u WHERE u.address='Null'; -- 不能查到结果
SELECT * FROM `user` AS u WHERE u.address='';

-- 查询表的结构
DESC `user`;
-- 查询id在2， 6， 7这三个数中的值
SELECT * FROM `user` WHERE id IN (2, 6, 7);
-- 查询id小于等于7的值，可用符号（ >,=,<,<>,!=,!>,!<,=>,=< ）
SELECT * FROM `user` WHERE id <= 7;

-- 按照country分类统计对应address的个数
SELECT u.country, COUNT(u.address) FROM `user` AS u GROUP BY u.country;

-- count=累计， 查询country中不重复的元素，并统计不重复元素的个数
SELECT COUNT(u2.country) FROM (SELECT DISTINCT u1.country FROM `user` as u1) AS u2;
-- sum=累加，去掉age重复元素后所有元素的和
SELECT SUM(u2.age) FROM (SELECT DISTINCT u1.age FROM `user` as u1) AS u2;
-- 不去重age列所有元素的和
SELECT SUM(u.age) FROM `user` AS u;
-- BETWEEN
SELECT * FROM `user` WHERE id BETWEEN 2 AND 6;

-- `TRUNCATE`(X,D)删除表中的数据
        TRUNCATE TABLE `user1` ;
        DROP TABLE user1;

-- null值转化为0求和（不转换统计将会出错）
        SELECT SUM(ping.number + ping.p) FROM (SELECT ps.number, IFNULL(ps.age,0) AS p FROM `user` AS ps) AS ping;

        SELECT IFNULL(ps.age,0) AS p FROM `user` AS ps

        SELECT SUM(u.age) + SUM(u.number) FROM `user` AS u; -- 推荐写这种
        SELECT SUM( + u.number)  FROM (SELECT  IFNULL(ps.age,0) FROM `user` AS ps)  AS u ;

-- 双表左右连接查询
        SELECT * from `user1` u1 LEFT JOIN `user` u ON `u`.id=u1.id;        
        SELECT * from `user1` u1 INNER JOIN `user` u ON `u`.id=u1.id;

        SELECT * FROM 

-- 联合查询()
        SELECT SUM(u1.age) + SUM(u1.number) from `user1` AS u1 LEFT JOIN `user` AS u ON `u`.id=u1.id;

        SELECT *, IFNULL(ps.age,0) FROM `user` AS ps;
        -- number去重， age不去重求和
        SELECT SUM(u2.number) + (SELECT SUM(u.age) FROM `user` AS u)  FROM (SELECT DISTINCT u1.number FROM `user` AS u1  ORDER BY u1.age DESC) AS u2;

-- 查询非空数据
SELECT * FROM `user` WHERE age <>;

-- 数学函数
        -- SIN(X)  COS(X) TAN(X)  ASIN(X) ACOS(X) ATAN(X) LOG2(X) LOG10(X) PI()
        -- 弧度转角度 DEGREES(X)
        -- 角度转弧度 RADIANS(X)
        -- 整除余数   MOD(N,M)

-- 字符串函数

        -- 合并字符串（注意null的不同）
        SELECT CONCAT(ps.address, ps.country) AS con FROM `user` AS ps WHERE id=6;
        SELECT CONCAT(ps.address, NULL) AS con FROM `user` AS ps WHERE id=6;
        SELECT CONCAT(ps.address, "NULL") AS con FROM `user` AS ps WHERE id=6;
        SELECT CONCAT(NULL, ps.address) AS con FROM `user` AS ps WHERE id=6;

        -- 比较字符串的大小
        SELECT STRCMP(ps.address,ps.country) AS con FROM `user` AS ps WHERE id=6;
        SELECT STRCMP(ps.address,NULL) AS con FROM `user` AS ps WHERE id=6;

        -- 求字符串的长度
        SELECT LENGTH(ps.address) FROM `user` AS ps WHERE ps.id=6;
        SELECT CHAR_LENGTH(ps.address) FROM `user` AS ps WHERE ps.id=6;

        -- 字符串大小写的转换
        SELECT UPPER(ps.address), LOWER(ps.address) FROM `user` AS ps WHERE ps.id=6;
        SELECT UCASE(ps.address), LCASE(ps.address) FROM `user` AS ps WHERE ps.id=6;
        SELECT UPPER(ps.country) FROM `user` AS ps WHERE ps.id=6;

        -- 查找字符串的函数
            -- 默认从1开始
        SELECT FIND_IN_SET(ps.address, "kl,rt,er,hm") FROM `user` AS ps WHERE ps.id=4;
        SELECT FIELD(ps.address,"rt","kl","rt","kf") FROM `user` AS ps WHERE ps.id=4;
        SELECT LOCATE(ps.address,"4errtrter") FROM `user` AS ps WHERE ps.id=4;
        SELECT POSITION(ps.address IN "4errtrter") FROM `user` AS ps WHERE ps.id=4;
                -- 注意位置反了
        SELECT INSTR("4errtrter", ps.address) FROM `user` AS ps WHERE ps.id=4;

        -- 获取指定位置的字符串
                -- SELECT ELT(2,"er","ff","cc","dd");
        -- 截取字符
                -- 截取左边n个字符
                        -- SELECT `LEFT`("klkl",2);
                -- 截取右边n个字符
                        -- SELECT  `RIGHT`(str,len);
                -- 从str的index 开始截取 len 长度的字符
                        -- SELECT SUBSTRING(str,pos,len)
                        -- SELECT MID(str,pos,len)
                -- 字符串替换(str要替换的字符串， from str 替换位置， to str 替换的字符串， 如果to str 为空，将是删除操作)
                        -- `REPLACE`(str,from_str,to_str) 不懂怎样用
                -- 截取字符串左侧是空格, 截取字符串右面的空格 RTRIM(), 截取两边的空格 TRIM()
                    SELECT ps.address, LTRIM(ps.address) FROM `user` AS ps WHERE id=6;
                    SELECT ps.address, TRIM(ps.address) FROM `user` AS ps WHERE id=6;

-- 日期函数

    -- 获取当前时间
            SELECT NOW();
            SELECT NOW(), SLEEP(5), NOW();
            SELECT curtime();
            SELECT SYSDATE();
            SELECT SYSDATE(), SLEEP(8), SYSDATE();
            SELECT current_timestamp;
            SELECT CURRENT_DATE;
            SELECT CURRENT_TIME;
            SELECT CURRENT_USER;
      SELECT * FROM `user` WHERE time<CURRENT_DATE;
            -- 查找时间并截取日期 
            -- date_format('1997-10-04 22:23:00','%y %M %b %D %W %a %Y-%m-%d %H:%i:%s %r %T')
            -- 结果：97 October Oct 4th Saturday Sat 1997-10-04 22:23:00 10:23:00 PM 22:23:00
            SELECT DATE_FORMAT(ps.time,'%Y-%m-%d') FROM `user` AS ps;
            SELECT DATE_FORMAT(ps.time,'%H:%i:%s') FROM `user` AS ps;
            -- 求日期对应的星期 w 是全写， a是简写
            SELECT DATE_FORMAT(ps.time,'%W') FROM `user` AS ps;
            SELECT DATE_FORMAT(ps.time,'%a') FROM `user` AS ps;
            SELECT DATE_FORMAT(NOW(),'%W');
            -- 计算当前日期是一月中的第几周
            SELECT (DAY(NOW())+WEEKDAY(NOW()-INTERVAL DAY(NOW()) DAY)) DIV 7 + 1;
            -- 计算当前时间是一年中的第几周
            SELECT WEEK(date_add(NOW(),interval 6 day),2);
    

-- 索引的创建和使用
    -- 索引是在存储引擎里面，不同的存储引擎的索引不同
    -- mysql的索引有 BTREE 和 HASH 两种， InnoDB和MyIsam存储引擎只支持btree；memory和heap存储引擎支持hash和btree索引。
    -- 索引的分类
            -- PRIMARY 主键，唯一且不能为空
            -- INDEX  普通索引
            -- UNIQUE 唯一索引，不能有重复
            -- FULLTEXT  是全文索引，用于在一篇文章中，检索文本信息的
    -- 索引的优势
            -- 加快检索速度
    -- 索引的不足
            -- 索引的创建和维护随着索引数量的创建而增加
            -- 对数据库数据增加和删除要对索引进行维护
    -- 索引的创建
        -- alter创建
             ALTER TABLE table_name ADD INDEX index_name (column_list); -- 创建普通索引
             ALTER TABLE table_name ADD UNIQUE index_name (column_list); -- 创建唯一索引
             ALTER TABLE table_name ADD  PRIMARY KEY (colum_list); -- 创建主键
        -- create创建
                CREATE INDEX index_name ON table_name(username(length)) -- 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

        -- 删除索引
            ALTER TABLE table_name DROP INDEX index_name;
            DROP INDEX index_name ON table_name;