要求服务器告诉你它的版本号和当前日期
mysql> select version(),current_date;
+------------+--------------+
| version() | current_date |
+------------+--------------+
| 5.0.22-log | 2011-08-10 |
+------------+--------------+
1 row in set (0.16 sec)
使用SHOW语句找出在服务器上当前存在什么数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
创建一个数据库
create database db_gou;
删除一个数据库
mysql> drop database db_gou;
Query OK, 0 rows affected (0.05 sec)
mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cid | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
-------------------------------------------------------------
创建一个表
create table table_name
mysql> create table person (id integer primary key auto_increment,
-> cid varchar(20));
Query OK, 0 rows affected (0.04 sec)
create table users (
-> id int auto_increment primary key,
-> name varchar(10),
-> age int);
create table person (id integer primary key auto_increment,
mnumber varchar(4),
name varchar(20),
cid varchar(20),
dates Date,
phone varchar(13),
img varchar,
tf varchar)
primary key 主键 out_increment自动增长 not null
reference_definition
[NOT NULL | NULL] [DEFAULT default_value](缺省值) [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition](参考定义)
-----------------------------------------------------------------------
create table user(id int auto _incerement primary key,name varchar(10),sex char(2) default'男');
向users表中放了两条数据,如下
insert into users(name,age)values('才子',32),('gdfd',34);
查看表如下
mysql> select * from users;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | gouanming | 25 |
| 2 | wuwu | 24 |
| 3 | 才子 | 32 |
| 4 | gdfd | 34 |
+----+-----------+------+
4 rows in set (0.00 sec)
查看users中的name
mysql> select name from users;
+-----------+
| name |
+-----------+
| gouanming |
| wuwu |
| 才子 |
| gdfd |
+-----------+
别名查看
mysql> select name a from users;
+-----------+
| a |
+-----------+
| gouanming |
| wuwu |
| 才子 |
| gdfd |
+-----------+
4 rows in set (0.00 sec)
条件查看
mysql> select * from users where id =2;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | wuwu | 24 |
+----+------+------+
1 row in set (0.02 sec)
修改一条数据
mysql> update users set name='qq',age=22 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除一条数据
mysql> delete from users where id=1;
Query OK, 1 row affected (0.00 sec)
--------------------------------
修改数据表alter change
mysql> alter table book
-> change b_name b_name varchar(30);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
增加数据表 alter addmysql> alter table book
-> add b_key varchar(30);
mysql> desc book;
-> +-----------+-------------+------+-----+---------+----------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| b_name | varchar(30) | YES | | NULL | |
| b_jiagai | int(11) | YES | | NULL | |
| b_suoming | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
增加数据表主外建
alter table book
-> add foreign key(u_id)references users(id);
创建表与外键关连
mysql> create table mybook(
-> id int auto_increment primary key,
-> my_name varchar(20),
-> b_id int,
-> foreign key(b_id)references book(id));
Query OK, 0 rows affected (0.01 sec)
---------------------------------------
关连查询
mysql> select b.b_name,u.name from book b,users u where u.id=b.u_id;
+--------+-----------+
| b_name | name |
+--------+-----------+
| java | 苟安明 |
+--------+-----------+
1 row in set (0.03 sec)
------------------------------------
mysql> create table team(
-> id int auto_increment primary key,
-> t_name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> create table student(
-> id int auto_increment primary key,
-> name varchar(20),
-> age int(3),
-> sdate Date);
Query OK, 0 rows affected (0.00 sec)
mysql> alter table student
-> add t_id int
-> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student
-> add foreign key(t_id)references team(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table course(
-> id int auto_increment primary key,
-> c_name varchar(30),
-> c_suoming varchar(50));
Query OK, 0 rows affected (0.01 sec)
多对多
mysql> create table st_cou(
-> st_id int not null,
-> cou_id int not null,
-> primary key(st_id,cou_id),
-> foreign key(st_id) references student(id),
-> foreign key(cou_id) references course(id));
Query OK, 0 rows affected (0.00 sec)
---------------------------------
mysql> select version(),current_date;
+------------+--------------+
| version() | current_date |
+------------+--------------+
| 5.0.22-log | 2011-08-10 |
+------------+--------------+
1 row in set (0.16 sec)
使用SHOW语句找出在服务器上当前存在什么数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
创建一个数据库
create database db_gou;
删除一个数据库
mysql> drop database db_gou;
Query OK, 0 rows affected (0.05 sec)
mysql> desc person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cid | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
-------------------------------------------------------------
创建一个表
create table table_name
mysql> create table person (id integer primary key auto_increment,
-> cid varchar(20));
Query OK, 0 rows affected (0.04 sec)
create table users (
-> id int auto_increment primary key,
-> name varchar(10),
-> age int);
create table person (id integer primary key auto_increment,
mnumber varchar(4),
name varchar(20),
cid varchar(20),
dates Date,
phone varchar(13),
img varchar,
tf varchar)
primary key 主键 out_increment自动增长 not null
reference_definition
[NOT NULL | NULL] [DEFAULT default_value](缺省值) [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition](参考定义)
-----------------------------------------------------------------------
create table user(id int auto _incerement primary key,name varchar(10),sex char(2) default'男');
向users表中放了两条数据,如下
insert into users(name,age)values('才子',32),('gdfd',34);
查看表如下
mysql> select * from users;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | gouanming | 25 |
| 2 | wuwu | 24 |
| 3 | 才子 | 32 |
| 4 | gdfd | 34 |
+----+-----------+------+
4 rows in set (0.00 sec)
查看users中的name
mysql> select name from users;
+-----------+
| name |
+-----------+
| gouanming |
| wuwu |
| 才子 |
| gdfd |
+-----------+
别名查看
mysql> select name a from users;
+-----------+
| a |
+-----------+
| gouanming |
| wuwu |
| 才子 |
| gdfd |
+-----------+
4 rows in set (0.00 sec)
条件查看
mysql> select * from users where id =2;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | wuwu | 24 |
+----+------+------+
1 row in set (0.02 sec)
修改一条数据
mysql> update users set name='qq',age=22 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
删除一条数据
mysql> delete from users where id=1;
Query OK, 1 row affected (0.00 sec)
--------------------------------
修改数据表alter change
mysql> alter table book
-> change b_name b_name varchar(30);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
增加数据表 alter addmysql> alter table book
-> add b_key varchar(30);
mysql> desc book;
-> +-----------+-------------+------+-----+---------+----------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| b_name | varchar(30) | YES | | NULL | |
| b_jiagai | int(11) | YES | | NULL | |
| b_suoming | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
增加数据表主外建
alter table book
-> add foreign key(u_id)references users(id);
创建表与外键关连
mysql> create table mybook(
-> id int auto_increment primary key,
-> my_name varchar(20),
-> b_id int,
-> foreign key(b_id)references book(id));
Query OK, 0 rows affected (0.01 sec)
---------------------------------------
关连查询
mysql> select b.b_name,u.name from book b,users u where u.id=b.u_id;
+--------+-----------+
| b_name | name |
+--------+-----------+
| java | 苟安明 |
+--------+-----------+
1 row in set (0.03 sec)
------------------------------------
mysql> create table team(
-> id int auto_increment primary key,
-> t_name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> create table student(
-> id int auto_increment primary key,
-> name varchar(20),
-> age int(3),
-> sdate Date);
Query OK, 0 rows affected (0.00 sec)
mysql> alter table student
-> add t_id int
-> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student
-> add foreign key(t_id)references team(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table course(
-> id int auto_increment primary key,
-> c_name varchar(30),
-> c_suoming varchar(50));
Query OK, 0 rows affected (0.01 sec)
多对多
mysql> create table st_cou(
-> st_id int not null,
-> cou_id int not null,
-> primary key(st_id,cou_id),
-> foreign key(st_id) references student(id),
-> foreign key(cou_id) references course(id));
Query OK, 0 rows affected (0.00 sec)
---------------------------------