Shulou(Shulou.com)06/01 Report--

The following mainly brings you a quick introduction to MySQL. I hope these words can bring you practical use, which is also the main purpose of this article on the introduction to MySQL. All right, don't talk too much nonsense, let's just read the following.

MySQL has three definition languages

DDL: definition language, such as creating a table, defining table properties such as index, wide bit wait

DML: operation language, add, delete, check and modify

DCL: control language, such as limiting which account can only log in through that IP, or that account can access those resources.

II. MySQL transactions:

1. MyISAM does not support

2. InnoDB support

The picture below is ordered by yourself. If you have any discomfort, please send a private message to Me~ ^-^

III. SQL language

A, DDL definition language commands include the following:

1 、 CREATE

2 、 ALTER

3 、 DROP

1 、 CREATE

1.1. Create a database

Mysql > SHOW DATABASES; # View the database +-+ in MySQL | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql > CREATE DATABASE Oracle # create database Oracle Query OK, 1 row affected (0.00 sec) mysql > SHOW DATABASES # check whether +-+ | Database | +-+ | information_schema | | Oracle | | mysql | | test | +-+ 4 rows in set (0.00 sec)

1.2. Create a table

Mysql > SELECT DATABASE () # check that the current database location DATABASE () is the MySQL built-in function +-+ | DATABASE () | +-+ | NULL | +-+ 1 row in set (0.00 sec) mysql > USE Oracle # switch to Database changed mysql > SELECT DATABASE () in the Oracle database we created earlier # check whether to switch to Oracle+-+ | DATABASE () | +-+ | Oracle | +-+ 1 row in set (0.00 sec) mysql > CREATE table BranchTab (# create table-> Id INT,-> Name CHAR (30)->); Query OK, 0 rows affected (0.09 sec) mysql > SHOW TABLES # check whether the BranchTab table is created successfully +-+ | Tables_in_Oracle | +-+ | BranchTab | +-+ 1 row in set (0.00 sec)

2. ALTER modifies the table

Mysql > SELECT DATABASE (); # check that the current database is +-+ | DATABASE () | +-+ | Oracle | +-+ 1 row in set (0.00 sec) mysql > SHOW TABLES # View the table in the current database location +-+ | Tables_in_Oracle | +-+ | BranchTab | +-+ 1 row in set (0.00 sec) mysql > ALTER TABLE BranchTab RENAME branchtab # modify table BranchTab to branchtabQuery OK, 0 rows affected (0.00 sec) mysql > SHOW TABLES; # check whether the modification is successful +-+ | Tables_in_Oracle | +-+ | brannhtab | +-+ 1 row in set (0.00 sec)

3 、 DROP

3.1. Delete the table

Mysql > SELECT DATABASE (); # View the current database location +-+ | DATABASE () | +-+ | Oracle | +-+ 1 row in set (0.00 sec) mysql > SHOW TABLES # View the table in the current database location +-+ | Tables_in_Oracle | +-+ | branchtab | +-+ 1 row in set (0.00 sec) mysql > DROP TABLE bracnhtab # DROP drop branchtab table Query OK, 0 rows affected (0.00 sec) mysql > SHOW TABLES; # check whether branchtabs is deleted Empty set (0.00 sec)

3.2.Delete database Oracle

Mysql > SHOW DATABASES; # check all the libraries in MySQL and find Oracle libraries +-+ | Database | +-+ | information_schema | | Oracle | | mysql | | test | +-+ 4 rows in set (0.00 sec) mysql > DROP DATABASE Oracle # DROP drops Oracle database Query OK, 0 rows affected (0.00 sec) mysql > SHOW DATABASES # check whether Oracke has been deleted +-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec)

B, DML manipulation language commands are as follows

1 、 INSERT

2 、 DELETE

3 、 SELECT

4 、 UPDATE

Build a database and a table before operation, and use it without introducing SHOW CREATE TABLE TABLE_NAME,DESC TABLE_NAME.

