mysql

数据库

1、连接mysql服务器
格式：mysql -h 主机名 -u 用户名  -p密码  库名
mysql -h localhost -u root -p

2、创建用户并授权授权
格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';
grant select,insert,delete,update on g21.* to malikuan@'%' identified by '123';

grant select,insert,delete,update on g21.* to malikuan@localhost identified by '123';

-- 删除用户
delete from `user` where `user` = 'cindy';
-- 更新权限
flush privileges;

-- 更改密码 update 
update `user` set `password` = password('jiegege') where `user` = 'jiege';
-- 更新权限
flush privileges;

基本操作

1、查询/创建/删除数据库
show databases;
create database [if not exits] 数据库名;
drop database 数据库名;

2、表操作

查看表
show tables

--查看表结构
desc tb1;

--查看建表的语句
show create table tb1\G  

删除表
drop table tb1;

插入表记录
mysql> insert into demo values('lisi',22,'m'); --不指定字段名来添加数据
mysql> insert into demo(name,age) values('wangwu',23); --指定部分字段名来添加数据
--批量添加数据
mysql> insert into demo(name,age,sex) values('aaa',21,'w'),("bbb",22,'m');

查看表记录
select * from tb1 where id=1;

修改表记录
update tb1 set age=22 where name='malikuan';

创建表
 create table [if no exists] 表名(
        字段1 数据类型 [ 完整性约束条件(属性,索引)],
        字段2 数据类型 [ 完整性约束条件(属性,索引)],
        字段3 数据类型 [ 完整性约束条件(属性,索引)]
 )engine=innodb default charset=utf8;

mysql>\h  --快捷帮助
mysql>\s  --查看当前数据库状态
mysql>\G  --切换查看方式
mysql>\q  --退出mysql
mysql>\c  --终止mysql命令
mysql>show warnings;  --查看警告

mysql数据类型

四大分类：数值型、字串型、日期类型、null

1、数值型

tinyint(1字节) 0-255

int(4字节) 42亿

bigint(8字节) 很大

float(4字节)   float(6,2)

2、字串类型

char(n) 定长字串 0-255
varchar(n) 不定长字串 大于255 

text  文本类型  常用于<textarea></textarea>
enum  枚举

3、时间和日期类型

date  年月日
time  时分秒
datatime 年月日时分秒
timestamp 时间戳

4、NULL值

NULL意味着“没有值”或“未知值”
可以测试某个值是否为NULL
不能对NULL值进行算术计算
对NULL值进行算术运算，其结果还是NULL
0或NULL都意味着假，其余值都意味着真

mysql特有的比较

in,not in,is null,is not null,like,between and

逻辑运算符

and,or,not

表字段的约束（属性）

unsigned 无符号(正数)
zerofill 前导零填充
auto_increment  自增
default	默认值
not null  非空
PRIMARY KEY 主键 （非null并不重复）
unique 唯一性   （可以为null但不重复）
index 常规索引

create table tb1(
	id int unsigned auto_increment not null,
	primary key(id),
	name varchar(12) unigque not null,
	sex tinyint unsigned not null,
	index `sex`(sex)
)engine=innodb default charset=utf8;

权限的深入

-- 授权一个用户（zhangsan）密码123，可以对所有的库，所有的表做所有操作。
mysql> grant all on *.* to zhangsan@'%' identified by '123';

--刷新生效，否则就要重启MySQL服务才可以。
mysql>flush privileges;

--浏览当前MySQL用户信息
mysql> select user,host,password from mysql.user\G;
	
-- 移除一些权限
-- revoke:只删除了用户权限，但没有删除这个用户
mysql> revoke insert,delete on *.* from admin@192.168.112.132 identified by'123';

-- 查看指定用户的权限信息
mysql> show grants for xbb@localhost;

--drop user:删除了整个用户及其权限（包括数据字典中的数据）
mysql> drop user 'xbb'@'localhost';

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| debian-sys-maint | localhost |
| root             | localhost |
| root             | wangxg    |
+------------------+-----------+
4 rows in set (0.00 sec)

修改表

-- 员工信息表
create table if not exists `xdl_user`(
	`id` int unsigned not null auto_increment primary key comment '自增',
	`name` varchar(255) not null comment '用户名',
	`sex` enum('0','1','2') not null default '2' comment '0:女,1:男,2:妖',
	`age` int unsigned not null comment '存储出生年月的时间戳',
	`tel` char(11) not null comment '电话', 
	`email` varchar(255) not null comment '邮箱',
	`major` varchar(255) not null comment '专业', 
	`marry` enum('0','1') not null default '1' comment '婚否',
	`department` varchar(255) not null comment '部门',
	`grade` tinyint unsigned not null default 3 comment '0:老板,1:经理,2:主管，3：普通员工',
	`status` tinyint unsigned not null default 0 comment '0:禁用，1：启用，2：离职',
	`addtime` int unsigned not null comment '添加时间'
)engine=innodb default charset=utf8;

