文档章节

MySQL学习笔记一

Aaron_DMC
 Aaron_DMC
发布于 2016/12/16 18:06
字数 5763
阅读 33
收藏 2

MySQL目录结构

这里写图片描述

配置my.ini

MySQL5.7的my.ini位于\ProgramData\MySQL\MySQL Server 5.7目录下(可能有的版本的my.ini就在安装目录下),该该目录下还有一个data目录存放我们的创建的数据库。 
打开my.ini文件[client]用于配置客户端,主要就是3306端口。[mysqld]配置的是服务端。 

这里写图片描述

特别要注意一点:编码方式是utf8而不是utf-8。 
Linux系统中该配置文件在/etc/mysql/my.cnf。

 

启动、关闭重启MySQL

service mysql start|stop|restart

 

登录与退出MySQL

root@ubuntu:/etc/mysql# mysql -v    # 查看mysql版本       
mysql  Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (i686) using readline 6.2
root@ubuntu:/etc/mysql# mysql -uroot -p123456 -P3306 -h192.168.132.128 # -u用户名 -p密码(为了安全可以不写,会提示输入) -P端口 -h主机名
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 330
Server version: 5.5.35-0ubuntu0.12.04.2 (Ubuntu)

mysql> \q|exit|quit # 退出mysql

 

修改mysql提示符

1、连接mysql时通过--prompt参数指定 

这里写图片描述

2、连接上客户端的时候通过prompt命令指定 

这里写图片描述

MySQL提示符如下表:

参数 描述
\D 完整日期
\d 当前数据库
\h 服务器名
\u 当前用户

以上的提示符还能连用,例如:

mysql> prompt \u@\h \d> # 用户名@主机名 数据库名
PROMPT set to '\u@\h \d>'
root@localhost demo>

 

MySQL常用命令

SET NAMES gbk; -- 客户端以GBK的编码形式呈现数据
mysql> SELECT VERSION();    # 显示mysql版本,相当于登录数据库之前输入mysql -V
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.5.35-0ubuntu0.12.04.2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT USER();       #   显示当前用户
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();        # 显示系统时间
+---------------------+
| NOW()               |
+---------------------+
| 2015-04-05 21:02:31 |
+---------------------+
1 row in set (0.00 sec)

mysql>                      # 在Linux的mysql中也可以使用Ctrl+L清屏

 

MySQL语句规范

  • 关键字和函数名称全部大写;
  • 数据库名、表名、字段名称全部小写;
  • SQL语句必须以分号结束。

 

操作数据库

创建数据库

-- 说明:花括号表示必选项,竖线表示多选一,方括号表示可选
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] character_name

mysql> CREATE DATABASE t1;
Query OK, 1 row affected (0.00 sec)

mysql> 

我们可以使用source命令运行sql脚本。登录mysql数据库。

source 数据库脚本名

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

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]

正确安装mysql后有4个数据库: 

这里写图片描述

这里写图片描述

mysql> SHOW CREATE DATABASE t1; # 查看创建数据库使用的命令
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| t1       | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET GBK; # 使用gbk编码方式创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE t2;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| t2       | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] character_name

这里写图片描述

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

 

数据类型和操作数据表

数据类型决定了数据的存储格式,代表了不同的信息类型。

1.整型 

这里写图片描述

2.浮点型 (项目开发中以float居多 )

这里写图片描述

3.日期型 

这里写图片描述

项目开发中该类型用得并不多,因为涉及到时区的问题,多用数字表示。 
4.字符型 

这里写图片描述

VARCHAR,TEXT,DATE,TIME,ENUM等类型的数据也需要单引号修饰,而INT,FLOAT,DOUBLE等则不需要。 
使用某个数据库

mysql> USE test;            # 使用数据库
Database changed
mysql> SELECT DATABASE();   #查看当前使用的数据库
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

创建表

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

--创建用户表tb_user
CREATE TABLE tb_user(
    username VARCHAR(20),       -- 【用户名】
    age TINYINT UNSIGNED,       -- 【年龄】     无符号微整数(0~255)
    salary FLOAT(8,2) UNSIGNED  -- 【工资】     一共有8位,小数部分有2位
);

查看表

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


