数据库操作
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)]