You are here
马哥 33_04 _MySQL系列之八——多表查询、子查询及视图 有大用
mysql> select * from students;
+-----+--------------+------+--------+------+------+------+---------------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+--------------+------+--------+------+------+------+---------------------+
| 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:10:10 |
| 2 | YangGuo | 17 | M | 2 | 3 | 1 | 2012-04-06 10:10:10 |
| 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 5 | HuangRong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:10:10 |
| 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
| 9 | LiHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:10:10 |
| 10 | YiLin | 19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
| 11 | stu1 | 23 | F | 4 | 1 | 6 | NULL |
| 12 | stu2 | 23 | F | 4 | 1 | 6 | NULL |
+-----+--------------+------+--------+------+------+------+---------------------+
12 rows in set (0.00 sec)
mysql> select cid2 from students where cid2 is not null; #cid2所有被学习的课程
+------+
| cid2 |
+------+
| 7 |
| 3 |
| 1 |
| 10 |
| 9 |
| 4 |
| 7 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql>
mysql> select distinct cid2 from students where cid2 is not null; #去重, 这就是cid2所有被学习的课程
+------+
| cid2 |
+------+
| 7 |
| 3 |
| 1 |
| 10 |
| 9 |
| 4 |
+------+
6 rows in set (0.00 sec)
mysql>
mysql> select cname from courses where cid not in (select distinct cid2 from students where cid2 is not null); #cid2中没有被学习的课程
+------------------+
| cname |
+------------------+
| TiaJiquan |
| Qianzhuwandushou |
| Qishangquan |
| Wanliduxing |
+------------------+
4 rows in set (0.00 sec)
mysql>
mysql> select * from courses;
+-----+------------------+------+
| cid | cname | tid |
+-----+------------------+------+
| 1 | Hamagong | 2 |
| 2 | TiaJiquan | 3 |
| 3 | Yiyangzhi | 6 |
| 4 | Jinshejianfa | 1 |
| 5 | Qianzhuwandushou | 4 |
| 6 | Qishangquan | 5 |
| 7 | Qiankundanuoyi | 7 |
| 8 | Wanliduxing | 8 |
| 9 | Pixiejianfa | 3 |
| 10 | Jiuyinbaiguzhua | 7 |
+-----+------------------+------+
10 rows in set (0.00 sec)
mysql>
mysql> select * from tutors;
+-----+--------------+--------+------+
| tid | tname | gender | age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfend | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
9 rows in set (0.00 sec)
mysql>
mysql> select tname from tutors where tid not in (select tid from courses); # 挑选出没有教课程的老师
+-------------+
| tname |
+-------------+
| NingZhongze |
+-------------+
1 row in set (0.00 sec)
mysql>
mysql> select cid1 from students group by cid1 having count(cid1)>=2; # 被2个或2个以上学生选的课程的cid
+------+
| cid1 |
+------+
| 2 |
| 8 |
+------+
2 rows in set (0.00 sec)
mysql>
mysql> select cname from courses where cid in (select cid1 from students group by cid1 having count(cid1)>=2); # 被2个或2个以上学生选的课程的 cname
+-------------+
| cname |
+-------------+
| TiaJiquan |
| Wanliduxing |
+-------------+
2 rows in set (0.00 sec)
mysql>
执行逻辑越简单,资源消耗就越小,性能就越好
事实上不建议把几个简单查询组合成一个复杂查询
mysql> select t.tname,c.cname from tutors as t left join courses as c on t.tid=c.tid; #左外连接
+--------------+------------------+
| tname | cname |
+--------------+------------------+
| HongQigong | Jinshejianfa |
| HuangYaoshi | Hamagong |
| Miejueshitai | TiaJiquan |
| Miejueshitai | Pixiejianfa |
| OuYangfend | Qianzhuwandushou |
| YiDeng | Qishangquan |
| YuCanghai | Yiyangzhi |
| Jinlunfawang | Qiankundanuoyi |
| Jinlunfawang | Jiuyinbaiguzhua |
| HuYidao | Wanliduxing |
| NingZhongze | NULL |
+--------------+------------------+
11 rows in set (0.00 sec)
mysql>
mysql> select t.tname,c.cname from tutors as t right join courses as c on t.tid=c.tid;#右外连接
+--------------+------------------+
| tname | cname |
+--------------+------------------+
| HuangYaoshi | Hamagong |
| Miejueshitai | TiaJiquan |
| YuCanghai | Yiyangzhi |
| HongQigong | Jinshejianfa |
| OuYangfend | Qianzhuwandushou |
| YiDeng | Qishangquan |
| Jinlunfawang | Qiankundanuoyi |
| HuYidao | Wanliduxing |
| Miejueshitai | Pixiejianfa |
| Jinlunfawang | Jiuyinbaiguzhua |
+--------------+------------------+
10 rows in set (0.00 sec)
mysql>
mysql> select name,cname,tname from students,courses,tutors where students.cid1=courses.cid and courses.tid=tutors.tid;
+--------------+------------------+--------------+
| name | cname | tname |
+--------------+------------------+--------------+
| GuoJing | TiaJiquan | Miejueshitai |
| YangGuo | TiaJiquan | Miejueshitai |
| DingDian | Qishangquan | YiDeng |
| HuFei | Wanliduxing | HuYidao |
| HuangRong | Qianzhuwandushou | OuYangfend |
| YueLingshang | Wanliduxing | HuYidao |
| ZhangWuji | Hamagong | HuangYaoshi |
| Xuzhu | TiaJiquan | Miejueshitai |
+--------------+------------------+--------------+
8 rows in set (0.00 sec)
mysql>
视图: 存储下来的 select 语句,把这个select语句当作表来用,也称为虚表
基于基表的查询结果
视图一般是不允许插入的,因为真正插的是基表,可能基表有约束规则,,,但是并不意味着一定不能往里面插入数据
view
创建视图:
create view
视图不用修改的,,我们可以直接删除视图,再创建一个视图
物化视图
有些数据库支持物化视图: 可以把视图的查询结果保存下来
视图无法建索引,mysql中视图用处不大的,不建议使用(只有基表中仅能让别人看某些字段,考虑到安全时才考虑到视图),
把语句查询的结果缓存到内存中,若缓存结果大,消耗资源历害,所以我们可以把视图查询的结果保存下来到磁盘上,这就是物化视图
物化视图好处
1)不用执行底层的sql语句
物化视图坏处
1)万一基表数据更新,物化视图也得更新,这个的话代价很大
对于更新不太繁频的表,建议使用物化视图,否则不建议使用物化视图
但是mysql是不支持物化视图的,不支持在视图上创建索引
mysql中是不建议使用视图的
视图被当作表来对待的
show create + 对象:显示创建某个对象时,使用的什么语句
视图被当作表来对待的
mysql> help create view
Name: 'CREATE VIEW'
Description:
Syntax:
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
The CREATE VIEW statement creates a new view, or replaces an existing
one if the OR REPLACE clause is given. If the view does not exist,
CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does
exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW.
The select_statement is a SELECT statement that provides the definition
of the view. (When you select from the view, you select in effect using
the SELECT statement.) select_statement can select from base tables or
other views.
The view definition is "frozen" at creation time, so changes to the
underlying tables afterward do not affect the view definition. For
example, if a view is defined as SELECT * on a table, new columns added
to the table later do not become part of the view.
The ALGORITHM clause affects how MySQL processes the view. The DEFINER
and SQL SECURITY clauses specify the security context to be used when
checking access privileges at view invocation time. The WITH CHECK
OPTION clause can be given to constrain inserts or updates to rows in
tables referenced by the view. These clauses are described later in
this section.
The CREATE VIEW statement requires the CREATE VIEW privilege for the
view, and some privilege for each column selected by the SELECT
statement. For columns used elsewhere in the SELECT statement you must
have the SELECT privilege. If the OR REPLACE clause is present, you
must also have the DROP privilege for the view. CREATE VIEW might also
require the SUPER privilege, depending on the DEFINER value, as
described later in this section.
When a view is referenced, privilege checking occurs as described later
in this section.
A view belongs to a database. By default, a new view is created in the
default database. To create the view explicitly in a given database,
specify the name as db_name.view_name when you create it:
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Within a database, base tables and views share the same namespace, so a
base table and a view cannot have the same name.
Columns retrieved by the SELECT statement can be simple references to
table columns. They can also be expressions that use functions,
constant values, operators, and so forth.
Views must have unique column names with no duplicates, just like base
tables. By default, the names of the columns retrieved by the SELECT
statement are used for the view column names. To define explicit names
for the view columns, the optional column_list clause can be given as a
list of comma-separated identifiers. The number of names in column_list
must be the same as the number of columns retrieved by the SELECT
statement.
Unqualified table or view names in the SELECT statement are interpreted
with respect to the default database. A view can refer to tables or
views in other databases by qualifying the table or view name with the
proper database name.
A view can be created from many kinds of SELECT statements. It can
refer to base tables or other views. It can use joins, UNION, and
subqueries. The SELECT need not even refer to any tables. The following
example defines a view that selects two columns from another table, as
well as an expression calculated from those columns:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
A view definition is subject to the following restrictions:
o The SELECT statement cannot contain a subquery in the FROM clause.
o The SELECT statement cannot refer to system or user variables.
o Within a stored program, the definition cannot refer to program
parameters or local variables.
o The SELECT statement cannot refer to prepared statement parameters.
o Any table or view referred to in the definition must exist. However,
after a view has been created, it is possible to drop a table or view
that the definition refers to. In this case, use of the view results
in an error. To check a view definition for problems of this kind,
use the CHECK TABLE statement.
o The definition cannot refer to a TEMPORARY table, and you cannot
create a TEMPORARY view.
o Any tables named in the view definition must exist at definition
time.
o You cannot associate a trigger with a view.
o Aliases for column names in the SELECT statement are checked against
the maximum column length of 64 characters (not the maximum alias
length of 256 characters).
ORDER BY is permitted in a view definition, but it is ignored if you
select from a view using a statement that has its own ORDER BY.
For other options or clauses in the definition, they are added to the
options or clauses of the statement that references the view, but the
effect is undefined. For example, if a view definition includes a LIMIT
clause, and you select from the view using a statement that has its own
LIMIT clause, it is undefined which limit applies. This same principle
applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that
follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE,
LOCK IN SHARE MODE, and PROCEDURE.
If you create a view and then change the query processing environment
by changing system variables, that may affect the results that you get
from the view:
mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)
mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc |
+-------+
1 row in set (0.00 sec)
The DEFINER and SQL SECURITY clauses determine which MySQL account to
use when checking access privileges for the view when a statement is
executed that references the view. The valid SQL SECURITY
characteristic values are DEFINER and INVOKER. These indicate that the
required privileges must be held by the user who defined or invoked the
view, respectively. The default SQL SECURITY value is DEFINER.
If a user value is given for the DEFINER clause, it should be a MySQL
account specified as 'user_name'@'host_name' (the same format used in
the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default
DEFINER value is the user who executes the CREATE VIEW statement. This
is the same as specifying DEFINER = CURRENT_USER explicitly.
If you specify the DEFINER clause, these rules determine the valid
DEFINER user values:
o If you do not have the SUPER privilege, the only valid user value is
your own account, either specified literally or by using
CURRENT_USER. You cannot set the definer to some other account.
o If you have the SUPER privilege, you can specify any syntactically
valid account name. If the account does not actually exist, a warning
is generated.
o Although it is possible to create a view with a nonexistent DEFINER
account, an error occurs when the view is referenced if the SQL
SECURITY value is DEFINER but the definer account does not exist.
For more information about view security, see
http://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html.
Within a view definition, CURRENT_USER returns the view's DEFINER value
by default. For views defined with the SQL SECURITY INVOKER
characteristic, CURRENT_USER returns the account for the view's
invoker. For information about user auditing within views, see
http://dev.mysql.com/doc/refman/5.5/en/account-activity-auditing.html.
Within a stored routine that is defined with the SQL SECURITY DEFINER
characteristic, CURRENT_USER returns the routine's DEFINER value. This
also affects a view defined within such a routine, if the view
definition contains a DEFINER value of CURRENT_USER.
View privileges are checked like this:
o At view definition time, the view creator must have the privileges
needed to use the top-level objects accessed by the view. For
example, if the view definition refers to table columns, the creator
must have some privilege for each column in the select list of the
definition, and the SELECT privilege for each column used elsewhere
in the definition. If the definition refers to a stored function,
only the privileges needed to invoke the function can be checked. The
privileges required at function invocation time can be checked only
as it executes: For different invocations, different execution paths
within the function might be taken.
o The user who references a view must have appropriate privileges to
access it (SELECT to select from it, INSERT to insert into it, and so
forth.)
o When a view has been referenced, privileges for objects accessed by
the view are checked against the privileges held by the view DEFINER
account or invoker, depending on whether the SQL SECURITY
characteristic is DEFINER or INVOKER, respectively.
o If reference to a view causes execution of a stored function,
privilege checking for statements executed within the function depend
on whether the function SQL SECURITY characteristic is DEFINER or
INVOKER. If the security characteristic is DEFINER, the function runs
with the privileges of the DEFINER account. If the characteristic is
INVOKER, the function runs with the privileges determined by the
view's SQL SECURITY characteristic.
Example: A view might depend on a stored function, and that function
might invoke other stored routines. For example, the following view
invokes a stored function f():
CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);
Suppose that f() contains a statement such as this:
IF name IS NULL then
CALL p1();
ELSE
CALL p2();
END IF;
The privileges required for executing statements within f() need to be
checked when f() executes. This might mean that privileges are needed
for p1() or p2(), depending on the execution path within f(). Those
privileges must be checked at runtime, and the user who must possess
the privileges is determined by the SQL SECURITY values of the view v
and the function f().
The DEFINER and SQL SECURITY clauses for views are extensions to
standard SQL. In standard SQL, views are handled using the rules for
SQL SECURITY DEFINER. The standard says that the definer of the view,
which is the same as the owner of the view's schema, gets applicable
privileges on the view (for example, SELECT) and may grant them. MySQL
has no concept of a schema "owner", so MySQL adds a clause to identify
the definer. The DEFINER clause is an extension where the intent is to
have what the standard has; that is, a permanent record of who defined
the view. This is why the default DEFINER value is the account of the
view creator.
The optional ALGORITHM clause is a MySQL extension to standard SQL. It
affects how MySQL processes the view. ALGORITHM takes three values:
MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if
no ALGORITHM clause is present. For more information, see
http://dev.mysql.com/doc/refman/5.5/en/view-algorithms.html.
Some views are updatable. That is, you can use them in statements such
as UPDATE, DELETE, or INSERT to update the contents of the underlying
table. For a view to be updatable, there must be a one-to-one
relationship between the rows in the view and the rows in the
underlying table. There are also certain other constructs that make a
view nonupdatable.
The WITH CHECK OPTION clause can be given for an updatable view to
prevent inserts or updates to rows except those for which the WHERE
clause in the select_statement is true.
In a WITH CHECK OPTION clause for an updatable view, the LOCAL and
CASCADED keywords determine the scope of check testing when the view is
defined in terms of another view. The LOCAL keyword restricts the CHECK
OPTION only to the view being defined. CASCADED causes the checks for
underlying views to be evaluated as well. When neither keyword is
given, the default is CASCADED.
For more information about updatable views and the WITH CHECK OPTION
clause, see
http://dev.mysql.com/doc/refman/5.5/en/view-updatability.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/create-view.html
mysql>
mysql> create view sct as select name,cname,tname from students,courses,tutors where students.cid1=courses.cid and courses.tid=tutors.tid;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> show tables; # 看到视图 sct 被当作表了
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses |
| sct |
| students |
| tutors |
+------------------+
4 rows in set (0.00 sec)
mysql>
mysql> show table status\G
*************************** 1. row ***************************
Name: courses
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10
Avg_row_length: 1638
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 11
Create_time: 2020-09-26 17:42:33
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row *************************** #视图中一堆都为Null,只有comment中为view
Name: sct
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
*************************** 3. row ***************************
Name: students
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10
Avg_row_length: 1638
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 11
Create_time: 2020-09-25 17:23:15
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 4. row ***************************
Name: tutors
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2020-09-28 09:47:48
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
4 rows in set (0.00 sec)
mysql>
mysql> help drop view;
Name: 'DROP VIEW'
Description:
Syntax:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
DROP VIEW removes one or more views. You must have the DROP privilege
for each view. If any of the views named in the argument list do not
exist, MySQL returns an error indicating by name which nonexisting
views it was unable to drop, but it also drops all of the views in the
list that do exist.
The IF EXISTS clause prevents an error from occurring for views that
don't exist. When this clause is given, a NOTE is generated for each
nonexistent view. See [HELP SHOW WARNINGS].
RESTRICT and CASCADE, if given, are parsed and ignored.
URL: http://dev.mysql.com/doc/refman/5.5/en/drop-view.html
mysql> show create view sct\G # 显示创建视图的时候使用的是什么语句
*************************** 1. row ***************************
View: sct
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sct` AS select `students`.`name` AS `name`,`courses`.`cname` AS `cname`,`tutors`.`tname` AS `tname` from ((`students` join `courses`) join `tutors`) where ((`students`.`cid1` = `courses`.`cid`) and (`courses`.`tid` = `tutors`.`tid`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
mysql>
mysql> show create table courses; # 显示创建表的语句
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| courses | CREATE TABLE `courses` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` char(30) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
[root@mail ~]# man mysql
.............
--execute=statement, -e statement
.............
[root@mail ~]# mysql -e 'create database edb;' # -e (e execute )执行语句 # 所以我们可以在shell脚本中往表中插入许多数据
[root@mail ~]# mysql -e 'show databases;' # 可看到数据库 edb
+--------------------+
| Database |
+--------------------+
| information_schema |
| edb |
| hellodb |
| jiaowu |
| mydb |
| mysql |
| performance_schema |
| students |
| test |
| test2 |
| testdb |
+--------------------+
[root@mail ~]#
[root@mail ~]# mysql -e 'select * from jiaowu.students;'
+-----+--------------+------+--------+------+------+------+---------------------+
| sid | name | age | gender | cid1 | cid2 | tid | createTime |
+-----+--------------+------+--------+------+------+------+---------------------+
| 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:10:10 |
| 2 | YangGuo | 17 | M | 2 | 3 | 1 | 2012-04-06 10:10:10 |
| 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 5 | HuangRong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:10:10 |
| 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
| 9 | LiHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:10:10 |
| 10 | YiLin | 19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
[root@mail ~]#
[root@mail ~]# mysql -e "insert into jiaowu.students (name,age,gender,cid1,cid2,tid) values('stu1',23,'F',4,1,6)" #脚本插入数据
[root@mail ~]# mysql -e "insert into jiaowu.students (name,age,gender,cid1,cid2,tid) values('stu2',23,'F',4,1,6)"
[root@mail ~]#