MYSQL数据库安装详解

2017-12-26 本文已影响173人凤之鸠

关系型数据库

1.数据的安装

1.1、mysql数据库的安装

１、打开下载的mysql安装文件,双击运行mysql-5.5.40-win32.msi。

２、选择安装类型，有“typical（默认）”、“complete（完全）”、“custom（用户自定义）”三个选项，选择“custom”，按“next”键继续。

３、在“developer components（开发者部分）”上左键单击，选择“this feature,

and all subfeatures, will be installed on local hard drive.”，即“此部分，及下属子部分内容，全部安装在本地硬盘上”。在上面的“mysql server（mysql服务器）”、“client programs（mysql客户端程序）”、“documentation（文档）”也如此操作，以保证安装所有文件。点选“change...”，手动指定安装目录。

4、填上安装目录，我的是“e:\software\install\mysql\”，也建议不要放在与操作系统同一分区，这样可以防止系统备份还原的时候，数据被清空。按“ok”继续。

[if !supportLists]1.[endif]确认一下先前的设置，如果有误，按“back”返回重做。按“install”开始安装。

５、正在安装中，请稍候，安装完成后会出现成功界面，点击成功“next”之后，出现以下界面。

这里询问是否继续配置mysql数据的参数，勾选上，然后点击“finish”

1.2、mysql的配置

１、安装完成了，出现如下界面将进入mysql配置向导。

２、选择配置方式，“detailed configuration（手动精确配置）”、“standard configuration（标准配置）”，我们选择“detailed configuration”，方便熟悉配置过程。

３、选择服务器类型，“developer machine（开发测试类，mysql占用很少资源）”、“server machine（服务器类型，mysql占用较多资源）”、“dedicated mysql server machine（专门的数据库服务器，mysql占用所有可用资源）”

４、选择mysql数据库的大致用途，“multifunctional database（通用多功能型，好）”、“transactional database only（服务器类型，专注于事务处理，一般）”、“non-transactional database only（非事务处理型，较简单，主要做一些监控、记数用，对myisam数据类型的支持仅限于non-transactional），按“next”继续。

５、选择网站并发连接数，同时连接的数目，“decision support(dss)/olap（20个左右）”、“online transaction processing(oltp)（500个左右）”、“manual setting（手动设置，自己输一个数）”。

６、是否启用tcp/ip连接，设定端口，如果不启用，就只能在自己的机器上访问mysql数据库了，在这个页面上，您还可以选择“启用标准模式”（enable strict mode），这样mysql就不会允许细小的语法错误。如果是新手，建议您取消标准模式以减少麻烦。但熟悉mysql以后，尽量使用标准模式，因为它可以降低有害数据进入数据库的可能性。按“next”继续

７、就是对mysql默认数据库语言编码进行设置（重要），一般选utf-8，按“next”继续。

８、选择是否将mysql安装为windows服务，还可以指定service name（服务标识名称），是否将mysql的bin目录加入到windows path（加入后，就可以直接使用bin下的文件，而不用指出目录名，比如连接，“mysql.exe -uusername -ppassword;”就可以了，不用指出mysql.exe的完整地址，很方便），我这里全部打上了勾，service name不变。按“next”继续。

９、询问是否要修改默认root用户（超级管理）的密码。“enable root access from remote machines（是否允许root用户在其它的机器上登陆，如果要安全，就不要勾上，如果要方便，就勾上它）”。最后“create an anonymous account（新建一个匿名用户，匿名用户可以连接数据库，不能操作数据，包括查询）”，一般就不用勾了，设置完毕，按“next”继续。

用户名和密码统一设置成：

用户名:root

用户密码：root

１０、确认设置无误，按“execute”使设置生效，即完成mysql的安装和配置。

注意：设置完毕，按“finish”后有一个比较常见的错误，就是不能“start service”，一般出现在以前有安装mysql的服务器上，解决的办法，先保证以前安装的mysql服务器彻底卸载掉了；不行的话，检查是否按上面一步所说，之前的密码是否有修改，照上面的操作；如果依然不行，将mysql安装目录下的data文件夹备份，然后删除，在安装完成后，将安装生成的data文件夹删除，备份的data文件夹移回来，再重启mysql服务就可以了，这种情况下，可能需要将数据库检查一下，然后修复一次，防止数据出错。

2.数据库的卸载

2.1停止window的mysql服务。

找到“控制面板”->“管理工具”->“服务”，停止mysql后台服务。

2.2卸载mysql安装程序。找到“控制面板”-> "程序和功能"，卸载mysql程序。

2.3删除mysql安装目录下的所有文件。

2.4删除c盘programdata隐藏目录中关于mysql的目录。

2.4.1打开window系统的“显示隐藏文件”功能，以便查看到系统的所有隐藏文件

2.4.2找到programdata目录

2.4.3删除mysql目录

3.数据库入门

数据是每个企业的最核心的、最重要的灵魂--------

优点：

>方便查询

>可存储的数据量大

>保证数据的完整、一致

>安全可靠

3.1数据可以保存到地方

1）数据保存在内存：

优点：数据的读写速度快

缺点：断电或者程序关闭的情况下，数据就丢失

2）数据保存在文件：

优点：数据可以永久保存

缺点：频繁的读写操作，效率不高

数据的管理不方便，查询数据的时候需要查询所有，然后在匹配

3）数据保存在数据库软件

优点：数据永久保存

数据的管理非常方便，当查询的时候速度快、操作也方便简单

3.2数据库软件

首先分开理解，数据库是其实可看成是保存数据的仓库，那自然明白数据库软件其实就是指的是管理数据库的软件或者说数据库管理软件。

3.3常见数据库软件

oracle，甲骨文公司的产品，也是当前最流行的关系型数据库，跟java语言兼容性非常好，因为现在java语言也是被甲骨文公司收购了。维护工作当然也是甲骨文公司维护。

sql server，微软公司的产品，windows操作系统平台上应用广泛，跟c#,net平台兼容性非常好，那是必须的，同属微软的产品。

