MySQL操作及SQL语句总结

注释符号：

a.-- (注意:--后有一空格)

b.#

c./*……*/

停止mysql：net stop mysql

启动mysql：net start mysql

/*注：在dos命令下*/

停止/启动mysql方法二：services.msc

mysql 改密码：

mysqladmin -u root -p password 新密码

Enter……：旧密码

/*注：在dos命令下 dos 清屏 cls */

mysql 密码破解(不用密码直接登录)：

a.找到mysql/my.ini

b.打开my.ini找到[mysqld]

c.在[mysqld]之后另起一行写下如下代码：skip-grant-tables

进入系统数据库：use mysql

/*注：在mysql中执行,以下查询,删除一样*/

查询用户：select * from user;

删除'%'用户('%'不安全的用户)：delete from user where host='%';

远程登录：mysql -u root -p19921116 -h 192.168.x.x

/*注：在dos命令下,192.168.x.x 代表IP号,请改为自己的*/

给用户授权：

1.把数据库(kyle)全部授权给用户(xiaopeng)：grant all on kyle.* to xiaopeng@'%' identified by '19930108';

2.只把数据库(kyle)的查询功能授权给用户(xiaopeng)：grant select on kyle.* to xiaopeng@'%' identified by '19930108';

/*注：在mysql中执行*/

用户登录：mysql -u xiaopeng -p19930108

/*注：在dos命令下*/

撤销授权：revoke all on kyle.* from xiaopeng@'%';

/*注：在mysql中执行*/

更改提示符(显示当前所在数据库)：prompt mysql (\d)> /*不用加分号结束*/

显示当前时间：prompt \u@\h(\d)\r:\m:\s> /*不用加分号结束*/

显示数据库：show databases;

显示表：show tables;

删除数据库：drop database 数据库名;

删除表：drop table 表名;

查询时所需函数：最大值max(),最小值min(),平均值avg(),求和sum(),总记录count(*)

简单创建数据库：create database 数据库名;

创建数据库(支持各种语言)：create database stu_data default character set utf8 collate utf8_unicode_ci;

/* stu_data -- 数据库名，任意改*/

打开某个数据库：use stu_data;

创建表：

常用字段类型: int 整型 、varchar 变长字符串 、char 定长字符串 、text 文本型 、datetime 日期时间类型（也可以使用 varchar）

获取当前日期时间函数： php 中 date('Y-m-d H:i:s') 、 mysql 中 now()

创建一个学生(student)表：

create table student

(

stu_id int auto_increment primary key,

stu_number int,

stu_name varchar(20),

stu_age int(3),

stu_sex char(8),

stu_tel char(18)

);

向student表中插入数据：

方法一：insert into student (stu_number,stu_name,stu_age,stu_sex,stu_tel) values (2012001,'小彭',20,'男','13716819647');

方法二：insert into student values (NULL,2012001,'小彭',20,'男','13716819647');

方法三：insert into student values (NULL,2012001,'小彭',20,'男','13716819647'),(NULL,2012002,'小凯',19,'男','18823589682');

/*方法三为多条插入*/

查询student表中内容：select * from student;

竖着显示：select * from student\G

创建一个

学生成绩(grade)表：

use stu_data;

create table grade

(

gra_id int auto_increment primary key,

gra_number int,

gra_name varchar(20),

gra_Chinese int(3),

gra_Math int(3),

gra_English int(3),

gra_Physics int(3),

gra_Chemistry int(3)

);

向grade表中插入数据：

方法一：insert into grade (gra_number,gra_name,gra_Chinese,gra_Math,gra_English,gra_Physics,gra_Chemistry) values (2012001,'小彭',80,85,78,86,83);

方法二：insert into grade values (NULL,2012001,'小彭',80,85,78,86,83);

查询grade表中内容：select * from grade;

查看student表结构：

1.describe student;

2.describe student \G /*垂直显示 注：\G 之后不加分号*/

3.desc student;

修改表名：alter table 原表名 rename to 新表名;

向student表中添加字段：alter table student add stu_zhuanye char(20);

修改student表中字段名：alter table student change stu_zhuanye stu_zy varchar(20);

修改student表中字段数据类型：alter table student modify stu_zy char(18);

删除student表中某字段：alter table student drop stu_zy;

更改student表中某数据：update student set stu_name='小凯' where stu_name='小彭';

删除student表中某条信息：delete from student where stu_name="小凯";

清空表数据：truncate table 表名;

/*格式化表后 id从1开始*/

查询显示时去处重复信息：select distinct stu_name from student;

分组查询：select stu_age,max(stu_age) from student group by stu_age;/*注：分组查询功能也可用去除重复功能实现*/

返回几条信息记录并按升/降序排列：select * from student order by stu_age asc/desc limit 0,8;

mysql按字段值的拼音首字母排序：（关键词binary ）SELECT name FROM topic ORDER BY binary name asc

随即抽取查询：select * from student order by rand() limit 0,1; // limit N; 随机显示 N 条

模糊查询字符：like 或者 _(下划线)

eg: select * from student where stu_name like '%彭%';

