15 sql语言基础与qt里sqlite, mysql数据库的连接

sql: struct query language
关系型数据通用语法

数据库里还有触发器(对数据库里做某种操作时会触发执行函数), 储存器(储存数据会触发的函数)

数据表: 相当于描述一个结构体里所有的对象列表
一个数据库里可以有多个数据表, 数据表之间可有一对多关系
字段：表示一个数据表中记录的内容项：如一个员工的数据表可有字段：　id, name, tel

如要实现一个学生成绩信息管理系统，则可用一个数据库来存放相关的信息。
主要的信息分成两种： 一种学生的基本信息， 一种学生的成绩信息. 也就是可以分成两个数据表
学生信息： id, name, sex, age, address, tel //字段
1, “羊忘”, 妖, 11, “hell”, “110” //学生数据表中的一条记录就是描述一个学生的信息
学生有很多个， 每个学生的都会有相关的信息，需存放在学生数据表.
学生成绩信息: 学生的id, 科目，成绩

数据库， 数据表， 记录，字段

数据库： access, foxpro, sql server, interbase, mysql, oracle, sqlite
sqlite常用于嵌入式开发中

sql语法基本只分成几条语法：
　　 1. 查询, 统计
2. 增加数据表的记录
3. 修改数据表的记录
4. 删除数据表的记录
5. 创建/修改/删除数据表

//sqlite3的操作/
yum install sqlite*

在终端输入：sqlite3进入操作界面:
sqlite>

命令: .quit 退出sqlite操作界面
.databases 列出当前有哪些数据库
.tables 列出当前有哪些数据表
.headers on 显示出字段名

sql语句:
//创建一个名为student的数据表, 数据表里有两个字段id和name, id为integer类型, name为varchar类型
create table student(id integer, name varchar); //注意后面的’;'不可少
也可以 : create table student(id int, name char(20)); //相当于定义结构体类型
class student {
int id;
varchar name;
};

  //把数据表student删除掉
  drop table student;

  //修改数据表student, 多增加一个字段tel
  alter table student add tel varchar;
//修改数据表名
 alter table student rename to new_table_name

  //在数据表student里增加一条记录, id＝002, name="da li", tel="1331447788"
  insert into student values(002, 'da li', '1331447788');

//在数据表student里增加一条记录, 只设id,name字段的值
  insert into student (id,name) values(003, 'op li');	

  //查看数据表student所有的记录
  select *from student;

  //查看数据表student所有的记录，但只显示id和name字段的内容
  select id, name from student;

  //在数据表student里查看指定电话号码的记录信息
  select * from student where tel='1331447788';

  //在数据表里查看电话号码以133开头的记录信息
  select * from student where tel like '133%';

  //在数据表里查看电话号码以133开头的记录信息而且要求id为2
  select * from student where tel like '133%' and id=2;
  

  //删除数据表student里所有的记录
  delete from student;

  //删除指定id号的记录
  delete from student where id=1;

  //修改记录, 把原name="li"的记录修改为name="liu"
  update student set name='liu' where name='li';

  //统计数据表里的记录个数
  select count(*) from student;

  //统计数据表里name="liu"的记录个数
  select count(*) from student where name='liu';

  //累加数据表里age字段的和
  select sum(age) from student;

  //统计平均年龄
  select avg(age) from student;   

  //按字段id来分组统计score的和
  select sum(score) from scores group by id;
  select id, sum(score) from scores group by id;	

/
生成自己的数据库:
.backup main “/mydb.sql” //把main数据库备份成我们自己要用的数据库
attach database “/mydb.sql” as mydb; //加入我们的数据库

学生表: id(学号):integer name(名字):varchar(20) birthday:date sex:boolean tel:varchar(20) address:varchar

   //在mydb里创建学生表
  create table mydb.student (id integer, name char(20), birthday date, sex boolean, tel varchar(20), address varchar);

  insert into mydb.student values(1, 'xiao li', '2014-06-08', 1, '131453549394', 'hell'); 
  //日期可以用'1997-10-01', 也可以date("1997-10-01");

  //查询学生的年龄
  select id, name, date('now')-birthday from mydb.student;

  //按id的升序显示
  select *from student order by id;
  select *from student order by id desc; //倒序

  //查找sex为1, name以'x'字母开始而且id大于2的学生信息
  select *from student where sex=1 and name like 'x%' and id>2;
  //查找年龄大于10的学生信息
  select *from student where (date('now')-birthday) > 10;

  //查询时，根据sex字段里的内容显示是"男"或"女"
  select id, name, case when sex=1 then '男' else '女' end as sex from student;

/
学生科目成绩表(lesson):
　　　　id integer, subject(科目名称) varchar(20), score integer;
create table mydb.lesson (id integer, subject varchar(20), score integer);

 //统计每个学生的科目数量

select id, count(score) from mydb.lesson group by id;

 //得到平均值
 select id, avg(score) from mydb.lesson;

 //要显示出学生id, name, tel, 科目总成绩
 select student.id, name, tel, sum(score) from mydb.student left join mydb.lesson on student.id = lesson.id group by lesson.id

