1基础语句

  1. net start mysql --开启mysql
  3. net stop mysql --关闭mysql
  5. exit --退出链接
  7. mysql -uroot -p密码 --链接数据库
  9. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码'; --修改密码
  11. flush privileges; --刷新权限
  13. show databases; --查看所有数据库
  15. use mysql; --切换至mysql这一个数据库
  17. creat dadabase 数据库名 --创建一个数据库
  19. show tables; --查看所有表
  21. describe 表名； --查看表

数据库语言

DDL：数据库定义语言

DML：数据库操作语言

DQL：数据库查询语言

DCL：数据库控制语言

2.操作数据库

操作数据库

  1.   CREATE DATABASE [IF NOT EXISTS] test；--创建数据库，if not exists是判断是否存在，可以不加
  3.   DROP DATABASE [IF EXISTS] test; --删除数据库

数据库的列数据类型

1.数值

  • tinyint 非常小的数据(1个字节)

  • smallint 比较小的数据(2个字节)

  • mediumint 中间小的数据(3个字节)

  • int 标准的整形(4个字节) 常用int

  • bigint 较大的数据(8个字节)

  • float 单精度浮点数 (4个字节) 常用float

  • double 双精度浮点数(8个字节)

  • decimal 字符串形式的浮点数(金融计算是精确计算)

2.字符串

  • char 字符串固定大小的(0-255)
  • varchar 可变字符串(0-65535) 常用 String
  • tinytext 微型文本 2**8-1
  • text 文本串 2**16-1

3.时间，日期

  • date YYYY-MM-DD,日期格式
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp(时间戳) 1970.1.1到现在的毫秒数,也常用
  • year年份表示

4.null

  • 没有值

数据库的字段属性（重点）

  • Unsigned

    • 无符号的整数
    • 表明该项数据不能声明为负数

  • zerofill

    • 0填充
    • int（3）: 5->005

  • Auto incr （自增）

    • 自动在上一条记录基础上+1
    • 通常用来设计位移的主键~index ,必须是整数类型
    • 可以自定义初始值与步长

  • 非空Not NULL

    • 如果设置为not null就必须赋值

  • 默认(Defult)

    • 设置默认值

创建一个表

  1. USE `student`;
  2. CREATE TABLE IF NOT EXISTS `students`(
  3.     `id` INT(20)NOT NULL AUTO_INCREMENT COMMENT'学号',
  4.     `name`VARCHAR(10) NOT NULL DEFAULT'匿名' COMMENT'姓名',
  5.     `age`INT(100)NOT NULL COMMENT'年龄',
  6.     `password`VARCHAR(50)NOT NULL COMMENT'密码',
  7.     `sex`VARCHAR(2)NOT NULL DEFAULT'男' COMMENT'性别',
  8.     `birthday`DATETIME NOT NULL COMMENT'出生日期'
  9.     PRIMARY KEY(`id`)
  10. )ENGINE=INNODB DEFAULT CHARSET=utf8

格式

  1. use `数据库名`
  2. create table [if not exists]`表名`(
  3.     `字段`[列类型][属性][索引][注释]
  4.     `字段`[列类型][属性][索引][注释]
  5.     ......
  6.     `字段`[列类型][属性][索引][注释]
  7.     primary key(`字段`)--设置为主键
  8. )[表类型][字符集][注释]
  10. SHOW CREATE DATABASE school --查看创建数据库的语句
  11. SHOW CREATE TABLE students  --查看创建表的语句
  12. DESC students(等价于describe students)               --查看表的结构

两种引擎INNODB与MYISAM

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL也导入了InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。

MyISAM InnoDB
构成上的区别： 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
事务处理上方面: MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持 InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能
SELECT UPDATE,INSERT，Delete操作 如果执行大量的SELECT，MyISAM是更好的选择 1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表 2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用
对AUTO_INCREMENT的操作 每表一个AUTO_INCREMEN列的内部处理。 MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引 更好和更快的auto_increment处理 如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上 关于该计算器的算法实现，请参考 AUTO_INCREMENT列在InnoDB里如何工作
表的具体行数 select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的 InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
锁 表锁 提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%”

INNODB与MYISAM选择:

  • 一、InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
  • 二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
  • 三、InnoDB支持外键，MyISAM不支持
  • 四、MyISAM是默认引擎，InnoDB需要指定
  • 五、InnoDB不支持FULLTEXT类型的索引
  • 六、InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
  • 七、对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
  • 八、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
  • 九、InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’

表以及字段的修改删除

  1. /*修改表名*/
  2. ALTER TABLE students RENAME AS  student
  3. ALTER TABLE students2 RENAME AS student2 
  5. /*修改表的属性*/
  6. ALTER TABLE `school`.`student`  
  7. ENGINE=INNODB AUTO_INCREMENT=1 COMMENT='第一张学生表' ROW_FORMAT=DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 
  9. /*添加字段*/
  10. ALTER TABLE student ADD height FLOAT(8)NOT NULL COMMENT'身高'
  12. /*修改表的字段（1.change可以修改字段名）*/
  13. ALTER TABLE `school`.`student`     CHANGE `age` `agee` FLOAT NOT NULL COMMENT '年龄'
  15. /*修改表的字段（2.modify不可以修改字段名,只是修改约束）*/
  16. ALTER TABLE `school`.`student`     MODIFY `age` INT(100)
  18. /*删除字段*/
  19. ALTER TABLE `school`.`student` DROP `height`
  21. /*删除表*/
  22. DROP TABLE IF EXISTS `student`

所有的创建与删除尽量加上判断，以免出错

3.mySQL数据管理

DML语言

  • insert
  • update
  • delete

添加insert

  1. /*插入语句insert*/
  2. -- insert into 表名([字段1，字段2，字段3...])value('值1'，'值2','值3'...)
  3. INSERT INTO `student`(`name`,`age`,`password`)VALUES('肖云飞','18','123456789')
  4. /*如果不写表的字段就会默认一对一匹配*/
  5. INSERT INTO `student` VALUES('10','王五','30','123456789','男','2000-1-1') 
  6. /*可以一次插入多个值*/
  7. INSERT INTO `student`(`name`,`age`,`password`)VALUES('张三','20','123456789'),('李四','22','123456789')

修改update

  1. /*修改语句update*/
  2. -- update  表名 set 字段=值 where[判断条件]
  3. UPDATE `student` SET `age`='18' WHERE `id`='1'
  4. UPDATE `student` SET `id`='3' WHERE `id`='6'
  6. /*可以同时修改多个值(用“,"隔开)*/
  7. UPDATE `student` SET `id`=4,`age`='25' WHERE `id`='8'
  9. /*where后面实际上就是一个判断,
  10. 可以用基本的逻辑运算符=，!=(<>),<,>,<=,>=
  11. 此外可以用 between...and...（如where `id` BETWEEN 1 AND 3）控制到一个闭区间内
  12.            and使多个条件同时成立（where `name`='肖云飞' and `sex`='男'）
  13.            or使范围变大    (where `id`='1' or `id`='2')
  14. */
  15. UPDATE `student` SET `name`='肖云飞' WHERE `id` BETWEEN 1 AND 2
  16. UPDATE `student` SET `name`='张三' where`id`='2' AND `age`='20'

