MySQL
mysql的安装
- 下载压缩包解压到自己想解压的路径;
- 添加环境变量:
-
我的电脑->属性->高级->环境变量;
-
选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹;
-
在D:\Environment\mysql-5.7.19下新建 my.ini 文件;
-
编辑 my.ini 文件 ,注意替换路径位置;
[mysqld] basedir=D:\Program Files\mysql-5.7\ datadir=D:\Program Files\mysql-5.7\data\ port=3306 skip-grant-tables
-
启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,然后输入mysqld –install (安装mysql)
-
再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
-
然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面(密码可为空)
-
进入界面后更改root密码:
update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;
最后输入flush privileges; 刷新权限
-
修改 my.ini文件删除最后一句skip-grant-tables
-
重启mysql即可正常使用
net stop mysql
net start mysql -
连接上测试出现welcome to the mysql monitor就安装好了
-
初识MySQL
-
数据库(DataBase,简称DB):数据库是长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据"仓库";可以保存,并能安全管理数据(如:增加、删除、改动、查看),减少冗余。现今社会,数据库是几乎软件体系中最核心的一个存在。
-
我要学习的MySQL是一个数据库管理系统(DataBase Management System:简称DBMS),它是现在流行的开源的,免费的,关系型数据库。
-
为了可以更清楚的学习数据库操作代码和更清楚的看到图形化的显示,我建议下载SQLyog软件。SQLyog是可手动操作,管理MySQL数据库的软件工具,具有:简洁,易用,图形化的特点。
-
使用SQLyog管理工具自己完成以下操作 :
-
连接本地MySQL数据库
-
新建MySchool数据库
- 数据库名称MySchool
- 新建数据库表(grade)
- 字段
- GradeID : int(11) , Primary Key (pk)
- GradeName : varchar(50)
- 字段
通过图形界面操作数据库很方便,但是我们必须要掌握数据库操作语句的使用。在SQLyog中,历史记录里可以看到操作相应的数据库操作语句。
-
-
创建数据表:
create table [if not exists] `表名`(#反引号用于区别MySQL保留字与普通字符而引入的 (英文状态下的键盘esc下面的键:“`”) '字段名1' 列类型 [属性][索引][注释], '字段名2' 列类型 [属性][索引][注释], '字段名n' 列类型 [属性][索引][注释]#最后一行语句后面不加逗号 )[表类型][表字符集][注释];#分号可以加可以不加,为区别语句建议加上
CREATE TABLE IF NOT EXISTS `student` ( `id` int(4) NOT NULL AUTO_INCREMENT 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 '地址', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
Auto_InCrement :自动增长的,每添加一条数据,自动在上一个记录数上默认加1;
-
DEFAULT:默认的,用于设置默认值;
-
NULL 和 NOT NULL:默认为NULL,即没有插入该列的数值;如果设置为NOT NULL,则该列必须有值;
-
ZEROFILL:0填充的;
-
UnSigned:无符号的,声明该数据列不允许负数;
-
数据值和列类型:
- 数值类型
- 字符串类型
- 日期和时间型数值类型
- NULL值
-
设置数据表的类型:
-
MySQL的数据表的类型有:MyISAM,InnoDB,HEAP,BOB,CSV等。其中常的MyISAM与InnoDB类型。
MyISAM:节约空间及相应速度;
InnoDB:安全性,事务处理及多用户操作数据表。
-
-
设置数据表字符集:
设定方法:
- 创建时通过命令来设置,如:CREATE TABLE 表名()CHARSET = utf8;
- 如无设定,则根据MySQL数据库配置文件my.ini中的参数设定。
-
修改数据库:
-
修改表(ALTER TABLE):
-
修改表名:ALTER TABLE 旧表名 RENAME AS 新表名
-
添加字段:ALTER TABLE 表名 ADD字段名 列属性[属性]
-
修改字段:
-
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
-
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
-
-
删除字段 :ALTER TABLE 表名 DROP 字段名
-
-
删除数据表:
DROP TABLE [IF EXISTS] 表名;(IF EXISTS为可选,判断是否存在该数据表,如删除不存在的数据表会抛出错误。)
-
-
MySQL数据管理
-
外键:在实际操作中,将一个表的值放入第二个表来表示关联,所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时,第二个表中保存这些值的属性称为外键(foreign key)。
-
创建外键:
-
建表时指定外键约束:
#创建外键的方式一:创建子表同时创建外键 #年级表(id\年级名称) CREATE TABLE `grade` ( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 #学生信息表(学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号) CREATE TABLE `student` ( `studentno` INT(4) NOT NULL COMMENT '学号', `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名', `sex` TINYINT(1) DEFAULT '1' COMMENT '性别', `gradeid` INT(10) DEFAULT NULL COMMENT '年级', `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机', `address` VARCHAR(255) DEFAULT NULL COMMENT '地址', `borndate` DATETIME DEFAULT NULL COMMENT '生日', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', PRIMARY KEY (`studentno`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8
-
建表后修改
ALTER TABLE student ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
-
-
删除外键:(删除具有主外键关系的表时 , 要先删子表 , 后删主表)
#删除外键 ALTER TABLE student DROP FOREIGN KEY FK_gradeid; #发现执行完上面的,索引还在,所以还要删除索引,索引是建立外键的时候默认生成的。 ALTER TABLE student DROP INDEX FK_gradeid;
-
数据库数据管理:
- 数据库意义就是:数据存储,数据管理;
- 管理数据库数据方法:可以通过SQLyog等管理工具管理数据库数据,也可以通过DML语句管理数据库数据;
- DML语言(数据操作语言):用于操作数据库对象中所包含的数据,包括:
- INSERT (添加数据语句)
- 语法:INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,‘值2’,‘值3’)
- 字段或值之间用英文逗号隔开;'字段1,字段2…'该部分可省略,但添加的值务必与表结构,数据列,顺序相对应,且数量一致;可同时插入多条数据,values后用英文逗号隔开。
- UPDATE (更新数据语句)
- 语法:UPDATE 表名 SET column_name=value [,column_name2=value2,…] [WHERE condition];
- column_name为要更改的数据列;value为修改后的数据,可以为变量,具体值,表达式或者嵌套的SELECT结果;condition为筛选条件,如不指定则修改该表的所有列数据。
- WHERE条件子句可以简单的理解为 : 有条件地从表中筛选数据。
- DELETE (删除数据语句)
- 语法DELETE FROM 表名 [WHERE condition];
- condition为筛选条件,如不指定则删除该表的所有列数据。
- TRUNCATE命令用于完全清空表数据,但表结构,索引,约束等不变;语法:TRUNCATE [TABLE] table_name;
- 区别于DELETE命令:
- 相同:都能删除数据,不删除表结构,但TRUNCATE速度更快
- 不同:使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器;使用TRUNCATE TABLE不会对事务有影响。
- INSERT (添加数据语句)
使用DQL查询数据
-
DQL(Data Query Language:数据查询语言)
-
SELECT语法:
-
查询表结果时,可指定查询结果的数据列
#查询所有学生信息 SELECT * FROM student; #查询指定列(学号 , 姓名) SELECT studentno,studentname FROM student;
-
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;
-
DISTINCT(去重)关键字的使用
#查看哪些同学参加了考试(学号)--去除重复项 SELECT * FROM result; #查看考试成绩 SELECT studentno FROM result; #查看哪些同学参加了考试 SELECT DISTINCT studentno FROM result; #了解:DISTINCT 去除重复项 , (默认是ALL)
-
where条件语句:
-
用于检索数据表中符合条件的记录;
-
搜索条件可由一个或多个逻辑表达式组成;结果一般为真或假。
#满足条件的查询(where): SELECT Studentno,StudentResult FROM result; #查询考试成绩在95-100之间的 SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 AND StudentResult<=100; #AND也可以写成&& SELECT Studentno,StudentResult FROM result WHERE StudentResult>=95 && StudentResult<=100; #模糊查询(对应的词:精确查询) SELECT Studentno,StudentResult FROM result WHERE StudentResult BETWEEN 95 AND 100; #除了1000号同学,要其他同学的成绩 SELECT studentno,studentresult FROM result WHERE studentno!=1000; #使用NOT SELECT studentno,studentresult FROM result WHERE NOT studentno=1000;
-
-
模糊查询
#模糊查询 between and \ like \ in \ null # LIKE #查询姓李的同学的学号及姓名 #like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符) SELECT studentno,studentname FROM student WHERE studentname LIKE '李%'; #查询姓李的同学,后面只有一个字的 SELECT studentno,studentname FROM student WHERE studentname LIKE '李_'; #查询姓李的同学,后面只有两个字的 SELECT studentno,studentname FROM student WHERE studentname LIKE '李__'; #查询姓名中含有 文 字的 SELECT studentno,studentname FROM student WHERE studentname LIKE '%文%'; #查询姓名中含有特殊字符的需要使用转义符号 '\' #自定义转义符关键字: ESCAPE ':' #查询学号为1000,1001,1002的学生姓名 SELECT studentno,studentname FROM student WHERE studentno IN (1000,1001,1002); #查询地址在北京,南京,河南洛阳的学生 SELECT studentno,studentname,address FROM student WHERE address IN ('北京','南京','河南洛阳'); #查询出生日期没有填写的同学 #不能直接写=NULL , 这是代表错误的 , 用 is null SELECT studentname FROM student WHERE BornDate IS NULL; #查询出生日期填写的同学 SELECT studentname FROM student WHERE BornDate IS NOT NULL; #查询没有写家庭住址的同学(空字符串不等于null) SELECT studentname FROM student WHERE Address='' OR Address IS NULL;
-
连接查询
#查询参加了考试的同学信息(学号,学生姓名,科目编号,分数) SELECT * FROM student; SELECT * FROM result; #思路: #1. 分析需求,确定查询的列来源于两个类,student result,连接查询 #2. 确定使用哪种连接查询?(内连接) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno #右连接(也可实现) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s RIGHT JOIN result r ON r.studentno = s.studentno #等值连接 SELECT s.studentno,studentname,subjectno,StudentResult FROM student s , result r WHERE r.studentno = s.studentno #左连接 (查询了所有同学,不考试的也会查出来) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno = s.studentn #查一下缺考的同学(左连接应用场景) SELECT s.studentno,studentname,subjectno,StudentResult FROM student s LEFT JOIN result r ON r.studentno = s.studentno WHERE StudentResult IS NULL
-
自连接
#创建一个表 CREATE TABLE `category` ( `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', `pid` INT(10) NOT NULL COMMENT '父id', `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryid`) ) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 #插入数据 INSERT INTO `category` (`categoryid`, `pid`, `categoryName`) VALUES('2','1','信息技术'), ('3','1','软件开发'), ('4','3','数据库'), ('5','1','美术设计'), ('6','3','web开发'), ('7','5','ps技术'), ('8','2','办公信息'); #编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称) #核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接) SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目' FROM category AS a,category AS b WHERE a.`categoryid`=b.`pid`
#查询参加了考试的同学信息(学号,学生姓名,科目名,分数) SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON sub.subjectno = r.subjectno #查询学员及所属的年级(学号,学生姓名,年级名) SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称 FROM student s INNER JOIN grade g ON s.`GradeId` = g.`GradeID` #查询科目及所属的年级(科目名称,年级名称) SELECT subjectname AS 科目名称,gradename AS 年级名称 FROM SUBJECT sub INNER JOIN grade g ON sub.gradeid = g.gradeid #查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩) SELECT s.studentno,studentname,subjectname,StudentResult FROM student s INNER JOIN result r ON r.studentno = s.studentno INNER JOIN `subject` sub ON r.subjectno = sub.subjectno WHERE subjectname='数据库结构-1'
-
子查询
# 查询数据库结构的所有考试结果(学号,科目编号,成绩),并且成绩降序排列 #使用子查询(执行顺序:由里及外) SELECT studentno,subjectno,StudentResult FROM result WHERE subjectno=( SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构' ) ORDER BY studentresult DESC; #查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名 #方法二:使用连接查询+子查询 #分数不小于80分的学生的学号和姓名 SELECT r.studentno,studentname FROM student s INNER JOIN result r ON s.`StudentNo`=r.`StudentNo` WHERE StudentResult>=80
-