mysql回顾复习

数据库操作

F5更新 Navicat中语句结尾写不写；都可以

Sql命令行基础语句

net start/stop mysql 启动/停止数据库

mysql -uroot -proot/p 输入密码进入数据库

create database teacher character set utf8; /create database teacher；创建teacher数据库

create table t_student(

id int(10),

name varchar(20),

sex char(2),

birthday date,

email varchar(30)

); 创建表

show create table teacher;显示建表语句

show databases;查看当前数据库

use teacher; 使用teacher数据库

show tables;查看该数据库下的表

show tables from zhy;查看其他数据库下的表

desc sys_user;显示zhy数据库下的表结构

select database(); 查询该数据库

drop database teacher； 删除数据库

show variables like ‘%char%’；显示数据库编码

set character_set_results = utf8; 设置数据库编码为utf8

mysql --version 显示MySQL的版本

drop table if exists t_student; 如果有学生表则删除

drop table t_student; 删除学生表

select * from t_student;查询表中语句

create table stu as select id,name from t_student; 复制t_student表，创建一张新的表stu

INSERT into t_student(id,name,sex,birthday,email) VALUES(2,“zz”,“男”,“2000-02-12”,“4564541”) 插入语句

INSERT into t_student(id,name,sex,birthday,email) VALUES(3,“zy”,“男”,now(),“43474541”) 插入数据，时间为当前时间

INSERT into t_student(id,name) VALUES（1，张），（2，王），（3，李）；插入多项数据

UPDATE t_student set sex = ‘女’ WHERE id = ‘2’; 修改数据

DELETE FROM t_student WHERE id = ‘2’ ；删除数据

ALTER TABLE t_student ADD tel VARCHAR(40); 添加字段

ALTER TABLE t_student modify name VARCHAR(40); 修改字段

ALTER TABLE t_student change sex gender char(2) not null;//更改列名sex——gender

ALTER TABLE t_student drop tel ; 删除字段

select count(*) from EMP; //查询出emp表中总共有多少条记录。

exit；结束进程

外键约束

1、非空约束：not null

create table t_student(

id int(10),

name varchar(20) not null,

sex char(2),

birthday date,

email varchar(30)

);

2、唯一约束：unique

create table t_student(

id int(10),

name varchar(20) not null,

sex char(2),

birthday date,

email varchar(30) unique //插入的数据邮箱不能重复

);

3、自定义唯一约束名称

create table t_student(

id int(10),

name varchar(20) not null,

sex char(2),

birthday date,

email varchar(30) ，

constraint email_unique unique(email)

);

4、主键约束：primary key（pk)

create table t_student(

id int(10) primary key,//插入数据主键不能相同

name varchar(20) not null,

sex char(2),

birthday date,

email varchar(30)

);

自定义主键约束名称

+constraint 名字 primary key（id）

5、外键约束：foreign key (fk)

创建外键

CREATE TABLE t_classes(

classes_id int(3),

classes_name varchar(40),

CONSTRAINT pk_classes_id FOREIGN KEY(classes_id)

)

关联外键 在t_student中插入的classes_id,t_classes要有这个classes_id的值

create TABLE t_student(student_id int(10),

student_name VARCHAR(20),

sex char(2),

brithday date,

email VARCHAR(20),

classes_id int(3),

CONSTRAINT student_id_pk FOREIGN KEY(student_id),

CONSTRAINT fk_classes_id FOREIGN KEY(classes_id) REFERENCES t_classes(classes_id))

自动递增

直接点击自动递增或 id int(10) primary key atuo_increment

6、查看约束

use information_schama; 进入系统数据库

select * from table_constraints where table_name = ‘t_student’; 查看学生表约束

级联操作 父表中更新或删除，子表也会更新或删除

Cascade

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ecKFcZjB-1658278235792)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps1.jpg)]

查询操作

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j5nKTMxC-1658278235793)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps2.jpg)]

Select 字段 from 表 select id,name,sex from student

select *from表 表示查询表中所有字段

select id,name,sex,salary*12 from employee //salary月薪 *12 表示年薪

****定义别名：****3种都可以

（1）select empno as 员工编号，name as 员工姓名，salary * 12 as 年薪 from employee as emp;

