MySQL基本操作

启动数据库

# sudo mysql -u账户 -p密码
sudo mysql -uroot -pmysql

1.数据库操作

# 数据库操作
# 连接数据库
mysql -uroot -p
mysql -uroot -p123456

# 退出数据库
exit/quit/ctrl+d

# sql语句最后需要家;结尾
# 显示数据库版本
select version();

# 显示时间
select now();

# 查看所有数据库
show databases;

# 创建数据库(字符默认使用拉丁文)
create database python4;
# 指定utf-8字符
create database python4new charset=utf8;

# 查看创建数据库的语句
show create database python4;  # CREATE DATABASE `python4` /*!40100 DEFAULT CHARACTER SET latin1 */
show create database python4new;  # CREATE DATABASE `python4new` /*!40100 DEFAULT CHARACTER SET utf8 */ 

# 查看当前使用的数据库
select database();

# 使用数据库
use python4new;

# 删除数据库
drop database python4;

2.数据表操作

# 数据表操作
# 查看当前数据库中所有表
show tables;

# 创建表
-- auto_increment 表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- DEFAULT 默认值
-- 格式：create table 数据表名字（字段 类型 约束）

create table demo(id int,name varchar(30));
create table demo02(
	id int primary  key not null auto_increment,
	name varchar(30)
	);

# 查看表的结构
desc demo;

# 创建students表(id\name\age\high\gender\cls_id) 
# 小数用decimal(5,2) 一共5位，2代表小数点后2位
# 最后一个不能加逗号
create table students(
	id int unsigned primary key not null auto_increment,
	name varchar(30),
	age tinyint unsigned,
	high decimal(5,2) unsigned not null,
	gender enum('男','女') default '男',
	cls_id int unsigned
	);

-- 创建classes表(id、name)
create table classes(
	id int unsigned primary key not null auto_increment,
	name varchar(20)
);

# 插入数据
insert into students values(0,'mak',18,180.88,'男',0);
insert into classes values(0,'python大神班');


# 查看表的创建语句
show create table students;

# 查看表结构
select * from students


# 修改表结构
# 修改表-添加字段
-- alter table table_name add field_name field_type;
alter table students add birthday datetime;

# 修改表-修改字段：不重命名字段，只修改字段中的属性
-- alter table table_name modify field_name field_type field_constraint;
alter table students modify birthday date;


# 修改表-修改字段：重命名字段名，
-- alter table table_name change field_original_name field_new_name field_type field_constraint;
alter table students change birthday birth date default '1990-01-01';

# 修改表-删除字段
alter table students drop high;

# 删除表
-- drop tabel tabel_name;
drop table demo;


# 增删改查CURD
# 增加
# 全列插入
# 主键字段可以用0 null default来占位
# 枚举中的下标从1开始
# 向classes 表中插入一个班级
insert into classes values(0,'python菜鸟班');

# 向students表插入 一个学生信息
insert into students values(0,'lizhiyi',35,'男',1,'1984-01-01');
insert into students values(null,'songchao',31,1,1,'1988-01-01');
insert into students values(default,'linzhimin',31,2,1,'1987-01-01');

# 部分插入
insert into students (name,gender) values('juaiyu',2);

# 多行插入
insert into students (name,gender) values('chenxingyu',2),('chengmingjian',2);
insert into students values 
	(0,'guoqian',28,2,1,'1992-01-01'),
 	(0,'panjiajia',27,2,1,'1993-01-01');

# 修改
update students set cls_id=1;
update students set birth='1987-12-01' where name='mak';
update students set age=26,birth='1992-01-01' where id=3;

# 删除 
# 物理删除(整个数据表中的数据将全部删除)
delete from students;
delete from students where name='mak';
# 逻辑删除
# 用一个字段来表示，这条信息十分以及不能再使用
# 给student 表条件一个is_delete字段bit 类型
alter table students add is_del bit default 0;

# 查询基本语句
# 查询所有列
select * from students;

# 指定条件查询
select * from students where name='mak';
select * from students where id>5;

# 查询指定字段
select name,age from students;
select name as 姓名 ,age as 年龄 from students;

# 字段的顺序
select age,name,id from students;

3.条件查询

# 条件查询
# 比较运算符
# 查询大于30岁的信息
select * from students where age>30;

