MySQL学习笔记
MySQL学习笔记
解决MYSQL中文乱码问题
一、乱码的原因：
1、 client客户端的编码不是utf8
2、server端的编码不是utf8
3、database数据库的编码不是utf8
4、数据库的表的编码不是utf8
5、表中的列字段编码不是utf8
主要的原因在于前三个偏多。
二、查看数据库的编码方式
mysql>show variables like ‘character%’;
三、解决的办法有2种：
1、对/etc/mysql/my.cnf 配置文件进行修改
character-set-server=utf8
default-character-set=utf8
default-character-set=utf8
2、由mysql -uroot -p命令进入mysql，输入以下设置
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
针对在客户端输入：数据库、表、列字段设置
alter database <数据库名> character set utf8；
alter table <表名> character set utf8;
alter table <表名> modify <字段名> character set utf8;
注意：设置之后记得重启，输入命令：service mysql restart
select user();查看当前用户名
select current_user;查看当前用户名
select user from mysql.user;查看所有用户名
数据结构图
|
更改字符集 |
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
|
|
还原 |
■用<还原 mysql -u user -p [dbName]<fileName.sql; mysql -u root -p myDB<C:\backup\myBackUp.sql; ■用source命令还原 use myDB; source C:\backup\myBackUp.sql; 先选择数据库，然后选择数据文件，数据将还原到当前数据库； ■mysqlhotcopy还原 chown -r mysql.mysql /var/lib/mysql/dbName; cp -r /usr/backup/test usr/local/mysql/data; ■拷贝文件还原：服务器版本相同，MyISAM引擎可用，InooDB引擎不可用； |
|
迁移 |
数据迁移的原因：安装新的服务器；MySQL版本更新；从SqlServer到MySQL； ▶不同主机间的迁移 将www.abc.com主机上的数据库迁移到www.xyz.com主机上； mysqldump -h www.abc.com -u root -p password dbName| mysql -h www.xzy.com -uroot -ppassword; mysqldump 导出的数据直接通过管道符|，传给mysql命令导入到www.xzy.com数据库中； dbName为需要迁移的数据库名称，如果要迁移所有数据库，用--all-databases; ▶不同MySQL版本间的迁移 备份老数据库→卸载旧版本→安装新版本→用新版本恢复数据； ▶不同数据库间的迁移（oracle、sqlserver、MySQL等等） 了解不同数据库的架构，比较差异； 关键字不同，非标准的SQL语句，需要进行映射处理； |
|
导出 |
MySQL数据库支持sql、xml、html文件的导出和导入； select volunmList from tableName where condition into outfile ‘fileName’[options] --options fields termianted by ‘value’字段间分隔字符，可以一个或多个，默认制表符\t； fields [optionally]enclosed by ‘value’包围字符，只能单个字符； fields escaped by‘value’设置转义字符，默认\； lines starting by ‘value’每行开头字符，可以一个或多个，默认无； lines terninated by‘value’每行结束字符，可以一个或多个，默认\n； into oufile 前面是一个查询语句，后面是导出的文件名和选项； select*from test.student into outfile “C:\student.txt”; select * from test.student into outfile “C:\student.txt” fields terminated by ‘,’enclosed by ‘\”’escaped by ‘\’’ lines terninated by ‘\r\n’;
mysqldump -t path -u root -p dbName [tables] [options] --options --fields-termianted-by=value分隔字符，默认\t； --fields-enclose-by=value包围字符； --fields-optionally-enclosed-by=value包围字符，只适用char、varchar等字符数据； --fields-escaped-by=value转义字符，默认\； --lines-terminated-by=value行结束符，默认\n； 指定参数-t才可以导出TXT格式文件；若不指定tables则导出所有表； mysqldump -t c:\ test person -u root -p; mysqldump -t C:\backup test person -u root -p --fields-terminated-by=,--fields-optionally-enclosed-by=\” --fields-escaped-by=? --lines-terminated-by=\r\n; mysql -u root -p --execute =”select语句;” dbName>fileName.txt; mysql -u root -p --execute=”select*from person;” test>C:\personOut.txt; mysql -u root -p --vertical --execute=”select*from person;” test>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导入sql文件（命令行方式）
|
用户管理
|
概述 |
MySQL是一个多用户数据库，可以为不同的用户指定不同的权限； root用户=超级管理员：拥有所有权限； 权限：创建用户、删除用户、修改用户密码； 普通用户：拥有被授予的权限； |
|
|
信息表 |
信息表：user,db,host,tables_priv,columns_priv,procs_priv; ■user表的权限是全局级的，user表有删除权限，则可以删除所有数据库的所有数据；
■db表：对某个数据库的操作权限； ■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;
1 |
||
|
查看 |
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
示例
delimiter//将MySQL结束符设置为//，保证procedure的完整性，完事后，再用delimiter；将结束符设置为默认值分号；delimiter也可以指定其他符号作为结束符； delimiter应该避免使用反斜杠\,因为\是SQL的转义字符；
■■■存储函数 create function funcName([parameter]) returns type [characteristic...]routineBody create function funcName([参数]) returns type [特性]函数体 如果定义的返回值类型与实际返回值类型不同，系统会自动转型； 只能对procedure指定参数类型in/out/inout; 不能对function指定参数类型in/out/inout，function参数默认in; |
||||||||||||
|
变量 |
■■■变量 声明语句：declare varName[,varName2]...dataType[default value]; declare 变量名 dataType[default 默认值]; declear count int default 0; 默认值可以指定为一个常量，也可以指定为一个表达式； 如果不指定默认值，初始值就是null； 赋值语句：set varName=expr[,varName2=expr2]...; declare a,b,c int;set a=1,b=2;set c=a+b; select colName[,...]into varName[,...] tableExpr;
|
||||||||||||
|
异常 |
■■■异常条件：将一个名字和一个错误条件相关联 意义：保证过程/函数在遇到警告或错误时能够继续运行，避免程序异常停止； declare conditionName condition for [conditionType] l [conditionType]:sqlstate[value]sqlstateValue|mysqlErrorCode l conditionName 条件名称conditionType条件类型 l sqlstateValue错误代码-长度为5的字符串 l mysqlErrorCode错误代码-数值类型 declare command_not_allowed condition for sqlstate’42000’; declare command_not_allowed condition for 1148; ■■■定义异常处理程序： declare handlerType handler for conditionValue[,...] spStatement handlerType:continue|exit|undo conditionValue:sqlstate[value]sqlstateValue |conditionName|sqlWarning|notFound|sqlException|mySqlErrorCode handlerType错误处理方式，参数可取3个值continue|exit|undo l continue表示不处理错误，继续执行| l exit表示遇到错误马上推出| l undo表示遇到错误后，撤回之前的操作，MySQL中断暂时不支持这样的操作； conditionValue表示错误类型，可以取值如下： l sqlstate[value]sqlstateValue包含5个字符的错误字符串； l conditionName表示declare condition定义的错误条件名称； l sqlWarning匹配所有以01开头的sqlstate错误代码； l notFound匹配所有以02开头的sqlstate错误代码； l sqlException匹配所有未被sqlWarning和notFound匹配的错误代码； l mySqlErrorCode匹配数值型错误代码； ① 捕获字符串错误码sqlStateValue declare continue handler for sqlstate’42s02’set@info=’no_such_table’; ② 捕获数字错误码mysqlErrorCode declare continue handler for 1146 set@info’no_such_table’; ③ 捕获自定义错误码 declare no_such_table conditon for 1146; delare continu handler for no_such_table set@info=’no_such_table’; ④ 捕获警告错误码sqlWarning declare exit handler for sqlwarning set@info=’error’; ⑤ 捕获未找到错误码notFound declare exit handler for not found set@info=’no_such_table’; ⑥ 捕获数据库异常错误码sqlException declare exit handler for sqlexception set @info=’error’; |
||||||||||||
|
光标 |
光标：用来逐条读取大量数据 顺序：声明条件和变量→声明光标→声明处理程序 顺序：声明declare→打开open→使用fetch→关闭close declare cursorName cursor for selectStatement declare cursorFruit sursor for select name,price from fruits; open cursorName;open cursorFruit; fetch cursorName into varName[,varName2...] fetch cursorFruit into name ,price; close cursorName;close cursorFruit;
|
||||||||||||
|
流程 |
流程控制：条件转移语句 if,case,loop,while,leave,iterate,repeat,,, 每个流程控制，可以包含单个语句，可以包含复合语句（begin end），可以嵌套；
leave用来退出任何流程控制构造；leave label; iterate用来跳转到开头处；可以放在loop,while,repeate内，表示再次循环；iterate label;
until后面是循环结束的条件，如果条件为真，则结束循环，条件为假，继续循环；
while后面是进行循环的条件，条件为真，继续循环，条件为假，结束循环； |
||||||||||||
|
调用 |
调用存储过程 call pName([parameter[,...]]); call CountProc(101,@num); select CountFunc(101); |
||||||||||||
|
查看 |
查看存储过程和函数状态 show {procedure|function}statuss [like’pattern’] show procedure status like ‘c%’\g; show create {procedure|function}name; show create function test.CountFunc \g; select*from information_schema.routines where routine_name=’sp_name’; select*from information_schema.routines where routine_name=’countFunc’ and routine_type=’function’\g’; |
||||||||||||
|
修改 |
修改存储过程和函数 alter {procedure|function} name [characteristic...] alter procedure CountProc modifies sql data sql security invoker; alter function countFunc reads sql data comment ‘find name’; |
||||||||||||
|
删除 |
删除存储过程和函数 drop {procedure|function} [if exists] name; drop prodedure countProc; drop function countFunc; |
索引index、key
|
创建索引 |
一、创建表的同时创建索引 create table name [colName dataType] [unique|fulltext|spatial] [index|key] [indexName](colName[length]) [asc|desc] create table teacher (id int(11),name varchar(25),salary int(11),index(id));普通索引 create table t1(id int(11),name varchar(25),unique index myindex(id));唯一索引 create table t2(id int(11) not null,name varchar(30) not null, index singleIndex(name(20)));单列索引，只能在string类型上创建，并且索引长度不得大于字段长度； create table t3(id int(11) not null,name varchar(30)not null,index(id,name(30))); 多列索引；索引长度不能超过string数据长度； 全文索引，只支持MyISAM引擎，创建表时，必须显示指明引擎类型，否则报错； create table t4(id int not null,name char(30) not null,fulltext index(name));× ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes create table t4(id int not null,name char(30) not null,fulltext index(name))engine=myisam;√ create table t5(g geometry not null ,spatial index myindex (g))engine=myisam ; |
|
二、在已经存在的表上创建索引 ■■使用alter table创建 alter table name add [unique|fulltext|spatial] [index|key] [indexName](colName[length],...)[asc|desc] alter table student add index myIndex(name(10)); 索引的查看方式： l show index from tableName;查看表中索引的详细信息；（这里不能用show key） l show create table tableName;查看创建表信息，包含创建索引信息； l explain select *from student where name='cat';可以查看该筛选所用的索引； alter table student add unique key(age); 创建唯一索引，要保证该列的数据唯一性，否则报错； ERROR 1062 (23000): Duplicate entry '22' for key 'age' 关键字key和index是等价的； alter table student add key multiKey(name,age); alter table t1 engine=myisam;先修改引擎为MyISAM，然后再添加fulltext索引； alter table t1 add fulltext key(name); ■■用create index创建 create [unique|fulltext|spatial] index indexName on tableName(colName[lenght],...) [asc|desc] create index idIndex on t2(id);普通索引 create unique index nameIndex on t2(name);唯一索引 create index multiIndex on t2(id,name);多列索引 alter table t3 engine=myisam;修改引擎后，再创建全文索引； create index fullIndex on t3(name); |
|
|
删除索引 |
■用alter table删除索引 alter table tableName drop index indexName;编辑表 删除索引； show create table tableName;首先查看包含的索引名称，然后再针对性删除； alter table t3 drop index fullIndex; ■用drop index删除索引 drop index indexName on tableName;删除某个表上的某个索引； drop index name on t3; 在删除表中的列时，该列也会从索引中删除； 如果索引中的列全部被删除，那么该索引也会被自动删除； |
查询指令
|
查询 |
查询是数据库最重要的功能；可以查询数据、筛选数据、指定显示格式； 几何函数查询、连接查询、子查询、合并查询、取别名、正则表达式；
select name from stu; select name,age from stu order by age; 单表查询：所有字段、指定字段、指定记录、空值、多条件查询、结果排序； 指定字段：列举所需查询的字段即可，*表示所有字段； select*from stu where age>10; where:>、<、>=、<=、=、<>、!=、between and、in、like、is null、and、or、distinct select*from student where age between 18 and 28; between a and b等价于x>=a&&x<=b,双闭区间，如果a>b,则返回空； select*from student where age in(55,66,77,88); in(a,b,c)表示是（）中的任意一个数据，等价于x=a||x=b||x=c； like通配符%表示任意个任意字符，字符个数0-n个，字符任意； ’b%’’%b’’%b%’’a%b’分别表示b开头，b结尾，包含b，a开头且b结尾； 下划线通配符_，表示任意一个字符，字符个数为1，字符任意； 多个逻辑条件查询：逻辑与and逻辑或or； select*from student where name like 'a%'; select*from student where name like '%o%'; select*from student where name like '%o%y'; select*from student where name like '___'; select*from student where name like '____y'; select*from student where name is null; select*from student where name like 'm%' and age>10; select*from student where name like'%y' or age<10; select distinct field from tableName; select distinct age from student; 排序： order by field;按照某个字段进行排序，默认升序；降序排序用desc关键字； select*from student where name like '%o%' order by age; order by filed1,field2;多列排序，首先第一列必须有相同的值，否则不会按照第二例进行排序； select*from student order by name ,age ; select *from student order by age desc; select*from student order by name desc,age desc; 分组：■■■ group by通常和集合函数一起使用，min,max,avg,count,sum,, select field count(*) as total from table group by field; select name count(*) as total from student group by name; select field1 group_concat(field2) as newField from table group by field1; select name ,group_concat(age) as ages from student group by name; select fiedl1, group_concat(field2) as newField from table group by field1 having count(field2)>1;分组→统计→筛选 select name,group_concat(age) as ages from student group by name having count(age)>1;
where→group by→having with rollup表示统计数据； select name,count(*) as total from student group by name with rollup; select*from table gruop by field1,field2,field3; 多字段分组，先按照第一个字段分组（相同项合并），然后在第一个字段相同的记录中，按照第二个字段分组，以此类推； select field from table limit count; 限制查询结果数量：limit[,位置偏移量]行数 select*from student order by name limit 4; select*from student limit 3,4; 从偏移量3开始共显示4行；偏移量3表示第四行，第一行的偏移量为0； 集合函数/聚合函数查询count,sum,avg,max,min,,, count(*)计算表中的总行数，不管是否为空； count(字段名)计算某字段的行数，为空不计入； select count(*) as rowCount from student; select count(name) as nameCount from student; select age,count(name) as nameCount from student group by age; select sum(age) as sumAge from student where age<10; select avg(age) as avgAge from student; select min(age)from student; select max(name)from student;字符串按照字码顺序进行排序； 连接查询■ 连接是关系型数据库的主要特点； 连接查询包括：内连接、外连接、复核连接； 通过连接运算符实现多个表的查询； 内连接查询inner join select suplliers.id supplyer.name fruits.name fruits.price from suppliers,fruits where suplliers.id=fruits.id; select suplliers.id supplyer.name fruits.name fruits.price from suppliers inner join fruits on suplliers.id=fruits.id; 对于两个表中都有的字段，需要使用完全限定名，用.连接符号，即表名.字段名； 从供应商表选择两个列，id和name，从水果表选择两个列，name和price，共同组成一个新的表；条件是二者的id相同； 自连接查询（自己连接自己） select s1.name,s2.age from student as s1, student as s2 where s1.age=s2.age and s1.age<10; 如果在一个连接查询中，涉及到的两个表是同一个表，这种查询称为自连接查询； 自连接是一种特殊的内连接，两个相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表； 为了避免同一个表名称相同导致的二义性，这里对两个表分别重命名，也叫别名； 外连接分为 左外连接，右外连接； left join 左连接：返回包括左表中所有记录和右表中连接字段相等的记录； right join 右连接：返回包括右表中的所有记录和左表中连接字段相等的记录； select customer.id,orders.num from customers left outer join orders on customers.id=orders.id; select customer.id,orders.num from customer right outer join orders on customer.id=orders.id; select customers.id,orders.num from customers inner join orders on customers.id=orders.id and customers.id=10001; 子查询也叫嵌套查询，是一个查询嵌套在另一个查询内； 先执行子查询，将子查询的结果作为外层查询的入参，进行第二层查询过滤； 子查询常用操作符：any,some,all,in,exists,<,<=,>,>=,=,!=,in,,,, 子查询可以添加到select,upodate,delete语句中，并且可以多层嵌套； select num from tb1 where num>any(select num from tb2); select num from tb1 where num>some(select num from tb2); any等价于some，表示大于集合中的任意一个，就算是满足条件；相当于逻辑或；也就是大于集合中的最小值即可； select num from tb1 where num >all(select num from tb2); all表示大于集合中的所有，相当于逻辑与；也就是大于集合中的最大值； select num from tb1 where exists(select num from tb2 where num<0); 子查询是否存在，也就是子查询是否为空，如果为空，则不再进行外层查询，如果不为空，也就是子查询结果存在，就进行外层查询； select num from tb1 where num in(select num from tb2); in表示是否属于集合的元素，也就是集合中是否有一个元素与该值相等；相当于交集； select num from tb1 where num not in(select*from tb2); non in表示不在集合中，相当于差集； 合并查询结果 union[all]可以将两个查询结果合并为一个结果集，前提是两个查询的列数和数据类型必须相同； select num from tb1 where num>5 union all select num from tb2 where num>5; select num from tb1 union select age from stu; select num from tb1 union all select num from tb2; select num from tb1 union select num from tb2; union all包含重复行；union会自动删除重复行；union all效率更高； 表和字段的别名 表名 [as] 表别名；字段名 [as] 字段别名；其中as可以省略； select*from student as s where s.age>10;使用as select*from student s where s.age>10;省略as 在为表取别名时，不能与已存在的表名冲突； select s.name n,s.age a from student s where s.age<10; 字段别名主要是为了显示的需要； 正则表达式查询regexp
select*from student where name regexp('^c');匹配cat Charles Clark select*from student where name regexp('y$');匹配mokey donkey Marry Lily select*from student where name regexp('o.*[stuvwxyz]');mokey donkey Colorful select*from student where name regexp('ap'); apple select*from student where name regexp('co|ca');cat Colorful |
基本指令
|
MySQL指令 |
举例 |
||||||||||||||||
|
数据库 |
创建：create database name;create database mydatabase; 删除：drop database name;drop database mydatabase; 查看定义：show create database name;show create database mydatabase; 显示：show databases;显示数据库清单（所有数据库）
|
||||||||||||||||
|
引擎 |
显示所有存储引擎：show engines; 显示默认引擎：show variables like 'storage_engine'; MySQL存储引擎：InnoDB,MyISAM,Memory,Merge,Archive,Fdedrated,CSV,BLACKHOLE； |
||||||||||||||||
|
数据表 |
选择数据库→在该数据库内创建数据表； use database mydatabase;create table mytable;
显示所有表：show tables; 查看数据表结构：describe tablename;describe student;desc student; 可以查看：字段名称、数据类型、约束信息； 查看创建表信息：show create table tablename;show create table mytable; 可以查看：创建表的语句、存储引擎、字符编码；
修改表名：alter table 旧名称rename [to] 新名称; alter table student rename to stu;alter table stu rename student; 修改数据类型：alter table student modify name varchar(50); 修改字段名称：必须指定数据类型，不可省略； alter table 表名change 原字段名 新字段名 数据类型； alter table tablename change name fullname varchar(50); change可以同时修改字段名称和数据类型，若只想修改名称，则把数据类型写成原先的类型；若只想修改数据类型，则把名称写成原名称，此时change等效于modify；
添加字段：alter table 表名 add 字段名 数据类型 [约束条件][first|after 已有字段名]; alter table student add sex varchar(10) default ‘male’ after name; alter table student add id int(11) not null first; alter table dogs add sex varchar(11) default 'male' after name; 添加位置有2中选择：开头或者某个字段后面； 删除字段：alter table student drop age; 修改位置：alter table student modify age int(11) after name; 修改存储引擎：alter table student engine=myISAM;(引擎名称不区分大小写) MySQL存储引擎：InnoDB,MyISAM,Memory,Merge,Archive,Fdedrated,CSV,BLACKHOLE； 删除无关联数据表：删除没有关联的一个或者多个数据表； drop table [if exists] name1 [,name2,name3...]; drop table if exists stu; 删除关联父表：先删除子表再删父表；先取消子表的外键约束，再删除父表；
|
||||||||||||||||
|
主键和外键 |
主键：列数据必须唯一且非空；主键与记录一一对应，主键是记录的唯一标识； 主键分为单字段主键和多字段联合主键； [constraint 约束名] primary key(字段名); primary key (id);id int(11)primary key;primary key(id,name); 外键： 用来在两个表的数据之间建立链接； l 外键可以使一列或者多列，一个表可以有一个或多个外键； l 外键可空，若不为空时，必须是另一个表中主键的某个值； l 外键是一个字段，可以不是本表的主键，但一定是另外一个表的主键； l 外键主要用来保证数据引用的完整性，定义外键后，关联表中的行不得删除； 主表（父表）：关联字段中主键所在的表称为主表； 从表（子表）：关联字段中外键所在的表称为从表； [constraint 外键名]foreign key 字段名 references主表名 主键列; [constraint 外键名]foreign key 字段名[,字段名2,...] references主表名 主键列[,主键列2,...]; constraint fk_name foreign key(students)reference class(id) 关联：指的是关系型数据库中，相关表之间的联系。 子表的外键必须关联父表的主键，并且关联字段的数据类型必须匹配； 建表以后添加外键： 创建表的同时添加外键 -> errorType varchar(25), -> constraint fk_errorType foreign key(errorType) references errorType(name) 表创建好之后再添加外键： alter table res2 add foreign key(errorType) references errorType(name); 删除外键约束：alter talbe name drop foreign key fkname; alter table res3 drop foreign key fk_errorType; |
||||||||||||||||
|
约束 |
定义列的同时指定约束，列定义完后为字段指定约束； 主键约束：id int(11)primary key;constraint pk primary key(id); 非空约束：name varchar(25)not null;constraint nnull not null(name); 唯一约束：name varchar(25)unique;constraint uni unique(name); 默认值约束：departId int(11) default(1111); 自增约束：id int(11) primary key auto_increment; 默认初始值1，每增加一条记录，字段值加1； 一个表只能有一个字段使用自增约束； 自增约束的字段必须是主键或主键的一部分； |
||||||||||||||||
|
数据 |
■插入数据： insert into tablename (field1,field2,field3)values(v1,v2,v3); insert into student (name,age) value('Tome',33); insert into student value('Joe',88); insert into student values('Trump',77); 关键词，用value或者value均可； 两种插入方式：指定字段和不指定字段； 如果不指定字段名称，则插入数据的个数和顺序，必须和所有的字段一一对应； insert into tablename (columnList)values(valueList1),(valueList2)...; insert into student(name,age)values('dog',3),('cat',4); insert into student values('mokey',11),('donkey',22); 将查询结果插入到表中 insert into tableName1(columnList)select (columnList)from tableName2 where condition; insert into stu(name,age)select name,age from student where age<20; ■修改数据： update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3 where 条件； update stu set name='monkey' where name='mokey'; update stu set name='dongkey',age=22 where name='monkey'; 如果忽略where子句，则更新表中的所有数据； ■删除数据： delete from tableName [where condition]; 如果不指定where条件，则删除表中的所有数据； delete from student where age=33; delete from student where age between 10 and 90; 修改和删除，如果不指定where条件，则会对所有的行进行操作； 一般应先用select语句进行查看，再进行修改和删除操作； |
|
内连接 |
关键字：left join on / left outer join on 语句：select * from a_table a left join b_table b on a.a_id = b.b_id; 组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分 |
|
|
左连接
左外连接
|
关键字：left join on / left outer join on 语句：select * from a_table a left join b_table b on a.a_id = b.b_id; left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。 |
|
|
右连接
右外连接
|
关键字：right join on / right outer join on 语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id; right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。 与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
|
|
|
|
|
- MySQL语句中，字符串应当用单引号表示，而不是双引号，例如’Tom’；
- 表中字段区分大小写，name和Name是不同的字段；
- 命令不区分大小写，DROP和drop是相同的命令；
- 表名称不区分大消息，Student和student被认为是同名的；
- 命令行后面必须跟分号；
通过MySQL命令行修改:（编码可选）
mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_database=utf8;
mysql> set character_set_results=utf8;
mysql> set character_set_server=utf8;
mysql> set character_set_system=utf8;
mysql> set collation_connection=utf8;
mysql> set collation_database=utf8;
mysql> set collation_server=utf8;
