数据库——MySQL(一)

最新推荐文章于 2024-01-15 23:45:00 发布

qq_31241107

最新推荐文章于 2024-01-15 23:45:00 发布

阅读量343

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_31241107/article/details/105976348

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

关系型数据库

关系型数据库最典型的数据结构是表
优点：
1、易于维护:都是使用表结构,格式一致;
2、使用方便:SQL语言通用,可用于复杂查询;
3、复杂操作:支持SQL,可用于一个表以及多个表之间非常复杂的查询。
缺点:
1、读写性能比较差,比如海量数据的高效读写;
2、表的结构固定,不够灵活;
3、对于高并发读写需求,硬盘I/O是一个很大的瓶颈;

非关系型数据库严格上不是一种数据库,应该是一种数据结构化存储方法的集合,可以是文档或者键值对等。
优点：
1、存储数据的格式灵活:可以是key,value形式、文档形式、图片形式等等,使用灵活,应用场景广泛,而关系型数据库则只支持基础类型。
2、速度快:nosql可以使用硬盘或者随机存储器作为载体,而关系型数据库只能使用硬盘;
3、高扩展性;
4、成本低:nosql数据库部署简单,基本都是开源软件。

缺点：
1、不提供sql支持,学习和使用成本较高；
2、无事务处理;
3、数据结构相对复杂,复杂查询方面稍欠
关系型数据库:sqllite,db2,oracle,access,sql server MySQL
非关系型数据库:MongoDB,redis

MySQL

数据库是按照数据结构来组织、存储和管理数据的仓库
管理数据库的软件就是数据库的管理系统,如MySQL、Oracle、SQLite、Access、MS SQL Server
MySQL是最流行的关系型数据库管理系统
MySQL是开源的
MySQL支持大型的数据库,可以处理拥有上千万条记录的大型数据库
所谓的关系型数据库,是建立在关系模型基础上的数据库

特点:
1.数据以表格的形式出现
2.表格由行和列组成
3.一行中所有列的信息代表一条完整的记录
4.若干的表单组成database(数据库)

术语

数据库:数据库是一些关联表的集合
数据表:表是数据的表示形式,若干的表单组成database(数据库)
列:一列(数据元素)包含了相同类型的数据
行:一行数据就是完整的一条记录
冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性
主键:主键是唯一的,一个数据表中只能包含一个主键,你可以使用主键来查询数据
外键:外键用于关联两个表的列
复合键:复合键将多个列作为一个索引键,一般用于复合索引
索引:使用索引可快速访问数据库表中的特定信息
mysql的注释--
一张表实际保存为三个文件，.frm存储表定义，.MYD存储数据,.MYI存储索引

MySQL的下载:https://dev.mysql.com/downloads/mysql/

MySQL的初始设置

MySQL安装后需要设置字符集编码
打开my.ini配置文件
在[client]节点下添加如下代码
default-character-set=utf8 	设置mysql客户端默认字符集
在[mysqld]节点下添加如下代码
character-set-server=utf8
character-set-client=utf8
collation-server=utf8_general_ci
在[mysql]节点下添加如下代码
default-character-set=utf8

[mysqld]
default-storage-engine=InnoDB#默认引擎为InnoDB
character-set-server=utf8
character-set-client=utf8
collation-server=utf8_general_ci
#绑定IPv4和3306端口
bind-address = 0.0.0.0
port = 3306
# 设置mysql的安装目录
basedir=C:\MySQL\MySQL Server 5.7
# 设置mysql数据库的数据的存放目录
datadir=C:\MySQL\MySQL Server 5.7\data
#Windows环境解决Mysql权限不足
skip-grant-tables#添加这段代码可以无密码进入mysql
# 允许最大连接数
max_connections=200
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysql_safe]
default-character-set=utf8
[client]
default-character-set=utf8

MySQL相关命令

window窗口输入命令,配置了mysql的环境变量后任意路径都可以输入命令
mysqld --initialize --console	初始化数据库
mysqld --initialize-insecure 	5.7需要初始化data目录
net start mysql 启动数据库
mysql -u 用户名 -p 密码	登录本机mysql
mysql -h 主机名 -u 用户名 -p 密码	登录其他主机的mysql
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('新密码'); 修改数据库密码,需要先进入数据库再修改
flush privileges;　修改密码后的必须操作——刷新权限
skip-grant-tables添加这段代码可以无密码进入mysql
quit 退出mysql 

进入mysql

show engines;	查看mysql可以提供的存储引擎
show variables like '%storage_engine%';	查看当前默认引擎
show databases;	查看所有的数据库
use 数据库名称;	进入数据库
show tables;	查看所有的表
show columns from 表名;	查看表的结构信息
show index from 表名;	查看表的索引信息
show table status from 数据库名 like 't%' ;	批量查询以t开头的表的信息
	
