数据库常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
数据库引擎区别
- MYISAM 和 INNODB:
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持(表锁) | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约2倍 |
修改和删除
-- 修改表名 ALTER TABLE 表明 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE teacher1 DROP age1
外键方法1:
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
-- 学生表的 gradeid 字段 引用年级表的 gradeid
-- 定义外键key
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 '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) 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:
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 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 '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 此时创建表时没有外键关系,只是创建了student和grade两个表
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
新增insert
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但必须一一对应
- values同时插入多条数据:values (),(),()…
修改update
-- 修改单个字段
UPDATE `student` SET `name`='谷玉洁' WHERE id = 1;
-- 修改多个字段
UPDATE `student` SET `name`='谷玉洁',`email`='1841731620@qq.com' WHERE id = 1;
-- 不指定条件情况下,会改动所有表
UPDATE `student` SET `name`='覆盖'
删除delete
TRUNCATE 和 delete 的不同:
TRUNCATE 重新设置自增量,从1开始;不会影响事务;
delete 不改变增量
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')
-- 用TRUNCATE 删除表
TRUNCATE TABLE `test`
-- 用delete删除表
DELETE FROM `test`
DQL查询数据
查询指定字段
-- 查询表所有信息
SELECT * FROM student
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 字段和表使用别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s
-- 函数 concat(a,b)
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student
去重
-- 查询有哪些同学参加了考试和成绩
SELECT `studentno` FROM result
-- 去掉重复数据
SELECT DISTINCT `studentno` FROM result
模糊查询
-- 模糊查询 %(字符中包含即可)_(字符中只含有一个)
-- 查询姓刘的同学
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 '%文%'
in关键字
-- 查询1001,1002,1003学号学员
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN (1001,1002,1003);
-- 查询在广东深圳的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address` IN ('济南','广东深圳');
联表查询
类型 | 描述 |
---|---|
inner join | 两个表的相同部分 |
right join | 返回右表的所有值,即使左表中没有匹配的 |
left join | 返回左表的所有值,即使右表中没有匹配的 |
/*
查询参加了考试的同学信息:学号,学生姓名,科目名,分数。用到三个表,先两个表进行查询,最后在加入最后一个表查询。
学生表中的 studentno = 成绩表中的 studentno
成绩表中的 subjectno = 科目表中的 subjectno
*/
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
自连接
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps设计 |
-- 实现查询父类对应的子类
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目',
FROM `category` AS a,`category` AS b,
WHERE a.`categoryid` = b.`pid`
-- 查询学员所属的年级(学号,学生的姓名,年纪名称)
SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid
-- 查询科目所属的年级(科目名称,年纪名称)
SELECT subjectname,gradename
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 s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
分页和排序
-- 查询参加了数据库结构-1考试的同学信息:学号,学生姓名,科目名,分数
-- 并根据查询成绩结果排序,升序排序
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN subject sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult ASC
-- 分页:每页只显示五条数据
-- limit 起始值,PageSize
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult ASC
LIMIT 0,5
子查询
-- 查询所有 数据库结构-1科目 的学生学号
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '数据库结构-1'
)
ORDER BY `studentresult` DESC
-- 查询分数不小于80分的学生学号和姓名,并且科目是高等数学-2
SELECT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.`studentno` = s.`studentno`
WHERE `studentresult` >= 80 AND `subjectno` = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname` = '高等数学-2'
)
-- 只用查询不连表
SELECT studentno,studentname FROM student WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult >=80 AND studentno = (
SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-2'
)
)
Mysql常用函数
-- 字符串函数
-- 返回字符串长度:
SELECT CHAR_LENGTH('即使再小的帆也能远航')
-- 拼接字符串:
SELECT CONCAT('编','程','代码')
-- 从某个位置开始替换字符
SELECT INSERT('编程helloword',1,2,'代码')
-- 转换大小写
SELECT UPPER('KuangShen')
SELECT LOWER('KuangShen')
-- 返回第一次出现的字串h的索引
SELECT INSTR('kuangshen','h')
-- 替换出现的指定字符
SELECT REPLACE('abcde','b','f')
-- 返回指定的字符串(源字符,截取的位置,截取长度)
SELECT SUBSTR('abcdefgh',4,3)
-- 反转
SELECT REVERSE('ABCDEFGH')
-- 查询周姓同学,把姓改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT SECOND(NOW())
MD5
CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) 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
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部密码
-- 插入时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行MD5加密,然后对比加密后的数值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')
索引
- 主键索引:只能有一个不能重复
- 唯一索引:避免重复的列出现,每一列都可以设置,可以重复
- 常规索引:KEY / INDEX
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引:索引名 (列名)
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname` (`studentname`);
-- EXPLAIN 分析sql的执行状况
EXPLAIN SELECT * FROM student;
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');
数据库用户管理
-- 创建用户 CREATE USER 用户名 INDENTIFIED BY '密码'
CREATE USER guyujie IDENTIFIED BY '123456'
-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456')
-- 修改指定用户密码
SET PASSWORD FOR guyujie = PASSWORD('123456')
-- 重命名 RENAME USER 原名字 TO 新名字
RENAME USER guyujie TO guyujie1
-- 用户授权 ( ALL PRIVILEGES全部权限,但是依然没有root权限高,除了GRANT权限 )
GRANT ALL PRIVILEGES ON *.* TO guyujie1
-- 查权限
SHOW GRANTS FOR guyujie1 -- 查指定用户权限
SHOW GRANTS FOR root@localhost -- 查root用户权限
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM guyujie1
-- 删除用户 DROP USER 用户名
DROP USER guyujie
数据库备份
- 直接拷贝
- 在sql yog手动导出,右键选择备份或导出
- 使用命令行
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理地址:/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理地址:/文件名
mysqldump -hlocalhost -uroot -p123456 school student... >D:/a.sql
三大范式
-
第一范式:每一列不可再分
-
第二范式:在第一范式的基础上,每张表只描述一件事
-
第三范式:满足第一范式和第二范式,第三范式需要确保数据表中的每一列数据和主键直接相关,不能间接相关。