mysql数据库基础SQL语句总结篇

常用的sql增删改查语句

创建数据库：create database db_name character set utf8;
删除数据库：drop database db_name;
切换数据库：use dbname
创建表：create table student(id int primary key comment'这里写注释',name varchar(20),sex char(1) ,address varchar(20));
删除表：drop table tb_name1,tb_name2;
写入数据：全字段写入
insert into student values(001,'刘亦菲','女','湖北武汉'),(002,'杨幂','女','北京')；
写入数据：可选字段写入，其他字段自增或有默认值
insert into student(name,address) values('刘亦菲','湖北'),('杨幂',,'北京'),('刘诗诗','北京');
删除数据：delete from tbname where 条件，只删数据，不删结构
delete from student where id=003;
修改数据：update student set key=value,key2=value2 where 条件
update student set address='湖北' where id=001;
查看数据：select 字段1，字段2，.. from tbname；
select * from student;
查看表的结构：desc tbname；
修改表名：alter table tb_name rename to tb_name_new;
修改字段(包括名称，类型，约束)：alter table tb_name change column_name column_name_new 数据类型 约束;
例：alter table student change sex 性别 char(1) not null default '女';
修改字段类型：alter table tb_name modify column_name 修改后的字段类型；
添加字段：alter table 表名 add 字段名 数据类型 约束 字段位置(first,或者after xxx);
例：alter table tb_name add column_name decimal not null after cno;第一个则用first
删除字段：alter table tb_name drop column_name；
修改字段的默认值：alter table tb_name alter column_name set Default 666;
删除字段的默认值：alter table tb_name alter column_name drop Default;
查看建表命令：show create table tbname；内含表的编码格式
修改数据库编码格式：alter database <数据库名> character set utf8;
修改表的编码格式：alter table 表名 convert to character set utf8;

+----+-----------+-----+---------+
| id | name | sex | address |
+----+-----------+-----+---------+
| 1 | 刘亦菲 | 女 | 湖北 |
| 2 | 杨幂 | 女 | 北京 |
| 3 | 刘诗诗 | 女 | 北京 |
+----+-----------+-----+---------+

建表约束

主键约束

primary key，主键，物理上存储的顺序，唯一且非空，能够用它唯一确定一张表中的一条记录。能提高检索速度。给id添加主键约束：
create table student(
id int primary key ,
name varchar(20),
sex char(1) ,
address varchar(20));

复合主键

或称联合主键，在一张表中用多个字段作为主键，给id和name添加主键约束：
create table student(
id int ,
name varchar(20),
sex char(1) ,
address varchar(20)
primary key(id,name));
此时，表中的id字段可相同，name可相同，但是id和name不能同时相同。

删除主键：
alter table student drop primary key;
当一个表没有主键时，添加主键：
alter table student add primary key(id);
alter table student add primary key(id,name);
修改字段的方式添加主键：
alter table student modify id int primary key;

自增约束

一般让id自增，让当前最大值加1
create table student(
id int primary key auto_increment,
name varchar(20));
删除已有的自增：不会影响主键的存在，添加自增也一样
alter table student modify id int;

注意：

1.给不是非空的字段加上主键后会变为非空，再删除主键，该字段仍会保留非空状态。
2.在删除主键之前要先删除主键字段的自增，否则报错。
3.char(1)里面的1表示一个字符而不是一个字节，一个中文和一个英文字母都会被当做1个字符。varchar也一样。
4.mysql中的插入语句insert into tbname value/values,其实用value和values均可，但是在插入数据量很大的时候建议用
value效率会更高。
5.空值""和 null 还是有区别的，在mysql中，空值不占空间，null 会占空间，当字段为 not null 时，可以插入空值，
但是不可以插入null。
6.在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。
7.当使用 is not null 或者 is null 时，只能查出字段中为 not null 的或者为 null 的，不能查出空值。
8.判断空字符用 =''或者<>''来进行处理。

唯一约束

约束修饰的字段的值不可以重复
create table student(
id int primary key auto_increment,
name varchar(20) unique);
create table student(
id int primary key auto_increment,
name varchar(20) unique(id,name));
上面第一个是给name添加唯一，第二个是给id和name同时添加唯一，这会形成类似于复合主键的复合唯一约束，只要id和name加起来是唯一的就是可以的。
给存在的表添加唯一约束：
alter table student add unique(name)
删除唯一约束：
alter table student drop index 字段名;
alter table student modify name varchar(20);
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| sex | char(1) | YES | | NULL | |
| address | varchar(20) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+

