MySql语句初步整理

自己整理的MySql语句，方便查询

/*关键词 DISTINCT 用于返回唯一不同的值*/
select distinct ID from city where CountryCode="afg" and District="Kabol";
select distinct District from city where ID=1803 ;
select distinct District from city where ID=120 or CountryCode="afg";
select CountryCode,ID,Population from city order by ID ;
insert into city (ID, Name, CountryCode, District, Population) values (61,"Yjiawei","alb","CHINESE",698797);
update city set District = "Hadnse" where ID=129;
update city set District = "Hadnse",Population=1234 where ID=62;
delete from city where ID=61;
update wei set wei.statue =null;
alter table wei drop country;
/* delete删除指定的ID=61这一行数据
drop 删除表的country这一项，删除表头
update 跟新某一列 或者某一行的数据，不删除表头*/

select * from city where ID not between 61 and 70 limit 5;
select * from city where Name like '%y' limit 5;
select * from city where Name like "_y%" limit 5;
select * from city where Name in ("Yjiawei","York");
select Name as N ,CountryCode as CC from city ;
select c.CountryCode from city as c where c.Name="Yjiawei";
select city.Name,country.Name,Country.Region from city,country where city.CountryCode=country.Code limit 5;

/*在表中存在至少一个匹配时，INNER JOIN 关键字返回行;
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，
即使在右表 (table_name2) 中没有匹配的行。
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，
即使在左表 (table_name1) 中没有匹配的行。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集*/
select city.Name,country.Name,country.Region from city inner join country on city.CountryCode = country.Code limit 5;
select city.Name,country.Name,country.Region from city left join country on city.CountryCode = "alb";
select Name from city union  select Name from country;
create table tb_stu1 select  * from city;
create database jia;
drop database userdb;
drop table tb_stu1;
create table  jia (
  id int(4) not null auto_increment,
  name varchar(5) not null ,
  sex varchar(5) default 'man',
  age int(2),
  primary key (id),
  unique (name),
  foreign key (name) references wei(name)
);
insert into  jia (name, sex, age)  values ("dong","man",23);
create table wei(
  name varchar(5) not null ,
  statue varchar(5),
  primary key (name)
);
/*ALTER TABLE 语句用于在已有的表中添加、修改或删除列 */
delete from jia where name="xiao";
alter table jia alter sex set default "male";
insert into wei values ("xiao","good");
insert into  jia (name, sex, age)  values ("dong","man",23);
/*CREATE INDEX 语句用于在表中创建索引*/
create index JiaIndex on jia(name);
truncate table wei;
alter table jia drop index JiaIndex;
alter table wei add Birthday datetime;
update wei set Birthday=date_format (now(),"%Y-%m-%d %H:%i:%s");
alter table wei drop Birthday;
alter table wei modify column Birthday year;
/*default 2018 默认约束Birthday 为2018
修改字段编码格式
alter table <表名> change <字段名> <字段名> <类型> character set utf8;
增加字段
ALTER TABLE table_name ADD field_name field_type
*/
alter table wei add country varchar(20) default "china";
alter table wei drop country;
alter table wei alter Birthday set default 2018;
alter table wei change statue statue varchar(10) character set utf8;

select * from jia where sex is  not null ;
/*ROUND 函数用于把数值字段舍入为指定的小数位数
AVG 函数返回数值列的平均值
COUNT() 函数返回匹配指定条件的行数
增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用
MID 函数用于从文本字段中提取字符*/
select round(avg(Population),2) from city;
select count(District) from city;
select max(Population) from city;
select min(Population) from city;
select sum(Population) from city;
select CountryCode,sum(Population) from city group by CountryCode having sum(Population)<10000;
select distinct mid(city.CountryCode,1,2) from city;
select distinct CountryCode,length(city.CountryCode) from city;
select jia.id,now() from jia order by id;
create table user (
  id int(10) not null auto_increment,
  name varchar(255) not null ,
  primary key (id)
)engine =innodb auto_increment=2 default charset =utf8;
/*ENGINE=InnoDB使用innodb存储引擎,MySQL中用到
DEFAULT CHARSET=utf8 数据库默认编码为utf-8
AUTO_INCREMENT=1 自增键的起始序号为1*/
insert into user(name) values ("jia");
alter table user change name name varchar(20) character set utf8;
create database if not exists userdb;
drop database if exists userdb;
create table if not exists ok(account varchar(20),password varchar(20));
drop table if exists ok;
/*设置user表的name为唯一值*/
alter table user add unique(name);