MYSQL笔记

32216

一·

1.社区版mysql官方地址 http://dev.mysql.com/downloads/windows/installer/8.0.html

2.phpstudy一定要关了里面集成有mysql

3.命令行（管理员）

启动  net start mysql80

关闭  net stop mysql80

命令行（非管理员

连接数据库 mysql [-h 127.0.0.1] [-P 3306] -u root -p

4.mysql

       1.关系型数据库：通过表来存储数据的数据库           \非关系型数据库：不通过表来存储数据的数据库

       2.数据模型：客户端——DBMS——数据库（多个—–表（存放数据）（多个

二·

1.通用语法

       1.sql语句可单行或多行，以分号结尾。

       2.可用空格或缩进来增强可读性

       3.mysql数据库不区分大小写（关键字建议用大写

       4.单行注释（    –或者#    ）    多行注释（  /*内容*/    ）

2.sql分类

       1.DDL(data definition language)数据定义语言,用来定义数据库对象(数据库，表，字段)

2.DML(data manipulation language)数据操作语言，用来对数据库中的数据进行曾删改

3. DQL(data query language)数据库查询语言，用来查询数据库中表的记录

4.DCL(data control language)数据库控制语言，用来创建数据库用户，控制数据库的访问权限

DDL

1.数据库操作

                       查询所有数据库SHOW DATABASES;

                         查询当前数据库SELECT DATABASE();

创建：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排列顺序];

                            删除：DROP DATABASE [IF EXISTS]数据库名;

                            使用：USE 数据库名;

                                          2. 表操作

                     CREATE TABLE 表名(

                            字段1 字段1类型 [COMMENT 字段1的注释],

                            字段2 字段2类型 [COMMENT 字段2的注释],

                            字段3 字段3类型 [COMMENT 字段3的注释],

                            ……

                            字段n 字段n类型 [COMMENT 字段n的注释],

                     )[COMMENT 表注释];

图片[1],MYSQL笔记,网络安全爱好者中心-神域博客网

数值：Age TINYINT UNSIGNED               Socre double(4,1)

图片[2],MYSQL笔记,网络安全爱好者中心-神域博客网

字符：char(10)不足10个字符自动补上空格    varchar(10)性能较差

图片[3],MYSQL笔记,网络安全爱好者中心-神域博客网

时间：birthday date

图片[4],MYSQL笔记,网络安全爱好者中心-神域博客网

查询当前数据库中的所有表：SHOW TABLES;

查询表结构：DESC 表名;

查询指定表的建表语句：SHOW CREATE TABLE 表名;

图片[5],MYSQL笔记,网络安全爱好者中心-神域博客网

   3.修改

添加字段：

ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT注释][约束];

修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)；

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段 新字段名 类型(长度)[COMMENT注释][约束];

删除字段：

ALTER TABLE 表名 DROP 字段名;

例：alert table emp add nickname varchar(20) comment ‘昵称’;

ALTER table emp change nickname username varchar(30) comment ‘用户名’;

ALTER TABLE EMP DROP username;

修改表名:

ALTER TABLE 表名RENAME TO 新表名;

删除表：

DROP TABLE[IF EXISTS] 表名;

删除指定表，并重新创建该表：

TRUNCATE TABLE 表名; (只删除内容，不删除字段和框架)

例：alter table emp rename to employee;

drop table if exists tb_user;

truncate table employee

MYSQL图像化界面工具：

              sqlyog                         navicat                    datagrip

DML(数据操作语言)

                                  1.

添加数据

给指定字段添加数据:

INSERT INTO  表名 (字段1,字段2,,,) VALUES(值1,值2,,,,);

给全部字段添加数据:

INSERT INTO 表名 VALUE (值1,值2,,,,);

批量添加数据:

INSERT INTO 表名 (字段名1,字段2,,,) VALUES (值1,值2,,,), (值1,值2,,,), (值1,值2,,,);

INSERT INTO 表名 VALUES (值1,值2,,,), (值1,值2,,,), (值1,值2,,,);

修改数据

UPDATE 表名 SET 字段名1=值1，字段名2=值2,,,,,[WHERE 条件];

删除数据

DELETE FROM 表名 [WHERE 条件];

DQL

SELECT 字段列表

FROM 表名列表                               基本查询

WHERE 条件列表                             条件查询

GROUP BY 分组字段列表          聚合函数（count,max,min,avg,sum）

HAVING 分组后条件列表                 分组查询

ORDER BY 排序字段列表                  排序字段查询

LIMIT 分页参数                               分页查询