非空约束

not Null
create table student(
id int primary key auto_increment,
name varchar(20) not null);

添加或删除已有的非空约束：还是通过modify来实现
alter table student modify address varchar(20);

默认约束

default
create table student(
id int primary key auto_increment,
name varchar(20),
sex char(1) Default '女');
删除已有的默认约束：也还是通过modify来实现
alter table student modify sex char(1);

外键约束

foreign key
外键是表的一个特殊字段，被参照的表示主表，外键所在字段的表为副表，
外键的作用是建立该表与主表的关联关系，主表中的字段被副表关联引用
外键的参照列必须具有相似的数据类型，数字的长度和是否有符号位必须相同，字符的长度可以不痛
比如，班级表和学生表，当删除班级表里的某个班级时，学生表里那个班级的学生还是存在的，显然这是
不合理的，班级都解散了学生也就不在了。所以有必要将班级表设为主表，学生表设为副表，形成外键约束，
这要当你要删除班级表里的某个班级时就不行了，除非你先删除副表学生表里的那个班级的学生。即解散班级前
你先安置好你的学生。
还是上面这个例子，当没有设置外键的时候，你在学生表里插入一条数据，说他的班级为312，但是班级表里的班级
为301-310共十个班，但是这条数据还是可以插入，显然这是一条垃圾数据。有了外键约束之后这种垃圾数据就不能被插入了。
create table classes(
id int primary key auto_increment,
name varchar(20) not null);

insert into classes value (1,'高301班'),(2,'高302班'),(3,'高303班');

create table students(
id int primary key auto_increment,
name varchar(20) not null,
class_id int,
constraint stu_fk_cla foreign key(class_id) references classes(id));

insert into students value (1001,'zww',1);
insert into students value (1002,'wy',1);
insert into students value (1003,'zyq',1);
insert into students value (1004,'lzx',1);

此时无法删除主表中的id=1的这行数据，因为被副表引用了：Cannot delete or update a parent row: a foreign key
删除外键：
alter table student drop foreign key stu_fk_cla;
添加外键：
alter table student add constraint stu_fk_cla foreign key(class_id) references classes(id);
上面提到有了外键约束之后不能随便删除或者更新主表，但是可以通过cascade串联实现。
cascade:从主表删除或更新且自动删除或更新副表中匹配的行。
用法：在创建外键语句的后面加 on delete/update cascade
constraint stu_fk_cla foreign key(class_id) references classes(id) on delete cascade on update cascade;
也可以通过 set null，实现从主表删除或者更新，对应的副表的外键列变为null，这就要求该列没有被指定 not 右表记录不足的地方均为NULL。
用法：在创建外键语句的后面加 on delete/update set null
以上数据表的存储引擎均为INNODB。

关系型数据库设计三大范式

第一范式：要求数据库表的每一列都是不可分割的原子数据项。
第二范式：在第一范式的基础上，需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
第三范式：在前面的基础上，需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
简而言之，第一范式的关键词是列的原子性，第二范式的关键词是不能包含部分依赖，第三范式的关键词是不能包含传递依赖。

数据查询

学生表：学号，姓名，性别，出生日期，所在班级
create table student(
sno int primary key,
sname varchar(20) not null,
ssex char(1) Default '女',
sbir date,
class varchar(20)
);

insert into student value(101,'lyf','女','1996-01-02',309);
insert into student value(102,'hqj','男','1995-01-02',309);
insert into student value(103,'lzx','女','1997-01-02',309);
insert into student value(104,'cb','女','1994-01-02',309);
insert into student value(105,'gyy','女','1993-01-02',309);
insert into student value(106,'wy','男','1996-01-02',309);
insert into student value(107,'wjh','男','1996-01-02',309);
insert into student value(108,'swy','女','1995-01-02',309);
insert into student value(109,'lp','男','1995-01-02',308);
insert into student value(110,'lp1','男','1995-01-03',308);
insert into student value(111,'lp2','男','1996-01-02',307);
insert into student value(112,'zww','女','1994-01-02',302);
insert into student value(113,'zzq','女','1994-01-03',301);

教师表：教师编号，教师姓名，教师性别，出生日期，职称，所在部门
create table teacher(
tno int primary key,
tname varchar(20) not null,
tsex char(1) Default '女',
tbir datetime,
prof varchar(20) not null,
depart varchar(20) not null
);

