Mysql数据类型
字符类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
| 类型 | 大小 | 用途 |
Mysql创建用户与授权
- 创建用户
mysql> CREATE USER 'mike'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
- 授权
命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
grant all PRIVILEGES on *.* to root@'%' identified by '123456';
| 权限名称 | 说明 |
|---|---|
| ALTER | Allows use of ALTER TABLE |
| ALTER ROUTINE | Alters or drops stored routines |
| CREATE | Allows use of CREATE TABLE |
| CREATE ROUTINE | Creates stored routines |
| CREATE TEMPORARY TABLE | Allows use of CREATE TEMPORARY TABLE |
| CREATE USER | Allows use of CREATE USER DROP USER RENAME USER and REVOKE ALL PRIVILEGES |
| CREATE VIEW | Allows use of CREATE VIEW |
| DELETE | Allows use of DELETE |
| DROP | Allows use of DROP TABLE |
| EXECUTE | Allows the user to run stored routines. |
| FILE | Allows use of SELECT ... INTO OUTFILE and LOAD DATA INFILE >. |
| INDEX | Allows use of CREATE INDEX and DROP INDEX |
| INSERT | Allows use of INSERT |
| LOCK TABLES | Allows use of LOCK TABLES on tables for which the user also has SELECT privileges |
| PROCESS | Allows use of SHOW FULL PROCESSLIST |
| RELOAD | Allows use of FLUSH |
| REPLICATION | Allows the user to ask where slave or master |
| CLIENT | servers are |
| REPLICATION SLAVE | Needed for replication slaves |
| SELECT | Allows use of SELECT |
| SHOW DATABASES | Allows use of SHOW DATABASES |
| SHOW VIEW | Allows use of SHOW CREATE VIEW |
| SHUTDOWN | Allows use of mysqladmin shutdown |
| SUPER | Allows use of CHANGE MASTER , KILL , PURGE MASTER LOGS , and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached. |
| UPDATE | Allows use of UPDATE |
| USAGE | Allows connection without any specific privileges. |
- 设置与更改用户密码
命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");
修改当前登录用户的密码
SET PASSWORD = PASSWORD("newpassword");
root用户修改其他用户密码 貌似这种方式会报一个警告
mysql> update mysql.user set authentication_string=password("222222") where User="mike" and Host="localhost";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
- 删除用户与权限
drop user 用户名@’%’;
drop user 用户名@ localhost;
- mysql 查看状态 开启 关闭
查看是否已开启
ubuntu@VM-0-10-ubuntu:~$ ps -ef|grep mysqld
mysql 25240 1 0 14:21 ? 00:00:02 /usr/sbin/mysqld
ubuntu 30182 28796 0 16:07 pts/0 00:00:00 grep --color=auto mysqld
开启
ubuntu@VM-0-10-ubuntu:~$ service mysql start
关闭
ubuntu@VM-0-10-ubuntu:~$ service mysql stop
