MySQL笔记#

基本使用#

INSERT INTO table_name [(column, column, ...)] VALUES (value, value, ...)

例如

创建一张商品表(id int , goods_name varchar(10), price double)

CREATE TABLE goods(
`id` int,
`goods_name` varchar(10),
`price` double);

其中添加两条数据

INSERT INTO goods VALUES (1, '牙刷', 10.0);

INSERT INTO goods (id, goods_name) VALUES (2, '牙膏');

mysql> select * from goods;

+----+------------+-------+
| id | goods_name | price |
+----+------------+-------+
|  1 | 牙刷       |    10 |
|  2 | 牙膏       | NULL  |
+----+------------+-------+
2 rows in set (0.08 sec)

注意细节#

1.插入的数据应该与字段的数据类型相同

2.数据的长度因该在列的规定范围内

3.value中列出的数据位置必须于被加入的列的排列位置对应

4.字符和日期类型数据需要包含在单引号中

5.列允许插入空值（前期是字段不是NOT NULL）

6.插入的记录可以有多条，在后续加（），（）即可

7.如果是表中的所有字段添加数据的时候，可以不写字段名称

8.默认值的使用，当不给某个字段值的时候，如果有默认值就会添加，否则填入null，如果NOT NULL则报错

基本使用#

UPDATE table_name SET col_name1 = expr1 [col_name2 = expr2] [WHERE where_definition]

以下是一个使用案例

1.将所有的员工薪水修改为5000元

UPDATE employee SET Salary = 5000;

2.将力宏的薪水修改为3000元

UPDATE employee SET Salary = 3000 WHERE `user_name`='力宏';

3.将lory的薪水上调1000元

UPDATE employee SET Salary = Salary + 1000 WHERE `user_name`='lory';

使用细节#

1.UPDATE语法可以指定用新值更新原有表行中的各列

2.SET子句指定要修改那些列和赋予的哪些值

3.WHERE子句指定应该更新哪些行，如果没有，则更新所有的行

4.如果需要修改多个字段，可以通过set 字段1= value，set 字段2= value…完成，例如如下代码，就顺便进行了id++

UPDATE employee SET Salary = Salary + 1000, id = id + 1 WHERE `user_name`='lory';

基本使用#

DELETE FROM table_name [WHERE where_definition];

案例

1.删除用户名为力宏的记录

DELETE FROM employee WHERE `user_name`='力宏';

2.删除表中的所有记录

DELETE FROM employee;

使用细节#

1.如果不加WHERE子句，就会删除表中所有的记录

2.DELETE语句不可以删除某一列的值（可以使用update更新字段为null）

3.使用DELETE语句尽可以删除表的记录，不可以删除表本身，需要使用DROP删除表

基本使用#

SELECT [DISTINCT] * | [column1, colum2, ...] FROM table_name [WHERE where_definition];

其中DISTANCT可选，表示是否去除重复数据

1.查询所有的员工

select * from employee;

+----+-----------+------+--------+
| id | user_name | job  | Salary |
+----+-----------+------+--------+
|  1 | 李明      | NULL |   2000 |
|  1 | 李明      | NULL |   2000 |
|  2 | 王丽      | NULL |   2000 |
|  3 | 丽华      | NULL |  20003 |
|  4 | 李儒      | NULL |   2000 |
|  5 | 小明      | NULL |   2000 |
|  6 | lory      | NULL |  20003 |
+----+-----------+------+--------+

2.查询非重复的员工（每个字段都相同）

select distinct * from employee;

+----+-----------+------+--------+
| id | user_name | job  | Salary |
+----+-----------+------+--------+
|  1 | 李明      | NULL |   2000 |
|  2 | 王丽      | NULL |   2000 |
|  3 | 丽华      | NULL |  20003 |
|  4 | 李儒      | NULL |   2000 |
|  5 | 小明      | NULL |   2000 |
|  6 | lory      | NULL |  20003 |
+----+-----------+------+--------+
6 rows in set (0.07 sec)

3.SELECT语句可以进行一些运算

SELECT * | (column1 | expression, column2 | expression, ...)FROME table_name;

例如在查询科目成绩时可以指定查询的一列为其他的几列相加

SELECT (english + math + chinese) as `grades` from table_name;

4.SELECT可以使用as语句取别名

SELECT column_name as other_name from table_name;

SELECT `user_name` as `name`, `Salary` as `money` FROM `employee`

+------+-------+
| name | money |
+------+-------+
| 李明 |  2000 |
| 李明 |  2000 |
| 王丽 |  2000 |
| 丽华 | 20003 |
| 李儒 |  2000 |
| 小明 |  2000 |
| lory | 20003 |
+------+-------+
7 rows in set (0.07 sec)

5.和改删一样，SELECT一样可以运用WHERE过滤

常见的运算符

6.使用order by设置排序

SELECT  * | [column1, colum2, ...] FROM table_name 
ORDER BY column1 asc|desc;

排序的列可以是表中的列名，也可以select语句中指定的列名

Asc升序 Desc降序

ORDER BY 子句应该位于SELECT语句的末尾

7.group by + having

使用group by子句对列进行分组

SELECT column1, colum2, ... FROM table_name 
group by column;

使用having子句对分组后的结果过滤

SELECT column1, colum2, ... FROM table_name 
group by column1 having ...;

使用案例

1.显示每个部门的平均工资和最高工资

SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP BY deptno;

2.显示每个部门的每种岗位的平均工资和最低工资

可以先显示每个部门的平均工资和最低工资

SELECT AVG(sal), MIN(sal), deptno FROM emp GROUP BY deptno;

还需要再添加一个分组的标准

SELECT AVG(sal), MIN(sal), deptno, job FROM emp GROUP BY deptno, job;

3.显示平均工资低于2000的部门号和他的平均工资

先显示各个部门的平均工资和部门号

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

然后运用having进行过滤实现平均工资小于2000

SELECT AVG(sal) as avg_sal, deptno FROM emp 
GROUP BY deptno
HAVING avg_sal < 2000;

以下是SELECT使用例题：

1.对数学成绩排序后输出[升序]

SELECT * FROM student ORDER BY `math` asc;

2.对总分按照降序排序

SELECT `name` , (`math`+`english`+`chinese`) as grade FROM student ORDER BY grade desc;

3.对姓李的学生按照成绩排序输出[升序]

SELECT * FROM student WHERE `name` LIKE '李%' ORDER BY (`math`+`english`+`chinese`)  asc;

例题#

1.查询math大于60并且id大于90的学生成绩

SELECT * FROM student WHERE `math` > 60 and id > 90;

2.查询英语成绩大于语文成绩的同学

SELECT * FROM student WHERE `math` > `chinese`;

3.查询总分大于200分并且数学成绩小于语文成绩的韩姓同学

SELECT * FROM student WHERE (`math`+`chinese`+`english`)>200 and `math`<`chinese` and user_name LIKE '韩%';

4.查询英语成绩在80-90分的同学(闭区间)

SELECT * FROM student WHERE `english` BETWEEN 80 AND 90;

5.查询数学分数为89，90，91的同学

SELECT * FROM student WHERE `english` IN(89,90,91);

基本使用#

SELECT COUNT(*) | COUNT(column) FROM table_name [WHERE where_definition];

count(*)和count(column)区别

count(*)返回满足条件记录的行数

count(column)统计满足条件的某列有多少的，但排除对应列为null的情况

使用案例#

1.统计班级里一共有几个学生

select count(*) from student where `grade`

+----------+
| count(*) |
+----------+
|        7 |
+----------+

2.统计总分大于300的学生个数

select count(*) from student where `grade` > 300;

+----------+
| count(*) |
+----------+
|        4 |
+----------+

基本使用#

SELECT SUM(column) FROM table_name [WHERE where_definition];

仅对数值起作用

使用案例#

1.统计一个班数学总成绩

SELECT SUM(math) FROM student;

2.统计一个班语文、英语、数学各科的总成绩

SELECT SUM(math),SUM(english),SUM(chinese) FROM student;

3.统计一个班语文、英语、数学各科和的总成绩

SELECT SUM(math + english + chinese) FROM student;

4.统计一个班级语文的平均分

SELECT SUM(chinese)/COUNT(*) FROM student;

或者我们也可以使用AVG统计函数求平均值

SELECT AVG(chinese) FROM student;

基本使用#

SELECT AVG(column) FROM table_name [WHERE where_definition];

使用案例如上，不再过多赘述

基本使用#

SELECT MAX/MIN(column) FROM table_name [WHERE where_definition];

使用案例#

1.求班级最高分

SELECT MAX(math + english + chinese)，MIN(math + english + chinese) FROM student;

2.求出班级数学最高分

