泛览天下

阅读,看尽天下事

MySQL操作初级

2022-08-04 02:12:11


select classid,sex,count(*) from stu group by classid,sex;order by 排序 -- asc 默认升序 desc 降序我们知道从 MySQL 表中使用 SQL select 语句


MySQL

MySQL 数据库

创建数据库

create database if not exists user default charset utf8;

创建数据库,该命令的作用:

  • 如果数据库不存在则创建,存在则不创建。
  • 创建user数据库,并设定编码集为utf8

删除数据库

删库有风险,动手需谨慎

drop database user;

MySQL 数据表

创建MySQL数据表需要以下信息:

  • 表名
  • 表字段名
  • 定义每个表字段
-- create table 表名(
--    字段名 类型 [字段约束],
--    字段名 类型 [字段约束],
--    字段名 类型 [字段约束]
--    ...
-- )default charset=utf8;

create table users(
    id int unsigned not null auto_increment primary key,
    name varchar(25) not null
    sex enum('w','m','x') not null
    )default charset=utf8;

查看表结构 desc users;

查看建表语句 show create table users\G; -- 查看建表的语句

修改表结构

格式: alter table 表名 action(更改选项);

添加字段:

-- 添加字段:alter table 表名 add 字段名信息

-- 在users表的最后追加一个num字段 设置为int not null

alter table users add num int not null;

-- 在users表的email字段后添加一个age字段,设置int not null default 20;

alter table users add age int not null default 20 after email;

-- 在users表的最前面添加一个phone字段设置为int类型

alter table users add phone int first;

删除字段:

-- 删除字段:alter table 表名 drop 被删除的字段名

-- 删除users表的num字段

alter table users drop num;

修改字段:

-- 修改字段:alter table 表名 change\[modify\] 被修改后的字段信息

-- 其中:change可以修改字段名, modify 不修改
-- 修改users表中age字段信息(类型),(使用modify关键字的目的不修改字段名)

alter table users modify age tinyint unsigned not null default 20;

-- 修改users表的phone字段改为mobile_phone字段并添加了默认值(使用change可以改字段名)

alter table users change phone mobile_phone int not null default 10;

添加和删除索引:

-- 为users表中的username字段添加唯一性索引,索引名为uni_name;
 alter table users add unique uni_name(username);

-- 为users表中的email字段添加普通索引,索引名为index_email

 alter table users add index index_email(email);

-- 将users表中名为index_email的索引删除
 alter table users drop index index_email;

修改表名:

-- alter table 旧表名 rename as 新表名
alter table users rename as user;
alter table user rename as users;

更改AUTO_INCREMENT初始值:

-- alter table 表名称 auto_increment=1
alter table users auto_increment=1;

更改表类型:

-- alter table 表名称 engine="InnoDB"
alter table users engine="MyISAM";
alter table users engine="InnoDB";

关于表类型

MySQL数据库中的表类型一般常用两种:MyISAM和InnoDB

区别:

MyISAM类型的数据文件有三个frm(结构)、MYD(数据)、MYI(索引)

MyISAM类型中的表数据增 删 改速度快,不支持事务,没有InnoDB安全。

InnoDB类型的数据文件只有一个 .frm

InnoDB类型的表数据增 删 改速度没有MyISAM的快,但支持事务,相对安全。

删除表

MySQL中删除数据表是非常容易操作的, 但是你再进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。

drop table users;

MySQL 数据操作 DML

数据的DML操作:添加数据,修改数据,删除数据

添加数据

格式: insert into 表名[(字段列表)] values(值列表...);

-- 标准添加(指定所有字段,给定所有的值)
insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');
-- 指定部分字段添加值
insert into stu(name,classid) value('lisi','lamp138');
-- 不指定字段添加值
insert into stu value(null,'wangwu',21,'w','lamp138');
-- 批量添加值
insert into stu values
   (null,'zhaoliu',25,'w','lamp94'),
   (null,'uu01',26,'m','lamp94'),
   (null,'uu02',28,'w','lamp92'),
   (null,'qq02',24,'m','lamp92'),
   (null,'uu03',32,'m','lamp138'),
   (null,'qq03',23,'w','lamp94'),
   (null,'aa',19,'m','lamp138');

