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

Database MYSQL Learning Series II

MySQL database objects and applications

2.1-MySQL data type

There is more than one Number

× × ×

Floating point type

× × ×

INT

SMALLINT

MEDIUMINT

BIGINT

Type

Storage

Minumun Value

Maximum Value

(Bytes)

(Signed/Unsigned)

(Signed/Unsigned)

TINYINT

one

-128

one hundred and twenty seven

0

two hundred and fifty five

SMALLINT

two

-32768

32767

0

65535

MEDIUMINT

three

-8388608

8388607

0

16777215

INT

four

-2147483648

2147483647

0

4294967295

BIGINT

eight

-9223372036854775808

9223372036854775807

0

18446744073709551615

A cliche question

Int (11) VS int (21) storage space, or is there a difference in storage scope?

The answer is: the two are exactly the same, except that the number of digits of the completion 0 is not the same when displayed.

This can be verified by the following example:

Create table t (an int (11) zerofill, b int (21) zerofill); insert into t values (1,1); select * from t

MySQL does not have 0 completion by default.

It's just that in some special cases, the two appear to be different, and their essence is exactly the same.

Floating point type

FLOAT (M, D)

DOUBLE (M, D)

Attribute

Storage space

Precision.

Accuracy

Float

4 bytes

Single precision

Imprecise

Double

8 bytes

Double precision

Higher precision than Float

Precision loss problem

Loss of accuracy

An example:

Create table t (an int (11), b float (7, 4)); insert into t values (2, 123.12345); select * from t

Fixed points-a more precise number typ

DECIMAL

O High-precision data types, often used to store transaction-related data

O DECIMAL (M, N). M represents the total precision, and N represents the number of digits to the right of the decimal point (scale).

O 1

< M < 254, 0 < N < 60; o 存储空间变长 性别、省份信息 一般使用tinyint、char(1)、enum类型。 经验之谈 · 存储性别、省份、类型等分类信息时选择TINYINT或者ENUM · BIGINT存储空间更大，INT和BIGINT之间通常选择BIGINT · 交易等高精度数据选择使用DECIMAL 存储用户名的属性 · CHAR · VARCHAR · TEXT CAHR与VARCHAR · CHAR和VARCHAR存储的单位都是字符 · CHAR存储定长，容易造成空间的浪费 · VARCHAR存储变长，节省存储空间 字符与字节的区别 编码\输入字符串 网易 netease gbk(双字节) varchar(2)/4 bytes varchar(7)/7 bytes utf8(三字节) varchar(2)/6 bytes varchar(7)/7 bytes utf8mb4(四字节) varchar(2) ? varchar(7)/7 bytes 对于utf8mb4号称占用四字节但是并不绝对。如果在utf8可以覆盖到的范围则仍然占用3字节。 utf8mb4最有优势的应用场景是用于存储emoji表情 emoji表情 · MySQL版本 >

5.5.3

JDBC driver version > 5.1.13

The encoding of libraries and tables is set to utf8mb4

The difference between TEXT and CHAR and VARCHAR

CHAR and VARCHAR are stored in characters

TEXT is stored in bytes, with a total size of 65535 bytes, approximately 64KB

The maximum CHAR data type is 255characters.

VARCHAR data type is variable length storage, which can exceed 255characters.

TEXT is mostly stored in overflow pages within MySQL, so it is not as efficient as CHAR.

An example:

Create table t (a char); create table t (a varchar)

Store avatar

BLOB

BINARY

Poor performance is not recommended

wise remark of an experienced person

The length defined by CHAR and VARCHAR is character length, not byte length.

It is recommended to use VARCHAR (N) to store strings. N is as small as possible.

Although the database can store binary data, its performance is poor. Do not use the database to store binary data such as files, audio, etc.

Store birthday information

DATE

TIME

DATETIME

TIMESTAMP

BIGINT

What's the difference between time types?

The difference in storage space

O DATE three bytes, such as 2015-05-01

O TIME three bytes, such as 11:12:00

O TIMESTAMP, such as: 2015-05-01 11 purge 1200

O DATETIME octet, such as: 2015-05-01 11 purl 12 purl 00

The difference of storage accuracy

O DATE is accurate to the year, month and day

O TIME accurate to hours, minutes and seconds

O TIMESTAMP and DATETIME both include both of the above

TIMESTAMP VS DATETIME

The difference in storage range

O TIMESTAMP storage range: 1970-01-01 00 to 2038-01-19 03:14:07

