MySQL基础
MySQL基础
数据库介绍!
.什么是数据库
按照数据结构来组织,存储和管理数据的仓库
专业的数据库是专门对数据进行创建,访问,管理,
搜索等操作的 软件,比起我们自己用文件读
写的方式对象数据进行管理更加的方便,快速,安全
2.数据库作用
1)对数据进行持久化的保存
2)方便数据的存储和查询、速度快、安全、方便
3)可以处理并发访问
4)更加安全的权限管理访问机制
3.常见的数据库
关系型数据库:MySQL,Oracle,PostgreSQL,SQLserver...
非关系型数据库:Redis内存数据库,MongoDB文档数据库
4.基本概念
数据库Database
数据表Tables
数据字段
行
列
MySQL基本命令
1.终端命令
登录:mysql:mysql -u root -p
展示数据库:show databases
打开库:use 数据库名称
展示表:show tables
查看表中数据:
select * form 表名
select 字段名,字段名、、、 form 表名
创建库:
database 库名 default charset = utf8;
create database tlxy default charset=utf8;
创建表:
creat table 表名(
字段名 字段类型 字段约束,
字段名 字段类型 字段约束,
、、、
)engine = innodb default charset = utf8mb4
–创建用户表
create table user(
name varchar(20),
age int,
sex char(1)
)engine=innodb default charset=utf8;
--Query OK, 0 rows affected (0.16 sec)
添加数据:
insert into 表名(字段名列表“,”隔开)
values(对应数据)
-- 栗子
insert into user(name,age,sex)
values('admin',26,'男')
2.表和库的概念
*库就像是文件夹,库中可以有很多个表
*表就像是我们的excel表格文件一样
*每一个表中都可以存储很多数据
*mysql中可以有很多不同的库,库中可以有很多不同的表
*表中可以定义不同的列(字段),
*表中可以根据结构去存储很多的数据
数据库操作
1.表的操作
1).创建表
create table 表名(字段名,类型,【字段约束】,。。。);
创建表的原则
表明和字段名 尽可能的符合命名规范,并且最好能够‘见名之意’
表中数据必须有唯一标示,即主键定义。
无特殊情况,主键都为数字并自增即可
表中字段所对应的类型设置合理,并限制合理长度
表引擎推荐使用innodb,并无特殊情况都要求为utf8或者utf8mb4的字符编码
2)修改表结构
语法格式:alter table 表名 action (更改的选项):
--在user表中追加一个num字段
alter table user add num int not nul l;
--在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段
alter table users add email varchar(50) after age;
--在指定字段后面追加字段,在 users 表中 age字段后面 添加一个 phone
alter table users add phone char(11) not null after age;
--在表的最前面添加一个字段
alter table users add aa int first;
3)删除字段
删除字段 alter table 表名 drop 被删除的字段名
alter table users drop aa;
4)修改表名
语法:alter table 原表名 rename as 新表名
5)更改表中的自增的值
在常规情况下,auto_increment 默认从1开始继续递增
alter table users auto_increment= 1000;
6)修改表的引擎
推荐在定义表时,表引擎为 innodb。
通过查看建表语句获取当前的表引擎
mysql> show create table users\G;
*************************** 1. row ***************************
Table: users Create Table: CREATE TABLE `users` (
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 直接查看当前表状态信息
mysql> show table status from tlxy where name = 'users'\G;
*************************** 1. row ***************************
Name: users Engine: InnoDB
修改表引擎语句
alter table users engine = 'myisam';
7)删除表
drop table 表名
8)表的约束
1.unsigned 无符号(给数值类型使用,表示为正数,不写可以表示正负数都可以)
2.字段类型后面加括号限制宽度
char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度
int(4) 没有意义,默认无符号的int为int(11),有符号的int(10)
在数字类型后面加括号,对取值的范围没有影响
int(4) unsigned zerofill只有当给int类型设置有前导零时,设置int的宽度才有意义。
3.not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ,就会报错
4.default 设置默认值
5.primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。
6.auto_increment 定义列为自增属性,一般用于主键,数值会自动加1
7.unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度
9)主键
1、表中每一行都应该有可以唯一标识自己的一列,用于记录两条记录不能重复,任意两行都不具有相同的主键值
2、应该总是定义主键 虽然并不总是都需要主键,但大多数数据库设计人员都应保证他们创建的每个表具有一个主 键,以便于以后的数据操纵和管理。
要求
记录一旦插入到表中,主键最好不要再修改
不允许NULL
不在主键列中使用可能会更改的值。
(例如,如果使用一个名字作为主键以标识某个供应商,当该供应商合并和更改其名字时,必须更改这个主键。)
自增整数类型:数据库会在插入数据时自动为每一条记录分配一个自增整数,这样我们就完全不用担心主键 重复,也不用自己预先生成主键
可以使用多个列作为联合主键,但联合主键并不常用。使用多列作为主键时,所有列值的组合必须是唯一的
2.添加数据
--标准添加(指定所有字段,给定所有的值)
mysql> insert into stu(id,name,age,sex,classid)
--指定部分字段添加值
mysql> insert into stu(name,classid) value('lisi','lamp138');
-- 不指定字段添加值
mysql> insert into stu value(null,'wangwu',21,'w','lamp138');
-- 批量添加值
mysql> insert into stu values
-> (null,'zhaoliu',25,'w','lamp94'),
-> (null,'uu01',26,'m','lamp94'),
-> (null,'uu02',28,'w','lamp92'),
-> (null,'qq02',24,'m','lamp92'),
-> (null,'uu03',32,'m','lamp138'),
-> (null,'qq03',23,'w','lamp94'),
-> (null,'aa',19,'m','lamp138');
3.修改数据
格式:
update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件
4.删除数据
–删除stu表中id值为100的数据
mysql> delete from stu where id=100;
Query OK, 0 rows affected (0.00 sec)
–删除stu表中id值为20到30的数据
mysql> delete from stu where id>=20 and id<=30;
Query OK, 0 rows affected (0.00 sec)
–删除stu表中id值为20到30的数据(等级于上面写法)
mysql> delete from stu where id between 20 and 30;
Query OK, 0 rows affected (0.00 sec)
–删除stu表中id值大于200的数据
mysql> delete from stu where id>200;
Query OK, 0 rows affected (0.00 sec)
5.数据库查询
select 字段列表|*from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]
1.where 条件查询
可以在where子句中指定任何条件
可以使用 and 或者 or 指定一个或多个条件
where条件也可以运用在update和delete语句的后面
where子句类似程序语言中if条件,根据mysql表中的字段值来进行数据的过滤
–查询users表中 age > 22的数据
select * from users where age > 22;
–查询 users 表中 name=某个条件值 的数据
select *from users where name=‘王五’;
–查询 users 表中 年龄在22到25之间的数据
select * from users where age >= 22 and age <= 25;
select*from users where age between 22 and 25;
–查询 users 表中 年龄不在22到25之间的数据
select * from users where age < 22 or age > 25;
select*from users where age not between 22 and 25;
–查询 users 表中 年龄在22到25之间的女生信息
select * from users where age >= 22 and age <= 25 and sex =‘女’;
2.like字句
– 使用 % 模糊搜索。%代表任意个任意字符
– 使用 _ 单个的下划线。表示一个任意字符,使用和%类似
where子句中的like在使用%或者_进行模糊搜索时,效率不高,使用时注意: 尽可能的不去使用%或者_ 如果需要使用,也尽可能不要把通配符放在开头处
Mysql中的统计函数(聚合函数)
max(),min(),count(),sum(),avg()
分组group by
group by 语句根据一个或多个列对结果集进行分组
– 统计1班和2班的人数
select classid,count(*) from users group by classid;
3.Oder by排序
我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的, 首先不要依赖默认的排序,另外在需要排序时要使用orderby对返回的结果进行排序
Asc 升序,默认
desc降序
4.Limit 数据分页
limit n 提取n条数据,
limit m,n 跳过m跳数据,提取n条数据
5.MySQL的运算符
算术运算符: +、 -、 *、 /、 %
比较运算符: =、 >、 <、 >=、 <=、!=
数据库特有的比较: in、not in、is null、is not null、like、between、and
逻辑运算符: and、or、not
like: 支持特殊符号%和_ ;
6.用户权限管理
mysql中的root用户是数据库中权限最高的用户,千万不要用在项目中。
可以给不同的用户,或者项目,创建不同的mysql用户,并适当的授权,完成数据库的相关操作
这样就一定程度上保证了数据库的安全。
创建用户的语法格式:
grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;
子句 说明 是否必须
select 要返回的列或表达式,字段列表|* 是
from 查询的数据表 需要在表中查询时
Where 数据行的过滤 否
group by 分组 仅在分组聚合计算时
having 分组后的数据过滤 否
order by 输出排序 否
limit 要提取的结果行数 否
6.数据库-表引擎以及字符集
MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录 组成的,但这只是一个逻辑上的概念,物理上如何表示记录,怎么从表中读取数据,怎么把数据写入具体的物理存 储器上,这都是 存储引擎 负责的事情。为了实现不同的功能, MySQL 提供了各式各样的 存储引擎 ,不同 存储引 擎 管理的表具体的存储结构可能不同,采用的存取算法也可能不同。 存储引擎以前叫做 表处理器 ,它的功能就是接收上层传下来的指令,然后对表中的数据进行提取或写入操 作。
为了管理方便,人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能,把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口(也就是存储引擎API),包含了几十个底层函数,像"读取索引第一条内容"、“读取 索引下一条内容”、"插入记录"等等。 所以在 MySQL server 完成了查询优化后,只需按照生成的执行计划调用底层存储引擎提供的API,获取到数据后返 回给客户端就好了。
7.MyISAM 和InnoDB的区别
MyISAM | InnoDB | |
---|---|---|
事务 | :(执行单元,1或0) 不支持 | 支持 |
存储结构 | 三个文件.firm存表结构.MYD 存数据.MYI 存索引 | 两个文件.frm 存表结构.ibd 存数据和索引(可以是多个,或独立的表空间文件) |
表主键 | 允许没有任何索引和主键, | 如果没有自动生成6字节的用户不可见的主键,数据为索引的一部分 |
表锁 | 只支持表级锁 | 支持事务锁和行级锁 |
具体行数 | 保存有总行数 | 没有保存 |
CURD操作 | 适合执行大量的Select | 适合大量的insert和update.清空大量数据表,最好用truncate table |
外键 | 不支持 | 支持 |
查询效率 | 相对简单,效率优于-> | 在并发很多的情况下,优于<- |
应用场景 | 执行大量Select查询 | 大量INSERT或UPDATE操作 |