MySQL数据库基础回顾

最新推荐文章于 2022-08-26 17:12:57 发布

Vic·Tory

最新推荐文章于 2022-08-26 17:12:57 发布

阅读量336

点赞数

分类专栏：数据库文章标签： mysql数据库数据库操作

本文链接：https://blog.csdn.net/theVicTory/article/details/55051181

版权

数据库专栏收录该内容

22 篇文章 6 订阅

订阅专栏

1、数据库操作

以root用户登录 mysql -uroot -p

显示数据库：show databases;

切换到demo数据库：use demo;

修改密码，通过IDENTIFIED BY指定用户的登录密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql1234!';

创建用户：

CREATE USER 'work'@'%' IDENTIFIED BY 'work1234';

分配权限：

GRANT ALL PRIVILEGES ON  *.*  TO  'root'@'%'  WITH GRANT OPTION;

ALL PRIVILEGES：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个SELECT权限，后来又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限
ON：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库、所有表。如果要指定将权限应用到test库的user表中，可以这么写：test.user
TO：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录
with grant option：表示允许用户将自己的权限授权给其它用户

重命名用户：

rename user 'test3'@'%' to 'test1'@'%'；

修改密码：

set password for 'root'@'localhost'=password('123456');

删除用户：

drop user 'test'@'localhost';

以上操作修改后需要刷新才会生效：flush privileges;

2、配置文件my.ini

一些基本设置如下：

# 数据库监听端口
port=3306

# 安装位置
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

# 数据存储位置
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# 默认字符集
character-set-server=gbk

# 默认存储引擎
default-storage-engine=INNODB

# 不区分表名称大小写
lower_case_table_names=1

FEDERATED存储引擎

开启该引擎后，可以在本地构建一个federated表来连接远程数据表，配置好了之后本地表的数据可以直接跟远程数据表同步。实际上这个表里面是不真实存放数据的，所需要的数据都在远程MySQL服务器上。

在配置文件中添加federated即可开启该引擎

接着，在建表的时候添加sql语句ENGINE=Federated 和 CONNECTION='mysql://[username]:[password]@127.0.0.1:3306/dbtestA/tabletestA';

create table remote_tabletestA(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT)
ENGINE=FEDERATED 
CONNECTION='mysql://test:123456@127.0.0.1:3306/dbtestA/tabletestA';

新建出来的表就是链接到了127.0.0.1:3306/dbtestA/tabletestA这个表，本身只是一个空壳，只能进行数据的增删改查操作，不能改变链接到的表的表结构，如果改变自身的表结构会出现意想不到的错误。

慢查询

在执行某些sql语句会花费大量的时间，我们可以将这些执行较慢的语句记录下来从而分析和改进查询效率，在my.ini中设置慢查询如下

# 是否开启慢查询日志，1表示开启，0表示关闭。
slow_query_log = 1

# mysql5.6以上为slow-query-log-file
# 慢查询日志存储路径，默认文件host_name-slow.log
log-slow-queries = C:\ProgramData\MySQL\MySQL Server 5.5\data\slow-query.log

# 慢查询阈值，当查询时间多于设定的阈值时，记录日志，默认为10s。
long_query_time=5

3、修改数据表

添加一列：ALTERTABLE tab_name ADD col_name column_defi[FIRST|AFTER col_name];可以指定新列的位置关系，位于最开头（FIRST）或者位于某列之后（AFTER…）,否则新列默认位于最后。

添加多列：ALTERTABLE tab_name ADD（col_name1 column_defi1，col_name2 column_defi2...）;

删除一列：ALTERTABLE tab_name DROP col_name;删除多列或者删除之后再新增一列，只需在drop col_name之后加逗号，再写drop/add。

添加约束：ALTERTABLE tab_name ADD [ CONSTRAINT constraint_name ] PRIMARY KEY （index_column）;其中primary key可以替换为其他约束UNIQUE、FOREIGN KEY、CHECK(col>16)，默认约束为SET DEFAULT。

