MySQL数据库的常用基本操作汇总整理详细齐全

Bertha 。 2023-07-02 11:30 6阅读 0赞

学习python少不了和数据库打交道，常见的数据库有：MySQL、SQLite、MongoDB、Redis等，这里主要介绍一下MySQL数据库的基本操作。

数据库听起来感觉好高大上，当你接触之后有种恍然大悟的感觉，不就是把我们的数据存到一个表格中吗。可以简单的这么理解，大家都知道表格都是由表名、表头、数据等几部分组成的，数据库跟这类似，只不过叫法不一样，这里叫数据库名、表名和字段。数据库就简单介绍这么多，说的不一定完全正确，下面就说一下怎么操作数据库。

更多精彩内容 [请点击我][Link 1]

一、MySQL字段类型

常用的有：数值(int, float)、字符串(varchar, text)、日期(date, datetime)

二、MySQL约束

主键：primary key

外键：foreign key

默认值：default

唯一：unique

非空：not null

三、物理删除和逻辑删除

在使用数据库的时候，一定会遇到删除数据库中的内容，这个时候一定要小心操作，不然就会被老板直接开除，网络上不是有个很火的命令操作叫”程序员如何执行一条命令让老板开除”，所以操作数据库时一定要小心谨慎。

说一下什么是物理删除和逻辑删除，物理删除就是把一条数据从数据库中永远删除，就是再也找不到也不能恢复；逻辑删除就是给你设计的表再增加一列(比如：isDelete，设默认值为0)，当你想删除数据时将这个字段的值设为1，当你再操作数据时把isDelete值为1的筛选掉就可以了，这样数据也不会丢失。对于重要的数据一定要设置这个字段，对于不重要的数据自己想怎么操作就怎么操作，大不了全部删除再重新建数据库。

四、数据库基本操作(增删改查)

1、创建数据库

create database \[if not exists\] db\_name \[character set utf8\]；

注意：\[if not exists\]：最好加上，这样可以避免麻烦，每操作一次数据库就多一次风险

\[character set utf8\]就是设置编码格式，也可以不指定

2、查看数据

show databases;

3、查看数据库创建方式

show create database db\_name;

4、修改数据库

alter database db\_name \[character set xxx\];

5、删除数据库

drop database \[if exists\] db\_name;

注意： \[if exists\]：如果你不确定数据库是否存在最好加上，存在就删除，不存在也不会报错

6、使用数据库

use db\_name;

7、查看当前使用数据库

select database();

五、数据库管理

1、通过grant命令，给test数据库，添加用户\*\*\*，密码123456

grant select,insert,update,delete,create,drop on test to ‘\*\*\*’@’localhost’ identified by ‘123456’;

2、修改用户密码

grant select,insert,update,delete,create,drop on test to ‘\*\*\*’@’localhost’ identified by ‘asdfasdf’;

3、显示用户

select user from mysql.user;

4、删除用户

drop user ‘\*\*\*’@’localhost’;

六、数据表的基本操作(增删改查)

1、查看数据表

show tables;

2、创建数据表

create table student(

id int primary key auto\_increment,

name varchar(20) not null,

age int not null,

gender tinyint(1) default 1,

birthday date,

hobby varchar(20)

);

3、查看数据表结构

desc student;

4、查看创建数据表语句

show create table student;

5、增加数据(列，字段)

alter table student add address varchar(30);

6、增加多个数据

alter table student add address varchar(30),

add age int not null,

add height int not null;

7、修改一列列名

alter table student change address addr varchar(20);

8、修改一列类型

alter table student modify age tinyint default 20;

9、删除一列

alter table student drop height;

10、修改表名

rename table student to stu;

11、修改表所用的字符集

alter table student character set utf8;

12、删除表

drop table student;

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

create table users(id int not null,

name varchar(10),

age int,

height int

);

13、添加主键

alter table users add primary key (id);

14、删除主键

alter table users drop primary key;

15、添加唯一索引

alter table users add unique (name);

16、添加唯一索引设置索引名

alter table users add unique key user\_name(name);

17、添加联合索引

alter table users add unique index name\_age(name,age);

18、删除索引

alter table users drop index name;

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

create table student( id int primary key auto\_increment,

name varchar(20),

birthday varchar(20),

age int

);

