MSYQL database simple operations and transactions and indexes

Source: Internet
Author: User
Tags auth create index

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.