Database Basics MySQL

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.

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).

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.

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< c2/> User name-p password--prompt command prompt  --delimiter specify delimiter--    mysql-h 127.0.0.1-p 3306-uroot-p123--    quit------Exit---- \q; Exit command----\s;   ------My.ini file: [MySQL] default-character-set=gbk [mysqld] character-set-server=gbk   view default character encoding----prompt command prompt (\d  : Current date \d: current  user)----\ t (start log) \ t (end log)----show warnings; when warnings is present, write to view the warning message----help ()? \h  Direct or? Add the command to view----\g;----select now ();  Show current Time-select version ();--Select User,----\c cancel command----delimiter specify delimiter

SQL and its specifications

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.

<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 library name            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

database Operations (DDL)

--1. Create a database (create a corresponding folder on disk) create a database    [if not exists] (optional write) db_name [character set GBK] (created in GBK encoding)     --2. View    the database show databases; View all databases show create DATABASE    db_name; see how the database was created;--3. Modify Database    Alter DB _name [Character set XXX] Modifying the way the database was created--4. Deleting a 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 ();

MySQL data type

MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types.

Numeric type

The following table shows the storage and scope of each integer type that is required.

Float (4,2) 99.99 Front represents a few, followed by a few digits after the decimal point

Date and Time type

The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.

Each time type has a valid value range and a value of "0", and a value of "0" is used when specifying an illegal MySQL value that cannot be represented.

String type

The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.

Char Order length Information Example: char (3) must be a three-byte
varchar (20) up to 20 characters

Char and varchar types are similar, but they are saved and retrieved in different ways. They are also different in terms of their maximum length and whether trailing spaces are retained. No case conversions are made during the storage or retrieval process.

Binary and varbinary classes are similar to char and varchar, but they contain binary strings rather than binary strings. That is, they contain a byte string instead of a character string.

A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

Data table Operation Base operation

1. Create a table (similar to an Excel table) create TABLE        tab_name (            field1 type[integrity constraint],            field2 type,            ...            FIELDN type        ) [character set XXX];        For example:         --Create an employee table Employee         CREATE TABLE employee (            ID int primary key auto_increment,            name varchar (20), C11/>gender bit default 1,   --Gender char (1)  default 1   -----    or TINYINT (1)             birthday date,            entry_date date,            job varchar,            salary double (4,2) unsigned,            resume text    --note, Here as the last field without commas          );    /* Constraint:       primary key primary key (not null and unique)  : The field that uniquely distinguishes the current record is called the primary key!       unique  unique       not NULL       auto_increment primary key field must be a numeric type. Self-increment       foreign KEY constraint foreign key  */    

--2. View table information

     desc Tab_nameView table structure Show columns from tab_name view table structure
Show TablesView all tables in the current database Show create TABLE tab_name view current database table Build Table statement--3. Modify table Structure--(1) Add column (field) ALTER TABLE tab_name Add [Colum       N] Column name type [integrity constraint][first|after field name] after which field;      Example: 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 Varc   Har () after name; --(2) Modify a column type ALTER TABLE Tab_nameModifyColumn name type [integrity constraint][first|after field name];
 　　   Example: Alert table employee modify age smallint NOT NULL default all after ID;ALTER TABLE USERS2 modify age tinyint default 20;      ALTER TABLE USERS2 modify age int after ID; --(3) modify the Column name ALTER TABLE Tab_name Change[Column] Column name new column name type [integrity constraint][first|after field name];
 　　　　 Example: ALTER TABLE employee Change Department depart varchar (a) after salary;      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?       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;--4. drop table Tab_name,---5 add primary key, delete primary key alter TA     BLE tab_name Add primary key (field name,...)    ALTER TABLE users drop PRIMARY key;    Eg:mysql> CREATE TABLE test5 (num int auto_increment); ERROR 1075 (42000): incorrect table definition; There can is only one auto column and it must is defined as a key CREATE TABLE test (num int primary key auto_increment)    ;    --thinking, how do I delete a primary key?   ALTER TABLE test modify ID int; --Auto_increment is gone, but the primary key is still there, so add the following sentence ALTER TABLE test drop primary key;--simply cannot delete the primary key directly--Unique index ALTER TABLE tab     _name add unique [index|key] [index name] (field name,...) ALTER TABLE users add unique (NAMe)--the index value defaults to the field name show create table users; ALTER TABLE users add unique key user_name (name);--index value user_name--Add Federated Index ALTER TABLE users add unique index name_    Age (Name,age); #show CREATE table users; --delete unique index ALTER TABLE tab_name drop {Index|key} index_name

1 CREATE TABLE article (2             int primary Key auto_increment, 3             Title varchar,4            publish_date INT,5            click_ Num INT,6             is_top TINYINT (1),7            content TEXT 8           );

Create an article table

Record of record of operation table, delete, change

