MySQL 数据库基础命令

KyrosWarner
769 阅读12分钟

前言:

我是一只相当白的小白,这是我的第一篇博客,记录学习的点滴.当中有些许我在课堂上记录的范例,如果发现疏漏错误的地方请指正,谢谢各位大神

注意点:

  • SQL语句不区分大小写
  • 若输入命令时不小心换行,可继续输入至命令结束,并不影响执行结果
  • 命令通常以";"结尾

数据库的基本操作

链接数据库

mysql -uroot -p
mysql -uroot -p你的密码  # 可直接追加明文密码(不安全,不推荐)

退出数据库

quit/exit/ctrl + d(快捷键)

显示当前数据库版本

select version();

显示当前时间

select now();

查看当前使用的数据库

select database();

查看所有数据库

show databases;

创建数据库 create database 数据库名 charset=utf8; (MySQL默认创建编码为latin1,为方便转码需在创建数据库时指定编码 charset=utf8 )

create database xxxx charset=utf8;

查看创建数据库的语句 show create database 数据库名

show create database xxxx;

使用数据库 use 数据库的名字 可使用该命令直接跳转数据库

use xxxx;

从sql文件中导入数据 source 具体地址/areas.sql;

source xxx.sql;

删除数据库 drop database 数据库名; 注:若数据库名称中包含符号"-",则删除时需在数据库名称上包裹``符号(键盘Tab键上方)

drop database `xxxx-xxxx`;
drop database xxxx;

数据表的操作

  • 注:在操作数据表之前需要先进入此数据表所在的数据库
  • 参考 "数据库的基本操作" 中命令 "使用数据库"

查看当前数据库中所有表

show tables;

​

  • 数据类型 数据类型是指在创建表的时候为表中字段指定数据类型,只有数据符合类型要求才能存储起来, 使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间。

    常用数据类型如下:

    整数：int，bit 小数：decimal 字符串：varchar,char 日期时间: date, time, datetime 枚举类型(enum)

    数据类型说明:

    decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位. char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数 varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径. 字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

  • 数据约束 约束是指数据在数据类型限定的基础上额外增加的要求.

    常见的约束如下:

    主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned(无符号整形,即为正数),创建主键时通常加上 auto_increment 表示自动增长. 非空 not null: 此字段不允许填写空值. 惟一 unique: 此字段的值不允许重复. 默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准. 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

创建表 create table 数据表名字 (字段 类型 约束,字段 类型 约束)

create table xxxx(xxxx varchar(30) not null,xxxx int unsigned);

查看表结构 desc 数据表的名字;

desc xxxx;

范例:创建 students 表(id、name、age、high、gender、cls_id、is_delete)

create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,   tinyint 表示字节大小为 -128~127 或 0~255
height decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0);

查看表的创建语句(可查看主键、引擎InnoDB等、自动增长变量、编码等信息) show create table 表名字;

show create table students;

修改表-添加字段 alter table 表名 add 列名 类型;

alter table students add birthday datetime;

修改表-修改字段：不可重命名,仅修改类型及约束 alter table 表名 modify 列名 类型及约束;

alter table students modify birthday date not null;

修改表-修改字段：可重命名,表名、类型、约束均可修改 alter table 表名 change 原名 新名 类型及约束;

alter table students change birthday hobby varchar(30) default "你猜";

修改表-删除字段 alter table 表名 drop 列名;

alter table students drop hobby;

删除表 drop table 表名;

drop table xxxx;

删除数据库 drop database 数据库;

drop database xxxx;

增删改查(curd)

curd 的解释: 创建 Create , 更新 Update , 读取 Retrieve , 删除 Delete

增加

模拟样表(仅供理解)

| Field  | Type                                | Null | Key | Default | Extra          |
| id     | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)                         | NO   |     | NULL    |                |
| age    | int(10) unsigned                    | YES  |     | NULL    |                |
| high   | decimal(3,2)                        | YES  |     | NULL    |                |
| gender | enum('男','女','保密')               |  YES |     | 保密    |                |
| cls_id | int(11)                             | YES  |     | NULL    |                |

全列插入 insert [into] 表名(字段...) values(...) 主键字段 可以用 0 null default 来占位

insert into students values(0,'吴彦祖','25','1.85','男','66');   -- 此插入需保证插入值数与字段数一致
insert into students values(null,'吴彦祖','25','1.85','男','66');
insert into students values(default,'吴彦祖','25','1.85','男','66');

