程序总是绕不过关于数据库的操作,MySQL在数据库里面往往是用得最多的。

目录:

mysql语法表(mysql数据库基础知识)

1.MySQL数据库基础

(1)概念:数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。MySQL是一个开源的关系型数据库管理系统。MySQL分为社区版和企业版。广义上,数据库是存储数据的东西。狭义上,数据库是存储在本地或网络,结构化的表格。目的是记录信息,意义是获取分析。

(2)简单操作:

启动MySQL服务:net start mysql
停止MySQL服务:net stop mysql
mysql登录:mysql -uroot -p
或mysql -hlocalhost -uroot -p -P3306 //服务器localhost,默认端口号3306;注:用户,密码都是root
退出:exit、quit、ctrl c

(3)MySQL常用函数:

SELECT USER()//得到登录用户
SELECT VERSION()//得到MySQL的版本信息
SELECT NOW()//得到当前的日期时间
SELECT DATABASE()//得到当前打开的数据库
SELECT @@SQL_MODE//检查数据库是否是严格模式

(5)MySQL语句语法规范:

常用的MySQL的关键字我们需要大写;

数据库库名,表名,字段名称等使用小写;

SQL语句支持折行操作,拆分的时候不能把完整单词拆开

数据库名称、表名称、字段名称不要使用MySQL的保留字,如果必须要使用,需要用反引号将其括起来。

SQL语句必须以分号结尾

2.MySQL数据库相关操作

(1)创建数据库:CREATE {DATABASE|SCHEMA} db_name;

(2)查看当前服务器下全部数据库:SHOW DATABASES|SCHEMAS;

(3)打开指定数据库:USE db_name;//下面可以创建表,进行操作

(4)查看指定数据库(数据表)详情信息:SHOW CREATE DATABASE(table) db_name;//主要是查看数据库(数据表)的编码方式。

(5)修改数据库的编码方式:ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;//charset一般修改为utf8

(6)得到当前打开的数据库(避免忘记打开了那个数据库):SELECT DATABASE()|SCHEMA();

(7)删除数据库:DROP DATABASE db_name;

(8)查看上一步警告信息:SHOW WARNINGS

(9)MySQL中的注释:A.# 注释内容;B.-- 注释内容

思考:主要的思路创建数据库CREATE DATABASE;修改数据库:ALTER DATABASE;删除数据库:DROP DATABASE;注释:db_name数据库名;[]可选参数也可以叫默认参数,{}两种方式中的一种。

3.MySQL中的数据类型

[数据类型 存储范围 占用字节]

(1)数值型:数值型分为整数型,浮点型两种数据类型。

整型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT五种。占用字节分别是1,2,3,4,8。

浮点型:float(M,D)、double(M,D)、decimal(M,D)等三种。A.其中M代表数字总位数,D代表小数点后面的位数;B.其中decimal同double一样,内部以字符串形式存储数值,默认保存的数是整数。

(2)日期时间类型:分为DATE,TIME,DATETIME,TIMESTAMP,YEAR五种。实际开发中用得很少,通常是用PHP将时间转化为时间戳的形式,存储进数据库。

(3)字符串类型:分为三类:

第一类:普通字符串,CHAR(固定长度类型),VARCHAR(可变长度类型)。注意:CHAR效率高于VARCHAR,CHAR相当于拿空间换时间,VARCHAR拿时间换空间。

第二类:TEXT和BLOM类型。这些类型大小可变,分别适用于长文本或二进制数据。

第三类:两种特殊类型:SET与ENUM。SET集合类型;ENUM枚举类型,举个栗子sex enum('男','女','保密');

注意:超出数据的范围,会产生截断的现象。

4.数据表结构操作

简单理解为一张二维型的表格

(1)概念:MySQL是关系型数据库,关系型的数据库其实就是一张二维型的表格,

既然是二维表格,当然就有行和列之分,行称之为记录,列称之为字段

(2)创建表:[字段名称 字段类型 约束条件]

CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称(id,uaername,price...) 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件]
    ...
)ENGINE=存储引擎 CHARSET=编码方式;

示例代码:
CREATE TABLE IF NOT EXISTS `test_user`(
#AUTO一般配合主键KEY使用(唯一性自动增长)
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户标号',

#UNIQUE也是取一个唯一性
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
`password` CHAR(32) NOT NULL COMMENT '用户密码',
)ENGINE=INNODB CHARSET=UTF8;