删除delete/truncate

  1. /*删除语句delete*/
  2. /*删除表项*/
  3. -- delete from 表名[where条件]
  4. DELETE FROM `school`.`student` WHERE `id`='10'
  6. DELETE FROM `teacher`  -- 实现删除所有表项，但不会影响自增(删除前主键自增到10,删除后再添加一项该项的主键值为11)
  8. /*删除语句truncate*/
  9. -- truncate table 表名(或直接truncate 表名)
  10. TRUNCATE `teacher`   -- 实现删除所有表项,同时自增归0(删除后再添加一项主键值一定是1)

相同点：两者都可以删除所有表项，但都不可以改变表的结构(要删除字段或表要用drop)

不同点：

  • truncate会重新设置自增列，计数器归0
  • truncate不会影响事务

4.DQL（数据库查询语言）

select完整语法

  1. select [all | DISTINCT]
  2. {* | table.* | table.filed1[as 别名1] [,table.filed2 [as 别名2] ] [......]}
  3. from table_name [as 表别名1]
  4.     [inner | left | right join table_name2 [as 表别名2] ]
  5.     [on ......]    -- 与join一同使用表的连接条件
  6.     [group by......]  -- 指定按照那几个字段类分组
  7.     [having ......]  -- 过滤分组的记录必须满足的次要条件
  8.     [order by ......] [asc | desc]    -- 排序
  9.     [limit 起始位置，查询数据数]

1.简单操作：

  1. /*查询语句select*/
  2. -- select 字段 from 表名            
  3. /*查询全部信息*/
  4. SELECT * FROM `student`
  5. /*查询指定字段*/
  6. SELECT `id`,`name` FROM `student`
  7. /*可以为查询的字段起一个别名*/
  8. SELECT `id` AS 学生编号,`name` AS 学生姓名 FROM `student`
  9. /*可以为查询的表其一个别名*/
  10. SELECT `id` AS 学生编号,`name` AS 学生姓名 FROM `student` AS 学生表
  11. /*简单函数的调用（concat）*/
  12. SELECT CONCAT('学生姓名：',`name`) AS 姓名介绍 FROM `student`
  14. -- select 字段1 [as 别名1]，字段2 [as 别名2]... from 表名 [as 别名]（添加别名时as甚至可以省略）

2.去重(dintinct):

  1. INSERT INTO `teacher`(`name`) VALUES ('A'),('A'),('A'),('B'),('B'),('B'),('B')
  2. SELECT `name` FROM `teacher`  -- 有很多重复的数据
  3. SELECT DISTINCT `name` FROM `teacher` -- 去掉重复的数据
  5. INSERT INTO `student`(`name`,`age`,`password`)VALUES('肖云飞','20','123456789'),
  6. ('肖云飞','30','123456789'),('肖云飞','40','123456789')
  8. SELECT DISTINCT `name` FROM `student`
  9. /*但是，当同时查询多个字段时，只有两个表项的查询字段都相同时才会去重*/
  10. SELECT DISTINCT `name` AS 姓名 ,`age` AS 年龄 FROM `student`

3.数据库的列(表达式)

  1. SELECT VERSION()-- 函数
  2. SELECT 100+100 AS 计算结果  -- 表达式
  3. SELECT @@auto_increment_increment -- 查询自增的步长(变量)
  5. SELECT `name` AS 学生姓名 , `age` AS 今年年龄 , `age`+1 AS 明年年龄  FROM `student`

4.where简单用法

  1. /*查询90-100的分数(where)*/
  2. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  3. WHERE studentresult >=90 AND studentresult <= 100;
  4. /*等价写法*/
  5. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  6. WHERE  studentresult BETWEEN 90 AND 100;
  8. /*查询编号不是1001的学生的成绩(where)*/
  9. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  10. WHERE studentno <> 1001;
  11. /*等价写法*/
  12. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  13. WHERE NOT studentno = 1001;

5.模糊查询

运算符 语法 描述
is NULL a is NULL 如果操作符为NULL则为真
is NOT NULL a is NOT NULL 如果操作符不为NULL则为真
BETWEEN a BETWEEN … ADN … 若a在b和c之间则为真
LIKE a LIKE b 若a与b匹配，则为真
IN a IN (a1,a2,a3….) 若a为a1,a2,a3其中任意一个值则为真 
  2. USE `school2`
  4. /*匹配LIKE*/
  5. -- SELECT 表达式 from 表名 where ...
  7. /*用_一个下划线表示之后只有一个字符*/
  8. SELECT * FROM `student` WHERE `studentname` LIKE '张_'
  9. SELECT * FROM `subject` WHERE `subjectname` LIKE '____1'
  11. /*用_下划线表示之后有任意多字符*/
  12. SELECT * FROM `student` WHERE `studentname` LIKE '张%'
  13. SELECT * FROM `subject` WHERE `subjectname` LIKE '%1'
  15. /*范围IN*/
  16. SELECT * FROM `subject` WHERE `classhour` IN (100,110,120)

6.连表查询

NUION

  1. /*UNION查询*/
  2. -- 当需要查询多张表的相同字段时使用(只用union的话会自动去重)
  3. SELECT `name` FROM `school`.student
  4. UNION 
  5. SELECT `name` FROM `school`.teacher;  
  7. -- 加上ALL就会查询所有的内容
  8. SELECT `name` FROM `school`.student
  9. UNION ALL
  10. SELECT `name` FROM `school`.teacher; 
  12. /*注意如果要为查询字段起别名，只会保留第一个*/
  13. SELECT `name` AS 学生姓名 FROM `school`.student
  14. UNION ALL
  15. SELECT `name` AS 教师姓名 FROM `school`.teacher; 
  17. -- 此外union也可以用于查询一张表
  18. SELECT `subjectno` ,`subjectname` FROM `school2`.`subject` WHERE `gradeid` IN ('1','2')
  19. UNION ALL
  20. SELECT `subjectno` ,`subjectname` FROM `school2`.`subject` WHERE `gradeid` IN ('3');

