MySQL statement summary

1. Select database USE
2. Display information SHOW
3. Create a table
4. Insert data INSERT

1. Select database USE

When you first connect to MySQL , there is no database open for you to use, and we need to select a database before we can perform the following operations.

Method: USE statement

USE Mdatabase;

In this way, the database can be opened and the data can be read.

2. Display information SHOW

MySQL extracts information from internal tables

SHOW database;

Returns a list of available databases

SHOW tables;

Returns a list of tables in the database

SHOW columns from table;

Show Table Columns

3. Create a table

Basic syntax:

//by LMY
CREATE DATABASE database name; /*Create database*/
USE database name; /*Connect to database*/

CREATE TABLE table name 1
(
    Column name 1 Data type requirements,
    Column name 2 Data type requirements,
    Column name 3 Data type requirements,
    .......
    PRIMARY KEY /*Note, there is no comma here*/
);

Take my database job as an example:

(Environment, MySQL workbench )

Require:

Create a database named " my_own_movie_database ", then create the following tables and insert the corresponding input

First, open workbench and add a new SQL file

After that, write the sql statement in the new file

Write the corresponding sql statement. After writing, you will find a new database on the left. Click it and you can see the table you created.

The complete code is as follows:

drop database if exists `my_own_movie_database`;
create database `my_own_movie_database`;

USE `my_own_movie_database`;

CREATE TABLE `movieexec` (
  `name` char(30) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `cert` int(11) NOT NULL DEFAULT '0
  ',
  `netWorth` int(11) DEFAULT NULL,
  PRIMARY KEY (`cert`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "movieexec"
#

INSERT INTO `movieexec` VALUES ('Calvin Coolidge','Fast Lane',123,20000000),('Merv Griffin','Riot Rd.',199,112000000),('Stephen Spielberg','123 ET road',222,100000000),('Ted Turner','Turner Av.',333,125000000),('George Lucas','Oak Rd.',555,200000000),('Jane Fonda','Turner Av.',567,200000000);

#
# Source for table "movies"
#

CREATE TABLE `studio` (
  `name` char(30) NOT NULL DEFAULT '',
  `address` varchar(100) DEFAULT NULL,
  `presC` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ;

#
# Data for table "studio"
#

INSERT INTO `studio` VALUES ('MGM','MGM Boulevard',123),('Fox','Fox Boulevard',199),('Disney','Disney Boulevard',222),('USA Entertainm','USA Entertainm Boulevard',333),('Paramount','Paramount Boulevard',555);


CREATE TABLE `movies` (
  `title` char(100) NOT NULL DEFAULT '',
  `year` int(11) NOT NULL DEFAULT '0',
  `length` int(11) DEFAULT NULL,
  `movieType` char(10) DEFAULT NULL,
  `studioName` char(30) ,
  `producerC` int(11) DEFAULT NULL,
  PRIMARY KEY (`title`,`year`),
 foreign key(studioName) references studio(name) on delete cascade
) ;

#
# Data for table "movies"
#

INSERT INTO `movies` VALUES ('Empire Strikes Back',1980,111,'drama','Fox',555),('Gone With the Wind',1938,null,'drama','MGM',123),('Logan\'s run',1977,120,'drama','MGM',888),('Pretty Woman',1990,119,'drama','Disney',999),('Star Trek',1979,132,'sciFic','Paramount',444),('Star Trek: Nemesis',2002,116,'sciFic','Paramount',321),('Star Wars',1977,124,'sciFic','Fox',555),('Terms of Endearment',1983,132,'drama','MGM',123),('The Man Who Wasn\'t There',2001,116,'comedy','USA Entertainm',777),('The Usual Suspects',1995,106,'drama','MGM',999);

#
# Source for table "moviestar"
#


CREATE TABLE `moviestar` (
  `name` char(30) NOT NULL DEFAULT '',
  `address` varchar(255) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  PRIMARY KEY (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "moviestar"
#
INSERT INTO `moviestar` VALUES ('Alec Baldwin','Baldwin Av.','M','1977-06-07'),('Debra Winger','A way','F','1978-05-06'),('Harrison Ford','Prefect Rd.','M','1955-05-05'),('Jack Nicholson''s','X path','M','1949-05-05'),('Jane Fonda','Turner Av.','F','1977-07-07');
#
# Source for table "starsin"
#
CREATE TABLE `starsin` (
  `movieTitle` char(100) NOT NULL DEFAULT '',
  `movieYear` int(11) NOT NULL DEFAULT '0',
  `starName` char(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`movieTitle`,`movieYear`,`starName`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "starsin"
#

INSERT INTO `starsin` VALUES ('Empire Strikes Back',1980,'Harrison Ford'),('Star Wars',1977,'Carrie Fisher'),('Star Wars',1977,'Harrison Ford'),('Star Wars',1977,'Mark Hamill'),('Terms of Endearment',1983,'Debra Winger'),('Terms of Endearment',1983,'Jack Nicholson'),('The Usual Suspects',1995,'Kevin Spacey');

#
# Source for table "studio"
#

4. Insert data INSERT

Basic writing:

INSERT INTO table name 1 VALUES (data 1, data 2, data 3.....), (data 1, data 2......)....;

Finally, run, shortcut key: Ctrl + Shift + Enter
(I didn’t know how to run this part until I was almost done with the course… I cried so much. I couldn’t install the software in the first half of the semester, and I was confused every day in the second half of the semester why I couldn’t create a table…)
After the table is created, you can see the contents of the table

This is the end of this article about MySQL statement summary. For more relevant MySQL statement summary content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: