mysql基础mysql服务启动,关闭启动MySQL服务 net start mysql 关闭MySQL服务 net

mysql服务启动,关闭

启动MySQL服务

net start mysql

关闭MySQL服务

net stop mysql

mysql登录,退出,查询用户,查询指令用法

登录mysql

mysql -h 主机IP -P端口 -u用户名 -p密码

退出mysql

quit
exit
\q

查询mysql中的用户及其权限

SELECT * FROM MYSQL.USER;

显示授权用户的安全权限

SHOW GRANTS;

输入完SQL语句后以;或者\g结束

查询指令

HELP 指令(例如:SELECT);

显示服务器的错误或者警告消息

SHOW ERRORS;
SHOW WARNINGS;

创建,删除,备份,还原,查询,选择数据库

创建数据库

CREATE DATABASE 数据库名字 CHARACTER SET 字符集 COLLATE 校对规则;

删除数据库

DROP DATABASE 数据库名字;

备份数据库(dos下执行)

mysqldum -u root -p -B 数据库1 数据库2 > 备份路径

还原数据库(登录到数据库服务器后进行还原)

source 备份库所在路径

查询MySQL服务器中的所有数据库

SHOW DATABASES;

查询指定数据库的参数

SHOW CREATE DATABASE 数据库名字;

Tips : 名称最好用反引号``来包括起来,防止关键字.

选择数据库

USE 数据库名字;

创建,删除,查询,修改,备份,还原表

创建表

CREATE TABLE 表的名称 (
    field01 datatype NOT NULL DEFAULT 值1,
    field02 datatype NOT NULL DEFAULT 值2,
    PRIMARY KEY ('字段')
    )
 CHARACTER SET 字符集
 COLLATE 校对规则
 ENGINE 存储引擎;

删除表(进入具体数据库后删除)

DROP TABLE 表名称;

查询表的结构

DESC 表名;
DESCRIBE 表名;
SHOW COLUMNS FROM 表名;

修改表名

RENAME TABLE 表名1 TO 表名2;

修改表的字符集

ALTER TABLE 表名 CHARACTER SET 字符编码;

备份表(dos下执行)

mysqldump -u root -p 数据库名字表1 表2 > 备份路径

还原表(进入具体数据库后还原)

source 备份表所在的路径

增加,删除,修改字段

增加列

ALTER TABLE 表名 ADD field datatype NOT NULL DEFAULT 值 AFTER 某个列;

删除列

ALTER TABLE 表名 DROP 列名;

修改列的数据类型或者长度

ALTER TABLE 表名 MODIFY 列名新数据类型(新数据类型长度) NOT NULL DEFAULT 值;

修改列名

ALTER TABLE 表名 CHANGE NAME 表名1 表名2 datatype NOT NULL DEFAULT 值;

增加,删除,修改数据

增加

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...),(值1, 值2, 值3, ...);

Tips : 字符和日期类数据应包含在单引号中. 如果是给所有字段添加数据,可以不写前面的字段名

删除(删除整行)

DELETE FROM 表名 where 列名 = 需要删除的值;

修改

UPDATE 表名 SET 需要更新字段1 = 值1,需要更新字段2 = 值2 where 索引列2 = 索引值;

查询数据

查询SELECT的简单使用

SELECT 字段1, 字段2 FROM 表名;(可以用*代替所有字段)

  select语句查询顺序
  
  SELECT column1, column2, ... columnx FROM table
  	   WHERE 表达式,
  	   GROUP BY column,
  	   HAVING condition,
  	   ORDER BY column,
  	   LIMIT start OFFSET rows;

去除重复数据DISTINCT

SELECT DISTINCT 字段 FROM 表名

DISTINCT不能部分使用

对于这张表(test)来说:
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
|    2 | tom  |
+------+------+
使用SELECT DISTINCT name, id from test;结果是:
+------+------+
| name | id   |
+------+------+
| tom  |    1 |
| tom  |    2 |
+------+------+

若要用在表达式中的话,直接在字段前面加distinct就行

example : COUNT(DISTINCT 字段)

限制结果LIMIT(一般用作数据分页)

用法	作用
`SELECT * FROM 表名 LIMIT x;`	取出前x行数据
`LIMIT start, rows;`	从start行开始取出rows行

[ 每页显示的记录数 * (第几页 - 1), 每页显示记录数 ]

注意 : 行0 ! LIMIT 1,1检索出来的是第一行

MySql 5 LIMIT新用法

LIMIT start OFFSET rows

完全限定的表名

SELECT * FROM 数据库名字.表名;(不用切换数据库)

ORDER BY(默认升序)排序数据

按照单个列排序

SELECT 字段1 FROM 表1 ORDER BY 字段1 ASC(升序)DESC(降序);

如果数据内容为英文字母,一般按照首字母进行排序(字典排序不区分大小写MySql的默认)

按照多个列排序

SELECT * FROM 表1 ORDER BY 字段1 ASC, 字段2 ASC;

先按照字段1排序,若字段1碰到相同的数据则按照字段2排序.

SELECT后面紧跟的字段是显示的字段,至于排序字段不显示也行.

WHERE过滤数据

SELECT 字段1 FROM 表名 WHERE 表达式;

WHERE子句操作符

> < = !=
字段1 BETWEEN 值1 AND 值2[值1, 值2]
<>不等于
IS一般用来空值检查

组合WHERE子句过滤数据

AND OR
IN(值1, 值2, 值3...)
NOT

MySQL中NOT支持对IN,BETWEEN和EXISTS子句取反

通配符+谓词LIKE过滤

SELECT 字段1 FROM 表名 WHERE 字段1 LIKE 值2

%通配符

%匹配的是多个字符

用法	说明
`LIKE 'x%' (NOT LIKE)`	显示x起头的数据(注意配置MySQL时是否区分大小写)
`LIKE 'y%'`	显示以y结尾的数据
`LIKE '%x%'`	显示任何位置包含x的数据
`LIKE 'x%y'`	显示x开头y结尾的字符

_通配符

_匹配的是单个字符

+----------+
| job      |
+----------+
| clerk    |
| salesman |
| alerk    |
| alerkger |
| manager  |
| malager  |
| malagxr  |
+----------+

SELECT job FROM emp WHERE job LIKE '_lerk';
结果:
+-------+
| job   |
+-------+
| clerk |
| alerk |
+-------+

`% + _` 通配符组合使用

LIKE '_x%' 显示第二个字符是x的数据(有n个下划线从n+1开始)

正则表达式进行搜索

计算字段

拼接字段

SELECT CONCAT (字段1, 字段2) FROM 表名;

mysql采用的是CONCAT函数对字段拼接进行处理,不同于其他DMBS的+或者||

使用别名(导出列)

SELECT CONCAT (字段1, 字段2) AS 别名 FROM 表名;

+------+-------+-------+
| id   | name  | price |
+------+-------+-------+
|    1 | A香蕉 |     5 |
|    2 | a香蕉 |    10 |
|    3 | b西瓜 |    30 |
|    4 | D菠萝 |    20 |
+------+-------+-------+

如使用 : SELECT CONCAT(name, '价格是:', price) FROM goods;
结果为 :
+--------------------------------+
| CONCAT(name, '价格是:', price)  |
+--------------------------------+
| a香蕉价格是:10                  |
| D菠萝价格是:20                  |
| A香蕉价格是:5                   |
| b西瓜价格是:30                  |
+--------------------------------+

如使用 : SELECT CONCAT(name, '价格是:', price) AS goods_price FROM goods;
结果为 :
+----------------+
| goods_price    |
+----------------+
| a香蕉价格是:10 |
| D菠萝价格是:20 |
| A香蕉价格是:5  |
| b西瓜价格是:30 |
+----------------+
使用别名后,可以按名引用这个列

使用算术计算(字段数据类型为数值)

SELECT 字段1*字段2 AS 别名1, 字段1+字段2 AS 别名2, ... FROM 表名;
有* / + -

数据处理函数

字符串函数

用法1 : SELECT 字符串函数(字段1) FROM 表名;

用法	说明
`CHARACTER(str)`	返回该列的字符串类型
`INSTR('串1', '串2')`	查询串2在串1中的起始位置
`UCASE(str/字段);` `UPPER(str/字段);`	转换为大写
`LCASE(str)`	转换为小写
`LEFT(字段, n)`	从左边提取n个字符
`RIGHT(字段, n)`	从右边提取n个字符
`LENGTH(str/字段)`	返回str长度(按照字节返回)
`REPLACE(str, search_str, replace_str)`	替换字符
`STRCMP(str1, str2)`	逐字符比较str1和str2大小(-1, 0, 1)
`SUBSTRING(列1, n, m)`	从列1取出从n开始m个字符的字符串
`LTRIM(str);` `RTRIM(str);` `TRIM(STR);`	去除左边,右边,两边的空格
`Soundex(str/字段)`	返回串的音值,(类似模糊查询?)

日期函数

用法1 : 在添加数据时调用函数
用法2 : SELECT 函数;
用法3 : 查询10分钟内的记录 SELECT * FROM 表名 WHERE (date1, INTERVAL 10 MINUTE) >= NOW()说明mysql里面日期类型也可以参与比较
DATETIME(1970-01-01 12:00:00) = DATE(1970-01-01) + TIME(00:00:00)
TIME与DATETIME相同但范围较小

用法	说明
`CURRENT_DATE();` `CurDate()`	返回(1970-01-01)格式时间
`CURRENT_TIME();` `CurTime()`	返回(12:00:00)格式时间
`CURRENT_TIMESTAMP();` `NOW()`	返回(1970-01-01 12:00:00)格式时间
`AddDate(date/datetime, INTERVAL d_value d_type)`	增加时期
`AddTime(date/datetime, time)`	增加时间
`DATE_ADD(date/datetime, INTERVAL d_value d_type);`	在date1中加上时间或者日期
`DATE_SUB(date/datetime, INTERVAL d_value d_type)`	在date1中减去时间或者日期
`DATEDIFF(date1, date2)`	两个日期的差
`DATE / YEAR / MONTH / DAY(date/datetime)`	取出date/datetime中的date, 年, 月, 日
`TIME / HOUR / MINUTE / SECOND(DATETIME)`	取出完整或者单独的时分秒
`DAYOFWEEK()`	返回今天一周第几天(按西方星期算)
`UNIX_TIMESTAMP()`	从1970-1-1 0:0:0到现在的秒数
`FROM_UNIXTIME(uxintime, '%Y-%m-%d %H : %i : %s')`	将时间戳转换为日期
`Date_Format(date, format)`	返回指定格式的日期

关于DATE_ADD中的d_type数据类型参考mysql手册

DATE_FORMAT中的format格式参考mysql手册

数学函数

用法1 : SELECT 数学函数(字段1/数值) FROM 表名;

用法	说明
`ABS()`	绝对值
`BIN()`	十进制转化为二进制
`CEILING()`	向上取整
`CONV(num, from_base, to_base)`	进制转换
`FLOOR()`	向下取整
`FORMAT(num, decimal_places)`	保留小数位(四舍五入)
`HEX(decimal_num)`	转成16进制
`LEAST(列1, 列2)`	返回列1,列2每一行数据对比后的最小值
`MOD(numerator, denominator)`	求余
`RAND(seed)`	返回[0,1]之间的随机数,加种子后随机数就固定了
`PI()`	返回圆周率

汇总数据

统计查询的数据有多少行COUNT

SELECT COUNT(*) FROM 表名 WHERE 表达式; 不会排除为空的行
SELECT COUNT(字段) FROM 表名; 排除为空的行

统计该列数据总和是多少SUM(仅对数值起作用)

SELECT SUM(列名1) AS 别名1, SUM(列名2) AS 别名2 FROM 表名;

求该列所有数据和的平均数AVG

SELECT AVG(列1), AVG(列2) FROM 表名;

AVG函数忽略列值为NULL的行

DISTINCT 和 AVG的组合使用

+------+--------+-------+
| id   | name   | price |
+------+--------+-------+
|    1 | A香蕉  |     5 |
|    5 | 樱桃   |     5 |
|    2 | 哈密瓜 |    10 |
|    6 | 牛奶   |    15 |
|    4 | D菠萝  |    20 |
|    3 | b西瓜  |    30 |
+------+--------+-------+

select avg(price) from goods;
+--------------------+
| avg(price)         |
+--------------------+
| 14.166666666666666 |
+--------------------+

select avg(distinct price) from goods;
使用distinct去除重复的数据价格:
+---------------------+
| avg(distinct price) |
+---------------------+
|                  16 |
+---------------------+

求指定列的最值或者日期MAX,MIN

SELECT MAX(列1), MIN(列2) FROM 表名;

最值函数忽略列值为NULL的行

分组数据

GROUP BY

SELECT 字段1, 字段2 FROM 表名 GROUP BY 字段1, 字段2;(将查询的数据按照字段1,字段2分组)
SELECT COUNT(*) FROM 表名 GROUP BY 字符1;(显示分组后,每种数据有几行)

