简介
MySQL是一种关系数据库管理系统，现在一般中小型网站的开发都选择MySQL作为网站数据库。
操作
1、启动MySQL服务
net start mysql
2、连接MySQL
mysql -h127.0.0.1 -uroot -p
3、退出MySQL
exit
Quit
4、关闭MySQL服务
net stop mysql
MySQL列类型
1、数值类型
SMALLINT: 2个字节
INT: 4个字节 // age int(10)
NTEGER:INT的同义词
BIGINT : 8个字节
FLOAT : 4个字节
DOUBLE : 8个字节 //score float(10,2)
2、字符串（字符）类型
CHAR：固定长度字符串 sex char(2)
VARCHAR:可变长度字符串 name varchar(20) 备注：VARCHAR使用起来较为灵活，CHAR处理速度更快
TEXT:非二进制大对象（字符）
BLOB:二进制大对象（非字符）
3、日期/时间类型
DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: YYYY-MM-DD HH:MM:SS
TIME:HH:MM:SS
YEAR:YYYY
SQL语句语法
SQL语言包含4个部分：
数据定义语言（如create,drop,alter等语句）
数据查询语言（select语句）
数据操纵语言（insert,delete,update语句）
数据控制语言（如grant,revoke,commit,rollback等语句）
数据操纵语言针对表中的数据，而数据定义语言针对数据库或表
1、数据定义语言
create database school; /*创建数据库*/
show databases; /*显示所有数据库select database()*/
use school; /*指定默认数据库*/
create table student( /*创建表*/
id int(10) primary key auto_increment, /*主键，自增*/
name varchar(8),
sex char(1),
score float(6,2)
);
show tables; /*显示当前库中表清单*/
/* 显示指定表结构show columns from student;*/
describe student;
show create table student; /*显示建表sql语句*/
drop table student; /*删除表*/
drop database school; /*删除数据库*/
2、数据操纵语言
insert into student values("张三", "t",87.5);
insert into student values(null,"张三","t",87.5);
//insert into student (name,sex,score) values("张三","t",87.5);
select * from student;
insert into student values(null,"李四","男",89);
alter table student modify sex char(2);
insert into student values(null,"李四","男",89);
select * from student;
update student set sex ="女";
select * from student;
update student set sex="男" where id=1;
select * from student;
update student set name="王五" ,score=100 where id=2;
select * from student;
delete from student where name="王五";
select * from student;
delete from student;
select * from student;
3、数据查询语言（select语句）
select * from student;
select id,name,score from student;
select * from student where id<5 and sex=“男”;
select count(*) from student ;
select max(score) from student;
select id ,name,score from student order by score desc;
select * from stu where name like "张%";
4、更改表结构语句
alter table student add birth date;
insert into student values(null,"赵六","男",100, now() );
insert into student values(null,"赵六","男",100, "1980-12-23");
alter table student change birth birthday date ;
alter table student modify sex char(4) not null ;
alter table student modify sex char(2) after birthday;
alter table student rename as stu;
alter table stu drop birthday;
5、多表查询和外键关联
create table person( id int primary key, name varchar(6), sex char(2), age int(10) );
insert into person values(1,"张三","男",45);
insert into person values(2,"李四","男",32);
insert into person values(3,"王五","女",32);
create table pet(
id int auto_increment ,
name varchar(6),
masterid int,
primary key(id),
constraint fk foreign key (masterid) references person(id)
);
insert into pet (name,masterid) values("happy",1);
insert into pet (name,masterid) values("lucky",1);
…………
select * from person p1,pet p2 where p1.id=p2.masterid;
select * from person p1,pet p2 where p1.id=p2.masterid and p1.id=1;
alter table pet drop foreign key fk;
alter table pet add constraint fk foreign key (masterid) references person(id) on delete cascade on update cascade;
update person set id=6 where id=2;
delete from person where id=6;
6、分页语句
select * from table limit (start-1)*limit,limit;
其中start是页码
limit是每页显示的条数。
导入导出
1、命令行操作
导入
方法1：mysql>source d:\dbname.sql
方法2：mysql -u root -p 数据库名 < dbname.sql
导出
导出整个数据库
mysqldump -u root -p 数据库名 > dbname.sql
只导出一个表
mysqldump -u root -p 数据库名 表名> dbname.sql
2、图形界面操作
Navicat
导入：运行SQL文件
导出：转储SQL文件
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)