Database DB

• The database itself supports concurrent network operations
• Using database to add, delete, check and modify data is more efficient than storing data in files
• Each folder is a database

Database management system DBMS: sqlserver MySQL Oracle

• install

  • Download: https://downloads.mysql.com/archives/community/ Select the version and download the zip package

  • Deployment:

    • Unzip the package to the mysql folder path of disk d

  • Access: you can use the command prompt in the bin directory. There is no password by default

    mysql -uroot -p --Sign in
    

  • All database packages are shown below:

    show databases;
    

  • Show current user:

    select user();   --only one root@localhost
    

  • Create password for current user:

    set password=password('123')
    

  • Create user:

    create user 'guset'@'localhost' identified by '123'   --(Locally available,'guest'@'localhost' (this is a fixed format)
    

  • Create user:

    create user 'tom'@'192.168.2.%' identified by '123'   --(All 192.168.2 People in the network segment can use tom Access my library, password 123)
    

  • Create database (create database + database name)

    create database test1 
    

  • grant includes select delete insert alter and so on:

    grant select on test1.* to 'guest'@'localhost'  --to guest Users only give select jurisdiction
    

  • If it doesn't take effect, you can refresh it

    flush privileges;
    

  • Log in to someone else's Library remotely

    mysql -uguest -p123 -h 192.168.2.111  --User is guest The password is 123 Library in 2.111 upper
    

  • Create an account and authorize:

    grant select on test1.* to 'jerry'@'192.168.2.111' indentified by '111' --Create user jerry He can be in 2.111 Log on to my mysql And use password 111
    

  • The default port of mysql is 3306

  • Switch to database:

    use test1
    

  • Show all tables

    show tables;
    

  • Delete database:

    drop database test1;
    

  • Create a table:

    create table t1(id int,name char(20), age int) --id  name and age Three columns
    

  • Insert data:

    insert into t1 values(101,'tom',25) 
    

  • Query data:

    selet * from t1;
    

  • modify

    update t1 set age=22 where id=101
    

  • Delete table record

    delete from t1 where id=101 --Be sure to add where Clause, otherwise it is easy to delete it all
    

• Storage engine

  • A table: data, index and table structure

  • Data and indexes are stored together, and a table can be stored in two files (Innodb engine)

    • Support transaction: there may be multiple operations (select insert, etc.) in a transaction. They either succeed at the same time or do not occur. Binding together is a transaction. Once the transaction is completed halfway, the power failure will roll back
    • Row level locks are supported: row level locks and table level locks. Row level locks are used when few rows are modified. Otherwise, table level locks and row level locks increase efficiency and have advantages when data is modified frequently.
    • Supports foreign keys: constrains the associated fields in two tables and cannot be added or deleted at will

  • Data and indexes are not stored together. A table can be stored in three files (Myisam engine)

  • The data is stored in the Memory structure (only 1 data is required to be stored in the Memory structure)

  • Display database engine

    show engines; --Default is Innodb
    

    show variables like '%engine%'
    

• data type

• integrity constraint

• Modify the structure of the table

• Multi table connection