Example code of Mysql library table operation

SQL
Concept: Structured Query Language (SQL = Structured Query Language),
is also a programming language (database query and programming language), which can be used for data access, query, and update. Managing relational database systems
ps: SQL between different database systems cannot be completely interchangeable;

Classification
Can be divided into different languages ​​for different operating objects
1: Data operation ( Data Management) Language DML(Data Management Language)
1): Query data DQL
2): Add, delete, modify DML
2: Data Definition Language DDL(Data Definition Language) --For example, table Definition
3: Data Control Language DCL(Data Control Language)

********************************** *************************************************** *********************************

The relationship between database, table and data
The table is the carrier of data, and the database is the container of the table
******************************** *************************************************** ******************************


Database Operation

mysql> show databases;    --查看所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Create database
Syntax: create database [if not exists] db_name [data option]
Example:

create database student_system;

Example:

create database if not exists student_system;

--will first determine whether student_system already exists , it will not be created if it exists, and exceptions can be avoided

Example:

C:\WINDOWS\system32>mysqladmin -uroot -p create bbbb

--can be created through mysqladmin
****************** *************************************************** ******

Naming rules for databases
1: Know the meaning by name, it is recommended to use underscores
2: You can use any characters, such as numbers, symbols, Chinese, etc.
create database Pangpang;
3: If the name is very special, such as when it is named with pure numbers or keywords, it must be wrapped with a qualifier (the qualifier refers to the backtick ``);
create database `123456`;
4: Is it case-sensitive (this depends on the current operating system);
5: The name of the database can be created using backticks
********** *************************************************** ************

ps: When the database is created, a directory will be formed. The directory name is the database name. If the database name is a special character, the file name will be represented in an encoded form. Formula
There will be a db.opt file under the directory to save the database selection information;
****************************** ************************************************

Database related operations

1: show databases;     --查看所有的数据库
2: drop [if exists] database bbbb; --删除指定的数据库
3: show create database student_system;  --查看数据库(student_system)创建信息
    +----------------+------------------------------------------------------------------------+
    | Database       | Create Database                           |
    +----------------+------------------------------------------------------------------------+
    | student_system | CREATE DATABASE `student_system` /*!40100 DEFAULT CHARACTER SET gbk */ |
    +----------------+------------------------------------------------------------------------+
4: alter database db_name [指定的操作]   --修改数据库信息
    例: alter database student_system character set 'utf8';

************************************ *************************************************** ******************************
Table related operations
Table creation
Creation syntax: create table [if not exists] tbl_name (column structure) [option]
The table is the carrier of data, and the database is the container of the table, so before creating the table, you need to determine the database to which it belongs.
The table must be Attributes to a certain database

1: When creating a table, you can specify the database to which it belongs before the table name

create table `student_system`.student(
    name varchar(20),
    sex varchar(3),
    age int
);

2: You can first use use db_name to specify the current default database , and then create the table

use student_system
   create table teacher(
       name varchar(20),
       sex varchar(3),
       age int
   );

3: show tables; --View all tables, and also specify the current default database first

4: show create table teacher; -- View the creation table (teacher) creation information
show create table teacher\G
##5: describe teacher; --View the structure of the table (teacher)

+-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | name  | varchar(20) | YES  |     | NULL    |       |
    | sex   | varchar(3)  | YES  |     | NULL    |       |
    | age   | int(11)     | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    desc teacher;  --可以简写describe teacher;

6: drop table [if exists] tbl_name; --Delete table (wrapped table structure)

Example: drop table student;
Example: drop table if exists student;
********** *************************************************** *************************************************

Modify table
Modify table name

语法:rename table old_table_name to new_table_name
例: rename table student to student_1;
例: rename table student_2 to student_1, teacher to teacher_1;   --可以同时修改多个表名
例: rename table student_1 to `test`.student_2; --可以跨数据库重命名, 可以通过这个表重命名的方式来对数据库重命名

Modify column definition

Add new column(add)

alter table student_1 add id int;

Delete column(drop )

alter table student_1 drop id;

Modify column definition (modify)

alter table student_1 modify name varchar(10);

Rename column (change)

alter table student_1 change age student_age int(3);

*******************************************************************************************************


表数据操作(增删改查)
插入数据(创建数据create)
语法: insert into 表名(字段列表) values(值列表)
例: insert into teacher_1(name,age) values('胖胖', 18);
例: insert into teacher_1 values('小胖','男', 16);   --如果没有指定字段列表,那么要插入的值要和列中的字段顺序一样
    insert into teacher_1(name,age) values('小未', 19);
    insert into teacher_1 values('阿哈','女',18);


查询数据(读取数据read)
语法: select 字段列表 from 表名 where 查询条件
例: select name,age from teacher_1;
例: select * from teacher_1;   --如果字段列表使用*号来代替, 那么表示查询所有的字段
例: select * from teacher_1 where name = '胖胖';    --可能使用查询条件进行数据过滤，拿到想要的数据;
例: select * from teacher_1 where 1;       --where 1表示条件永远成立
    select * from teacher_1 where 0;


修改数据(update)
语法: update 表名 set 字段=新值,... where 条件
例: update teacher_1 set sex='女' where name = '小胖';
    update teacher_1 set sex = '保密', age = 15, name = '阿呵' where name = '阿哈';


删除数据(delete)
语法: delete from 表名 where 条件
例: delete from teacher_1 where age = '18';
例: delete from teacher_1;   --如果没有条件进行删除，则会删除整个表的删除(不同于drop table teacher_1)
ps: 在删除数据时,一定要给一个具有严格逻辑判断条件，不然很容易造成数据误删除,最后造成数据的损失



curd(create update read delete)--增删改查

The above is the detailed content of Example code of Mysql library table operation. For more information, please follow other related articles on the PHP Chinese website!