MySQL (1)

I. Introduction to the database 1, database

A database (DATABASE,DB) is a collection of organized, shareable data that is stored in a computer for a long period of time. The data in the database is organized, described and stored in a certain mathematical model, with small redundancy, high data independence and extensibility, and can be shared by various users.

2. Database management system software

Database Management System is a large-scale software that manipulates and manages databases for the establishment, use, and maintenance of databases, referred to as DBMS. It has unified management and control of the database to ensure the security and integrity of the database. The user accesses the data in the database through the DBMS, and the database administrator maintains the database through the DBMS. It allows multiple applications and users to create, modify, and interrogate databases in different ways, at the same time or at different times. Most DBMS provides data definition language (Language) and data-manipulation language (manipulation Language), which allows users to define schema structure and permissions constraints of the database, to append the data, Delete operations.

Database management system is the core of database system and the software of managing database. Database management system is to realize the user's meaning of abstract logical data processing, transformation into a computer-specific physical data processing software. With a database management system, users can manipulate the data in an abstract sense without having to take into account the layout and physical location of the data in the computer.

Common database management software: Oracle's ORACLE,IBM db2,sql server, Access,mysql (open source, free, cross platform).

3. Database system

The database systems DBS (data Base system, or DBS) is typically comprised of software, database, and data administrators. Its software mainly includes operating system, various host languages, utilities and database management system. The database is managed by the database management system, and the data inserting, modifying and retrieving are all through the database management system. The Data Manager is responsible for creating, monitoring, and maintaining the entire database so that the data can be used effectively by anyone who has access to it.

Second, MySQL1, MySQL management installation

Linux:

--yum-y Install mariadb mariadb-serveror--yum-y install MySQL Mysql-server

Win

Start

--service mysqld start  #开启--chkconfig mysqld on   #设置开机自启OR--systemctl start Mariadb--systemctl enable MARIADB

View

--PS aux |grep mysqld    #查看进程--Netstat-an |grep 3306 #查看端口

Set Password

--mysqladmin-uroot password ' 123 '   #设置初始密码, the initial password is empty so the-p option is not used-mysqladmin-u root-p123 password ' 1234 ' #修改root用户密码

Login

--MySQL               #本地登录, Default user root, empty password, user for [email protected]--mysql-uroot-p1234 #本地登录, specify user name and password, user for [email protected]--MySQL -uroot-p1234-h 192.168.31.95 #远程登录, user is [email protected]

Common commands for MySQL

----start MySQL service with Stop mysql service command:----net start mysql--net stop  mysql------Login with exit command:----    mysql-h server ip-p port number-u< c3/> User name-p password--prompt command prompt  --delimiter specify delimiter--    mysql-h 127.0.0.1-p 3306-uroot-p123--    quit------Exit---- \q,------\s;   ------My.ini file: [MySQL] default-character-set=gbk [mysqld] CHARACTER-SET-SERVER=GBK----prompt command prompt (\d: current date \d: Current database  \u: Current user)----\ t (start log) \ t (end log)----show warnings;----help () \h----\g,----select now ();--select version ();- -Select User,----\c cancel command----delimiter specify delimiter

What if I forget my password? Method 1: Skip the authorization table when you start MySQL

[[email protected] ~]# service mysqld stop[[email protected] ~]# mysqld_safe--skip-grant-table &[[email  protected] ~]# mysqlmysql> Select User,host,password from mysql.user;+----------+-----------------------+-- -----------------------------------------+| user | Host | Password |+----------+-----------------------+------------------------------------------- +| Root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF | | Root |                                           Localhost.localdomain | || Root |                                           127.0.0.1 | || Root |                                           :: 1 |          || |                                           localhost |          || |                                           Localhost.localdomain | || Root | %                     | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |+----------+-----------------------+-------------------------------------------+mysql> update mysql.user set Password=password ("123") where user= "root" and host= "localhost";mysql> flush privileges;mysql> Exit[[email  protected] ~]# service mysqld restart[[email protected] ~]# mysql-uroot-p123

Method 2 (Delete library):

Delete permissions-related libraries MySQL, all the licensing information is lost, mainly for testing the database or just built the library soon no authorization data (from the deletion of the library to the escape) [[email protected] ~]# rm-rf/var/lib/mysql/mysql[[ Email protected] ~]# service mysqld restart[[email protected] ~]# MySQL

2. SQL statements

SQL is an abbreviation for Structured query Language (Structured Query language). SQL is a set of operational commands built for the database and is a fully functional database language.

