MYSQL基本语法字典

SQL语法

1、SQL语句不区分大小写
2、每一条SQL语句以；结尾
3、每个SQL关键字以空格隔开
4、SQL语句中可以无限制换行：有空格的地方可以换行，结尾可以换行在接；

DML数据库操作语句

• 查询数据库列表：show databases ;
• 创建数据库:creat database <数据库名> 或者 create database if not exists <数据库名>还可以在其后指定字符集：create database db_test character set utf8;
• 查询创建数据库的SQL语句：show create database < db_name>;
• 修改数据库字符集：alter database < db_name> character set utf8;
• 删除数据库会删除数据库中的所有数据表以及表中数据：drop database if exists < db_name>;
• 切换数据库 use < db name>;

DDL数据表操作

• 创建数据表：create table < tb_name> (有x列就有x-1个逗号分隔，然后写每列名字、类型，占的字节、约束：必填、不能相同) 例如：create table students (stu_num char(8) not null unique,stu_name varchar(6) not null,stu_age int);
• 查询表：show < tb name>;
• 查询表结构：desc < tb name>;
• 删除同数据库
• 修改表数据：alter table tbname rename to new_tbname;
• 添加字段/列：alter table tbname add stu_remark varchar(100);
• 修改字段和列的类型：alter table tbname change < old colnumu> < new colnumu>< new type>
• 只修改字段类型：alter table tbname modify < colnumu>< new type>
• 删除字段：alter table tbname drop <字段名>
• 添加数据：insert into < tbname>(字段1，字段2，…（可以不跟表中字段顺序一样，但是不能空的一定要给数据）value(数据1，数据2…)(跟前面的字段顺序一致，如果字段省略就和表中字段顺序一致))
• 删除数据：delete from < tb name> where condition 例如：delete from stus where stu_age = 21操作是从stus表中删除字段stu_age为21的数据；delete from stus where stu_num = ‘202005621’;如果不要where就是删除表中所有数据
• 修改数据：update < tb name> set <字段名1>=value1 <字段名2>=value2 where …例如：update stus set stu_num = ‘202005621’ where stu_num=‘202005622’;语句把学号202005622的改成学号202005621，如果不写where则把整列都改成202005621

DQL数据查询语言

1、单表查询

• select < 字段1>，< 字段2> from < tb name> where …;从表中查询该字段满足要求的行，如果要查询所有列可用*代替字段,例如:select * form tbname where…
• where 操作符：
• 等于 =
• 不等于 !=或者<>
• like 模糊查询，结合正则表达式使用如where stu_name like '%o%'查询stu_name中带有o的
• 其中 ‘%’ 表示任意多个字符 '_'表示任意一个字符
• 查询处理：计算列:select 2022-stu_age from stus;
• as 给字段取别名 select stu_age as 年龄，2022-stu_age as 出生年月 form tbname where…
• distinct 消除重复记录 select distinct stu_age as 年龄 form tbname where…
• order by 字段名（asc/desc）例如select stu_age as 年龄，2022-stu_age as 出生年月 form tbname where… order by stu_age desc;按年龄降序排列
• 多字段排序：select stu_age as 年龄，2022-stu_age as 出生年月 form tbname where… order by stu_gender desc,stu_age asc;先按性别排序，同性别的按照年龄升序
• 统计函数count(字段)，统计符合体条件的字段的数目
• max(字段)查询满足条件的字段的最大值；min()同
• sum()求和函数
• avg()求平均值
• now()获取当前日期，为日期形式赋值时能用字符串’yyyy-mm-dd hh-mm-mm’,也可用now()或者sysdate()
• curtime()只获取时间
• concat()连接字符串
• upper()将字段列变为大写
• lower()将字段列变为小写
• substring(字段，start,len)截取字段的一部分
• 分组查询：select * from < tbname> where … group by (…) having count(stu_num)>1 order by age：先按where筛选后进行分组，分组后按having的条件进行筛选count>1的才显示，最后按照age排序
• 分页查询：limite（num1，num2）表示：从num1开始查询num2条数据。如果用pagenum表示页码，pagesize表示每页显示条数，则分页查询通用写法：select * from tbname where … limite (pagenum-1)*pagesize,pagesize;