mysql> CREATE TABLE tb_user(            # 创建表
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;                     # 查看表
+----------------+
| Tables_in_test |
+----------------+
| tb_user        |
+----------------+
1 row in set (0.00 sec)

查看表结构

SHOW COLUMNS FROM tb_name # 或者
DESC tb_name

这里写图片描述

重命名表

RENAME TABLE 原名 TO 新名;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;

插入记录

INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...)


mysql> INSERT tb_user VALUES('Tom',22,3658.32); # 当我们省略字段名的时候所有的字段都要赋值!
Query OK, 1 row affected (0.03 sec)

mysql> INSERT tb_user VALUES('Tom',22);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> 
mysql> INSERT tb_user(username,age) VALUES('Kitty',18); # 为指定字段赋值
Query OK, 1 row affected (0.03 sec)

记录的查询

SELECT expr,... FROM tb_name

mysql> SELECT * FROM tb_user; # 这里的*表示的是字段的过滤
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| Tom      |   22 | 3658.32 |
| Kitty    |   18 |    NULL |
+----------+------+---------+
2 rows in set (0.00 sec)

空值与非空 

NULL允许空值(默认),NOT NULL禁止非空。例如: 

这里写图片描述

字段的自动编号 
AUTO_INCREMENT该字段要么是整数,要么小数位数为0,且必须和主键组合使用,默认情况下初始值为1,增量为1.——保证记录的唯一性。 
主键约束(PRIMARY KEY): 
每张表只能有一个主键,能够保证记录的唯一性,主键自动为NOT NULL,并自动创建索引。 
主键我们可以写成KEY或者PRIMARY KEY。 

这里写图片描述

这里写图片描述

 

唯一约束——UNIQUE KEY

  • 唯一约束也可以保证记录的唯一性
  • 唯一约束的字段可以为NULL
  • 一张表可以有多个唯一约束
mysql> CREATE TABLE tb_user(
    ->id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    ->username VARCHAR(20) NOT NULL UNIQUE KEY, age TINYINT UNSIGNED 
    ->);
Query OK, 0 rows affected (0.08 sec)

mysql> DESC tb_user;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> 

这里写图片描述

默认约束-DEFAULT 
当插入记录时,如果没有明确为字段赋值,则自动赋予默认值。

mysql> CREATE TABLE tb_user(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 主键约束,自动编号
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,        -- 唯一约束
    -> sex ENUM('1','2','3') DEFAULT '3'                -- 性别是枚举值,默认是3(保密)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> DESC tb_user;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT tb_user(username) VALUES('Tom');              # 没有指定性别时,默认是3
Query OK, 1 row affected (0.08 sec)

mysql> INSERT tb_user(username,sex) VALUES('Kitty','2');    # 指定性别
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM tb_user;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Tom      | 3    |
|  3 | Kitty    | 2    |
+----+----------+------+
2 rows in set (0.00 sec) 

 

约束和修改表

外键约束和唯一约束

  1. 约束保证了数据的完整性一致性
  2. 约束分为表级约束和列级约束。
  3. 约束类型包括 
    • NOT NULL(非空约束)
    • PRIMARY KEY(主键约束)
    • UNIQUE KEY(唯一约束)
    • DEFAULT(默认约束)
    • FOREIGN KEY(外键约束)

外键约束:保证数据的完整性、一致性,实现表的一对一或者一对多关系。

外键约束的要求

  1. 父表(子表所参照的表)和子表(具有外键列的表)使用相同的存储引擎,禁止使用临时表。
  2. 表的存储引擎只能是InnoDB。
  3. 外键列(加过FOREIGN关键字的列)和参照列(外键列所参照的列)必须具有相似的数据类型。其中数字的长度或者是否有符号位必须相同;而字符的长度可以不同。
  4. 外键列和参照列必须建立索引。如果外键列不存在索引,MySQL将自动创建索引。

编辑表的存储引擎 
MySQL配置文件default-storage-engine=INNODB 

这里写图片描述

现在我们要创建一张users表,users表中有省份,只需要在user表中保存省份id就行了,这样两张表就联系起来了!目前的子表就是users 

这里写图片描述

以上的命令中我们并没有显式创建索引,但是有了外键约束,MySQL会自动创建索引。 

这里写图片描述

这里写图片描述

外键约束的参照操作

  1. CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。
  2. SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该项,必须保证子表列没有指定NOT NULL
  3. RESTRICT:拒绝对父表删除或更新操作。
  4. NO ACTION:标准SQL关键字,在MYSQL中与RESTRICT相同。

重新建立users表:

# 建表
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE -- 在父表中删除或者更新记录,同时更新子表中的相应行
    -> );
