Day01回顾
1、MySQL的特点
1、关系型数据库
2、跨平台
3、支持多种编程语言
2、启动连接
sudo /etc/init.d/mysql start | stop | restart | reload
mysql -hIP地址 -u用户名 -p
MySQL中数据是以文件的形式存储在数据库目录/var/lib/mysql
3、基本SQL命令
1、库管理
1、show databases;
2、create database [if not exists] 库名 character set utf8;
3、select database();
4、use 库名;
5、show tables;
6、drop database 库名;
2、表管理
1、create table [if not exists] 表名(
字段名 数据类型,
... );
2、show create table 表名;
3、desc 表名;
4、drop table 表1,表2;
3、表记录的管理
1、insert into 表名(字段1,...) values(值1),(值2);
2、select 字段1,字段2... from 表名 [where 条件];
4、更改默认字符集
1、sudo -i
2、cd /etc/mysql/mysql.conf.d/
3、cp mysqld.cnf mysqld.cnf.bak
4、vi mysqld.cnf
[mysqld]
character_set_server = utf8
5、/etc/init.d/mysql restart
6、exit
5、数据类型
1、数值类型
1、整型
1、int(4字节)
2、tinyint(1字节)
默认有符号：signed
无符号 ：tinyint unsigned
2、浮点型
1、float(m,n) # 最多7个有效位
2、decimal(m,n) # 整数部分和小数部分分开存储
3、注意
1、浮点型插入整数时,自动补全小数位位数
2、小数位如果多于指定位数,对指定位下一位四舍五入
2、字符类型
1、char ：浪费存储空间,性能高
2、varchar ：节省存储空间,性能低
3、text / longtext / blob / longblob
4、字符类型宽度和数值类型宽度区别
1、数值类型宽度为显示宽度,仅用于select查询显示,和存储无关,zerofill
2、字符类型宽度超过则无法存储
3、枚举类型
1、enum(值1,值2)
2、set(值1,值2)
# 插入记录："mysql,python,study"
4、日期时间类型
*******************************************************************************************************************************************************
Day02笔记
1、数据类型
1、数值类型
2、字符类型
3、枚举类型
4、日期时间类型
1、date ："YYYY-MM-DD"
2、datetime ："YYYY-MM-DD HH:MM:SS"
3、timestamp ："YYYY-MM-DD HH:MM:SS"
4、time ："HH:MM:SS"
5、注意
datetime ：不给值默认返回 NULL
timestamp ：不给值默认返回系统当前时间
6、示例
1、创建一张表,用户充值表
create table t2(
id int,
username varchar(20),
password varchar(20),
money int,
birthday date,
cztime timestamp
)character set utf8;
2、插入记录
insert into t2 values
(1,"用户1","123456",500,"1995-05-20","2018-08-30 09:40:30");
insert into t2 values
(2,"用户2","123456",600,"1992-02-20",now());
2、日期时间函数
1、now() 返回服务器当前时间
2、curdate() 当前日期
3、date("1999-09-09 09:09:09") 提取 年月日
4、time("...") 提取 时分秒
5、year("...") 提取 年
6、练习
1、查找2018年8月30日用户充值的详细信息
select * from t2 where date(cztime)="2018-08-30";
2、查找2018年8月份所有用户充值的信息
select * from t2 where
date(cztime)>="2018-07-01" and date(cztime)<="2018-07-31";
3、查找2018年08月30日 08:00-10:00之间用户充值信息
select * from t2 where
cztime>="2018-08-30 08:00:00" and cztime<="2018-08-30 10:00:00";
3、日期时间运算
1、语法格式
select * from 表名
where 字段名 运算符(now()-interval 时间间隔单位);
时间间隔单位：
2 day | 3 hour | 1 minute | 2 year | 3 month
2、示例
1、查询1天以内的充值记录
select * from t2 where
cztime>=(now()-interval 1 day);
2、查询1年以前的充值记录
select * from t2 where
cztime<(now()-interval 1 year);
3、查询1天以前、3天以内的充值记录
select * from t2 where
cztime>=(now()-interval 3 day) and
cztime<=(now()-interval 1 day);
4、表字段操作
1、语法 ：alter table 表名 ...;
2、添加字段(add)
alter table 表名 add 字段名 数据类型;
alter table 表名 add 字段名 数据类型 first;
alter table 表名 add 字段名 数据类型 after 字段名;
3、删除字段(drop)
alter table 表名 drop 字段名;
4、修改字段数据类型(modify)
alter table 表名 modify 字段名 新数据类型;
## 会受到表中已有数据的限制
5、修改表名(rename)
alter table 表名 rename 新表名;
6、修改字段名(change)
alter table 表名 change 原字段名 新字段名 数据类型;
alter table new_t3 change name username char(15);
7、练习
1、在 db2 库中创建表 stutab ,字段有3个：
name、age、phnumber
use db2;
create table stutab(
name char(20),
age tinyint,
phnumber bigint
);
2、在表中第一列添加一个 id 字段
alter table studab add id int first;
3、把 phnumber 的数据类型改为 char(11)
alter table studab modify phnumber char(11);
4、在表中最后一列添加一个字段 address
alter table studab add address varchar(30);
5、删除表中的 age 字段
alter table stutab drop age;
5、表记录操作
1、删除表记录(delete)
1、delete from 表名 where 条件;
2、注意
一定要加where条件,不加where条件全部删除表记录
2、更新表记录(update)
1、update 表名 set 字段1=值1,字段2=值2 where 条件;
2、注意
一定要加where条件,不加where条件全部更新表记录
6、运算符操作
1、数值比较&&字符比较&&逻辑比较
1、数值比较 ：= != > >= < <=
2、字符比较 ：= !=
3、逻辑比较 ：
1、and ：两个或者多个条件同时成立
2、or ：有1个条件满足即可
where country="蜀国" or country="魏国";
4、练习
1、查找攻击力高于150的英雄的名字和攻击值
select name,gongji from sanguo
where gongji>150;
2、将赵云的攻击力设置为360,防御力设置为88,名字改为赵子龙
update sanguo set gongji=360,fangyu=88,name="赵子龙" where name="赵云";
3、将吴国英雄中攻击值为110的英雄的攻击值改为100,防御力改为60
update sanguo set gongji=100,fangyu=60
where country="吴国" and gongji=110;
4、查找蜀国和魏国的英雄信息
select * from sanguo where
country="蜀国" or country="魏国";
5、找出攻击力高于200的蜀国英雄的名字、攻击值和国家
select name,gongji,country from sanguo
where
gongji>200 and country="蜀国";
2、范围内比较
1、between 值1 and 值2
2、in(值1,值2)
3、not in(值1,值2)
4、练习
1、查找攻击值100-200之间的蜀国英雄信息
select * from sanguo
where
(gongji between 100 and 200) and country="蜀国";
2、查找蜀国和吴国以外的国家的女英雄信息
select * from sanguo
where country not in("蜀国","吴国") and sex="女";
3、查找id为1、3或5的蜀国英雄 和 貂蝉的信息
select * from sanguo
where
(id in(1,3,5) and country="蜀国") or name="貂蝉";
3、匹配空 和 非空
1、空 ：is null
2、非空 ：is not null
3、练习
1、查找姓名为 NULL 的蜀国男英雄信息
select * from sanguo
where
name is NULL and country="蜀国" and sex="男";
2、查找姓名为 "" 的英雄信息
select * from sanguo where name="";
3、在所有蜀国英雄中查找攻击力大于150的并且名字不为NULL的英雄的姓名、攻击值和国家
select name,gongji,country from sanguo
where
country="蜀国" and gongji>150 and name is not NULL;
4、查找魏蜀两国英雄中攻击力小于200并且防御力小于80的英雄信息
select * from sanguo
where
country in("魏国","蜀国") and gongji<200 and fangyu<80;
4、注意
1、NULL ：空值,只能用is、is not去匹配
2、"" ：空字符串,只能用 =、!= 去匹配
4、模糊查询(like)
1、where 字段名 like 表达式
2、表达式
1、_ ：匹配单个字符
2、% ：匹配0到多个字符
3、练习
# name中有2个字符以上的
select name from sanguo where name like "_%_";
# 匹配所有,但不包括NULL
select name from sanguo where name like "%";
# 匹配名字为3个字符
select name from sanguo where name like "___";
# 匹配姓赵的英雄
select name from sanguo where name like "赵%";
7、SQL高级查询
1、总结
3、select ...聚合函数 from 表名
1、where ...
2、group by ...
4、having ...
5、order by ...
6、limit ...;
2、order by ：给查询结果排序
1、order by 字段名 ASC/DESC
2、ASC(默认) ：升序
DESC ：降序
3、练习
1、将所有英雄按防御值从高到低排序
select * from sanguo order by fangyu DESC;
2、将蜀国英雄按攻击值从高到低排序
select * from sanguo
where country="蜀国" order by gongji DESC;
3、将魏蜀两国英雄中名字为3个字的,按防御值升序排序
select * from sanguo where
country in("魏国","蜀国") and name like "___"
order by fangyu;
select * from sanguo where
(country="蜀国" or country="魏国") and name like "___" order by fangyu;
select * from sanguo where
(country="蜀国" and name like "___") or
(country="魏国" and name like "___")
order by fangyu;
3、limit (永远放在SQL命令的最后写)
1、显示查询记录的条数
2、用法
limit n; --> 显示 n 条记录
limit m,n; --> 从第 m+1 条记录开始,显示 n 条
limit 2,3 : 显示第3、4、5三条记录
3、练习
1、在蜀国英雄中,查找防御值倒数第2名到倒数第4名的英雄的姓名、防御值和国家
select name,fangyu,country from sanguo
where country="蜀国"
order by fangyu ASC
limit 1,3;
2、在所有蜀国名字不为NULL的英雄中,查找攻击值前3名的英雄的姓名、攻击值和国家
select name,gongji,country from sanguo
where
country="蜀国" and name is not NULL
order by gongji DESC
limit 3;
4、分页
每页显示5条记录,显示第4页的内容
每页显示n条记录,显示第m页的内容
第1页 ：limit (1-1)*5,5 # 1 2 3 4 5
第2页 ：limit (2-1)*5,5 # 6 7 8 9 10
第3页 ：limit (3-1)*5,5 # 11 12 13 14 15
...
第m页 ：limit (m-1)*n,n
4、聚合函数
1、分类
avg(字段名) : 求该字段的平均值
sum(字段名) : 求和
max(字段名) : 最大值
min(字段名) : 最小值
count(字段名) : 统计该字段记录的个数
2、练习
1、所有英雄中攻击力最大值
select max(gongji) from sanguo;
2、统计id、name两个字段分别有多少条记录
select count(id),count(name) from sanguo;
5、group by : 给查询的结果进行分组
1、查询表中都有哪些国家
select country from sanguo group by country;
2、计算每个国家的平均攻击力
select country,avg(gongji) from sanguo
group by country;
先分组 再聚合 再去重
蜀国
蜀国
蜀国 200 蜀国
魏国
魏国 200 魏国
吴国 100 吴国
3、注意
1、select之后的字段名如果没有在group by之后出现,则必须要对该字段进行聚合处理(聚合函数)
4、练习
1、查找所有国家中英雄数量最多的前2名的国家名称和英雄数量(count())
select country,count(id) as number from sanguo
group by country
order by number DESC
limit 2;
6、having语句
1、作用 ：对查询结果进行进一步的筛选
2、练习
1、找出平均攻击力大于105的国家的前2名,显示国家名和平均攻击力
1、计算每个国家的平均攻击力
2、找到平均攻击力 > 105 的
select country,avg(gongji) as average from sanguo
group by country
having average>105
order by average DESC
limit 2;
3、注意
1、having语句通常和group by语句联合使用,过滤由group by语句返回的记录集
2、where只能操作表中实际存在字段,having语句可操作由聚合函数生成的显示列
7、表字段、表记录操作
表字段(alter table 表名) 表记录
增: add insert into 表名 ..
删: drop delete from 表名 ..
改: modify update 表名 set ...
查: desc 表名; select * from 表名 ..