// student inner join lesson on student.id = lesson.id(只有这两个数据表的记录都有相应的信息才会显示);
// student left join lesson on student.id = lesson.id(student这个数据表里的记录不管lesson里面有没有相关的记录都会显示出来);

//　right join, //右边的数据表的记录不管左边的数据是否有相关的记录都会显示出来
// full join 两边数据表只要有一方存在的记录都会显示出来

// 查找第二大的内容
select max(temp) from mydata where temp!=(select max(temp) from mydata);
/４个数据表组合
student: id|name|birthday|sex|tel|address
scores: student_id|subject_id|score
sex: type|desc
subjects: id|desc

select student.id, name, sex.desc, subjects.desc, score from scores left join student on student.id=scores.student_id left join subjects on scores.subject_id=subjects.id left join sex on student.sex=sex.type;

/ 在qt里操作sqlite数据库///
qmake : QT += sql

1. 获取qt支持哪些数据库
   QSqlDatabase::drivers();

2. QSqlDatabase db = QSqlDatabase::addDatabase(“QSQLITE”); //设置使用的数据库驱动
   db.setDatabaseName("…/mydb.sql"); //指定数据库文件
   db.open(); //打开数据库

   db.tables(); //获取打开的数据库里的所有数据表名

3. QSqlQuery类对象来执行sql语法语句

   1).QSqlQuery query(“select *from student”); //当构造函数给sql语句时,会马上执行sql语句的

   2).QSqlQuery query; //先创建对象,不指定sql语句
   query.exec(“select *from student”);

   取值:
   while (query.next())
   {
   qDeubug() << query.value(0).toString(); //value(0)表示第0字段的内容
   }

4. QSqlTableModel操作数据表的数据模型, 可以直接修改数据表里的记录
   QTableView可显示数据模型

5. QSqlQueryModel操作SQL语句生成的只读数据模型

///
mysql 数据库(mariadb)

1. 安装mariadb数据库
   yum install mariadb*

2. 启用mysql 服务器端服务
   service mariadb restart
   chkconfig mariadb on

启动mysql服务失败时,执行命令:　mysql_install_db //初始化数据库的配置

/
设置mysql用户的登录密码:
mysqladmin -u root password 123456

在终端输入mysql进入mysql交互界面:
不带密码: 直接输入"mysql"

带密码: 输入"mysql -u root -p"
出输入密码界面后再输入密码

命令:
show databases;//显示出数据库
quit //退出mysql界面

use 数据库名;   //使用指定的数据库
show tables;  //显示使用的数据库里的数据表
describe 数据表名; //查看指定的数据表的字段

charset UTF8; //指定使用UTF8编码，否则会中文件乱码

create database mydb; //创建数据库mydb
drop database mydb;  //删除数据库mydb
drop table student; //删除数据表student;

create table student(id int, name char(20), birthday date, tel char(20), address char(250)) CHARSET=UTF8;  //创建数据表, 并支持中文字符编码
insert into student(id, name) values(0, "小李子"); //插入一条记录

create table student(id int primary key auto_increment, name char(20), birthday date, tel char(20), address varchar(255) default "不知道") CHARSET=UTF8; //创建数据表,指定关键索引, 指定默认值
//主键(primary key)可指定字段里的值是唯一的，不可重复。而且在数据表里是按此字段的值进行排序的，　当查找时就会提高效率。
//外键(foreign key)除了排序作用外，还可以指定与另一个表的主键进行关联起来，　外键字段的值是可重复，但字段的值必须是关联数据表里的主键值.

create table scores(id int, subject char(20), score float, foreign key (id) references student(id)) CHARSET=UTF8; //创建数据表时指定外键, 这里用于约束成绩表里的ID必须是存在学生表里的

查看重复ID的记录:
select *from student where id in (select id from student group by id having count(id) > 1) order by id;

删除重复id的记录
delete from student where id in (select t.id from (select id from student group by id having count(id) > 1)t);

alter table student add primary key(id); //增加数据表的主键，注意表里不能有多个重复id值
alter table scores add foreign key (id) references student (id); //创建表,再修改增加外键功能


//增加用户"who", 可以对mydb数据里所有数据表进行操作, 可在任何主机上登录
grant select,insert,update,delete on mydb.* to who@"%" identified by "123456";
登录时: mysql -D mydb -u who -p -h 192.168.250.250

//增加用户"who", ...可在本机上登录使用
grant select,insert,update,delete on mydb.* to who@"localhost" identified by "123456";

/qt里操作mysql数据库///
在CentOS7上需:
cp /usr/lib64/mysql/libmysqlclient.so.18.0.0 /usr/lib64/libmysqlclient_r.so.16

在qt里连接mysql:
QSqlDatabase db = QSqlDatabase::addDatabase(“QMYSQL”);

db.setHostName("192.168.250.123");
db.setDatabaseName("mydb");
db.setUserName("GG");
db.setPassword("1234");

if (!db.open())
...;

测试简例：
http://download.csdn.net/detail/jklinux/9843098