赞
踩
MySQL 教程:https://www.runoob.com/mysql/mysql-tutorial.html
MySQL 官网:https://www.mysql.com/cn/products/
MySQL 8.0文档:https://dev.mysql.com/doc/refman/8.0/en/
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。这种所谓的"关系型"可以理解为"表格"的概念。关系型数据库由一个或数个表格组成,将数据保存在不同的表中。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 的一些术语:
mysql 特点
下载:https://dev.mysql.com/downloads/mysql/
成功安装 MySQL 后,一些基础表会表初始化,在服务器启动后,你可以通过简单的测试来验证 MySQL 是否工作正常。使用 mysqladmin 工具来获取服务器状态:在 linux 上该二进制文件位于 /usr/bin 目录,在 Windows 上该二进制文件位于C:\mysql\bin 。
php study 自带的 mysql 工具
使用 mysql 命令连接到 MySQL 数据库 服务
输出 mysql> 提示符说明已经成功连接到 Mysql 服务器上,可以在 mysql> 执行SQL命令。
以下操作都是在 MySQL 的提示符下进行的,而且每个命令以分号结束。
如果在文件 my_file.sql 中存放有查询,可如下执行这些查询:
例如,也可以将建表语句提前写在sql.txt中,然后执行:
mysql > mysql -h myhost -u root -p database < sql.txt
ubuntu上安装 MySQL 非常简单只需要几条命令就可以完成。
安装过程中会提示设置密码,安装完成之后,检查是否安装成功:sudo netstat -tap | grep mysql 看到有 mysql 的 socket 处于 listen 状态则表示安装成功。
登陆 mysql 数据库可以通过如下命令:mysql -u root -p
-u 表示选择登陆的用户名, -p 表示登陆的用户密码。默认没有 密码
在 Linux 系统上安装 MySQL 后,一般会涉及到以下几个重要的目录:
- 安装目录:默认安装目录为 /usr/local/mysql/ 或 /usr/local/mysql-<version>/
- 配置文件目录:/etc/mysql/my.cnf 或者 /etc/mysql/mysql.conf.d/mysqld.cnf
- 数据目录:默认数据目录为 /var/lib/mysql/
- 日志目录:默认日志目录为 /var/log/mysql/
- 启动脚本目录:MySQL的启动脚本通常存储在 /etc/init.d/ 目录下,用于启动、停止和管理MySQL服务器进程。
/usr/bin:此目录包含MySQL二进制文件,例如 mysql、mysqldump等命令行工具。
/usr/share/doc/mysql-server-<version>:该目录包含MySQL文档。
以上是MySQL在Linux系统上的一般目录结构,实际安装过程中可能会因发行版和安装方式的不同而有所差异。
在 Ubuntu 中,MySQL 8 的默认配置文件是 /etc/mysql/mysql.conf.d/mysqld.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysql]
auto-rehash # 配置tab自动补全
首先查看当前用户信息:
注意:安装时,需要通过管理员权限来安装,否则会由于权限不足导致无法安装。
其它命令:
show processlist; 列出每一笔联机的信息。
show varlables; 列出mysql的系统设定。
show tables from db_name; 列出db_name中所有数据表;
show [full] columns from table_name; 列出table_name中完整信息,如栏名、类型,包括字符集编码。
show index from table_name; 列出table_name中所有的索引。
show table status;; 列出当前数据库中数据表的信息。
show table status from db_name;;列出当前db_name中数据表的信息。
alter table table_name engine innodb|myisam|memory ; 更改表类型
explain table_name / describe table_name ; 列出table_name完整信息,如栏名、类型。
show create table table_name 显示当前表的建表语句
alter table table_name add primary key (picid) ; 向表中增加一个主键
alter table table_name add column userid int after picid 修改表结构增加一个新的字段
alter table table_name character set gb2312 改变表的编码
select user(); 显示当前用户。
select password(’root’); 显示当前用户密码
select now(); 显示当前日期
flush privileges 在不重启的情况下刷新用户权限
mysqld –default-character-set=gb2312 ; 设置默认字符集为gb2312
1,配置文件 ( mysqld.cnf ) 添加 skip-grant-tables 重启 mysql 后免密登陆进入mysql
2,查看当前认证方式以及用户,默认是 caching_sha2_password 认证,
select host, user, authentication_string, plugin from user;3,如果 2 是默认认证方式,则修改为密码认证为 mysql_native_password
修改方法:配置文件添加 default_authentication_plugin=mysql_native_password
再次重启 mysqld,免密登陆4,清空 root 密码 ( 要先清空,再修改,要不报错):
update user set authentication_string='' where user='root';
flush privileges;5,退出 mysql, 删除添加的 skip-grant-tables 再次重启 mysql,即可以免密码登录
mysql -u root -p6. 登陆后即可修改密码了:
alter user 'root'@'%' identified with mysql_native_password by 'root';
flush privileges;
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
# root 是用户名,123456 是密码
flush privileges;
quit;
在 MySQL8 中如何正确的配置远程访问用户,与 MySQL5.7 不同之处!
如果你只是想要把 root 用户允许远程访问,登录 mysql 后,直接执行以下命令,以下远程授权适用于MySQL8也适用于MySQL5.7。
mysql -uroot -p
mysql> use mysql;
mysql> update user set host = '%' where user ='root';
mysql> flush privileges;
mysql> exit
在MySQL5.7版本使用grant来进行用户授权
例:创建一个whsir用户,允许所有IP访问,密码是password:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'whsir'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
但是从 MySQL8 版本开始,不能再使用 GRANT 创建用户,而是要先 CREATE USER,然后使用GRANT 授权。上面的例子在 MySQL8 中的写法:
mysql> CREATE USER 'whsir'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
附:查看用户相关信息
mysql> use mysql;
mysql> select host,user,plugin from user;
总命令:
UPDATE mysql.user SET host='%' WHERE user='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'ThePassword';
FLUSH PRIVILEGES;
写一个简单的程序来访问该数据库,实现 show tables 功能:
- #include <mysql/mysql.h>
- #include <stdio.h>
- #include <stdlib.h>
- int main()
- {
- MYSQL *conn;
- MYSQL_RES *res;
- MYSQL_ROW row;
- char server[] = "localhost";
- char user[] = "root";
- char password[] = "mima";
- char database[] = "mysql";
-
- conn = mysql_init(NULL);
-
- if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0))
- {
- fprintf(stderr, "%s\n", mysql_error(conn));
- exit(1);
- }
-
- if (mysql_query(conn, "show tables"))
- {
- fprintf(stderr, "%s\n", mysql_error(conn));
- exit(1);
- }
-
- res = mysql_use_result(conn);
-
- printf("MySQL Tables in mysql database:\n");
-
- while ((row = mysql_fetch_row(res)) != NULL)
- {
- printf("%s \n", row[0]);
- }
-
- mysql_free_result(res);
- mysql_close(conn);
-
- printf("finish! \n");
- return 0;
- }
编译代码,并链接 mysql 的库,:g++ -Wall mysql_test.cpp -o mysql_test -lmsqlclient
然后运行编译好的代码:
可见结果和使用 SQL语句 show tables 是一样的。
格式: mysql -h主机地址 -u用户名 -p用户密码
1、连接到本机上的MYSQL
首先打开DOS窗口,然后进入目录mysql\bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密码。如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是: mysql>
2、连接到远程主机上的MYSQL
假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:
mysql -h110.110.110.110 -u root -p abcd123;(注:u与root之间可以不用加空格,其它也一样)
3、退出MYSQL命令: exit (回车)
mysql> use RUNOOB; // 选择要使用的数据库
mysql> SHOW DATABASES; // 显示所有数据库
mysql> SHOW TABLES; // 显示所有表
mysql> SHOW COLUMNS FROM runoob_tbl; // 显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
mysql> SHOW INDEX FROM runoob_tbl; // 显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
mysql> SHOW TABLE STATUS FROM RUNOOB; # 显示数据库 RUNOOB 中所有表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G; # 加上 \G,查询结果按列打印
MySQL命令包括 "连接数据库,修改密码,管理用户,操作数据库,操作数据表,数据库备份" 等。注意:你必须首先登录到 MySQL 中,
MySQL 客户端工具,可以自动补全,语法高亮:https://github.com/dbcli/mycli
mycli 是用 Python 写的,所以在各个平台下都可以通过pip安装。安装:pip install -U mycli 或者 sudo apt-get install mycli
mycli --help
Usage: mycli [OPTIONS] [DATABASE]
mysql 命令行工具,可以自动补全,语法高亮
示例:
- mycli my_database
- mycli -u my_user -h my_host.com my_database
- mycli mysql://my_user@my_host.com:3306/my_database
选项:
-h, --host TEXT 数据库地址.
-P, --port INTEGER 端口
-u, --user TEXT 连接到数据库的用户名
-S, --socket TEXT 用来连接的socket文件
-p, --password TEXT 连接到数据库的密码
--pass TEXT 连接到数据库的密码
--ssh-user TEXT User name to connect to ssh server.
--ssh-host TEXT Host name to connect to ssh server.
--ssh-port INTEGER Port to connect to ssh server.
--ssh-password TEXT Password to connect to ssh server.
--ssh-key-filename TEXT Private key filename (identify file) for the
ssh connection.
--ssh-config-path TEXT Path to ssh configuration.
--ssh-config-host TEXT Host to connect to ssh server reading from
ssh configuration.
--ssl Enable SSL for connection (automatically
enabled with other flags).
--ssl-ca PATH CA file in PEM format.
--ssl-capath TEXT CA directory.
--ssl-cert PATH X509 cert in PEM format.
--ssl-key PATH X509 key in PEM format.
--ssl-cipher TEXT SSL cipher to use.
--tls-version [TLSv1|TLSv1.1|TLSv1.2|TLSv1.3]
TLS protocol version for secure connection.
--ssl-verify-server-cert Verify server's "Common Name" in its cert
against hostname used when connecting. This
option is disabled by default.
-V, --version Output mycli's version.
-v, --verbose Verbose output.
-D, --database TEXT Database to use.
-d, --dsn TEXT Use DSN configured into the [alias_dsn]
section of myclirc file.
--list-dsn list of DSN configured into the [alias_dsn]
section of myclirc file.
--list-ssh-config list ssh configurations in the ssh config
(requires paramiko).
-R, --prompt TEXT Prompt format (Default: "\t \u@\h:\d> ").
-l, --logfile FILENAME Log every query and its results to a file.
--defaults-group-suffix TEXT Read MySQL config groups with the specified
suffix.
--defaults-file PATH Only read MySQL options from the given file.
--myclirc PATH Location of myclirc file.
--auto-vertical-output Automatically switch to vertical output mode
if the result is wider than the terminal
width.
-t, --table Display batch output in table format.
--csv Display batch output in CSV format.
--warn / --no-warn Warn before running a destructive query.
--local-infile BOOLEAN Enable/disable LOAD DATA LOCAL INFILE.
-g, --login-path TEXT Read this path from the login file.
-e, --execute TEXT Execute command and quit.
--init-command TEXT SQL statement to execute after connecting.
--charset TEXT Character set for MySQL session.
--password-file PATH File or FIFO path containing the password to
connect to the db if not specified
otherwise.
--help 帮助.
如果忘记 MySQL 密码,可以通过修改 my.cnf 文件添加 skip-grant-tables 来重置密码
打开 my.cnf 配置文件,找到 [mysqld] ,然后在该行下面添加以下参数:skip-grant-tables
重启 MySQL 服务:service mysql restart
登录 MySQL,此时不需要密码,直接回车:# mysql -u root -p
更改 root 密码 为 123456:
mysql> use mysql;
mysql> update user set authentication_string=password("123456") where user='root';
mysql> flush privileges; # 刷新权限
注意密码字段名 5.7 版本的是 authentication_string,之前的为 password。
修改完后,记得注释掉 my.cnf 中的 skip-grant-tables 参数,重启 MySQL 服务,就可以用你设置的密码登录了。
用 UPDATE 直接编辑 user 表
shell> mysql -u root -p
mysql> update user set password="xueok654123" where user='root';
// 在MySQL8.0版本后已经没有 password 或 password() 函数了。
// update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges // 刷新权限
mysql> use dbname; // 打开数据库:
mysql> show databases; // 显示所有数据库
mysql> show tables; // 显示数据库mysql中所有的表:先use mysql;然后
mysql> describe user; // 显示表mysql数据库中user表的列信息)
用 SET PASSWORD 命令
格式:mysql> set password for 用户名@localhost = password('新密码');
例子:mysql> set password for root@localhost = password('123');
使用 mysqladmin 修改密码
格式:mysqladmin -u用户名 -p旧密码 password 新密码
mysqladmin -u root password "new_password";
例子:mysqladmin -uroot -p123456 password 123
1、给root设置密码。
首先在DOS下进入目录mysql\bin,然后键入以下命令
mysqladmin -u root -password ab12 //设置 root 密码为 ab12
注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。
2、修改root密码。
mysqladmin -u root -p ab12 password djg345 // 修改 root 密码为 djg345
mysql -u root -p 时根据提示,无论尝试输入什么密码都报错。看网上大多的都是先在配置文件中加入 skip-grant-tables 参数,然后重启 mysql 服务,使得 mysql 能无密码登入,然后再使用命令来设置 mysql 密码,但是设置密码的这个命令中因为有 *password()*函数,结果在MySQL8.0版本后就会报错,因为这个版本下已经没有 password 或 password() 函数了。
方法 1:
8.0 以上版本修改密码使用:
ALTER user 'root'@'localhost' IDENTIFIED BY '你的密码';
示例:
mysql> ALTER user 'root'@'localhost' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;CREATE USER 'laowang'@'localhost' IDENTIFIED BY '123456';
方法 2:
解决方法:首先找到原始的mysql账户和密码,在 /etc/mysql/debian.cnf 文件中找到了对应信息:
然后使用改账户与密码登陆进 mysql: mysql -udebian-sys-maint -pJge19hvTiwgZJw9n
执行命令为 root 用户更改密码:ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
提示:将上面命令中的 password 单词换成自己的密码,另外,在输入新密码的时候,由于MySQL的规则,可能密码需要进行大小写混合组成。
mysql 8.0.12 版本包解压中没有 data 目录和 my-default.ini 文件以及服务无法启动的解决办法以及修改初始密码的方法。
1、没有 my-default.ini 文件,你可以在根目录下自己创建一个 my.ini 文件,具体内容如下:
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\web\mysql-8.0.11
#允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
2、如果设置 mysql 数据库的数据的存放目录:
不要自己新建 data 文件夹,由 mysql 自动生成 data 就好。以管理员的方式打开 cmd 命令窗口(直接打开 cmd 运行,可能会报错),并且进入到 mysql 安装目录的 bin 目录下。然后输入以下命令:
mysqld install
mysqld --initialize
net start mysql
最后就会生成 data 目录。
需要添加 MySQL 用户,在 mysql 数据库中的 user 表添加新用户即可。
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changedmysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
在添加用户时,请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 可以在以上实例看到用户密码加密后为: 6f8c114b58f2ce9e.
如果你不使用该命令,你就无法使用新创建的用户来连接mysql服务器,除非你重启mysql服务器。可以在创建用户时,为用户指定权限,在对应的权限列中,在插入语句中设置为 'Y' 即可,
用户权限列表如下:
另外一种添加用户的方法是通过SQL的 GRANT 命令,以下命令会给指定数据库TUTORIALS添加用户 zara ,密码为 zara123 。
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changedmysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123';命令会在mysql数据库中的user表创建一条用户信息记录。
格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"
示例:增加一个用户user_1密码为123,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以 root 用户连入 MySQL,
命令:
mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by "123";
增加的用户是十分危险的,如果知道了user_1的密码,那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了。
针对上面的问题,可以这样解决:增加一个用户user_2密码为123,让此用户只可以在localhost上登录,并可以对数据库aaa进行查询、插入、修改、删除的操作(localhost指本地主机,即MySQL数据库所在的那台主机),这样用户即使用知道user_2的密码,他也无法从网上直接访问数据库,只能通过MYSQL主机来操作aaa库。
mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123";
用新增的用户如果登录不了MySQL,在登录时用如下命令:
mysql -u user_1 -p-h 192.168.113.50(-h后跟的是要登录主机的ip地址)
// 用户:user01,密码:123456,这样就添加了一个新的用户,
GRANT USAGE ON *.* TO 'user01'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;//创建一个可以从任何地方连接服务器的一个完全的超级用户
mysql> grant all privileges on *.* to user@localhost identified by "password"增加新用户
格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY "password" WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY "password" WITH GRANT OPTION;格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
示例:增加一个用户user1密码为password1,让其可以在本机上登录,
并对所有数据库有查询、插入、修改、删除的权限。
首先用以root用户连入mysql,然后键入以下命令:
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
如果希望该用户能够在任何机器上登陆mysql,则将localhost改为"%"。
如果你不想user1有密码,可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";删除授权:
mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;
创建一个用户custom在特定客户端it363.com登录,可访问特定数据库fangchandb
mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’
重命名表:
mysql > alter table t1 rename t2;
注意:创建完成后需要执行 FLUSH PRIVILEGES 语句。
一、grant 普通数据用户,查询、插入、更新、删除 数据库中所有表数据的权利。
grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'
或者,用一条 MySQL 命令来替代:
grant select, insert, update, delete on testdb.* to common_user@'%'
二、grant 数据库开发人员,创建表、索引、视图、存储过程、函数。。。等权限。
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 外键权限。
grant references on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 临时表权限。
grant create temporary tables on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 索引权限。
grant index on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';
三、grant 普通 DBA 管理某个 MySQL 数据库的权限。
grant all privileges on testdb to dba@'localhost'
其中,关键字 privileges 可以省略。
四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。
grant all on *.* to dba@'localhost'
五、MySQL grant 权限,分别可以作用在多个层次上。
1. grant 作用在整个 MySQL 服务器上:
grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
2. grant 作用在单个数据库上:
grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
3. grant 作用在单个数据表上:
grant select, insert, update, delete on testdb.orders to dba@localhost;
这里在给一个用户授权多张表时,可以多次执行以上语句。例如:
grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
grant select on smp.mo_sms to mo_user@'%' identified by '123345';
4. grant 作用在表中的列上:
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
5. grant 作用在存储过程、函数上:
grant execute on procedure testdb.pr_add to 'dba'@'localhost'
grant execute on function testdb.fn_add to 'dba'@'localhost'
六、查看 MySQL 用户权限
查看当前用户(自己)权限:show grants;
查看其他 MySQL 用户权限:show grants for dba@localhost;
七、撤销已经赋予给 MySQL 用户权限的权限。
revoke 跟 grant 的语法差不多,只需要把关键字 to 换成 from 即可:
grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;
八、MySQL grant、revoke 用户权限注意事项
1. grant, revoke 用户权限后,该用户只有重新连接 MySQL 数据库,权限才能生效。
2. 如果想让授权的用户,也可以将这些权限 grant 给其他用户,需要选项 grant option
grant select on testdb.* to dba@localhost with grant option;
这个特性一般用不到。实际中,数据库权限最好由 DBA 来统一管理。
格式:grant 权限 on 数据库.表 to 用户名@登录主机 identified by "密码";
设置权限时必须给出一下信息
grant 和 revoke 可以在几个层次上控制访问权限
user 表中 host 列的值的意义
创建新用户并设置权限
例1:增加一个test1用户,密码为123456,可以在任何主机上登录,并对所有数据库有查询,增加,修改和删除的功能。需要在mysql的root用户下进行
mysql>grant select,insert,update,delete on *.* to test1@"%" identified by "123456";
mysql>flush privileges;
例2:增加一个test2用户,密码为123456,只能在192.168.2.12上登录,并对数据库student有查询,增加,修改和删除的功能。需要在mysql的root用户下进行
mysql>grant select,insert,update,delete on student.* to test2@192.168.2.12 identified by "123456";
mysql>flush privileges;
例3:授权用户test3拥有数据库student的所有权限
mysql>grant all privileges on student.* to test3@localhost identified by '123456';
mysql>flush privileges;
如果不想 test3 有密码,可以再打一个命令将密码消掉。
mysql>grant all privileges on student.* to test3@localhost identified by '';
mysql>flush privileges;
修改用户密码、删除用户、删除数据库和删除表、删除账户及权限
修改用户密码
mysql>update mysql.user set password=password("123456") where User=’test1′ and Host="localhost";
mysql>flush privileges;
删除用户
mysql>delete from user where user='test2' and host='localhost';
mysql>flush privileges;
删除数据库和删除表
mysql>drop database 数据库名;
mysql>drop table 表名;
删除账户及权限
drop user 用户名@'%'
drop user 用户名@localhost
MySQL添加新用户、为用户创建数据库、为新用户分配权限
登录MySQL
mysql -u root -p
添加新用户,允许本地 IP 访问 localhost, 127.0.0.1
create user 'test'@'localhost' identified by '123456';
允许外网 IP 访问
create user 'test'@'%' identified by '123456';
刷新授权
flush privileges;
为用户创建数据库
create database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
为新用户分配权限
授予用户通过外网IP对于该数据库的全部权限
grant all privileges on `testdb`.* to 'test'@'%' identified by '123456';
授予用户在本地服务器对该数据库的全部权限
grant all privileges on `testdb`.* to 'test'@'localhost' identified by '123456';
刷新权限
flush privileges;
用新帐号 test 重新登录,由于使用的是 % 任意IP连接,所以需要指定外部访问IP
mysql -u test -h 115.28.203.224 -p
在Ubuntu服务器下,MySQL默认是只允许本地登录,因此需要修改配置文件将地址绑定给注释掉:
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1 #注释掉这一行就可以远程登录了
示例:
1,Mysql下创建新的用户
语法:
1.create user 用户名 identified by '密码';
例:create user xiaogang identified by '123456';
新创建的用户,默认情况下是没有任何权限的。
2. 如何给用户分配权限
语法:
1.grant 权限 on 数据库.数据表 to '用户' @ '主机名';
例:给 xiaogang 分配所有的权限
grant all on *.* to 'xiaogang'@'%';
这个时候 xiaogang 就拥有了 所有权限了
3 如何更精准的控制用户的权限呢?
1.grant 权限 on 数据库.数据表 to '用户' @ '主机名';
例:让 xiaogang 有查询 tmp 数据库 tmp1 表的权限;
grant select on temp.temp1 to 'xiaogang'@'%'; //这个时候 xiaogang 就具有查询temp小的temp1的权限了。
例如:
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限,并设定口令为123。
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc所有表进行所有操作的权限,并设定口令为123。
mysql>grant all privileges on *.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对所有数据库的所有表进行所有操作的权限,并设定口令为123。
mysql>grant all privileges on *.* to joe@localhost identified by ‘123′;
给本机用户joe分配可对所有数据库的所有表进行所有操作的权限,并设定口令为123。
4. 如何收回 权限,一般指有root用户才具有该权限
语法:
1.revoke 权限 on 数据库.数据表 from '用户'@'主机名';
例:收回 xiaogang的所有权限
revoke all on *.* from 'xiaogang' @'%';
添加用户、授权 步骤总结:
第一步:mysql服务的启动和停止
net stop mysql
net start mysql
第二步:直接登陆mysql
语法如下: mysql -u用户名 -p用户密码
键入命令mysql -uroot -p, 回车后提示你输入密码,输入123456,然后回车即可进入到mysql中了,mysql的提示符是:
mysql>
注意,如果是连接到另外的机器上,则需要加入一个参数-h机器IP
第三步:增加新用户
格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
譬如,增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。
首先用以root用户连入mysql,然后键入以下命令:
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
如果希望该用户能够在任何机器上登陆mysql,则将localhost改为"%"。
如果你不想user1有密码,可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
第四步: 操作数据库
登录到mysql中,然后在mysql的提示符下运行下列命令,每个命令以分号结束
在windows中MySql以服务形式存在,在使用前应确保此服务已经启动,未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start"命令,注意启动者应具有管理员权限。刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户,这是很大的安全隐患。对于一些重要的应用我们应将安全性尽可能提高,在这里应把匿名帐户删除、 root帐户设置密码,可用如下命令进行:
use mysql;
delete from User where User="";
update User set Password=PASSWORD(newpassword) where User=root;
如果要对用户所用的登录终端进行限制,可以更新User表中相应用户的Host字段。改完重启 mysql 才能生效. ,此时登录时可用如下类似命令:
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
上面命令参数是常用参数的一部分,详细情况可参考文档。此处的mydb是要登录的数据库的名称。在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过User表来实现的,添加新用户常用的方法有两个: 一是在User表插入相应的数据行,同时设置相应的权限;二: 是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下:
grant all on mydb.* to NewUserName@HostName identified by "password" ;
grant usage on *.* to NewUserName@HostName identified by "password";
grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";
grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password";
grant all privileges on *.* to root@localhost
grant select,insert,delete,update,alter,create,drop on lybbs.* to NewUserName@"%" identified by "lybbs";
若要给此用户赋予他在相应对象上的权限的管理能力,可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户,Password字段应用PASSWORD 函数进行更新加密,以防不轨之人窃看密码。对于那些已经不用的用户应给予清除,权限过界的用户应及时回收权限,回收权限可以通过更新User表相应字段,也可以使用REVOKE操作。
常用权限的解释:
全局管理权限:
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表,刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限:
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
特别的权限:
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。
MySQL 有多个个权限? 很明显总共28个权限
mysql 有哪些权限?
数据库层面(db表)的权限分析
mysql 授权表共有5个表:user、db、host、tables_priv 和 columns_priv。
权限表的存取过程是:
以上权限分三组:
说明:
实例讲解:
-
- 只能在本地连接
- grant all on db_book.* to huaying@koowo.com identified by "yeelion"
-
- 允许从此域连接
- grant all on db_book.* to huaying@vpn.koowo.com identified by "yeeliong"
-
- 允许从任何主机连接 注:"%"字符起通配符作用,与like模式匹配的含义相同。
- grant all on db_book.* to huaying@% identified by "yeelion"
-
- 允许 huaying 从 koowo.com 域的任何主机连接
- grant all on db_book.* to huaying@%.koowo.com identified by "yeelion";
- grant all on db_book.* to huaying@192.168.1.189 identified by "yeelion"
- grant all on db_book.* to huaying@192.168.1.% identified by "yeelion"
- grant all on db_book.* to huaying@192.168.1.0/17 identified by "yeelion"
- 允许从单IP 段IP或一子网IP登陆
- 注:有时 用户@IP 需用引号 如"huaying@192.168.1.0/17″
- grant all on *.* to huaying@localhost identified by "yeelion" with grant option
-
- 添加超级用户 huaying 可在本地登陆做任何操作.
- grant reload on *.* to huaying@localhost identified by "yeelion" 只赋予reload权限
- grant all on db_book to huaying@koowo.com indetified by "yeelion" 所有权限
- grant select on db_book to huaying@% indetified by "yeelion" 只读权限
-
- 只有select,insert,delete,update的权限
- grant select,insert,delete,update on db_book to huaying@koowo.com indetified by "yeelion"
-
- grant select on db_book.storybook to huaying@localhost indetified by "yeelion" 只对表
- grant update (name) on db_book.storybook to huaying@localhost 只对表的name列 密码不变
- grant update (id,name,author) on db_book.storybook to huaying@localhost 只对表的多列
- grant all on book.* to ""@koowo.com 允许koowo.com域中的所有用户使用库book
-
- 允许huaying对库book所有表的管理员授权.
- grant all on book.* to huaying@%.koowo.com indetified by "yeelion" with grant option
2. 撤权并删除用户
revoke 的语法类似于 grant 语句,to 用 from 取代,没有 indetifed by 和 with grant option 子句。
如:revoke privileges (columns) on what from user
revoke 语句只删权限不删用户,撤销了所有权限后 user 表中用户记录保留,用户仍然可以连接服务器。
要完全删除一个用户必须用一条 delete 语句明确从 user 表中删除用户记录。
如:delete from user where user="huaying"
flush privileges; 重载授权表
注:使用grant和revoke语句时,表自动重载,而你直接修改授权表时不是.
实例:
- 1.创建数据库
- CREATE DATABASE `fypay` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-
- 2.为创建的数据库增加用户fypay
- grant create,select,insert,update,delete,drop,alter on fypay.* to fypay@"%" identified by "testfpay";
-
- 3.删除fypay用户
- delete from user where user="fypay"
- drop user fypay@localhost
-
- 4.刷新数据库
- flush privileges;
MySQL 赋予用户权限命令的简单格式可概括为:grant 权限 on 数据库对象 to 用户
一、grant 普通数据用户,查询、插入、更新、删除 数据库中所有表数据的权利。
grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'
或者,用一条 MySQL 命令来替代:
grant select, insert, update, delete on testdb.* to common_user@'%'
二、grant 数据库开发人员,创建表、索引、视图、存储过程、函数。。。等权限。
grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';
grant references on testdb.* to developer@'192.168.0.%'; grant 操作 MySQL 外键权限。
grant create temporary tables on testdb.* to developer@'192.168.0.%'; grant 操作 MySQL 临时表权限。
grant index on testdb.* to developer@'192.168.0.%'; grant 操作 MySQL 索引权限。
grant create view on testdb.* to developer@'192.168.0.%'; grant 操作 MySQL 视图、查看视图源代码 权限。
grant show view on testdb.* to developer@'192.168.0.%';
grant 操作 MySQL 存储过程、函数 权限。
grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';
三、grant 普通 DBA 管理某个 MySQL 数据库的权限。
grant all privileges on testdb to dba@'localhost'; 其中,关键字 “privileges” 可以省略。
四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。
grant all on *.* to dba@'localhost'
五、MySQL grant 权限,分别可以作用在多个层次上。
1. grant 作用在整个 MySQL 服务器上:
grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
2. grant 作用在单个数据库上:
grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
3. grant 作用在单个数据表上:
grant select, insert, update, delete on testdb.orders to dba@localhost;
这里在给一个用户授权多张表时,可以多次执行以上语句。例如:
grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
grant select on smp.mo_sms to mo_user@'%' identified by '123345';
4. grant 作用在表中的列上:
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
5. grant 作用在存储过程、函数上:
grant execute on procedure testdb.pr_add to 'dba'@'localhost'
grant execute on function testdb.fn_add to 'dba'@'localhost'
六、查看 MySQL 用户权限
查看当前用户(自己)权限:
show grants;
查看其他 MySQL 用户权限:
show grants for dba@localhost;
七、撤销已经赋予给 MySQL 用户权限的权限。
revoke 跟 grant 的语法差不多,只需要把关键字 “to” 换成 “from” 即可:
grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;
八、MySQL grant、revoke 用户权限注意事项
1. grant, revoke 用户权限后,该用户只有重新连接 MySQL 数据库,权限才能生效。
2. 如果想让授权的用户,也可以将这些权限 grant 给其他用户,需要选项 “grant option“
grant select on testdb.* to dba@localhost with grant option;
这个特性一般用不到。实际中,数据库权限最好由 DBA 来统一管理。
安全配置需要的常用命令
新建一个用户并给予相应数据库的权限
grant select,insert,update,delete,create,drop privileges on database.* to user@localhost identified by 'passwd';
grant all privileges on database.* to user@localhost identified by 'passwd';
刷新权限
flush privileges;
显示授权
show grants;
移除授权
revoke delete on *.* from 'jack'@'localhost';
删除用户
drop user 'jack'@'localhost';
给用户改名
rename user 'jack'@'%' to 'jim'@'%';
给用户改密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
删除数据库
drop database test;
从数据库导出文件
select * from a into outfile "~/abc.sql"
1.导出整个数据库
导出文件默认是存在mysql\bin目录下
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql
3.导出一个数据库结构
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
4.带语言参数导出
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql
例如,将aaa库备份到文件back_aaa中:
[root@test1 root]# cd /home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa
用 mysqldump 命令来备份数据库
c:/mysql/bin/>mysqldump –u root –p 3306 mysql>d:/backup.sql
执行此语句将把mydb 备份到D盘的backup.sql文件中备份多个数据库表
c:/mysql/bin/>mysqldump –u root –p 3306 school user01 user >d:/backup.sql
此句的意思是把school库中的user01表和user表的内容和表的定义备份到D盘backup.sql文件中。备份所有的数据库
c:/myql/bin>mysqldump –u root –p 3306 –all –database>d:backup.sql
还原Mysql数据库
c:/mysql/bin/mysql –u root –p 3306 school
还原其中的一个表
mysql> source d:/books.sql;
备份数据库
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql恢复数据库
shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql如果只想卸出建表指令,则命令如下:
shell> mysqladmin -u root -p -d databasename > a.sql如果只想卸出插入数据的sql命令,而不需要建表命令,则命令如下:
shell> mysqladmin -u root -p -t databasename > a.sql那么如果我只想要数据,而不想要什么sql命令时,应该如何操作呢?
mysqldump -T./ phptest driver其中,只有指定了-T参数才可以卸出纯文本文件,表示卸出数据的目录,
./表示当前目录,即与mysqldump同一目录。
如果不指定driver 表,则将卸出整个数据库的数据。
每个表会生成两个文件,一个为.sql文件,包含建表执行。
另一个为.txt文件,只包含数据,且没有sql指令。可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。
可利用外壳程序键入重定向实用程序来完成这项工作。
例如,如果在文件my_file.sql 中存放有查询,可如下执行这些查询:
例如,如果您想将建表语句提前写在sql.txt中:
mysql > mysql -h myhost -u root -p database < sql.txt
备份数据库:
mysqldump -h host -u root -p dbname >dbname_backup.sql
恢复数据库:
mysqladmin -h myhost -u root -p create dbname
mysqldump -h host -u root -p dbname < dbname_backup.sql
如果只想卸出建表指令:
mysqladmin -u root -p -d databasename > a.sql
如果只想卸出插入数据的sql命令,而不需要建表命令:
mysqladmin -u root -p -t databasename > a.sql
那么如果我只想要数据,而不想要什么sql命令时:
mysqldump -T./ phptest driver
其中,只有指定了-T参数才可以卸出纯文本文件,表示卸出数据的目录,
./表示当前目录,即与mysqldump同一目录。
如果不指定 driver 表,则将卸出整个数据库的数据。
每个表会生成两个文件,
一个为.sql文件,包含建表执行。
另一个为.txt文件,只包含数据,且没有sql指令。
c:\mysql\bin\mysqldump -uroot -proot staffer > e:\staffer.sql
备份数据库 staffer 得到的 staffer.sql 是一个sql脚本,不包括建库的语句,
所以需要手工创建一个空库 staffer,才可以导入恢复数据库staffer。
c:\mysql\bin\mysql -uroot -proot staffer < staffer.sql
如果不希望后来手工创建 staffer 可以
c:\mysql\bin\mysqldump -uroot -proot --databases staffer > e:\staffer.sql
mysql -uroot -proot >e:\staffer.sql
但这样的话系统种就不能存在staffer库,且无法导入其他名字的数据库,
当然你可以手工修改staffer.sql文件
mysqldump -u root -p test>c:\test.txt 备份数据库:(将数据库test备份)
mysqldump -u root -p test mytable>c:\test.txt 备份表格:(备份test数据库下的mytable表格)
mysql -u root -p test < c:\test.txt 将备份数据导入到数据库:(导回test数据库)
将文本数据导入数据库。文本数据的字段数据之间用tab键隔开。
use test;
load data local infile "文件名" into table 表名;
用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;
导入.sql文件命令(例如D:/mysql.sql)
mysql> use database;
mysql> source d:/mysql.sql;
导出数据:
把数据库dbname导出到文件mysql.dbname中。后者是一个文本文件
mysqldump -u root -p123456 --databases dbname > mysql.dbname
mysqldump --opt school>school.bbb (命令在DOS的\mysql\bin目录下执行)
注释: 将数据库school备份到school.bbb文件,school.bbb是一个文本文件,文件名任取,打开看看你会有新发现。
导入数据:
mysqlimport -u root -p123456 < mysql.dbname。
在MySQL中,你可以使用不同的数据类型来存储各种类型的数据。以下是MySQL支持的主要数据类型和它们的长度或大小范围:
整数类型
TINYINT
: 1 字节 (-128 to 127 或 0 to 255 无符号)SMALLINT
: 2 字节 (-32,768 to 32,767 或 0 to 65,535 无符号)MEDIUMINT
: 3 字节 (-8,388,608 to 8,388,607 或 0 to 16,777,215 无符号)INT
或 INTEGER
: 4 字节 (-2,147,483,648 to 2,147,483,647 或 0 to 4,294,967,295 无符号)BIGINT
: 8 字节 (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 或 0 to 18,446,744,073,709,551,615 无符号)浮点与定点类型
FLOAT
: 4 字节 (具体范围依赖于硬件)DOUBLE
: 8 字节 (具体范围依赖于硬件)DECIMAL
(NUMERIC
): 可变(M
和 D
指定), M
是数字总位数,D
是小数点后位数DATE
: 'YYYY-MM-DD' (范围: '1000-01-01' 到 '9999-12-31')DATETIME
: 'YYYY-MM-DD HH:MM:SS' (范围: '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59')TIMESTAMP
: Unix 时间戳 ('1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC)TIME
: 'HH:MM:SS' (范围: '-838:59:59' 到 '838:59:59')YEAR
: 年份 (范围: 1901 到 2155)CHAR
: 定长字符串 (0 到 255 字符)VARCHAR
: 变长字符串 (0 则65535 字符)BLOB
: 二进制形式的长文本数据
TINYBLOB
: 最大长度为 255 字节BLOB
: 最大长度为 65,535 字节MEDIUMBLOB
: 最大长度为 16,777,215 字节LONGBLOB
: 最大长度为 4,294,967,295 字节TEXT
: 非二进制形式的长文本数据
TINYTEXT
: 最大长度 255 字符TEXT
: 最大长度 65,535 字符MEDIUMTEXT
: 最大长度 16,777,215 字符LONGTEXT
: 最大长度 4,294,967,295 字符BINARY
: 定长二进制字符串 (0 到 255 字节)VARBINARY
: 变长二进制字符串 (0 则65,535 字节)ENUM
: 枚举,一个字符串对象,只能有一个值,列在可能值的列表中SET
: 字符串对象,可以有零个或多个值,每个值列在可能值的列表中GEOMETRY
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
JSON
: 用于存储 JSON 文档的类型请注意,MySQL 数据类型的存储需求和长度限制也可能受到 Storage Engine 的限制,并且还可能依赖于数据库的配置和表的属性。在设计数据库时,应该选择最合适的类型以满足数据的需求,并考虑到效率和存储空间的优化。
:https://blog.csdn.net/qq_37054881/article/details/90023611
MySQL在5.5.3之后增加了这个 utf8mb4 的编码,mb4 就是 most bytes 4 的意思,专门用来兼容四字节的 unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。
可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字。
:https://www.sjkjc.com/mysql/sample-database/
1)创建数据库staffer
mysql> create database staffer
2)创建表staffer,department,position,depart_pos
create table s_position
(
id int not null auto_increment,
name varchar(20) not null default '经理', #设定默认值
description varchar(100),
primary key PK_positon (id) #设定主键
);
create table department
(
id int not null auto_increment,
name varchar(20) not null default '系统部', #设定默认值
description varchar(100),
primary key PK_department (id) #设定主键
);
create table depart_pos
(
department_id int not null,
position_id int not null,
primary key PK_depart_pos (department_id,position_id) #设定复和主键
);
create table staffer
(
id int not null auto_increment primary key, #设定主键
name varchar(20) not null default '无名氏', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id) #设定唯一值
);drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '深圳',
year date
); //建表结束//以下为插入字段
insert into teacher values('','glchengang','深圳一中','1976-10-10');
insert into teacher values('','jack','深圳一中','1975-12-23');
注:在建表中
(1)将ID设为长度为3的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key
(2)将NAME设为长度为10的字符字段
(3)将ADDRESS设为长度50的字符字段,而且缺省值为深圳。varchar和char有什么区别呢,只有等以后的文章再说了。
(4)将YEAR设为日期字段。
如果你在mysql提示符键入上面的命令也可以,但不方便调试。
你可以将以上命令原样写入一个文本文件中假设为school.sql,然后复制到c:\下,并在DOS状态进入目录\mysql\bin,然后键入以下命令:
mysql -uroot -p密码 < c:\school.sql
如果成功,空出一行无任何显示;如有错误,会有提示。
增加一个字段:alter table tabelName add column fieldName dateType;
增加多个字段:alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
mysql>
#表position增加列test
alter table position add(test char(10));
#表position修改列test
alter table position modify test char(20) not null;
#表position修改列test默认值
alter table position alter test set default 'system';
#表position去掉test默认值
alter table position alter test drop default;
#表position去掉列test
alter table position drop column test;
#表depart_pos删除主键
alter table depart_pos drop primary key;
#表depart_pos增加主键
alter table depart_pos add primary key PK_depart_pos (department_id,position_id);
#插入表 department
insert into department(name,description) values('系统部','系统部');
insert into department(name,description) values('公关部','公关部');
insert into department(name,description) values('客服部','客服部');
insert into department(name,description) values('财务部','财务部');
insert into department(name,description) values('测试部','测试部');
#插入表s_position
insert into s_position(name,description) values('总监','总监');
insert into s_position(name,description) values('经理','经理');
insert into s_position(name,description) values('普通员工','普通员工');
#插入表depart_pos
insert into depart_pos(department_id,position_id)
select a.id department_id,b.id postion_id
from department a,s_position b;
#插入表staffer
insert into staffer(name,department_id,position_id) values('陈达治',1,1);
insert into staffer(name,department_id,position_id) values('李文宾',1,2);
insert into staffer(name,department_id,position_id) values('马佳',1,3);
insert into staffer(name,department_id,position_id) values('亢志强',5,1);
insert into staffer(name,department_id,position_id) values('杨玉茹',4,1);
查询及删除操作
#显示系统部的人员和职位
select a.name,b.name department_name,c.name position_name
from staffer a,department b,s_position c
where a.department_id=b.id and a.position_id=c.id and b.name='系统部';
#显示系统部的人数
select count(*) from staffer a,department b
where a.department_id=b.id and b.name='系统部'
#显示各部门的人数
select count(*) cou,b.name
from staffer a,department b
where a.department_id=b.id
group by b.name;
#删除客服部
delete from department where name='客服部';
#将财务部修改为财务一部
update department set name='财务一部' where name='财务部';
命令:create database <数据库名>
注意:创建数据库之前要先连接Mysql服务器
例1:建立一个名为xhkdb的数据库
mysql> create database xhkdb;
例2:创建数据库并分配用户
1. create database 数据库名;
2. grant select,insert,update,delete,create,drop,alter on 数据库名.* to 数据库名@localhost identified by '密码';
3. set password for '数据库名'@'localhost' = old_password('密码');
依次执行3个命令完成数据库创建。注意:命令中的 “密码”和“数据库”字段是需要自己设置的。
drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ‘深圳’,
year date
); //建表结束
//以下为插入字段
insert into teacher values(”,’allen’,'大连一中’,'1976-10-10′);
insert into teacher values(”,’jack’,'大连二中’,'1975-12-23′);
如果你在 mysql 提示符键入上面的命令也可以,但不方便调试。
1、你可以将以上命令原样写入一个文本文件中,假设为school.sql,然后复制到c:\\下,然后键入以下命令:mysql -uroot -p密码 < c:\\school.sql
如果成功,空出一行无任何显示;如有错误,会有提示。(以上命令已经调试,你只要将//的注释去掉即可使用)。
2、或者进入命令行后使用 mysql> source c:\\school.sql; 也可以将school.sql文件导入数据库中。
drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ''深圳'',
year date
); //建表结束
//以下为插入字段
insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');
insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');
注:在建表中
1、将ID设为长度为3的数字字段:int(3);并让它每个记录自动加一:auto_increment;并不能为空:not null;而且让他成为主字段primary key。
2、将NAME设为长度为10的字符字段
3、将ADDRESS设为长度50的字符字段,而且缺省值为深圳。
4、将YEAR设为日期字段。
命令:show databases (注意:最后有个s)
mysql> show databases;
注意:为了不再显示的时候乱码,要修改数据库默认编码。以下以GBK编码页面为例进行说明:
1、修改MYSQL的配置文件:my.ini里面修改default-character-set=gbk
2、代码运行时修改:
①Java代码:jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk
②PHP代码:header("Content-Type:text/html;charset=gb2312");
③C语言代码:int mysql_set_character_set( MYSQL * mysql, char * csname);
该函数用于为当前连接设置默认的字符集。字符串csname指定了1个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与SET NAMES语句类似,但它还能设置mysql- > charset的值,从而影响了由mysql_real_escape_string() 设置的字符集。
命令:drop database <数据库名>
例如:删除名为 xhkdb的数据库
mysql> drop database xhkdb;
例子1:删除一个已经确定存在的数据库
mysql> drop database drop_database;
Query OK, 0 rows affected (0.00 sec)
例子2:删除一个不确定存在的数据库
mysql> drop database drop_database;
ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
//发生错误,不能删除'drop_database'数据库,该数据库不存在。
mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)//产生一个警告说明此数据库不存在
mysql> create database drop_database;
Query OK, 1 row affected (0.00 sec)
mysql> drop database if exists drop_database;//if exists 判断数据库是否存在,不存在也不产生错误
Query OK, 0 rows affected (0.00 sec)
use:使用数据库
命令: use <数据库名>
例如:如果xhkdb数据库存在,尝试存取它:
mysql> use xhkdb;
屏幕提示:Database changed
use 语句可以通告MySQL把db_name数据库作为默认(当前)数据库使用,用于后续语句。该数据库保持为默认数据库,直到语段的结尾,或者直到发布一个不同的USE语句:
mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable
使用USE语句为一个特定的当前的数据库做标记,不会阻碍您访问其它数据库中的表。下面的例子可以从db1数据库访问作者表,并从db2数据库访问编辑表:
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
-> WHERE author.editor_id = db2.editor.editor_id;
USE语句被设立出来,用于与Sybase相兼容。
show databases 可以查询所有数据库,如果想跳到其他数据库,用 "use 其他数据库名字"命令 就可以跳到其他数据库。
命令:mysql> select database();
MySQL中SELECT命令类似于其他编程语言里的print或者write,你可以用它来显示一个字符串、数字、数学表达式的结果等等。
如何使用MySQL中SELECT命令的特殊功能?
1.显示MYSQL的版本
mysql> select version();
2. 显示当前时间
mysql> select now();
3. 显示年月日
SELECT DAYOFMONTH(CURRENT_DATE);
SELECT MONTH(CURRENT_DATE);
SELECT YEAR(CURRENT_DATE);
4. 显示字符串
mysql> SELECT "welecome to my blog!";
5. 当计算器用
select ((4 * 4) / 10 ) + 25;
6. 拼接字符串
select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';
注意:这里用到CONCAT()函数,用来把字符串串接起来。另外,我们还用到以前学到的AS给结果列'CONCAT(f_name, " ", l_name)'起了个假名。
create table:创建表
命令:create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);
例如,建立一个名为MyClass的表,
字段名 | 数字类型 | 数据宽度 | 是否为空 | 是否主键 | 自动增加 | 默认值 |
id | int | 4 | 否 | primary key | auto_increment | |
name | char | 20 | 否 | |||
sex | int | 4 | 否 | 0 | ||
degree | double | 16 | 是 |
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
desc 命令用于获取数据表结构。
desc命令格式:desc 表名;
同样 show columns from 表名; 也能获取数据表结构。
举例如下:
mysql> desc MyClass;
mysql> show columns from MyClass;
使用 MySQL 数据库 desc 表名时,我们看到 Key 那一栏,可能会有4种值,即 ' ','PRI','UNI','MUL'。
如果对于一个列的定义,同时满足上述4种情况的多种,比如一个列既是PRI,又是UNI,那么"desc 表名"的时候,显示的Key值按照优先级来显,PRI->UNI->MUL。那么此时,显示PRI。
一个唯一性索引列可以显示为PRI,并且该列不能含有空值,同时该表没有主键。
一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引,因为虽然索引的多列组合是唯一的,比如ID+NAME是唯一的,但是没一个单独的列依然可以有重复的值,只要ID+NAME是唯一的即可。
命令:drop table <表名>
例如:删除表名为 MyClass 的表
mysql> drop table MyClass;
drop table 用于取消一个或多个表。您必须有每个表的 drop 权限。所有的表数据和表定义会被取消,所以使用本语句要小心!
注意:对于一个带分区的表,drop table 会永久性地取消表定义,取消各分区,并取消储存在这些分区中的所有数据。drop table 还会取消与被取消的表有关联的分区定义(.par)文件。
对与不存在的表,使用IF EXISTS用于防止错误发生。当使用IF EXISTS时,对于每个不存在的表,会生成一个NOTE。
RESTRICT和CASCADE可以使分区更容易。目前,RESTRICT和CASCADE不起作用。
命令:insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]
例如:往表 MyClass中插入二条记录, 这二条记录表示:编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99, 编号为3 的名为Wang 的成绩为96.5。
mysql> insert into MyClass(id,name,degree) values(1,'Tom',96.45),(2,'Joan',82.99), (3,'Wang', 96.59);
注意:insert into 每次只能向表中插入一条记录。
也可以全部写在一行
insert into 语句的三种写法
方式 3.1、 INSERT INTO t2(field1,field2) SELECT col1,col2 FROM t1 WHERE ……这里简单说一下,由于可以指定插入到talbe2中的列,以及可以通过相对较复杂的查询语句进行数据源获取,可能使用起来会更加的灵活一些,但我们也必须注意,我们在指定目标表的列时,一定要将所有非空列都填上,否则将无法进行数据插入,还有一点比较容易出错的地方就是,当我们写成如下简写格式:
方式 3.2、 INSERT INTO t2 SELECT id, name, address FROM t1
此时,我们如果略掉了目标表的列的话,则默认会对目标表的全部列进行数据插入,且SELECT后面的列的顺序 必须和目标表中的列的定义顺序完全一致 才能完成正确的数据插入,这是一个很容易被忽略的地方,值得注意。
1)、查询所有行
命令: select <字段1,字段2,...> from < 表名 > where < 表达式 >
例如:查看表 MyClass 中所有数据
mysql> select * from MyClass;
2)、查询前几行数据
例如:查看表 MyClass 中前2行数据
mysql> select * from MyClass order by id limit 0,2;
select一般配合where使用,以查询更精确更复杂的数据。
命令:delete from 表名 where 表达式
例如:删除表 MyClass中编号为1 的记录
mysql> delete from MyClass where id=1;
下面是一个删除数据前后表的对比。
FirstName | LastName | Age |
---|---|---|
Peter | Griffin | 35 |
Glenn | Quagmire | 33 |
下面以PHP代码为例删除 "Persons" 表中所有 LastName='Griffin' 的记录:
- <?php
- $con = mysql_connect("localhost","peter","abc123");
- if (!$con)
- {
- die('Could not connect: ' . mysql_error());
- }
- mysql_select_db("my_db", $con);
- mysql_query("DELETE FROM Persons WHERE LastName='Griffin'"); mysql_close($con);
- ?>
在这次删除之后,表是这样的:
FirstName | LastName | Age |
---|---|---|
Glenn | Quagmire | 33 |
语法:update 表名 set 字段=新值,… where 条件
mysql> update MyClass set name='Mary' where id=1;
例子1:单表的MySQL UPDATE语句:
update [low_prioriity] [ignore] tbl_name set col_name1=expr1 [, col_name2=expr2 ...] [where where_definition] [order by ...] [limit row_count]
例子2:多表的UPDATE语句:
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有的行。
如果指定了ORDER BY子句,则按照被指定的顺序对行进行更新。
LIMIT子句用于给定一个限值,限制可以被更新的行的数目。
要删除表中的列
命令:ALTER TABLE table_name DROP COLUMN column_name
加索引
mysql> alter table 表名 add index 索引名 (字段名1[,字段名2 …]);
例子: mysql> alter table employee add index emp_name (name);
加主关键字的索引
mysql> alter table 表名 add primary key (字段名);
例子: mysql> alter table employee add primary key(id);
加唯一限制条件的索引
mysql> alter table 表名 add unique 索引名 (字段名);
例子: mysql> alter table employee add unique emp_name2(cardnumber);
删除某个索引
mysql> alter table 表名 drop index 索引名;
例子: mysql>alter table employee drop index emp_name;
增加字段:
命令:alter table table_name add field_name field_type 其他;
例如:在表MyClass中添加了一个字段passtest,类型为int(4),默认值为0
mysql> alter table MyClass add passtest int(4) default '0'
修改原字段名称及类型:
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段:
命令:ALTER TABLE table_name DROP field_name;
命令:rename table 原表名 to 新表名;
例如:在表MyClass名字更改为YouClass
mysql> rename table MyClass to YouClass;
当你执行 RENAME 时,你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限,以及对新表的 CREATE 和 INSERT 权限。
如果在多表更名中,MySQL 遭遇到任何错误,它将对所有被更名的表进行倒退更名,将每件事物退回到最初状态。
RENAME TABLE 在 MySQL 3.23.23 中被加入。
或者 使用下面这个命令重命名
mysql > alter table t1 rename t2;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。