从零开始MySql(基础操作篇)

Mysql 学习笔记

DDL(Data Definition Language)语句

创建数据库

create database test1;``

查询数据库

show databases;

mysql 自带表

mysql: 储存了系统的用户权限信息
information_schema:主要储存系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等、每个用户都可以查看这个数据库，但根据权限可查看的内容不同。
performance_schema：MySql5.5 引入的系统库，用户储存系统性能相关参数的动态参数表。
sys:MySql5.7 引入的系统库，本身不记录系统数据，给予performance_schema和information_schema之上，封装了一层更加易于调优和诊断的系统视图。

删除数据库 o

drop database test;

使用数据库表

use test;

查看数据库中的表

show tables;

创建表

create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));

关于主键相关的内容可以看我写的这篇文章

https://blog.lopponia.com/20201101/MySQL-engine-innodb/

查看表

desc emp;

或

show create table emp;

删除表

drop table emp;

修改表

修改表字段类型

alter table emp modify ename varchar(20);

增加表字段

alter table emp add colum age(10);

删除表字段

alter table emp drop colum age;

表字段改名

alter table emp change age age1 varchar(23);

ps:change 和modify 都可以修改表的定义，不同的是 change 还可以给字段改名
。

修改字段排列顺序

alter table emp add birth after sal;

修改表名

alter table emp rename employee;

DML(Data Manipulation Language)语句]

插入记录

insert into employee (ename, hiredate, sal, deptno) values ('姓名',now(),100.22,20);

也可以不用指定字段名称，这是插入数据会按照熟悉进行插入,没有填写的字段会被填充为NULL、默认值、自增的下一个数字，这样会在某种条件下大量节省 SQL 语句

insert into employee values ('姓名',now(),100.22,20);

不仅如此insert还可以一次插入多条记录

insert into employee (ename, hiredate, sal, deptno) values ('姓名',now(),100.22,20),('1',now(),200.33,12);

更新记录

update employee set ename = 'update' where ename = '姓名';

update也可以同时更新多个表内的记录,下面那条语句中的 employee 就是另一个表的表名。

update employee
set ename = 'update1'
where ename = 'update' and employee.hiredate=now();

删除记录

delete from employee where sal = 100.22;

delete 支持同时删除多个库中的数据

detele a,b from emplloyee a,dept b where a.deptno=b.deptno and a.deptno=3;

查询记录

标准查询

select * from employee;

其中*代表查询全部字段也可以指定要查询的字段

select ename,sal from employee;

查询不重复的记录

select distinct ename,sal from employee;

条件查询

select * from employee where ename = '1';

支持的运算符:<,>,<=,>=,!=,并支持使用 and 连接多个连接条件。

排序和限制

排序

select * from employee order by sal;

两个排序方式:默认 asc 正序， desc 倒序

分页限制

select * from employee limit 1
# 查询一条数据相当于0,1
select * from employee limit 1,1
# 查询从第二条记录开始的一条数据

聚合

很多情况下，用户需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数，这时候就要用到 sql 的聚合操作。
常用的聚合操作有sum(),count(*),avg(*),max(*),min(*)
group by 后面接需要聚合的字段
with rollup 是可选参数，表名是否对分类聚合后的结果进行在汇总。
having 关键字标识对分类后的结果再进行条件的过滤。
注意：having 和 where 的区别在于，having是对聚合后的结果进行条件的过滤，而where 是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用where先过滤记录，因为这样结果集减小、聚合的效率将大大提高，最后在根据逻辑看是否用having进行过滤。

select count(ename) from employee;
# 以deptno进行分组查询，相当于使用count()方法对分组查询的查询结果进行处理，返回出现次数
select deptno,count(ename) from employee group by deptno;
# 加上with rollup 之后相当于额外再对全部结果进行一次count()操作
select deptno,count(ename) from employee group by deptno with rollup;
# 语句中的as 可以对查询结果进行重命名
select deptno,count(1) as count from employee group by deptno with rollup having count(1)>1;
# select max(sal),min(sal),avg(sal) from employee;

表连接

当需要同时显示多个表中的字段时，可以使用表连接来实现。从大类上分，表连接分为内连接和外连接，他们之间的主要区别就是：内连接仅选出两张表中胡相匹配的记录，而外连接则会选出其他不匹配的记录。我们最常用的是内连接。
左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

select sur.user_id,nick_name,name
from sys_user
    left join sys_users_roles sur on sys_user.user_id = sur.user_id
    left join sys_role sr on sur.role_id = sr.role_id;

子查询

某些情况下，当进行查询的时候，需要的条件是另外一个 select 的查询结果，这个时候，就要用的子查询。用于子查询的关键字主要包括in,not in,=,!=,exist,not exist等。

# 如果子查询记录唯一则可以使用=来代替in
select nick_name from sys_user where user_id in (select user_id from sys_users_roles);
# 上述子查询可以转换为表连接查询
select nick_name from sys_user left join sys_users_roles sur on sys_user.user_id = sur.user_id;

ps:表连接在大多数情况下都由于子查询

记录联合

我们经常会遇到这样的应用，将两个表的数据按照一定的查询条件查询出来之后，将结果合并一起显示出来，这个时候就需要用 union 和 union all 关键字来实现这样的功能。（合并一列显示,名字以第一个查询查询语句的的目标字段名为主）

# union 全都放到一列
# union all 会进行一次去重
select nick_name as myUnion
from sys_user
union all
select name
from sys_role

DCL 语句(Data Control Language)

DCL 语句主要是 DBA 用来管理系统中的对象权限时使用。

操作权限:GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE

# 添加一个拥有全部增删改查权限且允许从任意host访问的用户lopponia,密码为xxxxxxxx
on 后接数据库与表demo:test.employee,*.*代表全部数据库与表
to 后接用户名与host demo:'lopponia'@'%'
by 后接密码
grant all on *.* to 'lopponia'@'%' identified by 'xxxxxxxxx';
# 刷新权限
flush privileges;