MySQL Base statement

mysql> show databases;

Mysql> Show Databases \g #以行的方式显示

-e commonly used with shell scripts.

[Email protected] ~]# mysql-e ' show databases '-usystem-p123456

mysql> CREATE DATABASE HA;

mysql> CREATE database ' ha-test ';

[Email protected] ~]# ls/usr/local/mysql/data/#查看数据库存放目录

Select Database

mysql> use ha-test;

See where you are and where you are by default

Mysql> Select Database ();

Select the default database on the command line

mysql-uroot-p123456 HA

Mysql> Select Now (), user (), database ();

To delete a database:

mysql> drop database ' ha-test ';

Delete without any hint, be careful to operate

Use IF EXISTS clause to avoid deleting a nonexistent database. MySQL error Message

Mysql> drop database if exists ' ha-test ';

Mysql> CREATE database if not exists HA;

To Create a table:

Mysql> CREATE TABLE student (id int (), name char (+), age int);

To view the structure of a table:

mysql> desc Student;

Mysql> explain Mysql.user;

Mysql> show columns from Mysql.user;

Mysql> show fields from Mysql.user;

Mysql> Show columns from mysql.user like '%user ';

See what commands were executed to create the table:

Mysql> Show CREATE TABLE student \g

Mysql> CREATE TABLE Student2 (id int (), name char (+), age int) Engine=myisam DEFAULT Charset=utf8;

You can specify the default storage engine and character set for a table

To Delete a table:

mysql> drop table Student2;

Modify Table name Alter :

mysql> ALTER TABLE student rename students; #studen表名修改为students

mysql> ALTER TABLE Students modify ID int (10); Change Type

mysql> ALTER TABLE students change name Stname char (20); Change Type and name

mysql> ALTER TABLE students add sex enum (' M ', ' W '); Add Field

mysql> ALTER TABLE Students add UID Int (ten) first;

mysql> ALTER TABLE Students add address char (+) after age;

mysql> ALTER TABLE Students drop address; Delete a field from a table

Inserting records

mysql> INSERT into student values (1, ' zhangs ', 21);

mysql> INSERT into student values (2, ' Lis ', (3, ' Wange ', 26);

mysql> INSERT into student (Id,name) VALUES (4, ' Hangl ');

Delete

Mysql> Delete from students where id=3;

Mysql> Delete from students where-is null;

Update record

Mysql> Update students set sex= ' M ' where id=2;

Update students set Stname= ' Zhangsan ', age=21 where uid=1;

View the table content, database name, specified under a database. Table name

mysql> Select *from ha.student;

Mysql> Select Id,name,age from students where id>3 and age>25;

SELECT * from students where stname= ' Zhangsan ' and (age=21 or age=24);

SELECT * from students where Stname like '%l%1%2%3% ';

Inner Connection outside connection

Test: Create a school database, create two tables to insert test data

Mysql> CREATE DATABASE School;

Mysql> CREATE TABLE student (SID Int (4) primary key auto_increment, name varchar (50));

mysql> INSERT into student values (1, ' Zhang San '), (2, ' John Doe '), (3, ' King two Flaxseed '), (4, ' HA '), (5, ' Tom ');

Mysql> CREATE table grade (ID int (4) primary key auto_increment, score varchar (), Sid Int (4));

Mysql> insert into Grade (SCORE,SID) VALUES (' 1567 ', 2), (' 1245 ', 3), (' 1231 ', 4), (' 1234 ', 5), (' 1243 ', 6);

Mysql> Select student.*,grade.* from Student,grade where Student.sid=grade.sid;

Mysql> Select student.*,grade.* from student Inner joins grade on Student.sid=grade.sid;

Mysql> Select student.*,grade.* from student joins grade on Student.sid=grade.sid;

Mysql> SELECT * FROM student as S right joins grade as G on S.sid=g.sid;

Mysql> SELECT * FROM student as S left joins grade as G on S.sid=g.sid;

Mysql queries are case-insensitive by default

Mysql> SELECT * from students where binary name= ' JK '; JK data will not show up

mysql Query sort: Default to Ascending ASC

Mysql> SELECT DISTINCT ID from the students order by ID;

Mysql> SELECT DISTINCT ID from students order by id DESC;

For example, select * FROM table limit m,n statement

M refers to the index at which the record starts, starting at 0, indicating the first record

n means starting from section m+1 and taking N.

-- Viewing System Information

Show variables;

Show global variables;

Show global variables like '%version% ';

Show variables like '%storage_engine% '; The default storage engine

Show engines; see which storage engines are supported

-- View System run status information

Show status;

Import Database

You must create an empty database before you import the database

Method 1

Create database book;

mysql-uroot-p123456 Book < Book.sql

mysql> use book;

Mysql> Show tables;

Method 2:

Create database book;

mysql> use book;

Mysql> Source/root/book.sql #sql脚本的路径

Mysql> Show tables;

Export Database

Mysqldump-u system-p123456 Book>book2.sql

How to put a Select the results are exported to text

select * into outfile '/tmp/123.txt ' from books; There is a file access issue here, MySQL users are able to access / tmp Path of , so put it in tmp under

SUM () sum

AVG () Average:

Max () max value:

Min () min value:

Count () Count Records:

String

substr (String, Start,len) Intercept: From start, intercept Len long. Start starts from 1.

Mysql> Select substr (btypename,1,6) from category where btypeid=10;

+-----------------------+

| substr (btypename,1,6) |

+-----------------------+

|      Autoca | was originally AutoCAD Technology

Concat (STR1,STR2,STR3 ...) stitching. Make multiple fields into one field output

Mysql> Select Concat (bname,publishing) from books;

Mysql> Select Concat (bname, "-----", publishing) from books;

Upper () uppercase

Lower () lowercase

Date

Curdate ():

Curtime ();

Now ();

Create table ckdata10 (Riqi date)

mysql> INSERT into ckdata10 values (' 2015-10-23 '), (20180925);

Mysql> CREATE TABLE Ckdata11 (Showtime time);

mysql> INSERT into CKDATA11 values (' 17:23:45 '), (' 18:00 '), (162634);

Mysql> CREATE TABLE Ckdata12 (Showyear year);

mysql> INSERT into CKDATA12 values (2015), (01), (9), (69), (70), (2070);

Mysql> CREATE TABLE Kdata14 (f_datatime datetime,f_timestamp timestamp);

mysql> INSERT into KDATA14 values (' 1999-11-12 23:23:45 ', 19991112232345)

If you want to query the timestamp select Unix_timestamp (' 1999-11-12 23:23:45 ');

Show time stamp:

[Email protected] ~]# date +%s

1444124814

Displays the timestamp for the specified time

[Email protected] ~]# date-d "2015-10-06 17:48:23" +%s

1444124903

[Email protected] ~]# date-d "1970-01-01 08:00:00" +%s

0

[Email protected] ~]# date-d "@1455455465"

Sun Feb 21:11:05 CST 2016

[Email protected] ~]# date-d "@1455455465" "+%y/%m/%d%h:%m:%s"

2016/02/14 21:11:05

[Email protected] ~]# date-d "@1455455465" "+%y-%m-%d%h:%m:%s"

2016-02-14 21:11:05

[Email protected] ~]# date +%f
2016-10-15

MySQL Base statement