传智播客韩忠康PHP视频教程第二版Mysql学习笔记-mysql语句

这是15年上半年看传智播客韩忠康PHP视频教程第二版Mysql学习mysql语句知识时写在txt文本里的语句笔记，平时也经常用到，当记不得常用的一些写法时，就翻翻这个文本。现在看看有些当时没理解的语句会了，有些复杂的没有用到还没有理解透彻，整理下这个文档。

1. 创建数据库
create database db_name[数据库选项];   //db_name为数据库的名字  下同
create database php_one character set gbk; 
//utf8 22:20 2015/4/4 星期六 最好把编码带上 要不一直中文是问号 郁闷好久  还是一个百度知道瞬间提醒了

2. 查看当前存在的数据库
show databases;

3. 查看数据的创建语句
show create database db_name;

4. 删除数据库
drop database db_name;

5. 修改数据库信息
alter database db_name;
例：
alter database php_one character set gbk;

6. 创建表  //指定库 然后.表 如 database.table
create table tbl_name(列结构)[表选项]；
例：
create table php_one.php_class(class_no varchar(20),date_start date);  //指定数据库为php_one的表php_class

create table one(user_name varchar(20),user_age int);

create table two(user_name varchar(20),user_age int);

7.指定当前默认数据库  只是设定了默认数据库不会影响操作其他数据库
use db_name;
例：
create table wtsxia_class(class_no varchar(20),date_start date);

8.查看表
show tables;

9.表名前缀 为了区分相同逻辑表名的不同应用 给逻辑表名增加前缀，形成真实表名
例：
create table info_student(name varchar(20),stu_no varchar(20)); //学生管理

create table exam_student(name varchar(20),stu_no varchar(20),score int);//在线考试

10.查看某个打头的表的字段 %为通配符
show tables like 'exam_%';   //这里的引号必须要的 还有tables后面有s

11.查看表的创建信息
show create table tbl_name;

12.查看表结构
describe tbl_name;
desc tbl_name;
例：
describe exam_student;

13.删除表
drop table tbl_name;
drop table exam_student;
或者
drop table if exists tbl_name;   //如果表存在

14.修改表  修改表名 修改列定义 修改表选项
rename table tbl_name1 to tbl_name2;
rename table info_student to exam_student;

rename table tbl_name1 to tbl_name2,tbl_name3 to tbl_name4;

支持 跨数据库重命名

rename table tbl_name1 to db_name.tbl_name2;

15.添加一个列
alter table tbl_name1 add 列名 类型;
例：
alter table tbl_name1 add height int;

16.删除一个列
alter table tbl_name1 drop 列名;
例：
alter table tbl_name1 drop height;

17.修改列的定义
alter table tbl_name1 modify 列名  新属性;
例：
alter table tbl_name1 modify stu_no varchar(40);


18.重命名列的名字
alter table tbl_name change 原来的列名 现在的列名 类型;
例：
alter table exam_student change height fenshu int;

19.修改表选项 
alter table tbl_name 新的表选项;
例：
alter table exam_student character set utf8;

19.创建数据
insert into exam_student (name,stu_no) values ('xiaowang','php001');
insert into exam_student (name,stu_no) values ('xiaomomg','php002');
insert into exam_student values ('xiaozhang','php003','24');
insert into exam_student values ('xiaozhang','php003','27');

20.查询数据
select 字段列表 from 表名 查询条件;
例：
select * from exam_student;
select name,stu_no from exam_student;
select name,stu_no from exam_student where 1;
select * from exam_student where fenshu>=30;

21.删除数据
delete from exam_student where fenshu <72;
delete from exam_student where fenshu =60;

22.修改数据
update 表名 set 字段=新值,.....条件;
例：
update exam_student set fenshu=70 where fenshu=60;
update exam_student set fenshu=70 where name='xiaowang';  

23.交换两个表
create table tb1(tbl1_id int);
create table tb2(tbl2_id int);

rename table tb1 to tb3,tb2 to tb1,tb3 to tb2;

24.查看变量
show variables like 'character_set%'; //variables的意思是可变的 就是变量

25.查看校对规则
show collation;

26.设置字符集
create table tbl_name(name varchar(10))character set gbk collate gbk_chinese_ci;

create table tbl4(name varchar(10))character set gbk collate gbk_bin;

27.order by 关键字 可以在获得数据时，将数据进行按照某个字段的排序
select * from tbl4 order by name;

28.数值类型
//当时没有写 不记得为什么不写了 估计这里应该写 int

29.定点数
create table num_1(send_money decimal(10,2));

insert into num_1 values (1234.567);

30.时间和时间戳
create table dt_1(a datetime,b timestamp);

insert into dt_1 values ('2015-3-2 16:09:23','2015-03-04 16:44:34');

31.time 时间
create table t_1(ago time);
insert into t_1 values ('23:44:33');

32.text 
create table s_7(a text,b text)character set latin1;

33.enum 枚举
create table s_8(gender enum('female','male'));

insert into s_8 values ('male');
insert into s_8 values ('female');
insert into s_8 values ('other');  //应该最后一条不能成功啊 居然成功了 只是警告 值为空  整型为0

34.set 集合
create table s_9(hobby set('basket','football','pingpang'));

insert into s_9 values ('basket,football');

35.不能为空
create table php1(a int not null,b int);

