Database Advanced 3

Index

1、普通索引(index)2、唯一索引(unique)    1、使用规则        1、一个表中可以有多个unique字段        2、unique字段的值不允许重复,但可以为空值        3、unique的key标志是UNI    2、创建唯一索引        1、创建表时创建            1、方式一                create table t1(                id int(3) zerofill,                name char(20) unique                );            2、方式二                create table t2(                id int,                name char(20),                age tinyint,                unique(name),                unique(age)                );        2、在已有表中创建            create unique index 索引名 on 表名(字段名);    3、删除唯一索引(unique)        drop index 索引名 on 表名;        删除普通索引、唯一索引只能一个一个删

Primary key (primary key)

    1, using rule 1, a table can only have one primary key (primary) field 2, the corresponding field value is not allowed to repeat, and cannot be null value 3, the key of the primary key field is the PRI 4, the table can uniquely identify a record of the field settings                Key field 2, create primary key (primary key) 1, CREATE table when creating 1, mode one: Field name data type primary key, create TABLE t3 (            ID int primary key, name Char (20));                 2, Mode two: CREATE TABLE t4 (id int, name char, primary key (ID)        );        2. Create a primary key in an existing table ALTER TABLE name add primary key (field name);            3. Delete primary key if the primary key is from the growth property, first delete the self-growth attribute, then delete the primary key ALTER TABLE name modify field name data type;    ALTER TABLE name drop PRIMARY key;            3. Self-growth attribute (auto_increment) 1, function: Usually used together with the primary key 2, when creating the table, add the Self-growth attribute field name data type primary key auto_increment        CREATE TABLE t5 (ID int primary key auto_increment, name char (15)); 3. Add self-growth property to existing tables ALTER TABLE name modify field name data type primary key Auto_increment 

2, data import
1, Role
Import the contents of the file system into the database
2, Syntax
Load data infile "file name"
into table table name
Fields terminated by "delimiter"
lines terminated by "\ n"
3, Exercise
Import the contents of the/etc/passwd file into the UserInfo table under the library day03

    TARENA:X: 1000:1000:tarena,,, User name password UID GID user description:/home/tarena:/bin/bash Home Directory Login Permissions 4, Action Step 1, create the corresponding table 2 in the data, copy the file to be imported into the default search path of the database 3, import the system files into the created table 1, create the table creat E Table userinfo (username char), password char (1), UID int, gid int, comment varcha    R (homedir), Shell varchar (50));        2. Copy the file you want to import into the default search path of the database 1, how to view the default search path for the database show variables like "Secure_file_priv"; 2, sudo cp/etc/passwd/var/lib/mysql-files/3, execute data import statement load infile "/var/lib/mysql-files/passwd" into table UserInfo fields terminated by ":" Lines terminated by "\ n" 4, add an ID field in the first column of the UserInfo table, type int, set the primary key with self-growing property Alt ER table userinfo add ID int primary key auto_increment FIRST5, Exercise 2 import aid1709.csv score table 1, create table of corresponding tables AID 1709 (id int, name varchar (), score float (5,2), Phone char (One), class char (7)) default Charset=utf8; 2, copy to the database directory sudo cp/home/tarena/aid1709.csv/var/lib/mysql-files/3, execute the Data import statement load infile "/var/lib/ Mysql-files/aid1709.csv "into table AID1709-terminated by", "lines terminated by" \ n "6, pay attention to Ubun Permission issues for files in Tu 1, sudo-i 2, Cd/var/lib/mysql-files 3, Ls-l aid1709.csv 4, chmod 644 aid1709.csv 5, Ls-l AID1709. CSV # # with r permission 6, execute import statement load data infile "/var/lib/mysql-files/aid1709.csv" Into table AID1709 field S terminated by "," lines terminated by "\ n";