db2，ibm公司的产品（ibm公司蓝色巨人），ibm的产品涉及软件、硬件。比如ibm有自己的操作系统unix，数据库有db2，还有web服务器websphere。

以上的数据库都是属于关系型数据库

非关系型数据库：mongodb（nosql）很好实现了面向对象的数据库

--------------------------------------课程体系是先学mysql再学oracle

4.mysql数据库

mysql是关系型数据库。什么是关系型数据库：

首先理解，数据库是管理数据的仓库，而数据库中的数据是现实世界上的某个实体，抽象出来有价值需要保存的属性值。数据的集合就在数据库中称为表，表的是由列、和行组成的二维模型。那关系型数据库就是由二维的表和它其中的关系组成的一个数据组织。

student表

studentidstudentnameteacherid

0001张三1000

0002李四1001

teacher表

teacheridteachername

1000张老师

1001黎老师

mysql关系型数据库管理系统（rdbms）是管理着这样的一个个关系型数据库。当然在rdbms中是先有数据库、然后有表、才有数据的。

c:\users\administrator>net start mysql

mysql服务正在启动.

mysql服务已经启动成功。

c:\users\administrator>net stop mysql

mysql服务正在停止.

mysql服务已成功停止。

5.sql语言入门

我们是如何对rdbms进行沟通的呢？我们是通过sql语言进行操作的，然后sql语言应该属于第4代语言，它是一门高级的结构化非过程化的查询语言。sql语言是国际标准化组织（iso）定义的关系型管理数据库系统的标准语言。----------àiso定义了一套标准。

经历了1986年sql86标准、1989年sql89标准、1992年sql92标准、1999年sql99标准、2003年sql03标准。

sql方言又是指的每种rdbms对sql标准提供基本数据操作有一些区别的实现，但是总体来讲都遵循sql标准，实现的方式只是有略微的区别。

面对sql语言，我们学习内容有：

[if !supportLists]–[endif]数据定义语言(ddldata definition language) :其语句包括动词create和drop。在数据库中创建新表或删除表（creat table或drop table）

[if !supportLists]–[endif]数据查询语言(dql data query language) *****

[if !supportLists]–[endif]数据操作语言(dmldata manipulation language) :其语句包括动词insert，update和delete。它们分别用于添加，修改和删除表中的行。也称为动作查询语言

[if !supportLists]–[endif]数据控制语言(dcldata control language) :它的语句通过grant或revoke获得许可，确定单个用户和用户组对数据库对象的访问

6.mysql登录

运行的输入cmd进入dos窗口

1.mysql –uroot –proot;窗口上会显示我们的密码

2.mysql –uroot –p回车

提示再输入我们的密码，这样登录密码不会暴露在窗体上。

3.安装好的mysql会给我们安装一个客户端也可以登录。

4.有第三种的工具来登录我们的mysql。

mysql –uroot –proot –hlocalhost

mysql- uroot –proot -h127.0.0.1

7.数据库的管理

7.1查询数据库

mysql>show databases;------查询数据库

+--------------------+

| database |

+--------------------+

| information_schema | --------------mysql元数据

| mysql | ----------- mysql的一些配置参数数据放在这里（用户名，密码）

| performance_schema | -------------mysql的一些运行时的数据（日志信息等。）

| test | -----------空的数据库，是用来我们测试用的。

+--------------------+

4 rows in set (0.00 sec)

7.2创建数据库

创建一个数据库

mysql>create database u29;

query ok, 1 row affected (0.03 sec)

mysql> create database u29 default character set gbk;创建数据库，并带字符编码的

查询数据库的字符编码

mysql> show create database u29;

+----------+--------------------------------------------------------------+

| database | create database |

+----------+--------------------------------------------------------------+

| u29 | create database `u29` /*!40100 default character set utf8 */ |

+----------+--------------------------------------------------------------+

1 row in set (0.00 sec)

7.3删除数据库

mysql> drop database u29;

query ok, 0 rows affected (0.13 sec)

7.4更改数据库的字符集编码

mysql> alter database u29 default character set utf8;

注意在控制台上我们使用的utf8编码：我们可以set names gbk;

show variables like ‘character%’;

8.表的管理

mysql>use u29;选择一个数据库

database changed

在sql中语，标识符的不区分大小写。但是我们一般是把一些关键字大写。

在创建一张表的时候，数据库它会去查询它有相同的表没有，如果有，不给予创建，会报错

error 1050 (42s01): table 'student' already exists

8.1查看当前数据库有哪些表

mysql>show tables; ---查看表

+---------------+

| tables_in_u29 |

+---------------+

| student |

+---------------+

8.2建表

mysql>create table student

-> (sid int,

-> name varchar(20),

-> age int

-> );

query ok, 0 rows affected (0.01 sec)

8.3删除表

mysql>drop table student;

query ok, 0 rows affected (0.00 sec)

8.1查看表结构

mysql>desc student;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| sid | int(11) | yes | | null | |

| age | int(11) | yes | | null | |

+-------+-------------+------+-----+---------+-------+

8.2数据类型

[if !supportLists]Ø[endif]整型

tinyint1b(-128，127)(0，255)极小整数值

smallint2b(-32 768，32 767)(0，65 535)小整数值

mediumint3b(-8 388 608，

8 388 607)

(0，16 777 215)小整数值

int4b(-2 147 483 648，

2 147 483 647)

(0，4 294 967 295)整数值

bigint8b(-9 233 372 036 854 775 808，

9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)大整数值

一般就用int类型。

[if !supportLists]Ø[endif]浮点型数据类型

float4b(-3.40e-38，3.40e+38)7位小数单精度浮点数

double8b(-8 388 608，

8 388 607)

15位小数双精度浮点数

decimal(m,d)17b(-10的38次方-1 ,

10的38次方-1)

30位小数大浮点数

numeric(m,d)同decimal

我们一般用double来表示浮点型小数

