数据库MYSQL

服务与连接

centos开启mariadb

systemctl start mariadb

Navicat连接远程数据库(centos7.9系统)

1.关闭selinux

setenforce 0

2.关闭防火墙

service firewalld stop

3.设置数据库密码

…

连接错误: 拒绝从192.168.149.1访问数据库

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.149.1'IDENTIFIED BY '123' WITH GRANT OPTION;

语法与使用

1. DDL(Data Definition Language) 数据定义

   CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME

2. DML(Data Manipulation Language) 数据操作

   SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE

3. DCL(Data Control Language) 数据控制

   用于授权/撤销数据库及其字段的权限

   GRANT,REVOKE

4. TCL(Transaction Control Language) 事务控制

   COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION

创建

创建数据库

CREATE DATABASE name CHARSET UTF8;

创建表

CREATE TABLE name(id int(2),name char(20));

复制表

create table newtable like sourcetable;

增加数据

INSERT INTO huawei values
(1,'tom','tester',DEFAULT),
(2,'jim',null,DEFAULT);

修改

ALTER

#修改字段类型 约束
ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度 新默认值 新注释;
范例:
ALTER TABLE table1 MODIFY COLUMN column1 DECIMAL(10,1) DEFAULT NULL COMMENT '注释';

#改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名;
eg:
ALTER TABLE emp2 CHANGE eid id int(3);

#添加列
ALTER TABLE tablename ADD [COLUMN] newcolumn type(length) [first|AFTER column];
eg:
ALTER TABLE orders ADD name1 int(3);

#删除列
ALTER TABLE tablename DROP [column] columnname

RENAME

#修改表名
RENAME TABLE 原表名 to 新表名;
ALTER TABLE 原表名 RENAME TO 新表名;

UPDATE, SET

UPDATE tablename SET column1='new data',column2='new data' WHERE ...;

DELETE

#删除行
DELETE FROM 表名称 WHERE 列名称 = 值

查询

SELECT DATABASE(); #当前使用的数据库
SELECT USER(); #当前登录的用户
SELECT NOW(); #当前时间
SELECT VERSION(); #数据库版本
SHOW TABLES FROM 其他数据库;
DESC emp; #查看emp表结构 <--------  == DESCRIBE emp;
SHOW COLUMNS FROM emp; #效果与上面的语句一样
SHOW CREATE TABLE emp; #查看emp表的创建代码


#去除重复统计
SELECT COUNT(DISTINCT deptno) FROM emp;

#CASE 写法1
select ename,job,sal,
case job
when 'clerk' then '显示的数据'
when 'xxx' then '显示的数据'
[else '显示的数据']
end as '新列名'
from emp;

#CASE 写法2
select ename,job,sal,
case 
when job='clerk' then '显示的数据' # when sal>4000 then ...
when job='xxx' then '显示的数据' # when sal>4000 then ...
[else '显示的数据']
end as '新列名'
from emp;

OR , AND 条件

A OR B AND C ==> (A) OR (B AND C)
A AND B OR C ==> (A AND B) OR (C)

1.UNION

实例

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

**注释：**UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

实例

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

2.GROUP BY

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

3.BEGIN & COMMIT

4.WITH ROLLUP

5.WHERE & HAVING

1. where是对from后面指定的表进行数据筛选，属于对原始数据的筛选； group by后面不能接where

2. having是对group by分组后的结果(显示的结果)进行筛选。HAVING后面接的条件一般有聚合函数,如

   ...group by score having max(score)>avg(score) #分组后,一组一组比较,组内最大score,是否大于改组的平均score,"正确的组留下,不正确的组去掉"??
   

   having没有使用聚合函数的区别

   #!!!!!!!!#
   (1)...group by score having max(score)>avg(score) 与
   (2)...group by score having score>avg(score) 的区别
   (3)...group by score having score>90;
   
   第(2)中只会在每一组中,取每一组的第一个值与该组的平均值比较!!
   第(3)中也是只会在每一组中,取该组的第一个值与90比较!!
   

3. 多层分组(group by a,b), 聚合函数的操作对象是最里层的组.

ANY, ALL,SOME

需要结合子查询使用

not in 是 “<>all”的别名，用法相同。

语句in 与“=any”是相同的。

语句some是any的别名，用法相同。
例如：

select s1 from t1 where s1 <> any (select s1 from t2);
select s1 from t1 where s1 <> some (select s1 from t2);

多表连接

...join.... on tablea.column=tableb.column 
==>
...join.... using(column)

select * from a,b,c;
/*结果
a1 b1 c1
a1 b1 c2
a1 b1 c3

a1 b2 c1
a1 b2 c2
a1 b2 c3

a1 b3 c1
a1 b3 c2
a1 b3 c3

a2 b1 c1
a2 b1 c2
a2 b1 c3
....
*/

select * from a,b where ....;#先得到拼接后的表,再过滤
select * from a join b on xxx where ....;#通过on的条件得到符合条件的内容组成表,再对表过滤

7.在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MYSQL 事务处理主要有两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

o

8. asc 为升序 desc 为降序

9. LIMIT

   .... LIMIT A,B      取从A,B, A从0开始,0是第一行, B表示一共显示多少行. A是可选项.意义:从A开始,显示B行.
   
   ....LIMIT 1;          显示前1行;
   
   查询最后一行
   select * from emp order by empno desc limit 1;
   

用户与权限

CREATE USER `xxx`@`%` IDENTIFIED BY 'xxx';
CREATE USER `xxx` IDENTIFIED BY 'xxx'; 作用同上
GRANT ALL ON *.* TO `xxx`@`xxx`;

视图,存储过程,事务,幻读…

索引

创建

create index indexname on table(column);

删除

drop index indexname on table;