(3)完整型约束条件:

(4)简单查询语句:

查看当前数据库下已有数据表:SHOW TABLES; 
扩展:SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr];
查看指定数据表的详细信息:SHOW CREATE TABLE tbl_name;
查看数据表结构:
    A.DESC tbl_name;
    B.DESCRIBE tbl_name;
    C.SHOW COLUMNS FROM tbl_name;
删除指定的数据表:DROP TABLE [IF EXISTS] tbl_name;

表结构相关操作(划重点)[字段名称 字段类型 完整性约束条件]

针对:字段、约束、数据表的CURD操作。

关键字:ADD、MODIFY、DROP

(1)添加字段:

ALTER TABLE tbl_name \\选中这个表
ADD 字段名称 字段类型 [完整性约束条件] \\添加字段
[FIRST|AFTER 字段名称] \\指定位置添加顺序

示例代码:
ALTER TABLE wp_users 
ADD user_passbak varchar(60) NOT NULL DEFAULT '' 
AFTER user_pass

(2)删除字段:

ALTER TABLE tbl_name
DROP 字段名称

(3)添加默认值:

ALTER TABLE tbl_name
ALTER 字段名称 
SET DEFAULT 默认值;

(4)删除默认值:

ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT

(5)修改字段类型、完整性约束条件:

ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [完整性约束条件]
[FIRST | AFTER 字段名称]

(6)添加主键

ALTER TABLE tbl_name
ADD PRIMARY KEY(字段名称)

(7)删除主键

ALTER TABLE tbl_name
DROP PRIMARY KEY;

(8)添加唯一:注意:MySQL创建表会自动为UNIQUE KEY生成索引名称(index_name)=字段名称

ALTER TABLE tbl_name
ADD UNIQUE KEY|INDEX [index_name] (字段名称)//注意:重复

(9)删除唯一

ALTER TABLE tbl_name 
DROP KEY|INDEX index_name;

(10)修改字段名称:

ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性(全等完整性约束条件) 
[FIRST | AFTER 字段名称]

(11)修改数据表名称

ALTER TABLE tbl_name 
RENAME [TO|AS] new_tbl_name

RENAME TABLE tbl_name TO new_tbl_name;

(12)修改AUTO_INCREMENT的值

ALTER TABLE tbl_name AUTO_INCREMENT=值

贴出代码:

--测试添加删除字段
CREATE TABLE `test_add`(
    `id` INT NOT NULL AUTO_INCREMENT KEY COMMENT '测试名称',
    `username` VARCHAR(20) NOT NULL COMMENT '用户名称'
);
INSERT test_add(username) VALUES('admin');

--添加密码字段名
ALTER TABLE `test_add`
ADD `password` CHAR(32) NOT NULL UNIQUE;

--添加年龄
ALTER TABLE `test_add`
ADD `age` TINYINT UNSIGNED NOT NULL DEFAULT 18
FIRST;--添加到字段首部

--添加用户电话(在密码字段后面)
ALTER TABLE `test_add`
ADD `tel` CHAR(11) NOT NULL UNIQUE
AFTER `password`;

--删除字段password
ALTER TABLE `test_add`
DROP `password`;

--添加默认值
ALTER TABLE `test_add`
ALTER `tel`
SET DEFAULT '用户电话';

--将用户名字段类型改为30
ALTER TABLE `test_add`
MODIFY `username` VARCHAR(30) NOT NULL;

--添加唯一
ALTER TABLE `test_add`
ADD UNIQUE KEY(`tel`);

5. 数据表内容操作

增删改查:INSERT、DELETE、UPDATE、SELECT

wp_users数据表:
ID      user_login    user_pass
1       lovebai       123
3       liang123      12


1、添加记录[INSERT...VALUES...]:
INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);//col_name字段名称
示例:
INSERT `test2`(`id`,`username`,`age`,`email`) VALUES(1,'liang',22,'010101010@qq.com');
INSERT wp_users(user_login,user_pass) VALUES('liang',123456);

2、删除记录[DELETE FROM...]:
DELETE FROM tbl_name [WHERE 条件]//删除是一行一行的删除。如果不添加条件,表中所有记录都会被删除
示例:
DELETE FROM wp_users where id=2;

