MySQL视频笔记_mysql ver 14.14 distrib 5.5.59-CSDN博客

已经发布博客

慕课网的免费课程

综合评分：9.7

非常高，听后效果也是很好，没有废话，都是干货，总共8.5小时，让你对MySQL有个初步了解。

https://www.imooc.com/learn/122

'''

#2018-06-19 484508 June Tuesday the 25 week, the 170 day SZ

C:\Users\z003tesj>mysql -uroot -p044500 -P3306 -h127.0.0.1

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 40

Server version: 5.5.59 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

'''

与MySQL的零距离接触

左下角开始输入cmd，回车，

打开mysql:

net start mysql

登陆MySQL客户端：

mysql -uroot -p044500 -P3306 -h127.0.0.1

或者：

mysql -uroot -p044500

出现mysql>说明成功登陆了，

然后就进入系统，展示所有的数据库：

mysql> SHOW DATABASES;

选择一个数据库，要不然会报错：ERROR 1046(3D000): No Database Selected

接着使用数据库：

mysql> USE test

显示所有的列表：

SHOW TABLES;

显示列表中某个内容：

SHOW COLUMNS FROM users;

显示users的具体内容：

SELECT * FROM users;

##############################################

SHOW DATABASE;

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| t2 |

| test |

+--------------------+

5 rows in set (0.01 sec)

SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| provinces |

| tb1 |

| tb2 |

| tb3 |

| tb4 |

| tb5 |

| tb6 |

| users1 |

| users3 |

+----------------+

SHOW TABLES FROM mysql;

SHOW COLUMNS FROM tb3;

SHOW TABLES;

SHOW COLUMNS;

SHOW CREATE TABLE provinces;

SHOW INDEXES FROM provinces;

SHOW COLUMNS FROM user;

SHOW CREATE TABLE users1;

mysql> CREATE TABLE users(

-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT

-> username VARCHAR(20) NOT NULL,

-> password VARCHAR(20) NOT NULL,

-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,

-> sex BOOLEAN

-> );

mysql> SHOW COLUMNS FROM users;

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | 10 | |

| sex | tinyint(1) | YES | | NULL | |

+----------+----------------------+------+-----+---------+----------------+

mysql> SELECT * FROM users;

+----+----------+----------+-----+------+

| id | username | password | age | sex |

+----+----------+----------+-----+------+

| 1 | Tom | 123 | 25 | 1 |

+----+----------+----------+-----+------+

1 row in set (0.00 sec)

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

登陆MySQL客户端：

mysql -uroot -p044500

出现mysql>说明成功登陆了

接着使用数据库

代码：

USE test;

接着创建数据表：

代码：

CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20) NOT NULL);

MySQL基础

1.1

瑞典 MySQL AB公司开发，目前属于Oracle公司，开源关系型数据库

社区版和企业版

Server version: 5.5.59 MySQL Community Server (GPL)

1.2 、安装

MSI安装方式

MySQL Server 5.5 安装

下载地址

https://dev.mysql.com/downloads/mysql/

C:\Program Files\MySQL\MySQL Server 5.5\bin\

MySQLInstanceConfig.exe

该文件进行配置

detailed Configuration不用，因为麻烦

我们选择standard configuration

下一步选择install as windows service

launch the mysql server

include bin directory in windows path添加到Windows路径

下一步：modify security settings

password 044500

root是超级用户

bin存储可执行文件

data存储数据文件

docs，文档目录

include包含的头文件

lib库文件

share错误信息和字符集文件

修改编码方式：

C:\Program Files\MySQL\MySQL Server 5.5\my.ini

双击my.ini

port端口号 3306

在port=3306后面修改编码格式为utf8

搜索“default-character-set”将其改为utf8，

搜索“character-set-server=utf8”将其改为utf8

按照老师要求，修改完后，发现无法保存。

解决办法：修改后另存为桌面，然后把桌面修改好的my.ini复制到路径替换以前的my.ini

1.3

启动mysql

控制栏输入

C:\Users\z003tesj>net start mysql

停止服务：net stop mysql

但是会报错：System error 5 has occurred. Access is denied."

解决方案，

先关闭mysql，

在开始的附件里找到 command prompt右击，用管理员模式打开就解决了。

说明系统正确配置和启动了

1.4

mysql登陆和退出

cls清屏

查看版本

C:\Windows\system32>mysql -V

mysql Ver 14.14 Distrib 5.5.59, for Win64 (AMD64)

-u用户名

-P端口号

-p密码

--prompt=name设置用户名

-h服务器名称

-D打开指定数据库

--delimiter=name指定分隔符

管理员权限用户名

mysql -uroot -p

C:\Windows\system32>mysql -uroot -p

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.59 MySQL Community Server (GPL)

本地回环地址-h127.0.0.1

mysql> mysql -uroot -p -P3306 -h127.0.0.1

默认0006端口和本地服务器

所以-P3306 和h 也可以不写

退出：exit or quit or \q

mysql> mysql -uroot -p -P3306 -h127.0.0.1

-> \q

Bye

按下键盘的上下箭头，让命令重新出现，选择自己需要的命令就行

1.5

修改MySQL提示符

1》连接客户端时候通过参数指定：shell>mysql -uroot -proot --prompt 提示符

连接上客户端后，通过prompt命令修改

mysql>prompt提示符

C:\Windows\system32>mysql -uroot -p044500 --prompt \h

提示符：

\D 完整日期

\d 当前数据库

\h 服务器名称

\u 当前用户

root用户名

localhost服务器名称

（none）当前数据库