SELECT MAX(math)，MIN(math) FROM student;

基本使用#

CHARSET(str)

SELECT CHARSET(name) FROM emp;
//返回字符集（utf8）

CONCAT(str2 [,…])

SELECT CONCAT(name, 'job is', job) as name_job FROM emp;
//字符串拼接

INSTR(string, substring)

SELECT INSTR(string, 'target') FROM DUAL;--其中DUAL是亚元表
//返回出现位置

UCASE(str2) LCASE(str2)

SELECT UCASE(name) FROM emp;//转为大写
SELECT LCASE(name) FROM emp;//转为小写

LEFT(str2, length) RIGHT(str2, length)

SELECT LEFT(name, 2) FROM emp;//从左取2个字符
SELECT RIGHT(name, 2) FROM emp;//从右取2个字符

LENGTH(string)

SELECT LENGTH(name) FROM emp;//返回名字长度

REPLACE(str, search_str, replace_str)

SELECT REPLACE(name, 'a', 'b') FROM emp;
//将名字中的a替换为b

STRCMP(str1,str2)

SELECT STRCMP(str1,str2) FROM DUAL;
//比较字符串大小 小于返回-1 大于返回1 等于返回0

SUBSTRING(str, position [length])

SELECT SUBSTRING(name, 1, 2) FROM emp;
//从第一个位置开始取两个字符

LTRIM(str2)

SELECT LTRIM(' 名字') FROM DUAL;//'名字'

例题#

以首字母小写的方式显示所有员工emp表的名字

取出第一个字符转为小写然后和后面的字符拼接

SELECT CONCAT(LCASE(LEFT(name, 1)),
SUBSTRING(name,2,LENGTH(name))) FROM emp;

ABS(num)#

绝对值

SELECT ABS(-1) FROM DUAL;//1

CEILING(number)#

向上取整，得到比number大的最小整数

SELECT CEILING(1.34) FROM DUAL;//2

FLOOR(number)#

向下取整，得到比number小的最大整数

SELECT FLOOR(1.34) FROM DUAL;//1

RAND([seed])#

随机数范围为[0,1,0]

不加种子返回的数每次都不同

种子指定后返回的数不变

SELECT RAND(6) FROM DUAL;

USER()#

显示登录到mysql有哪些用户以及登陆的IP

select user() from dual;

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

DATABASE()#

查询使用数据库名

select DATABASE() from dual;

+------------+
| DATABASE() |
+------------+
| dbtest     |
+------------+

MD5(str)#

为字符串计算出一个MD5 32的字符串，常用为用户密码加密(32位)

select MD5('密码') from dual;

+----------------------------------+
| MD5('密码')                      |
+----------------------------------+
| a8105204604a0b11e916f3879aae3b0b |
+----------------------------------+

UPDATE user SET `password`=MD5(654321);

PASSWORD(str)#

加密函数，是MySQL使用的加密方式

select * from mysql.user
//其中的$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED就是加密后的密码

IF(expr1,expr2,expr3)#

如果表达式1为真，返回expr2，否则返回expr3

SELECT IF(TRUE, 'BEIJING', 'SHANGHAI') FROM DUAL;//BEIJING

IFNULL(expr1,expr2)#

如果表达式1为NULL，返回expr1，否则返回expr2

SELECT IFNULL('BEIJING', 'SHANGHAI') FROM DUAL;//BEIJING
SELECT IFNULL('NULL', 'SHANGHAI') FROM DUAL;//SHANGHAI

多分支函数#

SELECT CASE 
WHEN expr1 THEN expr2 
WHEN expr3 THEN expr4 
ELSE expr5

SELECT CASE 
WHEN FALSE THEN expr2 
WHEN TRUE THEN expr4 
ELSE expr5 END
//返回expr2

例题#

查询emp表，如果comm是null，就显示0.0

SELECT name, IF(comm IS NULL, 0.0, comm) FROM emp;

SELECT name, IFNULL(comm, 0.0) FROM emp;

如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理，如果是SALESMAN就显示销售人员，其他正常显示

SELECT name, (
SELECT CASE 
WHEN job='CLERK' THEN '职员' 
WHEN job='MANAGER' THEN '经理' 
WHEN job='SALESMAN' THEN '销售人员' 
ELSE job END) AS 'job'
FROM emp;

以上就是MySQL的基本语句，查询的基础部分，和一些常见的函数，我们明天会进入增强查询（多表查询）