join

  1. /*JOIN*/
  2. -- 查询学号为1000的学生的所有信息与考试结果
  3. /*内连接inner join*/
  4. -- 内连接比较苛刻，必须要两张表的指定属性相同的行的数据才会被显示出来
  5. SELECT * FROM `student` 
  6. INNER JOIN `result` 
  7. ON `student`.studentno = `result`.studentno
  8. WHERE `student`.studentno = '1000';
  10. /*外连接1.lefe join*/
  11. -- 左表(第一个表)连接会把两张表的数据全部显示出来，其中左表完全显示，右表只有与左表匹配的行才会显示数据，否则为NULL
  12. SELECT * FROM `student` 
  13. LEFT JOIN `result` 
  14. ON `student`.studentno = `result`.studentno
  15. WHERE `student`.studentno = '1000';
  17. /*外连接2.right join*/
  18. -- 右表(第二个表)连接会把两张表的数据全部显示出来，其中右表完全显示，左表只有与右表匹配的行才会显示数据，否则为NULL
  19. SELECT * FROM `student` 
  20. RIGHT JOIN `result` 
  21. ON `student`.studentno = `result`.studentno
  22. WHERE `student`.studentno = '1000';
  24. /*交叉连接3.cross join*/
  25. -- 实际上就是把左表与右表的数据交叉组合，如果左表有6行数据，右表有7行数据，那交叉组合后就有42行数据
  26. SELECT * FROM `student` 
  27. CROSS JOIN `result`

