基于MySql数据库的基本sql语句

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的情况会出现求和失败如下图所示

所以需要进行判空的处理

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';