Mysql基础

Mysql基础
1、基本命令
登录数据库
mysql -h host_name -u user_name -ppassword
h:当连接MySQL服务器不在同台主机时，填写主机名或IP地址
u:登录MySQL的用户名
p:登录MySQL的密码
mysql程序常用命令
选定默认数据库：use dbname
显示所有数据库：show databases
显示默认数据库中所有表：show tables
放弃正在输入的命令：\c
显示命令清单：\h
退出mysql程序：\q
查看MySQL服务器状态信息：\s
2、通过CREATE DATABASE语句创建基本数据库
通过CREATE DATABASE语句创建一个名称为db_admin的数据库
create database db_admin;
3、创建数据库前判断是否存在同名数据库
在MySQL中，不允许同一系统中存在两个相同名称的数据库，如果要创建的数据库名称已经存在，那么系统将给出以下错误信息：
ERROR 1007 (HY000): Can’t create database ‘db_test’; database exists
create database if not exists db_test1
4、查看数据库
成功创建数据库后，可以使用SHOW命令查看MySQL服务器中的所有数据库信息。
语法如下：
SHOW {DATABASES|SCHEMAS}
[LIKE ‘模式’ WHERE 条件];
5、选择数据库
在MySQL中，使用CREATE DATABASE语句创建数据库后，该数据库并不会自动成为当前数据库。如果想让它成为当前数据库，需要使用MySQL提供的USE语句来实现，USE语句可以实现选择一个数据库，使其成为当前数据库。只有使用USE语句指定某个数据库为当前数据后，才能对该数据库及其存储的数据对象执行操作。
USE语句的语法格式如下：
USE 数据库名;
6、删除数据库
在MySQL中，可以通过使用DROP DATABASE语句或者DROPSCHEMA语句来删除已经存在的数据库。使用该命令删除数据库的同时，该数据库中的表，以及表中的数据也将永久删除，因此，在使用该语句删除数据库时，一定要小心，以免误删除有用的数据库。
DROP DATABASE或者DROP SCHEMA语句的语法格式如下：
DROP {DATABASE|SCHEMA} [IF EXISTS] 数据库名;
如果指定的数据库不存在，可以在DROP DATABASE语句中使用IF EXISTS从句来保证只有当数据库存在时才执行删除数据库的操作。
drop database if exists tb_admin;
7、MySQL数据库中，每一条数据都有其数据类型。MySQL支持的数据类型，主要分成3类：数字类型、字符串（字符）类型、日期和时间类型
数字类型
MySQL支持所有的ANSI/ISO SQL 92数字类型。这些类型包括准确数字的数据类型（NUMERIC、DECIMAL、INTEGER和SMALLINT），还包括近似数字的数据类型（FLOAT、REAL和DOUBLE PRECISION）。其中的关键词INT是INTEGER的同义词，关键词DEC是DECIMAL的同义词。
字符串类型
字 符 串 类 型 可 以 分 为 3 类 ： 普 通 的 文 本 字 符 串 类 型 （ C H A R 和VARCHAR）、可变类型（TEXT和BLOB）和特殊类型（SET和ENUM）。它们之间都有一定的区别，取值的范围不同，应用的地方也不同。
日期和时间数据类型
日期和时间类型包括：DATETIME、DATE、TIMESTAMP、TIME和YEAR。其中的每种类型都有其取值的范围，如赋予它一个不合法的值，将会被“0”代替。
8、创建数据表
创建数据表使用CREATE TABLE语句。语法如下：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名
[(create_definition,…)][table_options] [select_statement]
9、使用SHOW COLUMNS语句查看
在MySQL中，使用SHOW COLUMNS语句可以查看表结构，SHOW COLUMNS语句的基本语法格式如下：
SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
或
SHOW [FULL] COLUMNS FROM 数据库名.数据表名;
10、使用DESCRIBE语句查看
在MySQL中，还可以使用DESCRIBE语句查看数据表结构。DESCRIBE语句的基本语法格式如下：
DESCRIBE 数据表名;
其中，DESCRIBE可以简写成DESC。在查看表结构时，也可以只列出某一列的信息。其语法格式如下：
DESCRIBE 数据表名 列名;
11、添加新字段及修改字段定义
在MySQL的ALTER TABLE语句中，可以通过使用ADD[COLUMN]create_definition [FIRST | AFTER column_name ]子句来添加新字段；使用MODIFY [COLUMN] create_definition子句可以修改已定义字段的定义。
12、修改字段名
在MySQL的ALTER TABLE语句中，使用CHANGE [COLUMN]old_col_name create_definition子句可以修改字段名或者字段类型。
13、删除字段
在MySQL的ALTER TABLE中，使用DROP [COLUMN]col_name子句可以删除指定字段。
14、修改表名
在MySQL的ALTER TABLE中，使用RENAME [AS] new_tbl_name子
句可以修改表名。
15、重命名表
在MySQL中，重命名数据表可以使用RENAME TABLE语句来实现。
RENAME TABLE语句的基本语法格式如下：
RENAME TABLE 数据表名1 To 数据表名2
16、复制表
创建表的CREATE TABLE命令还有另外一种语法结构，在一张已经存在的数据表的基础上创建一份该表的拷贝，也就是复制表。
这种用法的语法格式如下：
CREATE TABLE [IF NOT EXISTS] 数据表名
{LIKE 源数据表名 | (LIKE 源数据表名)}
17、删除表
删除数据表的操作很简单，同删除数据库的操作类似，使用DROP TABLE语句即可实现。
DROP TABLE语句的基本语法格式如下：
DROP TABLE [IF EXISTS] 数据表名;
18、插入数据
在建立一个空的数据库和数据表时，首先需要考虑的是如何向数据表中添加数据，该操作可以使用INSERT语句来完成。使用INSERT语句可以向一个已有数据表插一个新行，也就插入一行新记录。在MySQL中，INSERT语句有3种语法格式，分别是INSERT… VALUES语句、INSERT… SET语句和INSERT… SELECT语句。
使用INSERT… VALUES语句插入数据使用INSERT… VALUES语句插入数据，是INSERT语句的最常用的语法格式。它的语法格式如下：
insert into 表名[(字段名1，字段名2,…)] values(值1,值2,…);
使用INSERT… VALUES语句插入数据
USE db_admin;
插入一条完整数据
INSERT INTO tb_user VALUES(1,‘zhangsan’,‘123456’,now());
SELECT * FROM tb_user;
插入数据记录的一部分
INSERT INTO tb_user (username,password) VALUES(‘rjkflm’,‘111’);
SELECT * FROM tb_user;
插入多条数据
INSERT INTO tb_user (username,password,createtime)
VALUES(‘mrbccd’,‘111’, ‘2019-09-05 10:35:26’)
,( ‘mingri’,‘111’, ‘2019-09-05 10:45:27’)
,( ‘mingrisoft’,‘111’, ‘2019-09-05 10:55:28’);
SELECT * FROM tb_user;
使用INSERT… SET语句插入数据
在MySQL中，除了使用INSERT… VALUES语句可以插入数据外，还可以使用INSERT… SET语句插入数据。这种语法格式用于通过直接给表中的某些字段指定对应的值来实现插入指定数据，对于未指定值的字段将采用默认值进行添加。
INSERT… SET语句的语法格式如下：
INSERT INTO 数据表名
SET 字段名1=值,字段名2=值2,… INSERT INTO tb_user
SET username =‘mrbccd’,password=‘111’;
插入查询结果
在 M y S Q L 中 ， 支 持 将 查 询 结 果 插 入 到 指 定 的 数 据 表 中 ，这 可 以 通 过INSERT…SELECT语句来实现。
INSERT [INTO] 数据表1 [(字段名,…)]
SELECT 列名1，列名2… FROM 数据表2；
19、创建数据表时给字段设置默认值
在字段名和类型后面，加上：default 默认值
20、创建数据表时加入约束
constraint
什么是约束？
实际上是对表中数据的限制条件
设计表时加入约束的目的？
保证表中数据的完整和有效
1、非空约束（not null）
作用：not null约束的字段不能为 NULL 值，必须赋予具体数据
2、唯一性约束（unique）
作用：unique约束的字段具有唯一性，不可重复
注意：unique约束的字段不能重复，但是可以为NULL，NULL不是一个值，也不能用等号比较
3、组合使用 not null 和 unique
被 not null 和 unique 约束的字段，该字段即不能为null 也不能重复。
4、主键约束PK（primary key）
添加主键primary key的字段即不能重复也不能为空，效果与“not null unique”相同，但本质是不同的，添加主键约束之后，主键不仅会有“not null
unique”作用，而且主键字段还会自动添加“索引 — index”
主键根据个数分类：单一主键、复合主键
5、MySQL中自动生成主键值（MySQL特有）
MySQL数据库管理系统中提供了一个自增数字auto_increment，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户生成，MySQL会自动生成。自增数字默认从1开始，以1递增：1、2、3、4、…
6、外键约束FK（foreign key）
外键是相对于主键说的，是建立表之间的联系的必须的前提
外键根据个数分为：单一外键、复合外键
外键可以使得两张表关联，保证数据的一致性和实现一些级联操作
例：设计数据库表用来存储学生和班级信息
学生表t_student包含：sno,sname,classno,cname学生信息和班级信息之间的关系：一个班级对应多个学生，典型的一对多关系
第一种解决方案：将学生信息和班级信息存储到同一张表中
学生信息表：t_student
sno（PK）sname classno cname
1 Jack 100 北京市101中学高三1班
2 Lucy 100 北京市101中学高三1班
3 Lily 100 北京市101中学高三1班
4 Ford 200 北京市101中学高三2班
5 allen 200 北京市101中学高三2班
6 Jason 300 北京市101中学高三3班
7 Teddy 300 北京市101中学高三3班
以上设计的缺点：数据冗余
第二种解决方案：将学生信息和班级信息分开两张表存储：学生表 + 班级表
第一步：创建t_student表和t_class表
学生表 t_student
Sno sname
1 Jack
2 Lucy
3 Lily
4 Ford
5 Allen
6 Jason
7 Teddy
班级表t_class
cno cname
100 北京市101中学高三1班
200 北京市101中学高三2班
300 北京市101中学高三3班
第二步：t_student表要不t_class表有关系，需要在t_student表中添加一个classno字段做为外键
学生表 t_student
sno（PK） sname classno（FK）
1 Jack 100
2 Lucy 100
3 Lily 100
4 Ford 200
5 Allen 200
6 Jason 300
7 Teddy 300
班级表 t_class
cno（PK） cname
100 北京市101中学高三1班
200 北京市101中学高三2班
300 北京市101中学高三3班
结论（classno值必须来自cno）：为了保证 t_student 表中的 classno 字段中的数据必须来自 t_class表中的 cno 字段中数据，有必要给 t_student 表中classno 字段添加外键约束；classno 称为外键字段，classno 中的100、200、300称为外键值，classno在这里是单一外键；
注意以下三点：
1、外键字段可以为NULL，外键为空的数据也叫孤儿数据；
2、被引用字段必须具有unique约束；
3、有了外键引用之后，表分为父表和子表，以上父表：班级表 t_class； 子表是：学生表 t_student；删除表时先删除子表，再删除父表；插入数据时，先插入父表数据再插入子表数据；
7、修改数据
要执行修改的操作可以使用UPDATE语句，语法如下：UPDATE 表名 SET 字段名 = 字段值，字段名 = 字段值 WHERE 条件；
注意：update如果没有条件限制，将把整张表的数据全部更新
UPDATE tb_user SET password=‘123’ WHERE username=‘mrbccd’;
UPDATE tb_user SET password=‘123’ ;
8、删除数据
在数据库中，有些数据已经失去意义或者错误时就需要将它们删除，在
MySQL中，可以使用DELETE语句或者TRUNCATE TABLE语句删除表中的一行或多行数据.
1、通过DELETE语句删除数据
通过DELETE语句删除数据的基本语法格式如下：
DELETE FROM 表名 WHERE 条件限制；
注意：若没有条件限制，会将表中所有记录全部删除；
DELETE FROM tb_user WHERE username=‘abs’;
DELETE FROM tb_user_bak;
SELECT * FROM tb_user;
2、通过TRUNCATE TABLE语句删除数据
在删除数据时，如果要从表中删除所有的行，通过TRUNCATE TABLE语句删除数据的基本语法格式如下：
TRUNCATE [TABLE] 数据表名
9、数据库的备份与还原
使用 mysqldump命令实现数据备份,mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
• 备份整个数据库
mysqldump -u root -p db_admin > db_admin.sql • 备份数据库中的一张 m y s q l d u m p - u r o o t - p d b _ a d m i n t b _ a d m i n >db_admin.tb_admin.sql
使用 source 命令导入数据备份
• 连接到数据库
• create database test4; • use test4; • source c:/xampp/db_admin.sql
10、算术运算符
算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括：
加、减、乘、除、求余。
例：使用算术运算符对数据表tb_book1中的row字段值进行加、减、乘、除运算
use db_database07;
select row,row+row,row-row,row*row,row/row from tb_book1;
11、比较运算符
比较运算符是查询数据时最常用的一类运算符。SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。
12、逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回1。如果表达式是假，结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符，分别是与、或、非和异或。
13、位运算符
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。MySQL中支持6种位运算符。分别是：按位与、按位或、按位取反、按位异或、按位左移和按位右移。
14、运算符的优先级
由于在实际应用中可能需要同时使用多个运算符。这就必须考虑运算符的运算顺序。正所谓：闻道有先后，术业有专攻。
优先级 运算符
1 !
2 ~
3 ^
4 *,/,DIV,%,MOD
5 +,-
6 >>,<<
7 &
8 |
9 =,<=>,<,<=,>,>=,!=,<>,IN,IS,NULL,LIKE,REGEXP
10 BETWEEN AND,CASE,WHEN,THEN,ELSE
11 NOT
12 &&,AND
13 ||,OR,XOR
14 :=