Mysql > CREATE DATABASE oracle; # create oracle database Query OK, 1 row affected (0.00 sec) mysql > use oracle # switch to oracle database Database changedmysql > CREATE TABLE branch (- > Id INT,-> Name CHAR (30)->); Query OK, 0 rows affected (0.16 sec) mysql > DESC branch # View table structure Add data briefly and don't make a mistake about +-+-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | Id | int (11) | YES | | NULL | Name | char (30) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > SELECT * FROM branch # View table structure details Empty set (0.00 sec) mysql > SHOW CREATE TABLE branch\ Graph * 1. Row * * Table: branchCreate Table: CREATE TABLE `branch` (`Id` int (11) DEFAULT NULL `Name` char (30) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 # you can see that we are using MyISMA1 row in set (0.00 sec) mysql > SELECT @ @ version +-+ | @ @ version | +-+ | 5.1.73 | +-+ 1 row in set (0.00 sec)

1. INSERT inserts data

Mysql > SELECT DATABASE (); # check whether your database location is correct +-+ | DATABASE () | +-+ | oracle | +-+ 1 row in set (0.00 sec) mysql > DESC branch # View table structure +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ -+ | Id | int (11) | YES | | NULL | | Name | char (30) | YES | | NULL | | +-+- + 2 rows in set (0.00 sec) mysql > INSERT INTO branch VALUES # insert data into the branch table-> (1 'Tom'),-> (2) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM branch # check whether +-+-+ | Id | Name | +-+-+ | 1 | Tom | | 2 | Sunshine | +-+-+ 2 rows in set (0.00 sec)

2. DELETE deletes data

Mysql > SELECT DATABASE (); # View the database location +-+ | DATABASE () | +-+ | oracle | +-+ 1 row in set (0.00 sec) mysql > DESC branch # View branch table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | Id | int (11) | YES | | NULL | | Name | char (30) | YES | | NULL | | +-+- -- + 2 rows in set (0.00 sec) mysql > DELETE FROM branch # to delete table data without adding WHERE condition is to delete all contents in this table Query OK, 2 rows affected (0.00 sec) mysql > SELECT * FROM branch # check to see if Empty set (0.00 sec) mysql > INSERT INTO branch VALUES # insert new data-> (1MagneAlis`),-> (2memorijeery'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM branch # check whether +-+-+ | Id | Name | +-+-+ | 1 | Alis | | 2 | jeery | +-+-+ 2 rows in set (0.00 sec) mysql > DELETE FROM branch WHERE Id=1 # add conditional judgment WHERE Id=1Query OK to delete the contents of the branch table, 1 row affected (0.00 sec) mysql > SELECT * FROM branch # check whether to delete the specified data +-+-+ | Id | Name | +-+-+ | 2 | jeery | +-+-+ 1 row in set (0.00 sec) mysql > DELETE FROM branch WHERE Name=jeery # deleting the contents of the branch table added conditional judgment WHERE Name=jeery;, but jeery did not add single quotation marks and reported an error ERROR 1054 (42S22): Unknown column 'jeery' in' where clause'mysql > DELETE FROM branch WHERE Name='jeery'; # deleting the contents of the branch table added conditional judgment WHERE Name='jeery' Added single quotation marks successfully Query OK, 1 row affected (0.00 sec) mysql > SELECT * FROM branch; # to see whether to delete your data Empty set (0.00 sec) specified by us.

3. SELECT to view data

