首页 > 数据库 >MySQL 基本语句总结

MySQL 基本语句总结

时间：2023-06-19 22:11:35浏览次数：37  
标签：语句 总结 sal 查询 emp student MySQL WHERE SELECT

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;

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);

多表查询

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`;

~感谢您的光临~

标签：语句,总结,sal,查询,emp,student,MySQL,WHERE,SELECT
From： https://www.cnblogs.com/niuyun/p/17492355.html

相关文章

  • 错误：“java.lang.ClassNotFoundException: com.mysql.jdbc.Driver“ 如何解决？
    ——运行项目时遇到java.lang.ClassNotFoundException:com.mysql.jdbc.Driver问题，解决方案:错误如下：java.lang.ClassNotFoundException:com.mysql.jdbc.Driver atorg.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1358) atorg.......
  • MySQL入门基础知识
    1MySQL基础1.1常用命令启动服务：netstart服务名停止服务：netstop服务名启动MySQL服务器：MySQL【-h主机名-P端口号】-u用户名-p密码查看MySQL版本：MySQL--version或MySQL–V或登录到MySQL服务端使用selectversion();1.2语法规范每条命令最好用分号结尾每条......
  • MySQL DQL语法汇总
    1数据准备1.1建表语句创建四个表：地址表、部门表、职位表和员工表：CREATETABLE`locations`(`location_id`INTNOTNULLAUTO_INCREMENT,`street_address`VARCHAR(40)DEFAULTNULL,`postal_code`VARCHAR(12)DEFAULTNULL,`city`VARCHAR(30)DEFAULTNULL,......
  • 6月19日学习总结
    今日主要学习了Java中的部分集合，如Arraylist，Collection，以及使用了集合的方法如add(),remove,get(),clear()等,同时了解了StringBuilder类（字符串缓冲区），此类能够高效处理字符串内存缓存问题，以及字符串转换方法toString()与字符串拼接或增加append()方法，也了解了迭代器Iterator，学会了......
  • Docker PHP如何安装mysqli扩展
    查找用于安装PHP扩展的命令文件whichdocker-php-ext-install安装mysqli扩展docker-php-ext-installmysqli需要注意的是，docker-php-ext-install命令仅适用于基于PHP的Docker镜像，并且需要与DockerCompose文件一起使用，以便在容器中运行PHP应用程序。......
  • MYSQL经典练习题
    题目来源：https://blog.csdn.net/flycat296/article/details/63681089Github地址：https://github.com/bladeXue/sql50添加测试数据库信息#创建数据库createdatabasesql50;usesql50;#学生表createtableStudent(SIdvarchar(10),Snamevarchar(10),Sagedatetime,Sse......
  • mysql 分组前3条
    测试数据DROPTABLEIFEXISTS`emp`;CREATETABLE`emp`(`empno`decimal(4,0)NOTNULL,`ename`varchar(10)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,`job`varchar(9)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULL,......
  • 总结加分
    课堂提问+5到软件体系结构开学刚开始大型数据库课堂测试第3完成，在老师的纸上记录了 老师酌情加分到软件体系架构。 ......
  • 阶段性知识总结解释版【Day01-Day25】
    day021.什么是编程和编程语言编程 是指使用计算机语言编写计算机程序的过程。编程语言 是一种用于编写计算机程序的形式化语言，它可以被解释器或编译器转换成机器码以便计算机执行。编程语言包括C、Java、Python、JavaScript、PHP等。2.计算机五大组成部分，分别阐释一......
  • 阶段性知识总结习题版【Day01-Day25】
    day02什么是编程和编程语言计算机五大组成部分，分别阐释一下各自特点计算机三大核心硬件，各自的特点常见的操作系统day03计算机存储数据的单位有哪些，之间的单位换算是怎样的编程语言的发展史，分别有什么特点编程语言的分类python解释器的版本有哪些，推荐使用的版本是哪个......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99