开发者社区> hello熊本> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQl之最全且必会的sql语句

简介: 原文链接:http://blog.csdn.net/qq_32059827/article/details/51763950 创建一个名称为mydb1的数据库,如果有mydb1数据库则直接使用,如果无则创建mydb1数...
+关注继续查看

原文链接:http://blog.csdn.net/qq_32059827/article/details/51763950

创建一个名称为mydb1的数据库,如果有mydb1数据库则直接使用,如果无则创建mydb1数据库  
create database if not exists mydb1;  
create database if not exists mydb1;  

创建一个使用UTF8字符集的mydb2数据库,注意这里不是UTF-8  
create database if not exists mydb2 character set UTF8;  
create database if not exists mydb2 character set UTF8;  

创建一个使用UTF8字符集,并带校对规则的mydb3数据库  
create database if not exists mydb3 character set UTF8 collate utf8_general_ci;  

校对规则:是数据库表中的所有记录按什么方式存储数据的先后顺序,例如:a在前,z在后  
字符集与校对规则是一一对应,不能乱改  
如果不写校对规则的话,默认是[对应字符集]默认的校对规则。参考<<MySQL5手册--10.10.10>>  

查看当前数据库服务器中的所有数据库  
show databases;  

查看前面创建的mydb1数据库的定义信息  
show create database mydb1;  
show create database mydb1;  

删除前面创建的mydb1数据库,如果有mydb1则删除  
drop database if exists mydb1;  
drop database if exists mydb1;  

使用mydb2数据库  
use mydb2;  
use mydb2;  

查看数据库服务器中的数据库,并把其中mydb3库的字符集修改为GBK  
alter database mydb3 character set GBK;  
alter database mydb3 character set GBK;  

以下代码是在mydb2数据库中创建users表,并插入几条记录,学员们暂时不用理会  
create table if not exists users(  
    name varchar(10)  
);  
insert into users values('XX');  
insert into users values('YY');  
insert into users values('ZZ');  

备份mydb1库中的数据到e:/xx.sql的文件中,以便将来恢复之用  
window7(先exit到windows环境)备份:mysqldump -uroot -p mydb1 > e:\mydb1.sql回车  
                                    mysqldump -uroot -p mydb1 > d:/myydb1.sql  
mysqldump是mysql提供的用于备份数据库的命令  
mysqldump命令必须window环境运行  
source命令中mysql环境运行  

mysql恢复:source e:\mydb1.sql回车  
注意:恢复时,[先创建数据库]并使用该数据库,[再]通过source命令,因为sql文件中[只有]表信息,[无]数据库信息  

创建一个users表,包含id/username/password/birthday/salary  
create table if not exists users(  
    id int(4),  
    username varchar(10),  
    password varchar(6),  
    birthday datetime,  
    salary float(6,2)  
);  


float(6,2)表示:2表示显示时小数点后最多几位,超过2位,四舍五入;  
                6表示整数和小数最多几位,整数部份最多(6-2)位  
以上表中的所有字段都允许为NULL,且默认NULL  
如果数据显示出来是乱码,按如下步骤:  

向users表中插入一条记录,先英后中(无法插入中文)  
insert into users values(1,'jack','123456','2015-8-8 8:8:8',9999.99);  
insert into users values(2,'哈哈','123456','2015-9-9 9:9:9',9999.99);  
insert into users values(3,'呵呵','123456','2015-7-7 7:7:7',7777.77);  

查询users表的结构  
desc users;  

创建employees表------------------------------用employees.sql表  

在上面员工表的基本上增加一个image列  
alter table employees  
add image blob;  
alter table employees add image blob;  

企业中,不是真真正正存照片本身,而是存照片路径,即E:/zgl.jpg  

修改name列,使其长度为60  
alter table employees  
modify name varchar(60);  

alter table employees  
modify name varchar(60);  

删除image列  
alter table employees  
drop column image;  

alter table employees  
drop column image;  
如果表中有内容的话,删除或添加某列,不影响其它列的信息  

表名employees改为staff  
rename table employees to staff;  
rename table employees to staff;  

修改表的字符集为GBK  
alter table staff  
character set UTF8/GBK/GB2312;  

列名name修改为username  
alter table staff  
change column name username varchar(60);  
alter table staff  
change column name username varchar(60);  

向staff表中插入数据  
insert into staff values(3,'赵君','男','2005-1-1',3333.33,'2005-7-1','这是备注信息');  

