mysql -p <_【干货4】MySQL基础知识

(如果我分享的干货内容对你有帮助，可以通过赞或者评论的方式告诉我，我会持续分享；或者留言你想要的IT方面的支持，我将分享大家感兴趣的IT类技术干货)

命令行连接MySQL

mysql -h localhost -u root -p

mysql -u root -p

#1.数据库的创建和删除

#1.1创建数据库

CREATE DATABASE MySchool;

#1.2删除数据库

DROP DATABASE MySchool;

#1.3查看数据库

SHOW DATABASES;

#2.表的创建 修改 查看 以及删除

#2.1创建表

#创建学生表

DROP TABLE IF EXISTS student;

CREATE TABLE student(

studentNo INT(4) NOT NULL PRIMARY KEY,

loginPwd VARCHAR(20) NOT NULL,

studentName VARCHAR(50) NOT NULL,

sex CHAR(2) DEFAULT '男' NOT NULL ,

gradeId INT(4),

phone VARCHAR(50),

address VARCHAR(255) DEFAULT '地址不详',

bornDate DATETIME,

email VARCHAR(50),

identityCard VARCHAR(18) UNIQUE KEY

)COMMENT="学生表";#表注释 “学生表”

#创建科目表

DROP TABLE IF EXISTS `subject`

CREATE TABLE `subject`(

subjectNo INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,

subjectNmae VARCHAR(50),

classHour INT(4),

gradeId INT(4)

)

#创建成绩表

DROP TABLE IF EXISTS result;

CREATE TABLE result(

studentNo INT(4) NOT NULL COMMENT '学号',

subjectNo INT(4) NOT NULL COMMENT '课程编号',

examDate DATETIME DEFAULT NOW() NOT NULL COMMENT '考试日期',

score INT(4) COMMENT '考试成绩' NOT NULL

);

#创建年级表

DROP TABLE IF EXISTS grade;

CREATE TABLE grade(

gradeId INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT,

gradeName VARCHAR(50) NOT NULL

);

#2.2查看当前数据库表

SHOW TABLES;

#2.3查看表结构

DESCRIBE student;

#2.4删除表

DROP TABLE IF EXISTS student;

#2.5修改表

#2.5.1修改表名

DROP TABLE IF EXISTS `demo01`;

CREATE TABLE IF NOT EXISTS `demo01`(

`id` INT NOT NULL,

`name` VARCHAR(20) NOT NULL

);

ALTER TABLE `demo01` RENAME TO `demo02`;

#2.5.2添加表字段

ALTER TABLE demo02 ADD pwd VARCHAR(20) NOT NULL;

#2.5.3修改字段

ALTER TABLE demo02 CHANGE pwd PASSWORD VARCHAR(10);

#2.5.4删除字段

ALTER TABLE demo02 DROP PASSWORD;

#2.5.5添加约束

ALTER TABLE demo01

ADD CONSTRAINT PK_id PRIMARY KEY(id);

ALTER TABLE result

ADD CONSTRAINT FK_studentNo FOREIGN KEY(studentNo) REFERENCES student(studentNo);

#2.5.6删除约束

ALTER TABLE demo01

DROP PRIMARY KEY;

#2.5.7设置自增以及使用

#3.DML和DQL

/*3.1存储引擎 指定了表的存储类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。

MySQL5.5支持的存储引擎有InnoDB、MYISAM、Memory、MRG_MyISAM、Archive、Federated、CSV、BLACKHOLE等9中

常用的存储引擎InnoDB、MYISAM

InnoDB存储引擎：在事务处理上有优势，，需要进行频繁的更新、删除操作，同时还对事务的完整性要求比较高，需要实现并发控制，适合使用该存储引擎。

MYISAM存储引擎：不支持事务，也不支持外键，访问速度比较快，因此对不需要事务处理、以访问为主的应用适合使用该引擎。

如果想修改默认存储引擎，可以通过配置向导，也可以通过配置文件my.ini eg:defalutstorage-engine=MylSAM

*/

#3.1.1查看系统所支持的引擎类型

SHOW ENGINES;

#3.1.2查看当前的默认存储引擎

SHOW VARIABLES LIKE 'storage_engine%';

#3.1.3指定表的存储引擎 (有时也称表的存储引擎为表类型)

CREATE TABLE demo01(

id INT(4)

)ENGINE=MYISAM;

