增删改查
在MySQL中库相当于一个目录,用来存表的;表则相当于文件,表中存放的为数据,也可以理解为库是用来存放数据的。
#库的应用
mysql> create database 库名; #创建库
mysql> show databases; #查看库
mysql> use 库名; #进入库
mysql> select database(); #查看当前所在库
创建表的语法:
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
#可以将小括号的内容写成一行,也可以敲回车,只要不巧分号就可以继续输入
#在同一张表中,字段名是不能相同
#宽度和约束条件可选
#字段名和类型是必须的
mysql> create table 表名(id int,name varchar(10),sex enum('m','f')); #创建一个表
mysql> show tables; #查看有哪些表
mysql> desc 表名; #查看表的结构
mysql> select * from 表名; #查看表中的所有数据
mysql> select 字段,字段 from 表名; #查看表中的某个指定字段
mysql> show table status like '表名'\G #查看表的状态,\G结尾时不用加分号
#表中插入数据
mysql> insert into 表名(字段1,字段2) values(值1,值2); #字段1要和值1对应
#修改表名称
mysql> rename table 旧表名 to 新表名; #方法1
mysql> alter table 旧表名 rename 新表名; #方法2
mysql> drop table 表名; #删除表
mysql> drop database 库名; #删除库
数据库储存引擎
数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引、锁定等功能,使用不同的存储引擎,还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
#MySQL存储引擎介绍
文件系统:操作系统组织和存取数据的一种机制。文件系统是一种软件。类型:ext2,3,4 ,xfs 数据,不管使用什么文件系统,数据内容不会变化。不同的是,存储空间、大小、速度。
MySQL引擎:可以理解为,MySQL的“文件系统”,只不过功能更加强大。MySQL引擎功能:除了可以提供基本的存取功能,还有更多功能,如事务功能、锁定、备份和恢复、优化以及特殊功能。
拓展
1、InnoDB存储引擎:默认引擎,最常用的。
InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键;InnoDB是默认的MySQL引擎。 InnoDB特点: 支持事务处理,支持外键,支持崩溃修复和并发控制。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为InnoDB支持事务的提交(commit)和回滚(rollback)。
2、MyISAM存储引擎:(了解)
MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事务。MyISAM特点: 插入数据快,空间和内存使用比较低。如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。如果对于应用的完整性、并发性要求比较低,也可以使用。
3、MEMORY内存型引擎:(了解)
MEMORY存储引擎将表中的数据存储到内存中,未查询和引用其他表数据提供快速访问。MEMORY特点: 所有的数据都在内存中,数据的处理速度快,但是安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMOEY。它对表的大小有要求,不能建立太大的表。所以这类数据库只使用在相对较小的数据库表。
4、Archive:(归档引擎)
1、如果要提供提交、回滚、并要求实现并发控制,InnoDB是一个好的选择;
2、如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率;
3、如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎;MySQL中使用该引擎作为临时表,存放查询的中间结果;
4、;如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive。
mysql> show engines; #查看储存引擎
mysql> alter table 表名 engine=引擎; #修改某个表的搜索引擎
mysql> show variables like '%storage_engine%'; #查看你的mysql当前默认引擎
mysql> select version(); #查看mysql的版本
mysql> create table t1(id int,manager char(10)) engine =innodb; #创建t1表时指定引擎
表的基础
数据类型
在MySQL数据库管理系统中,可以通过存储引擎来决定表的类型。同时,MySQL数据库管理系统也 提供了数据类型决定表存储数据的类型。
1.整型
作用:用于存储用户的年龄、游戏的Level、经验值等。
分类:tinyint,smallint,mediumint,int,bigint
常用的是int 显示宽度:类型后面小括号内的数字是显示宽度,不能限制插入数值的大小 比如:bigint(2) 2是显示宽度 结论:整形的宽度仅为显示宽度,不是限制。因此建议整形无须指定宽度。
————————————————————————————
2.浮点数
分类:float,double
作用:用于存储用户的身高、体重、薪水等
float(5.3) (一共5位,小数点后占3位.做了限制。)
————————————————————————————
3.定点数
类型:dec
定点数在MySQL内部以字符串形式存储,比浮点数更精确,适合用来表示货币等精度高的数据。
————————————————————————————
4.字符串
类型:char,varchar #后面要跟上宽度
作用:用于存储用户的姓名、爱好、发布的文章等
————————————————————————————
5.枚举
类型:enum
固定好的选项,不是1就是2
————————————————————————————
6.日期
类型:year,date,time,datetime,timestamp
作用:用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等。(插入年份时,尽量使用4位值。)
————————————————————————————
1.经常变化的字段用varchar
2.知道固定长度的用char
3.尽量用varchar
4.超过255字符的只能用varchar或者text
5.能用varchar的地方不用text
约束
用于保证数据的完整性和一致性
约束条件 | 说明 |
---|---|
primary key (pk) | 该字段为该表的主键,唯一的标识记录,不可以为空,独一无二。 |
foreign key (fk) | 该字段为该表的外键,实现表与表之间的关联(父表主键/子表外键1/子表外键2) |
null | 该字段是否允许为空,默认是null。 |
not null | 该字段不能为空,可以修改 |
unique key (uk) | 该字段的值是唯一的,可以为空,一个表中可以有多个unique key |
auto_increment | 该字段的值自动增长(整数类型,而且为主键) |
default | 为该字段设置默认值 |
unsigned | 无符号,正数 |
1.主键:每张表里只能有一个主键,不能为空,而且唯一。
create table 表名(字段名1 类型 primary key,字段名2 类型);
create table 表名(字段名1 类型,字段名2 类型,primary key(字段名1));
#定义主键的方法
mysql> create table t1(name char(7) primary key,age int); #方法1
mysql> create table t1(name char(7),age int,primary key(name)); #方法2
mysql> alter table t1 drop primary key; #删除t1表的主键
mysql> alter table t1 add primary key(id); #添加主键
索引
索引 (index):当查询速度过慢可以通过建立索引优化查询速度,可以当作调优。
index(key)每张表可以有很多列做index(索引)。
索引的类型
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引
#创建索引的方法
mysql> create table t2(name varchar(3) primary key,age int,index (age)); #方法1,索引名为字段名
mysql> create table t2(name varchar(3) primary key,age int,index nianling(age)); #方法2,索引名为nianling
mysql> alter table t2 add index index_id (id); #t2表id字段添加index索引,索引名为index_id
mysql> alter table t2 drop index index_id; #把t2表的index_id索引删除
mysql> drop index index_id on t2; #把t2表的index_id索引删除
自增
auto_increment(自增):每张表只能有一个字段为自曾,类型必须为整型(成了key才可以自动增长)一般自增都为主键。
mysql> create table t3(id int primary key auto_increment,name varchar(3),age int); #定义自增
唯一
某个字段被定义唯一后,那么插入数据时,这个字段的数据不能重复,否则会报错,无法插入数据。
修饰符
- 是否允许为空,默认NULL,可以为空。可设置NOT NULL,字段不允许为空,必须赋值。
- 字段是否有默认值,缺、省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值。
————————————————————————————————
default:默认是XX
枚举时:sex enum(‘male’,‘female’) not null default ‘male’ #只能选择maie和female,不允许为空,默认是male
age int unsigned not null default 20 #必须为正值(无符号) 不允许为空 默认是20
字符集
在创建表的最后面指定一个你想要的字符集,比如:default charset=utf8
#utf-8表示支持中文
表的修改与添加
添加字段
添加字段
添加字段
alter table 表名 add 字段名 类型 ; #添加单个字段
alter table 表名 add (字段名1 类型,字段名2 类型); #添加多个字段,中间用逗号隔开
alter table 表名 add 要添加的字段名 要添加的类型 after 表中已有的字段; #把添加的的字段放到这个已有的后面
alter table 表名 add 要添加的字段名 要添加的类型 first; #把要添加的字段放到第一个
mysql> alter table t1 add ip varchar(16) #添加单个字段
mysql> alter table t1 add (id varchar(21),money dec); #添加多个字段
mysql> alter table t2 add ip varcahr(16) after name; #把ip字段添加到name字段后面
mysql> alter table t2 add id varchar(21) first; #把id字段放到第一个
修改字段名,类型,修饰符,约束,顺序
#change修改字段名,类型,约束,顺序
alter table 表名 change 旧字段 新字段 类型; #修改字段名及类型
alter table 表名 change 旧字段 新字段 类型 after 已存在的字段; #修改字段名及类型并更换位置
#modify不能修改字段名
alter table 表名 modify 已存在的字段 新的类型 修饰符;
alter table 表名 modify 已存在的字段1 新的类型 修饰符 after 已存在的字段2; #修改并更换位置
3drop删除字段
alter table 表名 drop 字段名
mysql> alter table t1 change name hostname varchar(21) not null; #修改字段名修饰符
mysql> alter table t1 change hostname name char(3) after ip; #修改字段名,类型并更换位置
mysql> alter table t1 modify name varchar(21) null first; #修改类型,修饰符。无法修改字段名,并更换位置
mysql> alter table t1 drop ip; #删除、丢弃t1表中的ip字段
插入数据
插入数据
字符串必须用引号引起来,记录与表头相对应,表头与字段用逗号隔开。
1.添加一条记录
insert into 表名(字段1,字段2,字段3,字段4) values(1,“小白”,“little”,18);
2.添加多条记录
insert into 表名(字段1,字段2,字段3,字段4) values(1,“小白”,“little”,18),(2,“xiaobai”,“f”,20);
用set添加记录
insert into t3 set 字段1=3,字段2=“bai”,字段3=“m”,字段4=21;
更新记录
update 表名 set 修改的字段 where 给谁修改;
.删除记录
1.删除单条记录
delete from 表名 where 字段名=1; #删除匹配到的整条数据,where后面跟的是匹配条件
2.删除所有记录
delete from 表名; #这个表的所有数据
查询数据
单表查询
#查询格式
select 字段名1,字段名2 from 表名 条件;
as:设置别名
count():统计数量
distinct:避免数据相同
and:和,满足二者
or:或,二选一
between and:什么和什么之间
not:条件取反
in:集合查询
order by:排序查询升序,后跟desc为降序
limit:限制
group by:以该字段分组查询
group_concat():分别列举
max():最大值
min():最小值
avg():平均值
now():现在时间
sum():计算和
is null:空的
NULL说明:
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作,结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小(索引默认是降序排列,小→大),所以NULL值总是排在最前。
mysql> select * from t1; #查看t1所有数据
mysql> select name,age from t1; #查看t1表中name和age字段的数据
#条件查询
mysql> select name,age from t1 where age=18; #查看t1表中name和age字段的数据并且age要等于18
#as别名
mysql> select name as "名字" ,age from t1 where age=18; #查看t1表中name和age字段的数据并且age要等于18,显示结果中name字段的名称改为‘名字’,表字段不更改
#统计,去重
mysql> select count(*) from t1; #统计所有字段得到的数量
mysql> select distinct age from t1; #查看t1表中age字段的数据有哪些,重复的只显示一次
#and,or,between and
mysql> select name,age from t1 where name="xiaobai" and age=18; #多条件查询,and同时满足两个条件
mysql> select name,age from t1 where name="xiaobai" or age=18; #多条件查询,or满足其中一个
mysql> select name from t1 where age between 10 and 30; #查看age在10到30之间的name(不包括10和30)
#not,is null,in
mysql> select name from t1 where not age<15; #查看age不是小于15的name
mysql> select ip from t1 where ip is null; #查看ip从t1表,值可以为空
mysql> select age from t1 where age in (10,15,20); #查看age为10,15,20的数据
#排序
mysql> select age from t1 order by age; #查看age通过升序排列
mysql> select age from t1 order by age desc; #降序
#limit限制
mysql> select name from t1 limit 5; #显示前五行
mysql> select name from t1 limit 0,1; #从索引0开始,打印一行(第一行为索引号的0,第二行为1)
#分组查询
mysql> select count(name),age from t1 group by age; #以age分组,统计name有几个
mysql> select count(name),group_concat(name) from t1 where age>18; #age大于18的name有几个,分别是谁
#计算比较
mysql> select max(age) from t1; #t1表中age最大的数据
mysql> select min(age) from t1; #t1表中age最小的数据
mysql> select avg(age) from t1; #t1表中age的平均值
mysql> select sum(age) from t1; #t1表中age的和
mysql> select now(); #现在的时间
多表查询
mysql> select a.id,b.name_id from t1 as a,t2 as b where a.id=b.name_id; #多表查询
#a.id实际上为t1.id,后有as别名将t1定义了a,所以可以直接引用别名。
#t1为主表,id字段是主键,t2表为外表,name_id是外键,t1表的主键id和t2表的外键name_id是有关联的。
外键的添加方式和主键相同,但是外键一定是和主表的主键有关联的,一个主键可以同时有多个外键,但是一个外键只能有一个主键。
破解密码
破解密码
如果密码强度简单开启了请先关掉
root账户没了或者root密码丢失:
关闭mysql使用下面方式进入mysql直接修改表权限 5.6/5.7版本:
mysqld --skip-grant-tables --user=mysql &
mysql -uroot
#进入mysql界面
update mysql.user set authentication_string=password(‘XiaoBai@123!’) where user=‘root’ and host=‘localhsot’;