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;