decimal(m,d)这个数据类型往往是跟钱打交道的。不会有精度的损失。

127.9999 m代表7位，d就代表4位，在这里一要注意m大于d。

[if !supportLists]Ø[endif]字符串类型

char(m)m0-255字符型

varchar(m)m+1b0-65535字符型

binary(m)m0-m0-m变长字符串

varbinary(m)m+1b0-m0-m变长字符串

char：固常字符串类型char(255);长度就定下来了

我们录入的数据不能超过我们指定的长度。

如果我们录入的数据的长度没有超过我们指定的长度。

例如：录入的字符串为‘张三’，它就会在后面用空格把指定的长度

填满。

varchar：可变长字符串类型

它可以指定长度varchar（20）

录入的字符串的长度‘张三’，它就不会char来用空格给你补齐。

varchar（m+1b）

1b这个长度就是来计算你给的字符串的实际长度。

资源的占用上，其实它的消耗不见得比char类型要节省。

text(clob)类型sql语言的标准。属于msyql自己的搞的类型。

被视为非二进制字符串(字符字符串)

方言:数据库厂商自己搞的一些语法

[if !supportLists]Ø[endif]日期类型

year1byyyy1901 - 2155

date3byyyy-mm-dd1000-01-01 - 9999-12-31

timesamp4byyyy-mm-dd hh:mm:ss1970-01-01 00:00:01 - 2038

datatime8byyyy-mm-dd hh:mm:ss1000-01-01 00:00:00 - 9999-12-31 23:59:59

[if !supportLists]Ø[endif]二进制数据类型

tinyblob2550-255二进制大对象

blob65k0-65kb二进制大对象

mediumblob16m0-16m二进制大对象

longblob4g0-4g二进制大对象

这种数据类型，一般是用来保存一些图片、音频等文件。

8.3添加表的字段

mysql> alter table student add column sex char(1);

query ok, 0 rows affected (0.03 sec)

records: 0 duplicates: 0 warnings: 0

8.4删除表的字段

mysql> alter table student drop column sex;

query ok, 0 rows affected (0.03 sec)

records: 0 duplicates: 0 warnings: 0

8.5修改表字段名称

mysql> alter table student change column username name varchar(22);

query ok, 0 rows affected (0.03 sec)

records: 0 duplicates: 0 warnings: 0

8.6修改表的字段类型

mysql> alter table student modify column name varchar(10);

query ok, 0 rows affected (0.03 sec)

records: 0 duplicates: 0 warnings: 0

8.7修改表的名称

mysql> alter table student rename to stu;

query ok, 0 rows affected (0.00 sec)

是先有表，再有数据的。要先有表的话，我们是要先定表的字段名称和类型。

9.复制一张表

9.1复制表的结构，表的内容也要复制过来。

mysql>create table tempstu select * from stu;

query ok, 3 rows affected (0.00 sec)

records: 3 duplicates: 0 warnings: 0

mysql> status -------看下当前的状态

--------------

mysql ver 14.14 distrib 5.5.40, for win32 (x86)

connection id: 2

current database: u29

current user: root@localhost

ssl: not in use

using delimiter: ;

server version: 5.5.40 mysql community ser

protocol version: 10

connection: localhost via tcp/ip

server characterset: utf8

db characterset: utf8

client characterset: utf8

conn. characterset: utf8

tcp port: 3306

uptime: 4 hours 5 min 50 sec

windows操作系统的默认字符集是gbk；

数据库是uft-8；

一个中文字：如果是gbk编码方式2个字节—1个中文字

如果是utf-8编码方式3个字节---1个中文字

解决方案：1.修改我们的my.ini这个文件的默认字符集。

2. set names gbk；

mysql>show character set;

+----------+-----------------------------+---------------------+--------+

+----------+-----------------------------+---------------------+--------+

| big5 | big5 traditional chinese | big5_chinese_ci | 2 |

| dec8 | dec west european | dec8_swedish_ci | 1 |

| cp850 | dos west european | cp850_general_ci | 1 |

| hp8 | hp west european | hp8_english_ci | 1 |

| swe7 | 7bit swedish | swe7_swedish_ci | 1 |

| tis620 | tis620 thai | tis620_thai_ci | 1 |

| gb2312 | gb2312 simplified chinese | gb2312_chinese_ci | 2 |

| cp1250 | windows central european | cp1250_general_ci | 1 |

| gbk | gbk simplified chinese | gbk_chinese_ci | 2 |

| utf8 | utf-8 unicode | utf8_general_ci | 3 |

| ucs2 | ucs-2 unicode | ucs2_general_ci | 2 |

| cp866 | dos russian | cp866_general_ci | 1 |

| cp852 | dos central european | cp852_general_ci | 1 |

| cp1251 | windows cyrillic | cp1251_general_ci | 1 |

| utf16 | utf-16 unicode | utf16_general_ci | 4 |

| cp1256 | windows arabic | cp1256_general_ci | 1 |

| cp1257 | windows baltic | cp1257_general_ci | 1 |

| utf32 | utf-32 unicode | utf32_general_ci | 4 |

| cp932 | sjis for windows japanese | cp932_japanese_ci | 2 |

+----------+-----------------------------+---------------------+--------+

9.2复制表，只复制表的结构，不带数据。

mysql> create table tempstu2 select * from stu where 1=0;

query ok, 0 rows affected (0.02 sec)

records: 0 duplicates: 0 warnings: 0

简单的查询：

mysql> select * from tempstu2;

简单的插入：

mysql> insert into stu values(3,'况佳成',50);

10.索引

索引有利有弊，索引的作用实质就是为了提高查询的效率，但是反而会降低insert

update、delete语句的效率，因为我们在做操作数据的时候会改变的索引的重新位置，

一张表可以建立多个索引，也不是说索引越多越好。往往我们来定一个索引字段是唯一的。

并且索引字段类型int类型。

定义索引的长度可以指定。

怎么来一个索引？

mysql> create index s_index2 on stu(name(5));

删除字索引的方法

