Javaweb MySQL QuickStart (11)

MySQL Quick start 1. About MySQL

Structured Query Language Structured Query Language SQL standard, managed and maintained by ANSI (American Standards Institute, Core member of ISO). Database vendors support This standard and are expanded. An extended part, commonly called a dialect. SQL Standards and dialects: the role of Mandarin and dialect: Interacting with databases common database: Oracle/db2/mysql/sql Server

2. Classification of SQL statements

Ddl:data definition Language Data definition language alert/create/dropdml:data manipulation Language data Manipulation language insert/update/ Deletedcl:data Control Language Data Control Language dql:data query Language data Query Language SELECTTPL: Transaction processing language

3. Getting Started with the database

-----------------------------------------------------------------database operations 1). Create Database mydb1;2). View all databases show DATABASES; SHOW CREATE DATABASE mydb2;3). Create the database, specify the character set, and the database with the validation rules, create databases mydb3 CHARACTER SET UTF8 COLLATE utf8_general_ci;4). Delete the MYDB1 database that you created, drop DB mydb1;5). View the database in the server and modify the MYDB1 character set to UTF8; ALTER DATABASE mydb1 CHARACTER SET UTF8;----------------------------------------------------------------- Table structure operation data type blob binary data float (5,2) a total of 5 bits, the decimal 2 bits char (20) represents the character 20 varchar (20) If only three, the other 17 are recycled, the corresponding performance will be reduced by 1). Select Database Use mydb1;2). Create an employee table Employee3). All tables in the current database and details show Tables;desc employee View details show create TABLE Employee;4). Add column/Modify column type/change column name/delete column/modify table name alter tables employee ADD image blob; ALTER TABLE employee MODIFY Job varchar (60); ALTER TABLE Employee Change name username varchar (100); ALTER TABLE employee DROP image; RENAME TABLE employee to user; ALTER TABLE user CHARACTER SET gbk;// Modify the table's character set to GBK-----------------------------------------------------------------Insert data column name and column value order consistent insert into table name values ( Column value 1, columnValue 2 ...) How many columns do you write? insert into table name (column name 1, column name 2 ...) VALUES (column value 1, column value 2 ...) Specifies which column value data is inserted NOTE: If the DOS window operation data needs to inform the client to send GBK encoding, the server is received in GBK encoding, because the DOS window default GBK encoding tells the client that the encoding used by Gbkset character_set_client= GBK; Tell the server client to view the result set with the encoding Gbk;set CHARACTER_SET_RESULTS=GBK;------------------------------------------------------- ----------Updating Data Update user SET salary=4000,job= ' CCC ' WHERE username= ' HCH '; UPDATE user SET salary=salary+1000 where Username= ' Huang June Zhang ';---------------------------------------------------------- -------Delete the data delete from user WHERE username= ' zhw '; empty table records delete from user; TRUNCATE table user;//The entire table to destroy and then reconstruct the tables structure. Efficiency is faster than row-by-line deletion-----------------------------------------------------------------the top three names and fractions of the data query query with a total score greater than 200 select Name, sum (Math+english+chinese) as score from Studentgroup by name have score>200 ORDER BY score desc limit 0, 3;

4. Data integrity

Data integrity is to ensure that the data inserted into the data is correct, it prevents the user from possible input errors, divided into three categories 1. Entity integrity specifies that a row (that is, every record) in a table is the only entity in a table. Entity integrity is characterized by the primary key of the table: cannot be null, must have a value, and cannot repeat logical primary key: does not represent actual meaning, but distinguishes between different records. For example, ID business primary key: The representative of the specific practical significance. such as the identity card user name declaration PRIMARY key: The first way is generally used for a single primary key, ID int PRIMARY Key declaration ID is the primary key the second way is generally used for the Federated primary Key, PRIMARY key (Id,cardid) Note that the primary key can also be set from the growth ID int PRIMARY KEY auto_increment,2. Domain integrity refers to a column of a database table (that is, a field) that must conform to a specific data type or constraint non-null constraint: NOT NULL UNIQUE constraint: Uniqueusername varchar (+) NOT null unique, non-null and UNIQUE constraint 3. Referential integrity--------------------------------------------------------------One-to-many (with most) CREATE TABLE Department (ID int Primary Key,name varchar (100)); CREATE TABLE employee (ID int primary key,name varchar), salary float (8,2), department_id int,constraint department_id _FK FOREIGN KEY (department_id) REFERENCES Department (ID));-------------------------------------------------------- ----Many-to-many (with many) CREATE TABLE teacher (ID int primary key,name varchar), salary float (8,2)); CREATE TABLE student (ID int primary key,name varchar, grade varchar (10)); CREATE TABLE teacher_student (t_id int,s_id int,constraint t_iD_FK FOREIGN Key (t_id) REFERENCES teacher (id), CONSTRAINT s_id_fk FOREIGN KEY (s_id) REFERENCES student (ID), PRIMARY KEY (t_ id,s_id));-----------------------------------------------------------one-to-one (almost unused) CREATE TABLE person (ID int Primary Key,name varchar (100)); CREATE TABLE idcard (ID int primary key,number varchar), person_id int unique,constraint person_id_fk FOREIGN key (person _id) REFERENCES person (id));

5. Connection Query

Basic syntax form: From table 1 connection Type table 2 [on Join condition][where Filter] Convention: Table 1 on the left side of the connection type, referred to as the left table  2 on the right side of the connection type, called the right table 1. Cross Connect: Crosses Join returns the Cartesian product of the left and right tables (the left table 5 records, the right Table 6 records 5*6=30) SELECT * FROM Customer,orders;select * from the customer cross join ORDERS;2. Internal connection: Inner JOIN returns all records that satisfy the join condition. Implicit inner joins: (Do not use the INNER JOIN keyword) Select c.*,o.* from Customer c,orders o where c.id=o.customer_id; explicit inner join: (using the INNER JOIN keyword) SELECT * From customer c inner joins orders O on c.id=o.customer_id;3. Left OUTER join: Leave outer join=left join returns all records that satisfy the join condition, and returns all remaining records in the left table for all customers, with orders placed on the order also displayed select * from customer C LEFT OUTER join ORD ERs o on c.id=o.customer_id;4. Right outer join: Outer join=right Join returns all records that satisfy the join condition, while returning all remaining records in the right table to query all orders, while printing the customer select * from customer C of the order to outer join O Rders o on c.id=o.customer_id;

6. Sub-query/federated query

A subquery is also called a nested query, which is embedded in a SELECT clause or a WHERE clause, and also in a SELECT query statement select * from the orders where customer_id in (the Select ID from the customer where name= ' Tom '); Union keyword. Federated queries can merge query results from two query statements, remove duplicate rows from them, and return query results with no duplicate rows select * from Orders where price>200 UNION SELECT * from orders where CU stomer_id=1;

7. Backup and restore of MySQL database

Backup Database DOS window mysqldump-h localhost-u root-p mydb1>mydb1.sql Restore database Two ways 1:create databases Mydb1;use Mydb1;source C:/bak /mydb1.sql Way 2:create Database mydb1;mysql-u root-p mydb1<c:/bak/mydb1.sql;

Javaweb MySQL QuickStart (11)