SQL常见操作

安装服务器端：sudo apt_get install mysql-server
启动Mysql服务:sudo service mysql start
关闭Mysql服务：sudo service mysql stop
重启mysql服务：sudo service mysql restart
查看mysql服务是否存在：sudo service mysql status 或者 sudo -aux | grep mysql
验证mysql是否安装成功：mysql --help

使用数据库：use 数据库名称;

数据库导出：mysqldump -uroot -p 导出的数据库 > 路径/文件名.sql;
数据库导入： 1. 创建一个新的数据库
mysql -uroot -p
create database mytest2 charset = utf8;
exit;
2. 导入数据库
mysql -uroot -p mytest2 < /home/python/Desktop/sy07.sql;

查看表结构：desc class;

select：
显示时间：select now();
显示日期：select curdate();
显示时间：select curtime();
显示当前数据库：select database();

show：
查看所有数据库：show databases;
查看当前数据库中所有表：show tables;
查看创建数据库的语句：show create database 数据库名称;
查看创表语句：show create table 表名称; 或者 show create table students\G; \G表示按照行进行显示

create(表操作)：
创建数据库：create database 数据库的名字 charset=编码格式；
创表的总结：
create table 表名(
字段 数据类型 约束,
…
);

举例：
create table students(
	id int unsigned primary key auto_increment,
	name varchar(10) not null,
	age tinyint unsigned default 18,
	height decimal(3,2),
	gender enum('男', '女') default '女',
	cls_id int unsigned
);

————在创建表的时候可以直接将select的值插入到表中
————for example:create table goods_brands( *** ) select brand_name as name from goods group by brand_name;

alter(修改表结构)：
修改表-添加字段：alter table 表名 add 列名 类型;
修改表-修改字段（不重命名版）：alter table 表名 modify 列名 类型及约束;
修改表-修改字段（重命名版）：alter table 表名 change 原名 新名 类型及约束;
修改表-删除字段：alter table 表名 drop 列名;

drop：
删除表：drop table 数据表;
删除数据库：drop database sy07;

insert：
向表中插入数据：insert into 表名 values(***)； 注意：主键字段 可以用 0 null default 来占位
部分插入：insert into 表名(列1,…) values(值1,…)
插入数据的时候可以改变顺序：insert into students(height, name) values(1.67, ‘王五’);
多行插入：insert into students(height, name) values(1.70, ‘赵六’), (1.8, ‘冯七’);
————在插入数据的时候，可以将value的值替换为select的查询语句

update：
修改表中数据：update 表名 set 列1=值1,列2=值2… where 条件;
逻辑删除表中数据：update 表名 set is_delete = 1 where 条件;
联表更新：update 表
set 表名.字段名= 其他表名.新值
where 条件

foreign key(外键):
概念：当A表中的字段引用了B表中的字段，则称A表为子表，B表为主表，注意：在创建外键时，
A表中的字段必须完全匹配B表中的字段，且不能有超出B表中的字段的元素
格式：
添加外键：alter table 子表名 add foreign key(字段名) references 主表(字段名);
查看外键：show creare table 子表;
删除外键：alter table 子表名 drop foreign key 外键名称
（注意：此处删除的外键需要在查看外键中的获取外键名constra后面）

delete：
删除表中数据delete from 表名 where 条件

select：（查询）*****重点
查询所有列：select * from students;
查询指定列：select name, age from students;
可以使用as为列或表指定别名 as可以省略：select name as 姓名, age as 年龄 from students; 或者 select name 姓名, age 年龄 from students;

distinct 去重时候，如果后面跟两个对象，只有两个元素都相同时才会去重
distinct name，age

– 查询
– 给表起别名
select s.name, s.age from students as s
select students.name, students.age from students;
简写
select name, age from students;
– 消除重复行
select distinct gender from students;

– 条件查询
– 比较运算符

    -- 查询students表中，年龄大于18岁的所有学生的信息
    select * from students where age > 18;

    -- 查询students表中，年龄小于18岁的所有学生的信息
    select * from students where age < 18;

    -- 查询students表中，年龄大于18岁的id,name,gender
    select id, name, gender from students where age > 18;

    -- 查询大于等于18岁的所有学生的信息
    select * from students where age >= 18;

    -- 查询小于等于18岁的所有学生的信息
    select * from students where age <= 18;


    -- 查询students表中，年龄为18岁的所有学生的信息
    select * from students where age = 18;
    -- 查询students表中，年龄不是18岁的所有学生的信息
    select * from students where age != 18;
    select * from students where age <> 18;



-- 逻辑运算符
    -- and
    -- 查询students表中，年龄在18到28之间的所有学生信息
    select * from students where age >= 18 and age <= 28;

    -- 查询students表中，年龄在18岁以上的所有女性的信息
    select * from students where age > 18 and gender = '女';


    -- or
    -- 查询students表中，年龄在18以上或者身高超过1.8米(包含)以上的所有信息
    select * from students where age > 18 or height > 1.8;

    -- not
    -- 查询students表中，年龄不在18岁以上的女性这个范围内的信息
    select * from students where not age > 18 and gender = '女';

-- 模糊查询 -- 部分线索 
    -- like 像
    -- % 匹配任意个任意字符
    -- _ 匹配1个任意字符

    -- 查询students表中， 所有姓李的同学的相关信息
    select * from students where name like '李%'
    -- 查询students表中，姓名中姓是李 名字只有一位的数据
    select * from students where name like '李_';