# 查询小于30岁的信息
select * from students where age<30;

# 查询小于等于30岁的信息
select * from students where age<=30;

# 查询年龄为30岁的所有学生信息
select * from students where age=30;

# != <> 都是不等于的意思

# 逻辑运算符
# and

# 18至40之间的所有学生信息
select * from students where age>=18 and age<=40;

# 18岁以上的女性
select * from students where gender=2 and age>=18;

# or
# 18岁女性或身高超过180（包含）以上
select * from students where high>=180 or (age>=18 and gender=2);

# not
# 不在18岁以上女性 这个范围内
select * from students where not (age>=18 and gender=2);

# 年龄不少于或在等于18 并且是女性
select * from students where (not age<=18 ) and gender=2;


# 模糊查询
# like
# % 替换1个或者多个
# _替换1个
# 查询姓名中以“小”开始的名字
select * from students where name like'小%';
# 查询有2个字的名字
select * from students where name like'__';
# 查询有3个字的名字
select * from students where name like'___';
# 查询至少有3个字的名字
select * from students where name like'__%';

# rlike 正则
# 查询以孙开始的名字
select * from students where name rlike '^孙.*';

# 查询以孙开始的名字、香结尾的姓名
select * from students where name rlike '^孙.*香$';

# 范围查询
# in (1,3,8)表示在一个非连续的范围内
# 查询 年龄为18、34的姓名
select * from students where age in (18,34);

# not in 
# 查询不在 年龄为18、34的姓名
select * from students where age not in (18,34);

# between ... and ... 表示在一个连续范围内
# 查询24-35岁的学生信息
select * from students where age between 24 and 35;

# not between ...and ... 表示不再一个连续范围内
# 查询不在24-35岁的学生信息
select * from students where age not between 24 and 35;
select * from students where  not age between 24 and 35;


# 空判断
# 判断空 is null
# 查询身高为空的信息
select * from students where high is null;

# 查询身高不为空的信息
select * from students where high is not null;

4.排序

# 排序
 -- oreder by 字段
 -- asc 从小到大排序，即升序
 -- desc 从大到小排序，即降序
# 查询年内在18-50岁之间的男性，安装年龄从小到大排序
select name,age from students where (age between 18 and 50) and gender=1 ;

# 查询年龄在18-50岁之间的男性，身高从高到矮排序
select name as 姓名,age as 年龄, high as 身高 from students 
where (age between 18 and 50) and gender=1 order by high desc;

# order by 多个字段
# 查询年龄在18-34岁之间的男性，身高从高到矮排序，如果身高相同的情况下安装年龄从小到大排序
select name as 姓名,age as 年龄, high as 身高 from students 
where (age between 18 and 50) and gender=1 order by high desc,age asc;

# 查询年龄在18-34岁之间的男性，身高从高到矮排序，如果身高相同的情况下安装年龄从小到大排序
# 如果年龄也相同南门安装id从大到小排序
select name as 姓名,age as 年龄, high as 身高 from students 
where (age between 18 and 50) and gender=1 order by high desc,age asc,id desc;

# 按照年龄从小到大，身高从高到矮的排序
select name,age,high from students 
order by age,high desc;

5.聚合函数

# 聚合函数
# 总数 count
# 查询总人数
select count(*) as 总人数 from students;

# 查询男性有多少人，女性有多少人
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;

# 最大值 max
# 查询最大的年龄
select max(age) as 最大年龄 from students;

# 查询女性的最高 身高
select max(high) as 最高女性 from students where gender=2;

# 最小值 min
select min(high) as 最矮男性 from students where gender=1;

# 求和
# 计算所有人的年龄总和
select sum(age) as 年龄总和 from students;


# 平均值 avg
# 计算平均年龄 sum(age)/count(*)
select sum(age)/count(*) as 平均年龄 from students;

# 四舍五入 round(123.2345,1) 保留1位小数
# 计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*),2) as 平均年龄 from students;

# 计算男性的平均身高，保留2位小数
select round(sum(high)/count(*),2) as 男性平均身高 from students where gender=1;

6.分组

# 分组（分组要和聚合函数一起使用，否则没有意义）
# group by 
# 按照性别分组，查询所有的性别
select gender from students group by gender;

# 计算每种性别中的人数
select gender,count(*) from students group by gender;