insert into `xdl_user`(`name`,`sex`,`age`,`tel`,`email`,`major`,`marry`,`department`,`grade`,`status`,`addtime`) 
	values('lili','2',unix_timestamp(), 13838389438,'123321@qq.com','养猪专业','1','老板办公室',3,1,unix_timestamp());

在修改表结构前，请查看建表语句！！
show create table `xdl_user`;

1. 修改表名 rename
alter table `xdl_user` rename to `em_user`;
alter table `em_user`  rename `xdl_user`;

2.修改字段的数据类型 modify
`marry` enum('0','1') NOT NULL DEFAULT '1' COMMENT '婚否',
alter table `xdl_user` modify `marry` tinyint not null default 1 comment '婚否';

如果修改了数据类型，会破坏原有数据，在修改，安全起见，请将数据库先备份！！
在修改之前，表中已经存在数据，当修改表结构时，该列的数据，也会相应地转换为与该列数据类型一致的！！

3.修改字段名 change
 建议：直接把原语句复制下来，`marry` tinyint(4) NOT NULL DEFAULT '1' COMMENT '婚否',

alter table `xdl_user` change `marry` `new_marry` tinyint(4) NOT NULL DEFAULT '1' COMMENT '婚否' first;

alter table `xdl_user` change `new_marry` `marry` tinyint(4) NOT NULL DEFAULT '1' COMMENT '婚否' after `major`;

4.增加字段 add
默认情况排列在最后
alter table `xdl_user` add `address` varchar(255) not null comment '家庭住址';
alter table `xdl_user` add `address` varchar(255) not null comment '家庭住址' first; -- 添加到第一位
alter table `xdl_user` add `address` varchar(255) not null comment '家庭住址' after `name`; -- 添加到指定字段之后

5.删除字段 drop 
alter table `xdl_user` drop `address`;

6.修改位置
`address` varchar(255) NOT NULL COMMENT '家庭住址',

alter table `xdl_user` modify `address` varchar(255) NOT NULL COMMENT '家庭住址' after `addtime`;
7.修改存储引擎
alter table `xdl_user` engine=MyISAM;
alter table `xdl_user` engine=innodb;

索引

创建索引的目的：
	由于数据性能上的瓶颈，
	为了快速查找数据

1.primary key
	主键索引
		1.标记数据的唯一
		2.最常用
		3.每张必须设置(效率高),每张表，只能设置一个
		4.效率最高的索引
		5.最准确，快捷
2.unique
	唯一索引
		1.同一张表，可以设置多个
3.index
	普通索引或常规索引
		1.缺点减慢插入，删除
		2.多占空间

		索引合适就可以
		index索引，使用在经常查询的字段当中

4.fulltext 全文索引
	不支持中文

– 建表时，添加索引

create table if not exists `pj_user`(
	`id` int unsigned not null auto_increment  comment '主键' ,
	primary key(`id`) comment '将ID作为主键索引',

	`name` varchar(255) not null comment '使用该字段当作登录依据，必须唯一',
	unique un_name(`name`) comment '将name设置成唯一索引', 

	`tel` char(11)  not null ,
 	index in_tel(`tel`) comment '为tel字段设置普通索引',
 	`addtime` int unsigned not null default 1
)engine=innodb default charset=utf8;

唯一索引，可以有多个
普通索引，可以有多个
主键只能有一个

– 查看索引
show indexes from pi_user;

– 删除索引

-- 删除普通索引
drop index `in_tel` on `pj_user`;
-- 删除唯一索引
drop index `un_name` on `pj_user`;

i	-- 删除主键(极少删除主键)
		1.去除自增属性(修改表结构)
			alter table `pj_user` modify `id` int(10) unsigned not null comment '删除自增';
		2.删除主键
			alter table `pj_user` drop primary key;

– 建表后再添加索引

添加主键的两步：
	1.添加主键
		alter table `pj_user` add primary key (`id`) comment '将ID设置为主键';
	2.添加自增(修改表结构)
		alter table `pj_user` modify `id` int(10) unsigned NOT NULL auto_increment COMMENT '主键，自增';

添加唯一索引：
	alter table `pj_user` add unique un_name(`name`) comment '将name字段设置为唯一';

添加普通索引：
	alter table `pj_user` add index in_tel(`tel`) comment '将tel字段设置为普通索引';

– 没有指定引擎与字符集

create table if not exists `goods`(
	`id` int unsigned not null auto_increment  comment '主键' ,
	primary key(`id`) comment '将ID作为主键索引',

	`goodsname` varchar(255) not null 

)engine=innodb default charset=utf8;

1.如果不指定存储引擎，使用的是配置文件的默认值
2.如果不制定表的字符集，使用的是配置文件设置的字符集

	工作中没有权限修改

3.设置指定数据库的字符集
	ALTER DATABASE 数据库名 character SET 字符集;
	ALTER DATABASE g21 character SET utf8;
