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

1.MySQL 's table constraints (1) introduction to data integrity:

What    calls the integrity of the data is the accuracy and reliability of the data. You can improve the integrity of your data by adding integrity constraints:

Entity integrity: determine a unique identity for each entity (record). Implementation: primary key constraint, unique constraint, primary key self-increment domain integrity: indicates that the field is complete: field type, non-null constraint, default value referential integrity: reference table and referenced table implementation: foreign key constraint custom integrity: customize the scope implementation of certain fields: check constraints (not supported by MySQL) (2) Table fields add constraints and delete

Primary key constraint

   requirements: the number of primary key columns is unique and cannot be empty, and the primary key can contain one or more columns of the table (either one column becomes the primary key, or several columns can be combined into the primary key). Try not to select business data as the primary key.

   function: the primary key is the only field that can determine a row of data.

There are three ways to create primary keys:

Example 1: # add constraints create table stu (id int primary key, sname varchar (20)) when creating fields; # case 2sname varchar # finally add constraints create table stu (id int, sname varchar (20), primary key (id)); # examples table has been created, and then add constraints create table stu (id int, sname varchar (20)); alter table stu add constraint ky_id primary key (id) Ps: if you want to add primary key self-increment, just add: auto_increment after primary key

Delete the primary key constraint:

# to delete a primary key constraint, first delete its primary key self-increment, and then delete: alter table stu modify id int / / delete primary key self-increment alter table stu drop primary key; / / delete primary key constraint

Unique constraint

   requirement: the column is required to be unique and allowed to be empty.

# examples: alter table stu add constraint unique (sname)

Delete unique constraint

# when deleting a unique constraint, you first need to delete the index, because an index alter table stu drop index snamealter table stu drop index unique constraint name is created by default when creating a unique constraint

Non-empty constraint

   requirement: a certain type of content is not allowed to be empty

   syntax: name varchar (50) not null

Default value

   requirements: when the field is not given a fixed value, automatically assign an initial value.

   syntax: name varchar (50) default 'zs'

Foreign key constraint

   requires that a field in one table must be equal to a field in another table, and if it is not equal, or the other table does not have this value, the storage fails.

   syntax: foreign key (field of table) references exterior name (field)

# example: add foreign key constraint alter table table1 add constraint foreign key (field that needs to be set as foreign key) references table2 (associated field of parent table) (3) example: CREATE TABLE `tbook` (`bid`int (10) NOT NULL, `bname` varchar (50) NOT NULL, `price` double DEFAULT NULL, `author`varchar (50) DEFAULT NULL, `authorid`int (10) DEFAULT NULL, PRIMARY KEY (`bid`), UNIQUE KEY `author` (`author`)); index of 2.MySQL

   description: used in the database to speed up the query of the table, by using the method of fast path access, quickly locate data and reduce the IO of the disk.

   type: B-Tree index, bitmap index, hash index. The storage strategy for indexes is different in different storage engines:

   -Myisam engine: the address where records are kept in the leaf node

   -Innodb engine: the corresponding data is stored directly in the leaf node. (B+ Tree Index)

The advantage of    index: the establishment of index can accelerate the speed of query and reduce the efficiency of adding, deleting and changing.

Creation of    index: there are two types of index creation: automatic creation and manual creation:

    -automatic creation: when a primary key or unique constraint is defined in the table, the database automatically creates a corresponding unique index.

    -manually created:

# General index create index index_name on t_name (field) # unique index create unique index index_name on t_name (field) # composite index create index index_name on t_name (field1,field2)

Condition for the use of the    index: the index is used only when the query statement is used when the field of the index is set as the filter condition.

Efficiency of    indexing:

Use the: explain select * from emp where ename='scott'; explain keyword when querying to see the efficiency of the index:

About the level of type:

Notes on    's use of indexes:

   -the index is kept separately from the table and takes up a lot of disk space

   -the index improves the performance of retrieving data, but degrades the performance of adding, deleting, and modifying data.

   -limits the number of indexes in the index. The more indexes, the greater the workload of the database.

   -Index is used for data filtering and data sorting (tables have a large amount of data, and some fields are often used for sorting and querying, indexing)

View of 3.MySQL

   introduction: view is a virtual table constructed from several tables and other views, not to save data in physical files, but to dynamically obtain data from the base table in the process of execution, with the table as the underlying layer.

# example: create view create view v_viem as select * from emp1 where deptno= 20: modify view: update v_viem set deptno=10;# query view select * from vault; Note: delete or modify operations are generally not recommended for views, because they will affect the base table. The above case shows that if the condition field for creating a view is modified, but the view is still queried according to the original condition when querying, the data of the query will be inaccurate. # Control view cannot modify fields: CREATE OR REPLACE VIEW v_emp AS SELECT * FROM emp WHERE deptno = 20 WITH CHECK OPTION; Note: use the above method to create a view. If you want to modify the condition field of the view, an error of 1369-CHECK OPTION failed 'db1807.v_emp', will occur.

   's operation on the view

# query View select * from vandalism emptiness # insert data insert into v_emp (field1,field2,field3) values (value1,value2,value3) # rebuild View create or REPLACE view t_view as select * from emp where sal > 200 destroy View drop view view_name

Advantages of    view:

   -Security: bao secret sensitive fields

   -improve query efficiency

   -customized SQL: data that is often used in multiple tables can be placed in a view for quick query.

Creating users and authorizations of 4.MySQL

   originally thought that this part was not important, no, it should be said that it would not operate MySQL's DCL by itself, because companies all have their own database administrators, and we all have special permission restrictions to use MySQL through specific users, but after several times of installation of the cluster, we found that when the initial hadoop platform was built, the authorization of this MySQL had to be done by ourselves. Each time because of hive initialization Metabase, Azkaban components installation entangled for a long time. All right, sum it up for yourself, so as not to struggle next time.

(1) create MySQL & & Delete users

Syntax:

CREATE USER 'username'@'host' IDENTIFIED BY' password';username: the user name you will create host: specify the host on which the user can log in. If the local user can use localhost, if you want the user to log in from any remote host, you can use the wildcard% password: the user's login password can be empty. If it is empty, the user can log in to the server without a password.

Example: CREATE USER 'hadoop'@'localhost' IDENTIFIED BY' 123456 creation USER 'hadoop'@'%' IDENTIFIED BY' 123456 creation USER 'hive'@'%' IDENTIFIED BY'; CREATE USER 'azkaban'@'%'

After you have successfully created the user, you can log in through this user:

$mysql-hmysql_host-uhadoop-p123456

Delete a user:

DROP USER 'username'@'host'; (2) MySQL authorization

   only users with specific permissions can perform specific actions.

Syntax:

GRANT privileges ON dbname.tableanme TO 'username'@'host';privileges: the user's operation rights, such as SELECT,INSERT,UPDATE, etc. If you want to grant the permissions, use ALLdatabasename: database name tablename: table name. If you want to grant the user the corresponding operation permissions on all databases and tables, you can use *, such as *. *

Example: GRANT SELECT, INSERT ON test.user TO 'hadoop'@'%';GRANT ALL ON *. * TO' hadoop'@'%';GRANT ALL ON maindataplus.* TO 'hadoop'@'%'

The operation is usually refreshed after authorization:

Mysql > flush privileges

Revocation of authorization:

REVOKE privilege ON databasename.tablename FROM 'username'@'host'

The content is similar to the authorization operation.

(3) set and modify MySQL user password

Command:

SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword')

If you change the password of the current user:

SET PASSWORD = PASSWORD ("newpassword")

For example:

SET PASSWORD FOR 'hadoop'@'%' = PASSWORD ("123456")

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.