MySQL 增删改查----14



DB（database）：数据库
DBS（database  system）：数据库系统
DBMS(database  manager  system)：数据库管理系统

数据库的种类及特点
1、Oracle（安全性高，处理速度快，产品免费，服务收费） 
2、SQL Server（微软的数据库,针对不同用户群体的多个版本，易用性好） 
3、db2（IBM公司）：关系型数据库（传统类型）
4、MySql （开放源代码，网站应用广泛）

数据库能做什么：
1、存储大量数据，方便检索和访问
2、保持数据信息的一致，完整
3、共享和安全
4、通过组合分析，产生新的有用信息

MySQL:服务器
MySQL 地址是localhost或者是127.0.0.1   端口号默认3306
MySQL优势：运行速度快，使用成本低，可移植性强，适用用户广
支持接口：标准C的API,JDBC,CDBC,NET,PHP,Python,Perl,Ruby,Cobol
字符集是“utf8”gbk
连接池：验证与授权—线程—连接权限—内存与缓存管理
DOS命令：
net start mysql    #命令连接mysql
语法：mysql -h服务器本机地址 -u 用户名 -p 密码

###当mysql出现不是内部和外部命令时。
原因：不存在系统路径下，命令没有配环境变量。
配环境变量：计算机右键属性，高级系统设置，环境变量，path，将mysql路径加进去（路径后以分号结束）。

SQL(structured query language)：是用于访问和处理数据库的标准的计算机语言。
SQLyog：客户端
SQL可以做：
1、SQL 面向数据库执行查询
2、SQL 可从数据库取回数据
3、SQL 可在数据库中插入新的记录
4、SQL 可更新数据库中的数据
5、SQL 可从数据库删除记录
6、SQL 可创建新数据库
7、SQL 可在数据库中创建新表
8、SQL 可在数据库中创建存储过程
9、SQL 可在数据库中创建视图
10、SQL 可以设置表、存储过程和视图的权限

