MySql教程

MySQL

本文主要是介绍MySQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

1、什么是数据库

数据库 DataBase 简称db 
数据库管理系统 DataBase Management System 简称DBMS 
sql是非过程化语言
创建时间和更新时间的默认值   CURRENT_TIMESTAMP

2、数据库设计过程阶段

按照规范的设计方法,一个完成的数据库设计一般分为以下六个阶段:
1. 需求分析: 分析用户的需求,包括数据、功能和性能需求;
2. 概念结构设计:主要采用E-R模型进行设计,包括画E-R图;
3. 逻辑结构设计:通过将E-R图转换成表,实现从E-R模型到关系模型的转换,进行关系规范化;
4. 数据库物理设计:主要是为所设计的数据库选择合适的存储结构和存储路径;
5. 数据库的实施:包括编程、测试和试运行;
6. 数据库运行和维护:系统的运行和数据库的日常维护

3、完整性描述

1.实体完整性指表中行的完整性
2.域完整性指列的值域的完整性,如数据类型、格式、值域范围、是否允许空值等等
3.参照完整性基于外键与被引用主键之间的关系,确保键值在所有表中的一致性

4、基本命令

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

mysql -uroot -p123456

update user set password=password('123456')where user='root'; --修改密码 
flush privileges; --刷新数据库 
show databases; --显示所有数据库 
use dbname; --打开某个数据库 
show tables; --显示数据库mysql中所有的表 
describe user; --显示表mysql数据库中user表的列信息 
create database name; --创建数据库 
use databasename; --选择数据库 
exit; 退出Mysql 
sc delete mysql;  清空服务 安装失败的时候使用
? 命令关键词 
: 寻求帮助 
-- 表示注释

5、DDL 数据定义语言

数据库操作

创建数据库 : create database [if not exists] 数据库名;

删除数据库 : drop database [if exists] 数据库名; 可选属性 判断是否存在

查看数据库 : show databases;

使用数据库 : use 数据库名;

数据库的列类型

数值:
	tinyint :十分小的数值    1字节
	samllint:较小的数值		 2字节
	int:整数				 4字节
	mediumint:中等的数值	 3字节
	bigint:较大的数据		 8字节
	float:浮点数		     4字节
	double:双精度浮点数      8字节
	decimal:字符串形式的浮点数 金融计算的时候一般使用decimal		
	
字符串:
	char:字符串    固定大小   0-255
	varchar:可变字符串        0-65535 -->string
	tinytext:微型文本         2^8-1
	text:文本串               2^16-1
	
时间日期:
	date;YYYY-MM-DD  日期格式
	time:HH:mm:ss    时间
	datetime:YYYY-MM-DD HH:mm:ss 最常用的时间格式
	timestamp:时间戳  1920.1.1到现在的毫秒数
	year:年份表示
	
null:
	没有值,未知

数据库字段类型(属性)

UnSigned:
 - 无符号的 	
 - 声明该数据列不允许负数 .

ZEROFILL:
 - 0填充的
 - 不足位数的用0来填充 , 如int(3),5则为005
 - 
Auto_InCrement(自增):
 - 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
 - 通常用于设置主键 , 且为整数类型
 - 可定义起始值和步长
 - 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
 - SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
 
NULL 和 NOT NULL:
 - 默认为NULL , 即没有插入该列的数值
 - 如果设置为NOT NULL , 则该列必须有值

DEFAULT:
 - 默认的
 - 用于设置默认值
 - 例如,性别字段,默认为”男” , 否则为 “女” ; 若无指定该列的值 , 则默认值为”男”的值

mysql数据表的类型
在这里插入图片描述

创建数据库表

SQL语法:

	create table if not exists `student` (
	`id` int (4) not null default comment '学号',
	`name` varchar(30) not null default '匿名‘ comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码',
	`sex` varchar(2) not null default '男' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`address` varchar(100) default null comment '家庭住址',
	primary key(`id`)
) engine=innodb default charset=utf8

格式:

create table [if exists] `表名`(
	'字段名' 列类型 [属性] [索引] [、注释],
	'字段名' 列类型 [属性] [索引] [、注释],
	.....
	'字段名' 列类型 [属性] [索引] [、注释]
)[表类型] [字符集设置] [注释]

查看数据库创建语句:

show create database [数据库名]

查看表创建语句:

show create table [表名]

显示表结构:

desc [表名]

数据库表操作

-- 修改表名: 
ALTER TABLE 旧表名 RENAME AS 新表名

-- 增加表的字段: 
ALTER TABLE 表名 ADD 字段名 列属性

-- 修改表的字段(重命名,修改约束)
ALTER TABLE 表名 MODIFY 字段名 列属性[] -- 修改约束
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[] -- 字段重命名

-- 删除表的字段
ALTER TABLE 表名 DROP 字段名

删除表

-- 删除表(如果表存在再删除)
DROP TABLE [ IF EXISTS ] 表名

