第四模块 第27章 MySQL数据库开发(一)
1. MySQL简单介绍
2. 不同平台下安装MySQL
2.1 下载
版本求稳不求新
一般使用5.6版
官网:https://dev.mysql.com
2.2 安装
解压到相应路径下即安装成功
bin: 存放的是执行命令. mysqld: 服务端套接字; mysql: 客户端套接字.
data: 存放数据. data>mysql: 用户认证的信息.
2.3 配置环境变量
2.4 服务端(mysqld)启动
启用mysql的方式:
方式1: mysqld, 在普通cmd窗口中执行本命令.
方式2: net start MySQL(前提是启用了'服务', 且必须在管理员cmd窗口下执行)
方式3: '服务'中启动
停用mysql的方式:
方式1: taskkill /F /PID pid号 (在管理员cmd窗口运行, 需要先查出进程的pid号)
方式2: net stop MySQL(前提是启用了'服务', 且必须在管理员cmd窗口下执行)
方式3: '服务'中停用
在服务中设置成自动后, 开机自动启动, 不用每次开机后都启动.
要实现以上效果, 可以通过制作系统服务来实现, 步骤如下:
1. 找到进程的PID号>>>>以管理员的身份通过PID号强制结束进程.
2. 制作系统服务:
注意: 在管理员cmd窗口中执行
3. windows平台下mysql密码的设置与破解
客户端登录方式:
方式1: mysql, 登录到默认的本地账户
方式2: mysql -uroot -p, 登录到管理员账户
C:\Users\wangjian_tr>mysql 默认登录客户端 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> select user(); 查看当前登录的账号 +----------------+ | user() | +----------------+ | ODBC@localhost | 当前登录的默认账号 +----------------+ 1 row in set (0.00 sec) 如果想切换其他账号来登录, 则需要执行以下操作: mysql> exit 先退出 Bye C:\Users\wangjian_tr>mysql -uroot -p 以管理员的身份登录: 用户名为root, 默认原始密码为空 Enter password: 直接回车 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | 以管理员的身份登录, 没有密码, 这种情况十分危险. +----------------+ 1 row in set (0.00 sec) 通过以下方式为管理员账号设置密码 mysql> exit Bye C:\Users\wangjian_tr>mysqladmin -uroot -p password "123" mysqladmin为一种客户端工具, 用于指定管理员命令.
为管理员账号设置密码, 用户名为root, 原始密码为空, 新密码为123. Enter password: 原始密码为空 Warning: Using a password on the command line interface can be insecure. C:\Users\wangjian_tr>mysql -uroot -p123 以新密码登录 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> exit Bye C:\Users\wangjian_tr>mysqladmin -uroot -p123 password "456" 再次为管理员账户修改密码 Warning: Using a password on the command line interface can be insecure. C:\Users\wangjian_tr>mysql -uroot -p456 以新密码登录 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> exit Bye 如果忘记了密码, 可以跳过授权表启动mysql
1. 首先关闭mysql数据库, 如果已经启用了系统服务, 则可以使用指令: net stop/start MySQL(注意这个名字和系统服务中的相同).
C:\WINDOWS\system32>net stop MySQL 以管理员的身份通过指令关闭服务, 也可以通过系统服务终端关闭. MySQL 服务正在停止. MySQL 服务已成功停止。 2. 跳过授权表启动mysql
C:\WINDOWS\system32>mysqld --skip-grant-tables 2020-08-11 12:28:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2020-08-11 12:28:20 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled 2020-08-11 12:28:20 0 [Note] mysqld (mysqld 5.6.42) starting as process 21304 ... Microsoft Windows [版本 10.0.16299.192] (c) 2017 Microsoft Corporation。保留所有权利。
3. 另起一个终端, 以默认身份登录 C:\Users\wangjian_tr>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> select user(); +--------+ | user() | +--------+ | ODBC@ | +--------+ 1 row in set (0.00 sec) mysql> exit Bye
4. 以管理员身份登录, 由于跳过了授权表, 此处不用输入密码 C:\Users\wangjian_tr>mysql -uroot -p Enter password: 直接回车 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> select user(); +--------+ | user() | +--------+ | root@ | +--------+ 1 row in set (0.00 sec)
5. 修改密码 mysql> update mysql.user set password=password("") where user="root" and host="localhost"; 修改密码, 指定为空 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
6. 刷新权限 mysql> flush privileges; 刷新权限 Query OK, 0 rows affected (0.01 sec)
7. 退出 mysql> exit Bye
8. 结束进程, 然后以默认的正常方式重新启动mysql. C:\Users\wangjian_tr>tasklist |findstr mysql 找到进程的pid mysqld.exe 21304 Console 1 99,856 K C:\Users\wangjian_tr> Microsoft Windows [版本 10.0.16299.192] (c) 2017 Microsoft Corporation。保留所有权利。 C:\WINDOWS\system32>taskkill /F /PID 21304 以管理员身份通过PID号杀死进程 成功: 已终止 PID 为 21304 的进程。 C:\WINDOWS\system32>net start MySQL 启动进程 MySQL 服务正在启动 . MySQL 服务已经启动成功。 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.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>
注意:
就是如果启用了系统服务, 在关闭和启动mysql的时候可以使用 net start/stop MySQL, 如果没有启用系统服务, 只能用mysqld启动, 使用taskkill 强制杀死进程.
一般你用net stop 服务，那启动也有net start.
taskkill 相当于杀掉这个进程，属于强制操作.
stop 服务，相当于是安全退出.
有服务的，4个命令全部可以用，没有服务的，只能用非net的.
注意: 套接字客户端在连接的时候并未指定套接字服务端的id和端口. 默认的如下:
mysql -uroot -p -h 127.0.0.1 -P 3306
4. 统一字符编码
Microsoft Windows [版本 10.0.16299.192] (c) 2017 Microsoft Corporation。保留所有权利。 C:\WINDOWS\system32>mysql -uroot -p 登录 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> \s 查看字符编码 -------------- mysql Ver 14.14 Distrib 5.6.42, for Win64 (x86_64) Connection id: 2 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.6.42 MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 可以看出字符编码并不统一 Db characterset: latin1 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 4 hours 8 min 47 sec Threads: 1 Questions: 7 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.000 --------------
那么怎么统一windows平台下数据库的字符编码呢?
修改配置文件:
1. 新建my.ini文件
2. 以Notepad++的方式打开
复制进入以下内容:
[mysqld] character-set-server=utf8 collation-server=utf8_general_ci [client] default-character-set=utf8 [mysql] default-character-set=utf8
3. 以管理员的身份关闭并重新启动mysql.
Microsoft Windows [版本 10.0.16299.192] (c) 2017 Microsoft Corporation。保留所有权利。 C:\WINDOWS\system32>mysql -root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> exit 退出 Bye C:\WINDOWS\system32>net stop MySQL 关闭 MySQL 服务正在停止. MySQL 服务已成功停止。 C:\WINDOWS\system32>net start MySQL 重启 MySQL 服务正在启动 . MySQL 服务已经启动成功。 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.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> \s 查看编码格式 -------------- mysql Ver 14.14 Distrib 5.6.42, for Win64 (x86_64) Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.6.42 MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: utf8 可以看出编码统一了 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 30 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.166 -------------- mysql>
5. 初识sql语句
1. 操作文件夹(库) 增：create database db1 charset utf8; 查：
show databases; 查看所有的数据库
show create database db1; 查看刚创建的数据库的信息 改：alter database db1 charset latin1; 删除: drop database db1;
2. 操作文件(表) 先切换到文件夹下：use db1
查看当前所在文件夹: select database(); 增：create table t1(id int,name char); 查：show tables;
show create table t1;
desc t1;
改：alter table t1 modify name char(3); alter table t1 change name name1 char(2); 删：drop table t1; 3. 操作文件中的内容(记录) 增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); 查：select * from t1; 不推荐使用*
select name from db1.t1; 改：update t1 set name='sb' where id=2; 删：delete from t1 where id=1;
# 数据库的增删改查
Microsoft Windows [版本 10.0.16299.192] (c) 2017 Microsoft Corporation。保留所有权利。 C:\Users\wangjian_tr>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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.
1. 增 mysql> create database db1 charset utf8; Query OK, 1 row affected (0.00 sec)
2. 查 mysql> show create database db1; 查看刚创建的数据库 +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> show databases; 查看所有数据库 +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
3. 改
mysql> alter database db1 charset gbk; 只能修改数据库的字符编码 Query OK, 1 row affected (0.00 sec) mysql> show create database db1; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
4. 删
mysql> drop database db1; 删除数据库 Query OK, 0 rows affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
表的增删改查
mysql> create database db1 charset utf8; Query OK, 1 row affected (0.00 sec)
mysql> use db1; 切换文件夹 Database changed mysql> select database(); 查看当前所在文件夹 +------------+ | database() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec) mysql> create table t1(id int,name char); 新增表, 指定表名, 字段名和字段的类型 Query OK, 0 rows affected (0.32 sec) mysql> show create table t1; 查看表结构 +-------+---------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.06 sec) mysql> show tables; 查看当前库下所有的表 +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> alter table t1 modify name char(6); 修改表 Query OK, 0 rows affected (0.62 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; 查看表结构 +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(6) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> alter table t1 change name Name char(2); 修改表 Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | Name | char(2) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> drop table t1; 删除表 Query OK, 0 rows affected (0.13 sec) mysql>
# 记录的增删改查
mysql> mysql> create table t1(id int,name char(6)); 创建表 Query OK, 0 rows affected (0.23 sec) mysql> insert t1(id,name) values(1,'egon'),(2,'alex'); 新增记录 Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select id,name from db1.t1; 查看记录 +------+------+ | id | name | +------+------+ | 1 | egon | | 2 | alex | +------+------+ 2 rows in set (0.00 sec) mysql> select * from db1.t1; 查看某表的所有记录 +------+------+ | id | name | +------+------+ | 1 | egon | | 2 | alex | +------+------+ 2 rows in set (0.00 sec) mysql> insert t1 values(3,'maria'); 默认按照id, name的顺序新增 Query OK, 1 row affected (0.07 sec) mysql> select * from db1.t1; +------+-------+ | id | name | +------+-------+ | 1 | egon | | 2 | alex | | 3 | maria | +------+-------+ 3 rows in set (0.00 sec) mysql> update db1.t1 set name='LL'; 修改记录, 将所有name的值改为LL Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | LL | | 2 | LL | | 3 | LL | +------+------+ 3 rows in set (0.00 sec) mysql> update db1.t1 set name='alex' where id=2; 修改某id的name的值 Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | LL | | 2 | alex | | 3 | LL | +------+------+ 3 rows in set (0.00 sec) mysql> delete from t1 where id=3; 删除id为3的记录 Query OK, 1 row affected (0.07 sec) mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | LL | | 2 | alex | +------+------+ 2 rows in set (0.00 sec) mysql> delete from t1; 删除表中的所有记录 Query OK, 2 rows affected (0.07 sec) mysql> select * from t1; Empty set (0.00 sec) mysql>
6. 库操作
6.1 系统数据库
information_schema： 虚拟库，不占用磁盘空间(体现: 在安装路径下找不到文件夹)，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
6.2 创建数据库
1 语法(help create database)
create database 数据库名 charset utf8;
2 数据库命名规则
可以由字母、数字、下划线、＠、＃、＄, 不建议使用特殊符号
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位
6.3 数据库相关操作
查看数据库
show databases;
show create database db1;
select database();
选择数据库
USE 数据库名
删除数据库
DROP DATABASE 数据库名;
修改数据库
alter database db1 charset utf8;
补充: 如果忘记了命令怎么写, 可以采用以下方式:
Microsoft Windows [版本 10.0.16299.192] (c) 2017 Microsoft Corporation。保留所有权利。 C:\Users\wangjian_tr>mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.42 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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> help create 查找create的作用 Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE LOGFILE GROUP CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SPATIAL mysql> help create database 查找create database的用法 Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... []中内容可有可无, {}中内容必须要有, |代表'或' create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html mysql>
7. 存储引擎介绍
1. 什么是存储引擎?
存储引擎就是表的类型
2. 查看MySQL支持的存储引擎
show engines;
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
默认使用的存储引擎是: InnoDB
8. 表的增删改查
一、表介绍
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段
id，name，qq，age称为字段，其余的，一行内容称为一条记录
二、创建表
语法
create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的
MariaDB [(none)]> create database db1 charset utf8; MariaDB [(none)]> use db1; MariaDB [db1]> create table t1( -> id int, -> name varchar(50), -> sex enum('male','female'), -> age int(3) -> ); MariaDB [db1]> show tables; #查看db1库下所有表名 MariaDB [db1]> desc t1; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ MariaDB [db1]> select id,name,sex,age from t1; Empty set (0.00 sec) MariaDB [db1]> select * from t1; Empty set (0.00 sec) MariaDB [db1]> select id,name from t1; Empty set (0.00 sec)
往表中插入数据
MariaDB [db1]> insert into t1 values -> (1,'egon',18,'male'), -> (2,'alex',81,'female') -> ; MariaDB [db1]> select * from t1; +------+------+------+--------+ | id | name | age | sex | +------+------+------+--------+ | 1 | egon | 18 | male | | 2 | alex | 81 | female | +------+------+------+--------+ MariaDB [db1]> insert into t1(id) values -> (3), -> (4); MariaDB [db1]> select * from t1; +------+------+------+--------+ | id | name | age | sex | +------+------+------+--------+ | 1 | egon | 18 | male | | 2 | alex | 81 | female | | 3 | NULL | NULL | NULL | | 4 | NULL | NULL | NULL | +------+------+------+--------+
注意注意注意：表中的最后一个字段不要加逗号
注意: 在查看表信息的时候, 如果表的信息非常多, 显示混乱, 则可以采用以下方式查看:
mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(6) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(6) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
三. 查看表结构
MariaDB [db1]> describe t1; #查看表结构，可简写为desc 表名 +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ MariaDB [db1]> show create table t1\G; #查看表详细结构，可加\G
四. 修改表结构
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
示范
示例： 1. 修改存储引擎 mysql> alter table service -> engine=innodb; 2. 添加字段 mysql> alter table student10 -> add name varchar(20) not null, -> add age int(3) not null default 22; mysql> alter table student10 -> add stu_num varchar(10) not null after name; //添加name字段之后 mysql> alter table student10 -> add sex enum('male','female') default 'male' first; //添加到最前面 3. 删除字段 mysql> alter table student10 -> drop sex; mysql> alter table service -> drop mac; 4. 修改字段类型modify mysql> alter table student10 -> modify age int(3); mysql> alter table student10 -> modify id int(11) not null primary key auto_increment; //修改为主键 5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int(11) not null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int(11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 6. 对已经存在的表增加复合主键 mysql> alter table service2 -> add primary key(host_ip,port); 7. 增加主键 mysql> alter table student1 -> modify name varchar(10) not null primary key; 8. 增加主键和自动增长 mysql> alter table student1 -> modify id int not null primary key auto_increment; 9. 删除主键 a. 删除自增约束 mysql> alter table student10 modify id int(11) not null; b. 删除主键 mysql> alter table student10 -> drop primary key;
五. 复制表
复制表结构＋记录 （key不会复制: 主键、外键和索引） mysql> create table new_service select * from service; 只复制表结构 mysql> select * from service where 1=2; //条件为假，查不到任何记录 Empty set (0.00 sec) mysql> create table new1_service select * from service where 1=2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create table t4 like employees; 只copy表结构
六. 删除表
DROP TABLE 表名;
浙公网安备 33010602011771号