Mysql 常用语句及例子

create table student_subject(sid int(11) not null,cid int(11) not null,
primary key(sid,cid),
foreign key(sid) references student(id),
foreign key(cid) references result(subjectno));

2.5、数据字段属性

UnSigned

无符号的

声明该数据列不允许负数 .

ZEROFILL

0 填充的

不足位数的用 0 来填充 , 如 int(3),5 则为 005 Auto_InCrement

自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1( 默认 )

通常用于设置主键 , 且为整数类型

可定义起始值和步长

当前表设置步长 (AUTO_INCREMENT=100) : 只影响当前表

SET @@auto_increment_increment=5 ; 影响所有使用自增的表 ( 全局 )

NULL 和 NOT NULL

默认为 NULL , 即没有插入该列的数值

如果设置为 NOT NULL , 则该列必须有值

DEFAULT

默认的

用于设置默认值

例如 , 性别字段 , 默认为 " 男 " , 否则为 " 女 " ; 若无指定该列的值 , 则默认值为 " 男 " 的值

2.6、数据表的类型

  create table if not exists student(
     id int(11) not null auto_increment comment '学号',
     name varchar(255) not null default '匿名' comment '姓名',
     pwd varchar(255) not null default '123456' comment '密码',
     sex varchar(2) not null default '男' comment '性别',
     birthday datetime default null comment '生日',
     address varchar(255) default null comment '地址',
     email varchar(255) default null comment '邮箱',
     primary key(id)
     ) Engine=InnoDb default charset=utf8;

MySQL 的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV 等 ...

常见的 MyISAM 与 InnoDB 类型：

经验 ( 适用场合 ) :

适用 MyISAM : 节约空间及相应速度

适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

我们可为数据库 , 数据表 , 数据列设定不同的字符集，设定方法 :

创建时通过命令来设置 , 如 : CREATE TABLE 表名 ()CHARSET = utf8 ;

如无设定 , 则根据 MySQL 数据库配置文件 my.ini 中的参数设定

2.7、修改数据库

修改表名

alter table 名称 rename as 新名称

添加字段

alter table 名称 add 字段属性

修改字段

alter table 名称 modify 字段名属性

alter table 名称 change 旧字段名新字段名属性

例如修改为自增

alter table teacher change id id int AUTO_INCREMENT;

删除字段

alter table 名称 drop 字段名

3、MySQL数据管理

3.1、外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

外键作用

保持数据 一致性 ， 完整性 ，主要目的是控制存储在外键表中的数据 , 约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。



-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

--2、添加外键约束(关联字段要用括号括起来)

-- ALTER TABLE 从表

-- ADD CONSTRAINT 约束名 FOREIGN KEY (关联字段) references 主表(关联字段);

例如

alter table student add constraint FK_grade foreign key(gradeid) references grade(gradeid)

KEY `FK_gradeid` (`gradeid`),

CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`

(`gradeid`)

-- 创建外键方式二 : 创建子表完毕后 , 修改子表添加外键

ALTER TABLE `student`

ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`

(`gradeid`);

注意 : 删除具有主外键关系的表时 , 要先删子表 , 后删主表因为子表依赖主表

-- 删除外键

ALTER TABLE student DROP FOREIGN KEY FK_gradeid;

-- 发现执行完上面的 , 索引还在 , 所以还要删除索引

-- 注 : 这个索引是建立外键的时候默认生成的

ALTER TABLE student DROP INDEX FK_gradeid;

3.2、DML语言

DML 语言：数据操作语言

用于操作数据库对象中所包含的数据

包括 :

INSERT ( 添加数据语句 )

UPDATE ( 更新数据语句 )

DELETE ( 删除数据语句 )

3.3、添加数据

insert into student (, , , ) values('', '' ,)

3.4、修改数据

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [ WHERE

condition];

column_name 为要更改的数据列

value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的 SELECT 结果

condition 为筛选条件 , 如不指定则修改该表的所有列数据

-- 修改年级信息

UPDATE grade SET gradename = ' 高中 ' WHERE gradeid = 1 ;

3.5、删除数据

DELETE FROM 表名 [ WHERE condition];

-- 删除最后一个数据

DELETE FROM grade WHERE gradeid = 5

TRUNCATE [ TABLE ] table_name;

-- 清空年级表

TRUNCATE grade

4、使用DQL查询数据

4.1、DQL语言

DQL( Data Query Language 数据查询语言 )

查询数据库数据 , 如 SELECT 语句

简单的单表查询或多表的复杂查询和嵌套查询

是数据库语言中最核心 , 最重要的语句

使用频率最高的语句

4.2、指定查询字段

-- 查询表中所有的数据列结果 , 采用 **" \* "** 符号 ; 但是效率低，不推荐 .

-- 查询所有学生信息

SELECT * FROM student;

-- 查询指定列 ( 学号 , 姓名 )

SELECT studentno,studentname FROM student;

AS 子句作为别名

可给数据列取一个新别名

可给表取一个新别名

可把经计算或总结的结果用另一个新名称来代替


-- 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;
-- 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

DISTINCT 关键字的使用

去掉 SELECT 查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条

-- # 查看哪些同学参加了考试 ( 学号 ) 去除重复项

SELECT * FROM result; -- 查看考试成绩

SELECT studentno FROM result; -- 查看哪些同学参加了考试

SELECT DISTINCT studentno FROM result; -- 了解 :DISTINCT 去除重fu

4.3、where条件语句

作用：用于检索数据表中符合条件的记录

搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假 .