When using it, only the "What to Do" command is issued, "How to Do" is not considered by the user. SQL is powerful, easy to learn and easy to use, and has become the foundation for database operations, and now almost all databases support SQL.

2.1SQL specification

<1> in a database system, SQL statements are case-insensitive (uppercase is recommended). However, string constants are case-sensitive. Recommended command uppercase, table name Library name lowercase;

<2> SQL statements can be written in single or multiple lines, with ";" End. Keywords cannot span multiple lines or abbreviations.

<3> use spaces and indents to improve the readability of the statement. Clauses are usually located on separate lines, making editing easier and more readable.

SELECT * from tb_table            WHERE name= "YUAN";

<4> Note: single-line Comment:--

Multi-line Comment:/*......*/

<5>sql statement can be wrapped

<6> Ddl,dml and DCL

----the difference between DML, DDL, DCL in SQL.----DML (Data Manipulation language):--    They are select, UPDATE, INSERT, DELETE, just like its name, These 4 commands are used to manipulate the data in the database-the    language------DDL (language):-    more DDL than DML, the main commands are create, ALTER, drop, etc. DDL is primarily used in the initialization of defining or altering tables (table)-    The structure, data types, links and constraints between tables, and most of them use------DCL (data Control Language) When establishing tables:-    is the database control function. Is the statement used to set or change permissions for a database user or role, including (Grant,deny,revoke, etc.)-    statements. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to    perform DCL

----the difference between DML, DDL, DCL in SQL.----DML (Data Manipulation language):--    They are select, UPDATE, INSERT, DELETE, just like its name, These 4 commands are used to manipulate the data in the database-the    language------DDL (language):-    more DDL than DML, the main commands are create, ALTER, drop, etc. DDL is primarily used in the initialization of defining or altering tables (table)-    The structure, data types, links and constraints between tables, and most of them use------DCL (data Control Language) When establishing tables:-    is the database control function. Is the statement used to set or change permissions for a database user or role, including (Grant,deny,revoke, etc.)-    statements. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to    perform DCL

2.2 SQL statements about database operations

---1. Create a database (create a corresponding folder on disk) create a database    [if not exists] db_name [character set XXX]     --2. View Database    show Datab ases; View all databases    show CREATE database db_name; see how the databases are created--3. Modify the database    alter DB db_name [character set XXX]--4. Delete the database    drop databases [if exists] db_name;    ---5. Using Database to    switch database use db_name;--note: After entering a database, there is no way to return to the previous state, but you can use the switch to    view the currently used database select databases ();

2.3 Data Sheet additions and deletions change to create a table

--Syntax CREATE TABLE tab_name (            field1 type[integrity constraint],            field2 type,            ...            FIELDN type        ) [character set XXX];

--Create an employee table Employee         CREATE TABLE employee (            ID int primary key auto_increment,            name varchar),            gender Bit default 1,   --Gender char (1)  default 1   -----    or TINYINT (1)             birthday date,                   job varchar (),            salary double (4,2) unsigned,            resume text    --note that this is the last field without a comma          );/* constraint:       primary KEY ( Non-null and unique)  : A field that uniquely distinguishes the current record is called the primary key!       unique not                null       auto_increment: Used for primary key field, primary key field must be number type  */

View table Information

Desc tab_name View Table structure    show columns from tab_name  view table structure    show tables View all tables in the current database    Show create TABLE tab _name    

Modify Table Structure

--(1) Add column (field)      ALTER TABLE tab_name add [column] column name type [integrity constraint][first|after field name];      ALTER TABLE user add addr varchar (a) NOT null unique first/after username;      #添加多个字段      ALTER TABLE users2             add addr varchar (),            add age  int First,            add birth varchar (all) after name ;   --(2) Modify a column type      ALTER TABLE tab_name Modify column name type [integrity constraint][first|after field name];      ALTER TABLE USERS2 modify age tinyint default;      ALTER TABLE USERS2 modify age int after  ID;      --(3) Modify column name      ALTER TABLE tab_name change [column] columns name new column name type [integrity constraint][first|after field name];      ALTER TABLE USERS2 change age-age int default;   --(4) Delete a list of      ALTER TABLE tab_name drop [column] names;      --Think: Delete multiple columns? Delete one and fill in one?      ALTER TABLE users2             add salary float (6,2) unsigned not NULL after name,            drop addr;       --(5) Modify table name rename table name to the      new name;   --(6) The character set used to repair the table          ALTER TABLE student character set UTF8;

Delete a table

drop table tab_name;

MySQL (1)