MySQL 之数据操作及单表查询

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,'20150302','teacher',1000000.31,401,1),
('liu','male',81,'20130305','teacher',8300,401,1),
('ling','male',73,'20140701','teacher',3500,401,1),
('yongliang','male',28,'20121101','teacher',2100,401,1),
('marry','female',18,'20110211','teacher',9000,401,1),
('tom','male',18,'19000301','teacher',30000,401,1),
('liangliang','male',48,'20101111','teacher',10000,401,1),
#以下是销售部门
('阿哥','female',48,'20150311','sale',3000.13,402,2),
('丫头','female',38,'20101101','sale',2000.35,402,2),
('小丁','female',18,'20110312','sale',1000.37,402,2),
('月亮','female',18,'20160513','sale',3000.29,402,2),
('太阳','female',28,'20170127','sale',4000.33,402,2),
#以下是运营部门
('老才','male',28,'20160311','operation',10000.13,403,3), 
('老李','male',18,'19970312','operation',20000,403,3),
('老王','female',18,'20130311','operation',19000,403,3),
('老德','male',18,'20150411','operation',18000,403,3),
('老赵','female',18,'20140512','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;

posted @ 2020-10-22 13:42 永亮阅读(231) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

YongLiangのBOKE

———————————————— 我曾十步一回头@CYL

MySQL 之数据操作及单表查询

1、数据操作

（1）insert 增加数据

（2）delete 删除数据

（3）update 修改数据

（4）select 查询数据

2、单表查询

（1）前期建数据库和表

（2）简单查询

（3）根据条件筛选行 where

（4）逻辑运算 and or not

（5）关于 null

（6）5个聚合函数

（7）分组聚合 group by

（8）过滤 having (group by + 聚合函数)

（9）排序 order by

（10）限制查询的记录数 limit

3、在命令提示符窗口中遇到的编码问题解决方法：

公告

YongLiangのBOKE

———————————————— 我曾十步一回头@CYL

MySQL 之 数据操作及单表查询

1、数据操作

（1）insert 增加数据

（2）delete 删除数据

（3）update 修改数据

（4）select 查询数据

2、单表查询

（1）前期建数据库和表

（2）简单查询

（3）根据条件筛选行 where

（4）逻辑运算 and or not

（5）关于 null

（6）5个聚合函数

（7）分组聚合 group by

（8）过滤 having (group by + 聚合函数)

（9）排序 order by

（10）限制查询的记录数 limit

3、在命令提示符窗口中遇到的编码问题解决方法：

公告

MySQL 之数据操作及单表查询