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

The following is mainly to bring you a preliminary overview of mysql, I hope that the preliminary overview of mysql can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

API:Application Programming Interface, application programming interface

ODBC:Open DateBase ConnectionDBMS:DateBase Manage System, database management system data organization structure (logical structure) 1) hierarchical structure 2) mesh structure 3) Relational structure RDBMS:Relational DateBase Manage System, relational database management system

User view DBA view physical view RDBMS should have functions 1, database creation, deletion, modification 2, table creation, deletion, modification 3, index creation, deletion 4, users and permissions 5, data add, delete, change 6, query

Related command

DML:Data Manipulate Language, data manipulation language INSERT,REPLACE,UPFATE,DELETE

DDL:Data Definition Language, data definition language CREATE,ALTER,DROP

DCL:Data Control Language, data control language GRANT,REVOKE

SELECTSQL:Structured Quiry LanguageRDBMS: Oracle,Sybase,Infomix (acquired by IBM), SQL Server (a variant of Sybase), DB2 (IBM) MySQL,egrepSQL → PostgreSQL (pgsql) → EnterpriseDB

Ali de-IOEization, IBM,Oracle,EMC integrated software provider IBM:CPU,AIX (Advanced IBM Unix), CVM, DB2SUN:CPU,Solaris, CVM, MySQL,Java → acquired by Oracle BEA: provide WebLogicPeopleSoft: provide client management software OpenOffice (SUN): acquired by Oracle, privatization failed LibreOffice (OpenOffice author) start a new stove MariaDB The authors of MySQL continue to develop MySQL → (secondary development) Percona non-relational model: NoSQL (a technology) MongoDB: document database

Redis: cache database

HBase: key-based database, sparse database

Functions of DBMS: independence of data management; effective access to data; verification of data integrity and security; centralized management of data; concurrent storage and fault recovery; reduction of application development time. SQL command → parser (parse SQL syntax) → plan executor (how many ways can you accomplish the task) → optimizer → file access method MySQL Community Edition

Enterprise Edtion, added backup function

The format of the www.mysql.comMySQL package on the official website rpm,exe Universal binary format unique to the package Manager

Source program

Client: mysql server: mysqld snooping tcp/3306RDBMS data location / var/lib/mysql installation yum install mysql-server initialization service mysqld start starts for the first time to complete the initialization of metadata within the database. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER! To do so, start the server, then issue the following commands:/usr/bin/mysqladmin-u root password' new-password'/usr/bin/mysqladmin-u root-h hiyang.com password' new-password'Alternatively you can run:/usr/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd / usr; / usr/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.plcd mysql-test Perl mysql-test-run.plPlease report any problems with the / usr/bin/mysqlbug script MySQL-u USERNAME, default root-p, default is empty-h MYSER_SERVER, default localhost-h 127.0.0.1 Linux: socket Windows: memorymysql client: interactive mode batch mode executes mysql script command category in interactive mode: client commands cloud server commands must use statement Terminator, default is semicolon SQL interface: Oracle, PL/SQL SQL Server, T-SQL relational database object table

Indexes

View (virtual table)

Constraint

Stored procedure

Storage function

Trigger

Vernier

User permissions

Business

Tables: rows (row), columns (field,column)

Tables: solid

Field name, data type (strong type), type modifier (restriction)

Take up space scope remarks

Character type

Case insensitive

CHAR (n)

N byte

two hundred and fifty five

VARCHAR (n)

Number1 multi-end modifier

65535

Variable length is case sensitive

BINARY (n)

N byte

VARBINARY (n)

Nicked 1 byte

Variable length

Case-insensitive TEXT (n)

Numb2 byte 65535

Case sensitive BLOB (n)

65535

Binary large object

Numerical value

Integer type

TINYINT

1 byte

two hundred and fifty six

SMALLINT

2 byt

65535

MEDIUMINT

3 bytes

INT

4 byt

NOT NULL

BIGINT

8 byte

UNSIGNED, unsigned

DECIMAL

Decimal approximate value

FLOAT

4 byt

DOUBLE

8 byte

Date and time

DATE

TIME

DATETIME

TIMESTAMP

Boolean

Built in

