Experience Summary: Basic query of some basic mysql applications
The SELECT statement is used to query all output operations in the database and SQL.
SELECT c_u_name, email FROM cooya_users;
Output the values of attributes c_u_name and email of all rows (or records) in the cooya_users table.
If you need all attributes, you can use the asterisk (*) shortcut.
SELECT * FROM cooya_users;
The SELECT statement can also output data that is not in the database and can be used as a simple operator.
SELECT curtime ();
SELECT log (100) * 4 * pi ();
WHERE clause
The WHERE clause is used as part of most SELECT queries. it is limited to retrieving rows matching conditions.
SELECT * FROM cooya_users WHERE c_u_id <= 3;
Complex WHERE clauses use the Boolean operators AND, OR, AND functions.
SELECT * FROM cooya_users WHERE c_u_name = 'melon' AND email = 'melon. cooya@gamil.com ';
This will retrieve rows that match both conditions.
SELECT c_u_id FROM cooya_users WHERE (c_u_name = 'melon' AND city LIKE 'Shang % ') OR email = 'melon. cooya@gmail.com ';
This will look for a row in which c_u_name is melon and city starts with s, or a customer whose email address is a melon.cooya@gmail.com.
The WHERE clause is also a common component of the UPDATE and DELECT statements.
UPDATE cooya_users SET c_u_name = 'cooya1 'WHERE c_u_id = 2;
Delete from cooya_users WHERE c_u_id = 2;
Sort output and group
ORDER
SELECT c_u_name FROM cooya_users WHERE gender = 'female 'AND city = 'Shanghai' order by c_u_name;
BY default, the order by clause is sorted in ascending ORDER (or ASC. DESC can be used to sort data in descending order.
SELECT * FROM cooya_users WHERE city = 'Shanghai' order by c_u_id DESC;
GROUP
The group by clause is different from the order by clause because it does not sort the output data. On the contrary, it sorts data during the query process for grouping or aggregation.
SELECT city, COUNT (*) FROM cooya_users group by city;
This query outputs a sorted list of cities and the number of customers living in each city. COUNT (*) FROM is used to COUNT the number of rows in each group.
DML (Data Manipulation Language) contains all SQL statements used to manipulate Data. The following four statements form a DML statement set: SELECT, INSERT, DELETE, and UPDATE. This section describes the last three statements.
Insert data
Method 1:
Insert into cooya_users VALUES (NULL, 'melon', 'female ', 'melon. cooya@gmail.com ','');
Method 2:
Insert into cooya_users
SET c_u_name = 'cooya ',
Gender = 'female ',
Email = 'melon. cooya@gmail.com ';
Delete data
There is a big difference between undo and delete in SQL. DROP is used to DELETE tables or databases, while DELETE is used to DELETE data.
Delete from cooya_users;
Delete all data in the cooya_users table, but not the table. Conversely, undo tables delete data and tables.
A delete statement with a WHERE clause can DELETE specific rows.
Delete from cooya_users WHERE c_u_id = 1;
Update data
You can use a syntax similar to the INSERT statement to update data.
UPDATE cooya_users SET email = lower (email );
The UPDATE statement is often used with the WHERE clause.
UPDATE cooya_users SET city = 'Shanghai' WHERE c_u_id = 1;
Create a database:
Mysql> create database dbname;
To process the database, the command interpreter requires you to use the database before being able to publish SQL statements. Run the following command in the MySql interpreter:
Mysql> use dbname
The mysql> prompt for the command example is omitted below.
Create a table:
Create table cooya_users (
C_u_id int (5) default '0' not null auto_increment,
C_u_name varchar (20) not null,
Gender varchar (10) not null,
Email varchar (30) not null,
City varchar (20 ),
Primary key (c_u_id ),
KEY names (c_u_name)
);
The create table statement has three parts:
The create table statement is followed by a free TABLE name ------ In this example, it is cooya_users.
The start parentheses are followed by a list of attribute names, types, and modifiers.
The attribute list is followed by a column of keys, which define which attributes meet the uniqueness constraints of the primary key and which attributes will be indexed for quick access.
Change tables and indexes:
Add an index:
Alter table cooya_users add index cities (city );
Delete an index:
Alter table cooya_users drop index cities;
Show Database structure
SHOW DATABASES
Lists the databases accessible to My SQL DBMS.
SHOW TABLES
Displays the tables in the database selected by the use command.
Show columns from tablename
Displays attributes, attribute types, key information, whether to allow NULL, default values, and other table information.
For example:
Show columns from customer
Displays the attributes of the customer table. DESCRIBE table generates the same output.
Show index from tablename
Displays detailed information about all indexes in the table, including the primary key.
For example:
Show index from customer
Two indexes are displayed, namely the primary index and the names Index.
SHOW STATUS
Reports mysql dbms performance and statistics details.
MySQL escape character "/"
MySQL recognizes the following escape characters:
/0
An ASCII 0 (NUL) character.
/N
A newline character.
/T
A positioning character.
/R
A carriage return.
/B
A return character.
/'
A single quotation mark.
/"
A double quotation mark.
//
A backslash.
/%
A "%" character. It is used to search for the text instance of "%" in the body. otherwise, "%" is interpreted as a wildcard.
/_ A "_" character. It is used to search for the text instance of "_" in the body. otherwise, "_" is interpreted as a wildcard.
Note: If you use "/%" or "/% _" in some body environments _", these will return strings "/%" and "/_" instead of "%" and "_".
★★
There are several methods to enclose quotation marks in a string:
1. escape:
A string is referenced by a single quotation mark ('). The single quotation mark (') character in the string can be escaped by a single quotation mark.
A string is referenced by double quotation marks (""). The character "" in this string can be escaped.
At the same time, you can continue to use an escape character "/" to escape
2. escape-free:
A string is referenced by double quotation marks (""). The single quotation marks (') in the string do not need to be treated specially and do not need to be repeated or escaped. Similarly, if a string is referenced by single quotation marks ('), the double quotation marks ("") in the string do not need to be treated specially and do not need to be repeated or escaped.
The following SELECT statement demonstrates how quotes and escaping work:
Mysql> SELECT 'hello', '"hello"', '"hello" "', 'hello', '/'hello ';
+ ---------- + -------------- + ------------------- + ---------- + --------- +
| Hello | "hello" | "hello" "| El 'Lo | 'Hello |
+ ---------- + -------------- + ------------------- + ---------- + --------- +
Mysql> SELECT "hello", "'Hello'", "'Hello'", "El" "lo", "/" hello ";
+ ---------- + ----------- + ------------ + ----------- +
| Hello | 'hello' | El "lo |" hello |
+ --------- + ----------- + ------------ + ----------- +
Mysql> SELECT "This/nIs/nFour/nlines ";
+ -------------------- +
| This
Is
Four
Lines |
+ -------------------- +
★★
If you want to insert binary data into a BLOB column, the following characters must be represented by escape sequences:
NUL
ASCII 0. You should use '/0' (a backslash and an ASCII '0') to represent it.
/
ASCII 92, backslash. .
'
ASCII 39, single quotes. It is represented.
"
ASCII 34, double quotation marks. Expressed "/"