MySQL（上）

1、数据库分类：

1.1、关系型数据库（SQL）

• MySQL，Oracle，SQL Server，DB2
• 通过表和表之间，行和列之间的关系进行数据存储

1.2、非关系型数据库（NoSQL）

• Redis,，MongDB
• 以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对。

2、SQLyog

这里只为了记录细节

创建数据库的固定样式。

创建表的固定样式

Unsigned：不能为负数。
Zerofill：使用0填充，例如int(3)，5填充为005

一般来说每个表都存在以下字段：

id				主键
'version'		乐观锁
is_delete		伪删除
gmt_create		创建时间
gmt_update		修改时间

3、命令行操作数据库

net start mysql				--启动服务
mysql -uroot -p123456		--连接数据库
show databases				--查看数据库
use 数据库名称				--更换数据库
show tables					--查看表
describe 表名称			  --显示表的所有信息

4、SQL操作数据库

CREATE DATABASE 数据库名称		--创建数据库
DROP DATABASE 数据库名称			--删除数据库
USE 数据库名称					--使用数据库
SHOW DATABASES					--查看所有数据库

5、SQL操作表

5.1、创建表

CREATE TABLE `表名称` (
`字段名称` 数据类型 NOT NULL（非空？） AUTO_INCREMENT（自增？） COMMENT（注释） '注释内容',
`sex` VARCHAR(4) NOT NULL DEFAULT（默认） '默认为',
PRIMARY KEY(`id`)		--主键
)ENGINE=INNODB（搜索引擎） DEFAULT CHARSET=utf8（默认编码）		--这行基本都是固定的

CREATE TABLE `teacher` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '教师编号',
`name` VARCHAR(10) NOT NULL COMMENT '教师名称',
`sex` VARCHAR(4) NOT NULL DEFAULT '男',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

5.2、修改表和字段

--修改表名	
ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
--增加字段
ALTER TABLE 表名 ADD 字段名 字段类型
ALTER TABLE teacher1 ADD age INT(4)
--修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新字段类型
ALTER TABLE teacher1 MODIFY age VARCHAR(4)
--修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名
ALTER TABLE teacher1 CHANGE age age1
--删除字段
ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
--删除表（如果表存在）
DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS teacher1
--IF EXISTS	可以用在很多操作中，用于判断是否存在

6、DML

6.1、外键

一般要使用多张表由程序实现，不建议使用外键

ALTER TABLE `表名` 
ADD CONSTRAINT `FK_外键名称` FOREIGN KEY(`外键名称`) REFERENCES `外键连接的表`(`连接的表字段名称`)
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`geadeid`) REFERENCES `grade`(`gradeid`)

6.2、条件子句操作符

常见的省略

操作符	含义	实例	结果
<>或!=	不等于	5<>6	false
BETWEEN…AND…	范围	BETWEEN 2 AND 5	[2,5]
AND 或 &&	和	5>1 AND 1>2	false
OR或 ||	或	5>1 OR 1>2	true
NOT或 !	取反，非	NOT 1	0
IS NULL	是否为空	‘NULL’ IS NULL	0
IS NOT NULL	是否不为空	NOT NULL IS NOT NULL	1
LIKE	匹配	‘abcd’ LIKE ‘_b%’	1
IN	匹配

备注：

• LIKE有两个匹配符，%代表存在多个字符，_代表存在一个字符。

• IN关键字的实例：

  IN关键字通常可以用于一条语句查询多个数据。
  IN是精确查找，不能使用%或_。
  SELECT gradeid FROM grade WHERE gradeid IN(1,2,3)	//查询在grade表中gradeid字段分别为1，2，3的数据
  

6.3、添加

INSERT INTO `表名`(`字段1`,`字段2`) VALUE ('值1','值2'),('值3','值4')
自增和有默认值的字段可以不写
--插入多个对象
INSERT INTO `teacher`(`name`) VALUE ('a'),('b')

--插入单个对象多个字段
INSERT INTO `teacher`(`name`,`sex`) VALUE ('c','女')

--插入单个对象多个字段
INSERT INTO `teacher`(`name`,`sex`) VALUE ('a','女'),('b','男')

6.4、修改

UPDATE `表名` SET `字段1`='值1',`字段2`='值2' WHERE 条件

UPDATE `student` SET `name`='王五' WHERE id = 1
UPDATE `student` SET `name`='张三',`age`='20' WHERE id = 1

6.5、删除

DELETE FROM `表名` WHERE 条件
DELETE FROM `student` WHERE id=1

delete清空表后，重启数据库不同引擎自增量会不一样

• InnoDB 自增列会从1开始，因为存在内存中，断电即失
• MyISAM 继续冲上一个自增量开始，存在文件中，不会丢失

6.6、清空数据

TRUNCATE `表名`
TRUNCATE `student`

与delete区别

• 清空自增计数器
• 不会影响事务

7、DQL

7.1、查询全部

SELECT * FROM 表名（WHERE 条件）
SELECT * FROM student

7.2、查询指定字段

SELECT `字段1`,`字段2` FROM 表名（WHERE 条件）
SELECT `studentno`,`loginpwd` FROM student

7.3、给字段起别名

SELECT `字段` AS 别名 FROM 表名（WHERE 条件）
SELECT `studentno` AS 学号 FROM student

