表记录的增删改查,关键字筛选顺序介绍

一、表结构相关

什么是表:
表相当于文件，表中的一条记录就相当于文件的一行内容，表中的一条记录有对应的标题，称为表的字段
第一行的id、name2、age是字段，，其余的，一行内容称为一条记录。看下图

　　表的创建和删除

创建表
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名1 类型[(宽度) 约束条件]);

　　注意：
　　　　1. 在同一张表中，字段名是不能相同
　　　　2. 宽度和约束条件非必须，宽度指的就是字段长度约束，例如：char(10)里面的10
　　　　3. 字段名和类型是必须的
　　示例
　　create table t1(  
　　id int, 
　　name varchar(50),
　　sex enum('male','female'),
　　age int(3));
删除表
　　drop table student;

　　表名,字段相关

表名的修改
alter table 表名  rename 新表名;

字段名的增加
   　　1.末尾增加
　　　　　　alter table 表名 add 字段名 数据类型 [完整性约束条件…],
                      　　add 字段名 数据类型 [完整性约束条件…];
　　　　　

 

   　　 2.首位增加
　　　　　　alter table 表名 add 字段名 数据类型 [完整性约束条件…] first;
　　　　　　alter table student10  add sex enum('male','female') default 'male' first;   

   　　 3.指定位置增加
　　　　　　alter table 表名 add 字段名 数据类型 [完整性约束条件…] after 字段名;    
　　　　　　alter table student10  add stu_num varchar(10) not null after name;

　　增加字段外键关联

　　　　alter table 表名  add foreign key(c_id) references class(id);

　　解除外键关联
　　　　alter table e3 drop foreign key e3_ibfk_l;
字段名的删除
　　alter table 表名 drop 字段名;
　　alter table student10 drop sex;

字段(名字和约束条件)的修改
　　1. alter table 表名 modify 字段名 数据类型 [完整性约束条件…];
　　 2. alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 
　　　　　　#change比modify多了,修改字段名字，改数据类型、完整性约束等内容

字段名查看
　　　1.desc ti;  #查看表结构  也可写为:describe 表名
　　　2.show create table ti\G; #查看表详细结构

　　主键相关

介绍:
　　主键（primary key） 一列（或一组列），其值能够唯一区分表中的每个行。
　　唯一标识表中字段的每一行记录称为主键。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。

　　表中的任何列都可以作为主键，只要它满足一下条件:
 　　　　任何两行都不具有相同的主键值
 　　　　每个行都必须具有一个主键值（主键列不允许NULL值）
　　总结：不应该使用一个具有意义的字段(id 本身并不保存表有意义信息)作为主键，并且一个表必须要有一个主键，为方便扩展、松耦合，高可用的系统做铺垫。


自增条件的添加（针对已有的主键增加auto_increment）
alter table student10 modify id int(11) not null auto_increment;

复合主键的添加(对已存在的表)
alter table service2 add primary key(host_ip,port);

主键的增加
alter table student1 modify name varchar(10) not null primary key;

主键和自动增长的添加设置
alter table student1 modify id int not null primary key auto_increment;

删除主键
　　1.删除自增约束
alter table student10 modify id int(11) not null; 
　　2.删除主键
alter table student10 drop primary key;

二、表记录的操作:

　　插入表记录  insert

1.insert into 表名(字段1,字段2,字段3...) values(值1,值2,值3...); #插入的值要和你前面的字段相匹配

2. insert into 表名 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里面查询出来的结果来插入到我们的表中，但是注意查询出来的数据要和我们前面指定的字段要对应好

　　表记录修改    (update)

upadte 表名 set 
    字段1=值1,  
    字段2=值2,
    where condition; #更改哪些数据，通过where条件来定位到符合条件的数据

代码示范
update mysql.user set password=password("123")
    where user=’root’ and host=’localhost’;

update t1 set name='sb' where id=2; #把id为2的行（记录）中的name字段的数据改为sb；id>1;id<=1;等等都可以。

update t1 set name='sb',id=88 where id>2; #对两个字段进行修改

update t1 set name='sb';#如果不指定where，那么会name字段的所有数据都改成sb。

　　表记录的删除   (delete)

delete from 表名
    where conition(条件); #删除符合条件的一些记录

示例
delete from mysql.user 
    where password="123";

清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

　　表记录的查询    (单表查询) select

#0.自定义显示格式
    1.concat() 函数用于连接字符串
select concat('姓名',":",name,'  年薪',":", salary*12)  AS Annual_salary from employee
　　#结果如下
　　+---------------------+
　　| Annual_salary |
　　+---------------------+
　　| 姓名: egon 年薪: 87603.96 |

    2.concat_ws()   第一个参数为分隔符来进行字符串拼接
select concat_ws(':',name,salary*12)  as Annual_salary from employee;
　　#通过冒号来将name和salary连接起来.


#1:单条件查询
select name from employee
    where post='sale';
select salary*12 as Annual_salary from employee;  #  as+新字段名,起一个别名的意思

#2:多条件查询
select name,salary from employee
    where post='teacher' and salary>10000;

#3:关键字between and 写的是一个区间
select name,salary from employee
    where salary between 10000 and 20000;

select name,salary from employee
    where salary not between 10000 and 20000;

#4:关键字is null(判断某个字段是否为null不能用等号，需要用is) 判断null只能用is
select name,post_comment from employee
    where post_comment is null;

#5:关键字in集合查询
select name,salary from employee
    where salary in (3000,3500,4000,9000);

#6:关键字like模糊查询，模糊匹配，可以结合通配符来使用
通配符’%’  #匹配任意所有字符
select * from employee
    where name like 'eg%';
