MySQL学习笔记--01

SQL structed query language

连接MYSQL服务器：mysql -uroot -proot

	mysql -uroot -p --default_character_set=gbk; (影响数据的输入和输出)
	show variables like 'character%';

数据库的操作：创建，查看，修改，删除
	*创建：
		创建一个名称为mydb1的数据库。
			create database mydb1;
		创建一个使用utf-8字符集的mydb2数据库。
			create database mydb2 character set utf8;
		创建一个使用utf-8字符集，并带校对规则的mydb3数据库。
			create database mydb3 character set utf8 collate utf8_general_ci;
	*查看:
		显示所有数据库
			show databases;
		显示创建数据库的语句信息
			show create database mydb2;
	*修改:
		修改mydb1的字符集为gbk(不能修改数据库名)
			alter database mydb1 character set utf8;	
	*删除:
		删除数据库mydb2
			drop database mydb1;

表的操作：创建，查看，修改，删除
	*创建：
		根据实体类Person创建表person
			Person {
				int id;
				String name;
			}
			create table person(
				id int,
				name varchar(20)
			);
		mysql中的数据类型：
			bit 1位　但可以指定位数，如：bit<3>
			int 2字节 可以指定最大位数，如：int<4>　最大为4位的整数
			float 2个字节　可以指定最大的位数和最大的小数位数，如：float<5,2> 最大为一个5位的数，小数位最多2位
			double　4个字节　可以指定最大的位数和最大的小数位数，如：float<6,4> 最大为一个6位的数，小数位最多4位
			char　必须指定字符数,如char(5) 为不可变字符　即使存储的内容为'ab',也是用5个字符的空间存储这个数据
			varchar　必须指定字符数,如varchar(5) 为可变字符　如果存储的内容为'ab',占用2个字符的空间；如果为'abc',则占用3个字符的空间
			text: 大文本(大字符串)
			blob：二进制大数据　如图片，音频文件，视频文件
			date: 日期　如：'1921-01-02'
			datetime: 日期时间　如：'1921-01-02 12:23:43'
			timeStamp: 时间戳，自动赋值为当前日期时间
			
		创建一个员工表
			create table employee(id int,name varchar(20),sex bit,birthday date,salary double,entry_date date,resume text);
	*查看：
		查看所有的表：
			show tables;
		查看指定表的创建语句
			show create table employee;
			mysql表 名称区分大小写
		显示指定表的结构：
			desc employee;
	*删除：
		删除employee表
		drop table employee;
	*修改表：
		增加一个字段：alter table worker add column height double;
		修改一个字段：alter table worker modify column height float;
		删除一个字段：alter table worker drop column height;
		更改表名：rename table employee to worker;
		修改表的字符集:alter table worker character set gbk;
	