7.自连接

  2. /*创建一张表，pid是学科大的分类序号(父id),categoryid是学科的分类序号,现在要把父类学科与子类对应，但只有一张表，所以就要用   到自连接，实际上就是把一张表在一个SELECT语句里面通过起两个别名使用两次*/
  4. CREATE TABLE `school2`.`category`( 
  5.     `categoryid` INT(3) NOT NULL COMMENT 'id', 
  6.     `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
  7.     `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
  8.     PRIMARY KEY (`categoryid`)
  9.  ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
  11. INSERT INTO `school2`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
  12. INSERT INTO `school2`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
  13. INSERT INTO `school2`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
  14. INSERT INTO `School2`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库'); 
  15. INSERT INTO `school2`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
  16. INSERT INTO `school2`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发'); 
  17. INSERT INTO `SCHool2`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');
  19. /*自连接（实际上就是把一张表拆为两张表）*/
  20. SELECT father.`categoryname` AS '父种类',child.`categoryname` AS '子种类' FROM 
  21. `school2`.category AS father ,`school2`.category AS child
  22. WHERE father.categoryid = child.pid;

9.排序order by

  1. /*排序order by (asc升序，desc降序)*/
  2. -- 下面语句表示优先按照studentresult降序排序，如果studentresult相同，再按照subjectno降序排列
  3. SELECT * FROM `school2`.result
  4. ORDER BY `studentresult` DESC , `subjectno` DESC;

10.分页

  1. /*分页limit(最后一个select子句)*/
  2. -- limit 页起始值，页面大小
  3. SELECT student.identitycard,student.studentname,grade.gradename,result.studentresult
  4. FROM student 
  5. INNER JOIN grade ON student.gradeid = grade.gradeid
  6. INNER JOIN result ON student.studentno = result.studentno
  7. ORDER BY student.studentno DESC 
  8. LIMIT 0,5
  10. -- limit 0,5  第一页，5条数据
  11. -- limit 5,5  第二页，5条数据
  12. -- limit 10,5 第三页，5条数据
  13. -- limit (n-1)*5,5 第n页，5条数据

11.子查询

子查询实际上就是在where子句中使用一个select

  1. /*查询数据库结构-1的考试结果（学号，科目编号，成绩）*/
  2. SELECT `student`.`studentNo`,`subject`.`gradeid`,`subjectname`,`subjectno`
  3. FROM `student` 
  4. INNER JOIN `subject` 
  5. ON `student`.gradeid = `subject`.gradeid
  6. WHERE subject.subjectname = '数据库结构-2'
  7. ORDER BY student.studentno DESC;
  9. /*还是上一道题，用子查询*/
  10. SELECT `result`.`studentno` , student.studentname, `subject`.subjectname , `result`.`subjectno` , result.`studentresult`
  11. FROM `result` 
  12. INNER JOIN `student`
  13. INNER JOIN `subject`
  14. WHERE result.studentNo = 
  15. (SELECT student.studentno FROM student WHERE student.gradeid = 2)

5.MySQL函数

1.聚合函数

聚合函数是平时比较常用的一类函数，这里列举如下：

  1. COUNT(col)   统计查询结果的行数
  3. MIN(col)   查询指定列的最小值
  5. MAX(col)   查询指定列的最大值
  7. SUM(col)   求和，返回指定列的总和
  9. AVG(col)   求平均值，返回指定列数据的平均值
  1. SELECT COUNT(studentresult),MAX(studentresult),MIN(studentresult),
  2. SUM(studentresult),AVG(studentresult) FROM `result`;

2.数值型函数

数值型函数主要是对数值型数据进行处理，得到我们想要的结果，常用的几个列举如下，具体使用方法大家可以试试看。

  1. ABS(x)   返回x的绝对值
  3. BIN(x)   返回x的二进制
  5. CEILING(x)   返回大于x的最小整数值
  7. EXP(x)   返回值e（自然对数的底）的x次方
  9. FLOOR(x)   返回小于x的最大整数值
  11. GREATEST(x1,x2,...,xn)   返回集合中最大的值
  13. LEAST(x1,x2,...,xn)   返回集合中最小的值
  15. LN(x)   返回x的自然对数
  17. LOG(x,y)   返回x的以y为底的对数
  19. MOD(x,y)   返回x/y的模（余数）
  21. PI()   返回pi的值（圆周率）
  23. RAND()   返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
  25. ROUND(x,y)   返回参数x的四舍五入的有y位小数的值
  27. TRUNCATE(x,y)   返回数字x截短为y位小数的结果

3.字符串函数

字符串函数可以对字符串类型数据进行处理，在程序应用中用处还是比较大的，同样这里列举几个常用的如下：

  1. LENGTH(s)   计算字符串长度函数，返回字符串的字节长度
  3. CONCAT(s1,s2...,sn)   合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
  5. INSERT(str,x,y,instr)   将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
  7. LOWER(str)   将字符串中的字母转换为小写
  9. UPPER(str)   将字符串中的字母转换为大写
  11. LEFT(str,x)   返回字符串str中最左边的x个字符
  13. RIGHT(str,x)   返回字符串str中最右边的x个字符
  15. TRIM(str)   删除字符串左右两侧的空格
  17. REPLACE   字符串替换函数，返回替换后的新字符串
  19. SUBSTRING   截取字符串，返回从指定位置开始的指定长度的字符换
  21. REVERSE(str)   返回颠倒字符串str的结果

4.日期和时间函数

  1. CURDATE 和 CURRENT_DATE   两个函数作用相同，返回当前系统的日期值
  3. CURTIME 和 CURRENT_TIME   两个函数作用相同，返回当前系统的时间值
  5. NOW 和 SYSDATE   两个函数作用相同，返回当前系统的日期和时间值
  7. UNIX_TIMESTAMP   获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
  9. FROMUNIXTIME   将 UNIX 时间戳转换为时间格式，与UNIXTIMESTAMP互为反函数
  11. MONTH   获取指定日期中的月份
  13. MONTHNAME   获取指定日期中的月份英文名称
  15. DAYNAME   获取指定曰期对应的星期几的英文名称
  17. DAYOFWEEK   获取指定日期对应的一周的索引位置值
  19. WEEK   获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
  21. DAYOFYEAR   获取指定曰期是一年中的第几天，返回值范围是1~366
  23. DAYOFMONTH   获取指定日期是一个月中是第几天，返回值范围是1~31
  25. YEAR   获取年份，返回值范围是 1970〜2069
  27. TIMETOSEC   将时间参数转换为秒数
  29. SECTOTIME   将秒数转换为时间，与TIMETOSEC 互为反函数
  31. DATE_ADD 和 ADDDATE   两个函数功能相同，都是向日期添加指定的时间间隔
  33. DATE_SUB 和 SUBDATE   两个函数功能相同，都是向日期减去指定的时间间隔
  35. ADDTIME   时间加法运算，在原始时间上添加指定的时间
  37. SUBTIME   时间减法运算，在原始时间上减去指定的时间
  39. DATEDIFF   获取两个日期之间间隔，返回参数 1 减去参数 2 的值
  41. DATE_FORMAT   格式化指定的日期，根据参数返回指定格式的值
  43. WEEKDAY   获取指定日期在一周内的对应的工作日索引

5.流程控制函数

流程控制类函数可以进行条件操作，用来实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台，列举如下：

  1. IF(test,t,f)   如果test是真，返回t；否则返回f
  3. IFNULL(arg1,arg2)   如果arg1不是空，返回arg1，否则返回arg2
  5. NULLIF(arg1,arg2)   如果arg1=arg2返回NULL；否则返回arg1
  7. CASE WHEN[test1] THEN [result1]...ELSE [default] END   如果testN是真，则返回resultN，否则返回default
  9. CASE [test] WHEN[val1] THEN [result]...ELSE [default]END   如果test和valN相等，则返回resultN，否则返回default

6.加密函数

加密函数主要用于对字符串进行加密，常用的几个列举如下：

  1. ENCRYPT(str,salt)   使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
  3. ENCODE(str,key)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
  5. MD5()   计算字符串str的MD5校验和
  7. PASSWORD(str)   返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
  9. SHA()   计算字符串str的安全散列算法(SHA)校验和

13.分组

  1. /*分组group by*/
  2. SELECT subjectno,COUNT(result.studentresult) FROM result GROUP BY subjectno
  4. /*having与group连用添加约束条件*/
  5. SELECT subject.gradeid,COUNT(subject.subjectname) FROM `subject` GROUP BY `subject`.gradeid
  6. HAVING COUNT(subject.subjectname) > 4

select学习程序清单

  2. CREATE DATABASE IF NOT EXISTS `school2`;
  3. -- 创建一个school数据库
  4. USE `school2`;-- 创建学生表
  5. DROP TABLE IF EXISTS `student`;
  6. CREATE TABLE `student`(
  7.     `studentno` INT(4) NOT NULL COMMENT '学号',
  8.     `loginpwd` VARCHAR(20) DEFAULT NULL,
  9.     `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
  10.     `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
  11.     `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
  12.     `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
  13.     `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
  14.     `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
  15.     `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
  16.     `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
  17.     PRIMARY KEY (`studentno`),
  18.     UNIQUE KEY `identitycard`(`identitycard`),
  19.     KEY `email` (`email`)
  20. )ENGINE=MYISAM DEFAULT CHARSET=utf8;
  22. -- 创建年级表
  23. DROP TABLE IF EXISTS `grade`;
  24. CREATE TABLE `grade`(
  25.     `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  26.   `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
  27.     PRIMARY KEY (`gradeid`)
  28. ) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
  30. -- 创建科目表
  31. DROP TABLE IF EXISTS `subject`;
  32. CREATE TABLE `subject`(
  33.     `subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  34.     `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
  35.     `classhour` INT(4) DEFAULT NULL COMMENT '学时',
  36.     `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
  37.     PRIMARY KEY (`subjectno`)
  38. )ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
  40. -- 创建成绩表
  41. DROP TABLE IF EXISTS `result`;
  42. CREATE TABLE `result`(
  43.     `studentno` INT(4) NOT NULL COMMENT '学号',
  44.     `subjectno` INT(4) NOT NULL COMMENT '课程编号',
  45.     `examdate` DATETIME NOT NULL COMMENT '考试日期',
  46.     `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
  47.     KEY `subjectno` (`subjectno`)
  48. )ENGINE = INNODB DEFAULT CHARSET = utf8;
  50. -- 插入学生数据 
  51. INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
  52. VALUES
  53. (1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
  54. (1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');
  56. -- 插入成绩数据 
  57. INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
  58. VALUES
  59. (1000,1,'2013-11-11 16:00:00',85),
  60. (1000,2,'2013-11-12 16:00:00',70),
  61. (1000,3,'2013-11-11 09:00:00',68),
  62. (1000,4,'2013-11-13 16:00:00',98),
  63. (1000,5,'2013-11-14 16:00:00',58),
  64. (1001,1,'2013-11-11 16:00:00',85),
  65. (1001,2,'2013-11-12 16:00:00',70),
  66. (1001,3,'2013-11-11 09:00:00',68),
  67. (1001,4,'2013-11-13 16:00:00',98),
  68. (1001,5,'2013-11-14 16:00:00',58);
  70. -- 插入年级数据
  71. INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
  73. -- 插入科目数据
  74. INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
  75. (1,'高等数学-1',110,1),
  76. (2,'高等数学-2',110,2),
  77. (3,'高等数学-3',100,3),
  78. (4,'高等数学-4',130,4),
  79. (5,'C语言-1',110,1),
  80. (6,'C语言-2',110,2),
  81. (7,'C语言-3',100,3),
  82. (8,'C语言-4',130,4),
  83. (9,'Java程序设计-1',110,1),
  84. (10,'Java程序设计-2',110,2),
  85. (11,'Java程序设计-3',100,3),
  86. (12,'Java程序设计-4',130,4),
  87. (13,'数据库结构-1',110,1),
  88. (14,'数据库结构-2',110,2),
  89. (15,'数据库结构-3',100,3),
  90. (16,'数据库结构-4',130,4),
  91. (17,'C#基础',130,1);
  93. /*查询90-100的分数(where)*/
  94. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  95. WHERE studentresult >=90 AND studentresult <= 100;
  96. /*等价写法*/
  97. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  98. WHERE  studentresult BETWEEN 90 AND 100;
  100. /*查询编号不是1001的学生的成绩(where)*/
  101. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  102. WHERE studentno <> 1001;
  103. /*等价写法*/
  104. SELECT `studentno` AS 学生编号,`studentresult` AS 考试成绩 FROM `result`
  105. WHERE NOT studentno = 1001;
  108. USE `school2`
  110. /*匹配LIKE*/
  111. -- SELECT 表达式 from 表名 where ...
  113. /*用_一个下划线表示之后只有一个字符*/
  114. SELECT * FROM `student` WHERE `studentname` LIKE '张_'
  115. SELECT * FROM `subject` WHERE `subjectname` LIKE '____1'
  117. /*用_下划线表示之后有任意多字符*/
  118. SELECT * FROM `student` WHERE `studentname` LIKE '张%'
  119. SELECT * FROM `subject` WHERE `subjectname` LIKE '%1'
  121. /*范围IN*/
  122. SELECT * FROM `subject` WHERE `classhour` IN (100,110,120)
  124. /*UNION查询*/
  125. -- 当需要查询多张表的相同字段时使用(只用union的话会自动去重)
  126. SELECT `name` FROM `school`.student
  127. UNION 
  128. SELECT `name` FROM `school`.teacher;  
  130. -- 加上ALL就会查询所有的内容
  131. SELECT `name` FROM `school`.student
  132. UNION ALL
  133. SELECT `name` FROM `school`.teacher; 
  135. /*注意如果要为查询字段起别名，只会保留第一个*/
  136. SELECT `name` AS 学生姓名 FROM `school`.student
  137. UNION ALL
  138. SELECT `name` AS 教师姓名 FROM `school`.teacher; 
  140. -- 此外union也可以用于查询一张表
  141. SELECT `subjectno` ,`subjectname` FROM `school2`.`subject` WHERE `gradeid` IN ('1','2')
  142. UNION ALL
  143. SELECT `subjectno` ,`subjectname` FROM `school2`.`subject` WHERE `gradeid` IN ('3');
  145. /*JOIN*/
  146. -- 查询学号为1000的学生的所有信息与考试结果
  147. /*内连接inner join*/
  148. -- 内连接比较苛刻，必须要两张表的指定属性相同的行的数据才会被显示出来
  149. SELECT * FROM `student` 
  150. INNER JOIN `result` 
  151. ON `student`.studentno = `result`.studentno
  152. WHERE `student`.studentno = '1000';
  154. /*外连接1.lefe join*/
  155. -- 左表(第一个表)连接会把两张表的数据全部显示出来，其中左表完全显示，右表只有与左表匹配的行才会显示数据，否则为NULL
  156. SELECT * FROM `student` 
  157. LEFT JOIN `result` 
  158. ON `student`.studentno = `result`.studentno
  159. WHERE `student`.studentno = '1000';
  161. /*外连接2.right join*/
  162. -- 右表(第二个表)连接会把两张表的数据全部显示出来，其中右表完全显示，左表只有与右表匹配的行才会显示数据，否则为NULL
  163. SELECT * FROM `student` 
  164. RIGHT JOIN `result` 
  165. ON `student`.studentno = `result`.studentno
  166. WHERE `student`.studentno = '1000';
  168. /*交叉连接3.cross join*/
  169. -- 实际上就是把左表与右表的数据交叉组合，如果左表有6行数据，右表有7行数据，那交叉组合后就有42行数据
  170. SELECT * FROM `student` 
  171. CROSS JOIN `result`
  174. CREATE TABLE `school2`.`category`( 
  175.     `categoryid` INT(3) NOT NULL COMMENT 'id', 
  176.     `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
  177.     `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
  178.     PRIMARY KEY (`categoryid`)
  179.  ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 
  181. INSERT INTO `school2`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
  182. INSERT INTO `school2`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
  183. INSERT INTO `school2`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
  184. INSERT INTO `School2`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库'); 
  185. INSERT INTO `school2`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
  186. INSERT INTO `school2`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发'); 
  187. INSERT INTO `SCHool2`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');
  189. /*自连接（实际上就是把一张表拆为两张表）*/
  190. SELECT father.`categoryname` AS '父种类',child.`categoryname` AS '子种类' FROM 
  191. `school2`.category AS father ,`school2`.category AS child
  192. WHERE father.categoryid = child.pid;
  196. -- 查询学员所属年级（学号，姓名，年级）
  197. SELECT `student`.`identitycard`,`student`.`studentname`,`grade`.`gradename`
  198. FROM `student` INNER JOIN `grade`
  199. ON `student`.gradeid = `grade`.gradeid;
  201. /*排序order by (asc升序，desc降序)*/
  202. -- 下面语句表示优先按照studentresult降序排序，如果studentresult相同，再按照subjectno降序排列
  203. SELECT * FROM `school2`.result
  204. ORDER BY `studentresult` DESC , `subjectno` DESC;
  207. /*分页limit(最后一个select子句)*/
  208. -- limit 页起始值，页面大小
  209. SELECT student.identitycard,student.studentname,grade.gradename,result.studentresult
  210. FROM student 
  211. INNER JOIN grade ON student.gradeid = grade.gradeid
  212. INNER JOIN result ON student.studentno = result.studentno
  213. ORDER BY student.studentno DESC 
  214. LIMIT 0,5
  216. -- limit 0,5  第一页，5条数据
  217. -- limit 5,5  第二页，5条数据
  218. -- limit 10,5 第三页，5条数据
  219. -- limit (n-1)*5,5 第n页，5条数据
  222. /*查询数据库结构-1的考试结果（学号，科目编号，成绩）*/
  223. SELECT `student`.`studentNo`,`subject`.`gradeid`,`subjectname`,`subjectno`
  224. FROM `student` 
  225. INNER JOIN `subject` 
  226. ON `student`.gradeid = `subject`.gradeid
  227. WHERE subject.subjectname = '数据库结构-2'
  228. ORDER BY student.studentno DESC;
  230. /*还是上一道题，用子查询*/
  231. SELECT `result`.`studentno` , student.studentname, `subject`.subjectname , `result`.`subjectno` , result.`studentresult`
  232. FROM `result` 
  233. INNER JOIN `student`
  234. INNER JOIN `subject`
  235. WHERE result.studentNo = 
  236. (SELECT student.studentno FROM student WHERE student.gradeid = 2)
  239. SELECT COUNT(studentresult),MAX(studentresult),MIN(studentresult),
  240. SUM(studentresult),AVG(studentresult) FROM `result`;
  242. /*分组group by*/
  243. SELECT subjectno,COUNT(result.studentresult) FROM result GROUP BY subjectno
  245. /*having与group连用添加约束条件*/
  246. SELECT subject.gradeid,COUNT(subject.subjectname) FROM `subject` GROUP BY `subject`.gradeid
  247. HAVING COUNT(subject.subjectname) > 4

6.事务

本质就是把要执行的sql语句放在一个批次执行，要不都成功，要不都不成功

重点：事务四大原则(ACID：原子性atomicity,一致性consistence,隔离性isolation,持久性durability)

  1. --  一、原子性（atomicity)
  3. 一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性
  5. --  二、一致性（consistency)
  7. 事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
  9. 如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态
  11. --  三、隔离性（isolation）
  13. 事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。
  15. 在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化
  17. 1、读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取
  19. 2、授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。
  21. 3、可重复读（Repeatable Read)
  23. 就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20；
  25. 4、串行化
  27. 是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。
  29. --  四、持久性（durability）
  31. 一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。--即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态
  2. CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci
  4. USE `shop`
  6. /*创建一张表模拟转账*/
  7. CREATE TABLE `person`(
  8.     `id` INT(5) NOT NULL AUTO_INCREMENT ,
  9.     `name`VARCHAR(10) NOT NULL,
  10.     `money`DECIMAL(10,3) NOT NULL ,
  11.     PRIMARY KEY(`id`)
  12. )ENGINE = INNODB  DEFAULT CHARSET = utf8
  14. /*插入三条数据*/
  15. INSERT INTO `person`(`name`,`money`)
  16. VALUES ('张三',10000.000),('李四',20000.000),('王五',30000.00)
  18. /*mysql默认开启自动提交（只要运行一条就会吧结果提交到数据库），
  19. 而由于事务要求的ACID原则，必须关闭自动提交*/
  20. -- 有两种方法关闭自动提交 1.set autocommit = 0;
  21. --                        2.start transaction ; 开启事务默认关闭自动提交
  23. START TRANSACTION ;    -- 开启事务（同时关闭自动提交）
  25. UPDATE `person` SET `money` = `money`-5000.000 WHERE `name` = '李四'  -- 李四减少5000元
  26. UPDATE `persin` SET `money` = `money`+5000.000 WHERE `name` = '王五'  -- 王五收到5000元
  27. UPDATE `person` SET `money` = `money`-3000.000 WHERE `name` = '王五'  -- 王五减少3000元
  28. UPDATE `person` SET `money` = `money`+3000.000 WHERE `name` = '张三'  -- 张三收到3000元
  29. /*刚刚运行完的sql语句由于关闭了自动提交，只是展示修改了数据，可以回滚*/
  31. COMMIT;   -- 如果事务全部准确完成，就提交
  33. ROLLBACK; -- 如果事务有问题，就回滚，数据库回到提交之前的状态
  35. SET autocommit = 1;  -- 事务提交完成后必须将自动提交改回去

7.索引

索引实际上就是帮助MySQL高效获取数据的数据结构

1.索引分类

  • 主键索引(primary key):唯一的标识，只有一列可以用
  • 唯一索引(unicon key):避免重复的列出现，多个列都可以 表示
  • 常规索引(key | index):默认的
  • 全文索引(fulltext):特定的数据库引擎下才有(比如myisam),可以快速定位数据

2.索引原则

  • 索引不是越多越好
  • 经常变动的数据不加索引
  • 小数据量的表不需要索引
  • 索引一般加在经常查询的字段上

8.权限管理与备份

权限管理实际上就是不同用户对数据库的操作权限

用户的信息实际上是存储在mysql这个数据库里的user表中的信息,所以添加，删除用户，修改用户权限都是对这张表的操作

1.创建用户

create user ‘{用户名}’@’{主机地址}’ identified with {身份验证插件类型} by ‘{密码}’;

  1.  CREATE USER IF NOT EXISTS 'employees'@'%'
  2.  IDENTIFIED  WITH mysql_native_password BY 'Study@2019'
  3.  WITH    MAX_QUERIES_PER_HOUR 500    MAX_UPDATES_PER_HOUR 100;
  5. 用户名：employees
  6. 可以从任意主机访问，也可以限制对IP范围的访问，例如10.148.%.%
  7. 密码： Study@2019
  8. 使用mysql_native_password身份验证插件，还可以指定任何可选的身份验证，例如sha256_password、LDAP或Kerberos
  9. 用户可以在一小时内执行的最大查询数为500
  10. 用户可以在一小时内执行的最大更新次数为100

二、授予和撤销用户的访问权限

grant {权限类型} on {databases}.{table} to ‘{用户名}’@’{主机地址}’;（赋予权限）

revoke{权限类型} on {databases}.{table} to’{用户名}’@’{主机地址}’;（删除权限）

三.修改用户密码

  1. CREATE USER `xiaoyunfei` IDENTIFIED BY 'xiaoyunfei'     -- 创建用户
  2. ALTER USER `xiaoyunfei`@`%` IDENTIFIED BY '123456'      -- 修改密码

四.删除用户

  1. DROP USER `xiaoyunfei`@`%`                              -- 删除用户

注意，哪怕是授予了所有权限的用户也没有ruut的权限高，因为只有root用户才有grant（授予）权限

五。查看用户权限

  1. SHOW GRANTS FOR root@localhost     -- 查看权限

六。数据库备份（导出）与导入

  1. /*命令行执行*/
  3. mysqldump -hlocalhost -uroot -p123456 school student > D:/sql备份.sql
  5. /*
  6. mysqldump -h主机 -u用户 -p密码 数据库 表 > 文件位置 /文件名.sql
  7. */
  9. /*导入（必须先登录）*/
  10. source sql备份.sql

9.数据库设计三大范式

第一范式（1NF）

数据库的表的每一列的必须是不可再分的

第二范式（2NF）

在第一范式的基础上，数据库的表中的每一列都必须与主键相关，即每张表值描述一件事

第三范式（3NF）

在第二范式的基础上，数据库的表的每一列都必须与主键直接相关，不能是间接相关

但是，如果完全遵照范式，性能会降低(表会很多),

因此，要在优先考虑性能的基础上参考范式

10.IDBC

第一个程序

  1. ackage com.xiao.JDBC;
  3. import java.sql.*;
  5. /**
  6.  * JDBC学习1
  7.  */
  8. public class JDBCLearn1 {
  9.     public static void main(String[] args) throws ClassNotFoundException, SQLException {
  10.         //1.加载驱动
  11.         Class.forName("com.mysql.cj.jdbc.Driver");
  13.         //2.加载用户信息与URL
  14.         String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
  15.         /**
  16.         在高版本的mysql中需要指明是否要SSL连接
  17.         serverTimeZone服务时区一般有UTC(Coordinated Universal Time世界协调时间),比北京时间早8个小时，就导致时间出现偏差，所以可以申明为Asia/Shanghai保证时间没有问题.
  18.         */
  19.         String user = "root";
  20.         String pwd = "123456";
  22.         //3.连接成功后获取数据库对象(connection就代表数据库)
  23.         Connection connection = DriverManager.getConnection(url,user,pwd);
  26.         //4.创建一个用于执行sql指令的对象
  27.         Statement statement = connection.createStatement();
  29.         //5.执行sql的对象statement要执行的sql语句
  30.         String sql = "select * from jdbcstudy.users";
  32.         //6.执行sql语句
  33.         ResultSet resultSet = statement.executeQuery(sql);/*这是一条查询指令，查询的结果会议链表的形式封装在一个结果集中*/
  35.         while (resultSet.next()){
  36.             System.out.println("id = "+resultSet.getObject("id"));
  37.             System.out.println("name = "+resultSet.getObject("NAME"));
  38.             System.out.println("email = "+resultSet.getObject("email"));
  39.             System.out.println("birthday = "+resultSet.getObject("birthday"));
  40.             System.out.println("=============================================================");
  41.         }
  43.         //7.释放连接
  44.         resultSet.close();
  45.         statement.close();
  46.         connection.close();
  47.     }
  48. }
  50. /*结果*/
  51. id = 1
  52. name = zhansan
  53. email = zs@sina.com
  54. birthday = 1980-12-04
  55. =============================================================
  56. id = 2
  57. name = lisi
  58. email = lisi@sina.com
  59. birthday = 1981-12-04
  60. =============================================================
  61. id = 3
  62. name = wangwu
  63. email = wangwu@sina.com
  64. birthday = 1979-12-04
  65. =============================================================

将配置包装

首先需要一个配置文件(命名为db.properties)

  1. /**
  2. 这是文件中的配置信息
  3. */
  5. driverName=com.mysql.cj.jdbc.Driver
  6. url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
  7. user=root
  8. pwd=123456

之后，需要一个工具类获取该文件中的资源(命名为JDBCLoader)

  2. /**
  3.  * 这是一个工具类JDBCLoader
  4.  * loadConnection方法获取数据库对象connection
  5.  * getStatement方法获取一个用于调用sql语句的对象
  6.  * release方法释放资源
  7.  */
  10. import java.io.IOException;
  11. import java.io.InputStream;
  12. import java.sql.*;
  13. import java.util.Properties;
  15. public class JDBCLoad {
  16.     private static final InputStream in = JDBCLoad.class.getClassLoader().getResourceAsStream("db.properties");
  17.     private static final Properties properties = new Properties();
  19.     /*获取数据库对象*/
  20.     static Connection loadConnection(){
  21.         Connection connection = null;
  22.         try {
  23.             properties.load(in);
  24.             String driverName = properties.getProperty("driverName");
  25.             String url = properties.getProperty("url");
  26.             String user = properties.getProperty("user");
  27.             String pwd = properties.getProperty("pwd");
  28.             Class.forName(driverName);
  29.             connection = DriverManager.getConnection(url,user,pwd);
  30.         } catch (IOException e) {
  31.             e.printStackTrace();
  32.             System.out.println("获取配置失败");
  33.         } catch (SQLException e) {
  34.             e.printStackTrace();
  35.             System.out.println("获取连接失败");
  36.         } catch (ClassNotFoundException e) {
  37.             e.printStackTrace();
  38.             System.out.println("加载驱动失败");
  39.         }
  40.         return connection;
  41.     }
  44.     /*获取执行sql的对象*/
  45.     static Statement getStatement(Connection connection){
  46.         Statement statement = null;
  47.         try {
  48.             statement = connection.createStatement();
  49.         } catch (SQLException e) {
  50.             e.printStackTrace();
  51.             System.out.println("获取失败");
  52.         }
  53.         return statement;
  54.     }
  57.     /*释放资源*/
  58.     static void release(Connection connection,Statement statement,ResultSet resultSet){
  59.         if (resultSet != null){
  60.             try {
  61.                 resultSet.close();
  62.             } catch (SQLException e) {
  63.                 e.printStackTrace();
  64.             }
  65.         }
  66.         if (statement != null){
  67.             try {
  68.                 statement.close();
  69.             } catch (SQLException e) {
  70.                 e.printStackTrace();
  71.             }
  72.         }
  73.         if (connection != null){
  74.             try {
  75.                 connection.close();
  76.             } catch (SQLException e) {
  77.                 e.printStackTrace();
  78.             }
  79.         }
  80.     }
  83.     /*释放资源（没有结果集）*/
  84.     static void releaseWithoutResultSet(Connection connection,Statement statement){
  85.         if (statement != null){
  86.             try {
  87.                 statement.close();
  88.             } catch (SQLException e) {
  89.                 e.printStackTrace();
  90.             }
  91.         }
  92.         if (connection  !=  null){
  93.             try {
  94.                 connection.close();
  95.             } catch (SQLException e) {
  96.                 e.printStackTrace();
  97.             }
  98.         }
  99.     }
  100. }

接下来用工具类去完成上面的代码

  1. package com.xiao.JDBC;
  4. import java.sql.Connection;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  9. /**
  10.  * 用工具类获取数据库
  11.  */
  12. public class JDBCLearn2 {
  13.     public static void main(String[] args) throws SQLException {
  14.         Connection connection = JDBCLoad.loadConnection();
  15.         Statement statement = JDBCLoad.getStatement(connection);
  16.         String sql = "select * from jdbcstudy.users";
  17.         ResultSet resultSet = statement.executeQuery(sql);
  18.         while (resultSet.next()) {
  19.             System.out.println("id =  " + resultSet.getObject("id"));
  20.             System.out.println("name =  " + resultSet.getObject("NAME"));
  21.             System.out.println("email =  " + resultSet.getObject("email"));
  22.             System.out.println("birthday =  " + resultSet.getObject("birthday"));
  23.             System.out.println("==============================================================");
  24.         }
  25.         JDBCLoad.release(connection,statement,resultSet);
  26.     }
  27. }

sql注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

  1. package com.xiao.JDBC;
  3. import java.sql.Connection;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  8. /*sql注入*/
  9. public class Learn3 {
  10.     public static void main(String[] args) {
  12.         //login("zhansan","123456");    //理想的情况
  13.         login("'or'1=1","123456");      //这样的查询或返回所有的信息
  14.         login("'or'1=1","'or'1=1");     //甚至这样的查询或返回所有的信息
  16.     }
  18.         public static void login(String username,String password){
  19.         Connection connection = JDBCLoad.loadConnection();
  20.         Statement statement = JDBCLoad.getStatement(connection);
  21.         String sql = "SELECT * FROM `jdbcstudy`.`users` WHERE  `NAME`='"+username+"'" +"AND"+" `password`="+"'"+password+"'";
  22.         /**
  23.         通过字符串的拼接完成的sql语句
  24.         */
  25.         String sql1 = "SELECT * FROM `jdbcstudy`.`users` WHERE  `NAME`='zhansan' AND `password`='123456'";
  26.         String sql2 = "SELECT * FROM `jdbcstudy`.`users` ";
  27.         /**
  28.         不通过字符串的拼接完成的sql语句
  29.         */
  30.             try {
  31.                 ResultSet resultSet = statement.executeQuery(sql);
  32.                 while (resultSet.next()) {
  33.                     String name =(String) resultSet.getObject("NAME");
  34.                     String pwd = (String) resultSet.getObject("password");
  35.                     System.out.println(name);
  36.                     System.out.println(pwd);
  37.                     System.out.println("=================================================");
  38.                 }
  39.             } catch (SQLException e) {
  40.                 e.printStackTrace();
  41.             }finally {
  42.                 JDBCLoad.release(connection,statement,resultSet);
  43.             }
  44.         }

preparedStatement对象

这个对象的作用与Statement相同，只是可以一定程度上解决SQL注入，并且比Statement效率更高

  1. package com.xiao.JDBC;
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.SQLException;
  6. import java.util.Date;
  8. /**
  9.  * prepareStatement对象测试
  10.  */
  11. public class Learn4 {
  12.     public static void main(String[] args) {
  13.         Connection connection = JDBCLoad.loadConnection();
  14.         PreparedStatement preparedStatement = null;
  16.         //区别，PreparedStatement是预处理，用？占位后手动赋值
  17.         /**
  18.          * 如果用Statement就只能这么写
  19.          */
  20.         String oldSQL = "INSERT INTO `jdbcstudy`.users (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) " +
  21.                 "VALUES ('4','zhaoliu',123456,'zhaoliu@sina.com','1982-12-6')";
  22.         /**
  23.          * 用PreparedStatement就可以用？先占位
  24.          */
  25.         String newSQL = "INSERT INTO `jdbcstudy`.users (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) " +
  26.                 "VALUES (?,?,?,?,?)";
  27.         try {
  28.             preparedStatement = connection.prepareStatement(newSQL);
  29.             //在赋值时索引是从1开始的
  30.             //手动给参数赋值
  31.             preparedStatement.setObject(1,4);
  32.             preparedStatement.setObject(2,"zhaoliu");
  33.             preparedStatement.setObject(3,123456);
  34.             preparedStatement.setObject(4,"zhaoliu@sina.com");
  35.             preparedStatement.setObject(5,new java.sql.Date(new Date().getTime()));
  37.             int lines = preparedStatement.executeUpdate();
  38.             if (lines > 0){
  39.                 System.out.println("添加成功");
  40.             }
  41.         } catch (SQLException e) {
  42.             e.printStackTrace();
  43.         }finally {
  44.             JDBCLoad.releaseWithoutResultSet(connection,preparedStatement);
  45.         }
  47.     }
  48. }

用preparedStatement尝试之前的SQL注入

  1. package com.xiao.JDBC;
  4. import java.sql.Connection;
  5. import java.sql.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  9. /**
  10.  * 再次尝试之前的SQL注入
  11.  */
  12. public class Learn5 {
  13.     public static void main(String[] args) {
  15.         login("zhansan","123456");          //只有这一条可以查出来
  16.         login("'or'1=1","123456");          //无查询结果
  17.         login("'or'1=1","'or'1=1");         //无查询结果
  18.     }
  20.     public static  void login(String username,String password){
  21.         Connection connection = null;
  22.         PreparedStatement preparedStatement = null;
  23.         ResultSet resultSet = null;
  25.         try {
  26.             connection = JDBCLoad.loadConnection();
  27.             String sql =  "SELECT * FROM `jdbcstudy`.`users` WHERE  `NAME`= ? AND `password`= ? ";
  28.             preparedStatement = connection.prepareStatement(sql);
  30.             preparedStatement.setObject(1,username);
  31.             preparedStatement.setObject(2,password);
  33.             resultSet = preparedStatement.executeQuery();
  35.             while (resultSet.next()){
  36.                 String name =(String) resultSet.getObject("NAME");
  37.                 String pwd = (String) resultSet.getObject("password");
  38.                 System.out.println(name);
  39.                 System.out.println(pwd);
  40.                 System.out.println("=================================================");
  41.             }
  42.         } catch (SQLException e) {
  43.             e.printStackTrace();
  44.         }finally {
  45.             JDBCLoad.release(connection,preparedStatement,resultSet);
  46.         }
  47.     }
  48. }

java中完成事务

  1. package com.xiao.JDBC;
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.SQLException;
  7. /**
  8.  * 事务
  9.  */
  10. public class Learn6 {
  11.     public static void main(String[] args) {
  12.         Connection connection = JDBCLoad.loadConnection();
  13.         PreparedStatement preparedStatement = null;
  16.         try {
  17.             //关闭数据库自动提交，自动开启事务
  18.             connection.setAutoCommit(false);
  20.             //由于连接的是jdbcstudy,换数据库
  21.             String sql0 = "USE `shop`";
  22.             preparedStatement = connection.prepareStatement(sql0);
  23.             preparedStatement.executeUpdate();
  25.             String sql1 = "UPDATE `person` SET `money` = `money`-5000.000 WHERE `name` = '李四'";  //李四减少5000元
  26.             preparedStatement = connection.prepareStatement(sql1);
  27.             preparedStatement.executeUpdate();
  29.             String sql2 = "UPDATE `person` SET `money` = `money`+5000.000 WHERE `name` = '王五'";  //王五收到5000元
  30.             preparedStatement = connection.prepareStatement(sql2);
  31.             preparedStatement.executeUpdate();
  33.             String sql3 = "UPDATE `person` SET `money` = `money`-3000.000 WHERE `name` = '王五'";  //王五减少3000元
  34.             preparedStatement = connection.prepareStatement(sql3);
  35.             preparedStatement.executeUpdate();
  37.             String sql4 = "UPDATE `person` SET `money` = `money`+3000.000 WHERE `name` = '张三'";  // 张三收到3000元
  38.             preparedStatement = connection.prepareStatement(sql4);
  39.             preparedStatement.executeUpdate();
  41.             //事务执行完成，提交
  42.             connection.commit();
  43.             System.out.println("交易成功");
  45.         } catch (SQLException e) {
  46.             e.printStackTrace();
  47.             try {
  48.                 connection.rollback();
  49.             } catch (SQLException e1) {
  50.                 e1.printStackTrace();
  51.                 System.out.println("交易失败");
  52.             }finally {
  53.                 //释放资源
  54.                 JDBCLoad.releaseWithoutResultSet(connection,preparedStatement);
  55.             }
  56.         }
  57.     }
  58. }
  60. 结果：交易成功