Introduction to MySQL
1. What is a database?
Database is a warehouse that organizes, stores, and manages data according to its data structure. It was produced more than 60 years ago, with the development of information technology and market, especially after the 1990s, data management. It's no longer just about storing and managing data, it's turning into a variety of data management methods that users need. There are many types of databases, ranging from the simplest tables that store various data to the large database systems that can store massive amounts of data.
The mainstream databases are: sqlserver, mysql, Oracle, SQLite, Access, MS SQL Server, etc. This article mainly describes mysql
2. What is the use of database management?
a. Save the data to a file or memory
b. Receive a specific command and then perform the corresponding operation on the file
PS: If you have the above management system, you don't have to create files and folders yourself. Instead, you can directly pass commands to the above software to let them perform file operations. They are collectively called Database Management System (DBMS).
MySQL installation
MySQL is an open source relational database management system (RDBMS) that uses the most common database management language, Structured Query Language (SQL), for database management. MySQL is one of the best RDBMS (Relational Database Management System) applications for web applications.
Use mysql must have the following conditions
a. Install the MySQL server
b. Install the MySQL client
c. [Client] connection [server]
d. [Client] Send the command to the [Server MySQL] service accept command and perform the corresponding operation (addition, deletion, change, etc.)
MySQL operation
First, connect to the database
Mysql -u user -p Example: mysql -u root -p
Common mistakes are as follows:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.
Exit the connection:
QUIT or Ctrl+D
Second, view the database, create a database, use the database to view the database:
Show databases;
Default database:
Mysql - user permissions related data test - for user test data information_schema - MySQL itself architecture related data
Create a database:
Create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8code create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk encoding
Use the database:
Use db1;
Display all the tables in the currently used database:
SHOW TABLES;
Third, user management
Create user
Create user 'username'@'IP address' identified by 'password';
delete users
Drop user 'username'@'IP address';
Modify user
Rename user 'username'@'IP address'; to 'new username'@'IP address';
change Password
Set password for 'username'@'IP address' = Password('new password');
Note: User rights related data is stored in the user table of the mysql database, so you can also directly operate it (not recommended)
Fourth, authority management
Mysql has the following restrictions on permissions:
All privileges: all permissions except grant: select only check permissions, insert: check and insert permissions...usage: no access permissions alter: use alter tablealter routine: use alter procedure and drop procedurecreate: use create tablecreate routine: use Create procedurecreate temporary tables: use create temporary tablescreate user: use create user, drop user, rename user and revoke all privilegescreate view: use create viewdelete: use deletedrop: use drop tableexecute: use call and stored procedure file: use select into outfile and load Data infilegrant option: use grant and revokeindex: use indexinsert: use insertlock tables: use lock tableprocess: use show full processlistselect: use selectshow databases: use show databasesshow view: use show viewupdate: use updatereload: use flushshutdown: use mysqladmin shutdown (close MySQL Super: Use change master, kill, logs, purge, master, and set global. Also allows mysqladmin debug login replication client: server location access replication slave: used by replication slaves
For the database and other internal permissions are as follows:
Database name.* All database names in the database. Table Specifies a table database name in the database. Stored procedure Specifies the stored procedure in the database *.* All databases
The permissions for users and IP are as follows:
The user name @IP address can only be accessed under the IP address to access the user name @192.168.1.% The user can only access it under the modified IP segment (wildcard % means any) User name @% The user can access it under any IP address ( The default IP address is %)
1, view permissions:
Show grants for 'user'@'IP address'
2. Authorization
Grant permission on database. table to 'user'@'IP address'
3. Deauthorization
Revoke permission on database. table from 'username'@'IP address'
The authorization examples are as follows:
Grant all privileges on db1.tb1 TO 'username'@'IP'grant select on db1.* TO 'username'@'IP'grant select,insert on *.* TO 'username'@'IP'revoke select On db1.tb1 from 'username'@'IP'
MySQL table operation
First, view the table
Show tables; # View database all tables select * from table name; # View table all content
Second, create a table
Create table table name (column name type can be empty, column name type can be empty) ENGINE=InnoDB DEFAULT CHARSET=utf8
Come to an example to explain
CREATE TABLE `tab1` ( `nid` int(11) NOT NULL auto_increment, `name` varchar(255) DEFAULT zhangyanlin, `email` varchar(255), PRIMARY KEY (`nid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Note:
By default, you can specify a default value when creating a column, and automatically add a default value if you do not actively set it when inserting data.
Self-increase, if you set a self-increment column for a column, you do not need to set this column when inserting data. The default will be self-incrementing (only one self-increment column in the table). Note: 1. For self-incrementing columns, it must be an index (including primary key). 2, for self-increment can set the step size and start value
Primary key, a special unique index that does not allow null values. If the primary key uses a single column, its value must be unique. If it is multiple columns, its combination must be unique.
Third, delete the table
Drop table table name
Fourth, clear the table contents
Delete from table name truncate table table name
V. Revision table
Add column: alter table table name add column name type delete column: alter table table name drop column column name modify column: alter table table name modify column column name type; -- type alter table table name change original column name new column name type ; -- column name, type add primary key: alter table table name add primary key (column name); delete primary key: alter table table name drop primary key; alter table table name modify column name int, drop primary key; add foreign key: Alter table from the table add constraint foreign key name (form: FK_ from the table _ main table) foreign key from the table (foreign key field) references primary table (primary key field); delete foreign key: alter table table name drop foreign key The key name is modified by default: ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; delete the default value: ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
Is it a lot of trouble for these operations, it is a waste of time, don't panic! There are special software that can provide these functions. It is very simple to operate. The software name is Navicat Premium. Everyone downloads and exercises on the Internet. However, the following is about to mention the contents of the table.
Sixth, the basic data type
MySQL data types are roughly divided into: numeric, time, and string
Bit[(M)] binary bit (101001), m represents the length of the binary bit (1-64), default m=1tinyint[(m)] [unsigned] [zerofill] small integer, data type is used to save some range Integer value range: Signed: -128 to 127. Unsigned: 0 to 255 Special: There is no Boolean value in MySQL, constructed using tinyint(1). Int[(m)][unsigned][zerofill] Integer, the data type is used to hold some range of integer value ranges: Signed: -2147483648 ~ 2147483647 Unsigned: 0 to 4294967295 Special: m in integer type is only used Display, there is no limit to the storage range. For example: int(5), when inserting data 2, the data when select is displayed as: 00002bigint[(m)][unsigned][zerofill] Large integer, data type is used to save some range of integer value range: Signed: - 9223372036854775808 ~ 9223372036854775807 Unsigned: 0 ~ 18446744073709551615decimal[(m[,d])] [unsigned] [zerofill] The exact fractional value, m is the total number of digits (negative number is not counted), and d is the number after the decimal point. The maximum value of m is 65 and the maximum value of d is 30. Special: This type of decaimal is required for accurate numerical calculations. The reason for storing exact values ​​is that they are stored internally as strings. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] Single-precision floating-point numbers (inaccurate decimal values), m is the total number of digits, and d is the number after the decimal point. Unsigned: -3.402823466E+38 to -1.175494351E-38, 0 1.175494351E-38 to 3.402823466E+38 Signed: 0 1.175494351E-38 to 3.402823466E+38 **** The higher the value, the less accurate* ***DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] Double-precision floating-point numbers (inaccurate decimal values), m is the total number of digits, and d is the number after the decimal point. Unsigned: -1.7976931348623157E+308 to -2.2250738585072014E-308 0 2.2250738585072014E-308 to 1.7976931348623157E+308 Signed: 0 2.2250738585072014E-308 to 1.7976931348623157E+308 **** The bigger the value, the less accurate ** The **char (m) char data type is used to represent a fixed-length string and can contain up to 255 characters. Where m represents the length of the string. PS: Even if the data is less than m length, it will take up m length. varchar(m) The varchars data type is used for variable length strings and can contain up to 255 characters. Where m represents the maximum length of the string allowed to be saved by the data type, as long as the string whose length is less than the maximum value can be saved in the data type. Note: Although varchar is more flexible to use, the char data type is faster to process from the perspective of the overall system performance, and sometimes even exceeds 50% of varchar processing speed. Therefore, the user should consider all aspects when designing the database, in order to achieve the best balance text text data type is used to save large strings of variable length, which can be grouped up to 65535 (2**16 − 1) character. Mediumtext A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.longtext A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.enum Enumeration type, An ENUM column Can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) Example: CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large ', 'x-large') ); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small Example: CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) ) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');DATE YYYY -MM-DD(1000-01-01/9999-12-31)TIME HH:MM:SS('-838:59:59'/'838:59:59')YEAR YYYY(1901/2155)DATETIME YYYY - MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y)TIMESTAMP YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 Time)
MySQL table content operation
Table content operation is nothing more than additions, deletions and changes, of course, the most used is still to check, and check this piece of the most, the most difficult to use, of course, for the great god that is so easy, for my white is still very difficult to be flexible Use, let's take a look at the following
First, increase
Insert into table (column name, column name...) values ​​(value, value, ...) insert into table (column name, column name...) values ​​(value, value, ...), (value, Value, value...)insert into table (column name, column name...) select (column name, column name...) from table: insert into tab1(name,email) values('zhangyanlin',' ')
Second, delete
Delete from table # Delete all data in the table delete from the table where id=1 and name='zhangyanlin' # Delete ID =1 and name='zhangyanlin' that row of data
Third, change
Update table set name = 'zhangyanlin' where id>1
Fourth, check
Select * from table select * from table where id > 1select nid, name, gender as gg from table where id > 1
Checking the conditions of this piece is too much. I give it to the list. As for the combination, I still have to understand the understanding of everyone.
a, conditional judgment where
Select * from table where id > 1 and name != 'aylin' and num = 12; select * from table where id between 5 and 16; select * from table where id in (11,22,33) select * from table where Id not in (11,22,33)select * from table where id in (select nid from table)
b, wildcard like
Select * from table where name like 'zhang%' # zhang at the beginning of all (multiple strings) select * from table where name like 'zhang_' # zhang all at the beginning (a character)
c, limit limit
Select * from table limit 5; - the first 5 lines select * from table limit 4, 5; - 5 lines from the 4th line select * from table limit 5 offset 4 - 5 lines from the 4th line
d, sort asc, desc
Select * from table order by column asc - according to "columns" from small to large permutation select * from table order by column desc - according to "columns" from large to small permutation select * from table order by column 1 desc, column 2 asc - according to "Column 1" is arranged from large to small, and if they are the same, sorted by column 2 from small to large
e, grouping group by
Select num from table group by numselect num, nid from table group by num, nidselect num, nid from table where nid > 10 group by num, nid order nid descselect num, nid, count(*), sum(score), max( Score),min(score) from table group by num,nidselect num from table group by num having max(id) > 10 special: group by must be after where, before order by
Ok, let's get started with MySQL. After practice, some simple additions, deletions, and changes to the task have not been a problem. At this time, we should consider the advanced.
Limited to the topic and length of this article, we will not say more here, if you need students can go to the official MySQL documentation: https://dev.mysql.com/doc/ to view, English is not very good students also Can go to the rookie tutorial: http://.
MySQL resources
In addition to an introductory tutorial, Xiao Bian also prepared a gift for everyone, that is MySQL resources.
Workers must first sharpen their tools when they want to do their best. With these tools, the gap between us and the great gods will be much smaller, and many tools will help us in our daily promotion. With the toolkit, Mommy will Don't worry about your study!
analyzing tool
Performance, structure and data analysis tools
Anemometer – an SQL slow query monitor.
Innodb-ruby – A parser for InooDB format files for the Ruby language.
Innotop – A MySQL version of the 'top' tool with multiple features and extensibility.
Pstop – A class top program for MySQL that collects, summarizes, and presents information from performance_schema.
Mysql-statsd – A Python daemon that collects MySQL information and sends it to Graphite via StatsD.
Backup
Backup/storage/recovery tool
MyDumper – a logical, parallel MySQL backup/dump tool.
MySQLDumper – a web-based open source backup tool - very useful for sharing virtual hosts.
Mysqldump-secure – A mysqldump security script that integrates encryption, compression, logging, blacklisting, and Nagios monitoring.
Percona Xtrabackup - an open source hot backup utility for MySQL - does not lock your database during server backups.
Performance Testing
a tool to test your server
Iibench-mysql - Java-based MySQL/Percona/MariaDB index for insertion performance testing tools.
Sysbench – a modular, cross-platform and multi-threaded performance testing tool.
Chat app
Script integrated into the chat room
Hubot MySQL ChatOps
Configuration
MySQL configuration examples and instructions
Mysql-compatibility-config - Makes MySQL more like a new (or previous) version of MySQL.
Connector
MySQL connector for multiple programming languages
Connector/Python – A standardized database driver for the Python platform and development.
Go-sql-driver – A lightweight, extremely fast MySQL driver for the Go language.
libAttachSQL – libAttachSQL is a lightweight, non-blocking C language API for the MySQL server.
MariaDB Java Client – ​​The LGPL-licensed MariaDB client library for Java applications.
MySQL-Python – A MySQL database connector for the Python language.
PHP mysqlnd – MySQL native driver for MySQL, deprecating the outdated libmysql base driver.
Development
Tools that support MySQL related development
Flywaydb – database migration; easily and reliably evolve your database version in any situation.
Liquibase – source code control of your database.
Propagator – Centralized mode and data deployment on a multidimensional topology.
GUI
Front-end and application GUI
Adminer – a database management tool written in PHP.
HeidiSQL – MySQL graphical management tool for Windows.
MySQL Workbench – an integrated tool environment for database administrators and developers for database design and modeling; SQL development; database management.
phpMyAdmin – an open source software written in PHP that is intended to manage MySQL on the web.
SequelPro – A database management application that runs MySQL under a mac.
Mycli – a terminal MySQL client with auto-completion and syntax highlighting
HA
High availability solution
Galera Cluster – A multi-host clustering solution based on synchronous replication.
MHA – an excellent high availability manager and tool for MySQL
MySQL Fabric – an extensible framework for managing MySQL farms (Server Farms).
Percona Replication Manager – an asynchronous replication management agent for MySQL. Supports file and GTID-based replication, geo-distributed clustering using booth.
proxy
MySQL agent
MaxScale – an open source, database-centric agent.
Mixer – Go implements a MySQL proxy that provides a simple solution for MySQL sharding.
MySQL Proxy – A simple program between your client and the MySQL server that detects, analyzes, or changes their communication.
ProxySQL – a high performance MySQL proxy.
copy
Copy related software
Orchestrator – a tool for managing and visualizing MySQL replication topology.
Tungsten Replicator – A high performance, open source, data replication engine for MySQL.
mode
Additional mode
Common_schema – The framework for the MySQL DBA, which provides an interpreter with function libraries, view libraries, and query scripts.
Sys – a collection of views, functions, and procedures to help MySQL administrators gain a deeper understanding of the use of MySQL databases.
server
MySQL server flavors
MariaDB – A community-developed branch of the MySQL server.
MySQL Server & MySQL Cluster – Oracle's official MySQL server and MySQL cluster distribution.
Percona Server – an enhanced version of MySQL replacement
WebScaleSQL – WebScaleSQL, version 5.6, based on the MySQL 5.6 community version.
Fragmentation
Sharding solution/framework
Vitess – For large-scale web services, vitess provides services and tools to facilitate scaling of MySQL databases.
Jetpants – an automation suite for managing large-scale fragmented clusters, developed by Tumblr.
Toolkit
Toolkit, generic script
Go-mysql – A pure go library for handling network protocols and replication for MySQL.
MySQL Utilities - A collection of command line utilities written in the Python language for maintaining and managing single or multiple layers of MySQL.
Percona Toolkit – An advanced set of command line tools for performing tasks that are too difficult or complex for MySQL servers and systems.
Openark kit – A set of practical tools for everyday maintenance, including some complex or hands-on, written in Python.
UnDROP – A tool to recover data from deleted or corrupted InnoDB tables.
Interface Encoders Decoders Converters
Interface Encoders Decoders Converters,Integrated Circuit Oem Dip Original,Temperature Sensor Ic,Pptc Resettable Circuit Protection Fuse
Shenzhen Kaixuanye Technology Co., Ltd. , https://www.icoilne.com