MYSQL建库建表语句_mysql建库语句

MYSQL服务器常用命令

零、用户管理

使用管理员root用户进行
1、用户新建、更改、删除
新建用户：（基本格式）

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
1

说明：
　　 - 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');
1
2
3
4
5
6
7
8

3、权限管理
　　授权：（基本格式）

GRANT privileges ON databasename.tablename TO 'username'@'host';
1

说明：
　　 - privileges：用户的操作权限，如select，insert，update等，如果授予所有的权限使用all。
　　 - databasename：数据库名。
　　 - tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
　　 注意：
　　用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令：

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
1

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权限。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

刷新权限
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.%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

1. 登录MYSQL客户端

命令： mysql -u 用户名 -p
回车后输入密码【自己设置的-加密显示】；
或者在-p后加空格然后直接输入密码（有可能会丢失密码，不建议）
设置登录时转换GBK类库,防止中文乱码,命令:

mysql  –default  -character  -set=gbk

**注意:MySQL数据库中的mysql库为系统库,一般不用这个库.就和电脑的C盘一个性质**
1
2
3

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;
1
2
3
4
5
6
7
8
9
10
11
12

（推荐用软件进行操作：Notepad++软件(可以避免出错,有出错提示)、SQLyogEnt、navicat等(数据库建库表各种操作)）

—>重新创建db1库,重新创建了db1库，类型是utf8；

create database db1 charset utf8;
1

—>查看、进入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;  //进入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

建表：

建表基本格式格式如下

CREATE TABLE  [IF NOT EXISTS] `表名` (
	`字段名` 列类型 [属性] [索引] [注释]，
	`字段名` 列类型 [属性] [索引] [注释]，
	.......
	`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释]

[]中内容可省略
1
2
3
4
5
6
7
8

注意点：

• 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引擎
1
2
3
4
5
6
7
8
9
10
11

关于引擎：
INNODB 默认使用 ， MYISAM早些年使用

• MYISAM 节约空间，速度较快
• INNODB 安全性高，事务的处理，多表多用户操作

mysql>SHOW CREATE TABLE student; //查看student数据表的定义语句
mysql>DESC student; //查看表的结构
1
2

数据类型

->基本类型
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后，再根据数字填充零；
	 若数据长度超出设置在()中的数字，则直接添加。
1
2
3
4
5
6
7
8
9
10
11
12
13

->字符串类型

char()------定长字符串，最长255个字符。定长会浪费空间
varchar()----变长(不定长)字符串，最长不超过 65535个字符;
			一般超过255个字符，会使用text类型. 不定长节省空间,剩余空间会留给别的数据使用
注意：()中表示数据最长字符数。超出报错
1
2
3
4

->长文本类型

text--------最长65535个字节
1

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 外键名；

注意：

• 被外键关联的主键无法更改设置(属性)；外键跟随主键信息，本身不需要和主键设置(属性)一致。
• 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;
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10