MySQL基础知识

最新推荐文章于 2024-05-16 01:07:19 发布

原创最新推荐文章于 2024-05-16 01:07:19 发布 · 10w+ 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mysql基本使用

D-数据库+测试+搜索引擎+缓存专栏收录该内容

16 篇文章

订阅专栏

一些基础知识,不喜勿喷

一些常用SQL

主键从1开始

TRUNCATE TABLE log_monitor;

基本语句(#和-- 以及/*注释内容*/是mysql的3种注释的写法,注意--后必须空格,以及语句 ` 和 ' 的区别)

# 配置好环境变量后,在cmd进入mysql
# mysql -u root -p
# 创建数据库
create database test;
# 查看数据库
show databases;
# 选择(改变)数据库
use test;
# 删除数据库
drop database test
# 查看当前数据库
select database();
#-------------创建表----------------
use test2;
#创建学生表
CREATE TABLE `student`(
	`studentNo` INT(4) NOT NULL COMMENT '学号',
	`loginPwd` VARCHAR(20) NOT NULL COMMENT '密码',
	`studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
	`sex` CHAR(2) DEFAULT '男' COMMENT '性别',
	`gradeId` INT(4)  UNSIGNED COMMENT '年级编号',
	`phone` VARCHAR(50)  COMMENT '联系电话',
	`address` VARCHAR(255)  DEFAULT '地址不详'COMMENT '地址', 
        `bornDate` DATETIME  COMMENT '出生时间',  
	`email` VARCHAR(50) COMMENT'邮件账号',
	identityCard VARCHAR(18)  COMMENT'身份证号码',
	PRIMARY KEY(`studentNo`)	
);
#查看数据库里面的表
show tables;
#c查看表结构(属性相关,主键,默认值等详情)
DESC student;
#删除表(if exists最好加上)
drop table if exists student2;
#查看默认引擎
 show variables like '%engine%';
#修改表newstudentb表示修改后的新表
alter table student rename to newstudent;
#修改表也可以不要to,我把它改回去
alter table newstudent rename student
#添加字段
alter table student add `pwd` VARCHAR(32) not null;
#修改字段
alter table student change `pwd` `password` CHAR(10) not null;
#删除表字段
alter table student drop `password`;
#添加外键仅供练习,实际不会使用这种物理外键,而是使用对应关系的逻辑外键,具体操作百度
#查看mysql帮助手册(查API最好)
help contents;

DML和DQL

-- MySQ8L查询支持的存储引擎
show engines
-- 查询默认引擎
show variables like '%storage_engine%';
-- 插入数据
INSERT INTO `test2`.`student`(`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES (11, '1234', 'zhangsan', '男', 1, '18883173476', '重庆江北区', '1992-06-12 15:19:03', '1090510535@qq.com', '1');
-- 插入多行数据(值之间用逗号隔开)
INSERT INTO `test2`.`student`(`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES (12, '1234', 'zhangsan', '男', 1, '18883173476', '重庆江北区', '1992-06-12 15:19:03', '1090510535@qq.com', '1'),(13, '1234', 'zhangsan', '男', 1, '18883173476', '重庆江北区', '1992-06-12 15:19:03', '1090510535@qq.com', '1');
-- 将查询到的数据插入到新表phone_list(无需新建)
CREATE TABLE phone_list(SELECT `studentName`,`phone` FROM `student`)
-- 更新数据(注意where限制条件)
UPDATE phone_list SET phone=18883173475 WHERE studentName='zhangsan'
-- 删除条件(注意where限制条件)
DELETE FROM phone_list WHERE studentName='zhangsan'
-- 相当于没有where条件限制的删除,但是速度更加快一点,同时可以清楚id自增长的记录,清零(表结构,约束索引等不会被改动,但是不能用于外键约束的的表上删除)
TRUNCATE table phone_list
-- 查询控空值(注意别名的使用)
SELECT studentName AS '学生姓名', phone AS '电话' FROM phone_list WHERE phone IS null
--                  ==========常用函数(聚合函数,字符串函数,时间日期函数,数学函数)
-- 1聚合函数(平均值,总计,最大值,最小值,求和)
-- score表示求和的列
SELECT SUM(score) from result
-- 求平均值
select 	AVG(score) AS '平均分' from result
-- 2字符串函数(还有拼接替换大小写转换等)
-- 拼接函数
select CONCAT('My','sq','l')
-- 3时间函数(时间,日期,周,小时,间隔时间等)
-- 获取当前日期 2018-12-30
select CURDATE()
-- 获取当前时间 16:26:49
select CURRENT_TIME()
-- 获取当前日期和时间 2018-12-30 16:27:47
SELECT NOW()
-- 排序(默认升序,注意是首位先比较,逐位比较)
select *  from result ORDER BY score
-- 降序
select *  from result ORDER BY score DESC
-- limit限制查询结果长度(记录从1开始)
select *  from result LIMIT 2
-- 子查询,也叫嵌套查询(就是查询结果(注意可能为0或1个或者多个结果)做另一个查询的条件,然后通过比较运算符(只有确定查到的掉件为一个的情况下)再查询),
-- 太多还是百度或者查询资料

主要有MyISAM(不支持事务,主要用于查询快),InnoDB(支持事务,占用空间大一些)等9种

MyISAM：MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

InnoDB：InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL+API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

高级查询(就是多表多条件综合查询)

-- 高级查询(多表多条件加判断以及对查询(分组查询)结果进行处理,如排序等操作的查询)还有多表连接查询是重点难点
-- EXISTS和NOT EXISTS的子查询(重点是否有返回记录,而不是记录本身,这是和IN的区别之一)
-- EXIT的基本用法(判断数据库对象(如表)是否存在,或者在where条件后表示附件条件)
DROP TABLE IF EXISTS temp
-- 和whhere搭配(如果有name为张三的那么就全查询,否则不查询)
SELECT * FROM result where EXISTS(SELECT name from result where name='张三')
-- 单列分组查询(为每个科目编号(分组)求平均分并且降序排列)ORDER BY AVG(score)  DESC顺序不要搞错
SELECT subject_no , AVG(score) AS 课程平均分 from result group by subject_no ORDER BY AVG(score)  DESC
-- 多列分组查询(略)
-- 使用having子句进行分组筛选(分组后筛选,这是和where的区别之一),用法类似
-- 查询学习科目人数超过2个人的科目编号
SELECT count(*) AS 人数 ,subject_no AS 科目编号 from result GROUP BY subject_no HAVING count(*)>2
-- 连接查询的方式有1:内连接查询(比如主外键关系的两张表)2:外连接查询(表与表之间有主持之分,所以有左外外连接和右外外连接的却别)
-- LEFT OUTER JOIN ... ON 和RIGHT OUTER JOIN ... ON  (OUTER可以省略)
-- where连接两张表属相相同的字段,或者inner join 另一张表 on 条件,后者班可以连接多张表

事务,视图,索引,备份,恢复

-- 事务(逻辑单元必须有4个属性,原子性,一致性,隔离性,持久性),以转账为例子
-- 注意mysql默认开启自动提交,就相当于每一条语句都有commit语句,这样不方便看到效果
-- 关闭自动提交0表示关闭,1表示开启
SET autocommit=1
BEGIN;
UPDATE bank SET money=money-50 WHERE name='a';
UPDATE bank SET money=money+50 WHERE name='b';
COMMIT;
select * from bank
COMMIT;
-- 事务的回滚ROLLBACK
-- 查询尽量不要用事务浪费资源,修改操作尽量不要出现等待用户的操作,不然事务造成资源阻塞
-- 视图筛选部分数据作为查询,这样更安全和有针对性,可以理解为子查询的结果当做新的表,但是不是真正的表,是虚拟表,充当筛选器的
-- 视图作用
-- 1:进行表的筛选,2:防止敏感数据被读取,3:将多个物理表进行抽象成逻辑表
-- 视图的好处
-- 1:修改列名,可读性变强,并且不会对原始数据进行破坏,并且获取数据相对容易一些,2:调试视图比调试查询容易更容易
-- 创建视图(修改视图相应的表也会修改,但是会有一些限制,所以视图一般适用于查询的)
CREATE VIEW view_student AS SELECT studentName phone FROM student
-- 索引,首先和书的目录一样,都是可以快速查到相应数据的一种指针,主要用于改善查询性能
-- 索引分类
-- 1:普通索引,2:唯一索引,:3主键索引,4:符合索引,5:全文索引,6:空间索引
-- 创建索引 CREATE [索引类型可选] INDEX 索引名称 ON 表(需要添加的索引列[字符串可以指定长度])
CREATE INDEX index_studentName ON student(studentName)
-- 创建索引的选取原则
-- 1:频繁搜索的列,2:经常查询的额列,3:经常排序分组的列,经常用作连接的列(主外键)
-- 查看索引
show index FROM student
-- 备份数据库(一张表为例子,test2数据库,表student),当然一般工具更贱方便快捷,命令了解一下
-- (cmd命名窗口) mysqldump -u root -p test2 student > D:\Aworkspace\MySQL\backup\20181230.sql
-- 恢复略