Query OK, 0 rows affected (0.01 sec)

# 向父表(省份表)中插入记录
mysql> INSERT provinces(pname) VALUES('A');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT provinces(pname) VALUES('B');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
+----+-------+
3 rows in set (0.07 sec)
mysql> 

这里写图片描述

删除父表(province表中的记录后) 

这里写图片描述

在实际的开发中我们很少使用物理的外键约束,因为它只支持InnoDB这一种引擎。所谓逻辑的外键约束指的是我们在定义2张表的时候确定两张表的关系而不去使用FOREIGN KEY这个关键字。

表级约束和列级约束

  • 对一个数据列建立的约束,称为列级约束。
  • 对多个数据列建立的约束,称为表级约束。
  • 列级约束既可以在列定义时声明,也可以在列定义后声明。
  • 表级约束只能在列定义后声明。
  • 在实际的开发中经常使用的是列级约束。

修改数据表

增加列

-- 添加单列
ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

-- 添加多列,不能指定列的位置关系
ALTER TABLE tb_name ADD [COLUMN] (col_name col_definition,...)

这里写图片描述

这里写图片描述

删除列

-- 删除列
 ALTER TABLE tb_name DROP [COLUMN] col_name

删除列的同时可以新增列,同理新增列的同时可以删除列,操作之间以逗号分隔。

添加约束

-- 添加主键约束
ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...)

-- 添加唯一约束
ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

-- 添加外键约束
ALTER TABLE tb_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] [index_type] (index_col_name,...) reference_definiton

-- 添加/删除默认约束
ALTER TABLE tb_name ALTER [CONSTRAINT] col_name {SET DEFAULT literal|DROP DEFAULT}

例如:为users表的id字段添加主键: 

这里写图片描述

这里写图片描述

为username字段添加唯一约束: 

这里写图片描述

为users表添加外键:

这里写图片描述

这里写图片描述

为age字段添加默认值: 

这里写图片描述

删除age字段的默认值: 

这里写图片描述

删除约束

-- 删除主键约束
ALTER TABLE tb_name DROP PRIMARY KEY

-- 删除唯一约束(首先要知道索引的名字SHOW INDEXES FROM tb_name\G命令)
ALTER TABLE tb_name DROP {INDEX|KEY} inedx_name

-- 删除外键约束(需要知道外键约束的名称:SHOW CREATE TABLE tb_name命令)
ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol

例: 
删除users表的id字段的主键约束: 

这里写图片描述

删除users表的username字段的唯一约束: 

这里写图片描述

这里写图片描述

删除users表的pid字段的外键约束: 

这里写图片描述

这里写图片描述

这里写图片描述

这里写图片描述

修改列定义和列名称

-- 修改列定义(由大类型改为小类型的时候可能会丢失数据)
ALTER TABLE tb_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

-- 修改列名称
ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

例: 
将users表的id字段变为第一列: 

这里写图片描述

将users表的pid字段的名称和类型同时修改: 

这里写图片描述

修改表名

-- 方法一
ALTER TABLE tb_name RENAME [TO|AS] new_tb_name

-- 方法二,可以为多张数据表更名
RENAME TABLE tb_name TO new_tb_name [,tb_name2 TO new_tb_name2]...

例: 
将users表更名为user: 

这里写图片描述

这里写图片描述

在实际的开发中应该尽量避免数据列和表的更名——可能导致某些视图或者存储过程无法工作!

记录的操作

插入记录——INSERT

-- 如果省略了列名,表示所有的字段都要赋值;插入语句可以一次性插入多条记录
INSERT [INTO] tb_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...


--首先创建一张user表:
CREATE TABLE user(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(32) NOT NULL,
    age TINYINT UNSIGNED NOT NULL DEFAULT 10,
    sex BOOLEAN
);


--向user表中插入记录:
INSERT user VALUES(NULL,'A','111',11,1);            -- id字段为了保持自增可以使用NULL或者DEFAULT
INSERT user VALUE(DEFAULT,'B','222',31,2);
INSERT user(username,password) VALUES ('C','333');  -- 指定插入的字段
INSERT user VALUES(8,'D','444',36,2);               -- 人为指定id,该id必须不存在,后面记录的编号以此为基准
INSERT user VALUES(NULL,'G','555',DEFAULT,2);       -- 保持age字段的默认值

