#开启关闭服务 #管理员启动cmd，，net start/stop mysql，， #登录数据库，，mysql 【-h localhost -P 端口号】 -u 用户名 -p,, #退出数据库，，exit,, / #ctrl+c #查看版本号，，mysql -V,, ##常见语句 #查看当前所有数据库 SHOW DATABASES; #打开指定的库，，use 库名； #查看某库所有的表，，show tables； // show tables from 库名； /创建表，， create table（字段名称字段类型，字段名称字段类型）；/ #查看表结构，，desc 表名； #修改字符集 set names gbk; #查看表内容，，select * from 表名； #插入内容，，insert into 表名（id ， name） values （1，‘yangbo’）； #更新修改内容，，update 表名 set name='yb' where id=1; #删除内容，，delete from 表名 where id=1; ##不区分大小写 ##单行注释 #注释 -- 注释 ##多行注释 /* 注释 */ ###data query language/ data manipulation lan/

data define lan/transcation control lan/

###DQL数据查询语言 ##进阶1 基础查询-----------------------------------------

select (字段、常量、表达式、函数） from 表名；

USE myemployees; SELECT job_id #F12变规范 FROM jobs;

SELECT manager_id, #`` 区分系统sql关键字和字段名 first_name, #逗号自己加 email FROM employees;

SELECT * FROM employees;

SELECT 1+4;

#查询函数 SELECT VERSION();

#起别名alias 便于理解，防止重名 SELECT last_name AS 姓 , first_name AS 名 FROM employees; SELECT last_name 姓 FROM employees ; # 无AS SELECT last_name 'x #i ng' FROM employees;

#去重 SELECT DISTINCT department_id FROM employees;

+ 号的作用

SELECT 10+10; #==20 SELECT "10"+10; #==20 SELECT '1o'+10; #==11 SELECT 'oo'+10; #==10 SELECT NULL+10; #==null

#拼接文本，判断是否为null SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees; SELECT CONCAT(first_name,job_id,IFNULL(commission_pct,0)) AS infor FROM employees;

##进阶2 条件查询-------------------------------------------- /* select 查询列表 #3 from 表名 #1 where 条件 #2

条件表达 < > = != <> <= >= #逻辑表达 && || ! 连接多条件表达

and or not

#模糊查询 like between and in is null */ USE myemployees;

SELECT * FROM employees WHERE salary > 12000;

SELECT last_name, department_id FROM employees WHERE department_id <> 90;

SELECT last_name, salary FROM employees WHERE salary>=10000 AND salary<=20000;

#查询部门编号不是在90-120，或者工资大于15000的员工信息 SELECT * FROM employees #where not(department_id>=90 and department_id<=120) or salary>15000; #where department_id not between 90 and 15000 or salary>15000; WHERE NOT(department_id BETWEEN 90 AND 15000) OR salary>15000;

#模糊查询通配符

% 表示任意多个字符 _ 表示1个字符 , %%不能代表null!!!!!!!!!!

SELECT * FROM employees WHERE last_name LIKE '%a%'; #姓包含a SELECT * FROM employees WHERE last_name LIKE 'i%'; #查询第二个字符为i，注意%的使用 SELECT * FROM employees WHERE last_name LIKE '_%'; #查询第二个字符为下划线。\为转义符 SELECT * FROM employees WHERE last_name LIKE '$%' ESCAPE '$'; #escape指定转义符为$

#between A and B === >=A and <=B,所以 A<=B SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

#in /* in === multiple or , 不支持通配符，因为or是=，不是like */ SELECT * FROM employees WHERE job_id IN ('AD_VP','IT_PROG');

where `job_id`='AD_VP' OR `job_id`='IT_PROG';

#is null / is not null SELECT * FROM employees WHERE commission_pct IS NULL;

SELECT ISNULL(commission_pct),commission_pct #判断为null=1，notnull=0 FROM employees;

#安全等于 <=> SELECT * FROM employees WHERE job_id <=> 'AD_VP';

SELECT * FROM employees WHERE salary <=> 12000;

SELECT * FROM employees WHERE commission_pct <=> NULL;

##进阶3 排序---------------------------------------------

#ORDER BY 默认asc ，降序，放在查询最后(除limit之外) #工资从高到低 SELECT * FROM employees ORDER BY salary DESC;

#加筛选条件 #按表达式排序 #ifnull(字段，0)，字段值为null，赋值为0，不为null，为原值 SELECT salary, salary12(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary12(1+IFNULL(commission_pct,0));

#按照别名排序 SELECT salary, salary12(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC; #说明order by 最后一步执行

#按函数排序 SELECT last_name,LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name) DESC;

#多条件排序员工信息先按工资升序，后按员工编号降序 SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

##进阶4 常见函数-------------------------------------------------

/* 函数分为单行和多行函数单行比如length()，肯定有返回值 1.字符函数（处理字符串）多行函数，又称组函数，统计用 */

#一、字符函数##########################

#1.length，获取字符长度 SELECT LENGTH('莫默123aaa'); #UTF8中文为3个字节，GBK中文2个字节 SHOW VARIABLES LIKE '%char%';

#2.concat SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;

#3.upper lower

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

#4.substr / substring SELECT SUBSTR('李莫愁爱上了陆展元',7) AS output; #从第7个位置开始 SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS output; #从第1个位置开始的三个字符

#大写首字母 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) FROM employees;

#5 instr #返回substr在str中第一次出现的索引，若不匹配，返回0 SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS output; #substr和instr连用，取邮箱用户名 SELECT SUBSTR(email,1,INSTR(email,'@')-1) FROM ..; #6. trim #去掉首位空格或指定str SELECT TRIM(' 莫默 ') AS output; SELECT TRIM('a' FROM 'aa默aa') AS output;

#7. lpad #左填充str为10个字符长，用padstr填充 SELECT LPAD('莫默',10,'*') AS output;

#8. rpad SELECT RPAD('y',5,'-') AS output;

#9 replace

在str中，from_str被to_str替代

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS output;

#二、数学函数############################

SELECT ROUND(1.4); SELECT ROUND(1.459,2);

SELECT CEIL(1.01); # 取右取≥该参数的最小整数

SELECT FLOOR(-1.09); #往数轴取左

SELECT TRUNCATE(1.123456,4); #取小数点前4位

#取余 SELECT MOD(10,3); SELECT MOD(-10,3); #结果和被除数正负一致 SELECT 10%3;

#出一个随机数，范围0-1 SELECT RAND(0123456); #三、日期函数############################## SELECT NOW();

SELECT CURDATE();

SELECT CURTIME(); #获得指定的部分 year年 month月 date日 hour小时 minute分 second秒 SELECT YEAR(NOW()); SELECT YEAR('1994-09-10'); SELECT YEAR(hiredate) FROM employees; SELECT MONTH(NOW()); SELECT MONTHNAME(NOW()); #返回英文月

#日期格式的字符转换成指定的格式 #%Y1994 %y94 %m01 %c1 %d30 %H24 %h12 %i00 59 %s00 59 SELECT MONTHNAME( STR_TO_DATE('1994-4-2','%Y-%c-%d') )output ;

日期型字符该字符的格式

/* #查询入职日期为1992-4-3的员工信息 select * from employees where hiredate = '1992-4-3'; 若日期型字符格式为04-03 92 select * from employees where hiredate = str_to_date('04-03 1992','%m-%d %Y'); */ #时间格式的转换为特定格式的字符串 SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') 日期; #查询有奖金的员工的员工名和入职日期，要求该日期格式输出为xx月/xx日 xxxx年 SELECT last_name 姓名, DATE_FORMAT (hiredate, '%m月/%d日 %Y年') 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

#求日期之差 SELECT DATEDIFF(CURDATE(),'1994-01-21');

#四、其他函数################################### SELECT VERSION(); SELECT DATABASE(); SELECT USER(); SELECT PASSWORD('yb'); #加密 SELECT MD5('yb'); #加密 #五、流程控制函数################################### #if SELECT IF(10>5,1,0); #和excel的if一样 SELECT IF(salary<20000,IF(salary<10000,'small','media'),'high') grade #nesting 嵌套 FROM employees;

SELECT last_name, commission_pct, IF(commission_pct IS NULL,'呵呵','哈哈') 备注 FROM employees;

#case多条件判断，可以和select搭配，也可单独使用 /* case 常量 when 值1 then 显示的值1 when 值2 then 显示的值2 。。。 else 要显示的值n end / #在部门30的，工资×1.1 #在部门40的，工资×1.2 #在部门50的，工资×1.3 #其他部门，原工资 SELECT department_id, salary, CASE department_id WHEN 30 THEN salary1.1 WHEN 40 THEN salary1.2 WHEN 50 THEN salary1.3 ELSE salary END AS 新工资 FROM employees; #excel嵌套大法 SELECT department_id,salary, IF(department_id=30,salary1.1, IF(department_id=40,salary1.2, IF(department_id=50,salary*1.3,salary)))AS 新工资 FROM employees;

/* 多重if case when 条件1 then 显示的值1 when 条件2 then 显示的值2 。。。 else 要显示的值n end */ #工资<10000,small;10000<=工资<20000,media;20000<工资，high SELECT salary, CASE WHEN salary>20000 THEN 'high' WHEN salary>10000 THEN 'media' ELSE 'small' END AS grade FROM employees; #excel嵌套大法 SELECT salary,IF(salary<20000,IF(salary<10000,'small','media'),'high') grade #nesting 嵌套 FROM employees;

##二、统计函数

#sum avg max min count #sum avg只能处理数字型，max 、min、count可以字符型 #全部忽略null值 #可以搭配distinct，去重 #和统计函数一同使用的时group by 后的字段。☆☆☆☆☆

SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary) FROM employees;

SELECT SUM(salary),ROUND(AVG(salary),2),MAX(salary),MIN(salary),COUNT(salary) FROM employees;

SELECT SUM(commission_pct),MAX(hiredate) FROM employees;

SELECT SUM(DISTINCT salary), COUNT(DISTINCT commission_pct), SUM(salary),COUNT(commission_pct) FROM employees;

#count单独使用 SELECT COUNT(*) FROM employees;#☆☆☆ SELECT COUNT(1) FROM employees;

#进阶5 分组查询 ------------------------------------------------ /* select 统计函数，字段 from 表名【where 包含表名字段的条件】 #不可使用别名 group by 字段 #可使用别名【having 包含统计函数的字段】 #可使用别名【order by 字段】 #可使用别名

/ #简单分组查询：每个部门平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id; #前筛选--分组查询：邮箱中包含a字符的，每个部门平均工资， #where要跟在from 后面一行 SELECT AVG(salary),department_id,email FROM employees WHERE email LIKE '%a%' GROUP BY department_id; #有奖金的每个领导手下的员工最高工资 SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id; #后筛选--分组查询：领导编号>102的每个领导手下员工的最低工资＞5000的领导编号 #，以及其最低工资 SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 #优先考虑前筛选☆☆☆☆☆ GROUP BY manager_id HAVING MIN(salary)>5000; #按函数--分组查询：按员工姓名长度分组，查询每组个数，筛选员工个数>5的有哪些 SELECT COUNT(),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5; #多字段--分组查询 #每个部门每个工种的平均员工工资 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id; #排序--分组查询 #每个部门每个工种的员工平均工资，按照平均工资大小降序 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC;