目录

简介

注释

  • #,这是单行注释,并不是所有dbms都支持
  • --,这是单行注释(-- 后面有个空格)
  • /*这是多行注释*/

大小写问题:在 Liunx 语法中区分大小写,windows 相反。

select distinct 列名 from 表名 #distinct 表示从这一列中找出不同的数据(去重)

group by 列名 #用于查询后数据分组,加入有11、22、11、三条数据 我们查询进行 group by后就会分为 11、22。

select * from users where name=(select name from users wherer fight=80)# 括号内是子查询,查询的结果交给name使用

创建数据库

create database 库名 可选项;    #创建名为user的数据库、

可选项示范:

create database db_name charset utf8;    #在创建数据库时指定编码方式 

创建表

create table 表名 (
字段名称1 字段属性(类型) 字段选项,
字段名称2 字段属性(类型) 字段选项,
....           .....            .....         ....
)charset=utf8 engine=Myisam;        --在表后面加上字符集与引擎

字段:是对数据的说明,一个归类。
字段类型:这个字段存储的是什么数据,在创建表示要指定好
字段选项:设置字段一些选项,是否可以为空、是否是主键、是否是唯一键、是否加备注等。

  • null | not null,是否可以为空
  • default,是否有默认值
  • auto_increment,是否可以自动增长,值必须是数字才行, 如:给数据一个编号,数据内容可以重复编号不能重复,缩小范围方便查找
  • primary key,设置主键,数据内容不能重复,在查询数据中作为主查询条件,一张表中一个主键,一般是ID
  • unique [key],设定为唯一(键),及表中所有行的数据在该字段中的值不能有重复
  • comment,设置备注,给当前字段设置说明信息

字段类型

int.png

数值型:存储的数值大小不一样

  • tinyint、smallint、int
  • tinyint带符号的:-128到127
  • tinyint无符号:0-255
  • smallint带符号的:-32768到32767
  • tinyint无符号:0-65535
  • int带符号的:-2147483648到2147483647
  • int无符号:0到4294967295(40亿)

字符串:

  • char,最大长度 255
  • varchar,最大长度 65532
  • text,无限制

char 和 varchar的区别:定长型和变长型

char:定长型,固定长度了,如:char(30)最大存储30的长度,如果没有写到30长度的数据其他的空出来的位置用空格补齐,写入数据超出定义长度只取定义时长度位数超出部分忽略。

varchar:变长型,可以变化长度,如:varchar(30),最大存30长度,没写满30你写多少就存多少,超过30超出部分忽略。

日期时间

  • data,支持的范围'1000-01-01'到'9999-12-31'
  • datetime,支持的范围'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

向表里添加数据

向表里添加一行数据

insert into 表名[(字段名1,字段名2,.....)] values(值1,值1,....);

向表里添加多行数据(添加数据时值与字段名是一一对应的)

insert into 表名[(字段名1,字段名2,.....)] values(值1,值1,....),(值1,值1,....);

删除数据库

drop database xxx;

导入数据库

source file_path;

删除数据

delete from 表名 [where 条件] [order 排序] [limit 限定]
truncate table 表名    #删表内所有数据
where    #条件必须添加,否则删除所有数据,建议id,原因id唯一性,删除大范围数据
order by    #当前表倒序还是正序,默认就升序,降序为 order by desc。
limit    #限定范围

可选项示范:

delete from 表名 where id=1;
select * from 表名 order by 字段 desc limit 0,5;

注意:delete删除后会把删除的ld号保留,不在让其他数据占用,truncate 删除相反。

删除字段

alter table 表名 drop 要删除的字段名;

查询数据库

show databases;    #显示所有数据库
show create database 库名;    #查询数据库创建语句
select database();    #查看当前所在数据库。
use xxx(名字);    #打开数据库(切换)
show tables;    #显示当前数据库内所有数据表
show columns from 表名; 
show create table 表名;    #查询创建表时的语句
show collation;    #查看校对集
show charset;    #查看编码集(字符集)

查看表结构

desc 表名;
describe 表名; #与desc作用一致

部分函数。

select now();    #当前日期时间
select version();    #查询Mysql版本号
select user();    #查询当前数据库用户

查询数据

select * from 表名;    #查询表里的所有字段(也称列)
select 字段1,字段2,字段3,.... from 表名;    #查询xxx表里的指定的字段
select 字段 from xxx limit 1;    #查询的结果只显示1行数据
select * from where id=1    #查询满足id=1的字段
select count(字段名) from 表名    #用count()字段总个数,但是返回的字段名是count(字段名)
select count(字段名) as 别名 from 表名;    #用count()字段总个数,字段返回时用我们定义的别名显示
select distinct 列1,列2 #distinct对所有列查询出来的数据去重,如果是两列(或多列),只有列数据相同才会被去重。
select * from user -desc limit 3 offset 5 #limit显示几行 ,offset从第几行开始显示(0是第一行)
select * from user -desc limit 3,5 #简化语法,3是offset,5是显示几行(0是第一行)