-- 同时插入多条记录(中间用逗号分隔),列可以是函数或者表达式,md5()是MySQL内置函数
INSERT user VALUES(NULL,'E',md5('666'),3*5-6,2),(NULL,'F','777',18,2); 

SELECT * FROM user;                                 -- 运行结果

+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  11 |    1 |
|  2 | B        | 222                              |  31 |    2 |
|  3 | C        | 333                              |  10 | NULL |
|  8 | D        | 444                              |  36 |    2 |
|  9 | G        | 555                              |  10 |    2 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |   9 |    2 |
| 11 | F        | 777                              |  18 |    2 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

INSERT SET-SELECT

-- INSERT SET方式。可以使用子查询(SubQuery),只能一次插入一条记录;用得较少
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},...

-- INSERT SELECT方式(将查询结果写入到表)
INSERT [INTO] tb_name [(col_name,...)] SELECT ...

--使用INSERT-SET 方式向表中插入记录:
INSERT user SET username='G',password=md5('888');

SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  11 |    1 |
|  2 | B        | 222                              |  31 |    2 |
|  3 | C        | 333                              |  10 | NULL |
|  8 | D        | 444                              |  36 |    2 |
|  9 | G        | 555                              |  10 |    2 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |   9 |    2 |
| 11 | F        | 777                              |  18 |    2 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

有子查询的插入操作 
1.建立一张test表:

CREATE TABLE test(
    id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
    username VARCHAR(20)
);

--user表和test表分别如下:
mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  15 |    0 |
|  3 | C        | 333                              |  12 |    0 |
|  8 | D        | 444                              |  43 |    1 |
|  9 | G        | 555                              |   6 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    1 |
| 11 | F        | 777                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    1 |
+----+----------+----------------------------------+-----+------+

--现在需要将user表中年龄大于12的用户插入到test表:
mysql> INSERT test(username) SELECT username FROM user WHERE age > 12;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | A        |
|  2 | D        |
|  3 | E        |
|  4 | G        |
+----+----------+
4 rows in set (0.00 sec)

单表记录更新——UPDATE

-- 如果省略了WHERE条件所有的记录将全部更新!
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}] ... [WHERE where_condition]




SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  11 |    1 |
|  2 | B        | 222                              |  31 |    2 |
|  3 | C        | 333                              |  10 | NULL |
|  8 | D        | 444                              |  36 |    2 |
|  9 | G        | 555                              |  10 |    2 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |   9 |    2 |
| 11 | F        | 777                              |  18 |    2 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
UPDATE user SET age = age + 5;                      -- 让表中的年龄字段都在各自的基础上加5
Query OK, 8 rows affected (0.11 sec)
Rows matched: 8  Changed: 8  Warnings: 0

SELECT * FROM user; 
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  16 |    1 |
|  2 | B        | 222                              |  36 |    2 |
|  3 | C        | 333                              |  15 | NULL |
|  8 | D        | 444                              |  41 |    2 |
|  9 | G        | 555                              |  15 |    2 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    2 |
| 11 | F        | 777                              |  23 |    2 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.01 sec)

UPDATE user SET age = age - id,sex = 0;             -- 将年龄字段改为各自的年龄减去id,性别都变成0
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 0

SELECT * FROM user; 
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  15 |    0 |
|  2 | B        | 222                              |  34 |    0 |
|  3 | C        | 333                              |  12 |    0 |
|  8 | D        | 444                              |  33 |    0 |
|  9 | G        | 555                              |   6 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |   4 |    0 |
| 11 | F        | 777                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |   3 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

UPDATE user SET age = age + 10 WHERE id % 2 = 0;    -- 让所有的id为偶数的用户年龄在原来的基础上加10(注意是一个等号)
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

SELECT * FROM user; 
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  15 |    0 |
|  2 | B        | 222                              |  44 |    0 |
|  3 | C        | 333                              |  12 |    0 |
|  8 | D        | 444                              |  43 |    0 |
|  9 | G        | 555                              |   6 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    0 |
| 11 | F        | 777                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

 

单表删除记录

-- 注意:如果省略WHERE条件将删除数据表中的全部记录!
DELETE FROM tb_name [WHERE where_condition]


DELETE FROM user WHERE id = 2;  -- 删除id为2的记录,删除之后该id不会重用
DELETE FROM user;               -- 删除全表记录


 