显示插入NULL  
insert into employees values(1,'哈哈','男','2015-1-1',1111.11,'2015-5-5','这是备注信息',NULL);  

隐式插入NULL  
insert into employees(id,name,sex,birthday,salary,resume)   
values(2,'呵呵','男','2015-1-1',2222.22,'这是备注信息');  

如果存在表就删除表  
drop table if exists staff;//表不在了  
drop table if exists staff;  

truncate table users;//表还在,只不过没有内容了  

再重新创建表  
create table staff(  
  id int(5),  
  name varchar(6),  
  sal int(5)  
);  
insert into staff values(1,'哈哈',7000);  
insert into staff values(2,'呵呵',8000);  
insert into staff values(3,'嘻嘻',9000);  
insert into staff values(4,'明明',10000);  
insert into staff(id,name,sal) values(4,'星星',6000);  
insert into staff(name,id,sal) values('月月',5,6000);  
----------------------------------------------------------------------------------------------------------------  
将所有员工薪水修改为10000元  
update staff set sal=10000;  
update staff set sal=10000;  

以上就是SQL语句的威力,如果发送命令,MySQL数据库服务器自行会解析其命令,并做相对应的过程处理,这个  
过程处理对程序员来讲,是封闭的,看不见。  
SQL的全称【结构化查询语句】,第四代计算机语言  
第一代:机器语言,即01010010100100101  
第二代:汇编语言,即用一个代号去表示10101010这些数字  
第三代:高级语言,即c/c++/vb/java/c#/...  
第四代:命令语言,即SQL  
第五代:智能语言,。。。  

将姓名为'哈哈'的员工薪水修改为11000元  
update staff set sal=11000 where name = '哈哈';  
update staff set sal=11000 where name = '哈哈';  

将月月的薪水在原有基础上增加1000元  
update staff set sal = sal + 1000 where name = '月月';  
update staff set sal = sal+1000 where name = '月月';  

删除表中3号的记录  
delete from staff where id = 3;  
delete from staff where id = 3;  

删除表中所有记录  
delete from staff;  
delete它是一行一行删除,速慢较【慢 】          drop table staff;整个表都被删除了  

使用truncate删除表中记录  
truncate它是整表删除后再重构,速慢较【快】。但是它的表结构还在,知识表内容没了  
---------------------------------------------------------------------------------------------------------------  
students表  
use mydb1;  
drop table if exists students;  
create table if not exists students(  
    id int(5),  
    name varchar(20),  
    chinese int(5),  
    english int(5),  
    math int(5)  
);  

insert into students(id,name,chinese,english,math) values(1,'张小明',89,78,90);  
insert into students(id,name,chinese,english,math) values(2,'李进',67,98,56);  
insert into students(id,name,chinese,english,math) values(3,'王五',87,78,77);  
insert into students(id,name,chinese,english,math) values(4,'李一',88,98,90);  
insert into students(id,name,chinese,english,math) values(5,'李来财',82,84,67);  
insert into students(id,name,chinese,english,math) values(6,'张进宝',55,85,45);  
insert into students(id,name,chinese,english,math) values(7,'黄蓉',85,75,80);  
insert into students(id,name,chinese,english,math) values(8,'张一李',75,65,30);  
insert into students(id,name,chinese,english,math) values(9,'何李',75,65,30);  
insert into students(id,name,chinese,english,math) values(10,'单',75,65,30);  
insert into students(id,name,chinese,english,math) values(11,'李',75,65,NULL);  
insert into students(id,name,chinese,english,math) values(12,'jack',75,65,40);  
insert into students(id,name,chinese,english,math) values(13,'marry',75,65,60);  

查询表中所有学生的信息,*表示所有字段,顺序与表结构相同------------------------------用students.sql表  
select id,name,chinese,math,english from students;  
select name,id,chinese,math,english from students;  
select name,chinese,math,english,id from students;  
select * from students;  
*号虽然写好起来方便,但充满不确定因素,要慎用  

查询表中所有学生的姓名和对应的英语成绩  
select name,english from students;  
select name,english from students;  

过滤表中重复语文成绩distinct(区别的)放在单列名前面,可以对该列名重复你元素进行筛选,仅仅保留一个  
select distinct chinese from students;  
select distinct chinese from students;  

