MySQL 基本语句总结

牛·云说

已于 2023-08-23 10:34:14 修改

阅读量973

点赞数 2

分类专栏： MySQL 文章标签： mysql sql 数据库

于 2021-05-22 17:25:28 首次发布

本文链接：https://blog.csdn.net/m0_50762431/article/details/115802002

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL语句，需要知道一下~

总结的一些MySQL语句，仅供参考

创建数据库

create database mydb1; 
create database if not exists mydb2 character set GBK; #(不为空，字符集)
create database mydb3 character set GBK collate  gbk_chinese_ci;#（字符集，级别）

查看所有数据库

show databases;

查看前面创建的mydb2数据库的定义信息

show create database mydb2;

修改字符集

alter database mydb2 character set utf8;

删除数据库

drop database if exists mydb3;

查看当前使用的数据库

Select database(); #没有选择数据 null

切换使用数据库

USE mydb2;

退出数据库

quit;或exit;

临时表的创建、查询、删除

创建

CREATE TEMPORARY TABLE qyzcursor AS
SELECT a.id, a.name, a.publishTime, a.Unit, a.type
FROM myplan a
GROUP BY a.id;

查询

select * from qyzcursor;

删除

DROP TEMPORARY TABLE IF EXISTS qyzcursor;

SQL分类

1.DDL，数据定义语言

create：创建，alter：修改，drop：删除，rename：重命名，truncate：清空

删除现有表

drop table table_name;

修改表名为user

rename table student to user;

展示数据库中的所有表

show table;

查看表的字段信息

desc student;

查看表的创建细节

show create table user

修改表的字符集为gbk

alter table user character set gbk;

现表中增加一列

alter table  student add image blob;

删除一列,一次只能删一列。

alter table student drop image;

修改列字段的长度

alter table student modify address varchar(60);

列名name修改为username

alter table user change name username varchar(100);

truncate删除

truncate table emp;#删除表，然后创建一个空表

delete #删除表中的数据，表结构还在;删除后的数据使用日志可以找回。
truncate #删除是把表直接DROP掉，然后再创建一个同样的新表。
truncate #删除的数据不能找回。执行速度比DELETE快。

2.DML，数据操作语言

insert：添加，delete：删除，update：修改，select：查询

插入操作insert

一次添加一条数据

INSERT INTO student(id,name,age,address) values (1,'xiaoliu',20,'山西太原'); 
INSERT INTO student(id,name,age,address) values (2,'xaioqiang',22,'陕西西安');
INSERT INTO student(id,name,age,address) values (3,'xiaohu',23,'湖北武汉');

一次添加多条数据

INSERT INTO student(id,name,age,address) values (1,'xiaoliu',20,'山西太原'),
												(2,'xaioqiang',22,'陕西西安'),
												(3,'xiaohu',23,'湖北武汉');

小知识

#查看数据库编码的具体信息
Show variables like ‘character%’;
#临时更改客户端和服务器结果集的编码
Set character_set_client=gbk;
Set character_set_results=gbk;
#或者
SET NAMES ‘gbk’; // client connection results

3.修改操作 · UPDATE:

update user set name="xaioqi",age=12,gender='男' where id=2;

将所有学生的年龄修改为25。

update student set age=25;

将id为’1’的地址修改为“河北保定”。

update student SET address='河北保定' WHERE id=1;

将所有学生的年龄加5岁。

update student SET age=age+5;

删除操作 · DELETE：

delete from user where id=2;

删除表中姓名为’zhangsan’的记录。

delete from student where name=‘zhangsan’;

删除表中所有记录。

delete from emp;

3.DCL，数据控制语言

commit：提交，rollback：回滚，savepoint：事物

（单独）DQL，数据查询语言

—— SELECT 列名 FROM 表名【WHERE --> GROUP BY–>HAVING–> ORDER BY–>LIMIT】

查询所有列 *表示所有列

select * from stu;

查询指定列

select id,name,age from stu;

查询性别为女，并且年龄小于50的记录 ,用and;

select * from stu where gender='female' and age<50;

查询学号为S_1001，或者姓名为liSi的记录 ,用or；

SELECT * FROM stu WHERE sid =1 OR sname='liSi';

查询学号为S _ 1001 ，S _ 1002，S _ 1003的记录，用in或者or

SELECT * FROM stu WHERE sid in ('S_1001','S_1002','S_1003'); 
#等同于 
SELECT * FROM stu WHERE sid='S_1001' or sid='S_1002' or sid='S_1003';