from 子句表示从哪个(或哪些)表中获取数据。

where 子句之算数运算符

where.png

where子句:给条件的查询

算数运算符:

  • /
  • %

where 子句之比较运算符

wheere02.png

    select * from stu where id=11+1 -- 此时字段的值等于12
    ... where column_name <> value -- 不等于(!=)

比较运算符:

  • =
  • >
  • <
  • !=
  • >=
  • <=

where子句之逻辑运算符

where03.png

... where desc
... where -desc -- desc升序,-desc降序
... where -desc -- desc升序,-desc降序

逻辑运算符:与(and)或(or)非(not),这里不推荐使用符号。

where 子句之 between 语法

范围查询。

where05.png

字段名 between 小值 and 大值;

用于查询两个值之间的值,包含小与大值

where 子句之 in 语法

指定查询。

where04.png

单独查询某些语句时。

字段名 in (值1,值2,值3,值4,....);

select * from buy where shop in (查询语句); #子查询
select * from buy where shop in ('nike','adidas'); #等同于where shop='nike' or shop='adidas',语法更简洁。

where 子句之 like 模糊查询

where06.png

字段名 like '要查询的字符'; 如果直接写字符,匹配(相当于精确匹配)的结果左右不能有其他字符

字段名 like '%要查询的字符%'; %表示匹配后面的任何字符,建议前后都加上,在现实中有可能左右都有其他字符包含着。

修改数据库字符集

alter database 库名 charset=新字符集 collate=新的校对集

无法更改数据库名,字符集与校对集可以分开修改,但建议一起更改。

修改表名

alter table 旧表名 rename [to] 新表名;    #这里的to可选。

添加新字段

alter table 表名 add 新字段名 字段类型 字段选项;

修改表字段(包括字段)

alter table 表名 change 旧字段名 新字段名 新字段类型 新字段选项;

修改字段类型和选项

alter table 表名 modify 要修改的字段名 新的字段类型 新的字段选项

修改数据

update 表名 set 原有字段='新的值',age=50 [where]
update 表名 set 原有字段='新的值',age=50 where id=1;

join

join 将两张表做连接。

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
-- join将两个表内容相同的数据显示出来,把不同的丢弃掉。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
-- left join,把左表和右表做比对(以左表为基准),右边表数据在左表中不存在就显示成NULL,数据一致就合并显示。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
-- right join,把右表和左表做比对(以右表为基准),左边表数据在右表中不存在就显示成NULL,数据一致就合并显示。

mysql主从备份与双机热备
设置锁表(用户和管理员不能写),设置只读(普通用户不能写)

开启远程登录

#开启远程登录,密码是数据库密码,'root'@'%'百分号可以写192.168.4.%或192.168.4.1,就是指定那个地址可以登录。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '数据库密码' WITH GRANT OPTION;

#关闭远程登录
drop user 'root'@'%'; -- 如果开启时不是指定%,就填成对应地址。

#开启和关闭远程登录,每次都要刷新权限
flush privileges; 

安全防护

控制数据库账户权限

应当为不同功能设置不同账户权限(最小权限原则),以避免获取数据库 Shell 后利用账户权限进一步提权。

现在我这个页面功能只是查数据就没必要删除或增加数据,所以做个限制吧。

# 创建账号和密码为 gbb 只能在本地连接的账户。
CREATE USER gbb@localhost IDENTIFIED BY 'gbb';

# 只赋予 test 数据库下 select 所有表的权限
GRANT SELECT ON test.* to 'gbb'@'localhost';

授权

给用户 gbb 设置权限.png

查询权限

查询 gbb 用户权限.png

尝试使用操作数据。

MariaDB [test]> insert into testdatatable values('lisi', 88);
MariaDB [test]> delete from testdatatable where name='zhangdsan';
MariaDB [test]> update testdatatable set age=22 where name='zhangdsan';

确实只能查数据。

使用查询语句验证权限设置.png

设置监听地址

为了减小攻击面,数据库端口 3306 似乎不应对外开放才对,再说我们是 WebServer 和 Database 都放在一台服务器上,真实场景很可能是分别各在服务器上,那可以开放端口,不开放程序就没法连接嘛。

由于贫穷放在一台就一台吧,现在是要想法子让 3306 不暴露出来。

由于不需要应用服务器连接我们,所以只绑定在本地环回端口,编辑 /etc/my.cnf[client-server] 组下添加
bind-address = 127.0.0.1,相反填 0.0.0.0 端口就会绑定到所有网卡上。

[root@localhost ~]# netstat -pantu | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      2711/mysqld 

参考资料

最近更新:

发布时间:

摆哈儿龙门阵