Javaweb06-html Notes (i)

1.1 Previous lesson Content review:

BootStrap：* 响应式的HTML,CSS,JS的框架.* 响应式设计：    * 设计一套页面,适配不同的设备,在手机,PAD,PC端都能够正常浏览.* 响应式原理：    * 使用CSS3的媒体查询,根据屏幕的分辨率匹配不用的样式.* BootStrap:    * 全局CSS：        * 栅格：        * 表单:        * 按钮...    * 组件:        * 导航条：        * 分页栏:        * 标签页:    * JS的插件：        * 图片轮播.

1.2 MySQL review: 1.2.1 MySQL Overview: 1.2.1.1 What is a database:
Database: is a file system that must be accessed through standard SQL.
1.2.1.2 What is a relational database:
Relational databases are stored in relationships between entities.

1.2.1.3 commonly used relational database:

MySQL   ：免费的小型的数据库,现在被Oracle收购.Oracle  ：Oracle公司收费的大型的数据库.SQLServer   ：微软公司收费中型的数据库.DB2 ：IBM公司收费的大型的数据库.SyBase  ：SyBase公司收费的数据库.已经被淘汰.PowerDesigner数据建模的工具.SQLite  ：小型的嵌入式的数据库.***** Java程序中经常使用的数据库MySQLOracle

1.2.1.4 the structure of the database storage:
Overview of 1.2.2 sql: 1.2.2.1 What is sql:
SQL: Structured Query language.
1.2.2.2 SQL Classification:

DDL:数据定义语言    * create,alter,drop...DML:数据操纵语言    * update,insert,deleteDCL:数据控制语言    * grant,if..DQL:数据查询语言    * select

1.2.2.3 Features of SQL:
Non-procedural language: A statement will have a running result.
1.2.3 using SQL1.2.3.1 with SQL Operations database (CRUD operations on database)
"CREATE DATABASE"

语法:* create database 数据库名称 [character set 字符集 collate 字符集校对];练习:* 创建db1;    * create database db1;* 创建一个带有字符集的数据库db2;    *  create database db2 character set gbk;* 创建一个带有字符集和校对规则的数据库db3;    * create database db3 character set utf8 collate  utf8_bin;

"View Database"

语法:* 查看数据库服务器中所有的数据库:    * show databases;* 查看某个数据库的定义信息.    * show create database 数据库名;* 查看当前正在使用的数据库信息.    * select database();

"Delete Database"
Grammar:

• Delete database:
  • drop database name;
    Modify Database
    Syntax:
• modifies the database's character set and proofing rules that are modified by the databases.
  • ALTER DATABASE name character set new character set collate proofing rules;
    "Switch Database"
    Syntax:
• use database name;
  1.2.3.2 using SQL to manipulate tables in the database (Operations on table crud to the database)
  CREATE TABLE

? Syntax: * CREATE TABLE table name (field name type (length) constraint, field name type (length) constraint, field name type (length) constraint);?                            Data type: * Java type: Mysql:byte/short/int/long tinyint/smallint/int/bigint String Char/varchar                            * difference? Char is a fixed-length string, varchar variable-length string. * CHAR (8) and varchar (8) * If inserting a string hello inserts into char then insert hello. insert into varchar insert Hello float FL                            Oat Double double Boolean bit date Date/time/datetime/timestamp * datetime and timestamp are both date types that have both dates and time Difference? DateTime requires the use of external incoming dates. If this value is not passed, it is null. Timestamp uses the system's current time as the default value for this value. Text file Two-level file blob***** oralce use clob/blob***** mysql in addition to the string type need to set the length of the other types have a default length. Constraint: Single table constraint: * PRIMARY KEY constraint: Primary key (default is unique non-empty) * UNIQUE constraint: unique* non-null constraint: NOT NULL? Create a table:?    Select the database before creating the table: use a database; CREATE TABLE employee (Eid int primary key auto_increment, ename varchar) NOT NULL, Email varchar (() unique, birthday date, job varchar (), resume text); "Table View"? See which tables are in the database: * Show tables;? ViewTable structure: * DESC table name; "Table Delete"? Deletion of table: * drop table name; "Table modification"?    Modify table Add Column: * ALTER TABLE name add column name type (length) constraint; * ALTER TABLE employee ADD image varchar (50);?    Modify Table Delete Column: * ALTER TABLE name drop column name; * ALTER TABLE employee drop job;?    Modify the type length and constraints of a table's columns: * ALTER TABLE table name modify column name type (length) constraint; * ALTER TABLE employee modify image varchar (n) not null;?    Modify the column name of the table * ALTER TABLE name change old column name new column name type (length) constraint; * ALTER TABLE employee change image eimage varchar (60);?    Modify Table name * Rename table name to new table name; * Rename table employee to user;?    Modify the character set of the table: * ALTER TABLE name character set character set; * ALTER TABLE user character set GBK;

