柚子快報邀請碼778899分享：學習筆記MYSQL

http://yzkb.51969.com/

create table emp

(

id int comment '編號',

workno char comment '工號',

name varchar(10) comment '姓名',

gender char comment '性別',

age tinyint unsigned comment '年齡',

idcard char(18) comment '身份證號',

workaddress varchar(50) comment '住址',

entrydate date comment '入職時間'

) comment '員工表';

insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)

values (1, '1', '齊文濤', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(2, '1', '邊海霞', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(3, '1', '廖樂琴', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '巫海濤', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '葉林', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '樂樂', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '就戰(zhàn)機', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '迪麗熱巴·', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '齊文濤', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '齊文濤', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '齊文濤', '女', 18, '1111111111111111', '甘肅', '2024-01-01'),

(1, '1', '齊文濤', '女', 18, '1111111111111111', '甘肅', '2024-01-01');

select name, workno, age

from emp;

select *

from emp;

select workaddress as '工作地址'

from emp;

select distinct emp.workaddress

from emp;

select *

from emp

where age = '88';

select *

from emp

where age <= 20;

select *

from emp

where idcard is null;

select *

from emp

where idcard is not null;

select *

from emp

where age != 18;

select *

from emp

where age >= 15

and age <= 18;

select *

from emp

where age in (15, 18);

select *

from emp

where age between 15 and 20;

select *

from emp

where name like '__';

select *

from emp

where idcard like '%1';

select count(*)

from emp;

select count(emp.idcard)

from emp;

select avg(age)

from emp;

select max(age)

from emp;

select sum(age)

from emp

where workaddress = '甘肅';

-- where是分組之前過濾 having是分組之后的過濾，可用聚合函數(shù)判斷；

select gender, count(*)

from emp

group by gender;

select gender, avg(age)

from emp

group by gender;

select workaddress, count(*)

from emp

where age <= 20

group by workaddress

having count(*) >= 3;

select *

from emp

order by age asc;

select *

from emp

order by age desc;

select *

from emp

order by entrydate desc;

select *

from emp

order by age asc, entrydate desc;

select *

from emp

limit 0,10;

select *

from emp

limit 10;

select *

from emp

limit 10,10;

select *

from emp

where gender = '女'

and age = (20, 21, 22, 23);

select *

from emp

where name like '___'

and gender = '女'

and age between 15 and 20;

select gender, count(*)

from emp

where age <= 18

group by gender;

select name, age

from emp

where age <= 35

order by age asc, entrydate desc;

select *

from emp

where gender = '女'

and age between 14 and 20

order by age asc, entrydate asc

limit 5;

-- 執(zhí)行順序

-- from where group by having select order by limit

-- DCL管理數(shù)據(jù)庫用戶、控制數(shù)據(jù)庫訪問權(quán)限

create user 'itcast'@'localhost' identified by '123456';

-- 任意主機訪問

create user 'heima'@'%' identified by '123456';

-- 修改用戶密碼

alter user 'heima'@'%' identified with mysql_native_password by '1234';

drop user 'heima'@'%';

-- 權(quán)限管理

show grants for 'itcast'@'localhost';

show databases;

grant all on itcast.* to 'itcast'@'localhost';

use itcast;

show databases;

create user 'heima'@'localhost' identified by '123456';

grant all on itcast.* to 'heima'@'localhost';

show grants for 'heima'@'localhost';

revoke all on itcast.* from 'heima'@'localhost';

show grants for 'itcast'@'localhost';

-- 函數(shù)

select concat('hello', 'mysql');

select upper('hello');

select lpad('001', 5, '-');

select trim(' hello mysql ');

select substring('hello mysql', 1, 5);

-- 案例

alter table emp

modify column workno varchar(11);

update emp

set workno=lpad(workno, 5, '0');

-- 數(shù)值函數(shù)

-- ceil向上取整

select ceil(1.1);

-- floor 向下取整

select floor(1.1);

select mod(7, 4);

select rand();-- 0-1的隨機數(shù)

select round(2.344, 2);-- 求參數(shù)x的四舍五入的值，保留y位小數(shù)

select lpad(round(rand() * 1000000, 0), 6, '0');

-- 日期函數(shù)

select curdate();

select curtime();

select now();

select year(now());

-- data_add

select date_add(now(), INTERVAL 90 year);

-- datediff

select datediff(now(), '2006-06-20');

select name, datediff(curdate(), emp.entrydate) as 'entrydys'

from emp

order by entrydys desc;

-- 流程控制函數(shù)

select if(true, 'ok', 'error');

select ifnull('ok', 'default');

select ifnull('', 'default');

select ifnull(null, 'default');

-- case when then else end

-- 需求：查詢emp的員工姓名和工作地址區(qū)分一線和二線城市

select name,

(case emp.workaddress when '北京' then '一線' when '甘肅' then '十八線' end) as '工作地址'

from emp;

create table score

(

id int comment "id",

name varchar(20) comment '姓名',

math int comment '數(shù)學',

English int comment '英語',

chinese int comment '語文'

) comment '學院成績表';

insert into score()

values (1, 'Tom', 67, 66, 90),

(2, 'Rose', 90, 99, 98),

(3, 'Jack', 80, 85, 90);

select id,

name,

(case when math >= 85 then '優(yōu)秀' when math >= 60 then '及格' when math < 60 then '不及格' end) as '數(shù)學',

(case

when score.English >= 85 then '優(yōu)秀'

when score.English >= 60 then '及格'

when score.English < 60 then '不及格' end) as '英語',

(case when score.chinese >= 85 then '優(yōu)秀' when score.chinese >= 60 then '及格' else '不及格' end) as '語文'

from score;

select name, datediff(now(), '2000-11-12')

from emp;

-- 約束

create table user

(

id int primary key auto_increment comment '主鍵，自動增長',

name varchar(10) not null unique comment '姓名不為空，并且唯一',

age int check ( age > 0 and age <= 120 ) comment '年齡',

status char(1) default '1' comment '狀態(tài) 如果沒有指定默認為1',

gender char(1) comment '性別'

) comment '用戶表';

insert into user (name, age, status, gender)

values ('Tom', 18, 1, '男');

create table dept

(

id int auto_increment comment 'id' primary key,

name varchar(20) not null comment '職稱'

) comment '職稱表';

insert into dept(id, name)

values (1, '研發(fā)師'),

(2, '產(chǎn)品經(jīng)理'),

(3, '軟件工程師'),

(4, '項目經(jīng)理');

-- 添加外鍵

alter table emp

add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

alter table emp

drop foreign key fk_emp_dept_id;

-- 外鍵的刪除或更新行為

alter table emp

add constraint fk_emp_dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;

-- 多表查詢

-- 多對多

create table student

(

id int auto_increment primary key comment '主鍵',

name varchar(10) comment '姓名',

no varchar(10) comment '學號'

) comment '學生表';

insert into student

values (null, '齊文靜', '2209109052'),

(null, '齊文濤', '2209109053'),

(null, '林一', '2209109054'),

(null, '迪麗熱巴', '2209109055');

create table course

(

id int auto_increment primary key comment '主鍵ID',

name varchar(10) comment '課程名稱'

) comment '課程表';

insert into course

values (null, 'JAVA'),

(null, 'python'),

(null, 'mysql');

create table student_course

(

id int auto_increment primary key comment '主鍵',

studentid int not null comment '學生ID',

courseid int not null comment '課程ID',

constraint fk_courseid foreign key (courseid) references course (id),

constraint fk_studentid foreign key (studentid) references student (id)

) comment '學生課程中間表';

insert into student_course

values (null, 1, 1),

(null, 1, 2),

(null, 2, 3),

(null, 3, 3);

-- 一對一關系 多表拆分

-- 多表查詢 笛卡爾積

insert into dept

values (1, '工程師'),

(2, '研發(fā)部'),

(3, '項目部'),

(4, '軟件工程');

select *

from emp,

dept;

-- 內(nèi)連接 隱式 顯示

drop table employees;

create table A

(

id int primary key auto_increment comment '主鍵ID',

age int not null comment '年齡'

) comment '年齡表';

insert into A value (1, 10), (2, 11), (3, 12), (4, 13);

create table B

(

id int comment '主鍵',

name varchar(20) comment '姓名'

) comment '姓名表';

insert into B value (2, 'zahng'), (3, 'li'), (4, 'zhou'), (5, 'chen');

select *

from A,

B;

select A.age, B.name

from A,

B

where a.id = b.id;

create table employees

(

id int primary key auto_increment comment '主鍵',

name varchar(20) not null comment '姓名',

age int not null comment '年齡',

job varchar(20) comment '職業(yè)',

salary int comment '薪資',

entrydate date comment '入職時間',

managerid int comment 'ID',

dept_id int comment '外鍵'

);

insert into employees

values (1, '迪麗熱巴', 18, '形象代言人', 10000, '2014-09-11', 1, 1),

(2, '齊文濤', 18, '形象代言人', 10000, '2014-09-11', 2, 2),

(3, '迪麗', 88, '形象代言人', 10000, '2014-09-11', 1, 3),

(4, '熱巴', 58, '合伙人', 10000, '2014-09-11', 2, 3),

(5, '楊冪2', 18, '形象代言人', 10000, '2014-09-11', 1, 4),

(6, '來來', 40, '形象代言人', 10000, '2014-09-11', 2, 2),

(7, '呼呼', 18, '形象代言人', 10000, '2014-09-11', 1, 2),

(8, '弟弟', 18, '形象代言人', 10000, '2014-09-11', 1, 4),

(9, '咯咯咯', 18, '形象代言人', 10000, '2014-09-11', 1, 4);

create table dept

(

id int auto_increment primary key comment '主鍵',

name varchar(20) not null comment '職稱'

) comment '部門表';

insert into dept

values (1, '研發(fā)部'),

(2, '市場部'),

(3, '財務部'),

(4, '銷售部'),

(5, '總經(jīng)辦'),

(6, '人事部');

alter table employees

add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

select *

from employees,

dept;

select *

from employees,

dept

where employees.dept_id = dept.id;

-- 隱式內(nèi)鏈接

select employees.name, dept.name

from employees,

dept

where employees.dept_id = dept.id;

-- 顯示內(nèi)連接

select e.name, d.name

from employees e

inner join dept d on e.dept_id = d.id;

-- 外連接

select e.*, d.name

from employees e

left outer join dept d on e.dept_id = d.id;

select d.*, e.*

from employees e

right outer join dept d on e.dept_id = d.id;

-- 自連接

-- 員工和所屬領導

select a.name '員工', b.name '領導'

from employees a,

employees b

where a.managerid = b.id;

-- 所有員工emp及其領導emp 員工沒有領導也要查出來

select a.name '員工', b.name '領導'

from employees a

left join employees b on a.managerid = b.id;

-- 聯(lián)合查詢 多次查詢的結(jié)果合并 多張表的列數(shù)要一致

-- 將薪資高與5000和年齡大于18的員工查詢出來

select *

from employees

where salary > 5000

union

select*

from employees

where age > 19;

-- 子查詢SQL語句中嵌套SELECT語句 子查詢的外部可以是insert/update/select中的任何一個

-- 標量子查詢

-- 查詢銷售部的所有員工信息

select id

from dept

where name = '銷售部';

select *

from employees

where dept_id = (select id from dept where name = '銷售部');

-- 查詢齊文濤的入職 之后的員工信息

select employees.entrydate

from employees

where name = '齊文濤';

select *

from employees

where employees.entrydate > (select employees.entrydate from employees where name = '齊文濤');

-- 列子查詢

-- 查詢銷售部和市場部的所有員工信息

select id

from dept

where name = '銷售部'

or name = '市場部';

select *

from employees

where dept_id in (select id from dept where name = '銷售部' or name = '市場部');

-- 查詢比財務部所有人工資高的員工信息

select *

from employees

where salary > all (select employees.salary from employees where dept_id = (select id from dept where name = '財務部'));

-- 查詢比研發(fā)部任意一人工資高的員工信息

select *

from employees

where salary > any (select employees.salary from employees where dept_id = (select id from dept where name = '財務部'));

-- 行子查詢

-- 查詢與齊文濤有關的及直屬領導相同的員工信息

select salary, employees.managerid

from employees

where name = '齊文濤';

select *

from employees

where (salary, managerid) = (select salary, employees.managerid from employees where name = '齊文濤');

-- 表子查詢

-- 查詢和‘齊文濤’，‘熱巴’的職位和薪資相同的員工信息

select employees.job, employees.salary

from employees

where name = '齊文濤'

or name = '熱巴';

select *

from employees

where (job, salary) in (select employees.job, employees.salary from employees where name = '齊文濤' or name = '熱巴')

-- 查詢?nèi)肼毴掌谑?2006-01-01'及其以后的員工信息,及其部門信息

select *

from employees

where entrydate > '2006-01-01';

select *

from (select *from employees where entrydate > '2006-01-01') e

left join dept d on e.dept_id = d.id;

create table salgrade

(

grade int comment '成績',

losal int comment '最低薪資',

hisal int comment '最高薪資'

) comment '薪資等級表';

insert into salgrade

values (1, 0, 3000)

, (2, 3001, 5000)

, (3, 5001, 8000)

, (4, 8001, 10000)

, (5, 10001, 15000)

, (6, 15001, 20000)

, (7, 20001, 25000)

, (8, 25001, 3000);

-- 查詢員工姓名、年齡、職位、部門信息（隱式內(nèi)連接）

select employees.name, age, job, dept.name

from employees,

dept

where employees.dept_id = dept.id;

-- 查詢年齡小于30的員工姓名、年齡、職位、部門信息（顯示內(nèi)鏈接）

select e.name, age, job, d.name

from employees e

inner join dept d on e.dept_id = d.id

where e.age < 30;

-- 查詢擁有員工的部門id、部門名稱 distinct 去重

select distinct d.id, d.name

from employees e

left join dept d on d.id = e.dept_id;

select distinct d.id, d.name

from employees e,

dept d

where d.id = e.dept_id;

-- 查詢所有年齡大于40歲的員工，及其部門名稱，如果員工沒有分配部門，也要展示出來 外連接

select e.*, d.name

from employees e

left outer join dept d on e.dept_id = d.id

where age > 40;

-- 查詢所有員工的薪資等級

-- 連接條件：emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal

select e.*, salgrade.grade

from employees e,

salgrade

where e.salary >= salgrade.losal

and e.salary <= salgrade.hisal;

select e.*, salgrade.grade

from employees e,

salgrade

where salary between salgrade.losal and salgrade.hisal;

-- 查詢研發(fā)部所有員工的信息及工資等級

select e.name, d.name, s.grade

from employees e,

dept d,

salgrade s

where d.id = e.dept_id

and (salary between s.losal and s.hisal)

and d.name = '研發(fā)部';

-- 查詢研發(fā)部員工平均工資

select avg(e.salary) from employees e,dept d where d.id=e.dept_id and d.name='研發(fā)部';

-- 查詢工資比迪麗熱巴高的員工

select employees.salary from employees where name='迪麗熱巴';

select *

from employees where salary>(select employees.salary from employees where name='迪麗熱巴');

-- 查詢比平均薪資高的員工的信息

select *from employees where salary>(select avg(salary) from employees);

-- 低于本部門平均工資的員工

select avg(salary)from employees e where dept_id=2;

select *from employees e2 where e2.salary<(select avg(salary)from employees e1 where e1.dept_id=e2.dept_id);

select *,(select avg(salary)from employees e1 where e1.dept_id=e2.dept_id)'平均'from employees e2 where e2.salary<(select avg(salary)from employees e1 where e1.dept_id=e2.dept_id);

-- 查詢所有部門信息，并統(tǒng)計部門的員工數(shù)量

select count(*)from employees where dept_id=1;

select *,(select count(*)from employees where dept_id=dept.id)'人數(shù)'from dept;

-- 查詢所有學生選課情況，展示出學生姓名，學號，課程情況

select s.name,s.no,c.name from student s ,student_course sc,course c where s.id=sc.studentid and c.id=sc.courseid;

select s.name,s.no,c.name from student s left join student_course sc on s.id = sc.studentid left join course c on c.id = sc.courseid;

select *from course c left join student_course sc on c.id = sc.courseid;

-- 事務

create table account(

id int auto_increment primary key comment '主鍵ID',

name varchar(10) comment '姓名',

money int comment '余額'

)comment '賬戶表';

insert into account(id,name,money) values (null,'張三',2000),(null,'李四',2000);

-- 恢復數(shù)據(jù)

update account set money=2000 where name='張三' or name='李四';

select @@autocommit;

set autocommit =1;

-- 轉(zhuǎn)賬操作

-- 查詢張余額

select *from account where name='張三';

-- 張三-1000

update account set money=money-1000 where name='張三';

程序拋出異常...

-- 李四 +1000

update account set money=money+1000 where name='李四';

-- 提交事務

commit ;

-- 回滾事務

rollback ;

-- 方式二

-- 開啟事務

start transaction;

-- 查詢張余額

select *from account where name='張三';

-- 張三-1000

update account set money=money-1000 where name='張三';

程序拋出異常...

-- 李四 +1000

update account set money=money+1000 where name='李四';

-- 提交事務

commit ;

-- 回滾事務

rollback ;

柚子快報邀請碼778899分享：學習筆記MYSQL

http://yzkb.51969.com/

推薦閱讀