MySQL语句总结

MySQL语句总结

注释符号：a.-- (注意:--后有一空格)b.#c./*……*/

停止mysql：net stop mysql启动mysql：net start mysql/*注：在dos命令下*/

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

mysql 改密码：mysqladmin -u root -p password 新密码Enter……：旧密码/*注：在dos命令下*/

mysql 密码破解：a.找到mysql/my.inib.打开my.ini找到[mysqld]c.在[mysqld]之后另起一行写下如下代码：

skip-grant-tables

进入系统数据库：use mysql;/*注：在mysql中执行,以下查询,删除一样*/

查询用户：select * from user;

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

远程登录：mysql -u root -p1******* -h 192.168.x/*注：在dos命令下,192.168.x代表IP号,请改为自己的*/

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

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

identified by '19930108';/*注：在mysql中执行*/

用户登录：mysql -u xiaopeng -p1*******/*注：在dos命令下*/

撤销授权：revoke all on bjxh.* 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 stu_data default character set utf8 collate utf8_unicode_ci;

创建表：创建一个学生(student)表：use stu_data;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,'男','137********');方法二：insert into

student values (NULL,2012001,'小彭',20,'男

','137********');方法三：insert into student values (NULL,2012001,'小彭',20,'男

','137********'),(NULL,2012002,'小凯',19,'男

','188********');/*方法三为多条插入*/查询student表中内容：select * from student;

创建一个学生成绩(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_Englis h,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; /*垂直显示*/3.desc student;

向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="小凯";

查询显示时去处重复信息：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;

随即抽取查询：select * from student order by rand() limit 0,1;

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

表中数据的导出：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.sql2.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_Che mistryeg:select student.stu_id student.stu_nmu

student.stu_name,grade.gra_Chinese,ci.gra_Math,grade.g ra_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%';

修改编码:set names gbk/utf8/gb2312;/*后面可加条件(where)，指定修改某个*/