3、修改记录[UPADTE...SET...]:
UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件]。如果不添加条件,整个表中的记录都会被更新
示例:
UPDATE wp_users SET user_login='liang123',user_pass=12 WHERE id=3;

查询记录:划重点,查询字句

SELECT select_expr,... FROM tbl_name\\选定表中查询字段。select_expr:字段名称或表达式。
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]\\分组、cou_name字段名称
[ORDER BY {col_name|position|expr} [ASC|DESC]]\\排序
[LIMIT 限制结果集的显示条数]\\分页效果 

示例代码:
SELECT user_login FROM wp_users;

(1)查询表中所有记录:SELECT * FROM tbl_name;//星号*所有字段

(2)指定字段信息:SELECT 字段名称,... FROM tbl_name;//注意:字段名称的顺序,影响查询字段的顺序。

(3)给字段起别名:SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;

//不写db_name.tbl_name默认是当前表。作用:当字段名称过长时。

#查询出id一个字段,默认username是id的别名。
select id username from dbtable;

(4)给数据表起别名:SELECT 字段名称 ,... FROM tbl_name [AS] 别名;//单表对查询起别名,没有很多作用,主要适用于多表查询

(5)库名.表名:SELECT 字段名称,... FROM db_name.tbl_name;//db_name.tbl_name加点的形式,查询在db_name数据库下的tal_name表的信息,好处是不用打开数据库。

(6)表名.字段名的形式[适用于多表查询]:SELECT tbl_name.col_name,... FROM tbl_name;//主要告诉字段名来自哪个表,更适用于多表。

贴上代码:

--测试数据表插入,修改,删除,查询的操作
--测试添加记录
CREATE TABLE IF NOT EXISTS `test2`(
    `id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
    `username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    `age` TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    `email` VARCHAR(50) NOT NULL DEFAULT 'test@qq.com' COMMENT '邮箱'
)ENGINE=INNODB CHARSET=UTF8;

--插入值
INSERT `test2`(`id`,`username`,`age`,`email`) VALUES(1,'liang',22,'01010100@qq.com');

--一次添加多条记录
INSERT `test2`(`id`,`username`,`age`,`email`) VALUES(2,'liang2',23,'01010101@qq.com'),
(4,'liang3',24,'02526262@qq.com'),
(7,'liang4',25,'0425246456@qq.com');

--修改记录
UPDATE `test2` SET `age`=21 WHERE `id`=2; 

--删除记录
DELETE FROM `test2` WHERE `age`=21;

--查询指定字段名称
SELECT `id`,`username` FROM test2;

--查询test数据库下的test2表的内容
SELECT * FROM test.test2;
SELECT `id`,`username` FROM test.test2;

--查询test2表中的id 编号(给字段起别名)
SELECT id AS '编号' FROM test.test2;

--给数据表起别名
SELECT * FROM test2 AS `pikaqiu`;

--表名.字段名的形式.告诉字段名来自那个表
SELECT test2.id,test2.username FROM test2;

(7)WHERE条件:会筛选出符合条件的记录:

贴上代码:

--测试WHERE条件
--查询id,username,id=4的用户
SELECT `id`,`username` FROM `test2` 
WHERE `id`=4;

--指定范围[NOT] BETWEEN ... AND
SELECT `id`,`username` FROM `test2`
WHERE `id` BETWEEN 1 AND 5;

--测试指定集合IN.测试id(1,4)//超出集合的,不会显示
SELECT `id`,`username` FROM `test2`
WHERE `id` IN(1,4);

--测试逻辑运算符
--查询性别为男,年龄大于20的用户
SELECT `id`,`username`,`sex`,`age` FROM `test2`
WHERE `sex`='男' AND `age`>20;

--模糊查询
SELECT `id`,`username` FROM `test2`
WHERE `username` LIKE 'liang';

--匹配*liang*
SELECT `id`,`username` FROM `test2`
WHERE `USERNAME` LIKE '%liang%';

(8)分组GROUP BY:对查询结果进行分组。

贴上代码:

#举个栗子
SELECT `id`,`names` FROM `goods` GROUP BY `id` HAVING `id`>6;

--测试分组
SELECT `id`,`username`,`age` FROM `test2`
GROUP BY `age`;

--按照年龄分组,查询组中的用户名,同年龄段各有哪些用户
SELECT `id`,GROUP_CONCAT(`username`),`age` FROM `test2`
GROUP BY `age`;

