数据类型
整数：int，有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义
小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，整数占3位，如果插入的小数点后面超出长度，会自动的四舍五入
字符串：varchar，范围（0~65533），如varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符
日期时间: datetime，范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），如'2020-01-01 12:29:59'
查看任何一个数据类型的使用
1、打开mysql命令行客户端
2、help 数据类型名称，如 help tinyint
创建表
-- 创建学生表，字段要求如下：注释 ctrl + / ,取消注释 ctrl + shift + /
-- 创建学生表，字段要求如下：
-- 姓名(长度为10)， 年龄，身高(保留小数点2位)
create table students2(
id int unsigned primary key auto_increment,
name varchar(10),
age tinyint unsigned,
height decimal(5,2)
)
删除表
-- 删除学生表
-- drop table students
-- drop table if exists sutdents1
删除并创建新表
drop table if exists students;
create table students(
name varchar(10),
age int
)
增删改查
查询 select * from 表名
查询姓名和年龄
-- select name,age from stu
查询所有列的数据
-- select * from stu
查询性别为男的数据
select * from stu where sex='男'
插入 (添加数据)insert into 表名 values(...)
添加一行数据
格式一：所有字段设置值，值的顺序与表中字段的顺序对应
说明：主键列是自动增长，插入时需要占位，
通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准
insert into 表名 values(...)
-- 插入一个学生，设置所有字段的信息，值的顺序与表中字段的顺序对应
-- insert into students values('亚瑟',20)
格式二：部分字段设置值，值的顺序与给出的字段顺序对应
insert into 表名(字段1,...) values(值1,...)
-- 插入一个学生，只设置姓名，值的顺序与给出的字段顺序对应
-- insert into students(name) values('鲁班')
-- insert into students(age) values(30)
-- 值的顺序与给出的字段顺序对应
insert into students(age,name) values(30,'亚瑟2')
当表中有auto_increment的字段时，添加数据时使用0或者 default 或者 null 来占位
insert into students values(0,'老夫子3',20);
插入多条数据
添加多行数据
方式一：写多条insert语句，语句之间用英文分号隔开
方式二：写一条insert语句，设置多条数据，数据之间用英文逗号隔开
格式一：insert into 表名 values(...),(...)...
格式二：insert into 表名(列1,...) values(值1,...),(值1,...)...
-- insert into students values(0,'老夫子3',20);
-- insert into students values(0,'老夫子4',20);
-- insert into students values(0,'老夫子5',20);
-- 多条数据插入效率高
-- insert into students values (0,'老夫3',20),(0,'老夫4',20),(0,'老夫5',20)
-- insert into students(id,name) values (0,'老夫3'),(0,'老夫4'),(0,'老夫5')
修改数据 (更新数据)
格式：update 表名 set 列1=值1,列2=值2... where 条件
-- 修改id为5的学生数据，姓名改为 狄仁杰，年龄改为 20
update students set name='狄仁杰',age=20 where id=5
-- 更新数据 设置某一个学生的年龄加3岁
update students set age=age+3 where name='亚瑟3'
删除数据
格式：delete from 表名 where 条件
-- 删除id为6的学生数据
delete from students where id=6
-- 删除数据
delete from students where name='亚瑟3'
逻辑删除：对于重要的数据，不能轻易执行delete语句进行删除，
一旦删除，数据无法恢复，这时可以进行逻辑删除。
1、-- 添加字段，标识数据是否被删除 is_delete
-- 默认设置为0，代表数据没有被删除,1代表删除
-- update students set is_delete=0
2、-- 删除一条(某条)数据，只是修改了这条数据的is_delete 改为1
-- update students set is_delete=1 where name='老夫子6'
3、-- (以后在查询数据时，只查询出is_delete为0的数据)
-- 查询所有学生时，不显示删除的学生
select * from students where is_delete=0
-- alter table students drop column age -- 删除age字段
-- alter table students add age int(11) not Null; -- 添加students表字段age
*****************************************
mysql 修改 添加 删除 表字段
添加表的字段 alter table 表名 add 字段名 字段的类型
例子：alter table table1 add transactor varchar(10) not Null;
alter table table1 add id int unsigned not Null auto_increment primary key
在mysql数据库中怎样在指定的一个字段后面添加一个字段：
alter table newexample add address varchar(110) after stu_id;
修改表的字段类型 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型定义;
例子：ALTER TABLE chatter_users MODIFY COLUMN ip VARCHAR(50);
修改表的字段名 alter table 表名 change 原字段名 新字段名 字段的类型
例子： alter table student change physics physisc char(10) not null
删除表的字段 alter table 表名 drop column 字段名
例子：alter table `user_movement_log` drop column Gatewayid
调整表的顺序：ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID
表的重命名 alter table 原表名 rename 现表名;
例子： alter table t1 rename t2;
删除表的数据 delete from 表名 where （条件） id 不是从1开始 ，truncate table 表名 id是从1 开始的
*****************************************
sql查询
查询所有字段
select * from 表名
例：select * from students
查询指定字段
在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中
select 列1,列2,... from 表名
给字段起别名
-- select name as 姓名,age as 年龄,hometown as 家乡 from students where name='王昭君'
-- select name 姓名,age 年龄,hometown 家乡 from students where name='王昭君'
给表起别名
-- select s.name,s.age from students as s
消除重复行
在select后面列前使用distinct可以消除重复的行
select distinct 列1,... from 表名;
消除重复数据
-- select distinct age,class from students
select distinct * from students
1.条件
使用where子句对表中的数据筛选，符号条件的数据会出现在结果集中
select 字段1,字段2... from 表名 where 条件;
where后面支持多种运算符，进行条件的处理
比较运算,逻辑运算,模糊查询,范围查询,空判断
比较运算符
1.等于: = 2.大于: > 3.大于等于: >= 4.小于: < 5.小于等于: <= 6.不等于: != 或 <>
-- 例1：查询小乔的年龄
-- select age from students where name='小乔'
-- 例2：查询20岁以下的学生
-- select * from students where age<20
-- 例3：查询家乡不在北京的学生
-- select * from students where hometown<>'北京'
-- select * from students where hometown!='北京'
逻辑运算符
1.and(且) 2.or(或) 3.not(非)
-- 例1：查询年龄小于20的女同学
-- select * from students where age<20 and sex='女'
-- 例2：查询女学生或'1班'的学生
-- select * from students where sex='女' or class='1班'
-- 例3：查询非天津的学生
-- select * from students where not hometown='天津'
-- select * from students where hometown!='天津'
模糊查询
1.like 2.%表示任意多个任意字符 3._表示一个任意字符
-- 例1：查询姓孙的学生
-- select * from students where name like '孙%'
-- 例2：查询姓孙且名字是一个字的学生
-- select * from students where name like '孙_'
-- 例3：查询叫乔的学生
-- select * from students where name like '%乔'
-- 例4：查询姓名含白的学生
select * from students where name like '%白%'
范围查询
1.in表示在一个非连续的范围内 2.between ... and ...表示在一个连续的范围内
-- 例1：查询家乡是北京或上海或广东的学生
-- select * from students where hometown in ('北京','上海','广东')
-- 家乡不在北京或上海或广东
-- select * from students where hometown not in ('北京','上海','广东')
-- 例2：查询年龄为18至20的学生 ( between 20 and 18 小值在前 )
-- select * from students where age between 18 and 20
-- select * from students where age>=18 and age<=20 --不建议
空判断
1.注意：null与''是不同的 2.判空is null
-- 例1：查询没有填写身份证的学生
-- select * from students where card is null
非空
-- select * from students where card is not null
判断身份证为空字符
-- select * from students where card=''
2.排序
为了方便查看数据，可以对数据进行排序
语法：select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
1.将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
2.默认按照列值从小到大排列
3.asc从小到大排列，即升序
4.desc从大到小排序，即降序
-- 例1：查询所有学生信息，按年龄从小到大排序
-- select * from students order by age asc
--
-- 降序
-- select * from students order by age desc
-- 例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo
3.聚合函数
1.为了快速得到统计数据，经常会用到如下5个聚合函数
2.count(*)表示计算总行数，括号中写星与列名，结果是相同的
3.聚合函数不能在 where 中使用
-- 例1：查询学生总数
-- select count(*) as 学生总数 from students
-- select count(name) from students
-- count(card) 不统计为null数据
-- select count(card) from students
-- 例2：查询女生的最小年龄
-- select min(age) from students where sex='女'
-- 例3：查询1班的最大年龄
-- select max(age) from students where class='1班'
-- 例4：查询北京学生的年龄总和
-- select sum(age) from students
-- 例5：查询女生的平均年龄
select avg(age) from students where sex='女'
4.分组
1.按照字段分组，表示此字段相同的数据会被放到一个组中
2.分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中
3.可以对分组后的数据进行统计，做聚合运算
语法：select 列1,列2,聚合... from 表名 group by 列1,列2...
-- 例1：查询各种性别的人数
-- select sex,count(*) from students group by sex
-- 例2：查询各种年龄的人数
-- select age,count(*) from students group by age
分组后的数据筛选
语法：select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
having后面的条件运算符与where的相同
-- 例1：查询男生总人数
-- select sex,count(*) from students group by sex having sex='男'
select count(*) from students where sex='男'
对比where与having
1.where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
2.having是对group by的结果进行筛选
5.分页
获取部分行
当数据量过大时，在一页中查看数据是一件非常麻烦的事情
语法 select * from 表名
limit start,count
从start开始，获取count条数据
start索引从0开始
查询前3行学生信息
select * from students limit 0,3
练习:查询第4到第6行学生信息
select * from students limit 3,3
-- 12条,每一页显示3条,总共4页
select * from students limit 0,3
select * from students limit 3,3
select * from students limit 6,3
select * from students limit 9,3
分页
已知：每页显示m条数据，求：显示第n页的数据
select * from students limit (n-1)*m,m
求总页数
1.查询总条数p1
2.使用p1除以m得到p2
3.如果整除则p2为总数页
4.如果不整除则p2+1为总页数
练习:每页显示5条数据，显示每一页的数据
select * from students limit 0,5
统计每个班级中每种性别的学生人数，并按照班级升序排序
select class,sex,count(*) from students group by class,sex order by class
查询年龄最小的学生的全部信息
select * from students order by age limit 1
6.连接查询
笛卡尔积=两个表数据个数 相乘
1、等值连接
等值连接查询：查询的结果为两个表匹配到的数据
方式一 select * from 表1,表2 where 表1.列=表2.列
-- 查询成绩信息及课程信息
select * from courses,scores
where courses.courseNo=scores.courseNo
select c.courseNo as 课程表课程号,c.name 课程名, s.courseNo 成绩表课程号,s.score 成绩
from courses as c,scores as s
where c.courseNo=s.courseNo
方式二(又称内连接)
select * from 表1
inner join 表2 on 表1.列=表2.列
-- 查询学生信息及学生的成绩
select stu.name,sc.score from
students as stu ,scores as sc
where stu.studentNo=sc.studentNo
-- 查询成绩信息及课程信息 内连接
select sc.score,cs.name from
scores sc
inner join courses cs on sc.courseNo=cs.courseNo
-- 查询学生信息及学生的成绩 内连接
select stu.name,sc.score from
students stu
inner join scores sc on stu.studentNo=sc.studentNo
-- 例3：查询学生信息及学生的课程对应的成绩
select stu.name 姓名,sc.score 成绩,cs.name 课程名 from
students stu,scores sc,courses cs
where stu.studentNo=sc.studentNo and sc.courseNo=cs.courseNo
-- 例3：查询学生信息及学生的课程对应的成绩 内连接
select stu.name,sc.score,cs.name from students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
-- 例4：查询王昭君的成绩，要求显示姓名、课程号、成绩
select stu.name,sc.score,cs.name from students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where stu.name='王昭君'
-- 例5：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩
select stu.name,sc.score,cs.name from students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where stu.name='王昭君' and cs.name='数据库'
-- 例6：查询所有学生的数据库成绩，要求显示姓名、课程名、成绩
select stu.name,sc.score,cs.name from students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where cs.name='数据库'
-- 例7：查询男生中最高成绩，要求显示姓名、课程名、成绩
select stu.sex,stu.name,sc.score,cs.name from
students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where sex='男'
order by sc.score desc
limit 1
select stu.sex,stu.name,sc.score,cs.name from
students stu,scores sc,courses cs
where stu.studentNo=sc.studentNo and sc.courseNo=cs.courseNo
and sex='男'
order by sc.score desc
limit 1
2、左连接
左连接查询：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null
select * from 表1
left join 表2 on 表1.列=表2.列
-- 例1：查询所有学生的成绩，包括没有成绩的学生
select stu.name,sc.score from
students stu
left join scores sc on stu.studentNo=sc.studentNo
-- 例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select stu.name,sc.score,cs.name from
students stu
left join scores sc on stu.studentNo=sc.studentNo
left join courses cs on sc.courseNo=cs.courseNo
3、右连接
右连接查询：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null
select * from 表1
right join 表2 on 表1.列=表2.列
-- 例1：查询所有课程的成绩，包括没有成绩的课程
select sc.score,cs.name from
scores sc
right join courses cs on sc.courseNo=cs.courseNo
-- 例2：查询所有课程的成绩，包括没有成绩的课程，包含学生
select sc.score,cs.name,stu.name from
scores sc
right join courses cs on sc.courseNo=cs.courseNo
left join students stu on stu.studentNo=sc.studentNo
7.自关联
-- 例1：查询一共有多少个省
select count(*) from areas where pid is null
-- 例1：查询河南省的所有城市
select * from areas p,areas c
where p.aid=c.pid and p.atitle='河南省'
-- 添加一些区县
insert into areas values
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410103', '金水区', '410100');
-- 例2：查询郑州市的所有区县
select * from areas p,areas c
where p.aid=c.pid and p.atitle='郑州市'
-- 内连接inner join
select * from areas p
inner join areas c on p.aid=c.pid
where p.atitle='郑州市'
-- 例3：查询河南省的所有区县 (p省c市q区)
select * from areas p
inner join areas c on p.aid=c.pid
inner join areas q on c.aid=q.pid
where p.atitle='河南省'
8.子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询和子查询的关系
1.子查询是嵌入到主查询中
2.子查询是辅助主查询的,要么充当条件,要么充当数据源
3.子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类
1.标量子查询: 子查询返回的结果是一个数据(一行一列)
2.列子查询: 返回的结果是一列(一列多行)
3.行子查询: 返回的结果是一行(一行多列)
4.表级子查询: 返回的结果是多行多列
标量子查询
-- 例1：查询班级学生的平均年龄
-- select avg(age) from students
-- 查询大于平均年龄的学生 21.4167
-- select * from students where age>21.4167
-- 子查询返回的结果时一行一列：标量子查询
-- select * from students where age>(select avg(age) from students)
-- 例2：查询王昭君的成绩，要求显示成绩
-- 先查询王昭君的学号
-- select studentNo from students where name='王昭君'
-- select * from scores where studentNo=(select studentNo from students where name='王昭君')
列级子查询
-- 例3：查询18岁的学生的成绩，要求显示成绩
-- select * from scores where studentNo='0' or studentno='2'
-- 先查询18岁的学生的学号
-- select studentNo from students where age=18
--
-- select * from scores where studentNo in('002','006')
-- 子查询返回的结果时一列多行：列级子查询
select * from scores where studentNo in(select studentNo from students where age=18)
行级子查询
-- 例4：查询男生中年龄最大的学生信息
-- select max(age) from students where sex='男'
-- 方法一(标量查询)
-- select * from students where sex='男'
-- and age=(select max(age) from students where sex='男')
-- select * from students where (sex,age)=('男',30)
-- 方法二(标量查询)
select * from students where
(sex,age)=('男',(select max(age) from students where sex='男'))
-- select sex,age from students where sex='男' order by age desc limit 1
-- 方法三(行级查询)
-- 子查询返回的结果时一行多列：行级子查询
-- select * from students where
-- (sex,age)=(select sex,age from students where sex='男' order by age desc limit 1)
表级子查询
-- 例5：查询数据库和系统测试的课程成绩
-- (方法一)
-- select * from scores sc
-- inner join courses cs on sc.courseNo=cs.courseNo
-- where cs.name in ('数据库','系统测试')
-- select * from courses where name in ('数据库','系统测试')
-- 子查询返回的结果时多行多列：表级子查询
-- select * from 数据源
-- (方法二推荐)
select * from scores sc
inner join
(select * from courses where name in ('数据库','系统测试')) c
on sc.courseNo=c.courseNo
子查询中特定关键字使用
1.in 范围 (等于any或者some)
格式: 主查询 where 条件 in (列子查询)
2.any | some 任意一个
格式: 主查询 where 列 = any (列子查询)
在条件查询的结果中匹配任意一个即可,等价于 in
3.all
格式: 主查询 where 列 = all(列子查询) : 等于里面所有
格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有
select * from students where
age in (select age from students where age between 18 and 20)
select * from students where
age =any(select age from students where age between 18 and 20)
总结:
条件查询 where 字段 <,>,=,!=
and ,or, not
in,between and
like % _
排序 order by asc,desc
聚合函数 sum max min avg count(*)
分组 group by
分页 limit 0,3 游标 索引 角标
连接查询 等值连接（from 表1，表2 inner join） 左连接 left join 右连接 right join
子查询 标量子查询 列子查询 行子查询 表级子查询
9.查询演练
-- 求所有电脑产品的平均价格,并且保留两位小数
-- select round(avg(price),2) from goods
-- 查询所有价格大于平均价格的商品，并且按价格降序排序
-- select * from goods where price>(select round(avg(price),2) from goods)
-- order by price desc
-- 查询类型为'超极本'的商品价格
-- select price from goods where cate='超级本'
-- 查询价格大于或等于"超级本"价格的商品，并且按价格降序排列
-- select * from goods where price >=any (select price from goods where cate='超级本')
-- order by price desc
数据分表
-- 创建“商品分类”表
-- create table goods_cate(
-- cate_id int unsigned primary key auto_increment,
-- cate_name varchar(20)
-- )
-- select distinct cate from goods;
-- 把商品分类数据插入到“商品分类”表
-- insert into goods_cate(cate_name)
-- select distinct cate from goods
-- 创建“商品品牌”表 插入时，查询出来的字段名，插入到创建表的相同的字段名中
-- brand_name两个名不相同,如brand_ as brand_name(select distinct brand_ as brand_name from goods)
-- create table goods_brand(
-- brand_id int unsigned primary key auto_increment,
-- brand_name varchar(20)
-- )
-- select distinct brand_name from goods
-- 备份表
-- create table goods_back select * from goods
-- 更新表字段
-- update goods g inner join goods_cate c on g.cate=c.cate_name
--
-- set g.cate=c.cate_id
update goods g inner join goods_brand c on g.brand_name=c.brand_name
set g.brand_name=c.brand_id
-- 查询所有商品的详细信息 (通过内连接)
select id,name,cate_name,brand_name,price from goods as g
inner join goods_cate as c on g.cate_id = c.cate_id
inner join goods_brand as b on g.brand_id = b.brand_id;
-- 在商品数据表中插入记录，cate_id和brand_id的值都为品牌表和类型表不存在的值
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机','20','20','1849');
-- 查询所有商品的详细信息 (通过左连接)
select id,name,cate_name,brand_name,price from goods as g
left join goods_cate as c on g.cate_id = c.cate_id
left join goods_brand as b on g.brand_id = b.brand_id;
-- 分别在 good_scates 和 goods_brands表中插入记录
insert into goods_cates(cate_name) values ('路由器'),('交换机'),('网卡');
insert into goods_brands(brand_name) values ('海尔'),('清华同方'),('神舟');
-- 查询所有商品的详细信息 (通过右连接)
select id,name,cate_name,brand_name,price from goods as g
right join goods_cates as c on g.cate_id = c.cate_id
right join goods_brands as b on g.brand_id = b.brand_id;
连接服务端
打开cmd程序，进入到mysql安装目录的bin目录下
1、进入mysql的bin目录
cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
2、连接mysql
mysql -uroot -p
数据库
查看所有数据库 show databases;
使用数据库 use 数据库名;
查看当前使用的数据库 select database();
创建数据库 create database 数据库名 charset=utf8;
例：create database ceshi charset=utf8;
删除数据库 drop database 数据库名;
例：drop database ceshi;
乱码 set names 'gbk'
数据表
查看当前数据库中所有表 show tables;
查看表结构 desc 表名;
查看表的创建语句 show create table 表名;
例：show create table students;
备份
以管理员身份运行cmd程序
运行mysqldump命令
cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
mysqldump –u root –p 数据库名 > c:\ceshi.sql
# 按提示输入mysql的密码
恢复
先创建新的数据库
mysql -u root –p 新数据库名 < c:\ceshi.sql
# 根据提示输入mysql密码
内置函数
1.字符串函数
-- 拼接字符串concat(str1,str2...)
select concat(12,34,'ab');
select * from students
select name,sex,hometown,concat(name,'是',hometown,'的',sex,'生') as des from students
-- 包含字符个数length(str)
select length('abc');
-- 截取字符串
1.left(str,len)返回字符串str的左端len个字符
2.right(str,len)返回字符串str的右端len个字符
3.substring(str,pos,len)返回字符串str的位置pos起len个字符
select left('abc',1)
select left(name,1) from students;
select substring('abc123',2,3); --bc1 , pos 指的位置时从1开始
-- 去除空格
1.ltrim(str)返回删除了左空格的字符串str
2.rtrim(str)返回删除了右空格的字符串str
select ltrim(' bar '),rtrim(' bar '),rtrim(ltrim(' bar '));
-- 大小写转换
1.lower(str)
2.upper(str)
select lower('aBcD'),upper('aBcD');
2.数学函数
-- 求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0
select round(1.6,1);
-- 求x的y次幂pow(x,y)
select pow(2,3);
-- 获取圆周率PI()
select PI();
select round(PI(),10)
-- 随机数rand()，值为0-1.0的浮点数
select rand();
-- 随机取表中的一条
select * from students order by rand() limit 1
3.日期时间函数
当前日期current_date()
select current_date();
当前时间current_time()
select current_time();
当前日期时间now()
select now();
select current_date(),current_time(),now();
日期格式化date_format(date,format)
参数format可选值如下
%Y 获取年，返回完整年份
%y 获取年，返回简写年份
%m 获取月，返回月份
%d 获取日，返回天值
%H 获取时，返回24进制的小时数
%h 获取时，返回12进制的小时数
%i 获取分，返回分钟数
%s 获取秒，返回秒数
select date_format(now(),'%Y-%m-%d/%h/%i/%s');
例：将使用-拼接的日期转换为使用空格拼接
select date_format('2016-12-21','%Y %m %d');
4.流程控制
1.case语法：等值判断
2.说明：当值等于某个比较值的时候，对应的结果会被返回；
如果所有的比较值都不相等则返回else的结果；
如果没有else并且所有比较值都不相等则返回null
case 值
when 比较值1 then 结果1
when 比较值2 then 结果2 ...
else 结果 end
例：
select case 1
when 1 then 'one'
when 2 then 'two'
else 'zero'
end as result;
-- 流程控制 case语法
select
case 3
when 1 then 'one'
when 2 then 'two'
else 'zero'
end as result;
-- 姓 美女 帅哥
select left(name,1) ,sex,
case sex
when '男' then concat(left(name,1),'帅哥')
when '女' then concat(left(name,1),'美女')
else '保密'
end as res
from students
5.自定义函数
创建 语法如下
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter ;
说明：delimiter用于设置分割符，默认为分号
在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，
所以要创建存储过程前需要指定其它符号作为分割符，
此处使用//，也可以使用其它字符
示例
要求：创建函数my_trim，用于删除字符串左右两侧的空格
step1：设置分割符
delimiter $$
step2：创建函数
create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end
$$
------------------------
1、在navicat查询中，执行下面sql语句，可以在navicat中的数据库下面的函数中找到创建的自定义函数
create function my_trim(aaa varchar(100)) returns varchar(100)
begin
return rtrim(ltrim(aaa));
end
-- ltrim(str)返回删除了左空格的字符串str
-- rtrim(str)返回删除了右空格的字符串str
step3：还原分割符
delimiter ;
2、使用自定义函数
select ' abc ',my_trim(' abc ')
select my_trim(' name ')
存储过程
存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合
创建 语法如下
delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;
1.说明：delimiter用于设置分割符，默认为分号
2.在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，
所以要创建存储过程前需要指定其它符号作为分割符，
此处使用//，也可以使用其它字符
示例
要求：创建查询过程，查询学生信息
step1：设置分割符
delimiter //
1、在navicat查询中，执行下面sql语句，可以在navicat中的数据库下面的函数中找到创建的存储过程
step2：创建存储过程
create procedure proc_stu()
begin
select * from students;
end
//
step3：还原分割符
delimiter ;
调用
语法如下
call 存储过程(参数列表);
2、调用存储过程proc_stu
call proc_stu();
1.存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
2.存储过程和函数都是一次编译,就会被缓存起来,
下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
3.减少网络交互,减少网络访问流量
视图
1.对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，
则需要在多个地方进行修改，维护起来非常麻烦
2.解决：定义视图
3.视图本质就是对查询的封装
4.定义视图，建议以v_开头
create view 视图名称 as select语句;
例：创建视图，查询学生对应的成绩信息
create view v_stu_score_course as
select
stu.*,cs.courseNo,cs.name courseName,sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo
查看视图：查看表会将所有的视图也列出来
show tables;
删除视图
drop view 视图名称;
例：drop view v_stu_score_course;
使用：视图的用途就是查询
select * from v_stu_score_course;
事务
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。
所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性
事务命令
要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
查看表的创建语句，可以看到engine=innodb
show create table students;
修改数据的命令会触发事务，包括insert、update、delete
开启事务，命令如下：
开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
begin;
提交事务，命令如下
将缓存中的数据变更维护到物理表中
commit;
回滚事务，命令如下：
放弃缓存中变更的数据
rollback;
①提交
两个命令行客户端 左边客户端 右边客户端
1、左边客户端：查询学生信息
select * from students;
2、右边客户端：开启事务，插入数据
begin;
insert into students(studentNo,name) values ('013','abc');
3、右边客户端：查询数据，此时有新增的数据
select * from students;
4、左边客户端：查询数据，发现并没有新增的数据
select * from students;
5、右边客户端：完成提交
commit;
6、左边客户端：查询，发现有新增的数据
select * from students;
②回滚
两个命令行客户端 左边客户端 右边客户端
1、左边客户端：查询学生信息
select * from students;
2、右边客户端：开启事务，插入数据
begin;
insert into students(studentNo,name) values ('014','aaa');
3、右边客户端：查询数据，此时有新增的数据
select * from students;
4、左边客户端：查询数据，发现并没有新增的数据
select * from students;
5、右边客户端：回滚
rollback;
6、左边客户端：查询，发现没有新增的数据
select * from students;
7、右边客户端：查询，发现没有新增的数据
select * from students;
索引
查看索引 show index from 表名;
创建索引
方式一：建表时创建索引
create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);
方式二：对于已经存在的表，添加索引
如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
例：
create index age_index on create_index(age);
create index name_index on create_index(name(10));
删除索引：drop index 索引名称 on 表名;
创建测试表testindex
create table test_index(title varchar(10));
向表中加入十万条数据
创建存储过程proc_test，在存储过程中实现插入数据的操作
定义分割符 delimiter //
还原分割符 delimiter ;
创建存储过程，在navicat查询中，执行下面sql语句
create procedure proc_test()
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end
调用存储过程，向表中添加数据
call proc_test()
查询
开启运行时间监测：
set profiling=1;
查找第1万条数据test10000
select * from test_index where title='test10000';
查看执行的时间：
show profiles;
为表title_index的title列创建索引：
create index title_index on test_index(title(10));
执行查询语句：
select * from test_index where title='test10000';
再次查看执行的时间
show profiles;
缺点
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，
如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，
还要保存一下索引文件
2.但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,
所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引
-- 分析查询
explain
select * from test_index where title='test10000'
外键foreign key
1.如果一个实体的某个字段指向另一个实体的主键，就称为外键。
被指向的实体，称之为主实体（主表），也叫父实体（父表）。
负责指向的实体，称之为从实体（从表），也叫子实体（子表）
2.对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，
如果存在则填写成功，如果不存在则填写失败并报错
查看外键
show create table 表名
设置外键约束
方式一：创建数据表的时候设置外键约束
注意: goods 中的 cate_id 的类型一定要和 goods_cates 表中的 cate_id 类型一致
create table goods_fk(
id int unsigned primary key auto_increment,
name varchar(150),
cate_id int unsigned,
brand_id int unsigned,
price decimal(10,3) default 0,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(cate_id),
foreign key(brand_id) references goods_brands(brand_id)
);
foreign key(自己的字段) references 主表(主表字段)
方式二：对于已经存在的数据表设置外键约束
alter table 从表名 add foreign key (从表字段) references 主表(主表字段);
alter table goods add foreign key (cate_id) references goods_cates(cate_id);
alter table goods add foreign key (brand_id) references goods_brands(brand_id);
删除外键
-- 需要先获取外键约束名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key 外键名称;
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;
从表中插入数据
insert into goods_fk (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机','20','20','1849'); -- 插入不成功，因为主表中没有20这个值
insert into goods_fk (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机','1','1','1849'); -- 可以插入成功
从表添加、修改数据时，受主表的约束
主表删除数据时，如果已经被从表依赖了，不能删除
-- 创建表时，对于主键和unique字段，自动创建索引
create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);