以下是命令行的内容

localhost

localhostprompt mysql

PROMPT set to 'mysql'

mysqlPROMPT \u@\h \d

PROMPT set to '\u@\h \d'

root@localhost (none)USE test

Database changed

root@localhost test

USE test是还没有学过的命令

1.6

MySQL常用命令以及语法规范

SELECT VERSION();

显示当前服务器版本号

SELECT NOW();

显示当前时间

SELECT USER();

显示当前用户

MySQL语句的规范

关键字和函数名称全部大写

2数据库名称，表名称，字段名称全部小写

3SQL语句必须用分好结尾

1.7 操作数据库

系统自带四个数据库

创建数据库：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

大括号是必选项，中括号是可选项，|是从中做个选择，取出一个就行

localhost testSHOW DATABASE;

localhost testSHOW DATABASES;

查看当前服务器下的数据库列表

SHOW {DATABASES | SCHEMAS}

[LIKE 'pattern' | WHERE expr]

创建数据库

root@localhost testCREATE DATABASE IF NOT EXISTS t1;

Query OK, 1 row affected, 1 warning (0.00 sec)

root@localhost testSHOW WARNINGS;

root@localhost testSHOW CREATE DATABASE t1;

root@localhost testCREATE DATABASE IF NOT EXISTS t2;

这样t2就被创建了

修改数据库

ALTER {DATABASE | SCHEMA} [db_name]

[DEFAULT] CHARACTER SET [=] charset_name

控制栏：

root@localhost testALTER DATABASE t2 CHARACTER SET = gbk;

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

删除时候一定要保存数据库的存在。

控制栏：

root@localhost testDROP DATABASE t2

-> ;

Query OK, 0 rows affected (0.01 sec)

如果忘记写分号，可以在下一行继续补上分号就行。

root@localhost testSHOW DATABASES;

显示目前的数据库列表

2-1

内容回顾：

MySQL基础

安装MySQL

配置MySQL

使用MySQL

默认端口号：3306

超级用户名：root

创建数据库：CREATE DATABASE

修改数据库：ALTER DATABASE

删除数据库：DROP DATABASE

2-2 MySQL数据类型之整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT,

2-3 数据类型之浮点型

单精度浮点

FLOAT[(M,D)]

双精度DOUBLE[(M,D)]

M是数字总位数，D是小数点后面的位数，

2-4 数据类型之日期，时间

YEAR, TIME, DATE, DATETIME, TIMESTAMP,

2-5MySQL数据类型之字符型

CHAR(M), VARCHAR(M), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM('value', 'value2',....)

SET('value1', 'value2',...)

2-6

MySQL创建数据表

数据表是其他对象的基础，二维表格，行，列

行：记录，

列：字段

USE打开某个数据库

USE 数据库名称;

控制栏登陆数据库代码：

#ntsqk -yriit -p044500 -P3306 -h127.0.0.1

mysql -uroot -p004500 -P3306 -h127.0.0.1

USE test;

SELECT DATABASE();

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type, ...)

控制栏代码：

root@localhost testCREATE TABLE tb1(

-> username VARCHAR(20),

-> age TINYINT UNSIGNED,

-> salary FLOAT(8,2) UNSIGNED

-> );

Query OK, 0 rows affected (0.03 sec)

2-7 MySQL查看数据表

SHOW TABLES[FROM db_name] [LIKE 'pattern' | WHERE expr]

控制栏代码：

root@localhost testSHOW TABLES;

显示test用户里面的tables

root@localhost testSHOW TABLES FROM mysql;

显示mysql里面所有的数据表

root@localhost testSELECT DATABASE();

2-8

查看数据表结构

SHOW COLUMNS FROM tbl_name

控制栏内容：

2-9 MySQL记录的插入与查找

插入记录：

INSERT [INTO] tbl_name[ (col_name,...)] VALUESE(val,...)

列名字可以省略，但是要给所有列都赋值

代码：

root@localhost testINSERT tb1 VALUES('Tom', 25);

给某部分赋值

代码：

INSERT tb1(username,salary) VALUES('JOHN', 9666.68);

记录查找

SELECT expr,... FROM tbl_name

代码：

SELECT * FROM tb1;

2-10 MySQL空值与非空

添加相应属性

NULL, 字段值可以为空

NOT NULL, 字段值禁止为空

代码：

CREATE TABLE tb2(

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED NULL);

INSERT tb2 VALUES('tom',NULL);

SELECT * FROM tb2;

INSERT tb2 VALUES(NULL, 26);

2-11 MySQL自动编号

AUTO_INCREMENT

自动编号，必须和主键组合使用

默认情况，起始值是1，增量是1，增量为整数，如果是为浮点数，小数位数为零，

代码：

CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT, username VARCHAR(30) NOT NULL);

报错：

ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum

n and it must be defined as a key

必须定义一个主键为自动列

2-12 MySQL初涉主键约束

主键约束

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

代码：

CREATE TABLE tb3(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(30) NOT NULL);

root@localhost testSHOW COLUMNS FROM tb3;

INSERT tb3(username) VALUES('TOM');

INSERT tb3(username) VALUES('JOHN');

INSERT tb3(username) VALUES('ROSE');

INSERT tb3(username) VALUES('DIMITAR');

SELECT * FROM tb3;

代码：

CREATE TABLE tb4(id SMALLINT UNSIGNED PRIMARY KEY, username VARCHAR(20) NOT NULL);

SHOW COLUMNS FROM tb4;

INSERT tb4 VALUES(4,'TOM');

INSERT tb4 VALUES(22,'JOHN');

