MySql1数据库(简介、数据库操作、数据表操作)
一、1定义:安装了数据库服务器软件的计算机。
功能:提供数据的查询,更新等。
数据库服务器中date下一个文件夹对应一个数据库,也就是对应一个应用(商城系统,APP)
数据库服务器(1)--数据库(N),对应关系一对多
数据库下是表格(table),表格中每一
行称之为一条
记录,每一
列称之为
字段。
基本操作:增、删、改、查
2、mysql客户端
1、cmd模式的客户端:开始-->msql文件夹--》MySQL 5.5或
mysql -hlocalhost -p3306 -uroot -proot
2、网页客户端(1)拷贝phpmysql文件夹到虚拟站,(2)打开php.ini,搜索session.save_path,设置为"E:/CZwj/wdc",
(3)extension=php_mbstring.dll去除注释,(4)通过域名打开phpmyadmin
3、Navicat for MySQL:无需安装,只需要输入服务器参数即可
数据库系统DBS: DBMS+DB
3、数据库管理系统DBMS(database management system)
操作和管理数据库的大型软件,用于建立、使用和维护数据库(沟通客户端及数据库服务器两者的桥梁)。
主流的DBMS: Access:微软office小型,MySQL:大中型。
Oracle:大型、超大型数据库管理系统(体现公司实力)。
4、数据库的组织结构
-
数据表(table)-
记录(行,row,record)-
字段(列column,field) -
数据
各概念之间的关系
- 客户端和数据库服务器之间通过DBMS连接(桥梁)
- 数据库服务器中可以存储多个数据库(不同的产品)
- 每个数据库中可以存放多个数据表(不同方面的信息)
- 数据表可以存储N多记录(N多个体的同一方面记录)
- 数据表有多个字段(个体信息的各个
层次型数据库,网状型数据库,关系型数据库,非关系型数据库
数据冗余 :数据冗余只能减少不能杜绝,优点节约空间,缺点效率降低。
数据完整性:数据完整性=数据正确性+数据准确性
5、关系型数据库
- 什么是关系?就是
一张二维表,每个关系都具有一个关系名,就是通常说的
表名。一个关系数据库可以包含多个关系。通过公共字段去连接。
- 定义:采用
关系模型来组织数据的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
- 主流产品:Oracle、MySQL、Access、DB2、SQLServer、sybase等
- 优点:使用二维表组织数据(
结构清晰),使用标准语言。表和表之间独立,查询效率高。
- 缺点:查询关于个体的完整信息,
需要连接数据表。(单个数据表中只存储某一方面)多表查询效率低。
6、SQL(Structured Query Language)结构化查询语言。
-ACCESS:SQL -SQL-SERVER:T-SQL MYSQL:MYSQL ORACLE:PL/SQL
特点-1.关系型数据库标准语言。2. 数据库脚本语言的后缀。
- 分类- 1数据
查询语句(DQL,Data Query Language):SELECT
- 2数据
定义语句(DDL, Data Definition Language):CREATE、DROP(删除数据库)、ALTER
- 3数据
操纵语句(DML , Data Manipulation Language):INSERT、UPDATE、DELETE
- 4数据
控制语句(DCL, Data Control Language):GRANT、REVOKE(关于用户授权)
二 MySQL服务器的启动和停止
1、通过“计算机管理”中的“服务和应用程序”(或services.msc )-->服务-->服务名称为:MySQL
2、管理员权限:命令行启动net start mysql---停止服务net stop mysql
三 MySQL服务器登录及退出--命令行
登录MySQL服务器- 语法:mysql[.exe] [–h主机名] [–P端口号] –u用户名 –p密码 //大写P代表端口号
例:mysql -hlocalhost -p3306 -uroot -p再以密文输入密码,保护密码安全性 mysql -uroot -p也可以登录
mysql -hwww.wdc.com -p3306 -uroot -p //-h……可以写ip地址,也可localhost,也可写在Hosts中转换为127.0.0.1的域名
- 命令结束标志 Commands end with ; or \g. //分号;和\g都可以结束语句。
- 服务器版本号Server version: 5.5.49 MySQL Community Server (GPL)
退出MySQL数据库服务器(三种命令):exit,quit,\q
四 数据库操作
(一)查看服务器中的所有数据库:SHOW DATABASES;
Information_schema,Performance_schema,mysql,test
初始创建的
四个数据库,三重要的数据库(勿删除勿修改!!!)
1- Information_schema:信息目录。保存如:数据库名或表名,列的数据类型,字符集和校对规则,或访问权限等。虚拟数据库。
2- Performance_schema:数据库服务器性能参数。
3- mysql :系统数据库。数据库服务器用户信息、权限等。
(二) 查看数据库创建语句:SHOW CREATE DATABASE db_name; //注意这里DATABASE没有带S
可查看该数据库的创建语句及默认字符集设置。
(三) 创建数据库:CREATE DATABASE [IF NOT EXISTS] db_name [CHARSET 字符集];
1、db_name命名规范。 例:create database db1 charset=utf8;
- 由字母、数字、下划线组成;
可以使用
数字开头,
不能使用
纯数字的名称;
- 使用系统关键字作为表名时,使用
`~~
` (反引号`)包括起来 ; - 长度不超过64个字符。
- 字符集:utf8,latin1,gbk等。
2、 查看是否创建成功
创建完数据库以后,会同时创建了一个文件夹,文件夹中初始只有一个opt文件,里面是数据库的字符集编码以及校验级(utf8a-general_ci)。
可以使用查看数据库/查看数据库创建语句进行查看, 也可在磁盘中进行查看。
(三) 选择数据库:USE db_name; ---对数据表进一步
操作时需要先选择数据库。//注意不能少
(四) 删除数据库:DROP DATABASE [IF EXISTS ] db_name;例drop database if exists db1;
- 注意:谨慎操作,三思而后行。- 三个重要的数据库,勿进行删除操作
- if exists判断数据库是否存在,为可选项。不添加时,删除不存在的数据库,返回error信息。添加时,删除不存在的数据库,返回warning。错误等级不一致。
(五) 重命名数据库(少用)-原理:创建新数据库,导入原数据库的数据,删除原来的数据库。无法通过单一的语句实现。
(六) 修改数据库 alter database 数据库名称 charset=字符集编码; 例:
alter database db1 charset=utf8;
注意:mysql的字符集utf8中间不加横线,修改数据库
只能修
改字符集编码和校验集。
五、 数据表操作
1、显示所有数据表:SHOW TABLES [FROM 表名]; ---例
show tables from jike_chat;
- 注意:1已经选择数据库时可以省略后面的from子句,2可以使用\s查看当前已经选择的数据库,3可以查看非当前选择数据库中的数据表
2、显示数据表结构
1. 通过phpmyadmin查看
2.语法
DESC tb_name; 或者
DESCRIBE tb_name;
显示包含字段(field)、字段类型(type)、是否为空(null)、是否为索引(key)、是否有默认值(default字段属性)等。例:desc db1;
+---------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+----------------+
| ID | int(4) | NO | PRI | NULL | auto_increment |
3、 显示数据表创建语句:SHOW CREATE TABLE tb_name; ---例:
show create table db1;
- 注意:1可以将’;’换成\G,显示更清晰 2可查看完整的数据表创建语句。
3括号中为对字段的描述,包括字段名称,字段类型,字段属性。
4括号外为表属性,一般包括存储引擎(engine),自增长起始值(auto_increment),默认字符集等。
4、创建数据表
create table [IF NOT EXISTS] table_name(
col_name字段名称 col_type字段类型 col_attr字段属性 , //3部分组成
字段名称 字段数据类型 [not null][default][primary key][unique key][auto_increment][comment],
字段名称 字段数据类型 [not null][default][primary key][unique key][auto_increment][comment],
字段名称 字段数据类型 [not null][default][primary key][unique key][auto_increment][comment]
………………
)[engine][auto_increment][charset][collation][comment];
字段属性有6个,不分先后顺序
注:一个表可以有多个字段,各个字段之间用英文下的逗号‘
,’
隔开。最后一个字段不能使用‘,’
set names gbk;//在cmd中必须设置,否则返回ERROR 1067 (42000): Invalid default value for 'gender'
例 create table if not exists stu1(
id int not null primary key auto_increment,
name varchar(4) not null unique key,
gender enum('男','女','保密') not null default '保密',
age tinyint not null default 23,
salary float not null default 0,
grad_time date comment '毕业时间',
intro text comment '自我介绍' //注意这里不能有,逗号
)charset =utf8;
5、MySQL字段类型初步介绍
1. 整型int(0-21亿)、tinyint(0-255)--去掉符号位的范围 例:行号,年龄,产品数量等
2. 浮点型float,Double 例: 重量,工资,奖金等
3. 字符和文本型char(M)、varchar(M)、text,注意:char为定长字符类型,Varchar变长的字符类型。
char(M)、varchar(M)的M代表最大的存储长度,不可省略。例:文章标题,产品描述等
4. 日期时间型Date('年-月-日',2016-06-12),time('时:分:秒'),datetime('年:月:日 时:分:秒') 例: 出厂日期,文章发布日期,更新日期等
6、列的属性
1.null | not null指定该列存储的值是否可以为空。
2.default指定该列的默认值。
3.auto_increment自动增长,默认从1开始,依次递增整数,auto_increment只能和主键进行搭配 (行号,auto_increment只能有1个,且必须为索引)。
4.primary key是否主键,用于惟一地标识表中的某一条记录。一个表只能一个主键,主键不能重复,也不能为空。
key:索引,查字典使用的目录,快速定位数据。
5.unique key设置该列的值是唯一的,不可重复,但可以为空。一个数据表可以设置多个唯一索引。
6.comment备注,解释性文字,指定该列的说明文字。
- 注意事项:1.列的属性不分先后顺序;2.一个列可以定义多个属性,多个属性之间用空格隔开即可;
7、 表属性
1.[ charset=' 字符集名称 ']指定数据表的字符集;
2. [ auto_increment = number ]设定自动增长列初始值;默认1.例 charset =utf8 auto_increment=100;//从100开始
3. [ comment = ' 表的注释 '] 给表添加备注。
4. [engine = ' 存储引擎名称 ']设置数据表的存储引擎
例:)ENGINE=InnoDB charset =utf8 auto_increment=100;//从100开始
- 注意事项1-各属性均为可选项,省略时使用默认值。2-
自增长起始值设置只能在表属性中进行设置。
六修改数据表
1、添加字段---语法:alter table 表名 add 字段名 字段类型 字段属性 first|after 字段名;
ALTER
TABLE table_name
ADD
[ COLUMN ] col_namecol_type col_attr [FIRST | AFTER col_name] ,
ADD
[COLUMN] col_name col_type col_attr [FIRST | AFTER col_name] ,
……;
- 注意- 字段名称(col_name),字段类型(col_type),字段属性(col_attr),三者之间的顺序不可颠倒。
默认字段添加至最后。可以使用after,first改变字段添加的位置。
例:
alter table student1 add ismarry char(1) not null default '否';
2、修改字段modify,change
仅修改数据表字段的类型及属性
alter
table tb_name
modify
col_name col_type col_attr;
可同时修改数据表字段
名称
及字段类型、属性
alter
table tb_name
change
col_namenew_col_name col_type col_attr;
例:alter table student1 change ismarry address varchar(20) default '南方';
注意:
可以同时修改多个字段的类型及属性,直接在原来的语句后添加
modify….
即可。
如果修改字段时没有设置属性,他会自动将原来的字段属性还原,但是主键不需要重新设置,会自动保留。
3、删除字段
ALTER
TABLE table_name
DROP
[COLUMN] col_name, DROP [COLUMN] col_name,
……;
注意:可
以同时删除数据表的多个字段 例:alter table student2 drop city;
删除unique key唯一键:
ALTER
TABLE table_name
DROP Index
col_name;
例alter table student1 drop index name;
4、重命名数据表:
alter
table tb_name
rename
new_tb;
注意:
重命名数据表,数据不丢失。
5、删除数据表:
drop
table [if exits] tb_name;
注意删除操作,三思而后行。删除的是:表结构及数据。
6、复制数据表结构
create table tb_name1 like tb_name2; 例:create table stu2 like stu1;
注意:
复制表结构,不包含数据,
能够完整复制表结构(包括主键及自增
长)
7、复制数据表数据
create table 表名1 select * from 表2;
8、insert into 表名 values(数据);---作用: 给表格插入数据 //注意:是values不要漏了s
例:insert into student1 value(id,'张三','男','否',21,1000,'2017-01-01','杭州',null,'浙江');
9、select * from 表名---作用: 显示表单中的所有内容select * from student1;
MySql2数据库(字符集、存储引擎、数据类型)
一、字符集 :字符及其编码集合就是字符集(character set)。
(1) 常用字符集:ASCII,
big5(繁体中文),GB2312,GBK(简体中文)一个字符是两个字节,其中英文和数字还是一字节
latin1(英文字母)一个字符是一个字节
UTF8(8-bit Unicode Transformation Format国际编码)一个
中文字符是3个字节其中英文和数字还是一字节
查看服务器可用的字符集: 1.使用phpMyAdmin查看 2.
cmd--show character set;
查看当前环境数据库各个结构的字符集编码:show variables like 'character_set_%';
(2)校验集:对于数据英文字母大小写是否进行区分。查看:
show collation;
1.
校对规则 :是指
在同一字符集内字符之间的比较排序规则。(collation,collate,整理)
2.校验集命名规范3种
以校对规则对应的字符集名称开头,中间部分通常为语言,以_ci/_cs/_bin结尾。
1-
_ci:表示大小写
不敏感insensitive(A==a),2-
_cs:大小写
敏感sensitive(A!=a),3- _
bin:按ASCII
编码值比较a>A。
(3)字符集和校对规则对应关系
一种校对规则,唯一对应一个字符集。
一个字符集可以对应多种校对规则,其中有一个是默认校对规则(Default YES);
(4)字符集及校对规则的设置
1、查看当前环境的字符集设置
show variables like 'character_set%';
2、客户端设置:
设置接收客户端的字符集编码set character_set_client=gbk;
设置返回客户端的字符集编码set character_set_results=gbk;
设置完毕以后就可以存储或者读取中文字符。
快捷设置:
set names gbk 可以同时设置三个值,分别是
接收客户端数据,
返回客户端数据和
连接处理层的字符集编码。
set 是会话模式,意味着
一旦退出,就
还原,所以建议在每次登陆数据库的时候,第一时间输入
set names gbk;
cmd的字符集编码是gbk不能修改。所以要让cmd处理汉字,必须每次进入mysql数据库设置set names gbk;
3、服务器端设置:
继承关系:
创建数据库时不指定字符集,数据库继承数据库服务器默认字符集
创建数据表时不指定字符集,数据表继承数据库字符集
可以设置字段字符集,字段字符集优先级最高。
一般情况下,工作中不会给字段单独设置字符集编码,所以一般是以表格的字符集编码为主。
服务器端设置默认的字符集编码:
1.打开my.ini,搜索character-set-server,可以修改默认的字符集编码。重启mysql即可。
这个修改是永久生效。语法:
character_set_server=utf8;
2. set character_set_server = 字符集编码, 会话模式的修改,当退出的时候会自动还原。
(5)创建和修改数据库,表格和字段时
字符集编码以及校验集设置和修改
创建数据库时修改:
create database 数据库名称 charset=字符集 collate=校验集
创建
表格时修改:create table 表名()
charset=字符集 collate=校验集
创建
字段时修改:create table 表名( 字段名 字段数据类型 字段属性
charset 字符集 collate 校验集);
注意:设置字段时,校验集和字符集不能加上=,用空格区分。
修改数据库时修改:alter database 数据库名称 charset=字符集 collate =校验集
修改
表格时修改:alter table 表名 charset=字符集 collate=校验集
修改
字段时修改:alter table modify 字段名 字段类型 字段属性 charset 字符集 collate 校验集
二
存储引擎
数据表在内存或磁盘上的存储方式。存储引擎关系到数据表的性能。
- 查看MySQL支持的存储引擎:
show engines;
1、
Innodb存储引擎: Windows环境下MySQL数据库数据表的默认存储引擎
创建一个InnoDB表,对应在磁盘内db1下创建一个(
.frm)
表结构文件,所有的InnoDB
表数据都放在mysql的data文件下,文件名是
ibdata。数据较多时可以自动分为ibdata1,ibdata2…
引擎特点:1- 数据更新速度快,2- 支持事务,-不易产生大量垃圾碎片;3- 插入和读取稍慢;
2、
Myisam存储引擎
- 当创建一个MyISAM作为存储引擎的数据表时,就会在磁盘上建立三个文件:
1 .frm 表的结构文件;
2 .MY
D 表的
数据文件;
3 .MY
I 表的
索引。(索引相当于目录。实现数据的快速定位)
特点:MyISAM存储引擎数据表
独立于操作系统,可轻松
备份及移植数据,将三文件复制到别的数据库即可;
1- 数据表拥有较高的写入和查询速度;2- 数据更新效率低,3-
不支持事务,4-易产生大量垃圾碎片
3、
存储引擎修改:
alter table tb3 engine innodb;
4、
存储引擎的选择
- myisam:支持全文索引和表压缩
写入、读取数据速度非常快,适合使用场合dedecms/phpcms/discuz/微博系统,新闻网站等写入、读取操作多的系统。
- innodb:支持事务和外键
更新速度比较快,适合业务逻辑比较强的系统,修改操作较多的,例如ecshop商城系统、crm、办公系统。需要事务支持等场合,如银行等。
insert into 表 select * from 表(蠕虫复制);自己复制自己,成次方增加。
三MySQL数据类型
分类
- 整型:tinyint、smallint、mediumint、int、bigint占用字节分别为1、2、3、4、8
- 浮点型:float、double、decimal 占用字节分别为4、8
- 字符型: char、varchar、enum、set
- 文本型:tinytext、mediumtext、text、longtext,blob等
- 日期时间型:date、time、datetime、timestamp、year
1. 整型
Int[(M)],Smallint[(M)],tinyint[(M)]……- M表示
最小的显示宽度,表示最小M位,不足M位前补0
-[(M)]可选,表示整型的最小显示宽度。如果整数不够指定宽度,则左侧用空格填充。如果整数超出M指定的宽度而不超出表示范围,则值不受影响。
1.1Zerofilll 零填充属性:例---id int(5) zerofill
表示不够显示宽度[(M)]大,将原来默认的空格填充改为
零(0)填充。
- 必须位于类型之后,not null 之前。- 添加zerofill 属性后,自动添加unsigned属性。
1.2Unsigned无符号属性--语法:字段名 int unsigned 字段属性
添加unsigned属性之后,字段不可以存储负数。从0开始正数部分相应的扩大一倍。
如果不存储负数,尽量添加unsigned属性。
1.3 整型类型的选择
3.1首先考虑表示范围,其次是存储空间。
3.2尽量添加unsigned属性(不存储负数时)。
3.3Tinyint适合存储年龄,身高(cm为单位),分类编号,学院编号等
3.4mediumint unsigned,int unsigned
2、浮点型:float[(M,D)]、double[(M,D)]、decimal[(M,D)]占用字节分别是4、8、(约M/2+1)
M--
有效数字的位数,D--小数数字的位数。对于0.12345,M= 5 ,D=5
float
默认情况下为6位有效数字,超出6位四舍五入。double
默认情况下为16位有效数字
Decimal,默认的为(10,0)。存储小数时需要修改M,D.
Decimal
数据类型最多可以存储
30
位小数:
- Float,double不精确存储,类似工资的数据不能用浮点型存储。
要精确存储时使用定点型。Decimal是小数和整数分开存储,小数30位内永远精确。
- 如果可以使用整型存储,不建议使用浮点型。
不指定M,D时,float,double存储情况:
3、字符型:
3.1 char(N)和varchar(N)最大支持字符分别为255和65535
M表示最大的存储长度,指定char或者varchar的时候
必须要指定最大长度。
注意:一条记录最多支持65535个字节
Char和varchar区别:
Char是
定长字符串。不足最大长度,使用空格填充。固定占N字符
Varchar
变长的字符串,
专门使用1到2个字节
保存字符串
长度。最小1字符,最多占N+1字符。
空间上varchar较为灵活,
处理速度上char较高。
3.2枚举:enum枚举类似于html中的单选框,也就是给出几个选项,必须要从其中选择一个。
enum中的选项存储在mysql中,底层是以整数进行存储的,1代表第一个选项,2代表第二个选项。。。。。
使用()来罗列可选项,每个选项使用
' '包括。字段的默认值只能是罗列的值中的某一个。
如果存储的是null,null存储进去以后,是以整数0进行存储的。 最大取值65535
例:alter table student modify gender enum('男','女','保密') not null default '保密';//注意enum也是字符型,不能和float ,int等同时使用
3.3集合:set类似于html中的复选框,可以一次性选择多个值。 最大取值64。
第一个是2^0,第二个是2^1,第三个是2^2以此类推。选择多项直接将几项的值相加即可。
集合存储内容底层代码就是一个按位或的过程。
思考: 若一个枚举占用的空间是2个字节,这个枚举型的数据最多可以有多少个选项?答案:65535个选项。
思考: 已知一个集合占用空间是8个字节,这个集合型的数据最多可以有多少个选项?答案:64个选项。
一个字节8位, 一个中文字符GBK下2个字节,UTF8下是3个字节
3.4文本型
数据类型tinytext、text、mediumtext、longtext分别占用空间2^8-1、2^16-1、2^24-1、2^32-1。
数据类型tinyblob、blob、mediumblob、longblob分别占用空间2^8-1、2^16-1、2^24-1、2^32-1。
Blob用来存储媒体类型等文件的二进制字符串信息等。
注意:通常情况下,我们不会将图片,音乐或者视频直接存储在数据库内部,因为占据的空间很大,我们通常情况下存储的就是文件所在的路径。
3.5日期时间型
date 的取值范围是从0-0-0一直到9999-12-31
除了使用字符串来存储,同样也可以用数字进行存储,但是存储的数字需要保证8位。
time 除了使用字符串来存储以外,也可以用数字存储,并且没有位数要求。例20170104
时间戳是从1970-01-01早上8点开始计算一直到2038-01-19 11:14:07结束。
year取值是1901--2055年,如果year只存储2位数,如果这个数字小于70,则是20XX年,如果这个数字大于70,则是19XX。
仅添加name,work_year字段数据时,upd_time(timestamp类型)字段数据进行自动更新,获取系统当前时间:
Timestamp具备默认值和自动更新功能,可以用来记录数据的更新时间。datetime是从00年00秒开始
可以使用函数 now() 或 current_date变量、current_time变量、current_timestamp变量插入当前日期时间信息。now()函数可以获取系统当前时间。
4、类型总结
类型选择原则1、满足表示范围2、尽可能占用少的空间(能用整型尽量使用整型)
部分日期时间型类型可以使用int代替。
int-->tinyint,mediumint
unsigned
int<--float
char<-->varchar
varchar->enum/set
datetime,timestamp->int
MySql3数据库(
增删改查 ,mysql运算符,项目数据库流程
)
一、 数据库的原子操作CURD:增删改查介绍
C:Create( insert ),写入
U:Update,更新
R:Read(select):查询
D:Delete(drop)删除
二 、插入操作
语法1:insert into 表名[('字段1','字段2',…)] values ('值1','值2',…);
例1:insert into student1 (name,age) values ('张小兽',23);
例2:insert into student1 values (null,'宋小花',2,'否',20,default,20110801,'大美女','杭州');
注意:1.1 不指定插入的字段,那么values后面的值和原来表中的字段是一一对应的关系。
1.2如果指定插入的字段,指定字段的顺序可以和表格的顺序不一致。
1.3插入字段值的顺序一定要和指定的字段一一对应。
语法2: insert into 表名set 字段1='值1',字段2='值2',…..;字段名称与字段值一一对应:
例:insert into student1 set name='郑容和',age=27,salary=3000;
语法3:insert into 表名 [('字段1','字段2',…)] values(记录1),(记录2)….;//同时插入多条记录
例:insert into student1 (name,age) values ('郭靖',23),('黄蓉',22),('杨过',25),('小龙女',26);
注意:字段列表根据需要填写,字段列表省略时,记录中各个值与数据表中字段的顺序要一一对应。
语法4:insert into 表名 (字段列表) select 字段列表 from 表名;
例:insert into student1(name,intro) select name,intro from student1;//字段顺序要一一对应
蠕虫复制:快速数据填充(是自己复制自己)
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'//如果设置了UNIQUE KEY `name` (`name`)唯一值,
则无法蠕虫复制,解决办法
alter table student1 drop index name;
三、更新操作
语法:update 表名 set 字段1=值1,字段2=值2,…[where 条件];
强调:通常使用where条件来筛选所更新的记录,不添加where条件会导致整个数据表的对应字段数据被修改:
例1:update student1 set salary=salary+1000 where gender='女' and address='南方';
例2:update student1 set gender='男' where id=9or id=11;//同时修改两条记录
注意:
where条件通常
不可省略。如果省略,则更新整个数据表记录。
四删除、清空操作
1.删除数据操作---语法:delete from 表名 [where条件];
例
delete from student1 where id=6;//执行delete后id=6无数据,但id会保留。
强调: 1.1通常条件下,
where子句
不可以省略,省略时,会
删除整张数据表的
数据,
结构不变。
1.2对于有自增长属性的字段,删除整张表记录后,自增长id的起始值不会置1,而是等于原id+1.
2.清空truncate操作---语法:truncate 表名;
注意:清空整个数据表的记录。结构不变,id起始值重置为1。
3. delete/drop/truncate比较
3.1delete不加where条件,删除整个表的数据,auto_increment字段的值不清除(继续累加)。
delete from
逐行删除。效率低。删除数据,不删除数据表结构。
3.2truncate不能加where条件(会报错)。自增长字段,值清除,从1开始。(销毁原来的数据表,并创建新数据表)。
一次性删除。删除
数据,不删除结构。一般来说,清空表都是用truncate。
3.3drop删除数据库,数据表,字段。删除数据及结构。
五 查询操作(select)
1、select[all|distinct] * from 表名 [where 条件][group by 条件][having by 条件][order by 条件][limit 条件];
1.1语法初步:select 字段列表 from tb_name;例select id,name,salary,address from student1;
字段列表中可以有数据表中本身字段组合而成的字段:select id,name,salary,address,name+address from student1;
select单独使用就是用来
显示后面的内容。例:select 30;//30,例2:select unix_timestamp();//1483707354
1.2dual 伪表:dual不是一个具体的表,它是为了保证语法格式的正确性而进行使用的。select unix_timestamp() from dual;//1483707354
1.3as 字段别名 :
select id,nameas 姓名,salary as 工资,address as 地址 from student1;
在结果集中使用字段别名,as也可以省略不写如
select id,name 姓名 from student1;
1.
4字段列表可以是一个字段,可以是多个,可以使用*代替全部字段select * from student1
字段列表选项all/
distinct,显示全部/代表重复的记录只显示一条,默认是all。
select distinct name as 姓名 from student1;//若distinct多条字段,则每条记录完全一样才会去重复。
字段:* 代表所有的字段,也可以具体的指定某几个字段。
2、from
子句
from后面跟多张表时(笛卡尔积,无连接条件) ,如果字段列表为*,则结果集中的列总数为各个表的字段列表之和,行总数为各个数据表的行的乘积:
select * from student1,student3;//两个22行记录的表输出结果22*22=484行
3、where
子句
where
子句对
from
原数据表的信息进行筛
选,满足条件
的作为结果集返回。
语法:select 字段列表 from 表名 [where子句];例1select * from student1 where address='南方' and age<23 ;
例2select * from student1 where address in('浙江','江西');
例3select * from student1 where age between 25and 27;//25<=age<=27,大小顺序不能错
例4select * from student1 where intro is null;//注意:不能写成where=null,NULL不等于任何值包括自己本身。
例5select * from student1 where name like '___';//一个下划线匹配一个字符
例6select * from student1 where name like '%小%';//找到所有包含小的字符
where +in()或not in(),in后面罗列字段可取值,使用()包括。
where+between或not between,where+is null或is not null
匹配符
where+
like + '%'匹配任意多个字符和like +
'_
'匹配一个字符
SELECT * from ka where (user_id in (6)
or user_id in (10,12));//两句结果相同,union效率更高(索引可用),常用union代替or
SELECT * from ka where user_id in (6)
union (SELECT * from ka where user_id in (10,12));
4、group by语法:select 字段列表 from 表名 [where子句][group by 子句];分组group by 将字段进行分组,便于数据统计
。
例:
select gender,count(gender) from student1 group by gender;//显示表中所有男生和女生数量
SQL中聚合函数对一组值执行计算,并返回单个值。
聚合函数
avg(),返回指定组中的平均值。
count(),数量;null不进行统计会忽略空值 ,
' '统计。count(*)为记录总行数
max(),数据最大值;
min(),最小值;
sum(),和
group_concat(),可以显示一个分组中的普通字段的所有值。group_concat 后面如果是多个字段,会将两个字段进行统一分组,分别进行显示。
select group_concat(name,age), gender,count(gender) from student1 group by gender;//显示男生和女生数量以及姓名、年龄
注意:
-
- 按照字段进行分组
-
- 通常与聚合函数组合使用
-
- 字段列表一般为分组字段,以及聚合函数。通常不使用一般字段(使用时只显示该字段的第一字段值)
同时按照多个字段进行分组:
select address,gender,avg(salary) from student1 group by address,gender;//同时按照地址、性别、和平均薪资分组
5、having条件:select 字段列表from 表名 [where子句] [group by子句][having子句];
where 是 在原表中进行筛选。having 是对分组结果集进行筛选,通常跟在group by后面 。聚合函数筛选通常是使用having。
例:
select address,max(salary) from student1 group by address having max(salary)>1000;
//先按照地址,最高薪资分组,再筛选出分组结果中大于1000的地址和薪资信息。
select stuname,stuaddress from stu where stusex='女';//例:显示性别为女的学生的姓名和住址。
select stuname,stuaddress from stu having stusex='女'; 会报错,
因为结果集中只有stuname和stuaddress两个字段,没有stusex,无法使用having进行筛选。
6、order by 排序:select 字段列表 from 表名 [where 子句] [group by 子句] [having子句][order by子句];
语法:
order by 字段 [asc|desc]//将表格中的数据按照一定的顺序进行显示。
默认是asc代表升序排列,如果改为
desc,则为
降序排列。
注意:order by 后面如果添加多个字段,每个字段都要重新指定一下排序顺序,并且以第一个字段为主,
如果第一个字段中的值相等,则继续按照第二个字段进行排序。
7、limit
: 限制输出的记录数
select 字段列表 from 表名 [where子句] [group by子句] [having子句] [order by子句][limit 子句];
语法: limit n : 从第一条记录开始,连续获得n条记录,
limit m,n : 从m+1条记录开始,连续获得n条记录,
m为偏移量offset ,如果为0,可以省略。
例:显示语文考试前三名的个人信息
select * from student1 order by fenshu desc limit 3;
分页公式:
limit (page-1)*pagesize,pagesize。-------(即limitm,n )
pagesize : 每页显示条数,page : 当前页:例limit 5; limit 5,5; limit 10,5;//每页5条记录,第一页,第二页,第三页。
六、select 语法高级
select 字段列表 from 表名 [where子句] [group by子句] [having子句] [order by子句] [limit 子句];
注意:
-
- where子句可以进行条件判断,对from子句返回的结果集进行筛选
-
- group by子句可以对数据进行分组
-
- having子句可以对分组的结果集进行进一步的筛选
-
- order by子句可对结果集进行排序
-
- limit限制最终结果集的输出行数
-
- 注意各个子句之间的顺序不能错
七、mysql的运算符
算术运算符:+-*/%;辑运算符:and与、or或
比较运算符:>、<、>=、<=、!=(或<>)、=(mysql中=既代表赋值运算也代表比较运算)如:id!=10或id<>10
MySql4(连接查询、联合查询、数据库备份还原、创建授权、改密)
一、连接查询
1、内连接inner join……on
inner join语法: select 字段列表 from 表1 inner join 表2 on 表1.字段=表2.字段。
inner可以省略不写, 连接查询就是将两张表进行一个拼接,然后生成一个新的结果。
思考1: select * from 表1 inner join 表2 on 条件 和 select * from 表2 inner join 表1 on 条件,记录的值是否一样?答案: 是一样的。
思考2: 如何进行三张表的内连接查询答案: select * from 表1 inner join 表2 on 条件 inner join 表3 on 条件。
2、左连接left join……on :又叫左外连接,连接操作时以左表的数据为准。
语法 : select 字段列表 from 表名 left join 表名 on 条件。
思考: select * from 表1 left join 表2 和 select * from 表2 left join 表1记录内容是否一样?
答案: 不一样,是以左表中的数据为准,如果左边中的数据右边中没有对应的关系,那么直接显示null
3、右连接right join……on :又叫右外连接,以右表中的数据为主。
语法: select * from 表1 right join 表2 on 条件
思考1: select * from 表1 left join 表2 和 select * from 表2 right join 表1 ,显示的记录内容是否一样?
答案2: 都是以表1为主,显示的内容是一样的。
-
-
- 自然连接
-
4.1
自然内连接 natural join
4.2自然左连接 natural left join
4.3自然右连接 natural right join
自然连接会自动的去找两张表中的相同字段,然后利用相同字段去进行连接,并且相同的字段只会显示一个。
思考: 如果两张表中存在着多个同名字段,会怎么处理?答案: 只有当多个同名字段的值全部相等,那么才会显示这条记录。
5、using()可部分情况代替on 条件。
在左连接,右连接和内连接时可以不使用on,直接使用using(),可以指定一个同名字段去自动连接。
using显示的结果和自然链接的结果大体相似,
using指定的
同名字段只
显示一次。
//测试数据-------
通过以下测试来练手
/*stuinfo测试数据*/
create table stuinfo
(
stuNo char(6) primary key,
stuName varchar(10) not null,
stuSex char(2) not null,
stuAge tinyint not null ,
stuSeat tinyint not null,
stuAddress varchar(10) not null
);
insert into stuinfo values ('s25301','龙傲天','男',18,1,'北京');
insert into stuinfo values ('s25302','皮卡丘','男',31,3,'上海');
insert into stuinfo values ('s25303','皮丘','女',22,2,'北京');
insert into stuinfo values ('s25304','诸葛傲天','男',28,4,'天津');
insert into stuinfo values ('s25305','诸葛翠花','女',23,7,'河南');
insert into stuinfo values ('s25318','王狗蛋','男',26,6,'天津');
insert into stuinfo values ('s25319','梅超风','女',23,5,'河北');
/*stuMarks测试数据*/
create table stuMarks
(
examNo char(7) primary key,
stuNo char(6) not null ,
writtenExam int,
labExam int
);
insert into stumarks values ('s271811','s25303',80,58);
insert into stumarks values ('s271813','s25302',50,90);
insert into stumarks values ('s271815','s25304',65,50);
insert into stumarks values ('s271816','s25301',77,82);
insert into stumarks values ('s271819','s25318',56,48);
//1显示地区及每个地区参加考试的人数,并按人数降序排列
select stuaddress,count(writtenexam) from stuinfo left join stumarks using(stuno) group by stuaddress order by count(writtenexam) desc;
//2显示有学生参加考试的地区和学生姓名
select group_concat(stuname),stuaddress from stuinfo right join stumarks using(stuno) group by stuaddress;
select group_concat(stuname),stuaddress from stuinfo left join stumarks using(stuno) group by stuaddress having count(writtenexam)>0;
//3显示有学生参加考试的地区
select stuaddress from stuinfo right join stumarks using(stuno) group by stuaddress;
select distinct stuaddress from stuinfo right join stumarks using(stuno);
二、联合查询 union
连接查询是将两张表
横向的连接到一起,
联合查询是将两张表
纵向的连接到一起
select
字段列表
from tb_name
union
select
字段列表
from tb_name
;
联合查询的三不同:
-
- 如果两张表中的字段名称不一致,可以正常进行联合查询,显示的字段名是以最左边书写的表格为主。
- 如果两张表字段的数据类型不一致,也可以正常的联合查询,不影响结果。
- 如果两张表选择的字段数量不同,则会报错
老何有话说: 进行联合查询时,可以保证字段的数据类型不一致或者字段的名称不一致,但是一定要保证字段的数量要一致。
如果联合查询需要分别给两张表做排序,则需要注意一下两点:
1.每个select语句使用小括号包裹起来。2.order by 后面必须要加上limit,如果要取出所有的记录limit可以写一个比较大的值。
union [all|distinct] 默认是distinct,重复的记录只显示一条。
select id,name from student where city like '%西%' order by salary desc limit 100 //工资降序
union //排序结果上下组合到一张表
select id,name from student where age>35 order by salary asc limit 100; //工资升序
三、子查询
在查询的结果上再次进行查询就是一个子查询。where 比较运算符(子查询语句)
1.标量子查询:语法:select 语句 where字段 比较运算符 (subquery);
select * from student1 where salary>(select avg(salary) from student1);
查询返回的结果是一个
一行一列的表格。
2.列子查询
返回的结果是一个
一列多行的表格。(一个字段,多条记录)
select * from student1 where age in(select age from student1 where name in('宋小花','周同学'));
where 字段 in|not in(子查询语句)
where 字段 比较运算符 any(子查询语句)
where 字段 比较运算符 all(子查询语句)
思考: in 和 =any是否一样?
答案: 一样的, city in (‘北京’,’上海’)=> city=’北京’ or city=’上海’;
city =any(‘北京’,’上海’)=> city=’北京’ or city=’上海’;
思考: not in 和 !=any是否一样?
答案: 不一样,city not in (‘北京’,’上海’)=> city=’北京’ and city=’上海’;
city !=any(‘北京’,’上海’)=> city!=’北京’ or city!=’上海’;
city !=all(‘北京’,’上海’)=> city!=’北京’ and city!=’上海’;
not in 相当于!=all
3.行子查询
返回的结果是一个
一行多列的表格。(多个字段,一条记录)
语法:
select * from 表格 where(字段1,字段2) =(select 字段1,字段2 from 表);
where后面的字段需要放在一个小括号的内部。
4.表子查询
返回的结果是一个
多行多列的表格。(多个字段,多条记录)
语法:
select * from (表子查询的结果) as 表名
表子查询必须要给结果起一个表名
//显示男生和女生的人数
select stusex,count(stusex) from stuinfo group by stusex;
select stusex,count(stusex) from stuinfo where stusex=’男’ union select stusex,count(stusex) from stuinfo where stusex=’女’;
select sum(stusex='男') as '男',sum(stusex='女') '女' from stuinfo;
//查找笔试成绩是80分的学生姓名和学号
select stuno,stuname from stuinfo inner join stumarks using(stuno)where writtenexam=80;
select stuno,stuname from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);
//查找笔试成绩最高的学生
select stuno,stuname from stuinfo inner join stumarks using(stuno) order by writtenexam desc limit 1;
select stuno,stuname from stuinfo where stuno=(select stuno from stumarks order by writtenexam desc limit 1);
select stuno,stuname from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam)from stumarks));
//查找及格的同学
select stuno,stuname from stuinfo inner join stumarks using(stuno) where writtenexam>=60;
select stuno,stuname from stuinfo where stuno in(select stuno from stumarks where writtenexam>=60);
//查找不及格的同学
select stuno,stuname from stuinfo inner join stumarks using(stuno) where writtenexam<60;
select stuno,stuname from stuinfo where stuno in(select stuno from stumarks where writtenexam<60);
//查找缺考的同学
select stuno,stuname from stuinfo left join stumarks using(stuno) where writtenexam is null;
select stuno,stuname from stuinfo where stuno not in(select stuno from stumarks);
四、数据的备份及还原
备份数据使用的是mysqldump,还原数据使用的是mysql
1.数据的备份
备份文件的语法: mysqldump[.exe] [-h主机][-P端口号] -u用户名 -p密码 [-d][-B] 数据库[表格] > 路径.文件名.sql
-d : 如果存在,是只导出表结构,而
不导出数据。默认可不写,
C:\WINDOWS\system32>mysqldump -uroot -proot -d db3<e:/db3.sql
-B : 如果存在,那么他不单单导出
表结构和表数据,并且还
导出数据库的结构,无分号;
C:\WINDOWS\system32>mysqldump -uroot -proot
-B db3<e:/db3.sql
-p如果不写密码直接回车,那么回车以后去输入密码
2.数据的还原
mysql[.exe] [-h主机名][-P端口号]-u用户名 -p密码 [数据库]< 文件路径.文件名
C:\WINDOWS\system32>mysql -uroot -proot db3<e:/db3.sql
如果备份的文件中已经有创建数据库的语法了,那么数据库也可以省略不写
C:\WINDOWS\system32>mysql -uroot -proot <e:/db3.sql
source 文件名 是在登陆mysql以后还原文件。
mysql>source e:/db3.sql;
五、创建用户和用户授权
1.显示所有的用户:select host,user,password from mysql.user;
host: 代表运行在什么样的ip地址登陆,localhost代表自己本身主机,%代表运行所有的电脑登陆。
2.创建用户:create user ‘username’@’ip地址’ [identified by 密码];
create user 'hangzhou'@'localhost' identified by '123456';
3.设置密码: set password [for ‘user’@’ip地址’] = password(‘密码’);
4.用户授权
grant 权限 on 数据库.表格 to ‘user’@’ip地址’[with grant option]
grant all privileges on *.* to ‘user ’@‘ip地址’//给用户赋予所有的权限
5.移除用户的权限
revoke 权限 on 数据库.表格 from ‘user’@’ip地址’
revoke all privileges on *.* from ‘user’@’ip地址’
6.删除用户
drop user ‘user’@’ip地址’
六、找回密码
- 关闭mysql服务 net stop mysql
- 让mysql跳过验证密码的环节 mysqld --skip-grant-tables
- 打开一个新的cmd,然后直接输入mysql登陆
- 修改密码 update mysql.user set password=password(‘新密码’) where user=’root’;
- tastkill /f /im mysqld || tastkill /f /pid 3228 或者直接在任务管理器中结束mysqld任务
- 重新启动mysql服务即可正常登陆
七、
MySQL
中的预处理
基本语法:
//1
、创建预处理语句
prepare
预处理指令
from SQL
语句
(insert/delete/update/select)
//2
、设置参数
set @value = value;
//3
、执行预处理
execute
预处理指令
;
prepare stmt1 from 'insert into user values (null,?,?,?)';//1创建预处理语句
set @username='zhangsan';//2设置参数
set @password=md5('123456');
execute stmt1 using @username,@password;//3执行预处理语句
select * from user;//4返回执行结果
八、MySQL的事务
①开启事务
1
)标准事务:
start transaction;
当提交或回滚一个事务之后,事务结束。接下来的操作不在事务操作范围之内。
2
)语句事务:
set autocommit = 0;
当提交或回滚一个事务之后,事务不会终止,后面的操作仍处于事务之中。
②提交事务:
commit;
③回滚事务:
rollback;