基础
从系统架构的层次上看,MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。所以在 MySQL 中,一个完整的数据存储过程总共有 4 步,分别是创建数据库、确认字段、创建数据表、插入数据。
标识符命名规则:
- 数据库名、表名不得超过30个字符,变量名限制为29个;
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符;
- 数据库名、表名、字段名等对象名中间不要包含空格;
- 同一个MySQL软件中,数据库不能同名;同一个库中,表不能重名;同一个表中,字段不能重名;
- 必须保证字段没有与保留字、数据库系统或常用方法冲突。如果坚持使用,请在SQL语句中使用```(着重号)引起来
保持字段名和类型的一致性:在命名字段并为其指定数据类型的时候一定要保证一致性,假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了。
库操作
命令行登录MySQL服务器
本地登录
# usr_name表示用户名, usr_pwd表示用户密码, usr_pwd与-p无空格
mysql -u usr_name -pusr_pwd
远程登录:
# host表示主机IP地址,port表示端口
mysql -h host -P port -u usr_name -pusr_pwd
退出登陆
exit
或
quit
创建数据库
```sql — 方式1:创建名为database_name的数据库 create database
; — 方式2:创建数据库并指定字符集 CREATE DATABASE 数据库名 CHARACTER SET 字符集; — 方式3:判断数据库是否已经存在,不存在则创建数据库(推荐方式) CREATE DATABASE IF NOT EXISTS 数据库名;
<a name="dydtT"></a>
## 删除数据库
```sql
-- 删除名为database_name的数据库
drop database <database_name>;
-- 删除已经存在的数据库(推荐)
DROP DATABASE IF EXISTS 数据库名;
使用数据库
-- 查看当前有哪些数据库
show databases;
-- 切换/使用名为database_name的数据库
use <database_name>;
-- 查看当前正在使用的数据
SELECT DATABASE();
-- 查看指定数据库下的所有表
SHOW TABLES FROM 数据库名;
-- 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
修改数据库
-- 修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如:gbk、utf8等
--修改数据库名: DATABASE不能改名。一些可视化工具可以改名,它是建新库,
--把所有表复制到新库,再删旧库完成的。
执行sql脚本
# 1. 先进入到某个数据库
# 2. 执行source xxx.sql命令
# 注意: 执行source命令时,语句结尾不加分号,否则报错。
服务器元信息
MySQL服务器元信息 | |
---|---|
SELECT VERSION( ); | 服务器版本信息 |
SELECT DATABASE( ); | 当前数据库名 (或者返回空) |
SELECT USER( ); | 当前用户名 |
SHOW STATUS; | 服务器状态 |
SHOW VARIABLES; | 服务器配置变量 |
SHOW ERRORS
和SHOW WARNINGS
:用来显示服务器错误或警告消息。SHOW GRANTS
:用来显示授予用户(所有用户或特定用户)的安全权限。
表操作
表操作 | |
---|---|
show tables; | 列出当前数据库的表 |
show fields from tab; | 列出表tab的字段,显示表tab结构。 |
show columns from tab; | |
describe tab | |
desc tab; | |
show create table tab; | 显示创建表tab_name时的SQL语句 |
truncate table tab; | 删除表tab中的所有数据 |
drop table tab; | 删除表tab |
创建表
创建表必须具备两个条件:1. 具有CREATE TABLE
权限; 2. 有相应的存储空间。
--创建表语法格式
CREATE TABLE [IF NOT EXISTS] 表名(
字段1 数据类型 [约束条件] [默认值],
字段2 数据类型 [约束条件] [默认值],
字段3 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
-- IF NOT EXISTS是可选的,加上则仅在表不存在时创建表
CREATE TABLE [IF NOT EXISTS] `runoob_tbl`(
-- 反引号是为了区分 MySql 关键字与普通字符而引入的符号,一般表名与字段名都使用反引号。
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
# 默认值: "runoob_test"
`runoob_title` VARCHAR(100) NOT NULL DEFAULT "runoob_test",
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE, PRIMARY KEY ( `runoob_id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
NULL
是字段的默认属性,如果不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。AUTO_INCREMENT
定义为自增的属性,一般用于主键,数值会自动加1。每个表只允许有一个AUTO_INCREMENT
列,而且它必须被索引。- PRIMARY KEY关键字用于定义列为主键。 可以使用多列来定义主键,列间以逗号分隔。如果主键使用单个列,则它的值必须唯一;如果主键使用多个列,则这些列的组合值必须唯一。
- 建表时如果某列给出了默认值(
DEFAULT
),则在插入数据时,该列可以不用给出值。MySQL不允许使用函数作为默认值,它只支持常量。 ENGINE
设置存储引擎,引擎可以混合使用:不同的表可以使用不同的存储引擎。混合引擎的一个缺点是:外键不能跨引擎。CHARSET
设置编码。
MySQL 字段属性应该尽量设置为 NOT NULL,除非有很特别的原因去使用 NULL 值,否则应该总是保持字段为NOT NULL。
(1) 首先要搞清楚空值 **""**
和 **NULL**
的概念:
- 空值是不占用空间的
- MySQL中的NULL其实是占用1B空间的
(2) 数据库里严格区分NULL和空值,任何数跟NULL
进行运算都是NULL
;判断值是否等于NULL
,不能简单用=
,而要用 IS NULL
、IS NOT NULL
关键字或ifnull()
函数。判断空字符用=''
或者 <>''
来进行处理。
(3) 假设表字段 col1 设为 NOT NULL, 仅仅说明该字段不能为 NULL, 也就是说只有在:INSERT INTO table1(col1) VALUES(NULL);
这种情况下数据库会报错,而INSERT INTO table1(col1) VALUES('');
不会报错。
(如果字段是自增ID,第一句不会报错,这不能说明是可以为NULL,而是数据库系统会根据ID设的缺省值填充,或者如果是自增字段就自动加一等缺省操作。)
对于timestamp
数据类型:如果往这个数据类型插入的列插入NULL
值,则出现的值是当前系统时间。插入空值,则会出现0000-00-00 00:00:00
。
(4)在进行count()
统计某列的记录数的时候,如果采用的是NULL值,系统会忽略掉对应的行,但空值是会进行统计到其中的。
(5) 含有空值的列很难进行查询优化,而且对表索引时不会存储 NULL 值的,所以如果索引的字段可以为 NULL,索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用 0、一个特殊的值或者一个空串代替空值。
(6) 连接查询的时候,例如 LEFT JOIN table2,若没有记录,则查找出的 table2 字段都是 null。假如 table2 有些字段本身可以是 null,那么除非把 table2 中 not null 的字段查出来,否则就难以区分到底是没有关联记录还是其他情况。
MySQL存储引擎InnoDB与Myisam的六大区别
https://www.runoob.com/w3cnote/mysql-different-nnodb-myisam.html
删除表⭐️⭐️⭐️
DROP TABLE [IF EXISTS] <tab_name>;
:用来删除表全部数据和表结构,立刻释放磁盘空间,不管是 Innodb 还是 MyISAM。TRUNCATE TABLE <tab_name>;
:删除表全部数据,保留表结构,立刻释放磁盘空间 ,不管是 Innodb 和 MyISAM。主要用于清空表中数据。DELETE FROM <table_name>;
: 删除表全部数据,表结构不变,对于 MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间。DELETE FROM <tab_name> WHERE...
:只删除表中部分数据,表结构不变,不管是 innodb 还是 MyISAM 都不会释放磁盘空间;delete from
表以后未释放磁盘空间,所以下次插入数据的时候,仍然可以使用这部分空间。
delete,drop,truncate 都有删除表的作用,区别在于:delete 是 DML 语句,操作完以后如果不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚。执行的速度上:-- 删除student表中名字为'张三'的学生
delete from student where T_name = "张三";
drop>truncate>delete
。
修改表⭐️⭐️⭐️
修改表指的是修改数据库中已经存在的数据表的结构。使用ALTER TABLE
可以实现:
- 向已有的表中添加列;
- 修改现有表中的列;
- 删除现有表中的列;
- 重命名现有表中的列。
删除字段(列)
使用ALTER
命令及DROP
子句来删除创建表tab_name的col_name字段。如果数据表中只剩余一个字段则无法使用DROP来删除字段。 ```sql — 语法格式 ALTER TABLE tab_name DROP col_name;
— 示例 ALTER TABLE dept80 DROP COLUMN job_id;
<a name="bQlvP"></a>
### 添加字段(列)
`ADD`子句来向数据表中添加列,如在表tab_name中添加col_name字段,并定义数据类,col_name字段自动添加到数据表字段的末尾。
```sql
-- 语法格式
ALTER TABLE tab_name ADD col_name INT;
-- 示例
ALTER TABLE dept80 ADD job_id varchar(15);
如果需要指定新增字段的位置,可以使用MySQL提供的关键字FIRST
(设定位第一列), AFTER
字段名(设定位于某个字段之后)。 在执行成功后,使用SHOW COLUMNS FROM tab_name
查看表结构的变化:
-- FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句。
ALTER TABLE tab_name ADD first_col INT FIRST;
ALTER TABLE tab_name ADD second_col INT AFTER first_col;
SHOW COLUMNS FROM tab_name;
修改字段(列)
修改列可以修改一个列的数据类型、长度、默认值和位置。修改字段时可以指定该字段是否包含值或者是否设置默认值。如果不设置默认,则MySQL自动设置该字段默认为NULL。语法格式如下:
ALTER TABLE 表名 MODIFY 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名2];
-- 示例
ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
ALTER TABLE dept80 MODIFY salary double(9,2) NOT NULL default 1000;
使用 CHANGE
子句修改列名, 在CHANGE
关键字之后,紧跟着的是要修改的字段名,然后指定新字段名及类型。语法格式如下:
-- 修改表tab_name中col_name字段的名字为new_col_name,类型为BIGINT
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
--示例
ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);
修改字段(列)的默认值
-- 修改tab_name表中col_name的默认值为1000.
ALTER TABLE tab_name ALTER col_name SET DEFAULT 1000;
-- 查看tab_name的表结构
SHOW COLUMNS FROM tab_name;
-- 删除tab_name表中col_name列的默认值, 删除默认值后, 默认值为NULL.
ALTER TABLE tab_name ALTER col_name DROP DEFAULT;
修改存储引擎
-- 修改存储引擎为myisam
ALTER TABLE tab_name engine=myisam;
-- 查看表tab_name的类型
SHOW TABLE STATUS FROM 'tab_name';
添加/删除外键约束
-- 给表tab_name添加外键约束
ALTER TABLE tab_name ADD foreign key key_name;
-- 删除tab_name表中的外键约束 key_name
ALTER TABLE tab_name DROP foreign key key_name;
重命名表
-- 方式1
-- 将表tab_name重命名为new_tab_name
RENAME TABLE tab_name TO new_tab_name;
-- 重命名多个表
RENAME TABLE
tab_name1 TO new_tab_name1,
tab_name2 TO new_tab_name2,
...
tab_namen TO new_tab_namen;
-- 方式2
ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略
复制表⭐️⭐️⭐️
复制完整表
完整的复制一个表,包括表结构、索引、默认值、存储引擎等,有以下两种方法:
- 通过
SHOW CREATE TABLE
获取建表时的完整表结构;然后复制其SQL语句修改表名并执行; 如果想拷贝表的数据可以使用INSERT INTO... SELECT
语句来实现。``sql -- 查看建表old_tab时的完整结构 SHOW CREATE TABLE old_tab Table: old_tab Create Table: CREATE TABLE
old_tab(
runoob_idint(11) NOT NULL auto_increment,
runoob_titlevarchar(100) NOT NULL default '',
runoob_authorvarchar(40) NOT NULL default '',
submission_datedate default NULL, PRIMARY KEY (
runoob_id), UNIQUE KEY
AUTHOR_INDEX(
runoob_author`) ) ENGINE=InnoDB
— 复制上面的SQL语句,修改表名为new_tab并执行
CREATE TABLE new_tab
(
runoob_id
int(11) NOT NULL auto_increment,
runoob_title
varchar(100) NOT NULL default ‘’,
runoob_author
varchar(40) NOT NULL default ‘’,
submission_date
date default NULL,
PRIMARY KEY (runoob_id
), UNIQUE KEY AUTHOR_INDEX
(runoob_author
)
) ENGINE=InnoDB;
— 复制旧表old_tab的记录到新表new_tab
INSERT INTO new_tab
( runoob_id, , old_tab runoob_title, runoob_author, submission_date)
SELECT runoob_id,runoob_title, runoob_author,submission_date
FROM old_tab;
2. `like`方法:该方法能将一个表的结果复制生成一个一模一样的新表,包括复制旧表的备注、索引、主键外键、存储引擎等。
```sql
-- 复制表结构, 不拷贝数据
CREATE TABLE target_table LIKE source_table;
-- 拷贝表中数据(记录)
INSERT INTO target_table SELECT * FROM source_table;
复制表结构及记录⭐️⭐️
CREATE TABLE new_tab SELECT * FROM old_tab;
复制部份表
拷贝表中的一些字段,可以在建新表时对拷贝字段进行改名
CREATE TABLE new_admin AS
(
SELECT id, username AS uname, password AS pass
FROM admin -- 此处结尾无";"
);
(2)拷贝部分数据
CREATE TABLE new_admin AS
(
SELECT * FROM admin WHERE LEFT(username,1) = 's'
)
(3)创建新表的同时定义表中的字段信息:
CREATE TABLE new_admin
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)
临时表
MySQL临时表在需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
使用 SHOW TABLES命令无法查看临时表。默认情况下断开数据库连接临时表会自动销毁,也可以通过drop table 命令来手动删除临时表。
-- TEMPORARY关键字标识临时表
CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL,
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
-- 插入数据
INSERT INTO SalesSummary
(product_name, total_sales, avg_unit_price, total_units_sold)
VALUES ('cucumber', 100.25, 90, 2);
-- 查询数据
SELECT * FROM SalesSummary;
-- 删除临时表
DROP TABLE SalesSummary;