|
从今天开始学习mysql数据库，希望以后能够记录自己学习的点点滴滴 mysql中创建数据表 1.创建一个数据库为test_db mysql> create database test_db; Query OK, 1 row affected (0.00 sec) 切换到test_db上 mysql> use test_db; Database changed mysql> 2.创建一个表tb_emp1 create table tb_emp1 ( id INT(11), name VARCHAR(25), deptid INT (11), salary FLOAT ); mysql> create table tb_emp1 -> ( -> id INT(11), -> name VARCHAR(25), -> deptid INT (11), -> salary FLOAT -> ); Query OK, 0 rows affected (0.09 sec)
mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_emp1 | +-------------------+ 1 row in set (0.01 sec)
mysql> show create table tb_emp1 \G; *************************** 1. row *************************** Table: tb_emp1 Create Table: CREATE TABLE `tb_emp1` ( `id` int(11) DEFAULT NULL, `name` varchar(25) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified mysql> 3.使用主键约束 语法是字段名数据类型 primary key [默认值] create table tb_emp2 ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptid INT (11), salary FLOAT ); 或者如下 create table tb_emp2 ( id INT(11) , name VARCHAR(25), deptid INT (11), salary FLOAT PRIMARY KEY (id) );
mysql> create table tb_emp2 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(25), -> deptid INT (11), -> salary FLOAT -> ); Query OK, 0 rows affected (0.03 sec)
mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_emp1 | | tb_emp2 | +-------------------+ 2 rows in set (0.00 sec)
mysql> show create table tb_emp2 \G; *************************** 1. row *************************** Table: tb_emp2 Create Table: CREATE TABLE `tb_emp2` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql>
4.表中使用外键 语法[constraint <外键名>] foreign key 字段名1 [,字段名2,…] references <主表名> 主键列1[,主键列2,…] 主表： create table tb_dept ( deptid int(11) primary key, name varchar(22) not null, location varchar(50) ) 从表： create table tb_emp3 ( id int(11) primary key, name varchar(25), location varchar(50), deptid int(11), salary float, constraint fk_emp_dept foreign key (deptid) references tb_dept (deptid) );
mysql> create table tb_dept -> ( -> deptid int(11) primary key, -> name varchar(22) not null, -> location varchar(50) -> ) -> ; Query OK, 0 rows affected (0.05 sec) mysql> create table tb_emp3 -> ( -> id int(11) primary key, -> name varchar(25), -> location varchar(50), -> deptid int(11), -> salary float, -> constraint fk_emp_dept foreign key (deptid) references tb_dept (deptid) -> ); Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_dept | | tb_emp1 | | tb_emp2 | | tb_emp3 | +-------------------+ 4 rows in set (0.00 sec)
mysql> show create table tb_emp3 \G; *************************** 1. row *************************** Table: tb_emp3 Create Table: CREATE TABLE `tb_emp3` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `location` varchar(50) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_emp_dept` (`deptid`), CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptid`) REFERENCES `tb_dept` (`deptid` ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified mysql>
5.表空使用非空约束(not null )和唯一约束(unique) 语法字段名数据类型 not null , 字段名数据类型 unique create table tb_dept1 ( deptid int(11) primary key, name varchar(22) unique, location varchar(50) not null );
mysql> create table tb_dept1 -> ( -> deptid int(11) primary key, -> name varchar(22) unique, -> location varchar(50) not null -> ); Query OK, 0 rows affected (0.05 sec)
mysql> show create table tb_dept1 \G; *************************** 1. row *************************** Table: tb_dept1 Create Table: CREATE TABLE `tb_dept1` ( `deptid` int(11) NOT NULL, `name` varchar(22) DEFAULT NULL, `location` varchar(50) NOT NULL, PRIMARY KEY (`deptid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified
6.使用默认值 语法字段名数据库类型 default
mysql> create table tb_emp4 -> ( -> id int(11) primary key, -> name varchar(25), -> location varchar(50), -> deptid int(11) default 1111, -> salary float -> ); Query OK, 0 rows affected (0.05 sec) mysql> show create table tb_emp4 \G; *************************** 1. row *************************** Table: tb_emp4 Create Table: CREATE TABLE `tb_emp4` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `location` varchar(50) DEFAULT NULL, `deptid` int(11) DEFAULT '1111', `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified
mysql>
7.设置表的属性字段增加（auto_increment） mysql> create table tb_emp5 -> ( -> id int(11) primary key auto_increment, -> name varchar(25), -> location varchar(50), -> deptid int(11) default 1111, -> salary float -> ); Query OK, 0 rows affected (0.13 sec)
mysql> show tables ; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_dept | | tb_dept1 | | tb_emp1 | | tb_emp2 | | tb_emp3 | | tb_emp4 | | tb_emp5 | +-------------------+ 7 rows in set (0.00 sec) mysql> desc tb_emp5; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(25) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | | deptid | int(11) | YES | | 1111 | | | salary | float | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
mysql> show create table tb_emp5 \G; *************************** 1. row *************************** Table: tb_emp5 Create Table: CREATE TABLE `tb_emp5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) DEFAULT NULL, `location` varchar(50) DEFAULT NULL, `deptid` int(11) DEFAULT '1111', `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified
mysql> 插入如下语句验证；
mysql> insert into tb_emp5 (name,location,salary) values('ww','dd','1000'),('ja ck','ds','3000'),('rrw','dd','1500'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tb_emp5; +----+------+----------+--------+--------+ | id | name | location | deptid | salary | +----+------+----------+--------+--------+ | 1 | ww | dd | 1111 | 1000 | | 2 | jack | ds | 1111 | 3000 | | 3 | rrw | dd | 1111 | 1500 | +----+------+----------+--------+--------+ 3 rows in set (0.00 sec)
mysql> (责任编辑：IT) |