（2）select empno as ‘员工编号’，name as ‘员工姓名’，salary * 12 as ‘年薪’ from employee as emp;

（3）select empno‘员工编号’，name‘员工姓名’，salary * 12‘年薪’from employee emp;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wft1apWU-1658278235793)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps3.jpg)]

*查询符号：*

select id,name,sex,salary from employee where salary =3000; //查询月薪3000的字段

select id,name,sex,job from employee where job = ‘manager’; //数据不加‘’，字符串加

select id,name,sex,salary from employee where salary between 1000 and 3000;//查询月薪在1000-3000之间的字段 包含最大值和最小值

select * from employee where complement is null; //查询数据库complement字段是否为空 0不算空

select * from employee where job = ‘manager’ and salary >1500;

select * from employee where job = ‘manager’ or salary >1500;

select * from employee where salary >1500 and (depton = 20 or deptno = 30); //查询工资大于1500的所属deptno（部门）为20或30的部门。

select * from employee where job in (‘manager’,’salesman’); 相当于or条件

select * from employee where salary in(1000，3000); //表示两个值

select * from employee where not (salary = 1000 or salary = 3000);

select * from employee where ename like ‘M%’ / ‘%N’ / ‘%A%’ / ’ _B%’;

//模糊查询(like)查询姓名第一个为M的/最后一个字为N的/名字中有A的/第二个字为B的

****排序查询：****order by

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SP2GATeS-1658278235794)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps4.jpg)]

select * from employee order by salary desc; //月薪降序

select * from employee order by salary asc; //月薪升序

select * from employee where job = ‘manager’ order by salary desc; //职位为经理的月薪降序

select * from employee order by job asc, salary desc; //职位按字典升序，其中相同职位的月薪降序

*去重操作*：distinct

Select distinct job from employee;

数据处理函数/单行处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wvzX151v-1658278235794)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps6.jpg)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dOuydmHJ-1658278235794)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps7.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bKB9SBg1-1658278235795)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps8.jpg)]

*Lower：*

Select lower(ename) from employee; //查询姓名，并把姓名转换为小写

Update employee set ename = (select na from(select lower(ename)na from employee where empno = ‘1002’) as a ) where empno = ‘1002’; //最内层把编号为1002的姓名转为小写，na表示列表的别名 ，外层查询na 并更新 没有外层查询不可以

*Upper：*

Select upper(ename) from employee; //只是在这一次查询中转化大写，并不改变原来数据

*Substr:*

Select * from employee where substr(ename , 1, 1) = ‘M’; //查询表姓名中含有第一个字为M的名字，取一个字符 (ename , 1, 1)从1开始取一个字符

*Length：*

Select length(ename) from employee; //查询员工姓名的字符长度

MySQL：utf-8一个汉字占3位，GBK一个汉字占2位

*Trim：*

select * from employee where job =trim( ‘ manager’);

*Str_do_date:*

select * from employee where date = str_do_date(‘1981-02-20’,’%Y-%m-%d’);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FxWFeaKm-1658278235795)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps9.jpg)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ccIkofkB-1658278235796)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps10.jpg)]

*Date_format:*

Select empno,ename,date_format(date,’%Y-%m-%d %H:%i:%s’) as date from employee;

//查询日期精细到时分秒

Select date_format(now(),’%Y-%m-%d %H %i %s’); //查询当前时间

*Format：*

Select empno，ename，format(salary, 0) from employee; //给月薪加上千分位

(salary, 0) 0表示没有小数位

*Round：*

Select round(123.56); //四舍五入 =124 round(avg(salary),2)

*Rand：*

Select rand();

Select * from employee order by rand(); //随机抽取记录数

*Case…when…then…else…end:* //基本在程序中使用

Select *,empno,ename,job,salary,

case job

when ‘manager’ then salary * 1.1

when ’saleman’ then salary * 1.5

end as newsalary

from employee

*Ifnull：*

数据库中若空值参与运算，结果一定为空值

例如 complement补贴为空null，则算年薪应该用ifnull语句

Select empno, ename, salary, (salary+ifnull(complement,0) * 12 )as yearsal from employee;