-- 满足条件的查询(where)
SELECT Studentno,StudentResult FROM result;
-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以写成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查询(对应的词:精确查询)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;
-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查询：比较操作符

 

-- 模糊查询 between and \ like \ in \ null
-- =============================================
-- LIKE
-- =============================================
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';
-- 查询姓刘的同学,后面只有一个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘_';
-- 查询姓刘的同学,后面只有两个字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘__';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'
-- =============================================
-- IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
-- =============================================
-- NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4.4、连接查询

JOIN 对比

内连接查询 inner join

关键字：inner join on

语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

二、左连接查询 left join
关键字：left join on / left outer join on

语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

三、右连接 right join

键字：right join on / right outer join on

语句：SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
————————————————

4.5、排序和分页

语法 : ORDER BY

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照 ASC 升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

/*============== 分页 ================

语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

好处 : ( 用户体验 , 网络传输 , 查询压力 )

推导 :

第一页 : limit 0,5

第二页 : limit 5,5

第三页 : limit 10,5

......

第 N 页 : limit (pageNo-1)*pageSzie,pageSzie

[pageNo: 页码 ,pageSize: 单页面显示条数 ]

4.6、子查询

/*============== 子查询 ================

什么是子查询 ?

在查询语句中的 WHERE 条件子句中 , 又嵌套了另一个查询语句

嵌套查询可由多个子查询组成 , 求解的方式是由里及外 ;

子查询返回的结果一般都是集合 , 故而建议使用 IN 关键字 ;

5.2、聚合函数

-- 聚合函数

/*COUNT: 非空的 */

SELECT COUNT (studentname) FROM student;

SELECT COUNT (*) FROM student;

SELECT COUNT ( 1 ) FROM student; /* 推荐 */

-- 从含义上讲， count(1) 与 count(*) 都表示对全部数据行的查询。

-- count( 字段 ) 会统计该字段在表中出现的次数，忽略字段为 null 的情况。即不统计字段为 null

的记录。

-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为 null 的记录；

-- count(1) 用 1 代表代码行，在统计结果的时候，包含字段为 null 的记录。

很多人认为 count(1) 执行的效率会比 count(*) 高，原因是 count(*) 会存在全表扫描，而 count(1)

可以针对一个字段进行查询。其实不然， count(1) 和 count(*) 都会对全表进行扫描，统计所有记录的

条数，包括那些为 null 的记录，因此，它们的效率可以说是相差无几。而 count( 字段 ) 则与前两者不

同，它会统计该字段不为 null 的记录条数。

MD5 加密

一、 MD5 简介

MD5 即 Message-Digest Algorithm 5 （信息 - 摘要算法 5 ），用于确保信息传输完整一致。是计算机广泛

使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有 MD5 实现。将数据（如汉

字）运算为另一固定长度值，是杂凑算法的基础原理， MD5 的前身有 MD2 、 MD3 和 MD4 。

新建一个表 testmd5

CREATE TABLE `testmd5` (
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

如果我们要对 pwd 这一列数据进行加密，语法是：

update testmd5 set pwd = md5(pwd);

如果单独对某个用户 ( 如 kuangshen) 的密码加密：

INSERT INTO testmd5 VALUES ( 3 , 'kuangshen2' , '123456' )

update testmd5 set pwd = md5(pwd) where name = 'kuangshen2' ;

插入新的数据自动加密

INSERT INTO testmd5 VALUES ( 4 , 'kuangshen3' ,md5( '123456' ));

6、事务

6.1、概述

事务就是将一组 SQL 语句放在同一批次内去执行

如果一个 SQL 语句出错 , 则该批次内的所有 SQL 都将被取消执行

MySQL 事务处理只支持 InnoDB 和 BDB 数据表类型

ACID特性

原子性 (Atomic)

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执

行过程中发生错误，会被回滚（ ROLLBACK ）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性 (Consist)

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不

管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务

一样操作。其主要特征是保护性和不变性 (Preserving an Invariant) ，以转账案例为例，假设有五

个账户，每个账户余额是 100 元，那么五个账户总额是 500 元，如果在这个 5 个账户之间同时发生多

个转账，无论并发多少个，比如在 A 与 B 账户之间转账 5 元，在 C 与 D 账户之间转账 10 元，在 B 与 E 之

间转账 15 元，五个账户总额也应该还是 500 元，这就是保护性和不变性。

隔离性 (Isolated)

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相

同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系

统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同

一时间仅有一个请求用于同一数据。

持久性 (Durable)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，

7、索引

7.1、索引分类

索引的作用

提高查询速度

确保数据的唯一性

可以加速表和表之间的连接 , 实现表与表之间的参照完整性

使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间

全文检索字段进行搜索优化

分类

主键索引 (Primary Key)

唯一索引 (Unique)

常规索引 (Index)

全文索引 (FullText)

7.2、主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

最常见的索引类型

确保数据记录的唯一性

确定特定数据记录在数据库中的位置

7.3、唯一索引

31 作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

主键索引只能有一个

唯一索引可能有多个


CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

7.4、常规索引

作用 : 快速定位特定数据

注意 :

index 和 key 关键字都可以设置常规索引

应加在查询找条件的字段

不宜添加太多常规索引 , 影响数据的插入 , 删除和修改操zuo

CREATE TABLE `result`(

-- 省略一些代码

INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加

)

-- 创建后添加

ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

7.6、索引准则

索引不是越多越好

不要对经常变动的数据加索引

小数据量的表建议不要加索引

索引一般应加在查找条件的字段

8、权限管理


/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的
混编值，需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码





-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR
CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限