所有的创建和删除操作尽量加上判断,以免报错

外键

方式一:

	CREATE TABLE `student` (
`id` int NOT NULL COMMENT '学号',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`gradeid` INT(4) NOT NULL COMMENT '年级',
`sex` varchar(4) NOT NULL DEFAULT '男' COMMENT '性别',
`addr` varchar(50) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES 	`grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE IF EXISTS `grade`(
	`gradeid` INT(4) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二: 创建表成功后,添加外键约束

CREATE TABLE IF EXISTS `student` (
`id` int NOT NULL COMMENT '学号',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`gradeid` INT(4) NOT NULL COMMENT '年级',
`sex` varchar(4) NOT NULL DEFAULT '男' COMMENT '性别',
`addr` varchar(50) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE IF EXISTS `grade`(
	`gradeid` INT(4) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

6、DML数据操作语言

插入数据

INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,’值2’,’值3’)

INSERT INTO grade(gradename) VALUES (‘大三’),(‘大四’);

修改数据

UPDATE 表名 SET column_name=value [,column_name2=value2,…] [WHERE condition];

UPDATE grade SET gradename = ‘高中’ WHERE gradeid = 1;

where 查询条件
在这里插入图片描述

删除数据

DELETE FROM 表名 [WHERE condition];

— 删除最后一个数据

DELETE FROM grade WHERE gradeid = 2

Truncate命令删除数据

作用:用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

语法:

TRUNCATE [TABLE] table_name; 
-- 清空年级表 
TRUNCATE grade

区别:注意:区别于DELETE命令

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
不同 :
使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器(清零)
使用TRUNCATE TABLE不会对事务有影响
DELETE 删除表自增不会清零

7、DQL数据查询语言

select语法

SELECT [ALL | DISTINCT] 
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} 
FROM table_name [as table_alias] 
    [left | right | inner join table_name2] -- 联合查询 
    [WHERE ...] -- 指定结果需满足的条件 
    [GROUP BY ...] -- 指定结果按照哪几个字段来分组 
    [HAVING] -- 过滤分组的记录必须满足的次要条件 
    [ORDER BY ...] -- 指定查询记录按一个或多个条件排序 
    [LIMIT {[offset,]row_count | row_countOFFSET offset}]; 
    -- 指定查询的记录从哪条至哪条

where逻辑操作符
在这里插入图片描述
查询多个数据 – 多个列名,用英文逗号隔开

SELECT studentno,studentname FROM student;

统计表中有多少条数据 --count(*)

select count(*) from student

重复数据 去重 --distinct

SELECT DISTINCT `StudentNO` FROM result

模糊查询

运算符语法描述
IS NULLa IS NULL如果操作符为null,结果为真
IS NOT NULLa IS NOT NULL如果操作符不为null,结果为真
BETWEEN ANDa BETWEEN b AND c如果a在b和c之间,结果为真
LIKEa LIKE bSQL匹配,如果匹配b,结果为真
INa IN (A,B,C,D)如果a在(A,B,C,D)中,结果为真(为具体的一个或多个值)

模糊搜索查询

与like结合使用的操作符 ("%" 代表匹配0到任意个字符)("_" 匹配一个字符)
查询名字中间带嘉的同学
	select * from student WHERE studentname LIKE '%嘉%';
查询姓刘的同学,名字后只有一个字的
	select * from student where studentname like '刘_'

查询学号为1000,1001,1002学生的成绩

select * from student WHERE studentno IN (1000,1001,1002);

查询学号为在1002-1009之间的学生的成绩

select * from student WHERE studentno BETWEEN 1002 AND 1009;

查询BornDate为空的字段

select * from student WHERE BornDate IS NULL;

as取别名
作用:
· 可给数据列取一个新别名
· 可给表取一个新别名
· 可把经计算或总结的结果用另一个新名称来代替

– 这里是为列取别名(当然as关键词可以省略)

SELECT studentno AS 学号,studentname AS 姓名 FROM student; 

– 使用as也可以为表取别名

SELECT studentno AS 学号,studentname AS 姓名 FROM student AS S;

– 使用as,为查询结果取一个新名字
– CONCAT()函数拼接字符串

SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

连接查询

join

  • INNER JOIN 如果表中有至少一个匹配,则返回行
  • LEFT JOIN 即使右表中没有匹配,也从左表中返回所有的行
  • RIGHT JOIN 即使左表中没有匹配,也从右表中返回所有的行

key值为两个表相连的中间值 相等即为连接

select_list 需要查询的信息:查询条件和中间值

TableA A 给表起别名 as可以省略
在这里插入图片描述
分页和排序

排序:升序 ASC, 降序 DESC

语法:ORDER BY 字段 [ASC| DESC]
# 查询的结果根据成绩降序排列
select   studentno,studentname,subjectname,subjectresult from student s 
inner join result r 
on s.studentno = r.studentno
order by subjectresult DESC

分页 limit

语法:limit 起始值,页面大小
第N页 (n-1)* pagesize,pagesize
select   studentno,studentname,subjectname,subjectresult from student s 
inner join result r 
on s.studentno = r.studentno
order by subjectresult DESC
limit 0,10

分组 和 过滤

group by
注意:group by 可以多个字段进行分组,筛选条件使用having
having在group by后面使用

select count(*),字段2 from 表
where 字段 = 值1
group by 字段2  
having count(*) > 10;

子查询

在where语句中嵌套一个语句

从里向外查询(先执行最里面在执行外面)

在这里插入图片描述

8、Mysql函数

  • 常用函数

    • 数学运算函数

      select ABS(-8) -- 绝对值
      SELECT CTILING(9.4)   --向上取整
      SELECT FlOOR(9.4)   --向下取整
      SELECT RAND()    --取随机数
      SELECT SIGN(10)   --返回一个数的符号 正数返回1 负数返回-1
      
    • 字符串函数

      SELECT CHAR_LENGTH('hello world')   --返回字符串的长度
      SELECT CONCAT('我','你')   -- 拼接字符串
      SELECT INSERT('str',1,2,'str1')    --从第一个字符开始,将str的两个字符替换成str1
      SELECT LOWER('ABC')   --转换成小写
      SELECT UPPER('abc')   --转换成大写
      SELECT INSTR('hello world','w')  --返回第一次出现的字串的索引
      SELECT REPLACE('hello world','world','世界') --替换出现的指定字符串
      SELECT SUBSTR('坚持就能成功',4,3) --返回指定的字符串(源字符串,截取的位置,截取的长度)
      SELECT REVERSE ('坚持就能成功') --反转
      
    • 时间日期函数

      SELECT CURRENT_DATE() --获取当前日期
      SELECT CURDATE() --获取当前日期
      SELECT NOW() --获取当前时间
      SELECT LOCALTIME() --本地时间
      SELECT SYSDATE() --系统时间
      SELECT YEAR(NOW()) --年
      
    • 系统函数

      SELECT SYSTEM_USER() --系统用户
      SELECT USER() --系统用户
      SELECT VERSION() --系统版本
      
  • 聚合函数

     COUNT() 计数
     	select count(data) --会忽略所有的null值 data为字段名查询指定列的个数
     	select count(*) --计算行数 所有列都会查
     	select count(1) --计算行数 只会查一列
     SUM() 求和
     	select SUM(data) from 表名
     AVG() 平均值
     MAX() 最大值
     MIN() 最小值
    
  • MD5加密函数

增强算法复杂度和不可逆性

加密算法

UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1

插入时加密

INSERT INTO testmd5 VALUES(4,’xiaoming’,MD5(‘123456’))

如何校验 将用户传入数据库的密码 进行MD5加密

SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

9、事务

提交事务commit,回滚事务rollback

四大特性:ACID
A:原子性:事务是最小的工作单元,不可再分
C:一致性:事务必须保证多条DML语句同时成功或同时失败
I:隔离性:事务A与事务B之间具有隔离
D:持久性:最终数据必须持久化到硬盘文件中,事务才算成功结束

隔离性:
第一级别:读未提交(read uncommitted)

	事务A可以读取事务B未提交的数据

第二级别:读已提交(read committed)

	事务读取的数据是实时更新的数据

第三级别:可重复读(repeatable read)

	事务所读取的数据一直都是事务开启时的数据,即使数据被修改了,事务所读取的数据不会发生改变。事务开启读取的数据是备份数据,不能实时读取数据

第四级别:序列化读/串行读

	效率低,事务需要排队

10、索引

主键和具有unique约束的字段自动会添加索引

根据主键查询效率较高,尽量根据主键检索

创建索引:

create index 索引名 on 表名(字段名);
删除索引:

drop index 索引名 on 表名(字段名);
需要加索引的条件:

数据量庞大
该字段很少进行DML操作(字段进行修改操作,索引也需要维护)
该字段经常出现在where语句中(经常根据哪个字段查询)
索引底层采用的数据结构是:B + Tree

​ 通过B Tree 缩小扫描范围,底层索引进行了排序、分区,索引会携带数据在表中的“物理地址,最终通过索引检索到数据之后,获取数据的物理地址,通过物理地址定位到表中的数据

索引什么时候失效:

​ 模糊查询的时候,第一通配符使用的是%,这个时候索引会失效

11、三大范式

第一范式:任何一张表都应该有主键,并且每个字段原子性不可再分

第二范式:建立在第一范式基础之上,所有非主键的字段完全依赖主键,不产生部分依赖

多对多采用三张表,关系表存储另外两张表的主键,即关系表两个外键

第三范式:建立在第二范式基础之上,所有非主键的字段直接依赖主键,不能产生传递依赖

多对一采用两张表,多的加外键

一对一设计有两种方案:

主键共享:既是主键也是外键
外键唯一:一张表加外键,并且外键需要添加唯一约束

这篇关于MySQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!