说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.注意：%为任意远程主机，localhost和127.0.0.1权限不一样例子: CREATE USER ‘dog‘@‘localhost‘ IDENTIFIED BY ‘123456‘; CREATE USER ‘pig‘@‘192.168.1.101_‘ IDENDIFIED BY ‘123456‘; CREATE USER ‘pig‘@‘%‘ IDENTIFIED BY ‘123456‘; CREATE USER ‘pig‘@‘%‘ IDENTIFIED BY ‘‘; CREATE USER ‘pig‘@‘%‘;
授权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;
设置与更改用户密码SET PASSWORD FOR ‘username‘@‘host‘ = PASSWORD(‘newpassword‘);如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword"); 例子: SET PASSWORD FOR ‘pig‘@‘%‘ = PASSWORD("123456");
撤销用户权限REVOKE privilege ON databasename.tablename FROM ‘username‘@‘host‘; 注意: 假如你在给用户‘pig‘@‘%‘授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO ‘pig‘@‘%‘, 则在使用REVOKE SELECT ON *.* FROM ‘pig‘@‘%‘;命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON *.* TO ‘pig‘@‘%‘;则REVOKE SELECT ON test.user FROM ‘pig‘@‘%‘;命令也不能撤销该用户对test数据库中user表的Select 权限.
删除用户：DROP USER ‘username‘@‘host‘;
重新载入授权表：FLUSH PRIVILEGES;
在 mysql 数据库中的 user 表添加新用户：INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES (‘localhost‘, ‘guest‘, PASSWORD(‘guest123‘), ‘Y‘, ‘Y‘, ‘Y‘);注意：在 MySQL5.7 中 user 表的 password 已换成了authentication_string
通过GRANT命令添加用户并添加权限：mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON TUTORIALS.* -> TO ‘zara‘@‘localhost‘ -> IDENTIFIED BY ‘zara123‘;
示例：mysql > create database example default character set utf8;mysql > create user ‘server‘@‘%‘ identified by ‘example‘;mysql > grant all on example.* to ‘server‘@‘%‘ identified by ‘example‘;mysql > use example;mysql > CREATE TABLE `User` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `userName` varchar(50) NOT NULL DEFAULT ‘‘, `userPassword` varchar(50) NOT NULL DEFAULT ‘‘, `userDesc` varchar(100) NOT NULL DEFAULT ‘‘, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql > INSERT INTO `User` (`userName`, `userPassword`, `userDesc`) VALUES (‘test_user‘, ‘test_password‘, ‘Test user for server example‘);
//管理MySQL的常用命令mysqladmin -u root -p create <database>; //创建数据库mysqladmin -u root -p drop <database>; //删除数据库CREATE database example default character set utf8; //创建数据库USE <数据库名>; //选择要操作的数据库，以后的MySQL命令都只针对该数据库SHOW DATABASES; //列出MySQL数据库管理系统所管理的数据库列表SHOW TABLES; //列出指定数据库中的所有表，需要先使用use 数据库SHOW INDEX FROM <数据表>; //显示数据表的详细索引信息，包括PRIMARY KEY（主键）SHOW COLUMNS FROM <数据表>; //显示数据表的详细列头信息SHOW TABLE STATUS FROM <数据库> LIKE ‘需要匹配的字符串‘\G; //该命令将输出Mysql数据库管理系统的性能及统计信息CREATE TABLE table_name (column_name column_type); //创建数据表SHOW PROCESSLIST; //显示数据库连接线程列表SHOW CREATE TABLE table_name; //显示表信息SHOW VARIABLES LIKE ‘%character%‘; //显示字符集编码信息实例：mysql> CREATE TABLE runoob_tbl(-> runoob_id INT NOT NULL AUTO_INCREMENT,-> runoob_title VARCHAR(100) NOT NULL,-> runoob_author VARCHAR(40) NOT NULL,-> submission_date DATE,-> PRIMARY KEY ( runoob_id )-> ) ENGINE=InnoDB DEFAULT CHARSET = utf8;Query OK, 0 rows affected (0.16 sec)
DROP TABLE table_name; //删除数据表INSERT INTO table_name (field1, field2, ... fieldN) VALUES (value1, value2, ...valueN); //插入数据SELECT column_name, column_name FROM table_name [WHERE Clause] [OFFSET M] [LIMIT N]; //查询数据UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]; //更新数据DELETE FROM table_name [WHERE Clause]; //删除数据SELECT field1, field2, ... fieldN table_name1, table_name2,... table_nameN WHERE field1 LIKE condition1; //where从句中like和%的使用SELECT field1, field2, ...fieldN table_name1, table_name2, ...table_nameN ORDER BY field1, [field2...] ASC [DESC]//排序set names utf8; //设置为utf8编码SET sql_mode=‘NO_AUTO_VALUE_ON_ZERO‘; //设置AUTO_INCREMENT的值从0开始//GROUP BY语法，在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
实例：mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
mysql> SELECT coalesce(name, ‘总数‘), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
//MySQL连接的使用//使用INNER JOIN例：SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;等价于：SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;//使用LEFT JOIN例：SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;//使用RIGHT JOIN例：SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;//使用NULL必须使用IS NULL，或IS NOT NULL，或<=>用于比较两个NULL
//MySQL正则表达式MySQL中使用 REGEXP 操作符来进行正则表达式匹配。| 模式 | 描述 |
|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n‘ 或 ‘\r‘ 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n‘ 或 ‘\r‘ 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 ‘\n‘ 在内的任何字符，请使用象 ‘[.\n]‘ 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]‘ 可以匹配 "plain" 中的 ‘a‘。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]‘ 可以匹配 "plain" 中的‘p‘。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，‘z|food‘ 能匹配 "z" 或 "food"。‘(z|f)ood‘ 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，‘zo+‘ 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}‘ 不能匹配 "Bob" 中的 ‘o‘，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
//MySQL事务1，开始一个事务start transaction2, 做保存点savepoint 保存点名称3, 操作4，可以回滚，可以提交，没有问题，就提交，有问题就回滚。例：SET AUTOCOMMIT = 0; //设置为不自动提交，因为MYSQL默认立即执行BEGIN; //开始事务定义...; //操作ROLLBACK; //有问题回滚COMMIT; //成功则执行事务
//MySQL的ALTER命令//添加删除表字段ALTER TABLE testalter_tbl DROP i; //删除i字段，不能删除最后一个字段ALTER TABLE testalter_tbl ADD i INT; //增加i字段并定义类型ALTER TABLE testalter_tbl ADD i INT FIRST; //使用MySQL提供的关键字 FIRST (设定位第一列)ALTER TABLE testalter_tbl ADD i INT AFTER c; //AFTER 字段名（设定位于某个字段之后）//修改字段类型和名称ALTER TABLE testalter_tbl MODIFY c CHAR(10); //把字段 c 的类型从 CHAR(1) 改为 CHAR(10)ALTER TABLE testalter_tbl CHANGE i j BIGINT; //把字段i名称改为j，并将类型改为BIGINTALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100; //指定字段 j 为 NOT NULL 且默认值为100，若不指定默认值为NULL//修改字段默认值ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; //使用 ALTER 来修改字段的默认值ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; //使用 ALTER 命令及 DROP子句来删除字段的默认值//修改表名 ALTER TABLE testalter_tbl RENAME TO alter_tbl; //将数据表 testalter_tbl 重命名为 alter_tbl
//MySQL创建索引//普通索引CREATE INDEX indexName ON tableName(columnName(length)); //创建索引，方法1ALTER TABLE tableName ADD INDEX indexName(columnName(length)); //修改表结构，方法2CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length))); //创建表时直接指定，方法3DROP INDEX indexName ON tableName; //删除索引//唯一索引ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); //添加一个主键，要确保主键不为NULLCREATE UNIQUE INDEX indexName ON mytable(username(length)); //创建索引，方法1ALTER table mytable ADD UNIQUE [indexName] (username(length)); //修改表结构，方法2CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length))); //创建表时指定，方法3 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); //添加全文索引ALTER TABLE tbl_name DROP INDEX (column_list); //删除索引ALTER TABLE tbl_name DROP PRIMARY KEY; //删除主键SHOW INDEX FROM tableName\G; //显示索引信息
//MySQL临时表CREATE TEMPORARY TABLE tableName (...); //创建临时表删除临时表与删除其他表一样，但show tables不能显示临时表
//MySQL复制表SHOW CREATE TABLE runoob_tbl \G; //获取创建数据表(CREATE TABLE) 语句复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。INSERT INTO ... SELECT... 语句来实现复制表的内容mysql> INSERT INTO clone_tbl (runoob_id,-> runoob_title,-> runoob_author,-> submission_date)-> SELECT runoob_id,runoob_title,-> runoob_author,submission_date
-> FROM runoob_tbl;
//获取服务器元数据SELECT VERSION(); //服务器版本信息SELECT DATABASE(); //当前数据库名SELECT USER(); //当前用户名SHOW STATUS; //服务器状态SHOW VARIABLES; //服务器配置变量
//MySQL序列使用使用AUTO_INCREMENT定义列LAST_INSERT_ID() //获取最后的插入表中的自增的值的函数
重置序列：mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,-> ADD PRIMARY KEY (id);设置序列的开始值：mysql> ALTER TABLE t AUTO_INCREMENT = 100;
或者在创建时直接AUTO_INCREMENT = 100
//MySQL处理重复数据防止表中出现重复数据防止重复插入：INSERT IGNORE INTOREPLACE INTO设置唯一索引：CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10)
UNIQUE (last_name, first_name));
统计重复数据：mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;过滤重复数据：mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl
-> ORDER BY last_name;或使用GROUP BY来读取删除重复数据：mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
-> FROM person_tbl;-> GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;或者通过添加主键/索引的方法：mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
//MySQL导出数据使用SELECT ... INTO OUTFILE ...语句导出数据mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE ‘/tmp/tutorials.txt‘;指定格式，如CSV格式：mysql> SELECT * FROM passwd INTO OUTFILE ‘/tmp/tutorials.txt‘-> FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘-> LINES TERMINATED BY ‘\r\n‘;
或SELECT a,b,a+b INTO OUTFILE ‘/tmp/result.text‘
FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘
LINES TERMINATED BY ‘\n‘
FROM test_table;
使用LOAD DATA INFILE可以将文件读回数据库
导出表作为原始数据：mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。$ mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tbl
password ******
导出SQL格式的数据：$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******
导出整个数据库：$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******
导出所有数据库：$ mysqldump -u root -p --all-databases > database_dump.txt
password ******
将数据库拷贝到其他主机：$ mysqldump -u root -p database_name table_name > dump.txt
password *****
$ mysql -u root -p database_name < dump.txt
password *****
你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</p>
$ mysqldump -u root -p database_name | mysql -h other-host.com database_name
//MySQL导入数据使用LOAD DATA导入数据mysql> LOAD DATA LOCAL INFILE ‘dump.txt‘ INTO TABLE mytbl;
如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。指定FIELDS和LINES