课程表：课程号，课程名称，教师编号
create table course(
cno int primary key,
cname varchar(20) not null,
tno int not null,
foreign key(tno) references teacher(tno)
);

成绩表：学号，课程号，成绩
create table grade(
sno int primary key,
cno int not null,
score decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);

lemon：
member 用户表：
create table member(
Id int primary key auto_increment,
RegName varchar(50) not null comment'用户名',
Pwd varchar(50) not null comment'密码',
MobilePhone varchar(20) not null comment'手机号码',
Type int not null comment'1 普通会员 2 内部员工 5 测试用户',
LeaveAmount decimal(18,2) not null comment'可用余额',
RegTime datetime not null comment'注册时间'
);

loan表:
create table loan(
Id int primary key auto_increment,
MemberID int not null comment'用户Id',
Title varchar(50) not null comment'标题',
Amount decimal(18,2) not null comment'借款金额',
LoanRate decimal(3,1) not null comment'年利率，如年化18.0%，存储为18.0',
LoanTerm tinyint(1) not null comment'借款期限，如6个月为6，30天为30',
LoanDateType tinyint(1) not null comment'借款期限类型，借款期限单位，0-按月，2-按天，4-按周',
BiddingDays tinyint(1) not null comment'竞标天数',
CreateTime datetime not null comment'创建时间',
BiddingStartTime datetime comment'竞标开始时间',
FullTime datetime comment'满标时间',
Status tinyint(1) comment'状态 1：审核中 2：二审(初审中) 3：三审(复审中) 4：竞标中 5：核保审批 6：平台终审 7：还款中 8：审核不通过，9：流标，10：还款完成，11：申请流标'

);

invest表：投资记录表
create table invest(
Id int primary key auto_increment not null,
MemberID int not null comment'用户Id',
LoanId int not null comment'标Id',
Amount decimal(18,2) not null comment'投资金额',
CreateTime datetime not null comment'创建时间',
IsValid tinyint(1) not null comment'是否有限：0 无效，1 有效'
);

repayment表： 还款计划表

create table repayment(
Id int primary key auto_increment,
InvestId int not null comment'投资id',
CreateTime datetime comment'创建时间',
Terms tinyint(1) not null comment'回款期次，如6表示第六期回款',
UnfinishedPrincipal decimal(18,2) not null comment'待还本金',
UnfinishedInterest decimal(18,2) not null comment'待还利息',
RepaymentDate datetime not null comment'回款日期',
ActualRepaymentDate datetime comment'实际回款日期',
Status tinyint(1) not null comment'回款状态，0-未还，1-部分已还，2-全额已还，3-作废'

);

financelog表：流水记录表
create table financelog(
Id int primary key auto_increment,
CreateTime datetime not null comment'实际回款日期',
PayMemberId int not null comment'支付用户Id',
IncomeMemberId int not null comment'进账用户id',
Amount decimal(18,2) not null comment'交易金额',
IncomeMemberMoney decimal(18,2) )

as：给表和字段取别名，可省略
select s.id,s.name from student as s;
select id as 编号,name as 姓名 from student;
select s.id 编号,s.name 姓名 from student s;

范围查询

asc升序，desc降序，默认升序
select xx from xx order by xx asc(desc);
select * from student order by id desc,age asc;
select * from student order by rand();随机排序

指定集合

in(not in)
查询时，条件字段的值存在于某个数据集，数据集也可是子查询得到的数据
select xx from tb_name where 条件字段 in (数据集)
select * from member where id in (1,2,3,4,5);
select * from member where id not in (1,2,3,4,5);

指定范围

between...and...
包含边界值,取反not between...and...
select * from member where id between 1 and 10;

条件查询

where
判断是否等于null，用<=>,is null ,is not null
select * from pet where age<=>null;
select * from pet where death is null;

模糊查询

like 条件字段的值不完整的时候，%用来匹配任意字段的字符串
select * from member where RegName like '小%'；以小开头的
select * from member where RegName like '%子'；以子结尾的
select * from member where RegName like '%小%'；带有小的，不限位置
select * from member where RegName like '_'；一个字符的
select * from member where RegName like '_a%'；第二个字符为a的

聚合函数

count(),sum(),avg(),min(),max() 计数，求和，平均，最小，最大,不统计null
select *,avg(字段) from tb_name;

分组查询

