MySQL Database common operations

MySQL database common operation one, MySQL General Command 1) displays the current database version number and date.

MariaDB [(none)]> select version(),current_date();+----------------+----------------+| version()      | current_date() |+----------------+----------------+| 5.5.60-MariaDB | 2018-08-29     |+----------------+----------------+1 row in set (0.00 sec)

2) Set the field name with the AS keyword.

SELECT VERSION() AS name; //可设置中文，通过单引号select 字段 as 字段别名;例如：select version() as '版本号';MariaDB [(none)]> select version() as '版本号';+----------------+| 版本号         |+----------------+| 5.5.60-MariaDB |+----------------+1 row in set (0.00 sec)

3) return calculation results via SELECT

MariaDB [(none)]> select (20+5)*4;+----------+| (20+5)*4 |+----------+|      100 |+----------+1 row in set (0.00 sec)

4) User and date of database through multi-line implementation

MariaDB [(none)]> select    -> user()    -> ,    -> now()    -> ;+----------------+---------------------+| user()         | now()               |+----------------+---------------------+| [email protected] | 2018-08-29 21:59:39 |+----------------+---------------------+1 row in set (0.00 sec)

5) Display database users and dates by one line

MariaDB [(none)]> select user();select now();+----------------+| user()         |+----------------+| [email protected] |+----------------+1 row in set (0.00 sec)+---------------------+| now()               |+---------------------+| 2018-08-29 22:01:49 |+---------------------+1 row in set (0.00 sec)

6) Cancellation of the order

MariaDB [(none)]> show database    -> \c

7) MySQL window exit

MySQL Common data types

• Integer type: tinyint,smallint,int,bigint
• Float type: float,double,decimal (m,d)
• Character Type: Char,varchar
• Date Type: Datetime,date,timestamp
• Remark Type: Tinytext,text,longtext

Second, MySQL database operation 1) display the database that currently exists

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)

2) Select the database you need

MariaDB [(none)]> use test;Database changed

3) View the currently selected database

MariaDB [test]> select database();+------------+| database() |+------------+| test       |+------------+1 row in set (0.00 sec)

4) Set the Chinese code according to the database

MariaDB [test]> SET NAMES gbk; --set names utf8;Query OK, 0 rows affected (0.00 sec)

5) See how many tables there are

MariaDB [test]> show tables;Empty set (0.00 sec)

6) View all the contents of a single table

MariaDB [test]> select now() from dual;+---------------------+| now()               |+---------------------+| 2018-08-29 22:16:05 |+---------------------+1 row in set (0.00 sec)

7) Create a database

MariaDB [test]> create database book;Query OK, 1 row affected (0.00 sec)

8) Create a table in the database

MariaDB [book]> create table users(    -> username varchar(20) not null,    -> sex char(1),    -> birth datetime    -> );Query OK, 0 rows affected (0.01 sec)

9) Display the structure of the table

MariaDB [book]> desc users; --describe users;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(20) | NO   |     | NULL    |       || sex      | char(1)     | YES  |     | NULL    |       || birth    | datetime    | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

10) Insert a piece of data into the table

MariaDB [book]> INSERT INTO users (username,sex,birth) VALUES ('Lee','x',NOW());Query OK, 1 row affected (0.00 sec)

11) Filter the specified data

MariaDB [book]> SELECT * FROM users WHERE username = 'Lee';+----------+------+---------------------+| username | sex  | birth               |+----------+------+---------------------+| Lee      | x    | 2018-08-29 22:20:38 |+----------+------+---------------------+1 row in set (0.00 sec)

12) Modify the specified data

MariaDB [book]> UPDATE users SET sex = '男' WHERE username='Lee';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1

13) Delete the specified data

MariaDB [book]> DELETE FROM users WHERE username='Lee';Query OK, 1 row affected (0.00 sec)

14) Sort by the specified data

MariaDB [book]> SELECT * FROM users ORDER BY birth DESC; --正序Empty set (0.00 sec)

15) Delete the specified table

MariaDB [book]> DROP TABLE users;Query OK, 0 rows affected (0.00 sec)MariaDB [book]> show tables;Empty set (0.00 sec)

16) Delete the specified database

Third, MySQL commonly used function text function

numeric functions

Date and Time functions

Format Date and time (Date_format () and Time_format ())

Go: MySQL Database common operations

MySQL Database common operations