mysql_select * from students where age>18;select id, nam-CSDN博客

本文链接：https://blog.csdn.net/weixin_52260178/article/details/121343006

数据库

一、数据库基础

1.字段（列）；记录（行）；主键（唯一标记某字段）

2.数据类型

（1）使用原则：够用就行，尽量使用取值范围小的。

（2）常用数据类型

整数：int,bit(有符号signed 无符号unsigned)
小数：decimal（decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位）
字符串：varchar（varchar表示可变长度的字符串，如varchar(3)如果填充’ab’时就会存储’ab’）

char（char表示固定长度的字符串，如char(3)如果填充’ab’时会补一个空格为’ab '）

text(字符串text表示存储大文本，当字符大于4000时推荐使用)
日期时间：date,time,datetime
枚举类型：enum（枚举中下标从1开始，可用数字来表示枚举的类型）（对于图片，音频，视频等文件，不存储在数据库中，而是上传到某服务器上，然后在表中存储这个文件的保存路径）

3.约束

(1)主键（primary key: 物理上存储的顺序（主键字段可以用0 null default 来占位（自动增长））

(2)非空（not null：此字段不允许写空值

(3)唯一（unique：此字段的值不允许重复

(4)默认（default：当不填写此值时会使用默认值，如果填写时以填写为准

(5)外键（foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常（虽然外键约束可以保证数据的有效性，但在进行数据的crud（增加，修改，删除查询）时会降低数据库的性能，不推荐使用，保证数据有效性可以在逻辑层进行控制）

4.MySQL存储引擎：MyISAM InnoDB(两种类型主要差别是InnoDB支持事务处理与外键和行级锁，而MyISAM不支持)

5.curd：增删改查

create创建

update更新

retrieve读取

delete删除

二、数据库基本操作

链接数据库

mysql -uroot -p(密码);

退出数据库

exit/quit/ctrl+d;

显示数据库版本

select version();

显示时间

select now();

查看所有数据库

show databases;

创建数据库

create database 数据库名 charset=utf8;

查看创建数据库的语句

show create database 数据库名;

删除数据库