--1. Add a record insert insert [into] tab_name (field1,filed2,.......) VALUES (value1,value2,.......);  */CREATE TABLE employee_new (ID int primary key auto_increment, name varchar) not　　　  Null unique, Birthday varchar (salary), float (7,2)); The first way to add a record: one by one corresponding insert into employee_new (id,name,birthday,salary) VALUES (1, ' Yuan ', ' 1990-0　　　　9-09 ', 9000);       The second way to add records: you need to insert into employee_new values according to table structure one by one (2, ' Alex ', ' 1989-08-08 ', 3000);      Insert into employee_new (name,salary) VALUES (' Xialv ', 1000); --Insert multiple data inserts into Employee_new values (4, ' alvin1 ', ' 1993-04-20 ', +), (5, ' alvin2 ', ' 1995-05-12 ', 5000)      ; --Set Insert: Insert [into] tab_name set field name = Value Key value pair method add insert into Employee_new set id=12,name= "alvin3";--2. Modify table record up Date Tab_name Set Field1=value1,field2=value2,...... The [WHERE statement]/* Update syntax can be updated with the new value of the originalThe columns in the table row.            The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated.    */update employee_new set birthday= "1989-10-24" WHERE id=1;    ---will increase the salary of yuan by 1000 yuan on the original basis.        Update employee_new set salary=salary+4000 where name= ' yuan ';--3. Delete table record Delete from Tab_name [where ...] /* If you do not follow the WHERE statement delete data from the entire table delete can only be used to delete a row of records DELETE statement can only remove the contents of the table, cannot delete the table itself, want to delete the table, with drop Trunca TE table can also delete all the data in the table, the word sentence first destroys the table, and then creates a new table. Data that is deleted in this manner cannot be recovered in a transaction.                */-delete the record with the name ' Alex ' in the table.                Delete from employee_new where name= ' Alex ';                --Delete all records in the table.                Delete from employee_new;--note auto_increment is not reset: ALTER TABLE employee auto_increment=1;                --use Truncate to delete records in the table. TRUNCATE TABLE emp_new;

　Thinking:

<1> storage time is it possible to use varchar? What is the difference between it and the date data type? OK

<2> table Data Three, the ID is three-way, and suddenly inserted a id=7, then the next time as the primary key word growth ID will grow from a few? (Starting from 7)

Check the table record

