-- SELECT * FROM tb_dept t,time tt WHERE tt.id=3 ;
1、
SELECT id FROM tb_dept WHERE id =3 UNION ALL SELECT id FROM time WHERE id=3;
CREATE TABLE `employee_tbl` ( `id` int(11) NOT NULL, `name` char(10) NOT NULL DEFAULT '', `date` datetime NOT NULL, `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、 插入多条数据
INSERT INTO alter_test(name,created,updated) VALUES('Tina',SYSDATE(),SYSDATE()),('Jame',SYSDATE(),SYSDATE());
SELECT * FROM employee_tbl;
3、查询分组并统计总数用
with ROLLUP SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
4、 mysql正则
-- 查找name字段中以'ok'为结尾的所有数据匹配是REGEXP 不匹配前面加上not
SELECT dept FROM tb_dept WHERE dept REGEXP 'R$';
5、事务：
-- 原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）
CREATE TABLE `aa` ( `id` int NOT NULL AUTO_INCREMENT , `column_name` varchar(20) NULL , PRIMARY KEY (`id`) );
6、alter
-- 重命名
ALTER TABLE aa RENAME TO alter_test;
-- 修改列名
ALTER TABLE alter_test CHANGE column_name name VARCHAR(10);
-- 显示表的状态
SHOW TABLE STATUS LIKE 'alter_test';
-- 修改字段类型
ALTER TABLE alter_test MODIFY name datetime;
7、 索引
-- 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
--
-- 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
--
-- 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
--
-- 建立索引会占用磁盘空间的索引文件。
-- 删除索引是drop
-- 索引失效： 1、.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)；要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
-- 2、对于多列索引，不是使用的第一部分(第一个)，则不会使用索引；
-- 3、like查询是以%开头；
-- 4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；
-- 5、查询条件使用函数在索引列上，或者对索引列进行运算；
-- 6、not in ,not exist
-- 全值匹配我最爱，最左前缀要遵守；
-- 带头大哥不能死，中间兄弟不能断；
-- 索引列上少计算，范围之后全失效；
-- LIKE百分写最右，覆盖索引不写星；
-- 不等空值还有or，索引失效要少用。
-- left join（左连接）：右表创建索引。
-- right join（右连接）：左表创建索引。
-- 简记：左右外连接，索引相反建（left：右表建，right：左表建）。
8、复制表
CREATE TABLE test SELECT * FROM alter_test where 1=2; /*复制表结构*/ CREATE TABLE test1 SELECT * FROM alter_test /*复制表结构和数据*/
9、获取服务器元数据
-- SELECT VERSION( ) 服务器版本信息
-- SELECT DATABASE( ) 当前数据库名 (或者返回空)
-- SELECT USER( ) 当前用户名
-- SHOW STATUS 服务器状态
-- SHOW VARIABLES 服务器配置变量
10、统计重复数据
SELECT * FROM test1 GROUP BY NAME HAVING COUNT(*) >1;
-- 如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
-- 删除重复数据 复制表（结构和数据），然后删除原表，再修改临时表名称
CREATE TABLE temp SELECT *FROM test1 GROUP BY name; DROP TABLE test1;
ALTER TABLE temp RENAME TO test1;
-- 11、导出数据 导出路径在mysql安装盘的data/数据库名/文件名
SELECT * FROM test1 INTO OUTFILE 'test.txt';
12、 导入数据
-- mysql 命令导入；mysql -uroot -p123456 < sql的文件位置； -- source命令导入：mysql> create database abc; # 创建数据库 -- mysql> use abc; # 使用已创建的数据库 -- mysql> set names utf8; # 设置编码 -- mysql> source sql文件的位置 # 导入备份数据库 -- LOAD DATA 导入数据:LOAD DATA LOCAL INFILE 'D:/mysql/data/xiaojie/test.txt' INTO TABLE test;、 -- mysqlimport 导入数据
13、sql函数
ASCII(str):返回字符串 s 的第一个字符的 ASCII 码。eg:
SELECT ASCII(name) FROM test;
CHAR_LENGTH(str):返回字符数。eg :
SELECT CHAR_LENGTH(name) FROM test;
CHARACTER_LENGTH(str):返回字符数. eg:
SELECT CHARACTER_LENGTH(name) FROM test;
CONCAT(str1,str2,...) :字符串 s1,s2 等多个字符串合并为一个字符串。eg：
SELECT CONCAT('xiaoming','xiaoli','xiaohua') as NAME;
CONCAT_WS(separator,str1,str2,...):字符串连接 但是中间有个分隔符 eg:
SELECT CONCAT_WS('-','xiaoming','xiaoli','xiaohua') as NAME;
FIELD(str,str1,str2,str3,...) 返回第一个字符串 str 在字符串列表(str1,str2...)中的位置;
SELECT FIELD('miss','miss','start','skssed','miss','miss');
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配到第一个字符串的位置;
SELECT FIND_IN_SET('b','a,b,c,d,e,f,b');
FORMAT(X,D):函数将数字x格式化保留小数后d位，最后一位四舍五入;SELECT FORMAT('2541231.23549',2); 输出结果--------2,541,231.24; INSERT(str,pos,len,newstr):用newstr替换字符串str从pos开始长度为len的字符串； SELECT INSERT("www.baidu.com",2,6,"sina");----------wsinadu.com; LOCATE(s1,s):从s中获取s1第一次出现的位置： SELECT LOCATE('china','ilovechina');-----------6 LCASE(s)/LOWER(s):将 s转小写字母： SELECT LCASE('ILOVEYOU'); LEFT(str,len):选取字符串左侧的len个字符； SELECT LEFT('china','3'); LPAD(str,len,padstr):用padstr添加到字符串(前面添加)str是字符长度到len: SELECT LPAD('aa',5,'bb');-----bbbaa; LTRIM(str):去掉字符串str开始处的空格;SELECT LTRIM(' study day day up');----------study day day up MID(s,n,len)/SUBSTR(s, start, length)/SUBSTRING(s,n,len):从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len);SELECT MID('ilove',2,4);-----------love; POSITION(s1 IN s):从字符串 s 中获取 s1 的开始位置:SELECT POSITION('s' IN 'sabcdsdyu'); REPEAT(str,count): 将字符串str重复几次： SELECT REPEAT ('a',5); REPLACE(s,s1,s2):将字符串 s2 替代字符串 s 中的字符串 s1: SELECT REPLACE('goodgoodstudy','o','a');--------gaadgaadstudy; REVERSE(s):倒置字符串顺序; SELECT REVERSE('abcd'); `RIGHT`(str,len):返回字符串右边的len长度字符： SELECT RIGHT('good',1); TRIM(s):去掉两端的空格： SELECT TRIM(' ssss '); UCASE(str)/UPPER(str):转字符为大写; SELECT UPPER('xioaming'); /*************************数字函数***********************************/ ABS(X)：绝对值 ACOS(X)等三角函数:求反余弦值; SELECT ACOS(1); CEIL(X)/CEILING(X):向上取整 SELECT CEIL(-1.5); DEGREES(X):将弧度数转为角度：SELECT DEGREES(3.14159365358979); n DIV m :SELECT 10 DIV 2;----5; EXP(X):求自然数e的x次幂的值； FLOOR(X):向下取整； SELECT FLOOR(-1.5); GREATEST(value1,value2,...): 返回最大值； LEAST(value1,value2,...)：返回最小值； POW(X,Y)/POWER(X,Y):返回x的y次幂； RAND():返回0-1的随机数; SELECT RAND(); ROUND(X):返回距离x最近的整数： SELECT ROUND(-2.89); SQRT(X):返回x的平发根： SELECT SQRT(4); TRUNCATE(X,D) 对x保留小数 不会四舍五入 SELECT TRUNCATE(2.654523654,1); /*************************日期函数***************************************************/ ADDDATE(d,n): 在当前日期增加几天 SELECT ADDDATE('2020-01-04',3); ADDTIME(d,n):在当期时间加上几秒 SELECT ADDTIME('2020-01-07 16:04:25',35); CURRENT_DATE()/CURDATE():返回当前日期 SELECT CURDATE();------2020-01-07; `CURRENT_TIME`()/CURDATE();当前时间 SELECT CURRENT_TIME(); `CURRENT_TIMESTAMP`()；当前日期和时间 SELECT CURRENT_TIMESTAMP(); DATEDIFF(d1,d2):返回时间差天数d1-d2； SELECT DATEDIFF('2019-01-01','2020-01-01'); DATE_FORMAT(d,f);格式化时间； DATE_SUB(date,INTERVAL expr type) 当前日期减去： SELECT DATE_SUB(NOW(),INTERVAL 2 DAY); DAYNAME(date): 返回周几 ； DAYOFMONTH(date)： 计算是本月的第几天； SELECT DAYOFMONTH(NOW()); `DAY`(date): 返回天数； PERIOD_DIFF(P1,P2) 返回月份差： SELECT PERIOD_DIFF('2020-01-01','2020-01-02'); SEC_TO_TIME(seconds)将秒数改成时分秒： SELECT SEC_TO_TIME(120); STR_TO_DATE(str,format):将字符串转为时间格式： SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); /*****************************高级函数***********************************/ BIN(N):放回二进制编码 ： SELECT BIN(3); CONNECTION_ID():服务器的连接数: SELECT CONNECTION_ID(); IF(expr,v1,v2):如果满足表达式值为v1 不然值为v2 SELECT IF(1>2,1,2); ------2 IFNULL(v1,v2);如果为null v1不然v2 ; NULLIF(v1,v2):比较两个字符串相等返回null 不然返回v1: SELECT NULLIF('a','b'); SESSION_USER();当前用户： SELECT SESSION_USER(); --------root@localhost；