SELECT*FROM tb4;

2-13 MySQL初涉唯一约束

UNIQUE KEY

唯一约束

保证记录的唯一性

唯一约束的字段可以为空值NULL

每张数据表可以存在多个唯一约束

代码：

CREATE TABLE tb5(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE KEY, age tinyint UNSIGNED);

root@localhost testSHOW COLUMNS FROM tb5;

INSERT tb5(username, age) VALUES('Tom',22);

2-14

MySQL初涉默认约束

DEFAULT

默认值

当插入记录时候，如果没有明确为字段赋值，则自动赋予默认值

代码：

CREATE TABLE tb6(id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE KEY, sex ENUM('1','2','3') DEFAULT '3');

操作系统

代码：

SHOW COLUMNS FROM tb6;

INSERT tb6(username) VALUES('Tom');

SELECT * FROM tb6;

没有赋值时候，系统默认赋值3

2-15 总结

数据类型：字符型，整型，浮点型，日期时间型

SHOW TABLES;

SHOW COLUMNS;

查看数据表真实结构

数据表操作：INSERT插入记录，SELECT查找记录

记录操作：创建数据表，约束的使用

3-1 回顾和概述

数据表操作：创建，PRIMARY KEY(主键约束），UNIQUE KEY(唯一约束），DEFAULT(默认约束），NOT NULL(非空约束）

记录插入，查找

3-2 MySQL 外键约束的要求解析

约束：

保证数据的一致性和完整性

约束分为表级约束和列级约束

约束类型包括：

非空约束，主键约束，唯一约束，默认约束，外键约束FOREIGN KEY

外键约束：保证数据一致性，完整性，实现一对一或者一对多关系

外键约束的要求：

父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

2 数据表的存储引擎只能为InnoDB

3外键列和参照列必须具有相似的数据类型。数字的长度及是否有符号位必须相同；而字符长度可以不同。

4外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将会自动创建索引。

具有外键列的表为子表，子表所参照的表为父表

外键列：forray参照的列

编辑数据表的默认存储引擎

配置文件my.ini修改为：default-storage-engine =INNODB

登陆MySQL客户端：

mysql -uroot -p044500

出现mysql>说明成功登陆了

接着使用数据库

代码：

USE test;

接着创建数据表：

代码：

CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20) NOT NULL);

查看默认存储引擎：

代码：

SHOW CREATE TABLE provinces;

检查：外键列和参照列必须具有相似的数据类型。

代码：

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid BIGINT, FOREIGN KEY (pid) REFERENCES provinces (id) );

CREATE TABLE users1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE);

以上是错误代码，因为数据类型长度和符号都不同。

修改为：

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username, VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces (id) );

pid是省份编号，pname是省份名字

判断是否创建索引：

SHOW INDEXES FROM provinces;

SHOW COLUMNS FROM user;

3-3

外键约束的参照操作

3-3

代码：

CREATE TABLE users1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE);

mysql> SHOW CREATE TABLE users1;

CASCADE: 从父表删除或者更新且自动删除或者更新子表中匹配的行。

SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

RESTRICT:拒绝对父表的删除或者更新操作

NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同。

代码：

INSERT provinces(pname) VALUES('A');

INSERT provinces(pname) VALUES('B');

INSERT provinces(pname) VALUES('C');

SELECT * FROM provinces;

INSERT users1(username,pid) VALUES('Tom', 3);

INSERT users1(username,pid) VALUES('Tom',6);

INSERT users1(username,pid) VALUES('John',1);

INSERT users1(username,pid) VALUES('Rose',3);

SELECT * FROM users1;

DELETE FROM provinces WHERE id = 3;

3.3结束。

3-4 表级约束和列级约束

对一个数据列建立的约束称为列级约束，

对多个数据列建立的约束称为表级约束。

列级约束可以在列定义前后生命，表级约束只能在列定义后声明。

3-5

MySQL 修改数据表-添加/删除列

添加单列：

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

FIRST表示添加的列在最前面

AFTER col_name 表示添加的列在col_name后面

没有FIRST 或者AFTER 添加的列位于所有列的最下面

看一下users1数据表的结构

代码：

mysql> SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

+----------+----------------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

添加一列

代码：

mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;

Query OK, 1 row affected (0.05 sec)

Records: 1 Duplicates: 0 Warnings: 0

代码：

mysql> SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

+----------+----------------------+------+-----+---------+----------------+

4 rows in set (0.02 sec)

看来age确实被添加到了末尾，first可以添加到开头，after 可以指定添加到某行后面。

在username后面添加password：

mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

