1、关于SQL基本指令
- 创建表
CREATE TABLE IF NOT EXISTS `teacher`(
`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 '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
- 显示表信息
SHOW CREATE TABLE `student`
修改表的相关信息
CREATE TABLE `student` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
- 修改表
ALTER TABLE teacher RENAME AS `teacher1` -- 修改表名
ALTER TABLE teacher1 ADD age1 INT(11) -- 添加属性
- 修改表的字段
ALTER TABLE teacher1 MODIFY age1 VARCHAR(11) -- 修改字段的约束
ALTER TABLE teacher1 CHANGE age1 age INT(11) -- 字段重命名
- 删除表的字段
ALTER TABLE teacher1 DROP age
- 删除表
DROP TABLE IF EXISTS teacher1
2、 DML，数据库管理语言
- 插入
-- 插入语句（添加）: insert into `表名`(`字段`) values('值1'),('值2')...
INSERT INTO `student`(`name`) VALUES('老sun','老李')
-- 主键自增可以省略，插入语句一定要数据和字段一一对应
INSERT INTO `student`(`name`)
VALUES ('laowang'),('shjk')
-- 插入多个字段
INSERT INTO `teacher`(`name`,`pwd`)
VALUES ('张三','234'),('wang','2343434')
-- 如果省略字段，则后面添加的要跟字段列表一一对应，此时主键不能省略
INSERT INTO `teacher`
VALUES (7,'zhang','123','男','2000-01-01','sdf','5555')
- 修改
-- 修改表中满足==某个字段的判断条件==下的数据的==任意字段==属性
UPDATE `teacher` SET `id`=10 WHERE `id`=1
UPDATE `teacher` SET `pwd`='asdf' WHERE `id`<=3
UPDATE `teacher` SET `pwd`='asdfdsfsdfsdf' WHERE `id` BETWEEN 4 AND 7
-- 多个字段的判断条件
UPDATE `teacher` SET `address`='sdfsdfdsfsf' WHERE `sex`='男' AND `id`=2
-- 修改表中某个字段，会把该表中所有数据的该字段全部修改
UPDATE `teacher` SET `name`='张三'
-- 修改多个属性
UPDATE `teacher` SET `name`='王大妈',`sex`='男' WHERE `id`=2
- 删除
-- 删除指定数据
DELETE FROM `teacher` WHERE `id`=10
-- 清空一个数据库表，表的结构和索引约束不会变，delete from `student` 和 truncate `student`
-- 使用delete清空，自增量不变，truncate清空同时把自增量也归零了
TRUNCATE `student`
3、DML之查询
- 查询指定字段
-- 查询全部学生：select 字段 from 表
SELECT * FROM course
SELECT cname FROM course
SELECT `Cxueshi`,`Teacher` FROM course
-- 给查询的列起一个名字、给表起一个别名
SELECT `Cname` AS 名字,`Teacher` AS 老师 FROM course AS 课程
-- 函数:concat(a,b),字符串拼接
SELECT CONCAT('姓名：',`Cname`) AS 名字 FROM Course
-- 去重
SELECT DISTINCT `Cid` FROM score
-- 所有学生成绩加一分
SELECT `Sid`,`Score`+1 AS '加分后' FROM score
- where条件子句
-- 逻辑运算符//
-- 查询考试成绩90分以上的
SELECT `Sid`,`Score` FROM score
WHERE `Score`>90 AND `Score`<100
-- 模糊查询(区间)
SELECT `Sid`,`Score` FROM score
WHERE `Score` BETWEEN 90 AND 100
-- /模糊查询：比较运算符
-- 除了is null、is not null、between外，还有以下：
-- like：a like b，SQL匹配，如果a匹配b，则为真。
-- 查询姓刘的同学:like结合 %（代表0到任意个字符）, _代表（一个字符） %和_只能在like中使用
SELECT `Sid`,`Sname` FROM `student`
WHERE `Sname` LIKE '刘%' -- 查询姓刘的，任意长度的名字
SELECT `Sid`,`Sname` FROM `student`
WHERE `Sname` LIKE '刘_' -- 查询姓刘的，两个长度的名字
SELECT `Sid`,`Sname` FROM `student`
WHERE `Sname` LIKE '刘__' -- 查询姓刘的，三个长度的名字
SELECT `Sid`,`Sname` FROM `student`
WHERE `Sname` LIKE '%冈%' -- 查询名字中间有个嘉字的名字
-- in: a in (a1,a2,a3....)，假设a在a1，a2或者a3.....其中的某一个值中，则结果为真
-- 查询专业为英语软件和网络的学生信息
SELECT `Sid`,`Sname` FROM `student`
WHERE `Szhuanye` IN ('英语软件','网络')
- 联表查询(七种join)
/*
join on 连接查询
where 等值查询
*/
-- 查询参加了考试的同学（学号，姓名，分数）
/*
table student：学号、姓名
table score：分数、学号
*/
-- inner join：查询两张表共有部分的数据；left join：保留左表数据，右表没有的数据补null；
-- right join：保留右表数据，左表没有的数据补null
-- inner join
SELECT s.`Sid`,`Sname`,`Score`
FROM Student AS s -- 等同于 from student s
INNER JOIN score AS c
ON s.`Sid`=c.`Sid`
-- right join
SELECT s.`Sid`,`Sname`,`Score`
FROM Student s
RIGHT JOIN score c
ON s.`Sid`=c.`Sid`
-- left join
SELECT s.`Sid`,`Sname`,`Score`
FROM Student s
LEFT JOIN score c
ON s.`Sid`=c.`Sid`
-- 查询缺考的同学
SELECT s.`Sid`,`Sname`,`Score`
FROM Student s
LEFT JOIN score c
ON s.`Sid`=c.`Sid`
WHERE score IS NULL -- 左查询结果中做判断
- 连表查询之三表查询
-- 三表查询：查询参加了考试的同学信息（学号，姓名，科目编号，科目名，分数）
SELECT c.`Sid`,`Sname`,c.`Cid`,`Cname`,`Score`
FROM score c
LEFT JOIN student s
ON c.`Sid`=s.`Sid`
LEFT JOIN course u
ON c.`Cid`=u.`cid`
- 自连接
-- 插入具有父子信息的表
```sql
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`(
category INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主题id',
pid INT(10) NOT NULL COMMENT '父类id',
categoryName VARCHAR(20) NOT NULL COMMENT '主题名字'
)ENGINE=INNODB DEFAULT CHARSET=utf8;
USE `score`;
INSERT INTO `category` (`category`,`pid`,`categoryName`)
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
--查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` a,`category` b
WHERE a.`categoryid`=b.`pid`
- 排序和分页
- 排序 order by ：升序 ASC ，降序 DESC
- 分页：limit 起始值，页面大小，第n页：(n-1)*pageSize,pageSize
-- 三表查询：查询参加了考试的同学信息（学号，姓名，科目编号，科目名，分数）
SELECT c.`Sid`,`Sname`,c.`Cid`,`Cname`,`Score`
FROM score c
LEFT JOIN student s
ON c.`Sid`=s.`Sid`
LEFT JOIN course u
ON c.`Cid`=u.`cid`
-- 按照学号升序
ORDER BY c.`Sid` ASC
三表查询：查询参加了考试的同学信息（学号，姓名，科目编号，科目名，分数）
SELECT c.`Sid`,`Sname`,c.`Cid`,`Cname`,`Score`
FROM score c
LEFT JOIN student s
ON c.`Sid`=s.`Sid`
LEFT JOIN course u
ON c.`Cid`=u.`cid`
-- 按照学号升序
ORDER BY c.`Sid` ASC
LIMIT 0,5 -- 第一页
- 子查询
- 在where语句中嵌套一个子查询语句：where(select * from)
-- 查询英语考试的所有考试结果（科目编号、课程名称、学号、姓名、成绩），降序排列
-- 方式一：连接查询
SELECT c.`Cid`,u.`Cname`,c.`Sid`,s.`Sname`,c.`Score`
FROM score c
LEFT JOIN Student s
ON c.sid=s.sid
INNER JOIN course u
ON c.cid=u.cid
WHERE u.cname='英语'
ORDER BY c.score DESC
-- 方式二：子查询
SELECT `Cid`,`Sid`,`Score`
FROM score
WHERE cid IN(
SELECT cid FROM course WHERE cname='英语'
)
ORDER BY score DESC
连接查询结果
子查询结果
总结
- 连接查询能够将不同的多个表中的信息汇总
- 子查询的结果信息只能来自母表A中已经有的信息，子查询内的其他表只是用于筛选信息
4、聚合函数
- 统计表中的数据
SELECT COUNT(`Sage`) FROM `student`
SELECT COUNT(*) FROM student
SELECT COUNT(1) FROM student
== 执行效果上==
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
==执行效率上==
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优。
-- 查询总和、平均、最大最小
SELECT SUM(`Score`) AS 总和 FROM `score`
SELECT AVG(`Score`) AS 总和 FROM `score`
SELECT MAX(`Score`) AS 总和 FROM `score`
SELECT MIN(`Score`) AS 总和 FROM `score`
- 分组查询、过滤
- group by 和having同时使用
-- 查询不同课程名称+每门课平均分、最高最低分,平均分大于70进行筛选
-- 根据不同课程分组
SELECT `Cname`,AVG(`Score`),MAX(`Score`),MIN(`Score`)
FROM `score` s
INNER JOIN `course` c
ON s.`Cid`=c.`Cid`
GROUP BY s.`Cid` -- 指定结果按照哪几个字段分组
HAVING AVG(`Score`)>70 -- having 过滤分组的记录必须满足的次要条件
5、事务
要么都成功，要么都失败
- 数据库事务正确执行的四个基本要素：acid，具体参考：ACID
CREATE DATABASE bank CHARACTER SET utf8 COLLATE utf8_general_ci
USE bank
DROP TABLE IF EXISTS account
CREATE TABLE account(
`id` INT(3) AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(10) NOT NULL,
`money` DECIMAL(10,2) NOT NULL
)ENGINE INNODB DEFAULT CHARSET=utf8
INSERT INTO `account`(`name`,`money`)
VALUES ('A','2000.00'),('B','10000.00')
-- 模拟转账：事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET `money`=`money`-500.00 WHERE `name`='A'
UPDATE `account` SET `money`=`money`+500.00 WHERE `name`='B'
COMMIT; -- 提交事务 一旦提交，就被持久化了
ROLLBACK; -- 回滚，事务失败，则回滚为之前
SET autocommit =1; -- 恢复默认值
扫一扫
495
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