group by
分组显示的是该组的第一条数据，若要知道分组后的组内成员详情，则用group_concat(字段名)
select id,group_concat(name) from student group by sex;
+----+--------------------+
| id | group_concat(name) |
+----+--------------------+
| 2 | wy |
| 1 | wenwen,lzx,lyf,lhl |
+----+--------------------+
select ssex,group_concat(sname),count(*),sum(sno) from student group by ssex

+------+---------------------+----------+----------+
| ssex | group_concat(sname) | count(*) | sum(sno) |
+------+---------------------+----------+----------+
| 女 | lyf | 1 | 101 |
| 男 | hqj | 1 | 102 |
| 女 | lzx,cb,gyy | 3 | 312 |
| 男 | wy,wjh | 2 | 213 |
| 女 | swy | 1 | 108 |
| 男 | lp,lp1,lp2 | 3 | 330 |
| 女 | zww,zzq,,kkk,cjj | 5 | 570 |
+------+---------------------+----------+----------+
select * from student group by sex,address;多字段分组
与聚合函数配合：
例子：查询 MemberId<10 的用户的总投资额。
select member.id,member.Regname,sum(Amount)
from invest,member
where member.id = invest.MemberID
group by invest.MemberID
having member.id<10;
where 是对from后面的表进行数据筛选，having是对group by 的结果进行过滤。

去重查询

distinct 去除查询结果中的重复数据
select distinct MemberID from invest;

分页查询：limit
select * from tb_name limit m,n;
m指的是索引值从m开始，n表示每页取n条数据。
limit 15,10；从第15条数据开始，每页取10条；
select * from member limit 5; 取前5条数据

多表连接

内连接：tb_a inner join tb_b on 条件
例：学生表和成绩表，查学生语文成绩
select s.sname,s.class,s.ssex,sc.chinese
from student as s
inner join score as sc
on s.sno=sc.id
group by ssex
having chinese>125
order by s.sno
limit 2;

外连接：分为左连接和右连接
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。
右表记录不足的地方均为NULL。
右连接则刚好相反。

联合查询

union 和 union all
将多个表的查询结果整合到一个列，union去重，union all 不去重。
select sno from student union select chinese from score;
注意，每个表查询的字段个数要一致。

子查询：将一个查询语句嵌套在另一个查询语句中，内存查询结果作为外层查询的条件。先执行括号内的查询语句。
引发情况：in,not in,比较运算符，exists，not exists等
select * from student where class_id in(select id from class);
select * from student where class_id >(select id from class where ssum=90);
select * from student where exists (select id from class where ssum>190); 判断存在
select * from student where class_id >any(select id from class); 大于最大值
select * from student where class_id >all(select id from class); 大于最小值

正则表达式查询

regular expression，注意与模糊查询like对比
^ 匹配字符串开始的部分：
select * from student where sname regexp '^z';
$ 匹配字符串结尾的部分：
select * from student where sname regexp 'z$';
. 代表字符串中的任意一个字符，包括回车与换行
select * from student where sname regexp 'z..w';
[字符集合] 匹配字符集合中任何一个字符
select * from student where sname regexp '[mno]';
select * from student where sname regexp '[a-k]';
[^字符集合] 匹配不是全部以字符集合中字符组成的
select * from student where sname regexp '[^mno]'; mlxg,mnoq,nol都可以，mnmn，nnn不行，
s1|s2|s3 匹配s1，s2，s3中任意一个字符串
select * from student where sname regexp 'zn|mn|dc'; znm,kdc
s{n} 指定字符串出现几次,不必相连
select * from student where sname regexp 'mn{2}'; mnmna
select * from student where sname regexp 'q{3}'; qqq
s{n,m} 指定字符串最少出现n次，最多出现m次
select * from student where sname regexp 'n{1,3}'; mnad，nbnanj,

运算符

算术运算符：+ - * / div 除，% mod 取余
select 1+2,3-4,5*6,8/3,8/0,8 div 3,5%2,5 mod 2,1+null,;

+-----+-----+-----+--------+------+---------+------+---------+--------+
| 1+2 | 3-4 | 5*6 | 8/3 | 8/0 | 8 div 3 | 5%2 | 5 mod 2 | 1+null |
+-----+-----+-----+--------+------+---------+------+---------+--------+
| 3 | -1 | 30 | 2.6667 | NULL | 2 | 1 | 1 | NULL |
+-----+-----+-----+--------+------+---------+------+---------+--------+

比较运算符：=,<>,!=, >, <, <=,is null,is not null,between and,in,not in,like,not like...

select 2=2,2='2',2=3,1=null,2 in (1,2,3),3 is not null;

