mysql基本使用方法(持续更新)

mysql replace函数的使用

select name ,replace(name,"a","b") from city;//从 city表中选择name列 ，然后将name列中字符‘a’换成‘b’；

运行结果是

其他的函数大家可以自行去看一下

 

这里是数据处理函数

 

 

日期和时间处理函数

 

下面再对rtrim() 以及 lrim() 进行说明

Select ID,NAME,COUNTRYCODE,DISTRICT,POPULATION 
FROM world.city where name like ltrim("a c%");// 删除字符串左边的空格，如果是rtrim()就是删除字符串右边的空格，为什么要进行这样的操作呢，因为有时候字符串左边或则右边有空格会导致你找不到相关的数据

这里对日期函数date() 介绍一下，注意date() 返回的是日期时间的日期部分

 

select *from sakila.film;//看一下sakila库的film表
select date(last_update) from sakila.film where date(last_update) = "2006-02-15";//选择film表中 last_update列 中日期部分是（2006-02-15）的部分。date函数返回的就是日期时间的日期部分

 

上面的日期时间是timestamp类型的

 

 

另外还有一些函数用于统计数据：

常用的avg/count/max/min/sum/distinct    

分别是 求平均值 ，求次数，求最大值，求最小值，求总和，求唯一值

下面看一下这里面的例子吧

select *from city;
select distinct countrycode from city;#注意一下distinct 只能放在select后面，对后面的所有列都产生影响alter
#函数是来返回唯一值的

#展现countrycode 里面有多少不同的值
select count(countrycode) from city;
select count(distinct countrycode) from city;
#后面的是后面子查询的内容
select count(countrycode) from (select countrycode ,district ,avg(population) as avg_popu, sum(population) as sum_popu 
from city group by countrycode,district) a where avg_popu >= 1000000;

select count(countrycode)
from city group by countrycode,district having avg(population) >= 1000000 ;
#返回的是分组之后每组的个数

区分一下count

select count(countrycode)
from city group by countrycode having avg(population) >= 1000000 ;

#*代表all
select count(*) 
from city group by countrycode having avg(population) >= 1000000 ;

select countrycode
from city group by countrycode having avg(population) >= 1000000 ;

use world;
select countrycode from city where countrycode in("GIN", "HKG", "SGP", "URY");

 

       

分组计算：

使用 group by 进行数据分组，对每个组应用函数。 使用having对组进行过滤（不能用where进行过滤了）

 

code：

select countrycode ,district ,avg(population) as avg_popu, sum(population) as sum_popu 
from city group by countrycode,district having avg(population) >= 1000000 and sum(population) >= 1000000;
#个人觉得和这个分组就是一个元组，以这个元组为依据进行划分，比如上面的这个例子元组为(countrycode,district)

分组之后每个组元唯一的，也就是说如果有多个组元是相同的话只显示一个。

 

 

子查询：

子查询（subquery） 嵌套在其他查询中的查询 嵌套的语句经常使用在where 或者 from 中的字句     

个人理解：

就是将原来的表city换成新的表进行查询

from 后面的子查询

#如果我们还可有对里面的数进行过滤的话就不能用where了，这里用到了having(对组进行过滤)

select countrycode ,district ,avg(population) as avg_popu, sum(population) as sum_popu 
from city group by countrycode,district having avg(population) >= 1000000 ;

#子查询的例子
#这里分组之后可以不用having而是直接where
select *
from
(select countrycode ,district ,avg(population) as avg_popu, sum(population) as sum_popu 
from city group by countrycode,district) a
where avg_popu >= 1000000;

where后面的子查询

select avg(population) from city group by CountryCode;#返回的是每个组的平均值，因此返回值有很多个
select avg(population) from city ;#返回的是city表中所有population的平均值，返回值只有一个
#where 后面的子查询 
#一定要一对一，一个population只能对应一个(select avg(population) from city)
select *from city where population >= (select avg(population) from city)
#下面的这个即是错的
select *from city where population >= (select avg(population) from city group by countrycode);
#这里会提示返回的子查询超过了一个

 

表联结

还有一种outjoin：表示的是table1和table2 所有的内容

 

注意下：select * from city(这是个表名)  =  select city.* from city  ，

 

#先插一个三个表联结
select a.*,b.*,c.*
from
(select *from city) a
left join
(select *from country LIMIT 100) b
on a.countrycode = b.code
left join
(select * from countrylanguage) c
on c.countrycode = a.countrycode;

#注意一下这俩个表联结的时候b.code 是有空值的，a表中a.countyrcode != b. code的值都是NULL
select count(*) ,count(b.code)
from
(select *from city) a
left join
(select *from country LIMIT 100) b
on a.countrycode = b.code;

 

 

组合查询：

