命令行连接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;