查询表达式解析

--
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 {[offest,] row_count | row_count OFFEST offest}]
]

--SELECT语句最基本要有SELECT关键字和查询表达式,例如:
mysql> SELECT 9.9 + 1.1;
+-----------+
| 9.9 + 1.1 |
+-----------+
|      11.0 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-04-08 18:56:56 |
+---------------------+
1 row in set (0.09 sec)

mysql> 

查询表达式

  • 每一个表达式表示想要的一列,至少要有一个。
  • 多个列之间以逗号分隔。
  • 星号(*)表示所有列。tb_name.*表示命名表的所有列。
  • 查询表达式可以使用[AS] alias_name为其赋予别名。
  • 别名可以用于GROUP BY,ORDER BY或者HAVING子句。

例:

mysql> SHOW COLUMNS FROM user;
+----------+----------------------+------+-----+---------+----------------+
| 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.00 sec)

mysql> SELECT password,username FROM user;          -- 查询表达式的列的顺序决定结果集的顺序
mysql> SELECT user.password,user.username FROM user;-- 该语句与上面的等效,使用多表连接的时候可能两张表的字段相同
+----------------------------------+----------+
| password                         | username |
+----------------------------------+----------+
| 111                              | A        |
| 333                              | C        |
| 444                              | D        |
| 555                              | G        |
| fae0b27c451c728867a567e8c1bb4e53 | E        |
| 777                              | F        |
| 0a113ef6b61820daa5611c870ed8d5ee | G        |
+----------------------------------+----------+
7 rows in set (0.00 sec)

mysql> SELECT id AS user_id,username AS user_name FROM user;-- 使用别名(影响结果集的列名)
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | A         |
|       3 | C         |
|       8 | D         |
|       9 | G         |
|      10 | E         |
|      11 | F         |
|      12 | G         |
+---------+-----------+
7 rows in set (0.00 sec)

mysql> SELECT id username FROM user; -- 别名的使用可以省略AS关键字,下面的username就作为了id的别名
+----------+
| username |                         -- 强烈建议使用别名的时候加上AS关键字,上面的语句很容易让人抽风
+----------+
|        1 |
|        3 |
|        8 |
|        9 |
|       10 |
|       11 |
|       12 |
+----------+
7 rows in set (0.00 sec)

使用WHERE语句进行条件查询

  • WHERE语句的作用是对记录进行过滤,如果没有指定WHERE子句,则显示所有记录。
  • 在WHERE表达式中,可以使用MySQL支持的函数或者运算符。

使用GROUP BY对结果集进行分组

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

--例
mysql> SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  15 |    0 |
|  3 | C        | 333                              |  12 |    0 |
|  8 | D        | 444                              |  43 |    1 |
|  9 | G        | 555                              |   6 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    1 |
| 11 | F        | 777                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT sex FROM user GROUP BY sex;   -- 结果集按照性别分组
+------+
| sex  |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.13 sec)

使用HAVING语句设置分组条件

注意:HAVING子句后面要么是聚合函数,要么HAVING后面的字段出现在查找字段内。

mysql> SELECT * FROM user;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  15 |    0 |
|  3 | C        | 333                              |  12 |    0 |
|  8 | D        | 444                              |  43 |    1 |
|  9 | G        | 555                              |   6 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    1 |
| 11 | F        | 777                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT sex FROM user GROUP BY sex HAVING count(id) > 3; -- HAVING后面跟的是聚合函数
+------+
| sex  |
+------+
|    0 |
+------+
1 row in set (0.10 sec)

mysql> SELECT sex,age FROM user GROUP BY sex HAVING age > 15; -- HAVING后面出现的字段在查询字段中
+------+-----+
| sex  | age |
+------+-----+
|    1 |  43 |
+------+-----+
1 row in set (0.00 sec)

使用ORDER BY对结果集进行排序

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

