Play MySQL command

Connecting to a database mysql-hlocalhost-uroot-p

In MySQL with the directory file

Show Databses;
Show all databases

Workaround 1: Locate My.ini in the MySQL installation directory, change the default-character-set=latin1 under [MySQL] to Default-character-set=utf8, save, You can then restart the MySQL service to successfully import from the command line. Disadvantage, because the deployment, you may not be able to configure the user's computer, then the method is useless ...

Workaround 2: Add a row of set character set UTF8 at the beginning of the database script file, add the default character set UTF8 after the library name, add the default CharSet =utf8 after the table, and add a line of SE before adding data t character set UTF8; you can do it.

Show All Tables
Show tables;

Create a table
CREATE table if not exists five (created if the table does not exist)
ID INT (TEN) Auto_increment PRIMARY key, set the self-increment ID and then the primary key
NAME VARCHAR (not NULL)

) CHARSET =utf8 ' Six ' sets the character set

Add data

INSERT into six (' 1 ') if there is a self-increment id Be sure to specify the column to add

Add foreign key

Syntax: ALTER TABLE name add constraint fk_id foreign key (your foreign key field name) REFERENCES outer table name (the primary key field name for the corresponding table);

Delete foreign key

ALTER TABLE ' tb_active ' DROP FOREIGN KEY ' fk_id '

Query Previous Line
SELECT * FROM student LIMIT 1

Add a DEFAULT constraint

ALTER TABLE Result
ALTER examdate SET DEFAULT ' 1999-1-1 '

Delete
ALTER TABLE Result
ALTER examdate DROP DEFAULT

----things

ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Commit

Prohibit Auto-commit
SET autocommit = 0

Auto Commit
SET autocommit = 1

---view
Create an attempt to

CREATE VIEW Student_view
As
SELECT Studentno,studentname from Student

View View
SELECT * FROM Student_view

View all views

Use INFORMATION_SCHEMA;
SELECT * from views

--Index

Create an index
This is the most basic index and it has no limitations. It is created in the following ways:
CREATE INDEX indexname on Mytable-name (username (length));
If it is a Char,varchar type, length can be less than the actual length of the field, and length must be specified if it is a blob and text type.

Modify table structure (add index)
ALTER table TableName ADD INDEX indexname (columnName)

See all the Indexes

SHOW INDEX from ' student ';

/*--Create a Student table combined index--*/
CREATE INDEX Index_name_gradeid on student (Studentname,gradeid);
/*--Create a Student table unique index--*/
CREATE UNIQUE INDEX Index_iden on student (Identitycard);
/*--Create a score table normal index--*/
CREATE INDEX index_result on result (Studentresult);

---Mysql backup

Must DOS command inside 5
mysqldump-uroot-p123 Test > Test.sql

Import
mysql-uroot-p1234 DB1 < C:\a.txt

---stored procedures
To create a stored procedure with variables
DELIMITER $$

CREATE PROCEDURE ADD2

(

In a INT,
In B VARCHAR (20)

)

BEGIN

DECLARE c int default 0;

Set C = a + B;

SELECT C as C;

end$$
DELIMITER;

Call
Call ADD2 (1, ' DD ')

Creating variables
Set @on =1

SET @zhi = 1;

SELECT COUNT (*) into @zhi from student; The value of the statement is given to a variable, but this variable can only receive a row of values

SELECT @zhi

Play MySQL command