数据库（二）自动增长列，添加表数据，修改数据，删除数据，where条件，%通配符，is，查询数据(查询全表，指定列查询，排序查询)，聚合查询（平均、最大、最小、行数、求和）
通过选中表进行导入、到处操作进行数据表备份
自动增长列：数据表的主键应具备唯一性，每次写入不同主键值会比较麻烦，使用自动增长列替换主键值是常用的方式
CREATE TABLE `user`(
userId PRIMARY KEY AUTO_INCREMENT
)
自动增长列默认从1开始，修改起始值使用以下方式
ALTER TABLE 表名 AUTO_INCREMENT=起始值
创建一张表：
create table users ( userid int auto_increment, userName varchar(20) not null, -- 非空约束 birthday datetime, email varchar(50) default 'xxx@geekhome.com', -- 默认约束 detail text, constraint pk_userid primary key(userid) );
增加数据的方法：数据表中写入的数据以行为单位
增加数据的语法（一）：根据指定列写入值
insert into 表名(列名) values(值)
只有varchar 、文本 加单引号，其余的值都直接写
写入的列名和后面要写入的信息要对应
非空约束的信息必须要写入
如果主键是自动增长列，此时主键是可以不写的，但是如果不是自动增长的，则必须要写因为非空
注意要点：
1.写入的值的数量必须和列的数量匹配
2.写入的值和列的数据类型必须匹配
3.非空约束的列必须写在指定列中
4.主键必须写在指定列中
5.写入的值必须满足该列的check约束
insert into users(username,brithday,detail) values('tom',now(),'tom的个人信息') ； --如果指定的日期则按照字符串写入，系统会自动转换成时间格式 insert into users(username,brithday,detail) values('jack','1992-4-5','jack的个人信息')； --写入是列名和数据顺序要对应 insert into users(username) values('white');
增加数据的语法（二）：根据指定列写入值
INSERT INTO 表名 VALUES(值)
注意：因为没有列明，所以一定要将所有的数据写全，（添加主键和默认值将会失效），因为系统会按顺序写入，不会识别写入
1.表中所有列都必须写入
2.表中如果存在默认值则使用 default 替入
3.如果某列不需要写入数据则使用 null 替入
insert into users values(100,'jerry','1994-11-12','jerry@sina.com','jerry的个人信息');
防止主键失效的方法：在对应位置上写入null
-- 防止主键失效的方法：在对应位置上写入null insert into users values (null，'zake','1993-5-6','zack@sina.com','zack的个人信息')
防止默认值失效的方法：在有设置默认值的地方写入default，就会显示默认值
insert into users(null,'marry','1992-12-10',default,'marry 的个人信息')
建表时添加数据：（只是将数据写入，非空会写入，主键和其他约束都没有）
create table 表名2 as select * from 表名1 -- 将表1内容新增到创建的表2中
-- 将users的数据添加到新创建的backup_user表中 create table backup_user as select * from users
修改数据语法：
主键能用update修改但是尽量不改
主键被其他表引用是，如果其中内容被引用了则不能修改，如果没有被引用的是可以修改的
update 表名 se t 列 = 值 [where 条件] -- where条件可写可不写，不写where条件则表示删除这一列的搜友数据
alter table users add age int；-- 新增一列 alter table users add sex varchar(10);-- 新增一列 修改数据： -- 不写where是代表整个表的这一列全部数据都更改为统一数据 update users set age =20;
同时修改多个列的内容：
-- 同时对多个列的数据进行修改 update users set age=22,sex='男';
where条件的使用：
where语句后应为一个表达式，且表达式返回是或否的结果
where后的表达式可以为一个或多个使用逻辑运算符and和or关联
关系运算符：> , < , >= , <= , = , != , between and , in , like , is，is not
指定修改某一行的某一列数据 update users set sex ='女' where userid=103； --将用户的编号为1-3（三种表述方式）的用户年龄更改为25 update user set age=25 where betweem 1 and 3; update user set age=26 where userid>=1 and userid<=3; -- int可以添加不连续的数据 update user set age=27 where userid int(1,2,3);
%通配符：通过一个数据中包含的部分数据信息查找整条数据
-- 将姓名以t开头的用户，将他的邮箱清空 -- %通配符，表示匹配任意长度的任意字符 update users set email =null where username like 't%'; -- 将姓名中包含ac的用户，将他们的升入设为1995-10-1 update users set birthday ='1995-10-1' where username like '%ac%'； -- 将所有QQ邮箱的用户将他的年龄增加5岁 update users set age=age+5 where email like '%qq.com%';
is ：判断是否非空
--将所有没有填写过资料的用户将其用户信息变更为'待注销用户' 用等号代表填入的内容是否是null update users set detail ='待注销用户' where detail=null; 用is代表是否有填入数据 update users set detail ='待注销用户' where detail is null; -- 将所有填写过邮箱并且性别为男的用户，变更其年龄为20 update users set age =20 where email is not null and sex='男';
删除数据：
delete from 表名 [where 条件]
例子：删除backup_user表中的所有数据
delete和truncate两者的区别：
* truncate table与delete的区别在于delete是逐行执行，每次执行将执行事务，并将数据操作记录日志
* truncate table是整个数据块删除为写入日志无法恢复数据，且自动增长列重置
方法一： delete将表中的数据逐行删除，不添加条件就是删除整个表中的所以数据，
delete每次执行将执行事务，并将数据操作记录日志，可以恢复
delete from backup_user;
方法二：truncate不是逐行删除，是将整个数据块全部删除，且不能写where条件
是整表数据删除为写入日志，无法恢复数据，且自动增长列也会重置
truncate table backup_user;
删除其中一行数据：写where条件
-- 删除编号是3的整行数据 delete fron backup_user where uesrid=3;
查询数据：
1.查询全表 select * from 表名
2.指定列名查询 select 列 as 显示列名 from 表名
查询语法注意事项：
1.* 表示表中所有的列，也可以单独查看指定的列，用列名将*替换掉；
2. 查询所有的列比较影响性能，会将所有数据库中的每一行进行每一列的删选，所以要尽量根据业务需要的数据列进行查询
一、查询全表
-- 查询姓、名、工资和工作id select first_name,last_name,salary,job_id from emp; -- 查员工编号为100的 姓、名、工资和工作id select first_name,last_name,salary,job_id from emp where employee_id=100; -- 查询部门编号为50的所有员工的姓名和薪水 select first_name,last_name,salary from emp where department_id=50;
对日期进行判断条件：
-- 查询薪资超过8000并且入职时间早于2015年的员工姓名、邮箱、入职时间、所在部门的编号 select first_name,last_name,hire_date,salary,department_id from emp where salary>8000 and hire_date<'2015-1-1';
二、指定列名查询：
select 列 as 显示列名 from 表名
例子：查询员工的姓，增加500后的薪水
方法一：这样查询只能查看增加后的结果，但是不能持久保存，且列名是 ‘salary+500’
select first_name ,salary+500 from emp；
方法二：对数据列起别名（as 新列名），这样就对数据进行了保存，可以通过别名对数据进行查询
select first_name ,salary+500 as salary from emp；
对表取别名，便于在已经查询的数据中进行二次查询
select e.employee_id ,e.salary ,e.manager_id from emp as e；
将两列数据合并到一起concat查询：
select concat(first_name,last_name) as empname from emp;
排序查询：
select 列名 from 表名 where 条件 order by 列
升序排序 asc（默认值）
降序排列 desc
--根据工资由高到低查看部门编号为50的员工姓名和薪资 select concat(first_name,last_name) as empname, salary from emp where department_id=50 order by salary desc;
新建一张表数据，进行排序测试：
create table test ( A varchar(10), B varchar(10) ) insert into test values(50,22); insert into test values(50,42); insert into test values(50,32); insert into test values(60,45); insert into test values(50,78); insert into test values(60,19); insert into test values(50,20); insert into test values(50,34); insert into test values(60,43); insert into test values(80,89); insert into test values(50,26); insert into test values(70,36); insert into test values(70,93); insert into test values(50,73); insert into test values(70,63); insert into test values(80,54); insert into test values(50,82);
进行数据排序测试：
-- 对test表中的ab字段进行排序，对a和b都进行降序排序 -- 将a列进行降序排列 select *from test order by a desc; -- 先对a进行升序排列，如果a有重复的数据，在重复的过程中对b进行升序排列
-- 当对多列进行排序时，将排序前一个列的值，如果前一个列的值有出现重复的情况，才会对第二个列继续排序 select *from test order by a,b; -- 此时a升序，b降序，因为desc值作用于b列 select *from test order by a,b desc; -- 对a和b列进行降序，如果a重复再对b列进行降序 select *from test order by a desc,b desc;
高级查询
聚合函数：
sum：求和
avg :平均值
max：最大值
min：最小值
count：行数
-- 查询部门编号为40的员工薪资的总和 -- sum的结果只有一个值，如果出现非聚合列，则只会出现第一个，不允许这么写 select sum(salary) from emp where department_id = 50 ; -- 查询所有员工的平均工资 select avg(salary) from emp; -- 查询所以员工的工资汇总和平均工资 select sum(salary) as sum_salary，avg(salary) as avg_salary from emp; -- 查询最早入职员工的入职时间 select min(heir_date) from emp; -- 查询部门编号为50的最高的薪资 select max(salary) from emp where department_id=50;
-- 查询员工表中有多少员工信息 -- 只要这个行中只有一个列中有数据，写*也会统计进去 select count (*) from emp; -- 107 select count (first_name) from emp; -- 107 -- 其中一个数据的数值为null select count (manager_id) from emp; -- 106
课上练习：（在数据修改前将表格进行备份）
修改编号为100的员工的薪资，增加500块
修改姓名中包含s（不区分大小写）的员工，将其部门调整到编号为50的部门中
调整老板的薪资增加5000
将所有薪资低于6000的员工薪资上浮500
-- 创建备份数据 create table location as select * from locations; create table emp as select * from employees; create table dep as select * from departments; -- 修改编号为100的员工的薪资，增加500块 update emp set salary=salary+500 where employee_id=100; -- 修改姓名中包含s（不区分大小写）的员工，将其部门调整到编号为50的部门中 update emp set department_id=50 where concat(forst_name,last_name) like '%s%' or concat(forst_name,last_name) like '%S%'; -- 调整老板的薪资增加5000 update emp set salary=salary+5000 where manager_id is null; -- 将所有薪资低于6000的员工薪资上浮500 update emp set salary=salary+500 where salary<6000; -- 查看数据表信息 select * from location; select * from emp; select * from dep;
