我爱编程

MySQL入门

2018-06-20  本文已影响0人  stoneyang94

第一章：初涉MySQL

数据库

定义

数据库，可以简单的解释为：高效的存储和处理数据的介质（主要分为磁盘和内存两种）。

分类

根据数据库存储介质的不同，可以将其分为两类，即：关系型数据库（SQL）和非关系型数据库（NoSQL，Not Only SQL）。

举例

关系型数据库：

区别

关系型数据库：

MySQL概述

MySQL是一个开源的关系型数据库管理系统，由瑞典MySQL AB公司开发，后被Oracle收购，主要分为社区版和企业版。
MySQL由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

MySQL目录结构

启动与停止MySQL服务

利用cmd开启和关闭MySQL服务

  1. 用管理员的身份打开cmd（必须是管理员是身份打开，否则会报错）
  2. 启动Mysql服务
net start mysql
  1. 停止Mysql服务
net stop mysql

Mysql的常用参数

语法 含义
-p, --password[=name] 密码
-P, --port=# 端口号
-h, --host=name 服务器名称
-D, --database=name 打开指定数据库
--delimiter = name 指定分隔符
--prompt=name 设置提示符
-V,--version 输出版本信息并退出（必须是大写V）
-u, --user=name 用户名

登录与退出

登陆

mysql -uroot -pmima -P3306 -h127.0.0.1

-u后面是用户名
-p后面是密码
-P后面是端口号
-h后面是服务器名称，127.0.0.1是本地服务器
如果没有修改端口号，而且是本地服务器，则可以省略-P3306 -h127.0.0.1这两个参数
在-p后面直接输入密码会显示在当前窗口的标题栏中，这样是不安全的，我们可以直接输入mysql -uroot -p
后回车，就可以在密码框中输入密码了。

退出

主要有三种退出方式：

mysql  >  exit;

mysql  >  quit;

mysql > \q;

MySQL常用命令--SELECT

显示当前服务器版本

SELECT VERSION();

显示当前日期时间

SELECT NOW();

显示当前用户

SELECT USER();
SELECT USER();

MySQL语句规范

操作数据库

创建数据库--CREATE

创建数据库的语法结构：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 
[DEFAULT] CHARACTER SET [=] charset_name

{}是必选项，[]是可选项，|是做选择项
比如创建一个名为test1的数据库：

CREATE DATABASE test1;

查看数据库列表--SHOW

查看当前服务器下的数据库列表语法结构：

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

比如，查看上面创建的test1数据库是否成功

SHOW DATABASES;
SHOW DATABASES

修改数据库--ALTER

修改数据库的语法结构：

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

比如将刚刚创建的test2的编码方式修改为utf8

ALTER DATABASE test2 CHARACTER SET utf8;

删除数据库--DROP

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

比如删除我们上面创建的test2数据库

 DROP  DATABASE test2;

忽略错误产生

CREATE DATABASE test1;

会提示ERROR 1007 (HY000): Can't create database 'test1'; database exists错误

CREATE DATABASE IF NOT EXISTS test1;

SHOW WARNINGS;

编码信息--SHOW CREATE DATABASE

SHOW CREATE DATABASE 数据库名字;
SHOW CREATE DATABASE 
CREATE DATABASE test2 CHARACTER SET 编码方式;

第二章：数据类型与操作数据表

数据类型

整型 浮点型 日期时间型 字符型

创建数据表---CREATE

数据表TABLE

数据表又称表，是数据库最重要的组成部分之一，是其他对象的基础
行称之为记录，列称之为字段

创建数据表

创建数据表

  1. 打开数据库--USE
USE 数据库名;
  1. 检查打开的数据库是否正确--SELECT
SELECT DATABASE();
  1. 创建数据表的语法结构--CREATE
CREATE TABLE [IF NOT EXISTS] table_name (
column_name  data_type,   ……）

column_name是列名称，数据表有多少列需要根据项目分析事先规划好
data_types是数据类型, 逗号是两个字段之间的分隔符，最后一个字段不用加逗号

举例

我们创建一个简单是数据表，包含以下几项信息：

用户名：username ，用户名一般是字符型

年龄：age ，年龄一般是整型，另外年龄不可能是负值，所以常设置无符号位

工资：salary，工资一般设置为浮点型

mysql> CREATE TABLE tb1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );

查看数据表

查看数据表列表--SHOW TABLES

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

SHOW TABLES FROM mysql;

查看数据表结构--SHOW COLUMNS...FROM

SHOW COLUMNS FROM tbl_name

比如查看名为tb1这个列的数据表结构

SHOW COLUMNS FROM tb1;
SHOW COLUMNS

记录的插入与查找

插入记录--INSERT 表(哪几列) VALUES() 

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

tbl_name：表名字。 col_name：列名字。 VALUES：值