mysql> alter table stu drop index s_index2;

11.mysql约束

首先我们应该明白在使用数据库操作操作数据的时候，我们是先有表才有数据。表建立好了之后，当用户录入数据的过程中如何来保证数据的完整性呢？（数据的可靠性+数据的准确性=数据的完整性），所以我们要对数据加一些规则、即约束。

比如说：数据类型告诉我们需要存储什么样子的数据，而约束告诉我们这些数据具体需要满足的规则。如：age int,我们可以存储负整数，price float，我们可以存储负小数，但是负数是不满足实际规则的（规则可能也是按照我们的需求制定的），因此我们需要约束。

约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。

dbms帮助我们去维护和管理着这些约束。达到操作数据的安全性。

常见约束主要分为：

11.1检查约束check（但是mysql不支持）

mysql> create table test(sid int ,name varchar(20),age int check(age > 50) );

11.2非空约束not null

作用：限制字段必须赋值

我们也可以赋null值-----错误的

注意：加了非空的约束后，我们不能插入null值。

并且dbms也不能为我们插入默认值。

表中字段的类型不给值的时候，dbms自动给我们添加默认值为null。

alter table表名modify字段名varchar(16) not null; ----修改表字段不能为空。

create table表明(sid int not null ) -----生产表的时候加非空约束。

mysql> alter table stu modify column sid int null; -----改为可以为空。

mysql> insert into stu value(null,null,null);

error 1048 (23000): column 'sid' cannot be null ----错误，我们在sid上加了not null的约束

mysql> insert into stu value();

error 1364 (hy000): field 'sid' doesn't have a default value ---

mysql> alter table stu modify column sid int not null;

error 1265 (01000): data truncated for column 'sid' at row 1 -----你来修改字段约束，本身这个字段有字段值违反了，你将要修改的字段约束。

11.2.1默认值

mysql> alter table stu modify column sid int not null default 1000; ---修改字段的默认值

11.3唯一约束unique

作用：字段的值不能重复

注意：如果在某个字段上加上了unique这个约束后，我们就不能插入重复的字段值。

但是，我们可以插入null；并且这个字段加上了人为的默认值，我们插入null值，就是null值。如果说是让系统给默认值，它就会把认为的默认值给他。

如果说，表中没有主键，一个字段有非空约束和唯一约束=======》虚拟主键，会显示pri，但不是主键，用drop也删除不掉，当设置了一个主键后，虚拟主键就不再显示pri。

create table表名（字段名称字段数据类型unique）;

create table表明（字段名称1字段类型，字段名称2字段类型，

constraint约束名称（unique,pramry key…..）(字段1,字段2)

）;

alter table表名add constraint约束名称unque(字段);

mysql> create table test1 (sid int,name char(10),age int, contraint test_unique unique(sid,name) );-------多个字段组成的唯一约束。

alter table test1 drop index test_unique;------删除唯一的约束

注意：我们创建唯一约束，会自动给我们生产索引

11.4主键约束

作用：字段不能为空并唯一

[if !supportLists][endif]注意：最少性

[if !supportLists][endif]尽量选择单个键作为主键

[if !supportLists][endif]稳定性

[if !supportLists][endif]尽量选择数值更新少的列作为主键

mysql> create table test1

-> (

-> sid int primary key,

-> name varchar(20),

-> age int

-> );

mysql> alter table test1 add constraint test_key primary key(sid,name);---表级约束的语法

mysql> create table test1

-> (

-> sid int,

-> name varchar(20),

-> age int，

-> constraint test_key primary key( );

-> );

mysql> alter table test1 drop primary key; ------删除主键，其实not null约束没删掉

query ok, 0 rows affected (0.03 sec)

records: 0 duplicates: 0 warnings: 0

11.4.1主键自增长

作用：自动给字段一个字段值。

注意：

[if !supportLists][endif]auto_increment是自增长关键字

[if !supportLists][endif]默认自增种子为1，增量为1

[if !supportLists][endif]auto_increment自增只能是数字

[if !supportLists][endif]auto_increment最大值受数据类型影响，一但超过最大值自增则失效

[if !supportLists][endif]auto_increment在删除全表时会自动重新编号，而删除其中某一条纪录时则不会重新编号

auto_increment主键自增长必须加到主键字段上。

mysql> alter table test1 modify sid int auto_increment; -----sid必须是主键

mysql> alter table test1 modify sid int; ----删主键自增长。

mysql> create table test1(sid int auto_increment primary key,name varchar(10),age int);

query ok, 0 rows affected (0.01 sec)

11.5外键约束

作用：约束两张表的数据

create table emp(

sid int,

naem varchar(50),

age int,

detpid int

//detpname varchar(30)

)

create table detp(

detpid int,

deptname varchar(30)

)

我的公司只有两个部门

2000市场部

3000技术部

出现两种表的情况：

[if !supportLists]1.[endif]数据冗余高问题0

解决问题：独立出一张表

例如：员工表和部门表

2.满足上面的条件后新问题出现：在插入员工表数据的时候，员工表的部门id字段可以随便插入

使用外键约束：约束插入员工表的部门id字段值

解决办法：在员工表的部门id字段添加一个外键约束

注意：

主表一定要给定一个主键id，从表才能引用外键。

（因为我们的从表要参考主表）

有外键有的情况下：

1当我们插入数据的时候，我们是先插入主表数据，在插入从表数据。

2.当我们删除数据的时候，我们是先删从表，再删主表。

3.当我们修改数据的时候，我们是先修改从表，然后修改主表。

mysql> create table employee(

-> id int primary key,

-> empname varchar(20),

-> deptid int,

-> constraint emlyee_dept_fk foreign key(deptid) references dept(id) );

query ok, 0 rows affected (0.01 sec)

12.增删改（数据操作语句---dml(data manage language)）

12.1插入数据（增）insert

语法:

insert into <表名>(列名)

select <列名>

from <源表名> ------注意一下,我们的列名要保持一致

