mysql数据库4种基本操作语句mysql数据库常用命令

导读安装windows下载地址:https://dev.mysql.com/downloads/mysql/liunx安装:比较复杂【后面会单独总结】什么是数据....

安装

1. windows 下载地址:https://dev.mysql.com/downloads/mysql/
2. liunx安装:比较复杂【后面会单独总结】

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

DBMS (DataBaseManagementSystem): 关系数据库管理系统(数据库软件)

常见的几种DBMS:

• MySQL: Oracle公司产品, 08年被Sun公司收购, 09年Sun公司被Oracle收购. 开源产品 , MaraDB实际上就是MySQL的一个分支使用方式和MySQL一样. 市占率排名第一
• Oracle: Oracle公司产品, 闭源产品 ,性能最强 价格最贵, 市占率排名第二
• SQLServer: 微软公司产品, 闭源产品 , 市占率第三
• DB2: IBM公司产品（相同于mysql分支）
• SQLite: 轻量级数据库, 安装包几十K ,只具备最基础的增删改查功能.

SQL（Structured Query Language）

结构化查询语言, 通过此语言让程序员和数据库软件进行交流

如何连接数据库执行SQL语句

执行SQL语句需要先和数据库软件建立链接之后

[MariaDB为例]从开始菜单中找到MariaDB或MySQL,然后打开找到里面的MySQL Client 打开 , 然后输入密码后回车

1. 退出指令: exit
2. 登录指令: mysql -uroot -p

//登录[root@host]# mysql -u root -pEnter password:******//退出mysql> exitBye

SQL语句格式:

1. 以;号结尾
2. 关键字不区分大小写
3. 可以有空格或换行但一定要以;结尾

数据库相关的SQL语句

查询所有数据库 格式: show databases;

创建数据库 格式: create database 数据库名 charset=utf8/gbk;

1. create database db1;
2. create database db2 charset=utf8;
3. create database db3 charset=gbk;

查看数据库信息 格式: show create database 数据库名;

1. show create database db1;
2. show create database db2;
3. show create database db3;

删除数据库 格式: drop database 数据库名;

1. drop database db3;
2. drop database db2;
3. show databases;

使用数据库

1. 执行表相关和数据相关的SQL语句之前必须先使用了某个数据库
2. 格式: use 数据库名;
3. 举例:use db1;

//创建 mydb1数据库 字符集分别为utf8create database mydb1 charset=utf8;//查询所有数据库检查是否创建成功show databases;//检查数据库的字符集是否正确show create database mydb1;//使用 use mydb1;//删除数据库drop database mydb1;

表相关的SQL语句

执行表相关的SQL语句必须已经使用了某个数据库 use db1;

• 创建表 格式: create table 表名(字段1名 类型,字段2名 类型,.......);

1. create table person(name varchar(50),age int);
2. create table student(name varchar(50),chinese int,math int,english int)charset=utf8;

//创建一个员工表emp 保存名字,工资和工作 create table emp(name varchar(50),salary int,job varchar(20));

• 查询所有表 格式: show tables;
• 查询表信息 格式: show create table 表名;

1. show create table emp;

• 查询表字段 格式: desc 表名;
• 修改表名 格式: rename table 原名 to 新名;

1. rename table student to stu;

• 删除表 格式: drop table 表名;

添加表字段

• 最后面添加 格式: alter table 表名 add 字段名 类型;
• 最前面添加 格式: alter table 表名 add 字段名 类型 first;
• 在xxx字段后面添加: alter table 表名 add 字段名 类型 after xxx;

1. alter table emp add gender varchar(5);
2. alter table emp add id int first;
3. alter table emp add dept varchar(20) after name;

删除表字段 格式: alter table 表名 drop 字段名;

1. alter table emp drop dept;

修改表字段 格式: alter table 表名 change 原名 新名 新类型;

1. alter table emp change job dept varchar(5);