INSERT tb1 VALUES('datiangou',24,4000);

INSERT tb1(username,salary) VALUES('cimutongzi',14000);

记录查找---SELECT 列 FROM 表

记录查找语句

SELECT expr,... FROM tbl_name

比如：（这里的*是字段的过滤）

SELECT * FROM tb1;

显示如下：


SELECT * FROM

空值与非空

  1. 我们首先创建一个字段，设置username 为NOT NULL 
mysql> CREATE TABLE tb2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL
    -> );
  1. 然后查看这个字段的结构，看到username 为NOT NULL
  2. 我们插入一条数据，尝试使得该数据username为NULL：
mysql> INSERT tb2 VALUES(NULL,26);
  1. ERROR 1048 (23000): Column 'username' cannot be null。可以看到报错提示不允许username为NULL
不允许插入NULL到NULL值处

自动编号 AUTO_INCREMENT

所以该字段数据类型一定是数值型：整数，浮点数都可以，浮点数小数位数必须为0

举例：

mysql> 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 column and it must be defined as a key

初涉约束

主键约束

本节参考MySQL 为什么需要一个主键

主键原则

几个好习惯

主键其他作用

设置主键--PRIMARY KEY

我们将上节中的id定义成主键

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

Query OK, 0 rows affected (0.01 sec)
可以看到，创建成功了，主键可以写PRIMARY KEY,也可以直接写KEY

现在再来看一下数据表结构：

设置主键

可以看到，id不能为空，因为主键自动为NOT NULL，Key被定义成了PRI，Extra被定义成了自动编号，则写入记录的时候id不需要专门赋值了，我们插入三条记录：

测试主键

然后查看一下记录：

主键自增

可以看到id被设计AUTO_INCREMENT 的主键，被自动编号成了123

唯一约束

唯一约束原则

创建唯一约束-- UNIQUE KEY

我们创建一个既有主键约束，又有唯一约束的数据表：

mysql> CREATE TABLE tb5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(30) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED
    -> );

查看一下数据表的结构：

加入唯一约束

可以看到，id为主键约束，username为唯一约束，我们写入一些记录：

测试唯一约束

可以发现username不能重复

默认约束--DEFAULT

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

我们创建一个数据表：

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

查看一下数据表结构：

加入默认值

可以看到sex字段的默认值为3

我们插入一条记录，仅设置username，不设置sex

mysql> INSERT tb6(username) VALUES('jiutuntongzi');

然后查看一下记录：

测试默认约束

发现即使我们没有设置 jiutuntongzi 的性别，但是其sex默认被赋值成了3(DEFAULT 值)

第三章：约束以及修改数据表

约束意义和分类

外键约束-- FOREIGN KEY...REFERENCES

外键是表中的一列，其值必须在另一表的主键中。即一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

目的

要求

表级约束与列级约束

定义

使用情况

修改数据表

添加/删除列

添加列--ALTER TABLE...ADD

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

ALTER TABLE tbl_name ADD [COLUMN]   (col_name column_definition,...)
例子

用之前的tb6做例子：

插入列之前的tb6 
mysql> ALTER TABLE tb6 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18;

结果

新列插在列尾 
mysql> ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;

结果

在指定列后添加新列

删除列--ALTER TABLE...DROP

语法

删除列的语法结构：

ALTER TABLE tbl_name DROP [COLUMN] col_name
例子

删除刚才给tb6添加的age字段

mysql> ALTER TABLE tb6 DROP age;

结果

删除列

添加/删除约束

添加主键约束--ALTER TABLE...ADD PRIMARY KEY

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

创建一个测试用的数据表user2：

mysql> CREATE TABLE user2(
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED
    -> );

首先增加一列：

mysql> AlTER TABLE user2 ADD id SMALLINT UNSIGNED;

此时表的结构：

添加主键前

给id列添加主键约束：

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

此时表的结构：

添加id为主键

添加唯一约束--ALTER TABLE...ADD UNIQUE

语法
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type]
(index_col_nam,...)
例子

唯一约束可以有多个，我们给usersname添加唯一约束：

mysql> ALTER TABLE user2 ADD UNIQUE (username);

此时表的结构：

插入唯一约束

添加外键约束-ALTER TABLE..ADD FOREIGN KEY REFERENCES..

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

将users2中的pid参照tb6中id，则需要给users2中的pid设置外键约束：

mysql> ALTER TABLE user2 ADD FOREIGN KEY(pid) REFERENCES tb6(id);

此时表的结构：

添加外键约束

添加/删除默认约束-ALTER TABLE..ALTER..SET|DROP DEFAULT

语法

添加/删除默认约束的语法结构：

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

在user2表中添加一个age字段：

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

此时表的结构：

添加默认约束前

可以看到age字段并没有默认值，接下来添加默认约束：