表数据的CRUD
	*C(create增加数据) Insert语句 
		新建Employee表并表中添加一些记录
			create table employee(
				id int,
				name varchar(20),
				sex bit,
				birthday date,
				salary double,
				entry_date date,
				resume text
			);
			create table employee(id int, name varchar(20),sex bit,birthday date,salary double,entry_date date,resume text);

			insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,'张三',1,'1983-09-21',15000,'2012-06-24','一个大牛');
			insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-09-21',10000,'2012-07-24','一个中牛');
			insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-09-21',7000,'2012-08-24','一个小牛');
			delete from employee where id=1 

			create table employee(	id int,name varchar(20),sex bit,birthday date,salary double,entry_date date,resume text);
	
	*U(update更新数据) Update语句 
		将所有员工薪水都增加500元。
			update employee set salary=salary+500;
		将王五的员工薪水修改为10000元，resume改为也是一个中牛
			update employee set salary=10000,resume='也是一个中牛' where name='王五';
	*D(drop删除数据) Delete语句 
		删除表中姓名为王五的记录。
			delete from employee where name='王五';
		删除表中所有记录。
			delete from employee; --可以有条件，但删除所有记录差了一点
		使用truncate删除表中记录。
			truncate employee;--无条件 效率高
	
	*R(Retrieve查找数据) Select语句	
		准备环境：
			create table student(
			id int,
			name varchar(20),
			chinese int,
			english int,
			math int
			);

			insert into student(id,name,chinese,english,math) values(1,'何东',80,85,90);
			insert into student(id,name,chinese,english,math) values(2,'权筝',90,95,95);
			insert into student(id,name,chinese,english,math) values(3,'何南',80,96,96);
			insert into student(id,name,chinese,english,math) values(4,'叶坦',81,97,85);
			insert into student(id,name,chinese,english,math) values(5,'何西',85,84,90);
			insert into student(id,name,chinese,english,math) values(6,'丁香',92,85,87);
			insert into student(id,name,chinese,english,math) values(7,'何北',75,81,80);
			insert into student(id,name,chinese,english,math) values(8,'唐娇',77,80,79);
			insert into student(id,name,chinese,english,math) values(9,'任知了',95,85,85);
			insert into student(id,name,chinese,english,math) values(10,'王越',94,85,84);

		查询表中所有学生的信息。
			select * from student;
		查询表中所有学生的姓名和对应的英语成绩。
			select name,english from student;
		过滤表中重复数据。
			select english from student;
			select DISTINCT english from student;
			select DISTINCT english,name from student;

			select english+chinese+math from student;
			select english+chinese+math as 总分 from student;
			select name,english+chinese+math as 总分 from student;

		在所有学生英语分数上加10分特长分。
			select name,english+10 from student;
		统计每个学生的总分。
			select english+chinese+math from student;
		使用别名表示学生分数
			select name,english+chinese+math as 总分 from student;
			select name,english+chinese+math 总分 from student;

		查询姓名为何东的学生成绩
			select * from student where name='何东';
		查询英语成绩大于90分的同学
			select * from student where english>90;
		查询总分大于250分的所有同学
			select * from student where english+chinese+math>250;

		查询英语分数在 85－95之间的同学。
			select * from student where english>=85 and english<=95;
			select * from student where english between 85 and 95;
		查询数学分数为84,90,91的同学。
			select * from student where math=84 or math=90 or math=91;
			select * from student where math in(84,90,91);
		查询所有姓何的学生成绩。
			select * from student where name like '何%';
		查询数学分>85，语文分>90的同学。
			select * from student where math>85 and chinese>90;

		对数学成绩排序后输出。
			select * from student order by math;
		对总分排序后输出，然后再按从高到低的顺序输出
			select * from student order by math+chinese+english desc;
		对姓何的学生成绩排序输出
			select * from student where name like '何%' order by math+chinese+english desc;
			select name, math+chinese+english from student where name like '何%' order by math+chinese+english desc;

		统计一个班级共有多少学生？
			select count(*) from student;
		统计数学成绩大于90的学生有多少个？
			select count(*) from student where math>90;
		统计总分大于250的人数有多少？
			select count(*) from student where math+chinese+english>250;

		统计一个班级数学总成绩？
			select sum(math) from student;
		统计一个班级语文、英语、数学各科的总成绩
			select sum(math), sum(chinese), sum(english) from student;
		统计一个班级语文、英语、数学的成绩总和
			select sum(math+chinese+english)from student;
			select sum(math)+sum(chinese)+sum(english) from student;

		求一个班级数学平均分？
			select avg(math) from student;
		求一个班级总分平均分
			select avg(math+chinese+english)from student;
			select avg(math)+avg(chinese)+avg(english) from student;

		求班级最高分和最低分
			select max(math+chinese+english),min(math+chinese+english) from student;
	
		综合性练习：为学生表，增加一个班级列，然后训练分组查询
		查出各个班的总分，最高分
			准备环境
				给表添加一个字段：alter table student add column class_id int;
				更新表：
					update student set class_id=1 where id<=5;
					update student set class_id=2 where id>5;
			select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;

		查询出班级总分大于1300分的班级ID
			select class_id from student group by class_id having sum(math+chinese+english)>1300;
			select class_id from student where sum(math+chinese+english)>1300 group by class_id ;
		note:where和group区别: 在wehre子句中不能使用聚合函数
		
		

时间和日期
		mysql> select year (now()), month(now()), day(now()) , date(now());
		+--------------+--------------+------------+-------------+
		| year (now()) | month(now()) | day(now()) | date(now()) |
		+--------------+--------------+------------+-------------+
		|         2014 |            9 |          7 | 2014-09-07  |
		+--------------+--------------+------------+-------------+
	
		select date_add(now(), INTERVAL 2 year) from dual;／／增加两年
		select charset('name')  employee;
		select date_add(now(), INTERVAL -1 day) 昨天, now() 今天, date_add(now(), INTERVAL +1 day) 明天; 

字符串相关函数
	select concat( charset('name'), 'aaaa') 自定义 from dual;


表的约束　　
	*定义主键约束　primary key:不允许为空，不允许重复
	*定义主键自动增长　auto_increment
	*定义唯一约束　unique
	*定义非空约束　not null
	*定义外键约束　constraint ordersid_FK foreign key(ordersid) references orders(id)
	*删除主键：alter table tablename drop primary key ;

	create table myclass
	(
		id INT(11) primary key auto_increment,
		name varchar(20) unique
	);
	
	create table student(
		id INT(11) primary key auto_increment, 
		name varchar(20) unique,
		passwd varchar(15) not null,
		classid INT(11),  #注意这个地方不要少逗号
		constraint stu_classid_FK  foreign key(classid) references myclass(id)
	);
	

		+-------+-------+------+------------------------+
	内连接
		只返回满足连接条件的数据（两边都有的才显示）。
	select e.*, d.*
	from emp e
	inner join dept d
	on e.deptno=d.deptno
		-- 也可以省略inner关键字。

	select e.*, d.*
	from emp e  inner join dept d
	on e.deptno=d.deptno
		
	左外连接
		左边有值才显示。
 		select e.empno, e.ename, e.sal, d.dname
		from emp e
		left outer join dept d
		on e.deptno=d.deptno
		-- 也可以省略outer关键字
		

	右外连接
		右边边有值才显示。
		select e.*, d.*
		from emp e		
		right outer join dept d
		on e.deptno=d.deptno
		-- 也可以省略outer关键字
	
	满外联接（MYSQL目前不支持这样）
		任一边有值就会显示。
		select e.*, d.*
		from emp e
		full outer join dept d		
		on e.deptno=d.deptno
		-- 也可以省略outer关键字
	可以这样模拟满外连接
		select * from emp left outer join dept on emp.deptno=dept.deptno
		union
		select * from emp right outer join dept on emp.deptno=dept.deptno
	
	交叉连接：
		叉集，就是笛卡尔积
		select e.*, d.*
		from emp e
		cross join dept d
		-- 没有连接条件