在所有学生分数上加10分特长分  
select name,chinese+10,math+10,english+10 from students;  
select name,chinese+10,math+10,english+10 from students;  
NULL与任何数值进行运算,结果为NULL  

统计每个学生的总分  
select name,chinese+math+english from students;  
select name,chinese+math+english from students;  

**使用(别名)表示学生分数,注意使用别名加上双引号  
select name "姓名",chinese+math+english "总分" from students;  
select name "姓名",chinese+math+english "总分" from students;  

查询姓名为'张小明'的学生成绩    注意字符串用单引号'' 条件用where  
select id,name,chinese,math,english from students where name = '张小明';  
select * from students where name = '张小明';  

查询英语成绩大于90分的同学  
select id,name,chinese,math,english   
from students   
where english > 90;  
select * from students where english>90;  

查询总分大于200分的所有同学  
select id,name,chinese,math,english   
from students   
where chinese+math+english > 200;  

查询数学分数为89或者90或者91的同学  
方式一:  
select name,math  
from students  
where (math=89) or (math=91) or (math=90);  

方式二:(推荐)  
select name,math  
from students  
where math in (91,89,90,100,10000);//即使加上一些不存在的值也没问题  

查询英语分数在 8090之间的同学,包含8090  
方式一:  
select name,english  
from students  
where (english>=80) and (english<=90);  

方式二:  
select name,english  
from students  
where english between 80 and 90;  
select name,english from students where english between 80 and 90;  

查询所有姓'李'的学生成绩,%表示0或多个字符(模糊查询)  
select name,english,math,english  
from students  
where name like '李%';  
select * from students where name like '李%';  
=表示精确比较  
模糊比较,like关键字  

查询所有名'李'的学生成绩  
select name,english,math,english  
from students  
where name like '%李';  

查询所有姓名中包含’李’的学生成绩  
select name,english,math,english  
from students  
where name like '%李%';  

以下三条SQL都表示查询表中的[所有]记录  
select name,english,math,english  
from students  
where name like '%';  

select name,english,math,english  
from students  
where name like '%%';  

select name,english,math,english  
from students  
where name like '%%%';  

查询所有姓'李'的学生成绩,但姓名必须是三个字符,_表示1个字符  
select *  
from students  
where name like '李__';  
select * from students where name like '李__';  

查询数学分>80且语文分>80的同学  
select *  
from students   
where 1=1 and   
(math>80) and   
(chinese>80);  

select * from students where 1=1 and (math>80) and (chinese>80);  
---------------------------------------------  
对数学成绩排序(降序)后输出  
select id,name,math  
from students  
order by math desc;  
select id,name,math from students order by math desc;  
desc表示降序,排序字段用数值型  

select id,name,math  
from students  
order by math asc;  
asc表示升序,不写asc和desc默认是升序  

**对总分排序(降序)后输出  
SELECT name "姓名",math+chinese+english "总分"  
From students  
order by (math+chinese+english) desc;  
select name "姓名",math+chinese+english "总分" from students order by (math+chinese+english) desc;  

扩展:通常<order by>后面可以跟如下内容:  
1)字段  
   order by math desc  
2)表达式  
   order by (math+chinese+english) desc  
3)别名(这个别名可以不加引号)  
   select name "姓名",math+chinese+english "总分"  
   from students  
   order by 总分 desc;  
4)列号,即列在select中出现的位置,从1开始排序  
   select name "姓名",math+chinese+english "总分"  
   from students  
   order by 2 desc;  

对姓'李'的学生总分排序(降序)输出  
select name "姓名",(chinese+math+english) "总分"  
from students  
where name like '李%'  
order by 2 desc;  
--  
select name "姓名",(chinese+math+english) "总分" from students where name like "李%" order by 2 desc;  

查询数学分为NULL的学生  
select name,math  
from students  
where math is null;  
select name,math from students where math is null;  

select name,math  
from students  
where math is not null;  

还有not in,not between and,is not  
-------------------------------------------------------------------------------------------------------  
统计函数:统计函数会把null值得排除掉  
统计一个班级共有多少学生  
select count(*) "总人数"  
from students;  
select count(*) "总人数" from students;  

不提倡用*号,而用非NULL唯一列,即id(主健)  
select count(id)  
from students;  
select count(id) from students;  

select count(math)  
from students;  
建议不要统计含有NULL的列值  

统计数学成绩大于80的学生有多少个  
select count(id)  
from students  
where math>80;  
select count(id) from students where math>80;  

统计总分大于250的人数有多少  
select count(id)  
from students  
where (math+chinese+english)>250;  
-----  
统计一个班级数学总成绩  
select sum(math) "数学总成绩"  
from students;  
select sum(math) "数学总成绩" from students;  

统计一个班级语文、英语、数学各科的总成绩  
select sum(math) "数学总成绩",sum(english) "英语总成绩",sum(chinese) "语文总成绩"  
from students;  

统计一个班级语文、英语、数学的成绩总和  
select sum(chinese+math+english) "总成绩"  
from students;  
------  
统计一个班级语文成绩平均分  
select avg(chinese) "语文平均分"  
from students;  
select avg(chinese) "语文平均分"  
from students;  

求一个班级数学平均分  
select avg(math) "数学平均分"  
from students;  

求一个班级总分平均分  
select avg(math+chinese+english) "班级总分平均分"  
from students;  

求班级最高分和最低数学分数  
select max(math) "数学最高分",min(math) "数学最低分"  
from students;  

回顾:  
count()   统计总数  
sum()     求和  
avg()     平均  
max()     最值  
min()  

-------------------------------------------------------------------------------------------------------  

orders表:  
drop table if exists orders;  
create table if not exists orders(  
    o_id int,  
    o_product varchar(20),  
    o_price int  
);  

insert into orders values(1,'电视',900);  
insert into orders values(2,'洗衣机',100);  
insert into orders values(3,'洗衣粉',90);  
insert into orders values(4,'桔子',10);  
insert into orders values(5,'洗衣粉',80);  



对订单表中商品归类后,显示每一类商品的总价------------------------------用orders.sql表  
select o_product "商品",sum(o_price) "总价"  
from orders   
group by o_product;  

查询购买了几类商品,并且每类总价大于100的商品,即分类后,还要过滤  
select o_product "商品",sum(o_price) "总价"  
from orders   
group by o_product  
having sum(o_price) > 100;  

小结:  
where:  
行过滤器  
针对原始记录,即没有分组的记录  
可以不出现  
通常出现在where后面  
先执行where  

having:  
组过滤器  
针对分组后的记录  
可以不出现  
通过出现在group by 后面  
后执行having  

查询购买了几类商品,并且每类总价大于100的商品,同时按照总价的降序排列  
select o_product "商品",sum(o_price) "总价"  
from orders   
group by o_product  
having sum(o_price) > 100  
order by 2 desc;  

小结:  
select子句  
from 子句  
where子句  
group by子句  
having子句  
order by子句  

在MySQL数据库服务器中,上面的所有子句,哪个是必须写的呢?  
答:select  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql常用sql语句
本文主要介绍基本的 SQL 语句,数据的增删查改等。
73 0
MySQL系列专题(2)-MySQL的SQL语句和高级特性
MySQL系列专题(2)-MySQL的SQL语句和高级特性
93 0
MySQL关于SQL语句的一些题目
MySQL关于SQL语句的一些题目 1、用一条SQL语句查询出每门课都大于80的学生的姓名 (1)SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) > 80 (2)SELECT DISTINCT NAME FROM score WH.
1537 0
MySQL的常用SQL语句
修改密码 这是常见的大家一般都要用的   首先安装成功了打开cmd –> mysql -u root -p –>输入你的密码 –>修改mysql root用户密码    格式:mysql> set password for 用户名@localhost = password(‘新密码’);    举例:mysql> set password for root@localhost = password(‘root’); 上面例子将用户root的密码更改为root; 如果是学习使用一般密码该简单些,如root,123等,这样便于记忆。
1590 0
MySQL特有的SQL语句 第一弹
关于SQL,我们总是会有无穷无尽相关的话题,有时候碰到了一些觉得不错的SQL功能会标记下来,好记性不烂烂笔头,回头来看,自己也收集了不少的点子,但是从整体来看,可能仅仅是一瓢水而已。
1194 0
利用tcpdump抓取mysql sql语句
这个脚本是我之前在网上无意间找个一个利用tcpdump 抓包工具获取mysql流量,并通过过滤把sql 语句输入。 脚本不是很长,但是效果很好。 #!/bin/bash #this script used montor mysql network traffic.
997 0
+关注
hello熊本
热衷技术,热爱生活的熊本同学!
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
MySQL 5.7让优化更轻松
立即下载