mysql数据库的具体介绍和使用

1 数据库操作 （所以命令可大小写，规范为大写）

创建数据库：(数据库名开头不能为数字)

create  database  db2；（注意添加结尾的分号）

删除数据库：

drop database  db1;

查看数据库列表:

show databases;

进入其中的某个数据库：

use db1;

进入某个数据库后查看此库里有那些表：
show  tables;

在数据库中使用bsah的命令：stem/\!  +bash命令  （stem  ls /etc /或者\!  ls /etc/）

数据库的终端命令可查看帮助：> help

查看支持所有字符集：SHOW CHARACTER SET; 
查看支持所有排序规则：SHOW COLLATION; 
获取命令使用帮助： mysql> HELP KEYWORD;

 

2 .创建表  （数据库下存放各种表）

创建表：CREATE TABLE

(1) 直接创建 新表：

create table students ( id tinyint unsigned not null primary key,name char(10) not null,sex char(1),phone char(11) );

（第一项为创建的id，采用最小的整数，不为空，且为主键；第二项为姓名；给定十个字符；第四个为性别，给一个字符；第五项为电话号码，设定为11个字符）

create table emp ( id int unsigned auto_increment primary key,name char(4) not null ,sex char(1) default ‘m’,adress char(20) );

auto_increment：数字自动递增；unsigned 放在int定义数字的后面

小结：一个汉字，字母数字在char里都算一个字符。primary key放在最后；添加各种修饰符要注意前后顺序，如上例中，如果将修饰符的顺序更改则会报错不能生成此表；

(2) 通过查询现存表创建新的表，连数据一起创建了。

create table user select user,host,password from mysql.user;

mysql.user:mysql是一个库，user是其库里的一个表

(3) 通过复制现存的表的表结构创建，但不复制数据

create table user1 like mysql.user;

 

3 .表操作

查看创建的表结构：

desc  students；

查看库里的表；(首先进入到此库中然后查看)

show tables;

删除库里的某个表: (首先还是要进入到存放此表的库里，查看后删除)

drop table students

级联删除；

如果某两个表之间是相互关连的关系，是不能删除其中一个的，会报提示错误。

查看单个表状态：

show table status like ‘students’\G;   （表名必须要加引号）

查看库中所有表的状态：
show table status from db1\G;   （G 为竖列查看）

 

对创建好的表进行修改：（一般表创建好后不建议去修改它，会造成数据的丢失）

对已经创建好的表添加一个字段：

alter table students add address char (11) after name;

对students表添加一个地址的字段，定义地址字段的格式，在name 字段后添加；

对已经创建好的表删除某个字段：

alter table students drop address

对students表删除一个 字段

ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); （对phone字段改名并重新定义数据类型）

 

 

数据类型 ：

选择正确的数据类型对于获得高性能至关重要，

三大原则： 

更小的通常更好，尽量使用可正确存储数据的最小数据类型 

简单就好，简单数据类型的操作通常需要更少的CPU周期 

尽量避免NULL，包含为NULL的列，对MySQL更难优化

具体内容查看30天的第二个视频

1、整数的范围（找自己表合适的使用,使用越大的范围占用的内存空间越大）

tinyint            1个字节 范围(-128~127) 

smallint         2个字节 范围(-32768~32767) 

mediumint    3个字节 范围(-8388608~8388607) 

int                   4个字节 范围(-2147483648~2147483647) （无论是几都要占用4个字节）

bigint              8个字节 范围(+-9.22*10的18次方)   （无论是几都要占用8个字节）

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255) 取正整数。

 

2、浮点型(float和double)，近似值 

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位 

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位 

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是 123.457，但总个数还以实际为准，即6位

3、定点数 

在数据库中存放的是精确值,存为十进制  decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位

4 .字符串(char,varchar,_text)

char(n) 固定长度，最多255个字符 （常用的定义表字符的长度最少时4个字符，与下面的比效率高）

varchar(n)可变长度，最多65535个字符  （同上最少可以为一个字符减少空间的使用，但效率低）

tinytext 可变长度，最多255个字符 

text 可变长度，最多65535个字符 

mediumtext 可变长度，最多2的24次方-1个字符 

longtext 可变长度，最多2的32次方-1个字符 

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节 

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

修饰符
所有类型：

• NULL 数据列可包含NULL值

• NOT NULL 数据列不允许包含NULL值

• DEFAULT 默认值 • PRIMARY KEY 主键

• UNIQUE KEY 唯一键

• CHARACTER SET name 指定一个字符集 数值型

• AUTO_INCREMENT 自动递增，适用于整数类型   （数字自动增长不需要手动在添加了）

• UNSIGNED 无符号

 

 

4  .对表进行添加信息，删除信息，等操作

1 . 向表里添加记录;

insert students values(1,’bai’,’beijing’,’m’,’100233′);