-- 范围查询
    -- in 表示在一个非连续的范围内
    -- 查询students表中，年龄为18或者28或者38的姓名
    select * from students where age = 18 or age = 28 or age = 38;
    select * from students where age in (18, 28, 38);


    -- not in 不在非连续的范围之内
    -- 查询students表中，年龄不是18并且不是28并且不是38的姓名
    select * from students where age != 18 and age != 28 and age != 38;
    select * from students where not age in (18, 28 , 38)
    select * from students where age not in (18, 28, 38);

    -- between ... and ...表示在一个连续的范围内
    -- 查询students表中，年龄在18到25之间的的信息
    select * from students where age between 18 and 25;


    -- not between ... and ...表示不在一个连续的范围内
    -- 查询students表中，年龄不在18到34之间的的信息
    select * from students where not age between 18 and 34;

-- 空判断 NULL null
    -- 不能使用 = null判断字段是否为空
    -- 判空is null
    -- 查询students表中，没有填写身高的学生的所有信息
    select * from students where height is null;

    -- 判非空is not null
    -- 查询students表中，填了了身高的学生的所有信息
    select * from students where height is not null;
    select * from students where not height is null;

– 排序

-- 查询students表中，所有的学生 按照年龄从小到大;
select * from students order by age asc;
select * from students order by age; -》 默认是按照升序的方式进行排序

-- 查询students表中，年龄在18到58岁之间的男性，按照年龄从大到小排序
select * from students where (age between 18 and 58) and gender = '男' order by age desc;

-- 查询students表中，年龄在18到24岁之间的女性，身高从高到矮排序, 
select * from students where (age between 18 and 24) and gender = '女' order by height desc;
-- 如果身高相同的情况下按照年龄从小到大排序
select * from students where (age between 18 and 24) and gender = '女' order by height desc, age desc;

– 聚合函数

-- 总数 count, count不统计null，count可以结合一个不空字段进行统计
-- 在students表中，统计总人数
select count(*) from students;
-- 在students表中，统计男性人数
select count(id) from students where gender = '男';
-- 在students表中，统计女性人数
select count(id) from students where gender = '女';
-- 在students表中，统计身高这一列人数
select count(id) from students;
-- 在students表中，统计性别出现的种类
select count(distinct gender) from students;


-- 最大值 max
-- 查询students表中，最大的年龄
select max(age) from students;

-- 查询最高的身高
select max(height) from students;


-- 查询students表中，女性的最高 身高
select max(height) from students where gender = '女';


-- 最小值 min
-- 查询students表中，最小的年龄
select min(age) from students;

-- 求和 sum
-- 在students表中，计算所有人的年龄总和
select sum(age) from students;


-- 平均值 avg
-- 在students表中，计算所有的人的平均年龄

select sum(age)/count(id) from students;
select avg(age) from students;


-- 四舍五入 round(小数 , 保留的小数的位数)
-- 在students表中，计算所有人的平均年龄，保留2位小数
select round(avg(age),2) from students;
-- 在students表中，计算男性的平均身高 保留2位小数
select round(avg(height),2) from students where gender = '男';

– 分组
– group by
– 在students表中，按照性别分组,查询所有的性别
select gender from students group by gender;

-- 在students表中，计算每种性别中的人数
select gender, count(gender) from students group by gender;


-- group_concat() 显示分组信息
-- 在students表中，按照性别分组 显示每组中的姓名
select gender, group_concat(name) from students group by gender;

-- having 设定分组条件
-- having 只能结合group by 使用
-- 在students表中，查询每种性别中的人数多于2个的信息
select gender, group_concat(name), count(name) from students group by gender having count(name) > 2;


-- 以性别进行分组  并且查看每个分组中的数量 并且在最后一行新增一个汇总结构展示(with rollup)
select gender, count(gender) from students group by gender with rollup; 
-- with rollup 针对的是聚合函数，只有聚合函数这一列才会统计数据

– 分页 让结果集分部分显示给用户 --一般用以网站首页

-- limit [start=0,] count


-- 在students表中，查询前5条数据
select * from students limit 0, 5; -> 默认就是从第一条记录开始
select * from students limit 5;


-- 在students表中，查询第6条到第10条数据
select * from students limit 5, 5;

-- 每页显示m条记录，查询第n页数据

   1, 0, 5
   2. 5, 5

   （n-1)*m, m

-- (页码-1)* 数量, 数量
select * from students limit (n - 1) * m, m
-- 在students表中，查询女性信息 并且按照身高从高到矮排序 只显示前3个
select * from students where gender = '女' order by height desc limit 3;

– 连接查询
–使用内连接查询班级表与学生表
select * from students inner join class on students.cls_id = class.id;
–使用左连接查询班级表与学生表
select * from students left join class on students.cls_id = class.id;
–使用右连接查询班级表与学生表
select * from students right join class on students.cls_id = class.id;
–查询学生姓名及班级名称
select s.name, c.name from students s inner join class c on s.cls_id = c.id;

– 自关联

-- 查询所有省份
select * from areas where pid is null;

-- 查询出山东省有哪些市
select p.aid, p.atitle,c.atitle from areas p inner join areas c on p.aid = c.pid where p.atitle='山东省';

– 子查询 -> 在查询语句中使用到了另外一个查询语句的结果叫做子查询
– 标量子查询
– 查询出高于平均身高的信息
select * from students where age > (select avg(height) from students);

-- 列级子查询 产生的数据是一个集合
-- 查询有学生在班级的班级名称
select * from class where id in (select distinct cls_id from students);

-- 行级子查询  产生的数据是一个元组(记录)
-- 查询学生表中 年龄最大 和 身高最高的信息
select * from students where age = (select max(age) from students) and height = (select max(height) from students);
select * from students where (age, height) = (select max(age), max(height) from students);