MYSQL一揽子方案汇总

MySQL-5汇总

一、数据库操作语句

mysql -hlocalhost -p3306 -uroot -proot

SHOW DATABASES;

SHOW CREATE DATABASE db_name； 

CREATE DATABASE [IF NOT EXISTS] db_name [CHARSET 字符集];

USE db_name;

DROP DATABASE [IF EXISTS ] db_name；

alter database db1 charset=utf8;

SHOW TABLES [FROM 表名];

DESC tb_name;

SHOW CREATE TABLE tb_name;

linux下mysql设置远程连接详见Linux-3关闭防火墙和设置远程连接。

Mysql注释方法1-- 注释（注意：--后面要有空格）；注释方法2#

二、建表语句

set names gbk;//在cmd中必须设置，否则返回ERROR 1067 (42000): Invalid default value for 'gender'

例 create table if not exists stu1(

    id int not null primary key auto_increment,

    name varchar(4) not null unique key,

    gender enum('男','女','保密') not null default '保密',

    age tinyint,

    salary float not null default 0,

    grad_time date comment '毕业时间',

    intro text comment '自我介绍'             //注意这里不能有,逗号

    )charset =utf8;

修改表、字段

alter table student1 add ismarry char(1) not null default '否';//添加字段

alter  table tb_name  modify   col_name  col_type  col_attr;

alter  table tb_name  change  col_name new_col_name col_type;改字段

ALTER  TABLE table_name  DROP [COLUMN] col_name, DROP [COLUMN] col_name, ……；

ALTER  TABLE table_name  DROP Index  col_name;// alter table student1 drop index name;可删除unique唯一键索引

alter  table tb_name  rename  new_tb;

drop  table [if exits] tb_name;

create table tb_name1 like tb_name2;//表结构

create table 表名1 select * from 表2;

-------------------------------------------------------------------------------------

show character set;

show variables like 'character_set_%';

show collation;

show variables like 'character_set%';

set names gbk；三表合一设置字符集

show engines;

1、创建索引---在建表完成后添加

alter table tb3 engine innodb;

alter table tb3 add index(id);

alter table tb3 add unique index `index_sn`  (`sn`)；

2、创建索引---直接在建表时创建

primary key (`id`)

index `index_name` (`xing`,`ming`)

unique index `index_sn` (`sn`)

fulltext index `index_intro` (`index`)

三、增改删查----------------------------------------------------------------------------------------------

增 insert into student1 (name,age) values ('张小兽',23);

insert into student1 set name='郑容和',age=27,salary=3000;

insert into student1 (name,age) values ('郭靖',23),('黄蓉',22),('杨过',25),('小龙女',26);

insert into student1(name,intro) select name,intro from student1;//只复制了名字和介绍，其他默认

改update 表名 set 字段1=值1，字段2=值2,…[where 条件]；// update student1 set gender='男' where id=9 or id=11;//同时修改两条记录

删 delete from student1 where id=6;//执行delete后id=6无数据，但id会保留。

truncate 表名；

查询操作（select）

select[all|distinct]字段列表 * from 表名 [where 条件][group by 条件][having by 条件][order by 条件][limit 条件];

select unix_timestamp() from dual;//1483707354时间戳

select id,name as 姓名,salary as 工资,address as 地址 from student1;//字段别名

select all/distinct name as 姓名 from student1;//distinct必须是所有字段的值都不同时才能用上

where 条件+>或<或=     //例1select * from student1 where address='南方' and age<23  or age is null;注意数据库中null无法做比较

where +in（）或not in（），in后面罗列字段可取值，使用（）包括。select * from tb1 where name in('小华','张三');

where+between或not between，where+is null或is not null

匹配符 where+ like + '%'匹配任意多个字符和like + '_ '匹配一个字符。例：select * from tb1 where name like '%华' or name like '%三';

select gender,count(gender) from student1 group by gender;//显示表中所有男生和女生数量

select group_concat(name,age) gender,count(gender) from student1 group by gender;//显示男生和女生数量以及姓名、年龄

select address,max(salary) from student1 group by address having max(salary)>1000;

//先按照地址，最高薪资分组，再筛选出分组结果中大于1000的地址和薪资信息。

order by 排序：select 字段列表 from 表名 [where 子句] [group by 子句] [having子句] [order by子句];

select * from student1 order by fenshu desc limit 3;//显示语文考试前三名的个人信息 limit  (page-1)*pagesize，pagesize。（即limit m,n ）

查询总结：select 字段列表 from 表名 [where子句] [group by子句] [having子句] [order by子句] [limit 子句];

• • where子句可以进行条件判断，对from子句返回的结果集进行筛选

• • group by子句可以对数据进行分组