19、插入一条数据

insert into student(name,birthday,age) values(‘学生1′,’2001-1-1’,11);

20、插入多条数据

insert into student(name,birthday,age) values(‘学生2′,’2001-1-2’,11),

(‘学生3′,’2001-1-3’,11),

(‘学生4′,’2001-1-4′,11);

21、修改数据

update student set birthday=’2001-1-10’ where id=1;

22、数据表中的数据可以直接运算

update student set age = age + 5;

23、删除数据

delete from student where id = 1;

注意：删除数据时一定要加条件限制，不然整张数据表都被删除了

24、删除表中的所有数据

delete from student;

七、查询数据

create table grade( id int primary key auto\_increment,

name varchar(20),

js double,

java double,

python double );

insert into grade (name,js,java,python) values (‘Tom’,68,89,87),(‘Jim’,70,91,92),(‘Jake’,71,73,74),(‘Mike’,80,84,85),(‘Jame’,85,88,83);

更多精彩内容 [请点击我][Link 1]

1、select查询

查询所有数据： select \* from grade;

查询姓名和js成绩： select name,js from grade;

过滤表中重复数据： select distinct js from grade;

给所有js成绩+5，并使用as别名： select name,js+5 as ‘js成绩’ from grade;

2、where过滤查询

查询姓名XX的学生信息： select \* from grade where name=’Tom’;

查询总分大于250分的所有学生： select name,js+java+python as ‘总成绩’ from grade where js+java+python>250;

查询js分数和java分数大于90的学生： select \* from grade where js>90 and java>90;

查询java分数80~90的学生： select \* from grade where java between 80 and 90;

查询java分数在80或90的学生： select \* from grade where java in (80,90);

/\* 模糊查询，%表示多个字符，\_表示一个字符 \*/

查询姓名j开头的学生： select \* from grade where name like ‘j%’;

八、order by 排序

/\* asc:升序，默认值 desc：降序 \*/

根据js分数升序排序： select \* from grade order by js;

更加总分数从高到低排序：select name,(ifnull(js,0)+ifnull(java,0)+ifnull(python,0)) as ‘总分数’ from grade order by ‘总分数’ desc;

九、group by分组查询

create table product\_tab( id int primary key auto\_increment,

product\_name varchar(20),

price float(6,2),

product\_date date,

class varchar(20) );

insert into product\_tab (product\_name,price,product\_date,class) values (‘苹果’,10,’20180812′,’水果’),

(‘香蕉’,20,’20180826′,’水果’),

(“水壶”,120,’20170612′,”电器”),

(“被罩”,70,’20170612′,”家具”),

(“音响”,420,’20171012′,”电器”),

(“电视”,2000,’20170912′,”电器”),

(“床单”,55,’20171112′,”家具”),

(“草莓”,34,’20170512′,”水果”);

按位置分组： select \* from product\_tab group by 5;

按产品类别分类并显示平均价格： select class,avg(price) from product\_tab group by class;

按产品分类显示每一种商品价格总和超过200的商品： select class,sum(price) from product\_tab group by class having sum(price)>200;

分组显示所有产品：group\_concat select id,group\_concat(product\_name) from product\_tab group by class;

十、where和having之间的区别

首先，明确一点就是能使用where的地方都能使用having；where只能用于分组之前数据的筛选；having只能用于分组之后数据的筛选，

而且having中可以使用聚合函数。

十一、聚合函数

COUNT（列名）：统计行的个数

统计学生个数： select count(id) from grade;

SUM（列名）：统计总量

统计所有js总成绩： select sum(js) as ‘js总成绩’ from grade;

AVG（列名）：平均数

统计所有js平均分： select avg(js) as ‘js平均分’ from grade;

MAX,MIN （最高，最低）

select max(js) as ‘js最高分’ from grade;

select min(js) as ‘js最低分’ from grade;

十二、SQL语句执行顺序

from—-where—-select—-group by—-having—-order by

十三、limit和正则表达式

limit

查询前3条数据： select \* from grade limit 3;

跳过1条，查询3条数据： select \* from grade limit 1,3;

正则表达式

查询j开头的学生： select \* from grade where name regexp ‘^j’;

查询名字中m出现2次的学生： select \* from grade where name regexp ‘m\{2\}’;

十四、多表操作

