注释符号: 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.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 /*注:在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 -p19930108 /*注:在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,'男','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; 创建一个学生成绩(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; /*垂直显示*/ 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.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%'; 修改编码:set names gbk/utf8/gb2312; /*后面可加条件(where),指定修改某个*/ |
|