MYSQL服务器常用命令
零、用户管理
使用管理员root用户进行
1、用户新建、更改、删除
新建用户：（基本格式）
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明：
- username：你将要创建的用户名。
- host：IP地址，指定该用户在那个主机上可以登录。如果是本机用户的话可以使用localhost；如果想让该用户可以在任意的远程主机登录，可以使用通配符%。
- password：该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。
删除用户：
->首先查看select host,user,password from user;
->删除drop user 'username'@'host';
对账户重命名：rename user 'jack'@'%' to 'jim'@'%';
修改jack用户名为jim。
2、更改密码：
-- 设置name用户的密码为fdddfd（root管理员用户才可以给普通用户设置密码
SET PASSWORD FOR 'username'@'host'=PASSWORD('fdddfd');
-- 设置当前登录用户密码
SET PASSWORD=PASSWORD('fdddfd');
-- 例如
SET PASSWORD FOR 'dog'@'%'=PASSWORD('dogyyds');
3、权限管理
授权：（基本格式）
GRANT privileges ON databasename.tablename TO 'username'@'host';
说明：
- privileges：用户的操作权限，如select，insert，update等，如果授予所有的权限使用all。
- databasename：数据库名。
- tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
注意：
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令：
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
grant命令使用说明：
-- 例：创建一个任意ip都可以登录natasa用户，授予school数据库中所有表的权限，
并允许将权限赋予其他用户，密码为‘wula’
GRANT all (privileges) ON school.* TO 'natasa'@'%' identified by 'wula' with grant option;
Query OK, 0 rows affected (0.01 sec)
-- 说明：
-- all (privileges)：表示所有权限，privileges可省略；也可具体到具体权限如select，多个用逗号隔开。
-- on：表示指定权限针对那些库和表。
-- school.*：表示点前面代表指定数据库名，点后面表示指定的表名。
-- to：表示权限赋予给那个用户；该语句直接新建了用户并设置了密码。
-- 'natasa'@'%'：@前面为创建用户名，单引号可省略；
后面为限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。
注意：这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，
但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。
-- identified by：指定用户的登录密码。
-- with grant option： 这个选项表示该用户可以将自己拥有的权限授权给别人。
注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
-- 备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。
刷新权限flush privileges; 每次修改都需要重新刷新权限。
查看权限show grants;查看当前用户的权限。show grants for 'username'@'host';查看某个用户的权限。
回收（删除）权限revoke privileges on *.* from 'username'@'host';
示例
设置普通dba管理某个数据的权限
Grant ALL privileges on testdb to usernameDBA@'localhost';
Localhost是限制本地
Grant ALL on testdb to userNameDBA@'%';
ip没有限制
设置能查询mysql服务器上所有数据库的表
Grant select on *.* to userName@'%';
username用户不管在哪登陆都能操作mysql所有的数据库和表
Grant ALL on *.* to username@'%';
Username用户可以管理mysql服务器中所有的数据库
grant 作用在单个数据库上：
grant select on testdb.* to username@'localhost';
grant 作用在单个数据表上：
Grant select on testdb.tablename to username@'%';
Grant作用在表的列上
Grant select(id,sex,rank) on testdb.tableName to username@'%';
查看用户权限
Show grants for username;
赋予权限-为已创建用户赋予，新建并赋予权限时需要制定host和密码
Grant select on databaseName.* to username;
Grant select,update,delete,insert on databaseName.* to username;
回收权限
Revoke select, delete on databaseName.* from username;
每次权限的修改都需要刷新权限
Flush privileges/;
设置整个数据库的权限
Grant ALL on databaseName.* to username和 revoke ALL
Grant ALL on databaseName.table to username;
授权给普通用户查询，插入，更新，删除对数据库所有表的权利
%标识这个用户ip没有限制
grant select, insert, update, delete on testdb.* to common_user@'%';
授权给开发者用户在testdb数据库创建表的权限，
限制ip只能是192.168.0开头的用户
grant create on testdb.* to developer@'192.168.0.%';
设置索引的权限 设置用户在数据库上创建索引的权限
限制ip只能是192.168.0开头的用户
grant index on testdb.* to userName@'192.168.0.%';
设置存储过程的权限
grant create routine on testdb.* to username@'192.168.0.%';
grant alter routine on testdb.* to developer@'192.168.0.%';
| 权限 | 权限级别 | 权限说明 |
|---|---|---|
| CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 |
| DROP | 数据库或表 | 删除数据库或表权限 |
| GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 |
| REFERENCES | 数据库或表 | |
| ALTER | 表 | 更改表，比如添加字段、索引等 |
| DELETE | 表 | 删除数据权限 |
| INDEX | 表 | 索引权限 |
| INSERT | 表 | 插入权限 |
| SELECT | 表 | 查询权限 |
| UPDATE | 表 | 更新权限 |
| CREATE VIEW | 视图 | 创建视图权限 |
| SHOW VIEW | 视图 | 查看视图权限 |
| ALTER ROUTINE | 存储过程 | 更改存储过程权限 |
| CREATE ROUTINE | 存储过程 | 创建存储过程权限 |
| EXECUTE | 存储过程 | 执行存储过程权限 |
| FILE | 服务器主机上的文件访问 | 文件访问权限 |
| CREATE TEMPORARY TABLES | 服务器管理 | 创建临时表权限 |
| LOCK TABLES | 服务器管理 | 锁表权限 |
| CREATE USER | 服务器管理 | 创建用户权限 |
| RELOAD | 服务器管理 | 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限 |
| PROCESS | 服务器管理 | 查看进程权限 |
| REPLICATION CLIENT | 服务器管理 | 复制权限 |
| REPLICATION SLAVE | 服务器管理 | 复制权限 |
| SHOW DATABASES | 服务器管理 | 查看数据库权限 |
| SHUTDOWN | 服务器管理 | 关闭数据库权限 |
| SUPER | 服务器管理 | 执行kill线程权限 |
| 权限分布 | 可能的设置的权限 |
|---|---|
| 表权限 | ‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’ |
| 列权限 | ‘Select’, ‘Insert’, ‘Update’, ‘References’ |
| 过程权限 | ‘Execute’, ‘Alter Routine’, ‘Grant’ |
1. 登录MYSQL客户端
命令： mysql -u 用户名 -p
回车后输入密码【自己设置的-加密显示】；
或者在-p后加空格然后直接输入密码（有可能会丢失密码，不建议）
设置登录时转换GBK类库,防止中文乱码,命令:
mysql –default -character -set=gbk
**注意:MySQL数据库中的mysql库为系统库,一般不用这个库.就和电脑的C盘一个性质**
2. 查看库，表
查看所有数据库: show databases;
进入数据库: use 指定的库名;
查看库中所有的表: show tables;
查看表结构: desc 表名;
退出/断开连接:exit;或quit;或 \q;或ctrl+c;
3. 建库，建表
建库：
—>建库前先删除同名库（一个数据库链接下，库名不可重复）。
删库操作根据实际情况进行，可以通过命令直接查看是否有同名库，没有的话可以不用进行删库操作。若库较多不方便查找，可以直接使用判断删除原库（确定原库已不需要的情况下）。建议使用删除判断语句，便捷。
添加注释：杠杠加空格（-- ），后面添加注释内容
-- 假设已存在库
create database dbname;
-- 删库
drop database dbname;
-- 此时dbname库已被删除，若继续删除则会报错
drop database dbname;
ERROR 1008 (HY000): Can't drop database 'dbname'; database doesn't exist
-- 使用if exists 判断库是否存在，存在就删除，不存在不删除。
-- 注意database后面没有库名称了
drop database if exists dbname;
（推荐用软件进行操作：Notepad++软件(可以避免出错,有出错提示)、SQLyogEnt、navicat等(数据库建库表各种操作)）
—>重新创建db1库,重新创建了db1库，类型是utf8；
create database db1 charset utf8;
—>查看、进入db1库。
mysql>show databases; //查看全部数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
| db1 |
+--------------------+
7 row in set (0.22 sec)
mysql> SHOW CREATE DATABASE db1 ;//显示用于创建指定数据库的语句。这将显示 create 语句以及子句。
+----------+------------------------+
| Database | Create Database |
+----------+------------------------+
| db1 | CREATE DATABASE `db1 ` |
+----------+------------------------+
1 row in set (0.00 sec)
mysql>use db1; //进入
建表：
建表基本格式格式如下
CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名` 列类型 [属性] [索引] [注释]，
`字段名` 列类型 [属性] [索引] [注释]，
.......
`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释]
[]中内容可省略
注意点：
- IF NOT EXISTS可以省略，建议使用避免报错；
- 表名和字段名的
''单引号在navicat15版本及以上中不需要； - 使用英文() ，表的名称和字段尽量使用
''括起来； - AUTO_INCREMENT 自增，建议大写；
- 字符串用单引号括起来；
- 所有的语句用结尾加上
，,最后一句不用加； - PRIMARY KEY 主键，一般一个表只有一个唯一的主键；
-
comment注释，default默认； -
unsigned无符号的，即必须是正数，不能出现负号。默认为有符号的； -
zerofill填充零，整数后()中的数字表示数值长度，配合zerofill使用，长度不足其余填充零； -
(),unsigned,zerofill一般一起出现，填充零的话必须的无符号才行； - 设置字符集时，charset=utf8，utf8中间没有杠；
- 多个建表语句用分号隔开，最后一个可以省略分号。
-- 例子
CREATE TABLE IF NOT EXIST 'student'(
`id` INT(4) zerofill unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR (100) DEFAULT NULL COMMENT '家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
PRIMARY KEY(`id`) --主键-单独标注方便查看
)ENGINE=INNODB DEFAULT CHARSET=utf8 --INNODB引擎
关于引擎：
INNODB 默认使用 ， MYISAM早些年使用
| 引擎 | MYISAM | INNODB |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为2倍 |
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
mysql>SHOW CREATE TABLE student; //查看student数据表的定义语句
mysql>DESC student; //查看表的结构
数据类型
->基本类型
MySQL中支持5种整数类型，其实很大程度上相同的，只是存储值的大小范围不同而已。
其次是浮点类型float和double类型
tinyint：占用1个sss字节，相对于java中的byte
smallint：占用2个字节，相对于java中的short
int：占用4个字节，相对于java中的int
bigint：占用8个字节，相对于java中的long
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double
注意：整型后面的()中表示字符个数，配合zerofill使用，空闲位置填充零，超出规定个数则失效。
存储大小已确定，所以超出个数时不会报错，超出存储范围才会报错。
---添加数据时，数据进行消除除前置零操作；
当设置了int() zerofill unsigned后，再根据数字填充零；
若数据长度超出设置在()中的数字，则直接添加。
->字符串类型
char()------定长字符串，最长255个字符。定长会浪费空间
varchar()----变长(不定长)字符串，最长不超过 65535个字符;
一般超过255个字符，会使用text类型. 不定长节省空间,剩余空间会留给别的数据使用
注意：()中表示数据最长字符数。超出报错
->长文本类型
text--------最长65535个字节
char、varchar、text总结：
char、varchar、text都可以表示字符串类型，其区别在于：
(1)char在保存数据时, 如果存入的字符串长度小于指定的长度n,后面会用空格补全。
(2)varchar和text保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用.
(3)char会造成空间浪费(不足指定长度的会用空格补全), 但是由于不需要计算数据的长度, 因此速度更快。（即浪费空间、节约时间）
(4)varchar和text但是节省了空间, 但是存储的速度不如char快(因为要计算数据的实际长度)
->日期类型
1、date：年月日
（字段为date类型时，使用sql语句插入时间应该是19990101，而不是1999-01-01。
说明：服务器误认为1999-01-01为表达式，解析错误（1292错误））
2、time：时分秒
3、datetime：年月日 时分秒（常用）
4、timestamp：时间戳，与datetime存储相同的数据。
timestamp最大表示2038年，而datetime范围是1000~9999
timestamp在插入数、修改数据时，可以自动更新成系统当前时间
字段约束
创建表时, 除了要给每个列指定对应的数据类型, 有时也需要给列添加约束。常见的约束有：主键约束、唯一约束、非空约束、外键约束。
->主键(primary key)
主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号;
当主键为数值时，为了方便维护，可以设置主键为自增（auto_increment）；
设置主键有两种方法，在对应字段直接设置逐渐属性或者最后通过primary key('主键字段')指定。
->唯一(unique)
保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复。
->非空(not null)
保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。
->外键(foreign key)
外键是用于表和表之间关系的列。
1：在创建表的（）中单独一句语句设置外键。(constraint 外键名 )foreign key(id) references outTable(id) on delete cascade on update cascade;
说明：(constraint 外键名 )定义外键名，可省略使用默认名。把id列 设为外键 参照外表outTable的id列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。
事件触发限制：on delete 和 on update ，可设参数cascade（跟随外键改动），restrict（限制外键中的外键改动），set NULL（设空值），set Default（设默认值）
cascade用法： http://blog.csdn.net/kadwf123/article/details/8067381
2：使用单独语句添加外键约束alter table 表名 add (constraint 外键名) foreign key (column name) references 主表表名(关联主键);
删除外键：alter table 表名 drop foreign key 外键名；文章来源：https://www.toymoban.com/news/detail-457915.html
注意：文章来源地址https://www.toymoban.com/news/detail-457915.html
- 被外键关联的主键无法更改设置(属性)；外键跟随主键信息，本身不需要和主键设置(属性)一致。
- mysql 中被其他表外键应用的字段不能添加主键自增。因为有表的约束，外键表主键自增，另外一张表必须要有新增的主键数据。
即：有外键关联的主键，当设置了自增属性时自增失效。
解决方法：-- 有外键关联的主键先不设置自增，最后再添加自增属性 -- 1.关闭外键检查 set FOREIGN_KEY_CHECKS=0; -- 2.修改主键属性，在原有属性上加上自增属性，原有属性不能少 ALTER TABLE student MODIFY COLUMN s_id int(4) zerofill unsigned not null AUTO_INCREMENT; ALTER TABLE teacher MODIFY COLUMN t_id int(4) zerofill unsigned not null AUTO_INCREMENT; -- 3.打开外键检查（保证数据关联性） set FOREIGN_KEY_CHECKS=1;
到了这里，关于MYSQL建库建表语句的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
