ALTER TABLE student1 MODIFY phone VARCHAR(11)

删除表的字段：ALTER TABLE DROP 字段名

• ALTER TABLE student1 DROP phone1

删除表：DROP TABLE (if exists) student1

所有的创建和删除操作尽量加上判断是否存在（if exists），以免报错。

数据管理

外键

外键（了解即可）：1.在创建表的时候增加约束

CREATE TABLE `grade`(`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT \'年级ID\',`gradename` VARCHAR(10) NOT null COMMENT \'年级名称\',primary KEY(`gradeid`))ENGINE INNODB DEFAULT CHARSET=utf8------------------------------------------------------------------------------------------------------------CREATE TABLE `student` (`id` int(4) NOT NULL AUTO_INCREMENT COMMENT \'学号\',`name` varchar(20) NOT NULL DEFAULT \'匿名\' COMMENT \'姓名\',`pwd` varchar(20) NOT NULL DEFAULT \'123456\' COMMENT \'密码\',`sex` varchar(2) NOT NULL DEFAULT \'女\' COMMENT \'性别\',`birthday` datetime DEFAULT NULL COMMENT \'出生日期\',`gradeid` INT(10) NOT NULL COMMENT\'学生年级\',`addresws` varchar(50) DEFAULT NULL COMMENT \'地址\',`email` varchar(30) DEFAULT NULL COMMENT \'邮箱\',PRIMARY KEY (`id`),KEY `FK_gradeid` (`gradeid`),CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须先删除引用别人的表（从表），再删除被引用的表（主表）。

2.修改表添加外键约束

• ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)

• ALTER TABLE

  student

  ADD CONSTRAINT

  FK_gradeid

  FOREIGN KEY(

  gradeid

  ) REFERENCES

  grade

  (

  gradeid

  );

以上都是物理外键，数据库级别的外键，不建议使用。（避免数据库过多造成困扰）

最佳办法：数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）。如果想使用多张表，用程序去实现（不用外键）

DML语言

DML语言

DML语言：数据操作语言。

添加

语法格式：INSERT INTO 表名（字段1，字段2，字段3…）VALUES (\’值1\’,\’值2\’,\’值3\’…),(\’值1\’,\’值2\’,\’值3\’…),(\’值1\’,\’值2\’,\’值3\’…)

--添加 一定要保证数据和字段一一对应！！！！INSERT INTO 表名（字段1，字段2，字段3...）VALUES (\'值1\',\'值2\',\'值3\'...)--由于主键自增可以省略（如果不写表的字段，它就会一一匹配）INSERT INTO `grade`(`gradename`) VALUES (\'大一\')--插入多条数据INSERT INTO `grade`(`gradename`) VALUES (\'大二\'),(\'大三\'),(\'大四\')INSERT INTO `students`(`name`) VALUES (\'张三\')INSERT INTO `students`(`name`,`pwd`,`sex`)VALUES (\'张三\',\'bbbbb\',\'男\'),(\'李四\',\'bbbbb\',\'男\'),(\'王五\',\'bbbbb\',\'男\')--省略字段INSERT INTO `students` values (\'5\',\'麻子\',\'wwww\',\'男\',\'2010-01-01\',\'武汉\',\'email\')

注意：

• 字段和字段之间用英文逗号隔开
• 字段是可以省略的，但是插入的值必须一一对应
• 可以同时插入多条数据，每一条数据用（）,隔开

修改

语法格式：update 表名 set 字段=value , 字段=value where 字段 = ？

--修改学生的名字UPDATE `students` SET `name`=\'周\' WHERE id = 1--不指定条件会改动表中所有的名字UPDATE `students` SET `name`=\'周\'--修改多个条件UPDATE `students` SET `name`=\'李\',`pwd`=\'666666\' WHERE id =2--通过多个条件定位数据UPDATE `students` SET `name`=\'王\'  WHERE id=4 AND sex=\'男\'--变量UPDATE `students` SET `birthday`=CURRENT_DATE  WHERE id=4 AND sex=\'男\'

条件：where子句， id等于某个值 大于某个值 在某个区间

注意：

• 字段是数据库的列，尽量带上“
• where后面是筛选的条件，如果没有指定则会修改所有的列
• value是一个具体的值也可以是变量（比如时间，日期）
• 多个设置属性之间用英文的逗号隔开

删除

语法格式：delete from 表名 where 条件

--删除数据DELETE FROM `students` WHERE id=7--删除所有数据（避免）  表的自增列不会重置DELETE FROM `students`

TRUNCATE：完全清空一个数据库表的数据，表的结构和索引约束不变。

TRUNCATE TABLE 表名；清空表后表的自增列也会重置，计数器归0，而且不会影响事物。

CREATE TABLE `test`(`id` INT(4) NOT NULL AUTO_INCREMENT,`coll` VARCHAR(8) NOT NULL,PRIMARY KEY (`id`))ENGINE INNODB DEFAULT CHARSET=utf8INSERT INTO `test`(`coll`) VALUES(\'1\'),(\'2\'),(\'3\')--不影响自增DELETE FROM `test`--自增归0TRUNCATE TABLE `test`

了解：delete删除的时候，重启数据库

• innoDB 自增列会从1开始（存在内存中的 断电即失）
• MYISAM 继续从上一个自增两开始（存在文件中的 不会消失）

DQL查询数据（重点）

data query language：数据查询语言

• 所有的查询操作都用它 select
• 简单的查询，复杂的查询都能做
• 数据库最核心的语言

查询

查询语法：SELECT 字段 FROM 表

--查询全部的学生   SELECT 字段 FROM 表SELECT * FROM student1--查询指定字段SELECT `StudentNo`,`StudentName` FROM student1--给列起别名（也可以给表起别名）SELECT `StudentNo` AS \'学号\',`StudentName` AS \'姓名\' FROM student1--函数 Concat(a,b)SELECT CONCAT(\'姓名：\',StudentName) AS 新名字 FROM student1

去重

去重：distinct（去除查询结果中重复的数据）

--查询哪些同学参加了考试 有成绩SELECT * FROM `result` --查询全部考试的成绩SELECT `studentno` FROM `result` --查询哪些同学参加了考试--发现重复数据 去重SELECT DISTINCT `studentno` FROM `result`

数据库的列（表达式）

--查询系统的版本（函数）SELECT VERSION()--计算结果SELECT 100*3-5 AS --计算结果--查询自增的步长（变量）SELECT @@AUTO_INCREMENT_INCREMENT--学生的成绩 +1SELECT `studentno`,`studentresult`+1 AS \'提1分\' FROM `result`

where条件子句

作用：检索数据中符合符合条件的值

SELECT `studentno`,`studentresult` FROM `result`--查询成绩在80-100中间的  and   &&SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=80 AND `studentresult` <=100SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=80 && `studentresult` <=100--模糊查询（区间）SELECT `studentno`,`studentresult` FROM `result` WHERE studentresult BETWEEN 80 AND 100--查询学号不为1000的学生成绩   !=   notSELECT `studentno`,`studentresult` FROM `result` WHERE studentno != 1000SELECT `studentno`,`studentresult` FROM `result` WHERE NOT studentno = 1000

模糊查询

模糊查询：比较运算符

==========================================模糊查询======================================================--查询姓张的同学--like结合 %（代表0到任意个字符） _（一个字符）SELECT `studentno`,`studentname` FROM `student1` WHERE studentname LIKE \'张%\'--查询姓赵的同学 姓后面有一个字的SELECT `studentno`,`studentname` FROM `student1` WHERE studentname LIKE \'赵_\'--查询名字中带有强字的   %强%SELECT `studentno`,`studentname` FROM `student1` WHERE studentname LIKE \'%强%\'=========in=============--查询1001,1000学生信息SELECT `studentno`,`studentname` FROM `student1` WHERE studentno in (1000,1001)--查询在北京的学生SELECT `studentno`,`studentname` FROM `student1` WHERE address IN (\'北京\')===========null  not null===============--查询生日为空的学生SELECT `studentno`,`studentname` FROM `student1` WHERE borndate IS NULL OR borndate=\'\'address--查询地址不为空的学生SELECT `studentno`,`studentname` FROM `student1` WHERE address IS NOT NULL

连表查询

join：

==================================连表查询join===============================================--查询参加了考试的同学（学号 姓名 科目编号 分数）SELECT r.studentno,`studentname`,`subjectno`,`studentresult`FROM `result` AS rINNER JOIN `student1` AS sWHERE r.studentno = s.studentno-- INNER JOIN语句中用ON和where都可以--where在left和right语句中不能使用--join on 连接查询--where 等值查询=====RIGHT JOIN=======SELECT s.studentno,`studentname`,`subjectno`,`studentresult`FROM `student1` sRIGHT JOIN `result` rON s.studentno=r.studentno=====LEFT JOIN=======SELECT s.studentno,`studentname`,`subjectno`,`studentresult`FROM `student1` sLEFT JOIN `result` rON s.studentno=r.studentno--查询缺考的同学SELECT s.studentno,`studentname`,`subjectno`,`studentresult`FROM `student1` sLEFT JOIN `result` rON s.studentno=r.studentnoWHERE studentresult IS NULL--查询参加考试的同学的信息（学号，姓名，科目名，分数）SELECT r.studentno,`studentname`,`subjectname`,`studentresult`FROM `student1` sRIGHT JOIN `result` rON r.studentno=s.studentnoINNER JOIN `subject` subON r.subjectno=sub.subjectno--查询学生所属的年级(学号，学生姓名，年级名称)SELECT s.studentno,studentname,gradenameFROM student1 sINNER JOIN `grade` gON s.gradeid=g.gradeid--查询科目所属的年级（科目名称，年级名称）SELECT subjectname, gradenameFROM `grade` gINNER JOIN `subject` sON g.gradeid=s.gradeid--查询参加了C语言-1考试的同学（学号 姓名 科目 分数）SELECT s.studentno,studentname,subjectname,studentresultFROM student1 sINNER JOIN `result` rON s.studentno=r.studentnoINNER JOIN `subject` subon r.subjectno=sub.subjectnowhere subjectname=\'C语言-1\'

自连接（了解）

自连接：把一张表拆成两张一样的表

--查询父子信息  把一张表分为两个一样的表SELECT a.`categoryName`AS \'父栏目\',b.`categoryName` AS 子栏目FROM `category` AS a,`category` AS bWHERE a.`categoryid`=b.`pid`

分页和排序

排序：

====================分页 limit  排序 order by=====================================--排序： 升序asc 降序desc--根据成绩排序--LIMIT 0,5  1-5  从第几个开始一页显示几个--limit 1,5  2-6SELECT r.studentno,`studentname`,`subjectname`,`studentresult`FROM `student1` sRIGHT JOIN `result` rON r.studentno=s.studentnoINNER JOIN `subject` subON r.subjectno=sub.subjectnoORDER BY studentresult DESCLIMIT 0,5--第一页	limit 0,5--第二页	limit	5,5--第三页	limit	10,5--第n页		limit (n-1)*5,5--[pagesize 页面大小]--[(n-1)*pagesize 起始值]--[n  当前页]--[数据总数/页面大小=总页数]--分页  缓解数据库压力 体验更好    不分页的叫瀑布流--查询C语言排名前三并且分数大于80的学生信息（学号，姓名，课程名，分数）SELECT s.studentno,`studentname`,`subjectname`,`studentresult`FROM student1 sINNER JOIN result rON s.studentno=r.studentnoINNER JOIN `subject` subON r.subjectno=sub.subjectnoWHERE subjectname LIKE \'C语言%\' AND studentresult>80ORDER BY studentresult descLIMIT 0,3

语法：limit 查询起始下标 pagesize

子查询

where (计算出来的值)；本质是在where后面嵌套一个查询语句。

=============================where===========================================--查询C语言-1的所有的考试结果（学号，科目名，科目编号，成绩）降序排列--方式一，使用连接查询SELECT studentno,subjectname,r.subjectno,studentresultFROM result rINNER JOIN `subject` subon r.subjectno=sub.subjectnoWHERE subjectname=\'C语言-1\'ORDER BY studentresult DESC--查询C语言-1的所有的考试结果（学号，科目编号，成绩）降序排列--方式二，使用子查询（由里及外）SELECT studentno,subjectno,studentresultFROM resultWHERE subjectno = (SELECT subjectnoFROM `subject`WHERE subjectname=\'C语言-1\')ORDER BY studentresult desc--查询C语言-1的学生学号SELECT subjectno FROM `subject` WHERE subjectname=\'C语言-1\'--分数不小于80分的学生的学号和姓名SELECT DISTINCT s.studentno,studentnameFROM student1 sINNER JOIN result rON s.studentno=r.studentnoWHERE studentresult >=80--在这个基础上增加一个科目 高等数学-2--查询科目为 高等数学-2且分数不小于8分的学生的学号和姓名SELECT s.studentno,studentnameFROM student1 sINNER JOIN result rON s.studentno=r.studentnoINNER JOIN `subject` subON r.subjectno=sub.subjectnoWHERE studentresult >=80 AND subjectname = \'高等数学-2\'ORDER BY  studentresult DESC--子查询SELECT DISTINCT s.studentno,studentnameFROM student1 sINNER JOIN result rON s.studentno=r.studentnoWHERE studentresult >=80 AND subjectno = (SELECT subjectno FROM `subject` WHERE subjectname = \'高等数学-2\')--查询高等数学-2的编号SELECT subjectno FROM `subject` WHERE subjectname = \'高等数学-2\'--再改造SELECT studentno,studentname FROM student1WHERE studentno IN (SELECT studentno FROM result WHERE studentresult>=80AND subjectno = (SELECT subjectno FROM `subject` where subjectname = \'高等数学-2\'))--查询C语言-1 的同学的信息（学号，姓名，分数）SELECT s.studentno,studentname,studentresultFROM student1 sINNER JOIN result ron s.studentno=r.studentnoWHERE subjectno = (SELECT subjectno FROM `subject` WHERE subjectname = \'C语言-1\' )

MySQL函数

常用函数

=========================常用函数======================================--数学运算SELECT ABS(-8)		--绝对值SELECT CEILING(9.4)		--向上取整SELECT FLOOR(9.4)		--向下取整SELECT RAND()		--随机数SELECT SIGN(2)		--判断一个数的符号 负数返回-1 正数返回1--字符串函数SELECT CHARACTER_LENGTH(\'中国\')		--字符串长度SELECT CONCAT(\'我\',\'是\')		--拼接字符串SELECT INSERT(\'我爱helloword\',1,2,\'超级\')	--查询 替换 从某个位置替换某个长度SELECT LOWER(\'asdsAAAAadwa\')	--转小写SELECT UPPER(\'aaFDSFSDdxd\')	--转大写SELECT INSTR(\'kuang\',\'u\')	--返回第一次出现字符的索引SELECT REPLACE(\'坚持才能成功\',\'坚持\',\'努力\')		--替换字符串SELECT SUBSTR(\'坚持才能成功\',4,6)	--截取字符串SELECT REVERSE(\'坚持才能成功\')	--反转字符串--时间和日期函数SELECT CURRENT_DATE() --	获取当前日期SELECT CURDATE()  --	获取当前日期SELECT NOW() 	--获取当前时间SELECT LOCALTIME() 	--本地时间SELECT SYSDATE()	--系统时间SELECT YEAR(NOW())SELECT MONTH(NOW())SELECT DAY(NOW())SELECT HOUR(NOW())SELECT MINUTE(NOW())SELECT SECOND(NOW())--系统SELECT SYSTEM_USER()	--当前用户SELECT USER()	--当前用户SELECT VERSION()	--版本

聚合函数（常用）

===============聚合函数================--都能够统计表中的数据SELECT COUNT(studentname) FROM `student1`	--指定列  会忽略所有的null值SELECT COUNT(*) FROM `student1`	--不会忽略null值SELECT COUNT(1) FROM `student1`	--不会忽略null值SELECT SUM(`studentresult`) AS 总分 FROM resultSELECT AVG(`studentresult`) AS 平均分 FROM resultSELECT MAX(`studentresult`) AS 最高分 FROM resultSELECT MIN(`studentresult`) AS 最低分 FROM result--查询不同课程的最高分，最低分，平均分--核心	不同的课程分组SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult),MIN(studentresult)FROM result rINNER JOIN `subject` subON r.subjectno=sub.subjectnoGROUP BY r.subjectno	#通过什么分组HAVING 平均分>=80

select语法：

--顺序很重要SELECT 去重 要查询的字段 FROM 表 （表和字段可以起别名）***JOIN 要连接的表 ON 等值判断WHERE (具体的值，子查询语句)GROUP BY (通过哪个字段分组)HAVING (过滤分组后的信息，条件和where一样的，位置不同)ORDER BY (通过哪个字段排序 ASC/DESC)LIMIT startindex pagesize（分页）

数据库级别的MD5加密

主要增强算法的复杂度和不可逆性。

MD5不可逆，具体值加密后是一样的。

MD5破解网站的原理，后台有一个字典，存储加密前的值和加密后的值。

========================测试MD5加密==========================CREATE TABLE `testmd5`(`id` INT(4) NOT NULL,`name` VARCHAR(20) NOT NULL,`pwd` VARCHAR(100) NOT NULL,PRIMARY KEY(`id`))ENGINE INNODB DEFAULT CHARSET=utf8--明文密码INSERT INTO `testmd5` VALUES(1,\'zhangsan\',\'123456\'),(2,\'lisi\',\'123456\'),(3,\'wangwu\',\'123456\')--加密UPDATE  testmd5 SET pwd=MD5(pwd) WHERE id=1--插入的时候加密INSERT INTO `testmd5` VALUES(4,\'xiaoming\',MD5(\'123456\'))--如何校验 将用户传进来的密码加密 然后与数据库存储的比较SELECT * FROM testmd5 WHERE `name` = \'xiaoming\' AND pwd=MD5(\'123456\')

事务

什么是事物：要幺都成功，要么都失败

事物原则：ACID原则—>> 原子性，一致性，隔离性，持久性 （脏读，幻读…..）

• 原子性（Atomicity）：要么都成功，要么都失败
• 一致性（Consistency）：事务前后的数据完整性要保持一致
• 持久性（Durability）：事物一旦提交就不可逆，被持久化到数据库中
• 隔离性（Isolation）：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不会被其他事务的操作干扰

隔离产生的一些问题：

• 脏读：指一个事务读取了另一个事务未提交的数据
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（不一定是错误，只是某些场合不对）
• 虚读（幻读）：指在一个事务内读取到了别的事物插入的数据，导致前后读取不一致

===============事物=======================MySQL是默认开启事务自动提交的SET AUTOCOMMIT = 0	/*关闭自动提交*/SET AUTOCOMMIT = 1	/*开启自动提交（默认）*/-手动处理事务SET AUTOCOMMIT = 0	--关闭自动提交--事务开启START TRANSACTION	--标记一个事务的开始 从这个开始的sql都在一个事务内--提交，持久化（成功）COMMIT--回滚，回到原来的样子（失败）ROLLBACK--事务结束SET AUTOCOMMIT = 1	--开启事务提交--了解SAVEPOINT	--设置事务的一个保存点名ROLLBACK SAVEPOINT	--回滚到保存点RELEASE SAVEPOINT	--撤销保存点

模拟转账事务

--转账CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ciUSE shopCREATE TABLE `account` (`id` INT(4) NOT NULL AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL,`money`DECIMAL(8,2) NOT NULL,PRIMARY KEY (`id`))ENGINE INNODB DEFAULT CHARSET=utf8INSERT INTO `count`(`name`,`money`) VALUES(\'a\',2000.00),(\'b\',10000.00)--模拟转账  事务 一行一行执行SET autocommit = 0;START TRANSACTION;UPDATE account SET money=money-500 WHERE `name` = \'a\';UPDATE account SET money=money+500 WHERE `name` = \'b\';COMMIT;ROLLBACK;SET autocommit = 1;