4.修改表的字符集
	ALTER TABLE `表名` DEFAULT CHARACTER SET 字符集;
	ALTER TABLE `goods_bak` DEFAULT CHARACTER SET gbk;

	使用黑窗口查正确显示内容，请设置 set names gbk;
		这是与操作系统有关

		utf8 utf8

无论如何，在建表时，请设置好字符集与存储引擎，解决不必要的配置


5.建表后，修改表的存储引擎
alter table 表名 engine = innodb;
alter table `goods_bak` engine = innodb;

php操作数据库

一、操作步骤

1、连接mysql数据库
2、设置字符集
3、准备sql语句
4、发送sql
5、处理结果集
6、释放结果集（关闭数据库连接）

二、常用操作：

1、mysqli_connect();--连接数据库，并返回一个连接资源
格式： mysql_connect(主机名，用户，密码); 
--其中参数可选，若不写则参考php.ini中默认配置
	
mysqli_error(); --获取刚刚（最后）执行数据库操作的错误信息


mysqli_select_db(数据库名[,数据库连接])； 
	选择一个数据库，等同于"use 库名"语句
	
2、mysqli_set_charset(字符编码)；  --设置字符编码
例如：mysql_set_charset("utf8"); 等同于：mysql_query("set names utf8");

3、mysqli_query(sql语句[，数据库连接])； -- 发送一条sql语句
     sql语句若是查询，则返回结果集，其他则返回boolean值表示执行是否成功。
	
4、解析结果集函数：*mysqli_fetch_assoc(); --以关联式数组解析结果集

	mysqli_fetch_array();  --以关联和索引两种方式数组解析结果集
	也可以指定第二参数来定义返回格式：
	MYSQL_BOTH(0关联和索引)/MYSQL_NUM(2索引)/MYSQL_ASSOC(1关联)
		
	mysqli_fetch_row();	  --以索引式数组解析结果集
	
	mysqli_fetch_object(); --以对象方式解析结果集

5、mysqli_free_result(结果集名)； --释放结果集

6、mysqli_close(数据库连接); --关闭数据库连接

7、mysqli_affected_rows -- 取得前一次 MySQL 操作所影响的记录行数
关联的 INSERT，UPDATE 或 DELETE 查询所影响的记录行数。
	
8、mysqli_insert_id — 取得上一步 INSERT 操作产生的 ID 

mysqli_num_fields(结果集); --获取结果集中的列数（字段数量）

mysqli_result(); --定位取结果
		echo mysql_result($result,0,3)."<br/>"; //获取第1条数据的第4列中的值
		echo mysql_result($result,1,2)."<br/>"; //获取第2条数据的第3列中的值
		echo mysql_result($result,5,4)."<br/>"; //获取第6条数据的第5列中的值

mysqli_num_rows(结果集); --获取结果集中的数据条数

多表查询、左表查询和表的复制

-- 内连查询
	SELECT * FROM 表1,表2 WHERE 表1.字段 = 表2.字段;

	select * from `shop_user`,`shop_address` where shop_user.id = shop_address.user_id;
-- 别名的使用
	select u.id , u.user , a.id , a.name , a.address from `shop_user` as u,`shop_address` as a where u.id = a.user_id;

-- 外连接 
-- 以左表为主: 左表的所有数据，都会查询出来，没有与之关联，则填空类型
	SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段
-- 以左表为主，右表的所有数据都会查询，并与左表关联

	select u.id , u.user , a.user_id , a.name , a.address from shop_user as u left join shop_address as a on u.id = a.user_id;


-- 子查询
   select * from shop_address where `user_id` = (select id from shop_user where `user` = 'admin');

-- 数据的复制

-- 复制表结构
create table 新表 like 旧表;
create table n_address like shop_address;

-- 复制数据
insert into 新表 select * from 旧表;
insert into n_address select * from shop_address;

数据库备份

--备份一个库
mysqldump -u 用户名 -p 密码 数据库名 > 路径.sql
mysqldump -u root -p test2 > c:/test2.sql

--备份多个数据库
mysqldump -u root -p --databases g21 g20 > c:/g21_g20.sql

--备份所有数据库
mysqldump -u username -p -all-databases > BackupName.sql

--备份表
mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql
dbname参数表示数据库的名称；
table1和table2参数表示需要备份的表的名称，为空则整个数据库备份；

-- 还原一个库
-- 必须先创建一个库 
mysql -u root -p test < c:/g21.sql

-- 还原多个库
-- 不需要先创建库
mysql -u root -p < c:/g21_g20.sql

-- 我们无法知道 .sql 文件，保存的到底是一个库，还是多个库
-- 无论如何，请新建一个空仓库，把数据保存到空仓库当中

-- 如果你的备份文件只有一个库，则会保存到新仓库中
-- 如果你的备份文件有多个库，则会重新创建与之前同名的仓库
mysql -u root -p temp < c:/g21_g20.sql