查询学号不是S_1001，S_1002，S_1003的记录 ,用not in

SELECT * FROM student WHERE sid NOT IN('S1001','S1002','S_1003');

查询年龄为null的记录 ,is null

SELECT * FROM stu WHERE age IS NULL;

查询年龄在20到40之间的学生记录，用>=，<=，或者between

SELECT * FROM stu WHERE age>=20 AND age<=40;
#或者
SELECT * FROM stu WHERE age between 20 AND 40;

查询性别非男的学生记录，用!=，<>，not

SELECT * FROM stu WHERE gender!='male';
#或者
SELECT * FROM stu WHERE gender<>'male';
#或者
SELECT * FROM stu WHERE NOT gender='male';

查询姓名不为null的学生记录 用is null

SELECT * FROM stu WHERE NOT sname IS NULL;

模糊

查询姓名由3个字符构成的学生记录,用"_"，任意一个字符

SELECT * FROM stu WHERE sname LIKE '___';

查询姓名由5个字符构成，并且第5个字符为“i”的学生记录,用"_i"

SELECT * FROM stu WHERE sname LIKE '____i';

查询姓名以“z”开头的学生记录 ,用"z%"，其中“%”匹配0~n个任何字符。

SELECT * FROM stu WHERE sname LIKE 'z%';

查询姓名中第2个字符为“i”的学生记录 ,用"_i%"

SELECT * FROM stu WHERE sname LIKE '_i%';

查询姓名中包含“a”字符的学生记录,用"%a%"

SELECT * FROM stu WHERE sname LIKE '%a%';

字段控制

去除重复记录,用distinct

select distinct sal FROM emp; 

select distinct sal,comm FROM emp;

查看雇员的月薪与佣金之和，字符串相加concat

SELECT *,sal+comm FROM emp;
#字符串的合并不能使用+ ,使用concat(ename,'____',job);
select *,concat(ename,'______',job) from emp;

与null相加为null，将null转化成数值0的函数ifnull

select *,money+IFNULL(salary,0) from emp;

给列名添加别名 用as

select *,money+IFNULL(salary,0) as total from emp;
#可以不写，省略as
select *,money+IFNULL(salary,0) total from emp;

排序

查询所有学生记录，按年龄升序排序,用order by，asc

select * from stu ORDER BY age ASC;#asc升序
#或者
select * from stu ORDER BY age;

查询所有学生记录，按年龄降序排序，用order by，desc

select * from stu ORDER BY age desc;#desc降序

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序用order by，desc，asc

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

聚合函数

聚合函数是用来做纵向运算的函数：

l COUNT()：统计指定列不为NULL的记录行数；

l MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

l MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

l SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

l AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

查询emp表中记录数：用count（）总数

SELECT COUNT(*) AS ‘cnt’ FROM emp;

查询emp表中有佣金的人数：

SELECT COUNT(name) ‘cnt’ FROM emp;
#因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp WHERE sal > 2500;

统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

查询有佣金的人数，以及有领导的人数：

SELECT COUNT(comm), COUNT(mgr)FROM emp;

查询所有雇员月薪和：用sum（）相加

SELECT SUM(sal) FROM emp;

查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0))FROM emp;

统计所有员工平均工资：用avg（）平均

SELECT AVG(sal) FROM emp;

查询最高工资和最低工资：用max（）最大，min（）最小

SELECT MAX(sal), MIN(sal) FROM emp;

查询每个部门的部门编号和每个部门的工资和：用group by分组

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

查询工资总和大于9000的部门编号以及工资和：用having子句

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

注：having与where的区别:

1.having是在分组后对数据进行过滤,where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。

查询前5行记录，起始行从0开始 用limit限制

SELECT * FROM emp LIMIT 0, 5;

日期函数

下一个星期用next_day

select sysdate "当前日期",next_day(sysday,'星期一') 下周星期一 from emp;

最后一天用next_day

--所在月份的最后一天
select ename , birthdate,last_day(birthdate) from emp;

最近日子 round

select sysdate 当时日期,
	round(sysdate) 最近0点日期,
	round(sysdate,'day') 最近星期日,
	round(sysdate,'mouth') 最近月初,
	round(sysdate,'q') 最近季初日期,
	round(sysdate,'year') 最近年初日期
	from numTable;

返回当前日期，只包含年月日

CURDATE()，CURRENT_DATE()

返回当前时间，只包含时分秒