1、外键约束

/\* 一个班级对应多个学生，一个学生只能对应一个班级 \*/

主表：创建班级表 create table class( id int primary key auto\_increment, name varchar(20), stu\_nums int );

子表：创建学生表 create table student( id int primary key auto\_increment, name varchar(20), class\_id int, foreign key(class\_id) references class(id) );

注意：作为外键一定要和关联主键的数据类型保持一致

插入数据： insert into class (name,stu\_nums) values (‘班级一’,10),(‘班级二’,12),(‘班级三’,13),(‘班级四’,14),(‘班级五’,15);

insert into student (name,class\_id) values (‘Tom’,1),(‘Jim’,1),(‘Jake’,2),(‘Mike’,3),(‘Jane’,4);

增加外键： alter table student add constraint student\_fk\_class foreign key(class\_id) references class(id);

删除外键： alter table student drop foreign key student\_ibfk\_1;

2、INNODB支持的on语句

外键约束对子表的含义：如果在父表中找不到候选键，则不允许在子表进行`insert`/`update`

`　　　　`外键约束对父表的含义：在父表上进行`update`/`delete`以更新或删除在子表中的一条或多条对应数据，

父表的行为取决于：在定义子表的外键指定的`on update`/`on delete`语句

ON DELETE CASCADE 级联删除：父表记录被删除，子表对应的记录自动被删除

foreign key(class\_id) references class(id) on delete cascade;

ON DELETE SET NULL 置空：父表update/delete记录时，子表设为null

foreign key(class\_id) references class(id) on set null;

RESTRICT：拒绝对父表进行删除操作

NO ACTION：在mysql中同RESTRICT,如果子表中有匹配的记录,则不允许对父表对应候选键

3、多表查询

/\* 创建2张表：员工表和部门表 并插入相关数据 \*/

create table employee( emp\_id int primary key auto\_increment, emp\_name varchar(20), age int, dept\_id int );

create table department( dept\_id int primary key auto\_increment, dept\_name varchar(100) );

insert into employee(emp\_name,age,dept\_id) values (‘A’,19,200), (‘B’,26,201), (‘C’,30,201), (‘D’,24,202), (‘E’,20,200), (‘F’,38,204);

insert into department values (200,’人事部’), (201,’技术部’), (202,’销售部’), (203,’财政部’);

笛卡尔积查询：查询结果是m\*n

select \* from employee,department;

内连接：查询两张表中都有的关联数据

select \* from employee,department where employee.dept\_id=department.dept\_ id;

外连接：

左外连接：在内连接的基础上增加左边有右边没有的结果

select \* from employee left join department on employee.dept\_id=departmen t.dept\_id;

右外连接：在内连接的基础上增加右边有左边没有的结果

select \* from employee right join department on employee.dept\_id=departme nt.dept\_id;

4、多条件查询

查询员工大于25岁的信息：

select employee.emp\_name,department.dept\_name

\-> from employee,department

\-> where employee.dept\_id=department.dept\_id and age>25;

以内连接方式查询employee和department表，并以age字段升序显示：

select employee.emp\_id,employee.emp\_name,employee.age,department.dept\_name

\-> from employee,department

\-> where employee.dept\_id=department.dept\_id

\-> order by age asc;

子查询：查询employee表，dept\_id在department表中的所有信息

select \* from employee where dept\_id in (select dept\_id from department);

子查询：

select \* from employee where dept\_id in (select dept\_id from department where age>25);

使用EXISTS关键字

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。

而是返回一个真假值。Ture或False

当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

select \* from employee where EXISTS (SELECT dept\_name from department where dept\_id=205);

select \* from employee where EXISTS (SELECT dept\_name from department where dept\_id=203);

[Link 1]: http://www.ryxxff.com/52210.html

MySQL数据库的常用基本操作汇总整理详细齐全

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql几个常用操作汇总

相关 mysql数据库常用命令和数据库操作命令详解汇总

相关 MySQL数据库的常用基本操作汇总整理详细齐全

相关 Python常用数字处理基本操作汇总

相关 MySQL数据库常用基本操作

相关 MySQL数据库常用操作

相关 MySQL基本操作常用语句

相关 MySQL基本操作汇总

相关 MySQL知识整理3—数据库的基本操作

相关数据库_常用汇总