• • having子句可以对分组的结果集进行进一步的筛选

• • order by子句可对结果集进行排序

• • limit限制最终结果集的输出行数

• • 注意各个子句之间的顺序不能错-----------------------------------------------------------------------------------

一、连接查询

1、内连接inner join……on

inner join语法：  select 字段列表 from 表1 inner join 表2 on 表1.字段=表2.字段。

例：select b1.*,b2.num2 from n1j1 as b1 inner join n2j2 as b2 on b1.id=b2.id;

同理：三张表的内连接查询： select * from 表1 inner join 表2 on 条件 inner join 表3 on 条件。

 2、左连接语法 : select 字段列表 from 表名 left join 表名 on 条件。

 3、右连接语法： select * from 表1 right join 表2 on 条件

4、 自然内连接natural join， 自然左连接natural left join， 自然右连接natural right join

5、using()可部分情况代替on 条件。select * from n1j1 inner join n2j2 using(id);

二、联合查询 union, 连接查询是将两张表 横向的连接到一起， 联合查询是将两张表 纵向的连接到一起

    select id,name from student where city like '%西%' order by salary desc limit 100     //工资降序

    union                                                                            //排序结果上下组合到一张表

    select id,name from student where age>35 order by salary asc limit 100;               //工资升序

三、子查询

select * from student1 where salary>(select avg(salary) from student1); .标量子查询

select * from student1 where age in(select age from student1 where name in('宋小花','周同学')); 列子查询

where 字段 in|not in(子查询语句)，where 字段 比较运算符 any(子查询语句)，where 字段 比较运算符 all（子查询语句）

select * from 表格 where(字段1,字段2) =(select 字段1,字段2 from 表);行子查询

select * from (表子查询的结果) as 表名表子查询

四、数据的备份及还原---------------------------------------------------------------------------------------

C:\WINDOWS\system32>mysqldump -uroot -proot -d db3<e:/db3.sql

C:\WINDOWS\system32>mysqldump -uroot -proot -B db3<e:/db3.sql导出数据库结构、表结构、表数据

C:\WINDOWS\system32>mysql -uroot -proot db3<e:/db3.sql     // 数据的还原

C:\WINDOWS\system32>mysql -uroot -proot <e:/db3.sql

mysql>source e:/db3.sql;

五、创建用户和用户授权

1.显示所有的用户：select host,user,password from mysql.user;

create user 'hangzhou'@'localhost' identified by '123456'; .创建用户

set password [for ‘user’@’ip地址’] = password(‘密码’);设置密码

grant 权限 on 数据库.表格 to ‘user’@’ip地址’[with grant option]用户授权

grant all privileges on *.* to ‘user ’@‘ip地址’//给用户赋予所有的权限

  revoke all privileges on *.* from ‘user’@’ip地址’移除用户的权限

drop user ‘user’@’ip地址’删除用户

六、找回密码

1. 关闭mysql服务 net stop mysql
2. 让mysql跳过验证密码的环节 mysqld --skip-grant-tables
3. 打开一个新的cmd，然后直接输入mysql登陆
4. 修改密码  update mysql.user set password=password(‘新密码’) where user=’root’;
5. tastkill /f /im mysqld   ||  tastkill /f /pid 3228  或者直接在任务管理器中结束mysqld任务
6. 重新启动mysql服务即可正常登陆

七、 MySQL 中的预处理

基本语法：

//1 、创建预处理语句

prepare  预处理指令  from  SQL 语句 (insert/delete/update/select)

//2 、设置参数

set @value = value;

    //3 、执行预处理

    execute  预处理指令 ;

    prepare stmt1 from 'insert into user values (null,?,?,?)';//1创建预处理语句

    set @username='zhangsan';//2设置参数

    set @password=md5('123456');

    execute stmt1 using @username,@password;//3执行预处理语句

    select * from user;//4返回执行结果

在php中的应用

$link=new mysqli('localhost','root','123456','test'); #连接数据库

if ($stmt=$link-> prepare("select * from curl_songs_num where id in ($ids)") ) { #预处理语句

    $stmt-> execute();    #执行查询语句

    $stmt-> bind_result($id,$title,$author,$cont );    #给字段绑定参数

    while($stmt->fetch()){

     //$songs=...;

     echo "<span style='color:blue'>$song[0]</span><br>$song[1]<hr>";

    }

}

$stmt->close();

$link->close();

八、MySQL的事务

①开启事务

1 ）标准事务： start  transaction;

当提交或回滚一个事务之后，事务结束。接下来的操作不在事务操作范围之内。

2 ）语句事务： set  autocommit = 0;

当提交或回滚一个事务之后，事务不会终止，后面的操作仍处于事务之中。

②提交事务： commit;

③回滚事务： rollback;