insert into <表名> values (表的字段个数给全);

insert into <表名> (列名1,列名2…….) values (列值1,列值2……);

mysql> insert into demo1 values (); ----括号里的字段必须参考我们的demo1表里的字段.

必须给全.

mysql> insert into demo1 values(1000,'甘露2',19),(2000,'付贵',18);----插入多行数据

mysql> insert into demo2 select * from demo1 ---复制一个表的数据到另一个表.

query ok, 5 rows affected (0.00 sec)

records: 5 duplicates: 0 warnings: 0

mysql> insert into demo1 (sid,name) values(3000,'王金中');------指定插入的字段

注意:每次插入一行数据，不能只插入半行或者几列数据

插入的数据是否有效将按照整行的完整性的要求来检验

如果在设计表的时候就指定了某列不允许为空，则必须插入数据

12.2修改数据(update)

语法:

update <表名> set <列名=更新值>

[ where <更新条件> ]

注意：不带条件的更新语句是不推荐。

不带条件的情况,它会影响表里面的指定列所有值:mysql> update demo1 set name='甘露';

带条件的情况:它会影响的是满足条件要求的行数:

mysql> update student set name = '帅哥' where chinese = 59;

注意:我们的带条件的更新语句并不是说只能影响一行.按照你的条件进匹配.

12.3操作符

运算表达式：

12.3.1 =等于

12.3.2 <小于

12.3.3<=小于等于

12.3.4 >大于

12.3.5 >=大于等于

12.3.6 <>不等于

between下限值and上线值

注意：下限值和上线值是包括的。

mysql> update student set chinese =10 where english>=85 and english<=98;

等价

mysql> update student set chinese =10 where english between 85 and 98;

逻辑表达式：

连接我们的条件表达式的

and所有的条件都要满足的情况下才会去匹配。

or满足其中之一的条件都能匹配。

12.4删除数据(delete)

语法：

delete from表名

[ where <更新条件> ]

不带条件的情况：

mysql> select * from demo1;

删除表中所有的数据------我们是不推荐的。

delete的语句只能删除的表中的数据。并不能删除表的约束。

truncate table表名; --------àtable可以省略,不能带条件。不能跟from关键字

使用的它只会全表删除，如果表上有主键自增长，它就会把约束的起始值重新编号。

13查询语句(dql重点)

dql其实就是sql中的查询语言，我们通过查询语句对数据库发出查询数据的指令，数据库执行dql语句是不对我们的数据有改变，但是会从数据库发送查询的结果集给我们的客户端。查询的结果集会以二维表的形式给我们展现，我们可以理解这种二维表是一个虚表，不是真实的表。

那一个完整的查询语句是一个什么样子的呢？

语法：

select column_list /*要查询的列名称*/

from table_list /*要查询的表名称*/

where condition /*行条件*/

group by grouping_columns /*对结果分组*/

having condition /*分组后的行条件*/

order by sorting_columns /*对结果排序*/

limit offset_start, row_count /*结果限定*/

/*创建部门表*/

create table dept(

deptnointprimary key,

dnamevarchar(50),

locvarchar(50)

);

/*创建雇员表*/

create table emp(

empnointprimary key,

enamevarchar(50),

jobvarchar(50),

mgrint,

hiredatedate,

saldecimal(7,2),

commdecimal(7,2),

deptnoint,

constraint fk_emp foreign key(mgr) references emp(empno)

);

/*创建工资等级表*/

create table salgrade(

gradeintprimary key,

losalint,

hisalint

);

/*插入dept表数据*/

insert into dept values (10, '教研部', '重庆');

insert into dept values (20, '学工部', '成都');

insert into dept values (30, '销售部', '北京');

insert into dept values (40, '财务部', '天津');

/*插入emp表数据*/

insert into emp values (1009, '习近平', '董事长', null, '2001-11-17', 50000, null, 10);

insert into emp values (1004, '范冰冰', '经理', 1009, '2001-04-02', 29750, null, 20);

insert into emp values (1006, '李冰冰', '经理', 1009, '2001-05-01', 28500, null, 30);

insert into emp values (1007, '高圆圆', '经理', 1009, '2001-09-01', 24500, null, 10);

insert into emp values (1008, '周星驰', '分析师', 1004, '2007-04-19', 30000, null, 20);

insert into emp values (1013, '周星星', '分析师', 1004, '2001-12-03', 30000, null, 20);

insert into emp values (1002, '周六福', '销售员', 1006, '2001-02-20', 16000, 3000, 30);

insert into emp values (1003, '康熙', '销售员', 1006, '2001-02-22', 12500, 5000, 30);

insert into emp values (1005, '李世民', '销售员', 1006, '2001-09-28', 12500, 4000, 30);

insert into emp values (1010, '孙悟空', '销售员', 1006, '2001-09-08', 15000, 0, 30);

insert into emp values (1012, '唐僧', '文员', 1006, '2001-12-03', 9500, null, 30);

insert into emp values (1014, '令狐冲', '文员', 1007, '2002-01-23', 13000, null, 10);

insert into emp values (1011, '刘欢', '文员', 1008, '2007-05-23', 11000, null, 20);

insert into emp values (1016, '小沈阳', '文员', 1007, '2008-05-23', 14000, null, 20);

insert into emp values (1015, '张浩', '销售员', 1006, '2001-08-08', 17000, 0, 30);

insert into emp values (1001, '朱元璋', '文员', 1013, '2000-12-17', 8000, null, 20);

/*插入salgrade表数据*/

insert into salgrade values (1, 7000, 12000);

insert into salgrade values (2, 12010, 14000);

insert into salgrade values (3, 14010, 20000);

insert into salgrade values (4, 20010, 30000);

insert into salgrade values (5, 30010, 99990);

13.1基础查询

13.1.1查询所有列

select * from emp;

*星号查询所有字段。

select指定查询表的所有字段。

select sid,sname,age,gander,province ,tuition from stu;