CURTIME()，CURRENT_TIME()```

返回当前系统日期和时间

NOW()/SYSDATE()/CURRENT_TIMESTAMP()/LOCALTIME()/LOCALTIMESTAMP()

返回UTC（世界标准时间）日期

UTC_DATE()

返回UTC（世界标准时间）时间

UTC_TIME()

时间和时间戳的转换

SELECT UNIX_TIMESTAMP(‘2021-10-01 12:12:32’),FROM_UNIXTIME(1635173853) FORM DUAL;

YEAR()返回年，MONTH()返回月，DAY()返回日，HOUR()返回时，MINUTE()返回分，SECOND()返回秒
返回月份,July

MONTHNAME(DATE)

返回星期几：MONDAY

DAYNAME(DATE)

返回周几：注意周1是0

WEEKDAY(DATE)

返回日期对应的季度，范围为1~4

QUARTER(date)

返回一年中的第几周

WEEK(DATE)，WEEKOFYEAR(DATE)

返回日期是一年中的第几天

DAYOFYEAR(DATE)

返回日期位于所在月份的第几天

DAYOFMONTH(DATE)

返回time1减去time2的时间，当time2为数字时，代表为秒，可以为负数

SUBTIME(time1,time2)

返回date1-date2的日期间隔天数

DATEDIFF(date1-date2)

返回time1-time2的时间间隔

TIMEDIFF(time，time2)

返回0000年1月1日起，n天以后的日期

FROM_DAYS(N)

返回日期date距离0000年1月1日的天数

TO_DAY(date)

返回date所在月份的最后一天的日期

LAST_DAY	(date)

将给定的小时、分钟和秒组合成时间并返回

MARKTIME(hour,minute,second)

返回time加上n后的时间

PERIOD_ADD(TIME,N)

总结

查询语句书写顺序：select 列 from 表【where- group by- having- order by-limit】

查询语句执行顺序：from 表 where --> group by --> having --> select --> order by --> limit

主键约束

添加主键，primary key,添加方式

CREATE TABLE student( 
    id int primary key, 
    name varchar(50)
);
#或者
CREATE TABLE student( 
    id int, 
    name varchar(50),
    primary key(id)
    #此种方式优势在于，可以创建联合主键
);
CREATE TABLE student( 
    classid int, 
    stuid int, 
    name varchar(50),
    primary key(classid，stuid) 
);
#或者
CREATE TABLE student( 
    id int,
    name varchar(50) 
);
    ALTER TABLE student ADD PRIMARY KEY (id);#不推荐

唯一约束

数据不可以重复，用unique，可以为null

CREATE TABLE student( 
    Id int primary key, 
    Name varchar(50) unique 
);

自动增长，用auto_increment自增

CREATE TABLE student( 
    Id int primary key auto_increment, 
    Name varchar(50) 
) auto_increment=100; 
INSERT INTO student(name) values(‘tom’);

not null 非空

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null, 
    Sex varchar(10) 
);
    insert into student values(1,’tom’,null);

default默认值

CREATE TABLE student( 
    Id int primary key,
    Name varchar(50) not null, 
    Sex varchar(10) default '男' 
);
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);

外键约束

外键约束：**FOREIGN KEY(foreign key) **

#学生表(主表) 
CREATE TABLE student( 
    sid int primary key, 
    name varchar(50) not null, 
    sex varchar(10) default '男' 
);
#成绩表（从表） 
create table score( 
    id int, score int, 
    sid int , 
    constraint fk_score_sid foreign key(sid) references student(sid) 
);
-- 外键列的数据类型一定要与主键的类型一致.references参考，constraint约束

#或者
ALTER TABLE score ADD constraint fk_stu_score FOREIGN KEY(sid) references student(sid);

启动和关闭外键约束的方法(0为开启，1为关闭)
SET FOREIGN_KEY_CHECKS=1;

多表查询

l UNION：去除重复记录，例如：SELECT* FROM table1 UNION SELECT * FROM table2；

l UNION ALL：不去除重复记录，例如：SELECT * FROM table1 UNION ALL SELECT * FROM table2。

select * from table1,table2;

联表

SELECT table1.ename,table1.sal,table1.comm,user.dname FROM table1,user WHERE table1.deptno=user.deptno;

内连接

SELECT * FROM school s INNER JOIN class c ON s.deptno=c.deptno; 
#注意：on后面 主外键关系

外连接

a.左外连接：以左表为主表，右表是从表

SELECT * FROM school s LEFT OUTER JOIN class c ON s.deptno=c.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，左表中满足条件和不满足条件都显示出来，

右边不满足条件的显示NULL。

**右外连接 **

SELECT * FROM school s RIGHT OUTER JOIN class c ON s.deptno=c.deptno;

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept

表中的某部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出某部门，但相应的员

工信息为NULL。

子查询

子查询结果集的常见形式：

a. 单行单列（用于条件）

b. 多行单列（用于条件）

c. 多行多列（用于表）

示例

1.工资高于JONES的员工。

查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资

第二步：查询高于JONES工资的员工

结果：

SELECT sal FROM emp WHERE ename='JONES';

SELECT * FROM emp WHERE sal > (第一步结果);

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');

2.查询与SCOTT同一个部门的员工。

子查询作为条件

子查询形式为单行单列

查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。

第一步：查询SCOTT的部门编号

SELECT deptno FROM emp WHERE ename='SCOTT';

第二步：查询部门编号等于SCOTT的部门编号的员工

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');

3. 工资高于30号部门所有人的员工信息

分析：

SELECT * FROMemp WHERE sal>(SELECT MAX(sal)FROM emp WHERE deptno=30);

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键

字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (第一步)

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

l 子查询作为条件

l 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

导出导入数据库

导出数据库表

mysqldump -u root -p 数据库名 > school.sql

导入数据库表

mysql -u root -p
mysql>use 数据库 #然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/dbname.sql

创建用户和授权

创建用户

CREATE USER `zhangsan` IDENTIFIED BY '123';
CREATE USER `zhangsan`@`localhost` IDENTIFIED BY '123';
CREATE USER `zhangsan`@`%` IDENTIFIED BY '123';

授权

GRANT ALL ON school.* TO `zhangsan`;

撤销权限

DROP USER `zhangsan`;

于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (第一步)

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

l 子查询作为条件

l 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

导出导入数据库

导出数据库表

mysqldump -u root -p 数据库名 > school.sql

导入数据库表

mysql -u root -p
mysql>use #数据库 然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/dbname.sql

创建用户和授权

创建用户

CREATE USER `zhangsan` IDENTIFIED BY '123';
CREATE USER `zhangsan`@`localhost` IDENTIFIED BY '123';
CREATE USER `zhangsan`@`%` IDENTIFIED BY '123';

授权

GRANT ALL ON school.* TO `zhangsan`;

撤销权限

DROP USER `zhangsan`;

查询用户对某个表的权限

SHOW GRANTS FOR current_user;

如果用户没有访问表的权限，可以使用以下命令给予权限：

GRANT SELECT, INSERT, DELETE, UPDATE ON database_name.table_name TO 'user_name'@'localhost';

这个命令将授予’user_name’@'localhost’用户对database_name.table_name表的SELECT，INSERT，DELETE和UPDATE权限。

如果想要撤销权限，可以使用以下命令：

REVOKE SELECT, INSERT, DELETE, UPDATE ON database_name.table_name FROM 'user_name'@'localhost';

这个命令将撤销’user_name’@'localhost’用户对database_name.table_name表的SELECT，INSERT，DELETE和UPDATE权限。

在MySQL中，安全性非常重要。唯一授予权限给知道如何操作数据库的人是非常必要的。在授予权限时，请根据底层数据源的安全要求和最佳实践来授予权限

服务器连接mysql数据库，报错message from server: “Host xxx is not allowed to connect to this MySQL server“

错误描述：这里报的错误是因为该mysql权限只允许本地连接，不允许外部连接，这个时候就需要修改权限了，如下修改步骤：
1.进入 MySQL 的bin目录，管理员模式下快捷键打开cmd，输入mysql用户名和密码，出现以下内容就进去了；
在这里插入图片描述
2.输入use mysql —— 使用mysql库
3.输入 update user set host = “%” where user = “root”; 修改权限
4.flush privileges; 刷新权限

修改完成，重新连接即可

`~感谢您的光临~`

牛·云说

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
3
评论
MySQL 基本语句总结

MySQL语句创建数据库CREATE DATABASE mydb1; CREATE DATABASE IF NOT EXISTS mydb2 character SET GBK; #(不为空，字符集)CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;#（字符集，级别）查看所有数据库SHOW DATABASES;查看前面创建的mydb2数据库的定义信息ShOW CREATE DATABASE mydb2;修改字
复制链接

扫一扫