CREATE TABLE examresult (id INT PRIMARY KEY auto_increment, name VARCHAR (), JS double, Django double, Data Base DOUBLE); add a record; INSERT into Examresult VALUES (1, "Yuan", 98,98,98), (2, "Xialv", 35,98,67)                               , (3, "Alex", 59,59,62), (4, "Wusir", 88,89,82), (5, "Alvin", 88,98,67), (6, "Yuan", 86,100,55);--(1) SELECT [DISTINCT] *|field1,fie Ld2, ... from Tab_name--where from specifies which table to filter from, * means to find all columns, or to specify a column--the table explicitly specifies the column to find, and distinct is used to reject duplicate rows.--Query the information of all students in the table.                    SELECT * from Examresult;                    --Check the names of all the students in the table and the corresponding English scores.                    Select Name,js from Examresult;                    --Filter the repeating data in the table. Select DISTINCT JS, name from Examresult;--(2) Select can also use an expression, and can use: field asAlias or: Field Alias--Add 10 extra-long points to all student scores.                Select name,js+10,django+10,database+10 from Examresult; --count Each student's total score. Select Name,js+django+database from Examresult; --use aliases to represent student totals。                Select name as name, Js+django+database as total from Examresult;                Select Name,js+django+database Total from Examresult; Select name JS from Examresult;            What would happen?----> remember to add a comma--(3) Use the WHERE clause to filter the query.            --Query the student whose name is XXX select * from Examresult where name= ' yuan ';            --Query for students with English score greater than 90 select Id,name,js from Examresult where js>90; --Query all students with a total score greater than 200 select Name,js+django+database as score from Examresult where Js+django            +database>200;                        --The WHERE clause can be used:--comparison operators: > < >= <= <>! =                        Between and 100 values between 10 and 20 in (80,90,100) value is 10 or 20 or like ' yuan% '                        /* pattern can be% or _, if it is% it means any number of characters, such as Tang Monk, Tang Guoqiang                     If _ is a word 唐 _, only the Tang priest in accordance with.   */--logical operators can use logical operators and or not directly in multiple conditions--practice--query                JS score in 70-100 between the classmates.                Select Name, JS from Examresult where JS between and 100;                --Check the Django score for 75,76,77 's classmates.                Select Name, Django from Examresult where Django in (75,98,77);                --Check the student scores of all surnamed Wang.                SELECT * from Examresult where name is like ' Wang% ';                --Query JS Sub->90,django sub->90 students.                Select Id,name from Examresult where js>90 and Django >90; --Find the name of the student who lacks mathematics select name from Examresult where the Database is null;--(4)ORDER BY specifies the sorted column, from small to large, the sorted column can be a column name in a table or an alias specified after the SELECT statement. --Select *|field1,field2 ... from tab_name order by field [asc| DESC]--ASC Ascending,desc Descending, where ASC is the default value the ORDER by clause should be at the end of the SELECT statement.              --Practice:--sort the output after the JS score.              SELECT * from Examresult order by JS;  --Sort the total score from high to low in order to output select name, (Ifnull (js,0) +ifnull (django,0) +ifnull (database,0))              Examresult ORDER BY total desc; --The ranking of student grades in Li's name output select Name, (Ifnull (js,0) +ifnull (django,0) +ifnull (dababase,0)) Total score from Ex  Amresult where name like ' a% ' ORDER by Total desc;--(5) Group by group query:--                      Note that each group will only display the first record when grouped by grouping criteria-the group by sentence, which can then be followed by multiple column names, or with the HAVING clause to filter the results of GROUP by.                      --Filter By Location field select * FROM Examresult Group by 2;                      ---Practice: After grouping the scores by name, the sum of the JS points of each class name is displayed. Select Name, sum (JS) from Examresult group by name;                  --Exercise: After grouping the scores by name, the sum of the Django scores for each type of name >150-class name and Django Total score       --insert into Examresult VALUES ("Alex", 90,90,90); Select Name,sum (Django) from Examresult Group by name have sum (Django) &                   gt;150; /* Have and where both can be further filtered query results, the difference is: <1>where statement can only be used in the filter before grouping, having can be used after grouping                   Filter <2> use where statements can be replaced with the <3>having can be used in the aggregation function, where is not. */--Exercise: After grouping the scores by name, displays the Django--the sum of the scores of each class name except Yuan-->150 class name and Djang O Total Score Select Name,sum (Django) from Examresult WHERE                                                  name!= "Yuan" group by name            Having sum (Django) >130; --Group_concat () function SELECT id,group_concat (name), Group_concat (JS) from Examresult GROUP by id;--(6) Aggregate letterNumber: Do not control the aggregation function to do, first the content of the requirements to find out and then wrap the aggregation function. --(used in conjunction with a group query) all records in the--<1> table-count (column name): Number of statistics rows--How many students are there in a class?                    First find out all the students, and then use Count package on select COUNT (*) from Examresult;                     --The number of students who count JS scores more than 70?                    Select COUNT (JS) from Examresult where js>70;                     --What is the number of people with a total score greater than 280? Select count (name) from Examresult where (Ifnull (js,0) +ifnull (django,0) +ifnull (database,0)) >2                    80;     --Note: Count (*) counts all rows;            Count (field) does not count null values. --SUM (column name): Statistics The contents of the rows satisfying the conditions and--Statistics a class JS total?                        First find out all the JS scores, and then use SUM package on the Select JS as JS total from Examresult;                    Select sum (JS) as JS total from Examresult;                               --statistics of a class of each section of the total of select sum (JS) as JS total, sum (Django) as Django Total, SUM (Database) as DatabaSe total from Examresult;                                                    --statistics on the total score of a class of subjects select SUM (ifnull (js,0) +ifnull (django,0) +ifnull (database,0))                    As total from Examresult;                    --Statistics A class JS score average select sum (JS)/count (*) from Examresult;            -Note: Sum works only on numeric values, otherwise it will be an error. AVG (column name):---------------average class JS?                                First find out all the JS points, and then use AVG package.                            Select AVG (ifnull (js,0)) from Examresult;                                                         --to find a class total score of average select AVG ((Ifnull (js,0) +ifnull (django,0) +ifnull (database,0)))                From Examresult; --Max, Min--to find class highest and lowest (numeric range is particularly useful in statistics) Select Max (Ifnull (js,0) +ifnu                            LL (django,0) +ifnull (database,0))) highest score from Examresult; Select Min (Ifnull (js,0) +iFnull (django,0) +ifnull (database,0))) min. from Examresult;                -Note: null and all number calculations are NULL, so you need to convert NULL to 0 with Ifnull! -------ifnull (js,0)--the group record after using--<2> statistics grouping with Rollup--(7) Focus: Select from where Gro Up by have order by--the order in which MySQL executes the SQL statement: from where select group by has order by- -Analysis: Select JS as JS score from Examresult where JS score >70; ----unsuccessful Select JS as JS score from Examresult have JS score >90; ---success Select JS as JS score from Examresult GROUP by JS results have JS score >80;  ----Successful Select JS as JS score from Examresult order by JS score;----Success SELECT * FROM Examresult as score Where scores. js>85;                ----Success--(8) Limit SELECT * from Examresult limit 1; SELECT * from Examresult limit 1, 5;

　　

　

　

Database Basics MySQL