#3.1.4数据文件的存储位置 C:\ProgramData\MySQL\MySQL Server 5.6\data

#3.1.5MyISAM类型的表文件

/*

1..frm文件：表结构定义文件，存放表的元数据，包括表结构定义的信息，任何存储类型的表都会有。

2..MYI文件：索引文件，主要存放MyISAM类型表的索引信息。

3..MYD文件：数据文件，存放表中数据的文件。

*/

#3.1.6InnoDB类型的表文件

/*

1..frm文件：表结构定义文件，存放表的元数据，包括表结构定义的信息，任何存储类型的表都会有。

2..ibd文件：数据文件,可以通过my.ini文件中的参数innodb_data_home_dir查询或修改。

eg:innodb_data_home_dir="D:/MySQL DataFiles/"

*/

#3.1.7MySQL多行插入

INSERT INTO demo01 VALUES(1,'张三'),(2,'李四');

#3.1.8将查询结果插入到新表

CREATE TABLE demo02 (SELECT `id`,`name` FROM demo01);

#添加数据

/*subject表数据*/

INSERT INTO `subject` VALUES(1,'LogicJava',220,1),(2,'HTML',160,1),(3,'Java OOP',230,2);

/*student表数据*/

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10000','123','郭靖','男','1','13645667783','天津市河西区','1990-09-08 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10001','123','李文才','男','1','13645667890','地址不详','1994-04-12 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10002','123','李斯文','男','1','13645556793','河南洛阳','1993-07-23 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10003','123','张萍','女','1','13642345112','地址不详','1995-06-10 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10004','123','韩秋洁','女','1','13812344566','北京市海淀区','1995-07-15 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10005','123','张秋丽','女','1','13567893246','北京市东城区','1994-01-17 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10006','123','肖梅','女','1','13563456721','河北省石家庄市','1991-02-17 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10007','123','秦洋','男','1','13056434411','上海市卢湾区','1992-04-18 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('10008','123','何睛睛','女','1','13053445221','广州市天河区','1997-07-23 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('20000','123','王宝宝','男','2','15076552323','地址不详','1996-06-05 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('20010','123','何小华','女','2','13318877954','地址不详','1995-09-10 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30011','123','陈志强','男','3','13689965430','地址不详','1994-09-27 00:00:00',NULL,NULL);

INSERT INTO `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) VALUES('30012','123','李露露','女','3','13685678854','地址不详','1992-09-27 00:00:00',NULL,NULL);

/*result表数据*/

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10000','1','2016-02-15 00:00:00','71');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10000','1','2016-02-17 00:00:00','60');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10001','1','2016-02-17 00:00:00','46');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10002','1','2016-02-17 00:00:00','83');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10003','1','2016-02-17 00:00:00','60');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10004','1','2016-02-17 00:00:00','60');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10005','1','2016-02-17 00:00:00','95');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10006','1','2016-02-17 00:00:00','93');

INSERT INTO `result` (`studentNo`, `subjectNo`, `examDate`, `score`) VALUES('10007','1','2016-02-17 00:00:00','23');

#3.1.9删除数据

/*

1.DROP FROM 表名 [where限制条件]

2.truncate table 表名 执行速度快，删除后表的标识列会进行重新编号，删除数据不能恢复还原

*/

#3.1.10数据查询

#1.起别名

SELECT studentNo 学号, studentName AS 姓名 FROM student;

#2.合并列查询 (存在问题)

SELECT firstName+'.'+lastName AS 姓名 FROM employee;

#4.常用函数

/*

1.聚合函数

MAX()MIN()COUNT()AVG()SUM()

2.字符串函数

CONCAT(str1,str2,...,str3)

INSERT(str,pos,len,newstr)

LOWER(str)

UPPER(str)

SUBSTRING(str,startPos,len)

3.时间日期函数

CURDATE()获取当前日期

CURTIME()获取当前时间

NOW()获取当前日期和时间

WEEK(date)返回一年中的第几周

YEAR(date)返回年份

HOUR(time)返回小时

MINUTE(time)返回分钟

DATEDIFF(date1,date2)返回日期相隔的天数

ADDDATE(date,n)返回给定时间加上指定天数后的时间

4.数学函数

CELL(x)

FLOOR(x)

RAND()返回0~1之间的随机数

*/

#5.limit子句

/*注意：1.order by子句在MySQL中不再是放在最后

2.Limit子句第一个参数表示起始位置可以省略默认为0，第一条数据的起始位置为0

第二个参数表示要显示的条数

*/

SELECT * FROM Student WHERE gradeId=1 ORDER BY studentNo LIMIT 4;

SELECT * FROM Student WHERE gradeId=1 ORDER BY studentNo LIMIT 4,4;

#6.子查询 连接查询 分组查询

#7.创建临时表

CREATE TEMPORARY TABLE TT1(SELECT * FROM Student WHERE gradeId=1 ORDER BY studentNo LIMIT 4,5);

SELECT * FROM TT1;

#8.事务

/*

1.MySQL中支持事务的引擎有InnDB、BDB，InnDB存储引擎事务主要通过UNDO日志和REDO日志实现

UNDO日志：复制事务执行前的数据，用于在事务发生异常时回滚数据。

REDO日志：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，该内容将被刷新到磁盘。

2.事务的特性：

原子性(Atomicity)：事务是一个完整的操作，事务的各元素是不可分的。

一致性(Consistency)：当事务完成时，数据必须处于一致状态。

隔离性(Isolation)：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

持久性(Durability):事务的持久性是指无论系统是否发生了故障，事务处理的结果都是永久的，

3.事务使用步骤:

1.开始事务 begin或start TRANSACTION;

2.提交事务 COMMIT;

3.回滚事务 ROLLBACK;

4.设置事务自动提交关闭或开启

set autocommit = 0|1;

*/

#eg：转账 张三给李四转账1000元

CREATE TABLE Bank(

id INT(4) PRIMARY KEY,

`name` VARCHAR(20) NOT NULL,

account INT NOT NULL

)

INSERT INTO Bank VALUES(1,'张三',500),(2,'李四',2000);

SELECT * FROM bank;

BEGIN;

UPDATE Bank SET `account`-=1000 WHERE `name`='张三';

UPDATE Bank SET `account`+=1000 WHERE `name`='李四';

COMMIT;

#9.视图

CREATE VIEW vw_stuInfo

AS

SELECT studentNo,studentNAme,phone,address,email FROM Student;

SELECT * FROM vw_stuInfo;

DROP VIEW IF EXISTS `vw_stuInfo`;

#10.索引

/*

1.普通索引

2.唯一索引

3.主键索引

4.复合索引

5.全文索引

6.空间索引

创建语法：create [unique|fulltext|spatial] index_name on table_name(column_name[length]...)

length:指定索引长度，可选参数，只有字符串类型才能指定索引长度

*/

CREATE INDEX `index_student_studentName`

ON `student`(`studentName`)

DROP INDEX index_student_studentName ON student;

#10.1查看索引

SHOW INDEX FROM `student`;

#11.数据库的备份与恢复

/*1.DOS命令备份与恢复

1.mysqldump 备份命令 将包含数据的表结构和数据内容转换成相应的create语句和insert into 语句

mysqldump -u username -h host -p password dbname[tbname1[,tbname2...]] > filename.sql

2.mysql 还原命令 mysql -u username -p [dbname] < filename.sql

*/

CREATE DATABASE MySchoolDB;

USE myschooldb;

SELECT * FROM student;

DROP TABLE student;

/* 2.mysql命令恢复数据库 */

source d:\\BACKUP\\student.sql;

/*3.通过复制文件实现数据备份和恢复

通过C:\ProgramData\MySQL\MySQL Server 5.6\data找到指定数据库文件

3.1flush tables； 3.2停止数据库服务

*/

#4.表数据导出到文本文件

USE Myschool;

SELECT * FROM `result` INTO OUTFILE 'd:/backup/result.txt'

#5.文本文件导入到数据库

USE myschoolDB;

CREATE TABLE result(

studentNo INT(4) NOT NULL COMMENT '学号',

subjectNo INT(4) NOT NULL COMMENT '课程编号',

examDate DATETIME DEFAULT NOW() NOT NULL COMMENT '考试日期',

score INT(4) COMMENT '考试成绩' NOT NULL

);

LOAD DATA INFILE 'd:/backup/result.txt' INTO TABLE result;

SELECT * FROM result;

#查看MySQL帮助

HELP DATA TYPES;

HELP ALTER TABLE;

#注意： 设置结果集以某种编码格式显示 SET NAMES GBK;