use mysql; 自带的数据库,存储了mysql相关信息
show tables; 查看mysql数据库所有的表
select user,host from user;  查看当前所有的用户及其IP地址

SQl分类

SQL语句主要可以划分为
DDL:数据定义语言,这些语句定义了不同的数据段、数据库,常用关键字包括 create、drop、alter等
DML:数据操纵语句,用于添加、删除、更新数据库记录,常用关键字主要包括 insert、delete、udpate
TCL:事务控制语言,用于控制不同数据段直接的许可和访问级别的语句。常用关键字包括commit ,rollback 等
DQL:数据查询语言,常见关键字select

用户相关操作

create user '用户名'@'主机IP' identified by '密码'; 	创建用户
create user '用户名'@'192.168.1.%' identified by '123'; %表示任意IP字段	
drop user '用户名'@'IP地址';	删除用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';	修改用户
set password for '用户名'@'IP地址' = Password('新密码')	修改密码

授权操作:
	grant 权限 on 数据库名.表名(* 表示所有表) to '用户名'@'访问IP'
	grant select,insert,update  on db1.t1 to 'zs'@'%';
	grant all privileges  on db1.t1 to 'zs'@'%'; 给所有的权限但是不包括授权的权限
	show grants for '用户'@'IP地址'     查看用户权限
	revoke 权限 on 数据库.表 from '用户'@'IP地址'     取消用户权限

数据库操作

create database 数据库名 engine=innodb default charset=utf8;;	创建数据库默认编码为utf8,引擎为innodb 
	InnoDB是一个事务型的存储引擎，有行级锁定和外键约束,支持类似于Oracle的锁机制
	Myisam是一种非事务性的引擎,提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用
	新存储引擎Falcon将在MySQL6.0版本引进
show databases;	查看所有数据库
drop database 数据库名;	删除数据库
use  数据库名;	选中数据库
show tables; 查看当前数据库的所有表

数据类型

数字：
        tinyint 小整数,数据类型用于保存一些范围的整数数值范围
        int  整数,数据类型用于保存一些范围的整数数值范围：
        bigint  大整数,数据类型用于保存一些范围的整数数值范围

        float:单精度的小数
        double:双精度的小数
        decimal:非常准确的小数值
        如果使用小数时推荐使用decimal,decimal底层是字符串存储的,显示还是数字类型
       	示例:money decimal(10,5) 总共有10位数,小数点后最多可以有5位数
	
字符串：
        char(10)   括号中为长度,速度快,最多存储255个字符, 如果长度未占满,填充空格
        varchar(10)   节省空间,最多达255个字符,如果长度未占满,以当前字符串长度为准,不填充
        PS:创建数据表定长列往前放,这样SQL操作时效率会高些——SQL优化
        text    用于保存长度不确定的大字符串,可以组多到65535 (2**16 − 1)个字符
        mediumtext  用于保存长度不确定的大字符串，16,777,215 (2**24 − 1)
        longtext    用于保存长度不确定的大字符串，4,294,967,295 or 4GB (2**32 − 1)
        如果还有更长的字符串可以直接存入硬盘
        上传文件：
            文件内容存入硬盘
            文件路径存入数据库
时间类型
        DATETIME    最常用的类型
                YYYY-MM-DD HH:MM:SS
         DATE
                YYYY-MM-DD
         TIME
                HH:MM:SS
enum枚举
    示例:
        CREATE TABLE shirts (
                name VARCHAR(40),
                size ENUM('m', 'l', 'xl', 'xxl', 'xxxl')
            );
其它类型...
双引号表示字符串是mysql中特有,建议写为单引号一样表示字符串

表的操作

1.创建表
同一张表中，字段名是不能相同
create table 表名{
	字段名 字段类型	字段约束,
	字段名 字段类型	字段约束,
	...
}
主键约束:一张表只能有一个主键,表示当前字段唯一不重复且不能为空,primary key
auto_increment:当前主键自增1
主键可以是多个列组合而成,只有它们是唯一不重复的即可——联合主键
非空约束: NOT NULL
外键约束:约束当前表某一个字段是另一个表的主键
constraint 约束的外键名称 foreign key (当前表的外键字段) references 另一个表名(关联字段)

create table student(
	sid int  auto_increment primary key,
	name varchar(32) not null,
	department_id int,
	xx_id int,#外键
	constraint fk_user_depar foreign key (xx_id) references classes(cid)
#	constraint 约束名称 foreign key (当前表的外键字段) references 另一个表名(关联字段)  最后一个设置不要加逗号,会报错
)engine=innodb auto_increment=1 default charset=utf8;
#auto_increment=1设置主键自增的起始值为1
#alert table department auto_increment=1	修改主键自增的起始值为1
#set session auto_increment_increment=2; 基于会话级别的主键自增步长
#set global auto_increment_increment=2;基于全局级别的主键自增步长