insert into php1 (a) values (10);
insert into php1 (b) values (11);

36.默认值属性
create table php2(a int not null default 10,b int not null default 21);

insert into php2 (a) values (10);
insert into php2 (b) values (11);

37.主键      primary key 主键不能重复 所以插入的值不能重复 也不能为NULL
create table teacher (t_id int primary key,t_name varchar(5),class_name varchar(6),days tinyint unsigned);

或者
create table teacher (t_id int,t_name varchar(5),class_name varchar(6),days tinyint unsigned，primary key （t_id）);
或者  一个主键上包含了两个字段 组成了一个主键  一个表只可以有一个主键

create table teacher (t_name varchar(5),class_name varchar(6),days tinyint unsigned，primary key （t_name,class_name）);
insert into teacher values (1,'张A','0332','25');

38.自动增长 auto_increment
create table teacher2 (t_id int primary key auto_increment,t_name varchar(5),class_name varchar(6),days tinyint unsigned);
insert into teacher2 values (null,'韩c','0228',44);

39.修改自增长的初始
alter table teacher2 auto_increment 10;

40.外键 foreign key
//要先设置编码 set names gbk;
drop table if exists itcast_class;
create table itcast_class(class_id int primary key auto_increment,class_name varchar(10) not null default 'itcast_php' comment '班级名称' )character set utf8;

drop table if exists itcast_student;
create table itcast_student(stu_id int primary key auto_increment,stu_name varchar(10) not null default '' ,class_id int,foreign key  (class_id) references itcast_class (class_id))character set utf8;

如果没有对应的父表记录 则无法加入数据
insert into itcast_class values (null,'php0331');
insert into itcast_student values (null,'张三丰',1);

41. 注释 comment
//这里当时也没有写

42.修改外键
先删除 再新建 通过修改表完成
alter table tb1_name drop foreign key class_id;
alter table tb1_name add foreign key 外键定义

怎么看class_id
show create table tbl_name;
中的constraint 单引号里面的内容

alter table itcast_student drop foreign key itcast_student_ibfk_1;

alter table itcast_student add foreign key (class_id) references itcast_class
(class_id)
on delete set null;

delete from itcast_class where class_id=1;

43.级联操作
insert into itcast_class values (1,'php0331');
update itcast_student set class_id=1 where stu_id=2;

alter table itcast_student drop foreign key itcast_student_ibfk_1;
alter table itcast_student add foreign key (class_id) references itcast_class
(class_id)
on delete cascade;

delete from itcast_class where class_id=1;

即删除级联操作又更新时严格要求
alter table itcast_student drop foreign key itcast_student_ibfk_1;
alter table itcast_student add foreign key (class_id) references itcast_class
(class_id)
on delete cascade
on update restrict;

44.设置表的存储引擎
alter table itcast_class engine myisam;
alter table itcast_class engine innodb;

45.创建时修改存储引擎
create table room(room_id int primary key auto_increment,room_no char(3))engine myisam|innodb;

create table room(room_id int primary key auto_increment,room_no char(3))engine myisam character set utf8;

46.order by 排序  查询的时候排序
select * from tbl_name order by 字段;
select * from tbl_name order by 字段 desc; //desc代表降序  //asc代表升序
select * from tbl_name order by 字段 desc，字段 asc ;  //可以两种方式排序

47.limit  偏移量和长度
select * from  teacher_class limit 2,4;

48.distinct 去掉重复的记录
select distinct class_id from teacher_class;  //去掉class_id重复的记录
select distinct class_id，class_name from teacher_class; //去掉class_id，class_name 都相同重复的记录

select distinct all from teacher_class; //去掉完全全部重复的记录

49.union 联合查询
(select t_name,days from teacher c_name='php0115' order by days desc limit 1)
union
(select t_name,days from teacher c_name='php0228' order by days desc limit 1)

50.修改数据库默认空密码
use mysql

update user set password=PASSWORD('密码') where user='root';

flush privileges;  //privileges 权限的意思 flush冲洗的意思 合起来应该是突破权限

51.子查询
查最多的
select max(列名字) from teacher_class;

select t_name,gender from teacher_class where days = (select max(days) from teacher_class);  //带着括号最好

52.内连接
tbl_left inner join tbl_right on 连接条件

53.outfile 将查询的内容保存在文件里
select * into outfile 'd:/one' from itcast_class;

select * into outfile 'd:/one'
fields terminated by '\t' enclosed by 'x'
lines terminated by '\n' starting by 'start:'
from itcast_class where class_id = 'abc';


select * into outfile 'd:/two'
fields terminated by '\t' enclosed by 'x'
lines terminated by '\n' starting by 'start:'
from itcast_class;

54.insert 插入
insert into itcast_class (class_name) values ('ddd');
insert into itcast_class set class_name='dcc',class_id='7';

55.replace 替换插入
replace into itcast_class values (1,'rff');

56.创建视图
create view view_name as select statement;
create view v_teacher as select tagname,appid from pre_ucenter_tags;

57.自定义函数
delimiter $$
create function sayHello() return varchar(20)
begin
return 'hello world';
end
$$
delimiter;

58.
先开启事务 
start transaction;
设置保存点a 
savepoint a;
设置保存点b
savepoint b;

操作之后回到保存点
rollback to a;
回到一个点之后 另一个保存点就不在了