Mysql的一揽子方案用法

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为主，显示的内容是一样的。

1. 1. 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. 1. 如果两张表中的字段名称不一致，可以正常进行联合查询，显示的字段名是以最左边书写的表格为主。
   2. 如果两张表字段的数据类型不一致，也可以正常的联合查询，不影响结果。
   3. 如果两张表选择的字段数量不同，则会报错

老何有话说： 进行联合查询时，可以保证字段的数据类型不一致或者字段的名称不一致，但是一定要保证字段的数量要一致。

如果联合查询需要分别给两张表做排序，则需要注意一下两点：

     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地址’

六、找回密码

1. 关闭mysql服务 net stop mysql
2. 让mysql跳过验证密码的环节 mysqld --skip-grant-tables
3. 打开一个新的cmd，然后直接输入mysql登陆
4. 修改密码  update mysql.user set password=password(‘新密码’) where user=’root’;
5. tastkill /f /im mysqld   ||  tastkill /f /pid 3228  或者直接在任务管理器中结束mysqld任务
6. 重新启动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;