mysql学习笔记

一、mysql –h192.168.4.28 -uroot –ppassword

h：当连接MySQL服务器不在同台主机时，填写主机名或IP地址

u：登录MySQL的用户名

p：登录MySQL的密码

注意:密码如果写在命令行的时候一定不能有空格。如果使用的系统为linux并且登陆用户名字与MySQL的用户名相同即可不用输入用户名密码，linux默认是以root登陆，windows默认用户是ODBC

二、更改密码

在MySql安装目录下：mysql\bin下输入如下命令：

Mysqladmin –uroot –p旧密码 password 新密码

注意：结尾无分号

三、mysql程序常用命令

选定默认数据库：use dbname;

显示所有数据库：show databases;

显示默认数据库中所有表：show tables;

放弃正在输入的命令：\c 显示命令清单：\h 退出mysql程序：\q 查看MySQL服务器状态信息：\s

属于一个SQL语句，使用分号（;）结尾，否则mysql认为语句没有输入完。

四、mysql数据库备份及恢复命令mysqldump,source的用法

还原一个数据库: mysql -hlocalhost -uroot -p123456 数据库名<数据库所在路径（c:\数据库名.sql）

备份一个数据库: mysqldump -hlocalhost -uroot -p123456 数据库名> 数据库所在路径（d:\数据库.sql）

备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。 mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份 mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表 mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库 mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构 mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库 mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令 mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库 gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器 mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

几个常见例子：

1.导出整个数据库 mysqldump -u 用户名 -p 数据库名 > 导出的文件名 mysqldump -u root -p dataname >dataname.sql 这个时候会提示要你输入root用户名的密码,输入密码后dataname数据库就成功备份在mysql/bin/目录中.

2.导出一个表 mysqldump -u 用户名 -p 数据库名表名> 导出的文件名 mysqldump -u root -p dataname users> dataname_users.sql

3.导出一个数据库结构 mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库常用source 命令进入mysql数据库控制台，如mysql -u root -p mysql>use 数据库然后使用source命令，后面参数为脚本文件（如这里用到的.sql） mysql>source d:\wcnc_db.sql

五、常见完整性约束：

PRIMARY KEY 主码约束(主键)

UNIQUE 唯一性约束

NOT NULL 非空值约束

AUTO_INCREMENT 用于整数列默认自增1

UNSIGNED 无符号整数

DEFAULT default_value 默认值约束

DEFAULT cur_timestamp 创建新记录时默认保存当前时间（仅适用timestamp数据列）

ON UPDATE cur_timestamp 修改记录时默认保存当前时间（仅适用timestamp数据列）

CHARACTER SET name 指定字符集（仅适用字符串）

六、主键和外键

数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来的。主键：帮助MySQL以最快的速度把一条特点的数据记录的位置确定下来。主键必须是唯一的主键应该是紧凑的，因此整数类型比较适合外键：引用另外一个数据表的某条记录。外键列类型尽可能与主键列类型保持一致外键列应该加上NOT NULL

主键 create table student( sid int not null auto_increment, name varchar(20) not null, primary key(sid) ); 外键（自动检查外键是否匹配，仅适用InnoDB） create table score( cid int not null auto_increment primary key, score int, sid int, foreign key(sid) references student(sid) );

七、删除数据库或表操作

删除表操作语法：drop table [if exists] tab_name [,tab_name]... 叙述：从数据库中删除给定的表。如果给出if exists 子句，则删除不存在的表不会出错。

删除数据库操作语法：drop database [if exists] db_name 叙述：删除给定的数据库。在删除一个数据库后，它就永远没有了，因此要特别小心。如果给出if exists 子句，则删除不存在的数据库不会出错。

八、更改表结构操作

语法：alter table 表名 action;

说明：action 可以是如下语句：

add 列名建表语句 [first | after 列名] 可以为表添加一列，如果没指定first或者after，则在列尾添加一列，否则在指定列添加新列 add primary key (列名) 为表添加一个主键，如果主键已经存在，则出现错误

add foreign key(列名) references 表名(列名)为表添加一个外键

alter 列名 set default 默认值可以更改指定列默认值

change 旧列名新列名 <建表语句> [first | after 列名] 可以更改列类型和列名称，如果原列的名字和新列的名字相同

modify 列名 <建表语句> [first | after 列名] 和change的作用相同

drop 列名 //可以删除一列

drop primary key //可以删除主键

engine 类型名 //可以改变表类型

rename as 新表名 //可以将表名更改

九、比较运算符

十、MySQL数据库中的通配符

“%” (百分号) 代表任意长度（长度可以为0）的字符串

举例： a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串 “_ ”(下横线) 代表任意单个字符