字符要加引号。

添加后的表：select   *   from students   (*代表查看表里的所有的内容)

另一种方式添加记录：

insert students(id,name) values(2,’wang’);对指定的某个字段进行添加，字段和要赋值的内容要相对应。

其他没有指定的字段内容默认为null

也可同时添加多行记录：

insert students values(3,’li’,’shanghai’,’f’,’100010′),(4,’zhang’,’hefei’,’f’,’121212′);

 

最后一种批量将旧表中的某些数据插入到新表的指定的字段当中：

insert emp(name,adress) select user,host from user2;

emp为新表，user2为所查的表。将user2表的两项内容，user和host赋值到要插入表emp的name和user2两个字段中。

2 . update; 更改表里的某行记录的某一个字段：

update students set sex=’m’,phone=’44444′ where id=2;

where id=2 （修改的指定范围；既将id=2的哪一行的两个字段更改掉）

如果where不写系统会默认更改，所有行的sex和phone的字段。

如果where查询的字段有多个相同的，但只想更改其中的两个可以增加限制，但此限制只能按顺序选定，

update student  set sex=’m’ where sex=’f’ limit2;  (按顺序只更改前两个后面的满足条件也不更改了)

 

3 . 对表里的内容进行删除：

truncate table students  （快速删除整个表，且不可恢复）

delete from students where id=4; （删除id=4的哪一行）

delete from students；如果不写where限定条件，默认会删除此表的所有内容，如果在配置文件里面添加

vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]

safe-updates

添加此行内容就不会删除所有的内容了。

 

 

 

 

5  .SELECT （搜索查询）

select * from students where sex=’m’;（查询表里的所有字段，限定条件为，sex=m）

select id,name from students where sex=’m’;  (查看表里的某些特定的字段，限定条件为sex=m)

限定条件可以加多个：

select * from students where sex=’m’  and   id=2

WHERE子句：

指明过滤条件以实现“选择”的功能：

过滤条件：布尔型表达式 算术操作符：+, -, *, /, % 比较操作符：=, !=, <>, <=>, >, >=, <, <=

BETWEEN min_num AND max_num  (在两者之间)select * from students where id between 2 and 4 ;

IN (element1, element2, …)    select * from students where sex in (‘f’,’m’,null);

IS NULL   （表示的为空，查找是遇到null的不能写=号只能用is）

select * from students where sex is null;

IS NOT NULL   （表示的为非空，查找是也不能用=号只能用is）

select * from students where sex is not null;

逻辑操作符：

NOT  （取反）

AND   （并且）

OR  （或者）

XOR （异或）

select * from students where id>2;  （查找id号大于2的行）

select * from students where not id>2;（id>2并取反，即是查找id<=2的行）

模糊搜索查询：

LIKE:

%: 任意长度的任意字符

_：任意一个个字符

select * from students where name like ‘%w%’  （name字段包含有w的）

 

 

字段显示可以使用别名

格式；字段名as字段的别名 （前面为字段的名称后面为定义的别名，只是用于显示）

select name as 姓名,sex as 性别 from students where sex is not null;

 

 

 

6 .分组：

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

avg(), max(), min(), count(), sum()  （平均值；最大值；最小值；组里成员的个数；总和；）

下面是此表的内容：

select sex from students group by sex;  （一旦做分组前后的sex应该是相互关连的，不能随便写了）

select sex,max(address) from students group by sex; （以性别为分组统计address中的最大值）

max(address) 把max换成avg统计以性别为分组的平均成绩；换成sum 为男女组的总成绩；等等；

 

统计上图中一班和二班男女生的平均成绩：

select class,sex,avg(address) from students group by sex,class;  （group分组根据多个条件）

统计效果如下图所示：

HAVING: 对分组聚合运算后的结果指定的限定条件 ：

select sex,class,avg(address) from students group by class,sex having avg(address) > 80;（一班和二班男女生的平均成绩：并加限定条件，大于80分的，此处的限定条件就不能用where了，语法要求在分组后加限定条件只能用having ）

ORDER BY: 根据指定的字段对查询结果进行排序

升序：ASC

降序：DESC

select * from students order by age asc; (对表中对其age组进行升序排列)

select * from students order by age desc;(对表中对其age组进行降序排列)

select sex,class,avg(address) from students group by class,sex asc; (分组时可以如此使用升序排列)

select sex,class,avg(address) from students group by class,sex desc;  （分组时可以如此使用降序排列）

小结：如果有null的想要将其排在最后：可以在分的组名上添加—

例如：select sex,class,avg(address) from students group by -sex desc ；（此实例中只按性别进行分组）

 

 

7 .多表查询 (多个表关连进行查询)

 

上图为多表联系的7中结构：

如何将写的数据库程序导入到mysql表中：

第一种方法：mysql < /hellodb_innodb.sql  （没有进入数据库前）