结构化查询语言：
DML（数据操作语言）：用来操作数据库中所包含的数据 insert （插入） update （修改） delete （删除）
DDL（数据定义语言）：用于创建删除数据库对象等操作 create（创建）  drop（删除）  alter（修改）
DQL(数据查询语言）：用来对数据库中的数据进行查询  select（查询）
DCL（数据控制语言）：用来控制数据库组件的存取许可，存取权限等。  grant（授权）  commit（提交）  rollback（回滚）

运算符：
1、+ 2、 - 3、 *  6、=（赋值）
4、 / 除运算，求两个数或表达式相除的商，如5/3的值为1
5、%取模运算，求两个数或表达式相除的余数，如：5%3的值为2
逻辑运算符：
1、AND 当且仅当两个布尔表达式都为true时，返回TRUE。
2、OR   当且仅当两个布尔表达式都为false，返回FALSE。
3、NOT 布尔表达式的值取反
比较运算符：（<>和！= 不等于）

语法：
1、DESC 表名；（看表的结构）
2、SELECT * FROM 名；（查看）
3、ALTEB TABLE 表名； （变更数据库）
4、CREATE DATABASE 数据库名；（创建数据库）
5、DROP DATABASE 数据库名； （删除数据库）
6、SHOW databases; （查看数据库列表）
7、SHOW TABLE；（看表）
8、USE 数据库名； （选择数据库）
9、CREATE TABLE 表名（）； （创建表）
10、DROP TABLE 表名；（删除表）
11、DROP TABLE IF EXISTS 表名；（在删表之前使用if exists语句验证表是否存在。）
12、ALTER TABLE 旧表名 RENAME 新表名；（修改表名）
13、ALTER TABLE 表名 ADD 字段名  数据类型  属性；（添加字段）
14、ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 属性；（修改字段）
15、ALTER TABLE 表名 DROP 字段名；（删除字段）
16、ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名（主键字段）； （添加主键）
17、ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY（外键字段）REFERENCES 关联表名（关联字段）；（添加外键）
18、HELP 查询类容；
19、DELETE FROM 表名 WHERE条件；（删除数据记录）
20、TRUNCATE TABLE 表名；（删除数据记录）  （TT删除记录将重置自曾列执行速度比DF快）
21、SELECT 列名  FROM 表名  WHERE条件  ORDER BY 排序列名 【ASC或DESC】；（查询语法）
        ORDER BY 子句实现按一定顺序显示查询结果
22、AS  （命名）
23、CREATE TABLE 新表（SELECT 字段1，字段2  FROM 原表）；  （将查询结果插入新表）
24、UPDATE 表名 SET 字段1=值1，字段2=值2  WHERE 条件；（数据更新）
25、INSERT INTO 表名（字段名列表） VALUES （值列表1）……（值列表N）；（插入多条数据语录）
注意：
1、字段名是可选的，如省略则一次插入所有字段；
2、多个列表和多个列表之间使用逗号分隔；
3、值列表和字段名列表一一对应；
4、如插入的是表中部分数据，字段名列表必填。
26、LIMT 位置偏移量，行数；第一条记录位置是1
27、SELECT ……FROM 表1 WHERE 字段1 比较运算符（子查询）
     子查询是一个嵌套在SELECT \ INSERT \ UPDATE或DELETE语句或其他子查询中的查询
      将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个
28、IN/NOT IN   常用IN 替换（=）的子查询；IN后面的子查询可以返回多条记录
**分了组在写条件，就不能写WHERE 而是HAVING
单行注释：#    多行注释：/*...*/
1、行：记录  
2、列：字段
***当所命名为关键字时，需要加上撇号，才可识别。

聚合函数：
1、AVG()  返回某字段平均值
2、COUNT()  返回某字段的行数
3、MAX()   返回某字段的最大值
4、MIN()   返回某字段的最小值
5、SUM()  返回某字段的和

时间日期函数：
1、CURDATE() 获取当前日期
2、CURTIME() 获取当前时间
3、NOW()  获取当前日期和时间
4、WEEK(date)  返回日期date为一年中的第几周
5、YEAR(date)   返回日期date的年份
6、HOUR(time)  返回时间time的小时值
7、MINUTE(time) 返回时间time的分钟值
8、DATEDIFF(date1,date2)   返回日期参数date1和date2之间相隔的天数
9、ADDDATE(date,n)   计算日期参数date加上n天后的日期

数学函数：
CEIL(x)  返回大于或等于数值x的最小整数
FLOOR(x)  返回小于或等于数值x的最大整数
RAND()  返回0~1间的随机数

字符串函数：
1、CONTCAT    字符串连接     SELECT  CONCAT(str1,str2)
2、INSERT        字符串替换     SELECT INSERT(str，pos,len,newstr)
3、LOWER(str)  转换小写
4、UPPER（str）转换大写
5、SUB STRING   字符串截取   SELECT SUB STRING（str，num，len）

数值类型：
1、TINYINT（非常小的数据 1字节）
2、SMALLINT（较小的数据 2字节）
3、MEDIUMINT（中等大小的数据   3字节）
4、INT （标准整数  4字节）
5、BIGINT （较大的整数  8字节）
6、FLOAT   （单精度浮点数   4字节）
7、DOUBLE  （双精度浮点数   8字节）
8、DECIMAL  （字符串形式的浮点数   M+2个字节）
9、UNSIGNED   无符号数
10、ZEROFILL    宽度（位数）不足以0填充

字符串类型：
1、CHAR 定长字符串
2、VARCHAR 可变字符串
3、TINYTEXT 微型文本串
4、TEXT 文本串

日期类型：
1、DATE 
2、DATETIME
3、TIME
4、TIMESTAMP
5、YEAR

字段的约束及属性：
1、NOT NULL   非空约束（字段不允许为空）
2、DEFAULT     默认约束（赋予某字段默认值）
3、UNIQUE KEY 唯一约束 （设置字段值是唯一的，允许为空，但只能有一个空值）
4、PRIMARY KEY 主键约束（设置该字段为表的主键，可唯一标识该表记录）
5、FOREIGN KEY 外键约束（用于在两表之间建立关系，需要指定引用主表的那一字段）
6、AUTO_INCREMENT  自动增长（默认自增1，通常用于设置主键）

在数据库里当存在数据冗余（数据重复），而且数据量较大时，很占内存，所以就出现了外键（外间引用主键的数据）
主键的特点：不允许重复，不允许为空，没有要求数据类型。
***减少数据冗余后增加了查找数据的复杂性，从而降低了效率，因此，数据库中允许数据有一定的冗余。

存储引擎的常见类型
1、MyISAM（查询）：不需事物，空间小，已查询为主
.frm（表结构定义文件） .MYD （数据文件）.MYI（索引文件）
2、InnoDB （增删改 ）多删除，更新操作，安全性高，事物处理及并发控制
.frm（表结构定义文件） ibdata1文件

查看当前默认引擎：
SHOW  VARIABLES LIKE'storage_engine%';
修改存储引擎，修改my.ini配置文件；创建表时，可在结尾处指定引擎。

在数据库中，第一行报错，下面代码继续执行，与Python相反。
外键所在的表一定是从表  添加外键 修改的是从表
如果表中汉字出现乱码，不能识别：
在表后加上  DEFAULT  CHARSET utf8；

#创建学生表
CREATE TABLE student1(
 studentNo INT(4) PRIMARY KEY NOT NULL COMMENT'学号',
 loginpwd VARCHAR(20) NOT NULL COMMENT'密码',
 studentName VARCHAR(50) NOT NULL COMMENT'姓名',
 sex CHAR(2) NOT NULL 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) UNIQUE KEY COMMENT'身份证号'
 
)DEFAULT CHARSET utf8;

#创建年级表
CREATE TABLE grade(
 gradeID INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT'年级编号',
 gradeName VARCHAR(20) NOT NULL COMMENT'年级名'
)DEFAULT CHARSET utf8;

#创建外键 学生表和年级表
ALTER TABLE student1
ADD CONSTRAINT fk_gradeId FOREIGN KEY(gradeid)
REFERENCES grade(gradeID)

