一、安装
1、安装mysql服务端
sudoapt-get install mysql-server
验证mysql -u root -p密码
2、安装mysql开发包
sudoapt-get install libmysqlclient-dev
3、安装图形开发界面
sudoapt-get install mysql-workbench
二、数据类型
数据类型种类、数值列类型
1 .创建表
1、创库和表:造库->进入库->造表->添加元素
show databases;//查看当前有那些库
createdatabases www;//创建一个名为www的数据库
usewww;//进入www库
例如:
createtable data11(showtime time);//进入www库以后在www库里创建一个名叫data11的表,有一列列名为showtime 类型为time
showtables;//看表建好没有
insertinto data11 values('11:11:11'),('11:11');//往data11表里添加内容添加了两行
select* from data11;//看一下添加在data11表中的内容
descdata11;//描述表
2、创建表data12
createtable data12(f_data date,f_time time);//创建一个表叫data12,有两列第一列名为f_data 类型为date 第二列为f_time类型为time
insertinto data12 values('2016-8-8','12:1:1');//往data12表里面添加元素,第一列为2016-8-8,第二列为12:1:1
insertinto data12 (f_data) values('2016-1-1');//只往第一列f_data里插值2016-1-1,第二列的值默认为NULL
3、创建表data13
createtable data13(num1 int,num2 int usigned);//第一列num1为int型第二列num2为无符号int型,如果添加表元素时第二列添加为-1,则系统会自动转化为0,有警告
createtable data13(num1 int null,num2 int not null);//第二列必须要添加元素不能为空
createtable data13(num1 int,num2 int default 18);//第二列不添加时默认为18
4、创建表data14
createtable data14(num1 int unique,num2 int);//第一列num1里不允许重复添加,即num1里的元素不能重复
5、创建表15
createtable data15(num1 int not null unique auto_increment,num2 int default 18);//第一列num1 必须添加 不重复 自增长
mysql>insert into data18 (num2) values(100);//添加四次之后如下:
mysql>select * from data18;
+----+------+
|id | num2 |
+----+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 100 |
+----+------+
2.删除表
a、删除表中所有内容
mysql>delete from data18;
删除之后在添加一个:mysql> insert into data18 (num2) values(100);
再查看:
mysql>select * from data18;
+----+------+
|id | num2 |
+----+------+
| 5 | 100 |
+----+------+//id是5,之前设置的auto_increment接着自增
b、删除表
mysql>drop table data18;//这样删除之后data18表就不存在了
c、删除库
mysql>drop database www;
d、删除一个不存在的表会报错若这样写则只有警告:mysql> drop table if exists data18;
3.更改表结构(alter)
在原有的表中多添加一列:
1、mysql> alter table data17 add num3 int;//在最后面插入一列num3
mysql>alter table data17 add num5 int after num2;//在num2后面插如一列num5
mysql>alter table data17 add num4 int first;//在最前面插入一列num4
mysql>desc data17;
+-------+---------+------+-----+---------+-------+
|Field | Type | Null | Key | Default |Extra |
+-------+---------+------+-----+---------+-------+
|num4 | int(11) | YES | |NULL | |
|num1 | int(11) | YES | UNI | NULL | |
|num2 | int(11) | YES | |NULL | |
|num5 | int(11) | YES | |NULL | |
|num3 | int(11) | YES | |NULL | |
+-------+---------+------+-----+---------+-------+
2、mysql> alter table data17 alter num4 set default 18;
mysql>desc data17;
+-------+---------+------+-----+---------+-------+
|Field | Type | Null | Key | Default |Extra |
+-------+---------+------+-----+---------+-------+
|num4 | int(11) | YES | |18 | |
|num1 | int(11) | YES | UNI | NULL | |
|num2 | int(11) | YES | |NULL | |
|num5 | int(11) | YES | |NULL | |
|num3 | int(11) | YES | |NULL | |
+-------+---------+------+-----+---------+-------+
3、mysql> alter table data17 change num2 name char(20);//将num2改名为name
mysql>select * from data17;
+------+------+------+------+------+
|num4 | num1 | name | num5 | num3 |
+------+------+------+------+------+
|NULL | 1 | 100 | NULL | NULL |
|NULL | 2 | 100 | NULL | NULL |
+------+------+------+------+------+
4、mysql> alter table data17 drop num4;//将data17表里名字为num4删除
5、mysql> alter table data17 rename as yin17;//将data17表改名为yin17
6、mysql> create table day17(num int,name char(20)) defaultcharset=utf8;//第二列可以输入汉字(charset=utf8)
mysql>select * from day17;
+------+--------+
|num | name |
+------+--------+
| 20 | 你好 |
+------+--------+
三、主键和外键
1、先建主键才能建外键
mysql> create table student(
-> sid int not null auto_increment,
-> name varchar(20) not null,
-> primary key(sid)
-> );//主键
Query OK, 0 rows affected (0.10 sec)
mysql> create table score(
-> cid int not null auto_increment primary key,
-> score int,
-> sid int,
-> foreign key(sid) references student(sid)
-> );//外键
2、先添加主键,在添加外键
mysql>insert into student (name) values('yin');
mysql>insert into score values(1,98,1);
3、删除时先删除外键在删除主键
4、比较符:注:任何任何数和NULL相比结果为NULL
a、mysql> select 'x'<>'y';//x不等于y
+----------+
|'x'<>'y' |
+----------+
| 1 |
+----------+
b、mysql> select 7 in (6,7,8,9,0);//7在6 7 8 9 0之间是对的结果为1
+------------------+
|7 in (6,7,8,9,0) |
+------------------+
| 1 |
+------------------+
c、<=>安全等于
mysql>select 2=NULL,2<=>Null
->;
+--------+----------+
|2=NULL | 2<=>Null |
+--------+----------+
| NULL | 0 |
+--------+----------+
1row in set (0.00 sec)
mysql>select 0=NULL,0<=>Null;
+--------+----------+
|0=NULL | 0<=>Null |
+--------+----------+
| NULL | 0 |
+--------+----------+
1row in set (0.00 sec)
mysql>select NULL=NULL,NULL<=>Null;
+-----------+-------------+
|NULL=NULL | NULL<=>Null |
+-----------+-------------+
| NULL | 1 |
+-----------+-------------+
d、通配符like
mysql>select 'yin says hello' like '%ll%';//%表示任意长的字符,此句表示ll前和ll后所有字符
+------------------------------+
|'yin says hello' like '%ll%' |
+------------------------------+
| 1 |
+------------------------------+
5、更新表中的数据update
a、修改某一列的数据:mysql> update students set semail='@qq.com';
mysql> select * from students;
+------+---------+----------+--------+---------+------+
| sode | sname | saddress | sgrade | semail | ssex |
+------+---------+----------+--------+---------+------+
| 1| 女生1 | 未知 | 75 | @qq.com | 0 |
| 2| 女生2 | 未知 | 78 | @qq.com | 0 |
| 3| 女生3 | 未知 | 88 | @qq.com | 1 |
| 4| 女生4 | 未知 | 89 | @qq.com | 0 |
| 5| 女生1 | 未知 | 88 | @qq.com | 0 |
| 6| 女生2 | 未知 | 99 | @qq.com | 1 |
| 7| 女生3 | 未知 | 78 | @qq.com | 0 |
| 8| 女生4 | 未知 | 88 | @qq.com | 0 |
| 9| 女生1 | 未知 | 68 |@qq.com | 1 |
| 10| 男生1 | 未知 | 68 | @qq.com | 1 |
| 11| 男生2 | 未知 | 76 | @qq.com | 1 |
+------+---------+----------+--------+---------+------+
b、mysql> update students setsemail='@hotmail.com' where sname="女生1";//将女生1的smail更新为@hotmail.com
mysql> select * from students;
+------+---------+----------+--------+--------------+------+
| sode | sname | saddress | sgrade | semail | ssex |
+------+---------+----------+--------+--------------+------+
| 1| 女生1 | 未知 | 75 | @hotmail.com | 0 |
| 2| 女生2 | 未知 | 78 | @qq.com | 0 |
| 3| 女生3 | 未知 | 88 | @qq.com | 1 |
| 4| 女生4 | 未知 | 89 | @qq.com | 0 |
| 5| 女生1 | 未知 | 88 | @hotmail.com | 0 |
| 6| 女生2 | 未知 | 99 | @qq.com | 1 |
| 7| 女生3 | 未知 | 78 | @qq.com | 0 |
| 8| 女生4 | 未知 | 88 | @qq.com | 0 |
| 9| 女生1 | 未知 | 68 | @hotmail.com | 1 |
| 10| 男生1 | 未知 | 68 | @qq.com | 1 |
| 11| 男生2 | 未知 | 76 | @qq.com | 1 |
+------+---------+----------+--------+--------------+------+
c、mysql> update students setsgrade=sgrade+2 where sgrade<90;//成绩sgrade小于90的都加2
d、删除表中某行数据
mysql>delete from students where sgrade=77;
mysql>select * from students;
+------+---------+----------+--------+--------------+------+
|sode | sname | saddress | sgrade |semail | ssex |
+------+---------+----------+--------+--------------+------+
| 2 | 女生2 | 未知 | 80 | @qq.com | 0 |
| 3 | 女生3 | 未知 | 90 | @qq.com | 1 |
| 4 | 女生4 | 未知 | 91 | @qq.com | 0 |
| 5 | 女生1 | 未知 | 90 | @hotmail.com | 0 |
| 6 | 女生2 | 未知 | 99 | @qq.com | 1 |
| 7 | 女生3 | 未知 | 80 | @qq.com | 0 |
| 8 | 女生4 | 未知 | 90 | @qq.com | 0 |
| 9 | 女生1 | 未知 | 70 | @hotmail.com | 1 |
| 10 | 男生1 | 未知 | 70 | @qq.com | 1 |
| 11 | 男生2 | 未知 | 78 | @qq.com | 1 |
+------+---------+----------+--------+--------------+------+
6、查看
mysql>select sname from students;//相对路径
+---------+
|sname |
+---------+
|女生3 |
|女生4 |
|女生1 |
|女生2 |
|女生3 |
|女生4 |
|女生1 |
|男生1 |
|男生2 |
+---------+
9rows in set (0.00 sec)
mysql>select students.sname from www.students;//绝对路径或者mysql> select students.sname from students;
+---------+
|sname |
+---------+
|女生3 |
|女生4 |
|女生1 |
|女生2 |
|女生3 |
|女生4 |
|女生1 |
|男生1 |
|男生2 |
+---------+
查询:
mysql>select * from students where sname='女生1';
+------+---------+----------+--------+--------------+------+
|sode | sname | saddress | sgrade |semail | ssex |
+------+---------+----------+--------+--------------+------+
| 5 | 女生1 | 未知 | 90 | @hotmail.com | 0 |
| 9 | 女生1 | 未知 | 70 | @hotmail.com | 1 |
+------+---------+----------+--------+--------------+------+
mysql>select sname,sode,ssex,sgrade from students where sname='女生1';
+---------+------+------+--------+
|sname | sode | ssex | sgrade |
+---------+------+------+--------+
|女生1 | 5| 0 | 90 |
|女生1 | 9| 1 | 70 |
+---------+------+------+--------+
mysql>select distinct sname from students;//distinct去重
+---------+
|sname |
+---------+
|女生3 |
|女生4 |
|女生1 |
|女生2 |
|男生1 |
|男生2 |
+---------+
7、排序:
mysql>select *from students order by sgrade desc;//表中数据sgrade降序排序
mysql>select *from students order by sgrade;//默认升序排序
mysql>select *from students order by semail,sgrade desc;//表中数据先按email排序之后在按sgrade排序
显示:mysql> select *from studentslimit 2,3;//从第三行开始显示3行
mysql>select *from students limit 2;//从第一行开始显示2行
mysql>select *from students where sgrade between 60 and 80;//显示分数60到80之间学生的信息
mysql>select *from students order by sgrade desc limit 0,3;//显示分数最高的三个学生的信息
mysql>select sode as 'scode' from students;//将sode列一scode名字显示
+-------+
|scode |
+-------+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+-------+
===========================================day02========================================
mysql> select *from students;
+------+---------+----------+--------+--------------+------+
| sode | sname | saddress | sgrade | semail | ssex |
+------+---------+----------+--------+--------------+------+
| 3| 女生3 | 未知 | 90 | @qq.com | 1 |
| 4| 女生4 | 未知 | 91 | @qq.com | 0 |
| 5| 女生1 | 未知 | 90 | @hotmail.com | 0 |
| 6| 女生2 | 未知 | 99 | @qq.com | 1 |
| 7| 女生3 | 未知 | 80 | @qq.com | 0 |
| 8| 女生4 | 未知 | 90 | @qq.com | 0 |
| 9| 女生1 | 未知 | 70 | @hotmail.com | 1 |
| 10| 男生1 | 未知 | 70 | @qq.com | 1 |
| 11| 男生2 | 未知 | 78 | @qq.com | 1 |
+------+---------+----------+--------+--------------+------+
四、统计函数
mysql>select sum(sgrade) from students;//sum()对成绩sgrade求和
mysql>select avg(sgrade) from students;//avg()对成绩求平均值
mysql>select count(sgrade) from students;//count()数表中总共有多少行
mysql>select count(*) from students;//count(*)数表中总共有多少行,即查询学生的总数
mysql>select avg(sgrade) from students where sname='女生1';
|avg(sgrade) |
+-------------+
| 80.0000 |
+-------------+
mysql>select avg(sgrade),sname from students group by sname;//先分组,再对每组求平均值
+-------------+---------+
|avg(sgrade) | sname |
+-------------+---------+
| 80.0000 | 女生1 |
| 99.0000 | 女生2 |
| 85.0000 | 女生3 |
| 90.5000 | 女生4 |
| 70.0000 | 男生1 |
| 78.0000 | 男生2 |
+-------------+---------+
mysql>select avg(sgrade),sname from students group by sname having avg(sgrade)>80;//显示平均成绩大于80的学生平均成绩
mysql>select avg(sgrade),sname from students group by sname whereavg(sgrade)>80;//error,不能这样
五、多表查询
外连接()和内连接(inner join)
1、内连接
mysql>select *from students;//建立的一个新表students
+-------+------+
|sname | sno |
+-------+------+
|mcf | 1 |
|cxf | 2 |
|lcf | 3 |
|qlf | 4 |
+-------+------+
mysql>select *from sc;//sc表里面的内容
+------------+----------+-------+
|studentsid | courseid | grade |
+------------+----------+-------+
| 1 | 1 | 97 |
| 2 | 1 | 98 |
| 2 | 2 | 67 |
| 3 | 2 | 76 |
| 3 | 3 | 81 |
+------------+----------+-------+
内连接之后:
1)、mysql> select * from students inner join sc on sc.studentsid=students.sno;//内连接inner join,连接条件为sc的studentsid等于students的sno
+-------+------+------------+----------+-------+
|sname | sno | studentsid | courseid |grade |
+-------+------+------------+----------+-------+
|mcf | 1 | 1 | 1 | 97 |
|cxf | 2 | 2 | 1 | 98 |
|cxf | 2 | 2 | 2 | 67 |
|lcf | 3 | 3 | 2 | 76 |
|lcf | 3 | 3 | 3 | 81 |
+-------+------+------------+----------+-------+//5行6列
2)、mysql> select * from students,sc wheresc.studentsid=students.sno;//第二种内连接
mysql> select avg(grade) from students,scwhere sc.studentsid=students.sno;//对内连接之后的所有学生求平均值
2、外链接
1、左连接右连接
mysql>select * from students left join sc on sc.studentsid=students.sno;//左连接
+-------+------+------------+----------+-------+
|sname | sno | studentsid | courseid |grade |
+-------+------+------------+----------+-------+
|mcf | 1 | 1 | 1 | 97 |
|cxf | 2 | 2 | 1 | 98 |
|cxf | 2 | 2 | 2 | 67 |
|lcf | 3 | 3 | 2 | 76 |
|lcf | 3 | 3 | 3 | 81 |
|qlf | 4 | NULL | NULL | NULL |
+-------+------+------------+----------+-------+
mysql>select * from sc right join students on sc.studentsid=students.sno;//两种写法都是右连接
mysql>select * from sc right outer join students on sc.studentsid=students.sno;//
+------------+----------+-------+-------+------+
|studentsid | courseid | grade | sname | sno |
+------------+----------+-------+-------+------+
| 1 | 1 | 97 | mcf | 1 |
| 2 | 1 | 98 | cxf | 2 |
| 2 | 2 | 67 | cxf | 2 |
| 3 | 2 | 76 | lcf | 3 |
| 3 | 3 | 81 | lcf | 3 |
| NULL | NULL | NULL | qlf | 4 |
+------------+----------+-------+-------+------+
注:以上两种左、右连接结果都是一样的,只是表中列的顺序不一样
六、子查询
将一个查询嵌套在另外一个查询里
七、复制表
mysql>create table sc2 select * from sc;//将sc表里面的内容全部拷贝到sc2里 *代表拷贝所有的内容若只要拷贝一部分可把*改为studentsid等
mysql>select *from sc2;
+------------+----------+-------+
|studentsid | courseid | grade |
+------------+----------+-------+
| 1 | 1 | 97 |
| 2 | 1 | 98 |
| 2 | 2 | 67 |
| 3 | 2 | 76 |
| 3 | 3 | 81 |
+------------+----------+-------+
*代表拷贝所有的内容若只要拷贝一部分可把*改为studentsid等如:
mysql>create table xx1 select studentsid from sc;//将scimian的内容studentsid拷贝到表xx1里面
mysql>select * from xx1;
+------------+
|studentsid |
+------------+
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
+------------+
mysql>create table sc3 select * from sc where 0=1;//将sc表的结构拷贝到sc3,没有拷贝数据,因为0=1为假
mysql>insert into sc3 select * from sc;//sc3上面已经建好,现在将sc里面的内容全部拷贝到sc3中
八、在ubuntu中使用mysql
1、g++ mysql.cpp -L/user/lib/mysql -lmysqlclient