1.创建数据库:
CREATE DATABASE IF NOT EXISTS 数据库名称;
指定字符集的方法有两种(效果完全相同)
CHARACTER SET utf8
CHARSET = utf8
2.查看数据库列表:
SHOW DATABASES;
3.使用数据库:
USE 数据库名称;
SELECT database(); 查看当前使用的数据库名字
SHOW CREATE DATABASE day02; # 查看指定数据库的详情
4.删除数据库:
DROP DATABASE 数据库名称;
5.修改数据库:
ALTER DATABASE day02 CHARSET 'utf8';
1.3.2DDL之表操作
1.创建数据表:
create table if not exists 数据表名(
字段名 数据类型(长度) 约束,
字段名 数据类型(长度) 约束,
字段名 数据类型(长度) 约束
);
Sql中常见的数据类型:
整数: int
小数: decimal, double
字符串: varchar, char
日期类型: date(年月日), time(时分秒), datetime(年月日, 时分秒), timestamp(时间戳, 毫秒值)
枚举类型: enum;ENUM ('男', '女', '保密')
2.查看数据表:
SHOW TABLES;
DESC 表名; -- 查看该表的详情的, 有哪些字段, 字段类型, 约束等.
3.修改表名:
RENAME TABLE 原字段名 TO 新字段名;
4.删除数据表:
DROP TABLE category;
1.3.3DDL之字段操作
1.查看表结构:
DESC category;
2.添加字段(列), addr int 非空约束
alter table 数据表名 add 列名 数据类型 约束;
ALTER TABLE category ADD address INT NOT NULL;
3.修改字段(列), 修改 类型, 约束:
alter table 数据表名 modify 列名 数据类型 约束;
ALTER TABLE category MODIFY address VARCHAR(50);
alter table 数据表名 change 旧列名 新列名 数据类型 约束;
ALTER TABLE category CHANGE addr address VARCHAR(20) UNIQUE;
4.删除字段(列)
alter table 数据表名 drop 列名;
ALTER TABLE category DROP address;
1.4.DML表数据的操作
1.4.1.DML之表数据的操作
1.添加表数据:
添加多条数据, 格式: insert into 数据表名(列名1, 列名2) values(值1, 值2),(值1, 值2),(值1, 值2);
语法糖的简写方法:
insert into 数据表名 values(值1, 值2, 值3); 必须是全列值
2.更新表数据:
update 数据表名 set 列名=值, 列名=值 where 条件;
3.删除表数据:
delete from 数据表名 where 条件; 只清空表数据,不会重置主键id
truncate table 数据表名; 清空表数据的同事,清空主键id
4.查询数据:
sele *from 表名
1.SELECT 列名(S) FROM 表名;
2.条件语句:
SELECT * FROM 表名 WHERE 条件;
3.比较运算符:不等于: != 或 <>,大于等于: >=
4.逻辑运算符:and 逻辑与,or 逻辑或,not 逻辑非
5.模糊查询:like是模糊查询关键字,%表示任意多个任意字符,_表示一个任意字符
SELECT * FROM product WHERE pname LIKE '%斯';
6.范围查询:
第一种and:SELECT * FROM product WHERE price BETWEEN 800 AND 2000;
第二种in:SELECT * FROM product WHERE price in (600,800,2000);
7.空判断查询:判断为空使用: is null,判断非空使用: is not null
1.5.2.排序查询
1.ASC 升序;DESC 降序
SELECT * FROM product ORDER BY price DESC;
1.分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组。
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;
说明:
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP:在所有记录的最后加上一条记录,显示select查询时聚合函数的统计和计算结果
1.5.3.分页查询
1.当我们在京东购物,浏览商品列表的时候,由于数据特别多,一页显示不完,一页一页的进行显示,这就是分页查询
2.SELECT 字段列表 FROM 表名 LIMIT M, N
说明:
limit是分页查询关键字
M表示开始行索引,默认是0
N表示查询条数
#起始索引,数据条数;每页的起始索引从:(当前页数-1)*每页数据条数
#总页数:(总条数+每页的数据条数-1)/每页的数据条数
#获取当前产品中,类别为c002的产品里,价格最低的2件商品
SELECT *FROM product WHERE category_id='c002' ORDER BY price ASC LIMIT 0,2;
3.#去重查询 distinct:
SELECT DISTINCT *from 表名;
1.5.4.多表查询
1.5.4.1.外键约束
1.外键约束相关:就是用来保证数据的完整性和安全性
2.添加外键约束:
ALTER TABLE employee add CONSTRAINT fk01 FOREIGN key(did) references dept(did);
2.删除外键约束:
ALTER TABLE employee DROP FOREIGN KEY fk01;
1.5.4.2.连接查询
1.内连接查询:求交集
SELECT 字段 FROM 左表 INNER JOIN 右表 ON 左表和右表的连接规则
2.左连接查询:保留左侧表信息,求交集
SELECT 字段列表 FROM 左表 LEFT OUTER JOIN 右表 ON 左表和右表的连接规则
3.右连接查询:保留右侧表信息,求交集
SELECT 字段列表 FROM 左表 RIGHT OUTER JOIN 右表 ON 左表和右表的连接规则
4.自关联查询:自连接查询必须对表起别名
SELECT *FROM areas as city INNER JOIN areas as prov on prov.id=city.pid WHERE prov.title='新乡市';
5.子查询:
SELECT *FROM areas WHERE pid=(SELECT id FROM areas WHERE title='河南省');
6.拓展之case when
case when 字段逻辑判断 then 替换值
when 字段逻辑判断 then 替换值
esle 替换值
end 新的字段名称
7.case 字段 when 值 then 替换值
when 值 then 替换值
else 替换值
end 新字段名称
1.6.窗口函数的基本用法
1.窗口函数的用法有:聚合函数+窗口函数
SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table
SELECT first_name,last_name, salary,sum(salary) OVER () as totalSalary FROM employee;
SELECT *,dense_rank() OVER (PARTITION BY country ORDER BY salary) FROM hero ;其中partition一般用来分组,将每一组的结果进行排序
排序函数+窗口函数
#排序函数(row_number,rank,dese_rank)
# row_number:1,2,3,4
# rank:1,1,3,4
# des_rank:1,1,2,3
SELECT
name,genre,updated,
row_number() OVER (ORDER BY updated desc) rn,
rank() OVER (ORDER BY updated desc) rk,
dense_rank() OVER (ORDER BY updated desc) dk FROM game;
1.在转换中,点击主对象树---DB连接---右键共享---新建---转换---将组件添加到平台中--双击表输入---获取SQL查询语句---选择表---否---预览---输出双击组件---目标表--SQL--执行---确定--运行
2插入更新---新建--转换--找到两个组件输入表和插入更新---双击输入表---选择表---双击插入更新表---目标表预览---添加关键字,设置id,比价符号为=,流里的字段为id--获取更新字段--确定---运行
3.switch-case:新建-转换-选择组件,表输入,Switchcase--再拖出两个excel-按住shift键盘将线连接并且选择create a new for this step-双击表输入--获取sql查询-确定-双击switchcase-switch字段选择gender-值写男,女确定-字段-获取字段-双击excel-浏览写下名字-执行.
1.8.Pytheon与mysql交互-pymysql模块
1.8.1.分表操作
1.分表操作步骤:
第一步:创建商品种类表
第二步:同步数据到商品种类表中
第三步:更新商品信息表数据
第四步:修改商品信息表表结构
2.创建分类表:
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null);
(1)将分组结果写入到goods_cates商品种类数据表中
insert into goods_cates (name) select cat_name from goods group by cate_name;
(2)将商品表中的分类名称更改为分类名称对应的分类id,注意:通过goods_cates数据表来更新goods表
update goods g inner join goods_cates gc on g.cate_name=gc.name set g.cate_name=gc.id
(3)修改goods表中的cate_name 名称和类型
alter table goods change cate_name cat_id int unsigned not null;