SQL 常用语句大全

#############本地连接MySQL的语法：快捷键 command+R CMD ###########
# mysql -uroot -p 回车可以隐藏密码 u为用户名，P为密码；
#  Enter password: ******
############# 加IP地址连接MySQL的语法：##########
# mysql -h ip地址 -P 3306 -uroot -p 123456 (本地IP地址表达方式： localhost=127.0.0.1 );
############# 退出MySQL########
#mysql>exist/quit;

######## DDL #################
————————————————创建数据库—
create database if not exists db_lianjing charset utf8;# create database [其他] 数据库名 [设置编码格式];
# 查看有哪些数据库
show databases;
# 查看数据库的创建语句
show create database db_lianjing;#show create database 数据库名;
#当需要切换及使用另一个数据库时,务必切换至所需数据库；
use db_lianjing;#use 数据库名;
#删除数据库的语法
drop database db_lianjing;#drop database 数据库名称;
#查看当前正在使用的数据库名
select database();
#————————————————————创建数据表—————————————————
#创建数据表：create table 表名(
  #字段名1 数据类型(长度) [约束],
  #字段名2 数据类型(长度) [约束],
#  字段名3 数据类型(长度) [约束],
#  ...
# );
use db_lianjing;
create table if not exists tb_lianjing(
   id int primary key auto_increment, #主键自动增长，当给字段添加自动增长后，可以不给自动增长列添加值，且会设定自动整数值。
   name varchar(20),
   gender varchar(10)
) engine =InnoDB default charset utf8;
#查看所有数据表
show tables ;#查看所有数据表；
# 查看数据表的创建语句
show create table tb_lianjing;#show create table 表名;
# 查看表结构
desc tb_lianjing;# #desc 表名;
#删除数据表语法：
drop table tb_lianjing;#drop table 表名;
#修改数据表 alter table 表名 add/modify/change/drop 字段名 类型(长度);rename 旧表名 to 新表名;
#添加一列 alter table 表名 add 字段名 类型(长度) [first|after 其他字段名称];
alter table  tb_lianjing add hobby varchar(20);
alter table  tb_lianjing add strange varchar(20) first;
alter table  tb_lianjing add age int after name;
#修改列类型 alter table 表名 modify 要修改的字段名 类型(长度) [约束];   # 不为空
alter table  tb_lianjing modify strange char(10);
#修改列名 alter table 表名 change 旧列名 新列名 类型(长度) [约束];当旧列名被修改后，则无法使用旧列名来操作数据。
alter table tb_lianjing change strange competiter varchar(20);
#删除一列 alter table 表名 drop 列名;
alter table tb_lianjing drop competiter;
#修改表名 rename table 表名 to 新表名;
rename table tb_lianjing to tb_newlianjing;
#alter tbale tb_lianjing rename to tb_newlianjing;可运行，但不推荐；

############## DML ################
use db_lianjing;
#插入(insert)数据，常见语法：#insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);
insert into tb_newlianjing values (5,'ass','male','love',10); #如所有字段都赋值，字段名可省略；可一次性插入多条数据之间使用,（逗号）分隔。
insert into tb_newlianjing (id, name, gender) VALUES (1,'lianjing','female');#1）当要给字符串类型的字段插入值时，要使用单引号把值引起来，否则显示出错；
insert into tb_newlianjing (name, gender) values ('duli','male');##表名后字段个数与类型等，要与values后的值对应，字段名顺序可调节。
#修改(update)数据，常见语法：#update 表名 set 字段名1=值,字段名2=值,字段名3=值,... where 条件;
update tb_newlianjing set name= 'zhuzuh',gender ='male' where id=4;#如不指定where条件，则该字段下所有数据均被修改；
#删除(delete)数据，常见语法：#delete from 表名 where 条件;
delete from tb_newlianjing where id=4; #如不添加where 条件语句，则整张表的数据均被清除。（表头字段的DDL语句，不会受到影响）；
#truncate清空数据；truncate  [table] 表名;不可以带条件删除，适用于删除销毁大量无效数据，原理是删除表格再创建一张新的空白表格，自增序列会清零。
truncate tb_newlianjing;
################## 设定引擎 ##########################
# MySQL引擎常用的为INnoDB（事务型，大数据开发常用，行锁），MyISAM（注重性能，不能处理事务型命令，表锁）
# create table 表名(
#     字段名 数据类型(长度) [约束],
#     ...
# # ) engine = 引擎名 default charset utf8;
###################### SQL约束 #############################