区别：使用* rdbms系统会先检测表本身，查找表有多少列，列的名字是什么，效率低，一般仅用于数据测试。指定所有列的名字，效率高，而且可以自定义列的顺序。

13.1.2查询指定列

select ename,sal,comm from emp;

13.1.3去除重复记录

select distinct deptno from emp;

把job，deptno看成一个组合，去它的重复的。

select distinct job,deptno from emp;

13.1.4列运算

1.）数值类型可以做（+ - * /）

select sal+1000 from emp;

2.）字符串类型可以拼接（连续拼接）

selectconcat('￥',sal,'工资'),ename from emp;

问题：数值类型跟null进行计算会得到结果为null。

解决：select ename,sal,comm,sal+ifnull(comm,0)from emp;

13.1.5取列别名

select ename as '员工姓名',sal as '薪水',comm as '奖金',sal+ifnull(comm,0) as '总收入' from emp;

select emp.comm,emp.empno,emp.sal from emp e;

我们使用的是as，但是我们可以把as去掉。

字段名可以打引号，也可以不打引号。表的别名打引号会报错。

13.2条件查询

运算表达式最终会给我们一个布尔类型的值，也就是真假，所以说是我们where字句的判断执行的条件。

=、!=、<>、>、<、>=、<=、between...and、

in(...):

select * from emp where empno in(1001,1002,1008,1009,1015);

is null：

select ename from emp where comm is null;

select ename from emp where comm is not null;

not:

select * from emp where empno not in(1001,1002,1008,1009,1015);

or:条件满足一个都可以。

and：必须条件都满足才会执行。

13.3模糊查询

select * from emp where ename like '%云%';

%-------它代表匹配---n个字符

select * from emp where empno = 1001 and ename like '%云%';

_ ------它代表匹配1个字符。

13.4排序

select * from emp order by deptno asc, empno desc;---先按deptno升序排，再按empno降序排列。

默认按照升序；

中文按拼音排序：

select * from emp order byconvert(ename using gbk)desc;

order by字句一定在查询语句的最后面。

单词：

desc:descend;

asc:ascend;

课堂作业：

emp表1.查询员工表中工资大于15000 且在2005年前入职的所有员工信息。2.查询销售员中工资最高的员工。3.查询文员中工资最低的员工信息。4.查询姓张的员工中工资大于20000的员工信息。5.查询工资最高的前3名员工信息。6.查询总工资最高的员工信息。7.查询有津贴且在2001-02-01前入职的员工信息。8.查询员工的姓名、部门、总工资。9.查询名字为三个字的工资最高的前2名员工信息。10.查询职位为文员或销售员中工资最高的员工信息，工资要带￥符号。

课后作业：

emp表

1.查询经理中入职时间最早的员工信息。

2.查询入职时间在2002-2004年的且有津贴补助的员工信息。

3.查询30部门中名字带木的员工信息。

4.查询名字为2个字，工资最高的2名员工信息。

5.查询工资小于10000或工资大于30000的员工的信息。

6.查询没有津贴的员工中，工资最低的2名员工的信息。

7.查询文员中入职最早的员工信息。

8.查询销售员中入职最晚的员工信息。

9.查询名字为3个字，没有津贴，工资最低的员工信息。

10.查询10号或20号部门中工资最低的员工的姓名、职位、部门、工资。

update emp表

11.把名字为3个字的员工的津贴都改为100元。

12.把文员的工资都涨100元。

13.把销售员中入职最早的员工的津贴增加200元。

14.把所有员工中入职最早的员工的工资增加200元。

15.把2002-2006年间入职的员工的津贴降低50元。

16.把30号部门的所有员工的工资改为8000元，津贴改为500元。

emp表

17.删除工资最高的员工的信息。

18.删除工资最低的员工的信息。

19.删除没有津贴，名字为2个字的员工信息。

20.删除30号部门中入职时间早的员工信息。

21.删除所有员工中名字带张的员工的信息。

13.5limit

limit是mysql的一个方言：限制显示数据的条数。

limit 100；------显示的数据为100条。那如果查出来的数据总数都不足100条。有好多条显示好多条。

limit 0，100；-------0就是指的数据的第一条开始，显示100条。

逗号前面的值表示从哪条数据开始，逗号后面的值表示显示多少条数据。

它将来可以用来做分页。

查出一张表，带来了有10万条数据。

网页来说：分成几个页面来显示呢？

一个1页面来显示-----100条数。

1000个页面。

第一个页面：limit 0, 1000；

第二个页面：limit 1001，2000；

注意:如果limit用在dml语句中，只能限定条数，而不能设置起始索引，因为没有投影查询，索引起始位置不固定。

13.6聚合函数

函数的作用是把是表中的某列进行计算，这种内置的函数求值，运算速度快。

常见的聚合函数有：

[if !supportLists]l[endif]count()：

[if !supportLists]1.[endif]selectcount(*)from emp;

查询表中总行数

[if !supportLists]2.[endif]selectcount(all comm)as计数from emp;

可省略all，查询某列中

[if !supportLists]3.[endif]selectcount(distinct comm)as计数from emp;

对去重计数非null值的个数

以上的结论：count(*)是求表中的数据条数。

count（字段）指明字段求数据的记录，会过滤掉有null值这个记录。

注意：如果不适用分组，count()不能与其他列名并列使用；

[if !supportLists]l[endif]max()：是求纵向列最大的值。

[if !supportLists]l[endif]min()：求纵向列最小值。

以上两种往往给定的字段的类型是数字型。

[if !supportLists]l[endif]sum()：这个是求纵向列的值的和。不能使用*，如sum(*)。也可以使用all或distinct。

[if !supportLists]l[endif]avg()：这个是求纵向列的平均值。不能使用*，如sum(*)。也可以使用all或distinct。

注意：如果说是sum()和avg()指定的字段类型是字符串的话，求出的结果值是0；

我们往往是指定数字型的字段。

其他常用函数：

http://blog.csdn.net/sugang_ximi/article/details/6664748