#创建科目表
CREATE TABLE `subject`(
 `subjectNo` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT'课程编号',
 subjectName VARCHAR(50) COMMENT'课程名称',
 classHour INT(4) COMMENT'学时',
 gradeID INT(4) COMMENT'年级编号'
)DEFAULT CHARSET utf8;

#创建成绩表
CREATE TABLE result(
 studentNo INT(4) NOT NULL COMMENT'学号',
 subjectNo INT(4) NOT NULL COMMENT'课程编号',
 examDate DATETIME NOT NULL COMMENT'考试日期',
 studentResult INT(4) NOT NULL COMMENT'考试成绩'
)DEFAULT CHARSET utf8;

#创建外键  科目表和年级表
ALTER TABLE `subject`
ADD CONSTRAINT fk_gradeId1 FOREIGN KEY(gradeID)
REFERENCES grade(gradeID);

#创建外键 成绩表和学生表
ALTER TABLE result
ADD CONSTRAINT fk_result_studentno FOREIGN KEY(studentno)
REFERENCES student1(studentNo);

#创建外键 成绩表和科目表
ALTER TABLE result
ADD CONSTRAINT fk_result_subject FOREIGN KEY(subjectno)
REFERENCES `subject`(subjectno);

#result表主键约束：学号\课程编号\日期构成组合组件
ALTER TABLE result
ADD CONSTRAINT pk_stuno_subno_examDate
PRIMARY KEY(studentno,subjectno,examdate);

#插入数据
INSERT INTO grade(gradeName) VALUES('c++');
INSERT INTO `subject` VALUES(NULL,'python基础',200,1),
(NULL,'html',150,1);

#更改学生姓名毕智傲的email为1101@qq.com
UPDATE student1 SET email='1101@qq.com' WHERE studentName='毕智傲';
SELECT * FROM student1;

#为科目表添加数据，要求使用INSERT语句
INSERT INTO `subject` VALUES (NULL,'java',188,7);
SELECT *FROM `subject`;

#查询所在年级在2年级的学生
SELECT studentName AS '学生姓名' FROM student1
WHERE gradeID=2 ORDER BY borndate ASC;

#修改段名
ALTER TABLE student1 CHANGE brondate bor ndate DATETIME;
SELECT * FROM student1;

#常量列
SELECT studentNo AS '学号', studentName AS '姓名','武昌职业' AS 学校
FROM student1;

#利用聚合函数查询成绩
SELECT AVG(studentResult) AS '平均成绩',MAX(studentResult) AS '最高成绩',
MIN(studentResult) AS '最低成绩',SUM(studentResult) AS '总和',
COUNT(studentResult) AS '记录' FROM result;

#时间日期函数：
SELECT NOW();#获取当前日期和时间
#利用数学函数组合随机生成数字
SELECT CEIL (RAND()*12+1);

#将所有男生按年龄大小排序，第二条记录开始显示6名学生的姓名、年龄
SELECT studentName,FLOOR(DATEDIFF(NOW(),borndate)/365)
FROM student1 WHERE sex='男'
ORDER BY borndate DESC LIMIT 1,6;

#按出生年份分组统计学生人数，将各组中人数达到2人的年份和人数显示出来
SELECT COUNT(*),borndate FROM student1 GROUP BY YEAR(borndate)
HAVING COUNT(*)>1;#分了组在写条件，就不能写WHERE 而是HAVING

#查询参加2018、01、01考试的所有学员的最高最低平均分
SELECT MAX(studentResult) AS '最高',MIN(studentResult) AS '最低',AVG(studentResult) AS'平均'
FROM result WHERE examdate='2018-01-01';

#利用子查询查出比杨文博小的同学
SELECT studentName FROM student1 WHERE borndate>
(SELECT borndate FROM student1 WHERE studentName='杨文博');

#查询最近一次参加HTML考试成绩的为60分的同学
SELECT studentname FROM student1 WHERE studentno IN
(SELECT studentno FROM result WHERE studentresult=60 AND
subjectno=(SELECT subjectno FROM SUBJECT WHERE subjectname='html'));

#查询参加HTML课程最近一次考试在读学生名单
SELECT studentno,studentname FROM student1
WHERE studentno IN
(SELECT studentno FROM result WHERE examdate=
(SELECT MAX(examdate) FROM result WHERE subjectno=
(SELECT subjectno FROM SUBJECT WHERE subjectname='html'))
AND subjectno=(SELECT subjectno FROM SUBJECT WHERE subjectname='html'));
 
#查询s1学期开设的课程
SELECT subjectname FROM `subject` WHERE
gradeid=(SELECT gradeid FROM grade WHERE gradename='s1');

#查询没有参加HTML考试的同学
SELECT studentname FROM student1 WHERE studentno IN
(SELECT studentno FROM result WHERE subjectno=
(SELECT subjectno FROM SUBJECT WHERE subjectname='html')
AND examdate=(SELECT MAX(examdate) FROM result WHERE subjectno=
(SELECT subjectno FROM SUBJECT WHERE subjectname='html')))
AND gradeid=(SELECT subjectno FROM SUBJECT WHERE subjectname='html');