db_name:数据库名字
tb_name:表名字
1、create database db_name;
创建数据库
2、show databases;
查看数据库
3、show create database db_name;
查看数据创建信息
4、drop database db_name;
删除数据库
5、alter database db_name character set gbk;
修改数据库编码
6、create table db_name.tb_php_class (class_no varchar(20),date_start date);
创建表
7、use db_name;
设定默认数据库
8、create table tb_php_class (class_no varchar(20),date_start date);
创建表
9、show tables;
查看表
10、show tables like ‘xx%’;
模糊查询
11、show create table tb_name;
查看表创建信息
12、show create table tb_name\G;
查看表创建信息,另一种方式显示
13、drop table tb_name;
删除表
14、drop table if exists tb_name;
如果表存在删除
15、rename table tb_name_1 to tb_name_2;
表重命名
16、alter table tb_name add field_name int;
给表添加一个字段
17、alter table tb_name drop field_name;
删除一个字段
18、alter table tb_name modify field_name varchar(40);
修改字段定义
19、alter table tb_name change field_name_1 field_name_2 int;
重命名字段
20、alter table tb_name character set utf8;
修改表选项
21、insert into tb_name (name, stu_no) values (‘zhangsan’,’php_001’);
对应字段插入一条数据
22、select * from tb_name;
查询当前所有字段数据
23、select name from tb_name where stu_no=’php_001’;
查询字段stu_no值为’php_001’的字段name的值
24、insert into tb_name values (‘zhangsan’,’php_001’);
直接插入数据,默认字段对应
25、delete from tb_name where id>10;
删除满足条件的数据
26、update tb_name set score=100 where score>72;
设置分数大于72的分数为100
27、rename table tb_name_1 to tb_name_3,tb_name_2 to tb_name_1,tb_name_3 to tb_name_2;
交换两个表的名称
28、show collation;
查看校对规则
29、create table tb_name (name varchar(10)) character set gbk collate gbk_bin;
创建表,并设置字符集
30、alter table tb_name add field_name tinyint(2) zerofill;
增加一个字段,并设置零填充(插入的数据长度不够时自动用零补齐)
31、create table tb_name (a float(5,2));
创建一个表,字段类型为float并且保存数据时总长度为5小数位为2,再插入数据时整数不能超过3位,小数超出2位之后会被截掉。
32、create table tb_name (a int not null,b int);
创建表,并且设置字段a 不能为null
33、create table tb_name (a int default 1,b int);
创建表,并设置字段a默认值为1
34、create table tb_name (a int primary key,b int);
创建表,并设置字段a为主键
35、create table tb_name (a int primary key auto_increment,b int);
创建表,并设置字段a为主键,值自动增加,默认初始值为1
36、alter table tb_name auto_increment 10;
设置表中的自动增加的默认初始值为10
37、create table tb_name (id int primary key auto_increment,class varchar(10) comment ‘班级名称’);
创建表,并未字段class添加注释‘班级名称’
38、create table tb_name_1 (id int primary key auto_increment, class varchar(10), class_id int foreign key (class_id) references tb_name_2 (class_id));
创建表tb_name_1,并指定class_id为关联字段,关联tb_name_2中class_id
39、alter table tb_name drop foreign key tb_name_ibfk_1;
删除外键名称
40、alter table tb_name add foreign key (class_id) references tb_name_2 (class_id) on delete set null;
添加外建名称,并设置删除表数据时将从表(tb_name_1)相关字段设置为null
41、alter table tb_name add foreign key (class_id) references tb_name_2 (class_id) on delete cascade;
添加外建名称,并设置删除表数据时将从表(tb_name_1)数据删除—级联操作
42、alter table tb_name add foreign key (class_id) references tb_name_2 (class_id) on update restrict;
添加外建名称,并设置删除表数据时将从表(tb_name_1)数据删除—级联操作;并设置不允许主表(tb_name_2)更新操作
43、alter table tb_name engine myisam;
指定表的存储引擎
44、create table tb_name (id int primary key auto_increment,num char(3)) engine myisam character set utf8;
创建表,并指定存储引擎
45、select * from tb_name limit 3, 4;
从第3条数据开始共获取4条数据
46、select distinct num from tb_name;
查看数据并去掉重复的数据
47、select * from tb_name where num=20 order by days desc limit 1;
查找数据并且num等于20,按days排序最高的一个
48、(select * from tb_name where num=20 order by days desc limit 1) union (select * from tb_name where num=30 order by days desc limit 1);
联合查找数据,num等于20,按days排序最高的一个 和 num等于30,按days排序最高的一个
49、(select * from tb_name where num=20 order by days desc limit 5) union all (select * from tb_name where num=30 order by days desc limit 5);
联合查找数据,num等于20,按days排序最高的5个 和 num等于30,按days排序最高的5个,并包含重复的,去掉all,就会消除重复的
50、(select * from tb_name where num=20) union all (select * from tb_name where num=30) order by days;
对两个查询的结果进行排序
51、select * from tb_name where days=(select max(days) from tb_name);
查询days最大的所有数据
52、select * from tb_name where name in (select name from tb_name where num=10);
查询数据,并且name在num=10的查询结果中的数据信息
53、select * from tb_name where name = any (select name from tb_name where num=10);
查询数据,并且name等于num=10的查询结果中的任意一个数据信息
54、select * from tb_name where (gender, name) = (select distinct gender,name from tb_name where name=’xxx’ and class=’xxx’ limit 1);
查询数据,gender, name等于子查询的结果中
55、select * from (select name,class,days from tb_name where days > 15) as temp where name like ‘xx%’;
从子语句查询结果中模糊查询数据,并给子语句查询结果取别名temp
56、select * from tb_name_1 where exists (select * from tb_name_2 where tb_name_1 .id_1=id_2);
查询tb_name_2中id_2的值在tb_name_1中id_1存不存在
57、select * from tb_name_1 inner join tb_name_2 on tb_name_1.id=tb_name_2.id;
从内连接的数据表中查询
58、select * from tb_name_1 outer join tb_name_2 on tb_name_1.id=tb_name_2.id;
从外连接的数据表中查询
59、select * from tb_name_1 natural left join tb_name_2
60、select * into outfile ‘e:/data.txt’ from tb_name;
将检索到的数据保存到E盘的data.txt文件中
61、delete from one, two using one join two on one.public_field=two.public_field where one_id=2;
删除one, two表中两个字段相等的并且one_id=2的数据;
62、create view ijon_info as select *, tc.id as tc_id, t.id as t_id, c.id as c_id from join_teacher_class as tc left join join_teacher as t on tc.t_id left join join_class as on tc.c_id=c.id;
创建连接查询的视图
63、create trigger tr_name after update on tb_name_1 for each row update tb_name_2 set num=num-20;
创建触发器,更新tb_name_1时tb_name_2的num-20
64、
delimiter $$ create trigger trigger_name after insert on tb_name_1 for each row begin update tb_name_2 set num=num+1; update tb_name_2 set count=count+20; end $ $ delimiter ; 语句块创建触发器
65、
delimiter $$ create function sayHello() returns varchar(20) begin return 'hello world!'; end $ $ delimiter ; 自定义函数
66、
delimiter $$ create function func1() returns varchar(20) begin if hour(now()) >=18 then return '晚'; else return '早'; end $ $ delimiter ; 自定义带分支结构的函数,判断早还是晚
67、
delimiter $$ create function func2() returns int begin set @i = 1; //自定义变量 set @sum = 0; while @i<=10 do set @sum = @sum + @i; set @i = @i + 1; end while; return @sum; end $ $ delimiter ;
68、
delimiter $$ create function func3() returns int begin set @i = 1; //自定义变量 set @sum = 0; w:while @i<=10 do if @i = 5 then leave w; end if; set @sum = @sum + @i; set @i = @i + 1; end while w; return @sum; end $ $ delimiter ; 自定义带循环体的函数,循环体内有终止循环的功能,对1—10求和
69、
delimiter $$ create function sayHello(user_name varchar(10)) returns varchar(20) begin return concat( 'hello ',user_name); //把字符串连接到user_name end $ $ delimiter ; 自定义带参数的函数
70、
delimiter $$ create function func4() returns int begin declare i int default 0; //自定义局部变量 declare total int default 0; while @i<=10 do set @ total = @ total + @i; set @i = @i + 1; end while; return @ total; end $$ delimiter ;
71、
1)、create table join_student (stu_id int not null auto_increment,
stu_no char(10),class_id int not null,stu_name varchar(10),
stu_info text,primary key (stu_id));
create table class (id int primary key not null auto_increment,
c_name char(7),room varchar(3));2)、 insert into join_student values (null, ‘php0331004’, 3, ‘zhangsan’, ‘some info’);
3)、学号自动加功能,获取当前班级内最大的学号(或得到则加1,或没有则从001开始)
4)、delimiter $$
create function sno(c_id int) returns char(10)
begin
declare s_no char(10); //保存当前班级内最大的学号,如果没有就是null
declare class_name char(7);
slect stu_no from join_student where class_id=c_id order by stu_no desc;
if isnull(s_no) then
select c_name from join_class where id_c_id into class_name;
return concat(class_name,’001’;);
else
return concat(left(s_no,7),lpad(right(s_no,3) + 1, 3, ‘0’));
end if;
end
$ $
delimiter ;
72、
delimiter $$ create function sname() returns char(2) begin declare first_name char(16) default '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨'; declare last_name char(10) default '甲乙丙丁戊己庚辛壬癸'; declare full_name char(2); set full_name = concat(substring(first_name,floor(rand()*16+1),1),substring(last_name,floor(rand()*10+1),1)); return full_name; $ $ delimiter ;