Mysql > DESC branch # View table structure +-+ | Field | Type | Null | Key | Default | Extra | +-+- +-+ | Id | int (11) | YES | | NULL | | Name | char (30) | YES | | NULL | | +-+- -+ 2 rows in set (0.00 sec) mysql > INSERT INTO branch VALUES # insert some data-> (1 'Sunshine'),-> (2),-> (3) (3),-> (4) Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > SELECT * FROM branch # View data in branch table +-+-+ | Id | Name | +-+-+ | 1 | Sunshine | | 2 | jeery | | 3 | Alis | | 4 | Tom | +-+- -+ 4 rows in set (0.00 sec) mysql > SELECT * FROM branch WHERE Id=1 # View the data in the branch table under the condition "WHERRE Id=1" +-+-+ | Id | Name | +-+-+ | 1 | Sunshine | +-+-+ 1 row in set (0.00 sec) mysql > SELECT Name FROM branch # View the data in Name field in branch table +-+ | Name | +-+ | Sunshine | | jeery | | Alis | | Tom | +-+ 4 rows in set (0.00 sec) mysql > SELECT Name FROM branch WHERE Id=1 # View the data in the Name field in the branch table under the condition "WHERRE Id=1" +-+ | Name | +-+ | Sunshine | +-+ 1 row in set (0.00 sec) mysql > SELECT count (*) FROM branch # use count built-in function to check the number of rows in branch table +-+ | count (*) | +-+ | 4 | +-+ 1 row in set (0.00 sec) mysql > SELECT count (*) FROM bransh where Id=1 # use the count built-in function to see how many rows there are in the branch table under the condition "WHERE Id=1" ERROR 1146 (42S02): Table 'oracle.bransh' doesn't existmysql > SELECT count (*) FROM bransh;ERROR 1146 (42S02): Table' oracle.bransh' doesn't existmysql > SELECT count (*) FROM branch WHERE Id=1 +-+ | count (*) | +-+ | 1 | +-+ 1 row in set (0.00 sec)

4. UPDATE changes the data

Mysql > DESC branch # View the table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +- -+ | Id | int (11) | YES | | NULL | | Name | char (30) | YES | | NULL | | +-+-+ 2 Rows in set (0.00 sec) mysql > UPDATE branch SET Id=5 # change data, Id=5, it is best to add conditions in the production environment, otherwise it will be Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0mysql > SELECT * FROM branch # this is what happens without conditions Not the +-+-+ | Id | Name | +-+-+ | 5 | Sunshine | | 5 | jeery | | 5 | Alis | | 5 | Tom | +-+-+ 4 rows in set (0.00 sec) mysql > UPDATE branch SET Id=1 WHERE Name='Sunshine' # change data Id=1 with conditional "WHERE Name='Sunshine'" Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT * FROM branch # check whether it is changed to the desired +-+-+ | Id | Name | +-+-+ | 1 | Sunshine | 5 | jeery | | 5 | Alis | | 5 | Tom | +-+ -+ 4 rows in set (0.00 sec)

C, DCL control language commands are as follows

1 、 GRANT

2 、 REVOKE

1 、 GRANT