# 1.主键   （ primary key ） MySQL建议所有表的主键字段都叫id，类型为int。
# 2.非空   （ not null ） 此字段不允许填写空值。
# 3.唯一   （ unique  ） 此字段的值不允许重复。
# 4.默认值  （ default ） 当不填写字段对应的值时，会使用默认值；如果填写时，则以填写为准。
# 5.外键   （ foreign key ） 涉及到多张表时才会使用。<br/>对表与表关系字段进行约束，当为表与表关系字段填写值时，会到关联的表中查询此值是否存在。<br>如果存在，则填写成功；如果不存在，则填写失败。
# create table 表名(
#  字段名 数据类型(长度) primary key [其他约束],
#  ...
# ) engine = 引擎名 default charset utf8;
# 使用alter table关键字删除主键
alter table tb_newlianjing drop primary key;#删除主键约束后，该字段依旧有非空约束，不能为空。

##################  DQL #############################select [*|字段名1,字段名2,...|函数(...)] from 表名 where 条件 另外的要求;
#简单查询 select [distinct] 字段名1,字段名2,... from 表名; select * from 表名：查看所有字段的数据。
select * from tb_newlianjing;
select name from tb_newlianjing;
select  name ,gender from tb_newlianjing;
select distinct  gender from tb_newlianjing;#distinct 去除重复数据；
# 取外号/别名 字段名 as 别名
SELECT name AS NA FROM tb_newlianjing;
SELECT name NA FROM tb_newlianjing; #as可省略；
#比较查询 select [*|字段名1, 字段名2, ...] from 表名 where 条件; 比较运算符有>,<,=,>=,<=;不等于：!= 或 <>;
select * from tb_newlianjing where  id=3;
select name from tb_newlianjing where id >2;
#范围查询
# 1.in是用于非连续值的范围查询，语法：select * from 表名 where 字段名 in (范围值1,范围值2,...);
select * from tb_newlianjing where  id in (1,2);
# 2. between and 是用于值在连续范围的查询，语法：select * from 表名 where 字段名 between 范围值1 and 范围值2; 闭区间，包含值1和值2；
select * from tb_newlianjing where id between 1 and 3;
#逻辑查询 select [*|字段名1, 字段名2, ...] from 表名 where 条件; and 和/且， or 或，not 非；
select name from tb_newlianjing where id=3 or gender = 'female';
select  name, id from tb_newlianjing where  not (id =3); #增加括号可提升代码阅读性
#模糊查询 select * from 表名 where 字段名 like '%某个字%'; 或 select * from 表名 where 字段名 like '某个字_';
select * from tb_newlianjing where name like '%a%';#%代表通配符，是替代任意多个字符。
select * from tb_newlianjing where gender like 'f_____';#_下划线表示一个字符，位置和数量均会影响查询结果。
#非空查询 select * from 表名 where 字段名 [条件]; is null 判断为空; is not null 判断为非空;
select  * from tb_newlianjing where id is null;
#排序查询 select * from 表名 where 条件 order by 字段名 [asc|desc];(asc 可省略， 默认升序。desc 降序）
select * from tb_newlianjing order by id desc;
select  * from tb_newlianjing where gender='female' order by id;
#聚合查询 select 函数(...) from 表名 [where 条件];注意：聚合函数会忽略空值进行统计处理。
#  count(col) | 用于统计指定列不为null的总数据条数。
#  sum(col)   | 用于计算指定列的数值和。
#  max(col)   | 用于计算指定列的最大值。
#  min(col)   | 用于计算指定列的最小值。
#  avg(col)   | 用于计算指定列的平均值。
select sum(id) from tb_newlianjing where gender='female';
select  count(*) from tb_newlianjing;
# limit分页查询 #select 字段名1,字段名2,... from 表名 limit M, N;M为整数，表示从第几条索引开始，默认值是0；N为整数，表示查询多少条数据。
select * from tb_newlianjing order by id desc limit 2;
select * from tb_newlianjing order by id asc limit 0, 2;
# 分组查询 将查询结果按照指定字段分组，其中，数值相等的可划分为同一组。select 字段名1,字段名2,... from 表名 group by 分组字段名 [having 分组条件];
select  gender from person group by  gender;
#使用group by + 聚合函数进行分组查询 select 字段名1,字段名2,聚合函数(...) from 表名 group by 分组字段名;
select  gender, avg (age) from person group by  gender;
# select 字段名1,字段名2,... from 表名 group by 分组字段名 having 分组条件;
select gender,count(*) from person  group by  gender having gender='female';#having 优先级最低，where 放在 group by 前面，优先级高于group by。
select gender,count(*) from person  group by  gender having count(gender)>2;