round(comm,1)对数字列四舍五入精确到1位小数

year(字段)取出年份

month(字段)取出月份

now()当前系统日期

curdate()当前日期

curtime()当前时间

select * from emp wheredayname(hiredate)='thursday';

select * from emp wherequarter(hiredate)=2;

select*,monthname(hiredate)月份from emp where monthname(hiredate)='may';

课堂作业：

[if !supportLists]1.[endif]查询员工的总人数；

[if !supportLists]2.[endif]查询员工的平均工资；

[if !supportLists]3.[endif]查询文员的总人数；

[if !supportLists]4.[endif]查询30号部门中文员的总工资；

[if !supportLists]5.[endif]查询10号部门中员工的最低工资；

[if !supportLists]6.[endif]查询入职时间在2001-2005的员工的最高工资

[if !supportLists]7.[endif]查询2005年之前入职的员工的平均工资

[if !supportLists]8.[endif]查询2002年之后入职的员工的最高工资和最低工资和平均工资。

[if !supportLists]9.[endif]查询30号部门的最高工资、最低工资、平均工资；

[if !supportLists]10.[endif]查询10号或20号部门的文员的最高工资、最低工资、平均工资；

13.7分组查询

group by ------

1.单级（单列）分组：

按部门分组求部门的平均工资。

select deptno,avg(sal)

from emp

group by deptno

---带分组条件的having字句

按部门分组后求部门平均工资大于20000的部门。

select deptno,avg(sal)

from emp

group by deptno

having avg(sal) >20000;

2.多级（多列）分组：

select deptno,job ,sum(sal)

from emp group bydeptno,job

having sum(sal)>10000;

先按deptno分组再按job分组。

在group by子句后用with rollup会增加一个汇总行。

注意：having子句一定是跟在group by字句后面的。它是对分组记录过滤。

有where子句的时候，是先执行where的条件。然后才分组过滤。

13.8使用having字句

使用聚合函数的值来过滤查询结果。

where字句不能用聚合函数。

select deptno ,sum(sal)

from emp

group by deptno

having sum(sal)>100000;

having字句要放在group by字句的后面。

13.9 sql语句的执行顺序

课本72页

select sid,avg(mark) as averagemark

from studentexam

where sid>10

group by sid

having avg(mark)<50 or avg(mark)>70

order by averagermark

limit 0,100;

from----where ----group by ---select ----having ----order by ---limit

课堂分组作业：

[if !supportLists]1.[endif]查询各个部门的最高工资、最低工资、平均工资。

[if !supportLists]2.[endif]查询各个职位的平均工资降序排列

[if !supportLists]3.[endif]查询平均工资大于10000的岗位

[if !supportLists]4.[endif]查询每个部门的人数

[if !supportLists]5.[endif]查询人数大于5的部门

[if !supportLists]6.[endif]查询部门人数小于3的部门的平均工资、最高工资、最低工资

[if !supportLists]7.[endif]查询各个部门中工资大于10000的人数降序显示

[if !supportLists]8.[endif]查询各个岗位中工资大于10000的人数升序显示

[if !supportLists]9.[endif]查询平均工资大于10000的前两个部门。

[if !supportLists]10.[endif]查询每个部门中没有津贴的人数。

课后作业：

创建学员信息表t_u29，要求字段有（主键id、学号、姓名、性别、年龄、身高、左边口袋的钱、右边口袋的钱、手机号、家庭省份、备注）。主键自动递增，合理定义列名称及数据类型，姓名、学号唯一非空约束，性别默认为男，。完成以下操作：

1）、插入u29所有学员的信息，至少10个以上。

建表语句与数据插入语句写在这里

2）、将学生主键id等于5的学生的电话改成13345678901

3）、将学生年龄在18到30岁之间的学生的地址改成空值

4）、将口袋的钱少于100元的男生的电话改成null值

5）、将身高160以上的女生的年龄改为22岁。

6）、查询所有学员的所有信息并按学号降序排序；

7）、查询所有学员的学号、姓名、性别、年龄、身高、手机号这6项信息，并且取中文别名；

8）、查询所有学员的学号、姓名、身上钱的总和，并且要求中文别名；

9）、找出22岁以上的学员的所有信息并按年龄升序排序；

10）、找出22岁以上、并且身高高于160的男生所有信息；

11）、找出年龄在20至27岁之间、并且身高高于160、并且左边或右边口袋有钱的学员所有信息；

12）、找出年龄不是20岁或21岁或24岁或26岁、并且左边或右边口袋有钱、

并且钱总和大于100的男生的学号、姓名、

性别、年龄、左边口袋的钱、右边口袋的钱、

手机号这7项信息，并按年龄升序，按学号降序排序，

并且要求中文别名，并分页，每页显示6条数据；

13）、查询所有左边口袋有钱的男生的所有信息

14）、查询所有低于25岁且身高小于160的女生的所有信息

15）、查询身上总金额大于300的男生的省份分布情况。

16）、查询30岁及30岁以上的姓张的男学员信息。

17）、查询左边口袋钱低于10块的25岁以下的男生所有信息。

18）、查询姓张或姓杨的，23岁以下或30以上的男生的姓名和年龄

19.查询男和女各有多少人升序

20.查询18岁以上的男女各有多少人降序

21.查询男生和女生的平均年龄降序

22.查询各个省份学生的的人数

23.查询各个省份中男生和女生的人数

24.查询各个男生人数大于3的省份，要求显示家庭省份、性别、人数；

25.查询年龄大于20或年龄小于15的男女各多少人；

26.查询身上的钱的和大于100元的男女各多少人；

27查询男生和女生身上钱的平均值分别是多少；

28.查询身高大于160的男女分别多少人

29.查询每个省份学生的平均身高，只显示平均身高大于160的省份。

30.查询每个省份中男女学生的平均年龄，只显示平均年龄大于18的。

14.组合查询

14.1子查询：查询中嵌套着查询

一）子查询语句主要出现的位置：from语句后-------（子查询语句）作为表来用

