0311lc.com说：

 

 

 PHP

连接  mysql_connect 域名:端口 用户 密码  or  die               或 mysql_errno  mysql_eror  mysql_pconnect
mysql_select_db 数据库 连接  or  die
mysql_query   set names 'utf8'  或 gbk 根据页面定
mysql_query  sql 连接

mysql_fetch_array  rs   MYSQL_BOTH      多时候 可while $row
mysql_fetch_assoc  rs

mysql_num_rows  rs

mysql_data_seek  rs  id

mysql_close

 

用户权限

select user from mysql.user; //查询账号

 select password ('111111'); // 111111的散列值

//创建用户
create user 'luxilang'@'localhost' identified by '111111',
'luxilang_ps'@'localhost' identified by password 
'*FD571203974BA9AFE270FE62151AE967ECA5E0AA'

drop user luxilang@localhost ; //删除用户

 rename user luxilang_ps@localhost to luxilang1@localhost; //重命名

select password('888888')
//修改密码
set password for 'luxilang1'@'localhost'
='*DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB';  

show grants for 'luxilang1'@'localhost'; //查看权限 grants 后面有s
//权限授予  grant  权限 on 表  to 用户和域  identified by  '密码' 
//用户存在时候
grant select(age)  on  db_school.stuinfo1  to 'luxilang1'@'localhost';
//用户 luxilang1登陆 SELECT * FROM stuinfo1 时错的没权限只能age字段
grant select,update on    db_school.stuinfo1  
 to 'luxilang2'@'localhost'  identified by '111111';

grant all on    db_school.stuinfo1  
 to 'luxilang3'@'localhost'  identified by '111111'; //所有权限luxilang3

grant create user on *.*  to 'luxilang2'@'localhost' ; //授予权限给luxilang2
//用户不存在 用下面
grant select,update on stuinfo1
 to  'luxilang3'@'localhost'  identified by '111111'
with grant option; //创建 并 授权权限

grant delete on stuinfo1 to  'luxilang8'@'localhost'
with max_queries_prer_hour 1  // 每小时只能处理删除一条delete 数据

//撤销 revoke  权限 on 表 from 用户和域       是 from
revoke update  on stuinfo1   from 'luxilang2'@'localhost'; //权限撤销

备份和恢复

show variables like '%secure%'; //查看 secure_file_priv  
//secure_file_priv =目录            指定目录，
//secure_file_priv=Null             关闭,
//secure_file_priv=                 开启
然后 my.ini  里面 设置 为 secure_file_priv =    才能用
secure_file_priv =''

注意  set names 'GBK';
//双反斜杠 确定没有目录下没文件 有就删除它
//导出 数据
select * from stuinfo1
into outfile 'd:\\1.txt'    
fields terminated  by ','
optionally enclosed by '"'
lines terminated by "?\n";

CREATE TABLE `stu100` (
  `stu_no` INT(10) NOT NULL AUTO_INCREMENT,
  `stu_name` VARCHAR(10) DEFAULT NULL,
  `class_no` INT(4) NOT NULL,
  `sex` TINYINT(1) DEFAULT '1',
  `age` INT(4) DEFAULT '0',
  PRIMARY KEY (`stu_no`)
) ENGINE=INNODB  
//做一个拷贝 导入数据
load  data infile 'd:\\1.txt'  
into  table stu100
fields terminated  by ','
optionally enclosed by '"'
lines terminated by "?\n";


//用mysqldump 表的 导出sql 包含结构 
mysqldump -h localhost -u root -proot db_school stuinfo1 > d:\\stuinfo1.sql
mysqldump  -u root -proot db_school > "d:\db_school.sql" //数据库备份
mysqldump  -u root -proot --events --ignore-table=mysql.events --all-databases > "d:\alldatabases.sql"  //需要--events --ignore-table=mysql.events
mysqldump  -u root -proot db_school --tab="d:\back" //建立目录back在备份

//创建db_school1
CREATE DATABASE `db_school1`CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
//备份 数据库  db_school stuinfo1 表  d:\stuinfo1.sql
mysqldump -u root -proot  db_school stuinfo1 >d:\stuinfo1.sql
//恢复表d:\stuinfo1.sql  到  db_school1 stuinfo1
mysql -u root -proot db_school1 <d:\stuinfo1.sql

存储过程和存储函数

//存储过程  call调用 有返回值
delimiter $$
create procedure sp_update_age(IN s_age int(10))
begin
 update stuinfo1 set age=s_age  where stu_no =10;
end $$
delimiter  ;

create  trigger age_update after update
ON stuinfo1 for  each row
insert into operate values(null,'update age stuinfo1',now());

call sp_update_age(20) ; 

delimiter  $$
create function  fn_search(sno int(10))
returns varchar(10)
begin 
declare ssex int(4);
select  sex into ssex from  stuinfo1 where  stu_no = sno;
if ssex is null then
   return (select 'no stu');