select * from student where stu_name like 'abc%'; //查询此字段以abc开头的数据

表中数据的导出：select * from student into outfile 'd:\\stu.txt' fields terminated by '#' lines terminated by '\r\n';

数据的导入表中：

1.load data infile 'd:\\stu.txt' into table student fields terminated by '#' lines terminated by '\r\n';

2.load data infile 'd:\\stu.txt' IGNORE into table student fields terminated by '#' lines terminated by '\r\n';

3.load data infile 'd:\\stu.txt' REPLACE into table student fields terminated by '#' lines terminated by '\r\n';

/*注：其中 ' #' 为分隔符号，可换成 '%' ; IGNORE 跳过重复信息 REPLACE 替换重复信息(小写也可以：ignore/replace)*/

SQL脚本数据(表以及结构)的导出：mysqldump -u root -p 数据库名 > d://stu.sql

导出数据库中的某个表：mysqldump -u root -p 数据库名 表名> d://stu2.sql

导出数据库中的任意多个表：mysqldump -u root -p 数据库名 表1

表2 表3> d://stu3.sql

/*注：以上三行代码在dos命令下执行(cmd)；表1 表2 表3 之间用空格，而不用逗号*/

SQL脚本数据(表以及结构)的导入：source d://stu.sql;

/*注：在MySQL中执行*/

导出stu_data数据库结构(不包括数据) ：

1.mysqldump -u -root -p -d --add-drop-table stu_data > d:/stu.sql

2.mysqldump -u -root -p -d stu_data > d:/stu.sql

/*注：在dos命令下执行(cmd)*/

两表联合查询

例：从student和grade表查出某学生信息，要求显示字段：stu_id,stu_num,stu_name,gra_Chinese,gra_Math,gra_Chemistry

eg:select student.stu_id student.stu_nmu student.stu_name,grade.gra_Chinese,ci.gra_Math,grade.gra_Chemistry from

student,grade where student.stu_num=grade.gra_num and student.stu_name="小彭";

内连接：select student.stu_name,grade.gra_Math from student inner join grade where student.stu_id,grade.gra_id and stu_name='小彭';

左外连：select student.stu_name,grade.gra_Math from student left join grade on student.stu_id,grade.gra_id and stu_name='小彭';

/*注：左外连 以from 后第一个表为主*/

右外连：select student.stu_name,grade.gra_Math from student right join grade on student.stu_id,grade.gra_id and stu_name='小彭';

/*注：右外连 以from 后第二个表为主*/

using替换on：select * from 表1 left join 表2 using(学号);

联合查询：

1.去除重复：select * from 表1 union select * from 表2;

2.显示全部：select * from 表1 union all select * from 表2;

/*两表要查询显示的字段，数据类型必须相同*/

联合倒叙：(select * from 表1) union (select * from 表2) order by 学号 desc;

交叉查询：select * from 表1,表2;

/*查询结果包含所有可能组合的数据*/

查看数据库编码：show variables like '%char%';

查看数据存放位置：show variables like '%datadir%';

修改编码:set names gbk/utf8/gb2312;

/*后面可加条件(where)，指定修改某个*/

替换字段中指定字符：update 表面 set 字段名 = replace(字段名, "旧字符", "新字符");

/******************************* 2014-05-27 ******************************************************/

查看mysql版本号：1. mysql -V （dos下执行）

2. mysql --help / mysql -I / mysql -? （dos下执行）

3. select version(); （mysql中执行）

4. status; （mysql中执行）

/******************************* 2014-08-26 ******************************************************/

mysql创建定时任务（mysql5.1.6以及以上版本）

事件调度器(Event Scheduler)：事件调度器有时也可称为临时触发器(temporal triggers)，因为事件调度器是基于特定时间周期触发来执行某些任务，

而触发器(Triggers)是基于某个表所产生的事件触发的，区别也就在这里。

开启event_scheduler： 1）执行命令：SET GLOBAL event_scheduler = 1; 或 SET GLOBAL event_scheduler = ON;

2）在

配置文件my.cnf中加上 event_scheduler = 1;

查看当前是否已开启事件调度器： 1）SHOW VARIABLES LIKE 'event_scheduler';

2）SELECT @@event_scheduler;

3) SHOW PROCESSLIST;

创建事件（CREATE EVENT）：

语法： CREATE EVENT [IFNOT EXISTS] event_name

ONSCHEDULE schedule

[ONCOMPLEFTION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement;

SCHEDULE:

AT TIMESTAMP [+ INTERVAL INTERVAL]

|EVERY INTERVAL[STARTS TIMESTAMP][ENDS TIMESTAMP]

INTERVAL:

quantity{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

例子：

创建：

CREATE TABLE aaa (timeline TIMESTAMP);

CREATE EVENT aaa_insert ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO test.aaa VALUES (CURRENT_TIMESTAMP);

删除：

DROP EVENT IF EXISTS test.aaa_test;

---------------------------------------------- 2015/5/4 ---------------------------------------------------

查看最大连接数：show variables like '%max_connections%';

修改最大连接数：set GLOBAL max_connections = 200;

查看当前链接数：show full processlist;