O Storage range of DATETIME: 1000-01-01 00:00:00 to 9999-12-31 23:59:59

MySQL after version 5.6.4, TimeStamp and DateTime support subtlety

The relationship between field types and urban areas

O TIMESTAMP will convert based on the system time zone, but DATETIME will not

Relationship between field type and time zone

Internationalized system

An example:

Create table test (a datetime, b timestamp); select now (); insert into test values (now (), now ()); select * from test;set time_zone ='+ 00 select now * from test

How BIGINT stores time types

The application converts time to a numeric type

2.2-MySQL data objects what are the common data objects in MySQL

DataBase/Schema

Table

Index

View/Trigger/Function/Procedure

Library, table, row-level relationships

One DataBase corresponds to one Schema.

A Schema contains one or more tables

A table contains one or more fields

A table contains one or more records

A table contains one or more indexes

Multiple DataBase uses

Service isolation

Resource isolation

What are the commonly used data objects on the table

Index

Constrain

Views, triggers, functions, stored procedures

What is a database index

How to quickly locate a chapter when reading

O find the book catalogue

O bookmark your favorite chapters and locate them directly.

An index is a directory of data in a database (indexes and data are stored separately)

O Index and data are two objects.

O Index is mainly used to improve the query efficiency of the database.

O data changes in the database also need to synchronize changes in index data.

How to create an Index (1)

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_option]

[algorithm_option | lock_option]...

Index_col_name:

Col_name [(length)] [ASC | DESC]

Index_type:

USING {BTREE | HASH}

How to create an Index (2)

ALTER [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification]...]

[partition_options]

Alter_specification:

Table_options

| | ADD [COLUMN] col_name column_definition |

[FIRST | AFTER col_name]

ADD [COLUMN] (col_name column_definition,...)

ADD {INDEX | KEY} [index_name]

[index_type] (index_col_name,...) [index_option]...

| | ADD [CONSTRAINT [symbol]] PRIMARY KEY |

[index_type] (index_col_name,...) [index_option]...

| | ADD [CONSTRAINT [symbol]] |

UNIQUE [INDEX | KEY] [index_name]

Constraint

What are the constraints in life?

O everyone's fingerprint information must be unique.

O the requirements of each person are unique

O online shopping requires login before placing an order

Unique constraint

O set a unique key constraint on a field or fields of a table to ensure that the corresponding data in the table must be unique, such as user ID, mobile phone number, × ×, and so on.

Create a unique constraint

The unique constraint is a special index.

The only constraint can be one or more fields

The unique constraint can be created when the table is created, or it can be added later

The primary key is also the only constraint

Unique constraint

Take the following table as an example

