數(shù)據(jù)庫（二）自動增長列，添加表數(shù)據(jù)，修改數(shù)據(jù)，刪除數(shù)據(jù)，where條件，%通配符，is，查詢數(shù)據(jù)(查詢?nèi)恚付胁樵?，排序查?，聚合查詢（平均、最大、最小、行數(shù)、求和）

抱風 2023-05-06 發(fā)布于甘肅

展開全文

通過選中表進行導入、到處操作進行數(shù)據(jù)表備份

自動增長列：數(shù)據(jù)表的主鍵應(yīng)具備唯一性，每次寫入不同主鍵值會比較麻煩，使用自動增長列替換主鍵值是常用的方式

CREATE TABLE `user`(
userId PRIMARY KEY AUTO_INCREMENT
)

自動增長列默認從1開始，修改起始值使用以下方式
ALTER TABLE 表名 AUTO_INCREMENT=起始值

創(chuàng)建一張表：

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)
);

增加數(shù)據(jù)的方法：數(shù)據(jù)表中寫入的數(shù)據(jù)以行為單位

增加數(shù)據(jù)的語法（一）：根據(jù)指定列寫入值
　　insert into 表名(列名) values(值)

　　　　只有varchar 、文本加單引號，其余的值都直接寫

　　　　寫入的列名和后面要寫入的信息要對應(yīng)

　　　　非空約束的信息必須要寫入

　　　　如果主鍵是自動增長列，此時主鍵是可以不寫的，但是如果不是自動增長的，則必須要寫因為非空

　　注意要點：

　　　　1.寫入的值的數(shù)量必須和列的數(shù)量匹配
　　　　2.寫入的值和列的數(shù)據(jù)類型必須匹配
　　　　3.非空約束的列必須寫在指定列中
　　　　4.主鍵必須寫在指定列中
　　　　5.寫入的值必須滿足該列的check約束

insert into users(username,brithday,detail) values('tom',now(),'tom的個人信息') ；

--如果指定的日期則按照字符串寫入，系統(tǒng)會自動轉(zhuǎn)換成時間格式
insert into users(username,brithday,detail) values('jack','1992-4-5','jack的個人信息')；

--寫入是列名和數(shù)據(jù)順序要對應(yīng)
insert into users(username) values('white');

增加數(shù)據(jù)的語法（二）：根據(jù)指定列寫入值

　　INSERT INTO 表名 VALUES(值)

　　注意：因為沒有列明，所以一定要將所有的數(shù)據(jù)寫全，（添加主鍵和默認值將會失效），因為系統(tǒng)會按順序?qū)懭?，不會識別寫入

　　1.表中所有列都必須寫入
　　2.表中如果存在默認值則使用 default 替入
　　3.如果某列不需要寫入數(shù)據(jù)則使用 null 替入

insert into users values(100,'jerry','1994-11-12','jerry@sina.com','jerry的個人信息');

　　防止主鍵失效的方法：在對應(yīng)位置上寫入null

-- 防止主鍵失效的方法：在對應(yīng)位置上寫入null
insert into users values (null，'zake','1993-5-6','zack@sina.com','zack的個人信息')

　　防止默認值失效的方法：在有設(shè)置默認值的地方寫入default，就會顯示默認值

insert into users(null,'marry','1992-12-10',default,'marry 的個人信息')

建表時添加數(shù)據(jù)：（只是將數(shù)據(jù)寫入，非空會寫入，主鍵和其他約束都沒有）

　　create table 表名2 as select * from 表名1 -- 將表1內(nèi)容新增到創(chuàng)建的表2中

-- 將users的數(shù)據(jù)添加到新創(chuàng)建的backup_user表中
create  table  backup_user as select * from users

修改數(shù)據(jù)語法：

　　主鍵能用update修改但是盡量不改
　　主鍵被其他表引用是，如果其中內(nèi)容被引用了則不能修改，如果沒有被引用的是可以修改的

　　update 表名 se t 列 = 值 [where 條件] -- where條件可寫可不寫，不寫where條件則表示刪除這一列的搜友數(shù)據(jù)

alter table users add age int；-- 新增一列
alter table users add sex varchar(10);-- 新增一列
修改數(shù)據(jù)：
-- 不寫where是代表整個表的這一列全部數(shù)據(jù)都更改為統(tǒng)一數(shù)據(jù)
update users set age =20;

　同時修改多個列的內(nèi)容：

-- 同時對多個列的數(shù)據(jù)進行修改
update users set age=22,sex='男';

where條件的使用：
　　where語句后應(yīng)為一個表達式，且表達式返回是或否的結(jié)果
　　where后的表達式可以為一個或多個使用邏輯運算符and和or關(guān)聯(lián)

　　關(guān)系運算符：> , < , >= , <= , = , != , between and , in , like , is，is not

指定修改某一行的某一列數(shù)據(jù)
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可以添加不連續(xù)的數(shù)據(jù)
update user set age=27 where userid int(1,2,3);

%通配符：通過一個數(shù)據(jù)中包含的部分數(shù)據(jù)信息查找整條數(shù)據(jù)

-- 將姓名以t開頭的用戶，將他的郵箱清空
-- %通配符，表示匹配任意長度的任意字符
update users set email =null where username like 't%';

-- 將姓名中包含ac的用戶，將他們的升入設(shè)為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 ：判斷是否非空

--將所有沒有填寫過資料的用戶將其用戶信息變更為'待注銷用戶'

用等號代表填入的內(nèi)容是否是null
update users set detail ='待注銷用戶' where detail=null;