基础查询

1,查询多个字段

SELECT 字段1,字段2,字段3……FROM 表名;

SELECT * FROM 表名;

2,设置别名

Select 字段1[AS 别名1],字段2[AS 别名]…… FROM 表名;

3,去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1,语法

SELECT 字段列表 FROM 表名 where 条件列表;

2,条件

select * from emp where age<23 && age>=18; select *from emp where age between 12 and 30;select *from emp where idcard  is not null;select *from emp where age <> 20;select *from emp where gender =’女’ and age<28;
select *from emp where age =23 or age =22 or age =29;
select *from emp where age in(22,23,29);select *from emp where name like ‘__’;
select *from emp where idcard like ‘%9’;

图片[6],MYSQL笔记,网络安全爱好者中心-神域博客网

3,聚合函数：将一列数据作为一个整体，进行纵向计算.

Count 统计数量        max 最大值      min 最小值      avg 平均值     sum 求和

Select 聚合函数(字段列表) FROM 表名; select count(*) from emp;
select avg(age)from emp;
select max(age)from emp;
select min(age)from emp;
select sum(age) from emp where workaddress = ‘背景’;

*所有的null值不参与运算;

4.分组查询

Select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组过滤条件];

Where和having区别

执行条件不同：where是分组前进行过滤,having是分组之后对结果进行过滤。

判断条件不同; where不能对聚合函数进行判断，而having可以。

执行顺序:where > 聚合函数>having

分组之后,查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义 select gender, count(*)from emp group by gender ;
select gender,avg(age) from emp group by gender;
select  workaddress,count(*) from emp where age<45 group by workaddress having count(*) >=3;
select  workaddress,count(*)  address_count from emp where age<45 group by workaddress having address_count >=3;

5.排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2,排序方式2;

ASC升序    DESC 降序   第一个相同才根据第二个字段进行排序 select *from emp order by age desc;
select * from emp order by age;
select * from emp order by entrydate desc;
select * from emp order by age asc ,entrydate desc;

6,分页查询

SELECT 字段列表 FROM 表名 LIMT 起始索引 ，查询记录数;

注意：

起始索引从零开始，起始索引=(查询页码-1)*每页显示的记录数.

分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是LIMT .

如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10. select * from emp limit 0,5;
select * from emp limit 5;
select * from emp limit 5,5;

dql巩固加强: select * from emp where gender =’女’and age in(20,21,23);
select * from emp where gender =’男’ and age between 20 and 40 and name like ‘___’;
select gender,count(*) from emp where age<60 group by gender;
select name ,age from emp where age<=35 order by age , entrydate desc;
select * from emp where gender = ‘男’and age between 20 and 40 order by age asc,entrydate asc limit 5;

执行顺序:

1,from 2,where3,group by having 4,select 5.ouderby 6.limit; select name,age from emp where age>15 order by age asc;
select name,age from emp e where e.age>15 order by age asc;
select e.name,e.age from emp e where e.age>15 order by age asc;
select e.name ename,e.age eage from emp e where e.age>15 order by eage asc;

DCL

控制数据库有哪些用户可以访问

控制每个用户有怎样的访问权限

1.查询用户

Use mysql;

Select * from user;

2.创建用户

Create user ‘用户名’@’主机名’ IDENTIFIED BY ‘密码’;

3.修改用户密码

ALTER USER ‘用户名’@’主机名’ identified WITH mysql_native_password BY ‘新密码’;

4.删除用户

DROP USER ‘用户名’@’主机名‘； create user ‘biu’@’localhost’ identified by ‘123456’;
create user ‘biu1’@’%’ identified by ‘123456’;
alter user  ‘biu1’@’%’ identified with mysql_native_password by ‘1234’;
drop user ‘biu1’@’%’ ;

权限控制

图片[7],MYSQL笔记,网络安全爱好者中心-神域博客网
  1. 查询权限

Show grants for ‘用户名’@’主机名’ ;

  • 授权权限

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’;

  • 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’; create user ‘biu’@’localhost’ identified by ‘123456’;
alter user  ‘biu1’@’%’ identified with mysql_native_password by ‘1234’;
drop user ‘biu1’@’%’ ;
show grants for ‘biu’@’localhost’;
grant all on biu801.* to ‘biu’@’localhost’;
revoke all on biu801.*  from ‘biu’@’localhost’;

三，

函数：是指一段可以直接被另一段程序调用或代码。

字符串函数

Concat(s1,s2,s3…sn)   字符串拼接，将s1.s2.s3..sn拼接成一个字符串

