1. Agregar usuarios
1.1 Login MYSQL:
@>mysql -u root -p
@>Password
Crear usuario:
Format:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username-the username you will create description:
host-Specify the host on which the user can log in, if it is a local user, localhost can be used, if you want the user to log in from any remote host, you can use the wildcard%
Example:
CREATE USER 'javacui'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'javacui'@'172.20.0.0/255.255.0.0' IDENDIFIED BY '123456';
CREATE USER 'javacui'@'%' IDENTIFIED BY '123456';
CREATE USER 'javacui'@'%' IDENTIFIED BY '';
CREATE USER 'javacui'@'%';
password-the user's login password,Password can be empty,If it is empty, the user can log in to the server without a password
Autorización(Debe crear un usuario antes de poder autorizar, la autorización directa notificará un error)
format:GRANT privileges ON databasename.tablename TO 'username'@'host';
Privilegios -Los permisos de funcionamiento del usuario, como SELECT, INSERT, UPDATE, etc. (consulte el final de este artículo para obtener la lista detallada). Si desea conceder los permisos, utilice las instrucciones ALL:
Databasename – nombre del almacenamiento de datos
Tablename -Nombre de tabla, si desea conceder al usuario los permisos de operación correspondientes en todas las bases de datos y tablas, puede utilizar * para indicar, como *.*
Por ejemplo:
AuthorizationSELECT,INSERT
GRANT SELECT, INSERT ON test.user TO 'javacui'@'%';
Authorization to query, insert, modify, delete permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON test.user TO 'javacui'@'%';
Grant all permissions ALL
GRANT ALL ON *.* TO 'javacui'@'%';
FLUSH PRIVILEGES;
#Note: "localhost" here means that the user can only log in locally and cannot log in remotely on another machine.
#If you want to log in remotely, change "localhost" to "%",
# Means that you can log in on any computer. You can also specify that a certain machine can log in remotely.
Nota: El usuario autorizado por el comando anterior no puede autorizar a otros usuarios. Si desea que se autorice al usuario, utilice el siguiente comando
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
Revocar permisos de usuario
REVOKE privileges ON databasename.tablename FROM 'username'@'host';
Descripción del ejemplo: privilege, databasename, tablename-same como parte de autorización
REVOKE SELECT ON *.* FROM 'javacui'@'%';
2. Modifique la contraseña
método uno:
Establecer y cambiar la contraseña de usuario
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
Si actualmente ha iniciado sesión en el usuario
SET PASSWORD = PASSWORD("newpassword");
Método dos:
useUPDATEDirect edituserTable
mysql -u root -p password
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;
When you lose the root password, you can do this
mysqld_safe --skip-grant-tables&
mysql -u root -p
mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';
mysql> FLUSH PRIVILEGES;
3. Modifique el IP de inicio de sesión para que ese IP pueda iniciar sesión
método uno:
Change the table method.
It may be that your account does not allow remote login, only localhost. At this time, as long as the computer on localhost, after logging in mysql, change "mysql" In the database "user" External "host" Items from"localhost"Renamed"%"
mysql -u root -p
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
Método dos:
Authorization law.
For example, if you want myuser to use mypassword to connect to the mysql server from any host.
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
If you want to allow the user myuser to connect to the mysql server from the host whose ip is 192.168.1.6, and use mypassword as the password
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
If you want to allow the user myuser to connect to the dk database of the mysql server from the host whose ip is 192.168.1.6, and use mypassword as the password
GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Para modificar el ip de inicio de sesión, consulte másLa base de datos se puede conectar de forma remota o se puede acceder a ella con una dirección IP
4. Eliminar usuario
@>mysql -u root -p
@>Password
mysql>Delete FROM user Where User='test' and Host='localhost';
mysql>flush privileges;
mysql>drop database testDB; //Delete the user's database
Delete account and permissions:
>drop user username@'%';
>drop user [email protected];
5. Enumere todas las bases de datos
mysql>show database;
6. Cambiar la base de datos
mysql>use 'data storage name';
7. Enumere todas las tablas
mysql>show tables;
8. Mostrar la estructura de la tabla de datos
mysql>describe Table Name;
9. Elimine la base de datos y la tabla de datos
mysql>drop database data storage name;
mysql>drop table Data table name;
10. Operaciones de mesa
Construir una tabla
Remarks: Use "use <data storage name>"Should connect to a database.
Command:create table <Table Name> (<Field name 1> <Types of 1> [,..<Field name n> <Type n>]);
Example:
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));
Obtener la estructura de la mesa
Command: desc Table name, orshow columns from Table Name
Example:
mysql> describe MyClass
mysql> desc MyClass;
mysql> show columns from MyClass;
Eliminar tabla
Command:drop table <Table Name>
For example: delete the table named MyClass
mysql> drop table MyClass;
Insertar datos
Command:insert into <Table Name> [( <Field name 1>[,..<Field name n > ])] values ( Value 1 )[, ( Value n )]
Example:
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
Consultar los datos de la tabla
Query all rows
mysql> select * from MyClass;
Consultar las primeras filas de datos
For example: view the front of the table MyClass 2 Row data
mysql> select * from MyClass order by id limit 0,2;
or
mysql> select * from MyClass limit 0,2;
Eliminar los datos de la tabla
Command:delete from Table Name where Expression
For example: delete the number in the table MyClass 1 record of
mysql> delete from MyClass where id=1;
Modificar los datos de la tabla
Command:update Table Name set Field=New value,... where Condition
mysql> update MyClass set name='Mary' where id=1;
Añadir campos a la tabla
Command:alter table Table Name add Field Type Other;
For example: add a field passtest to the table MyClass,Type is int(4),The default value is 0
mysql> alter table MyClass add passtest int(4) default '0'
Cambiar el nombre de la tabla
Command:rename table Original table name to New table name;
For example: Change the name of MyClass to YouClass in the table
mysql> rename table MyClass to YouClass;
Actualizar el contenido del campo
Command:update Table Name set Field name = New content
update Table Name set Field name = replace(Field name, 'Old content', 'New content');
For example: add before the article 4 Spaces
update article set content=concat(' ', content);
.