--例
mysql> SELECT * FROM user; -- 默认是按照id升序排列
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 111                              |  15 |    0 |
|  3 | C        | 333                              |  12 |    0 |
|  8 | D        | 444                              |  43 |    1 |
|  9 | G        | 555                              |   6 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    1 |
| 11 | F        | 777                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM user ORDER BY id DESC; -- 按照id降序排列
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    1 |
| 11 | F        | 777                              |  12 |    0 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    1 |
|  9 | G        | 555                              |   6 |    0 |
|  8 | D        | 444                              |  43 |    1 |
|  3 | C        | 333                              |  12 |    0 |
|  1 | A        | 111                              |  15 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM user ORDER BY age ASC,id DESC; -- 按照年龄升序、id降序。权重年龄大于id
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  9 | G        | 555                              |   6 |    0 |
| 11 | F        | 777                              |  12 |    0 |
|  3 | C        | 333                              |  12 |    0 |
| 12 | G        | 0a113ef6b61820daa5611c870ed8d5ee |  13 |    1 |
| 10 | E        | fae0b27c451c728867a567e8c1bb4e53 |  14 |    1 |
|  1 | A        | 111                              |  15 |    0 |
|  8 | D        | 444                              |  43 |    1 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

使用LIMIT语句限制结果集的数量

-- OFFSET常用于PHP中的分页(当前页面-1)* 每页显示的记录数
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

子查询与连接

数据准备

--  创建商品表
  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
  );

-- 向商品表中插入22条记录
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- 省略

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
  • 子查询必须嵌套在循环内部,其必须出现在圆括号内。
  • 子查询可以包含多个关键字或者条件,如:DISTINCT、GROUP BY、ORDER BY、LIMIT,函数等。
  • 子查询的外层查询可以是:SELECT 、INSERT、 UPDATE 、SET、 或者DO。【这里的查询并不是指查找,是所有SQL语句的统称】。

子查询的返回值: 
子查询可以返回标量、一行、一列或者子查询。

使用比较运算符的子查询

= ,>,>= , < ,<=,<> !=,<=>,常用ANY,SOME,ALL来修饰,语法:

operand comparison_operator subquery

operand comparision_operator ANY (subquery)
operand comparision_operator SOME (subquery)
operand comparision_operator ALL (subquery)

这里写图片描述

例:

mysql> SELECT AVG(goods_price) FROM tdb_goods; -- 查询所有商品价格的平均值
+------------------+
| AVG(goods_price) |
+------------------+
|     5636.3636364 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods; -- 查询商品价格平均值四舍五入保留2位小数
+---------------------------+
| ROUND(AVG(goods_price),2) |
+---------------------------+
|                   5636.36 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods -- 查看商品价格大于等于平均价格的商品id,名称和价格
    -> WHERE goods_price >= ( SELECT ROUND(AVG(goods_price),2) FROM tdb_goods ); -- 子查询
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|        3 | G150TH 15.6英寸游戏本                   |    8499.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
|       18 |  HMZ-T3W 头戴显示设备                   |    6999.000 |
|       20 | X3250 M4机架式服务器 2583i14            |    6888.000 |
|       21 |  HMZ-T3W 头戴显示设备                   |    6999.000 |
+----------+-----------------------------------------+-------------+
7 rows in set (0.00 sec)