Lower(str)            将字符串str全部转成小写

Upper(str)                    全部转化成大写

Lpad(str,n,pad)            左填充，用字符串pad对str左边填充，达到n个字符串

Rpad(str,n,pad)            右填充，

Trim(str)                      去掉字符串头部和尾部的空格

Substring                    返回从字符串str从start位置起的len个长度的字符串 select concat(‘hello’,’mysql’);
select lower(‘HELLO’);
select upper(‘hello’);
select lpad(‘hello’,10,’haa’);
select rpad(‘hello’,10,’haa’);
select trim(‘ hello  mysql ‘);
select substring(‘hello mysql’,1,5);

数值函数

Ceil(x)                   向上取整

Floor(x)                 向下取整

Mod(x,y)               返回x/y的模

Rand()                  返回0-1内的随机数

Round(x,y)            求参数x的四舍五入的值，保留y位小数 select ceil(1.4);
select floor(1.8);
select mod(3,4);
select rand();
select round(2.34,2);
select round(2.345,2);# 求一个6位随机数
select  lpad(round(rand()*1000000,0),6,’0′);

日期函数

Curdate()                     返回当前日期

Curtime()                     返回当前时间

Now()                          返回当前日期和时间

Year(data)            获取指定date的月份

Month(date)                获取指定date的月份

Day(date)                    获取指定date的日期

Date_add(date,interval expr type)  返回一个日期加上时间间隔expr后的时间值

Datediff (date1,date2)         返回起始时间date和结束时间date2之间的天数 select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(),interval 39 day);
select datediff(now(),date_add(now(),interval 39 day));求入职时间
select name ,datediff(curdate(),entrydate) as ‘entrydates’ from emp order by entrydates desc;

流程控制函数

If(value,t,f)            如果value为true，则返回t，否者返回f

Ifnull(value1,value2) 如果value1不为空返回value1否则放回value2

Case when [val1] then [res1] ..else [default] end      如果val1为true返回res1 否则返回default默认值

Calse [expr] when [val1] then [res1] ..else [default] end         如果expr的值等于val1,返回res2，否则返回default默认值 select if (true,’ok’,’error’);
select ifnull (‘ok’,’default’);      
select ifnull(”,’default’);
select ifnull(null,’default’);
select ifnull (‘null’,’default’);
select name,
       (case workaddress when’北京’ then ‘一线城市’ when ‘上海’ then ‘一线城市’ else ‘二线城市’end)as ‘工作地址’
from emp;
select
       id,
       name,
       case when math>=85 then’优秀’ when math>=60 then ‘及格’ else ‘不及格’end)’数学’,
       case when english>=85 then’优秀’ when english>=60 then ‘及格’ else ‘不及格’end)’英语’,
       case when chinese>=85 then’优秀’ when chinses>=60 then ‘及格’ else ‘不及格’end)’语文’,
from score;

四,

约束:作用于表中的字段上的规则,用于限制储存在表中的数据.

保证数据库中数据的正确性,有效性,完整性

图片[8],MYSQL笔记,网络安全爱好者中心-神域博客网

create table user(
    id int primary key auto_increment comment ‘主键’,
    name varchar(10) not null unique comment ‘姓名’,
    age int check(age>0&&age<=120)comment ‘年龄’,
    statuss char(1)default ‘1’ comment ‘状态’,
    gender char(1) comment ‘性别’
)comment ‘用户表’;insert into user(name,age,statuss,gender)values(‘tom1′,10,’1′,’男’),(‘tom2′,28,’0′,’男’);
insert into user(name,age,statuss,gender)values(‘tom3′,10,’1′,’男’);
insert into user(name,age,statuss,gender)values(‘tom5′,121,’1′,’男’);
insert into user(name,age,gender)values(‘tom5′,10,’男’);

外键约束

添加外键;

Alter table 表名 add constraint 外键名称foreign key (外键字段名) references 主表(主表列名);

Create table 表名(

       字段名 数据类型,

       ..

       [constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)

); alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept (id);

删除外键

Alter table 表名 drop foreign key 外键名称;

删除/更改外键

图片[9],MYSQL笔记,网络安全爱好者中心-神域博客网

Alter table 表名 add constraint 外键表名 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade; alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;

五,

1,多表关系

一对多

在多的一方建立外键,指向一方的主键

多对多

学生和课程的关系

建立第三方中间表，其中至少有两个主键，分别指向两方

一对一

单表拆分，一张是基础信息，一张是详细信息

在任意一方加入外键，关联另一方，并且设置外键唯一

