Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to operate mssql, the introduction in the article is very detailed, has a certain reference value, interested friends must read!

It is recommended to use the Grant statement for authorization, as follows:

GRANT USAGE ON *. * TO 'username'@'localhost' IDENTIFIED BY' password' WITH GRANT OPTION

The last sentence:

Replace "username" with the user name to be authorized, such as clientusr

Replace "password" with the password set by clientusr

3. Authorization of accessible data sheet

After you have created the account, you will start to authorize the data table to the above common user. The default permissions of the connection users added in step 3 are all "N". You must authorize the account in the db table and allow it to access the dedicated database. Of course, the superuser will not say.

Use the following statement:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON tablename.* TO 'username'@'localhost' IDENTIFIED BY' password'

The permissions in this statement are determined according to the actual needs:

Replace "tablename" with the table name of the data table to which access is granted

"username" is the authorized user name in step 2

"password" is the password set by the authorized user in step 2

* * gorgeous dividing line *

Desc means descend in descending order.

Asc means ascend in ascending order.

Sql = "select table content name from database table name Putout=true order by read sort table name asc"

For example

Sql = "select * from user where Putout=true order by time desc" / / sort by latest time

Sql = "select * from user where Putout=true order by time asc" / / sort by early time

* * gorgeous dividing line * *

(0) View all databases:

Show databases; or show databases like 'regular expression'; (1) location 'specific location' (2) with dbproperties ('creater'='ming'))

The contents in the above parentheses can be seen in the following ways

Describe database extended database name

(1) create a database:

Create database if not exists database name

(2) check the description of the database:

Describe database database name

(3) Select the database:

Use database name

(3-0) look at the existing tables in the database:

Show tables

(3-1) create a table

Create table table name (an INT)

(3-2) the description of the production table:

Describe table shows that

(3-3) Delete the table:

Drop table shows that

(4) Delete the database:

Drop database if exists database name

If there are tables in this database, then hive is not allowed to delete, so do the following:

Drop database if exists database name cascade

* pay attention to if not exists in hive

* magnificent dividing line * *

Changes to the table in mysql:

MySQL changes the table structure to add fields:

Alter table `user_movement_ log` Add column GatewayId int not null default 0 AFTER `Regionid` (after which field is added)

MySQL changes the table structure to delete fields:

Alter table `user_movement_ log` drop column Gatewayid

MySQL changes the table structure and adjusts the order of fields:

ALTER TABLE `user_movement_ log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID

Primary key

Al

Ter table tabelname add new_field_id int (5) unsigned default 0 not null auto_increment, add primary key (new_field_id)

Add a new column

Alter table T2 add d timest alter table infos add ex tinyint not null default'0'

Delete column

Alter table t2 drop column c

Rename the column

Alter table t1 change ab integer

Change the type of column

Alter table T1 change b b bigint not null; alter table infos change list list tinyint not null default'0'

Rename table

Alter table t1 rename t2

Indexing

> alter table tablename change depno depno int (5) not null; > alter table tablename add index index name (field name 1 [, field name 2...]) ; > alter table tablename add index emp_name (name)

MySQL changes the index with primary keywords in the table structure

> alter table tablename add primary key (id)

Index with unique restrictions

> alter table tablename add unique emp_name2 (cardnumber)

Delete an index

> alter table tablename drop index emp_name

Modify the table:

Add fields:

> ALTER TABLE table_name ADD field_name field_type

Modify the original field name and type:

> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type

Delete a field:

> ALTER TABLE table_name DROP field_name

*

It's new again today: import and export xxxx.sql files

The Mysql command line exports the database:

1. First, we go to the bin directory of the mysql installation directory through the command line. For example, the command I typed: cd C:\ Program Files\ MySQL\ MySQL Server 5.5\ bin

(if it is a win7 system and mysql is installed in the C disk directory, if the above instruction prompts you to deny access, just run the command prompt as an administrator)

two。 Export database: mysqldump-u user name-p database name > exported file name

As I typed the command line: mysqldump-u root-p atm > atm.sql (it will let you enter the password to enter MySQL)

(if you export a single table, enter the table name after the database name)

3. After executing the above command, we can see the atm.sql database file we just exported in the bin directory, which contains all the tables in the database and the records in the tables.

The Mysql command line imports the database:

1. First of all, we will import the .SQL file into the database and put it in the bin directory, which is more convenient.

two。 Then we go to MySQL Command Line Client, enter the password, go to "mysql >" and create a new database (mysql > create database test;).

3. Use the newly created database mysql > use test

4. Import file: mysql > source imported file name; for example, I want to import the atm.sql data file I just exported: mysql > source atm.sql

5. If there is no error message, we can see through the show tables; directive that the newly created test database has been imported into the just atm database.

The above is all the content of this article "how to operate mssql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.