MySQL Basic introduction

Rdbms:
1, database creation, deletion except
2. Create a table, delete a table, modify a table
3. Creation and deletion of indexes
4. Users and Permissions
5. Data increase, deletion, change
6. Enquiry


Dml:data manapulate Language: Data Manipulation language
INSERT, REPLACE, UPDATE, DELETE
Ddl:data defination lanuage: Data Definition language
CREATE, ALTER, DROP
Dcl:data control Language: Data Controls language
GRANT, REVOKE

SELECT


Rdbms:egresql
Oracle, Sybase, Infomix, SQL Server, DB2
MySQL, PostgreSQL, Pgsql, Enterprisedb

MySQL, SQL, MySQL AB
Go to IOE:IBM, Oralce, EMC


Oracle,

Ibm
Sun-->mysql

Bea:weblogic
Pepolesoft

OpenOffice

LibreOffice

MariaDB

Mysql


-Percona

Inverse Relational Model: NoSQL
Mongodb
Redis
HBase



Dbms:
Data management independence;
Data access is accomplished efficiently;
data integrity and security;
Centralized data management;
Concurrent storage and failure recovery;
Reduce application development time;


Sql:ansi
Sql86, sql89, SQL92, sql99

DDL, DML, DCL


Mysql:
Community edtion
Enterprise edtion

Package format:
Package Manager-specific formats
RPM package,. exe format, etc.
Universal binary Format
SOURCE program



RHEL 5.8 (32bit)
MySQL, Mysql-server

MySQL's RPM package

LAMP:
Mysql
Universal Binary Binary


MySQL, Mysql-server

Binary program
Mysql
-U USERNAME
-P
-H Myser_server

-H 127.0.0.1
Linux:socket
Windows:memory

MySQL client:
Interactive mode
Batch processing mode
Execute MySQL Script


Command categories in interactive mode:
Client commands
Server-side commands
Must use a statement terminator, the default is a semicolon;




SQL interface:
Oracle, PL/SQL
SQL Server, T-SQL



User: [Email protected]

Mysqld

tcp/3306, MySQL, MySQL

Rdbms:
/var/lib/mysql/

Initialization


Relational database objects:
Library
Table
Index
View
Constraints
Stored Procedures
Storage functions
Trigger
Cursor
User


Permissions
Transaction

Table:
Row, column
Table: Entities
Row: Row
Column: Field, column

Field name, data type, type decoration (limit)
Character
CHAR (N)
VARCHAR (N)
BINARY (N)
VARBINARY (N)
TEXT (N)
BLOB (N)
Numerical
Exact values
Integral type
TINYINT
SMALLINT
Mediumint
Int
BIGINT

Modifier: UNSIGNED, unsigned
Not NULL
Decimal
DECIMAL
Approximate values
Floating point Type
FLOAT
DOUBLE
Date Time
DATE
Time
Datetime
STAMP
Boolean
Built in: ENUM, SET


DDL Definition Data Objects
CREATE
Alter
DROP
DML Manipulation Language
INSERT
UPDATE
DELETE
DCL Control Language
GRANT
REVOKE

Create a database
CREATE DATABASE db_name;
CREATE DATABASE [IF not EXISTS] db_name;

DROP DATABASE [IF EXISTS] db_name;

Create a table
CREATE TABLE tb_name (col1,col2,...);

View the tables in the library: show TABLES from Db_name;
View the structure of the table: DESC tb_name;

Delete tables: drop table tb_name;

To modify a table:
ALTER TABLE Tb_name
MODIFY
Change
ADD
DROP

Dml:
INSERT into Tb_name (col1,col2,...) values| VALUE (' STRING ', NUM,...);
INSERT into Tb_name (col1,col2,...) values| VALUE (' string ', num,...), (' String ', num,...);

UPDATE tb_name SET Column=value WHERE

DELETE from Tb_name WHERE CONDITION;

Choose:
SELECT field from Tb_name WHERE CONDITION

*: All fields
WHERE: No condition indicates all rows are displayed;

To create a user:
CREATE USER ' USERNAME ' @ ' HOST ' [identified by ' PASSWORD '];
DROP USER ' USERNAME ' @ ' HOST ';

HOST:
Ip:
HOSTNAME:
NETWORK:
Wildcard characters
_: Matches any single character, 172.16.0._
%: matches any character;

[email protected] '% '
DCL:
GRANT Pri1,pri2,... On Db_name. Tb_name to ' USERNAME ' @ ' HOST ' [identified by ' PASSWORD '];
REVOKE Pri1,pri2,... On Db_name. Tb_name from ' USERNAME ' @ ' HOST ';

To view the user's authorization: Show GRANTS for ' USERNAME ' @ ' HOST ';

All privileges






Selection and projection


MySQL Basic operations exercise (the library called TestDB):

Create a new table (including structure and content) as follows:

ID Name Age Gender Course
1 Ling huchong Male Hamogong
2 Huang Rong Female Chilian Shenzhang
3 Lu Wushaung Female Jiuyang Shenggong
4 Zhu ziliu Male Pixie JIANFA
5 Chen jialuo Male xianglong Shiba Zhang


2. Complete the following operations
(1) Identify all persons with gender as women;
(2) To identify all persons older than 20;
(3) Modify the course of Zhu Ziliu to Kuihua Baodian;
(4) Delete all persons aged less than or equal to 19 years of age;
(5) Create this form and the library to which it belongs;
(6) Authorized to testuser all access rights to the TestDB library;


mysql> FLUSH privileges;

To set a password for a 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 ';


and
OR
Not

Graphics client:
1, PhpMyAdmin
2, Workbench
3. MySQL Front
4. Navicat for MySQL
5, Toad

MySQL Basic introduction