通配符’_’  #匹配任意一个字符 
select * from employee
    where name like 'al__';

 　　关键字筛选的顺序

单表筛选语法顺序
select distinct 字段1,字段2... from 库名.表名
        where 筛选条件  #条件不能用聚合函数
        group by field(字段)
        having 筛选条件 #在 group by 后面使用,条件可以用聚合函数
        order by field(字段)
        limit 限制条数

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.执行select（如果有分组则不需要distinct）

5.将分组的结果进行having过滤

6.将结果按条件排序：order by

7.限制结果的显示条数

关键字的执行优先级
from　　　　#找表. 

where      #从表中找符合条件的记录

group by   #将记录划分组,将所有记录按照某个相同字段进行归类(如果没有group by,则整体为一组)

select　　  #执行select

having     #将分组结果过滤.确定需要哪个字段的数据

distinct   #去重(后面跟去重的依据,字段名)

order by   #设置排序顺序

limit　　　 #限制条数

mysql 优化了查询---->如果group by 没有找到这个分组的依据,就取select 里面找依据(是否被改名字了),再进行分组

 设置默认值:

alter table student10 add  age int(3) not null default 22;

 

 

三、where的用法

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100)  值是80或90或100
4. like 'egon%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符 
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

 

#创建表
create table employee(
    id int not null unique auto_increment,
    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(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部，全都是老师
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

四、group by和group_concat()的用法

1.group by  
　　#将记录划分组,将所有记录按照某个相同字段进行归类(如果没有group by,则整体为一组)
2.group_concat()
　　#将组中的指定字段内容以逗号分割,显示出来

解释
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、没有where,默认是表中所有的数据;没有group by,默认整个表是一组.

group by关键字和group_concat()函数一起使用,比如说我想按部门分组，每个组有哪些员工，都显示出来，怎么搞
    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内所有成员名，通过逗号拼接在一起
    SELECT post,GROUP_CONCAT(name,':',salary) as emp_members from employee GROUP BY post;
GROUP BY一般都会与聚合函数一起使用，聚合是什么意思：聚合就是将分组的数据聚集到一起，合并起来搞事情，拿到一个最后的结果
    select post,count(id) as count from employee group by post;

关于集合函数，mysql提供了以下几种聚合函数：count、max、min、avg、sum等，上面的group_concat也算是一个聚合函数了，做字符串拼接的操作

　　聚合函数介绍

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
示例：
    SELECT COUNT(*) FROM employee;  #count是统计个数用的
    SELECT COUNT(*) FROM employee WHERE depart_id=1;  #后面跟where条件的意思是统计一下满足depart_id=1这个的所有记录的个数
    SELECT MAX(salary) FROM employee;  #MAX 最大值，这里没有写group by，那么就是统计整个表中所有记录中薪资最大的，薪资的值
    SELECT MIN(salary) FROM employee;   #MIN最小
    SELECT AVG(salary) FROM employee;  #AVG平均
    SELECT SUM(salary) FROM employee;  #SUM 总和
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

　　only_full_group_by   #设置@@global.sql_mode为该模式后,select的条件只能是查看分组依据和使用聚合函数

 

set global sql_mode='ONLY_FULL_GROUP_BY';
select post,count(id) from emp group by post; 
    #因为分组之后，我们再考虑其中一条数据就没有什么意义了，所以我们都会在这种模式下进行分组

四、having的用法

having的语法格式和where是一模一样的，只不过having是在分组之后进行的进一步的过滤，having只能在group by 后面运行            
    where不能使用聚合函数，having是可以使用聚合函数的
    执行优先级从高到低：where > group by > having 

1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,having是可以使用聚合函数

示例
    #来个需求：统计各部门年龄在30岁及以上的员工的平均薪资，并且保留平均工资大于           
      10000的部门
select post,avg(salary) as new_sa from employee1 where age>=30 group by post having avg(salary) > 10000;

　　如果不写group by 直接在having这个阶段添加一条聚合函数,那么相当于对整个表进行一次分组,将整张表分组为一组(sql_mode=ONLY_FULL_GROUP_BY模式下),你查找的字段,将只获得符合条件的第一条数据

五、distinct的用法    #去重

select distinct post from employee; 注意distinct去重要写在查询字段的前面，不然会报错;
有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条数（count(distinct id)）,
其原因是distinct只能返回他的目标字段，而无法返回其他字段，distinct 想写在其他字段后面需要配合聚合函数来写。
select count(distinct post) from employee;

六、order by      #设置排序规则

按单列排序
    SELECT * FROM employee ORDER BY salary; #默认是升序排列
    SELECT * FROM employee ORDER BY salary ASC; #升序
    SELECT * FROM employee ORDER BY salary DESC; #降序
    按多列排序:先按照age升序，如果年纪相同，则按照薪资降序
    SELECT * from employee
        ORDER BY age, #注意排序的条件用逗号分隔
        salary DESC;
示例
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;

 

七、limit  #限制查询的记录数(第一个参数,是索引,第二个是条数)

示例：
　　#取出工资最高的前三位
    SELECT * FROM employee ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0,从第一条开始顺序取出三条
    select * from employee order by salary desc
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
　　#以后可以做分页查询.
　　select * from employee1 limit 0,5;

八、正则表达式查询

#之前我们用like做模糊匹配，只有%和_，局限性比较强，所以我们说一个正则，之前我们是不是学过正则匹配，你之前学的正则表达式都可以用，正则是通用的
SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';


小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';

查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where name regexp '^jin.*[g|n]$';