MYSQL数据库SQL语句集锦
*特别说明:filed代表数据表字段,conditions代表where之后的条件,tablename代表数据表名 []中括号内的内容代表 可有可无。
创建数据库
create database database;
删除数据库
drop database database
创建数据表
create table tablename(
`id` int unsigned auto_increment primary key, //把id设置为主键,数据类型是无符号int型,自动递增
//注:unsigned 只能紧跟在数据类型 后,顺序不能乱 unsigned相当于去掉数字负数部分 例:tinyint 默认取值范围 -128到127 unsigned 后 取值范围变 0到 255
`userid` int unsigned references foreigntable(foreignfiled), //把userid设置为无符号整型,并设置成外键。关联主键表(foreigntable)中的主键(foreignfiled)
`imgid` int unsigned not null default 1 comment '图片id', //把imgid 设置成无符号整数 且不为空 默认值为1 注释说明为 “图片id”
foreign key(imgid) references foreigntable(foreignfiled) //设置 imgid 为外键。 关联主键表(foreign_table)中的主键(foreign_filed) (下同)
)engine=myisam default charset=utf8 collate=utf8_general_ci //设置数据表引擎为myisam ,默认字符集为utf8 且字符集排序规则为 utf8_general_ci
删除数据表
drop table if exists tablename;
添加字段
alter table tablename add colum field datatype attributes //datatype :字段数据类型 attributes字段属性
eg: alter table test add column ceshi varchar(50) not null default '' comment '测试字段'
删除字段
alter table tablename drop colum field ;
eg:alter table test drop column ceshi;
修改字段属性
alter table tabename modify field char(10) default 0 comment "这是整数";
修改字段名称
alter table tabename change field mytest int unsigned not null default 1;
添加默认值
eg: alter table test alter column user_id set default 1;
删除默认值
alter table tabename alter column field drop default;
设置主键
alter table tabename add primary key(field1 [ ,field2,.. ] );
删除主键
alter table tablename drop primary key; //注意:如果表里的主键是具有自增长属性的;那么直接删除是会报错的
添加唯一索引
alter table tablename add constraint key_name unique(field); //key_name :索引名称 (下同)
添加外键索引
alter table tablename add constraint key_name foreign key(field) references foreign_table(foreign_field) ;
删除索引(外键、唯一)
alter table tablename drop index key_name
或
alter table tablename drop key key_name
检查约束 :注意,mysql 目前并不支持check检查约束。可以用以下方法,设置字段为枚举值
eg: alter table tabename modify sex enum("男","女");
修改表的存储引擎
alter table tabename engine=innodb
修改数据表的自增长值
alter table tabename auto_increment=100;
添加数据
insert [ into ] tablename (field1,feld2,...) values(值1,值2,值3,...) ,(值1,值2,值3,...),...
修改数据
update tablename set filed1=值1,feild2=值2,.... where conditisons
删除数据
delete from tablename [ where conditions ];
清空数据
truncate table tablename; 自增id会重置。
查询数据表所有
select * from tablename
统计查询(查询结果集中的数据条数)
select count(field) from tablename [ where conditions]
查询某个字段的最大、最小、平均、求和的值
select max(field) from tablename [where conditions];
select min(field) from tablename [where conditions];
select avg(field) from tablename [where conditions];
select sum(field) from tablename [where conditions];
排序查询
select * from tablename [ where conditions] order by field ; 从小到大排
select * from tablename [ where conditions] order by field desc; 从大到小排
限制查询
select * from tablename [ where conditions] [ order by field ] limit start,num //start代表数据集的开始位置,0为第一条数据的位置,依次往后为数据的位置。num代表限制数据集的数据条数。至少1条。
去重查询
select distinct filed1,field2,... from tablename [ where conditions] [ order by field ] [ limit start,num ] //代表field1,field2,....所有字段都重复时,才去除重复的数据条。
分组查询 ( group by field)
示例::select name, first_letter,sum(parent_id) as cid from mm_city where `parent_id` = 18 group by first_letter having cid>50 limit 0,1 //顺序不能乱。当有统计字段别名做条件时,不能用where,只能用 having。
模糊查询
select * from tablename where field like '%值%'; // % 代表任意多个任意字符
内连接查询
select tablename1.field1,tablename2.field2,tablename3.field3,... from tablename1
join tbalename2 on tablename1.field = tablename2.field
( join tbalename3 on tablename1.field = tablename3.field )
或
( join tbalename3 on tablename2.field = tablename3.field )
[ where tablename1.field = 值 ... ] //查询或作为条件的字段中,若所有表中任意两张表都有该字段,则必须要指明数据表。即在字段前用 表名连上点(.)
外连接
左联接:left join ;查询出来的数据,若有空值,则以在left join前面的表中的数据条数为准
select tablename1.field1,tablename2.field2,tablename3.field3,... from tablename1
left join tbalename2 on tablename1.field = tablename2.field
(left join tbalename3 on tablename1.field = tablename3.field )
或
(left join tbalename3 on tablename2.field = tablename3.field )
[ where tablename1.field = 值 ... ] //查询或作为条件的字段中,若所有表中任意两张表都有该字段,则必须要指明数据表。即在字段前用 表名连上点(.)
右联接:rightjoin ;查询出来的数据,若有空值,则以在left join后面的表中的数据条数为准
select tablename1.field1,tablename2.field2,tablename3.field3,... from tablename1
right join tbalename2 on tablename1.field = tablename2.field
(right join tbalename3 on tablename1.field = tablename3.field )
或
(right join tbalename3 on tablename2.field = tablename3.field )
[ where tablename1.field = 值 ... ] //查询或作为条件的字段中,若所有表中任意两张表都有该字段,则必须要指明数据表。即在字段前用 表名连上点(.)
常用数据库函数
length :返回字符串或列的数据的长度
eg: select length(city) as citylen from demo
lower/upper:返回字符串的小写/大写
eg: select upper(account) from admininfo;
replace :替换字符串
eg: select replace('sql server','sql','sql') 结果是'sql server'
power() 取数值的幂值
eg:select power(5,3) 结果是125
abs 返回绝对值
eg:select abs(-99) 结果是99
round 根据指定精度返回数值的四舍五入
eg: select round(3.1415926,3) 结果是3.142
数据库对象
存储过程
drop procedure if exists pr_multi; //如果存在名为pr_multi的存储过程,则删掉
create procedure pr_multi( out c int, a int, b int ) //创建名为pr_multi的存储过程(函数),第一个为数据类型int的输出参数,第二、三个分别为数据类型int的输入参数
begin //过程体开始标记
if a is null then //判断 a 参数是否为空,若果为空,
set a=10; //给 a赋值10
end if;
if b is null then //判断 b 参数是否为空,若果为空,
set b=20; //给 b 赋值20
end if;
set c=a*b; //将参数c设置为 a 和 b 的乘积
end //过程体结束标记
call pr_multi(@name,5,3); //调用存储过程pr_multi 用变量 name 接收输出参数 c ,给参数 a 传值 5,b传值 3
select @name //查询变量name的值。。 上述结果为 15
详情请看 https://www.cnblogs.com/bobi-php-blog/p/7501392.html
视图
create view viewname //viewname 视图名称 (下同)
as
selectblock //查询语句
eg:
create view myview as select name,age,sex from users where id > 10;
查询视图: select fields from viewname
触发器:
create trigger 自定义名称
after | before insert | update | delete on 表名 for each row
begin
sql语句集
end
eg:
create trigger mytrigger after insert on users for each row
begin
insert into depts(`deptname`,`create_at`) values('测试','2018-06-19 20:20:20');
update depts set create_at = current_timestamp where id=1827;
end
事件:
请参考:https://www.cnblogs.com/bobi-php-blog/p/7506306.html
推荐阅读
-
Oracle与MySQL的分页查询sql语句格式实例讲解
-
SQL Server 数据库管理常用的SQL和T-SQL语句
-
MySQL中SQL语句常见优化策略
-
.net(C#数据库访问) Mysql,Sql server,Sqlite,Access四种数据库的连接方式
-
SqlServer中用SQL语句附加数据库及修改数据库逻辑文件名
-
使用数据库客户端工具Oracle SQL Developer加载第三方驱动连接mysql的方法
-
用SQL语句查询数据库中某一字段下相同值的记录方法
-
mysql替换表中的字符串的sql语句
-
MySQL学习【第六篇sql语句下】
-
28_数据库_第28天(数据库、表及表数据、SQL语句)_讲义