Query OK, 1 row affected (0.06 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

+----------+----------------------+------+-----+---------+----------------+

5 rows in set (0.04 sec)

在头部添加truename。用first

mysql> ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;

Query OK, 1 row affected (0.07 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users1;

+----------+----------------------+------+-----+---------+----------------+

+----------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| age | tinyint(3) unsigned | NO | | 10 | |

+----------+----------------------+------+-----+---------+----------------+

6 rows in set (0.04 sec)

添加多列只可以在列的最后依次添加，不可以随机添加。

删除列：

ALTER TABLE tbl_name DROP [COLUMN] col_name

删除一个

mysql> ALTER TABLE users1 DROP truename;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

一次删除两个列。

mysql> ALTER TABLE users1 DROP password, DROP age;

Query OK, 1 row affected (0.05 sec)

Records: 1 Duplicates: 0 Warnings: 0

3.5结束

3-6 MySQL 修改数据表--添加约束

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

创建一张废表users2

mysql> CREATE TABLE users2(

-> username VARCHAR(10) NOT NULL,

-> pid SMALLINT UNSIGNED);

Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE users2;

+--------+----------------------------------------------------------------------

---------------------------------------------------------------------+

| Table | Create Table

+--------+----------------------------------------------------------------------

---------------------------------------------------------------------+

| users2 | CREATE TABLE `users2` (

`username` varchar(10) NOT NULL,

`pid` smallint(5) unsigned DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+----------------------------------------------------------------------

---------------------------------------------------------------------+

1 row in set (0.00 sec)

这个表目前没有主键，下面添加主键。

mysql> ALTER TABLE users2 ADD id smallint unsigned;

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | | NULL | |

| id | smallint(5) unsigned | YES | | NULL | |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

把id设置成主键： PK是主键primary key的意思

mysql> ALTER TABLE users2 ADD CONSTRAINT PK_user2_id PRIMARY KEY(id);

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | | NULL | |

| id | smallint(5) unsigned | NO | PRI | 0 | |

+----------+----------------------+------+-----+---------+-------+

3 rows in set (0.02 sec)

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...)

假设为username添加唯一约束

代码：

mysql> ALTER TABLE users2 ADD UNIQUE (username);

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users2;

+--------+---------------------------------------------------------------

-------------------------------------------------------------------------

-------------------+

| Table | Create Table

+--------+---------------------------------------------------------------

-------------------------------------------------------------------------

-------------------+

| users2 | CREATE TABLE `users2` (

`username` varchar(10) NOT NULL,

`pid` smallint(5) unsigned DEFAULT NULL,

`id` smallint(5) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `username` (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+---------------------------------------------------------------

-------------------------------------------------------------------------

-------------------+

1 row in set (0.00 sec)

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

mysql> SHOW COLUMNS FROM provinces;

+-------+----------------------+------+-----+---------+----------------+

+-------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

+-------+----------------------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id)

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users2;

+--------+---------------------------------------------------------------

-------------------------------------------------------------------------

----------------------------------------+

| Table | Create Table

+--------+---------------------------------------------------------------

-------------------------------------------------------------------------

----------------------------------------+

| users2 | CREATE TABLE `users2` (

`username` varchar(10) NOT NULL,

`pid` smallint(5) unsigned DEFAULT NULL,

`id` smallint(5) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

UNIQUE KEY `username` (`username`),

KEY `pid` (`pid`),

CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+---------------------------------------------------------------

-------------------------------------------------------------------------

----------------------------------------+

1 row in set (0.00 sec)

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

给users2添加表age，

mysql> ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

显示所有的列

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| id | smallint(5) unsigned | NO | PRI | 0 | |

| age | tinyint(3) unsigned | NO | | NULL | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.05 sec)

改变users2表里面的age列为默认15，

mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

显示表users2里面的列

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| id | smallint(5) unsigned | NO | PRI | 0 | |

| age | tinyint(3) unsigned | NO | | 15 | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.02 sec)

删除表users2里面的age

mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| id | smallint(5) unsigned | NO | PRI | 0 | |

| age | tinyint(3) unsigned | NO | | NULL | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.03 sec)

3.6结束

3-7 MySQL 修改数据表--删除约束

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY

主键后面没有加名称，因为任何数据表有且仅有一个主键

mysql> ALTER TABLE users2 DROP PRIMARY KEY;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

查看数据表的结构：

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| id | smallint(5) unsigned | NO | | 0 | |

| age | tinyint(3) unsigned | NO | | NULL | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

确实没有了主键id

删除唯一约束：

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

mysql> SHOW INDEXES FROM users2;

+--------+------------+----------+--------------+-------------+-----------+-----

--------+----------+--------+------+------------+---------+---------------+

+--------+------------+----------+--------------+-------------+-----------+-----

--------+----------+--------+------+------------+---------+---------------+

| users2 | 0 | username | 1 | username | A |

| users2 | 1 | pid | 1 | pid | A |

+--------+------------+----------+--------------+-------------+-----------+-----

--------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

用网格的形式表现

mysql> SHOW INDEXES FROM users2\G;

*************************** 1. row ***************************

Table: users2

Non_unique: 0

Key_name: username

Seq_in_index: 1

Column_name: username

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: users2

Non_unique: 1

Key_name: pid

Seq_in_index: 1

Column_name: pid

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.00 sec)

ERROR:

No query specified

删除 username

mysql> ALTER TABLE users2 DROP INDEX username;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| pid | smallint(5) unsigned | YES | MUL | NULL | |

| id | smallint(5) unsigned | NO | | 0 | |

| age | tinyint(3) unsigned | NO | | NULL | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.02 sec)

显示索引，发现username确实不见了。

mysql> SHOW INDEXES FROM users2\G;

*************************** 1. row ***************************

Table: users2

Non_unique: 1

Key_name: pid

Seq_in_index: 1

Column_name: pid

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

ERROR:

No query specified

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

mysql> SHOW CREATE TABLE users2;

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

------------------+

| Table | Create Table

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

------------------+