2.删除表
drop table 表名;
3.修改表
alter table 表名 drop column 字段名; 删除列
alter table 表名 add column 字段名 字段类型; 添加列
alter table 表名 add primary key(主键字段); 添加主键
alter table 表名 drop primary key; 删除主键
alter table 表名 add unique 索引名('字段名'); 添加唯一索引
alter table 表名 add index 索引名('字段名'); 添加普通索引
alter table 表名 drop index 索引名; 删除索引
alter table 表名 default character set utf8;修改表的字符集
rename table 表名 to 新表名;修改表名
rename table 数据库名.表名 to 新数据库名.表名; 将当前表的数据移动到其他数据库下

表中记录的操作

1.插入记录
insert into 表名 (字段1,字段2...) values (字段1的值,字段2的值...),(字段1的值,字段2的值...)...
insert into 表2(name,age) select name,age from 表1;将表1中部分数据添加到表2
2.查询记录
select * from 表名;查询所有字段信息(这种方式查询效率比较低)
select 字段1 from 表名; 查询一个字段所有信息
3.修改记录
update 表名 set 字段名=字段值 where 条件判断;
4.删除记录
delete from 表名 where 条件;
delete from 表名;删除所有记录

单表查询

语法:
	select distinct 字段1,字段2... from 表名 as 别名
                          where	条件
                          group by  字段
                          having 筛选条件
                          order by  字段
                          limit 限制条数

关键字优先级

from
where
group by
select
distinct (去重)
having
order by
limit

as

as关键字可以给字段获取表起别名,可以省略
select  distinct  s.grades from student as s;
select  distinct  grades  g  from student  ;
select  distinct  grades  as g  from student  ;

distinct  去除重复记录

where

where放在from之后,用来过滤查询条件的
select  name,grades from student  where grades > 60;查询成绩大于60的学生姓名与成绩

where字句中可以使用:
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是80或90或100
4. like 'e%' 模糊查询,以e开头的字符串
    通配符可以是%或_，
    %表示任意个字符
    _表示一个字符 
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

group by

group by分组查询,将分组字段相同的分为一组
select查询的字段只能是分组字段,想要获取组内的其他相关信息,需要借助函数
select sex,max(age) from person where age < 25 group by sex;根据性别分组,找到年龄小于25的男女中年龄最大的值

having

having也是过滤的,用于过滤分组后的数据
执行优先级从高到低：where > group by > having
1. Where发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数
2. Having发生在分组group by之后,因而Having中可以使用分组的字段,但是无法直接取到其他字段,可以使用聚合函数
select sex,max(age) from person where age < 25 group by sex having sex = 1;

order by

order by 字段 根据字段排序,默认升序(asc),降序(desc)
select * from student  order by age;
select * from student  order by age desc; 降序
select * from student  order by age,name; 如果age相同根据name字段继续排序

limit

limit 限制查询的记录数(只在MYSQL中有用)
select * from student limit 5,5;从第五条记录后查询5条记录数

case…when

格式:
select	字段A
	case 字段A
	when 字段A的可能值1 then	执行相应操作
	when 字段A的可能值2	then	执行相应操作
	else 都不满足时的操作
	end 结束case语句
	
select  job,ename,
(case job   			当job是java时,薪水为14薪,python为13薪,其它照旧
when 'java' then sal * 14
when 'python' then sal *13
else sal 
end ) as newsal from emp;

聚合函数

数据处理函数(单行处理函数:处理一行输出一行)
Lower 转换小写
select lower(name)  from student;
upper转换大写
select upper(name)  from student;
substr 截取字符串,(参数1 被截取的字符串,参数2 起始下标,参数3 截取长度)数据库下标从1开始
select substr(name,1,4)  from student;
length 字符串长度
select length(name)  from student;
trim 去除前后空格，不包括中间空格
select * from student where name like trim(' k  ');
str_to_date 将字符串转换为日期
select ename ,hiredate from emp where hiredate =str_to_date('12-03-1981','%m-%d-%Y');
date_format	将日期转换为字符串
select date_format(date,’%Y-%m-%d’)  from student;
format 设置千分位
round 四舍五入
rand() 生成随机数
select round (rand()*122);
If null(x,1)  如果x为null就转为1
select name, if null(com,0)*12 as yearsal from student;

多行处理函数(分组函数/聚合函数)
sum 求和
avg 取平均值
max 取最大值
min 取最小值
count 取得记录数
1.分组函数自动忽略空值
2.分组函数不能直接使用在where关键字后面
select sun(sal + if null(comm,0))  from emp;
select count (comm) from emp where comm is null; 记录数为0；因为分组函数忽略空值