枚举中的下标从 1 开始 1->'男' 2->'女' ....

insert into students values(default,'吴彦祖','25','1.85',1,'66');

部分插入

insert into students(name,gender) values("蔡徐坤",2);

一次插入多条数据

insert into students(name) values("吴亦凡"),("张艺兴");
insert into students values(default,'吴彦祖','25','1.85','男','66'),
(default,'蔡徐坤','18','2.50',2,'99');

修改

update 表名 set 列1=值1,列2=值2... where 条件; 全部修改

update students set name = 'CXK';

按条件修改

update students set name = "CXK" where id = 3;

按条件修改多个值

update students set name = 'CXK',age = 20 where id = 2;

​

查询

查询所有列 select * from 表名;

select * from students;

定条件查询 select * from students where id < 6;

select * from students where name = "CXK";

查询指定列 select 列1,列2,... from 表名;

select name,age from students;

可以使用as为列或表指定别名 select 字段[as 别名] , 字段[as 别名] from 数据表;

select name as 姓名,age as 年龄 from students;

字段的顺序

select age,name from students;

删除

物理删除 delete from 表名 where 条件 delete from students -> 整个数据表中的所有数据全部删除

delete from students where id = 3;

逻辑删除 用一个字段来表示 这条信息是否已经不能再使用了 给students表添加一个 is_delete 字段 bit 类型 alter table 表名 add 字段 类型 default 默认值;

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

查询进阶

查询所有字段 select * from 表名;

select * from students;

查询指定字段 select 列1,列2,... from 表名;

select name,age from students;

使用 as 给字段起别名 select 字段 as 名字.... from 表名;

select name as 姓名 from students;

select 表名.字段 .... from 表名;

select students.name from students;

可以通过 as 给表起别名 select 别名.字段 .... from 表名 as 别名;

select s.name from students as s;

消除重复行(查性别) distinct 字段

select distinct gender from students;

条件查询 比较运算符

= < <= != = <> select .... from 表名 where .....

查询年纪大于18岁的信息

select * from students where age > 18;

同样可以查询,并输出对应信息(以下举一反三)

select id,name,gender from students where age > 18;

< 查询年纪小于18岁的信息

select * from students where age < 18;

= <= 查询小于或者等于18岁的信息

select * from students where age <= 18;

= 查询年龄为18岁的所有学生的名字

select * from students where age = 18;

!= 或者 <> 查询年龄不为18岁的所有学生的名字

select * from students where age != 18;
select * from students where age <> 18;

​

逻辑运算符

and 18和28之间的所有学生信息

select * from students where age > 18 and age < 28;

18岁以上的女性

select * from students where age > 18 and gender = '女';

or 18以上或者身高高过180(包含)以上

select * from students where age > 18 or height >= 180;

not 不在 18岁以上的女性 这个范围内的信息 错误:select * from students where not age > 18 and gender = '女';

select * from students  where not (age > 18 and gender = '女');

模糊查询(where name like 要查询的数据) like % 替换任意个 _ 替换1个 查询姓名中 以 "小" 开始的名字

select name from students where name like '小%';

查询姓名中 有 "小" 所有的名字

select name from students where name like '%小%';

查询有2个字的名字

select name from students where name like '__';

查询有3个字的名字

select name from students where name like '___';

查询至少有2个字的名字

select name from students where name like '__%';

rlike 正则 查询以 周开始、伦结尾的姓名

select name from students where name rlike '^周.伦$*'

范围查询 in (1, 3, 8)表示在一个非连续的范围内 查询 年龄为18或34的姓名

select * from students where age = 18 or age = 34;
select * from students where age in (18,34);

not in 不在非连续的范围之内 年龄不是 18或34岁的信息

select * from students where age not in (18,34)

between ... and ...表示在一个连续的范围内 查询 年龄在18到34之间的的信息 包含两端从小到大

select * from students where age between 18 and 34;

not between ... and ...表示不在一个连续的范围内 查询 年龄不在18到34之间的的信息

select * from students where age not between 18 and 34;

​

空判断 判空is null 查询身高为空的信息

select * from students where height is null;

判非空is not null

select * from students where height is not null;

排序

order by 字段 默认 asc (升序) asc 从小到大排列，即升序 desc 从大到小排序，即降序

查询年龄在18到34岁之间的男性，按照年龄从小到大到排序

select * from students where age > 18 and age < 34 
and gender = '男'  order by age asc;

select * from students where (age between 18 and 34) 
and gender = '男' order by age;

