MySql数据库常用的详细操作语句（包括增删改查等）

目录

创建相关的MySQL语句

删除相关MySQL语句

修改相关的MySQL语句

查询相关的MySQL语句

备份与恢复：

数据库权限设置

 其它语句

---

创建相关的MySQL语句

创建一个数据库并指定编码格式：

create DATABASE  IF NOT EXISTS mydb CHARSET=utf8

创建表（指定表的主键和编码格式）:

CREATE TABLE
IF NOT EXISTS `student` (
		`id` INT UNSIGNED AUTO_INCREMENT,
		`name` VARCHAR ( 50 ) NOT NULL,
		`age` INT NOT NULL,
		`sex` bit ( 2 ),
		`create_time` datetime,
		PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB DEFAULT CHARSET = utf8;

表全部列都插入数据：

INSERT INTO students VALUES (3,'xiaofang',20,1,'2022-11-29 09:46:46','1999-03-16')

表部分列插入数据，没有插入的列，为默认值 null：

INSERT INTO students (`id`,`name`,`age`,`sex`) VALUES (1,'panshunjun',18,0)

创建表的时候指定某一列不为空且唯一：

create TABLE t_stu(

sid int PRIMARY KEY auto_increment,

`name` VARCHAR(50) not null UNIQUE);

创建表并指定表的外键：

create table emp(
id int PRIMARY KEY auto_increment,
emp varchar(20),
deptId int,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES dept(id))

删除相关MySQL语句

删除一个数据库：

DROP DATABASE IF EXISTS mydb

删除一个表（student是表名）：

DROP table student

删除表中的某一列

alter table student drop `telphone`

根据条件删除表的内容：

delete from students where id=1

注：delete是删除表的内容，表的结构还在，drop是删除整个表

先刪除该表，再 重新创建该表：

TRUNCATE table t_project

删除表的一个主键

ALTER table students drop PRIMARY key

修改相关的MySQL语句

修改数据的编码格式（mydb是数据库名称，utf8是编码格式）：

ALTER DATABASE mydb CHARACTER set utf8

给表增加几列（student是表名）：

alter table student add(`birthday` date,`phone` VARCHAR(20))

修改表中某一列的类型（student是表名）：

ALTER table student MODIFY `birthday` datetime

修改表中某一列的名称：

alter TABLE student change `phone` `telphone` varchar(22)

修改表的表名：

alter table student RENAME to students

根据条件修改表中数据：

update students set age=28 where id BETWEEN 1 AND 3
update students set age=NULL where name in('panshunjun')

修改数据库的编码格式：

set CHARACTER_set_client =utf8

注意：（CHARACTER_set_client这是数据传进MySQL的格式，character_set_results这是MySQL传出来的格式，这种修改方式只是一次性的，想要一次想修改后不改变需要到my.ini配置文件进行修改）

给已经创建的表添加主键：

ALTER table students ADD PRIMARY key(id)

给已经创建的表增加外键约束

ALTER table emp

add CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES dept(id)

查询相关的MySQL语句

查看当前所有数据库:

show databases

查看当前数据库下的所有表：

show tables

查询指定表的创建语句（student是表名）：

SHOW CREATE TABLE student

查询表的结构（student是表名）：

desc student

查询表的所有列数据

select * from students

查询指定列

select name,age,sex,birthday from students

DISTINCT:重复数据只显示一次(只有全部一样才会合并到一行）

SELECT DISTINCT `name`,age,id from students

列运算(计算，字符串拼接）

select age+1 ,CONCAT('姓名：',name) from students

IFNULL(age,0)--如果age=null,就把他转成0

select name,IFNULL(age,0)+1 from students

给列起别名（As 可以省略）

select name,IFNULL(age,0)+1 as newAge from students

条件查询

select * from students where age>10 and `name` is not null

in方法：查询姓名是xiaofang和zhangsan的所有信息

select * from students where `name` in ('xiaofang','zhangsan')

模糊查询：’_’表示任意匹配一个字符，‘%’表示任意匹配n个字符

select * from students where `name` like 'panshunju_'
select * from students where `name` like 'pan%'

排序：通过年龄进行升序排列，如果年龄相同在根据name降序排列（默认是升序，）

select * from students ORDER BY age ASC,name desc

使用聚合函数查询:

查询表的行数:

select count(1) as '行数' number from students

求和：查询age列的和以sumAge作为别名

select sum(age) sumAge from students

最大值问题：查询年龄中的最大值

select max(age) as maxAge from students

平均值问题：查询年龄的平均值

select avg(age) as avgAge from students

函数综合运用：计算表的综合数据

select count(1) as 人数总和,sum(age) as 年龄总和,max(age) as 最大年龄,min(age) as 最小年龄,avg(age) as 年龄平均值 from students

分组：分组(以性别分组查询男女总人数）

select sex,count(*) from students where id>2 GROUP BY sex

分组条件：where条件是引导分组前的条件，HAVING引导分组后的条件

select sex,count(*) from students where id>2 GROUP BY sex HAVING count(*)>1

Limit:LIMIT用来指定从几行开始查，一共查询几条数据(0代表从都一行数据开始）

select * from students LIMIT 0,5

查看mysql数据库编码：

SHOW VARIABLES LIKE 'char%'

两个表合并结果集（union）：

//创建表ab
create table ab(a int,b varchar(10))
INSERT into ab VALUES(1,'1')
INSERT into ab VALUES(2,'2')
INSERT into ab VALUES(3,'3')
INSERT into ab VALUES(4,'4')

//创建表cd
create table cd(c int,d varchar(10))
INSERT into cd VALUES(3,'3')
INSERT into cd VALUES(4,'4')

//合并结果集查询
select * from ab UNION select * from cd

多表连接查询--内连接：

//标准写法：
select * from emp e INNER JOIN dept d on e.deptId=d.id

注意：内连接会从两张表的笛卡尔积中查出所有符合条件的内容，不符合条件的内容不会出现在表中；

左外连接查询：

//标准写法：

select emp,deptId,IFNULL(dname,'无数据') as dname from emp as e

left join dept d

on e.deptId=d.id

 注意：以左边的表作为主表，主表中存在数据都会显示出来，当主表没有对应的从表不满足条件时，会使用null来替代出来。

右外连接查询：（与左外连接查询类似）

//标准写法
select emp,deptId,d.id ,d.dname from emp as e

right JOIN dept d

on d.id=e.deptId

对多表连接查询的理解：例如a表中有5条数据（主），b表中有4条数据，在使用无条件的内连接和左右外连接查询时，会显示20条数据，使用有条件内连接查询时会显示20条数据中符合条件的，有条件的左右外连接查询时会显示20条数据中符合条件的并且会把主表中的没有条件的数据全部显示；

备份与恢复：

//备份数据脚本（只备份数据库的内容）

mysqldump -uroot -p123 mydb3>c:/a.sql

//恢复数据库脚本（两种方式）

mysqldump -uroot -p123 mydb3<c:/a.sql

source c:/a.sql

注：root 数据库账号  123 数据库密码 mydb3 数据库名称  c:/a.sql 保存地址

数据库权限设置

给数据库指定用户名和固定ip：

create USER admin@192.168.0.1 IDENTIFIED by '123456'

给数据库指定用户名和任意ip：

create USER admin@'%' IDENTIFIED by '123456'

把ry数据库的所有权限给任意ip下的admin用户

GRANT ALL ON ry.* to admin@'%'

把ry数据库的admin账号收回数据库的CREATE,ALTER,DROP权限

REVOKE CREATE,ALTER,DROP ON ry.* FROM admin@'%'

查看某个ip下的账号所拥有的权限

SHOW GRANTS for admin@'%'

删除某个ip下的用户（权限问题）

drop user admin@'%'

注：其中（GRANT ALL（所有权限）,GRANT CREATE（创建权限）,ALTER（修改权限）,DROP（删除权限）,INSERT（表的插入权限）,UPDATE（表的修改权限）,DELETE（表的删除权限）,SELECT ON（表的查询权限）

 其它语句

数据库切换：

use mydb