mysql数据库基本语句类型-CSDN博客

本文链接：https://blog.csdn.net/hjgdtykn/article/details/140073028

创建数据库

create database name;

创建一个使用gbk字符集的myab2数据库

create database myab2 character set gbk;  //character set 指定字符集

创建一个使用utf8字符集的mydb3数据库

create datebase mydb3 character set utf8;

修改数据库
查看服务器中的数据库，并把其中某一个库的字符集修改为utf8。

alter database mydb2 character set utf8;

创建数据表mydb

create table mydb(
    id int not null comment'编号',       //comment创建表时添加注释内容将其保存到表结构中
    name varchar(32),
    price  int,
);

查看数据表mydb

show tables mydb;

查看数据表mydb中含有good的数据表

show tables like '%good%';

删除数据表mydb

drop table mydb;

desc 数据表名 //查看所有字段的信息

desc student;

desc 数据表名字段名； //查看指定字段信息

desc student id;

查看创建数据表的具体SQL语句，含字符编码 | 查看定义语句

show create table 表名;

删除数据表

drop table 数据表1 ，数据表2；

修改表结构

alter table  数据表名 change 旧字段名 新字段名 字段类型；
alter table student change age us int;

修改指定字段名

alter table 数据表名 modify 字段名 新类型;
alter table student modify us varchar(10);

新增字段名
格式1：新增一个字段，并可指定位置

alter table 数据表名 add 新字段名 字段类型；
alter table student add my int;

格式2：同时新增多个字段

alter table 数据表名 add (新字段名1，字段类型，新字段名2，字段类型)
alter atble student add (num int,name int)

删除字段

alter table 数据表名 drop 字段名；
alter table student drop us;

修改表名
格式1：

alter table 旧表名 rename 新表名；
alter table student_u rename student;

格式2：

rename table 旧表名 to 新表名；
rename table student_u to student;

创建默认值约束

create table Class(
    age int default 18
);

创建检查约束