2，多表查询概述

从多张表中查询数据

迪卡尔积：笛卡尔乘积指在数学中，两个集合A集合和B集合的所有组合情况.(在多表查询时需要清除无效的笛卡尔积

多表查询的分类：

  1. 连接查询

内连接：相当于查询A，B的交集部分数据

外连接：

        左外连接：左表所有数据，以及两张表交集部分数据

        右外连接：右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询,自连接必须用表的别名

  • 子查询

内连接

隐式内连接

Select 字段列表 from 表1，表2 where 条件;

显式内连接

Select 字段列表 from 表1[inner] join表2 on 连接条件…; select emp.name,dept.name from emp ,dept where emp.dept_id =dept.id;
#也可以取别名，但是取了别名就不能用原名了
select e.name,d.name form emp e,dept d where e.dept_id =d.id;
select e.name,d.name form emp e inner join dept d on e.dept_id=d.id;

外连接

左外连接

Select 字段列表 from 表1 left [outer] join 表2 on 条件…;

右外连接

Select 字段列表 from 表2 right [outer] join 表2 on 条件…; select e.*,d.name form emp e left outer join dept d on e.dept_id=d.id;
select d.*,e.* form emp e right outer join dept d on e.dept_id=d.id;
#也可以调换一下两个表的位置变成一个左外连接

自连接

Select  字段列表  from  表A  别名A  join  表A  别名B  on 条件..; select a.name,b.name from emp a,emp b where a.managerid=b.id;
select a.name,b.name from emp a left join emp b on a.nanagerid=b.id;

联合查询

Union(去重)  union(all)   查询的字段要相同,类型也要一样;

Select 字段列表 from 表a…

Union(all)

Select 字段列表 from 表b…; select * from emp where salary <5000
union(all)
#把all删了就不会有重复的了
select * from emp where age>50;

子查询

概念:sql语句中嵌套select语句,称为嵌套查询,又称子查询;

子查询外部的语句可以是insert/update/delete/select/的任何一个.

Select * from t1 where column1=(select column1 from t2);

根据子查询结果不同，分为:

标量子查询（子查询结果为单个值）

列子查询(子查询结果为一列)

行子查询(子查询结果为一行)

表子查询（子查询结果为多行多列）

根据子查询出现的位置分为：where之后，from之后,select之后,

标量子查询;

常用的操作符:= <> > >= < <= select id from dept where name=’销售部’;
select * from emp where dept_id=(select id from dept where name=’销售部’);
select entrydate from emp where name=’房东白’;
select *from emp where entrydeate>(select entrydate from emp where name=’房东白’);

列子查询

常用的操作符:in not in any some all select id from dept where name =’销售部’ or name=’市场部’;
select * from emp where dept_id in (select id from dept where name =’销售部’ or name=’市场部’);
select id from dept where name=’财务部’;
select salary from emp where dept_id =(select id from dept where name=’财务部’);
select * from emp where salary >all(select salary from emp where dept_id =(select id from dept where name=’财务部’));
select * from emp where salary >any(select salary from emp where dept_id =(select id from dept where name=’研发部’));

行子查询

常见的操作符:=   <>  in    not in

Select salary ,managerid from emp where name =’张无忌’;

Select * from emp where (salary,managerid)=( Select salary ,managerid from emp where name =’张无忌’);

表子查询

常用操作符:in select job,salary from emp where name =’路障可’ or nane =’宋远桥’;
select * from emp where (job,salary) in(select job,salary from emp where name =’路障可’ or nane =’宋远桥’);
select *from emp where entrydate>’2006-01-01′;
select e.*,d.* from (select *from emp where entrydate>’2006-01-01′) e left join dept d.id;

案列： select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id;
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id=d.id where e.age<30;
select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id;
select e.*,d.name from emp e left join dept d on e.dept_id =d.id where e.age>48;
select e.*,s.grade from emp e ,salgrede s where e.salary between s.losal and s.hisal;select *
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = ‘研发部’;
select avg(e.salary) from emp e,dept d where e.dept_id =d.id and d.name =’研发部”;
select * from eemp where salary >(select salary from emp whee name =’灭绝’);
select * from emp where salary >(select avg(salary) from emp);
select avg(e1.salary) from emp e1 where e1.dept_id=1;
select * from emp e2 where e2.salary<(select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id)’人数’ from dept d;
select s.name,s.no,c.name from student s ,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;

六．

1.   事务简介

事务：是一组操作的集合,他是一个不可分割的工作单位,事务会把所有操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失效.

2.   事务操作

查看/设置事务提交方式

Select @@autocommit

Set @@sutocommit =0;

提交事务

Commit;

回滚事务;

Rollback; select * from account where name=’张三’;
update account set money =money-1000 where name=’张三’;
update account set money =money+1000 where name=’李四’;
commit;
#如果不正确就执行rollback，不要执行commit

开启事务

Start transaction 或 begin

提交

Commit；

回滚

Rollback； start transaction;
select * from account where name=’张三’;
update account set money =money-1000 where name=’张三’;
update account set money =money+1000 where name=’李四’;
commit;
#如果不正确就执行rollback，不要执行commit

3.   事务的四大特性

1.原子性：事务时不可分割的，是最小的操作单位,要么全部成功,要么全部失败

2.一致性:事务完成时,必须使所有的数据都保持一致状态

3.隔离性:数据库提供的隔离机制，保证事务在不受外界并发操作影响的独立环境下运行

4.持久性:事务一旦提交或回滚，他对数据库的数据改变是永久的

4.并发事务问题

藏读：一个数据读到另一个数据没提交的数据

不可重复读：一个事务先后读取同一条记录,但两次读取的数据不同,

幻读:一个事务按照条件查询数据时,没有对应的数据行，但是在插入数据时,又发现这行数据已经存在，好像出现了幻影。

4.   事务的隔离级别

图片[10],MYSQL笔记,网络安全爱好者中心-神域博客网

查看事务隔离级别

Select @@transaction_isolation;

设置事务隔离级别

Set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable}

