sql语句（基于MySql案例大全）

 

数据库相关

 

  1. 创建数据库

create database test;

  1. 查看数据库

show databases;

  1. 修改数据库

// 修改字符集 alter database test character set utf8;

  1. 删除数据库

drop database test;

  1. 选择数据库

use test;

// 查询当前使用的数据库

select database();  

表相关

字段的约束

主键约束（值必须唯一而且不空）：primary key auto_increment

唯一约束（值必须唯一相互之间不产生冲突）：unique

非空约束（值不能为空）：not null

创建表

普通建表

create table user(
id int primary key auto_increment,
name varchar(20) unique,
gender bit not null,
birthday date   
);
带有约束的建表
Create table us(
    Id int ,
Uid int,
Constraint key_name primary key (id),
Constraint f_k foreign key(Uid) references user(id)
);

根据子查询建表

Create user1

As select * from user;

查看表

查看所有表：show tables;

查看表结构：desc user;

查看数据库建表语句：show create table user;

修改表

增加列：

alter table user add (
nickname varchar(20),
Hobby varchar(20)
);

修改列的属性：

Alter table user

 modify nickname varchar(50);

修改列名：

Alter table user

change nickname nickna varchar(50);

修改表名：

rename table user to myuser;

修改字符集：

Alter table user character set utf8;

删除一列：

Alter table user drop image;

修改列的顺序：

Alter table user modify nickname varchar(20) after gender;

在nickname上设置唯一约束

Alter table user add constraint ni_ck unique(nickname);

删除约束：

Alter table user drop constraint ni_ck;// 有问题

 

删除表中数据

删除表

Drop table user;

删除表数据

delete 可以精准删除，一条一条的删除。

truncate 将表摧毁，然后重建表，效率高。

约束

非空约束

非空约束不能够指定名字
添加非空约束
Alter table user
Modify img blob not null;
 
删除非空约束
Alter table user
Modify img blob null;
 
同时可以使用default来指定默认值
Alter table user
Modify name varchar default ‘asd’;

 

唯一约束

删除约束

Alter table user

Drop index u_k;

 

 

主键

Create table us(
    id int ,
Uid int,
Constraint p_k primary key(id)
);

Mysql中的主键名是PRIMARY所以我们修改或者添加一个带有一个名字的主键是无效的。

Constraint union Primary key (id,uid)//联合主键

删除主键

Alter table user
Drop primary key;

 

外键约束

Alter table us
Add constraint f_k foreign key(uid) references user(id);
Create table us(
    id int ,
Uid int,
Constraint f_k foreign key(Uid) references user(id)
);
Alter table us
Drop foreign key f_k;
 
级联删除

在外键约束后面添加

On delete cascade 从表记录级联删除

On delete set null 从表外键设置成null

 

CRUD

增

insert into user (id,gender,nickname,birthday,name) values (null,1,'主流7','2018-08-19','hyl');

改

Update user set name = '主流7' , birthday = '2019-05-05' where id = 2;
Update user set

删

Delete from user where id = ' 5' ;

查

唯一查询
Select distinct nickname from user; 
查询结果在原来的结果上加10
Select salary + 10 from user;
将前两种结合起来
Select distinct salary + 10 from user;
 
新建一个表
Create table stu(
Id int,
Name varchar(20),
Chinese int,
Math int ,
English int,
Constraint p_k primary key (id) 
);
 
Insert into stu values (null,'zhuliu7',45,12,32);
Insert into stu values (null,'tom',75,52,42);
Insert into stu values (null,'jack',95,82,52);
Insert into stu values (null,'rose',45,82,62);
 
查询姓名对应的总分
Select name ,chinese + math + english as summary from stu;
所有的项都使用别名
Select name as 姓名, math + chinese + english as 总分 from stu;

 

条件查询

查询总分大于180的人

Select name from stu where math + chinese + english > 180;
查询英语成绩在70到90之间的人
Select name from stu where english between 60 and 90;
查询英语为特定值的同学
Select name from stu where english in (32,42,62);
查询以j开头的同学
Select * from stu where name like 'j%'; 
Select * from stu where name like 'j_'; 
Select * from stu where name like 'j__'; // 由两个字构成的名字

 