# 计算男性的人数
select gender,count(*) from students where gender=1 group by gender;

# group_concat(...)
# 查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;
select gender,group_concat(name,',age:',age,',id:',id,' ') 
from students group by gender;

# where 是对原表的条件判断，在group by前
# having 是查询出来后再进行的条件判断，它经常在分组使用 在group by后
# 查询平均年内超过30 岁的性别，以及姓名 having avg(age)>30
select gender,avg(age),group_concat(name) from students
group by gender having avg(age)>30;
 
# 查询每种性别中的人数多于2个信息
select gender,group_concat(name) from students
group by gender having count(*)>2;

7.分页

# limit start count
# 限制查询出来的数据个数
select * from students where age>24 limit 2;
# 查询前5个数据
select * from students limit 0,5;
# 查询id 6-10（包括）的书序 
select * from students limit 5,5;

# 每页显示2个，第1个页面
select * from students limit 0,2;

# 每页显示2个，第2个页面
select * from students limit 2,2;

# 每页显示2个，第3个页面
select * from students limit 4,2;

# 每页显示2个，第4个页面
select * from students limit 6,2;--->limit (第N页-1)*每页的个数，每页的个数
 
# 每页显示2个显示第6页的信息，按照年龄从小到大
-- 错误写法 select * from students limit 2*(6-1),2;
-- 错误写法 select * from students limit 10,2 order by age asc;
select * from students limit 10,2 order by age asc;
# 显示2个按身高降序排列女性
select name,high from students where gender=2 order by high desc limit 2;

8.连接查询

# 内连接
# inner join ... on
select * from students join classes;
# 查询 有能够对于班级的学生以及班级信息
select * from students as s join classes as c 
on s.cls_id = c.id;

# 按照要求显示姓名、班级，给数据表起名字
select s.name,c.name from students as s join classes as c 
on s.cls_id = c.id;


# 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.*,c.name from students as s join classes as c 
on s.cls_id = c.id;

# 在以上的查询中，将班级姓名显示在第一列
select c.name,s.* from students as s join classes as c 
on s.cls_id = c.id;

# 查询能够对应班级的学生以及班级信息，按照班级进行排列
select c.name,s.* from students as s join classes as c 
on s.cls_id = c.id order by c.name;

# 当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name,s.* from students as s join classes as c 
on s.cls_id = c.id order by c.name,s.id;

# 外连接
# left join
# 查询每位学生对应的班级信息
select s.*
from students as s left join classes as c
on s.cls_id=c.id;
# 查询有没有对应班级信息的学生
# where
select s.*
from students as s left join classes as c
on s.cls_id=c.id 
where cls_id is null;
# having
select s.*
from students as s left join classes as c
on s.cls_id=c.id 
having cls_id is null;

# right join
# 将数据表名字互换位置，用right join完成
select s.*
from classes as c right join students as s 
on s.cls_id=c.id 
having cls_id is null;

9.自关联

# 新建表
create table areas(
	aid int primary key auto_increment,
	atitle varchar(20),
	pid int
);
CREATE TABLE t_area (
	id int(11) unsigned primary key,
	name varchar(48),
	level tinyint(2),
	pid int(11)
);

# 导入数据(不行就直接打开sql文件拷贝粘贴执行)
source /Users/makchikin/Desktop/areas.sql;
# 省级联动 URL: http://demo.lanrenzhijia.com/2014/city0605/

# 查询所有省份
select * from t_area where pid is null;

# 查询广东省有哪些城市
# 1 
select * from t_area where pid=440000;
# 2
select * from t_area as province join t_area as city
on province.id = city.pid 
having province.name='广东省';

# 查询广州市有哪些区
# 1
select * from t_area where pid=440100;
# 2
select * from t_area as city join t_area as district 
on city.id=district.pid
having city.name='广州市'; 

# 同时查询省市
select * from t_area as province join t_area as city
on province.id = city.pid 
having province.name='浙江省';

# 同时查询省市区
select * from t_area as province join t_area as city join t_area as district 
on province.id = city.pid and city.id = district.pid
having province.name='浙江省';

10.子查询（当大量数据查询时不推荐，性能慢）

# 查询广州市有多少个区
select * from t_area where pid =(
	select pid from t_area where name='广州市'
);