查询年龄在18到34岁之间的女性，身高从高到矮排序

select * from students where (age between 18 and 34)
and gender = '女' order by height desc;

order by 多个字段 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序

select * from students where (age between 18 and 34)
and gender = '女' order by height desc,age asc;

如果年龄也相同那么按照id从大到小排序

select * from students where (age between 18 and 34)
and gender = '女' order by height desc,age asc,id desc;

聚合函数

总数 count 查询男性有多少人

select count(*) from students where gender = '男';

最大值 max 查询最大的年龄

select max(age) from students;

查询女性的最高 身高

select max(height) from students where gender ='女';

最小值 min

select min(height) from students where gender = '男';

求和 sum 计算所有人的年龄总和

select sum(age) from  students;

平均值 avg 计算平均年龄

select avg(age) from students;

计算平均年龄 sum(age)/count(*)

select sum(age)/count(*) from students;

四舍五入 round(保留前的结果 , 1) 保留1位小数 拓展:小数后默认保留六位,若需要精确值可将值扩大10的次方倍数再进行存储 如:3.1415 * 10^4 -> 存储 计算所有人的平均年龄，保留2位小数

select round(avg(age),2) from students;

计算男性的平均身高 保留2位小数

select round(avg(height),2) from students where gender = '男';

分组

group by select 分组字段 from 表名 group by 分组字段; 按照性别分组,查询所有的性别

select gender from students group by gender;

计算每种性别中的人数

select gender,count(*) from students group by gender;

计算男性的人数

select gender,count(*) from students where gender = '男' group by gender;

查询每组性别的平均年龄

select gender,avg(age) from students group by gender;

group_concat(...) 查询同种性别中的姓名 返回一组的姓名

select gender,group_concat(name) from students group by gender;

查询男性的姓名、年龄、id '_' 只为方便阅读

select gender,group_concat(name,'_',age,'_',id) from students 
where gender = '男' group by gender;

having(注意having和group by 连用 having后通常也要跟 聚合函数) 查询平均年龄超过30岁的性别，以及姓名

select gender ,avg(age), group_concat(name) from students 
group by gender having avg(age) > 30;

查询每种性别中的人数多于2个的信息

select gender,count(*) from students group by gender
having count(*) > 2;

分页

limit start, count limit 放在最后面(注意)

起始位置 = (第N页-1)*每一页的个数

限制查询出来的数据个数 查询前5个数据

select * from students limit 0,5;

每页显示2个，第1个页面

select * from students limit 0,2;

每页显示2个，第2个页面

select * from students limit 2,2;

每页显示2个，第3个页面

select * from students limit 4,2;

每页显示2个，第4个页面

select * from students limit 6,2;

每页显示2个，显示第6页的信息, 按照年龄从小到大排序

select * from students order by age asc limit 10,2;

连接查询

inner join ... on select ... from 表A inner join 表B on 条件; 查询 有能够对应班级的学生以及班级信息

select * from students inner join classes
on students.cls_id = classes.id;

select * from students inner join classes
on students.cls_id = classes.id where students.gender ='女';

按照要求显示姓名、班级

select students.name,classes.name from students inner join classes
on students.cls_id = classes.id;

给数据表起名字

select s.name,c.name from students as s 
inner join classes as c on s.cls_id = c.id;

查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.

select students.* ,classes.name from students inner join classes
on students.cls_id = classes.id;

在以上的查询中，将班级名显示在第1列

select classes.name ,students.*  from students inner join classes
on students.cls_id = classes.id;

查询 有能够对应班级的学生以及班级信息, 按照班级名进行排序

select classes.name ,students.*  from students inner join classes
on students.cls_id = classes.id order by classes.name;

当时同一个班级的时候，按照学生的id进行从小到大排序

select classes.name ,students.*  from students inner join classes
on students.cls_id = classes.id order by classes.name asc ,students.id asc;

 left join 对于右表不存在的数据使用 null 填充
 查询每位学生对应的班级信息
select * from students left join classes on 
students.cls_id = classes.id;

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

select * from students left join classes on 
students.cls_id = classes.id having classes.name is null;

 right join   on
 将数据表名字互换位置，用left join完成
select * from students right join classes on 
classes.id = students.cls_id;

​

子查询

查询出高于平均身高的信息(height)

得到平均身高

select avg(height) from students;

大于平均 身高

select * from students where height > 172.076923;

select * from students where 
height > (select avg(height) from students);
avatar
文章
阅读
粉丝