############################################################################################
create database consumer;
use consumer;
# 创建数据表
create table if not exists person
(
   id     int primary key auto_increment not null,
   name   varchar(18),
   age    int,
   gender varchar(10),
   height double
)engine = InnoDB default charset utf8;
# 插入数据
insert into person(name, age, gender, height) values ('洪七公', 76, 'Male', 1.6);
insert into person(name, age, gender, height) values ('欧阳峰', 68, 'Male', 1.65);
insert into person(name, age, gender, height) values ('郭靖', 43, 'Male', 1.7);
insert into person(name, age, gender, height) values ('黄蓉', 39, 'Female', 1.59);
insert into person(name, age, gender, height) values ('杨过', 26, 'Male', 1.8);
insert into person(name, age, gender, height) values ('小龙女', 33, 'Female', 1.65);
insert into person(name, age, gender, height) values('郭芙', 18, 'Female', 1.60);
insert into person(name, age, gender, height) values ('李莫愁', 35, 'Female', 1.65);
# 查询数据
select * from person;
select  gender from person group by  gender;
select  sum(height), avg (age) from person group by  gender;
select gender,count(*) from person  where  gender='female' group by  gender;
select gender,count(*) from person  group by  gender having count(gender)>2;

###################### 外键约束   #############################
# 两个有关系的表，可以通过了某个字段来建立连接，这个字段在第一张表A中是主键primary key，在第二张表B中，就称为外键约束foreign key。
# create table 表名(
#  ...
#  字段名 数据类型(长度) [约束],
#  foreign key (字段名) references 另外表名(主键列名)
# ) engine = InnoDB default charset utf8;
create table if not exists tb_product(
   pid int primary key auto_increment,
   age  int ,
   name varchar(20),
   person_id int,
   foreign key (person_id) references person(id) #外键命名一般为 主键表名_id 字段类型需要与主键完全一致。需提前将两个关联表格创建好。
)engine =InnoDB default  charset utf8;#设置引擎为InnoDB,否则可能无法成功设定外键约束。

# 通过外部添加外键 alter table 表A add constraint xxxfk foreign key (外键字段名) references 另外表名(主键字段名);
alter  table tb_product add foreign key (person_id) references person (id);

# 当数据表之间产生关联后，需要注意：
# （1）不能给表B（外键）添加一个表A不存在的主键字段值；
# （2）不能删除表B（外键）已使用表A存在的主键字段值。
#########################  子查询 #########################################
#子查询指的是将一条select语句的结果，作为另一条select语句的一部分（如：查询条件、查询结果、表等），来进行数据查询。
# select *|字段名1,字段名2,.. from 表名 where 字段名 = (另外select语句的结果);
select * from category where cid=(select category_id from product where pname='格力');
# 注释快捷键：ctrl+/