文章目录
前言
这篇文章是基于cmd控制台操作的数据库教程。使用前需要安装mysql数据库,并开启mysql服务。
SQL语句规范
- SQL语句不区分大小写。但字符串常量区分大小写,建议命令大写,表名库名小写;
- SQL语句可单行或多行书写,以分号结尾;
- 用空格和缩进来提高语句的可读性。
- 注释:有三种风格的注释
1.单行注释可以用“#”
2.单行注释第二种写法用“-- ”,“–" 与注释之间是有空格的。
3.多行注释可以用/* */
一、数据库的基本操作
- SOURCE ×××.sql
导入sql文件。sql文件实际上是一个脚本,里面有多行sql语句,通过命令可以批量执行。 - SHOW DATABASES;
查看所有数据库。 - USE 数据库名;
选择使用哪个数据库。 - SELECT DATABASE();
查看当前选择的是哪个数据库。 - SHOW TABLES;
查看该数据库下所有的数据表。 - DESC 表名;
查看某表的结构(有哪些字段) - CREATE DATABASE 数据库名;
自己创建数据库 - DROP DATABASE 数据库名;
删除数据库
二、基本查询
MySQL数据库使用select语句来查询数据
1.查询多个字段
SELECT 字段名,字段名… FROM 表名; #查询某些字段,用(,)分隔
SELECT * FROM 表名; #查询所有字段
2.设置别名
SELECT 字段1 AS 别名,字段2 AS 别名… FROM 表名;
简写形式:
SELECT 字段1 ‘别名’,字段2 ‘别名’ FROM 表名;
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;#如果是多个字段,只有都一样才会被去重,否则不会
三、条件查询
有条件地从表中选出数据。
SELECT 字段名 FROM 表名 WHERE 条件;
1.运算符
- 四则运算符
+(加法)、-(减法)、*(乘法)、/(除法)、%(求余) - 关系运算符
(大于)、>=(大于等于)、<(小于)、<=(小于等于)、=(等于)、<>或!=(不等于)
- 逻辑运算符
AND 或 &&(并且,多个条件同时成立)、OR 或 ||(或者,多个条件中的任意一个成立)、NOT 或 !(非,不是) - 其它
- BETWEEN…AND…(在某个范围之间,包括最大最小值)
- IN…(多个值,多个值中的一个满足就行)
- LIKE…(模糊匹配,_匹配单个字符,%匹配任意个字符)
比如:需要匹配第2个字符是s,第4个字符是y的4个字符的字符串,可以这样写_s_y - IS[NOT]NULL(是否为NULL)
四、函数
1.聚合函数(统计函数)
- MAX(column)
返回某列最大值 - MIN(column)
返回某列最小值 - COUNT(column)
返回某列行数 - SUM(column)
求和 - AVG(column)
求平均值
2.字符串函数
- UPPER(列|字符串) 将一列或者某个字符串中的每个字符转为大写,与之相反的LOWER
- CONTACT(str1,str2…) 将所有字符串接成一个字符串(按顺序接)
- REPLACE(列|字符串,新字符串) 使用新字符串替换旧字符串
- LENGTH(列|字符串) 求字符串长度
- SUBSTR(列|字符串) 字符串截取
- LEFT(str,len) 获取字符串左边len个字符组成的字符
- RIGHT(str,len) 获取字符串右边len个字符组成的字符串
- MID(str,pos,len) 获取字符串中从pos位置开始,长度为len的字符串
- ASCII(字符) 返回与指定字符对应的十进制整数
- CHR(数字) 返回与整数对应的字符
- RPAD(列|字符串,长度,填充字符) 用指定的字符在字符串右填充,对应的LPAD
- LTRIM(字符串) 去掉字符串左边的空格,对应的RTRIM
- INSTR(列|字符串,要查找的字符串,开始位置,出现位置) 查找一个子字符串是否在指定的位置上出现
3.数学函数
- ROUND(数字[,保留位数) 根据指定的保留位数对小数进行四舍五入,如果不指定,则小数点后的数字进行四舍五入
- TRUNCATE(数字[,截取位数]) 保留指定位数的小数,如果不指定,则表示不保留
- CEIL(数字),CEILING(数字) 对括号内的小数向上取整,对应的FLOOR想下取整
- RAND([n]) 随机返回0到0.1之间的小数,如果有n,则每次产生的都是一样的
4.日期和时间函数
- CURDATE(),CURRENT_DATE() 返回当前日期
- CURTIME(),CURRENT_TIME() 返回当前时间
- NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP() 返回当前日期和时间
- YEAR(日期) 返回日期中的年份(注意日期要字符串的形式),同理还有MONTH,DAY,HOUR,MINUTE,SECOND
- DAYOFWEEK(日期) 返回星期几(返回1代表星期日,2星期一。。。),WEEKDAY也是返回星期几(0代表星期一,1代表星期二)
- DAYOFYEAR(日期) 计算指定日期是本年第几天,同理有DAYOFMONTH本月
- QUARTER(日期) 计算日期是第几季度
- TIME_TO_SEC(time) 将指定时间转换为秒
- SEC_TO_TIME(sec) 将以秒为单位的时间转换为时分秒的格式
- DATEDIFF(d1,d2) 计算两个日期相隔的天数
- ADDDATE(date,n) 计算指定日期加上n天后的日期,同理有SUBDATE(date,n)
- ADDTIME(time,n) 计算指定时间加上n秒后的时间,同理有SUBTIME(time,n)
- DATE_FORMAT(date,f) 按格式返回日期
f中,格式,年%Y,月%m,日%d - TIME_FORMAT(time,f) 按格式返回时间
f中,格式,时%h,分%i,秒%s
5.条件判断函数
- IF(expr,v1,v2) 如果表达式expr成立,返回结果v1,否则返回v2
- IFNULL(v1,v2) 如果v1的值不为NULL,则返回v1,否则返回v2
- CASE 具体用法如下
CASE
WHEN expr1 THEN V1
WHEN expr2 THEN V2
…
ELSE V3
END
配合SELECT使用:
SELECT CASE sal
WHEN 800
THEN ‘显示的字符串’ #如果字段sal等于800则
WHEN 2000 #如果字段sal等于2000则
THEN ‘显示的字符串’
ELSE ‘显示的字符串’ #不满足以上显示的字符串
END
FROM 表名;
注意:当满足上一个WHEN时,后面的都不会执行了
6.系统信息函数
- VERSION() 返回数据库的版本号
- CONNECTION_ID() 返回服务器的连接数
- DATABASE()、SCHEMA 返回当前数据库名
- USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER 返回当前用户
- CHARSET(str) 返回字符串str的字符集
- COLLATION(str) 返回字符串str的字符排列方式
7.加密函数
- MD5(str) 对字符串str进行散列,可以用于一些普通的不需要解密的数据加密,如密码
- sha1(str) 对字符串str进行散列,可以用于一些普通的不需要解密的数据加密,如密码,比MD5更安全
8.其它函数
1.进制转换
- BIN(x) 返回x的二进制
- HEX(x) 返回x的十六进制
- OCT(x) 返回x的八进制
- CONV(x,from_base,to_base) 将from_base进制的x,转成to_base进制
2.ip地址转换
- INET_ATON(IP) 将点分十进制的IP地址转为数字
- INET_NTOA(number) 将数字形式的IP转为点分十进制
五、分组查询
将多条且某个字段中有相同值的数据分为一组。
SELECT 分组字段/聚合函数 FROM 表名[WHERE 条件] GROUP BY 分组字段 [分组后的操作];
举个例子:统计出每种职位的最低和最高工资
Tips: 员工表emp中,每个员工的部门名在job字段中,工资在sal字段中
SELECT job,MIN(sal),MAX(sal) FROM emp GROUP BY job;
注意事项:
1.如果查询中不存在GROUP BY子句,那么如果SELECT有聚合函数,那就不允许有字段出现。
可以想象一下表的结构,如果两者同时存在,那么每一行中聚合函数那一列应该填什么?似乎很不合理,所以不允许这样
2.如果查询中有GROUP BY子句,SELECT中只允许出现用于分组的字段(例子中的job)和聚合函数。
WHERE 和 HAVING的区别
- WHERE是分组之前使用,且不允许使用聚合函数。
分组之前进行筛选 - HAVING是分组之后使用(配合GROUP BY),允许使用统计函数。
分组之后进行筛选
六、排序查询
SELECT 字段名 FROM 表名 ORDER BY 用于排序的字段名 ASC|DESC,[用于二次排序的字段名 ASC|DESC ]…;
不写ASC|DESC,默认ASC
注意事项
1.ORDER BY子句一般放到查询语句的最后面,如果有LIMIT就放LIMIT前
2.当排序的字段中存在空值时,ORDER BY 会将该空值作为最小值来对待
3.ORDER BY 指定多个字段进行排序时,MySQL 会按照字段的顺序从左到右依次进行排序(先按第一个排,并在第一个字段重复中按第二个字段)
七、分页查询
SELECT 字段名 FROM 表名 LIMIT 起始索引,查询记录数;
如果查询的是第一页可以省略起始索引。
注意:起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数
举个例子:查询第二页,每页10条记录
SELECT job FROM emp LIMIT 10,10;