2、多表联合查询

• 一对一
• 一对多
• 多对一
  添加外键约束：

create database db_text;//创建数据库
use db_text;//切换数据库
create table classes (
class_id int primary key auto_increment,
class_name varchar(40) not null unique,
class_remark varchar(200)
);//创建被关联的表并添加字段
insert into classes (class_name,class_remark) value('java01','...');
insert into classes (class_name,class_remark) value('java02','...');
insert into classes (class_name,class_remark) value('java03','...');
insert into classes (class_name,class_remark) value('java04','...');
create table students(
stu_num varchar(9) not null unique,
stu_name varchar(20) not null unique,
stu_gender char(2) not null,
stu_age int not null,
cid int,//添加外键约束
constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) value('202005621','刘备','男',22,2);//添加数据，cid只能是class_id里面有的数据，否则会出错，在控制台操作注意字符编码问题查看字符编码命令：satatus;改编码：set names gbk;
//另一种方式：
create table students(
stu_num varchar(9) not null unique,
stu_name varchar(20) not null unique,
stu_gender char(2) not null,
stu_age int not null,
cid int
);//先创建后设置外键约束
alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id);
//删除外键约束方法
alter table students drop foreign key FK_STUDENTS_CLASSES;

• 多对多

修改已关联的数据：如上案例改班级id1改为5

• 把主动依赖的那个表的数据置null

update students set cid = null where cid = 1;

• 把被动依赖的数据改了

update classes set class_id = 5 where class_name = 'java01';

• 再改主动依赖

update students set cid = 5 where cid is null;

这样改太麻烦，于是可以利用级联操作：

alter table students drop foreign key FK_STUDENTS_CLASSES;
alter table students add constraint FK_STUDENTS_CLASSES foreign key (cid) references classes(class_id) NO UPDATE CASCADE NO DELETE CASCADE;

这样就可以同步修改：

多表连接查询

内连接inner join

结果：只获取两张表中匹配成立的，如果一张表中得数据在另一张表里没有相匹配得数据则不会出现在结果中

语法：
select * from tablename1 inner join tablename2;

但是会产生很多无意义数据，怎样过滤无意义数据：
1、使用where设置条件：

select * from tablename1 inner join tablename2 where tablename1.class_id = tablename2.cid;

但这个是先生成，再过滤，效率低
2、一般使用on设置匹配条件

select * from tablename1 inner join tablename2 on tablename1.class_id = tablename2.cid;

左连接

结果：显示左边表中的所有数据，有跟右表匹配的数据则显示右表数据，没有则显示null

select * from lefttable left join righttable on lefttable.class_id = righttable.cid;

右连接

结果：显示右边表中的所有数据，有跟左表匹配的数据则显示左表数据，没有则显示null

select * from lefttable right join righttable on lefttable.class_id = righttable.cid;

数据表别名

在连接查询时如果各表中有相同的字段名，要用表明.字段来区分，有的表名太长，不便于程序编写，可以使用在表明空格后接一个别名

select * from lefttable ltb right join righttable rtb on ltb.class_id = rtb.cid;

子查询/嵌套查询

在第一次查询的结果基础上再查询第二次。
案例：只知道班级名字，不知道编号，第一次返回结果只有一个用=

select * from students where cid = (select * from classes where class_name = 'java2001');

union关键字：将多个查询语句的结果整合在一个结果中
案例：查一种班级，班级有几个，即第一次返回结果有单列多行用IN

select * from students where cid IN (select * from classes where class_name LIKE 'java%');

子查询返回多行多列

select * from (select * from students where cid = 1) cc where cc.stu_gender = '男';//临时表必须有别名

也可以用多条件查询：
select * from students where cid = 1 and stu_gender = ‘男’；