mysql> ALTER TABLE user2 ALTER age SET DEFAULT 18;

此时表的结构：

添加age默认约束值18

接下来删除默认约束

mysql> ALTER TABLE user2 ALTER age DROP DEFAULT;

此时表的结构：

删除age的默认约束

修改列定义,列名称，表名称

修改列定义--ALTER TABLE...MODIFY...

语法
ALTER TABLE tbl_name MODIFY [COLUMN] col_name
column_definition  [FIRST| AFTER col_name]
例子

比如users2表中的id并没有处于第一位置，虽然没有什么影响，但是不符合我们平常的习惯，可以将id字段修改为第一位置：

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

可以看到id：

修改了id列的定义

修改数据类型时，将大类型修改为小类型，有可能造成数据丢失。

修改列名称--ALTER TABLE...CHANGE...

语法
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name
new_col_name column_definition  [FIRST| AFTER col_name]
例子

我们修改一下pid的数据类型和名字：

mysql> ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

可以看到pid已经被修改为new_pid：

修改列名字

修改数据表名称--...RENAME TO...

尽量不要随意修改数据表名。

语法

修改数据表名称方法一：

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

修改数据表名称方法二（可以修改多个数据表）：

RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2]...
例子

我们用方法一将user2名字修改为new_user2：

mysql> ALTER TABLE users2 RENAME users3;

查看表名：

修改表名

再用方法二将tb1,tb2分别改为new_tb1,new_tb2：

mysql> RENAME TABLE users3 TO users2;

查看表名：

修改表名

第四章：操作数据表中的记录

本章内容在前几章有部分体现，这里做一个补充与总结。

插入记录--INSERT...VALUES...

语法

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

例子

首先创建一个用于测试的数据表：

mysql> CREATE TABLE users(
    -> 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
    -> );

现在我们省略col_name，则插入记录的时候需要依次为所有字段赋值，自动编号的id字段可以NULL或者DEFAULT

mysql> INSERT users VALUES(NULL,'datiangou','123456',18,1);
mysql> INSERT users VALUES(DEFAULT,'cimutongzi','123456',24,1);

查看表中记录：

插入记录

接下来一次性写入多条记录，只需要用逗号分隔即可，这里以两条记录为例：

mysql> INSERT users VALUES(DEFAULT,'yuzaoqian','098765',25,1),
(NULL,'xuetongzi','098765',DEFAULT,0);

查看表中记录：

image

## 插入记录--INSERT SET-SELECT

单表更新记录--UPDATE...SET...

语法

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

expr1 | DEFAULT 是指表达式或者默认值

例子

mysql> UPDATE users set age=age+5;

查看表中记录：
可以看到，表中每一条记录的age值都被更新了。

不限定范围更新数据 
mysql> UPDATE users SER age = age + 10 WHERE id % 2 == 0 ;

查看表中记录：
可以看到，表中id为偶数记录的age值都被更新了。

特定范围更新数据

单表删除记录--DELETE FROM...

语法

DELETE FROM tbl_name [WHERE where_condition]

例子

比如删除id为2的用户记录：

mysql> DELETE FROM users WHERE id = 2;

查看表中记录：

删除记录

可以看到id为2的用户已经被删除，如果此时再插入一条记录，id会为5，而不是2：

mysql> INSERT users VALUES(NULL,'huiyeji','1234',12,NULL);

查看表中记录：

删除之后，主键不会“自动填充”

查询表达式解析--SELECT...FROM...

语法

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 id, username FROM users;

查看表中记录：

查询指定列 
mysql> SELECT id AS userID,username AS uname FROM users;

查看表中记录：

别名

条件查询--WHERE

区间查询---BETWEEN..AND..

区间查询

语句对查询结果分组--GRUOP BY

语法

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

例子

将表中的数据按字段 sex 进行分组，并进行统计：

mysql>SELECT sex,count(age),max(age),min(age),avg(age),sum(age) FROM users GROUP BY sex;

查看表中记录：

GROUP BY

count()函数里面可以使用两种参数，分别为

语句设置分组条件--HAVING

语法

[HAVING where_condition]

例子

比如对年龄大于20岁的用于进行性别分组：

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

查看表中记录：

image

语句对查询结果排序--ORDER BY

语法

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

例子

  1. 按照年龄默认排序：
mysql> SELECT * FROM users ORDER BY age;
  1. 插入一条同age数据，当有同龄的用户，我们可以设置如果同龄，则按照id降序排列：
mysql> INSERT users VALUES (DEFAULT,'shanfeng','123456',23,1);
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 2,2;

查看表中记录：

限制查询数量

参考文章
慕课网：《与MySQL的零距离接触》笔记目录
MySQL 为什么需要一个主键
慕课网与MySQL的零距离接触

上一篇 下一篇

猜你喜欢

热点阅读