1.2.3.3 using SQL to manipulate the records of tables in a database (a CRUD operation on a table's records)
"Insert Record"
? Grammar

• Insert into table name (column name, column name,...) VALUES (value 1, value 2,...); ---Insert the value of the specified column
• Insert into table name values (value 1, value 2,...); ---Insert values for all columns
  ? Precautions:
• The number of column names corresponds to the number of values.
• The type of the column corresponds to the type of the value. The location also corresponds.
• If the type of column is a string or a date, a single quotation mark is used when writing the value.
• The maximum length of the inserted value cannot exceed the maximum length of the column.
  ? Insert Record:

* 插入某几列的值:    * insert into employee (eid,ename,email) values (null,‘aaa‘,‘[email protected]‘);* 插入所有列的值:    * insert into employee values (null,‘bbb‘,‘[email protected]‘,‘1990-09-01‘,‘HR‘,‘I am HR‘);? 插入中文：insert into employee (eid,ename,email) values (null,‘张三‘,‘[email protected]‘);ERROR 1366 (HY000): Incorrect string value: ‘\xD5\xC5\xC8\xFD‘ for column ‘ename‘ at row 1***** 插入中文问题的解决：* show variables like ‘%character%‘;***** 找到MYSQL的安装路径/my.ini文件：

Reload the MySQL configuration file: * services.msc* stop the MySQL service and restart the MySQL service. * Execute the previous SQL statement. "Modify Record"? Syntax: * Update table SET column name = value, column name = value [where condition];? NOTE: * The column name and value type should also be consistent. * Values cannot exceed the maximum length of the column. * Values are strings or dates and need to use single quotes. Exercise: * Modify the job for all records in the employee table for worker * Update employee set job= ' worker '; * Modify the employee table to change the mailbox with name AAA to [email][email  Protected][/email] * Update employee Set email = ' [email protected] ' where ename = ' aaa '; * Modify the Employee table mailbox with name BBB Change to [Email][email protected][/email] simultaneously modify job for HR * Update employee Set email = ' [email protected] ', job= ' hr ' whe Re ename= ' BBB '; "Delete Record"? Syntax: * Delete from table [where condition];? Note: * Delete a row of records in a table, you cannot delete a column value * If there are no conditions to delete all columns in the table. Exercise: * Delete records with ID 8: * Delete FROM employee where Eid = 8;* Delete all records: * Delete from employee;?    Delete all records from a table TRUNCATE TABLE table name and delete from table difference? * Difference: * TRUNCATE TABLE Delete records: Delete the entire table and recreate a new table. Truncate belongs to the DDL. * Delete from delete record of table: one to delete.        Delete belongs to DML. * Transaction management can only function on DML statements. If you delete all records in one transaction using Delete, you will be able to retrieve them. "Basic query"? Query statement: * SELECT [DISTINCT] *| column name from table [where condition];?Preparation: CREATE TABLE exam (ID int primary KEY auto_increment,name varchar), 中文版 int,chinese int,math int); INSERT INTO E Xam values (null, ' Zhang San ', 85,74,91); INSERT into exam values (NULL, ' John Doe ', 95,90,83); INSERT into exam values (null, ' Harry ', 85,84,59 INSERT INTO exam values (null, ' Zhao Liu ', 75,79,76), insert into exam values (null, ' Tianqi ', 69,63,98), insert into exam values (NULL, ' John Doe ', 89,90,83);? Query all records: * select * from exam;? Check the name of the class person and English score: * Select name,english from exam;? Query English results, will repeat the English results removed: * SELECT distinct 中文版 from exam;? Query John Doe student score: SELECT * from exam where name= ' John Doe ';? The query name is called John Doe and the English score is greater than 90 of the SELECT * from exam where name= ' John Doe ' and Chinese >90;? Show score +10: select name, english+10,chinese+10, math+10 from exam;? Show the name of the person and the score of the corresponding scores: select Name,english+chinese+math from exam;? Use as as an alias, as can be omitted. Select name, english+chinese+math as sum from exam; "Conditional query"?  The where statement can be added after: The keyword of the condition: =, >, >=, <, <=, <>like can use placeholders: _ and%: Underscores match one character,%: can match any number of characters. * like ' Zhang% '; Like ' Zhang _ '; Like '% Ming '; Like '%% ';followed by a set of values. * ID in (All-in-one) and or not "sort query"? Order by sorts the data. The default ascending order.    (ASC ascending, desc descending) * Queries all students for information and sorts them by language scores.    * SELECT * FROM exam ORDER BY chinese;* queries all students for information and is sorted in descending order by language score.    * SELECT * FROM exam ORDER by Chinese desc;* query the student's information, in descending order according to English grades, if the English result is the same, according to the language descending.    * SELECT * FROM exam ORDER BY Chinese desc, Chinese desc;* inquires the information of the students surnamed Li while sorting in ascending order of English. * SELECT * from exam where name like ' Li% ' order by 中文版; "Aggregate function"? SUM ()? Count ()? Max ()? Min ()? AVG () * Query for each student's total score: * Select Name, (English+chinese+math) from exam;* statistics for all students: * Select SUM (english+chinese+math) fro   M exam;  --Ifnull (english,0) * Select sum (中文版) +sum (Chinese) +sum (math) from exam;* count Students: * Select COUNT (*) from exam;* The highest score in statistical English: * Select Max (Chinese) from exam;* Statistical language score: * Select min (Chinese) from exam;* statistical English score average: * Select AVG (中文版) from exam; "Grouping"?    Group by creates a table of order Details: * Count the number of items purchased for each category in the order: * SELECT product,count (*) amount spent on each category of goods in the OrderItem GROUP by product;* statistics Order: * SELECT product,sum (price) FROM OrderItem GROUP by product;* The total amount spent on each category of items in the order is greater than 2000 information. * SELECT product,sum (prices) from OrderItem GROUP by product have SUM (price) > 2000;* Statistics orders have electronic goods and the amount spent is greater than 1500 at the same time Ordering: * SELECT product,sum (price) from OrderItem WHERE product like ' electricity% ' GROUP by product have SUM (price) > DER by SUM (price) DESC; "Summary of SQL query Statements" ORDER: S...F...W...G...H...O ...;

Javaweb06-html notes (i)