MySQL常用操作笔记

1.创建root用户的密码

mysqladmin -u root password "new_password";

2.连接到Mysql服务器

mysql -u root -p 123;

3.登录 MySQL

mysql -h 主机名 -P 端口号 -u 用户名 -p;

4.输入 exit 或 quit 退出登录

5.sql规范：语句以“ ; ”号结尾，sql命令是以大写书写，单行注释：-- ，多行注释：/*....*/，sql语句可以折行操作

----------------------------------------------

数据库操作（DDL）

-------

6.查看 show 操作

show databases; (查看数据库)

show warnings; (查看警告信息) errors (错误信息)

show create database 名字; (查看数据库创建信息)

---------------------------

7.创建 create 操作

create database 名字; (创建数据库)

create database if not exists 名字; (判断不存在时在创建，如果有不操作)

create database if not exists 名字 character set utf8; (创建数据库及设置编码方式)

----------------------------

8.删除数据库

drop database 名字;

9.修改数据库信息

alter database 名字 character set utf8

10.进入或切换数据库

use 名字

11.查看当前数据库信息

select version(); # 服务器版本信息

select database() # 当前数据库名 (或者返回空)

select user() # 当前用户名

show status; # 服务器状态

show variables; # 服务器配置变量

------------------------

表操作

-------

#主键：非空且唯一 not null unique

12.创建表 create 操作

CREATE TABLE tab_name(

id INT PRIMARY KEY auto_increment, #主键唯一(PRIMARY KEY),自增(auto_increment) 注：自增id如果突然加个10会以10开始加

name VARCHAR(25), #字符

gender BOOLEAN DEFAULT True, #boolean 设置默认值 (DEFAULT)

salary DOUBLE(7,2), #双精度浮点数

num FLASE, #浮点数

startTime DATETIME, #日期时间

INDEX index_name, #创建普通索引

out_id INT,

FOREIGN KEY (out_id 外键关联库名) REFERENCES 表名(id) #外键用于约束（注意：外键一定要和绑定字段类型保持一致，有外键的是子表，绑定的是父表）

)

CREATE TABLE 库名.tab_name(XXX) ENGINE=INNODB; #没进入库时指定库名创建, ENGINE 指定搜索引擎默认INNODB

# 动态添加外键

ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes(id);

---------------

# 外键三种模式（外键约束默认，级联操作，set null）

1.外键约束 restrict (默认):

子表更新添加时必须是父表里有的主键，主表删除时子表必须没有与之绑定的外键。

2.级联执行 cascade:

如果想父表删除关联的子表也删除加: ON DELETE CASCADE;

如果想父表主键跟新子表外键也更新后加：ON UPDATE CASCADE;

级联更新删除：ON UPDATE CASCADE ON DELETE CASCADE；

3.set null

如果不想删除子表数据可以用：ON DELETE SET NULL;（给绑定子表设个null值）

---------------

# 删除一个外键约束

ALTER TABLE students DROP FOREIGN KEY fk_class_id;

13.查看表 show

show tables; (查看当前数据库中的所有表)

show create table tab_name; (查看表创建信息)

14.查看表信息

desc 表名

15.改变字段 alter

alter table 表名 add 字段名 INT,add 字段名 VARCHAR(10); (添加字段“,”号可以加多字段)

alter table 表名 drop 字段名; (删除字段)

alter table 表名 modify 字段名 INT; (修改字段) (后加（first | after 字段名）是放到哪个字段前后的意思 )

alter table 表名 change 原字段名新字段名 INT; (改字段名)

alter table 表名 alter 字段名 drop DEFAULT; 删除字段的默认值

alter table 表名 rename TO 新表名; 修改表名

16.改表名

rename table 表名 to 新表名;

17.删除表

drop table if exists 表名; #if exists 存在就删除

-----------

# 表（增删改查）操作

-------

18.插入数据(增)

insert into 表名 (id,name,age) values (1,'小明',10),(2,'小红',11); (“,”号插入多条数据)

insert into 表名 set id=11,name="小刚"; #名字插入

19.删除数据(删)

delete from 表名 where gender=1 AND age=11;

delete from 表名 #删除所有表数据

truncate table 表名 #删除表数据（性能最好的删除，原理是删除表在建一张空的表）

20.更改数据(改)

update 表名 set name="小刚",age=12 where id=11; (不加 where 条件是更新所有)

21.查询数据(查)

select * from 表名1,表名2; ( * 所有字段可以指定查看单一字段如：id,name ,查询多表：表名1,表名2 )

select distinct name from 表名; ( 去除重复字段：distinct )

select name,age+10 from 表名; ( 数据加值显示，不影响原数据 )

select name as 姓名,age 年龄 from 表名; ( 字段取别名显示，不影响原数据，加不加as都可以 )

where 条件语句

-----

运算符:

--------

1.算术运算符:

+ - * (乘法) / (除法) % 或 MOD (取余)

2.比较语句:

= != > < <= >=

BETWEEN (在两值之间) NOT BETWEEN (不在两值之间) REGEXP 或 RLIKE (正则式匹配)

IN (在集合中) NOT IN (不在集合中) <=> (严格比较两个NULL值是否相等)

IS NULL (为空) IS NOT NULL (不为空) LIKE (模糊匹配)

3.逻辑语句:

NOT 或 ! (非) AND (与) OR (或) XOR（异或）

4.位运算符:

& (按位与) | (按位或) ^ (按位异或) << (左移) >> (右移)

--------

参考 https://www.runoob.com/mysql/mysql-operator.html

加 BINARY 关键字表示区分大小写

where age between 1 and 11; #age字段的（between 两值之间 1~11）

# not between 不在两值之间 # name is null (name值为null的数据)

# in(10,11) 在集合中 , not in(10,11) 不在集合中

# name like "小%"; name字段模糊匹配以小开头任意位“ % ”任意字符, "小_"匹配小后一位 “_” 符号代表几位

# name regexp '小\d'; (name正则查询: regexp 'xxx' )

# order by age (默认按age字段升序排序,后加desc降序排序) order by age,id desc,name 多列排序,desc只对前字段生效

# group by age (按age字段分组,相同的内容合成一组)

# select gender,sum(age) from name group by gender; (聚合函数 sum(age) 求age组的和)

# select gender,sum(age) from name group by gender having sum(age)>20; ( having 对分组进行过滤，age组的和大于20)

注：where 在分组前过滤 having 在分组后过滤

# 聚合函数

1.求和：sum(age) 2.求个数：count(age) 3.求平均值：AVG(age) 4.遇到null转为0：ifnull(age,0)

5.取最大和4结合用：max(ifnull(age,0)) 6.最小：min(age)

参考 https://www.runoob.com/mysql/mysql-functions.html

-----

# limit 限制显示几条 (参数 1 位是从0开始显示几条，两位是从第几条往后几条)

select name from 表名 limit 5; # 显示五条数据

# limit 分页查询

LIMIT 10, 15; # 偏移量，显示条数(查询第11条到第25条)

例：

SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20

对上面的mysql语句说明：limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。文中还提到limit n性能是没问题的，因为只扫描n行。

优化：

----

如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是1020，最小的是1000，如果我们只提供上一页、下一页这样的跳转（不提供到第N页的跳转），那么在处理上一页的时候SQL语句可以是：

SELECT * FROM tb_goods_info WHERE auto_id >=2500 ORDER BY auto_id ASC LIMIT 0,20

比如要跳到第9页，SQL语句可以这样写：

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20

比如要跳到第8页，SQL语句可以这样写：

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20

比如要跳到第7页，SQL语句可以这样写：

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 40,20

跳转到第11页：

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20

跳转到第12页：

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 20,20

跳转到第13页：

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 40,20

原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。

注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。

---

注：获取分页总数及数据

SELECT SQL_CALC_FOUND_ROWS * FROM my_info WHERE id<=100 LIMIT 1,20;

SELECT FOUND_ROWS() as total;

注：SQL_CALC_FOUND_ROWS 是 mysql 的内置关键字，可以记录下当前 sql 的总行数（受 where 影响，但不受 limit 影响），虽然看起来是两条SQL语句，但是实际上只执行了一次数据库查询。

----

# 格式化时间

strftime('%Y-%m-%d %H:%M:%S',date); 2017-11-03 15:31:26

22.多表查询

---------

# 连接查询

内连接：inner join

1.select tableA.name,tableB.age from tableA,tableB where tableA.id=tableB.uid;

2.select * from tableA inner join tableB on tableA.id=tableB.uid;

外连接：left join right join

1.select tableA.name,tableB.age from tableA left join tableB on tableA.id=tableB.uid;

注：left join 以左表为主（左表显示所有匹配，右只显示符合条件的）

2.select * from tableA right join tableB on tableA.id=tableB.uid;

注：right join 以右表为主（右表显示所有匹配，左只显示符合条件的）

全连接：full join （mysql 不支持）

# 子查询（查询嵌套，不限查询也可创建等等）

select * from tableA where age IN (select age from tableB);

注：意思是tableA表里的age能在tableB表里查到才显示

create table copy_data(select * from tableB)

注：创建嵌套实例

---------

23.创建索引

# 修改表结构(添加索引)

ALTER TABLE 表名 ADD INDEX 索引名 (name,id); # “,”添加多个字段

# 删除索引

DROP INDEX 索引名 ON 表名;

# 显示索引信息

show index from 表名;

参考 https://www.runoob.com/mysql/mysql-index.html

24.事务处理

1.用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认提交

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

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

3.使用保留点操作

SAVEPOINT savepoint_name; # 声明一个 savepoint

ROLLBACK TO savepoint_name; # 回滚到savepoint

RELEASE SAVEPOINT savepoint_name; # 删除指定保留点

----------

25.复制表

CREATE TABLE 表名 LIKE 复制表名; # 复制表信息

INSERT INTO 表名 SELECT * FROM 复制表名; # 复制表数据

26.同时修改两张表数据

UPDATE table1

LEFT JOIN table2 ON table1.xx=table2.xx (关联的字段)

SET table1.xx=value,table2.xx=value （update value）

WHERE table1.xx=xx （条件）

----------

27.用户权限

#创建用户不指定权限

create user '用户名'@'localhost' identified by '密码';

#创建用户,拥有对所有表查询和更新权限（*.*指：数据库.表，* 所有）

grant select,update on *.* to '用户名'@'localhost' identified by '密码';

#删除用户

drop user '用户名'@'localhost';

#修改用户密码(修改后需要输入 flush privileges 加载权限列表)

update mysql.user set Password=password('新密码') where User='root' and Host='localhost';

#自己登录的情况下修改自己的密码

set Password=password('新密码');

#取消用户权限

revoke update on *.* from '用户名'@'localhost'; #取消update权限

#查看权限

show grants for '用户名'@'localhost';

----------

28.数据备份/恢复

#备份数据库：test到test_2020310.sql

mysqldump -u root -p test > c:/test_2020310.sql

#备份多个数据库(test,excel_data)

mysqldump -u root -p --databases test excel_data > new_database.sql

#备份系统中所有数据库

mysqldump -u root -p --all-databases > all.sql

#备份表：test数据库my_name表到test_2020310.sql

mysqldump -u root -p test my_name > c:/my_name_2020310.sql

# 数据恢复

mysql -uroot -p test < c:/my_name_2020310.sql #指定数据库插入表

mysql -uroot -p < c:/test_2020310.sql #直接插入数据库

#不同主机之间迁移数据

mysqldump -h www.xx.com -uroot -pmi123456 test | mysql -h www.newxx.com -uroot -pmi123456

-----------

29.性能优化

#查看性能信息

explain select * from my_name;

最后编辑于：2020.05.24 18:03:08

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,937评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,503评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,712评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,668评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,677评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,601评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,975评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,637评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,881评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,621评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,710评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,387评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,971评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,947评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,189评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,805评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,449评论 2赞 342

MySQL常用操作笔记

推荐阅读更多精彩内容