| users2 | CREATE TABLE `users2` (

`username` varchar(10) NOT NULL,

`pid` smallint(5) unsigned DEFAULT NULL,

`id` smallint(5) unsigned NOT NULL DEFAULT '0',

`age` tinyint(3) unsigned NOT NULL,

KEY `pid` (`pid`),

CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

------------------+

1 row in set (0.00 sec)

users2_ibfk_1是上表已经告诉我们的内容，直接拿来用就行

删除外键约束：

mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users2;

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

------------------+

| Table | Create Table

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

------------------+

| users2 | CREATE TABLE `users2` (

`username` varchar(10) NOT NULL,

`pid` smallint(5) unsigned DEFAULT NULL,

`id` smallint(5) unsigned NOT NULL DEFAULT '0',

`age` tinyint(3) unsigned NOT NULL,

KEY `pid` (`pid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

------------------+

1 row in set (0.00 sec)

mysql> ALTER TABLE users2 DROP INDEX pid;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users2;

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

-----------------------------------------------------------------------------+

| Table | Create Table

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

-----------------------------------------------------------------------------+

| users2 | CREATE TABLE `users2` (

`username` varchar(10) NOT NULL,

`pid` smallint(5) unsigned DEFAULT NULL,

`id` smallint(5) unsigned NOT NULL DEFAULT '0',

`age` tinyint(3) unsigned NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+--------+----------------------------------------------------------------------

--------------------------------------------------------------------------------

-----------------------------------------------------------------------------+

1 row in set (0.00 sec)

3.7 结束

3-8 MySQL 修改数据表--修改列定义和更名数据表

修改列定义：

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

移动id到第一行：

mysql> ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

显示移动的效果：

mysql> SHOW COLUMNS FROM users2;

+----------+----------------------+------+-----+---------+-------+

+----------+----------------------+------+-----+---------+-------+

| id | smallint(5) unsigned | NO | | NULL | |

| pid | smallint(5) unsigned | YES | | NULL | |

| age | tinyint(3) unsigned | NO | | NULL | |

+----------+----------------------+------+-----+---------+-------+

4 rows in set (0.03 sec)

修改id的类型为TINYINT代码：

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]

pid名字改为p_id

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]...

修改users2名字为users3代码：

mysql> ALTER TABLE users2 RENAME users3;

Query OK, 0 rows affected (0.03 sec)

查看数据库test里面所有的table，发现users2确实不见了。

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| provinces |

| tb1 |

| tb2 |

| tb3 |

| tb4 |

| tb5 |

| tb6 |

| users1 |

| users3 |

+----------------+

9 rows in set (0.00 sec)

把users3又更名回来users2代码：

mysql> RENAME TABLE users3 TO users2;

注意：尽可能少使用数据列和数据表的更名。

3.8总结

约束：

按照功能划分为：

NOT NULL, PRIMARY KEY, UNIQUE KEY, DEFAULT, FOREIGN KEY

按照数据列的数目化为：

表级约束和列级约束

修改数据表：

针对字段的操作：添加，删除字段，修改列定义，修改列名称等。

针对约束的操作：添加删除各种约束

针对数据表的操作：数据表更名的两种方式

3-9 小结

4-1 回顾和概述

4-2 MySQL 插入记录INSERT

插入记录

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr|DEFAULT},...),(...),...

创建一个表users代码：

mysql> CREATE TABLE users(

-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

-> username VARCHAR(20) NOT NULL,

-> password VARCHAR(20) NOT NULL,

-> age TINYINT UNSIGNED NOT NULL DEFAULT 10,

-> sex BOOLEAN

-> );

Query OK, 0 rows affected (0.03 sec)

插入一个值代码：

mysql> INSERT users VALUES(NULL, 'Tom', '123', 25, 1);

显示刚才输入的内容，代码：

mysql> SELECT * FROM users;

+----+----------+----------+-----+------+

| id | username | password | age | sex |

+----+----------+----------+-----+------+

| 1 | Tom | 123 | 25 | 1 |

+----+----------+----------+-----+------+

1 row in set (0.00 sec)

mysql> INSERT users VALUES(NULL, 'John', '456', 25, 1);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT users VALUES(DEFAULT, 'John', '456', 25, 1);

Query OK, 1 row affected (0.01 sec)

可以用表达式：3*7+4

mysql> INSERT users VALUES(DEFAULT, 'John', '456', 3*7+4, 1);

Query OK, 1 row affected (0.01 sec)

默认值：

mysql> INSERT users VALUES(DEFAULT, 'John', '456', DEFAULT, 1);

该方法还可以一次性插入多条记录：

mysql> INSERT users VALUES(DEFAULT, 'John', '456', DEFAULT, 1),(NULL, 'John', '456', 25, 1);

4-3 MySQL 插入记录INSERT SET-SELECT

插入记录

INSERT [INTO] tbl_name SET col_name = {expr | DEFAULT}, ...

这种方法可以使用子查询(subquery)

由比较运算符引发的子查询。

这种方法一次只能插入一条记录，

mysql> INSERT users SET username = 'Ben', password = '456';

插入记录

INSERT [INTO] tbl_name [(col_name,...)] SELECT...

这种方法可以把查询结果插入到指定数据表中

4-4 MySQL 单表更新记录UPDATE

更新记录（单表更新）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}]...[where where_condition]

让表中所有年龄都加上5岁，代码：

mysql> UPDATE users set age = age + 5;

Query OK, 6 rows affected (0.01 sec)

Rows matched: 6 Changed: 6 Warnings: 0

更改所有年龄减去前面的序号，更改性别为1，代码：

mysql> UPDATE users SET age = age - id, sex = 0;

更新年龄加上10岁，当取余数为0才执行的代码：

mysql> UPDATE users SET age = age + 10 WHERE id % 2 =0;

4-5 MySQL 单表删除记录DELETE

删除记录：（单标删除）

DELETE FROM tbl_name [WHERE where_condition]

删除第六条记录代码：

mysql> DELETE FROM users WHERE id = 6;

插入一条记录，id号码到底为什么？代码：

mysql> INSERT users VALUES(null, '111','222',33,NULL);

Query OK, 1 row affected (0.01 sec)

发现代码号在原来的基础上加1了，和删除的号码没有啥关系了。

mysql> SELECT * FROM users;

+----+----------+----------+-----+------+

| id | username | password | age | sex |

+----+----------+----------+-----+------+

| 1 | Tom | 123 | 29 | 0 |

| 2 | John | 456 | 38 | 0 |

| 3 | John | 456 | 27 | 0 |

| 4 | John | 456 | 36 | 0 |

| 5 | John | 456 | 10 | 0 |

| 7 | 111 | 222 | 33 | NULL |

+----+----------+----------+-----+------+

6 rows in set (0.00 sec)

4-6 MySQL 查询表达式解析

SELECT语句占据了8成以上的使用率

查找记录：

SELECT select_expr [, select_expr ...]

[

FROM table_references

[WHERE where_condition]

[GROUP 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}]

]

mysql> SELECT VERSION();

+-----------+

| VERSION() |

+-----------+

| 5.5.59 |

+-----------+

1 row in set (0.00 sec)

mysql> SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2018-03-09 17:31:27 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT 3+ 6;

+------+

| 3+ 6 |

+------+

| 9 |

+------+

1 row in set (0.00 sec)

显示users的所有列：

mysql> SHOW COLUMNS FROM users;

显示users里面的id和username两个内容代码：

mysql> SELECT id, username FROM users;

变化顺序代码：

mysql> SELECT username, id FROM users;

查询表达式：

每一个表达式表示想要的一列，必须有至少一个。

多个列之间用逗号分隔。

星号表示所有列

查询表达式可以使用[AS] alias_name为其赋予别名。

别名可以用于GROUP BY, ORDER BY 或HAVING子句

修改名称代码：

mysql> SELECT id AS userId, username AS uname FROM users;

4-7 MySQL where语句进行条件查询

条件表达式

对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。

在WHERE表达式中，可以使用MySQL支持的函数和运算符。

4-8 MySQL group by语句对查询结果分组

[GROUP BY {col_name|position} [ASC|DESC],...]

代码：

mysql> SELECT sex FROM users GROUP BY sex;

4-9 having语句设置分组条件

分组条件：对某一部分记录做分组。

[HAVING where_condition]

选择年龄大于35的分组代码：

mysql> SELECT age FROM users GROUP BY age HAVING age > 35;

4-10 order by语句对查询结果排序

对查询结果进行排序

[ORDER BY { col_name | expr | position} [ASC|DESC],...]

按照id降序排列：DESCEND下降意思

mysql> SELECT * FROM users ORDER BY id DESC;

+----+----------+----------+-----+------+

| id | username | password | age | sex |

+----+----------+----------+-----+------+

| 7 | 111 | 222 | 33 | NULL |

| 5 | John | 456 | 10 | 0 |

| 4 | John | 456 | 36 | 0 |

| 3 | John | 456 | 27 | 0 |

| 2 | John | 456 | 38 | 0 |

| 1 | Tom | 123 | 29 | 0 |

+----+----------+----------+-----+------+

6 rows in set (0.00 sec)

默认按照年龄增长的方式代码：

mysql> SELECT * FROM users ORDER BY age;

4-11 limit语句限制查询数量

限制查询结果返回的数量

[LIMIT {[offset,] row_count |row_count OFFSET offset}]

指定返回三条数据

mysql> SELECT * FROM users LIMIT 3;

+----+----------+----------+-----+------+

| id | username | password | age | sex |

+----+----------+----------+-----+------+

| 1 | Tom | 123 | 29 | 0 |

| 2 | John | 456 | 38 | 0 |

| 3 | John | 456 | 27 | 0 |

+----+----------+----------+-----+------+

3 rows in set (0.00 sec)

从第三条记录开始显示2条：

mysql> SELECT * FROM users LIMIT 3,2;

+----+----------+----------+-----+------+

| id | username | password | age | sex |

+----+----------+----------+-----+------+

| 4 | John | 456 | 36 | 0 |

| 5 | John | 456 | 10 | 0 |

+----+----------+----------+-----+------+

2 rows in set (0.00 sec)

mysql> CREATE TABLE test(

-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

-> username VARCHAR(20)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM test;

Empty set (0.00 sec)

把旧表的内容插入新表

mysql> INSERT test(username) SELECT username FROM users WHERE age >=30;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

下表展示了添加的test表。test也是一个数据库呢。

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| provinces |

| tb1 |

| tb2 |

| tb3 |

| tb4 |

| tb5 |

| tb6 |

| test |

| users |

| users1 |

| users2 |

+----------------+

11 rows in set (0.00 sec)

4-12 总结

数据记录的

增删改查

INSERT

UPDATE

DELETE

SELECT

5-1 数据准备

退出旧的数据表代码：

mysql> \q

Bye

mysql -uroot -p044500 imooc;

mysql> CREATE DATABASE imooc;

Query OK, 1 row affected (0.01 sec)

mysql> USE imooc;

Database changed

mysql> CREATE TABLE IF NOT EXISTS tdb_goods(

-> goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

-> goods_name VARCHAR(150) NOT NULL,

-> goods_cate VARCHAR(40) NOT NULL,

-> brand_name VARCHAR(40) NOT NULL,

-> goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,

-> is_show BOOLEAN NOT NULL DEFAULT 1,

-> is_saleoff BOOLEAN NOT NULL DEFAULT 0

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is

_saleoff) VALUES('R510VC 15.6?????','???','??','3399',DEFAULT,DEFAULT);

Query OK, 1 row affected (0.01 sec)

mysql>

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,i

s_saleoff) VALUES('Y400N 14.0???????','???','??','4899',DEFAULT,DEFAULT);

Query OK, 1 row affected (0.00 sec)

mysql> SHOW TABLES;

+-----------------+

| Tables_in_imooc |

+-----------------+

| tdb_goods |

+-----------------+

1 row in set (0.00 sec)

错误代码：

mysql> SELECT * FROM imooc;

ERROR 1146 (42S02): Table 'imooc.imooc' doesn't exist

正确代码：

mysql> SELECT * FROM tdb_goods;

错误代码：

mysql> SHOW tdb_goods;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near 'tdb_g

oods' at line 1

数据乱码，因为最开始用utf8的编码方式

在客户端用gbk的编码方式显示我们的数据，但是并不会影响数据表数据编码方式。

SET NAMES gbk;

查看当前数据库编码：

mysql> use xxx

mysql> show variables like 'character_set_database';

修改xxx数据库的编码，把它修改为你要的编码 gbk (注意不是utf-8)。或许你的是gbk gb2312 。

mysql> alter database imooc CHARACTER SET gbk;

删除数据库

DROP DATABASE imooc;

5-2 MySQL 子查询简介.

子查询subquery

出现在其他sql语句内的select子句。

子查询指嵌套在查询内部，且必须始终出现在圆括号内。

子查询可以包含多个关键字或者条件。如DISTINCT, GROUP BY, ORDER BY, LIMIT函数等。

子查询的外层查询可以使SELECT, INSERT, UPDATE, SET , DO

子查询返回值：标量，一行，一列，或者子查询

5-3 由比较运算符引发的子查询

使用比较运算符的子查询=，>,<,>=等等

语法结构：

operand comparison_operator subquery

求价格平均值

select avg(goods_price) from tdb_goods;

进行四舍五入，保留小数点后面两位。代码：

select round(avg(goods_price),2) from tdb_goods;

avg是求平均值的函数

求大于平均值的产品价格代码

select goods_id, goods_name, goods_price from tdb_goods where goods_price >= 5636.36;

求大于平均值的产品价格代码

select goods_id, goods_name, goods_price from tdb_goods where goods_price >= (select round(avg(goods_price),2) from tdb_goods);

以上代码其实就是子查询了。把小代码放在括号里。

查找超极本的价格代码：

select goods_price from tdb_goods where goods_cate ='超极本';

有哪些超极本代码：

select * from tdb_goods where goods_cate = '超极本'\G;

查询哪些商品价格大于超极本代码：

select goods_id, goods_name, goods_price from tdb_goods where goods_price >(select goods_price from tdb_goods where goods_cate ='超极本');

以上代码错误，因为>后面有三个结果。

策略，用any, some, all修饰的比较运算符

select goods_id, goods_name, goods_price from tdb_goods where goods_price >any (select goods_price from tdb_goods where goods_cate ='超极本');

这里使用了any,意识是大于任何一个数字，也就是大于最小值就行。some也是大于最小值，all必须大于所有值，也就是最大值。

5-4 由[NOT] IN/EXISTS引发的子查询

5-5

使用INSERT...SELECT插入记录

再做一张新的数据表存储重复的品牌和分类，为了降低存储容量，加快查询数据。

新建数据表代码：

create table if not exists tdb_goods_cates(cate_id smallint unsigned primary key auto_increment, cate_name varchar(40) not null);

快速查找重复部分代码：

select goods_cate from tdb_goods group by goods_cate;

把查询结果写入数据表中insert...select

insert [into] tbl_name[(col_name,...)] select...

证明数据表还是空的代码：

select * from tdb_goods_cates;

insert tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;

数据已经写入，但是里面的数据没有使用外键，也就是在旧的数据表里面还是汉字，应该用新的数据表里面的数字代码来代替重复汉字。

也就是下节课的多表更新

5-6 多表更新

update table_references set col_name1={expr1|default} [, col_name2={expr2|default}]... [where where_condition]

语法结构：

table_reference

{[inner|cross join|{left|right} [outer] join}

table_reference on conditional_expr

连接类型

inner, outer, left, right

代码：

update tdb_goods inner join tdb_goods_cates on goods_cate = cate_name set goods_cate = cate_id;

5-7 多表更新之一步到位

1创建表

2记录写入新创建的表

3 更新旧表

把以上1,2步骤融合为一个操作更简便。

叫做多表更新：create ...select

create table [if not exists] tbl_name [(create_definition,...)]

查找有几个品牌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;

更新商品表代码：

update tdb_goods inner join tdb_goods_brands on brand_name = brand_name set brand_name = brand_id;

以上代码又问题，因为两个表的brand_name名字一样，唯一的办法就是两个表的名字不同。

或者用别名

update tdb_goods as g inner join tdb_goods_brands as b on g.brand_name = b.brand_name set g.brand_name = brand_id;

5-8 连接的语法结构

数据表参照

table_reference

5-9

内连接INNER JOIN

join, cross join, inner join是等价的。

使用on设定连接条件，用where进行结果过滤。

5-10

外连接OUTER JOIN

左外连接是显示左表的全部和右表符合连接条件的记录。

右外连接：显示右表的全部记录及其左表符合连接条件的记录。

5-11多表连接

5-12关于连接的几点说明

外连接：

5-13

无限级分类表设计：商品种类下面可以进行无限分类，例如图书下面可以分太多。

自身连接：同一个数据表对其自身进行连接。

代码：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;

select * from tdb_goods_types;

5-14多表删除

delete tbl_name

6-1 课程回顾

6-2 字符函数

concat()连接字符

select concat('imooc', 'mysql');

使用指定的分隔符进行字符连接。

select concat_ws('|','a','b','c');

a|b|c

select concat_ws('-','a','b','c');

a-b-c

得到小写字符lower()

大写字符upper()

获取左侧

select left('mysql',2);

select upper(left('mysql',2));

求字符长度length()

删除左侧空格ltrim()

删除右侧空格rtrim()

删除左右两侧空格trim()

替换

select replace('???my???sql','?','!');

截取,从第一位截取2个

select substring('mysql',1,2);

从第二位截取到末尾

select substring('mysql',2);

模式匹配like

%代表任意字符

_代表任意一个字符

6-3

MySQL 数值运算符和函数

进1取整，向上取整

select ceil(3.01);返回4

select 3/4;返回0.75000

select 3 div 4;返回0；

floor()舍1取整

mod()取余数

power()幂运算

round()四舍五入

truncate()数字截取

6-4 比较运算符和函数

between...and...在范围内

select 15 between 1 and 22;

select 35 not between 1 and 22;

select 10 in(5,10,15,20);

在之间。在集合里面。

is null。。。为空

select * from test where first_name is null;

6-5 日期时间函数

now()返回当前日期时间

select now();

curdate();

curtime();

日期变化

date_add();

select date_add('2014-3-12', interval 365 day);

select date_add('2014-3-12', interval -365 day);

添加三周

select date_add('2014-3-12', interval 3 week);

日期格式化

select date_format('2014-3-12', '%m/%d/%Y');

03/12/2014

6-6 MySQL 信息函数

连接ID

select connection_id();

当前数据库

select database();

最后插入记录

6-7 聚合函数

聚合函数只有一个返回值

avg()求平均值。

select avg(goods_price) as avg_price from tdb_goods;

6-8 MySQL 加密函数

信息摘要算法

md5()

select md5('admin');

password('admin');

7-1 课程回顾

7-2 自定义函数简介

user-defined function UDF是一种MySQL扩展的途径，用法和内置函数相同

两个必要条件：参数和返回值

创造自定义函数

create function function_name

关于函数体：

函数体

7-3 MySQL 创建不带参数的自定义函数

select now();

select date_format(now(), '%Y年%m月%d日 %H点:%i分:%s秒');

create function f1() returns varchar(30) return date_format(now(), '%Y年%m月%d日 %H点:%i分:%s秒');

select f1();就代替上面的代码了

7-4 创建带有参数的自定义函数

create function f2(num1 smallint unsigned, num2 smallint unsigned) returns float (10,2) unsigned return (num1 + num2)/2;

调用下,用来求平均值：

select f2(12, 15);

7-5 MySQL 创建具有复合结构函数体的自定义函数

create function adduser(username varchar(20)) return int unsigned return insert test(username) values(username);

修改分隔符，现在都用//来表示语句结束

delimiter//

create function adduser(username varchar(20)) return int unsigned return begin insert test(username) values(username)； last_insert_id(); end//

select addusers('rose');//

8-1 课程回顾

自定义函数，对MySQL扩展的一种途径。

8-2 存储过程简介

sql语句和控制语句的预编译集合，用一个名称存储并且作为一个单元处理。

优点：

增强功能和灵活性

实现快速执行速度

减少网络流量

8-3 MySQL 存储过程语法结构解析

in参数表示参数的值必须在调用存储过程中指定

out表示值可以被修改

inout表示

comment注释

过程体：

8-4 MySQL 创建不带参数的存储过程

create procedure spl() select version();

call spl;

call spl();

8-5

带有in类型参数的存储过程

delete from table where

8-6

MySQL 创建带有IN和OUT类型参数的存储过程

8-7MySQL 创建带有多个OUT类型参数的存储过程

8-8 存储过程和自定义函数的区别

9-1 课程回顾

9-2 MySQL 存储引擎简介

查看数据表的创建命令

show create table test;

存储引擎有：

myISAM,INNODB,MEMORY, CSV, Archive

9-3 MySQL 相关知识点之并发处理

并发控制：当多个连接对记录进行修改时候保证数据的一致性和完整性。

解决方法：锁

共享锁，排它锁。

共享锁（读锁），同一时间，多个用户可以读取同一个资源。

排他锁（写锁）：任何一个时候只能有一个用户可以写入资源。

锁的颗粒

表锁：是一种开销最小的锁策略

行锁：是一种开销最大的锁的策略。

9-4 MySQL 相关知识点之事务处理

9-5 MySQL 相关知识点之外键和索引

外键：保证数据一致性的策略

索引：对数据表中的一列或者多列进行排序的一种结构。

9-6 各个存储引擎特点

9-7 MySQL 设置存储引擎

修改存储引擎方法：

修改配置文件，

创建数据表命令实现。

create table tp1( s1 varchar(10) )engine = MyIsam;

show create table tp1;

alter table tp1 engine = InnoDB;

10-1 课程回顾

10-2

MySQL图形管理工具之phpMyAdmin

打开网址，下载对应软件，下载解压，在浏览器里面输入127.0.0.1/PHPMyAdmin/

然后输入账号root,密码：044500

10-3

MySQL图形管理工具之Navicat for MySQL

10-4

MySQL图形管理工具之MySQL Workbench

认识你是我们的缘分，同学，等等，学习人工智能，记得关注我。

微信扫一扫
关注该公众号

《湾区人工智能》