mysql> SELECT goods_id,goods_price FROM tdb_goods  -- 查询商品价格大于【所有】超极本的商品的id,价格
    -> WHERE goods_price > ALL ( SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本' );
+----------+-------------+
| goods_id | goods_price |
+----------+-------------+
|        3 |    8499.000 |
|       13 |    9188.000 |
|       17 |   28888.000 |
+----------+-------------+
3 rows in set (0.00 sec)

mysql>

使用[NOT]IN的子查询

语法

operand comparison_operator [NOT] IN (subquery)
  • = ANY 运算符与IN等效
  • != ALL 或者<>ALL和NOT IN等效

使用[NOT]EXISTS的子查询

如果子查询返回任何行,EXISTS将返回true;否则返回false。

使用INSERT-SELECT插入记录和多表更新

-- 插入记录
INSERT [INTO] tb_name[(col_name, ... )] SELECT ...

-- 多表更新
UPDATE table_references SET col_name1 = {expr1|DEFAULT} [,col_name2 = {expr2|DEFAULT}] [WHERE where_condition]  

例: 
查看以上的tdb_goods表我们发现有很多的重复,例如: 

这里写图片描述

为此,我们需要另外创建一张商品分类表tdb_goods_cates

-- 商品分类表
CREATE TABLE IF NOT EXISTS tdb_goods_cates(
    cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    cate_name VARCHAR(40)

);

我们需要向上面的表中插入记录。

INSERT tdb_goods_cates(cate_name)  -- 将分组信息写入商品分类表
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

表的参照关系——表的连接

table_reference {[INNER|CROSS] JOIN {LEFT|RIGHT} [OUTER] JOIN} table_reference ON condition_expr
  • INNER JOIN,内连接。在MYSQL中,JOIN,CROSS JOIN和INNER JOIN等价。
  • LEFT [OUTER] JOIN,左外连接。
  • RIGHT [OUTER] JOIN,右外连接 。
UPDATE tdb_goods INNER JOIN tdb_goods_cates
    ON goods_cate = cate_name
    SET goods_cate = cate_id; -- 参照tdb_goods_cates表更新tdb_goods的记录

修改表

修改列(表结构的修改)

# 增加列
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;
或者
ALTER TABLE 表名 ADD 列名 数据类型 约束;

# 删除列
ALTER TABLE 表名 DROP COLUMN 列名;
或
ALTER TABLE 表名 DROP 列名;

# 对原有的列进行修改
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

# 修改某一列的数据类型
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

新增加的列默认是在最后,如果想要在第一列加入列,请参考以下的写法:

ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

 

索引

 

 

© 著作权归作者所有

共有 人打赏支持
Aaron_DMC
粉丝 8
博文 65
码字总数 73707
作品 0
上海
后端工程师
私信 提问
MySQL入门很简单-学习笔记 - 索引页

索引一下,方便阅读,后面有完整的PDF下载地址 MySQL入门很简单-学习笔记 - 第1 章 数据库概述 MySQL入门很简单-学习笔记 - 第 2 章 Windows平台下安装与配置MySQL MySQL入门很简单-学习笔...

晨曦之光
2012/03/09
0
0
读《PHP和MySQL Web开发》

【第一部分】PHP基础与入门 1、PHP学习笔记1:基础知识快速浏览 http://my.oschina.net/bluefly/blog/472673 2、PHP学习笔记2:文件 http://my.oschina.net/bluefly/blog/477601 3、PHP学习笔...

slyso
2015/07/14
0
0
《PHP和MySQL Web 开发》 第9章 创建Web数据库 (9.8-9.10)

LCL WARNING 这是我学习《PHP和MySQL Web 开发》的读书笔记,一些重要的知识点我会记录下来,当然只会写我觉得重要的。 如果有幸有人看到这个学习笔记了,你要结合着书看,不要光看这个笔记。...

十万猛虎下画山
07/26
0
0
MySQL入门教程系列-1.5 如何学习MySQL

在这里持续更新 MySQL入门教程系列-1.5 如何学习MySQL 如何学习 MySQL 这是一个伪命题,每个人都有适合自己的一套学习方法,各

同一种调调
2016/09/28
15
0
【原创】proftp学习笔记-配置全功略

献给CU的新年礼物哦 :mrgreen: 国内关于proftp设置的文档实在太少,只能看着官方文档慢慢学习,特把学习经验分享给大家,祝大家新年快乐:) 原文地址:http://www.5ilinux.com/blog/archive...

zt371
2009/05/06
302
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周二乱弹 —— 哥们之间报恩的想法被上帝实现了

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 小小编辑:推荐歌曲《消愁》 《消愁》- 毛不易 手机党少年们想听歌,请使劲儿戳(这里) @过遥 :周一的早上就应该用来补觉,太困了 周末不想...

小小编辑
38分钟前
48
5
MariaDB 服务器在 MySQL Workbench 备份数据的时候出错如何解决

服务器是运行在 MariaDB 10.2 上面的,在使用 MySQL Workbench 出现错误: mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"'......

honeymose
今天
3
0
apache顶级项目(二) - B~C

apache顶级项目(二) - B~C https://www.apache.org/ Bahir Apache Bahir provides extensions to multiple distributed analytic platforms, extending their reach with a diversity of s......

晨猫
今天
7
0
day152-2018-11-19-英语流利阅读

“超级食物”竟然是营销噱头? Daniel 2018-11-19 1.今日导读 近几年来,超级食物 superfoods 开始逐渐走红。不难发现,越来越多的轻食餐厅也在不断推出以超级食物为主打食材的健康料理,像是...

飞鱼说编程
今天
22
1
SpringBoot源码:启动过程分析(二)

接着上篇继续分析 SpringBoot 的启动过程。 SpringBoot的版本为:2.1.0 release,最新版本。 一.时序图 一样的,我们先把时序图贴上来,方便理解: 二.源码分析 回顾一下,前面我们分析到了下...

Jacktanger
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部