mysql自连接查询去重_MySQL命令汇总

数据库相关操作：

1.数据库级别: CURD

show databases;

create database db1 default charset=utf8;

use db1;

select database();

drop database db1;

show create database db1;

2.文件级别： CURD

show tables;

create table students(

id int unsigned auto_increment primary key not null,

name varchar(30) not null,

age tinyint unsigend default 0,

height decimal(5,2),

constraint 外键名 foreign key (自己列名) references 主表(主列)

gender enmu('男','女','人妖','保密'),

tel int unique not null

)engine=innodb default charset=utf8;

delete from students;

truncate table students;

drop table students;

show create table students;

desc students;

alter table 表名 add 列名 类型 约束;

alter table 表名 modify 列名 新类型 新约束;

alter table 表名 change 原名 新名 新类型 新约束;

alter table 表名 drop 列名;

3.数据级别： CURD

insert into students(id,name) values('0','alex'),('0','Eva-J');

insert into students values('0','alex',18,17.5,'女',138888);

delete name from students where id<3;

update students set name='Eva-J' where id=1;

select * from students;

4.去重查询：

select distinct name,age from students;

select * from students order by age ASC height desc;

5.排序查询：

select * from students order by age ASC height desc;

6.分页:

select * from students limit 0,10;

select * from students limit m*(n-1),m;

7.分组：group by 可以做去重查询。

*****报错*****select * from students group by gender;

select gender,group_concat(name) from students group by gender;

select gender,count(*) from students group by gender;

筛选用having

select gender,count(*) from students group by gender having count(*)>3;

8.聚合函数：

select sum(age) from students;

select count(*) from students;

select avg(age) from students;

select ifnull(school,'要查找的不存在') from students;

9.连接：

内连接

select * from A表 join B表;----------->笛卡尔积

seleft * from A表 join B表 on A.gongfuid=B.id;

外连接

****报错*****select * from A left join B ;

select * from A left join B on A.gongfuid = B.id;

10.自连接：

--创建表--

create table areas(

id varchar(30) not null primary key,

title varchar(30),

pid varchar(30)

);

-- 导入 sql 文件--

source /路径.areas.sql

查询------------>关键就是给area表起两个不同的别名连接。

select * from areas city join areas pro on city.pid=pro.id;

11.子查询：

-标量 求班级中身高高于平均身高的学生信息

select * from students where height > (select avg(height) from students);

-行 求出班级中身高最高并且年龄最大的同学

select * from students where (age,height) = (select max(age), max(height) from students);

-列 查询所有使用了技能表中技能的所有英雄

select * from hero where gongfuid in (select id from gongfu);

12.数据库范式：

1NF 一个字段存储一个数据

2NF =1NF + 表中有主键 + 表中非主键元素必须完全依赖于主键

3NF = 2NF + 非主键字段必须直接依赖与主键字段(不能传递依赖)

13.E-R图

4种图形

多对多的时候要建立中间关系表

14.外键(唯一)

创建外键

表已经存在添加外键

alter table 表名 add foreign key(字段名) references 主表名(主表字段)；

创建表的同时添加外键约束

create table students(

字段名 类型 约束;

....;

forign key (字段名) references 主表名(主表字段)

);

查看外键约束名称

show create table students；

alter table 表名 drop foreign key 外键约束名；

15.sql高级语法：

插入另外一个表的查询语句

insert into 表名(字段名)select...

insert into good_cates(name) select cate_name from goods group by cate_name;

在创建表的同时插入select 语句数据需要起一样的名字

create table xxx() select....

create table good_brands (

id int unsigned primary key auto_increment,

name varchar(36) not null)

select brand_name as name from goods group by brand_name;

连表更新 在连表过程中使用一个标的字段值去修改另外一个表的值

update A join B on 条件 set 字段名=值;

16.参数化列表

sql='insert into xxx values(0,%s,%s,%s)'

course.execute(sql,['参数1','参数2','参数3’])

17.事务

原子性

一致性

隔离性

永久性

Begin / start transaction

commit

rollback

命令行默认自动开启事务自动提交事务

关闭命令行自动提交  set autocommit=0;

pymsql默认自动开启手动提交

conn.commit()

conn.rollback()

一般放在事务中的是insert操作

隐式提交不能撤销

18.索引

加速查找

show index from 表;

alter table 表名 add index 索引名(字段名(字符串长度));

alter table 表名 add index 索引名(字段名);

alter table 表名 drop 索引名(字段名);