//创建表 create table t1(name varchar(20),age int) charset=utf8/gbk;//查询所有表 show tables;//查询表信息 show create table t1;//查询表字段 desc t1;//删除表 drop table t1;//修改表名 rename table t1 to t2;//添加表字段 alter table t1 add age int first/after xxx;//删除表字段 alter table t1 drop age;//修改表字段 alter table t1 change 原名 新名 新类型;

数据相关SQL

操作数据必须保证已经使用了某个数据库并且已经准备好了保存数据的表

1. create database mydb5 charset=utf8;
2. use mydb5;
3. create table person(name varchar(50),age int)charset=utf8;

(1)往表中插入数据（添加）

• 全表插入 格式: insert into 表名 values(值1,值2);
• 指定字段插入 格式: insert into 表名(字段1名,字段2名)values(值1,值2);

1. insert into person values(tom,18);
2. insert into person(name) values(jerry);
3. insert into person values(aaa,10),(bbb,20),(ccc,30);
4. insert into person(name) values(xxx),("yyy"),("zzz");

(2)查询数据 格式: select 字段信息 from 表名 where 条件;

1. select name from person;select name,age from person;
2. select * from person;select * from person where age=50;
3. select age from person where name="悟空";

(3)修改数据 格式: update 表名 set 字段名=值 where 条件;

1. update person set age=88 where name=刘备;
2. update person set name=张飞,age=18 where name=关羽;
3. update person set name=黎明 where age=5;

(4)删除数据 格式: delete from 表名 where 条件;

1. delete from person where name=张飞;
2. delete from person where age<30;
3. delete from person;

//1. 创建数据库day1db 字符集utf8并使用create database day1db charset=utf8;use day1db;//2. 创建t_hero表, 有name字段 字符集utf8create table t_hero(name varchar(50))charset=utf8;//3. 修改表名为herorename table t_hero to hero;//4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段alter table hero add money int;alter table hero add id int first;alter table hero add age int after name;//5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888 insert into hero values(1,李白,50,6888),(2,赵云,30,13888),(3,刘备,25,6888);//6. 查询价格为6888的英雄名 select name from hero where money=6888;//7. 修改刘备年龄为52岁update hero set age=52 where name=刘备;//8. 修改年龄小于等于30岁的价格为5000update hero set money=5000 where age<=30;//9. 删除价格为5000的信息delete from hero where money=5000;//10. 删除表, 删除数据库drop table hero;drop database day1db;

主键约束

• 主键: 表示数据唯一性的字段称为主键
• 约束: 创建表时给表字段添加的限制条件
• 主键约束: 限制主键的值 唯一且非空

1. use day2db;create table t1 (id int primary key,name varchar(50))charset=utf8;
2. insert into t1 values(1,"aaa");
3. insert into t1 values(1,"bbb"); //报错 主键值重复insert into t1 values(null,"ccc");

主键约束 自增

自增规则: 从历史最大值基础上 1

1. create table t2(id int primary key auto_increment,name varchar(50))charset=utf8;
2. insert into t2 values(null,"aaa");
3. insert into t2 values(null,"bbb");

SQL语句分类

• DDL: 数据定义语言,包括数据库相关和表相关的SQL语句
• DML: 数据操作语言, 包括增删改查
• DQL: 数据查询语言, 只包含select查询相关的SQL语句
• TCL: 事务控制语言
• DCL: 数据控制语言

数据类型

数据类型

• 整数: int(m)和bigint(m) m代表显示长度, m=5 存18 查询得到00018

ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

取值范围

create table t3(age int(5) zerofill);insert into t3 values(18);select * from t3;

• 浮点数: double(m,d) m代表总长度,d代表小数长度 , 存23.212 m=5 d=3

create table t5(price double(5,3));insert into t5 values(23.32123);insert into t5 values(233.32123); //报错

• 字符串:

1. char(m), 固定长度, m=10 存abc 占10, 执行效率略高, 当保存数据的长度相对固定时使用, 最大值255
2. varchar(m),可变长度,m=10 存abc 占3,更节省空间, 最大值65535 但推荐保存短的数据(255以内)
3. text(m),可变长度, 最大值65535,建议保存长度大于255的

区分

• 日期:

日期

