mysql常见查询语句和学习笔记

实践成功的例子进入到 C 盘的 mysql server 5 5 下执行以下语句 1 备份和还原某个表 备份语句 mysqldump h192 168 32 89 uwjf p wjf teams F wjd sql 注意 在 wjd sql 后面 不要加分号 或者 mysqldump h192 168 32 89 uwjf p123456 wjf teams F sda sql 后面不加分号的 喔 还原语句 mysqldump h192 168 32 89 uwjf p123456 wjf F sda sql 后面不加分号的喔 1 cmd 命令进入到 mysql 指令中 然后登录数据库 mysql h192 168 32 89 uwjf p123456 回车 ok 2 数据库 SHOW DATABASES use 数据库名 CREATE DATABASE school DROP DATABASE school 3 存储引擎 查看 mysql 数据库支持的存储引擎 SHOW ENGINES 查看 mysql 数据库支持的存储引擎 SHOW VARIABLES LIKE have 查看你 mysql 默认的存储引擎 SHOW VARIABLES LIKE storage engine 查看某张表使用的存储引擎 SHOW CREATE TABLE 表名 修改存储引擎 ALTER TABLE teacherInfo ENGINE MYISAM 4 字段 修改字段名用 change 修改字段其它用 modify 表 外键 修改字段类型 ALTER TABLE teams MODIFY division CHAR 6 修改字段位置 ALTER TABLE players MODIFY TOWN VARCHAR 30 AFTER STREET 增加字段并指定位置 ALTER TABLE teams ADD long time DATE AFTER division 修改字段名字和类型 ALTER TABLE 表名 CHANGE 旧属性名 新属性名 数据类型 新或旧的 ALTER TABLE teams CHANGE long time time long DATETIME 删除字段 ALTER TABLE teacher DROP address 清除数据 不删除数据结构的语句 truncate table 表名 删除表名 DROP TABLE department 修改表名 ALTER TABLE teacher RENAME teacherInfo 修改表名 CREATE TABLE grade id INT 10 NOT NULL UNIQUE PRIMARY KEY AUTO INCREMENT course VARCHAR 10 NOT NULL s num INT 10 NOT NULL grade VARCHAR 4 CONSTRAINT grade fk FOREIGN KEY s num 创建表并指定外键 constraint 外 键名 foreign key 指定 为外键的字段名 reference 父表 外键对应父表里的对应字段 REFERENCES student num 删除表的外键 ALTER TABLE worker DROP FOREIGN KEY worker fk 增加主键 CREATE TABLE Persons Id P int NOT NULL LastName varchar 255 NOT NULL FirstName varchar 255 Address varchar 255 City varchar 255 CONSTRAINT uc PersonID PRIMARY KEY Id P LastName 增加主键 5 索引 普通索引 是最基本的索引 没有任何限制 唯一索引 与普通索引类似 不同在于索引列的值必须唯一但是允许有空值 主键索引 是一种特殊的唯一索引 不允许有空值 一般是在建表的时候同时创建主键索 引 创建索引 create index index name on table name 字段属性 字段长度 CREATE INDEX index name ON workInfo name 10 创建多字段索引 ALTER TABLE workInfo ADD INDEX index t type address 删除索引 注意 on 表名一定要写不然语法出错 DROP INDEX index id ON workInfo 6 视图 创建视图 CREATE ALGORITHM UNDEFINED VIEW college view student num 视图字段属性 1 student name 视图字段属性 2 student age 视 图字段属性 3 department 视图字段属性 4 AS SELECT 原表属性 1number name age major FROM college WITH LOCAL CHECK OPTION 查看视图的详细结构 SHOW CREATE VIEW college view 向视图中插入记录 INSERT INTO college view VALUES 0901 张三 20 外语 修改视图也可以用 CREATE OR REPLACE ALGORITHM UNDEFINED VIEW college view student num student name student age department AS SELECT number name age major FROM college WHERE major 计算机 WITH LOCAL CHECK OPTION 删除视图 DROP VIEW college view 7 触发器 创建触发器 DELIMITER END 附加 new sno 表示新插入的记录 OLD sno 表示新删除的记录 8 存储过程 例子 1 第一步创建存储过程 DELIMITER END 第二步调用存储过程 CALL matches index 8 第三步查询表看结果是否正确 SELECT FROM matches 例子 2 计算两个日期相差的年数的存储过程代码如下 1980 1990 年相差 9 年 DELIMITER SET years 0 SET prev date start date SET next date start date INTERVAL 1 YEAR WHILE next dateprice info1 AND price 1992 01 01 UNION SELECT like COUNT FROM mittee members WHERE BEGIN DATE 1992 01 01 AND POSITION 海 UNION SELECT not like COUNT FROM mittee members WHERE BEGIN DATE 1992 01 01 AND POSITION 海 注释 Mysql 中的 like 不能带有中文 字符 不兼容 但是可以 用等号或不等号代替 Mysql 中的 in 在 mysql 中也不兼容 实实 践表明有时候可以有践表明有时候可以有 时候不兼容时候不兼容 11 OR in 和 union all 查询效率到底哪个快 12 查看是否自动提交 mysql 默认自动提交即 autocommit 1 查看语句 SHOW VARIABLES LIKE autocommit 或者 SELECT autocommit 注意 有一点要注意 连接 mysql 用户的权限不能大于启动 mysql 的用户的权限 不然 init connect SET autocommit 0 根本不会启作用 也不会报任何错误 13 var 是用户自定义变量 user defined vars var 是系统变量 system vars var 又分成两种 一个是 seesion 的 local 一个是 server 的 global declare 临时变量只能放在 begin end 区域中 而其作用域也只是在 begin end 中 而 set 定义的变量是全局变量 14 为什么 wjf 帐号不在 user 里却依然能登录进是为什么 15 PHP 开发者常犯的几个开发者常犯的几个 MySQL 错误得出的结论 错误得出的结论 1 要使用 innoDB 而不是 MyISAM Mysql 默认数据库存储引擎是 MyISAM 但是 MyISAM 不支持外键约束也不支持事务 myisam 会在在添加或者更新数据的时候将 整个表锁住 扩展性不强 2 应该使用 UTF 8 3 一定要确保自动备份数据或者保存副本 4 应该适量使用索引 避免索引不足或者过度索引 5 要使用 在表中返回所有的字段 这会非常的慢 你只需要取出你需要的数据字段 6 Myisam 表级锁 不会随着 SQL 线程的增加而降多少 Innodb 行级锁 随着 SQL 线程的大幅度剧烈变化 16 日志 1 mySQL 默认是不开启那些日志的 如 二进制日志 错误日志 慢日志 查询日志等 2 查看二进制日志文件存放的目录 show variables like datadir 3 当前的二进制日志文件 show master status 17 问题 创建存储函数 DELIMITER CREATE FUNCTION myfunction in string VARCHAR 255 in find str VARCHAR 20 in repl str VARCHAR 20 RETURNS VARCHAR 255 BEGIN DECLARE l new string VARCHAR 255 DECLARE l find pos INT SET l find pos INSTR in string in find string IF l find pos 0 THEN SET l new string INSERT in string l find pos LENGTH in find str in repl str ELSE SET l new string in string END IF RETURN l new string END DELIMITER 时出错信息 ERROR 1418 HY000 This function has none of DETERMINISTIC NO SQL or READS SQL DATA in its declaration and binary logging is enabled you might want to use the less safe log bin trust function creators variable 原因 如果开启了二进制日志 考虑需要 复制 备份等 mysql 从安全角度考虑 比如存储函数里存在一些不特定的函数 会导致 主从的数据可能不一致 存储过程和函数需要遵守一些限制 参数如 1 DETERMINISTIC 不确定的 2 NO SQL 没有 SQl 语句 当然也不会修改数据 3 READS SQL DATA 只是读取数据 当然也不会修改数据 4 MODIFIES SQL DATA 要修改数据 5 CONTAINS SQL 包含了 SQL 语句 解决方法 set global log bin trust function creators 1 1 之后 show variables like log bin trust function creators 该值为 on 设置为 on 的时候数据库也会存在风险 的哦 18 使 create time 的值默认是系统时间的设置是 变量是 create time 类型是 timestamp default 是 current timestamp 不为空即可 19 循环语句 while do en while repeat until end repeat loop end loop 其中 loop 语句必须遇到 leave 才能停止循环 Mysql 用 行注释 段注释例子 如下 DELIMITER USE test DROP PROCEDURE IF EXISTS ww1 CREATE DEFINER wjf PROCEDURE ww1 BEGIN DECLARE i INT SET i 1 WHILE i5 END REPEAT END DELIMITER CALL ww2 DELIMITER USE test DROP PROCEDURE IF EXISTS ww3 CREATE DEFINER wjf PROCEDURE ww3 BEGIN DECLARE i INT SET i 1 loop1 LOOP INSERT INTO stud age NAME VALUES i ff SET i i 1 IF i 5 THEN LEAVE loop1 END IF END LOOP END DELIMITER CALL ww3 20 mysql 存储过程使用 repeat 来打开游标读取数据 因为 stud 表里边的数据记录有18 条 代码如下 DELIMITER CREATE PROCEDURE ww4 in stud id INT BEGIN DECLARE l id INT DECLARE l name VARCHAR 15 DECLARE l age INT 4 DECLARE l status INT DECLARE ww cursor CURSOR FOR SELECT id NAME age STATUS FROM stud DECLARE CONTINUE HANDLER FOR NOT FOUND SET l status 1 SET l status 0 OPEN ww cursor loop1 REPEAT FETCH ww cursor INTO l id l name l age l status 从 select 的查询结果 中提取存入到 l id l name l age l status 这三个变量中 IF l status 0 THEN SET l age l age 1 END IF SELECT l id l name l age l status UNTIL l status 此处不要分号 不然会出错 END REPEAT loop1 CLOSE ww cursor SET l status 0 END DELIMITER 结果出现一排18个结果 如下图所示 21 Mysql 分组统计问题 数据 xinxi 表如下图 和右边的结果 执行的 sql 语句 SELECT riqi haoma COUNT AS cishu FROM xinxi GROUP BY riqi haoma ORDER BY riqi 22 查询与索引优化分析 可以通过 show 命令查看 MySQL 状态及变量 找到系统的瓶颈 1 Show status 显示状态信息 扩展 show status like XXX 2 Show variables 显示系统变量 扩展 show variables like XXX 3 Show engine innodb status 显示 InnoDB 存储引擎的状态 4 Show processlist 查看当前 SQL 执行 包括执行状态 是否锁表等 5 慢查询日志是否开启 show variables like slow 查询慢查询日志的时间 show variables like long 6 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句 从而知道 MySQL 是如何处理你 的 SQL 语句的 例如 EXPLAIN SELECT num FROM stud WHERE num a123 OR num 123 第四列 type 这列很重要 显示了连接使用了哪种类别 有无使用索引 从最好到最差的连接类型为 const eq reg ref 引用 range indexhe 和 ALL 1 system 这是 const 联接类型的一个特例 表仅有一行满足条件 2 const 表最多有一个匹配行 它将在查询开始时被读取 因为仅有一行 在这行的列值可被优化 器剩余部分认为是常数 const 表很快 因为它们只读取一次 3 eq ref 对于每个来自于前面的表的行组合 从该表中读取一行 这可能是最好的联接类型 除了 const 类型 它用在一个索引的所有部分被联接使用并且索引是 UNIQUE 或 PRIMARY KEY eq ref 可以用于使用 操作符比较的带索引的列 比较值可以为常量或一个使用在该表前 面所读取的表的列的表达式 4 ref 对于每个来自于前面的表的行组合 所有有匹配索引值的行将从这张表中读取 如果联接 只使用键的最左边的前缀 或如果键不是 UNIQUE 或 PRIMARY KEY 换句话说 如果联接不 能基于关键字选择单个行的话 则使用 ref 如果使用的键仅仅匹配少量行 该联接类型 是不错的 ref 可以用于使用 或操作符的带索引的列 5 ref or null 该联接类型如同 ref 但是添加了 MySQL 可以专门搜索包含 NULL 值的行 在解决子查询中 经常使用该联接类型的优化 在下面的例子中 MySQL 可以使用 ref or null 联接来处理 ref tables SELECT FROM ref table WHERE key column expr OR key column IS NULL 6 index merge 该联接类型表示使用了索引合并优化方法 在这种情况下 key 列包含了使用的索引的清 单 key len 包含了使用的索引的最长的关键元素 7 unique subquery 该类型替换了下面形式的 IN 子查询的 ref value IN SELECT primary key FROM single table WHERE some expr unique subquery 是一个索引查找函数 可以完全替换子查询 效率更高 8 index subquery 该联接类型类似于 unique subquery 可以替换 IN 子查询 但只适合下列形式的子查询中 的非唯一索引 value IN SELECT key column FROM single table WHERE some expr 9 range 只检索给定范围的行 使用一个索引来选择行 key 列显示使用了哪个索引 key len 包含 所使用索引的最长关键元素 在该类型中 ref 列为 NULL 当使用 IS NULL BETWEEN 或者 IN 操作符 用常量比较关键 字列时 可以使用 range 10 index 该联接类型与 ALL 相同 除了只有索引树被扫描 这通常比 ALL 快 因为索引文件通常比 数据文件小 当查询只使用作为单索引一部分的列时 MySQL 可以使用该联接类型 11 ALL 对于每个来自于先前的表的行组合 进行完整的表扫描 如果表是第一个没标记 const 的 表 这通常不好 并且通常在它情况下很差 通常可以增加更多的索引而不要使用 ALL 使得行能基于前面的表中的常数值或列值被检索出 7 使用 profiling 分析查询优化 默认 profiling 是关闭的 1 查看状态 show profiling 2 查询被执行的 SQL 语句的时间和 ID show profiles 3 通过 show profiles 查出的 id 号查询对应的那条 SQL 语句执行的详细信息 show profile for query id 号 8 索引及查询优化 1 limit Mysql 中的 top 用 limit 来代替 比如查询前 3 条记录的语句 select from stud limit 0 3 或者 select from stud limit 3 比如查询最 后 3 条记录 select from stud order by id desc limit 3 2 少使用 select 3 使用 join 来代替子查询 在 linux 环境下 1 在 red hat enterprise Linux5 下安装 MySQL client 5 0 22 0 i386 rpm 和 MySQL server 5 0 22 0 i386 rpm 的时候出现 starting mysql 失败 的错误 解决方法 进入 etc selinux 然后把 selinux enforcing 改成 selinux disabled 然 后重启就 ok 以后就可以直接通过 mysql 进入到到数据库咯 2 like 的匹配 代表匹配任意个字符 包括零个字符 严格匹配一个字符 匹配字符 匹配字符 例如 1 mysql select 1 not like 1 1 not like 1 0 1 row in set 0 00 sec 2 mysql select 1 not like 1 1 not like 1 0 1 row in set 0 00 sec 3 mysql select 1 like 1 like 1 1 row in set 0 00 sec 4 mysql select 1 like 1 like 1 1 row in set 0 00 sec 5 mysql select 1 like 1 1 like 1 1 1 row in set 0 00 sec 6 mysql select 1 like 1 1 like 1 0 3 正则表达式 所谓的正则表达是就是指字符串匹配 rlike 和 regexp 是同义词 代表 匹配前面所有的字符串 而 代表匹配后面所有的字符串 匹配任何一个字符 X X 匹配后 面的任意 包括零个 为 X 的字符 X X 匹配后面的任意个字符 X X 匹配后面的零个或 1 个 字符 a abc defabc def 是匹配前面的 abc 或者匹配后面的 def abc abc 含义是匹配零个或多个 abc 例如 4 length str 返回字符串的个数 substring str pos 返回字符串 str 的 pos 位置之后 的字串 substring str pos len 返回字符串 str 的 pos 位置后的 len 个字符的子串 Right str 返回字符串 str 最后的 len 个字符串 Locate substr str 返回字串 substr 在字符串 str 最开始出现的位置 例如 5 null 测试 有两个函数 ifnull 和 nullif 其中 ifnull exp1 exp2 如果 exp1 是 null 则返 回 exp2 的值 否则返回 exp1 Nullif exp1 exp2 如果 exp1 exp2 则返回 null 否则返回 exp1 例如 6 linux 中目录是使用斜杠 而 windows 中是使用反斜杠 7 Linux 几个重要的目录如 数据库目录 var lib mysql 配置文件 usr share mysql mysql server 命令及配置文件 相关命令 usr bin mysqladmin mysqldump 等命令 启动脚本 etc rc d init d 启动脚本文件 mysql 的目录 8 Linux 安装完 mysql 的时候 MySQL 默认没有密码 直接输入 mysql 即可进入 9 修改登录密码 10 自动启动 1 察看 mysql 是否在自动启动列表中 root test1 local sbin chkconfig list 2 把 MySQL 添加到你系统的启动服务组里面去 root test1 local sbin chkconfig add mysql 3 把 MySQL 从启动服务组里面删除 root test1 local sbin chkconfig del mysql 11 停止 usr bin mysqladmin u root p shutdown 要启动回来就用到了启动脚本 12 Linux MySQL 常用操作 1 show databases 2 use 库名 3 show tables desc 表名 4 Create table wjf id int not null primary key auto increment name varchar 20 not null 5 insert into wjf name values www 或者 insert into w values 2 haifeng 2001 6 查看表创建的具体信息 show create table 表名 7 删除记录 delete from w where id 1 8 删除字段 alter table w drop age 9 增加字段 alter table w add num int 20 alter table w add age int after name 10 修改字段名 alter table w change num num xin int 20 其中 num 是旧名 num xin 是新名 11 查看存储引擎 show variables like storage engine 12 更改存储引擎 set storage engine innodb 13 查看编码格式 数据库编码默认是 Latin1 show variables lik