数据库记录(多表)操作
1.记录操作:增删改查
insert,update,delete,selete
与库表操作的对比:create,alter,drop,show/desc
插入语句:
语法1 -- insert into table(字段1,字段2...) values (值1,值2...)
语法2 -- insert into table values (值1,值2...),
(值1,值2...),
(值1,值2...); 插入多个值
语法3 -- insert into table1 (sname) select name from table2 where id <3;
删除语法:
delete from table where id = 5;
delete from mysql.user where user = 'liming';
修改语法:
update mysql.user set password = password('123') where user = 'liming';
update mysql.user set password = password('mysql123') where user = 'root';
查询语法:
select * from table where id = 1 group by part having order by field limit 3
2.单表查询:
select age from user where id = 2;
去重:
select distinct name from user where age = 18;
四则运算:
select salary*12 ysalary from employee;(查看年薪,重命名)
显示格式:
select concat('员工:',name, ':','年薪',salary*12) from employee; (concat字符串拼接)
select concat_ws(':',name,age,sex) from employee;(以:拼接)
结合case语句:(if...else),查出的结果,进行不同的处理
select (
case
when emp_name = 'liming'
then emp_name
when emp_name = 'lining'
then concat(emp_name,'NB')
end) as new_name
from employee;
3 where 约束条件:
select emp_name from emploee where salary > 1000;
select emp_name from emploee where salary between 1000 and 2000;
select emp_name from employee where post in ('teacher' ,'sale');
select salary from employee where name like 'a%';
select salary from employee where name like 'a_';
select emp_name from employee where salary = 1000 or salary = 2000;
4.group by 分组
select * from employee group by post;
5.聚合函数
select avg(salary) from employee group by post;
select post,count(salary) from employee group by post;
select post,sum(salary) from employee group by post;
select post,max(salary) from employee group by post;
6.having 条件过滤:组过滤,group by 与having 搭配
select post,count(id) from employee group by post having count(id) < 2;
select post,avg(salary) from employee group by post having avg(salary) > 1000;
7,order by 查询结果排序
select * from employee order by salary asc/desc;
select * from employee order by salary,age desc;
8.limit 限制查询记录条数
select * from employee where age = 18 order by salary desc limit 3;
select * from employee where age = 18 order by salary desc limit 5,3;(从第5条开始取3条,分页,top榜)
9,正则表达式查询:
select * from employee where emp_name regexp '^li';
select * from employee where emp_name regexp 'i{2}';
select * from employee where emp_name regexo 'li.*?[ng]';
10:多表查询:
1.交叉连接:不适合任何匹配条件,生成笛卡尔积
select * from table1,table2
2.内连接:只连接匹配的行;inner join
select * from table1 join table2 on table1.外键字段 = table2.外键字段;
只有两个表中互相匹配的项才会显示在新表中
3.左外连接:以左表为主,所有左表信息显示,右表没有匹配到的项null表示
select * from table1 left join table2 on table1.外键字段 = table2.外键字段;
4.右外连接:
select * from table1 rightjoin table2 on table1.外键字段 = table2.外键字段;
5,全外连接
select * from table1 left join table2 on table1.外键字段 = table2.外键字段;
union
select * from table1 rightjoin table2 on table1.外键字段 = table2.外键字段;
11:子查询:
带in关键字的子查询
select * from department where id in (select dep_id from emloyee group by
dep_id having avg(age) > 25);
带比较运算符的子查询
select * from department where id not in (select dep_id from employee group by dep_id);
带exists关键字的子查询:
exists关键字表示存在,返回一个真假值,True or False;
当返回True时,外层查询语句将进行查询,当返回值为False时,外层查询语句不进行查询;
select * from department where exists (select name from employee where age = 100);