1. date, 只能保存年月日
2. time, 只能保存时分秒
3. datetime, 保存年月日时分秒, 默认值为null , 最大值 9999-12-31
4. timestamp(时间戳,举例1970年1月1日的毫秒数),保存年月日时分秒,默认值为当前系统时间,最大值 2038-1-19

①create table t6(t1 date,t2 time,t3 datetime,t4 timestamp);②insert into t6 values("2022-5-15",null,null,null);③insert into t6 values(null,"14:20:25","2011-10-22 10:20:30",null);

导入*.sql批处理文件

• 在客户端中 执行 source f:/emp.sql;（sql 文件所在目录地址）
• 执行以下SQL语句 检查是否成功

1. show tables; //检查是否出现了 emp和dept两个表
2. select * from emp; //检查是否出现了数据, 如果出现乱码 执行set names utf8;

常用关键词

去重distinct

• 查询员工表中所有不同的工作
• select distinct job from emp;
• 查询员工表中出现了哪几个不同的部门id?
• select distinct dept_id from emp;

is null和is not null

• 查询有领导的员工姓名和领导id
• select name,manager from emp where manager is not null;
• 查询没有领导的员工姓名
• select name from emp where manager is null;

and 和 or

• 查询1号部门工资高于2000的员工信息
• select * from emp where dept_id=1 and sal>2000;
• 查询3号部门或工资等于5000的员工信息
• select * from emp where dept_id=3 or sal=5000;
• 查询出孙悟空和猪八戒的员工信息
• select * from emp where name="孙悟空" or name="猪八戒";

比较运算符 > < >= <= = !=和<>

• 查询工资大于等于3000的员工信息
• select * from emp where sal>=3000;
• 查询工作不是程序员的员工信息(两种写法)
• select * from emp where job!="程序员";
• select * from emp where job<>"程序员";

between x and y 两者之间

查询工资在2000到3000之间的员工信息

1. select * from emp where sal>=2000 and sal<=3000;
2. select * from emp where sal between 2000 and 3000;
3. select * from emp where sal not between 2000 and 3000;

in关键字

查询工资等于5000,1500,3000的员工信息

1. select * from emp where sal=5000 or sal=1500 or sal=3000;
2. select * from emp where sal in(5000,1500,3000);
3. select * from emp where sal not in(5000,1500,3000);

//查询1号部门有哪几种不同的工作select distinct job from emp where dept_id=1;//查询1号部门中有上级领导的员工信息select * from emp where dept_id=1 and manager is not null;//查询工作是程序员,销售和人事的员工信息select * from emp where job in("程序员","销售","人事");//查询工资不在1000-2000之间的员工信息select * from emp where sal not between 1000 and 2000;//查询有奖金的员工信息 select * from emp where comm>0;

模糊查询like

• %: 代表0或多个未知字符
• _:代表1个未知字符

1. 以x开头 x%
2. 以x结尾 %x
3. 包含x %x%
4. 第二个字符是x _x%
5. 以x开头以y结尾 x%y
6. 第二个是x倒数第三个是y _x%y__

//查询名字姓孙的员工信息select * from emp where name like "孙%";//查询工作第二个字是售的员工姓名和工作select name,job from emp where job like "_售%";//查询名字中包含僧并且工资大于2000的员工姓名和工资select name,sal from emp where name like "%僧%" and sal>2000;

排序order by

格式: order by 字段名 asc(升序默认)/desc(降序)

//查询所有员工姓名和工资并按照工资升序排序select name,sal from emp order by sal;select name,sal from emp order by sal asc;//查询所有员工姓名和工资并按照工资降序排序select name,sal from emp order by sal desc;

分页查询

• 格式: limit 跳过的条数,请求的条数(每页的条数)
• 跳过的条数=(请求的页数-1)*请求的条数(每页条数)

//查询工资最高的员工信息select * from emp order by sal desc limit 0,1;//查询按照工资降序第2页的5条数据select * from emp order by sal desc limit 5,5;

别名

• select name as "姓名" from emp ;
• select name "姓名" from emp ;
• select name 姓名 from emp ;

聚合函数

