mysql基础 进阶 提升

>>> 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等这些模糊的匹配条件