else 
  if ssex = 1  then
   return (select 'nv');
   else 
   return (select 'nan');
  end if;
end if;
end $$
delimiter  ;

select fn_search(10);  // nv
select fn_search(11); // no data

drop  function fn_search

触发器

=========触发器===mysql5.0======
show triggers; //查看trigger

//触发事件-增加的
create trigger  stuinfo1_triggers  after  insert 
on  stuinfo1 for each row  set @str = New.stu_no;

insert into stuinfo1(stu_name,class_no,sex,age) values ('nihao1','1','1',21);

select @str;

//触发事件-删除
create trigger  stuinfo1_del_trigger  after delete
on stuinfo1  for each row set @str_del = 'del one';
select @str_del
//触发事件-更新  只能用 before update
select age from stuinfo1 where  stu_no =10; //老的20
 create trigger stuinfo1_update_trigger  before update
 on stuinfo1 for each row set NEW.age=OLD.age+1;

update stuinfo1 set stu_name='up_nihao' where stu_no=10;
select age from stuinfo1 where  stu_no =10; //新变的21
//删除trigger
drop trigger stuinfo1_trigger;

触发器
 create table product(
 id int(10) not null  unique primary key,
 pro_name varchar(10) not null,
 pro_fun varchar(50),
 company varchar(20) not null,
 address varchar(50)
 ) engine = innodb;

create table operate
(
 op_id int(10) not null unique primary key  auto_increment,
 op_name varchar(20) not null,
op_time time not null
) engine = innodb;

create trigger  product_inster  before insert
on product for each row
insert into operate values(null,'insert product',now());

 create trigger product_update after update
 on product for each row
 insert into operate values(null,'update product',now());

 insert into product values(1,'kangtai','china-bj-bj','china-bj-bj-cp');
 select * from operate
 update product set address='china-sjz-ca' where id =1;
 select * from operate

show triggers \G;
drop trigger

事件调度器

=========事件调度器===mysql5.1======
查看 
SELECT `EVENT_NAME` FROM `INFORMATION_SCHEMA`.`EVENTS` WHERE `EVENT_SCHEMA` = 'db_school' ORDER BY EVENT_NAME;
//if exists

select @@event_scheduler;  //撕扯度乐 注意 @@后面紧贴 如果off 就开开
set global event_scheduler = 1;
select @@event_scheduler;  //为 on 
//hour 小时 minute 分 second 秒 day  week  month  year QUARTER季度

SELECT NOW();
//在 2019-09-18 18:52:00 时刻 更新
CREATE EVENT  event_insert_stuinfo1
ON SCHEDULE   AT '2019-09-18 18:52:00'
DO
 UPDATE stuinfo1 SET age=39 WHERE stu_no=10;
//每分钟都更新
CREATE EVENT  event_insert_stuinfo1
ON SCHEDULE   EVERY  1 MINUTE 
DO
 UPDATE stuinfo1 SET age=age+1 WHERE stu_no=10;

//关闭
alter event event_insert_stuinfo1 disable
//启动
alter  event event_insert_stuinfo1 enable
//改名
alter event event_insert_stuinfo1 rename to  e_stuinfo1
//删除
drop event e_stuinfo1

索引

show  index  from stuinfo1; //查看索引

//create table  创建索引
//create table 创建普通索引
create table tb_stu9
(
  stu_no int(10) not  null ,
 stu_name  char(15) not null,
index (stu_name)) engine = innodb;
show index from tb_stu9;  // Non_unique  1  普通的

//create table 创建唯一索引
create table  tb_stu10
(
 stu_no int(4) not null unique,
 stu_name varchar(8) not null
) engine = innodb;
show index from tb_stu10;  //  Non_unique  0 唯一 

//创建两个表外键主键  自动 生成索引
create  table tb_stu11
(
  stu_no int(4),
  class_no int(4)
) engine = innodb;
//注意  foreign key  后面 是要括号的
 create table tb_stu13
 (
   stu_no  int(4),
   class_no  int(4),
   score float,
 constraint PK_stu primary key (stu_no, class_no),
 constraint FK_stu_no  foreign key (stu_no) references stuinfo1(stu_no),
 constraint FK_class_no foreign key (class_no)  references  tb_class(class_no)
 ) engine = innodb;
show index from tb_stu13; 
//create  index  索引名 on  表名( 字段名(长度) desc/asc)
//建立空索引表
create  table  tb_stu14 (
  stu_no int(4),
   class_no int(4),
  stu_name varchar(20),
 score float
) engine = innodb;
show index from tb_stu14; 

