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;
网友评论