第二种方法：source hellodb_innodb.sql    （在进入数据库后）

上图中的结构分析：

第三种为：内连接

select s.name,t.name from students as s,teachers as t where s.teacherid=t.tid;  （取交集老旧的写法）

（s.name中，s为后面students的别名，组和使用为了确保它是students的name的字段的唯一性，以免其他表中有相同的字段，t.name也是此意思；where后面的限制条件为：students表中的teacherid=teachers表中的tid;要显示的内容为：students表中的name和teachers表中的tid）

现在一般采用较新的写法：

select s.name as student_name,t.name as teacher_nameb from students as s inner join teachers as t on s.teacherid=t.tid;

用inner join …on 代替where;

其中inner join前面是一个表后面是一个表然后在on后面添加条件。

inner join 可以取多个表的交集 ；

例如：表A inner join 表B on 条件1  inner join 表C  on  条件2 ；（等依次类推）

（将A和B先查询后在和C进行内连接的查询）

select * from students cross join teachers;（笛卡尔乘积）：两张表的记录完全组和一遍类似于{1，2，3}{4，5，，6} （交叉组和各种可能）

子查询：（将一个查询的结果定义为一个限定的条件）

select * from students where age > (select avg(age) from students);（括号里的为查询平均年龄作为限定条件来使用）

 

第一种为；左外连接 （左边的全部都要，右边只要交集的部分；交集的部分按条件规则来取）

select s.name as student_name,t.name as teacher_name from students as s left outer join teachers as t on s.teacherid=t.tid;                                                                              （此为左边的表全部留下来）      （右边的表只留交集）

第二种为：右外连接（右边的全部都要，左边的只要交集）

select s.name as student_name,t.name as teacher_name from students as s right outer join teachers as t on s.teacherid=t.tid;                                                                        （此为左边表只要交集）          （此为表的右边全部都要）

 

第四种为：左内连接

第五种为；右内连接

第六种为：

第七种为：

 

练习 （单表查询的）
导入hellodb.sql生成数据库 

两种方法：

第一种：mysql < /hellodb_innodb.sql （没有进入数据库前）
第二种：source hellodb_innodb.sql （在进入数据库后）
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

select name,age from students where age>25 and gender=’m’;

select name,age from students where age>25 and gender=’m’ order by age; （对查询的结果在进行年龄的排序）

(2) 以ClassID为分组依据，显示每组的平均年龄

select avg(age),classid from students group by classid ;

(3) 显示第2题中平均年龄大于30的分组及平均年龄

elect avg(age),classid from students group by classid having avg(age) > 30;

（group分组时如果需要添加限定条件只能用having ）

(4) 显示以L开头的名字的同学的信息 

select * from students where name like ‘l%’；

(5) 显示TeacherID非空的同学的相关信息 

select * from students where teacherid is not null;

（当遇到null时必须使用is 不能用=号了）

select * from students where not teacherid is null; (反向使用也是可以的)

(6) 以年龄排序后，显示年龄最大的前10位同学的信息 

select * from students order by age desc limit 10;

（先反向排序后在限定只查找前十位的信息）

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息

select * from students where age>20 and age<=25;

（并且的限定条件用and）

小结：对于select搜索查询；题目要得到的显示结果应该写在select……….from students 之间；

搜所的限定条件写在where 后面；做题的思路如是；

练习：（多表查询）

导入hellodb.sql，以下操作在students表上执行 

1、以ClassID分组，显示每班的同学的人数 

select count(classid) as sum ,classid from students group by classid;

（as后面为定义的别名）

2、以Gender分组，显示其年龄之和 

select sum(age),gender from students group by gender;

3、以ClassID分组，显示其平均年龄大于25的班级 

elect avg(age) as ping,classid from students group by classid having ping > 25 and classid is not null;

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和

select gender,sum(age) from (select * from students where age > 25) as t group by gender;

（第一次搜索的表作为第二次查询的表必须要添加别名，否则无法执行）

5、显示前5位同学的姓名、课程及成绩 

select name,score,course from students as st inner join scores as sc inner join courses as co on st.stuid=sc.stuid and co.courseid=sc.courseid limit 10;

(因为每个名字都是重复的所以xuan)

6、显示其成绩高于80的同学的名称及课程； 

select st.name as student_name,sc.score as score,course from students as st inner join scores as sc inner join courses as co on st.stuid=sc.stuid and co.courseid=sc.courseid and score > 80;

7、求前8位同学每位同学自己两门课的平均成绩，并按降序排列 

8、显示每门课程课程名称及学习了这门课的同学的个数 

9、如何显示其年龄大于平均年龄的同学的名字 

10、如何显示其学习的课程为第1、2，4或第7门课的同学的名字 

11、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学 

12、统计各班级中年龄大于全校同学平均年龄的同学