//普通索引 index
create index index_stu_class on tb_stu14(class_no);
//字段值前缀字符的索引
create index  index_stu_name3str on tb_stu14(stu_name(3) DESC);
//组合索引
create index  index_no_nane on tb_stu14(stu_no,stu_name DESC);
//alter table 表名 add index  索引名(字段名) 
alter table tb_stu14 add index idx_class_no(class_no);
//删除索引  drop index  索引 on 表   或 alter table 表 drop index 索引
drop  index  idx_class_no on tb_stu14;
alter table tb_stu14 drop index  index_stu_class;
show index  from  tb_stu14;

主键、候选键、外键

//create table 建主键外键
create table tb_student2 ( stu_no int(10) not  null auto_increment, stu_name varchar(20) not null, primary key (stu_no) )ENGINE = INNODB;  //表级完整性约束 定义主键
drop table student2 ;

create table tb_student2 ( stu_no int(10) not  null auto_increment, stu_name varchar(20) not null,constraint PK_stu primary key (stu_no) ) engine = innodb;  //表级完整性约束 定义主键 并命名

drop table student2 ;

create table tb_student2 ( stu_no int(10) not  null auto_increment primary key, stu_name varchar(20) not null ) engine = innodb; //列级完整约束 定义主键

drop table student2 ;

create table tb_student2 ( stu_no int(10) not  null   auto_increment primary key, stu_name varchar(20) not null,stu_class char(10)  unique  ) engine = innodb; //建立候选键  

create table tb_student2 ( stu_no int(10) not  null   auto_increment primary key, stu_name varchar(20) not null,stu_class char(10),constraint UQ_class unique (stu_class)  ) engine = innodb;//建立候选主键 表级建立并命名

 create  table stuinfo1(stu_no int(10) not null auto_increment, stu_name varchar(10),class_no int(10),
constraint PK_stu primary key (stu_no),
constraint FK_stu foreign key (class_no) references tb_class(class_no) 
) engine = innodb; //只能表级建立 -- 完整外键  foreign key  references

alter table stuinfo1 drop foreign key FK_stu; //删除外键约束 foreign key

//alter table 建主键外键
alter table stuinfo1 add foreign key (stu_no) references tb_class(class_no) ; //添加没有名的外键    会自动生成名

 show create  table  stuinfo1; //查看表结构 并查 外键为 stuinfo1_ibfk_1
//删除外键
alter table  stuinfo1 drop foreign key stuinfo1_ibfk_1; // 删除该外键

添加主键、候选键、外键

alter table  stuinfo1 add constraint PK_stu primary key (stu_no);
alter  table stuinfo1 add constraint QU_stu unique key (stu_name);
alter  table stuinfo1 add constraint FK_stu1 foreign key (class_no) references tb_class(class_no);

//删除主键
如果是自增 那么就要修改为不自增才能删除主键
alter table  stuinfo1  change column stu_no stu_no int(4) not null ;
alter table stuinfo1 drop primary key;
//删除候选键
alter table  stuinfo1  drop index UQ_stu
//删除外键
alter  table  stuinfo1 drop foreign key FK_stu1

建立数据库、修改表、修改列

SHOW VARIABLES LIKE 'lower_case_table_names';
SET NAMES 'utf8';
SET sql_mode='';
SHOW DATABASES;

drop database db_school;
show databases;
use  db_school;

create database db_school
default  character set utf8mb4
default collate utf8mb4_general_ci;

show databases;
use  db_school;

 create table  tb_student
 (
  studentNo char(10)  not null unique,
  studentName varchar(20)  not  null,
  sex  char(2),
  birthday date,
  native varchar(20),
  nation varchar(10),
  classNo char(10)
 ) engine = innoDB;

 create table  tb_student1
 (
 studentNo int(10) not  null unique  auto_increment,
 studentName varchar(20) not  null
 ) engine =  innodb;


CREATE TABLE tb_student2
(
 studentNo INT(10)  NOT  NULL  UNIQUE  AUTO_INCREMENT,
nation VARCHAR(20) DEFAULT '汉'
 ) ENGINE = INNODB;

show engines
show tables

show columns from tbstudent2;  结构描述
desc tbstudent2;  结构描述
show create table tbstudent2;  所有详细的

alter  table tbstudent2 add column luxilang int(10)  not  null after nation;

alter table tbstudent2  change column   department  depart varchar(30)  default 'nihao';  修改  department   为   depart 和数据类型

alter  table  tbstudent2  alter  column  depart  drop  default; 清除默认值
alter  table  tbstudent2  alter  column  depart  set  default 'j8'; 修改默认值

alter  table tbstudent2  add column  luxilang2 int(8); 添加一个列
alter table  tbstudent2  drop column  luxilang2; 删除一个列

rename table db_school.tbstudent2  to  db_school.tb_student2; 修改表名
 show tables;
alter table tb_student2 rename to tb_student3; 修改表名
 show tables;
drop  table  tb_student3; 删除表