7.4、拼接字符函数

CONCAT('str1','str2',...)
SELECT CONCAT('学号',studentno) FROM student

7.5、去除重复数据

SELECT DISTINCT 字段 FROM 表
SELECT DISTINCT name FROM student

7.6、查询更改表数据后结果

SELECT `studentresult`+1 FROM result
显示更改后数据，但不改变数据库原数据

7.7、模糊查询

SELECT `字段` FROM 表 WHERE `字段` LIKE '值'
SELECT `studentno` FROM student WHERE `studentname` LIKE '_佳%'
在'值'中可以写 % 代表存在多个字符，_ 代表存在一个字符。

7.8、联表查询

--两表交集部分INNER JOIN
SELECT s.`sid`,`sname`,`sresult`
FROM student AS s
INNER JOIN result AS r
ON s.`sid`=r.`sid`
--左表数据LEFT JOIN
SELECT s.`sid`,`sname`,`sresult`
FROM student s
LEFT JOIN result r
ON s.`sid`=r.`sid`
--右表数据RIGHT JOIN
SELECT s.`sid`,`sname`,`sresult`
FROM student s
RIGHT JOIN result r
ON s.`sid`=r.`sid`
WHERE `sresult` IS NOT NULL
--可以加上WHERE筛选数据

--多表查询
SELECT s.`sid`,`sname`,`sresult`,`subject`
FROM student AS s
INNER JOIN result AS r
ON s.`sid`=r.`sid`
--先查询其中两张表关系，生成临时表，再和第三张表联表查询
RIGHT JOIN subject sub
ON sub.`sid`=r.`sid`
WHERE `subject` IS NOT NULL

• AS可以省略

• 在使用left jion时，on和where条件的区别如下：

  1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

  2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

7.9、自连接

一张表看为两张进行查询

SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

7.10、排序

--ORDER BY 字段 DESC
--DESC降序
--ASC升序
SELECT * FROM student ORDER BY result DESC

7.11、分页

--LIMIT 当前页-1 显示数据数量
--从第1条数据开始显示4条数据（包括第1条数据）
SELECT * FROM student LIMIT 0,4
--从第6条数据开始显示8条数据
SELECT * FROM student LIMIT 5,8

7.12、子查询

在WHERE内嵌套查询语句

--查询`subjectname`='高等数学-1'的学生信息
SELECT `studentno`,`subjectno`,`studentresult` FROM result
WHERE `subjectno`=(		--这里的'='也可以用IN
	SELECT `subjectno` FROM `subject` 
    WHERE `subjectname`='高等数学-1'
)

8、MySQL函数

8.1、常用函数

函数	解释	实例	结果
ABS	绝对值	ABS(-5)	5
CEILING	向上取整	CEILING(1.23)	2
FLOOR	向下取整	FLOOR(3.5)	3
RAND	0~1间的随机数	RAND()

8.2、字符串函数

函数	解释	实例	结果
CHAR_LENGTH()	字符串长度	CHAR_LENGTH(‘abc’)	3
CONCAT()	拼接字符串	CONCAT(‘a’,1,‘c’)	a1c
INSERT(‘str’,pos,len,‘newstr’)	替换字符串	INSERT(‘abc’,1,2,‘efg’)	efgc
LOWER()	大写转小写	LOWER(‘Abc’)	abc
UPPER()	小写转大写	UPPER(‘abC’)	ABC
REPLACE(‘str’,‘from_str’,‘to_str’)	替换字符串	REPLACE(‘abc’,‘a’,‘de’)	debc
SUBSTR(‘str’,pos,len)	截取字符串	SUBSTR(‘abcde’,3,2)	cd
REVERSE()	反转字符串	REVERSE(‘abc’)	cba

8.3、时间和日期

函数	解释	结果
CURDATE()	查询当前日期	yyyy-mm-dd
NOW()	查询当前日期和时间	yyyy-mm-dd hh:mm:ss
LOCALTIME()	查询本地时间	yyyy-mm-dd hh:mm:ss
SYSDATE()	查询系统时间	yyyy-mm-dd hh:mm:ss

8.4、聚合函数

函数	解释
COUNT()	计数
SUM()	求和
AVG()	平均数
MAX()	最大值
MIN()	最小值

SELECT COUNT(`sid`) FROM student	--计数指定字段会忽略所有NULL
SELECT COUNT(*) FROM student		
SELECT COUNT(1) FROM student

8.5、分组

--根据科目名称进行分组查询每个科目最高分
SELECT `subjectname`,MAX(`studentresult`)
FROM result r
INNER JOIN `subject` sub
GROUP BY `subjectname`

8.6、MD5加密

UPDATE `user` SET pwd=MD5(pwd)

平均数 |
| MAX() | 最大值 |
| MIN() | 最小值 |

SELECT COUNT(`sid`) FROM student	--计数指定字段会忽略所有NULL
SELECT COUNT(*) FROM student		
SELECT COUNT(1) FROM student

8.5、分组

--根据科目名称进行分组查询每个科目最高分
SELECT `subjectname`,MAX(`studentresult`)
FROM result r
INNER JOIN `subject` sub
GROUP BY `subjectname`

8.6、MD5加密

UPDATE `user` SET pwd=MD5(pwd)