MySQL学习:主要学习这两大类
一、SQL语言:
1.DDL :数据定义语句,定义即为定义结构。主要有一下关键字:create,drop,alter,show
2.DML:数据操纵语句,即操纵具体数据。分为:insert、delete、select、update
3.DCL:数据数据库控制语句,即控制权限。分为:自主权限管理,强制权限管理。
二、MySQL内部机制:
存储引擎 索引 事物
存储过程 触发器
-------------------------------------
MySQL为C/S架构
首先服务器server:service mysql start //开启服务器
然后客户端:mysql -u root -p //开启客户端连接服务器
-------------------------------------
MySQL:是关系型数据库的应用语言
1.DDL:数据库定义语言 create、 drop、alter、desc
2.DML 数据库操纵语言:insert delete update selec
3.DCL :数据库控制语句 :用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。grant revoke
bash命令行:进入数据库:mysql -u root -p (在~目录下执行命令)
进入mysql可以看到以下信息:版本号,连接数,结尾方式,版权所属···
数据库的结构:一行一行的数据在一张表中,一个一个表构成一个库。
查看mysql库:
查看user表:
查看用户,主机,密码。
----------------------------------------------------------------------------------------------------
一:DDL 数据库定义语言 create drop alter show
(1).库
1.创建库
create database [if not exists] zdw;
2.删除库
drop database [if exists] test;
3.查询
Show databases;
4.使用(选择库):use database_name;
(2).表
0.查看当前库中所有表:show tables;
//xiaoqu库中有两张表:resitdent rootlist
1.创建表
create table tb_name(
字段名称 字段类型 [字段约束] [注释],
字段名称 字段类型 [字段约束] [注释],
......
);
//创建学生表
create table stu(
id varchar(20) primary key,
name varchar(10) not null,
age int not null,
sex enum("man","woman")
);
//创建教师表
create table teach(
id varchar(20) primary key,
name varchar(10) not null,
age int not null,
sex enum("man","woman")
);
//插入数据到教师表中
insert into teach values("t001","yang",18,"man"),
("t002",'su',17,"man"),
("t003",'he',16,"man");
2.查看表
a. desc tb_name; //查看表的字段属性
b.show create table tb_name; //查看表的创建过程
3.删除表
drop table tb_name;
4.修改表结构
1.修改字段类型
alter table stu modify id varchar(20);
2.修改字段名称
alter table stu change id mid varchar(20);
3.添加字段 after first;
alter table stu add score1 float first;
4.删除字段
alter table stu drop score1;
5.修改表名
alter table stu rename student;
二、DML 数据操纵语言 数据
insert delete update select
1.插入元素 load source //
insert into stu values('003','wangwu',19,"woman"),
('002','lisi',22,"woman");
insert into stu(mid,name,age) values("004","zhaoliu",17);
insert into stu(mid,name,age) values(" ","",17);
2.删除元素 truncate DDL (truncate table stu;)
delete from stu;
delete from stu where age = 17;
3.修改元素
update stu set name = "zhangsan";
update stu set mid = "007" where age = 17 and name = "";
4.查询
1.普通查询
select * from tb_name;
示例语句: select * from stu;
select mid,name,age,sex from stu;
2.去重查询 distinct
示例语句:select distinct age from stu;
3.排序查询 order by asc desc
示例语句:select distinct age from stu order by age;
4.分组 group by
select mid,sum(score)
from result
group by mid;
5.查询年龄大于等于20的学生的不及格成绩
select name, score
from stu,result
where stu.mid = result.mid and age >= 20 and score < 60;
多表 5.等值查询
6.连接查询
1.左外连接
select name,score
from
(select mid, name from stu where age >= 20) a
left join
(select mid, score from result where score < 60) b
on a.mid = b.mid
where score is not null;
2.右外连接
select name, score
from
(select mid, name from stu where age >= 20) a
right join
(select mid, score from result where score < 60) b
on a.mid = b.mid
where name is not null;
3.全外连接
select name, score
from
(select mid, name from stu where age >= 20) a
full join
(select mid, score from result where score < 60) b
on a.mid = b.mid
where name is not null;
4.内连接查询
select name, score
from
(select mid, name from stu where age >= 20) a
inner join
(select mid, score from result where score < 60) b
on a.mid = b.mid;
7.联合查询 union
select mid, name, age ,sex from stu
union all
select id, name, age ,sex from teach;
三、DCL 数据控制语言 权限管理
grant revoke
1.创建用户
create user usr_name@host;
2.授权
grant select on cy1201.* to u3;
3.回收权限
revoke select on cy1201.* from u3;
字段约束
主键 非空 唯一
外键
唯一
非空
默认为空
varchar(length) varchar(10) 不定长
char(length) char(10) 定长
-----------------------------------------------------------------------------------------------------------
c++项目中连接数据库:
//Database.h //头文件
class Database
{
public:
static Database* getDatabase();
bool Data_login(string name,string pw);
bool Data_registe(string name,string pw);//updata:maybe name exit...
bool Data_insert(string id,string name,string sex,int age);
private:
MYSQL * _mpcon ;//mysql对象,用于连接mysql数据库(连接后可以选择database)
static Database* _instance;
Database();
};
#include"Database.h"
Database* Database::_instance = NULL;
//单例模式
Database* Database::getDatabase()
{
if(NULL == _instance)
{
static pthread_mutex_t _mutex; //static -> to make pthread safe
pthread_mutex_init(&_mutex,NULL);
pthread_mutex_lock(&_mutex);
if(NULL == _instance)
{
_instance = new Database();
}
pthread_mutex_unlock(&_mutex);
pthread_mutex_destroy(&_mutex);
}
return _instance;
}
//构造函数
Database::Database()//constractor:build the mysql instance
{
this->_mpcon = mysql_init((MYSQL*)0);//初始化mysql的一个对象
//connect myql: ip user pw database(can not select) port 0 0
if(!mysql_real_connect(this->_mpcon,"127.0.0.1","root","123123",NULL,3306,NULL,0))
{
cout<<"mysql connect fail;"<<endl;
}
//select database 选择数据库
if(mysql_select_db(this->_mpcon,"xiaoqu"))
{
cout<<"select fail"<<endl;
}
}
//val["TYPE"]:login registe
bool Database::Data_login(string name,string pw)
{
char cmd[100] = "select* from rootlist where name='";//sql语句 写成字符串
strcat(cmd,name.c_str());
strcat(cmd,"';");//strcat the cmd
if(mysql_real_query(this->_mpcon,cmd,strlen(cmd)) )//让sql对象执行命令
{
cout<<"quary fail..."<<endl;
return false;
}
//该命令执行后,会有返回的结果
MYSQL_RES * _mpres = mysql_store_result(this->_mpcon);//create 结果集
bool result = false;
MYSQL_ROW _mprow;//结果集的一行
while(_mprow = mysql_fetch_row(_mpres))//
{
result = strcmp(_mprow[1],pw.c_str()) == 0;//_mprow[1]结果集中这一行的第二个元素
}
mysql_free_result(_mpres);//释放结果集
_mpres = NULL;
return result;
}
bool Database::Data_insert(string id,string name,string sex,int age)
{
if(true == Data_collect(id))
{
return false;//id exit can't insert
}
//age是int类型,需要转换成字符串,连接到命令中。
char buff[10];//itoa() Linux don't have(windows have) so we need sprintf()
sprintf(buff,"%d",age);//int convert to char/string
//insert into resident values('id','name','sex',age);//the hole cmd
char cmd[100] = "insert into resident values('";
strcat(cmd,id.c_str());
strcat(cmd,"','");
strcat(cmd,name.c_str());
strcat(cmd,"','");
strcat(cmd,sex.c_str());
strcat(cmd,"',");
strcat(cmd,buff);//age type is int -> convert to chat*
strcat(cmd,");");
if(mysql_real_query(this->_mpcon,cmd,strlen(cmd)) )
{
cout<<"quary fail..."<<endl;
return false;
}
return true;
}
MySQL 常用的网络资源
http://dev.mysql.com/downloads/是 MySQL 的官方网站,可以下载到各个版本的 MySQL 以及
相关客户端开发工具等。
http://dev.mysql.com/doc/提供了目前最权威的 MySQL 数据库及工具的在线手册。
http://bugs.mysql.com/这里可以查看到 MySQL 已经发布的 bug 列表,或者向 MySQL 提交 bug
报告。
http://www.mysql.com/news-and-events/newsletter/通常会发布各种关于 MySQL 的最新消息。