python 操作三大主流数据库 第二课
tags:
- python3
- 慕课网
categories:
- python3
- mysql
- 数据库
文章目录
第一节 mysql安装配置
1. windows 下 mysql官网直接安装
1.下载地址:www.mysql.com/downloads
2.下载社区版本:https://dev.mysql.com/downloads/installer/
3.下5.7.X的版本就行。8.0代表5.8版本
4.大的.MSI直接安装
2. windows 下 XAMPP集成安装
1. 下载XAMPP然后安装就可以了
3. mysql图形化管理工具
1. Navicat
2. phpMyAdmin
第二节 mysql语法基础
1. mysql 数据定义语句 DDL
1. CREATE TABLE/DATABASE
2. ALTER TABLE/DATABASE
3. DROP TABLE/DATABASE
# 创建数据库
create database if not exists school default character set = 'utf8';
# 创建数据表
CREATE TABLE IF NOT EXISTS `students`(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY kEY,
`name` VARCHAR(20) NOT NULL,
`nickname` VARCHAR(20) NOT NULL,
`sex` CHAR(1) NULL,
`in_time` DATETIME NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. mysql 数据管理语句 DML
1. INSERT新增
2. DELETE删除
3. UPDATE修改
4. SELECT查询
2. 1mysql 插入语句
# 插入语句
INSERT INTO `students` VALUE (1,'张三','三哥','男',now());
INSERT INTO `students`(`name`,`nickname`,`sex`) VALUE ('李四','四哥','男');
# 添加多条 VALUES
INSERT INTO `students`(`name`,`nickname`,`sex`) VALUES
('王五','无哥','男'),
('陈留','留哥','女'),
('网吧','吧第','男');
2. 2mysql 查询语句
# 查询语句
SELECT 表中字段 FROM FROM table_references(表)
[WHERE where_ definition]
[GROUP BY {col name | expr | position}]
[HAVING where_ _definition]
[ORDER BY {col name | expr | position}]
[ASC | DESC], ..
[LIMIT {[offset,] row_count}]
# 查询所有
SELECT * FROM `students`;
# 查询具体字段
SELECT `name`,`nickname` FROM `students`;
SELECT `id`,`name`,`nickname` FROM `students` WHERE `sex`='男' ORDER BY `id` DESC;
# 从0开始查两个
SELECT `id`,`name`,`nickname` FROM `students` WHERE `sex`='男' ORDER BY `id` DESC LIMIT 0,2;
2. 3mysql 修改语句
# 修改语句
UPDATE table_ references
SET col_ name1=expr1 [, col_ name2=expr2 ..
[WHERE where_ definition]
# 修改所有数据
UPDATE `students` SET `sex`='女';
# 具体修改某一个
UPDATE `students` SET `sex`='男' WHERE sex='女';
UPDATE `students` SET `name`='女人',nickname='小女' WHERE id='1';
2. 4mysql 删除语句
# 删除语句
DELETE FROM tb_name
[WHERE where_ definition]
# 删除具体数据 一定加上WHERE 小心删库跑路
DELETE FROM `students` where `id`=1;
2. 5 mysql 其他语句
1. 新建索引( CREATE INDEX )
2. 新建索引( CREATE INDEX )
3. 删除数据库、表、索引、视图等(DROP)
4. ...
第三节 mysql新闻表设计
1. 数据表设计
ID: 新闻的唯一标示
title:新闻的标题
content:新闻的内容
created_at:新闻添加的时间
types:新闻的类型
image:新闻的缩略图
author:作者
view_count:浏览量
is_valid:删除标记 新闻是否有效
2. 删除:
物理删除:在数据库中直接将数据删除掉
逻辑删除:is_value 是否有效 ,有效1,无效0
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`content` varchar(2000) NOT NULL,
`types` varchar(10) NOT NULL,
`image` varchar(300) DEFAULT NULL,
`author` varchar(20) DEFAULT NULL,
`view_count` int(11) DEFAULT '0',
`created_at` datetime DEFAULT NULL,
`is_valid` smallint(6) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `news` VALUES ('1', '朝鲜特种部队视频公布 展示士兵身体素质与意志', '新闻内容', '推荐', '/static/img/news/01.jpg', null, '0', '2018-02-28 20:34:20', '1');
INSERT INTO `news` VALUES ('2', '男子长得像\"祁同伟\"挨打 打人者:为何加害检察官', '新闻内容', '百家', '/static/img/news/02.jpg', null, '0', '2018-03-01 20:34:29', '1');
INSERT INTO `news` VALUES ('3', '导弹来袭怎么办?日本政府呼吁国民堕入地下通道', '新闻内容', '本地', '/static/img/news/03.png', null, '0', null, '1');
INSERT INTO `news` VALUES ('4', '美监:朝在建能发射3发以上导弹的3000吨级新潜艇', '新闻内容', '推荐', '/static/img/news/04.png', null, '0', null, '1');
INSERT INTO `news` VALUES ('5', '证监会:前发审委员冯小树违法买卖股票被罚4.99亿', '新闻内容', '百家', '/static/img/news/08.png', null, '0', null, '1');
INSERT INTO `news` VALUES ('6', '外交部回应安倍参拜靖国神社:同军国主义划清界限', '新闻内容', '推荐', '/static/img/news/new1.jpg', null, '0', null, '1');
INSERT INTO `news` VALUES ('7', '\"萨德\"供地违法?韩民众联名起诉要求撤回供地', '新闻内容', '百家', '/static/img/news/new2.jpg', null, '0', null, '1');
INSERT INTO `news` VALUES ('10', '标题1', '新闻内容1', '推荐', '/static/img/news/01.png', null, '0', null, '1');
INSERT INTO `news` VALUES ('11', '标题20180202', '内容20180202', '百家', null, null, null, null, null);
INSERT INTO `news` VALUES ('12', '标题0', '内容0', '百家0', null, null, null, null, null);
INSERT INTO `news` VALUES ('13', '标题1', '内容1', '百家1', null, null, null, null, null);
INSERT INTO `news` VALUES ('14', '标题2', '内容2', '百家2', null, null, null, null, null);
INSERT INTO `news` VALUES ('15', '标题3', '内容3', '百家3', null, null, null, null, null);
INSERT INTO `news` VALUES ('16', '标题4', '内容4', '百家4', null, null, null, null, null);
INSERT INTO `news` VALUES ('17', '标题5', '内容5', '百家5', null, null, null, null, null);
INSERT INTO `news` VALUES ('18', '标题6', '内容6', '百家6', null, null, null, null, null);
INSERT INTO `news` VALUES ('19', '标题7', '内容7', '百家7', null, null, null, null, null);
INSERT INTO `news` VALUES ('20', '标题8', '内容8', '百家8', null, null, null, null, null);
INSERT INTO `news` VALUES ('21', '标题9', '内容9', '百家9', null, null, null, null, null);
INSERT INTO `news` VALUES ('22', '标题0', '内容0', '百家0', null, null, null, null, null);
INSERT INTO `news` VALUES ('23', '标题1', '内容1', '百家1', null, null, null, null, null);
INSERT INTO `news` VALUES ('24', '标题2', '内容2', '百家2', null, null, null, null, null);
INSERT INTO `news` VALUES ('25', '标题3', '内容3', '百家3', null, null, null, null, null);
INSERT INTO `news` VALUES ('26', '标题4', '内容4', '百家4', null, null, null, null, null);
INSERT INTO `news` VALUES ('27', '标题5', '内容5', '百家5', null, null, null, null, null);
INSERT INTO `news` VALUES ('28', '标题6', '内容6', '百家6', null, null, null, null, null);
INSERT INTO `news` VALUES ('29', '标题7', '内容7', '百家7', null, null, null, null, null);
INSERT INTO `news` VALUES ('30', '标题8', '内容8', '百家8', null, null, null, null, null);
INSERT INTO `news` VALUES ('31', '标题9', '内容9', '百家9', null, null, null, null, null);
INSERT INTO `news` VALUES ('32', '标题0', '内容0', '百家0', null, null, null, null, null);
INSERT INTO `news` VALUES ('33', '标题1', '内容1', '百家1', null, null, null, null, null);
INSERT INTO `news` VALUES ('34', '标题2', '内容2', '百家2', null, null, null, null, null);
INSERT INTO `news` VALUES ('35', '标题3', '内容3', '百家3', null, null, null, null, null);
INSERT INTO `news` VALUES ('36', '标题4', '内容4', '百家4', null, null, null, null, null);
INSERT INTO `news` VALUES ('37', '标题5', '内容5', '百家5', null, null, null, null, null);
INSERT INTO `news` VALUES ('38', '标题6', '内容6', '百家6', null, null, null, null, null);
INSERT INTO `news` VALUES ('39', '标题7', '内容7', '百家7', null, null, null, null, null);
INSERT INTO `news` VALUES ('40', '标题8', '内容8', '百家8', null, null, null, null, null);
INSERT INTO `news` VALUES ('41', '标题9', '内容9', '百家9', null, null, null, null, null);
INSERT INTO `news` VALUES ('42', '标题0', '内容0', '百家0', null, null, null, null, null);
INSERT INTO `news` VALUES ('43', '标题1', '内容1', '百家1', null, null, null, null, null);
INSERT INTO `news` VALUES ('44', '标题2', '内容2', '百家2', null, null, null, null, null);
INSERT INTO `news` VALUES ('45', '标题3', '内容3', '百家3', null, null, null, null, null);
INSERT INTO `news` VALUES ('46', '标题4', '内容4', '百家4', null, null, null, null, '1');
INSERT INTO `news` VALUES ('47', '标题5', '内容5', '百家5', null, null, null, null, '1');
INSERT INTO `news` VALUES ('48', '标题6', '内容6', '百家6', null, null, null, null, '1');
INSERT INTO `news` VALUES ('49', '标题7', '内容7', '百家7', null, null, null, null, '1');
INSERT INTO `news` VALUES ('50', '标题8', '内容8', '百家8', null, null, null, null, '1');