GROUP BU + WHITH ROLLUP 统计分组后数据的总和

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

+------+--------+-------+
| id   | name   | price |
+------+--------+-------+
|    1 | A香蕉  |     5 |
|    2 | 哈密瓜 |    15 |
|    3 | b西瓜  |     5 |
|    4 | D菠萝  |    20 |
|    5 | 樱桃   |     5 |
|    6 | 牛奶   |    15 |
+------+--------+-------+

若不按name分组,出现这种一个5对应三种水果的状况:
+--------+-------+
| A香蕉  |       |
| b西瓜  |   5   |
| 樱桃   |       |
+--------+-------+

若加上price分组, name分组后:
+--------+-------+
| A香蕉  |     5 |
---------
| b西瓜  |     5 |
---------
| 樱桃   |     5 |
+--------+-------+
| 哈密瓜 |    15 |
---------
| 牛奶   |    15 |
+--------+-------+
| D菠萝  |    20 |
+--------+-------+

若改成count(name),再加上price分组:
+-------------+-------+
| count(name) | price |
+-------------+-------+
|           3 |     5 |
|           2 |    15 |
|           1 |    20 |
+-------------+-------+

HAVING过滤GROUP BY分组后的组

HAVING过滤的是组,而WHERE过滤的是数据.WHERE在分组前过滤数据,HAVING在分组后过滤组.
HABING支持所有WHERE操作符
SELECT 字段1 FROM 表名 GROUP BY 字段1 HAVING expression;

系统函数

用法1 : SELECT 函数;

用法	说明
`SELECT USER()`	查询登录到sql的有哪些用户以及ip是多少
`SELECT DATEBASE()`	查询当前使用数据库的名称
`SELECT DATEBASE() FROM DUAL`	查询当前使用数据库的名称

加密函数

用法1 : SELECT 函数;
用法2 : 数据库存放的是加密后的密码

用法	说明
`MD5(str)`	为字符串算出一个MD5 32的字符串
`PASSWORD(str)`	mysql自带的加密函数,user登录密码采用此函数加密

流程控制函数

用法1 : SELECT 函数;

用法	说明
`IF(expr1, expr2, expr3)`	如果exp为真,返回exp2,否则返回exp3(Java三元运算符)
`IFNULL(exp1, exp2)`	如果exp1不为空,返回exp1,否则返回exp2
`CASE WHEN exp1 THEN exp2 WHEN exp3 THEN exp4 ELSE exp5 END;`	如果exp1为真返回exp2(多重分支).....

mysql judge null(not null) use 'is(is not)' not '==(!=)' ,

example : exp1 is null

example : exp1 is not null

如果是具体值(例如字符串)的话就用 ' = '

多表查询

多表查询的默认返回结果是笛卡尔积

A×B = {(x,y)|x∈A∧y∈B}

多表查询的条件不能少于表的个数-1,否则会出现笛卡尔积

多表的关键就是写出正确的过滤条件where

第一张表emp
+-------+-------+----------+------+------------+------+------+--------+
| empno | ename | job      | mgr  | hiredate   | sal  | com  | deptno |
+-------+-------+----------+------+------------+------+------+--------+
|  7369 | s1    | clerk    | 7902 | 2022-03-24 |  800 | NULL |     20 |
|  7499 | s2    | salesman | 7968 | 1991-02-20 | 1600 |  300 |     30 |
|  7521 | s3    | manager  | 7968 | 1991-02-22 | 1250 |  500 |     30 |
|  7782 | s7    | manager  | 7893 | 1991-06-05 | 2450 | NULL |     10 |
|  7788 | s8    | manager  | 7833 | 1992-06-05 | 2450 | NULL |     20 |
+-------+-------+----------+------+------------+------+------+--------+

第二章表dept
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | accounting | new york |
|     20 | research   | dallas   |
|     30 | sales      | Chicago  |
|     40 | operation  | Boston   |
+--------+------------+----------+

若查询是增加筛选条件SELECT enanme, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno;
查询结果是:

+-------+------+------------+
| ename | sal  | dname      |
+-------+------+------------+
| s1    |  800 | research   |
| s2    | 1600 | sales      |
| s3    | 1250 | sales      |
| s7    | 2450 | accounting |
| s8    | 2450 | research   |
+-------+------+------------+

在自己没有加emp.ename, emp.sal好奇mysql是如何知道将两个字段ename, sal属于第一张表?

mysql基础