where语句后------（子查询语句）作为条件用

二）子查询结果集的形式：

Ø单行单列（用于条件）

查询比徐可工资都高的员工？？

1. select sal

from emp

where ename = '许可';

2. select *

from emp

where sal > (select sal

from emp

where ename = '许可');

查询比30号部门的最高工资都高员工的薪水？

1. select max(sal)

from emp

group by deptno

having deptno=30;

select *

from emp

where sal >(select max(sal)

from emp

group by deptno

having deptno=30);

Ø单行多列（用于条件）-----可以成一个对象实体

求跟王金中不同工作并且不同工资的员工的信息？

select *

from emp

where (job,sal)not in

(select job,sal from emp where empno=1002);

Ø多行单列（用于条件）-----可以看成一个集合，如果是集合，我们就可以把用

in运算符all any关键字

求比30号部门所有员工的薪水都要高的员工的薪水？

1. select sal

from emp

where deptno=30;

2.select *

from emp

where sal > all(select sal

from emp

where deptno=30);

Ø多行多列（用于表）

select e.收入

from (select ename,(sal + ifnull(comm,0)) as '收入'

from emp

where deptno =30) e;

where后，子查询作为条件的时候，还可以用all any关键字

14.2合并结果集

合并的结果的字段个数，字段类型要一样。

注意，字符串类型可以跟数字类型合并。

union：去除重复记录

select * from a

union

select * from b;

union all：不去除重复记录

select * from a

union all

select * from b;

15.联接

15.1内连接

内连接查询出的所有记录都满足条件

--方言

select *

from emp,dept

where emp.deptno = dept.deptno;

-- sql标准

select *

from emp inner join dept

on emp.deptno = dept.deptno;

--自然连接

select *

from emp natural join dept

注意：它会去匹配两张表相同字段名称相对关系。

15.2外连接

---左外连接

select e1.ename,e2.ename

from emp e1 left outer join emp e2

on e1.mgr = e2.empno;

左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为null

--右外连接

select *

from emp e right outer join dept d

on e.deptno = d.deptno;

右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为null

全外连接：mysql是不支持的

但是可以用：

select *

from emp e right outer join dept d

on e.deptno = d.deptno

union

select *

from emp e left outer join dept d

on e.deptno = d.deptno;

16.补充

一）表与表的几种关联关系：

一对一的关系：

需求：一个老公对应一个老婆，一个老婆也只能对应一个老公

抽取实体模型（java中一个类：pojo，entity）：

create table husband

(

hid int auto_increment primary key,

hname varchari(50)

)

create table wife

(

wid int auto_increment primary key,

wname varchar(50),

constraint fk_wife_husband foreign key (wid) references husband(hid)

)

注意：要建议一对一的关系，把wife的主键设为外键就ok了。

一对多的关系：这种关系，也是最常用的。

多对多的关系：

需求分析：我们的需求分析师来做的。-------需求----à业务模型

需求问题：一个学生可以学习多门课程，一门课程可以被多个学生学习

------业务模型：学生模型：名字，年龄，。。。。。。。。。。。。。。

课程模型：课程名称，课程时间。。。。。。。。。。。。。

------设计实体：（数据库，对应我们的表，java：class）

概要说明书：

详细说明书：详细的表明，详细的类，字段。。。。。方法。。。

create table student

(

sid int auto_increment primary key,

sname varchar(50)

)

create table courses

(

cid int auto_increment primary key,

cname varchar(50)

)

如果要实现多对多的关系：先人已经终结了一下，再创建一张中间表来维护这种关系。

中间表

create table stu_cou

(

sid int,

cid int,

constraint fk_stu foreign key (sid) references student(sid),

constraint fk_cou foreign key (cid) references courses (cid)

)

多对多的关系：它的这种关系是被一张中间表维护着，外键在中间表里

二）级联操作

外键约束存在问题：当我们删除数据的时候，我们是先删从表，再删主表。

当我们修改数据的时候，我们是先修改从表，然后修改主表。

解决：我们如果加了级联操作的话，我们就可以先删或修改主表

emp表（从表）

create table emp

(

empno int auto_increment primary key,

ename varchar(50),

deptno int,

constraint fk_emp_dept foreign key(deptno) references dept(deptno) on delete cascade on update cascade

)

dept表（主表）

create table dept

(

deptno int auto_increment primary key,

dname varchar(50)

)

三）触发器：

例子：比如说当你操作公司里的某一个数据表的时候，你操作后，我们给你记录下来。

student表------insert into一条数据，就在另外一张log表里把记录下来。

student表

create table student

(

sid int auto_increment primary key,

sname varchar(50)

)

logss表

create table logss

(

logid int auto_increment primary key,

loginfo varchar(200)

)

创建触发器

create trigger strigger_stu_log

after insert on student for each row

insert into logss values(null,'你插入了一条数据');

四）数据库的设计

三大范式：

1nf:原子性，列的字段不可再分解

student表：sid name age………

realname aliasname ------name

李四狗娃李四/狗娃

2nf:在1nf基础上，字段列表达的是一个意思，其他的字段列跟主键字段有相关

user表：sid , username password age goods(商品)

goods表：did dname………..,…..

3nf:在2nf的基础上，除开主键字段其他列字段跟主键字段有绝对的依赖关系

user表：sid , username password age goods(商品) goodsname

goodsid

违反了第二范式，增加数据的冗余。

goods表：goodsname………goodsid

附加题：求每年收入的增长率

create table lovo

(

yr int, --年的字段

money int --年收入

)

insert into lovo values(2008,100);

insert into lovo values(2009,150);

insert into lovo values (2010,500);

insert into lovo values (2011,800);

insert into lovo values(2012,1500);

select l2.yr,concat(ifnull((l2.money-l1.money)/l1.money*100,0),'%') as增长率

from lovo l1 right outer join lovo l2

on l1.yr+1 = l2.yr;

MYSQL数据库安装详解

猜你喜欢

热点阅读