Mysql > CREATE TABLE branchone (# to distinguish, we are creating a table here-> Id INT,-> Name CHAR (30)->); Query OK, 0 rows affected (0.06 sec) mysql > SHOW TABLES # check how many tables there are in oracle library +-+ | Tables_in_oracle | +-+ | branch | | branchone | +-+ 2 rows in set (0. 00 sec) mysql > GRANT SELECT ON oracle.branch TO 'sunshine'@'192.168.11.28' IDENTIFIED BY' sunshine' # authorized sunshine users can only access the database through 192.168.11.28 IP, and only have the view permission Query OK of oracle database branch, 0 rows affected (0.00 sec) mysql > SHOW GRANTS FOR 'sunshine'@'192.168.11.28' # check whether the authorization is successful We see GRANT SELECT ON `oracle`.`branch` TO 'sunshine'@'192.168.11.28'+- -- + | Grants for sunshine@192.168.11.28 | + -+ | GRANT USAGE ON *. * TO 'sunshine'@'192.168.11.28' IDENTIFIED BY PASSWORD' * D6B63C1953E7F096DB307F8AC48C4AD703E57001' | GRANT SELECT ON `oracle`.`branch`TO 'sunshine'@'192.168.11. 28' | +- -- + 2 rows in set (0.00 sec) [root@redis_master ~] # ifconfig | grep "inet addr:192.168" # using Linux system Check the native IP, 192.168.11.28 inet addr:192.168.11.28 Bcast:192.168.11.255 Mask: 255.255.255.0 [root @ redis_master ~] # mysql-h292.168.11.28-usunshine-psunshine # use the sunshine user to connect to the database Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > mysql > SHOW DATABASES # View database +-+ | Database | +-+ | information_schema | | oracle | | test | + -+ 3 rows in set (0.00 sec) mysql > USE oracle # enter oracle database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > SHOW TABLES # check whether you can only see our branch table +-+ | Tables_in_oracle | +-+ | branch | +- -+ 1 row in set (0.00 sec) mysql > DESC branch # View table structure +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | Id | int (11) | YES | | NULL | | Name | char (30) | YES | | NULL | | +- -+-+ 2 rows in set (0.00 sec) mysql > INSERT INTO branch VALUES # insert data Prompt permission to deny command denied-> (10 ~ (10) dint Test') ERROR 1142 (42000): INSERT command denied to user 'sunshine'@'gitlab.jinr.com' for table' branch'mysql > DELETE FROM branch; # Delete data and prompt permission to deny command deniedERROR 1142 (42000): DELETE command denied to user 'sunshine'@'gitlab.jinr.com' for table' branch'mysql > UPDATE branch SET Id=1 # change data and prompt permission to deny command deniedERROR 1142 (42000): UPDATE command denied to user 'sunshine'@'gitlab.jinr.com' for table' branch'mysql > SELECT * FROM branch # View data Normal +-+-+ | Id | Name | +-+-+ | 1 | Sunshine | | 5 | jeery | | 5 | Alis | 5 | Tom | +-+-+ 4 rows in set (0.00 sec) [root@jroa ~] # ifconfig | grep "inet addr:192.168" # use another Linux system Check IP, 192.168.11.21 inet addr:192.168.11.21 Bcast:192.168.11.255 Mask:255.255.255.0 [root@jroa ~] # mysql-h292.168.11.28-usunshine-psunshine # try to connect To log in to ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.28' (113)

2 、 REVOKE

Mysql > SHOW GRANTS FOR 'sunshine'@'192.168.11.28' # View permissions Found GRANT SELECT ON `oracle`.`branch` TO 'sunshine'@'192.168.11.28' +- -+ | Grants for sunshine@192.168.11.28 | +- -+ | GRANT USAGE ON *. * TO 'sunshine'@'192.168.11.28' IDENTIFIED BY PASSWORD' * D6B63C1953E7F096DB307F8AC48C4AD703E57001' | GRANT SELECT ON `oracle`.`branch`TO 'sunshine'@'192.168.11.28' | +- -+ 2 rows in set (0.00 sec) mysql > REVOKE SELECT ON oracle.branch FROM 'sunshine'@'192.168.11.28' # withdraw authorization Query OK, 0 rows affected (0.00 sec) mysql > SHOW GRANTS FOR 'sunshine'@'192.168.11.28' # View permissions GRANT SELECT ON `oracle`.`branch`TO 'sunshine'@'192.168.11.28'+- not found -+ | Grants for sunshine@192.168.11.28 | +- -+ | GRANT USAGE ON *. * TO 'sunshine'@'192.168.11.28' IDENTIFIED BY PASSWORD' * D6B63C1953E7F096DB307F8AC48C4AD703E57001' | +- -+ 1 row in set (0.00 sec) [root@redis_master ~] #! if # check the native IP 192.168.11.28ifconfig | grep "inet addr:192.168" inet addr:192.168.11.28 Bcast:192.168.11.255 Mask: 255.255.255.0 [root @ redis_master ~] #! mys # connect to mysql, because it is authorized for the first time, even if it is withdrawn The public library still has the permission of mysql-h292.168.11.28-usunshine-psunshineWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 5Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases # View the database Found that oracle data is missing +-+ | Database | +-+ | information_schema | | test | +-+ 2 rows in set (0.00 sec)

For the above basic knowledge about MySQL, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.