drop database 数据库名;
若数据库名中含有特殊字符，加Tab键上边的 ` 键
drop database `数据库名`;

使用数据库

use 数据库名;

查看当前使用的数据库

select database();

三、数据表的基本操作

查看数据库中所有表

show tables;

创建表

–auto-increment 表示自自动增长
–not null 表示不能为空
–primary key 表示主键
–default 默认值

create table 数据表名字(字段 类型 约束[,字段 类型 约束]);
create table xxxxx(id int, name varchar(30));
create table yyyyy(id int primary key not null auto_increment, name varchar(30));
create table zzzzz(
	id int primary key not null auto_increment primary key,
	name varchar(30)
);
create table students(
	id int unsigned not null auto_increment primary key,
	name varchar(30),
	age tinyint unsigned default 0,
	high decimal(5,2),
	gender enum("男","女","保密")default "保密",
	cls_id int unsigned
);
insert into students values(0, "老王", 18, 188.88, "男", 0);
insert into studentsvalues(default, "小李", 20, 170.55, 1, 1);  性别处写1，2，3可用来表示相应位置的性别
select * from student;

查看数据表结构

desc 数据表名字

查看表的创建语句

show create table 表名字;

删除表

drop table 表名;

四、修改数据表

添加字段

alter table 表名 add 列名 类型;
alter table students add birthday datetime;

添加字段—重命名

alter table 表名 change 原名 新名 类型及约束;
alter table students change birthday birth datetime not null;
alter table students change birthday birth date default "2000-01-01";

修改字段—不重命名

alter table 表名 modify 列名 类型及约束;
alter table students modify birth date not null;

五、增删改查

1.增加

(1)全列插入

insert into 表名 values(...);
insert into classes values(0, "班级"); 若为指明插入字段则每个字段都要写上插入的值
insert into students values(default,"西施",20,"女",1,"1990-01-01"),(default,"王昭君",20,"女",1,"1990-01-01");

(2)部分插入

insert into 表名 (列1,...) values (值1);
insert into students(name, gender) values("大乔",2);
insert into students(name, gender) values("大乔", 2),("貂蝉", 2);

2.修改

update 表名 set 列1=值1, 列2=值2... where 条件;
update students set gender=2 where name="小乔";
update students set gender=2 where id=3;  条件要唯一就where id
update students set age=22,gender=2 where id=3;
update students set gender=1;  全部都改

3.查询

(1)查询所有列

select * from 表名;

(2)指定条件查询

select * from 表名 where 条件;
select * from students where name="小乔";
select * from students where id=4;

(3)查询指定列

select 列1，列2,... from 表名;
select name,gender from students;

(4)可用as为字段或表指定别名

select 字段[as 别名], 字段[as, 别名] from 数据表 where ...;
select name as 姓名, gender as 性别 from students;

(5)修改字段顺序(哪个字段写前面先显示哪个字段)

select id as 序号, gender as 性别, name as 姓名 from students;  显示顺序： id gender name
select name as 姓名, gender as 性别 from students;  显示顺序： name, gender

4.删除

(1)物理删除

delete from 表名 where 条件;
delete from stidents;  没有条件，整个数据表中的所有数据都被删除
delete from students where name="小乔";

(2)逻辑删除(用一个字段来表示这条信息是否已经不能再用了)

alter table students add is_delete bit default 0;
update students set is_delete=1 where id=6;

六、MYSQL查询

1.查询

(1)查询所有字段

select * from 表名;

(2)查询指定字段

select 列1,列2,... from 表名;

(3)使用as给字段起别名

select 字段 as 名字... from 表名;
select 表名.字段... from 表名;    当表名写成别名后，必须写成别名.字段

(4)通过as给表起别名

select 别名.字段... from 表名;    当表名写成别名后，必须写成别名.字段

(5)消除重复行

distinct 字段;
select distinct gender from students;

2.条件查询

(1)比较运算符 > < >= <= = !=

select ... from 表名 where ...;
select * from students where age>18;
select * from students where age<18;
select * from students where age=18;
select id,name,gender from students where age>18;

(2)逻辑运算符 and(与） or(或） not(取反)

select ... from 表名 where ...;
select * from students where age>18 and age<28;
select * from students where age>18 and gender=2;
select * from students where age>18 or height>=180;
select * from students where not age>18 and gender=2;    只否定了age
select * from students where not(age>18 and gender=2);    age, gender一起否定了  not会把and变成or,把or变成and

3.模糊查询

(1)like: %表示任意0个或多个字符 _替换1个

select name from students where name like "小%";    查询姓名中以小字开头的名字
select name from students where name like "%小%";    查询姓名中有"小"的名字
select name form students where name like "__";    查询有两个字的名字
select name from students where name like "___";    查询有3个字的名字
select name from students where name like "__%";    查询至少有2个字的名字

(2)rlike 正则

. 匹配除换行符

^ 匹配输入字符串的开始位置

$ 匹配输入字符串的结尾位置

*前一个字符出现零次或多次

select name from students where name rlike "^周.*";    查询以周开始的姓名
select name from students where name rlike "^周.*伦$";    查询以周开始，伦结尾的名字

4.范围查询

(1) in(, ,) 表示在一个非连续的范围内

select name, age from students where age=18 or age=34;
select name, age from students where age in (12,18,34);    查询年龄为12，18，34之间的信息

(2) not in 不在连续的范围之内

select name, age from students where age not in (12,18,34);    查询年龄不是12,18,34之间的信息

(3)between … and … 表示在一个连续的范围内

select name,age from students where age between 18 and 34;    查询年龄在18到34之间的信息

(4) not between … and … 表示不在一个连续的范围内

select name,age from students where age not between 18 and 34;    查询年龄不在18到34之间的信息
select name,age from students where not age between 18 and 34;    查询年龄不在18到34之间的信息

5.空判断

(1)判空 is null

select * from students where height is null;    查询身高为空的信息

(2)判非空 is not null

select * from students where height is not null;

七、排序

order by 字段

order by 多个字段(先写哪个字段就先按照哪个字段排序)

asc 从小到大排

desc 从大到小排

select * from students where (age between 18 and 34) and gender=1 order by age;    默认从小到大排，按照年龄排序
select * from students where (age between 18 and 34) and gneder=1 order by age asc;    查询年龄在18到34之间的男性，按照年龄从小到大排
select * from students where (age between 18 and 34) and gender=1 order by age desc;    查询年龄在18到34之间的男性，按照年龄从大到小排
select * from students where (age between 18 and 34) and gender=2 order by height desc;    查询年龄在18到34之间的女性，身高从高到矮排，但身高相同时默认主键从小到大排
select * from students where (age between 18 and 34) and gender=2 order by height desc,id desc;    查询年龄在18到34之间的女性身高从高到矮排，身高相同时按id从大到小排
select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc, id desc;    查询年龄在18到34之间的女性，身高从高到矮排，如果身高相同按年龄从小到大排，若年龄相同则按id从大到小排
select * from students order by age asc,height desc;    按照年龄从小到大排排序，若年龄相同则按身高从高到矮排序

八、聚合函数

1.总数 count

select count(*) from students where gender=1;    查询男性有多少人
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;

2.最大值 max

select max(age) from students;  查询最大年龄
select max(height) from studnets where gender=2;  查询女性最高身高

3.最小值 min

4.求和 sum

select sum(age) from students;    计算所有人的年龄总和

5.平均值 avg

select avg(age) from students;    计算平均年龄
select sum(age)/count(*) from students;    通过其他表达式计算平均年龄

6.四舍五入 round(小数，保留小数点位数)

select round(avg(age), 2) from students;  计算所有人的平均年龄，保留两位小数
select round(sum(age)/count(*), 3) from studnets;    计算所有人的平均年龄，保留三位小数
select round(avg(height), 2) from students where gender=1;    计算男性的平均身高，保留两位小数

九、分组

1.group by

select gender from students group by gender;    按照性别分组，查询所有性别
select gender,count(*) from students group by gender;    计算每种性别中的人数
select gender,count(*) from studnets where gender=1 group by gender;    计算男性人数

2.group_concat(…)

select gender, group_concat(name) from students where gender=1 group by gender;    查询同种性别中的姓名
select gender,group_concat(name,age,id) form students where gender=1 group by gender;
select gender,group_concat(name, "_", age,"", id) from students where gender=1 group by gender;

3.having

select gender,group_concat(name),ave(age) from students group by gender having avg(age)>30;    查询平均年龄超过30岁的每种性别的姓名
select gender,group_concat(name) from students group by gender having count(*)>2;    查询每种性别中人数多于2个的信息

having 与 where 的区别：

having 针对分组，where 针对数据表原始数据
having 用在group by 后面， where 用在group by 前面

十、分页

limit 放在最后写，来限制数据的个数

limit (第 N页 - 1) * 每个的个数，每页的个数

select * from students where gender=1 limit 2;    显示两个查询出来的个数
select * from students limit 0,5;    查询前5个数据
select * from students limit 5,5;   查询6到10个数据
select * from students limit 0,2;    每页显示两个，第一个页面
select * from students limit 2,2;   每页显示两个，第二个界面
select * from students limit 4,2;    每页显示两个，第三个界面
select * from studnets limit 6,2;    每页显示两个，第四个界面
select * from students order by age asc limit 10,2;    每页显示两个，显示第六页的信息，按年龄从小到大排
select * from students where gender=2 order by height desc limit 0,2;

根据大小排序分页显示数据时，要注意排序条件，条件相等的数据分页可能会出现错误

十一、连接查询

(当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回)

1.内连接

内连接查询：查询结果为两个表匹配到的数据

select * from students inner join classes on students.cls_id=classes.id;    
有对应的就显示，没有就不显示

select students.*,classes.name from students inner join classes on students.cls_id=class.id;  
显示students表中所有字段，classes表中的name字段

select students.name,classes.name from students inner join classes on students.cls_id=class.id;   
显示students表中的name字段，classes表中的name字段

select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;    给数据表起名字

select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;    在上条查询结果中，将班级姓名显示在第1列

select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;    
按照班级排序，直接按c.name排序结果和想象中可能不大一样

select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;    
按照班级排序，当在同一个班级时，按照学生id从小到大进行排序

2，外连接

（1）左连接查询：查询结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据用null填充

select * from students as s left join calsses as c on s.cls_id=c.id;

（2）右连接查询：查询结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据用null填充右连接很少用，直接交换左连接两个数据表的位置，用left join完成右连接操作

select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;    查询没有对应班级信息的学生

十二、自关联

一个表中的一个字段与另一个字段关联

select * from areas as province inner join areas as citys on city.pid=province.aid having province atitle="山东省";

select province.atitle,city.atitle from areas as province inner join areas as city on city.pid=province.pid having province atitle="山东省";

通过as把一张表当成两张表操作

十三、子查询

select里面嵌套了另一个select，执行的时候先执行里面的select

select * from students where height = (select max(height) from students);

select * from areas where pid = (select aid from areas where atitle="河北省");

(使用时效率较低)

十四、补充

1.添加外键

alter table goods add foreign key(cate_id) references good_cates(id);    
给cate_id添加外键

确定两列数据的对应关系，关系不对的数据插入不进去

在创建表的时候设置外键约束：

create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

实际开发中很少使用外键约束，会降低表的更新效率

2.从另一个表中选取数据插入另一张表

create table goods_brands(id int unsigned primary key auto_increment,name varchar(40) not null) select brand_name as name from goods group by brand_name;
 这的别名name和表中字段的名字需要一致

3.取消外键约束：

（1）需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称

 show create table goods;

（2）获取名称之后就可以根据名称来删除外键的约束

alter table goods drop foreign key 外键名称;