CREATE TABLE `order` (

`id`int (10) unsigned NOT NULL AUTO_INCREMENT

`orderid` int (10) unsigned NOT NULL

`bookid` int (10) unsigned NOT NULL DEFAULT'0'

`userid`int (10) unsigned NOT NULL DEFAULT'0'

`number`tinyint (3) unsigned NOT NULL DEFAULT'0'

`address`varchar (128) NOT NULL DEFAULT''

`postcode`varchar (128) NOT NULL DEFAULT''

`orderdate`datetime NOT NULL DEFAULT '0000-00-0000: 0000'

`status` tinyint (3) unsigned zerofill DEFAULT '000'

PRIMARY KEY (`id`)

UNIQUE KEY `idx_ orderid` (`orderid`)

UNIQUE KEY `idx_uid_ orderid` (`userid`, `orderid`)

KEY `bookid` (`bookid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

What is the index?

O Primary key index ID

O single key index orderid

O single key index bookid

O Composite index (userid + orderid)

What are the only constraints?

O Primary key constraint (ID)

O single key unique index (orderid)

O combined unique index (userid + orderid)

Add unique constraint

Add primary key

O alter table `order` add primary key (id)

Add a unique index

O alter table `order` add unique key idx_uk_orderid (orderid)

Foreign key constraint

A foreign key means that the data of two tables are related by certain conditions.

Create a foreign key constraint

Associate the user table with the order table through a foreign key

O alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY (userid) REFERENCES user (userid)

Considerations for using foreign keys

O must be an INNODB table. Myisam and other engines do not support foreign keys.

O mutually constrained field types must be the same.

O constraint fields of the main table require an index

O constraint names must be unique, even if they are not on a table

View

Product demand

O if colleagues from other departments want to query the data in our database, but we do not want to expose the table structure and provide them with only part of the data

The role of View

The result set of a view consisting of a set of query statements is a virtual structure, not actual data.

Views can simplify database access and structure multiple query statements into a virtual structure.

View can hide the back-end table structure of the database and improve the security of the database

View is also a kind of rights management, which only provides part of the data to the user.

Create View

Create a view of a completed order

O create view order_view as select * from `order` where status=1

Trigger

Product demand

O with the improvement of the customer's personal level, the system needs to update the user's points automatically, in which there are two tables, namely, the user information table and the score table.

Trigger is commonly known as trigger, which means that you can do some other actions before or after the data is written to Table A.

Use Trigger to update the score table every time you update the user table

What else is there?

Function

Procedure

2.3-MySQL Rights Management the necessary conditions for connecting to MySQL

The network should be unobstructed.

The user name and password should be correct

IP whitelist needs to be added to the database

Finer-grained validation (libraries, tables, column permission types, etc.)

What permissions does the data have?

The show privileges command can view all permissions

Permission granularity

Data Privileges

O DATA: SELECT, INSERT, UPDATE, DELETE

Definition Privileges

O DataBase: CREATE, ALTER, DROP

O Table: CREATE, ALTER, DROP

O VIEW/FUNCTION/TRIGGER/PROCEDURE: CREATE, ALTER, DROP

Administrator Privileges

O Shutdown DataBase

O Replication Slave

O Replication Client

O File Privilege

MySQL weighting operation

GRANT

Priv_type [(column_list)]

[, priv_type [column_list]]...

ON [object_type] priv_level

TO user_specification [, user_specification]...

[REQUIRE {NONE | ssl_option [[AND] ssl_option]...}]

[WITH with_option...] GRANT PROXY ON user_specification

TO user_specification [, user_specification]...

[WITH GRANT OPTION]

How to create a new user and empower it

Use the commands that come with MySQL

O CREATE USER 'netease'@'localhost' IDENTIFIED BY' netease163'

O GRANT SELECT ON *. * TO 'netease'@'localhost' WITH GRANT OPTION

Other methods

Change database records

O first insert a record into the User table and choose whether or not to insert the record into the db and table_pirv tables according to your needs

O execute the flush privileges command to make the permission information effective

A simpler way.

The GRANT statement determines whether the user exists, and if it does not, create a new

O GRANT SELECT ON *. * TO 'NETEASE'@'localhost' IDENTIFIED BY' netease163' WITH GRANT OPTION

View the user's permission information

View the permissions of the current user

O show grants

View the permissions of other users

O show grants for netease@'localhost'

How to change a user's permissions

Reclaim unwanted permissions

O revoke select on *. * from netease@'localhost'

Re-empower

O grant insert on *. * to netease@'localhost'

How to change a user's password

Re-authorize with the new password, grant statement

Change the database record, Password field of the Update User table

O Note: in this way, flush privileges refresh permission information is required after the change, and it is not recommended.

Delete user

DROP USER user [, user]...

With Grant Option

Allow the person to whom the right is granted to grant the right to another person

MySQL permission information storage structure

MySQL permission information is stored in the database table

The password corresponding to the MySQL account is also encrypted and stored in the database table

Each permission type is an enumerated type in the metadata, indicating whether the permission is available.

What permissions are related to the table?

User

Db

Table_pirv

Columns_pirv

Host

Permission verification process

When querying, validate from user- > db- > table_pirv- > columns_pirv, and execute the query if it passes.

Summary

MySQL permission information is stored in tables in the database in the form of data records.

MySQL's permission verification has more whitelist links than website login, and the granularity is finer, which can be accurate to tables and fields.

What are the problems with MySQL permissions?

Using Binary binary installation admin user did not set password

The default test library of MySQL is not subject to permission control, so there is a security risk.

Mysql_secure_installation

You can set a Password for root accounts.

You can remove root accounts that are accessible from outside the localhost.

You can remove anonymous-user accounts.

You can remove the test database.

Summary

Do not directly manipulate the table for permission-related operations, but use the MySQL command.

After installing MySQL using binary, you need to reset the password of the administrative user (root).

Don't leave test library in online database.

Practice course: what is the table structure design of database objects?

Table structure design needs to be completed before formal development

Abstract the complex business model according to the product requirements

What should be paid attention to when designing a table?

Understand the dependencies of each table

Understand the functional features of each table

O constraints between fields, indexes

O Field type, field length

Collection table properties

Nickname

Birthday

Sex

Mobile phone number

Residential number

Postcode

Residential address

Registered address

Log in to IP

Last login time

Email address

Understand the functional features of tables-- data use

Create table tb_account (

Account_id int not null auto_increment primary key

Nick_name varchar (20)

True_name varchar (20)

Sex char (1)

Mail_address varchar (50)

Phone1 varchar (20) not null

Phone2 varchar (20)

Password varchar (30) not null

Create_time datetime

Account_state tinyint

Last_login_time datetime

Last_login_ip varchar (20)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Create table tb_goods (

Good_id bigint not null auto_increment primary key

Goods_name varchar (100) not null

Pic_url varchar (500) not null

Store_quantity int not null

Goods_note varchar (4096)

Producer varchar (500)

Category_id int not null

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Create table tb_goods_category (

Category_id int not null auto_increment primary key

Category_level smallint not null

Category_name varchar (500)

Upper_category_id int not null

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Create table tb_order (

Order_id bigint not null auto_increment primary key

Account_id int not null

Create_time datetime

Order_amount decimal (12pr 2)

Order_state tinyint

Update_time datetime

Order_ip varchar (20)

Pay_method varchar (20)

User_notes varchar (500)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Create table tb_order_item (

Order_item_id bigint not null auto_increment primary key

Order_id bigint not null

Goods_id bigint not null

Goods_quantity int not null

Goods_amount decimal (12pr 2)

Uique key uk_order_goods (order_id, goods_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Data types-naming conventions

All table names and field names are in lowercase letters

Table names are distinguished by different prefixes for different businesses.

The generation environment table name field name should have practical meaning.

Try to use the full field name for a single field; multiple fields are separated by an underscore

Field design specification

Field type selection, and try to select the smallest data type that can meet the application requirements.

Try to use × × instead of characters. It is more efficient to spend less on field length and index size, such as zip code field, mobile phone number, etc.

Note, each field must give the role of the field in a comment statement

Frequently accessed large fields need to be placed in a separate table to avoid reducing the efficiency of sql. Large file data such as pictures and movies are prohibited from being stored in the database.

Utf8mb4 character set is recommended for new business unification.

User empowerment

Understand what permissions the user needs

O ordinary users only have access to read and write data.

O the system administrator has super privileges

The granularity of permissions should be as fine as possible

O ordinary users do not set the with grant option attribute

O permission granularity: system level > library level > surface level > field level

Prohibit simple passwords

O online password is required randomly

Advanced 2.4-SQL language

This course covers the creation of table SQL

-Table structure for `play_ fav`-DROP TABLE IF EXISTS `play_ fav`; CREATE TABLE `play_ fav` (

`userid`bigint (20) NOT NULL COMMENT 'favorites user id'

`play_ id` bigint (20) NOT NULL COMMENT 'playlist id'

`createtime` bigint (20) NOT NULL COMMENT 'Collection time'

`status`int (11) DEFAULT'0' COMMENT 'status, whether to delete'

PRIMARY KEY (`play_ id`, `userid`)

KEY `IDX_ USERID` (`userid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' playlist Collection Table'

-Records of play_fav-- INSERT INTO play_fav VALUES ('2,'0,'0,'0); INSERT INTO play_fav VALUES ('116,' 1, '1430223383,' 0') INSERT INTO play_fav VALUES ('143,' 1,'0,'0'); INSERT INTO play_fav VALUES ('165,' 2,'0,'0'); INSERT INTO play_fav VALUES ('170,' 3,'0,'0'); INSERT INTO play_fav VALUES ('185, 3,' 0,'0') INSERT INTO play_fav VALUES ('170,' 4,'0,'0'); INSERT INTO play_fav VALUES ('170,' 5,'0,'0')

-Table structure for `play_ list`-DROP TABLE IF EXISTS `play_ list`; CREATE TABLE `play_ list` (

`id`bigint (20) NOT NULL COMMENT 'primary key'

`play_ name`varchar (255) DEFAULT NULL COMMENT 'playlist name'

`userid` bigint (20) NOT NULL COMMENT 'playlist author account id'

`createtime` bigint (20) DEFAULT'0' COMMENT 'playlist creation time'

`updatetime`bigint (20) DEFAULT'0' COMMENT 'playlist update time'

`bookedcount` bigint (20) DEFAULT'0' COMMENT 'number of playlist subscribers'

`trackcount`int (11) DEFAULT'0' COMMENT 'number of songs'

`status`int (11) DEFAULT'0' COMMENT 'status, whether to delete'

PRIMARY KEY (`id`)

KEY `CreateTime` (`createtime`)

KEY `CTIME` (`userid`, `createtime`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' playlist'

-Records of play_list-- INSERT INTO play_list VALUES ('1Qing,' Old Boy', '1Qing,' 1430223383, '1430223383, 558, 6mm, 0') INSERT INTO play_list VALUES ('2songs, prince of love songs', '3songs,' 1430223384songs, '1430223384songs,' 7songs, '3songs,' 0'); INSERT INTO play_list VALUES ('3songs,' daily song recommendations', '5songs,' 1430223385 songs, '14302223385songs,' 1430223385songs, '2songs,' 4songs,'0') INSERT INTO play_list VALUES (1430223386, 1430223386, 1430223386, 5mm, null, 0'); INSERT INTO play_list VALUES (5mm, Li Ronghao, 1Qing, 1430223387, 1430223387, 1430223387, 1mm, 10mm, 0') INSERT INTO play_list VALUES ('6mm,' affectionate','5', '1430223388', '1430223389','0','0','1')

-Table structure for `song_ list`-DROP TABLE IF EXISTS `song_ list`; CREATE TABLE `song_ list` (

`id`bigint (20) NOT NULL COMMENT 'primary key'

`song_ name`varchar (255) NOT NULL COMMENT 'song name'

`artist` varchar (255) NOT NULL COMMENT 'Art Festival'

`createtime`bigint (20) DEFAULT'0' COMMENT 'song creation time'

`updatetime`bigint (20) DEFAULT'0' COMMENT 'song update time'

`album`varchar (255) DEFAULT NULL COMMENT 'album'

`playcount`int (11) DEFAULT'0' COMMENT 'VOD'

`status`int (11) DEFAULT'0' COMMENT 'status, whether to delete'

PRIMARY KEY (`id`)

KEY `IDX_ artist` (`artist`)

KEY `IDX_ album` (`album`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' song list'

-Records of song_list-- INSERT INTO song_list VALUES ('1x, 'Good Lovin\' Gone Bad', 'Bad Company',' 0th, '0th,' Straight Shooter', '453,' 0') INSERT INTO song_list VALUES ('2x, 'Weep No More',' Bad Company','0,'0C, 'Straight Shooter',' 280,'0'); INSERT INTO song_list VALUES ('3, 'Shooting Star',' Bad Company','0,'0, 'Straight Shooter',' 530,'0') INSERT INTO song_list VALUES ('411', 'Elephant','Li Zhi','0','0', '1701', '560','0'); INSERT INTO song_list VALUES ('5', 'Dingxi','Li Zhi','0','0', '1701', '1023','0') INSERT INTO song_list VALUES ('615,' Red Xuelian', 'Hong Qi', '0Qing,' 0Qing, 'Red Xuelian', '220','0'); INSERT INTO song_list VALUES ('7Qing,' people from the wind cabinet','Li Zongsheng', '0Qing,' 0Qing, 'works Li Zongsheng', '566','0')

-Table structure for `stu`-DROP TABLE IF EXISTS `stu`; CREATE TABLE `stu` (

`id`int (10) NOT NULL DEFAULT'0'

`name` varchar (20) DEFAULT NULL

`age`int (10) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

-Records of stu

-Table structure for `tbl_proc_ test`-DROP TABLE IF EXISTS `tbl_proc_ test`; CREATE TABLE `tbl_proc_ test` (

`id`int (11) NOT NULL AUTO_INCREMENT

`num` int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8

-Records of tbl_proc_test-- INSERT INTO tbl_proc_test VALUES ('11,'1'); INSERT INTO tbl_proc_test VALUES ('12,'2'); INSERT INTO tbl_proc_test VALUES ('13,'6') INSERT INTO tbl_proc_test VALUES ('14,'24'); INSERT INTO tbl_proc_test VALUES ('15, '120'); INSERT INTO tbl_proc_test VALUES ('16, '720'); INSERT INTO tbl_proc_test VALUES ('17, '5040'); INSERT INTO tbl_proc_test VALUES ('18, '40320'); INSERT INTO tbl_proc_test VALUES ('19, '362880') INSERT INTO tbl_proc_test VALUES ('20, '3628800'); INSERT INTO tbl_proc_test VALUES ('21,'1'); INSERT INTO tbl_proc_test VALUES ('22,'2'); INSERT INTO tbl_proc_test VALUES ('23,'6'); INSERT INTO tbl_proc_test VALUES ('24,'24); INSERT INTO tbl_proc_test VALUES ('25,'1') INSERT INTO tbl_proc_test VALUES ('26,'2'); INSERT INTO tbl_proc_test VALUES ('27,'6'); INSERT INTO tbl_proc_test VALUES ('28,'24'); INSERT INTO tbl_proc_test VALUES ('29, '120')

-Table structure for `tbl_ test1`-DROP TABLE IF EXISTS `tbl_ test1`; CREATE TABLE `tbl_ test1` (

`user`varchar (255) NOT NULL COMMENT 'primary key'

`key`varchar (255) NOT NULL

`value` varchar (255) NOT NULL

PRIMARY KEY (`user`, `key`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' row-column conversion test'

-Records of tbl_test1---INSERT INTO tbl_test1 VALUES ('li',' age', '18'); INSERT INTO tbl_test1 VALUES (' li', 'dep',' 2') INSERT INTO tbl_test1 VALUES ('li',' sex', 'male'); INSERT INTO tbl_test1 VALUES (' sun', 'age',' 44'); INSERT INTO tbl_test1 VALUES ('sun',' dep','3'); INSERT INTO tbl_test1 VALUES ('sun',' sex', 'female'); INSERT INTO tbl_test1 VALUES (' wang', 'age',' 20'); INSERT INTO tbl_test1 VALUES ('wang',' dep','3') INSERT INTO tbl_test1 VALUES ('wang',' sex', 'male')

-Procedure structure for `proc_ test1`

DROP PROCEDURE IF EXISTS `proc_ test1`

DELIMITER

CREATE DEFINER= `root`PROCEDURE `proc_ test1` (IN total INT,OUT res INT) BEGIN

DECLARE i INT

SET I = 1

SET res = 1

IF total, > =

Greater than or equal to

Column level

Server level

O system variable (can be set dynamically):

§character_set_server: default internal operation character set

§character_set_system: system metadata (field names, etc.) character set

Character set setting level

Server level

Configuration file

[mysqld]

Character_set_server=utf8

Collation_server=utf8_general_ci

Database level

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci

Character_set_database: the default character set of the currently selected database

It mainly affects the default character set of statements such as load data. If the character set of CREATE DATABASE is not set, the character set of character_set_server is used by default.

Table level

CREATE TABLE tbl1 (....) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin

Column level

CREATE TABLE tbl1 (col1 VARCHAR (5) CHARACTER SET latin1 COLLATE latin1_german1_ci)

Character set setting level

Rules for using data storage character sets:

O use the CHARACTER SET setting of the column set

O if the column-level character set does not exist, the corresponding table-level DEFAULT CHARACTER SET setting value is used.

O if the table-level character set does not exist, use the database-level DEFAULT CHARACTER SET setting value

O if the database-level character set does not exist, use the server-level character_set_server setting value.

-- View character set

Show [global] variables like 'character%'

Show [global] variables like 'collation%'

-- modify character set set global character_set_server=utf8;-- Global alter table xxx convert to character set xxx;-- Table

Client connection and character set

Connection and character set

O character_set_client: the character set used by the client source data.

O character_set_connection: connection layer character set.

O character_set_results: query result character set.

Mysql > set names utf8

Profile Settings:

[mysql]

Default-character-set=utf8

Character conversion process

Client > character_set_client > character_set_connection > Storage > character_set_results > client

It is recommended to use a unified character set

Common reasons for garbled codes:

O data storage character set does not correctly encode data from client: client (utf8)-> Storage (latin1)

O the character set used by the program connection is inconsistent or incompatible with the character_set_client that notifies the mysql.

Use suggestion

O explicitly specify the character set when creating the database / table, without using the default.

O the connection character set is consistent with the storage character set. It is recommended to use utf8.

O explicitly specify the character set (set names XXX) when the driver connects.

Mysql CAPI: immediately after initializing the database handle, use mysql_options to set the MYSQL_CHARSET_NAME property to utf8.

Mysql php API: explicitly use the SET NAMES statement to set the connection character set once after connecting to the database.

Mysql JDBC: url= "jdbc:mysql://localhost:3306/blog_dbo?user=xx&password=xx&userUnicode=true&characterEncoding=utf8"

Summary

Character set: represented character set and / character encoding

Setting level of characters: server / database / table / column

Client character set: the cause and solution of garbled code

Basic principles of Program connection MySQL Program connection MySQL

JDBC client applications-> java.sql.* or javax.sql.*-> drivers-> SQLserver/Oracle/MySQL

Java code example

Structure:

DriverManager-> Driver (interface for driver object pointing to specific database driver object) = DriverManager.getDriver (String URL)-> Connectinon (interface for connection object pointing to specific database) = DriverManager.getConnection (String URL)-> Statement (interface for executing static SQL statements) = Connection.CreateStatement ()-> ResultSet (interface to result set object) = Statement.excuteXXX ()

Import java.sql.*

/ * use JDBC to connect MySQL * / public class DBTest {

Public static Connection getConnection () throws SQLException

Java.lang.ClassNotFoundException

{

/ / step 1: load the driver of MySQL's JDBC

Class.forName ("com.mysql.jdbc.Driver")

/ / set MySQL connection string, MySQL database ip, port, user name, password to be accessed

String url = "jdbc:mysql://localhost:3306/blog"

String username = "blog_user"

String password = "blog_pwd"

/ / step 2: create an instance of the connection class to the MySQL database

Connection con = DriverManager.getConnection (url, username, password)

Return con

}

Public static void main (String args []) {

Connection con = null

Try

{

/ / step 3: get the connection class instance con, and create the Statement object class instance sql_statement with con

Con = getConnection ()

Statement sql_statement = con.createStatement ()

/ * perform related operations on the database * /

/ / if the database with the same name exists, delete it

Sql_statement.executeUpdate ("drop table if exists user;")

/ / A sql statement was executed to generate a table named user

Sql_statement.executeUpdate ("create table user (id int not null auto_increment," +)

"name varchar (20) not null default 'name', age int not null default 0, primary key (id);")

/ / insert data into the table

System.out.println ("JDBC insert operation:")

String sql = "insert into user (name,age) values ('liming', 18)"

Int num = sql_statement.executeUpdate ("insert into user (name,age) values ('liming', 18)")

System.out.println ("execute sql:" + sql)

System.out.println (num + "rows has changed!")

System.out.println ("")

/ / step 4: execute the query and use the object of the ResultSet class to return the result of the query

String query = "select * from user"

ResultSet result = sql_statement.executeQuery (query)

/ * perform related operations on the database * /

System.out.println ("JDBC query operation:")

System.out.println ("-")

System.out.println ("userid" + "" + "name" + "+" age ")

System.out.println ("-")

/ / A pair of query results are processed to manipulate the objects of the Result class

While (result.next ())

{

Int userid = result.getInt ("id")

String name = result.getString ("name")

Int age = result.getInt ("age")

/ / get the data in the database

System.out.println ("" + userid + "" + name + "" + age)

}

/ / close result,sql_statement

Result.close ()

Sql_statement.close ()

/ / use PreparedStatement to update records

Sql = "update user set age=? where name=?;"

PreparedStatement pstmt = con.prepareStatement (sql)

/ / set the value of the binding variable

Pstmt.setInt (1,15)

Pstmt.setString (2, "liming")

/ / perform the operation

Num = pstmt.executeUpdate ()

System.out.println ("")

System.out.println ("JDBC update operation:")

System.out.println ("execute sql:" + sql)

System.out.println (num + "rows has changed!")

/ / close PreparedStatement

Pstmt.close ()

/ / streaming read result,row-by-row

Query = "select * from user"

PreparedStatement ps = (PreparedStatement) con.prepareStatement

(query,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

Ps.setFetchSize (Integer.MIN_VALUE)

Result = ps.executeQuery ()

/ * perform related operations on the database * /

System.out.println ("JDBC query operation:")

System.out.println ("-")

System.out.println ("userid" + "" + "name" + "+" age ")

System.out.println ("-")

/ / A pair of query results are processed to manipulate the objects of the Result class

While (result.next ())

{

Int userid = result.getInt ("id")

String name = result.getString ("name")

Int age = result.getInt ("age")

/ / get the data in the database

System.out.println ("" + userid + "" + name + "" + age)

}

/ / close result,ps

Result.close ()

Ps.close ()

Con.close ()

} catch (java.lang.ClassNotFoundException e) {

/ / error loading JDBC, the driver to be used could not be found

System.err.print ("ClassNotFoundException")

/ / other errors

System.err.println (e.getMessage ())

} catch (SQLException ex) {

/ / display database connection error or query error

System.err.println ("SQLException:" + ex.getMessage ())

}

}

}

Skills of using JDBC

The difference between Statement and PreparedStatement

The significance of connection, Statement and ResultSet shutting down

Use of jdbc connection parameters

Use of ResultSet cursors (setFetchSize)

The difference between Statement and PreparedStatement

PreparedStatement is precompiled on the database side with high efficiency and can prevent SQL injection.

When performing one-time access to the database, the Statement object is used for processing.

PreparedStatement is recommended for online business.

The story behind PreparedStatement

PREPARE-> EXECUTE-> DEALLOCATE PREPARE

PREPARE stmt1 FROM 'SELECT productCode, productName From products WHERE productCode =?'; SET @ pc = 'S10' 1678'

EXECUTE stmt1 USING @ pc

DEALLOCATE PREPARE stmt1

The significance of connection, Statement and ResultSet shutting down

The MySQL database maintains the memory state for connection and ResultSet, and keeping it open all the time will consume server resources.

The maximum number of connections in MySQL is limited by max_connections, so connections cannot be created indefinitely, so close them in time after use.

After JDBC connection shuts down, ResultSet, Statement will automatically shut down. However, if connection pooling is used, it will not be closed, so proactive shutdown is recommended.

Use of jdbc connection parameters

Character set settings:

Url= "jdbc:mysql://localhost:3306/blog_dbo?userUnicode=true&characterEncoding=utf8"

Timeout setting:

Url= "jdbc:mysql://localhost:3306/blog_dbo?connectionTimeout=1000&socketTimeout=30000"

Use of ResultSet cursors

The default ResultSet object is not updatable and has only a pointer that moves forward. Therefore, it can only be iterated once, and only in the order from the first line to the last line. You can generate scrollable and / or updatable ResultSet objects.

SetFetchSize () sets the number of rows ResultSet fetches from the database at a time to prevent memory from exploding if the amount of data returned is too large.

Python connection MySQL

Python: scripting language, no need to compile, easy to develop

The general scenario of DBA using Python is to write automated operation and maintenance tools, reports, and data migration.

Python MySQL driver: python-mysqldb

Import MySQLdb

# establish a connection to the mysql database

Conn = MySQLdb.connect (host='localhost', port=3306,user='bloguser',passwd='xxxx') # get cursors

Curs = conn.cursor ()

# Select a database

Conn.select_db ('blog')

# execute SQL to create a table

Curs.execute ("create table blog (id int, name varchar)")

# insert a record

Value = [1, 'user1']

Curs.execute ("insert into blog values (s, s)", value)

# insert multiple records

Values = [(2, "user2"), (3, "user3")]

Curs.executemany ("insert into blog values (s, s)", values)

# submit

Conn.commit ()

# close cursors

Curs.close () # close the connection

Conn.close ()

The use of 2.9-DAO framework DAO framework

Using data access objects (DAO) in the application allows us to separate the underlying data access logic from the business logic. The DAO framework builds classes that provide CRUD (create, read, update, delete) operations for each data source.

DAO pattern is one of the standard J2EE design patterns. Developers use this pattern to separate the underlying data access operations from the high-level business logic. A typical DAO framework implementation has the following sets of operations:

O A DAO factory class

O one DAO interface (select/insert/delete/update)

O A concrete class that implements the DAO interface

O data transfer object

Characteristics of DAO framework

Shielding the underlying data access details to achieve the separation of business logic and data access logic.

Simplify code development and improve code reuse rate.

There may be additional performance losses compared to native SQL (encapsulating objects using reflection mechanisms, SQL conversions, etc.)

Introduction to MyBatis

MyBatis is a mainstream DAO framework and an updated version of iBatis, an open source project of apache.

MyBatis supports normal SQL queries, stored procedures and advanced mappings, eliminating manual settings for almost all JDBC code and parameters, as well as result set retrieval.

Rich interfaces and easy to use

Compared to hibernate, it is more lightweight and supports native sql statements.

Support for query caching

MyBatis code example

The environment is built, and the data comes from the compilation of the mapping configuration file.

Single-valued and multi-valued query

Add, delete and modify data

Connected table query

The sample code is in the sorence/DAO framework code sample .rar

MyBatis workflow

Load the configuration and initialize it, generating the MappedStatement object internally.

Call API (SqlSession.select/insert....) provided by MyBatis to pass SQL ID and data objects to the processing layer.

The processing layer parses the MappedStatement object, obtains the connection of the MySQL, executes the corresponding SQL statement, and receives the returned result.

MyBatis encapsulates the returned results into corresponding data objects to return.

Skills of using MyBatis

Distinguish between the different application scenarios of # {} and ${}: # {} generates a precompiled SQL that correctly processes the type of data, while ${} is just a text replacement.

Note the performance loss when MyBatis encapsulates data: only the number of rows and fields you need are returned.

Use the connection pooling feature that comes with MyBatis:

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.