Today’s content

  1. Basic concepts of databases

  2. MySQL Database software

    1. The installation
    2. uninstall
    3. configuration

  3. SQL

Basic concepts of databases

1. DataBase 2. What database? * A repository for storing and managing data. 3. Characteristics of database: 1. Persistent storage of data. In fact, the database is a file system 2. Convenient storage and management data 3. The use of a unified way to operate the database -- SQL 4. Common database software * see MySQL Basics. PDFCopy the code

MySQL Database software

* See MySQL Basics. PDF. 2. To find my mysql installation directory. The ini file * copy datadir = "C: / ProgramData/mysql/mysql Server 5.5 / Data/" 2. Uninstall MySQL. 3. Delete the MySQL folder in C:/ProgramData. 3. Configure * MySQL service startup. 1. CMD --> services. MSC open the service window 3. CMD * net start mysql: Net stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql: stop mysql --user=root --password= connection target password * MySQL exit 1. Exit 2. Quit * MySQL directory structure 1. Basedir ="D:/develop/MySQL/" Datadir = "C: / ProgramData/MySQL/MySQL Server 5.5 / Data/" * * database several concepts: folder * table: file * Data: DataCopy the code

SQL

1. What is SQL? Structured Query Language (Structured Query Language) defines the rules that operate on all relational databases. Each database operates in a different way, called a "dialect." SQL statements can be written in single or multiple lines, ending with a semicolon (;). 2. Use Spaces and indents to improve the readability of the statement. 3. The SQL statements of the MySQL database are case-insensitive. You are advised to use uppercase keywords. 4. 3 types of comment * single-line comment: -- comment content or # comment content (mysql specific) * multi-line comment: Data Definition Language (DDL) is used to define database objects: databases, tables, columns, etc. Key words: Create, DROP,alter, etc. 2) Data Manipulation Language (DML) Is used to add, delete, or alter the Data of tables in a database. Keywords: INSERT, delete, update, etc. 3) Data Query Language (DQL) The Data Query Language is used to Query the records (Data) of tables in the database. 4) Data Control Language (DCL) is used to define access permissions and security levels of databases and create users. Key words: GRANT, REVOKE, etcCopy the code

DDL: Operates databases and tables

C(Create): Create * Create database: * Create database name; * Create database if not exists; * create database if not exists; * Create DATABASE name character set character set name; * Create database if not exists db4 character set GBK * create database if not exists db4 character set GBK; 2. R(Retrieve) : Retrieve all database names: * show databases; * show create database database name; 3. U(Update): alter * ALTER DATABASE character set * ALTER DATABASE character set CHARACTER set; 4. D(Delete): Delete * drop database * drop database name; * Drop database if exists Indicates the database name. * select database(); * Use the database name; C(Create): Create 1. Syntax: Create table name (column name 1 data type 1, column name 2 Data type 2,.... Column name n Data type n); * Database type: 1. Int: Integer type * age int, 2. Double: score double(5,2) 3. Date: date, yyyy-mm-dd 4. Timestamp: timestamp: timestamp contains yyyy-MM- DD HH: MM :ss 5. Timestamp: timestamp contains yyyy-MM- DD HH: MM :ss * If no value or null is assigned to this field, the current system time is used by default. 6. Varchar: String * name varchar(20): maximum 20 characters * zhangsan 8 characters * zhangsan 32 characters * create table create table student(id int, name varchar(32), Age int, score double(4,1), birthday date, insert_time timestamp); * create table name like the name of the table to be copied; 2.r (Retrieve) : Query * Query all table names in a database * show tables; * Query table structure * desc table name; 3. U(Update): alter table name alter table name rename to new table name; Alter table table name character set character set name; Alter table name add column name data type; Alter table table name change column name new column type new data type; Alter table table name modify column name New data type; Alter table table_name drop table_name; 4. D(Delete): drop the * drop table name; * drop table if exists table name;Copy the code
  • Client graphics tool: SQLYog

DML: Add, delete, or modify data in a table

* insert into table name (1, 2,... N) values(1, 2... The value of n); * Note: 1. Column names and values must correspond one by one. Insert into values(values 1, 2... The value of n); 3. Use quotation marks (even and odd) for all types except numeric ones. * delete from table name [where condition] * note: 1. If no condition is added, all records in the table are deleted. 1. Delete from table name; -- Not recommended. 2. TRUNCATE TABLE name. Drop the table and create the same table. Update table_name set table_name 1 = 1, table_name 2 = 2,... [] the where condition; * Note: 1. If no condition is added, all entries in the table will be modified.Copy the code

DQL: queries records in a table

* select * from table_name; 1. Select * from * where * group by * having * order by * order by * order by * order by * limit 2. Select * from 'select * from' where 'select * from' where 'select * from' where ' From the name of the table; * Note: * If all fields are queried, * can be used instead of the list of fields. In general, four operations can be used to calculate the value of a number of columns. * ifNULL (expression 1, expression 2) : the operation in which null is involved and the result is null * Expression 1: Which field is required to determine whether the field is null * The replacement value if the field is null. Alias: * as: as can also be omitted 3. Conditional query 1. Operators * >, <, <=, >=, =, <> * BETWEEN... AND * (set) IN * LIKE: fuzzy query * placeholder: * _ : single any character * % : multiple any character * * AND or && * or IS NULL or | | * not or! SELECT * FROM student WHERE age > 20; SELECT * FROM student WHERE age >= 20; SELECT * FROM student WHERE age = 20; SELECT * FROM student WHERE age = 20 SELECT * FROM student WHERE age = 20 = 20; SELECT * FROM student WHERE age <> 20; SELECT * FROM student WHERE age >= 20 && age <=30; SELECT * FROM student WHERE age >= 20 && age <=30; SELECT * FROM student WHERE age >= 20 AND age <=30; SELECT * FROM student WHERE age BETWEEN 20 AND 30; Select * from user where age 22, age 18, SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25 SELECT * FROM student WHERE age = 22 OR age = 25; SELECT * FROM student WHERE English = null; -- No. Null values cannot be used with = (! SELECT * FROM student WHERE English IS NULL; SELECT * FROM student WHERE English IS NOT null; Select * from horse; Like SELECT * FROM student WHERE NAME like '%'; SELECT * FROM student WHERE NAME LIKE "_ %"; SELECT * FROM student WHERE NAME = '___'; SELECT * FROM student WHERE NAME LIKE '% de %';Copy the code