更改字符集 | alter database dbName character set 'charsetName'; alter database dwg2pdf character set 'utf8'; alter table tableName convert to character set ‘'charsetName'’; alter table tasks convert to character set 'utf8'; |
创建表之后 添加约束 | 1.主键约束 添加:alter table table_name add primary key (字段) 删除:alter table table_name drop primary key 2.非空约束 添加:alter table table_name modify 列名 数据类型 not null 删除:alter table table_name modify 列名 数据类型 null 3.唯一约束 添加:alter table table_name add unique 约束名(字段) 删除:alter table table_name drop key 约束名 4.自动增长 添加:alter table table_name modify 列名 int auto_increment 删除:alter table table_name modify 列名 int 5.外键约束 添加:alter table table_name add constraint 约束名 foreign key(外键列) references 主键表(主键列) 删除: 第一步:删除外键 alter table table_name drop foreign key 约束名 第二步:删除索引 alter table table_name drop index 索引名 [^1]: 约束名和索引名一样 6.默认值 添加:alter table table_name alter 列名 set default '值' 例子: alter table tasks modify sDwgPath varchar(255) not null; alter table tasks modify id int(20) not null; alter table tasks add unique(id); alter table tasks modify id int(64) not null; alter table tasks modify id int auto_increment; |
概述 | MySQL性能优化:通过合理安排资源,调整系统参数,使MySQL运行更快,更节约资源; 包括:查询速度优化、更新速度优化、数据库结构优化、服务器优化; 查看服务器性能参数show status like 'value'; connections连接服务器次数uptime上线时间 slow_queries慢查询次数com_select查询操作次数 com_insert插入操作次数com_update更新操作次数 com_delete删除操作次数 |
查询 | 用explain和describe,分析查询语句; explain [extended]selectOptions; extended将产生附加信息;selectOptions包括from where等子句; describe select selectOptions;describe 可以简写为desc; describe和explain的语法相同,结果也相同; 索引可以提高查询速度; 并非所有查询都会用到索引; l like关键字查询,匹配字符串第一个字符是%,索引就不会起作用; l 多列索引,只有查询使用了第一个字段时,索引才会起作用; 子查询效率低,因为要建立一个临时表,查询结束后又要销毁临时表; 可以用join连接查询代替子查询,因为join查询不会创建临时表; |
结构 | 分解表:将字段多的表分解成多个表,将使用频率低的字段分离到单独表; 建立中间表:将联合查询的数据放入中间表,用中间表查询代替联合查询; 增加冗余字段,可以提高查询速度; |
插入 | 插入记录时,影响插入速度的因素有:索引、唯一性校验、一次插入条数等; ①禁用索引:禁用→插入→启用; 插入数据时,系统会为新插入数据建立索引,拖慢了插入速度; 在插入大量数据前禁用索引,可以提高插入速度; 禁用索引 alter table tableName disable keys; 启用索引alter table tableName enable keys; ②禁用唯一性检查:禁用→插入→启用; set unique_check=0; set unique_check=1; ③使用批量插入:使用一条语句插入多条数据,比一条语句插入一条数据要快; ④使用load data infile批量导入 load data infile比insert快; 对于InnoDB引擎,优化方法: 禁用唯一性检查set unique_checks=0;set unique_checks=1; 禁用外键检查set foreign_key_checks=0;set foreign_key_checks=1; 禁用自动提交set autocommit=0;set autocommit=1; |
表 | ■分析表:分析关键字分布 analyze [local|no_write_to_binlog] table tableName[,...]; local是no_write_to_binlog的别名,二者等价,表示不写入二进制日志记录; analyze table message; ■检查表:是否存在错误 check table tableName [,...] [option]... option={quick|fast|medium|extended|changed} ① quick不检查行,不检查错误连接| ② fast只检查没有正确关闭的表| ③ medium扫描行,以验证被删除的连接有效;计算各行关键字校验和,并用校验和验证;| ④ extended对每行的所有关键字查找,可以确保100%一致,但是很慢| ⑤ changed只检查上次检查后更改的和没有正常关闭的表; ■优化表:消除删除或更新造成的空间浪费 optimize [local|no_write_to_binlog] table tableName [,...]; optimize 只能优化varchar、blog或text类型字段; optimize 可以清理由删除更新操作造成的数据碎片; |
服务器 | 服务器优化:硬件优化、参数优化; ■硬件优化:大内存、高速磁盘、分散磁盘IO、多处理器多线程; ■MySQL参数优化:配置参数在my.cnf或者my.ini文件的[mysqld]组中; (1) key_buffer_size索引缓冲区大小; (2) table_cache同时打开表个数;不是越大越好; (3) query_cache_size查询缓冲区;0-1-2 (4) sort_buffer_size排序缓冲区,越大,排序越快; (5) read_buffer_sizs 扫描表缓冲区; (6) read_rnd_buffer_size每个线程的缓冲区;频繁进行多次连续 扫描,可增加该值; (7) innodb_buffer_pool_size表示InnoDB表和索引缓存;越大查询越快,太大影响系统性能; (8) max_connections最大连接数,过大浪费内存或者服务器卡死; (9) innodb_flush_log_at_trx_commit写入日志的时机;0-1-2 (10) back_log侦听队列大小,存入堆栈的最大请求数;高并发才需要调高; (11) interactive_timeout服务器关闭连接前等待的秒数; (12) sort_buffer_size排序线程的缓冲区;增大排序快;默认2M; (13) wait_timeout关闭连接时等待的秒数,默认28800; |
备份 | mysqldump -u user -h host -ppassword dbName [tableName[,...]]>fileName.sql; user 用户名;host 主机名;dbName 数据库;tableName表名;fileName.sql另存的文件名及后缀; ■备份一个数据库 mysqldump -u root -p myDatabase>C:\backup\myBackUp_20220901.sql enter password 666; ■备份数据表 mysqldump -u user -h host -p dbName [tableName[,...]]>fileName.sql; mydqldump -u ‘root’-h ‘localhost’-p ‘myDB’student>C:\Users\Administrator\Desktop\backup.sql; ■备份多个数据库 mysqldump -u root -h host -p --databases dbName[,...]>fileName.sql; databases后面至少指定一个数据库名称,多个用逗号隔开; mysqldump -u root -h ‘localhost’ -p --databases db1,db2,db3>threeBackUp.sql; ■备份所有数据库 mysqldump -u root -h host -p --all-databases >fileName.sql; mysqldump -u root -h ‘localhost’-p --all-databases >allBackUp.sql; ■MyISAM引擎备份插件(Unix系统) mysqlhotcopy dbName[,...] /path/to/new.directory; mysqlhotcopy -u root -p test /usr/backup |
还原 | ■用C:\personOut.txt; mysql -u root -p --html --execute=”select*from person;” test>C:\personOut.html; mysql -u root -p --xml --execute=”select*from person;” test>C:\personOut.xml; |
导入 | load data infile ‘fileName.txt’int table tableName [options] [ignore number lines] --optoins 同上 load data infile ‘C:\person.txt’ into table test.person; 还原之前,应先删除原有数据;use test; delete from person; load data infile ‘C:\person.txt’ into table test.person fields terminated by ‘,’ enclosed by ‘\”’ escaped by’\’’ lines terminated by ‘\r\n’; mysqlimport -u root-p dbName fileName.txt [options]; C:\>mysqlinport -u root -p test C:\backup\person.txt --fields-terminated-by=,--fields-optionally-enclosed-by=\” --fields-escaped-by=? --lines-terminated-by=\r\n; |
概述 | MySQL是一个多用户数据库,可以为不同的用户指定不同的权限; root用户=超级管理员:拥有所有权限; 权限:创建用户、删除用户、修改用户密码; 普通用户:拥有被授予的权限; | |
信息表 | 信息表:user,db,host,tables_priv,columns_priv,procs_priv; ■user表的权限是全局级的,user表有删除权限,则可以删除所有数据库的所有数据;
■host表:对主机的操作权限; ■tables_priv表:对表的操作权限; ■columns_priv表:对列的操作权限; select*from mysql.tables_priv; select user,password,host,select_priv,update_priv from mysql.user; select*from mysql.db; | |
| ■procs_priv表:对存储过程和存储函数的权限; | |
登录 | -h主机名(host),指定主机名或IP,不指定默认localhost; -u用户名(user); -p密码(password),密码和-p之间不能有空格; -P端口号(Port)默认3306; 数据库名,可以再命令最后指定数据库名称; -e要执行的sql语句,登录→执行→退出; mysql --user=tom --password=guess db_name; mysql -u tom -pguess db_anme;-p和密码之间不能有空格; mysql -uroot -p -hlocalhost test; mysql-uroot-p-hlocalhost test -e”desc person;” | |
用户 | ■■■创建用户create user create user userSpecification [,...] userSpecification:user@host [identified by [password]’password’|identified with authPlugin[as ‘authString’] ] l user 表示用户名,host表示允许登录的主机名称; l identified by用来设置登录密码; l [password]表示使用哈希值密码; l ’password’表示使用普通明文密码; l identified with为用户指定身份验证插件; l authPlugin身份验证插件名称,可以是单引号字符串或双引号字符串; l authString’该参数传递给身份验证插件,由该插件解释参数的意义; 使用create user必须要有全局的create user权限,或者数据库的insert权限; 创建后,会在user表中添加一条记录,但是该账户没有任何权限; 如果待添加账户已经存在,则会返回错误; create user ‘jeffrey’@’localhost’ indentified by ‘mypass’;账号+密码 create user ‘jeffrey’@’localhost’;不指定密码,则登录不需要密码 create user ‘jeffrey’;不指定主机,则可访问所有主机; 如果只指定用户名,主机名则默认‘%’,表示对所有主机开放; select password(‘mypass’);显示密码的哈希值 显示结果:password('mypass') *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 create user ‘jeffrey’@’localhost’ identified by password ‘*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4’; 使用哈希值设置密码; create user ‘jeffrey’@’localhost’ idendified with my_auth_plugin; 使用插件进行身份验证; identified by和identified with是互斥的,只能二选一; ■■■创建用户grant(推荐) grant privileges on db.table to user@host [idendified by ‘password’][,...] [with grant option] ; privileges 赋予用户的权限类型; db.table 表示用户权限作用的表; idendified by设置密码;‘password’表示用户密码; [with grant option]赋予用户grant权限,该用户可以对其他用户赋予权限; select host,user,select_priv,update_priv from mysql.user where user=’testUser’; ■■■创建用户 编辑user表 insert into mysql.user(host,user,password,[privlegeList]) values(‘host’,’userName’,password(‘password’),[privilegeValueList]); password()函数为密码的加密函数; privilegeValueList的值只能是‘Y’或‘N’; insert into user(host,user,password) values(‘localhost’,’testUser’,password(‘123456’)); mysql> insert into mysql. user(host,user,password) values('localhost','testUser',password('666')); ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value flush privileges;重新加载授权表; insert into需要使用加密函数password(),而grant会自动加密,不需要加密函数; ■■■删除用户 drop user[,...]; drop user ‘user’@’localhost’;删除user在本地登录权限; drop user;删除来自所有授权表的账户权限记录; 被删除用户打开时,drop不会立即生效,等到用户关闭对话后才会生效; 当用户再次尝试登录时,则登录失败; delete from mysql.user where host=’hostName’ and user=’userName’; delete from mysql.user where host=’localhost’ and user=’test’; | |
密码 | ■■■root用户改密码 ①用mysqladmin 改密码 mysqladmin -u username -h localhost -p password ‘newPassword’; mysqladmin -u root -p password “654321”;新密码需要用双引号; ②通过user表改密码 update mysql.user set password=password(“newPassword”) where user=’root’and host=’localhost’ update mysql.user set password=password(“645321”); ③用set改密码 set password=password(“666888”); ■■■root修改普通用户密码 set password for ‘user’@’host’=password(“newPassword”); set password for’test’@’localhost’=password(“666”); update mysql.user set password=password(“newPassword”) where user=’userName’and host=’hostName’; update mysql.user set password=password(“999”)where user=’test’and host=’localhost’; grant usage on *.* to ‘userName’@’%’idendified by ‘newPassword’; grant usage on *.* to ‘test’@’localhost’idendified by ‘666’; ■■■普通用户自己改密码 set password=password(‘newPassword’); ■■■忘记密码的解决方案 以skip-grant-tables选项启动时,不加载权限判断,任何用户都能访问数据库; mysqld --skip-grant-tables;(Windows系统) mysqld-nt --skip-grant-tables;(Windows系统) mysqld_safe --skip-grant-tables user=mysql;(Linux系统) /etc/init.d/mysql start-mysql --skip-grant-tables;(Linux系统) 使用root登录,重新设置密码(XP系统) ① C:\>net stop mysql 停止服务(命令行窗口输入) ② C:\mysqld --skip-grant-tables 启动服务(命令行窗口输入) ③ C:\mysql -u root 无密码登录(打开另外一个命令行窗口) ④ update mysql,user set password=password(‘newPassword’)where user=’root’and host=’localhost’; 重新设置密码; ⑤ flush privileges;重新加载权限表,新的密码才会生效; 关闭输入 mysqld --skip-grant-tables窗口,用新密码登录; | |
权限 | ■■■授权grant 全局层级:grant all on *.*;revoke all on *.*;存储在mysql.user表中 数据库层级:grant all on dbName;revoke all on dbName;存储在mysql.db和mysql.host表中; 表层级:grant all on dbName.tableName; revoke all on dbName.tableName;存储在mysql.tables_priv中; 列层级:存储在mysql.columns_priv表中;当使用revoke时,必须指定与被授权列相同的列; 子程序层级:create routine,alter routine,execute,grant适用于已存储的子程序;这些权限可以授予全局层级和数据库层级;除了create routine外,可以授予子程序层级,存储在mysql.procs_priv表中; grant privType [(columns)] [,...] on [objectType] table1,table2... to user [identified by [password]’password’][,...] [with grant option] grant option: max_queries_per_hour count每小时查询次数 max_updates_per_hour count每小时更新次数 max_connections_per_hour count每小时连接次数 max_user_connections count同时最大连接数 privType 权限类型;columns权限作用的列,不指定表示作用于整个表; objectType授权作用的对象类型,有table,function,procedure; table1,table2...权限作用的表; user用户账户,用户名+主机名,例如’userName’@’hostName’; identified by密码; grant select,insert on *.* to ‘student’@’localhost’ idendified by ‘123456’; ■■■取消权限revoke revoke取消权限后,用户的账户记录将从db,host,tables_priv,columns_priv表中删除; 但是用户账号不会删除,仍在user表中,想要删除账号,用drop user; 从user表中删除账号前,应该回收所有权限; revoke all privileges,grant option from ‘userName’@’hostName’[,...] revoke privType [(columns)] [,...] on table1 [,...] from ‘userName’@’hostName’[,...]; revoke update on *.* from ‘student’@’localhost’; | |
查看 | show grants for ‘userName’@’hostName’; show grants for ‘student’@’localhost’; select privilegesList from user where user=’userName’,host=’hostName’; select tables_priv,update_priv from user where user=’tom’,host=’localhost’; | |
访问 | ①连接核实阶段 MySQL用3个字段检查身份,host,user,password; ②请求核实阶段 对用户将要进行的操作,检查是否有足够的权限来执行; 检查顺序:user表→db表→tables_priv表→columns_priv表; |
定义 | 1. 触发器也是一段程序,相当于事件处理程序EventHandler; 2. 触发器和存储过程一样,都是嵌入到MySQL中的一段程序; 3. 触发器由事件来触发,触发事件包括insert,update,delete; 4. 触发器基于某个表,当该表进行特定操作时,就会激活该触发器; 5. 触发器不需要call来调用,而是由系统自动调用; | ||
创建 | create trigger triggerName triggerTime triggerEvent on tableName for each row triggerBody; triggerName 触发器名称,用户指定; triggerTime 触发时机,可以指定为before或after; triggerEvent 触发事件,包括insert、update、delete; tableName 建立触发器的表名,即在哪张表上建立触发器; triggerBody触发程序体; 触发器可以包含多条语句,用begin和end标识; create talbe account(num int,amount decimal(10,2)); create trigger myTrigger before insert on account for each row set@sum=@sum+new.amount; create trigger triggerName triggerTime triggerEvent on tableName for each row triggerBody;
| ||
查看 | show triggers; select*from information_schema.triggers where condition; select*from infromation_schema.triggers where trigger_name=’myTrigger’\g; | ||
使用 | 当满足触发条件时,系统自动激活触发器,执行相关代码; | ||
删除 | drop trigger [dbName.]triggerName; 如果不指定数据库名称,则在当前数据库执行删除操作; drop trigger test.myTrigger; |
概述 | 1. 视图中的数据是对现有表数据的引用; 2. 视图是从一个表或者多个表中导出的; 3. 视图中可以使用:select,insert,update,delete,, 4. 视图可以从现有视图中创建,视图一经定义便存储于数据库中; 5. 通过视图看到的数据只是基本表中的数据; 6. 视图中数据修改,基本表中数据也同时修改; 7. 基本表中的数据修改,视图中的数据也同时修改; |
创建 | 视图创建基于select语句和基本表数据; create [or replace] [algorithm={undefined|merge|templateble}] view viewName[{columnList}] as select statement [with [cascaded|local] check option] create 创建新视图,replace替换已有视图; algorithm选择的算法; viewName视图名称 ;columnList属性列;select statement选择语句; [with [cascaded|local] check option]视图更新时保证在视图的权限范围内; l undefined自动选择算法| l merge将使用的视图语句和视图定义结合起来,使得视图定义的某一部分取代语句对应的部分| l templateble将视图结果存入临时表,然后用临时表来执行语句; Ø cascaded更新视图时需要满足所有相关视图和表的条件;(满足所有条件) Ø local表示可更新视图时满足该视图本身定义的条件即可;(满足自身条件) 要求具有create view权限和select权限,如果replace要求drop视图权限; 视图属于数据库,默认在当前数据库创建; 要在指定数据库创建视图,需要使用完全限定名daName.viewName; create view myView as select quantity,price,quantity*price from t; create view myView(quantity,price,total) as select quantity,price,quantity*price from t; create view class(id,name,class) as select student.id,student.name,stuInfo.class from student,stuInfo where student.id=stuInfo.id; |
查看 | describe viewName; show table status like’viewName’;show table status like ‘viewT’\g; show create view viewName;show create view myView; select*from information_schema.views; |
修改 | create or replace view myView as select*from t; 可以用create or replace修改,语法同创建; alter view myView as select quantity from t; |
更新 | update view myView set quantity=5; insert into t values(3,5);更新表,视图会跟随更新; delete from myView where price=5; 视图中的删除是通过删除基本表中的数据实现的; |
删除 | drop view [if exists]viewName[,...] [restrict|cascade] 可以同时删除多个视图,逗号隔开;必须要有drop权限; drop view if exists myView; |
创建 | create procedure create function ■■■存储过程 create procedure pName([pParameter])[characteristics...]routineBody; create procedure 过程名([参数])[特性...]过程体; 参数:[in|out|inout]pParameter输入、输出、可入可出 特性:characteristics [table] |
①语言特性language sql ②结果是否确定[not]deterministic ③使用SQL语句的限制:{constrains sql|no sql|reads sql data|modifies sql data} l constrains sql表明子程序包含SQL语句,但是不包含读写数据的语句;(默认) l no sql表明子程序不包含SQL语句; l reads sql data子程序包含读写数据的语句; l modidies sql data包含读写数据的语句; ④指明谁拥有权限来执行sql security{definer|invoker} l definer表示只有定义者才能执行;(默认) l invoker表示拥有权限的调用者可以执行; ⑤comment ‘string’注释信息,用来描述存储过程或函数; |
create prodedure avgAge() begin select avg(age) as avgAge from student; end; | delimiter // create procedure proc() begin select*from student; end// delimiter; |
create procedure CountProc (out count int) begin select count(*)into count from student; end; | create funtion myFunc() returns char(50) return (select name from student where age=22); |
declare fruitName char(50); declare fruitPrice decimal(8,2); select name,price into fruitName,fruitPrice from fruits where id=’a1’; |
if exprCondition then statement; [elseif exprCondition then statement2]...; [else statementN]; end if; | if val is null then select ‘val is null’; else select ‘val is not null’; end if; |
case expr when value1 then statement1 [when value2 then statement2]... [else statementN] end case; | case val when 1 then select’val is 1’; when 2 then select’val is 2’; else select ‘val is not 1 or 2’; end case; |
case when expr then statement [when expr2 then statement2]... [else statementN] end case; | case when val is null then select’val is null’; when val0 then select’val is greater than 0’; else select’val is 0’; end case; |
[loopLabel:]loop statement end loop [loopLabel] | declare id int default 0; addLoop:loop set id=id+1; if id>=10 then leave addLoop; end if; end loop addLoop; |
[repeatLabel:]repeat statement until expr end repeat[repeatLabel] | declare id int default 0; repeat set id=id+1; until id>=10 end repeat; |
欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) | Powered by Discuz! X3.4 |