Enumerate

ENUM

The collection of ENUM ('Mauremeng F')

SET

SET ('Maurem')

Create database create database [if not exists] db_name

Delete the database drop database [if not exists] db_name; view the table show tables from db_name in the library

Create table create table tb_name (col1,col2,...) mysql > create table student (name char (10) not null,age tinyint unsigned,gender char (1) not null); view the structure of the table desc tb_name; delete table drop table tb_name Modify table alter table tb_name modif modify the attributes of field mysql > alter table student modify course varchar (50); change change the name of field mysql > alter table student change course lesson varchar (50); add add fieldmysql > alter table student add high int after age;mysql > alter table student add course varchar (100); drop delete fieldmysql > alter table student drop lesson; insert data insert into tb_name (col1,col2,...) Values | value ('STRING', NUM,...); insert into tb_name (col1,col2,...) Values | value ('STRING', NUM,...), (' STRING', NUM,...),...; insert data mysql > insert into student (name,gender) value ('Li','M'), (' Yang','F') in the specified field; mysql > select * from student +-+ | name | age | high | gender | lesson | +-+ | Li | NULL | NULL | M | NULL | | Yang | NULL | NULL | F | NULL | + -- + No field specified Use the default field mysql > insert into student value ('Zhang',26,162,'M','food') Query OK, 1 row affected (0.00 sec) mysql > select * from student +-+ | name | age | high | gender | lesson | +-+ | Li | NULL | NULL | M | NULL | | Yang | NULL | NULL | F | NULL | | Zhang | 26 | 162m | food | | +-+ modify data update tb_name set column=value WHERE mysql > update student set high=178 where name='yang' | Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > select * from student +-+ | name | age | high | gender | lesson | +-+ | Li | NULL | NULL | M | NULL | | Yang | NULL | 178F | NULL | | Zhang | 26 | 162m | food | | +-+ delete data delete from tb_name where CONDITION | Mysql > delete from student where name='Li'

Select SELECT field FROM tb_name WHERE CONDITION *: all fields do not specify WHERE: show all lines; mysql > select name,high from student where lesson='food';+-+-+ | name | high | +-+-+ | Zhang | 162,162 | create user create user 'username'@'host' [identified by' password']; delete user drop user 'username'@'host'

HOST:IP: HOSTNAME: NETWORK: wildcard _: match any single character, 172.16.0._%: match any character; DCL: authorized user grant pri1,pri2,... On db_name.tb_name to 'username'@'host' [identified by' password']; create and authorize de-authorization revoke pri1,pri2,... directly if it does not exist On db_name.tb_name from 'username'@'host'; check the user's authorization show grants for' username'@'host'; ALL PRIVILEGES add the user's password before you can log in mysql > create user 'jerry'@'%';mysql > show grants for' jerry'@'$';ERROR 1141 (42000): There is no such grant defined for user 'jerry' on host' $' mysql > create user 'tom'@'%' identified by' tom' Query OK, 0 rows affected (0.00 sec) mysql > show grants for 'tom'@'%' +-+ | Grants for tom@% | +-- -- + | GRANT USAGE ON *. * TO 'tom'@'%' IDENTIFIED BY PASSWORD' 675bd1463e544441' | +-- -+ set the password for the user: 1. Mysql > set password for 'username'@'host'=password (' password') 2. # mysqladmin-uusername-hhost-p password' password' 3, mysql > update user set password=password ('password') where user='root' and host='127.0.0.1'; blue password is the function select User,Host,Password from user +-+ | User | Host | Password | root | localhost | 565491d704013245 | | root | hiyang.com | 565491d704013245 | | root | 127.0.0.1 | localhost | | | hiyang.com | | jerry |% | tom |% | 675bd1463e544441 | | root | 192.168.40 | 565491d704013245 | +-+ mysql graphical client tool 1. | PhpMyAdmin2 、 Workbench3 、 MySQL Front4 、 Navicat for MySQL5 、 Toad# yum install php53-php

Test the communication between php and mysql. Phpmyadmin decompresses the downloaded file to DocumentRoot and accesses it by path in the browser.

For the above overview of the preliminary introduction to mysql, do you find it very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.