数据库装的目录：
D:\p4workspace\1668\SRCT_TG\Settings\00.TG New Feature\ActivationV2\Server\softwares\MySQL\mysql-5.7.10-winx64
MySQL 默认的端口号是 3306
MySQL 中超级用户是 root
创建数据库 CREATE DATABASE
修改数据库 ALTER DATABASE
删除数据库 DROP DATABASE
SELECT VERSION();
显示当前服务器版本
SELECT NOW();
显示当前日期时间
SELECT USER();
显示当前用户
显示 errors warnings
关键字和函数名称全部大写
数据库名称、表名称、字段名称全部小写
SQL语句必须以分号结尾
mysql> PROMPT \u@\h \d>
PROMPT set to '\u@\h \d>
root@localhost (none)>
用户 host 哪个库
改变输出的提示符
CREATE { DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
{}为必选项 []为可选项
创建数据库
SHOW {DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr]
查看数据库
root@localhost (none)>SHOW CREATE DATABASE xiaoleilei;
Database | Create Database xiaoleilei | CREATE DATABASE `xiaoleilei` /*!40100 DEFAULT CHARACTER SET latin1 */
查看数据库的命令
root@localhost (none)>CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET utf8;
用指定的字符集创建数据库
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
ALTER DATABASE xiaoleilei CHARACTER SET utf8;
改变数据库的编码方式
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
root@localhost (none)>DROP DATABASE t2;
删除数据库
数据类型
数据表
数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础。
USE
打开数据库
USE 数据库名称；
root@localhost (none)>USE xiaoleilei;
Database changed
创建数据表
CREATE TABLE [IF NOT EXISTS] table_name (
Column_name data_type,
….
)
root@localhost xiaoleilei>CREATE TABLE tb1(
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.54 sec)
root@localhost xiaoleilei>SHOW TABLES;
Tables_in_xiaoleilei
tb1
1 row in set (0.03 sec)
SHOW TABLES
查看数据表列表
SHOW TABLES [FROM db_name] [LIKE ‘pattern’ | WHERE expr]
root@localhost xiaoleilei>SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
SHOW COLUMNS
查看数据表结构
SHOW COLUMNS FROM tbl_name
INSERT
插入记录
INSERT [INTO] tbl_name [(col_name,…)] VALUES(val,…)
root@localhost xiaoleilei>INSERT tb1 VALUES('Tom',25,7863.25);
Query OK, 1 row affected (0.12 sec)
root@localhost xiaoleilei>INSERT tb1(username,salary) VALUES('John',4500.69);
Query OK, 1 row affected (0.10 sec)
SELECT
记录查找
SELECT expr,… FROM tb1_name
空值和非空
NULL, 字段可以为空
NOT NULL, 字段值禁止为空
创建数据表
mysql> CREATE TABLE TB2(
-> usename VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
Query OK, 0 rows affected (0.40 sec)
查看表结构
mysql> SHOW COLUMNS FROM tb2;
AUTO_INCREMENT
自动编号，且必须与主键组合使用
默认情况下，起始值为1，每次的增量为1
PRIMARY KEY
主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为 NOT NULL
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(30) NOT NULL
-> );
UNIQUE KEY
唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（NULL）
每张数据表可以存在多个唯一约束
mysql> CREATE TABLE tb5
-> (
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> usename VARCHAR(20) NOT NULL UNIQUE KEY,
-> age TINYINT UNSIGNED
-> )
-> ;
DEFAULT
默认值
当插入记录是，如果没有明确为字段赋值，则自动赋予默认值。
mysql> CREATE TABLE tb6(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> sex ENUM('1','2','3') DEFAULT '3'
-> );
数据类型：字符型，整型，浮点型，日期时间型
数据表操作：插入记录，查找记录
记录操作：创建数据表，约束的使用，PRIMARY KEY, UNIQUE KEY, DEFAULT, NOT NULL
约束
FOREIGN KEY
保持数据一致性，完整性。
实现一对一或一对多关系。
编辑数据表的默认存储引擎
MySQL 配置文件
default-storage-engine=INNODB
显示创建表的过程
创建主键，外键过程：
显示表创建时的完整语句：
显示一个表的索引：
创建含外键的表
mysql> CREATE TABLE user1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
-> );
在插入数据不成功时，id 编号也会自动增长
InnoDB 支持级联删除，其他引擎不支持。
在大部分项目中都是定义逻辑外键不使用物理外键。
修改数据表
添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
增加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,……)
删除表的一列：
mysql> alter table users drop truename;
同时删除两列：
mysql> alter table users drop password, drop age;
可以在一句SQL中做增加列和删除列的操作
添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [INDEX_TYPE] (index_col_name,…)
添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
实例:
给表加上一列
mysql> alter table users2 add id smallint unsigned;
给一列加上主键约束并给其命名
mysql> alter table users2 add constraint pk_users2_id primary key (id);
给一列加上唯一性约束
mysql> alter table users2 add unique (username);
在表中加入外键
mysql> alter table users2 add foreign key (pid) references provinces (id);
在表中加一列
mysql> alter table users2 add age tinyint unsigned not null;
设置列的默认值
mysql> alter table users2 alter age set default 15;
删除列的默认值
mysql> alter table users2 alter age drop default;
删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
删除表的主键约束
alter table users2 drop primary key;
删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
查看表的约束条件
mysql> show indexes from users2\G;
删除约束：
mysql> alter table users2 drop index username;
删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
删除外键约束
mysql> alter table users2 drop foreign key users2_ibfk_1;
删除索引
mysql> alter table users2 drop index pid;
修改数据表
修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
把一列放到表的最前方
mysql> alter table users2 modify id smallint unsigned not null first;
修改列定义的数据的类型
mysql> alter table users2 modify id tinyint unsigned not null;
修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]
修改列名称并定义
mysql> alter table users2 change pid p_id tinyint unsigned not null;
数据表更名
方法1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法2
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] …
mysql> alter table users2 rename users3;
mysql> rename table users3 to users2;
本节知识点
INSERT
插入记录
方式一:
INSERT [INTO] tbl_name [(col_name,…)] {VALUES | VALUES} ({expr | DEFAULT},…),(…),…
例子：
自增长的主键id 可以插入 null 或者 default
mysql> insert users value(null, 'Tom', '123', 25, 1);
mysql> insert users value(null, 'John', '456', 25, 1);
mysql> insert users value(default, 'Tom', '123', 25, 1);
可以写表达式，mysql 会自动计算出来
mysql> insert users value(default, 'Tom', '123', 3*7-5, 1);
有默认值的地方可以用 default 表示
mysql> insert users value(default, 'Tom', '123', default, 1);
可以同时插入多行，可以用函数的插入
mysql> insert users value(default, 'Tom', '123', 3*7-5, 1), (null, 'Rose', md5('123'), default, 0);
方式二:
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…
可以使用子查询
方式三:
INSERT [INTO] tbl_name [(col_name,…)] SELECT …
可以将查询结果插入到指定数据表
UPDATE
更新记录(单表更新)
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr1 | DEFAULT} [, col_name={expr2 | DEFAULT}] … [WHERE where_condition]
例子:
mysql> update users set age = age + 5;
mysql> update users set age = age - id, sec = 0;
mysql> update users set age = age + 10 where id % 2 = 0;
DELETE
删除记录(单表删除)
DELETE FROM tab_name [WHERE where_condition]
mysql> delete users where id = 5;
SELECT
查找记录
SELECT select_expr [ , select_expr … ]
[
FROM table_references
[ WHERE where_condition ]
[ GROIP BY { col_name | position} [ASC | DESC], … ]
[ HAVING where_condition ]
[ ORDER BY { col_name | expr | position} [ ASC | DESC], … ]
[ LIMIT {[ offset, ] row_count | row_count OFFSET offset } ]
]
每一个表达式表示想要的一列，必须有至少一个。
多个列之间以英文逗号分隔。
星号(*)表示所有列。Tbl_name.* 可以表示命名表的所有列。
查询表达式可以使用 [AS] alias_name 为其赋予别名。
别名可以用于 GROUP BY, ORDER BY 或 HAVING 子句。
WHERE
条件表达式
对记录进行过滤，如果没有指定where子句，则显示所有记录。
在where表达式中，可以使用MySQL支持的函数或运算符。
GROUP BY
查询结果分组
[GROUP BY { col_name | position} [ ASC | DESC], …]
mysql> select sex from users group by sex;
HAVING
分组条件
[ HAVING where_condition ]
mysql> select sex,age from users group by sex having age > 20;
mysql> select * from users group by sex having age > 20;
mysql> select * from users group by sex having count(sex) > 1;
ORDER BY
对查询结果进行排序
[ ORDER BY { col_name | expr | position} [ ASC | DESC ], … ]
mysql> select * from users order by age desc;
mysql> select * from users order by id desc;
mysql> select * from users order by id desc,password desc;
mysql> select * from users order by id desc,password asc;
mysql> select * from users order by age desc, id desc;
mysql> select * from users order by age, id desc;
LIMIT
限制查询结果返回的数量
[ LIMIT { [ offset, ] row_count | row_count OFFSET offset }]
mysql> select * from users limit 2;
mysql> select * from users limit 3,2;
mysql> select * from users limit 1,2;
mysql> select * from users limit 1,3;
mysql> select * from users order by id desc limit 2,2;
INSERT
插入记录
INSERT [ INTO ] tbl_name SET col_name = { expr | DEFAULT }, …
mysql> insert test(username) select username from users where age >= 30;
此方法可以使用子查询( SubQuery )
SET NAMES GBK;
改变命令行显示的编码
子查询
子查询( Subquery ) 是指出现在其他 SQL 语句内的 SELECT 子句。
例如：
SELECT * FROM t1 WHERE col1 = ( SELECT col2 FROM t2);
其中 SELECT * FROM t1, 称为 Outer Query / Outer Statement
SELECT col2 FROM t2, 称为 SubQuery
子查询指嵌套在查询内部，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件，
如DISTINCT, GROIP BY, ORDER BY, LIMIT, 函数等。
子查询的外层查询可以是: SELECT, INSERT, UPDATE, SET 或 DO.
子查询返回值
子查询可以返回标量，一行，一列或子查询
使用比较运算符的子查询
使用比较运算符的子查询
=, >, <, >=, <=, <>, !=, < = >,
语法结构
operand comparison_operator subquery
mysql> select round(avg(goods_price), 2) from tdb_goods;
mysql> select goods_id, goods_name, goods_price from tdb_goods where goods_price >= 5636.36;
mysql> select goods_id, goods_name, goods_price from tdb_goods where goods_price >= (select round(avg(goods_price), 2) from tdb_goods);
mysql> select goods_price from tdb_goods where goods_cate = '超级本';
mysql> select * from tdb_goods where goods_cate = '超级本'\G;
用 ANY, SOME 或 ALL修饰的比较运算符
operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)
operand comparison_operator ALL(subquery)
mysql> select goods_id, goods_name, goods_price from tdb_goods where goods_price > any ( select goods_price from tdb_goods where goods_cate = '超级本');
mysql> select goods_id, goods_name, goods_price from tdb_goods where goods_price > all ( select goods_price from tdb_goods where goods_cate = '超级本');
mysql> select goods_id, goods_name, goods_price from tdb_goods where goods_price = any ( select goods_price from tdb_goods where goods_cate = '超级本');
使用 [ NOT ] IN 的子查询
语法结构
operand comparision_operator [NOT] IN (subquery) = ANY 运算符与 IN 等效。
!= ALL 或 <> ALL 运算符与NOT IN 等效。
mysql> select goods_id, goods_name, goods_price from tdb_goods where goods_price <> all ( select goods_price from tdb_goods where goods_cate = '超级本');
使用 [ NOT ] EXISTS 的子查询
如果子查询返回任何行; EXISTS 将返回 TRUE; 否则为 FALSE;
mysql> create table if not exists tdb_goods_cates(
-> cate_id smallint unsigned primary key auto_increment,
-> cate_name varchar(40) not null
-> );
INSERT…SELECT
将查询结果写入数据表
INSERT [ INTO ] tbl_name [ ( col_name, …) ]
SELECT …
mysql> insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;
多表更新
UPDATE table_references set col_name1 = { expr1 | DEFAULT }
[ , col_name2 = { expr2 | DEFAULT} ] …
[ WHERE where_condition ]
mysql> update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name
-> set goods_cate = cate_id ;
CREATE…SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [ IF NOT EXISTS ] tbl_name
[ ( create_definition, … ) ]
select_statement
mysql> create table tdb_goods_brands (
-> brand_id smallint unsigned primary key auto_increment,
-> brand_name varchar(40) not null
-> )
-> select brand_name from tdb_goods group by brand_name;
mysql> update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name = b.brand_name
-> set g.brand_name = b.brand_id;
mysql> alter table tdb_goods
-> change goods_cate cate_id smallint unsigned not null,
-> change brand_name brand_id smallint unsigned not null;
FROM子句中的子查询
语法结构
SELECT … FROM ( subquery ) [ AS ] name …
说明:
名称为必选项, 且子查询的列名称必须唯一。
连接
MySQL 在 SELECT 语句, 多表更新, 多表删除语句中支持 JOIN 操作。
语法结构
table_reference
{ [ INNER | CROSS] JOIN | { LEFT | RIGHT } [ OUTER] JOIN }
table_reference
ON confitional_expr
数据表参照
table_reference
tbl_name [ [ AS ] alias ] | table_subquery [ AS ] alias
数据表可以使用 tbl_name AS alias_name
或 tbl_name alias_name 赋予别名。
table_subquery 可以作为子查询使用在 FROM 子句中,
这样的子查询必须为其赋予别名。
连接类型
INNER JOIN, 内连接
在 MySQL 中, JOIN, CROSS JOIN 和 INNER JOIN 是等价的。
LEFT [ OUTER ] JOIN , 左外连接
RIGHT [ OUTER ] JOIN, 右外连接
连接条件
使用 ON 关键字来设定连接条件，也可以使用 WHERE 来代替。
通常使用 ON 关键字来设定连接条件，
使用 WHERE 关键字进行结果集记录的过滤。
内连接
mysql> select goods_id, goods_name, cate_name from tdb_goods inner join
-> tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_id;
左外连接
mysql> select goods_id, goods_name, cate_name from tdb_goods left join
-> tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_id;
右外连接
mysql> select goods_id, goods_name, cate_name from tdb_goods right join
-> tdb_goods_cates on tdb_goods.cate_id = tdb_goods_cates.cate_id;
多表连接
mysql> select goods_id, goods_name, cate_name, brand_name, goods_price from tdb_goods as g
-> inner join tdb_goods_cates as c on g.cate_id = c.cate_id
-> inner join tdb_goods_brands as b on g.brand_id = b.brand_id;
外连接
A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A。
数据表A的结果集根据左连接条件依赖所有数据表 ( B 表除外 )。
左外连接条件决定如何检索数据表B (在没有指定WHERE 条件的情况下)。
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，
将声场一个所有列为空的额外的B行。
如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE 子句中尝试以下操作:
col_namd IS NULL 时，如果 col_name 被定义为 NOT NULL， MySQL 将在找到符合连执着条件的记录后停止搜索更多的行。
无限分类的数据表设计
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
自身连接
同一个数据表对其自身进行连接。
所有记录:
找其父类
mysql> select s.type_id, s.type_name, p.type_name from tdb_goods_types as s left join
-> tdb_goods_types as p on s.parent_id = p.type_id;
找其子类
mysql> select p.type_id, p.type_name, s.type_name from tdb_goods_types as p left join
-> tdb_goods_types as s on s.parent_id = p.type_id;
显示子类数量
mysql> select p.type_id, p.type_name,count(s.type_name) as child_count from tdb_goods_types as p left join
-> tdb_goods_types as s on s.parent_id = p.type_id group by p.type_id;
多表删除
DELETE tbl_name [ .* ] [ , tbl_name [ .* ] ] …
FROM table_references
[ WHERE where_condition ]
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (
SELECT goods_id,goods_name FROM tdb_goods
GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2
ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;
字符函数
CONCAT() 正常的字符连接功能
mysql> select concat('imooc','MySQL');
mysql> select concat('imooc', '-', 'MySQL');
mysql> select concat(id,username) as Full from test;
CONCAT_WS() 带分隔符的字符连接功能，分隔符为第一个参数
mysql> select concat_ws('|', 'A', 'B', 'C');
mysql> select concat_ws('-', 'imooc', 'MySQL', 'Functions');
FORMAT() 数字格式化
mysql> select format(12560.75,2);
| 12,560.75 |
mysql> select format(12560.75,1);
| 12,560.8 |
mysql> select format(12560.75,0);
| 12,561 |
LOWER() 将字符转化为小写
mysql> select lower('MySQL');
| mysql |
UPPER() 将字符转化为大写
mysql> select upper('mysql');
| MYSQL |
LEFT() 得到左侧字符
mysql> select left('MySQL',2);
| My |
mysql> select lower(left('MySQL',2));
| my |
RIGHT() 得到右侧字符
mysql> select right('MySQL',3);
| SQL |
LENGTH() 获得字符串的长度
mysql> select length('My SQL');
| 6 |
LTRIM() 去除左侧的空格
mysql> select length(' MySQL ');
| length(' MySQL ') |
| 11 |
mysql> select length(ltrim(' MySQL '));
| 9 |
RTRIM() 去除右侧的空格
mysql> select length(rtrim(' MySQL '));
| 7 |
TRIM() 去除两端的空格
mysql> select length(trim(' MySQL '));
| 5 |
mysql> select length(trim(' My SQL '));
| 6 |
mysql> select trim( leading '?' from '??MySQL???');
| MySQL??? |
mysql> select trim( trailing '?' from '??MySQL???');
| ??MySQL |
mysql> select trim( both '?' from '??MySQL???');
+-----------------------------------+
| MySQL |
REPLACE() 字符串替换
mysql> select replace('??MY??SQL???', '?', '');
| MYSQL |
mysql> select replace('??MY??SQL???', '?', '!*');
| !*!*MY!*!*SQL!*!*!* |
mysql> select replace('??MY??SQL???', '??', '!');
| !MY!SQL!? |
SUBSTRING() 字符串截取
mysql> select substring('MySQL', 1, 2);
| My |
mysql> select substring('MySQL', 3);
| SQL |
mysql> select substring('MySQL', -1);
| L |
mysql> select substring('MySQL', -3, 3);
| SQL |
[NOT] LIKE 模式匹配
% 代表任意个字符
_ 代表任意一个字符
mysql> select * from test where first_name like '%o%';
mysql> select * from test where first_name like '%1%%' escape '1';
数值运算符与函数
mysql> select ceil(3.01);
| 4 |
mysql> select floor(3.01);
| 3 |
mysql> select 3 /4;
| 0.7500 |
mysql> select 3 div 4;
| 0 |
mysql> select 5 % 3;
| 2 |
mysql> select 5 mod 3;
| 2 |
mysql> select 5.3 mod 3;
| 2.3 |
mysql> select power(3,3);
| 27 |
mysql> select round(3.652, 2);
| 3.65 |
mysql> select round(3.652, 1);
| 3.7 |
mysql> select round(3.652, 0);
| 4 |
mysql> select truncate(125.89, 2);
| 125.89 |
mysql> select truncate(125.89, 1);
| 125.8 |
mysql> select truncate(125.89, 0);
| 125 |
mysql> select truncate(125.89, -1);
| 120 |
mysql> select 15 between 1 and 22;
| 1 |
mysql> select 35 between 1 and 22;
| 0 |
mysql> select 35 not between 1 and 22;
| 1 |
mysql> select 10 in(5,10,15,20);
| 1 |
mysql> select 13 in(5,10,15,20);
| 0 |
mysql> select null is null;
| 1 |
mysql> select '' is null;
| 0 |
mysql> select 0 is null;
| 0 |
mysql> select * from test where first_name is null;
mysql> select * from test where first_name is not null;
日期时间函数
mysql> select now();
| 2018-05-10 16:09:27 |
mysql> select curdate();
| 2018-05-10 |
mysql> select curtime();
| 16:10:15 |
mysql> select date_add('2018-5-10', interval 365 day);
| 2019-05-10 |
mysql> select date_add('2018-5-10', interval -365 day);
| 2017-05-10 |
mysql> select date_add('2018-5-10', interval 1 year);
| 2019-05-10 |
mysql> select date_add('2018-5-10', interval 3 week);
| 2018-05-31 |
mysql> select datediff('2018-5-10','2019-5-10');
| -365 |
mysql> select date_format('2018-5-2','%m/%d/%Y');
| 05/02/2018 |
信息函数
mysql> select connection_id();
| 2 |
mysql> select database();
| study |
mysql> select last_insert_id();
| 0 |
mysql> select user();
| root@localhost |
mysql> select version();
| 5.5.60 |
聚合函数
mysql> select avg(goods_price) as avg_price from tdb_goods;
| 5654.8095238 |
mysql> select round(avg(goods_price),2) as avg_price from tdb_goods;
| 5654.81 |
mysql> select count(goods_id) as counts from tdb_goods;
| 21 |
mysql> select max(goods_price) as counts from tdb_goods;
| 28888.000 |
mysql> select min(goods_price) as counts from tdb_goods;
| 99.000 |
mysql> select sum(goods_price) as counts from tdb_goods;
| 118751.000 |
加密函数
mysql> set password = password('root');
mysql> select md5('admin');
| 21232f297a57a5a743894a0e4a801fc3 |
mysql> select password('admin');
| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
自定义函数
自定义函数
用户自定义函数( user-defined function, UDF) 是一种对 MySQL 扩展的途径，其用法与内置函数相同。
自定义函数的两个必要条件:
1, 参数
2, 返回值
函数可以返回任意类型的值，同样可以接收这些类型的参数
创建自定义函数
CREATE FUNCTION function_name
RETURNS
{ STRING | INTEGER | REAL | DECIMAL}
routine_body
关于函数体
1, 函数体由合法的SQL语句构成;
2, 函数体可以是简单的 SELECT 或 INSERT 语句;
3, 函数体如果为复合结构则使用 BEGIN…END语句;
4, 复合结构可以包含声明, 循环, 控制结构;
创建函数:
mysql> create function f1() returns varchar(30)
-> return date_format(now(), '%Y年%m月%d日 %H点:%i分:%s秒');
Query OK, 0 rows affected (0.01 sec)
mysql> select f1();
+-------------------------------+
| 2018年05月11日 10点:49分:32秒 |
+-------------------------------+
mysql> drop function f2;
Query OK, 0 rows affected (0.01 sec)
mysql> create function f2(num1 smallint unsigned, num2 smallint unsigned)
-> returns float(10,2) unsigned
-> return (num1 + num2) / 2;
mysql> select f2();
ERROR 1318 (42000): Incorrect number of arguments for FUNCTION study.f2; expected 2, got 0
mysql> select f2(10,15);
+-----------+
| 12.50 |
+-----------+
mysql> delimiter //
mysql> create function adduser(username varchar(20))
-> returns int unsigned
-> begin
-> insert test(username) values(username);
-> return last_insert_id();
-> end
-> //
mysql> select adduser('Rose');
-> //
+-----------------+
| adduser('Rose') |
+-----------------+
| 3 |
+-----------------+
mysql> delimiter ;
mysql> select adduser('Tom');
+----------------+
| adduser('Tom') |
+----------------+
| 4 |
+----------------+
mysql> select * from test;
+----+----------+
| id | username |
+----+----------+
| 1 | John |
| 2 | 111 |
| 3 | Rose |
| 4 | Tom |
+----+----------+
删除函数
DROP FUNCTION [IF EXISTS] function_name
mysql> drop function f2;
存储过程
SQL语句执行过程
存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
存储过程的优点
增强SQL语句的功能和灵活性
实现较快的执行速度
减少网络流量
创建存储过程
CREATE
[ DEFINER = { user | CURRENT_USER} ]
PROCEDURE sp_name ([ proc_parameter[,…]])
[characteristic …] routine_body
Proc_parameter
[ IN | OUT | INOUT] param_name type
参数
IN 表示该参数的值必须在调用存储过程时指定
OUT 表示该参数的值可以被存储过程改变，并且可以返回
INOUT 表示该参数的调用时指定，并且可以被改变和返回
特性
COMMENT ‘string’
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY { DEFINER | INVOKER}
COMMENT: 注释
CONTAINS SQL: 包含SQL语句，但不包含读和写数据的语句
NO SQL: 不包含SQL 语句
READS SQL DATA: 包含读数据的语句
MODIFIES SQL DATA: 包含写数据的语句
SQL SECURITY { DEFINER | INVOKER}: 指明谁有权限来执行
过程体
过程体有合法的SQL语句构成;
过程体可以是任意SQL语句；
过程体如果为复合结构则使用BEGIN…END语句；
复合结构可以包含声明，循环，控制结构；
调用存储过程
CALL sp_name([ parameter[,…]])
CALL sp_name[()]
mysql> create procedure sp1() select version();
mysql> call sp1;
+-----------+
| version() |
+-----------+
| 5.5.60 |
+-----------+
mysql> call sp1();
+-----------+
| version() |
+-----------+
| 5.5.60 |
+-----------+
mysql> create procedure sp1() select version();
Query OK, 0 rows affected (0.01 sec)
mysql> call sp1;
+-----------+
| version() |
+-----------+
| 5.5.60 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call sp1();
+-----------+
| version() |
+-----------+
| 5.5.60 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> desc users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> select * from users;
+----+----------+----------------------------------+-----+------+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | Tom | 123 | 27 | 0 |
| 5 | Tom | 123 | 10 | 0 |
| 6 | Rose | 202cb962ac59075b964b07152d234b70 | 8 | 0 |
| 7 | Ben | 456 | 17 | 0 |
| 8 | 111 | 222 | 33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> delimiter //
mysql> create procedure removeUserById(IN id int unsigned)
-> begin
-> delete from users where id = id; // 删除了所有记录，执行过程中不能分析出 id 含义
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call removeUserById(3);
Query OK, 8 rows affected (0.00 sec)
mysql> select * from users;
Empty set (0.00 sec)
mysql> drop procedure removeUserById;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> create procedure removeUserById( IN p_id int unsigned)
-> begin
-> delete from users where id = p_id;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 9 | A | | 20 | 1 |
| 10 | B | | 20 | 1 |
| 11 | C | | 20 | 1 |
| 12 | D | | 20 | 1 |
| 13 | E | | 20 | 1 |
| 14 | F | | 20 | 1 |
| 15 | G | | 20 | 1 |
| 16 | H | | 20 | 1 |
| 17 | I | | 20 | 1 |
| 18 | J | | 20 | 1 |
| 19 | K | | 20 | 1 |
| 20 | L | | 20 | 1 |
| 21 | M | | 20 | 1 |
| 22 | N | | 20 | 1 |
| 23 | O | | 20 | 1 |
| 24 | P | | 20 | 1 |
| 25 | Q | | 20 | 1 |
+----+----------+----------+-----+------+
17 rows in set (0.00 sec)
mysql> call removeUserById(10);
Query OK, 1 row affected (0.03 sec)
mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 9 | A | | 20 | 1 |
| 11 | C | | 20 | 1 |
| 12 | D | | 20 | 1 |
| 13 | E | | 20 | 1 |
| 14 | F | | 20 | 1 |
| 15 | G | | 20 | 1 |
| 16 | H | | 20 | 1 |
| 17 | I | | 20 | 1 |
| 18 | J | | 20 | 1 |
| 19 | K | | 20 | 1 |
| 20 | L | | 20 | 1 |
| 21 | M | | 20 | 1 |
| 22 | N | | 20 | 1 |
| 23 | O | | 20 | 1 |
| 24 | P | | 20 | 1 |
| 25 | Q | | 20 | 1 |
+----+----------+----------+-----+------+
16 rows in set (0.00 sec)
mysql> drop procedure removeUserByAgeAndReturnInfos;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> create procedure removeUserByAgeAndReturnInfos( IN p_age smallint unsigned, out deleteUsers smallint unsigned,
-> out userCounts smallint unsigned)
-> begin
-> delete from users where age = p_age;
-> select row_count() into deleteUsers;
-> select count(id) from users into userCounts;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
-> //
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 9 | A | | 20 | 1 |
| 11 | C | | 20 | 1 |
| 12 | D | | 20 | 1 |
| 13 | E | | 20 | 1 |
| 14 | F | | 20 | 1 |
| 15 | G | | 21 | 1 |
| 16 | H | | 21 | 1 |
| 17 | I | | 21 | 1 |
| 18 | J | | 20 | 1 |
| 19 | K | | 20 | 1 |
| 20 | L | | 20 | 1 |
| 21 | M | | 20 | 1 |
| 22 | N | | 20 | 1 |
| 24 | P | | 20 | 1 |
| 25 | Q | | 20 | 1 |
+----+----------+----------+-----+------+
15 rows in set (0.00 sec)
mysql> delimiter ;
mysql> call removeUserByAgeAndReturnInfos(21, @a, @b);
Query OK, 1 row affected (0.00 sec)
mysql> select @a, @b;
+------+------+
| @a | @b |
+------+------+
| 3 | 12 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 9 | A | | 20 | 1 |
| 11 | C | | 20 | 1 |
| 12 | D | | 20 | 1 |
| 13 | E | | 20 | 1 |
| 14 | F | | 20 | 1 |
| 18 | J | | 20 | 1 |
| 19 | K | | 20 | 1 |
| 20 | L | | 20 | 1 |
| 21 | M | | 20 | 1 |
| 22 | N | | 20 | 1 |
| 24 | P | | 20 | 1 |
| 25 | Q | | 20 | 1 |
+----+----------+----------+-----+------+
12 rows in set (0.00 sec)
删除存储过程
DROP PROCEDURE [ IF EXISTS ] sp_name
用户变量
mysql> set @i = 7;
mysql> select @i;
+------+
| 7 |
+------+
存储过程与自定义函数的区别
存储过程实现的功能要复杂一些；而函数的针对性更强
存储过程可以返回多个值；函数只能有一个返回值
存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。
修改存储过程
ALTER PROCEDURE sp_name [ characteristic … ]
COMMENT ‘string’
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
修改存储过程
1, 创建存储过程或者自定义函数时需要通过delimiter 语句修改定界符。
2, 如果函数体或过程体有多个语句，需要包含在BEGIN……END语句块中。
3, 存储过程通过call 来调用。
存储引擎
MySQL 可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制，索引技巧，锁定水平，最终提供广泛且不同的功能。
存储引擎
- MyISAM: 适用与事务的处理不多的情况。
存储限制可达256TB，支持索引，表级锁定，数据压缩。
- InnoDB: 适用于事务处理比较多，需要有外键支持的情况。
存储限制为64TB，支持事务和索引，锁颗粒为行锁。
- Memory
- CSV
- Archive
相关知识点
并发控制
- 当多个连接对记录进行修改时保证数据的一致性和完整性。
锁
- 共享锁（读锁）
在同一时间段内，多个用户可以读取同一个资源，读入过程中数据不会发生任何变化
- 排他锁（写锁）
在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒
- 表锁，是一种开销最小的锁策略。
- 行锁，是一种开销最大的锁策略。
事务
- 事务用于保证数据库的完整性
事务的特性
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability）
外键
- 是保证数据一致性的策略
索引
- 是对数据表中一列或多列的值进行排序的一种结构。
存储引擎的特点
索引：普通索引，唯一索引，全文索引，btree索引，hash索引……
修改存储引擎的方法
MySQL 5.6 之后默认是 InnoDB
通过修改MySQL配置文件实现
- Defaultstorage-engine = engine
通过创建数据表命令实现
- CREATE TABLE table_name(
- …
- …
- ) ENGINE = engine;
mysql> create table tp1 (
-> s1 varchar(10)
-> ) engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table tp1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| tp1 | CREATE TABLE `tp1` (
`s1` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
通过修改数据表命令实现
- ALTER TABLE table_name ENGINE [=] enginename;
mysql> alter table tp1 engine = innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tp1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| tp1 | CREATE TABLE `tp1` (
`s1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
MySQL 管理工具
- PHPMyAdmin
- Navicat
- MySQL Workbench
视图
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