修改数据

格式:update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件

-- 将id为11的age改为35,sex改为m值
update stu set age=35,sex='m' where id=11;
-- 将id值为12和14的数据值sex改为m,classid改为lamp92
update stu set sex='m',classid='lamp92' where id=12 or id=14; -- 等价于下面
update stu set sex='m',classid='lamp92' where id in(12,14);

删除数据

格式:delete from 表名 [where 条件]

-- 删除stu表中id值为41的数据
delete from stu where id=41;
-- 删除stu表中id值为20到30的数据
delete from stu where id>=20 and id<=30;
-- 删除stu表中id值为20到30的数据(等于上面写法)
delete from stu where id between 20 and 30;
-- 删除stu表中id值大于200的数据
delete from stu where id>20;

MySQL 数据操作 DQL

数据的DQL操作:数据查询

格式:

    select [字段列表]  |  *  from 表名

    [where 搜索条件]

    [group by 分组字段 [having 子条件]]

    [order by 排序 asc|desc]

    [limit 分页参数]

基础查询

select * from stu;

where条件查询

  • 你可以在where 子句中指定任何条件。
  • 你可以使用 and或者 or 指定一个或多个条件。
  • where 子句也可以运用于 SQL 的 delete 或者 update 命令。
  • where 子句 类似于程序语言中的 if 条件 ,根据 MySQL 表中的字段值来读取指定的数据。
-- 查询班级为lamp138期的学生信息
select * from stu where classid='lamp138';

-- 查询lamp138期的男生信息(sex为m)
select * from stu where classid='lamp138' and sex='m';

-- 查询id号值在10以上的学生信息
select * from  stu where id>10;

-- 查询年龄在20至25岁的学生信息
select * from stu where age>=20 and age<=25;
select * from stu where age between 20 and 25;

-- 查询年龄不在20至25岁的学生信息
select * from stu where age not between 20 and 25;
select * from stu where age<20 or age>25;

-- 查询id值为1,8,4,10,14的学生信息
select * from stu where id in(1,8,4,10,14);
select * from stu where id=1 or id=8 or id=4 or id=10 or id=14;

-- 查询lamp138和lamp94期的女生信息
select * from stu where classid in('lamp138','lamp94') and sex='w';
select * from stu where (classid='lamp138' or classid='lamp94') and sex='w

Like 子句

where 子句中可以使用等号=来设定获取数据的条件,如 "name= 'zhangsan'"。

但是有时候我们需要获取 name字段含有 "zhang" 字符的所有记录,

这时我们就需要在 where 子句中使用 SQL like 子句。

like 子句中使用百分号%和_字符来表示任意字符,类似于UNIX或正则表达式中的星号*。

如果没有使用百分号%, like 子句与等号=的效果是一样的。

like 通常与 % 一同使用,类似于一个元字符的搜索。
你可以使用and 或者 or 指定一个或多个条件。
你可以在 delete 或 update 命令中使用 where...like 子句来指定条件。
可以使用regexp正则来代替 like

-- 查询name字段值是以zh开头的所有信息
select * from stu where name like "zh%";
select * from stu where name regexp  "^zh"; --正则写法
-- 查询姓名name中含有ang子串的所有信息
select * from stu where name like "%ang%";
select * from stu where name regexp  "ang";
-- 查询姓名是任意四位字符构成的信息。
select * from stu where name like "____";
select * from stu where name regexp "^[a-z0-9]{4}$";

MySQL的统计函数(聚合函数):max() min() count() sum() avg()

-- 获取学生表中最大、最小以及平均年龄是多少?
select max(age),min(age),avg(age) from stu;