分组函数/聚合函数/多行处理函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yfNFIPbx-1658278235796)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps11.jpg)]

返回单一值，除了count ，其他分组函数自动忽略空值，不能直接使用在where后面

*Count：*

Select count() from employee; 或Select count(empno) from employee; //查询员工人数，建议使用主键不用号 Select count(*) c from employee; //可以加列名c

Select count(distinct job) from employee; //去重

*Sum:*

Select sum(salary) from employee;

Select sum(salary + ifnull(complement, 0)) from employee;

*Avg:*

Select avg(salary) from employee;

*Max:*

Select max(salary) from employee;

Select max(str_to_date(date , ‘%Y-%m-%d’)) from employee; //入职时间最晚的员工

*Min：*

Select min(salary) from employee;

分组查询：Group by

先分组后排序，order by放group by后面

Select job,sum(salary) from employee group by job;//每个岗位的工资合计

Select job,deptno,max(salary)from employee group by job,deptno;//查询不同岗位，不同部门的最高薪水

****Having：****对分组数据再过滤（聚合函数）

Select job,avg(salary) from employee group by job having avg(salary)>2000;//查看每个岗位平均工资大于2000的员工

Select max(salary) from employee group by job having job!= ’manager’;或

Select max(salary) from employee where job !=’manager’ group by job;//最好用先过滤再分组，效率高

连接查询/跨表查询/多表查询

*Join on:*

1、等值链接

Select e.ename, e.salary, d.dname from employee e join dept d on e.deptno = d.deptno where e.salary>2000; //e,d表示表的别名

2、非等值连接

Select e.ename, e.salary, s.grade from employee e join salgrade s on e.salary between s.losal and s.hisal;//查询员工表里的销售员介于最高值和最低值之间的薪资等级

3、内连接/自链接

Select e.ename ‘员工名’, m.mname ‘领导名’ from employee e, employee m where e. mgr=m.empno; //e. mgr=m.empno 领导编号和员工编号相同 查询每位员工的上级领导

4、外连接

左外连接：以左表为基准

Select e.ename, e.salary, d.dname from dept d left join employee e on e.deptno = d.deptno;

//d.dname 部门名称 dept d 以部门名称为主 查询员工信息，并展示其所属部门，如果一个部门没有员工，也要显示

右外连接：以右表为基准

Select e.ename, e.salary, d.dname from employee e right join dept d on e.deptno = d.deptno;

子查询

Select empno,ename,salary from employee where salary >(select avg(salary)from employee);

Select e.name,(select d.dname from dept d where e.deptno) as dname from employee e;

合并查询：union

union 可以不在一张表，但合并结果集查询字段需相同

Select * from employee where job in (‘manager’, ‘saleman’);

或

Select * from employee where job = ‘manager’

Union

Select * from employee where job = ‘saleman’

限制查询：limit

limit 获取一张表中某部分数据 limit默认从0开始取值

Select * from employee limit 2; //取前两条数据

Select * from employee limit 1,2; //从第二条开始取两条

Select * from employee e order by e.salary desc limit 5; //取薪水最高的前五名

分页查询：(currentPage -1) * perNum, perNum; //perNum 每页多少条数据

索引

*何时加索引：*

1、表中该字段中的数据量庞大

2、经常被检索，经常出现在where子句中的字段

3、字段不经常被修改

注意：建表不加索引，后期调优再加

*创建索引：*

Create [unique] index 索引名 on 表名（列名）

例：Create unique index u_name on employee(ename);

*删除索引：*

Alter table 表名 drop index 索引名

例：alter table emloyee drop index u_ename;

视图

*创建视图：*

Create view emdeptview as select empno,ename,job,e.deptno ,dname from employee e join dept d on e.deptno = d.deptno;//创建员工表和部门表的视图

Select * from emdeptview； //查询视图

*修改视图信息：*

Alter view emdeptview as…

*删除视图：*

Drop view emdeptvie;

*大小写：*

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-haZHjkd5-1658278235796)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps12.jpg)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kuI7HBNa-1658278235797)(file:///C:\Users\Lenovo\AppData\Local\Temp\ksohtml3548\wps13.jpg)]