举例：

a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串

十一、插入记录操作

语法：

INSERT [INTO] <表名> [列名] VALUES <值列表>

叙述：如果表名后面没写字段名，则默认是向所有的字段添加值，另外字符串值应该用‘ ’或“ ”引号括起来举例1：向people表中添加一条记录： insert into people(name,age) values(“zhangsan”,20);

1、插入数据行 1

注意事项1:每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (‘小明') ×

2、插入数据行 2

注意事项2:每个数据值的数据类型、精度和小数位数必须与相应的列匹配；

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (‘张青裁’,‘上海松江’,ZQC,‘ZQC@Sohu.com’, ‘男') ×

3、插入数据行3

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

INSERT INTO Students (SAddress,SGrade,SEmail,SSEX) VALUES ('上海松江',6,'ZQC@Sohu.com',0) ×

4、插入数据行4

注意事项6:具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES ('张青裁',DEFAULT,6,'ZQC@Sohu.com',0)

5、插入多行数据

INSERT INTO <表名>(列名) VALUES(<列名值>), (<列名值>), (<列名值>) ……

INSERT STUDENTS (SName,SGrade,SSex) VALUES('测试女生1',75,0), ('测试女生2',77,0), ('测试女生3',83,0), (‘测试男生1',81,1), ('测试女生4',90,0), (‘测试男生2',94,1), ('测试女生5',51,0), (‘测试男生3',53,1);

6、插入多行数据（select）

INSERT INTO <表名>(列名) SELECT <列名> FROM <源表名>

INSERT INTO TongXunLu ('姓名','地址','电子邮件') SELECT SName,SAddress,SEmail FROM Students

十二、更改记录操作

UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]

叙述： where 子句是判断语句，用来设定条件，限制只更新匹配的行,如果不带where子句，则更新所有行数据。

举例：将student表中的所有学生名称为"Alex"的改为"Tom": update student set sname="Tom" where sname="Alex";

【更新数据行】

UPDATE Student SET SSEX = 0

UPDATE Student SET Sdept ='家政系' WHERE Sdept = '刺绣系'

UPDATE Sc SET Grade = Grade + 5 WHERE Grade <= 95

【删除记录操作】

语法：

DELETE FROM <表名> [WHERE <删除条件>]

叙述：此语句删除表中的行，如果不带where子句，则删除整个表中的记录，但是表不被删除。

举例：删除student表中的所有年龄小于18岁的记录： delete from student where sage<18;

【补充说明】

删除学生编号为'95005'的学生信息包括成绩信息

delete from student,sc using student,sc where student.sno = sc.studentid and student.sno='95005';

说明：表和字段的引用方式有两种：绝对引用和相对引用绝对引用：数据库名.表名(.字段名) 相对引用：表名.(字段名)

十三、查询记录操作

from子句：指定查询数据的表

where子句：查询数据的过滤条件

group by子句：对匹配where子句的查询结果进行分组

having子句：对分组后的结果进行条件限制

order by子句：对查询结果结果进行排序，后面跟desc降序或asc升序（默认）。　

limit子句：对查询的显示结果限制数目

procedure子句：查询存储过程返回的结果集数据

十四、使用集函数

SQL提供的统计函数称为集函数.

主要的集函数：

记数函数： count(列名) 计算元素的个数

求和函数： sum(列名) 对某一列的值求和，但属性必须是整型

计算平均值：avg(列名)对某一列的值计算平均值

求最大值： max(列名) 找出某一列的最大值

求最小值： min(列名) 找出某一列的最小值

十五、在WHERE子句中使用谓词 :

BETWEEN AND :在两数之间

NOT BETWEEN AND　：不在两数之间

IN <值表> ：是否在特定的集合里（枚举）

NOT IN <值表>　：与上面相反

LIKE ：是否匹配于一个模式

IS NULL（为空的）或 IS NOT NULL（不为空的）

REGEXP : 检查一个值是否匹配一个常规表达式。

十六、多表查询-分类

连接查询

-同时涉及多个表的查询称为连接查询

-用来连接两个表的条件称为连接条件

内连接(INNER JOIN)

外连接

——左外联结 (LEFT JOIN)

——右外联结 (RIGHT JOIN)

外连接与普通连接的区别

普通连接操作只输出满足连接条件的元组

外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出：

多表查询—内连接-1

多表查询—内连接-2

SELECT Students.SName, Sc.CourseID, Sc.Grade FROM Students,Sc WHERE Students.SNo = Sc.StudentID

多表查询—三表连接

SELECT S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩 FROM Students AS S INNER JOIN Sc AS C ON (S.SCode = C.StudentID) INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

多表查询—左外连接