MySQL Basics-1

*SQL of databse

1.create databse

Create Database mydb1;

Create database mydb2 character set UTF8;

Create database mydb3 character Set UTF8 collate

Create database name;

Create database name character set code;

Create database name character Set code COLLATE check set;

2.show databses;

show databases;

Select Database (); View Current Database

3. Display the database creation statement

Show CREATE DATABASE name

4.delete Database

drop database name;

5.update Database

ALTER DATABASE name character set character encoding set collate check set;

Use database name; switch database

*SQL for the table

1.create

CREATE TABLE Table name (

Field 1 field type,

Field 2 field type,

....

field N field type

);

ex:

CREATE TABLE Employee (

ID int primary key auto_increment,

name varchar () is not NULL,

Gender varchar (TEN) unique,

Birthday date,

entry_date date,

Job varchar (+),

Salary Double,

Resume longtext

);

2.show the table

desc tablename;

3. Delete a table

drop table name;

4. Modify the table

ALTER TABLE table name

1.add add a new column.

ALTER TABLE users add password varchar (a);

2.modify Modifying columns

ALTER TABLE users modify password int;

3.drop Delete Columns

ALTER TABLE users drop password;

4.change Modifying column names

ALTER TABLE users change password pwd varchar ();

5.insert

insert into table name (Field 1, Field 2,...) VALUES (value 1, value 2,....);

5.update data

Update table name Set field = value, field 1= value.

Modify by condition

Update table name Set field = value, field 1= value,..... where condition;

6.delete data

Delete from table name;---All data is deleted

Delete from table name where condition.

*datatype for the MySQL

there are more than N types of fields for tables in MySQL

data types in Java mysql data type

byte SMALLINT

short mediumint

int int

long bigint

float float

double double

boolean bit

String Char varchar

java.sql.Date Date DateTime

java.sql.Time time

Java.sql.TimeStamp TimeStamp

Characters and strings are the same concept in a database. varchar Char

What is the difference between char and varchar?

char fixed-length.

A varchar is a variable length.

About the date types in the database and the date type handling problems in Java:

in the Java---java.util.Date type.

and there is a date type in the database. It corresponds to the java.sql.Date in Java.

There are several other types of databases that correspond to the database in Java.

java.sql.Date--------Date DateTime

Java.sql.Time-------- time

Java.sql.TimeStamp---TimeStamp

Big Data types

The blob----a binary type.-------inputstream corresponds.

text----Large text type-------reader correspondence

tinyblob tinytext 255 bytes

blob text 64KB

mediumblob mediumtext 16MB

longblob longtext 4GB

About the decimal type in MySQL

float

Double

NUMERIC (5,2)

3. Constraints on the table

What are the constraints? What is its role?

Constraining a field is forcing control over the properties of the field.

Adding constraints can improve the integrity and security of our data.

1. PRIMARY KEY constraints

we can make a field a primary key, and if the field is a primary key, it has two properties.

1. Uniqueness

2. Non-null

How do I add a primary key?

add primary key to the field.

in the development of the primary key, it does not have any meaning, but to make the identity.

for primary keys, it is unique and non-repeatable, and in MySQL you can automatically grow fields of type int by auto_increment.

Sqlservet--------Identity (+)

Oracle------------use it sequence

2. Non-null constraints

Not null

3. Unique constraints

Unique

4. FOREIGN KEY constraint (multi-table)

Considerations when inserting data:

1. The data needs to be quoted. The number of types can be used without.

2. If you insert a null value, use NULL.

3. The inserted data must match the type of the field.

4. Specify the number of columns to correspond to the values.

5. The length of the inserted data cannot exceed the specified length of the column.

Questions about the Chinese when inserting:

Insert into users values (null, ' Zhang San ', ' 1999-10-10 ');

ERROR 1366 (HY000): Incorrect string value: ' \xd5\xc5\xc8\xfd ' for column 'username ' at row 1

In inserting the Chinese times wrong.

cause: The cmd window we are currently using is a client, and the encoding used in this client is the default encoding for the current system.

when the MySQL database server is installed, we specify the encoding UTF8, then its client and server side are UTF8 encoded.

Solution:

Find a person My.ini file under the MySQL installation file.

Modify the My.ini file to restart the service.

Temporary modification

in MySQL, a total of 6 places use the character set.

Client connetion result is related to clients, database server system and server side

View all system character sets: Show variables like ' character% ';

can be gbk by-->set names;

SQL's about deleting data:

drop table name-------It is used to delete the table structure.

Delete from table name------It is used to delete data from the table, and the table also exists.

TRUNCATE table name.---------It is the same as delete, which is used to delete data from the table.

What is the difference between delete and truncate?

1.

when delete is deleted, it is a one-piece deletion. It is less efficient.

truncate DELETE is to first delete the table structure before recreating the table structure.

2.

Delete is a DML statement.

truncate is not a DML statement.

for DML statements it is subject to transactional control. In a transaction, we can recover the data by deleting it.

truncate it is not subject to transaction control. In other words, it cannot be recovered after deletion.

MySQL Basics-1