MySQL基础

---

MySQL基础复习

一、查询

1.常规查询

查询语法：select 字段 from 表名;
查询常量值：select 100;
查询表达式：select 100*0.98;
查询表达式：select 100+0.98;//+号：两个操作数都为数值型，做加法运算，其中一方为字符型，字符型转换为数值，转换成功，做加法，转换失败，字符数值变为0，若操作数有null，则结果为null
查询相关函数：select avg(age) from users;
查询单个字段：select name from users;
查询多个字段：select name,age,sex from users;
查询所有字段：select * from users;#优化数据库语句时，不建议使用
查询字段并取名：select name 姓名,age 年龄 from users;
查询字段并去重：select distinct age 年龄 from users;
查询字段并去重：select distinct age 年龄 from users;

2.条件查询

语法：select 字段 from 表名 where 查询条件;
按条件表达式筛选:条件运算符：> < = != <>(不等于) >= <=，其中= <>不可以判断null值;
查询字段：select age 年龄 from users where age=15;
查询字段：select age 年龄 from users where age>15;
查询字段：select name 姓名,age 年龄 from users where name != '张三';
查询字段：select name 姓名,age 年龄 from users where sex != '女';

按逻辑表达式筛选：逻辑运算符：&&，||，!，and，or，not
查询字段：select name 姓名 from users where age=15 and sex='男';
查询字段：select name 姓名 from users where age=15 or sex='男';

模糊查询：like一般和通配符%搭配，% 任意多个字符并且包含0个字符，_ 任意单个字符，$表示转义
查询包含a的字段：select name 姓名 from users where name like '%a%';
查询以a为开头的字段：select name 姓名 from users where name like 'a%';
查询第二个字符为a的字段：select name 姓名 from users where name like '_a%';
查询第二个字符为_的字段：select name 姓名 from users where name like '_$_%';
between and查询字段：select name 姓名 from users where age between 13 and 18;
in查询字段：select age from users where name in ('张三','李四');
is null 和is not null查询字段：select age from users where name in null;#可以判断null值
<=>查询字段：select age from users where name <=> null;#可以判断null值
ifnull查询字段：select ifnull(age,0) as 年龄 from users;#ifnull(a,b)判断是否为空，为null返回b，否则返回a

order by排序，语法：select 字段 from 表 （where 筛选条件）order by 排序列表（asc升序，desc降序，默认升序）
查询字段并排序：select name from users where age!=20 order by name desc;

二、增删改

1.增加

插入语法：insert into 表名（列名，…） values（值1，…）;
插入字段：insert into users(name,age,sex) values('刘六',22,'男');
插入字段：insert into users(name,age,sex) select '老七',28,'女;'
插入字段：INSERT into beauty set id=19,name='刘涛',phont='546';

2.修改

修改单表字段语法：update 表名 set 列1=值1，列2=值2 where 筛选条件;
修改单表字段：update users set name='张二',age='32' where id=1;

修改多表字段语法（先关联后修改）：update 表1 别名 inner|right|left join 表2 别名 on 关联条件 set 列1=值1，列2=值2，… where 筛选条件;
修改多表字段：update boy b inner join girl g on b.id=g.id set b.name ='李三' where g.id is null;

3.删除

单表删除语法：delete from 表名 where 筛选条件（删除整行数据）
删除数据：delete from users where id=1;

多表删除语法（先关联后删除）：delete 别名1，别名2 from 表1 别名1 inner|left|right join 表2 别名2 on 关联条件 where 筛选条件；
删除数据：delete b,g from boy b inner join girl g on b.id=g.id where b.name='李三' and g.name='王红';

三、函数

1.字符函数

常见字符函数：concat（拼接字符）,length（获取字符长度）,substr（截取字符）,upper（大写）,lower（小写）,replace（替换字符）,trim（去空格）,lpad（左填充）,rpad（右填充）,instr（获取子字符第一次出现的索引）
查询字段：select length('张三');#GKB格式下一个汉字3个字符，UTF8则为2个字符
查询字段：SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
查询字段：SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
查询字段：SELECT SUBSTR('小龙女爱上了1',1,3) out_put;#从下标为1开始拆分，3个字符
查询字段：SELECT INSTR('12上课的就爽快点','12') AS out_put;
查询字段：SELECT LPAD('张三',10,'*') as k;#向左填充*，字符长度为10
查询字段：SELECT TRIM('as' FROM 'asadadsdasdasfasfasf ') as out1;#去掉指定的字符as
查询字段：SELECT REPLACE('战士赶到撒旦','战士','将军') as k;#用将军字符代替战士字符

2.数学函数

常见数学函数：round（四舍五入），ceil（向上取整，返回>=该参数的最小整数），floor（向下取整），truncate（截断），mod（取余），rand（随机数，返回0-1之间的小数），sum（求和），avg（平均值），max（最大值），min（最小值），count（总数）
查询字段：SELECT ROUND(-1.567,2);#保留两位，输出-1.57
查询字段：SELECT CEIL(-1.01);#输出-1
查询字段：SELECT floor(-1.01);#输出--2
查询字段：SELECT TRUNCATE(1.65,1);#保留一位，输出1.6
查询字段：SELECT MOD(-10,3);#取余，输出-1

3.流程控制函数

常见流程控制函数：if函数，case函数，日期函数（now，curdate，curtime，year，STR_TO_DATE，DATE_FORMAT），分组函数
查询字段：select last_name,commission_pct,if(commission_pct is null,'没奖金，呵呵','有奖金，嘻嘻') 备注 from employees;#判断正确，输出第二个语句，错误，第三个语句
查询字段：

/*case语法（类似switch case）：
case 字段或者表达式 
when 常量1 then 显示的值1或者语句1;
when 常量2 then 显示的值2或语句2;
... 
else 显示的值n或语句n;
end
*/
SELECT salary 原始工资, department_id, 
case department_id
WHEN 30 THEN salary*1.1;
WHEN 40 THEN salary*1.2;
WHEN 50 THEN salary*1.3;
ELSE salary end as 新工资 FROM employees;

查询字段：

/*case语法（类似多重if）：
case 
when 条件1 then 显示的值1或者语句1;
when 条件2 then 显示的值2或语句2;
... 
else 显示的值n或语句n;
end
*/
SELECT salary,
case
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END as 工资级别
FROM employees;

查询字段：select now();#查询当前日期+时间
查询字段：select curdate();#返回当前日期，不包含时间
查询字段：select curtime();#返回当前时间，不包含日期
查询字段：select year(curdate);#返回指定年份
查询字段：select date_format(now(),'%y年%m月%d日') as 时间;#将日期转换成指定格式的字符
查询字段：select str_to_date(now(),'%y-%m-%d') as 时间;#将日期转换成指定格式的字符查询字段：

/*分组查询语句：
select 分组函数,列1 from 表 【where 条件】 group by 列1 having 筛选条件 order by 排序;
分组函数：
sum 求和，avg平均值，max最大值，min最小值，count计算个数
1、sum、avg处理数值类型，count，max，min处理任何类型
2、是否忽略null值,max,min,sum,count,count忽略null
3、sum，count和DISTINCT搭配实现去重的运算
*/
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
select MAX(salary),manager_id from employees e inner join manager m on e.id =m.employees_id where commission_pct is not null group by manager_id order by max(salary) desc;
SELECT AVG(salary),department_id,job_id FROM employees WHERE department_id is not null GROUP BY department_id,job_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) DESC;