将多个查询结果通过 union 进行组合 union 规则： 必须由两条或者两条以上的select语句组成，语句之间用union分隔 union中的每个查询必须包含相同的列，表达式或聚集函数 列数据类型必须兼容 union会自动对数据进行去重，如果不需要请使用 union all ，order by 只能用于最后一条select语句

 

如果上面的文字描述不是很详细，请看下面的图形描述：

select id, name from city where population >= 1000000
union
select id,name from city where population <= 100000

看上面的条数相加相等    

一定要注意select返回表的列一定要相同或者说列的数据必须一致

 

排序：

#要注意一下order by 必须要放到最后一行
select id, name from city where population >= 1000000
union
select id,name from city where population <= 100000
order by id asc

 

表的创建和操作：

利用 CREATE TABLE 创建表，必须给出下列信息： 新表的名字，在关键字CREATE TABLE 之后给出 表列的名字和定义，用逗号隔开

使用NULL值 在创建表的是否可以定义列允许或者不允许NULL值

主键 表中的一列或者多列可以成为主键，这些值或者组合必须唯一 PRIMARY KEY（col），主键不允许有NULL值

指定默认值 关键字DEFAULT可以对列进行默认值设置

#创建这个表，可以加上一个if not exists来判断表是否存在
create table if not exists world.xiaoming_test1
as#一定要有as但是如果是INSERT INTO就不需要as了
select a.name as name_city,a.population as popu_city,a.countrycode as code_city,a.district,a.id,b.*,c.*
from
(select *from city) a
left join
(select *from country LIMIT 100) b
on a.countrycode = b.code
left join
(select * from countrylanguage) c
on c.countrycode = a.countrycode;


#展现一下之前创建的表xiaoming_test1;
select * from xiaoming_test1 LIMIT 100; 


#删除表 用到了 drop table关键字,一定要规范化一下 if exists
drop table if exists world.xiaoming_test1

 

创建一个新表，不能用之前的表进行创建了，这里就需要指明列名字和数据类型

首先给出mysql里面的数据类型

    

 

创建一个表 id , name , language , countrycode ,gnp
create table if not exists world.xiaoming_text1
(id int,
countrycode char(5),
GNP float,
population int,
language char(15),
primary key (id)
);
#虽然创建好了但是还是空的
select * from xiaoming_text1;

 

 

 

插入查询结果：

注意：设置联合主键和主键 

只能有一个自增的列，而且一般来说该列都是主键  ，自增的列是

auto_increment

drop table if exists xiaoming_text1;
create table if not exists world.xiaoming_text1
(id int not null,#表示不能为null值
name char(40) null,#表示可以为null值
countrycode char(5),
GNP float,
population int,
language char(40),
primary key (id)#一个表中只能有一个主键，但是可以设置联合主键如下所示
primary key (id,countrycode ,name)
);

INSERT INTO world.xiaoming_text1
select a.id , a.name ,a.countrycode as code_city , b.gnp, a.population as popu_city ,c.language
from
(select *from city) a
inner join
(select * from  country LIMIT 100) b
on a.countrycode = b.code
inner join
(select * from countrylanguage) c
on c.countrycode = a.countrycode
group by id;#我们选取上面的表可以发现这样返回的id值是有重复的，主键是不允许有重复值的

 

 

 

NULL值：

NULL值不是空值也不是0，它是一种特殊的值 我们不能使用数学操作符对它进行操作，比如 > = 等 使用 is null / is not null null 值会被忽略计算 创建表的时候要注明是否允许列存储null值

 

#注意一下NULL值不是0
select * from xiaoming_test1 where code is not null LIMIT 100;
select * from xiaoming_test1 where code != null LIMIT 100;

 

 

最后一个更改表和删除表

 
ALTER TABLE 用来更新表结构，但是强烈建议在创建表的时候就仔细考虑表结构，不要轻易变动

格式：

add column char(20)

drop column DROP

TABLE 删除表

 

delete update 

和INSERT INTO一样 不需要写出关键字 table 

格式：

delete from

 

update 

set 

但是一般都不是很推荐使用这些函数  因为mysql一般都是不可逆的

#增加new_name
alter table world.xiaoming_text1
add new_name char(80)

select * from xiaoming_text1

#删除gnp这一列
alter table world.xiaoming_text1
drop gnp;


update world.xiaoming_text1
set new_name = 1
where id = 1;

delete from world.xiaoming_text1
where id = 2;

 

给表加上唯一性约束：

drop table if exists xiaoming_text1;
create table if not exists world.xiaoming_text1
(id int not null auto_increment,
name char(40) not null,
countrycode char(5),
GNP float,
population int,
language char(40),
primary key (id)
);
alter table xiaoming_text1
add unique(name);

show columns from world.xiaoming_text1