4. Data export
1. function
Save records from a database table to a system file
2. Syntax format
Select ... from table name
into outfile "file name"
Fields terminated by "delimiter"
Lines terminated by "\ n"
3. Practice
1. Export the user name, password and UID three fields in the UserInfo table, the file name is User.txt
Select Username,password,uid from UserInfo
into outfile "/var/lib/mysql-files/user.txt"
Fields terminated by ""
Lines terminated by "\ n"
2. Export the values of user and host two fields from the user table in MySQL library to User2.txt, and store them under the database search path
Select User,host from Mysql.user
into outfile "/var/lib/mysql-files/user2.txt"
Fields terminated by ""
Lines terminated by "\ n";
4. Attention
1, the content of the export is entirely determined by the SQL query statement
2. The path must be specified in the corresponding database search path when the Export command is executed
Show variables like "Secure_file_priv";

Replication of tables

  1, copy of table 1, Syntax create TABLE table name select query statement;        2, Exercise 1, copy all the records and fields in the UserInfo table, Userinfo2 CREATE TABLE Userinfo2 select * from UserInfo;        2. Copy the first 10 records of the UserInfo table, Userinfo3 CREATE TABLE Userinfo3 select * from UserInfo limit 10; 3. Copy the 第2-10条 record of Username,password,uid three fields of the UserInfo table, Userinfo4 create table Userinfo4 Select Username,password,uid    From UserInfo limit 1,9;2, copy table Structure 1, Syntax format CREATE TABLE table name select query statement where false; 2, pay attention to copy the table when the key property of the original table will not be copied over 3, Exercise 1, create a User1 table, containing the UserInfo table Username,uid two fields of the first 2 records create TABLE User1 Select    Username,uid from userinfo limit 2; 2, create the User2 table, contains the UserInfo table Gid,homedir two fields of the first 3 records CREATE TABLE User2 select Gid,homedir from UserInfo limit 3;  

6. Nested query
1. Definition
The query result of the inner layer as the outer query condition
2. Syntax format
Select query statement where condition (select query statement);
3, exercise (using UserInfo table operation)
1, the value of the UID is less than the average of the field user name and UID display
Select Username,uid from UserInfo where UID < (select AVG (UID) from userinfo);
7. Multi-Table Query
1. Two kinds of ways
1, select field Name list from table name list; Cartesian product
SELECT * from User1,user2;
2. Select field List from table name list where condition;
3. Practice
1, show the provinces and cities detailed information
Select Sheng.s_name,city.c_name from sheng,city where sheng.s_id=city.cfather_id;
2, show the provinces and counties detailed information
Select Sheng.s_name,city.c_name,xian.x_name
From Sheng,city,xian
, where
-Sheng.s_id=city.cfather_id and
city.c_id=xian.xfather_id;
8. Connection Query
1. Internal connection
1. Definition
Remove all rows from the table that do not match the other connected tables
2. Syntax format
List of select field names from Table 1
Inner JOIN table 2 on condition;
3, show the provinces and cities detailed information, no match to not show
Select Sheng.s_name,city.c_name,xian.x_name
From Sheng
Inner JOIN City on sheng.s_id=city.cfather_id
-Inner join Xian on city.c_id=xian.xfather_id;

External connection

    1、左连接        1、定义            以左表为主显示查询结果        2、语法            select 字段名列表 from 表1             left join 表2 on 条件;        3、练习            1、显示省市详细信息,以省表为主显示                select sheng.s_name,city.c_name from sheng left join city on sheng.s_id=city.cfather_id;            2、显示省市县详细信息,要求市全部显示                -> select sheng.s_name,city.c_name,xian.x_name                 -> from sheng right join city                -> on sheng.s_id=city.cfather_id                -> left join xian on city.c_id=xian.xfather_id;    2、右连接        以右表为准显示查询结果,用法同左连接

ER model &er diagram

1、定义    ER模型即实体-关系模型,ER图即实体-关系图2、三个概念    1、实体        1、定义：现实世界中任何可以被认知、区分的事物        2、示例            1、学校 ：学生、教师、课程、班主任            2、企业 ：职工、产品    2、属性        1、定义：实体所具有的特性        2、示例            1、学生属性：学号、姓名、年龄、性别            2、产品属性：产睥睨编号、名称、规格    3、关系        1、定义：实体之间的联系        2、分类            一对一关系(1:1) 班级和班长            一对多关系(1:n) 公司和职工            多对多关系(m:n) 学生和课程    4、ER图的绘制        1、矩形框代表实体,菱形框代表关系,椭圆形代表属性

Database Advanced 3