W3School SQL 教程:http://www.w3school.com.cn/sql/index.asp菜鸟教程 --- SQL 教程:http://www.runoob.com/sql/sql-tutorial.html
MySQL是一个关系型数据库管理系统,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越,搭配 PHP 和 Apache 可组成良好的开发环境。现在主要介绍常用的MySQL命令,包括连接数据库,修改密码,管理用户,操作数据库,操作数据表,数据库备份等
MySQL 数据库一些操作
注意:你必须首先登录到MySQL中,以下操作都是在MySQL的提示符下进行的,而且每个命令以分号结束。
1、MySQL常用命令
create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库,不提醒。
mysqladmin drop database name 删除数据库前,有提示。
show tables; 列出数据库中的所有表
describe tablename; 表的详细描述.显示表结构. 等价 show columns from tableName;
select 中加上 distinct 去除重复字段
drop table MYTABLE; 删除表
delete from MYTABLE; 清空表
MySQL中SELECT命令类似于其他编程语言里的print或者write,你可以用它来显示一个字符串、数字、数学表达式的结果等等
select version(),current_date; 显示当前mysql版本和当前日期
select now(); 查询时间
select user(); 查询当前用户
select version(); 查询数据库版本
select database(); 查询当前使用的数据库
显示年月日
select dayofmonth(current_date);
select month(current_date);
select year(current_date);
SELECT "welecome to my blog!"; 显示字符串
select ((4 * 4) / 10 ) + 25; 当计算器用
拼接字符串
select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';
这里用到CONCAT()函数,用来把字符串串接起来。另外,AS 用来 给 结果列'CONCAT(f_name, " ", l_name)'起个 别名。
create temporary table zengchao(name varchar(10)); 创建临时表:(建立临时表zengchao)
create table if not exists students(……); 创建表时先判断表是否存在
create table table2 select * from table1 where 1<>1; 从已经有的表中复制表的结构
create table table2 select * from table1; 复制表
alter table table1 rename as table2; 对表重新命名
修改列的类型
alter table table1 modify id int unsigned; //修改列id的类型为int unsigned
alter table table1 change id sid int unsigned; //修改列id的名字为sid,而且把属性修改为int unsigned
创建索引
alter table table1 add index ind_id (id);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id); //建立唯一性索引
删除索引
drop index idx_id on table1;
alter table table1 drop index ind_id;
select concat(id,':',name,'=') from students; 拼接字符或者多个列(将列id与":"和列name和"="连接)
select * from students order by id limit 9,10; limit(选出10到20条)<第一个记录集的编号是0>
MySQL不支持的功能
事务,视图,外键和引用完整性,存储过程和触发器
update MYTABLE set sex="f" where name='hyq'; 更新表中数据
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
or
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
UPDATE 以新的值更新现存表中行的列。
SET 子句指出要修改哪个列和他们应该给定的值。
WHERE 子句如果被给出,指定哪个记录行应该被更新。否则,所有的记录行被更新。
如果 ORDER BY 子句被指定,记录行将被以指定的次序更新。
如果你指定关键词 LOW_PRIORITY,UPDATE 的执行将被延迟,直到没有其它的客户端正在读取表。
如果你指定关键词 IGNORE,该更新语句将不会异常中止,即使在更新过程中出现重复键错误。导致冲突的记录行将不会被更新。
如果在一个表达式中从 tbl_name 中访问一个列,UPDATE 使用列的当前值。
举例来说,下面的语句设置 age 列值为它的当前值加 1 :
mysql> UPDATE persondata SET age=age+1;
UPDATE 赋值是从左到右计算的。
举例来说,下列语句将 age 列设置为它的两倍,然后再加 1 :
mysql> UPDATE persondata SET age=age*2, age=age+1;
如果你设置列为其当前的值,MySQL 注意到这点,并不更新它。
UPDATE 返回实际被改变的记录行数目。
在 MySQL 3.22 或更新的版本中,C API 函数 mysql_info()返回被匹配并更新的记录行数目,以及在 UPDATE 期间发生的警告的数目。
在 MySQL 3.23 中,你可以使用 LIMIT # 来确保只有给定的记录行数目被更改。
如果一个 ORDER BY 子句被使用(从 MySQL 4.0.0 开始支持),记录行将以指定的次序被更新。这实际上只有连同 LIMIT一起才有用。
从 MySQL 4.0.4 开始,你也可以执行一个包含多个表的 UPDATE 的操作:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
注意:多表 UPDATE 不可以使用 ORDER BY 或 LIMIT。
2、修改 mysql中 root 的密码:
shell> mysql -u root -p
mysql> update user set password="xueok654123" where user='root';
mysql> flush privileges // 刷新数据库
mysql> use dbname; // 打开数据库:
mysql> show databases; // 显示所有数据库
mysql> show tables; // 显示数据库mysql中所有的表:先use mysql;然后
mysql> describe user; // 显示表mysql数据库中user表的列信息);
增加MySQL用户
格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"
增加一个用户user_1密码为123,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。
首先用以root用户连入MySQL,然后键入以下命令:
mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by "123";
增加的用户是十分危险的,如果知道了user_1的密码,那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了。
针对上面的问题,可以这样解决
增加一个用户user_2密码为123,让此用户只可以在localhost上登录,并可以对数据库aaa进行查询、插入、修改、
删除的操作(localhost指本地主机,即MySQL数据库所在的那台主机),这样用户即使用知道user_2的密码,
他也无法从网上直接访问数据库,只能通过MYSQL主机来操作aaa库。
mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123";
用新增的用户如果登录不了MySQL,在登录时用如下命令:
mysql -u user_1 -p -h 192.168.113.50 (-h后跟的是要登录主机的ip地址)
3、grant
创建一个可以从任何地方连接服务器的一个完全的超级用户,但是必须使用一个口令 password 做这个
mysql> grant all privileges on *.* to user@localhost identified by "password" with
增加新用户
格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY "password" WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY "password" WITH GRANT OPTION;
格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
示例:增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。
首先用以root用户连入mysql,然后键入以下命令:
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
如果希望该用户能够在任何机器上登陆mysql,则将localhost改为"%"。
如果你不想user1有密码,可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
删除授权:
mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;
创建一个用户custom在特定客户端it363.com登录,可访问特定数据库fangchandb
mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’
重命名表:
mysql > alter table t1 rename t2;
4、mysqldump
备份数据库
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
恢复数据库
shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql
如果只想卸出建表指令,则命令如下:
shell> mysqladmin -u root -p -d databasename > a.sql
如果只想卸出插入数据的sql命令,而不需要建表命令,则命令如下:
shell> mysqladmin -u root -p -t databasename > a.sql
那么如果我只想要数据,而不想要什么sql命令时,应该如何操作呢?
mysqldump -T./ phptest driver
其中,只有指定了-T参数才可以卸出纯文本文件,表示卸出数据的目录,./表示当前目录,即与mysqldump同一目录。
如果不指定driver 表,则将卸出整个数据库的数据。每个表会生成两个文件,一个为.sql文件,包含建表执行。
另一个为.txt文件,只包含数据,且没有sql指令。
5、可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。
例如,如果在文件my_file.sql 中存放有查询,可如下执行这些查询:
例如,如果您想将建表语句提前写在sql.txt中:
mysql > mysql -h myhost -u root -p database < sql.txt
6、创建数据库staffer
create database staffer;
7、下面的语句在mysql环境在执行
show databases; 显示用户拥有权限的数据库
use staffer; 切换到staffer数据库
show tables; 显示当前数据库中有权限的表
desc staffer; 显示表staffer的结构
8、创建测试环境
1)创建数据库staffer
mysql> create database staffer
2)创建表staffer,department,position,depart_pos
create table s_position
(
id int not null auto_increment,
name varchar(20) not null default '经理', #设定默认值
description varchar(100),
primary key PK_positon (id) #设定主键
);
create table department
(
id int not null auto_increment,
name varchar(20) not null default '系统部', #设定默认值
description varchar(100),
primary key PK_department (id) #设定主键
);
create table depart_pos
(
department_id int not null,
position_id int not null,
primary key PK_depart_pos (department_id,position_id) #设定复和主键
);
create table staffer
(
id int not null auto_increment primary key, #设定主键
name varchar(20) not null default '无名氏', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id) #设定唯一值
);
drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '深圳',
year date
); //建表结束
//以下为插入字段
insert into teacher values('','glchengang','深圳一中','1976-10-10');
insert into teacher values('','jack','深圳一中','1975-12-23');
注:在建表中
(1)将ID设为长度为3的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key
(2)将NAME设为长度为10的字符字段
(3)将ADDRESS设为长度50的字符字段,而且缺省值为深圳。varchar和char有什么区别呢,只有等以后的文章再说了。
(4)将YEAR设为日期字段。
如果你在mysql提示符键入上面的命令也可以,但不方便调试。
你可以将以上命令原样写入一个文本文件中假设为school.sql,然后复制到c:\下,并在DOS状态进入目录\mysql\bin,然后键入以下命令:
mysql -uroot -p密码 < c:\school.sql
如果成功,空出一行无任何显示;如有错误,会有提示。
3)删除
mysql>
drop table depart_pos;
drop table department;
drop table s_position;
drop table staffer;
drop database staffer;
9、修改结构
增加一个字段:alter table tabelName add column fieldName dateType;
增加多个字段:alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
mysql>
#表position增加列test
alter table position add(test char(10));
#表position修改列test
alter table position modify