删除约束：ALTERTABLE tab_name DROP {INDEX|KEY} index_name。

删除外键：ALTERTABLE tab_name DROP FOREIGN KEY fkey_name.其中外键约束名fkey_name可以通过show create table tab_name;查看constraint可以看到：CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES foreign_tab(f_id);

修改列定义：ALTERTABLE tab_name MODIFY col_name col_defi;

修改列名称：ALTERTABLE tab_name CHANGE old_colname new_name col_defi;

修改数据表名称：RENAMETABLE old_name TO new_name;

删除表内数据：TRUNCATE tab_name; 删除操作不可回滚

删除整个表结构：DROP tab_name;

4、增删改查

插入记录：

其中列名可选，如果不设置列名，默认为对每一列都插入。

INSERT tab_name[(col_name)] VALUES (val1,val2...)

第二种插入方法，以键值对的形式插入

INSERT tab_name SET col_name=val...

第三种方法：insert tab_name select…，将查询结果插入数据表。

更新记录：

where条件如果不填，默认对所有记录进行更新。

UPDATE tab_name SET col_name=val [WHERE condition];

还可以使用REPLACE对数据表进行更新，如果数据的主键不存在则直接插入，若主键已存在，则删除该主键记录并插入，这时该操作返回的影响行数为2

值得注意的是在使用主从数据库同步时，对于replace操作，生成binlog时也生成delete和insert两个事件而非一个update事件，因此从数据库在没有同步删除操作的话就会只执行insert，这时就会造成主键冲突。

REPLACE INTO users (id,name,age) VALUES(123, ‘小曾’, 50);

删除记录：删除满足条件的记录，如不填where，全删。

DELETE FROM tab_name [WHERE condition];

查询记录：

SELECT col_name1,col_name2... /*选择需要查询的列名*/

FROM tab_name/*选择需要查询的数据表*/

WHERE condition/*查询条件*/

GROUP BY col_name [ASC|DESC]/*查询结果分组*/

HAVING condition/*对查询分组经行筛选*/

ORDER BY col_name[asc|desc]/*对查询结果经行排序*/

LIMIT [n1,]n2/*返回从n1开始的n2条结果，不填n1默认从开头返回*/

5、子查询

父查询与子查询的连接由比较符号连接，子查询返回多个结果时还可以由ANY、ALL对结果进行修饰。

多表更新：参照另外一个表来更新本表。

内连接：INNERJOIN ON join_condition，返回左右两表中满足条件的项。

左外连接：LEFTJOIN ON join_condition，返回左表全部与右表中满足条件的项。

左外连接： RIGHT JOIN ON join_condition，返回右表全部与左表中满足条件的项。

6、数据库函数

CONCAT（’a’,’b’…）	连接a,b多个字符串
CONCAT_WS（’s’,’a’,’b’…）	以s为连接符连接多个字符串
FORMAT（f，n）	以n位小数显示数字f
LOWER（）/UPPER（）	将内容转换为小/大写
LEFT/RIGHT(‘s’,n)	获取字符串左边/右边前n个字符
LENGTH（）	获取字符串长度
LTRIM/RTRIM/TRIM（）	去除字符串左边/右边/两边空格
REPLACE(‘s’,’a’,’b’)	将字符串s中的a字符替换为b
SUBSTRING（’s’,n,e）	截取字符串从第n位开始的e个字符
IS NULL	判空
n IN（a,b,c…）	判断n是否在列出的值中
n BETWEEN a AND b	判断n是否在a到b之间

Vic·Tory

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库基础回顾

1、修改数据表添加一列：ALTERTABLE tab_name ADD col_name column_defi[FIRST|AFTER col_name];可以指定新列的位置关系，位于最开头（FIRST）或者位于某列之后（AFTER…）,否则新列默认位于最后。添加多列：ALTERTABLE tab_name ADD（col_name1 column_defi1，col_name2...
复制链接

扫一扫