check //指定某一列可接受的值 例如学生表中出生日期必须大于1986年1月1日，性别列只有男女两个值
create table Class(
    性别 char(2) check(性别='男'or 性别='女'）
);

--------------------------------------------------------------
定义主键
可以在创建表的后面添加主键
表的完整性约束

primary key(字段名1，字段名2)
create table my_primary_1{
    studentID char(11),
    courseID char(4),
    score tinyint unsigned,
    primary key(studentID,courseID)
);

也可以在列的后面添加主键
列的完整性约束

primary key(字段名1，字段名2)
create table my_primary_1{
    studentID char(11),
    courseID char(4),
    score tinyint unsigned,
    primary key(studentID,courseID)
);

修改创建好的表的主键约束

alter table my_primary_2 add primary key(studentID,courseID);

--------------------------------------------------------------
创建唯一约束

方法1：
列的完整性约束：

unique
create table my_unique_1(
    id int unsigned primary key,
    username varchar(10) unique//唯一约束
);

方法2：
表的完整性约束：

unique(字段名1，字段名2)
create table my_unique_2(
    studentID char(11) not null,
    courseID char(4) not null,
    nuique(student,courseID)
);

修改创建好的表的唯一约束

alter table my_unique_2 add unique(studentID,courseID);

1.表中只能有一个primary key(主键)，但unique(唯一键)可以有多个
2.primary key的各个列必须声明为 not null; 而unique的各个列可以声明为null值，且可以接受多个null值

--------------------------------------------------------------
自增约束AUTO_INCREMENT //auto_increment

create table my_atuo(
    id int primary key auto_increment,
    username varchar(20)
);

修改已经创建好的表的自增约束

alter table my_atuo
modify 字段 值 primary key atuo_increment;

--------------------------------------------------------------
外键的创建
先建父表

create table num1(
    id int primary key,
    name varchar(50) not null
);

再建子表

create table num2(
    id int primary key atuo_increment//自增约束
    name varchar(20) noy null,
    dept_id int not null,
    foreign key(dept_id) references num1(id)//子表的dept_id字段跟父表的id字段建立
);

创建好的表添加外键

alter table num2 add foreign key(dept_id) references num1(id);

----------------------------------------------------------------------------------------------------------------------------
插入数据
完整格式：insert into 表名（字段名1，字段名2） values(值1，值2)；
简化格式：insert into 表名 values(值1，值2，null,null)；
省略格式：insert into 表名（字段名1，字段名2） values(值1，值2)；

----------------------------------------------------------------------------------------------------------------------------

修改数据
基本格式
update 表名
set 字段名1 = 值1，字段名2 = 值2
where 条件表达式；

无条件格式
update 表名
set 字段名1 = 值1

例题1：
将作者是钱江言的书名由“计算方法”改为“数值计算”
update tbl_bookinfo
set bookname='数值计算'
where author='钱江言'；
----------------------------------------------------------------------------------------------------------------------------
删除数据
基本格式
delete from 表名
where 条件表达式；

无条件格式
delete from 表名；

清空表
truncate 表名；

删除表tbl_bookinfo中姓名为李四的记录
delete from tbl_bookinfo
where author='李四'；

例1：
删除书名以“机”字结束的所有图书
delete from book
where bookName like '%机';

例2：
删除姓名第二字是“宁”且年龄大于20的学生。
delete from stu
where sno like'_宁%' and age>20;
----------------------------------------------------------------------------------------------------------------------------
基础查询
select *|{字段名1，字段名2，字段名3}
from 表名

[例1]从TBL_Bookinfo 表中查询所有图书的记录内容
select * from TBL_Bookinfo;

[例2]查询TBL_Bookinfo 表中所有图书的BookID,ISBN,BookName
select BookID,ISBN,BookName from TBL_Bookinfo;

[例3]查询TBL_Bookinfo 表中所有图书的BookID,ISBN，BookName
方法1：select BookID 索取号,ISBN 标准编号,BookName 书名
from TBL_Bookinfo;

方法2：select BookID as 索取号，ISBN as 标准编号，BookName as 书名
from TBL_Bookinfo;
----------------------------------------------------------------------------------------------------------------------------

1.条件查询
select *|{字段名1，字段名2，字段名3}
from 表名
where 条件表达式

[例1]在TBL_Bookinfo 表中查询所有"清华大学出版社"出版的书
select * from TBL_Bookinfo where Publisher='清华大学出版社'；

[例2]在TBL_Bookinfo 表查询95年及95年以后出版的书
select * from TBL_Bookinfo where PublisherDate>='19955-1-1';

2.比较条件语句
and       逻辑‘与’操作数全部为真，则结果为1，否则为0
or        逻辑 ‘或’操作数中只要有一个为真，则结果为1，否则为0
not        逻辑 ‘非’操作数为0，则结果为1，操作数为1，则结果为0

[例1]查询不是“清华大学出版社”出版的书
select * from TBL_Bookinfo where not Publisher='清华大学出版社'；

[例2]查询所有“清华大学出版社”或 “电子工业出版社”出版的书
select * from TBL_Bookinfo
where Publisher='清华大学出版社' or Publisher='电子工业出版社'；

[例3]在TBL_Bookinfo 表查询页数在200至400之间、同时出版社为“清华大学出版社”的书
select * from TBL_Bookinfo
where Pagecount>=200 and Pagecount<=400 and Publisher='清华大学出版社'

----------------------------------------------------------------------------------------------------------------------------
between 语句

between....and..... 在指定的闭区间范围内
not between....and.... 不在指定的闭区间范围内

[例1]从TBL_Bookinfo表中检索1990年出版的图书信息
select * from TBL_Bookinfo
where PublisherDate between '1990-01-01' and '1990-12-31';

----------------------------------------------------------------------------------------------------------------------------

in()关键字
in（）在给定的集合内
not in() 不在给定的集合内

[例1]从TBL_Bookinfo 表中检索出索取号为'TB11/46','TG/71','TG38-62/12'的图书信息
select * from TBL_Bookinfo
where BookID in('TB11/46','TG/71','TG38-62/12')；
#等价于
select * from TBL_Bookinfo
where BookID='TB11/46' or BookID='TG/71' or BookID='TG38-62/12'；

----------------------------------------------------------------------------------------------------------------------------
like关键字
like 匹配模式获取匹配到的数据（用于模糊匹配）
not like '匹配模式' 获取匹配不到的数据

在like关键字中常用的通配符：
%----代表任意多个字符
_(下划线) ---代表一个随意字符

[例1]从TBL_User表中检索出姓“李”的读者信息
select * from TBL_User
where UserName like '李%'；

[例2]从TBL_User表中检索出名字的第二个字是“文”或“明”的读者信息
select * from TBL_User
where UserName like '_文%' or UserName like '_明%'；

----------------------------------------------------------------------------------------------------------------------------

is null 关键字
is null 比较一个数据是否是NULL
is not nul 比较一个数据是否不是NULL

[例1]从TBL_User表中检索Email为空的读者信息
select * from TBL_User
where Email is null;

---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
select [distinct] *|{字段名1，字段名2}
from 表名
[where 条件表达式]
[group by 字段名{having 条件表达式}]
[order by 字段名[asc|desc]]
[limit 记录数]
注意：各子句位置和顺序
---------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
group by 子句
分组统计：在应用中，通常需要对数据按照某个或多个字段进行分组统计
[例1]从TBL_Bookinfo 表查询出各出版社的图书册数
select Publisher,count(*) from TBL_Bookinfo
group by Publisher;

[例2]查询各出版社最厚的书的页数
select Publisher,max(PageCount) from TBL_Bookinfo
group by Publisher;

[例3]显示各出版社在1990年以后出版的图书册数
select Publisher,count(*) from TBL_Bookinfo
where year(PageDate)>=1990
group by Publisher;
注意：group by 子句要放在where语句后面

多字段分组统计：按某个字段进行分组后，对已经分组的数据进行再次分组的操作
group by 字段1，字段2
[例4]从TBL_User表中统计每班男女人数
#从TBL_User表中统计各班的人数
select class 班级，count(*) 人数 from TBL_User
group by class;
#先按班级分组排序，班级相同再按性别分组排序
select class 班级,sex 性别,count(*) 人数 from TBL_User
group by class,sex;

group by 分组排序功能
分组排序：为分组字段进行升序或降序排序，默认升序排序
group by 字段名[asc|desc]

[例1]按图书分类编号分类汇总图书册数
select ClassID,count(*) from TBL_Bookinfo group by ClassID asc;
--------------------------------------------------------------
having子句
分组筛选：对查询的数据分组时，可以利用having子句对分组筛选

语法顺序：
select 查询列表 from 数据表名
[where 条件表达式]
group by 字段名 [asc|desc]
having 条件表达式;

[例1]从TBL_Bookinfo表查询出各出版社的图书册数在2册以上的图书册数
#1.分组统计
select Publisher,count(*) from TBL_Bookinfo
group by Publisher;
#2.分组筛选
select Publisher,count(*) from TBL_Bookinfo
group by Publisher
having count(*)>2;

having子句与where的区别
where子句的条件不可以使用聚合函数
having子句的条件可以使用聚合函数

[例1] 从TBL_Bookinfo 表查询出1990年以后各出版社的图书册数在2册以上的图书册数
selectPublisher,count(*) from TBL_Bookinfo
where year(PublishDate)>=1990
group by Publisher
having count(*)>2;

--------------------------------------------------------------

distinct关键字
distinct消除重复行只保留一条
[例1]在TBL_Bookinfo表中检索所有的出版社
1.select Publisher from TBL_Bookinfo;
2.select distinct Publisher from TBL_Bookinfo;

--------------------------------------------------------------

order by子句
order by 字段名[asc|desc]
说明：asc升序，desc降序，缺省值为升序

[例1]按出版日期的升序检索TBL_Bookinfo表的记录
select * from TBL_Bookinfo order by PublishDate asc;

[例2]按出版社的降序检索TBL_Bookinfo表的记录
select * from TBL_Bookinfo order by Publisher desc;

多字段排序
[例3]按出版社的降序和出版日期的升序检索TBL_Bookinfo表的记录
select * from TBL_Bookinfo
order by Publisher desc,PublishDate asc;

--------------------------------------------------------------

limit关键字
limit[],[]第一个空从0开始，到第二个空结束
[例1]查询借阅表中第6-10行记录
select * from TBL_Borrowinfo limit 5,5；

[例2]从TBL_Bookinfo表中检索出最厚的三本书的书名、出版社及页数
select BookName,Publisher,PageCount
from TBL_Bookinfo
order by PageCount desc
limit 3；

--------------------------------------------------------------
计算列
1.显示出版日期
select PublishDate from TBL_Bookinfo; #直接字段

2.显示出版日期的年份
select year(PublishDate)from TBL_Bookinfo;#计算列

3.计算图书至2022年的年限
select 2022-year(PublishDate) from TBL_Bookinfo;#计算列

4.计算图书至今的年限
select year(now())-year(PublishDate) from TBL_Bookinfo;#函数嵌套

--------------------------------------------------------------
聚合函数
count(字段名) 返回参数字段的数量，参数可以使用*，即count(*)
sum(字段名)   返回参数字段之和
avg(字段名)    返回参数字段的平均值
max(字段名 )   返回参数字段的最大值
min(字段名)   返回参数字段的最小值

[例1]从TBL_Bookinfo表中查询图书的总数
select count(*) from TBL_Bookinfo;#包含NULL
select count(bookid) from TBL_Bookinfo;#bookid为主键，非空，计算准确
select count(Publisher) from TBL_Bookinfo;#去除NULL,不准确
select count(*) as 总册数 from TBL_Bookinfo;#改变列标题

[例2]查询最厚、最薄的图书页数
select max(PageCount) from TBL_Bookinfo;#最厚
select min(PageCount) from TBL_Bookinfo;#最薄
select max(PageCount),min(PageCount) from TBL_Bookinfo;#合在一起
select max(PageCount)最大页数，min(PageCount)最小页数 from TBL_Bookinfo;

注意！在where子句中不能使用聚合函数如max,min,sum,avg.count