传智播客韩忠康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; 回到一个点之后 另一个保存点就不在了