Mysql-基础
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql-基础,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含11244字,纯文字阅读大概需要17分钟。
内容图文
![Mysql-基础](/upload/InfoBanner/zyjiaocheng/917/50a89c990df74597ab0eabc96d7282b0.jpg)
1. 新建数据库,表
1. 创建数据库 mysql> create database maizi1; Query OK, 1 row affected (0.01 sec) mysql> create database if not exists maizi default character set "utf8"; Query OK, 1 row affected (0.00 sec) mysql> use maizi; Database changed mysql> 2. 创建数据库表 user create table if not exists user( id smallint, username varchar(20), age tinyint, sex enum("男","女","保密"), email varchar(50), addr varchar(200), birth YEAR, salary float(8,2), tel int, married tinyint(1) comment "0,代表未婚.非零代表已经结婚." )engine =innodb charset =utf8; 3. 创建 数据库表 course create table if not exists course( cid tinyint, couseName varchar(50), courseDesc varchar(200) ); 4. 创建表cms create table if not exists cms_cate( id tinyint, cateName varchar(50), cateDesc varchar(200) )engine=myisam charset =utf8; 5. 创建表cms_news create table if not exists cms_news( id int, title varchar(50), content text, pubTime int, clickNum int, isTop tinyint(1) comment "0代表不置顶,1代表置顶." );
2.查看数据库表
6. 查看表结构 mysql> describe user; +----------+------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------+------+-----+---------+-------+ | id | smallint(6) | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | enum('男','女','保密') | YES | | NULL | | | email | varchar(50) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | | birth | year(4) | YES | | NULL | | | salary | float(8,2) | YES | | NULL | | | tel | int(11) | YES | | NULL | | | married | tinyint(1) | YES | | NULL | | +----------+------------------------+------+-----+---------+-------+ mysql> show columns from user; +----------+------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------+------+-----+---------+-------+ | id | smallint(6) | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | enum('男','女','保密') | YES | | NULL | | | email | varchar(50) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | | birth | year(4) | YES | | NULL | | | salary | float(8,2) | YES | | NULL | | | tel | int(11) | YES | | NULL | | | married | tinyint(1) | YES | | NULL | | +----------+------------------------+------+-----+---------+-------+ 10 rows in set (0.00 sec)
3.创建主键与自增长
7. 创建主键.(可以省掉primary 关键字.) mysql> create table if not exists user1( -> id int primary key, -> username varchar(20) -> ); Query OK, 0 rows affected, 1 warning (0.00 sec) 查看表. mysql> desc user1; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | username | varchar(20) | YES | | NULL | | +----------+ -------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> 8.设置复合主键. mysql> create table if not exists user2( -> id int, -> username varchar(20), -> card char(18), -> primary key(id,card) -> ); Query OK, 0 rows affected (0.04 sec) 9. 自增长. auto_increment mysql> create table if not exists user10( -> id smallint key auto_increment, -> username varchar(20) -> ); Query OK, 0 rows affected (0.04 sec) mysql> desc user10; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | smallint(6) | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
4.非空、默认与唯一约束
10. 非空. NOT NULL. mysql> create table if not exists user7( -> id int unsigned key auto_increment, -> username varchar(20) not null, -> password char(32) not null, -> age tinyint unsigned -> ); Query OK, 0 rows affected (0.06 sec) mysql> desc user7; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | password | char(32) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert user7(username,password) values("king","king") -> ; Query OK, 1 row affected (0.00 sec) 11. DEFAULT 默认值. mysql> create table if not exists user8( -> id int unsigned key auto_increment, -> username varchar(20) not null, -> password char(32) not null, -> age tinyint unsigned default 19, -> addr varchar(50) not null default "北京" -> ); Query OK, 0 rows affected (0.04 sec) mysql> desc user8 -> ; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | password | char(32) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | 19 | | | addr | varchar(50) | NO | | 北京 | | +----------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) 12. 唯一约束.unique mysql> create table if not exists user11( -> id tinyint unsigned key auto_increment, -> username varchar(20) not null unique , -> card char(18) unique -> ); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> desc user11; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | card | char(18) | YES | UNI | NULL | | +----------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert user11(username) value("a"); Query OK, 1 row affected (0.01 sec) mysql> select * from user11; +----+----------+------+ | id | username | card | +----+----------+------+ | 1 | a | NULL | +----+----------+------+ 1 row in set (0.00 sec)
5.重命名、添加字段
13. 重命名表名. mysql> create table user13( -> id smallint unsigned key auto_increment, -> username varchar(20)not null unique, -> password char(32) not null, -> email varchar(50) not null default "393376780@qq.com", -> age tinyint unsigned default 18, -> addr varchar(200) not null default " 北京", -> salary float(6,2), -> regTime int unsigned, -> face char(100) not null default "default.jpg" -> ); Query OK, 0 rows affected (0.03 sec) mysql> desc user13; +----------+----------------------+------+-----+------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+------------------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | | 393376780@qq.com | | | age | tinyint(3) unsigned | YES | | 18 | | | addr | varchar(200) | NO | | 北京 | | | salary | float(6,2) | YES | | NULL | | | regTime | int(10) unsigned | YES | | NULL | | | face | char(100) | NO | | default.jpg | | +----------+----------------------+------+-----+------------------+----------------+ 9 rows in set (0.00 sec) mysql> alter table user13 rename to user31; Query OK, 0 rows affected (0.01 sec) mysql> rename table user31 to user13; Query OK, 0 rows affected (0.01 sec) 14. 添加字段. mysql> alter table user13 add card char(18); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 #在username字段后面添加字段. mysql> alter table user13 add test10 int not null default 100 after username; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 #在第一个字段位置添加. mysql> alter table user13 add test100 int not null default 100 first; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 15. 删除字段. mysql> alter table user13 drop test10; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 16. 修改字段属性 mysql> alter table user13 modify card varchar(200); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table user13 change card cardchange char(44); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user13; +------------+----------------------+------+-----+------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------------------+------+-----+------------------+----------------+ | test100 | int(11) | NO | | 100 | | | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | | 393376780@qq.com | | | age | tinyint(3) unsigned | YES | | 18 | | | addr | varchar(200) | NO | | 北京 | | | salary | float(6,2) | YES | | NULL | | | regTime | int(10) unsigned | YES | | NULL | | | face | char(100) | NO | | default.jpg | | | cardchange | char(44) | YES | | NULL | | +------------+----------------------+------+-----+------------------+----------------+ 11 rows in set (0.00 sec)
内容总结
以上是互联网集市为您收集整理的Mysql-基础全部内容,希望文章能够帮你解决Mysql-基础所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。