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

还原语句：mysqldump -h192.168.32. -uwjf -p123456 wjf【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 && #如果向licenseinfo 表中插入记录说明该学员已经结业，那么studentinfo 表中的scondition只应该更新为“结业”。代码如下

CREATE TRIGGER license_stu AFTER INSERT ON licenseinfo FOR EACH ROW BEGIN UPDATE studentinfo SET leave_time=new.receive_time,scondition='结业' WHERE sno=new.sno;

END &&

DELIMITER ;

附加：new.sno:表示新插入的记录；OLD.sno:表示新删除的记录。 【8】存储过程 例子1:：

第一步创建存储过程：DELIMITER &&

CREATE PROCEDURE matches_index(IN p_PLAYERNO INTEGER) BEGIN DELETE FROM matches WHERE PLAYERNO=p_PLAYERNO; END &&

DELIMITER ;

第二步调用存储过程：CALL matches_index(8);

第三步查询表看结果是否正确：SELECT * FROM matches;

*******************************************************

例子2：计算两个日期相差的年数的存储过程代码如下1980-1990年相差9年： DELIMITER &&

CREATE PROCEDURE AGE(IN start_date DATE,IN end_date DATE,OUT years INTEGER) BEGIN DECLARE prev_date,next_date DATE; SET years=0; SET prev_date=start_date; SET next_date=start_date+INTERVAL 1 YEAR; WHILE next_dateDELIMITER ;

CALL AGE('1980-01-01','1990-01-01',@years); SELECT @years;

************************************************************* 例子3：结果是p1=4,p2=1 DELIMITER &&

CREATE PROCEDURE small_exit(OUT p1 INTEGER,OUT p2 INTEGER) BEGIN SET p1=1; SET p2=1; block1:BEGIN LEAVE block1;//leave语句后面的set语句没有执行,lock1语句结束后声明的那条set语句实际执行了。 SET p2=3; END;

SET p1=4;

END &&

DELIMITER ;

CALL small_exit(@p1,@p2); SELECT @p1,@p2;

************************************************************ 例子4：计算某一个球员的所有罚款的总额select into或set语句 DELIMITER &&

CREATE PROCEDURE amount(IN pn INTEGER,OUT total_amount DECIMAL(7,2)) BEGIN #select sum(amount) into total_amount from wjf.penalties where pn=PLAYERNO;或 SET total_amount=(SELECT SUM(amount) FROM wjf.penalties WHERE pn=PLAYERNO); END &&

DELIMITER ;

CALL amount(104,@total_amount); SELECT @total_amount; 【9】存储函数 DELIMITER &&

CREATE FUNCTION food_price_count1(price_info1 FLOAT,price_info2 FLOAT ) RETURNS INT READS SQL DATA BEGIN RETURN (SELECT COUNT(*) FROM food WHERE price>price_info1 AND price调用存储函数

SELECT food_price_count1(2,18);

删除存储函数

DROP FUNCTION food_price_count1;

 又例如2： -- Mysql 存储过程

       

/*

set @result = 0;

create procedure login( -- 定义参数，有in、out、inout三种类型 in user varchar(10), in pass varchar(10), out result int ) begin

 declare passd varchar(10);-- declare 声明临时变量、类型，然后用set 进行赋值，declare 临时变量只能放在begin end 区域中，而其作用域也只是在begin end 中, 而 set @ 定义的变量是全局变量

 select password into passd from login where username=user;

 if passd like pass then -- If 语句，后面要加上 End IF,就像是case 后也要加 End Case 一样

 select 'Login Success' as Massage;  set result = 1;  else

 select 'Login Failed' as Message;  set result =0;  end if;  end;  */ 

 -- 调用存储过程 call login('root','root',@result);  -- 删除存储过程 drop procedure login

【10】优化：

1. (其实实践的效果差不多)用count(*)统计所有字段的一种优化方案是只计算id字段，速

度比count（*）快，SQL语句可写成：select count(emp_id) from emp_infor; 2. SELECT 'all',COUNt(*) FROM wjf.committee_members WHERE

BEGIN_DATE>'1992-01-01' UNION SELECT 'like',COUNT(*) FROM wjf.committee_members WHERE BEGIN_DATE>'1992-01-01' AND POSITION='海' UNION SELECT 'not like',COUNT(*) FROM wjf.committee_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; 之后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 i<5 DO

INSERT INTO stud(age,NAME)VALUES(i,'ff'); SET i=i+1; END WHILE; END$$

DELIMITER ; CALL ww1();

******************************************************** DELIMITER $$ USE `test`$$

DROP PROCEDURE IF EXISTS `ww2`$$

CREATE DEFINER=`wjf`@`%` PROCEDURE `ww2`() BEGIN

DECLARE i INT; SET i=1; REPEAT

INSERT INTO stud(age,NAME)VALUES(i,'ff'); SET i=i+1;

UNTIL i>5 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表里边的数据记录有1。代码如下：

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？匹配后面的零个或1个字符a,abc|def是匹配前面的abc或者匹配后面的def,(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 like'%character%'; 14、Read_lock:叫共享锁，可以同时读不可任何的写。Write_lock:叫排他锁或者独占 锁，即不允许同时读同时写。

FLUSH TABLES WITH READ LOCK;//一般用在数据库联机备份时候。

解锁：unlock tables; 15、修改编码格式：

16、新服务平台199服务器重启步骤：这些步骤都在安装服务器的bin目录下进行

一、查看进程： ps -ef |grep tomcat 二、杀掉进程： kill -9 进程号

三、等待3分钟后再次查看进程： ps -ef |grep tomcat 四、重新启动： ./startup.sh

17、导入文本文件到数据库中：load data local infile \"/me/daoru\"into table w;（前提需要 在Linux中建一个文本文件）。

18、MySQL服务器最大连接数：show variables like 'max_connections';

服务器响应的最大连接数：show global status like 'Max_used_connections';

19、mysql :一个基于命令行的MySQL客户端程序

mysql_install_db :用于以缺省权限创建MySQL权限表,该程序通常仅在系统上第 一次安装MySQL时执行一次

mysqladmin :用于执行数据库的管理操作,如创建或删除数据库,加载授权表和停 止MySQL服务等.也可以用于查看MySQL版本,进程和状态信息

myisamchk:用于描述,检查,优化和修复MySQL中和各个表,并可以显示表的相关信 息 mysqlshow:用于显示数据库,表,列和索引等信息 。

mysqlaccess:一个脚本,用于检查对主机,用户和数据库组合的存取权限 mysqlbug:MySQL错误报告脚本,用于向MySQL邮件列表中添加错误报告 mysqldump:用于将MySQL数据库中的数据导出一个文本文件.

mysqlmport:提供一个到SQL语句LOAD DATA INFILE的命令行接口,通过LOAD DATA INFILE命令,可以将文本文件导入到数据库中. 20、一个表最多可有16个索引,一个索引可以由最多15个列组成。 21、列出文件内容：cat /me/daoru 22、创建文件夹：mkdir ww; 23、删除文件夹：rm -ri ww; 24、查询当前

CREATE TABLE `xinxi` (

`id` int(11) NOT NULL AUTO_INCREMENT, `riqi` varchar(11) NOT NULL, `haoma` int(20) NOT NULL, PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8