常用的Mysql数据库操作语句大全整理
一、用户管理:
1、新建用户:
>create user name identified by 'ssapdrow';2、更改密码:
>set password for name=password('fdddfd');3、权限管理
>show grants for name; //查看name用户权限> grant select on db_name.* to name; //给name用户db_name的所有权限
> revoke select on db_name.* to name; //grant的反操作,去除权限;
二、数据库操作:
1、查看数据库:
>show databases;2、创建数据库:
>create database db_name; //db_name为数据库名3、使用数据库:
>use db_name;4、删除数据库:
>drop database db_name;三、创建表:
1、创建表:
>create table table_name(> id tinyint unsigned not null auto_increment, //id值,无符号、非空、递增——唯一性,可做主键。
> name varchar(60) not null
> score tinyint unsigned not null default 0, //设置默认列值
> primary key(id)
> )engine=innodb //设置表的存储引擎,一般常用innodb和myisam;innodb可靠,支持事务;myisam高效不支持全文检索
> default charset=utf8; //设置默认的编码,防止数据库中文乱码
如果有条件的创建数据表还可以使用
>create table if not exists tb_name(........2、复制表:
>create table tb_name2 select * from tb_name;或者部分复制:
>create table tb_name2 select id,name from tb_name;3、创建临时表:
>create temporary table tb_name(这里和创建普通表一样);4、查看数据库中可用的表:
>show tables;5、查看表的结构:
>describe tb_name;也可以使用:
>show columns in tb_name; //from也可以6、删除表:
>drop [ temporary ] table [ if exists ] tb_name[ ,tb_name2.......];实例:
>drop table if exists tb_name;7、表重命名:
>rename table name_old to name_new;还可以使用:
>alter table name_old rename name_new;四、修改表:
1、更改表结构:
>alter table tb_name add[change,rename,drop] ...要更改的内容...实例:
>alter table tb_name add column address varchar(80) not null;> alter table tb_name drop address;
> alter table tb_name change score score smallint(4) not null;
五、插入数据:
1、插入数据:
>insert into tb_name(id,name,score)values(null,'张三',140),(null,'张四',178),(null,'张五',134);这里的插入多条数据直接在后边加上逗号,直接写入插入的数据即可;主键id是自增的列,可以不用写。
2、插入检索出来的数据:
>insert into tb_name(name,score) select name,score from tb_name2;六、更新数据:
1、指定更新数据:
>update tb_name set score=189 where id=2;> update tablename set columnname=newvalue [ where condition ]
七、删除数据:
1、删除数据:
>delete from tb_name where id=3;八、条件控制:
1、where 语句:
>select * from tb_name where id=3;2、having 语句:
>select * from tb_name group by score having count(*)>23、相关条件控制符:
=、>、<、<>、in(1,2,3......)、between a and b、notand 、or
like()用法中 % 为匹配任意、 _ 匹配一个字符(可以是汉字)
is null 空值检测
九、mysql的正则表达式:
1、mysql支持regexp的正则表达式:
>select * from tb_name where name regexp '^[a-d]' //找出以a-d 为开头的name2、特殊字符需要转义。
十、mysql的一些函数:
1、字符串链接——concat()
>select concat(name,'=>',score) from tb_name2、数学函数:
avg、sum、max、min、count;3、文本处理函数:
trim、locate、upper、lower、substring4、运算符:
+、-、*、5、时间函数:
date()、curtime()、day()、year()、now().....十一、分组查询:
1、分组查询可以按照指定的列进行分组:
>select count(*) from tb_name group by score having count(*)>1;2、条件使用having;
3、order by 排序:
order by desc|asc =>按数据的降序和升序排列十二、union规则——可以执行两个语句(可以去除重复行)
十三、全文检索——match和against
1、select match(note_text)against('picaso') from tb_name;2、innodb引擎不支持全文检索,myisam可以;
十四、视图
1、创建视图
>create view name as select * from tb_name where ~~ order by ~~;2、视图的特殊作用:
a、简化表之间的联结(把联结写在select中);
b、重新格式化输出检索的数据(trim,concat等函数);
c、过滤不想要的数据(select部分)
d、使用视图计算字段值,如汇总这样的值。
十五、使用存储过程:
个人理解,存储过程就是一个自定义函数,有局部变量参数,可传入参数,可以返回值,不过这语法够呆滞的~~~
1、创建存储过程:
>create procedure pro(> in num int,out total int)
> begin
> select sum(score) into total from tb_name where id=num;
> end;
***这里的 in (传递一个值给存储过程),out(从存储过程传出一个值),inout(对存储过程传入、传出),into(保存变量)
2、调用存储过程:
>call pro(13,@total) //这里的存储过程两个变量,一个是in一个是out,这里的out也是需要写上的,不写会出错> select @total //这里就可以看到结果了;
3、存储过程的其他操作:
>show procedure status; //显示当期的存储过程> drop procedure pro; //删除指定存储过程
十六、使用游标:
对这个理解不是很懂,朋友多多指点哦~~~
1、游标的操作
>create procedure pro()> begin
> declare ordername cursor for
> select order_num from orders;
> end;
> open ordername; //打开游标
> close ordername; //关闭游标
十七、触发器:
触发器是指在进行某项指定操作时,触发触发器内指定的操作;
1、支持触发器的语句有delete、insert、update,其他均不支持
2、创建触发器:
>create trigger trig after insert on orders for each row select new.orser_name;> insert语句,触发语句,返回一个值
3、删除触发器
>drop trigger trig;十八、语法整理:
1、alter table(修改表)
alter table table_name( add column datatype [ null | not null ] [ constraints ]
change column datatype columns [ null | not null ] [ constraints ]
drop column,
。。。。
)
2、commit(处理事务)
>commit;3、create index(在一个或多个列上创建索引)
create index index_name on tb_name (column [ asc | desc ] , .......);4、create procedure (创建存储过程)
create procedure pro([ parameters ])begin
........
end
5、create table(创建表)
create table tb_name(column_name datetype [ null | not null ] [ condtraints] ,
column_name datetype [ null | not null ] [ condtraints] ,
.......
primary key( column_name )
)engine=[ innodb | myisam ]default charset=utf8 auto_increment=1 ;
6、create user(创建用户)
create user user_name [ @hostname ] [ identified by [ password ] 'pass_word' ];7、create view (在一个或多个表上创建视图)
create [ or replace ] view view_name as select。。。。。。8、delete (从表中删除一行或多行)
delete from table_name [where ......]9、drop(永久删除数据库及对象,如视图、索引等)
drop datebase | index | procedure | table | trigger | user | view name10、insert (给表添加行)
insert into tb_name [ ( columns,...... ) ] values(value1,............);使用select值插入:
insert into tb_name [ ( columns,...... ) ]select columns , ....... from tb_name [ where ...... ] ;
11、rollback(撤销一个事务处理块)
rollback [ to savapointname ];12、savepoint(为rollback设置保留点)
savepoint sp1;13、select (检索数据,显示信息)
select column_name,.....from tb_name [ where ] [ union ] [ rroup by ] [ having ] [ order by ]14、start transaction (一个新的事务处理块的开始)
start transaction15、update(更新一个表中的一行或多行)
update tb_name set column=value,......[ where ]