1、数据操作
(1)insert 增加数据
1. 插入完整数据(顺序插入)
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES (值1,值2,值3…值n), (值1,值2,值3…值n), (值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
(2)delete 删除数据
语法：
DELETE FROM 表名 WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE password=’’;
(3)update 修改数据
语法：
UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE CONDITION;
示例：
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;
(4)select 查询数据
语法
SELECT DISTINCT FROM 表名;
2、单表查询
单表查询的语法
SELECT DISTINCT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数;
单表查询的关键字执行的优先级
from
where
group by
select
distinct
having
order by
limit
单表查询的执行过程
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.执行select(也可以distinct去重)
5.将分组的结果进行having过滤
6.将结果按条件排序：order by
7.限制结果的显示条数
(1)前期建数据库和表
# 建表和数据准备
# 创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
# 插入记录
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
#以下是教学部
('cai','male',78,'0302','teacher',1000000.31,401,1),
('liu','male',81,'0305','teacher',8300,401,1),
('ling','male',73,'0701','teacher',3500,401,1),
('yongliang','male',28,'1101','teacher',2100,401,1),
('marry','female',18,'0211','teacher',9000,401,1),
('tom','male',18,'19000301','teacher',30000,401,1),
('liangliang','male',48,'1111','teacher',10000,401,1),
#以下是销售部门
('阿哥','female',48,'0311','sale',3000.13,402,2),
('丫头','female',38,'1101','sale',2000.35,402,2),
('小丁','female',18,'0312','sale',1000.37,402,2),
('月亮','female',18,'0513','sale',3000.29,402,2),
('太阳','female',28,'0127','sale',4000.33,402,2),
#以下是运营部门
('老才','male',28,'0311','operation',10000.13,403,3),
('老李','male',18,'19970312','operation',20000,403,3),
('老王','female',18,'0311','operation',19000,403,3),
('老德','male',18,'0411','operation',18000,403,3),
('老赵','female',18,'0512','operation',17000,403,3)
;
(2)简单查询
# 查看表中所有
select * from 表;
# 指定列查询
select emp_name,salary from employee;
# 在列中使用四则运算
select emp_name,salary*12 from employee;
# 重命名
select emp_name,salary*12 as annul_salary from employee;
select emp_name,salary*12 annul_salary from employee;
# 去重 distinct
select distinct post from employee;
select distinct sex,post from employee;
# 定义显示格式
concat()函数 # 用于拼接字符串
select concat('姓名 ：',emp_name),concat('年薪:',salary*12) from employee;
concat_ws()函数 第一个参数为分隔符
select concat_ws('|','a','b','c');
# 条件判断
case when语句 == if条件判断句
select
(
case
when emp_name = 'cai' then
emp_name
when emp_name = 'liu' then
CONCAT(emp_name,'_haha')
else
concat(emp_name, 'hehe')
end
) as new_name
from
employee;
(3)根据条件筛选行 where
# 比较运算 = > < >= <= !=/<>
select * from employee where age>18;
select * from employee where salary<10000;
select * from employee where salary=20000;
# between 在一个范围内； between a and b #[a,b]
select * from employee where salary between 10000 and 20000;
# in集合查询 ； in(80,90,100) 值是80或90或100
select * from employee where salary in (17000,19000);
# like 模糊查询
# _ 通配符 表示一个字符长度的任意内容
select * from employee where emp_name like 'c__';
# % 通配符 表示任意字符长度的任意内容
select * from employee where emp_name like 'c%';
select * from employee where emp_name like '%u';
select * from employee where emp_name like '%a%';
# regexp 正则匹配
select * from employee where emp_name regexp '^cai';
(4)逻辑运算 and or not
# and
select * from employee where age>18 and post='teacher';
# or
select * from employee where salary<10000 or salary>30000;
# not
select * from employee where salary not in (10000,17000,18000);
(5)关于 null
# 关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
# 查看岗位描述为NULL的员工信息
select * from employee where post_comment is null;
# 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null;
(6)5个聚合函数
强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
count # 统计次数
max # 求最大值
min # 求最小值
avg # 求平均值
sum # 求和
(7)分组聚合 group by
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
# 单独使用group by关键字分组
select post from employee group by post;
#注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
#group by关键字和group_concat()函数一起使用
#按照岗位分组，并查看组内成员名
select post,group_concat(emp_name) from employee group by post;
select post,group_concat(emp_name) as emp_members from employee group by post;
# group by与聚合函数一起使用
#按照岗位分组，并查看每个组有多少人
select post,count(id) as count from employee group by post;
# 查询各部门年龄在20岁以上的人的平均薪资
select post,avg(salary) from employee where age>20 group by post;
(8)过滤 having (group by + 聚合函数)
# having与where不一样的地方在于：
执行优先级从高到低：where > group by > having
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
# 查询平均薪资大于1w的部门
select avg(salary) from employee group by post having avg(salary) > 10000;
# 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(emp_name),count(id) from employee group by post having count(id)<2;
# 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
(9)排序 order by
# 升序 asc (默认升序)
select * from employee order by salary;
select * from employee order by salary asc;
# 降序 desc
select * from employee order by salary desc;
# 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
select * from employee order by age,salary;
select * from employee order by age,salary desc;
select * from employee order by age desc,salary;
(10)限制查询的记录数 limit
# 取前n条
select * from 表 order by 列 limit n;
# 从m+1开始,取n条
select * from 表 order by 列 limit m,n;
# 从m+1开始,取n条
select * from 表 order by 列 limit n offset m;
小结：select * from 表 where 条件 group by 分组 having 过滤 order by 排序 limit n;
3、在命令提示符窗口中遇到的编码问题解决方法：
1.临时解决问题 在客户端执行 set xxxx = utf8;
2.永久解决问题 在my.ini添加 set xxxx = utf8;
3.实时解决问题 create table 表名() charset=utf8;