排序查询

使用英语成绩排序
Select * from stu order by english desc;
使用总分进行排名
Select name ,chinese + math + english as sum from stu order by (chinese + math + english) desc;

成绩为null的情况会出现求和失败如下图所示

mysql更改键名 mysql修改数据库名称的sql语句_基本语句

所以需要进行判空的处理

Select name, ifnull(english,0) + ifnull(chinese,0) + ifnull(math,0) as sum from stu where name = 'eminem';

正常显示，如果为空的话那么就将值置为0。

聚合函数

统计大于70分的人数

Select count(*)  from stu where chinese > 70;
统计所有人的总成绩
Select sum(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) from stu;
注意：Sum函数后面不能有空格。
Select count(*) from stu where (ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) > 230;
求一个班级数学的平均分
Select avg(math) from stu;
求一个班级的总平均分
Select avg(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) from stu;
求一个班级的最高分和最低分
Select max(chinese) from stu;
最低分为min()

分组查询

创建表
Create table product(
Id int auto_increment,
Pro varchar(50),
Name varchar(50),
Price int,
Constraint p_k primary key(id)
);
Insert into product values(null,'iphone','电器',20);
Insert into product values(null,'小灵通','电器',200);
Insert into product values(null,'杯子','日用',2000);
Insert into product values(null,'充电宝','电器',30);
Insert into product values(null,'数学之美','书籍',40);
Insert into product values(null,'机器学习','书籍',50);

 

查询各种商品的总价

Select pro,sum(price) from product group by pro;
查询购买了每类总价大于100的商品
Select pro ,sum(price) from product group by pro having sum(price) > 100;
查询总价大于100但是小于300的商品
Select pro, sum(price) from product group by pro having sum(price)> 100 and sum(price) < 300;
Having 是Group by之后的选择，比如说上述的sql语句是对分组之后的sum的选择。但是group对于前面的查询语句来说是没有什么限制条件的。
Select pro from product group by pro having sum(price)> 100 and sum(price) < 300;

 

Where与having的区别

**where 和 having都可以用来进行过滤，但是应用场景不同。where语句用来在分组之前进行过滤，having语句用来在分组之后进行过滤。

并且where语句中不允许使用聚合函数的。having语句中可以使用聚合函数。

使用where语句的地方一般都可以使用having替代，但是使用having的地方一般不能用where替代。

备份恢复数据库

备份数据库：

在cmd窗口中：mysqldump -u root -p db_name > c:/xxx.sql

 

恢复数据库：

方法1:在cmd窗口：mysql -u root -p db_name < c:/xxx.sql （这个命令是跑不通的目前）

方法2:在mysql中，通过source命令执行一个sql文件，恢复数据。

 

多表查询

create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部');
insert into dept values (null,'人事部');
insert into dept values (null,'销售部');
insert into dept values (null,'科技部');
 
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int
);
insert into emp values (null,'奥巴马',1);
insert into emp values (null,'哈里波特',2);
insert into emp values (null,'葫芦娃',3);
insert into emp values (null,'萨达姆',3);
insert into emp values (null,'朴乾',5);
笛卡尔积查询
Select * from dept,emp;
内连接查询(左边和右边都有的记录),类似于于一个等值连接不会去除等值列
Select * from dept,emp where dept.id = emp.dept_id;
Select * from dept inner join emp on dept.id = emp.dept_id;
左外链接
在内连接的基础上加上左边表特有的记录,对应在这个实例里就是查找所有的部门以及对应的员工，其中部门一定是全查但是对应的没有部门的员工则没有查到
Select * from dept left join emp on dept.id = emp.dept_id;
右外连接（在内连接的基础上加上右边表特有的记录）
Select * from dept right join emp on dept.id = emp.dept_id;
全连接就是求两张表的并集
由于Mysql中不支持全外链接所以只能是求两张表的并集
Select * from dept left join emp on dept.id = emp.dept_id
Union
Select * from dept right join emp on dept.id = emp.dept_id;

所有不合群的值都放在了最下面

存储引擎

Show table status like 'user';