------本文已结束，感谢您的阅读------
THE END
未定义
喜欢就支持一下吧
点赞6 分享
biu801的头像,网络安全爱好者中心-神域博客网
为什么有的密码题脚本你运行不了sage,网络安全爱好者中心-神域博客网
为什么有的密码题脚本你运行不了sage
为什么有的密码题脚本你运行不了sage
8个月前 368
古典密码之维吉尼亚密码(无密钥破解),网络安全爱好者中心-神域博客网
古典密码之维吉尼亚密码(无密钥破解)
古典密码之维吉尼亚密码(无密钥破解)
8个月前 266
Docker命令笔记,网络安全爱好者中心-神域博客网
Docker命令笔记
Docker命令笔记
7个月前 199
密码学的数学基础,网络安全爱好者中心-神域博客网
密码学的数学基础
密码学的数学基础
8个月前 199
RSA0-1,网络安全爱好者中心-神域博客网
RSA0-1
RSA0-1
8个月前 194
8086教育版汇编语言,网络安全爱好者中心-神域博客网
8086教育版汇编语言
8086教育版汇编语言
8个月前 161
相关推荐
[Thm红队]Hackpark之MSF渗透入侵Windows主机和脚本提权(非常上强度,失败了),网络安全爱好者中心-神域博客网
[Thm红队]Hackpark之MSF渗透入侵Windows主机和脚本提权(非常上强度,失败了)
[Thm红队]Hackpark之MSF渗透入侵Windows主机和脚本提权(非常上强度,失败了)
9个月前 340
MYSQL笔记,网络安全爱好者中心-神域博客网
MYSQL笔记
MYSQL笔记
8个月前 221
Docker命令笔记,网络安全爱好者中心-神域博客网
Docker命令笔记
Docker命令笔记
7个月前 199
[XML]知识笔记,网络安全爱好者中心-神域博客网
[XML]知识笔记
[XML]知识笔记
8个月前 160
【滴滴滴】通知后几天文章更新路径-网络安全原理,网络安全爱好者中心-神域博客网
【滴滴滴】通知后几天文章更新路径-网络安全原理
【滴滴滴】通知后几天文章更新路径-网络安全原理
9个月前 152
谈谈汉字编码,网络安全爱好者中心-神域博客网
谈谈汉字编码
谈谈汉字编码
1年前 137
评论 共3条

请登录后发表评论

    只看作者
    • 头像Maxie Luettgen0
      Thank you for the auspicious writeup It in fact was a amusement account it Look advanced to far added agreeable from you However how can we communicate
      6个月前
    • 头像Meaghan Trantow0
      you are in reality a just right webmaster The site loading velocity is incredible It seems that you are doing any unique trick In addition The contents are masterwork you have performed a wonderful task on this topic
      6个月前
    • 头像Katelyn Harvey0
      Your blog is a treasure trove of knowledge! I'm constantly amazed by the depth of your insights and the clarity of your writing. Keep up the phenomenal work!
      6个月前