Mysql常用命令操作小结
2016-10-01 15:30 Miss朱 阅读(306) 评论(0) 编辑 收藏 举报Mysql操作大全
一、概述
SQL(Structured Query Language)语言的全称是结构化查询语言。数据库管理系统通过SQL语言来管理数据库中的数据。
SQL语言分为三个部分:数据定义语言(Data DefinitionLanguage,简称为DDL)、数据操作语言(DataManipulation Language,简称为DML)和数据控制语言(Data Control Language,简称为DCL)。分别如下:
DDL语句:CREATE、ALTER、DROP
DML语句:update、insert、delete、select
DCL语句:是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句
MySQL是一个关系型数据库管理系统,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多互联网公司选择了MySQL作为后端数据库。
MySQL数据库的优点:
1、多语言支持:Mysql为C、C++、Python、Java、Perl、PHP、Ruby等多种编程语言提供了API,访问和使用方便。
2、可以移植性好:MySQL是跨平台的。
3、免费开源。
4、高效:MySql的核心程序采用完全的多线程编程。
5、支持大量数据查询和存储:Mysql可以承受大量的并发访问。
注:mysql的命令及相关操作,跟sql都极为相似。
二、MySQL程序常用命令
1、常用程序命令:
显示所有数据库:show databases;
选定默认数据库:use dbname;
显示默认数据库中所有表:show tables;
2、创建新据库
语法: create database 数据库名
例:CREATE DATABASE Students CHARACTER set utf8;
3、删除数据库
drop database 数据库名
例:Drop database 数据库名
4、创建表
语法:create table 表名(
列名1 列类型 [<列的完整性约束>],
列名2 列类型 [<列的完整性约束>],
... ... );
例:创建students表
use Students;
CREATE TABLE Students(
id INT(10) PRIMARY key auto_increment,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4),
age INT(10),
class VARCHAR(20),
addr VARCHAR(50)
);
列表约束:
• PRIMARY KEY 主码约束(主键)
• UNIQUE 唯一性约束
• NOT NULL 非空值约束
• AUTO_INCREMENT 用于整数列默认自增1
• UNSIGNED 无符号整数
• DEFAULT default_value 默认值约束
• DEFAULT cur_timestamp 创建新记录时默认保存当前时间
(仅适用timestamp数据列)
• ON UPDATE cur_timestamp 修改记录时默认保存当前时间
(仅适用timestamp数据列)
• CHARACTER SET name 指定字符集(仅适用字符串)
5、修改表操作
改表名:
ALTER TABLE 旧表名 RENAME [TO] 新表名 ;
Alter table school rename school2;
改表字段类型:
ALTER TABLE 表名 MODIFY 属性名 数据类型 ;
Alter table school modify school _name char(20);
加字段:
ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件]
[FIRST | AFTER 属性名2] ;
Alter table school add addr varchar(50) not null first;
加外键:
alter table 表名 add constraint FK_ID foreign key(
你的外键字段名) REFERENCES 外表表名(对应的表
的主键字段名);
alter table xiaodi add constraint FK_1 foreign
key(dage_id) REFERENCES dage(id);
删除外键约束:
ALTER TABLE 表名 DROP FOREIGN KEY 外键别名 ;
alter table xiaodi drop foreign key FK_1;
删字段:
ALTER TABLE 表名 DROP 字段名 ;
Alter table school drop addr;
清空表内容:
Truncate table;
Truncate students;
6、删除表
DROP TABLE 表名;
drop table school;
7、插入表内容
第一种方式是不指定具体的字段名。第二种方式是列出表
的所有字段。
1.INSERT语句中不指定具体的字段名
2.INSERT语句中列出所有字段
例1:
insert into teacher values('001','张三','11000000000');
insert into teacher values('002’,'李四','12000000000');
insert into teacher values('003','王五','13000000000');
INSERT INTO 表名(属性1, 属性2, … , 属性m)
VALUES(值1,值2, …, 值m);
例2:
insert into classes(class_no,class_name,department_name) values(null,'一年级', 'aaa
');
insert into classes(class_no,class_name,department_name) values(null,'一年级', 'aaa');
insert into classes(class_no,class_name,department_name) values(null,'二年级', 'ccc');
8、查询操作:
一般查询:
1.列出表的所有字段
2.使用“*”查询所有字段
select * from students;
多表查询:
多表连接可以通过join关键字来连接,也可以直接用关联表中相同的id来进行关联;
Join:
Left join:左连接, 连接两张表,以左边表的数据匹配右边表中的数据,如果左边
表中的数据在右边表中没有,会显示左边表中的数据。
Right join:右连接,连接两张表,以右边表的数据匹配左边表中的数据,如果左边
表中的数据在左边边表中没有,会显示右边表中的数据。
Inner join:内连接,连接两张表,匹配两张表中的数据,和前面两个不同的是只
显示匹配的数据。
select a.name 学生姓名,b.score 学生成绩 from students a left join score b on
a.id=b.student_id;
select a.name 学生姓名,b.score 学生成绩 from students a right join score b on
a.id=b.student_id;
select a.name 学生姓名,b.score 学生成绩 from students a INNER join score b on
a.id=b.student_id;
select a.name 学生姓名,b.score 学生成绩 from students a,score b where a.id=b.student_id;
带IN关键字的查询:
[ NOT ] IN ( 元素1, 元素2, …, 元素n )
select * from users where id in (1 ,2);
select * from users where id not in (1 ,2);
带OR的多条件查询:
条件表达式1 OR 条件表达式2 [ …OR 条件表达式n ]
其中,OR可以用来连接两个条件表达式。而且,可以
同时使用多个OR关键字,这样可以连接更多的条件表达。
select * from student where id=1 or id=2;
select * from student where id!=1 or id!=2;
带AND的多条件查询:
条件表达式1 AND 条件表达式2 [ … AND 条件表达式n ]
Select * from users where id >100 and sex = 2;
Select * from users where id >100 and sex = 2 and addr not null;
distinct来剃重:
select distinct phone from classes;
limit关键字限制条数:
Select * from users limit 5;
Select * from users limit 10,20;
BETWEEN AND的范围查询:
[ NOT ] BETWEEN 取值1 AND 取值2
Select * from students where score between 60 and 100;
LIKE的字符匹配查询:
[ NOT ] LIKE '字符串'
select * from student where name like '张_';
查询空值:
IS [ NOT ] NULL
Select * from users where addr is null;
Select * from users where sex is not null;
表结果排序:
ORDER BY 属性名 [ ASC | DESC ]
Select * from students where sex = '女' order by score;
聚合函数查询
COUNT()函数统计记录的条数:
SELECT COUNT(*) FROM employee ;
select count(*) 学生人数 from student;
SUM()函数求和函数
select sum(score) 总成绩 from choose;
select student.name 学生名称,sum(score.score) 学生总成绩 from student,score where student.id = score.student_id;
AVG()函数是平均值的函数
select student.name 学生名称,avg(score.score) 学生总成绩from student,score where student.id = score.student_id;
MAX()函数求最大值的函数
select student.name 学生名称,max(score.score) 学生总成绩 from student,score where student.id = score.student_id;
MIN()函数求最小值的函数
select max(score) 最高分,min(score) 最低分 from score;
group by子句:
GROUP BY关键字可以将查询结果按某个字段或多个字
段进行分组。字段中值相等的为一组。其语法规则如下:
GROUP BY 属性名 [ HAVING 条件表达式 ]
1.单独使用GROUP BY关键字来分组
2.GROUP BY关键字与集合函数一起使用
3.GROUP BY关键与HAVING一起使用
4.按多个字段进行分组
注:一般与聚合函数一起用
例如把学生表中的男生和女生分成两组。
select * from students GROUP BY sex;
having子句:
having子句用于设置分组或聚合函数的过滤筛选条件,having子句通常与group by子句一起使用。having子句语法格式与where子句语法格式类似,having子句语法格式如下。
Having 条件表达式
其中条件表达式是一个逻辑表达式,用于指定分组后的筛选条件。
例如查询1班男女学生的人数。
SELECT a.sex,count(a.id),b.class_name from students a ,class b
where a.id=b.student_id GROUP BY a.sex HAVING b.class_name ='一班';
union合并结果集:
语法:select 字段列表1 from table1 union [all] select 字段列表2 from table2...
union 与 union all 的区别:
当使用union时,MySQL 会筛选掉select结果集中重复的记
录(在结果集合并后会对新产生的结果集进行排序运算,
效率稍低)。而使用union all时,MySQL会直接合并两个
结果集,效率高于union。如果可以确定合并前的两个结
果集中不包含重复的记录,建议使用union all。
select name 姓名,sex 性别,phone 电话 from students UNION select
teacher_name,sex,mobile from teacher;
比较运算符:
通过这些比较运算符,可以判断表中的哪些记录是符合条件的。
1.运算符“=”
2.运算符“<>”和“!=”
3.运算符“>”
4.运算符“>=”
5.运算符“<”
6.运算符“<=”
三、索引
索引是创建在表上的,是对数据库表中一列或多列的值进行排序的一种结
构。索引可以提高查询的速度。
创建索引的语法格式:
– 创建普通索引:
create index 索引名称 on 表名(列)
alter table 表名 add index 索引名称 (列)
– 创建唯一索引:
create unique index 索引名称 on 表名(列名)
alter table 表名 add unique index 索引名称 (列)
例子:
– 给students 表的 phone加上唯一索引
– Create unqiue index st_phone on students(phone);
– 给students表的name加上普通索引
– Create index st_name on students(name);
– 给订单表中的订单状态和用户id加上组合索引
– Create index status_user on orders(status,user_id)
删除索引的语法格式:
DROP INDEX 索引名 ON 表名 ;
drop index complex_index on book;
四、视图
视图是从一个或多个表中导出来的表,是一种虚拟存在的表。
创建视图的语法格式
Create view 视图名称(视图列1,视图列2) as select 语句 创建一个视图,只显示bug表中的bugid,bug标题,bug状态,单表 视图
create view bug_view (id,title,status) as select id,title,bug_status from
bf_bug_info;
创建一个视图,显示bug表中的bugid,bug标题,bug状态,bug创
建者,归属产品,多表视图
create view bug_view_new (bug_id,title,tester,bug_status,product_name) as select
a.id,a.title,b.realname,a.bug_status,c.name from bf_bug_info a,bf_test_user
b,bf_product c where a.created_by=b.id and a.product_id=c.id;
修改视图:
CREATE OR REPLACE VIEW 语句修改
– 语法格式:
• CREATE OR REPLACE VIEW 视图名称 (列1,列2) as select语句
– 示例,把上面创建的视图修改成只显示10条的bug标题:
• create or replace view bug_view(title) as select title from
bf_bug_info limit 10;
• Alter view 语句修改
– 语法格式
• Alter view 视图名称(列1,列2) as select语句
– 示例,把上面创建的视图修改成只显示15条的bugid和标题 :
• alter view bug_view(id,title) as select id,title from
bf_bug_info limit 15;
删除视图:
DROP VIEW [ IF EXISTS] 视图名列表
举例,删除上面创建的两个视图
DROP VIEW IF EXISTS bug_view,bug_view_new;
五、存储过程和函数
创建存储过程和函数是指将经常使用的一组SQL语句的组合在一起,并将这些SQL语句当作一个整体存储在MySQL服务器中。
创建存储过程的基本形式如下:
delimiter $$;
CREATE PROCEDURE 名称(参数列表)
BEGIN
SQL语句块
End
$$;
delimiter;
例子:
delimiter $$;
CREATE PROCEDURE test_p()
begin
Select * from bf_bug_info limit 10;
End
$$;
Delimiter;
– Call test_p;
创建函数语法:
定义函数的格式如下:
create function 函数名( 变量1,变量2.....)
returns 数据类型
begin
......执行的程序代码
return 数据;
end;
例子:
delimiter $$;
create FUNCTION get_stuid(s_name varchar(20))
RETURNS int
begin
declare num int;
select id from students where name=s_name into num;
return num;
end
$$;
delimiter ;
调用 select get_stuid('aaa');
六、触发器
触发器(TRIGGER)是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。
创建触发器:
MySQL中,触发器触发的执行语句可能有多个。创建有多个执行语句的触发器的基本形式如下:
CREATE TRIGGER 触发器名 BEFORE | AFTER 触发
事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;
例子:在students表中删除一条数据后,从score表中也把它的成绩删除
delimiter $$;
create trigger del_score after delete
on students for each row
begin
delete from socre where student_id=old.id;
end;
$$;
delimiter ;
Delet from studens where id =1;
查看触发器:
show triggers语句
– show triggers;
show create 语句
– show create trigger 触发器名;
从information_schema查看
– SELECT * FROM information_schema.triggers where
TRIGGER_NAME='存储过程名称';
删除触发器:
DROP TRIGGER 触发器名;
七、事件
事件(event),它类似与定时任务(crontab),但内部机制却完全不同。你可以创建事件,它会在某个特定时间或时间间隔执行一次预先写好的SQL代码。通常的方式就是将复杂的SQL语句包装到一个存储过程中,然后调用一下即可。
创建事件:
语法格式
create event 事件名称 on schedule 执行频率
Starts 开始时间
ends 停止时间
do
Sql语句
实例:有一张临时表stu_tmp,写一个事件实现每30分钟删一下这个表里的数据
• delimiter $$;
• create event del_tmp on schedule every 30
MINUTE
• do
• delete from stu_tmp;
• $$;
• delimiter ;
查看事件:
show event语句
– show triggers;
• show create 语句
– show create event 事件名;
• 从information_schema查看
– select * from information_schema.EVENTS;
删除事件:
Drop event 事件名称;