-- 获取学生表中男生m的数量
select count(*) from stu where sex='m';

group by 语句 分组

group by语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 count, sum, avg,等函数。

-- 统计班级信息,按性别分组,并统计每组人数;
select sex,count(*) from stu  group by sex;

-- 统计每个班级的人数
select classid,count(*) from stu  group by classid;

-- 统计每个班级的,男生和女生各多少人数。
select classid,sex,count(*) from stu  group by classid,sex;

order by 排序 -- asc 默认升序 desc 降序

我们知道从 MySQL 表中使用 SQL select 语句来读取数据。

如果我们需要对读取的数据进行排序,我们就可以使用 MySQL 的 group by 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。

  • 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 asc 或 desc 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
  • 你可以添加 where..like 子句来设置条件。
-- 按年龄升序排序查询学生信息
select * from stu order by age;
select * from stu order by age asc;  --默认asc升序 可省略
-- 年龄降序排序
select * from stu order by age desc;
-- 查询学生信息,按班级做升序排序,相同班级按年龄降序排序
select * from stu order by classid asc,age desc;

limit 关键字 查询部分数据

-- 例如: .... limit m; 查询数据只显示前m条

-- 例如: .... limit m,n; 排除前m条,然后再查询出前n条

-- 查询前5条信息
 select * from stu limit 5;
-- 排除前2条后再获取4条信息
select * from stu limit 2,4;
-- 以4条数据分一页,取第一页。
select * from stu limit 0,4;
-- 以4条数据分一页,取第二页。
select * from stu limit 4,4;
-- 分页公式:.... (页号-1)*页大小, 页大小;

-- 页号     limit语句                起始分页码数值是页大小的几倍。
-- ---------------------------------------------------------------------
-- 1       ... limit 0,4;                0
-- 2       ... limit 4,4;                1
-- 3       ... limit 8,4;                2
-- 4       ... limit 12,4;            3
-- 5       ... limit 16,4;            4

MySQL的多表联查

多表查询:

  1. 嵌套查询
  2. where关联查询
  3. join连接查询(左联,右联,内联)

嵌套查询 (不推荐)

-- 查询年龄最大的所有学生信息
select max(age) from stu;
select * from stu where age=29;

select * from stu where age=(select max(age) from stu);

-- 查询python02期的所有学生信息
select * from users where classid=(select id from class where cname='py2');
select * from users where classid in(select id from class where cname='py2');

where关联查询

-- 查询所有学生信息,并跨表显示对应的班级名称信息
select s.*,c.cname cname from users s,class c where s.classid=c.id;
-- 统计每个班级的人数
select c.cname,count(s.id) from class c,users s where c.id=s.classid group by c.id;

select c.cname,count(s.id) from class c,users s where c.id=s.classid group by c.id order by count(s.id) desc;

join连接查询(左联,右联,内联)

join 按照功能大致分为如下三类:

  • inner join(内连接,或等值连接) :获取两个表中字段匹配关系的记录。
  • left join(左连接): 获取左表所有记录,即使右表没有对应匹配的记录。
  • right join(右连接): 与 left join 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
-- 查询新闻信息,并补齐新闻类别信息
select n.id,n.title,t.name\ from news n,newstype t where n.typeid=t.id;
select n.id,n.title,t.name from news n inner join newstype t on n.typeid=t.id;
-- 同上,但采用的是左联查询left join
select n.id,n.title,t.name from news n left join newstype t on n.typeid=t.id;
-- 同上,但采用的是右联查询right join
select n.id,n.title,t.name from news n right join newstype t on n.typeid=t.id;
-- 统计每个新闻类别下的新闻数量,采用where关联统计
select t.name,count(n.id) from newstype t,news n where t.id=n.typeid group by t.id;
-- 统计每个新闻类别下的新闻数量,采用左联统计
select t.name,count(n.id) from newstype t left join news n on t.id=n.typeid group by t.id;