>>> mysql基础 <<<
需要修改配置文件时(端口port:3306)
cd /
ls
cd etc
ls
cd mysql
ls
cd mysql.conf.d/
ls
vim mysqld.cnf
etc键 :q
ctrl+c 打断当前的状态
查看mysql是否运行 在根目录下 service mysql status 若没有运行则在根目录下输入service mysql start/stop
服务开始了,开始登陆,在根目录下输入mima可以查看密码,有两个用户root(root123),admin(qwe123)
登录mysql:[若云服务器在本机,即本地连接]根目录下输入mysql -uadmin -p(可后面再输入qwe123)
[若云服务器不在本机,即远程连接]根目录下输入 mysql -h127.0.0.1 -p3306 -uadmin -p【-h:服务器ip地址,-p:mysql端口,1234是虚拟机的端口】
退出mysql:mysql>exit;
查看所有数据库:show databases;(分号必写,上下文同理)
进入或使用某个数据库xxx:use xxx;
查看当前所在数据库或用户:select database()/user();
MySQL语句规范 关键字 函数名称 全部大写
数据库名称 表名称 字段名称 全部小写 用反括号括起来
语句必须以分号结尾
创建数据库:create {database|schema} [if not exists] `xxx`;
在数据库里创建数据库会报错不会覆盖原数据库,'''create database if not exists xxx;'''-->这样不报错^--^
删除数据库:drop database [if exists] xxx;
查看某数据库内所有表:show tables[from xxx];(xxx为库名)
创建表:create table `yyy`(
-> `id` int,
->`name` varchar(20)
->);
括号内20为字符串长度,int类型默认是11位
查看表的创建信息:show create table yyy;
查看表内字段信息:describe yyy; / show columns from yyy;
删除表:drop table [if exists] yyy;
修改表的结构,字段:
添加单个:alter table yyy add age int;
添加多个:alter table yyy add (sex varchar(5),test1 int,test2 int);
添加索引:alter table yyy add index(s_name);
删除单个:alter table yyy drop test2;
删除字段:alter table vvv drop sex,drop address;
仅改字段类型:alter table yyy modify test1 varchar(50);
change 改列名和数据类型:alter table yyy change test1 address varchar(50);
改表名:alter table yyy rename vvv;
修改表的内容:
全字段添加数据:insert into vvv value(1,'xixi',18);
指定字段插入数据:insert into vvv(id,name) value(2,'lala');
插入多条数据:insert into vvv values(3,'cici','45'),(4,'gugu',23);
修改数据 update vvv set age=20 where name='lala';不加where默认修改全字段
删除表内容:delete from vvv where age=23;
查表内容:select * from vvv; 也可指定行来查看select age,name from vvv;
>>> mysql进阶 <<<
表约束:非空约束:给name添加约束:alter table vvv modify name varchar(19) not null;
唯一约束:给id添加约束:alter table vvv add unique key(id);
给多个添加就在括号内加入逗号隔开
删除唯一约束:alter table vvv drop key id;
主键约束:一张表只能设置一个主键,非空+唯一
给id添加约束:alter table vvv add primary key(id);
删除主键约束:alter table vvv drop primary key;
自增约束:alter table `vvv` change `id` `id` int not null auto_increment;
auto_increment 自动编号,且必须与主键组合使用,默认情况下,起始值为1,每次的增值为1
删除自增约束:alter table `vvv` change `id` `id` int not null;
默认约束:alter table vvv alter age set default 18;
正确的创建表的方法:create table mr(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> age int default 18,
-> phone varchar(11) unique key
-> )auto_increment=1000,charset='utf8';
---
表关系:
创建学院表create table department(
d_id int primary auto_increment,
d_name varchar(20) not null
);
desc department;
一对多
创建学生表create table student(
s_id int primary key auto_increment;
s_name varchar(20) not null,
dept_id int,
constraint s_d_key foreign key(dept_id)外键 references department(d_id)主键
on delete set null);
添加学院insert into department(d_name) values('艺术'),('计算机'),('英语'),('会计');
select * from department;
添加学生insert into student(s_name,dept_id) values('xiaohong',2),('xiaolan',3),('xiaohei',4),('xiaobai',1);
删除一个学院试试delete from department where d_id=2; 则dept_id is null
一对一
创建学生信息表create table stu_detail(
sd_id int primary key auto_increment,
s_age int,
s_sex varchar(20) default 'man',
s_id int unique key,
constraint s_sd_key foreign key(s_id) references student(s_id) on delete cascade);
desc stu_detail;
添加学生insert into stu_detail(s_age,s_id) values(20,4),(22,3),(21,1),(18,2);
select * from stu_detail;
多对多
创建课程表create table cource(
c_id int primary key auto_increment,
c_name varchar(20) not null
);
创建选课表create table stu_cource(
s_id int,
c_id int,
primary key(s_id,c_id),联合主键
constraint s_s_key foreign key(s_id) references student(s_id) on delete cascade,
constraint s_c_key foreign key(c_id) references course(c_id) on delete cascade);
添加课程insert into cource(c_name) values('python'),('mysql'),('redis'),('mongdb');
select * from cource;
学生选课insert into stu_cource values(1,1),(1,4),(2,3),(2,2),(3,2);
select * from stu_cource;
---
数据库的三范式:第一范式:表的结构复杂,操作麻烦;
第二范式:表结构简单,各表之间存在重复数据
第三范式 范式的作用:尽可能避免数据的冗余和插入/删除/更新的异常
---
数据库事务:几个操作是绑在一起的,要么都做完,要么一个都不做。
事务之内,数据不记录,提交后才行
开始事务:begin;
提交,结束事务:commit;
回滚事务,撤销之前的操作:rollback;
---
查询学生的选课详情:select s_name,c_name from student
inner join stu_cource on student.s_id=stu_cource.s_id
inner join cource on cource.c_id=stu_cource.c_id;
视图:
创建视图:create view s_c as select s_name,c_name from student
inner join stu_cource on student.s_id=stu_cource.s_id
inner join cource on cource.c_id=stu_cource.c_id;
select * from s_c;
删除视图:drop view s_c;
视图的意义:s_c是一个虚拟的表,视图里面的内容是不允许增删改查的
>>> mysql 提升 <<<
单表查询
全字段查询:select * from 表名;
指定字段查询:select 字段1,字段2 from 表名;
条件查询:select * from 表名 where 字段1=值1 and 字段2=值2; and or > < = 均可
模糊查询:select * from student where s_name like '%ong';
内连接 笛卡尔坐标轴 无条件链接
表联结的查询:select * from A表 inner join B表 on A表.主键字段=B表.外键字段;
select * from A表,B表 where A表.主键字段=B表.外键字段;
select * from A表 left/right join B表 on A表.主键字段=B表.外键字段;
select * from A表 where 字段=(select 字段 from B表 where 字段=值);
select * from A表 inner join B表 on A表.s_id=B表.s_id;
学院与学生的连接查询:
select * from department,student where department.d_id=student.dept_Id;
或者
select * from department inner/cross join student on/where department.d_id=student.dept_id;
或者
select dt.d_name,s.s_name from department as dt inner join student as s on dt.d_id=s.dept_id;
学生与课程的联结查询:
select * from student inner join stu_cource on student.s_id=stu_cource.s_id;
->
select * from student inner join stu_cource on student.s_id=stu_cource.s_id inner join cource on stu_cource.c_id=cource.c_id;
->
select s_name,c_name from student inner join stu_cource on student.s_id=stu_cource.s_id
inner join cource on stu_cource.c_id=cource.c_id;
学院 学生 课程的联结查询:
select * from student inner join stu_cource on student.s_id=stu_cource.s_id
inner join cource on stu_cource.c_id=cource.c_id
inner join department on student.dept_id=department.d_id;
->
select d_name,s_name,c_name from student
inner join stu_cource on student.s_id=stu_cource.s_id
inner join cource on stu_cource.c_id=cource.c_id
inner join department on student.dept_id=department.d_id;
外连接
左连接:select * from student left join department on student.dept_id=department.d_id;
这时以左边的student为主,没有分院的学生也被列示
右连接:select * from student right join department on student.dept_id=department.d_id;
这时以右边的department为主,没有学生的学院也被列示
---
子表查询:
查学生的选课情况:
select * from student inner join stu_cource on student.s_id=stu_cource.s_id;
select s_name,c_name from cource inner join(select s_name,c_id from student inner join stu_cource on student.s_id=stu_cource.s_id) as e on cource.c_id=e.c_id;
查计算学院的学生:
select * from student where dept_id=(select d_id from department where d_name='计算机');
排序查询:select * from 表名 order by 字段;默认是升序asc,降序是desc
select s_name,s_age,s_sex from student inner join stu_detail on student.s_id=stu_detail.s_id order by s_age;
限制数量查询:
select s_name,s_age,s_sex from student inner join stu_detail on student.s_id=stu_detail.s_id order by s_age order by s_age desc limit 3;
从索引为0开始,获取前三条:select * from 表名 order by 字段 limit 0,3;
分组查询 统计各学院人数
select d_id,d_name,count(*) from department inner join student on department.d_id=student.dept_id group by d_id,d_name;
select d_id,d_name,count(*) from department inner join student on department.d_id=student.dept_id group by d_id,d_name having count(*)<2;
---
处理null:select s_name,ifnull(dept_id,1) from student;
字段去重:select distinct 字段 from 表名;
字符串截取:left从左截取 right从右截取 substring指定截取范围
截取某表中字段的前两个字符:select left(字段,2) from 表名;
查询 最大/最小/平均/总和 年龄:
select max/min/avg/sum(s_age) from student inner join stu_detail on student.s_id=stu_detail.s_id;
mysql优化: 尽量避免整表扫描
建立合适的索引
使用合适的存储索引
在join中,尽量用小表left join大表
除非十分必要,尽量不要使用order by,group by,distinct(去重),尽量用索引代替
from后为子表,join后为主表,使用*子表会被执行n次
想要使SQL执行快一点,应避免使用like,in,not in等这些模糊的匹配条件