Carding of database knowledge system (I.)

Characteristics of relational database

(1) Holding data information in two-dimensional form

(2) Traditional enterprises using Oracle (with capital), Internet Enterprises using MySQL (open source free, more community crowd)

(3) SQL statements are key to managing data

(4) Safety aspects (ACID)

2.MySQL Version Selection

(1) The earliest version I contacted was the 5.0 version of 2009-2010.

(2) The mainstream version of the company, GA version released more than 6 months, even version (I used to be like 5.6.34,5.6.36)

(3) test environment used over 5.7 version, currently relatively new version (5.7.18,5.7.20), the latest beta version 8.0

(4) The choice of MySQL version is based on the business decision of our company.

3. Relational database (MySQL) and non-relational database (MongoDB) features

(1) Relationship type (strong security)

Powerful query function

Strong consistency

Second-level index

(2) Non-relational (excellent performance)

Flexible mode

Scalable, clustered

Good performance, distributed storage

4. Advantages of relative innodb,tokudb (used on Zabbix)

(1) Insert operation is 3-4 times higher than InnoDB performance

(2) Data compression ratio can reach more than 8 times times

(3) The query performance is also much stronger than InnoDB

(4) Other functions and InnoDB almost

5. What is meta data? What do you mean?

(1) metadata is produced at the time of MySQL initialization

(2) Popular speaking meta-data is used to store the table's column properties, various correspondence relations

6.DDL: Object Definition language in database (libraries, tables)

Library

(1) Create schema Zabbix character set UTF8; (Increase)

(2) Drop database Zabbix; (delete)

(3) ALTER DATABASE Zabbix CharSet UTF8MB4;

Table

(1) CREATE TABLE stu (ID int,name varchar (), age int, gender int);

(2) desc stu; (table structure)

(3) ALTER TABLE Stu Rename to student; (Rename-change)

(4) ALTER TABLE student add addr varchar (20); (last column Plus)

(5) ALTER TABLE student add stu_id int first; (header Plus)

(6) ALTER TABLE student add QQ int after name; (name plus)

(7) ALTER TABLE student add Tel_num int after age,add email varchar (20);

(8) ALTER TABLE student drop ID; (delete column)

(9) ALTER TABLE student change name stu_name varchar (20);

(+) ALTER TABLE student modify gender varchar (20); (Change column properties)

CREATE table student_0 like stundet; (Table structure empty table with student)

CREATE TABLE T1_1 as SELECT * from T1; (identical table)

7.DCL: Database Control Language (permission grant revoke)

(1) Grant Insert,select, UPDATE, DELETE, CREATE, DROP on oldboy.* to [e-mail protected] ' 10.0.0.% ' identified by ' 123 ';

(2) Revoke Insert,select, UPDATE, DELETE, CREATE, DROP on oldboy.* from [email protected] ' 10.0.0.% ';

8.DML: Data line Operation language (add, delete, change)

(1) INSERT into student values (1, ' zhang3 ', 123,20,110, ' Male ', ' bj ', ' [email protected] '); (insert data)

(2) INSERT into student (STU_ID,STU_NAME,QQ) VALUES (2, ' li4 ', 456); (Specify columns to insert data)

(3) INSERT into student values (1, ' zhang3 ', 123,20,110, ' Male ', ' bj ', ' [email protected] '), (5, ' zz ', 12322,202,1102, ' Female ', ' bj ', ' [email protected] '); (multiple inserts)

(4) Update student set stu_name= ' Wang5 ' where stu_id=5; (Modify data)

(5) Delete from student where stu_name= ' zhang3 '; (delete data)

(6) TRUNCATE TABLE oss_base; drop table oss_base; (Delete large table operation)

9.DQL: Data Line Query Language (select Show)

(1) Select User,passoword, host from Mysql.user;

10. Complex statements

(1) SELECT co.name from city as CI, country as Co

WHERE

ci.population<100

and Co.code=ci. CountryCode; (Multi-table query)

(2) SELECT * from city WHERE countrycode= ' au ' ORDER by population DESC; (sort)

(3) SELECT * from city

WHERE countrycode= ' CHN '

ORDER by population

DESC

Limit 10; (line limit)

Carding of database knowledge system (I.)