数据库基本操作整理
单表
记不住全部但是重点关注：创建，数据的查询，删除，添加，更改。其中查询最重要。
操作
数据库相关
– 列出所有的数据库
SHOW DATABASES;
– 创建数据库
CREATE DATABASE java1812 DEFAULT CHARACTER SET utf8;
– 删除数据库
DROP DATABASE java1812;
数据库表
– 切换数据库
USE java1812;
– 创建表
CREATE TABLE student(
id INT,
NAME CHAR(10),
age INT,
gender CHAR(1)
);
– 查看所有表
SHOW TABLES;
– 查看表的结构
DESC student; -- description
– 删除表
DROP TABLE student;
– 更改表的结构
– 添加字段
ALTER TABLE student ADD COLUMN address CHAR(10);
– 删除字段
ALTER TABLE student DROP COLUMN address;
– 修改表的字段
ALTER TABLE student CHANGE address addr CHAR(20);
– 修改表的名字
ALTER TABLE student RENAME TO stu;
– 创建表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
gender VARCHAR(1)
);
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT,--主键且递增
NAME CHAR(10) NOT NULL,--不能为空
age INT COMMENT '年龄',--注释
address CHAR(10) DEFAULT '中国', -- 插入数据时候如果不赋值，默认值是"中国"
UNIQUE KEY(NAME) -- 唯一键，代表这个字段不能重复
);
表内数据
– 查询所有的列
SELECT * FROM student;
– 插入数据
Duplicate entry '1' for key 'PRIMARY'
INSERT INTO student(id,NAME,age,gender) VALUES(1,'wangwu',23,'男');
INSERT INTO student(id,NAME,age,gender) VALUES(3,'赵六',23,'男');
INSERT INTO student VALUES(4,'赵六22',33,'男');
– 插入部分字段值(必须把前面的字段名都写上)，插图全部字段可省略
INSERT INTO student(NAME,age,gender) VALUES('小张11',23,'男');
– 一次插入多条数据
INSERT INTO student(NAME,age,gender) VALUES('小张77',23,'男'),('小王',22,'男');
– 修改数据（全部、个别）
UPDATE student SET age=age+1;
UPDATE student SET age=age+1 WHERE id=7;
– 删除数据
– 删除表中所有数据（很少使用，是非常危险）
DELETE FROM student;
DELETE FROM student WHERE age=24;//所有age是24的数据都被删除了，可能有多条数据都是age=24
– 因为id是主键是唯一的，所以根据id删除只能删除唯一的一条数据
DELETE FROM student WHERE id=12;
– TRUNCATE删除表里面所有数据，自增的id会重新初始化为初始值1
TRUNCATE TABLE student;
– 查询数据
– 显示所有列(字段)数据
– 学习时候可以写*， * 代表查询所有的列，但是在企业开发中需要什么字段就写什么字段
SELECT * FROM student;
SELECT id,NAME,age,gender FROM student;
– 查询指定列
SELECT NAME,age FROM student;
– 查询时候添加常量列，通过as可以起别名
SELECT id,NAME,age AS '年龄','java1812' AS '班级' FROM student;
– 查询时候和并列，字段名可以当成java里面的变量来运算
SELECT id,NAME,(php+java) AS '总成绩' FROM student;
– 查询时候去掉重复的记录
SELECT DISTINCT address FROM student;
– 条件查询 where
SELECT * FROM student WHERE NAME='小王';
其他
逻辑、运算
– 逻辑条件: and（同时成立） or(只要有一个成立)
SELECT * FROM student WHERE NAME='小王' AND address='青岛';
SELECT * FROM student WHERE NAME='小王' OR address='北京';
– 比较运算: > < >= <= !=
SELECT * FROM student WHERE java>=70 AND java<=80;
– between and (等价于>= and <=)
SELECT * FROM student WHERE java BETWEEN 70 AND 80;
– 查询地址不是青岛的学生信息
SELECT * FROM student WHERE address != '青岛';
聚合查询函数：sum(),avg(),max(),min(),count()
– 统计学生php的总成绩（sum求和）
SELECT SUM(php) AS 'php总成绩' FROM student;
– 统计学生php的平均值
SELECT AVG(php) AS 'php平均值' FROM student;
– 统计学生php的最大值
SELECT MAX(php) AS 'php最大值' FROM student;
– 统计学生表里面一共有多少学生
SELECT COUNT(*) AS '总人数' FROM student;
SELECT COUNT(id) AS '总人数' FROM student;
SELECT COUNT(address) AS '总人数' FROM student;
– 注意：count()函数统计的是指定列不包含NULL的数据个数，所以一般count（主键）。
查询排序
– 语法：order by 字段 asc/desc 默认是asc升序，可以不写
SELECT * FROM student ORDER BY php;
SELECT * FROM student ORDER BY php ASC;
SELECT * FROM student ORDER BY php DESC;
– 多个条件排序
– 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)
SELECT * FROM student ORDER BY php DESC, java ASC;
– 产生从头开始的rowno标记第几行
SET @rowno := 0;
SELECT (@rowno := @rowno +1) AS rowno,id,NAME,php,java FROM student;
分组查询(group by)
– 需求：查询男女分别有多少人
SELECT gender,COUNT(id) FROM student GROUP BY gender;
– select后面的查询都是基于group by之后的
SELECT address,COUNT(id) FROM student GROUP BY address;
– 分组查询后筛选
– 需求：address大于1
– group by之后的条件查询使用having
SELECT address AS '地址',COUNT(id) AS '人数' FROM student
GROUP BY address HAVING COUNT(id)>1;
字段属性设置:
1、not null： 不为空，表示该字段不能放“null”这个值。不写，则默认是可以为空
2、auto_increment: 设定int类型字段的值可以“自增长”，即其值无需“写入”，而会自动获得并增加
此属性必须随同 primary key 或 unique key 一起使用。primary key = unique key + not null
3、[primary] key： 设定为主键。是唯一键“加强”：不能重复并且不能使用null，并且可以作为确定任意一行数据的“关键值”，最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;
通常，每个表都应该有个主键，而且大多数表，喜欢使用一个id并自增长类型作为主键。
但：一个表只能设定一个主键。
4、unique [key] : 设定为唯一键：表示该字段的所有行的值不可以重复（唯一性）。
Duplicate entry ‘zhangsan’ for key ‘name’
5、default ‘默认值’： 设定一个字段在没有插入数据的时候自动使用的值。
6、comment ‘字段注释’
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(10) NOT NULL,
age INT COMMENT '年龄',
address CHAR(10) DEFAULT '中国',// 插入数据时候如果不赋值，默认值是"中国"
UNIQUE KEY(NAME) // 唯一键，代表这个字段不能重复
);
// Duplicate entry 'zhangsan' for key 'name'
INSERT INTO teacher(NAME) VALUES('zhangsan');
多表
几个语法
limit 偏移到哪个位置,往下数几个
– 取出价格最高的前三名商品
SELECT goods_id, goods_name, shop_price FROM goods
ORDER BY shop_price DESC LIMIT 0,3;
– 取出点击量前三名到第五名的商品
SELECT goods_id, goods_name, click_count
FROM goods ORDER BY click_count DESC LIMIT 2,3;
concat()显示拼接字符，不改变数据库
– 把goods表中商品名为’诺基亚xxxx’的商品,改为’HTCxxxx’,
– 提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .
– substring(),concat()
SELECT SUBSTRING(goods.goods_name FROM 4) FROM goods;
SELECT goods.goods_id,CONCAT('HTC',SUBSTRING(goods.goods_name FROM 4)),
cat_id FROM goods WHERE goods_name LIKE '诺基亚%';
关系：一对一，一对多，多对一，多对多
一对一，站在学生的角度，学生与班级是一对一的
一对多，站在班级角度，班级对学生是多对一
多对一，学生与班级
多对多，课程与学生
student表和class表：
对于student来说student和class是多对一关系 ，对于class来说class和student是 一对多关系。选取一对多关系中多的一方来加入外键。即先建立class表，在建立student表并在其中插入外键。
外键：
FOREIGN KEY(class_id) REFERENCES class(id)
之后再建立course表，这是一个独立的表。最后建立class_course表，插入联合主键和外键。
联合主键：
PRIMARY KEY(class_id,course_id)
CREATE TABLE class(
id INT(1) PRIMARY KEY AUTO_INCREMENT,
name CHAR(10) NOT NULL
);
INSERT INTO class(name) VALUES ('201801'),('201802');
CREATE TABLE student(
id INT PRIMARY KEY auto_increment,
name CHAR(10) NOT NULL,
gender CHAR(1),
age INT,
class_id INT,
FOREIGN KEY (class_id) REFERENCES class(id)
);
ALTER TABLE student CHANGE gender gender CHAR(3);
INSERT INTO student VALUES (1,'zhangsan','nan',19,1),
(2,'lisi','nv',21,2),
(3,'wangwu','nan',22,1);
CREATE TABLE course(
id INT PRIMARY KEY,
name CHAR(10) NOT NULL,
credit INT COMMENT '学分'
);
INSERT INTO course VALUES(1,'java',5),(2,'UI',4),(3,'H5',4);
CREATE TABLE class_course(
class_id INT,
course_id INT,
PRIMARY KEY (class_id, course_id),
FOREIGN KEY (class_id) REFERENCES class (id),
FOREIGN KEY (course_id) REFERENCES course (id)
);
INSERT INTO class_course VALUES (1,1),(1,3),(2,1),(2,2),(2,3);
子查询
– 子查询：嵌套查询，一个查询语句是另一个查询语句的条件
– 查询班级是201802班所有学生信息
SELECT *
FROM student WHERE class_id=(SELECT id FROM banji WHERE `name`='java1807';
SELECT *
FROM student WHERE class_id
IN(SELECT id FROM banji WHERE `name`='java1807' OR `name`='java1812');// 条件太多用in
关联查询
– inner join on 只有左右两个表有关联的才查询出来
– left join on 左表中都显示出来，右表没有显示空
– right join on 右表都显示，左表没有显示空
– 统计每个班有多少学生
– 学生数量，仅这样不需要关联
SELECT COUNT(id) as '学生数量' FROM student GROUP BY class_id;
– 班级名称 数量，要求班级名称，需要关联class表
–关联后每个指向都要详细
SELECT c.name, COUNT(s.id) FROM student as s INNER JOIN class as c ON s.class_id=c.id;
多表查询总结：
主要是注意下面两点
1、整个查询涉及到几张表，涉及到几张表就连接这几张表。
2、如果涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键建立的关系）
3.把inner join之后查询的结果当成一张表来使用， 在这个结果集里面根据班级id统计每个班级下面学生数量。
模糊查找：like
语法形式：字段 like ‘要查找字符’
说明：
1、like模糊查找用于对字符类型的字段进行字符匹配查找。
2、要查找的字符中，有两个特殊含义的字符：% , _:
2.1: %含义是：代表0或多个的任意字符
2.2: _含义是：代表1个任意字符
2.3: 这里的字符都是指现实中可见的一个“符号”，而不是字节。
3、语法：like ‘%关键字%’
SELECT * FROM student WHERE name LIKE ‘张%’; – 以张开头
SELECT * FROM student WHERE name LIKE '张_'; // 以张开头，而且名字是两个字
SELECT * FROM student WHERE name LIKE '%张%'; // 名字里面只要有张就可以
如果要查找的字符里中包含"%",""，
如果要查找的字符中包含“%”或“”，“ ’”，则只要对他们进行转义就可以：
like ‘%ab%cd%’ //这里要找的是： 包含 ab%cd 字符的字符
like ‘_ab%’ //这里要找的是： _ab开头的字符
like ‘%ab’cd%’ //这里要找的是： 包含 ab’cd 字符的字符
范式
第一范式（１ＮＦ）
原子性：
存储的数据应该具有“不可再分性”。
第二范式（２ＮＦ）
唯一性 (消除非主键依赖联合主键中的部分字段)
需要实现每一行数据具有唯一可区分的特性，并不能有部分依赖关系。
通常，给一个表加主键（也是推荐做法），就可以做到“唯一可区分”。
（唯一可区分：在这里“学生姓名”或者“课程名称”作为主键都不能做到唯一可区分，因为一个学生学了好几门课，一门课被几个学生学，所以只有“学生姓名”和“课程名称”一起作为联合主键才能“唯一可区分”）
不良做法：
第三范式（3NF）
独立性，消除传递依赖(非主键值不依赖于另一个非主键值，都应该依赖于主键)
总结
通常，在实践中，满足3范式只要做到“一个表只存一种类型数据”基本就可以实现。
另外，范式不是绝对要求，有时候我们为了数据的使用方便，还会（需要）故意违反范式。
扫一扫
2904
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