+-----+-------+-----+--------+--------------+---------------+
| 2=2 | 2='2' | 2=3 | 1=null | 2 in (1,2,3) | 3 is not null |
+-----+-------+-----+--------+--------------+---------------+
| 1 | 1 | 0 | NULL | 1 | 1 |
+-----+-------+-----+--------+--------------+---------------+
select class ,class<=>null from student;
逻辑运算符：&& ||
select 2&&3,2&&0,2&&null,2||4,2||0,2||null,0||null,1!,0!,null!;

+------+------+---------+------+------+---------+---------+----+----+-------+
| 2&&3 | 2&&0 | 2&&null | 2||4 | 2||0 | 2||null | 0||null | !1 | !0 | !null |
+------+------+---------+------+------+---------+---------+----+----+-------+
| 1 | 0 | NULL | 1 | 1 | 1 | NULL | 0 | 1 | NULL |
+------+------+---------+------+------+---------+---------+----+----+-------+

函数

数学函数库：ceil()，floor()，round(),mod，power(),abs(),pi(),rand(),rand(x)
select ceil(3.14),floor(3.14),round(3.141592,2);
+------------+-------------+-------------------+
| ceil(3.14) | floor(3.14) | round(3.141592,2) |
+------------+-------------+-------------------+
| 4 | 3 | 3.14 |
+------------+-------------+-------------------+
select 3 mod 4,power(2,3),abs(-12),pi(),rand();
+---------+------------+----------+----------+--------------------+
| 3 mod 4 | power(2,3) | abs(-12) | pi() | rand() |
+---------+------------+----------+----------+--------------------+
| 3 | 8 | 12 | 3.141593 | 0.6086010379796858 |
+---------+------------+----------+----------+--------------------+
字符串函数库：
char_length(s) 返回字符串的字符数
length(s) 返回字符串的长度
concat(s1,s2..) 将字符串合并为一个字符串
concat_ws(x,s1,s2) 指定分隔符连接字符串
upper(s) 将字符串转换为大写
lower(s) 将字符串转换为小写
left(s,n) 返回字符串的前n个字符
right(s,n) 返回字符串的后n个字符
trim(s) 去掉字符串两端的空格
ltrim(s) / rtrim(s) 去左右空格
trim(s1 from s) 去掉字符串s中开始和结尾处的字符串s1
repeat(s,n) 重复字符串n次
space(n) 返回n个空格
select char_length('wen'),char_length('雯'),length('wen'),length('雯');
+--------------------+--------------------+---------------+---------------+
| char_length('wen') | char_length('雯') | length('wen') | length('雯') |
+--------------------+--------------------+---------------+---------------+
| 3 | 1 | 3 | 3 |
+--------------------+--------------------+---------------+---------------+

select concat('wy','zww','lyf'),upper('wenwen'),left('wen',2);
+--------------------------+-----------------+---------------+
| concat('wy','zww','lyf') | upper('wenwen') | left('wen',2) |
+--------------------------+-----------------+---------------+
| wyzwwlyf | WENWEN | we |
+--------------------------+-----------------+---------------+
select concat_ws('^_^','wy','wen');

+-----------------------------+
| concat_ws('^_^','wy','wen') |
+-----------------------------+
| wy^_^wen |
+-----------------------------+

select concat('_',trim(' adc '),'_'),concat('_',ltrim(' adc '),'_'),concat('_',rtrim(' adc '),'_');

-------------------+-----------------+-----------------+
| _adc_ | _adc _ | _ adc_ |
+------------------+-----------------------------------+
select trim('a' from 'abcdeaa'),repeat('wen',2),concat('_',space(3),'_');
+--------------------------+-----------------+--------------------------+
| trim('a' from 'abcdeaa') | repeat('wen',2) | concat('_',space(3),'_') |
+--------------------------+-----------------+--------------------------+
| bcde | wenwen | _ _ |
+--------------------------+-----------------+--------------------------+

日期时间函数：
curdate() 当前日期
curtime() 当前时间
now() 当前日期和时间
month(now())
monthname(now())
dayname(now())
week(now()) 一年中第几周
year(now())
hour(now())
minute(now())
second(now())
datediff(d1,d2) 计算自己活了多少天了

select datediff(curdate(),'1993-04-19');
+----------------------------------+
| datediff(curdate(),'1993-04-19') |
+----------------------------------+
| 9712 |
+----------------------------------+

未完待续。。。