MYSQL is RDBMS (relational Database Management System)
Create a user who connects to MySQL
% mysql-p-U root
mysql> CREATE USER ' sampadm ' @ ' localhost ' identified by ' secret ';
Mysql> GRANT all on sampdb.* to ' sampadm ' @ ' localhost '
Connect to MySQL
Mysql-h host_name-p-U user_name
Quit MySQL
Mysql> quit
Execute MySQL
Mysql> Select Now (), User (), Version ();
Mysql> Select Now (), User (), Version () \g//; \g Terminating statement
Mysql> Select Now (), User (), Version () \g//Vertical display, one value per line
Script execution
% MySQL < MyScript.sql
% MySQL sampdb < create_member.sql
Create a data table
Mysql> CREATE TABLE Member
(
member_id INT UNSIGNED not NULL auto_increment,
PRIMARY KEY (member_id),
First Name VARCHAR () not NULL,
Last Name VARCHAR () not NULL,
Suffix VARCHAR (5) is not NULL,
Expiration DATE NULL
);
View table Structure
Mysql> DESCRIBE member;
Show specific columns
Mysql> SHOW COLUMNS from member like '%name ';
List tables
Mysql> SHOW TABLES;
% Mysqlshow sampdb
List databases
Mysql> SHOW DATABASE;
% MySQL Show
Inserting data rows
mysql> CREATE TABLE Student
(
NameVARCHAR () not NULL,
Sex ENUM (' F ', ' M ') not NULL,
student_id INT UNSIGNED not NULL auto_increment,
PRIMARY KEY (student_id)
) ENGINE = InnoDB
mysql> INSERT into student VALUES (' Alex ', ' M ', NULL);
mysql> INSERT into student VALUES (' Alex ', ' M ', null), (' Bob ', ' F ', null); Include all columns in parentheses
Mysql> INSERT into student (name,sex) VALUES (' Alex ', ' M ');
Loading data files
mysql> LOAD DATA LOCAL INFILE ' member.txt ' into TABLE member;
% Mysqlimport--local Samdb member.txt
Retrieving information
Book ordering, Select--from--where--group By--having--order by
Execution order, From--where--group By--having--select--order by
Null value means ' no data ' and cannot be compared with ' data ' value
Limit number, restricting display lines
Date function, year (), MONTH (), DayOfMonth ()
Variable, @variable
Mysql> SELECT @birth: =birth from student where name= ' Bob ';
Mysql> SELECT name from student WHERE birth< is @birth ORDER by birth;
Mysql> SET @today =curdate ();
Mysql> SET @one_week_ago: =date_sub (@today, INTERVAL 7 day);
Statistical information
DISTINCT, clear Duplicate information
Count (*), counting all rows
COUNT (data column), non-null row
Mysql> SELECT State,count (*) from President GROUP by Status ORDER by COUNT DESC LIMIT 4;
Multi-table junction
Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right table
Right join returns records that include all records in the right table and the junction fields in the left table
INNER JOIN (equivalent join) returns only rows that have the same join field in two tables
The biggest difference between exists and in is that the in-boot clause can only return a single field, such as:
Select name from student where sex = ' m ' and mark in (select-from-grade where ...),
The IN clause returns three fields, which is incorrect, the EXISTS clause is allowed, but in only one field is allowed to return, and two fields can be randomly
Delete data rows
Delete from Tbl_name WHERE which rows to delete;
Update Data rows
Update Tbl_name SET which columns to change WHERE which rows to update;
Streamline the linking process
1. ~/.my.cnf
[Client]
Host=server_host
User=your_name
Password=your_pass
2. Command history
%!my//Recently used commands
3. Alias sampdb ' Mysql-h host_name-p-u sampadm sampdb '
aggregate function, MIN (), MAX (), SUN (), AVG ()
Create a database
CREATE DATABASE [IF not EXISTS] db_name [CHARACTER SET charset][collate collation];
Deleting a database
DROP DATABASE db_name;
Change Database
ALTER DATABASE [Db_name][character SET charset][collate collation];
MySQL Quick check-1-Database basic operations