--按照年龄分组,得到用户名详情,并且分别组中的总人数
SELECT `id`,GROUP_CONCAT(`username`) AS userDetail,`age`,COUNT(*) AS totalusers FROM `test2`
GROUP BY `age`;

--HAVING二次筛选,组中人数大于2
SELECT `id`,GROUP_CONCAT(`username`) `age`,COUNT(*) FROM `test2`
GROUP BY `age`
HAVING COUNT(*)>2;

(9)排序ORDER BY升序或降序:对查询结果进行排序。语法:ORDER BY 字段名称 ASC|DESC。

贴上代码:

--按照id降序排列
SELECT `id`,`username`,`age` FROM `test2`
ORDER BY `id` DESC;

--实现随机记录RABD()
SELECT `id`,`username`,`age` FROM `test2`
ORDER BY RAND();

(10)LIMIT(分页效果):限制查询结果返回的数量。

贴上代码:

--测试LIMIT语句
--显示结果集前两条
SELECT `id`,`username`,`age` FROM `test2`
LIMIT 2;

--从1开始的两条记录
SELECT `id`,`username`,`age` FROM `test2`
LIMIT 1,2;

6.运算符和函数

MySQL内置函数库包括:字符函数、数值运算符与函数、比较运算符与函数、日期时间函数、信息函数、聚合函数、加密函数等等。详情参考官方教程。

(1)字符函数

--字符连接CONCAT()
SELECT CONCAT(`username`,`age`) as fullmessage FROM employe;

--使用指定的分割符进行字段连接CONCAT_WS()
SELECT CONCAT_WS('|','A','B','C');//A|B|C

--数字格式化FORMAT()
SELECT FORMAT('1024.512','2');//1024.51

--转化为小写字母LOWER(),大写字母UPPER()
SELECT LOWER('MySQL');//mysql

--获取左侧字符LEFT(),右侧RIGHT()
SELECT LEFT('MySQL','2');//My

--获取字符长度LENGTH();
SELECT LENGTH('MySQL');//5

--字符串替换REPLACE();
SELECT REPLACE('??My??SQL','?','|');//||My||SQL

--字符串截取SUBSTRING()
SELECT SUBSTRING('MySQL','1','2');//My

--模式匹配[NOT] LIKE
--类似于linux中的*号
--[通配符]%(百分号):代表任意个字符;_(下划线)代表一个字符
SELECT * FROM employe WHERE `age` LIKE '%1';

(2)数值运算符与函数

--向上取整CEIL();向下取整FLOOR()
SELECT CEIL(4.01);//5

(3)比较运算符与函数

--[不]在范围之内[NOT] BETWEEN...AND...
SELECT 12 BETWEEN 1 AND 30;//1;
--[不]在列出值范围[NOT]IN()
SELECT 10 IN(1,10,15,20);//1

(4)日期时间函数:其实很少用,一般都是用PHP将时间转换为时间戳的形式存储。

--NOW():当前日期和时间
--CURDATE():当前日期
--CURTIME():当前时间
SELECT NOW();
--DATE_ADD()日期变化
SELECT DATE_ADD('2018-05-16',INTERVAL -365 DAY);//2017-05-16
--DATEDIFF()日期差值
SELECT DATEDIFF('2018-05-16','2017-05-20');//361

(5)信息函数

(6)聚合函数:都是大写。一般用在数据表中,单独书写会报错。并且只有一个返回值。

select AVG(`age`) from employe;//查询employe表年龄的平均值

(7)加密函数

7.自定义函数

(1)自定义函数:简称UDF,是对MySQL扩展的一种途径。

(2)创建自定义函数:[参数,返回值]

CREATE FUNCTION function_name() 
RETURNS  返回类型
RETURN   函数体;

(3)关于函数体:A.函数体由合法的SQL语法构成;B.函数体可以是简单的SELECT或INSERT语句;C.函数体如果为复合结构则使用BEGIN...END语句;D.复合结构可以包括声明,循环,控制结构。

举个栗子:

--自定义函数
#创建无参函数
CREATE FUNCTION f1()
RETURNS CHAR(32)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

--调用函数
SELECT fi();//2018年05月16日 16点:47分:36秒

--删除函数
DROP FUNCTION f1;

#创建有参函数
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2)
RETURN (num1 num2)/2;

SELECT f2(6,8);//7