MySQL 数据库-基础操作
目录
简介
注释
#
,这是单行注释,并不是所有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,设置备注,给当前字段设置说明信息
字段类型
数值型:存储的数值大小不一样
- 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子句:给条件的查询
算数运算符:
-
-
-
- /
- %
where 子句之比较运算符
select * from stu where id=11+1 -- 此时字段的值等于12
... where column_name <> value -- 不等于(!=)
比较运算符:
=
>
<
!=
>=
<=
where子句之逻辑运算符
... where desc
... where -desc -- desc升序,-desc降序
... where -desc -- desc升序,-desc降序
逻辑运算符:与(and)或(or)非(not),这里不推荐使用符号。
where 子句之 between 语法
范围查询。
字段名 between 小值 and 大值;
用于查询两个值之间的值,包含小与大值
where 子句之 in 语法
指定查询。
单独查询某些语句时。
字段名 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 模糊查询
字段名 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';
授权
查询权限
尝试使用操作数据。
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';
确实只能查数据。
设置监听地址
为了减小攻击面,数据库端口 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
参考资料
最近更新:
发布时间: