Mysql命令大全mysql服务的启动和停止net stop mysql net start mysql连接mysqlmysql (-h) -u 用户名 -p 用户密码注意如果是连接到另外的机器上则需要加入一个参数-h机器IP键入命令mysql -u root -p 回车后提示输入密码然后回车即可进入到mysql中了权限控制-DCL权限验证过程mysql中存在4个控制权限的表分别为user表db表tables_priv表columns_priv表mysql权限表的验证过程为先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在存在则通过验证。通过身份认证后进行权限分配按照userdbtables_privcolumns_priv的顺序进行验证。即先检查全局权限表user如果user中对应的权限为Y则此用户对所有数据库的权限都为Y将不再检查db, tables_priv,columns_priv如果user中为N则到db表中检查此用户对应的具体数据库并得到db中为Y的权限一般来说权限管理只会到db层过于细化也不方便管理如果db中为N则检查table_priv和columns_priv(如果是存储过程操作则检查mysql.procs_priv)如果满足则执行操作如果以上检查均失败则系统拒绝执行操作。因为MySQL是使用User和Host两个字段来确定用户身份的这样就带来一个问题就是一个客户端到底属于哪个host。如果一个客户端同时匹配几个Host对用户的确定将按照下面的优先级来排基本观点越精确的匹配越优先Host列上越是确定的Host越优先[localhost, 192.168.1.1, wiki.seven97.top] 优先于[192.168.%, %.seven97.top]优先于[192.%, %.top]优先于[%]User列上明确的username优先于空username。空username匹配所有用户名即匿名用户匹配所有用户Host列优先于User列考虑增加新用户grant 权限列表 on 数据库.* to 用户名登录主机 identified by 密码例增加一个用户user密码为password让其可以在本机上登录 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql然后键入以下命令grant select,insert,update,delete on . to userlocalhost Identified by “password”;如果希望该用户能够在任何机器上登陆mysql则将localhost改为%。数据库操作-DDL帮助命令: help登录到mysql中然后在mysql的提示符下运行下列命令每个命令以分号结束。选择所创建的数据库use 数据库名导入.sql文件命令(例D:/mysql.sql):source d:/mysql.sql;显示数据库列表show databases;缺省有两个数据库mysql和test。 mysql库存放着mysql的系统和用户权限信息我们改密码和新增用户实际上就是对这个库进行操作。创建数据库创建数据库CREATE DATABASE 数据库名称;创建数据库(判断如果不存在则创建)CREATE DATABASE IF NOT EXISTS 数据库名称;使用数据库使用数据库USE 数据库名称;查看当前使用的数据库SELECT DATABASE();删除数据库删除数据库DROP DATABASE 数据库名称;删除数据库(判断如果存在则删除)DROP DATABASE IF EXISTS 数据库名称;查询表查询当前数据库下所有表名称SHOW TABLES;查询表结构DESC 表名称;查看建表语句SHOW CREATE TABLE [表名]创建表创建表CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, … 字段名n 数据类型n );注意最后一行末尾不能加逗号修改表修改表名ALTER TABLE 表名 RENAME TO 新的表名; -- 将表名student修改为stu alter table student rename to stu;添加一列ALTER TABLE 表名 ADD 列名 数据类型; -- 给stu表添加一列address该字段类型是varchar(50) alter table stu add address varchar(50);修改数据类型ALTER TABLE 表名 MODIFY 列名 新数据类型; -- 将stu表中的address字段的类型改为 char(50) alter table stu modify address char(50);修改列名和数据类型ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; -- 将stu表中的address字段名改为 addr类型改为varchar(50) alter table stu change address addr varchar(50);删除列ALTER TABLE 表名 DROP 列名; -- 将stu表中的addr字段 删除 alter table stu drop addr;增加、删除和修改字段自增长增加自增长字段ALTER TABLE table_name ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;注意table_name代表要增加自增长字段的表名id代表要增加的自增长字段名。修改自增长字段ALTER TABLE table_name CHANGE column_name new_column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;table_name代表包含自增长字段的表名column_name代表原始自增长字段名new_column_name代表新的自增长字段名。请注意将数据类型更改为INT否则无法使该列成为自增长主键。完成后需要重新启动表格才能使修改生效。删除自增长字段ALTER TABLE table_name MODIFY column_name datatype;注意table_name代表要删除自增长字段的表名column_name代表要删除的自增长字段名datatype代表要设置的数据类型。增加、删除和修改数据表的列增加数据表的列ALTER TABLE 表名 ADD COLUMN 列名 数据类型; -- 在student表中增加一个名为age的INT类型列 ALTER TABLE student ADD COLUMN age INT;删除数据表的列ALTER TABLE 表名 DROP COLUMN 列名; -- 从student表中删除名为age的列 ALTER TABLE student DROP COLUMN age;修改数据表的列ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型; -- 将student表中的age列的数据类型修改为VARCHAR(10) ALTER TABLE student MODIFY COLUMN age VARCHAR(10);添加、删除和查看索引添加索引ALTER TABLE table_name ADD INDEX index_name (column_name); -- 为名为users的表的email列添加名为idx_email的索引 ALTER TABLE users ADD INDEX idx_email (email);删除索引ALTER TABLE table_name DROP INDEX index_name; -- 删除名为users的表的idx_email索引 ALTER TABLE users DROP INDEX idx_email;查看索引SHOW INDEX FROM table_name;删除表删除表DROP TABLE 表名;删除表时判断表是否存在DROP TABLE IF EXISTS 表名;创建其它类型表创建临时表CREATE TEMPORARY TABLE temp_table_name ( column1 datatype, column2 datatype, ... ); -- 创建一个名为temp_users的临时表其中包含id、name和email列。id列是主键。 CREATE TEMPORARY TABLE temp_users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) );创建内存表CREATE TABLE mem_table_name ( column1 datatype, column2 datatype, ... ) ENGINEMEMORY; -- 创建一个名为mem_users的内存表其中包含id、name和email列。id列是主键 CREATE TABLE mem_users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ENGINEMEMORY;注意内存表存储在内存中因此数据的修改会立即生效并且对所有用户可见。但是当MySQL服务器关闭时内存表中的数据将丢失。因此它适用于临时存储数据或缓存等场景。数据操作-DML清空数据truncate table 表名 delete from 表名 delete from 表名 where 列名value drop form 表名truncate删除所有数据保留表结构不能撤销还原delete是逐行删除速度极慢不适合大量数据删除drop删除表数据和表结构一起删除快速添加记录insert into 表名 values (字段列表);批量插入添加记录循环插入这个也是最普通的方式如果数据量不是很大可以使用但是用for循环进行单条插入时每次都是在获取连接(Connection)、释放连接和资源关闭等操作上如果数据量大的情况下极其消耗资源导致时间长。拼接一条sqlINSERT INTO tablename (username,password) values (xxx,xxx),(xxx,xxx),(xxx,xxx),(xxx,xxx)使用存储过程1、修改 mysql 的界定符语句结束符 delimiter /// 或者 delimiter $$$ (左对齐不要有空格) 2、创建存储过程 CREATE PROCEDURE 名字() declare i int default 0; set i0; start transaction; while i80000 do //这里是一次插入8万条 //your insert sql set ii1; end while; commit; $$$ # 表示结束不加也可以会随着存储过程的创建会自动恢复 ; delimiter; 3、调用存储过程 CALL 过程名称();使用MYSQL LOCAL_INFILELOAD DATA INFILE语句是MySQL中实现大数据批量插入的一种高效方式。该语句可以通过将文本文件中的数据加载到数据库表中从而达到批量插入的目的。该语句的语法如下LOAD DATA [LOCAL] INFILE file_name [REPLACE|IGNORE] INTO TABLE table_name [CHARACTER SET charset_name] [FIELD TERMINATED BY delimiter] [LINES TERMINATED BY delimiter] [IGNORE number LINES] [(column1, column2, ..., column n)];LOCAL为可选参数表示将文本文件加载到本地MySQL客户端file_name是文本文件的路径和名称table_name是待插入数据的目标表replace和ignore是可选参数表示当目标表中存在同样的记录时如何处理charset_name是可选参数指定文本文件的编码delimiter是可选参数指定字段和行的分隔符number是可选参数指定跳过文件的前几行column1到column n表示待插入数据的字段名。例如INTO TABLE mytable FIELDS TERMINATED BY , LINES TERMINATED BY \n (col1, col2, col3);插入语句的骚操作INSERT IGNORE INTO用于在将数据插入表中时忽略可能导致错误的冲突。当插入的数据违反唯一索引或主键约束时使用INSERT IGNORE将忽略该行的插入并不会引发错误。假设有一个表users该表的主键是id且表中已经有id为1的数据则以下语句会被忽略INSERT IGNORE INTO users (id, name) VALUES (1, seven); -- 这个操作会被忽略而不会产生错误。当使用ON DUPLICATE KEY UPDATE插入数据时如果插入的数据导致唯一索引或主键冲突SQL 引擎不会报错或忽略该操作而是会执行指定的更新操作。假设有一个表users该表的主键是id且表中已经有id为1的数据则以下语句会把name更新为seven2INSERT INTO users (id, name) VALUES (1, seven2) ON DUPLICATE KEY UPDATE name seven2;更新数据update 表名 set 字段值 where 子句 order by 子句 limit 子句WHERE 子句可选项。用于限定表中要修改的行。若不指定则修改表中所有的行。ORDER BY 子句可选项。用于限定表中的行被修改的次序。LIMIT 子句可选项。用于限定被修改的行数。导出和导入数据导出数据mysqldump --opt test mysql.test即将数据库test数据库导出到mysql.test文本文件 例mysqldump -u root -p用户密码 --databases dbname mysql.dbname导入数据:mysqlimport -u root -p用户密码 mysql.dbname。将文本数据导入数据库文本数据的字段数据之间用tab键隔开。use test; load data local infile 文件名 into table 表名;数据操作-select查询语句执行顺序首先SQL语句的基本语法如下select 查询字段1查询字段2聚合函数(如count max)distinctfrom 表名join on 表名where 条件group by 分组排列having 条件order by 排序升序降序limit 结果限定按照以上书写顺序完整的执行顺序应该是这样from子句识别查询表的数据join on/union用于连接多表数据where子句基于指定的条件对记录进行筛选group by 子句将数据划分成多个组别如按性别男、女分组有聚合函数时要使用聚集函数进行数据计算Having子句筛选满足第二条件的数据执行select语句进行字段筛选distinct筛选重复数据对数据进行排序执行limit进行结果限定。举例Mysql执行顺序select 查询字段 from 表列表名/视图列表名 where 条件 执行顺序先 from 再 where 最后selectselect 查询字段 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件 执行顺序先 from 再 where 再 group by 再 having 最后selectselect 查询字段 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件 order by 列列表 执行顺序先 from 再 where 再 group by 再 having 再 select 最后 order byselect 查询字段 from 表1 join 表2 on 表1.列1表2.列1...join 表n on 表n.列1表(n-1).列1 where 表1.条件 and 表2.条件...表n. 执行顺序先 from 再 join 再 where 最后 selecthaving只能用于筛选分组后的结果即group by之后joinINNER JOIN内连接需要用ON来指定两张表需要比较的字段最终结果只显示满足条件的数据工作步骤如下生成笛卡尔积首先INNER JOIN会生成两个表的笛卡尔积即每一行与另一表的每一行进行组合。在实际实现中数据库查询优化器不会显式生成笛卡尔积而是会直接进行连接条件的过滤。应用连接条件然后INNER JOIN会根据指定的连接条件通常是相等条件过滤这些组合只保留那些在连接条件上匹配的行。生成结果集最后根据过滤后的匹配行生成最终结果集。SELECT * FROM tab1 INNER JOIN tab2 ON tab1.id1 tab2.id2LEFT JOIN左连接可以看做在内连接的基础上把左表中不满足ON条件的数据也显示出来但结果中的右表部分中的数据为NULL。LEFT JOIN的工作原理是从左表中的每一行开始将其与右表中的每一行进行匹配。如果匹配成功则返回匹配的组合行如果匹配失败则返回左表的行并用NULL填充右表的列。因此左表中的每一行都要扫描右表。SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.id1 tab2.id2在进行左连接时需要保证左表尽可能的小。下同右连接需要保证右表尽可能小。 因为当左表较小时需要扫描的数据量较小I/O开销较低。如果左表很大每一行都需要进行多次匹配操作导致更高的I/O成本和数据扫描量。RIGHT JOIN右连接就是与左连接完全相反从右表中的每一行开始将其与左表中的每一行进行匹配。如果匹配成功则返回匹配的组合行如果匹配失败则返回右表的行并用NULL填充右表的列。因此右表中的每一行都要扫描左表。SELECT * FROM tab1 RIGHT JOIN tab2 ON tab1.id1 tab2.id2相关规范命名规范库表命名规范库名、表名必须使用小写字母并采用下划线分割。库名、表名禁止超过32个字符。库名、表名必须见名知意。命名与公司内业务、产品线等相关联。库名、表名禁止使用MySQL保留字。保留字列表见官方网站临时库、表名必须以tmp为前缀,并以日期为后缀。例如 tmp_test01_20130704。备份库、表必须以bak为前缀,并以日期为后缀。例如 bak_test01_20130704。字段命名规范字段名必须使用小写字母,并采用下划线分割禁止驼峰式命名字段名禁止超过32个字符。字段名必须见名知意。命名与公司内业务、产品线等相关联。字段名禁止使用MySQL保留字。保留字列表见官方网站索引命名规范索引名必须全部使用小写字母并采用下划线分割禁用驼峰式。非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。组合索引建议包含所有字段名过长的字段名可以采用缩写形式。例如idx_age_name_add。使用规范基础规范使用INNODB存储引擎必须要有主键推荐使用业务不相关UNSIGNED AUTO_INCREMENT列作为主键。表字符集使用UTF8UTF8MB4字符集。所有表、字段(除主键外)都需要添加注释。推荐采用英文标点避免出现乱码。禁止在数据库中存储图片、文件等大数据。每张表数据量建议控制在2000W以内。禁止在线上做数据库压力测试。禁止从测试、开发环境直连数据库索引规范单张表中索引数量不超过5个。单个索引中的字段数不超过5个。索引名必须全部使用小写。非唯一索引按照“idx_字段名称[_字段名称]”进行命名。例如idx_age_name。唯一索引按照“uniq_字段名称[_字段名称]”进行命名。例如uniq_age_name。组合索引建议包含所有字段名过长的字段名可以采用缩写形式。例如idx_age_name_add。表必须有主键推荐使用UNSIGNED自增列作为主键。唯一键由3个以下字段组成并且字段都是整形时可使用唯一键作为主键。其他情况下建议使用自增列作主键。禁止冗余索引例如(a,b,c)、(a,b)后者为冗余索引。那么就建议将(a,b)索引删除即可。禁止重复索引例如 primary key a; uniq index a; 重复索引增加维护负担、占用磁盘空间,同时没有任何益处。合理创建联合索引(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。禁止使用外键。联表查询时JOIN列的数据类型必须相同并且要建立索引。如果join列有索引但是数据类型不相同那么是不会走索引的不在低基数列上建立索引例如“性别”。选择区分度大的列建立索引。组合索引中区分度大的字段放在最前。对字符串使用前缀索引前缀索引长度建议不超过8个字符需要根据业务实际需求确定。不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引或添加CRC32或MD5伪列并建立索引。合理使用覆盖索引减少IO避免排序覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省IO。字符集规范表字符集使用UTF8必要时可申请使用UTF8MB4字符集UTF8字符集存储汉字占用3个字节存储英文字符占用一个字节。UTF8统一而且通用不会出现转码出现乱码风险。如果遇到EMOJ等表情符号的存储需求可申请使用UTF8MB4字符集。同一个实例的库表字符集必须一致JOIN字段字符集必须一致。当JOIN字段字符集不一致时即使JOIN字段有索引数据库也不会走索引禁止在字段级别设置字符集字段设计规范库表设计禁止使用分区表将大字段、访问频率低的字段拆分到单独的表中存储分离冷热数据表的默认字符集指定UTF8MB4(特殊需求除外)无须指定排序规则主键用整数类型并且字段名称用id使用AUTO_INCREMENT数据类并指定UNSIGNE禁止以非字母开头命名表名及库名禁止使用分区表分表策略推荐使用HASH进行散表表名后缀使用十进制数数字必须从0开始按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2017011601。年份必须用4位数字表示。例如按日散表user_20170209、 按月散表user_201702采用合适的分库分表策略。例如千库十表、十库百表等字段设计及类型选择建议使用UNSIGNED存储非负数值同样的字节数非负存储的数值范围更大。如TINYINT有符号为 -128-127无符号为0-255。建议使用INT UNSIGNED存储IPV4/IPV6UNSINGED INT存储IP地址占用4字节CHAR(15)则占用15字节。另外计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。所有字段均定义为NOT NULL设置默认值用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。例如与货币、金融相关的数据INT类型固定占用4字节存储例如INT(4)仅代表显示字符宽度为4位不代表存储长度区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型使用VARBINARY存储大小写敏感的变长字符串或二进制内容使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAM强烈建议使用TINYINT来代替ENUM类型ENUM类型在需要修改或增加枚举值时需要在线DDL成本较大ENUM列值如果含有数字类型可能会引起默认值混淆。禁止在数据库中存储明文密码禁止使用order by rand() order by rand()会为表增加一个伪列然后用rand()函数为每一行数据计算出rand()值然后基于该行排序这通常都会生成磁盘上的临时表因此效率非常低。建议先使用rand()函数获得随机的主键值然后通过主键获取数据。建议尽可能不使用TEXT、BLOB类型innodb是以页为单位默认16K如果使用TEXT、BLOB类型会对innodb有性能影响规范中提到的点的解释为什么建议用自增id作为主键这主要是和mysql的索引类型是B 树 有关系如果使用自增主键那么每次插入的新数据就会按顺序添加到索引节点最后的那个位置就不需要移动已有的数据。当页面写满就会自动开辟一个新页面。因为是自增主键每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。如果使用非自增主键由于每次插入主键的索引值都是随机的因此每次插入新的数据时就可能会插入到现有数据页中间的某个位置那么就不得不移动其它数据来满足新数据的插入当前页插入不下了那就会发生页分裂。造成额外的开销。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。InnoDB 存储引擎会根据不同的场景选择不同的列作为索引如果有主键默认会使用主键作为聚簇索引的索引键key如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键key在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键key为什么不建议使用null作为默认值Mysql不建议用Null作为列默认值不是因为不能使用索引而是因为索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化。比如进行索引统计时count(1),max(),min() 会省略值为NULL 的行。NULL 值是一个没意义的值但是它会占用物理空间所以会带来的存储空间的问题因为 InnoDB 存储记录的时候如果表中存在允许为 NULL 的字段那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。建议用或默认值0来代替NULL不建议使用null作为默认值并且建议必须设置默认值原因如下既然都不可为空了那就必须要有默认值否则不插入这列的话就会报错数据库不应该是用来查问题的不能靠mysql报错来告知业务有问题该不该插入应该由业务说了算对于DBA来说允许使用null是没有规范的因为不同的人不同的用法。但像合同生效时间、获奖时间等这种不可控字段是可以不设置默认值的但同样需要not null为什么禁止使用外键外键会降低数据库的性能。在MySQL中外键会自动加上索引这会使得对该表的查询等操作变得缓慢尤其是在大型数据表中。外键也会限制了表结构的调整和更改。在实际应用中表结构经常需要进行更改而如果表之间使用了外键约束这些更改可能会非常难以实现。因为更改一个表的结构需要涉及到所有以其为父表的子表这会导致长时间锁定整个数据库表甚至可能会导致数据丢失。在MySQL中外键约束可能还会引发死锁问题。当想要对多个表中的数据进行插入、更新、删除操作时由于外键约束的存在可能会导致死锁需要等待其他事务释放锁。MySQL中使用外键还会增加开发难度。开发人员需要处理数据在表之间的关系而这样的处理需要花费更多的时间和精力以及对数据库的深入理解。同时外键也会增加代码的复杂度使得SQL语句变得难以理解和调试。在阿里巴巴开发手册中也有提到传送门char和varchar的区别CHARCHAR类型用于存储固定长度字符串MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时MySQL会删除字符串中的末尾空格同时CHAR值会根据需要采用空格进行剩余空间填充以方便比较和检索。但正因为其长度固定所以会占据多余的空间也是一种空间换时间的策略CHAR适合存储很短或长度近似的字符串。例如CHAR非常适合存储密码的MD5值、定长的身份证等因为这些是定长的值。对于经常变更的数据CHAR也比VARCHAR更好因为定长的CHAR类型占用磁盘的存储空间是连续分配的不容易产生碎片。对于非常短的列CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值如果采用单字节字符集只需要一个字节但是VARCHAR(1)却需要两个字节因为还有一个记录长度的额外字节。VARCHARVARCHAR类型用于存储可变长度字符串是最常见的字符串数据类型。它比固定长度类型更节省空间因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。VARCHAR需要使用1或2个额外字节记录字符串的长度如果列的最大长度小于或等于255字节则只使用1个字节表示否则使用2个字节。假设采用latinl字符集一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节因为需要2个字节存储长度信息。VARCHAR节省了存储空间所以对性能也有帮助。但是由于行是变长的在UPDATE时可能使行变得比原来更长这就导致需要做额外的工作。如果一个行占用的空间增长并且在页内没有更多的空间可以存储在这种情况下不同的存储引擎的处理方式是不一样的。例如MylSAM会将行拆成不同的片段存储InnoDB则需要分裂页来使行可以放进页内。操作内存的方式对于varchar数据类型来说硬盘上的存储空间虽然都是根据字符串的实际长度来存储空间的但在内存中是根据varchar类型定义的长度来分配占用的内存空间的而不是根据字符串的实际长度来分配的。显然这对于排序和临时表会较大的性能影响。
MySQL使用全攻略：从命令大全到规范的最佳实践
发布时间：2026/7/31 2:28:17
Mysql命令大全mysql服务的启动和停止net stop mysql net start mysql连接mysqlmysql (-h) -u 用户名 -p 用户密码注意如果是连接到另外的机器上则需要加入一个参数-h机器IP键入命令mysql -u root -p 回车后提示输入密码然后回车即可进入到mysql中了权限控制-DCL权限验证过程mysql中存在4个控制权限的表分别为user表db表tables_priv表columns_priv表mysql权限表的验证过程为先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在存在则通过验证。通过身份认证后进行权限分配按照userdbtables_privcolumns_priv的顺序进行验证。即先检查全局权限表user如果user中对应的权限为Y则此用户对所有数据库的权限都为Y将不再检查db, tables_priv,columns_priv如果user中为N则到db表中检查此用户对应的具体数据库并得到db中为Y的权限一般来说权限管理只会到db层过于细化也不方便管理如果db中为N则检查table_priv和columns_priv(如果是存储过程操作则检查mysql.procs_priv)如果满足则执行操作如果以上检查均失败则系统拒绝执行操作。因为MySQL是使用User和Host两个字段来确定用户身份的这样就带来一个问题就是一个客户端到底属于哪个host。如果一个客户端同时匹配几个Host对用户的确定将按照下面的优先级来排基本观点越精确的匹配越优先Host列上越是确定的Host越优先[localhost, 192.168.1.1, wiki.seven97.top] 优先于[192.168.%, %.seven97.top]优先于[192.%, %.top]优先于[%]User列上明确的username优先于空username。空username匹配所有用户名即匿名用户匹配所有用户Host列优先于User列考虑增加新用户grant 权限列表 on 数据库.* to 用户名登录主机 identified by 密码例增加一个用户user密码为password让其可以在本机上登录 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql然后键入以下命令grant select,insert,update,delete on . to userlocalhost Identified by “password”;如果希望该用户能够在任何机器上登陆mysql则将localhost改为%。数据库操作-DDL帮助命令: help登录到mysql中然后在mysql的提示符下运行下列命令每个命令以分号结束。选择所创建的数据库use 数据库名导入.sql文件命令(例D:/mysql.sql):source d:/mysql.sql;显示数据库列表show databases;缺省有两个数据库mysql和test。 mysql库存放着mysql的系统和用户权限信息我们改密码和新增用户实际上就是对这个库进行操作。创建数据库创建数据库CREATE DATABASE 数据库名称;创建数据库(判断如果不存在则创建)CREATE DATABASE IF NOT EXISTS 数据库名称;使用数据库使用数据库USE 数据库名称;查看当前使用的数据库SELECT DATABASE();删除数据库删除数据库DROP DATABASE 数据库名称;删除数据库(判断如果存在则删除)DROP DATABASE IF EXISTS 数据库名称;查询表查询当前数据库下所有表名称SHOW TABLES;查询表结构DESC 表名称;查看建表语句SHOW CREATE TABLE [表名]创建表创建表CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, … 字段名n 数据类型n );注意最后一行末尾不能加逗号修改表修改表名ALTER TABLE 表名 RENAME TO 新的表名; -- 将表名student修改为stu alter table student rename to stu;添加一列ALTER TABLE 表名 ADD 列名 数据类型; -- 给stu表添加一列address该字段类型是varchar(50) alter table stu add address varchar(50);修改数据类型ALTER TABLE 表名 MODIFY 列名 新数据类型; -- 将stu表中的address字段的类型改为 char(50) alter table stu modify address char(50);修改列名和数据类型ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; -- 将stu表中的address字段名改为 addr类型改为varchar(50) alter table stu change address addr varchar(50);删除列ALTER TABLE 表名 DROP 列名; -- 将stu表中的addr字段 删除 alter table stu drop addr;增加、删除和修改字段自增长增加自增长字段ALTER TABLE table_name ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;注意table_name代表要增加自增长字段的表名id代表要增加的自增长字段名。修改自增长字段ALTER TABLE table_name CHANGE column_name new_column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;table_name代表包含自增长字段的表名column_name代表原始自增长字段名new_column_name代表新的自增长字段名。请注意将数据类型更改为INT否则无法使该列成为自增长主键。完成后需要重新启动表格才能使修改生效。删除自增长字段ALTER TABLE table_name MODIFY column_name datatype;注意table_name代表要删除自增长字段的表名column_name代表要删除的自增长字段名datatype代表要设置的数据类型。增加、删除和修改数据表的列增加数据表的列ALTER TABLE 表名 ADD COLUMN 列名 数据类型; -- 在student表中增加一个名为age的INT类型列 ALTER TABLE student ADD COLUMN age INT;删除数据表的列ALTER TABLE 表名 DROP COLUMN 列名; -- 从student表中删除名为age的列 ALTER TABLE student DROP COLUMN age;修改数据表的列ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型; -- 将student表中的age列的数据类型修改为VARCHAR(10) ALTER TABLE student MODIFY COLUMN age VARCHAR(10);添加、删除和查看索引添加索引ALTER TABLE table_name ADD INDEX index_name (column_name); -- 为名为users的表的email列添加名为idx_email的索引 ALTER TABLE users ADD INDEX idx_email (email);删除索引ALTER TABLE table_name DROP INDEX index_name; -- 删除名为users的表的idx_email索引 ALTER TABLE users DROP INDEX idx_email;查看索引SHOW INDEX FROM table_name;删除表删除表DROP TABLE 表名;删除表时判断表是否存在DROP TABLE IF EXISTS 表名;创建其它类型表创建临时表CREATE TEMPORARY TABLE temp_table_name ( column1 datatype, column2 datatype, ... ); -- 创建一个名为temp_users的临时表其中包含id、name和email列。id列是主键。 CREATE TEMPORARY TABLE temp_users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) );创建内存表CREATE TABLE mem_table_name ( column1 datatype, column2 datatype, ... ) ENGINEMEMORY; -- 创建一个名为mem_users的内存表其中包含id、name和email列。id列是主键 CREATE TABLE mem_users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ) ENGINEMEMORY;注意内存表存储在内存中因此数据的修改会立即生效并且对所有用户可见。但是当MySQL服务器关闭时内存表中的数据将丢失。因此它适用于临时存储数据或缓存等场景。数据操作-DML清空数据truncate table 表名 delete from 表名 delete from 表名 where 列名value drop form 表名truncate删除所有数据保留表结构不能撤销还原delete是逐行删除速度极慢不适合大量数据删除drop删除表数据和表结构一起删除快速添加记录insert into 表名 values (字段列表);批量插入添加记录循环插入这个也是最普通的方式如果数据量不是很大可以使用但是用for循环进行单条插入时每次都是在获取连接(Connection)、释放连接和资源关闭等操作上如果数据量大的情况下极其消耗资源导致时间长。拼接一条sqlINSERT INTO tablename (username,password) values (xxx,xxx),(xxx,xxx),(xxx,xxx),(xxx,xxx)使用存储过程1、修改 mysql 的界定符语句结束符 delimiter /// 或者 delimiter $$$ (左对齐不要有空格) 2、创建存储过程 CREATE PROCEDURE 名字() declare i int default 0; set i0; start transaction; while i80000 do //这里是一次插入8万条 //your insert sql set ii1; end while; commit; $$$ # 表示结束不加也可以会随着存储过程的创建会自动恢复 ; delimiter; 3、调用存储过程 CALL 过程名称();使用MYSQL LOCAL_INFILELOAD DATA INFILE语句是MySQL中实现大数据批量插入的一种高效方式。该语句可以通过将文本文件中的数据加载到数据库表中从而达到批量插入的目的。该语句的语法如下LOAD DATA [LOCAL] INFILE file_name [REPLACE|IGNORE] INTO TABLE table_name [CHARACTER SET charset_name] [FIELD TERMINATED BY delimiter] [LINES TERMINATED BY delimiter] [IGNORE number LINES] [(column1, column2, ..., column n)];LOCAL为可选参数表示将文本文件加载到本地MySQL客户端file_name是文本文件的路径和名称table_name是待插入数据的目标表replace和ignore是可选参数表示当目标表中存在同样的记录时如何处理charset_name是可选参数指定文本文件的编码delimiter是可选参数指定字段和行的分隔符number是可选参数指定跳过文件的前几行column1到column n表示待插入数据的字段名。例如INTO TABLE mytable FIELDS TERMINATED BY , LINES TERMINATED BY \n (col1, col2, col3);插入语句的骚操作INSERT IGNORE INTO用于在将数据插入表中时忽略可能导致错误的冲突。当插入的数据违反唯一索引或主键约束时使用INSERT IGNORE将忽略该行的插入并不会引发错误。假设有一个表users该表的主键是id且表中已经有id为1的数据则以下语句会被忽略INSERT IGNORE INTO users (id, name) VALUES (1, seven); -- 这个操作会被忽略而不会产生错误。当使用ON DUPLICATE KEY UPDATE插入数据时如果插入的数据导致唯一索引或主键冲突SQL 引擎不会报错或忽略该操作而是会执行指定的更新操作。假设有一个表users该表的主键是id且表中已经有id为1的数据则以下语句会把name更新为seven2INSERT INTO users (id, name) VALUES (1, seven2) ON DUPLICATE KEY UPDATE name seven2;更新数据update 表名 set 字段值 where 子句 order by 子句 limit 子句WHERE 子句可选项。用于限定表中要修改的行。若不指定则修改表中所有的行。ORDER BY 子句可选项。用于限定表中的行被修改的次序。LIMIT 子句可选项。用于限定被修改的行数。导出和导入数据导出数据mysqldump --opt test mysql.test即将数据库test数据库导出到mysql.test文本文件 例mysqldump -u root -p用户密码 --databases dbname mysql.dbname导入数据:mysqlimport -u root -p用户密码 mysql.dbname。将文本数据导入数据库文本数据的字段数据之间用tab键隔开。use test; load data local infile 文件名 into table 表名;数据操作-select查询语句执行顺序首先SQL语句的基本语法如下select 查询字段1查询字段2聚合函数(如count max)distinctfrom 表名join on 表名where 条件group by 分组排列having 条件order by 排序升序降序limit 结果限定按照以上书写顺序完整的执行顺序应该是这样from子句识别查询表的数据join on/union用于连接多表数据where子句基于指定的条件对记录进行筛选group by 子句将数据划分成多个组别如按性别男、女分组有聚合函数时要使用聚集函数进行数据计算Having子句筛选满足第二条件的数据执行select语句进行字段筛选distinct筛选重复数据对数据进行排序执行limit进行结果限定。举例Mysql执行顺序select 查询字段 from 表列表名/视图列表名 where 条件 执行顺序先 from 再 where 最后selectselect 查询字段 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件 执行顺序先 from 再 where 再 group by 再 having 最后selectselect 查询字段 from 表列表名/视图列表名 where 条件 group by (列列表) having 条件 order by 列列表 执行顺序先 from 再 where 再 group by 再 having 再 select 最后 order byselect 查询字段 from 表1 join 表2 on 表1.列1表2.列1...join 表n on 表n.列1表(n-1).列1 where 表1.条件 and 表2.条件...表n. 执行顺序先 from 再 join 再 where 最后 selecthaving只能用于筛选分组后的结果即group by之后joinINNER JOIN内连接需要用ON来指定两张表需要比较的字段最终结果只显示满足条件的数据工作步骤如下生成笛卡尔积首先INNER JOIN会生成两个表的笛卡尔积即每一行与另一表的每一行进行组合。在实际实现中数据库查询优化器不会显式生成笛卡尔积而是会直接进行连接条件的过滤。应用连接条件然后INNER JOIN会根据指定的连接条件通常是相等条件过滤这些组合只保留那些在连接条件上匹配的行。生成结果集最后根据过滤后的匹配行生成最终结果集。SELECT * FROM tab1 INNER JOIN tab2 ON tab1.id1 tab2.id2LEFT JOIN左连接可以看做在内连接的基础上把左表中不满足ON条件的数据也显示出来但结果中的右表部分中的数据为NULL。LEFT JOIN的工作原理是从左表中的每一行开始将其与右表中的每一行进行匹配。如果匹配成功则返回匹配的组合行如果匹配失败则返回左表的行并用NULL填充右表的列。因此左表中的每一行都要扫描右表。SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.id1 tab2.id2在进行左连接时需要保证左表尽可能的小。下同右连接需要保证右表尽可能小。 因为当左表较小时需要扫描的数据量较小I/O开销较低。如果左表很大每一行都需要进行多次匹配操作导致更高的I/O成本和数据扫描量。RIGHT JOIN右连接就是与左连接完全相反从右表中的每一行开始将其与左表中的每一行进行匹配。如果匹配成功则返回匹配的组合行如果匹配失败则返回右表的行并用NULL填充右表的列。因此右表中的每一行都要扫描左表。SELECT * FROM tab1 RIGHT JOIN tab2 ON tab1.id1 tab2.id2相关规范命名规范库表命名规范库名、表名必须使用小写字母并采用下划线分割。库名、表名禁止超过32个字符。库名、表名必须见名知意。命名与公司内业务、产品线等相关联。库名、表名禁止使用MySQL保留字。保留字列表见官方网站临时库、表名必须以tmp为前缀,并以日期为后缀。例如 tmp_test01_20130704。备份库、表必须以bak为前缀,并以日期为后缀。例如 bak_test01_20130704。字段命名规范字段名必须使用小写字母,并采用下划线分割禁止驼峰式命名字段名禁止超过32个字符。字段名必须见名知意。命名与公司内业务、产品线等相关联。字段名禁止使用MySQL保留字。保留字列表见官方网站索引命名规范索引名必须全部使用小写字母并采用下划线分割禁用驼峰式。非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。组合索引建议包含所有字段名过长的字段名可以采用缩写形式。例如idx_age_name_add。使用规范基础规范使用INNODB存储引擎必须要有主键推荐使用业务不相关UNSIGNED AUTO_INCREMENT列作为主键。表字符集使用UTF8UTF8MB4字符集。所有表、字段(除主键外)都需要添加注释。推荐采用英文标点避免出现乱码。禁止在数据库中存储图片、文件等大数据。每张表数据量建议控制在2000W以内。禁止在线上做数据库压力测试。禁止从测试、开发环境直连数据库索引规范单张表中索引数量不超过5个。单个索引中的字段数不超过5个。索引名必须全部使用小写。非唯一索引按照“idx_字段名称[_字段名称]”进行命名。例如idx_age_name。唯一索引按照“uniq_字段名称[_字段名称]”进行命名。例如uniq_age_name。组合索引建议包含所有字段名过长的字段名可以采用缩写形式。例如idx_age_name_add。表必须有主键推荐使用UNSIGNED自增列作为主键。唯一键由3个以下字段组成并且字段都是整形时可使用唯一键作为主键。其他情况下建议使用自增列作主键。禁止冗余索引例如(a,b,c)、(a,b)后者为冗余索引。那么就建议将(a,b)索引删除即可。禁止重复索引例如 primary key a; uniq index a; 重复索引增加维护负担、占用磁盘空间,同时没有任何益处。合理创建联合索引(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。禁止使用外键。联表查询时JOIN列的数据类型必须相同并且要建立索引。如果join列有索引但是数据类型不相同那么是不会走索引的不在低基数列上建立索引例如“性别”。选择区分度大的列建立索引。组合索引中区分度大的字段放在最前。对字符串使用前缀索引前缀索引长度建议不超过8个字符需要根据业务实际需求确定。不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引或添加CRC32或MD5伪列并建立索引。合理使用覆盖索引减少IO避免排序覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省IO。字符集规范表字符集使用UTF8必要时可申请使用UTF8MB4字符集UTF8字符集存储汉字占用3个字节存储英文字符占用一个字节。UTF8统一而且通用不会出现转码出现乱码风险。如果遇到EMOJ等表情符号的存储需求可申请使用UTF8MB4字符集。同一个实例的库表字符集必须一致JOIN字段字符集必须一致。当JOIN字段字符集不一致时即使JOIN字段有索引数据库也不会走索引禁止在字段级别设置字符集字段设计规范库表设计禁止使用分区表将大字段、访问频率低的字段拆分到单独的表中存储分离冷热数据表的默认字符集指定UTF8MB4(特殊需求除外)无须指定排序规则主键用整数类型并且字段名称用id使用AUTO_INCREMENT数据类并指定UNSIGNE禁止以非字母开头命名表名及库名禁止使用分区表分表策略推荐使用HASH进行散表表名后缀使用十进制数数字必须从0开始按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2017011601。年份必须用4位数字表示。例如按日散表user_20170209、 按月散表user_201702采用合适的分库分表策略。例如千库十表、十库百表等字段设计及类型选择建议使用UNSIGNED存储非负数值同样的字节数非负存储的数值范围更大。如TINYINT有符号为 -128-127无符号为0-255。建议使用INT UNSIGNED存储IPV4/IPV6UNSINGED INT存储IP地址占用4字节CHAR(15)则占用15字节。另外计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。所有字段均定义为NOT NULL设置默认值用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。例如与货币、金融相关的数据INT类型固定占用4字节存储例如INT(4)仅代表显示字符宽度为4位不代表存储长度区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型使用VARBINARY存储大小写敏感的变长字符串或二进制内容使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAM强烈建议使用TINYINT来代替ENUM类型ENUM类型在需要修改或增加枚举值时需要在线DDL成本较大ENUM列值如果含有数字类型可能会引起默认值混淆。禁止在数据库中存储明文密码禁止使用order by rand() order by rand()会为表增加一个伪列然后用rand()函数为每一行数据计算出rand()值然后基于该行排序这通常都会生成磁盘上的临时表因此效率非常低。建议先使用rand()函数获得随机的主键值然后通过主键获取数据。建议尽可能不使用TEXT、BLOB类型innodb是以页为单位默认16K如果使用TEXT、BLOB类型会对innodb有性能影响规范中提到的点的解释为什么建议用自增id作为主键这主要是和mysql的索引类型是B 树 有关系如果使用自增主键那么每次插入的新数据就会按顺序添加到索引节点最后的那个位置就不需要移动已有的数据。当页面写满就会自动开辟一个新页面。因为是自增主键每次插入一条新记录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。如果使用非自增主键由于每次插入主键的索引值都是随机的因此每次插入新的数据时就可能会插入到现有数据页中间的某个位置那么就不得不移动其它数据来满足新数据的插入当前页插入不下了那就会发生页分裂。造成额外的开销。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。InnoDB 存储引擎会根据不同的场景选择不同的列作为索引如果有主键默认会使用主键作为聚簇索引的索引键key如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键key在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键key为什么不建议使用null作为默认值Mysql不建议用Null作为列默认值不是因为不能使用索引而是因为索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化。比如进行索引统计时count(1),max(),min() 会省略值为NULL 的行。NULL 值是一个没意义的值但是它会占用物理空间所以会带来的存储空间的问题因为 InnoDB 存储记录的时候如果表中存在允许为 NULL 的字段那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。建议用或默认值0来代替NULL不建议使用null作为默认值并且建议必须设置默认值原因如下既然都不可为空了那就必须要有默认值否则不插入这列的话就会报错数据库不应该是用来查问题的不能靠mysql报错来告知业务有问题该不该插入应该由业务说了算对于DBA来说允许使用null是没有规范的因为不同的人不同的用法。但像合同生效时间、获奖时间等这种不可控字段是可以不设置默认值的但同样需要not null为什么禁止使用外键外键会降低数据库的性能。在MySQL中外键会自动加上索引这会使得对该表的查询等操作变得缓慢尤其是在大型数据表中。外键也会限制了表结构的调整和更改。在实际应用中表结构经常需要进行更改而如果表之间使用了外键约束这些更改可能会非常难以实现。因为更改一个表的结构需要涉及到所有以其为父表的子表这会导致长时间锁定整个数据库表甚至可能会导致数据丢失。在MySQL中外键约束可能还会引发死锁问题。当想要对多个表中的数据进行插入、更新、删除操作时由于外键约束的存在可能会导致死锁需要等待其他事务释放锁。MySQL中使用外键还会增加开发难度。开发人员需要处理数据在表之间的关系而这样的处理需要花费更多的时间和精力以及对数据库的深入理解。同时外键也会增加代码的复杂度使得SQL语句变得难以理解和调试。在阿里巴巴开发手册中也有提到传送门char和varchar的区别CHARCHAR类型用于存储固定长度字符串MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时MySQL会删除字符串中的末尾空格同时CHAR值会根据需要采用空格进行剩余空间填充以方便比较和检索。但正因为其长度固定所以会占据多余的空间也是一种空间换时间的策略CHAR适合存储很短或长度近似的字符串。例如CHAR非常适合存储密码的MD5值、定长的身份证等因为这些是定长的值。对于经常变更的数据CHAR也比VARCHAR更好因为定长的CHAR类型占用磁盘的存储空间是连续分配的不容易产生碎片。对于非常短的列CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值如果采用单字节字符集只需要一个字节但是VARCHAR(1)却需要两个字节因为还有一个记录长度的额外字节。VARCHARVARCHAR类型用于存储可变长度字符串是最常见的字符串数据类型。它比固定长度类型更节省空间因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。VARCHAR需要使用1或2个额外字节记录字符串的长度如果列的最大长度小于或等于255字节则只使用1个字节表示否则使用2个字节。假设采用latinl字符集一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节因为需要2个字节存储长度信息。VARCHAR节省了存储空间所以对性能也有帮助。但是由于行是变长的在UPDATE时可能使行变得比原来更长这就导致需要做额外的工作。如果一个行占用的空间增长并且在页内没有更多的空间可以存储在这种情况下不同的存储引擎的处理方式是不一样的。例如MylSAM会将行拆成不同的片段存储InnoDB则需要分裂页来使行可以放进页内。操作内存的方式对于varchar数据类型来说硬盘上的存储空间虽然都是根据字符串的实际长度来存储空间的但在内存中是根据varchar类型定义的长度来分配占用的内存空间的而不是根据字符串的实际长度来分配的。显然这对于排序和临时表会较大的性能影响。
