[计算机软件及应用]MySql55数据库命令总结

1、第一部分 数据库基本操作命令语句标准的sql语句：select * from 表名;数据库创建语句：creat database test;数据库登陆语句：mysql -h主机ip -u用户名 -p密码例如：远程登陆：mysql -h131.17.99.43 -uroot -p12345ok;本机登陆: mysql -uroot -p12345ok; 省略了主机ip地址查看当前mysql所有数据库show databases;使用某数据库use test;显示连接后的数据库的所有表show tables;查看连接了的数据库的某表里面的记录select * from person;select

2、id,password from person;查看表的结构：describe admin;数据库结构图：第二部分 数据库常见操作1 删除数据库drop database xue_xiao;2创建一个数据库create database xue_xiao; 3 显示所有数据库 show databases;4 创建一个简单的表(注意：先一定要使用某一个数据库,语句：use xue_xiao;)create table xue_sheng(name varchar(50);5查看正在使用的数据库的所有表show tables;6查看正在使用的数据库的某一个表结构：describe xue_she

3、ng; 或desc xue_sheng(常用);7在某张表中增加一个字段：alter table xue_sheng add nian_ling int;查看表结构 desc xue_sheng;8删除表的一个字段alter table xue_sheng drop nian_ling;9在表中插入一条记录insert into xue_sheng value(li ming);10 在表中插入中文字符insert into xue_sheng value(李明)11 删除一个表drop table xue_sheng;12 删除一个数据库drop database xue_xiao;13 创

4、建一个指定字符编码的数据库,即在创建数据库的时候指定编码(建议使用：utf-8)create database xue_xiao character set utf8 collate utf8_general_ci;注意：由于在创建数据库的使用指定了字符编码，所以在插入中文字符时，可以不用指定字符编码14 查看一个表的记录select * from user;或select user_id,user_name,user_password from user;第三部分 数据记录的基本操作1 创建一个完整的表create table xue_sheng(id int,xing_ming varch

5、ar(50),fen_shu int,xing_bie char(2);注意：int型默认长度为11，在创建时可以不指定，使用默认长度;创建时如果不指定，默认可以为空2往表中插入一条记录insert into xue_sheng values(1,张三,90,男);查看表中的所有记录select * from xue_sheng;3 查询表中的某一个字段select xing_ming from xue_sheng;4 模糊查询 like %关键字%查询姓李的所有记录select * from xue_sheng where xing_ming like 李%;5 多条件查询select *

6、from xue_sheng where xing_ming like 李% and xing_bie=女;6 进行排序查询order by 字段名 desc(降序) 或者 asc(默认升序);select * from xue_sheng order by fen_shu desc;select * from xue_sheng order by fen_shu asc;7 分页查询select * from xue_sheng limit 1,2;(从第1条开始(不包括第一条)，查询2条记录)8 更新指定记录update xue_sheng set xing_bie=男 where id=

7、3;9删除指定记录delete from xue_sheng where id=2;注意：不指定删除条件，则删除所有记录第四部分 常用函数和分组查询，表连接，嵌套查询1 查询总成绩select sum(fen_shu) from xue_sheng;2 求最大数select max(fen_shu) from xue_sheng;3 求最小数select min(fen_shu) from xue_sheng;4 求平均数select avg(fen_shu) from xue_sheng;5 统计一个表有多少记录(求和)select count(*) from xue_sheng;6 分组查

8、询select xing_bie, sum(fen_shu) from xue_sheng group by xing_bie;7 同时查询两张表select xing_ming ,ban_ming from xue_sheng,ban_ji;8 别名的使用select xing_ming,ban_ming from xue_sheng x,ban_ji b where x.bj_id=b.id;9 表连接查询 select xing_ming,ban_ming from xue_sheng x join ban_ji b on x.bj_id=b.id;10 子查询（嵌套查询）in() 或者

9、not in查询一年级1班的所有的学生信息 select * from xue_sheng where bj_id in(select id from ban_ji where ban_ming=年级(1)班);select id from ban_ji where ban_ming=一年级(1)班;的结果为 1select * from xue_sheng where bj_id in(1);第五部分 主键（primary key）外键（foreign key）1 在建立表的时候，建立一个自动增长的id作为主键drop table xue_sheng;注意：删除表和删除记录的语句不一样cre

10、ate table xue_sheng( id int(20) auto_increment not null primary key, xing_ming varchar(50), fen_shu int, xing_bie char(2), bj_id int);2 插入一条记录 insert into xue_sheng(xing_ming,fen_shu,xing_bie,bj_id) values(张三,90,男,1);3 一次插入多条记录insert into xue_sheng(xing_ming,fen_shu,xing_bie,bj_id) values(李四,70,男,2)

11、,(李小红,80,女,1),(陈小明,80,男,3);4 外键，数据参照的完整性，保持数据一致/一张表的外键必须是另一张表的主键alter table xue_sheng add constraint fk_xue_sheng foreign key(bj_id) references ban_ji(id);5 check约束alter bable xue_sheng add constraint ck_xue_sheng check(xing_bie=男 or xing_bie=女);alter table xue_sheng add constraint ck_xue_sheng chec

12、k(xing_bie in(男,女);alter table xue_sheng change xing_ming xing_ming varchar(50) not null;6 not null 非空alter table xue_sheng change xing_ming xing_ming varchar(50) not null;7 默认值alter table xue_sheng change xing_bie xing_bie char(2) default 男 not null;第六部分 索引index(快速查询)与视图view(安全，方便查询)视图是一个逻辑表，它并不存在硬

13、盘上。1创建视图create view v_xue_sheng as select xing_ming,yu_wen+shu_xue from xue_sheng; 2访问视图(但是不能删除，插入，更新视图里面的数据)select * from v_xue_sheng;3修改视图alter view v_xue_sheng as select xing_ming as 姓名,yu_wen+shu_xue as 总分 from xue_sheng ;select * from v_xue_sheng;4删除视图(和删除表是一样的)drop view v_xue_sheng;5查询两张表的数据6在

14、两张表上建立视图create view v_xue_sheng as select xing_ming,ban_ming from xue_sheng x,ban_ji b where x.bj=b.id;7两个表上的视图查询8查询当前数据库的表和视图show tables;9索引 index 用来快速查找特定值的记录。加快查询速度创建索引create index idx_xing_ming on xue_sheng(xing_ming);删除索引drop index idx_xing_ming on xue_sheng;10建立唯一索引(主键是一种唯一索引)create unique ind

15、ex idx_xing_ming on xue_sheng(xing_ming);11另一种创建，删除索引的方法alter table xue_sheng add index idx_xing_ming(xing_ming);alter table xue_sheng add unique idx_xing_ming(xing_ming);alter table xue_sheng drop index idx_xing_ming;第七部分 存储过程procedure 与存储函数function1创建存储过程delimiter / (/ 表示结束)delimiter /create proce

16、dure simpleproc(out param1 int)beginselect sum(yu_wen) into param1 from xue_sheng;end/delimiter ;2调用存储过程call simpleproc(a);select a;3带输入，输出参数的存储过程drop procedure if exists simpleproc;delimiter /create procedure simpleproc(in id int,out result1 varchar(100)beginselect xing_ming into result1 from xue_s

17、heng where xue_sheng.id=id;end/delimiter ;call simpleproc( 1 ,a);select a;4存储函数里面声明变量和赋值，逻辑判断drop procedure if exists simpleproc;delimiter /create procedure simpleproc(in in_name varchar(50),out result_1 varchar(150)begindeclare temp_1 int;declare temp_2 int default 60;select (yu_wen+shu_xue)/2 into

18、 temp_1 from xue_sheng where xing_ming=in_name;if temp_1 = temp_2 thenset result_1 = 及格;elseset result_1 = 不及格;end if;end/delimiter ;call simpleproc( 张三 ,a);select a;call simpleproc( 李四 ,a);select a;5存储函数delimiter /create function hello( s char(20) returns intdeterministicbegindeclare temp_sum int;s

19、elect yu_wen+shu_xue into temp_sum from xue_sheng where xing_ming=s;return temp_sum;end/delimiter ;select hello(张三);select hello(李四);第八部分 事务transaction与锁定lock事务的出现， 考虑这样的一个经典例子：张三账户转账100元到李四的账户1，张三账户减去100元2，李四账户增加100元1创建数据库create database yin_hang character set utf8 collate utf8_general_ci;use yin_h

20、ang;create table zhang_hao(id int(20) auto_increment not null primary key, xing_ming varchar(50) not null, jin_e int);insert into zhang_hao(xing_ming,jin_e) values(张三,100),(李四,100);start transaction;update zhang_hao set jin_e=0 where xing_ming=张三;rollback;commit;2回滚到自定义点start transaction;update zhang_hao set jin_e=0 wh