通过聚合函数可以对查询的多条数据进行统计查询,统计查询的方式包括:求平均值, 求最大值,求最小值,求和,计数

//平均值avg(字段名)查询1号部门的平均工资select avg(sal) from emp where dept_id=1;//查询销售的平均工资select avg(sal) from emp where job="销售";//最大值max(字段名)查询程序员的最高工资select max(sal) from emp where job="程序员";//最小值min(字段名)查询3号部门的最低工资select min(sal) from emp where dept_id=3;//求和sum(字段名)查询2号部门的工资总和select sum(sal) from emp where dept_id=2;//计数count(*)查询程序员的数量select count(*) from emp where job="程序员";

数值计算 - * / %

//查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)select name,sal,sal*5 年终奖 from emp;//给3号部门的员工每人涨薪5块钱update emp set sal=sal 5 where dept_id=3;

分组查询 group by

格式: group by 分组的字段名

//查询每个部门的平均工资select dept_id,avg(sal) from emp group by dept_id;//查询每个部门工资高于2000的人数select dept_id,count(*) from emp where sal>2000 group by dept_id;//查询每个部门有领导的员工的人数select dept_id,count(*) from emp where manager is not null group by dept_id;

having

• where后面只能写普通字段的条件,不能包含聚合函数
• having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后

//查询每个部门的平均工资要求平均工资高于2000select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;//查询每种工作的人数,只查询人数大于1 的select job,count(*) from emp group by job having count(*)>1;select job,count(*) c from emp group by job having c>1;//查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;//查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;

UTF8字符集

1. 目前utf8 和utf8mb3 一样 ,代表的是 用3个字节表示一个字符 (mb3=most byte 3)
2. utf8mb4 最多不超过4个字节表示一个字符

中文问题

1. 错误：Incorrect string value
2. 错误原因：客户端和MySQL之间编解码字符集不一致导致的
3. 解决方案: ①修改MySQL的解码字符集为gbk。 set names gbk;②将表的字符集 utf8修改为支持四字节字符集utf8bm4也可。

mysql编解码图

//--1.语法错误：SQL syntax[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near (`sno`,`con`,`degree`) at line 1//--2.未知列：查看表中是否存在con该字段[Err] 1054 - Unknown column con in field list//--3.超过列的值的范围：degree列的长度超了[Err] 1264 - Out of range value for column degree at row 1//--4.值数量与字段数量不匹配：[Err] 1136 - Column count doesnt match value count at row 1//--5.存在重复的主键列[Err] 1062 - Duplicate entry 3-105 for key PRIMARY//--6.定义了多个主键[Err] 1068 - Multiple primary key defined//--7.该表已存在[Err] 1050 - Table grade already exists//--8.找不到该表（该表不存在）[Err] 1051 - Unknown table grade//--9.数据库已存在，无法创建[Err] 1007 - Cant create database user; database exists//--10.数据库不存在，不能删除[Err] 1008 - Cant drop database school; database doesnt exist//--11.在where 语句中，6-166字段是未知的[Err] 1054 - Unknown column 6-166 in where clause//--12.日期格式不正确：取值范围：月：1-12 日：1-31[Err] 1292 - Incorrect datetime value: 1100-02-80 for column tbirthday at row 1//--13.子查询中返回字段数不唯一。[Err] 1241 - Operand should contain 1 column(s)//-- 错误13示例:子查询中只需要查询sid的值，实际查询了sid 和 sname的值select sname,sid from student where sid in(select sid,sname from sc where score is not null and score !="");//-- 14.子查询返回值不唯一[Err] 1242 - Subquery returns more than 1 row--错误14示例:子查询中sid返回的结果值不唯一，父句中的where条件后的sid不能用=连接，应用inselect sname,sid from student where sid = (select sid from sc where score is not null and score !="");//-- 注意，13和14的两种错误是不一样的。//--15.未选择数据库[Err] 1046 - No database selected//--16.该列的列类型使用的约束或其他有问题。例如自增不能用于varchar类型的数据[Err] 1063 - Incorrect column specifier for column id

学习记录，如有侵权请联系删除。