用is代表是否有填入數(shù)據(jù)
update users set detail ='待注銷用戶' where detail is null;

-- 將所有填寫過郵箱并且性別為男的用戶，變更其年齡為20
update users set age =20  where email is not  null and sex='男';

刪除數(shù)據(jù)：

　　delete from 表名 [where 條件]

　　例子：刪除backup_user表中的所有數(shù)據(jù)

　　　　delete和truncate兩者的區(qū)別：

　　　　　　* truncate table與delete的區(qū)別在于delete是逐行執(zhí)行，每次執(zhí)行將執(zhí)行事務(wù)，并將數(shù)據(jù)操作記錄日志

　　　　　　* truncate table是整個數(shù)據(jù)塊刪除為寫入日志無法恢復數(shù)據(jù)，且自動增長列重置

　　　　方法一： delete將表中的數(shù)據(jù)逐行刪除，不添加條件就是刪除整個表中的所以數(shù)據(jù)，

　　　　　　　　delete每次執(zhí)行將執(zhí)行事務(wù)，并將數(shù)據(jù)操作記錄日志，可以恢復

delete from backup_user;

　　　　方法二：truncate不是逐行刪除，是將整個數(shù)據(jù)塊全部刪除，且不能寫where條件

　　　　　　　　是整表數(shù)據(jù)刪除為寫入日志，無法恢復數(shù)據(jù)，且自動增長列也會重置

truncate table backup_user;

刪除其中一行數(shù)據(jù)：寫where條件

-- 刪除編號是3的整行數(shù)據(jù)
delete fron backup_user where uesrid=3;

查詢數(shù)據(jù)：

　　1.查詢?nèi)?nbsp; select * from 表名

　　2.指定列名查詢 select 列 as 顯示列名 from 表名

　　查詢語法注意事項：

　　　　1.* 表示表中所有的列，也可以單獨查看指定的列，用列名將*替換掉；

　　　　2. 查詢所有的列比較影響性能，會將所有數(shù)據(jù)庫中的每一行進行每一列的刪選，所以要盡量根據(jù)業(yè)務(wù)需要的數(shù)據(jù)列進行查詢

一、查詢?nèi)?/span>

-- 查詢姓、名、工資和工作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后的薪水

　　　　方法一：這樣查詢只能查看增加后的結(jié)果，但是不能持久保存，且列名是 'salary+500’

select  first_name ,salary+500  from  emp；

　　　　方法二：對數(shù)據(jù)列起別名（as 新列名），這樣就對數(shù)據(jù)進行了保存，可以通過別名對數(shù)據(jù)進行查詢

select  first_name ,salary+500  as  salary  from  emp；

　　對表取別名，便于在已經(jīng)查詢的數(shù)據(jù)中進行二次查詢

select  e.employee_id  ,e.salary  ,e.manager_id   from  emp  as  e；

　　將兩列數(shù)據(jù)合并到一起concat查詢：

select  concat(first_name,last_name)  as   empname  from  emp;

排序查詢：

　　select 列名 from 表名 where 條件 order by 列

　　　　升序排序 asc（默認值）

　　　　降序排列 desc

--根據(jù)工資由高到低查看部門編號為50的員工姓名和薪資
select  concat(first_name,last_name)  as  empname, salary from emp  where   department_id=50  order   by   salary   desc;

　　新建一張表數(shù)據(jù)，進行排序測試：

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);

　　進行數(shù)據(jù)排序測試：

-- 對test表中的ab字段進行排序，對a和b都進行降序排序

-- 將a列進行降序排列
select *from test order by a desc;

-- 先對a進行升序排列，如果a有重復的數(shù)據(jù)，在重復的過程中對b進行升序排列
-- 當對多列進行排序時，將排序前一個列的值，如果前一個列的值有出現(xiàn)重復的情況，才會對第二個列繼續(xù)排序
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;

高級查詢

　　聚合函數(shù)：

　　　　sum：求和

　　　　avg :平均值

　　　　max：最大值

　　　　min：最小值

　　　　count：行數(shù)

-- 查詢部門編號為40的員工薪資的總和
-- sum的結(jié)果只有一個值，如果出現(xiàn)非聚合列，則只會出現(xiàn)第一個，不允許這么寫
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;


-- 查詢員工表中有多少員工信息
-- 只要這個行中只有一個列中有數(shù)據(jù)，寫*也會統(tǒng)計進去
select count (*) from emp;    -- 107
select count (first_name) from emp;   -- 107
-- 其中一個數(shù)據(jù)的數(shù)值為null
select count (manager_id) from emp;   -- 106

課上練習：（在數(shù)據(jù)修改前將表格進行備份）

　　修改編號為100的員工的薪資，增加500塊
　　修改姓名中包含s（不區(qū)分大小寫）的員工，將其部門調(diào)整到編號為50的部門中
　　調(diào)整老板的薪資增加5000
　　將所有薪資低于6000的員工薪資上浮500

-- 創(chuàng)建備份數(shù)據(jù)
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（不區(qū)分大小寫）的員工，將其部門調(diào)整到編號為50的部門中
update emp set department_id=50 where concat(forst_name,last_name) like '%s%' or concat(forst_name,last_name) like '%S%';

-- 調(diào)整老板的薪資增加5000
update emp set salary=salary+5000 where manager_id is null;

-- 將所有薪資低于6000的員工薪資上浮500
update emp set salary=salary+500 where salary<6000;

-- 查看數(shù)據(jù)表信息
select * from location;
select * from emp;
select * from dep;