MySQL first day of the detailed

Mysql01

MYSQL01 Database Introduction

2. Introduction to Databases

   Previously used by the flow of data stored in the TXT document, for modification/insertion, and so on, first code cumbersome, for the query operation is very inefficient, in addition, if the data is large, it is not possible to persist in the file for a long time. To solve these problems, we need to use the database

   • DB: File Warehouse

   • DBMS: Database management software

   • Classification of databases

     • relational database

       • Save the data with "table" and the relevant data in a table

     • Non-relational database

       • Key-Value Database
       • Object Database

4. Mainstream relational database

   • Oracle Oracle Larry Eliasson charges nuclear closed source products

   • DB2 IBM, applied to large systems, Unix/linux, is Oracle's main competitor

   • SQL Server Microsoft can only run. NET (C #) under Windows

   • Mysql free Open Source selling service

     2008 acquired by Sun, except version 5.1

     2010 Sun more than 200 billion, was ORACLE74 billion acquisition

     MySQL Cow x programmer, request MySQL not closed source, Oracle Oral promise, no sign contract

     In addition to version 5.5, MySQL uses Oracle's core technology to improve performance 15%-30%

     MySQL threatens Oracle market, resulting in 5.5 version of closed source charges

     MySQL Cow x programmer, MARIADB

     MySQL free open source community, support MARIADB

     mysql5.5 mariadb5.5 mysql5.6 mariadb10.0

5. SQL Basic Operations

   The SQL language is the language that operates the database every kind of data has its own unique dialect

   Linux Open Terminal input mysql-u root-p carriage return Password: (enter the password with the password, no password will be directly return)

   • manipulating databases

   • Operations on a database

     1. Show all the data

        • show databases;

     2. Select the database you want to manipulate

        • Use library name;

     3. Create a new database

        • Create database name;

     4. View the statement that created the database

        • Show create database name;

     5. Set the default decoded create LIBRARY statement

        • Create DATABASE demo default character set UTF8;

     6. Deleting a database

        • drop database name; Drop database demo;

     7. requirements, create the library demo1, require the default encoding utf8, and after the creation is complete, select this library demo1.

     8. Show All Tables

        • Show tables;

     9. Query table structure

        • DESC table name;

   • Create a table

     • CREATE TABLE table name (column name data type (length) constraint, column name data type (length) constraint, ... Column name data type (length) constraint);

       create table stu(    id int not null,    name varchar(10),    sex  varchar(10));

   • View table Structure

     DESC table name;

     CREATE TABLE stu ( id int (one) not NULL, name varchar (TEN) default NULL, sex varchar (TEN) default null) Engine=innod B DEFAULT Charset=utf8;

There are two types of MySQL engines Engine=

- InnoDB    - 支持数据库高级功能        - 事务        - 外键    - myisam    - 数据存储基本功能    - 效率非常高

--Classroom practice

CREATE table User (name varchar (+), age int) Engine=innodb charset=gbk;-* * Modify a table that has been created * * 1.    Modify table name-Rename table name to new table name-Rename table user to Tb_user;        2. Modify table properties (engine, character encoding)-ALTER TABLE name Engine=myisam Charset=utf8;    -ALTER TABLE Tb_user Engine=myisam Charset=utf8;        3. Add field-ALTER TABLE name add new field New Field's data type constraint for new field first;        -ALTER TABLE Tb_user add ID int first;    -ALTER TABLE Tb_user Add (gender char (5), Tel char (11));        4. Modify field name-ALTER TABLE name change old field name new field name new field data type;    -ALTER TABLE tb_user change gender sex varchar (10); 5. Change data Type-delete a field and add a new one-use the command keyword to modify directly-ALTER TABLE name modify field name fields new Type-ALTER TABLE Tb_us    ER Modify Tel varchar (11); 6. Change the order of columns-ALTER TABLE table name modify destination column name destination column data type after a column column name-ALTER TABLE TB_USER MODIFY TEL varchar (one) afte        R age; -Little practice, put sex in front of the age field-alterTable Tb_user Modify sex varchar (ten) after name;    7. Delete a column-ALTER TABLE name DROP column name-ALTER TABLE Tb_user DROP sex;-* * Delete Table * *-the name of the drop table table;  -DROP Table tb_user;-* * Classroom Exercise 1**--Create an Employee table EMP CREATE TABLE EMP (//Employee table Empno Int (4),//work number ename        varchar (10),//Name Job varchar (10),//Jobs Mgr Int (4),//Supervisor's work number HireDate date,//Entry time      Sal Double (7,2),//Payroll Comm Double (7,2),//Bonus Deptno int (4)//department number);     CREATE TABLE Dept (//Department table Deptno Int (4),//Department number dname varchar (14),//Department name Loc varchar (13)//Department office location );

dml--Statement INSERT statement that operates on data in a table

- insert into 表名 (列名1,列名2,...) values (值1,值2,...);insert into emp(empno,ename,job,sal) values (1001,‘lily‘,‘programmer‘,5500);insert into emp(empno,ename,job,sal) values (1001,"lily","programmer",5500);insert into emp(empno,ename,hiredate) values (1002,‘simth‘,‘18-01-24‘);

UPDATE statement

- update 表名 set 字段名=值 where 列名=值;- update emp set ename=‘lucy‘ where empno=1001;- 1.把叫simth的员工的入职时间改为17年10月12日    update emp set hiredate="17-10-12" where ename="simth";- 2.插入新员工lilei,工号1003,工资3000,奖金5000,上级领导编号1002    insert into emp(empno,ename,sal,comm,mgr) values(1003,‘lilei‘,3000,5000,1002);- 3.插入新员工zhangsanfeng,工号1004,上级领导编号1001,工资8000,奖金1000    - 修改字段类型    alter table emp modify ename varchar(20);    insert into emp(empno,ename,mgr,sal,comm) values(1004,‘zhangsanfeng‘,1001,8000,1000);- 4.插入新员工liuchuanfeng,工号1005,上级领导1004,入职时间18年01月22日,工资800,奖金2000- 5.修改zhangsanfeng的工资,修改为8500- 6.修改lilei的奖金,修改为2000- 7.修改liuchuanfeng的上级领导为1001,工资为3000    update emp set mgr=1001,sal=3000 where ename=‘liuchuanfeng‘

Delete statement

- delete from 表名 where 条件- delete from emp where job is null;

SELECT * FROM table name ddl--is a statement truncate statement that operates on the table itself

- truncate删除表内容的原理    先把整张表删除,然后重新创建一个表结构一模一样的空表truncate table 表名truncate table emp;

DDL does not support transactions, executes immediately after run, cannot roll back DML support transaction jobs

1. Re-knock all the code in the class

Exercise 1:

1. Create an employee table EMP 2. Create a departmental table dept;

Exercise 2:

1. Insert 4 data to Dept table, ' ACCOUNTING ', ' New York '-Finance Department New York, ' Research ', ' DALLAS '-study department Dallas, ' Sales ', ' CHICAGO '--Sales department Chicago, ' OPE Rations ', ' BOSTON '--Operation Department Boston

Exercise 3:

1. Create a MyDB database using UTF8 encoding

2. Create a T_item Product table

3. Insert product data in T_item table 7, ' Apple ', iphone X 9999 now ()

4. The modified ID is 7 of the product and the modified stock is 20
5. Delete Item 7

INSERT into EMP values (1001, ' Hanmeimei ', ' Sal ', 1001,now (), 3000,5000);

MySQL first day of the detailed