Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the basic knowledge points in MYSQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Overview of Database

Database: database, software for permanent data storage, mass storage, efficient access.

Types of database software:

(1) mesh database (2) Tree / hierarchical database (3) Relational database (Relational DB) (4) non-relational database (NoSQL)

RDBMS (RDB Management System) deployment structure:

(1) Server side: responsible for storing / managing data, all of which are stored in binary format and cannot be viewed directly-such as the database server in the head office of ICBC

(2) client: responsible for connecting to the server and sending add, delete, change and search instructions to the server, such as ATM.

Logical structure of RDBMS server-side data:

Server= > Database= > Table= > Row= > Column

Mysqld 、 httpd 、 ftpd 、 sshd 、 smbd....

Demon: sprite, guardian, daemon, sprite, server program

The use of MySQL system

(1) Server side: download and install MySQL server software

Mysql.commariadb.orgxampp.org = Apache+MySQL+PHP

(2) Server side: start MySQL server software

C:/xampp/mysql/bin/mysqld.exe guarantees that port 3306 is open

= =

(3) client: download and install a MySQL client software

C:/xampp/mysql/bin/mysql.exe acts as the ATM terminal client of a bank.

(4) client: provide a user name and password and log in to the database server

Mysql.exe-uroot-p cannot add a semicolon! Mysql-uroot cannot add semicolons! Management commands commonly used in MySQL servers

Tip: all administrative commands must end with a semicolon! Except for use and quit!

(1) quit; exits the connection to the server

(2) show databases; displays all databases on the current server

(3) use library name; enter / start using the specified database

(4) show tables; shows which tables are in the current database

(5) desc table name; describe the column of the specified table (description header)

Commonly used SQL commands

SQL:Structured Query Language, a structured query language, is a programming language. It was first put forward by IBM, and later adopted by ISO as the international standard of relational database industry, and several versions have been launched, such as SQL87, SQL92, SQL99;, which are supported by major database manufacturers.

How the SQL statement is executed:

(1) Interactive mode: enter one line to execute one line,., suitable for temporary viewing of data. Enter mysql-uroot

(2) script mode: multiple commands to be executed are written in a text file and submitted to the server for execution at once, which is suitable for multiple statements executed repeatedly in batches. Mysql-uroot

< d:/xx.sql 回车 SQL语言的语法： (1)所有的SQL语句必须以;分号结尾。 (2)SQL语句不区分大小写。习惯上，系统预定义的关键字都大写，非关键字都小写。 (3)SQL语句中可以使用 单行注释(#...) 和 多行注释(/.../) DROP DATABASE IF EXISTS 库名; CREATE DATABASE 库名 CHARSET=UTF8; USE 库名; CREATE TABLE 表名(列名 类型, 列名 类型, ....); INSERT INTO 表名 VALUES(值, 值, ....); SELECT * FROM 表名; 数据库中的乱码问题 产生的原因：计算机把每个字符都分配唯一个数字。若存字符时与取字符时所用的编码方案不同，就会产生乱码。 a 97 b 98.... 字符编码方案/字符集：把每个需要呈现的字符都分配一个唯一的数字编码。世界上有几套常用的字符集： (1)ASCII字符集：只对所有的英文字符进行了编码(128个) (2)GB2312/GBK：对常用的英文字符、中文简体字符都进行了编码(40000多个) (3)BIG5：对常用的英文字符、中文繁体字符都进行了编码 (4)Unicode字符集：对常用的英文字符、简体汉字、繁体汉字、日文、韩文...主流语言的常用符号都进行了编码，具体存储时又分为UTF-8/UTF-16/UTF-32三种存储方案 解决乱码问题的方法--保证"三处统一"： (1).sql文件的存储编码 (2)mysql.exe连接mysqld.exe所用的编码 (3)mysqld.exe中存储数据所用的编码 MySQL服务器端数据的逻辑结构： SERVER=>

DATABASE= > TABLE= > ROW= > COLUMN

How to connect to a database server:

Interaction mode: mysql.exe-uroot-p

Script mode: mysql.exe-uroot-p

< d:/2.sql 常用的SQL语句： (1)增：INSERT INTO 表 VALUES(值, 值, ...); (2)删：DELETE FROM 表; (3)改：UPDATE 表 SET 列=值, ...,列=值 ; (4)查：SELECT * FROM 表; DELETE FROM laptop; #删除所有的记录行 DELETE FROM laptop WHERE lid=10; #删除满足条件的记录行 UPDATE laptop SET price='3000',pic='img/50.jpg',isOnsale='否'; #更新所有的记录行 UPDATE laptop SET price='3000',pic='img/50.jpg',isOnsale='否' WHERE lid=31; #更新满足条件的行 MySQL中的列类型 (1)数值类型 -- 可用引号括起来也可以不用 整数数值类型： student( age TINYINT ) TINYINT：微整数，占1字节，-128~127 SMALLINT：小整数，占2字节，-32768~32767 INT：整数，占4个字节, -2147483648~2147483647 BIGINT：大整数，占8个字节，.....小数数值类型： product( price DECIMAL(7, 4) ) FLOAT(M,D)：单精度浮点型，占4字节，3.4E38，计算时可能产生四舍五入 DOUBLE(M,D)：双精度浮点型，占8字节 1.8E30，计算时可能产生四舍五入 DECIMAL(M,D)：定点小数，不会产生精度舍入布尔数值类型： product( isOnsale BOOL) BOOL，布尔/真假类型只能取值为TRUE/FALSE。注意：MySQL数据库中没有真正意义上的布尔类型，TRUE等同于1，FALSE等同于0 (2)日期时间类型 -- 必须用引号括起来 DATE：日期类型，形如'2017-5-10'TIME：时间类型，形如'22:08:5' DATETIME：日期/时间类型，形如'2017-10-25 22:8:5' (3)字符串类型 -- 必须用引号括起来 emp(resume ...) CHAR(M)：定长字符串，比VARCHAR操作速度更快，M不能超过255VARCHAR(M)：变长字符串，比CHAR更能节约空间，M不能超过65535TEXT(M)：大型变长字符串，M不能超过2Gename CHAR(11) ename VARCHAR(11) a a000 a0 ab ab00 ab0 abc abc0 abc0 abcd abcd abcd abcde abcd abcd 一二三四 一二三四 一二三四 一二三四五 一二三四 一二三四 true 真 ture x false 假 flase x MySQL中的列约束 Constraint：约束，数据库中某列上的数据往往必须符合某种规范，如编号不能重复、年龄必须在一定范围、密码有长度限制、员工所在部门必须真的存在......类似的限制/规范就称为"列约束" (1)主键约束 -- PRIMARY KEY 声明为主键的列上，不能出现重复值，也不能出现NULL值，所有的记录会自动按照主键列上值由小到大排序 -- 因此一个表中至多只能有一个主键列。 (2)非空约束 -- NOT NULL 声明为非空的列，不能出现NULL，但可以出现重复值。 (3)唯一约束 -- UNIQUE 声明为唯一约束的列，不能出现重复的值，但可以出现NULL，且允许多个NULL出现（两个NULL值是不等的） (4)检查约束 -- CHECK 检查约束可以检查新插入的数据是否满足指定的条件，如：student( age INT CHECK(age>

= 18 AND age

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.