MSYQL database simple operations and transactions and indexes

MSYQL database simple operations and transactions and indexes

MSYQL Database operations:
[[email protected] ~]# mysql-u root-p//Enter the database

mysql> show databases; View all databases

mysql> use MySQL; Go to MySQL Database

Mysql> Show tables; View tables in a database

mysql> desc User; View the structure of a table

mysql> CREATE DATABASE auth; Create a new empty library

mysql> show databases;
, the library that you just created is displayed under all library commands.

[Email protected] ~]# ls/usr/local/mysql/data/

One more auth directory.
Mysql> Show tables; View tables in the AUTH database

There are no tables in the library

Mysql> CREATE TABLE users (user_name char (+) not null,user_passwd char (ON) DEFAULT ', PRIMARY KEY (user_name));
Create the Users table in the Auth library and specify the primary key

mysql> desc users; View the structure of the Users table

mysql> drop table auth.users; To delete the users table in the Auth library

mysql> INSERT into users (USER_NAME,USER_PASSWD) VALUES (' Zhangsan ', password (' 123123 '));
Insert data contents in the Users table Zhangsan, password is ciphertext password

mysql> INSERT into users (USER_NAME,USER_PASSWD) VALUES (' Lisi ', ' 123123 ');
Insert data contents Lisi in the Users table, password is plaintext password 123123

Mysql> INSERT into users values (' Wangwu ', ' 123abc ');
Insert data content Wangwu in the Users table, password is plaintext password 123abc
Note: When inserting new data content, the part of the specified field in the INSERT statement can be omitted if the record completely includes the values of all the fields in the table

Mysql> INSERT into users values (' Jack ', password (' 123abc '));
Insert data contents in the Users table Wangwu, password is ciphertext password

Mysql> select from users;//view data records in the Users table

Mysql> Select from Users \g//\G indicates vertical display

Mysql> Update users SET User_passwd=password (") WHERE user_name= ' Lisi ';
Modify the user name Lisi record in the users table to set the password substring to a null value
Mysql> SELECT * from users;//View modified results

Skip authorization Form reset root password----hack root password
[[email protected] ~]# vi/etc/my.cnf//modify MySQL master configuration file

Skip_grant_tables

[Email protected] ~]# service mysqld restart
[[email protected] ~]# MySQL//Direct input MySQL Enter

mysql> Update Mysql.user Set Authentication_string=password (' 123456 ') where user= ' root ';
In the MySQL library users table, change the password for user name root to 123456 ciphertext form

mysql> flush Privileges; Refresh Permissions

After the modification is completed, the MySQL master configuration file will be skip_grant_tables deleted and then restarted.
[Email protected] ~]# service mysqld restart

Now login with MySQL will error
[Email protected] ~]# mysql-u root-p

Mysql> Delete from users where user_name= ' Lisi '; Delete a record of user name Lisi in the Users table in the Auth library

mysql> drop database AA; Delete Database AA

Index
mysql> use imployee_salary;
Mysql> Show tables; View tables in the Imployee_salary library

Mysql> select * from It_salary; Querying data records in a it_salary table

Normal index
Mysql> CREATE index Salary_index on it_salary (payroll);
Create a normal index for the payroll column of the It_salary table Salary_index

Mysql> Show index from It_salary; View Normal Index

Uniqueness Index
Mysql> Create unique index salary_unique on it_salary (name);
It_salary The Name column of the table to create a unique index Salary_unique

Mysql> Show index from It_salary;

Mysql> ALTER TABLE It_salary add PRIMARY key (employee ID);

This error, is to remind that there is a primary key, has been set before the primary key

Transaction, the operation statement is committed as a whole after the batch execution--
mysql> use auth;
Mysql> begin;
Mysql> INSERT into users values (' Tom ', password (' 123abc '));
Mysql> INSERT into users values (' Lucy ', password (' 123abc '));

And then open a remote login to see, there is wood to insert data records into the table

Mysql> commit; Submit

Look again.

Now, transactions are typically used in scripts

MSYQL database simple operations and transactions and indexes