通过选中表进行导入、到处操作进行数据表备份 自动增长列:数据表的主键应具备唯一性,每次写入不同主键值会比较麻烦,使用自动增长列替换主键值是常用的方式 CREATE TABLE `user`( 自动增长列默认从1开始,修改起始值使用以下方式
创建一张表: 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) );
增加数据的方法:数据表中写入的数据以行为单位 增加数据的语法(一):根据指定列写入值 只有varchar 、文本 加单引号,其余的值都直接写 写入的列名和后面要写入的信息要对应 非空约束的信息必须要写入 如果主键是自动增长列,此时主键是可以不写的,但是如果不是自动增长的,则必须要写因为非空 注意要点: 1.写入的值的数量必须和列的数量匹配 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.表中所有列都必须写入 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条件的使用: 关系运算符:> , < , >= , <= , = , != , 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进行升序排列
高级查询 聚合函数: 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;
课上练习:(在数据修改前将表格进行备份) 修改编号为100的员工的薪资,增加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; |
|