I. Introduction to the Database
What is a database?
Data warehouses, such as: In an instance of ATM we create a DB directory called a database
What is MySQL, Oracle, SQLite, Access, MS SQL Server, and more?
They are database software, the main function is two parts: 1, to save data to memory or file. 2. Accept specific commands to manipulate the data
What is SQL?
The above question says that the database accepts a specific command, which is written in SQL, which is the abbreviation for the Structured Query Language (structured Query Language), a language specifically for interacting with the database.
Second, the installation of the database
Windows version
1. Download
MySQL Community Server 5.7.16 http://dev.mysql.com/downloads/mysql/
2. Decompression
If you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: C:\mysql-5.7.16-winx64
3. Initialization
After MySQL extracted the bin directory there is a lot of executable files, execute the following command to initialize the data:
CD C:\mysql-5.7.16-winx64\bin mysqld--initialize-insecure
4. Start the MySQL service
Execute the command to start the MySQL service
# Go to executable directory CD C:\mysql-5.7.16-winx64\bin # start MySQL service mysqld
5. Start the MySQL client and connect to the MySQL service
The default password is not set to the root account due to the "mysqld--initialize-insecure" command used during initialization
# Enter the executable directory CD C:\mysql-5.7.16-winx64\bin # connection MySQL server mysql-u root-p # Prompt Please enter the password, go directly
Enter the entry to see that the installation was successful:
、
To this end, the MySQL server has been successfully installed and the client is ready to connect, and in the future to operate MySQL, it is only necessary to repeat the 4, 5 steps. However, in the 4, 5 steps of repeated access to the executable directory is cumbersome, if you want to be easy to operate later, you can do the following operations.
Adding environment variables
Add the MySQL executable to the environment variable to execute the command
"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box to find the variable named path of a row, double-click"-"to append the MySQL bin directory path to the variable value, and split" such as: \ C Program Files (x86) \parallels\parallels tools\applications;%systemroot%\system32;%systemroot%;%systemroot%\ System32\wbem;%systemroot%\system32\windowspowershell\v1.0\; C:\Python27; C:\Python35; C:\mysql-5.7.16-winx64\bin
This way, when you start the service and connect later, you only need to:
# to start the MySQL service, enter Mysqld # on the terminal to connect the MySQL service in terminal input: Mysql-u root-p
. Making a MySQL service into a Windows service
The previous step solves some problems, but is not exhaustive, because the current terminal will be stuck while executing "MYSQD" to start the MySQL server, then do the setup to resolve the problem:
# make MySQL Windows service, execute this command at Terminal: "C:\mysql-5.7.16-winx64\bin\mysqld"--install # Remove the MySQL Windows service and execute this command at the terminal: "C \ Mysql-5.7.16-winx64\bin\mysqld "--remove
After registering as a service, you only need to execute the following command when you start and close the MySQL service later:
# Start MySQL service net start MySQL # close MySQL service net stop MySQL
Linux version
Installation
Yum Install Mysql-server
Start
Mysql.server start
Link
Connection: mysql-h host-u user-p Common errors: Error 2002 (HY000): Can ' t connect to local MySQL server through socket '/TM P/mysql.sock ' (2), it means that the MySQL server daemon (Unix) or service (Windows) was not running. Quit: Quit or Contro L+d
Third, the operation of the database
1. Display Database
SHOW DATABASES;
Default database:
MySQL-User rights-related data
Test-for user testing data
Information_schema-mysql itself schema-related data
2. Create a database
# utf-8create database name DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; # gbkcreate database name DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;
3. Using the Database
Use db_name;
4. User Management
Create user ' username ' @ ' IP address ' identified by ' password ', delete user ' username ' @ ' IP address ', modify user rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';; Modify password set password for ' username ' @ ' IP address ' = password (' new password ') PS: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)
5. Authorization Management
Show grants for ' user ' @ ' IP address ' --View permissions grant permissions on database. Table to ' user ' @ ' IP address ' --Authorize revoke permissions on database. Table from ' user ' @ ' IP Address ' --Cancel permissions
for Permissionsfor Databasefor IPExample
Note: Flush privileges, which reads the data into memory so that it can take effect without restarting.
Iv. Processing of data sheets
1. Create a table
View Codeparameter description When creating a table
2. Delete a table
DROP table Name
3. Clear the Table
Delete from table name TRUNCATE TABLE table name
4, modify the table
Add Column: ALTER TABLE table name add column name type Delete column: ALTER TABLE table name drop Column name Modify column: ALTER TABLE table name modify column name type; --type ALTER TABLE name change original column name new column name type;--column name, type add primary key: ALTER TABLE table name add primary key (column name); Delete PRIMARY key: alter TABL e table Name drop primary key; ALTER TABLE name modify column name int, drop primary key; Add foreign key: ALTER TABLE from TABLE ADD constraint foreign key name (shape: fk_ from Table _ Main Table) foreign key from table (foreign key field) references Main Table (primary key field); Delete foreign key: ALTER TABLE name drop for Eign key FOREIGN Key name modifies default value: ALTER TABLE TESTALTER_TBL alter I SET default 1000; Delete defaults: ALTER TABLE TESTALTER_TBL alter I DROP D Efault;
5. Basic data type
MySQL data types are broadly divided into: numeric, time, and string
View Code
For more information:
- Http://www.runoob.com/mysql/mysql-data-types.html
- Http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html
V. Operation of table contents
1, increase
Insert into table (column name, column name ...) values (value, value, Value ...) Insert into table (column name, column name ...) values (value, value, Value ...), (value, value, Value ...) Insert into table (column name, column name ...) select (column name, column name ...) from table
2. By deleting
Delete from table delete from table where id=1 and Name= ' Alex '
3, change
Update table Set name = ' Alex ' where id>1
4. Check
SELECT * FROM Table SELECT * from table where ID > 1select nid,name,gender as GG from table where ID > 1
5. Other
A, condition select * from table where ID > 1 and name! = ' Alex ' and num = 12; SELECT * FROM table where ID between 5 and 16; SELECT * FROM table where ID in (11,22,33) select * FROM table where ID not in (11,22,33) select * FROM table where ID in (Selec T nid from table) b, wildcard select * from table where name like ' ale% '-ale begins with all (multiple strings) select * FROM table where name like ' Ale_ ' -Ale begins with all (one character) c, restricts select * from table limit 5; -First 5 lines select * FROM table limit 4, 5; -5 rows starting from line 4th select * FROM table Limit 5 offset 4-5 rows starting from line 4th D, sort select * from table ORDER BY column ASC-according to column "From small to large arrange select * from table ORDER BY column desc-rank from largest to smallest according to" column "SELECT * from Table order BY column 1 desc, column 2 ASC-according to "Column 1" is arranged from large to small, if same column 2 from small to large sort e, group Select Num from table GROUP by NUM Select Num,nid from table GROUP by Num,nid Select Num , nid from table where nid > Group by num,nid Order Nid desc Select num,nid,count (*), SUM (score), Max (score), Min (score) From table GROUP by Num,nid SelectNum from table GROUP by NUM has max (ID) > 10 Special: Group by must be in where, before order by F, no correspondence of the list does not display select A.num, A . Name, b.name from A, Where A.nid = B.nid No correspondence does not show select A.num, A.name, b.name from A inner JOIN B o n A.nid = B.nid A table All display, if there is no correspondence in B, then the value is NULL select A.num, A.name, B.name from a left join B on a.nid = B.nid b table all display, if there is no correspondence in B, then the value is NULL select A.num, A.name, b.name from A right join B on a.nid = B.nid g, combined combination, automatic processing coincident Select nickname from a union select name from B combination, do not process coincident select nickname from a UNION ALL Sele CT name from B
Reprint: http://www.cnblogs.com/kading/
MySQL Beginner's introductory article