mysql基础

要求服务器告诉你它的版本号和当前日期
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)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－