====================================================

eg:查询员工信息,员工号,姓名,月薪,部门名称
	select e.empno, e.ename, e.sal, d.dname
	from emp e, dept d
	where e.deptno=d.deptno

	select e.empno, e.ename, e.sal, d.dname
	from emp e inner join dept d  -- 逗号join
 	on e.deptno=d.deptno    -- where on

//显示所有部门信息
//显示各个部门的部门人数
	select d.deptno 部门号, d.dname 部门名称,count(e.empno) 人数
	from emp e, dept d
	where e.deptno(+)=d.deptno
	group by d.deptno, d.dname

	select d.deptno 部门号, d.dname 部门名称,count(e.empno) 人数
	from emp e right outer join dept d
	on e.deptno=d.deptno
	group by d.deptno, d.dname

自连接: -- 查询员工信息 ,老板信息
	显示:   ****的老板是****
select e.ename , b.ename
	from emp e, emp b 
	where e.mgr=b.empno

select concat ( concat(e.ename, '的老板是'), b.ename)
	from emp e, emp b 
	where e.mgr=b.empno


select e.ename, ifnull(b.ename,'他自己')
	from emp e left outer join emp b 
	on e.mgr=b.empno


select concat(e.ename, '的老板是', ifnull(b.ename,'他自己'))
from emp e left outer join emp b 
on e.mgr=b.empno


select concat ( concat(e.ename, '的老板是'), ifnull(b.ename,'他自己'))
	from emp e left outer join emp b 
	on e.mgr=b.empno
+------------------------------------------------------------------------+
| concat ( concat(e.ename, '的老板是'), ifnull(b.ename,'他自己'))        |
+------------------------------------------------------------------------+
| SMITH的老板是FORD                                                      |
| ALLEN的老板是BLAKE                                                     |
| WARD的老板是BLAKE                                                      |
| JONES的老板是KING                                                      |
| MARTIN的老板是BLAKE                                                    |
| BLAKE的老板是KING                                                      |
| CLARK的老板是KING                                                      |
| SCOTT的老板是JONES                                                     |
| KING的老板是他自己                                                     |
| TURNER的老板是BLAKE                                                    |
| ADAMS的老板是SCOTT                                                     |
| JAMES的老板是BLAKE                                                     |
| FORD的老板是JONES                                                      |
| MILLER的老板是CLARK                                                    |
+------------------------------------------------------------------------+


aaaaddd    ccccbbbbbb
aaaaddd    ccccbbbbbb
aaaaddd    ccccbbbbbb
aaaaddd    ccccbbbbbb

方法:按住alt键以后,鼠标拖动,选中一个矩形区域
	
#include<stdio.h>
#include<string.h>
#include<stdlib.h>
#include<mysql/mysql.h>

int main()
{
	int ret=0;
	MYSQL mysql;
	MYSQL *connect=NULL;
	
	connect= mysql_init(&mysql);
	if(connect ==NULL)
	{
		ret=mysql_errno(&mysql);
		printf("fun mysql init() erro \n");
		return ret;
	}
	printf("fun mysql init() success \n");
	//MYSQL *mysql_real_connect(MYSQL *mysql,const char *host,const char *user,const char *passwd,
	//const char *db,unsigned int port,const char *unix_socket,unsigned long client_flag);
	
	connect=mysql_real_connect(connect,"localhost","root","dreamer","mydb61",0,NULL,0);
	if(connect ==NULL)
	{
		ret=mysql_errno(&mysql);
		printf("fun mysql connect() erro \n");
		return ret;
	}
	printf("fun mysql connect() success \n");
	
	//查询
	const char *query="select *from dept";
	ret=mysql_query(&mysql,query);
	if(ret !=0)
	{
		printf("fun mysql query() erro \n");
		return ret;
	}
	printf("fun mysql query() success \n");
	
	//获取结果集合
	MYSQL_RES *result=mysql_store_result(&mysql);
	unsigned int num=mysql_field_count(&mysql);
	MYSQL_FIELD *fields=mysql_fetch_fields(result);
	for(i=0;i<num;i++)
	{
		printf("%s\t",fields[i].name);
	}//打印表头
	
	MYSQL_ROW row=NULL;
	while(row=mysql_fetch_row(result))
	{
		for(i=0;i<num;i++)
		{
		printf("%s\t",row[i]);	
	  }
	  printf("\n");
	}
	mysql_free_result(result);

	
	mysql_close(&mysql);
	printf("hello...\n");
	return ret;	
}



//-I/usr/include/ -L/usr/lib/i386-linux-gnu/ -lmysqlclient